4.1.2 Data Reduction

Data-Reduction

Deleting

Delete Columns

You can delete columns by name or index:

del col("Sample 6"); // By Long Name
del col(G);          // By Short Name
del col(7);          // By column index (counting from 1)

NextCol = 7;         // By variable index
del wcol(NextCol);

Averaging

Average Data from All Sheets with Row Statistics

The following code will use the rowStats X-Function to average each column in a workbook across of all the sheets and put the results into a new book. Start with a workbook with two or more worksheets each containing two or more columns of data (same number of columns in each).

string ss$ = %H;        // Save active book name.
int ncols = wks.ncols;  // Save number of columns of active sheet.
newbook s:=3;         // New book for Results, with 3 sheets.

// Name each sheet:
range s1=1!;s1.name$ = Mean;
range s2=2!;s2.name$ = N;
range s3=3!;s3.name$ = SD;

loop(i,1,ncols)
{
   rowstats [ss$](1:end)!wcol(i) 
   mean:=1!wcol(i) 
   n:=2!wcol(i) 
   sd:=3!wcol(i);
}

Data Average over Selected Sheets

The following code will use the rowStats X-Function to average each column in a workbook across of all the Selected sheets and put the results into 3 sheets named Mean, N, SD in the same book. Start with a workbook with three or more worksheets each containing two or more columns of data (same number of columns in each). Select two or more sheets by holding the Ctrl key and clicking the sheet names.

int ncols=wks.ncols;//save number of columns of active sheet
// get selected sheets like 3,4,6
string sel$=SelLayers()$;

if(exist([%H]Mean!,2)==0)
{
	newsheet name:="Mean";
	newsheet name:="N";
	newsheet name:="SD";
}

type "selected sheets %(sel$)";
loop(i,1,ncols) 
{
   rowstats (%(sel$))!wcol(i) mean:=Mean!wcol(i) n:=N!wcol(i) sd:=SD!wcol(i);
}
page.active$=Mean;

Average XYY Data from All Sheets with Interpolation of X

The following code will use the average X-Function to average the common XYY pairs in all of the sheets and put the

results into a new sheet. All of the sheets in the book should be of similar data; i.e. first column is X values and

each sheet has the same number of columns and the same column names, etc.. Each sheet does not have to have the same

number of X values as linear interpolation will occur.

// Point to where the data files are 
string path$ = system.path.program$ + "Samples\Import and Export\"; 
// Find all files with wild card 
findfiles ext:="S*-*-*.dat"; 
int numFiles = fname.GetNumTokens(CRLF); 
// Start a new book 
newbook; 
// Loop over all files and import each as a new sheet in the book
for(int ifile = 1; ifile <= numFiles; ifile++) 
{ 
   string filepath$, file$; 
   // Get next file name 
   filepath$=fname.gettoken(ifile,CRLF)$; 
   // Import by adding new sheet 
   impasc fname:=filepath$ options.ImpMode:= 4; 
} 

//Average all sheets	

// Get number of layers
int nlayers = page.nlayers;

// Get X  
range rXIn = col(1);

// Get number of Y cols - same in all sheets
int ncols = wks.ncols - 1;

// Add a new output sheet 
newsheet name:="Average";

// Loop over all data cols and do averaging
for(int icol = 1; icol<=ncols; icol++)
{
  averagexy iy:=(1:$(nlayers))!(1,$(icol + 1)) method:=1 interp:=1 x:=col(1) y:=wcol(icol+1);
  // Get long name and units of data Y col in first sheet and assign to output Y col
  range rYIn = 1!wcol(icol+1);
  col($(icol+1))[L]$ = rYIn[L]$; 
  col($(icol+1))[U]$ = rYIn[U]$; 
}
// Get long name and units of data X col and assign to output x col
col(1)[L]$ = rXIn[L]$; 
col(1)[U]$ = rXIn[U]$;