topic Re: how do I multiply each row values backward in one column until a criteria is met in Discussions

how do I multiply each row values backward in one column until a criteria is met

Tinghui — Fri, 09 Jun 2023 22:22:25 GMT

Hello, I have the following challenge that I have trouble scripting with.

My starting data table looks like below. I would like to multiply Xi backwards starting from col2 =0 as the criteria and put the new values in a new column.

col1 col2

X1 0

X2 y2

X3 y3

…

Xn yn

XX1 0

XX2 yy2

….

XXn yyn

….

My end data table would look like this:

col1 col2 col3

X1 0 Xn*Xn-1*……X3*X2*X1

X2 y2 Xn*Xn-1*…..X3*X2

X3 y3 Xn*Xn-1*….*X3

…

Xn yn Xn

XX1 0 XXn*XXn-1*…..XX2*XX1

XX2 yy2 XXn*XXn-1*…..XX2

….

XXn yyn XXn

….

Can someone please help out? Very much appreciated!

Re: how do I multiply each row values backward in one column until a criteria is met

txnelson — Mon, 16 Nov 2020 19:31:10 GMT

I don't understand your 3 sets of columns. It appears as if you are changing column names in the middle of a data table, which will not work in JMP. Is this table format coming from an Excel spreadsheet?

Here is a simple formula that will do what you want for a data table that looks like

I have attached the data table.

The formula used is:

x = :col2[Row()];
If( Row() < N Rows( Current Data Table() ),
	For( i = Row() + 1, i <= N Rows( Current Data Table() ), i++,
		x = x * :col2[i]
	)
);
x;

This same idea can be used if there are multiple places in a data table that need to restart the multiplying. The identification of when to restart and when to end would just have to be worked out

ARe: how do I multiply each row values backward in one column until a criteria is met

Dan_Obermiller — Mon, 16 Nov 2020 21:57:51 GMT

I removed my post since I missed the grouping variable. My approach would not work in this scenario.

Re: how do I multiply each row values backward in one column until a criteria is met

Tinghui — Mon, 16 Nov 2020 20:18:10 GMT

Hi Jim @txnelson

Thanks for your reply.

No, my column names do not change. They stay as col1, col2 as given. I would like to add a new column (col3) that does the backward multiplication for a same value given in col2. I'm demonstrating my need in the following table. Could you please help?

col1 col2 col3

1 x 6 (i.e. 1 x 2 x 3)

2 x 6 (i.e. 2 x 3)

3 x 3 (i.e. 3)

2 y 240 (i.e. 2x4x5x6)

4 y 120 (i.e. 4 x 5 x6)

5 y 30 (i.e. 5 x6)

6 y 6 (i.e. 6)

7 z 504 (i.e. 7x8x9)

8 z 72 (i.e. 8x9)

9 z 9

Thanks!

Re: how do I multiply each row values backward in one column until a criteria is met

txnelson — Mon, 16 Nov 2020 21:13:44 GMT

Here is the new formula......it is the same method as the last formula, but just having to find the start and end point for each of the groups.

If( Row() == 1,
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
,
	:col2 != curCol;
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
);
For( i = Row() + 1, i <= end, i++,
	x = x * :col1[i]
);
x;

Re: how do I multiply each row values backward in one column until a criteria is met

Tinghui — Tue, 17 Nov 2020 14:42:30 GMT

Hi Jim @txnelson

That actually worked on a smaller table (i.e. several thousands of rows). Thank you.

The 'new' issue that I'm having is that my data table is very large. As I embedded the formula into the new column and click on 'apply', the program got hung and I had to kill jmp to get out.

As I hover over the formula, here is the warning message regarding 'current data table'.

"Current data table() has side effects and is usually inappropriate to be used inside a formula. If this is necessary, you might use expr or eval to prevent execution in formulas....."

My scripting skills are very limited. Thanks for your further help on this subject!

Regards,

Tinghui

@txnelson wrote:
Here is the new formula......it is the same method as the last formula, but just having to find the start and end point for each of the groups.
If( Row() == 1,
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
,
	:col2 != curCol;
	curCol = :col2;
	end = Max( Current Data Table() << get rows where( :col2 == curCol ) );
	x = :col1;
);
For( i = Row() + 1, i <= end, i++,
	x = x * :col1[i]
);
x;

Re: how do I multiply each row values backward in one column until a criteria is met

txnelson — Tue, 17 Nov 2020 16:29:56 GMT

The calculations you want are very easy to do if the data are reversed, so here is a little script that will calculate the values using that idea

names default to here(1);

// create the sample table
dt = New Table( "example",
	Add Rows( 10 ),
	New Column( "col1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 2, 4, 5, 6, 7, 8, 9] ),
		Set Display Width( 48 )
	),
	New Column( "col2",
		Character( 1 ),
		"Nominal",
		Set Values( {"x", "x", "x", "y", "y", "y", "y", "z", "z", "z"} ),
		Set Display Width( 45 )
	)
);

// JSL to create the column you want
// create a new column to preserve the order of the data
dt << new column("origRow", formula(row()));
dt:origRow << delete formula;

// Reverse the order of the data
dt << sort(by(:origRow), order(descending), replace table(1));

// create the new column
dt << new column( "col3", formula(
	If( Row() == 1, x = :col1 );
If( Lag( :col2 ) != :col2,
	x = :col1,
	x = x * :col1
);
x;
));

// Since the formula is dependent on the order of the data, the formula needs
// to be removed and the values set to static values
dt:col3 << delete formula;

// reorder the data table back to the original order and delete the origRow column
dt << sort(by(:origRow), order(ascending), replace table(1));
dt << delete columns(:origRow);

And here is a piece of JSL that will create the new column without sorting

Names Default To Here( 1 );

// create the sample table
dt = New Table( "example",
	Add Rows( 10 ),
	New Column( "col1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 2, 4, 5, 6, 7, 8, 9] ),
		Set Display Width( 48 )
	),
	New Column( "col2",
		Character( 1 ),
		"Nominal",
		Set Values( {"x", "x", "x", "y", "y", "y", "y", "z", "z", "z"} ),
		Set Display Width( 45 )
	)
);


// create the new column
dt << New Column( "col3b" );

:col3b[N Rows( dt )] = :col1[N Rows( dt )];
For( i = N Rows( dt ) - 1, i >= 1, i--,
	If( :col2[i + 1] != :col2[i],
		:col3b[i] = :col1[i],
		:col3b[i] = :col3b[i + 1] * :col1[i]
	)
);

Re: how do I multiply each row values backward in one column until a criteria is met

Tinghui — Tue, 17 Nov 2020 16:43:52 GMT

@txnelson

Thanks Jim! I really wish I were a scripting genius like yourself. It works great!