Summary Statistics of all numeric columns by grouping variable

Question

I borrowed a section of another JSL that someone wrote a while back, but unfortunately has left the company now.

Original JSL creates box plots along with a table with summary statistics of all numeric columns by grouping variable ("split").

My goal is to get only the summary table with Summary Statistics (Mean, Std-dev) of all numeric columns by grouping variable.

I am using JMP16.

Sample input data table has 200 numeric columns, thousands of rows with 5 unique value of grouping character variable ("split").

output summary table should have 200 rows with 5 columns for Mean & 5 columns for std-dev, where each of the new columns (10) is Mean/Std-dev of each numeric column grouped by each unique value of grouping character variable ("split").

Thank you

10222024025450.jsl

hogi · Answer

As an alternative suggestion, you can generate a temporary stacked table  - and use Tables/Summary in combination with the options Group (original column) and Subgroup (split):

dt = Open( "$SAMPLE_DATA/Semiconductor Capability.jmp" );

tmp = dt <<
Stack(
	columns( Column Group( "Processes" ) )
);

tmp << Summary(
	Group( :Label ),
	Mean( :Processes ),
	Std Dev( :Processes ),
	Subgroup( :SITE ),
	statistics column name format( "stat" )
);

txnelson · Answer

The script would run much faster by using the Response Screening platform to get the p-Values.  It would also be more efficient to use the Tabulate platform to generate the means and standard deviations

Here is an example of how to create the Summary table using the above suggestions.  I have excluded the PValue limiting, since it did nothing in the original script.

Names Default To Here( 1 );
WorkTable = Current Data Table();
GetColName = WorkTable << get column names( Numeric );

// Generate the mean/std table
tab = WorkTable << (Tabulate(
	Add Table(
		Column Table( Grouping Columns( :split ), Statistics( Mean ) ),
		Column Table( Grouping Columns( :split ), Statistics( Std Dev ) ),
		Row Table( Analysis Columns( Eval( GetColName ) ) )
	)
));
tab << Make Into Data Table;
tab << close window;
SumDT = Current Data Table();
SumDT << set name( "SummaryData" );

// Generate the p-values
rs = WorkTable << Response Screening(
	Y( Eval( GetColName ) ),
	X( :split ),
	PValues Table on Launch( 1 )
);

ResponseDT = rs << get PValues;
Report( rs ) << close window;

ResponseDT << select columns( {"Y", "PValue"} );
ResponseDT << Invert Column Selection;
ResponseDT << delete columns;
ResponseDT:Y << set name( "Analysis Columns" );

SumDT << Update( With( ResponseDT ), Match Columns( :Analysis Columns = :Analysis Columns ) );

SumDT << Select columns( :PValue );
SumDT << Move Selected Columns( After( :Analysis Columns ) );
Close( ResponseDT, nosave );

It should give you a leg up on completing the script to meet your exact specifications.

I suggest you take the time to read through the scripting guide, found in the JMP Documentation Library under the Help Pull Down Menu.

vikramavtar · Answer

Thank you for quick response.

New JSL works and provides the desired results, but I think it is slow.
I am looking to use this script for a very large dataset.

Are there any options to improve performance ? Thank you.