cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bk
bk
Level I

how to make a subset of a table and append data from loop

Hi,

 

I have a set of data, (see attached). i want to check every row on a column (against a certain criteria) and then isolate rows that do not meet that criteria and subset them into a new data table. i want to be able to repeat this for multiple columns (where each has its own criteria) and continue adding to this subset table that i originally created. i'm having some trouble with this and was hoping if someone could help.

 

here is what i was doing:

 

path = "xxxxxxx/";
data_table = Open( path || "Sample Data.jmp" );
Sub_Table = New Table( "Rejected Parts Table", invisible );
 
New_table = data_table;
For( k = 1, k < N Col( New_table ), k++,
	Print( k );
	New_table << select where( Column( 1 + k )[] >= 0.2 | Column( 1 + k )[] <= 0.02 );
	alp = New_table << Subset( Selected Rows, output table name( "subset" ) );
	alp << Concatenate( Sub_Table, output table name( "Rejected Parts Table" ) ) <<
	Save( path || "Rejected Parts.jmp" );
	Close( alp );
	New_table << delete rows();
);

 

2 REPLIES 2
pmroz
Super User

Re: how to make a subset of a table and append data from loop

This one was a bit tricky.  The main fix is to use AS COLUMN in your select statement.  I changed the logic to use get rows where and build up a list of rows to subset/delete.  The get_unique_values function is courtesy of this forum!

 

get_unique_values = Function( {in_list},
	{Default Local},
	tmp = [=> 0];
	Insert Into( tmp, in_list );
	tmp << get keys;
);
 
path = "c:\temp\";
New_table = Open( path || "Sample Data.jmp" );
 
all_selected_rows = [];
 
For( k = 1, k < N Col( New_table ), k++,
	Print( k );
 
   	selected_rows = new_table << get rows where( As Column( 1 + k ) >= .2 | As Column( 1 + k ) <= .02 );
 
// Append the rows we just found to the master list
   	If( N Rows( selected_rows ) > 0,
		all_selected_rows = all_selected_rows |/ selected_rows
	);
);
 
// Convert matrix to a list and get unique values
all_selected_list = get_unique_values( As List( all_selected_rows ) );
 
alp = New_table << Subset( rows( all_selected_list ), output table name( "Rejected Parts Table" ) );
 
alp << Save( path || "Rejected Parts.jmp" );
 
Close( alp );
 
New_table << delete rows( all_selected_list );

 

ms
Super User (Alumni) ms
Super User (Alumni)

Re: how to make a subset of a table and append data from loop

An alternative approach using the same criteria for all columns:

 

dt = Data Table( "Sample Data.jmp" );
r = Loc( V Sum( Transpose( !(0.02 < dt << get as matrix < 0.2) ) ) );
dt << Subset( rows( r ), output table name( "Rejected Parts Table" ) );
dt << delete rows( r );

Slightly more complicated when each column has its own criteria, for example:

dt = Data Table( "Sample Data.jmp" );
upper = [0.2 0.3 0.2 0.4 0.2];
lower = [0.02 0.03 0.02 0.04 0.02];
r = Loc( V Sum( Transpose( !(Repeat( lower, N Row( dt ) ) < dt << get as matrix < Repeat( upper, N Row( dt ) )) ) ) );
dt << Subset( rows( r ), output table name( "Rejected Parts Table" ) );
dt << delete rows( r );