4.1 Set Column Values

Python functions can be called from LabTalk, such as from Set Column Values dialog, Script Window, script associated with a button, or anywhere else in Origin with access to LabTalk scripting.

1 Using Python Functions in Column Formula
2 Calling Python Functions from External Files
3 Calling Functions from File Attached to Project

Using Python Functions in Column Formula

Python functions can be defined and accessed in the Set Values dialog for both Worksheets and Matrixsheets. The Python function should be defined in the Python Function tab. The function can then be called from the formula edit box or from the Before Formula Scripts tab.

If your Python function requires other packages, they need to be installed prior to defining and accessing the function from the Set Values dialog.

With column formula, the function should be defined to return a list of floats or strings in order to have optimal performance. If the function is defined to return a single float or a string, then the function will be called for each row involved in the calculation.

Simple Example

Define function in Python Function tab

def add1(a):
    return [val+1 for val in a]

Use the function above in column formula

py.add1(A)

You can also run these LabTalk script:

//define function in python field
wks.col2.py$=
"def add1(a):
    return [val+1 for val in a]
";

//use the function above in column formula
wks.col2.formula$="py.add1(A)";

Example 1

In this example, a Python function is used in column formula of column C to smooth noisy data from column B. The function is defined as:

import numpy as np
from scipy import signal

def smooth(y, npts, norder):
    """
    F:Fii
    """
    y=signal.savgol_filter(np.array(y), npts, norder)
    return y

This function accepts a list of floats and two integers that specify the smoothing window and polynomial order, and returns a list of floats. The input and output types are specified using docstring as F:Fii. It is assumed here that NumPy and SciPy packages are already installed.

To access the function from the formula edit box, the function name needs to be pre-pended with py...:

py.smooth(B, 51, 2)

The image below shows the worksheet with the raw data and results, and the Set Values dialog with the Python Function tab.

Example 2

In this example, a Python function is used to perform group statistics on data columns. The function accepts three columns of data and returns three columns with the results. The function therefore cannot be directly called from the Formula box. Instead, the Before Formula Scripts tab is used to call the function.

The function is defined as:

import pandas as pd

def groupstats(ID, x, y):
    if len(ID) == len(x) and len(ID)==len(y):
        df = pd.DataFrame({'ID':ID,'x':x,'y':y})
        df1 = df.groupby('ID').mean()  
        return df1.index, df1.x, df1.y
    else:
        return [], [], []

Note that the input and output types are not explicitly defined in this function. The three input columns passed to the function are converted to lists. The computation is performed using dataframe, and the function returns three lists. The values returned are placed in three output columns using the script command in the Before Formula Scripts tab:

col(D), col(E), col(F) = py.groupstats(col(A), col(B), col(C))

Example 3

In the previous two examples, the Python function was defined in the Set Values dialog. The worksheet could then be saved as a template along with the Python function and then a new instance of the template can be opened for future use.

Python functions can also be defined in external files and then accessed in column or cell formula. In the following example, a function defined in the file labTalk.py is called. More details on defining functions in external files are available in the sections that follow.

You can save a column formula, along with Before Formula Scripts and Python Formula codes by (1) clicking Formula: Save As on the Set Values menu bar, (2) by clicking the Save Formula button in Set Values or (3) by right-clicking on the column label row F(x)= cell and choosing Save As. You can later load the formula from the Origin menu: Column: Fill with User Formula and select your saved formula from the fly-out menu.
If the worksheet F(x)= cell begins "py." and calls a Python function defined in an external file, you can right-click on the F(x)= cell and choose Open Python File to open the Python function in the Code Builder IDE

Calling Python Functions from External Files

Python functions can be defined in external .py files. By default Origin will look for the function in a file named labtalk.py located in your User Files Folder (UFF). You can open this file from the Origin menu Connectivity: Open Default Python Functions....

Functions defined in the labtalk.py file can be called from LabTalk script using syntax such as:

col(B)=py.f1(col(A));

where f1 is the function name.

The file examples.py has several examples of defining Python functions. You can open this file from the Origin menu Connectivity: Open examples.py. Copy the desired function from this file to labtalk.py.

If you define functions in other .py files, you can access them with syntax such as:

col(b) = py.myfuncs.sort(col(a))

where the function sort() is defined in a file named myfuncs.py.

Origin will look for the .py file in the User Files Folder (UFF) by default. A different working directory can be set using the Python object property: Python.LTwd$.

The file name can be left out when calling functions by setting the Python object property: Python.LTwf$.

Python.LTwd$="D:\Python";
Python.LTwf$=MyFunctions.py;
col(B)=py.sort(col(A));   // call function sort from file MyFunctions.py located in D:\Python folder

The object properties used above are empty, and not set by default. The default python file location is internally set to UFF folder, and the default file name is internally set to labtalk.py Once you assign a value to either of these properties, the value is saved in the Origin.ini file located in the UFF.

Multiple Worksheets Example

This example shows how to set multiple columns value by calling Python functions from external files.

1. Define Python function add1() in external file test1.py. It will be used to set column value.

import originpro as op
def add1(a):
    return [val+1 for val in a]

2. Add Python function load() to labtalk.py. It loads external Python file into a string variable.

import originpro as op
def load(fname:str)->str:
    path = op.path()
    path+= fname
    f = open(path, "rt")
    data = f.read()
    return data

3. Run these Labtalk script to set column value

string strPy$ = py.load("test1.py")$;//load the Python codes in external file test1.py 
loop(ii,1,page.nlayers) {
	page.active=ii;
	wks.ncols=wks.ncols+1;
	wks.col=wks.ncols;
	wks.col.formula$="1";
	wks.col.py$=strPy$;
	wks.col.formula$="py.add1(A)";//use Python function add1() to set value
}

Specifying Input and Output Types

Python functions can be defined with specific input and output argument types. There are two ways to specify types:

Using Function annotation
Using Docstrings

Here are several examples:

# Examples where function annotation is used to specify type
def a1(a, k:int):
    return a + k
    
def a2(a, b)->int:
    return a + b
    
def ss1(a:str, b:str)->str:
    """using function annotations"""
    c = a + b
    return c

def ss3(a:list)->list:
    return [len(x) for x in a]

def ss2(a:list, b:list)->list:
    c = [ x + y for x,y in zip(a, b)]
    return c

# Examples where docstring is used to specify type
def s2(a,b):
    """
    S:SS
    concatenate strings, array version
    """
    c = [ x + y for x,y in zip(a, b)]
    return c    

def s1(a, b):
    """
    s:ss
    concatenate strings, scaler version
    """
    c = a + b
    return c

def s3(a):
    """
    F:S
    intput string list and return float list
    """
    return [len(x) for x in a]

    
def sin(a):
    """
    F:F
    return sine
    """
    return np.sin(np.asarray(a))

This function accepts a list of float and two integers as input, and returns a list of float. It also uses NumPy and SciPy packages:

import numpy as np
from scipy import signal
def smooth(y,npts, norder):
     """
     F:Fii
     Perform Savitzky-Golay smoothing using scipy signal
     """
     #npts: number of points for smoothing window
     #norder: polynomial order
     y=signal.savgol_filter( np.array(y), npts, norder )
     return y

However whether the function can be accessed from column formula will depend on how the function is defined.

If it is defined as a scalar function and will be called for each row, the input/output type needs to be specified, such as:

def a1(a:float, b:int = 10)->float:
    return a+b

If it is defined as a vector function, input/output type does not need to be specified, such as

def a1(a, b:int = 10):
    return [x + b for x in a]

Here, the input/output type is assumed as list.

Either way, the function can be used in column formula or from script as in col(B)=py.a1(col(A)).

Returning Multiple Values or Arrays

Multiple values or lists can be returned to LabTalk from Python functions.

This function accepts a list and two floats as input, and returns two lists:

def f3(a, b=5, c=10):
    b = [x + b for x in a]
    c = [x + c for x in a]
    return b, c

In a new worksheet, fill col(A) with some numbers. Then from script, you can access the function such as:

col(b),col(c)=py.f3(col(a))

Tuple-returning example:

You could add the following to the file labtalk.py in your User Files Folder, then save the file:

def TestReturnTuple(arg1, arg2, arg3):
    print('TestReturnTuple11: arg1 = ', arg1)
    print('TestReturnTuple11: arg2 = ', arg2)
    print('TestReturnTuple11: arg3 = ', arg3)
    return ([arg1, 10*arg1, 100*arg1], [arg2, arg2/10., arg2/100., arg2/1000.], 1000 * arg3)

To run the example, do the following:

From the main menu, choose Window: Script Window.
Open a new workbook then, copy the following to the Script Window, select the entire script and press Enter:

col(A), col(B), myvar= py.TestReturnTuple(3., 45., 66);
myvar=;

The function returns a tuple with two lists -- one to col(A) and one to col(B) -- and one numeric scalar that is written to the LabTalk variable, myvar.

Calling Functions from File Attached to Project

Python functions can also be defined in .py file that are attached to the Origin project (OPJU). The functions in attached files can be accessed with the notation such as:

col(b)=py.@filename.func(col(a))

The @ character in front of the file name indicates to Origin that the file is attached to the project.

If there is only one .py file attached to the project, which is recommended, then the file name can be dropped when accessing the function, such as: