Creating a Stack script with Varying Column Names

khuynh — Fri, 09 Aug 2019 23:49:35 GMT

Hi JMP Community,

I have a list of column headers (17 capture probes) and depending on the data, a particular data set may have any number of those 17 capture probes.

I need to create a script where the stack function will stack columns that exist in the data set and ignore any other columns that are missing.

I pulled the source script for one particular data set and tried to place an if() statement with a loop in order to try and accomplish this, but it's still not working because certain columns were not found.

I've included the script that I have now.

dt = Current Data Table();
text_col_list = 
	{"SP1_A","SP12_B","SP35_A","SP37_A","SP5_A","SP7_A","SP2_A","SP39_A","SP38_A","SP41_A","SP40_A","SP42_A","SP3_A","SP4_A","SP6_A","SP10_A","SP11_A"};
jmp_col_list = 
	{"cSCR1","CP 34","CP 24 v.6.3","CP 34 v.1","CP 24 v.1","CP 44 v.1","CP 24","CP24 v.14
","CP24 v.13","CP24 v.16","CP24 v.15","CP24 v.17","CP 26","CP 44","CP 24 v.10","CP 32","CP 33"};

col_name_list = dt << get column names(string);

for (i = 1, i <= nitems(text_col_list), i++,

    if (contains(col_name_list, text_col_list[i]),

        column(dt, text_col_list[i]) << set name(jmp_col_list[i]);

    );
);

dt = Current Data Table();
jmp_col_list = 
	{"cSCR1","CP 34","CP 24 v.6.3","CP 34 v.1","CP 24 v.1","CP 44 v.1","CP 24","CP24 v.14
","CP24 v.13","CP24 v.16","CP24 v.15","CP24 v.17","CP 26","CP 44","CP 24 v.10","CP 32","CP 33"};

col_name_list = dt << get column names(string);
 
for (i = 1, i <= nitems(jmp_col_list), i++,

	if (contains(col_name_list, jmp_col_list[i]), 
		<< Stack(columns(jmp_col_list),
		<<ignore errors(true)),
	Source Label Column( "Capture Probes" ),
	Stacked Data Column( "Intensity" ),
	Name( "Non-stacked columns" )(Keep(
		:Filename,
		:Condition,
		:Rep #,
		:Name( "Time (s)" ),
		:Name( "Date/Time" ),
		:Cartridge #
	))
));

Re: Creating a Stack script with Varying Column Names

txnelson — Sat, 10 Aug 2019 01:43:27 GMT

If my interpretation of the various lists in your script is correct, and what I think you are attempting to do, I believe the following script will do what you want

Names Default To Here( 1 );
dt = Current Data Table();

// List of possible columns to be stacked
jmp_col_list = {"cSCR1", "CP 34", "CP 24 v.6.3", "CP 34 v.1", "CP 24 v.1", "CP 44 v.1",
"CP 24", "CP24 v.14", "CP24 v.13", "CP24 v.16", "CP24 v.15", "CP24 v.17", "CP 26",
"CP 44", "CP 24 v.10", "CP 32", "CP 33"};

// List name of the list of columns found in the current data table
found_List = {};

// Columns in the data table
col_name_list = dt << get column names( string );

// Loop through the columns in the data table, and if found in the
// jmp_col_list, then place them into the found_List

For( i = 1, i <= N Items( col_name_list ), i++,
	If( Contains( jmp_col_list, col_name_list[i] ),
		Insert Into( found_List, col_name_list[i] )
	)
);

// Stack the columns found
dtStacked = dt << Stack(
	columns( Eval( found_list ) ),
	Source Label Column( "Capture Probes" ),
	Stacked Data Column( "Intensity" ),
	Name( "Non-stacked columns" )(Keep(
		:Filename,
		:Condition,
		:Rep #,
		:Name( "Time (s)" ),
		:Name( "Date/Time" ),
		:Cartridge #
	))
);

topic Creating a Stack script with Varying Column Names in Discussions

Creating a Stack script with Varying Column Names

Re: Creating a Stack script with Varying Column Names