How do I multiply two tables together

Angela24996

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.

brady_brady

Hi,

 

I took a look at this and noticed a performance wall once the table got near the size of yours (actually, well before that.) I was able to improve things a bit once I began to process the table in "blocks" of about 10K rows by 1K columns. You could play with the size to see what works best, but these dimensions seemed to work well.

 

This method does not create a new table--it overwrites the original... so you'd want to save the original, then save the result under a different name.

 

The script below makes a couple of assumptions, as it is just meant to illustrate the overall concept--

- corresponding columns are in the same order in each table.

- for p parameters, the main table has p+2 columns, and the factor table has p+1 columns. there are no other columns in either table.

 

The actual processing time for a 1million row table with a thousand columns was about 40 seconds... each "block" took about 0.3 - 0.4 seconds to process.

 

The table setup portion, to mock the tables you have, took a while... probably a couple of minutes. Currently the script is set for a smaller table... modify the lines under the //table settings comment to change this.

 

The code is neither elegant nor compact, but it runs faster than anything else I could come up with (including using element-wise multiplication on the entire block at once... for some reason, iterating over the columns took about half the time.) I didn't try Craige's formula-based approach, though.

 

The initial sort is needed to keep from having to fetch rows using where() (if you have to do that, it slows things considerably.)

 

Cheers,

Brady

 

Names Default To Here( 1 );

//set limits on block size
blockRowLimit = 1e4;
blockColLimit = 1e3;

// table setup
rowsdt1 = 1e4;
colsdt1 = 1e2;

dt1 = As Table( (1 :: rowsDt1)` || J( rowsDt1, 1, Random Integer( 1, 2 ) ) || J( rowsdt1, colsdt1, Random Uniform() ), <<invisible );
Column( dt1, 1 ) << set name( "ID" );
Column( dt1, 2 ) << set name( "Type" );

dt2 = As Table( [1 1, 2 2] || J( 2, colsdt1, Random Uniform() ), <<invisible );
Column( dt2, 1 ) << set name( "Type" );
dt2 << Delete Columns( 2 );


////processing begins here

dt1 << sort( by( :type ), order( ascending ), replacetable );
type2Begin = Contains( dt1:type << getasmatrix, 2 );		//row # of first occurrence of type == 2 in sorted table.

//build matrices that show where row & column divisions of "blocks" will be
rowBreaks = (1 :: N Row( dt1 ) :: blockRowLimit) || Matrix( type2Begin ) || Matrix( N Row( dt1 ) );
rowbreaks = rowbreaks[Rank( rowbreaks )]; //sort the matrix

colBreaks = (3 :: N Col( dt1 ) :: blockColLimit) || Matrix( N Col( dt1 ) );
colBreaks = colBreaks[Rank( colBreaks )]; //sort the matrix

nrowBreaks = N Row( rowBreaks );
ncolBreaks = N Row( colBreaks );

For( i = 1, i < nrowBreaks, i++,
	lowRow = rowBreaks[i];
	highRow = if ( i == nRowBreaks - 1, rowBreaks[i+1], rowBreaks[i+1] -1 ); //we don't need to subtract 1 off the last upper bound.
	rowRange = lowRow :: highRow;
	
	If( rowrange[1] < type2Begin, factorRow = 1, factorRow = 2); //determine which row of factors we'll work with for this row range of data
	
	For( j = 1, j < ncolBreaks, j++,		
		lowCol = colBreaks[j];
		highCol = if ( j == ncolBreaks - 1, colbreaks[j+1], colbreaks[j+1] -1 );  //we don't need to subtract 1 off the last upper bound.
		For( c = lowCol, c <= highCol, c++,
			dt1[rowRange, c] *= dt2[factorRow, c - 1]   // -1 because dt1 has an ID column while dt2 does not
		);				
	);
);

dt1 << bring window to front;