Choose Language Hide Translation Bar
Highlighted
Newbie2Jumpie
Level IV

How to filter / cleanse data JMP using a list [tipp, JSL, scripting]

After receiving so much great support, I thought it was about time I returned this favour. Below you'll find a little program that might make it easier to filter data tables. Hard encoding by "entry" is one way to filter entries; this is tedious and prone to typos. An alternative is to filter entries by their "position" in a filter list. This approach just assumes that the sequence of fields in the filter list does not change. To make sure, just check the JMP log. As a quality check, it will tell you which entries were excluded .

Good luck

Newbie2Jumpie

			names default to here( 1 );
			// Open a sample data table
			dt = Open( "$SAMPLE_DATA/big class.jmp" );

			/* Get entries/levels from Column NAME and store them in NAME_LIST */
                    NAME_LIST = dt:NAME << get values ;
					NAME_LIST  ;					
			/* Remove selected entries ("levels") on given position in list = JMP table */
	                remove from(NAME_LIST, {1,3,5} );  /* {removes: "KATIE", "JANE", "LILLIE" */
	                
			/* Cleans data by removing rows using level list  */
	            dt << Select where(contains(NAME_LIST, :NAME)) ;
	      	    dt_2 = dt   << subset(selected rows(1),  all columns);
	      	    
  	    	/* Results in a subset without the levels "KATIE", "JANE", "LILLIE" */
	      	    
				close (dt, nosave) ;	

 

2 REPLIES 2
Highlighted
Newbie2Jumpie
Level IV

Re: How to filter / cleanse data JMP using a list [tipp, JSL, scripting] - How to make it work for duplicates?


Hi all,
Last Friday I provided a small JMP that helps to filter data by their "position" in a filter list.
In a PM I received a message that this works only as long the entries scanned for the filter list are unique. For example, if alle entries filtered from NAME are unique, e.g. if you want to filter out "Katie"; "Jane" or "Lillie". Now, "Robert" appears two times in "BIG CLASS". As soon as you want to exclude "Robert" too, then this approach gets stuck. Any idea how to solve that?
Newbie2Jumpie
			names default to here( 1 );
			// Open a sample data table
			dt = Open( "$SAMPLE_DATA/big class.jmp" );

			/* Get entries/levels from Column NAME and store them in NAME_LIST */
                    NAME_LIST = dt:NAME << get values ;
					NAME_LIST  ;					
			/* Remove selected entries ("levels") on given position in list = JMP table */
	                remove from(NAME_LIST, {1,3,5} );  /* {removes: "KATIE", "JANE", "LILLIE" */
	                
			/* Cleans data by removing rows using level list  */
	            dt << Select where(contains(NAME_LIST, :NAME)) ;
	      	    dt_2 = dt   << subset(selected rows(1),  all columns);
	      	    
  	    	/* Results in a subset without the levels "KATIE", "JANE", "LILLIE" */
	      	    
				close (dt, nosave) ;	

 


 

Highlighted
txnelson
Super User

Re: How to filter / cleanse data JMP using a list [tipp, JSL, scripting] - How to make it work for duplicates?

Here is one way to do it.  The only complexity is to convert the matrix of row numbers into a list

Names Default To Here( 1 );
// Open a sample data table
dt = Open( "$SAMPLE_DATA/big class.jmp" );

/* Get entries/levels from Column NAME and store them in NAME_LIST */

eval(parse("NAME_LIST =as list (" || char(index(1,n rows(dt))) || ")[1];"));

NAME_LIST ;					
/* Remove selected entries ("levels") on given position in list = JMP table */
Remove From( NAME_LIST, {1, 3, 5} );  /* {removes: "KATIE", "JANE", "LILLIE" */
	                
/* Cleans data by removing rows using level list  */
dt << Select where( Contains( NAME_LIST, Row() ) );
dt_2 = dt << subset( selected rows( 1 ), all columns );
	      	    
/* Results in a subset without the levels "KATIE", "JANE", "LILLIE" */
	      	    
Close( dt, nosave );
Jim
Article Labels

    There are no labels assigned to this post.