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
khuynh
Level I

Creating a Stack script with Varying Column Names

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Creating a Stack script with Varying Column Names

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

View solution in original post

1 REPLY 1
txnelson
Super User

Re: Creating a Stack script with Varying Column Names

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