topic Creating column subset based on list which may contain non-existent columns in Discussions

Creating column subset based on list which may contain non-existent columns

simon_2 — Sat, 10 Jun 2023 23:27:33 GMT

Hi Users,

I read through a prior discussion related to this topic but did not find all the answers.

I would like to know how to create a column subset based on a given list of columns. I know that you can easily do this when the columns in the given list exist in the datatable you would like to subset.

What I want to know is how do you make the script robust so that it does not give the error that "Column not found in access or evaluation of 'Bad Argument' in the case that a given column in the list does not exist.

For example, in my script below, I want to still be able to create a subset of "Big Class.jmp" with columns, name, age and height because they exist but my script breaks down as it is because I have a non-existent column in the given list called, none.

I tried putting Try() around the code but no success.

cols_of_interest = {:name, :age, :height, :none};
show(cols_of_interest);

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
big_class_cols = dt << Get Column Names();
show(big_class_cols);

subDt1 = dt << Subset(
	Columns( cols_of_interest ),
	Output Table Name( "Big Class 2" )
);

Thanks,

Simon

Re: Creating column subset based on list which may contain non-existent columns

txnelson — Sat, 20 Mar 2021 04:57:05 GMT

Here is a simple way to handle this. It simply checks to see if each column from the cols_of_interest list exists in the data table, and if not, it removes it from the cols_of_interest list.

names default to here(1);

cols_of_interest = {:name, :age, :height, :none};
show(cols_of_interest);

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

For(i=nitems(cols_of_interest), i>=1, i--,
	if(try(column(dt,char(cols_of_interest[i]))<<get name,"") == "",
		remove from(cols_of_interest,i,1);
));

subDt1 = dt << Subset(
	Columns( cols_of_interest ),
	Output Table Name( "Big Class 2" )
);

Re: Creating column subset based on list which may contain non-existent columns

ms — Sat, 20 Mar 2021 16:57:50 GMT

Here's a way to use <<intersect to determine the columns that exist in both lists.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
cols_of_interest = {name, age, height, none}; //names without scoping colon

AA1 = Associative Array( dt << Get Column Names() );
AA2 = Associative Array( cols_of_interest );
cols = AA2 << intersect( AA1 ) << get keys;

subDt1 = dt << Subset( Columns( cols ), Output Table Name( "Big Class 2" ) );

Re: Creating column subset based on list which may contain non-existent columns

simon_2 — Sun, 21 Mar 2021 01:41:37 GMT

Very elegant solution. It works great.
Thanks Jim!