cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
jinsejoseph
Level III

Script to do : Subtraction of multiple column

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

 

jinsejoseph_0-1634133124668.png

 

 

expected output:

jinsejoseph_0-1634137668769.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Subtraction of multiple column

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]], . );
Jim

View solution in original post

jinsejoseph
Level III

Re: Subtraction of multiple column

Hi Jim,

 

Thank you for the help,

solution working fine

View solution in original post

10 REPLIES 10
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Subtraction of multiple column

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 theScripting Tools add-in for.

 

ih_0-1634133519854.png

 

jinsejoseph
Level III

Re: Subtraction of multiple column

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 

jinsejoseph
Level III

Re: Subtraction of multiple column

Expected output in delta as below,

 

jinsejoseph_0-1634137515021.png

 

ih
Super User (Alumni) ih
Super User (Alumni)

Re: Subtraction of multiple column

Now I see, in that case I would personally use the custom transformation function in theScripting Tools 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:

 

ih_0-1634139466162.png

 

jinsejoseph
Level III

Re: Subtraction of multiple column

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 

txnelson
Super User

Re: Subtraction of multiple column

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]], . );
Jim
jinsejoseph
Level III

Re: Subtraction of multiple column

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 ?

txnelson
Super User

Re: Subtraction of multiple column

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 ) );

txnelson_0-1634181607565.png

The sample data table I used is attached

 

Jim
jthi
Super User

Re: Subtraction of multiple column

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

 

-Jarmo