Multiplying multiple columns one by one

[#1]

I have 20 columns of data.  I need to multiply each column with all of the other columns - one by one.  For example, column 2 * column 3; column 2 * column 4; column 2 * column 5...column 3* column 4, column 3 * column 5...Is there an easy way to do this with JMP?

Thanks for your help,

Jess

[#2]

Hi @Jess,

I'm not aware of any pairwise multiplication of that sort available interactively, but it can be done with some straightforward scripting. Have you worked with JSL at all? Below is one approach that allows you to specify the column number to start with and end with, and the script takes care of making the pairwise product columns. If you haven't used JSL before let me know, I'm happy to walk through the details a bit more. 

dt = Open( "$SAMPLE_DATA\US Demographics.jmp" );

startCol = 5;
endCol = 14;

For( i = startCol, i <= endCol - 1, i++,
	For( j = i + 1, j <= endCol, j++,
		colName = Eval( :Column( i ) << Get Name ) || " * " || Eval( :Column( j ) << Get Name );
		New Column( colName, seteachvalue( :Column( i )[] * :Column( j )[] ) );
	)
);

I hope this helps!

@julian 

[#3]

You can get the combinations of columns with nchoosekmatrix().  If you only want the matrix of numbers (for whatever reason) you can do it pretty quickly.  

Names Default to Here( 1 );
dt = new table("Test", 
	add rows(20), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
	New Column("C", <<Set Each Value(random integer(1, 10))), 
);

if(ncols(dt) > 14, 
	dt << Delete Column(15::ncols(dt));
);

cols_to_multiply = 2::14;
n = nitems(cols_to_multiply);
nck_mat = nchoosekmatrix(n, 2); // this only picks 2 of each combination

// if you just want a matrix
st = HPTime();
dt_mat = dt[0, cols_to_multiply]; //get the values as a matrix 
big_mat = dt_mat[0, nck_mat]; // get the combinations of dt values
m_mat = shape(big_mat, nitems(big_mat)/2); // have to shape this to only 2 columns so you can multiply accross
m_mat = shape(m_mat[0, 1] :* m_mat[0, 2], 20); // multiply together then reshape to n rows
tot_mat = HPTime() - st;

//turning it into a data table
st = HPTIme();
nck_cols = shape(cols_to_multiply[nck_mat], nrows(nck_mat), 2);// to get the column names
for(i=1, i<=ncols(m_mat), i++, 
	col_name = (Column(dt, nck_cols[i, 1]) << Get Name()) || " * " || (Column(dt, nck_cols[i, 2])<< Get name());
	New Column(col_name, <<Set Values(m_mat[0, i]));
);
tot_dt = HPTIme() - st;


// you can also do it with a formula but it's a little slower
st = HPTIme();
for(i=1, i<=nrows(nck_cols), i++, 
	col_name = (Column(dt, nck_cols[i, 1]) << Get Name()) || " * " || (Column(dt, nck_cols[i, 2])<< Get name());
	New Column(col_name, <<Set Each Value(Column(dt, nck_cols[i, 1])[] * Column(dt, nck_cols[i, 2])[]));
);
tot_dt_formula = HPTIme() - st;
show(tot_mat, tot_dt, tot_dt_formula);

// tot_mat = 81;
// tot_dt = 37961;
// tot_dt_formula = 44007;