topic Re: Split table based on Column Names list in Discussions

Split table based on Column Names list

Pim — Sat, 10 Jun 2023 23:41:45 GMT

Hi JMP community, hope you can help me with this problem:

I have made a script that allows users to calculate 1x, 2x and/or 3x SD-values for several sample concentrations with a given (to be entered) factor. The 1x, 2x and/or 3x SD-values can all be chosen via 3 check boxes. This first part works perfectly.

Based on the selected amount of check boxes, the resulting data table gives a variable amount of columns that I would like to split into a new data table.

I tried to build a second script that splits the data table based on all SD-columns present (always starting from column 3) but without success. This is my script so far:

Names Default To Here( 1 );

dt1 = Data Table( "SD Calculation" );

//Get list of Column names
ColList = dt1 << Get Column Names();

//Split dt1 based on ColList
dt2 = Data Table( "SD Calculation" ) << Split(
          Split By( :nomRP ), 
          Split( ColList[3] ), 
          Remaining Columns( Drop All ), 
          Sort by Column Property
);

The resulting splitted table looks like this and is missing most of the data:

This is what it should look like (I can only achieve this by specifying all column names, however, not all columns are going to be present in each table (it’s based on user selection):

When all SD calculations are chosen (±1-3 SD for 5 sample concentrations), 30 columns should be present.

The goal for my script:

nomRP values are: 50, 71, 100, 141 and 200 and 1-3x SD values can be calculated for each of those nomRP values. All the calculated results should be split in a new data table. With these data I want to build a figure in the Graph Builder showing ranges based on the calculated SD values.

Kind regards,

Pim.

-Edit: "SD Calculation" table added.

Re: Split table based on Column Names list

HadleyMyers — Tue, 14 Dec 2021 14:56:28 GMT

Hi,

I think this is one of those times that Eval(Eval Expr(Expr())) would help. Take a look at the code below and see if it does what you're hoping. Also here's a post on this topic that explains these types of methods.

Names Default to Here(1);

dt1=Data Table("SD Calculation");

 

//Get list of Column names

ColList=dt1<<Get Column Names();

ColList=remove(ColList,1,2);
//Split dt1 based on ColList


eval(eval expr(dt2=Data Table( "SD Calculation" ) << Split(

       Split By( :nomRP ),

       Split(expr(ColList)),

       Remaining Columns( Drop All ),

       Sort by Column Property

);));

Re: Split table based on Column Names list

Pim — Wed, 15 Dec 2021 10:44:40 GMT

Hadley, this was exactly what I was looking for.

Thank you very much and also thanks for sharing the post about Eval(Eval Expr(Expr())). This will come in handy in my future scripting adventures!

Re: Split table based on Column Names list

jc510_2 — Tue, 29 Aug 2023 18:01:31 GMT

Many thanks Pim/Hadley for the question/answer on this topic!

I realize this is an old post, but wanted to leave a crumb for anyone who comes after.

I was trying to do something similar to this today, along the lines of Hadley's solution posted above, and at the same time understand the nested Eval(Eval Expr(Expr())) approach (still working on the latter...).

After playing around a little, I found that this:

dt2 = Data Table( "SD Calculation" ) << Split(
    Split By( :nomRP ),
    Split(Eval Expr(ColList)),
	Remaining Columns( Drop All ),
    Sort by Column Property
);

Gives me the same result as this from Hadley's solution:

Eval(
	Eval Expr(
		dt2 = Data Table( "SD Calculation" ) << Split(
			Split By( :nomRP ),
			Split(expr(ColList)),
			Remaining Columns( Drop All ),
			Sort by Column Property
		);
	)
);