topic Re: Filter table columns based on a list in Discussions

Filter table columns based on a list

Shiz — Mon, 11 May 2020 17:03:29 GMT

Hi all,

I've been using JMP Pro for sometime, although I've never run scripts with it. I am also exploring JMP Genomics, since I do use a fair amount of 'omics' analyses using various platforms. For now, I'm in the JMP Pro world and I am trying to figure out how to filter data tables.

One thing I cannot figure out is how to filter (or create a new table) a table based on a list (containing column names) or on a results of a test. For example, I have a wide table (Table 1) of 600 rows x 10,000 columns. I have another table (Table 2) with a single column of column IDs from Table 1 (200 column names).

Is there a way to:

Make column groups in Table 1 based on the column IDs in Table 2?
Alternatively, make a subset of Table 1, based on the column IDs in Table 2?

I think either of these will help with my workflow, but I have not been able to find a solution on the forums here or within the help guide.

Thanks in advance!

Jonathan

Re: Filter table columns based on a list

txnelson — Mon, 11 May 2020 17:55:17 GMT

Here is a simple script that shows you both of the requests you specified

names default to here(1);
dt=open("$SAMPLE_DATA/semiconductor capability.jmp");

dtSingle = New Table("Single Column", new column("Cols", character,values({"PNP2","NPN3","INM1"})));

// Create a group
theList = dtSingle:Cols << get values;
dt << group columns( theList);

// Create a subset
// Select columns in data table dt, that are in dtSingle
try( dt << Select Columns( dtSingle:Cols << get values ));
dtSubset = dt << subset( selected rows(0), selected columns(1) );

Re: Filter table columns based on a list

Shiz — Wed, 13 May 2020 14:06:44 GMT

Thanks Jim!

I was able to get the "create a subset" script to work on my 2 data tables, but the "create a group" on those same 2 tables doesn't seem to create the group in the main table. It works fine on the sample data example though. Probably missing something obvious :) Thanks for your help!

names default to here(1);
dt = Current Data Table();
dtSingle = Open ("TCAlist.jmp");

// Create a group
theList = dtSingle:Cols << get values;
dt << group columns(theList);

// Create a subset
// Select columns in data table dt, that are in dtSingle
try( dt << Select Columns( dtSingle:Cols << get values ));
dtSubset = dt << subset( selected rows(0), selected columns(1) );

Re: Filter table columns based on a list

txnelson — Wed, 13 May 2020 14:29:20 GMT

What messages are in the log? Are the columns all spelled correctly? Is there and upper/lower case issue???

Re: Filter table columns based on a list

Shiz — Wed, 13 May 2020 14:55:32 GMT

So there are no messages/errors that appear. The columns are spelled correctly and there is no case issue. When I run this, the second part of the script works...it creates a subset with the 3 items listed in "TCAlist". The only thing that doesn't happen is the creation of the group on the main table. Is there something wrong with the first few lines regarding my tables? Does the current data table throw this off?

Re: Filter table columns based on a list

txnelson — Wed, 13 May 2020 15:28:47 GMT

If you can attach the 2 data tables, I will take a look at the issue

Re: Filter table columns based on a list

Shiz — Wed, 13 May 2020 17:19:08 GMT

Re: Filter table columns based on a list

txnelson — Wed, 13 May 2020 17:48:35 GMT

From my testing, I believe that the list is being created, but it is getting lost in the number of columns you have. So I have made a simple change to the code, adding a name for the list, and then telling the list to be moved to the front of the list of columns.

names default to here(1);
dt = Current Data Table();
dtSingle = open ("TCAlist.jmp");

//longList=dt<<get column names(string)
//contains(longList,theList[3])
// Create a group
theList = dtSingle:Cols << get values;
dt << group columns("myList", theList);
dt << move column group( to first, "myList");

// Create a subset
// Select columns in data table dt, that are in dtSingle
try( dt << Select Columns( dtSingle:Cols << get values ));
dtSubset = dt << subset( selected rows(0), selected columns(1) );

Re: Filter table columns based on a list

Shiz — Fri, 15 May 2020 13:36:56 GMT

Great! Thanks for walking me through this. Very helpful!