cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
nhun
Level II

Script for column stack by prefix for variable number of columns

I am trying to produce a general script that will import my data from excel as a data table, and then stack my columns based on the prefix of the column name. I wish for it to be applied to different datasets (not at the same time - just a general script that could apply for all) each with a different sample numbers (columns), where each data column is listed as: S01 Vol Density (%), S02  Vol Density (%), S03 Vol Density (%) ... Sn Vol Density (where n = any integer).

 

I am thinking the way to do this is by grouping the columns by the prefix and then stacking the group however I am unsure how to proceed with this.

 

 

Hope this made sense - still new to scripting so sorry if my question is not the clearest

 

N.B. Importing data is not an issue, just the stacking

1 ACCEPTED SOLUTION

Accepted Solutions
cwillden
Super User (Alumni)

Re: Script for column stack by prefix for variable number of columns

Hi @nhun,

Welcome to the community!

This is pretty easy to do in just a short amount of code.  Here's one way that loops through the column names and inserts the ones with the matching substrings into a list we can use with Stack.  I use the Thickness data set from the Sample Data Library.

dt = Open("$SAMPLE_DATA/Quality Control/Thickness.jmp");

stack_cols = {}; //list to hold the names of columns I want to stack
str_match = "Thickness"; //Substring in column name I want to find

names = dt << Get Column Names;
for(i=1,i<=N Items(names),i++,
	If(Contains(char(names[i]),str_match) > 0,
		Insert Into(stack_cols, names[i])
	)
);

dt << Stack(
	columns(stack_cols),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

You would just need to replace the dt with your own table and str_match = "Vol Density"

-- Cameron Willden

View solution in original post

3 REPLIES 3
cwillden
Super User (Alumni)

Re: Script for column stack by prefix for variable number of columns

Hi @nhun,

Welcome to the community!

This is pretty easy to do in just a short amount of code.  Here's one way that loops through the column names and inserts the ones with the matching substrings into a list we can use with Stack.  I use the Thickness data set from the Sample Data Library.

dt = Open("$SAMPLE_DATA/Quality Control/Thickness.jmp");

stack_cols = {}; //list to hold the names of columns I want to stack
str_match = "Thickness"; //Substring in column name I want to find

names = dt << Get Column Names;
for(i=1,i<=N Items(names),i++,
	If(Contains(char(names[i]),str_match) > 0,
		Insert Into(stack_cols, names[i])
	)
);

dt << Stack(
	columns(stack_cols),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

You would just need to replace the dt with your own table and str_match = "Vol Density"

-- Cameron Willden
nhun
Level II

Re: Script for column stack by prefix for variable number of columns

Thanks! If I wished to then recode the column (with a script) so that only the prefix remained i.e. so it read only S01, S02 etc., can this be done in a similar fashion to the stack?
cwillden
Super User (Alumni)

Re: Script for column stack by prefix for variable number of columns

Yes, here's how you might do that:

For( i = 1, i <= N Col( dt ), i++,
	temp_name = Column( dt, i ) << Get Name;
	If( Contains( temp_name, "Vol Density" ) > 0,
		Column( dt, i ) << Set Name( Word( 1, temp_name ) )
	);
);
-- Cameron Willden