Subscribe Bookmark RSS Feed

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

bk

Community Trekker

Joined:

Jul 13, 2013

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
pmroz

Super User

Joined:

Jun 23, 2011

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 (nrows(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

Joined:

Jun 23, 2011

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, nrow(dt)) < dt<<get as matrix < repeat(upper, nrow(dt))) ) ) );

dt << Subset( rows( r ), output table name( "Rejected Parts Table" ) );

dt << delete rows( r );