Choose Language Hide Translation Bar
Highlighted
Shiz
Level II

Filter table columns based on a list

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:

  1. Make column groups in Table 1 based on the column IDs in Table 2?
  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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Filter table columns based on a list

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

View solution in original post

8 REPLIES 8
Highlighted
txnelson
Super User

Re: Filter table columns based on a list

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) );
Jim
Highlighted
Shiz
Level II

Re: Filter table columns based on a list

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) );
Highlighted
txnelson
Super User

Re: Filter table columns based on a list

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

Re: Filter table columns based on a list

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?
Highlighted
txnelson
Super User

Re: Filter table columns based on a list

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

Jim
Highlighted
Shiz
Level II

Re: Filter table columns based on a list

 
Highlighted
txnelson
Super User

Re: Filter table columns based on a list

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

View solution in original post

Highlighted
Shiz
Level II

Re: Filter table columns based on a list

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

    There are no labels assigned to this post.