Saving output of a for loop so that the data isn't over-written.

[#1]

Hello all,

I have a for loop that generates individual tables based on unique items in a list. On each table generated, calculations are done, and new columns are added to each table. Please see below for the code and my comments for each line.

MaskList = Associative Array(Column(dt3, "mask") << Get Values) << Get Keys; // from dt3, puts unique masks into a list, removes duplicates

show(MaskList); // show for debugging purposes, to be viewed in Log window

// This loop creates a new table for each unique mask in MaskList
For (i = 1, i <= N Items(MaskList), i++,	  
 
 dt3 << Select Where( :mask == MaskList[i]); // selects the rows of the chosen mask at the i-th position in MaskList
 dt4 = dt3 << Subset (Output Table("Mask Table"), Get Selected Rows, "visible");  // outputs a separate table for each unique mask
 
 dt4 << New Column("new_x_offset", Numeric, "Continuous", Format( "Best", 12 ),	Formula( Round( :best_x_offset, 2 ) )); // calculation and new column
 dt4 << New Column("new_y_offset", Numeric, "Continuous", Format( "Best", 12 ),	Formula( Round( :best_y_offset, 2 ) )); // calculation and new column
 dt4 << New Column("new_xscl", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:xscl - (-0.5)), 2 ) )); // calculation and new column
 dt4 << New Column("new_yscl", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:yscl - (-0.5)), 2 ) )); // calculation and new column
 dt4 << New Column("new_ort", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:ort), 2 ) ));	// calculation and new column
 dt4 << New Column("new_rotg", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:rotg), 2 ) ));	// calculation and new column
 dt4 << New Column("new_mag", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:mag - (-1.0)), 2 ) ));	// calculation and new column
 dt4 << New Column("new_rotf", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:rotf - (-1.0)), 2 ) )); // calculation and new column
 
 dt4 << New Column("Mean_x_offset", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_x_offset ) )); // take average of values in column new_x_offset
 dt4 << New Column("Mean_y_offset", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_y_offset ) )); // take average of values in column new_y_offset
 dt4 << New Column("Mean_new_xscl", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_xscl ) )); // take average of values in column new_xscl
 dt4 << New Column("Mean_new_yscl", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_yscl ) )); // take average of values in column new_yscl
 dt4 << New Column("Mean_new_ort", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_ort ) )); // take average of values in column new_ort
 dt4 << New Column("Mean_new_rotg", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_rotg ) )); // take average of values in column new_rotg
 dt4 << New Column("Mean_new_mag", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_mag ) )); // take average of values in column new_mag
 dt4 << New Column("Mean_new_rotf", Numeric, "Continuous", Format( "Best", 12 ), Formula( Col Mean( :new_rotf ) )); // take average of values in column new_rotf
 
Output_x_offset = Associative Array(Column(dt4, "Mean_x_offset") << Get Values) << Get Keys; // saves average x_offset, removes duplicates 
Output_y_offset = Associative Array(Column(dt4, "Mean_y_offset") << Get Values) << Get Keys; // saves average y_offset, removes duplicates 
Output_xscl = Associative Array(Column(dt4, "Mean_new_xscl") << Get Values) << Get Keys; // saves average xscl, removes duplicates 
Output_yscl = Associative Array(Column(dt4, "Mean_new_yscl") << Get Values) << Get Keys; // saves average yscl, removes duplicates 
Output_ort = Associative Array(Column(dt4, "Mean_new_ort") << Get Values) << Get Keys; // saves average ort, removes duplicates 
Output_rotg = Associative Array(Column(dt4, "Mean_new_rotg") << Get Values) << Get Keys; // saves average rotg, removes duplicates 
Output_mag = Associative Array(Column(dt4, "Mean_new_mag") << Get Values) << Get Keys; // saves average mag, removes duplicates 
Output_rotf = Associative Array(Column(dt4, "Mean_new_rotf") << Get Values) << Get Keys; // saves average rotf, removes duplicates 
    	
    );

My question is 3-fold:

1. Is it possible to take the average of values in a column without creating a new column to store the data? Ideally, the average would just be a single number, not a column with the same number over and over again. Perhaps just spitting in out in a new table, list, or something else, just make the code/table more readable and less bloated.

This is part of the output of the above code. I just get columns with the same number over and over for the calculated averages.

1. For each data table created by the for loop, I would like for the data in the calculated columns to be stored in some sort of table or list so that they're not overwritten with each new iteration of the for loop.

1. The Associative array function is rounding the data in the column. Can it be set to not do that? 

I've attached an example of the output of the code. For simplicity, I restricted MaskList to 1 item, so the for loop only runs once.

Any help is appreciated.

Thanks.

[#2 · Accepted solution]

I believe the below script will give you the separate data table you want with the means for each mask.  It creates the new rounded columns in the dt3 table, and then just runs a Summary Platform(tables=>Summary) on the rounded columns.

Names Default To Here( 1 );
dt3 = Data Table( "dt3" );
dt3 << New Column( "new_x_offset", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( :best_x_offset, 2 ) ) ); // calculation and new column
dt3 << New Column( "new_y_offset", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( :best_y_offset, 2 ) ) ); // calculation and new column
dt3 << New Column( "new_xscl", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:xscl - (-0.5)), 2 ) ) ); // calculation and new column
dt3 << New Column( "new_yscl", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:yscl - (-0.5)), 2 ) ) ); // calculation and new column
dt3 << New Column( "new_ort", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:ort), 2 ) ) );	// calculation and new column
dt3 << New Column( "new_rotg", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:rotg), 2 ) ) );	// calculation and new column
dt3 << New Column( "new_mag", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:mag - (-1.0)), 2 ) ) );	// calculation and new column
dt3 << New Column( "new_rotf", Numeric, "Continuous", Format( "Best", 12 ), Formula( Round( 1 - (:rotf - (-1.0)), 2 ) ) ); // calculation and new column
 
dtSum = dt3 << Summary(
	Group( :mask ),
	Mean( :new_x_offset, :new_y_offset, :new_xscl, :new_yscl, :new_ort, :new_rotg, :new_mag, :new_rotf ),
	Freq( "None" ),
	Weight( "None" )
);

You may also want to look at the Tabulate Platform(Analyze=>Tabulate), which could also be used 

[#3]

Thank you. Either option works great.

I wonder if there a way to combine the dtSum tables (or the output of Tabulate) into one single table.