895150_en-US

liliconcom

Dynamically passing a set of columns to a table summary function

I have a workflow that generates a table (sample attached), but the number of columns and name of those columns from 14 on will vary depending on the dataset the end user is looking at. 

 

The first part of the script works no problem - generating a list of the column names. What I'm struggling with and is beyond anything I've done in JSL so far is passing that list back into a table summary function. Full transparency, I did try to troubleshoot this with MS Copilot (AI Agent pointed to JMP documentation) with no success. 

//num cols
dt = Data Table( "plate_data_wide_for_dynamic_summary Anonymized" );
nCols = N Col( dt );

//empty list of colnames, populate with col names from 14 to end of table
colnames = {};
For( i = 14, i <= nCols, i++,
	Insert Into( colnames, Column( dt, i ) << Get Name )
);

//create summary expression with dynamic column placeholder
summaryExpr = Expr(
	Data Table( "plate_data_wide_for_dynamic_summary Anonymized" ) << Summary(
		Group(
			:X__1, :X__3, :X__10
		),
		__DYNAMIC_COLUMNS__,
		Freq( "None" ),
		Weight( "None" ),
		output table name( "Summarized by PC plate_well wide" )
	)
);

//create list of summary statistics and columns to pass to summary function
dynamicCols = {};
For( i = 1, i <= N Items( colnames ), i++,
	Insert Into( dynamicCols, Eval Expr( Median( As Column( dt, colnames[i] ) ) ) )
);

//pass dynamicCols list to summary expression and evaluate
Insert Into( summaryExpr[1][2], dynamicCols );
Eval( summaryExpr );

Automation and ScriptingRe: Dynamically passing a set of columns to a table summary function

I believe this gives you what you want

Names Default to Here( 1 );
dt = Data Table( "plate_data_wide_for_dynamic_summary Anonymized" );
nCols = N Col( dt );

//empty list of colnames, populate with col names from 14 to end of table
colnames = dt << get column names( string ); //num cols

staticNames = {"X__1", "X__3", "X__10"};

For Each( {sName}, staticNames, Try( Remove From( colnames, Contains( colnames, sName ), 1 ) ) );

//create summary expression with dynamic column placeholder
Eval(
	Eval Expr(
		Data Table( "plate_data_wide_for_dynamic_summary Anonymized" ) <<
		Summary(
			Group( Expr( staticNames ) ),
			Median( Expr( colnames ) ),
			Freq( "None" ),
			Weight( "None" ),
			output table name( "Summarized by PC plate_well wide" )
		)
	)
);

 

Re: Dynamically passing a set of columns to a table summary function

Awesome Jim thank you, I made a slight modification to pass column names 14 to end vs. any columns that weren't used for grouping. Here's the final version:

Names Default to Here( 1 );
dt = Data Table( "plate_data_wide_for_dynamic_summary Anonymized" );
nCols = N Col( dt );

//empty list of colnames, populate with col names from 14 to end of table
colnames = {};
For( i = 14, i <= nCols, i++,
	Insert Into( colnames, Column( dt, i ) << Get Name )
);

GroupColNames = {"X__1", "X__3", "X__10"};



//create summary expression with dynamic column insertion
Eval(
	Eval Expr(
		Data Table( "plate_data_wide_for_dynamic_summary Anonymized" ) <<
		Summary(
			Group( Expr( GroupColNames ) ),
			Median( Expr( colnames ) ),
			Freq( "None" ),
			Weight( "None" ),
			output table name( "Summarized by PC plate_well wide" )
		)
	)
);