topic Re: Subtraction of multiple column in Discussions

Script to do : Subtraction of multiple column

jinsejoseph — Sat, 10 Jun 2023 23:38:24 GMT

Hi Team,

Need help to develop one script to do subtraction of multiple column , column name and count is variable,

can change based on analysis,

Need to populate column delta with differences all the value from same row,

sample data as below

expected output:

Re: Subtraction of multiple column

ih — Wed, 13 Oct 2021 13:59:06 GMT

Hi @jinsejoseph

Can you give an example of the formula you want to use? This sounds like something I would use the custom transformation in the add-in for.

Re: Subtraction of multiple column

jinsejoseph — Wed, 13 Oct 2021 14:02:21 GMT

Hi , Thanks for the reply,
I am using JMP15 can you help to point to the code that i can use,
I am a beginner in JMP

Thanks in advance

Re: Subtraction of multiple column

jinsejoseph — Wed, 13 Oct 2021 15:05:23 GMT

Expected output in delta as below,

Re: Subtraction of multiple column

ih — Wed, 13 Oct 2021 15:38:28 GMT

Now I see, in that case I would personally use the custom transformation function in the add-in. I would select all of the columns you want to transform (I think data1-data10) and then select Custom Transformation from the Cols menu (available after installing the add-in) and enter these settings:

Re: Subtraction of multiple column

jinsejoseph — Wed, 13 Oct 2021 15:55:14 GMT

Hi ,
I was writing a scrip for data analysis, and need this to be implement this logic in between the code only for one data table,
can you help to point to the code that i can use

Thanks in advance

Re: Subtraction of multiple column

txnelson — Wed, 13 Oct 2021 16:05:45 GMT

Here is the formula that I would use. It relies on the matrix function Loc() which return all non zero entries in a matrix.

x = :data[Row()] || :data1[Row()] || 
:data2[Row()] || :data3[Row()] || 
:data4[Row()] || :data5[Row()] ||
:data6[Row()] || :data7[Row()] || 
:data8[Row()] || :data9[Row()];
y = Loc( x );
Try( x[y[2]] - x[y[1]], . );

Re: Subtraction of multiple column

jinsejoseph — Thu, 14 Oct 2021 02:37:15 GMT

Hi Jim,

Thanks for the input,

But column name is variable control and name can change based on run to run,

how to code in that situation ?

Re: Subtraction of multiple column

txnelson — Thu, 14 Oct 2021 03:21:35 GMT

Assuming you don't know the names, and you don't know the number of columns, the below code will create the required formula, and then create the new column with the generated formula

Names Default To Here( 1 );
dt = Current Data Table();

// Get the columns
colNames = dt << get column names( string, continuous );

// Build the formula
theExpr = "dt<<new column(\!"Delta\!",formula(x = " || colNames[1] || "[Row()]";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || " || " || colNames[i] || "[Row()]"
);
theExpr = theExpr || ";
y = Loc( x );
Try( x[y[2]] - x[y[1]], . )));";

// Execute the Created JSL
Eval( Parse( theExpr ) );

The sample data table I used is attached

Re: Subtraction of multiple column

jthi — Thu, 14 Oct 2021 05:45:44 GMT

You could also turn it into a function if you need to use it multiple times.

Here is one example:

Function
Uses regex to filter down to the columns of interest
Requires JMP16 due to Filter Each
Uses Loc() and matrix calculations
Creates new column delta with the values

Names Default To Here(1);

//example datatable
dt = New Table("Untitled",
	Add Rows(5),
	Compress File When Saved(1),
	New Column("Column 1",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 0, 0, 1, 0]),
		Set Display Width(55)
	),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([0, 2, 3, 0, 0])),
	New Column("Column 3",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([0, 3, 0, 0, 0]),
		Set Display Width(87)
	),
	New Column("Column 4", Numeric, "Continuous", Format("Best", 12), Set Values([3, 0, 4, 0, 0])),
	New Column("col", Character, "Nominal", Set Values({"a", "a", "a", "a", "a"}))
);


difference_between_min_max = Function({dt_ref, col_prefix_regex},
	{Default Local},
	//get columnn names of interest based on col_prefix
	col_names_all = dt_ref << Get Column Names("Numeric", "String");
	col_names_of_interest = Filter Each({val}, col_names_all, 
		!IsMissing(Regex(val, col_prefix_regex));
	);
	show(col_names_of_interest);
	//all values for columns of interest, transpose for calculations
	m = Transpose(dt_ref[0, col_names_of_interest]);
	//replace 0 with missing to avoid calculation with those
	m[Loc(m, 0)] = .;
	//Get max and min values and deduct them
	result = V Max(m) - V Min(m);
	//Replace missing values with 0 for final results
	result[Loc(result,.)] = 0;
	//we could return result matrix here OR we can create new column with the function
	dt_ref << New Column("delta", Numeric, Continuos, Set Values(result));
);

//example table in this example
difference_between_min_max(dt, "(Column )");

//example table by txnelson
//difference_between_min_max(Current Data Table(), "^(\D{1})$");

Could also turn it into Custom Function

Re: Subtraction of multiple column

jinsejoseph — Thu, 14 Oct 2021 08:02:31 GMT

Hi Jim,

Thank you for the help,

solution working fine :)