Script for column stack by prefix for variable number of columns

[#1]

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

[#2 · Accepted solution]

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"

[#3]

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?

[#4]

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