topic Re: Keep Columns Matching a List, Remove the Rest in Discussions

Keep Columns Matching a List, Remove the Rest

uProf — Fri, 09 Jun 2023 18:02:46 GMT

Hi -

I would like to remove columns (based on column names) from a data table that do not match a list. I have following JSL code so far but it is not working.

Input appreciated.

Names Default To Here( 1 );
bigClass = Open( "$SAMPLE_DATA/Big Class.JMP" );
dt = Current Data Table();

col_of_interest = {"name", "age", "weight"};

col_names = dt << Get Column Names( String );

For(j = 1, j <= N Items( col_of_interest ), j++,
   For( i = N Items( col_names ), i > 0, i--,  
		if ( not( col_names[i] == col_of_interest[j],
			 Remove From( col_names, i ))     	
		  );  
	);
);
dtNew = dt << subset( all rows, columns( col_names ) ); Wait( 0 );

Re: Keep Columns Matching a List, Remove the Rest

jthi — Fri, 15 Oct 2021 19:15:26 GMT

I would loop over all columns in the data table and remove when needed (if you want to keep to your idea, I can also help debugging that):

Names Default To Here(1);
bigClass = Open("$SAMPLE_DATA/Big Class.JMP");
dt = Current Data Table();

col_of_interest = {"name", "age", "weight"};

col_names = dt << Get Column Names(String);

For(i = 1, i <= N Items(col_names), i++,
	If(!Contains(col_of_interest, col_names[i]),
		dt << Delete Columns(col_names[i]);
	);
);
Wait(0);

Also I would suggest not using J as variable name, as JMP has a matrix function J().

Edit:

Also there is no need to use Current Data Table() (and you shouldn't) because you already have the reference after opening the table. Also here is my preferred method to remove columns (using associative array to get only the extra columns and removing those):

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.JMP");

col_of_interest = {"name", "age", "weight"};

//get columns which are "extra"
col_names_aa = Associative Array(dt << Get Column Names(String));
col_names_aa << Remove(Associative Array(col_of_interest));
col_names_to_remove = col_names_aa << get keys;
Show(col_names_to_remove);

dt << Delete Columns(col_names_to_remove);

Re: Keep Columns Matching a List, Remove the Rest

ngambles — Fri, 15 Oct 2021 19:13:31 GMT

When comparing two lists, I find it easier to make a third list to contain the results while working through the loops and then use the third list to take action when all the looping is complete. The code below may accomplish what you are attempting.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.JMP" );

col_of_interest = {"name", "age", "weight"};

col_names = dt << Get Column Names( String );

col_list = {};

For( i = N Items( col_names ), i > 0, i--,  
	if(contains(col_of_interest, col_names[i]),
		insert into(col_list, col_names[i])
	); 
);

dtNew = dt << subset( all rows, columns( col_list ) ); 
Wait( 0 );

Re: Keep Columns Matching a List, Remove the Rest

uProf — Fri, 15 Oct 2021 19:14:21 GMT

Thanks for the input and the tip on matrix function J, jthi. Let me retry.

Re: Keep Columns Matching a List, Remove the Rest

ih — Fri, 15 Oct 2021 19:25:05 GMT

Another option if using JMP 16:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.JMP" );

//list of columns to include
col_of_interest = {"name", "age", "weight", "does not exist"};

// Option 1 - subset table
// Filter out columns in the list that aren't in the table and subset
cols_checked = filter each({cn}, dt << Get Column Names( String ), n items(loc( col_of_interest, cn ))>0);
dtNew = dt << subset( all rows, columns( cols_checked ) );

// Option 2 - delete columns from original table
for each({c}, dt << get column references, if( n items( loc( col_of_interest, c << get name ) ) == 0, dt << delete columns(c) ) );

Re: Keep Columns Matching a List, Remove the Rest

Mark_Bailey — Mon, 18 Oct 2021 13:36:50 GMT

As a matter of fact, JMP will not confuse the variable j and the function J() because when you use the name of a function, it must be followed by the parentheses, or it will be scoped as something else, like a variable if that name exists.

But I take your warning seriously in general because names are important in JSL!