2.7.3.2 Worksheet Column Data Manipulation

Basic Operation

Once you have loaded or created some numeric data, here are some script examples of things you may want to do.

Basic Arithmetic

Most often data is stored in columns and you want to perform various operations on that data in a row-wise fashion. You can do this in two ways in your LabTalk scripts: (1) through direct statements with operators or (2) using ranges. For example, you want to add the value in each row of column A to its corresponding value in column B, and put the resulting values in column C: 

Col(C) = Col(A) + Col(B);  // Add 
Col(D) = Col(A) * Col(B);  // Multiply
Col(E) = Col(A) / Col(B);  // Divide

The - and ^ operators work the just as above for subtraction and exponentiation respectively.

You can also perform the same operations on columns from different sheets with range variables: 

// Point to column 1 of sheets 1, 2 and 3
range aa = 1!col(1);
range bb = 2!col(1);
range cc = 3!col(1);
cc = aa+bb;
cc = aa^bb;
cc = aa/bb;

When performing arithmetic on data in different sheets, you need to use range variables. Direct references to range strings are not supported. For example, the script Sheet3!col(1) = Sheet1!col(1) + Sheet2!col(1); will not work!

Functions

In addition to standard operators, LabTalk supports many common functions for working with your data, from trigonometric functions like sin and cos to Bessel functions to functions that generate statistical distributions like uniform and Poisson. All LabTalk functions work with single-number arguments of course, but many are also "vectorized" in that they work on worksheet columns, loose datasets, and matrices as well. Take the trigonometric function sin for example: 

// Find the sine of a number:
double xx = sin(0.3572)
// Find the sine of a column of data (row-wise):
Col(B) = sin(Col(A))
// Find the sine of a matrix of data (element-wise):
[MBook2] = sin([MBook1])

As an example of a function whose primary job is to generate data consider the uniform function, which in one form takes as input N, the number of values to create, and then generates N uniformly distributed random numbers between 0 and 1: 

/* Fill the first 20 rows of Column B 
   with uniformly distributed random numbers: */
Col(B) = uniform(20);

For a complete list of functions supported by LabTalk see Alphabetic Listing of Functions.

Set Formula for Column

In the Origin GUI, the Set Column Values dialog can be used to generate or transform data in worksheet columns using a specified formula. Such transformation can also be performed in LabTalk by using the csetvalue X-Function. Here are some examples on how to set column value using LabTalk. 

newbook;
wks.ncols = 3;
// Fill column 1 with random numbers
csetvalue formula:="rnd()" col:=1;
// Transform data in column 1 to integer number between 0 ~ 100
csetvalue formula:="int(col(1)*100)" col:=2;
// Specify Before Formula Script when setting column value
// and set recalculate mode to Manual
csetvalue formula:="mm - col(2)" col:=3 script:="int mm = max(col(2))" recalculate:=2;
string str$ = [%h]%(page.active$)!;
newsheet cols:=1;
// Use range variables to refer to a column in another sheet
csetvalue f:="r1/r2" c:=1 s:="range r1=%(str$)2; range r2=%(str$)3;" r:=1;

When logic statement is used to set formula for columns, values such as 0.0, NANUM (missing value) and values between -1.0E-290 to 1.0E-290 will be evaluated to be False. For instance, LabTalk command will return a value 0 (False) instead of 1 (True). 

type $(-1e-290?1:0); // Returns 0 (False)
type $(1/0?1:0); // Returns 0 (False), where 1/0 == NANUM

Copy Column

The colcopy X-Function copies column(s) of data including column label rows and column format such as date or text and numeric.

The following example copies columns two through four of the active worksheet to columns one through three of sheet1 in book2: 

// Both the data and format as well as each column long name, 
// units and comments gets copied:
colcopy irng:=(2:4) orng:=[book2]sheet1!(1:3) data:=1 
        format:=1 lname:=1 units:=1 comments:=1;

Sort Column

To sort a specified column, you can use wsort X-Function. And when using this X-Function to sort just one column, the arguments c1 and c2 should be the same column in worksheet, and the bycol also needs to be the same as c1. 

// Create a new workbook
newbook;

// Fill first column with row number, and second column with uniform random number
col(1) = {1:32};
col(2) = uniform(32);

// Sort column 2 descending
wsort c1:=2 c2:=2 bycol:=2 descending:=1;

Reverse Column

The X-Function colreverse is available for reversing column. 

// Create a new workbook
newbook;

// Fill first column with row number, and second column with uniform random number
col(1) = {1:32};
col(2) = uniform(32);

// Reverse column 1 by using index
colreverse rng:=1;  // colreverse rng:=col(A);  // this also works

// Reverse column 2 by using range variable
range rr = 2;
colreverse rng:=rr;
English | Deutsch | 日本語