topic Re: JSL Create new summary table with Max values of variable columns in Discussions

JSL Create new summary table with Max values of variable columns

Glo_Roh — Fri, 09 Jun 2023 19:52:59 GMT

Hello,

I've enjoyed reading and finding solutions on these discussion pages for a while now, but now have been fumbling to figure this out.

I have a data table that gives variable number of columns, auto generated by machine, where the first 14 columns are always the same identifying information. The other columns, 15 onwards, are Numeric Continuous data where the column names will always vary.

Issue is I am trying to automate the creation of a summary table that will group the data by it's identifying information and also have a column of the Max data for each data column.

With a varying number of data columns and varying column names, Is it possible to iterate through each column? Such as a for-loop to get all the Max values into the summary table? I am limited by being unable to name all the columns b/c of the varying column names each pull, but it is safe to say every column after the 14th column will need the Max values found.

This is the JSL script I've been trying to edit, but hit a dead end on generating the Max columns.

sumdt = table << Summary(
	Group(:NAME,:ID),
	(Max(Column(table, 15:154)), // having issue on this line
	Output Table Name("Summary Max Values")
);

Thank you for any inputs to my issue!

Re: JSL Create new summary table with Max values of variable columns

txnelson — Wed, 21 Jul 2021 00:38:15 GMT

Here is an example of doing what you want with the Big Class sample data table.

Names default to here( 1 );

// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colNamesList = dt << get column names( continuous, string );
	

sumdt = dt << summary(
	Group( :Sex, :Age ),
	max( colNamesList ),
	Output Table( "Summary Max Values"),
	Link to Original Data Table( 0 )
);

Re: JSL Create new summary table with Max values of variable columns

Glo_Roh — Wed, 21 Jul 2021 01:52:14 GMT

Thank you @txnelson ,

that partially solved my issue.

My original data table has date columns that I do not want Max values analyzed, basically running into issue that I don't want to be calculating the Max values of the first 14 columns regardless of their data type.
Is there a way to ignore the first 14 columns or would it be easier create a new table that deletes the unwanted columns first before passing it through for a summary table?

Thanks,

Gloria

Re: JSL Create new summary table with Max values of variable columns

txnelson — Wed, 21 Jul 2021 01:57:09 GMT

After the colNamesList is created, you can use the Remove From() function to delete specific rows in the list, and then once the list is whittled down to just the columns you want, you can use it for the generation of the Summary Table.

See the Scripting Index entry for the definition and example of the Remove From() function

Re: JSL Create new summary table with Max values of variable columns

Glo_Roh — Wed, 21 Jul 2021 02:23:41 GMT

Thanks so much @txnelson
That is perfect. Since I know which columns I want removed, the Remove from() function is just what I needed.

Here's basically the code that does exactly what I need:

Names default to here( 1 );

// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

colNamesList = dt << get column names( continuous, string );
Remove From(colNamesList, 1,2 ); //removes first two columns that I don't want evaluated	

sumdt = dt << summary(
	Group( :Sex, :Age ),
	max( colNamesList ),
	Output Table( "Summary Max Values"),
	Link to Original Data Table( 0 )
);

Re: JSL Create new summary table with Max values of variable columns

GoodMan — Wed, 21 Jul 2021 04:24:49 GMT

Hi, Thanks for your code. Here i found i just can get Max of first numeric column, height. Please see attachment. I guess the Max just function to the first value of list. Can you help me know why?

Re: JSL Create new summary table with Max values of variable columns

GoodMan — Thu, 22 Jul 2021 03:17:20 GMT

Under thw latest version JMP, the code works fine. Thanks!