Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
KellyT
Level II

perform calculations using selected columns

Hi,

I am struggling to find a good scripting solution for this.  I would like to select a few columns and then perform calculations across them.  The selected columns are flexible, sometimes it could be 2 but sometimes it could be 10+ columns to perform this calculation.

 

For example 

ABCnew column with formula sqrt(sum(A^4, B^4, C^4)
123...
456...
789...

 

At this moment, I have the script to extract selected column. But once they are in a list with column name, I don't know how to sum up the column using the formula I have above...

dt = Current Data Table();
cols = dt << get selected columns;

Thank you so much for your help!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

Re: perform calculations using selected columns

Here is, using JSL,

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

dt << New Column( "Calc",
	formula(
		cols = dt << get selected columns;
		mat = [];

		For( i = 1, i <= N Items( cols ), i++,
			mat = mat || Column( cols[i] )[Row()]
		);
		mat = Sqrt( Sum( mat ^ 4 ) );
	)
);

dt:calc << delete formula;

how I would create a formula column to handle your issue

 

Jim

View solution in original post

Highlighted
txnelson
Super User

Re: perform calculations using selected columns

I chose the method to use, based upon the very nice capabilities the Matrix operators have in JSL.  The statement

mat = Sqrt( Sum( mat ^ 4 ) );

is an illustration of how nice and concise the calculations can be made using matrices. 

But to use matrix operators, the data must be in a matrix. So for each row, the matrix Mat is first set to being an empty matrix

mat = [];

and then the For() loop takes each of the columns specified in the << Get Selected Columns and adds the value for that column for the given row, to the matrix "Mat" as a new element in the matrix.  It is "Concatenating" the value of the column for the current row to the matrix.

mat = mat || Column( cols[i] )[Row()]
Jim

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

Re: perform calculations using selected columns

Here is, using JSL,

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

dt << New Column( "Calc",
	formula(
		cols = dt << get selected columns;
		mat = [];

		For( i = 1, i <= N Items( cols ), i++,
			mat = mat || Column( cols[i] )[Row()]
		);
		mat = Sqrt( Sum( mat ^ 4 ) );
	)
);

dt:calc << delete formula;

how I would create a formula column to handle your issue

 

Jim

View solution in original post

Highlighted
KellyT
Level II

Re: perform calculations using selected columns

Thank you Jim! Your script is very helpful as always.  To help me understand JSL better, can you please elaborate what this line of code does?

 

mat = mat || Column( cols[i] )[Row()]
Highlighted
txnelson
Super User

Re: perform calculations using selected columns

I chose the method to use, based upon the very nice capabilities the Matrix operators have in JSL.  The statement

mat = Sqrt( Sum( mat ^ 4 ) );

is an illustration of how nice and concise the calculations can be made using matrices. 

But to use matrix operators, the data must be in a matrix. So for each row, the matrix Mat is first set to being an empty matrix

mat = [];

and then the For() loop takes each of the columns specified in the << Get Selected Columns and adds the value for that column for the given row, to the matrix "Mat" as a new element in the matrix.  It is "Concatenating" the value of the column for the current row to the matrix.

mat = mat || Column( cols[i] )[Row()]
Jim

View solution in original post

Article Labels

    There are no labels assigned to this post.