Manipulate large dataset (scripting)Hello,
I want to manipulate a large GC-MS dataset (48 Millions rows) and I get stuck at an intermediate step of data manipulation.
I want to create a new table, through scripting, from an intermediate table Num-Example.jmp here attached (it is a subset of 14 rows of my intermediate table of 340 000 rows = conditions which should lead to the final table of 48 millions rows = condition & data) .
For this subset I need to automatically (through scripting) create a new table containing 2119 rows (this corresponds to row 14 of column Cumulative[A] in Num-Example.jmp) with a column ":ID#" populated with ID# = "1" in the first 131 rows, then ID# = "2" in the next 150 rows, and so on.
ID# = "1" and 131 come from informations in row 1 of Num-Example.jmp (column "ID#" for the ID# and column ":A" for the number of rows to create in the new table).
ID# = "2" and 150 come from informations in row 2 of Num-Example.jmp.
and so on up to last row = 14 (ID# = 14).
Thank you for your help.
Emmanuel
Re: Manipulate large dataset (scripting)Given the size of the data sets, I'd avoid putting anything into a Data Table until the very end. Work as much as you can with matrices. Something like this might work:
Names Default to Here(1);
mainTbl = Current Data Table();
idCol = Column(mainTbl,"A");
idNumCol = Column(mainTbl,"ID#");
ids = idCol << Get Values;
idNums = idNumCol << Get Values;
firstVals = [1] |/ (Cumulative Sum(ids)+1)[1::(N Row(ids)-1)];
lastVals = Cumulative Sum(ids);
idMat = J(Sum(ids),1,0);
For Each({idVal,i},ids,
idMat[firstVals[i]::lastVals[i]] = Repeat(idNums[i],idVal)
);
Re: Manipulate large dataset (scripting)This seems to work well only using a data table
names default to here(1);
dt=current data table();
ca=column(dt,"A");
cid=column(dt,"id#");
rowranges=column(dt,"Cumulative [A]")<< get values;
rowranges=[0] |/ rowranges;
dtnew=new table();
cidnew=dtnew << new column("id#");
for(ii=1, ii<=n rows(dt), ii++,
dtnew << add rows(ca[ii], at end);
cidnew[rowranges[ii]::rowranges[ii+1] ]=cid[ii];
);Re: Manipulate large dataset (scripting)I'd be careful with this approach. Unless the input table is small, it's going to be really slow. I took a quick look at the two approaches with an input tables of 1000 and 10,000 rows. The difference in speed was ~4X and ~12X, respectively (0.6958 sec vs. 0.1722 sec for 1,000 and 6.64 sec vs. 0.543 sec for 10,000)
What would be interesting would be to preallocate the table rows then write directly to the table. Would there be much of a difference then?
Re: Manipulate large dataset (scripting)Hello, thank you both Don and Sam. The 2 solutions work but as Don said the use of matrices is faster with my large dataset. Emmanuel
