topic Summing all columns using loop in Discussions

Summing all columns using loop

OC200m — Thu, 08 Jun 2023 16:42:30 GMT

Hi all, building a fairly lengthy script where I won't know what columns are present in a given table as it's creating multiple tables from different data sets. A summary table is created with upwards of 50 columns, sometimes more than 70. I need to sum all the values in these columns, but can't specify the sum by :Name("Column Name") in case some of the categories are present/missing. This is the loop I am trying to use:

Current Data Table() << New Column( "Total Die",
	formula(
		For( j = 1, j <= N, j++,
			cols = Column( dt, j ) << getname()
		);
		Sum( cols );
	)
);

The sum won't accept the format of the list , so just wondering how else to do this? Thanks!

Re: Summing all columns using loop

OC200m — Wed, 18 Jan 2023 16:33:17 GMT

I should add that the script intitialises dt and N like so:

dt = Current Data Table();
N = N Col( dt );

Re: Summing all columns using loop

ian_jmp — Wed, 18 Jan 2023 17:15:31 GMT

If I understand correctly, then rather than building a loop you could exploit 'Tables > Summary' and let JMP do the work:

NamesDefaultToHere(1);

// Get some data
dt = Open("$SAMPLE_DATA/Semiconductor Capability.jmp");

// Get all the numeric columns
cols = dt << getColumnNames("Numeric");

// Get all the column means
dt2 = dt << Summary(Mean(cols));

Whether or not this is better might depend on what else your JSL is doing, but generally it's good to use JMP platforms, especially if numerics are involved, even simple ones.

Re: Summing all columns using loop

ian_jmp — Thu, 19 Jan 2023 09:50:20 GMT

Ooops! Sorry - It looks like you actually want to sum across columns row-wise. See, for example.

Alternatively, if you do want a formula:

NamesDefaultToHere(1);

// How many columns in the table?
nc = RandomInteger(2, 20);

// Build the table with some random data
dt = NewTable("Sum Columns", << addMultipleColumns("Col ", nc, Numeric));
for(c=1, c<=nc, c++, Column(dt, c) << setFormula(RandomNormal()));
dt << addRows(10);
dt << runFormulas;

// Start here with the current data table
dt2 = CurrentDataTable();
cols2 = dt2 << getColumnNames("Numeric");

// Add the row-wise sum as a new formula column
afc = Expr(dt2 << newColumn("Sum over Columns", Numeric, Continuous, Formula(Sum(colsTBD))));
SubstituteInto(afc, Expr(colsTBD), Eval(cols2));
afc;

(This posting was originally incomplete).

Re: Summing all columns using loop

txnelson — Wed, 18 Jan 2023 17:28:00 GMT

Stretching your knowledge a bit, I think the easiest way to handle the issue is to not create a formula column, but to rather create the column, and then use JSL's functionality using matrices to perform the magic.

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

dt << New Column( "new" );

For Each Row(
	dt:new = Sum( dt[Row(), index(1,ncols(dt)-1)] )
);

Re: Summing all columns using loop

Mark_Bailey — Wed, 18 Jan 2023 18:48:28 GMT

Completely vectorize the computation.

Names Default To Here( 1 );
dt = Current Data Table();
n = N Col( dt );
vals = (dt << Get As Matrix) * J( n, 1, 1);
dt << New Column( "new", "Numeric", "Continuous",
	Values( vals )
);