topic Cumulated sum of rows in new column by different columns in Discussions

Cumulated sum of rows in new column by different columns

Martin91 — Thu, 07 Nov 2019 08:49:19 GMT

dt = Current Data Table();


// List of column names for the table

col_names = dt << get column names( string );


// Create cumulative sum of each D

looking_for_channel_1 = "D";

For( i = 1, i <= N Items( col_names ), i++,

// search column names for "D"

If( Starts With( col_names[i], looking_for_channel_1 ),

new_col = dt << New Column( Char( col_names[i] ) || ">=",
Numeric,
.................................
);

);

);

Dear all,

I am a new scripter trying to solve a current issue. I am not very familiar with the syntax but I want to loop trough columns and rows to calculate the cumulated sum for each tool and row and save the result in a new column. I have attached a data table which shows the solution.

Many thanks for any provided feedback.

Martin

Re: Cumulated sum of rows in new column by different columns

txnelson — Thu, 07 Nov 2019 11:21:40 GMT

I have taken a different approach to solving your problem. Looping through the data, accumulating the data as you propose will become quite slow with a lot of columna and rows. Using builtin JMP functions will be much faster. Below is a script that transposes the data into a tall data table to do the processing, using the builtin Col Cumulative Sum() function, and then transposes the data back to the wide format after processing is done.

names default to here(1);
dt=current data table();

// Transform the data into a long table from the current wide table
// to allow for faster processing using the built in column function
//      col cumulative sum()
dtStack = dt << Stack(
	columns( :D300, :D200, :D100, :D50, :D10 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Name( "Non-stacked columns" )(Keep( :Tool, :Column 13 ))
);

// Add a new column to insure an alphbetical ordering of the original
// data table
dtStack << New Column( "Row Number",
	Character,
	"Nominal",
	Formula(
		If( Lag( :Tool ) != :Tool,
			counter = 0
		);
		counter = counter + 1;
		result = Substr( "00", Length( Char( counter ) ) - 3 ) || Char( counter )
		 || " " || :Label;
	)
);

// Add the Cumulative Sum column
dtStack << New Column( "xx",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Col Cumulative Sum( :Data, :Tool ) ),
	Set Selected
);

// Transpose the data back into a wide format 
dtFinal = dtStack << Split(
	Split By( :Row Number ),
	Split( :Data, :Name( "xx" ) ),
	Group( :Tool ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

// Close the nolonger needed stack data table
close(dtStack,nosave);

// Pass across the column names adjusting them back to the desired form
For( i = 2, i <= N Cols( dtFinal ), i++,
	If(
		Word( 1, Column( dtFinal, i ) << get name, " " ) == "Data",
			Column( dtFinal, i ) << set name( Substr( Column( dtFinal, i ) << get name, 10 ) )
	,
		Word( 1, Column( dtFinal, i ) << get name, " " ) == "xx",
			Column( dtFinal, i ) << set name( Substr( Column( dtFinal, i ) << get name, 8 ) || ">=" )
	)
);

Re: Cumulated sum of rows in new column by different columns

txnelson — Thu, 07 Nov 2019 11:48:19 GMT

Here is another approach, just creating the new columns using formula columns

Names Default To Here( 1 );

// This JSL assume the table contains only the Tool column,
// followed by the Dnnn columns.  No cummulative columns have been
// created yet
dt = Current Data Table();

totCol = N Cols( dt );

// Loop across the columns creating the new cumulative sums
For( i = 2, i <= totCol, i++,
	Eval(
		Substitute(
				Expr(

					dt << New Column( __newname__, formula( __formula__ ) )
				),
			Expr( __newname__ ), Column( dt, i ) << get name || ">=",
			Expr( __formula__ ),
				If( i == 2,
					Parse( Column( dt, i ) << get name ),
					Parse(
						"sum( :Name(\!"" || char( Column( dt, i - 1 ) << get name ) || ">=\!"), " || char( Column( dt, i ) << get name )
						 || ")"
					)
				)
		)
	)
);