topic Re: Subsetting a datatable with a For loop and performing colunm operations in each subset in Discussions

Subsetting a datatable with a For loop and performing colunm operations in each subset

lmickael — Mon, 25 Mar 2019 13:08:48 GMT

Dear community, i have a hard time on this topic may be someone can help. My Datatable comprises many parts under "Serialnumber" that are measured at different point of time "Timestamp". The results of the Measurement (like Isc, Pmpp, ...)

ist stored in rows with increasing measurement time. What i need to do ist to normalize the measurement value with the very first measurement (earliest in time) for each part individually. Like Part XY -> (Pmpp/Pmpp0; t=0)

I wrote a script subsetting my big datatable into many subtables containing only one part and its consecutives measurement over time. As below code:

dt_LT = Open(big_datatable.jump);
Mod_Tab = dt_LT << Tabulate( Add Table( Row Table( Grouping Columns( :SerialNumber ) ) ) );
Mod_dt = Mod_Tab << Make Into Data Table;
Mod_dt << Set Name( "Modul-Datatable" );
Zahl_Mod = NRow (Mod_dt);
Current Data Table (Mod_dt);
Mod_List = :Name( "SerialNumber") << Get Values;
content= Text Box(Mod_List);
New Window ("Modul gefunden", content);
Current Data Table (dt_LT);
For (i = 1, i <= Zahl_Mod, i++,
dt_LT << clear row states;
dt_LT << select Where (:Name ("SerialNumber") == Mod_List[i]);
If( N Row( dt_LT << get selected rows ) < 3, Continue() );
Mod = dt_LT << Subset ((selected Rows), Output Table Name("Module_"||Mod_List[i]));

This works just fine. I get as many subtables as needed. Also find the earliest measurement in each subset works fine. See code below:

Current data table(Mod);
Eval (init_Zeit = col minimum(:Timestamp);
init_flash = Mod << get rows where ((:Timestamp) == init_Zeit);
row = init_flash[1];

What fails is the normalization. The script get stuck on the last subset and normalizes all other data with the earliest measurement of the last subset table. See the script below

Em0 = :Eff_Modul[row];
Ez0 = :Eff_Zelle[row];
FF0 = :FF[row];
Impp0 = :Impp[row];
Isc0 = :Isc[row];
Pmpp0 = :Pmpp[row];
Rs_Serial0 = :Rs_Serial[row];
Rsh_Shunt0 = :Rsh_Shunt[row];
Vmpp0 = :Vmpp[row];
Impp0 = :Impp[row];
Voc0 = :Voc[row];
New Column ("Pmpp_Norm", Numeric, continuous,
Formula ( :Pmpp / Pmpp0));

In the last subset table i get a 1 after normalisation in the measurement colunms In all other subtables i get a value that is below 1 for the normalized value. How can i switch between subset tables within the for loop? Can anybody help? Thx in advance

Mickael

Re: Subsetting a datatable with a For loop and performing colunm operations in each subset

ms — Thu, 14 Mar 2019 16:23:56 GMT

You use a global variable Pmpp0 in a column formula and when the value of Pmpp0 keeps changing with each iteration the formula calculations change accordingly. If using this approach you must explicitly use the evaluated current value of the variable in the Formula.

For example:

Pmpp0 = :Pmpp[row];
F = Eval Expr(:Pmpp / Expr(Pmpp0));// Formula expression with evaluated Pmpp0
Mod << New Column("Pmpp_Norm", Numeric, continuous, Formula(Name Expr(F)));

Re: Subsetting a datatable with a For loop and performing colunm operations in each subset

lmickael — Fri, 15 Mar 2019 07:42:50 GMT

Hello, thank you for your super fast answer!

i rewrote the script as proposed. The calculation works nicely for the first item of the list than the for loop breaks and never switches to the next item. The debugger mentions something with reference to an object that has no L-value...

Don't really know what it means. Do i need to reset any variable? or delete selection on an active table?

Thanks a lot again!

VG, Mickael

Re: Subsetting a datatable with a For loop and performing colunm operations in each subset

Mark_Bailey — Mon, 18 Mar 2019 09:54:26 GMT

The L-value is the left side of an assignment. You are trying to assign something to a non-variable. For example:

Insert Into( { 5, 6, 7 }, 8 );

This alternative would work:

my list = { 5, 6, 7 };
Insert Into( my list, 8 );

Re: Subsetting a datatable with a For loop and performing colunm operations in each subset

lmickael — Mon, 25 Mar 2019 12:22:18 GMT

Thanks for your reply. Actually i had in my for loop an expression that i called "I" this created confusion with the incrementation index "i". I renamed my expression "I1" and it worked!

Thx, Mickael

Re: Subsetting a datatable with a For loop and performing colunm operations in each subset

lmickael — Mon, 25 Mar 2019 12:23:30 GMT

it works nicely. Thx for your help!

Rgds, Mickael