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

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

bk — Sun, 22 Jul 2018 23:48:13 GMT

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();
);

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

pmroz — Sun, 22 Jul 2018 23:49:25 GMT

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 );

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

ms — Sun, 22 Jul 2018 23:58:31 GMT

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 );