topic perform calculations using selected columns in Discussions

perform calculations using selected columns

KellyT — Sun, 31 May 2020 20:09:24 GMT

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

A	B	C	new column with formula sqrt(sum(A^4, B^4, C^4)
1	2	3	...
4	5	6	...
7	8	9	...

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!

Re: perform calculations using selected columns

txnelson — Sun, 31 May 2020 23:10:51 GMT

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

Re: perform calculations using selected columns

KellyT — Mon, 01 Jun 2020 15:44:31 GMT

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()]

Re: perform calculations using selected columns

txnelson — Mon, 01 Jun 2020 16:06:55 GMT

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()]