topic Re: How can I get the sum of all different combinations of a group of columns? in Discussions

How can I get the sum of all different combinations of a group of columns?

DivisiveFerret8 — Thu, 18 May 2023 18:49:16 GMT

Hi,

I am using JMP PRO v16

I have a group of 10 columns with numerical values, and I want to try to get a sum of the scores in all possible combinations between these 10 columns (e.g., column A+ column B, column A+ column B + Column C, Column B + Column C and so on).

Thank you so much for your help

Re: How can I get the sum of all different combinations of a group of columns?

jules — Thu, 18 May 2023 20:39:08 GMT

Hi @DivisiveFerret8.,

I don't know of a fast way to do this interactively in JMP, but with some jsl we can make those columns quickly:

Names Default to Here (1);

dt = Current Data Table();

// Get the number of columns
ncols = N Col( dt );

// Nested loop to go through all pairwise combinations of columns
For( i = 1, i < ncols, i++,
	For( j = i + 1, j <= ncols, j++,
		colName1 = Column( dt, i ) << get name;
		colName2 = Column( dt, j ) << get name;
		
		// create new column
		newColName = colName1 || " + " || colName2;
		dt << New Column( newColName, Numeric, "Continuous", Format( "Best", 12 ) );

		// compute the sum
		Column( dt, newColName ) << set each value(
			Column( dt, colName1 )[Row()] + Column( dt, colName2 )[Row()]
		);
	)
);

//EDIT//

The above code is for all combinations of columns in the table, but I see now you were asking about a group of columns:

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

// List of specific columns
// columns = {"Column A", "Column B", "Column C", "Column D", "Column E", "Column F", "Column G", "Column H", "Column I", "Column J"};

// Using selected columns
// columns = dt << Get Selected Columns;

// Referencing by Group Name
columns = dt << get column group( "XY" );

// Number of specific columns
ncols = N Items( columns );

// Nested loop to go through all pairwise combinations of specified columns
For( i = 1, i < ncols, i++,
	For( j = i + 1, j <= ncols, j++,
		colName1 = columns[i]<<Get Name;
		colName2 = columns[j]<<Get Name;
		
		// create new column
		newColName = colName1 || " + " || colName2;
		dt << New Column( newColName, Numeric, "Continuous", Format( "Best", 12 ) );

		// compute the sum
		Column( dt, newColName ) << set each value(
			Column( dt, colName1 )[Row()] + Column( dt, colName2 )[Row()]
		);
	)
);

I left some other options in there, like a list of specific columns, or using selected columns.

I hope this helps!

@jules

Re: How can I get the sum of all different combinations of a group of columns?

DivisiveFerret8 — Thu, 18 May 2023 21:03:05 GMT

Thank you so much! I think this script only produces pairwise combinations, is there a way to have all possible combinations? (i.e, all the possible combinations of 2, 3, 4, 5, 6, 7, 8 and 9 columns),

Thank you again for your help,

Re: How can I get the sum of all different combinations of a group of columns?

txnelson — Thu, 18 May 2023 21:15:57 GMT

I am really not clear on exactly what the end result needs to be. However, below is a script that sums up all combinations of columns for a data table that has 7(not 10, but will work with 10) numeric columns. It creates a new table with the sum for each of the combination of columns being created.

Names Default To Here( 1 );
dt = 
// Open Data Table: Blood Pressure.jmp
// → Data Table( "Blood Pressure" )
Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

startCol = 3;
endCol = N Col( dt ) - startCol - 1;

colNames = dt << get column names( string );
Remove From( colNames, 1, startCol - 1 );

dtOutput = New Table( "output", New Column( "Columns Used", character ), New Column( "Sum" ) );

// Build Combinations
For( nInCombo = 2, nInCombo <= endCol, nInCombo++,
	combos = NChooseK Matrix( endCol, nInCombo );
	Show( endcol, combos );
	For( row = 1, row <= N Rows( combos ), row++,
		Current Data Table( dt );
		name = "";
		cols = N Cols( combos );
		code = "x=Sum( col sum( :\!"" || colNames[combos[row, 1, 1]] || "\!"n)";
		name = colNames[combos[row, 1, 1]];
		For( col = 2, col <= cols, col++,
			code = code || ", col sum( :\!"" || colNames[combos[row, col, 1]] || "\!"n)";
			name = name || " + " || colNames[combos[row, col, 1]];
		);
		code = code || " )";
		Eval( Parse( code ) );
		dtOutput << add rows( 1 );
		dtOutput:Columns Used[N Rows( dtOutput )] = name;
		dtOutput:Sum[N Rows( dtOutput )] = x;
	);
);

If you want the summations done row by row, my code can easily be changed to create in the existing data table, a new column for each of the combinations

Re: How can I get the sum of all different combinations of a group of columns?

DivisiveFerret8 — Thu, 18 May 2023 21:31:44 GMT

Hi Jim,

Thank you so much for your help,

This is exactly what I was looking for, I need to get a column with the sum of each of the combinations if possible (all possible combinations of 2, 3, 4, 5, 6, 7, 8 AND 9 columns)

I am just starting in JSL and I really appreciate all your help,

Re: How can I get the sum of all different combinations of a group of columns?

txnelson — Thu, 18 May 2023 22:23:48 GMT

Here is the modification to the code that I previously submitted. This code add a new column for each combination of the first 10 columns. It adds 1013 new columns. Please check the correctness of the additions to the data table.

Names Default To Here( 1 );
dt = data table("sum combined");

startCol = 2;
endCol = N Col( dt ) ;

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

// Build Combinations
For( nInCombo = 2, nInCombo <= endCol, nInCombo++,
	combos = NChooseK Matrix( endCol, nInCombo );
	For( row = 1, row <= N Rows( combos ), row++,
		// create the column name and the formula for the new column
		name = "";
		cols = N Cols( combos );
		code = "Sum(  :\!"" || colNames[combos[row, 1, 1]] || "\!"n";
		name = "\!"" || colNames[combos[row, 1, 1]];
		For( col = 2, col <= cols, col++,
			code = code || ",  :\!"" || colNames[combos[row, col, 1]] || "\!"n";
			name = name || " + " || colNames[combos[row, col, 1]];
		);
		name = name || "\!"";
		code = code || " )";
		eval(parse("dt << new column(" || name || ", formula(" || code || "));"));
		// Uncomment the next line to remove the formula for the column.  This 
		// will probably make the data table run faster
		//column(dt,ncols(dt)) << delete property("formula");
	);
);

Re: How can I get the sum of all different combinations of a group of columns?

DivisiveFerret8 — Thu, 18 May 2023 22:46:04 GMT

Thank you so much Jim for your help!