Script for column stack by prefix for variable number of columns

nhun

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

cwillden

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"