632913_en-US

liliconcom

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

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

Automation and ScriptingRe: How can I get the sum of all different combinations of a group of columns?

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);<BR />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=""></GET>

 

 

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

 

 

I hope this helps!

@julian 

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

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?

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?

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?

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?

Thank you so much Jim for your help!