373116_en-US

liliconcom

How do I multiply two tables together

I have a big data table and a normalization factor table.

The data table looks like this:

ID	Type	parameter1	parameter2	parameter3
01	1	data	data	data
02	1	data	data	data
03	2	data	data	data
04	2	data	data	data

The normalization factor table looks like this:

Type	parameter1	parameter2	parameter3
1	normalization factor1	normalization factor2	normalization factor3
2	normalization factor4	normalization factor5	normalization factor6

How can I multiply the first table by the second table based on type and parameter and obtain a new table like this:

ID	Type	parameter1	parameter2	parameter3
01	1	data*normalization factor1	data*normalization factor2	data*normalization factor3
02	1	data*normalization factor1	data*normalization factor2	data*normalization factor3
03	2	data*normalization factor4	data*normalization factor5	data*normalization factor6
04	2	data*normalization factor4	data*normalization factor5	data*normalization factor6

My raw data table is huge in both rows and columns so I want a fast solution.

Thank you very much.

Data TableScriptingRe: How do I multiply two tables together

You could use a virtual join and formula columns.

Virtual join adds the blue and gold keys

You can link them interactively or with JSL.

This may be using JMP16 names with the  :"parameter3[Type]"n syntax; you might need name("parameter3[Type]") instead.

 

// make example tables. **save them** so virtual join works.
factorDT = New Table( "Imported Data 2",
 New Column( "Type", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2] ) ),
 New Column( "parameter1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1.1, 1.2] ) ),
 New Column( "parameter2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0.8, 0.9] ) ),
 New Column( "parameter3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1.1, 0.9] ) )
);
factorDT << save( "$temp/factor.jmp" );
dataDT = New Table( "Imported Data",
 New Column( "ID", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4] ) ),
 New Column( "Type", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 1, 2, 2] ) ),
 New Column( "parameter1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4] ) ),
 New Column( "parameter2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [4, 3, 2, 1] ) ),
 New Column( "parameter3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 3, 3, 2] ) )
);
dataDT << save( "$temp/data.jmp" );

// join the tables
factorDT:type << Set Property( "Link ID", 1 );
dataDT:type << Set Property( "Link Reference", Reference Table( "factor.jmp" ) );

// add formula columns
dataDT << New Column( "p 1", Numeric, "Continuous", Format( "Best", 12 ), Formula( :"parameter1[Type]"n * :parameter1 ) );
dataDT << New Column( "p 2", Numeric, "Continuous", Format( "Best", 12 ), Formula( :"parameter2[Type]"n * :parameter2 ) );
dataDT << New Column( "p 3", Numeric, "Continuous", Format( "Best", 12 ), Formula( :"parameter3[Type]"n * :parameter3 ) );

// force the evaluation to complete
dataDT<<RUNFORMULAS></RUNFORMULAS>

 

 

 

Re: How do I multiply two tables together

And if you don't need the formulas, you could consider element wise multiplication of matrices which may or may not be 'better'. On my MacBook the code below took about 15 seconds.

NamesDefaultToHere(1);

nr = 10000;  // Number of rows
nc = 10000;  // Number of Columns

dt1 = AsTable(J(nr, nc, RandomInteger(1, 20)));
dt2 = AsTable(J(nr, nc, RandomUniform(0, 1)));

m1 = dt1 << getAsMatrix;
m2 = dt2 << getAsMatrix;

dtFinal = AsTable(m1 :*</img> m2);

 

Re: How do I multiply two tables together

Thank you for your reply. However, I have many columns and column names are complicated. Therefore I want to write a for loop to create new columns. Can you explain more on this statement?

Formula( :"parameter1[Type]"n * :parameter1 )

 I am thinking about writing a for loop like this:

 

</CODE></PRE><PRE class="lia-code-sample language-markup"><CODE>colnames = dataDT << Get Column Names();
nc = ncols(dataDT);

DT = New Table("normalized", New Column("ID", values(dataDT:ID << get values)),
 New Column("Type", values(dataDT:Type << get values)));
For(i = 3, i<= nc, i++,
DT << New Column( char( colnames[3]), Numeric, "Continuous", Format( "Best", 12 ), Formula( XXXX ) ));

But I don't know how to construct the formula correctly. It keeps giving me errors. Can you help me?

Thank you.

 

Re: How do I multiply two tables together

Thank you, it works on a small dataset. But I have millions of rows in table 1 and only two rows in table 2. Therefore I want to avoid creating another huge table. If I can't find a better solution, I will try this method.

Re: How do I multiply two tables together

The loop is not easy; I think this will do it:

// make example tables. **save them** so virtual join works.
factorDT = New Table( "Imported Data 2",
 New Column( "Type", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2] ) ),
 New Column( "parameter1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1.1, 1.2] ) ),
 New Column( "parameter2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0.8, 0.9] ) ),
 New Column( "parameter3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1.1, 0.9] ) )
);
factorDT << save( "$temp/factor.jmp" );
dataDT = New Table( "Imported Data",
 New Column( "ID", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4] ) ),
 New Column( "Type", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 1, 2, 2] ) ),
 New Column( "parameter1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4] ) ),
 New Column( "parameter2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [4, 3, 2, 1] ) ),
 New Column( "parameter3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 3, 3, 2] ) )
);
dataDT << save( "$temp/data.jmp" );

originalColumns = dataDT<<GETCOLUMNNAMES></GETCOLUMNNAMES>

• capture the original columns before adding the hidden columns with virtual join
• build the expression e using \!" to escape the embedded quotation marks
• use write() to debug your work, not print
• <