Script to Find Best Fitting Model for Each Data in Each Group and Create Summary Report

This post will show you how to write a analysis routine by Origin’s LabTalk script to find the best fitting model for each data in each group and create a summary report.

If you want to walk through this step by step with me, please download this file: Sample.xlsx and Tutorial_Sample.ogs

There are 5 columns in Sample.xlsx. The first column is GroupID, which contains 1, 2, & 3. The second column is X data and the rest 3 columns are Y data. So there are 3 * 3 = 9 Y datasets in all.


There are 3 function candidates: ExpDec1, ExpDec2 and ExpDec3. The goal is to find best fitting model for each Y data in each group.


Run the script to see the result

In Windows Explorer, copy Tutorial_Sample.ogs into your Origin’s User Files Folder. You can find Origin’s User Files folder by choosing Start: All Programs: OriginLab: Origin 2015/(the version you have): User Files Folder.

In Origin, choose Window: Script Window menu to open the Script window. Run the following script

run.section(Tutorial_Sample.ogs, main)

It will popup a dialog, asking you to select the data. Select Sample.xlsx. Click OK. The script will run automatically and at the end, a Summary workbook is created to reportthe best fitting function for each Y data in each group.


Review the Script behind the Scene

Now, let’s open the Tutorial_Sample.ogs to see how the script is written.

Choose View: Code Builder menu to open the Code Builder. choose File: Open menu in Code Builder to open Tutorial_Sample.ogs in your Origin User Files folder.

Or in Code Builder, you can choose File: New menu and create your own LabTalk Script File by following the steps below. See this page for more information about creating and running ogs file

Origin’s Labtalk script can be written into an ogs file with sections. Each section starts with section name in []. You can add [main] section at the beginning of the file to control the follow of the script.


Import Excel File

Go to  [ImportExcel] section. It opens a dialog for user to choose an Excel file and import it into Origin.

    string FileName$; //define a string variable FileName$
    dlgfile fname:=FileName$ group:="*.xlsx; *.xls";  // Open Browser to find an Excel file. Give the file name to FileName$
    impExcel fname:=FileName$ subheader:=1 lname:=1 desig:=<Unchanged> options.names.FNameToSht:=0 options.names.FNameToBk:=0;  //Import the Excel file
    wks.col1.type = 2;  // Set 1st column as Disregard since it's just GroupID
    wks.col2.type = 4;  // Set 2nd column as X

In [Main] section,  run.section(,ImportExcel); is used to call the [ImportExcel] section. See this page of how to run sections in ogs file.


Add Filter to the Worksheet

We want to define a StringArray variable GroupList to hold group IDs. We will use it to set and change filter conditions. We define it in [main] section so that all later sections can have access to it, according to variable scope.

    run.section( , ImportExcel);
    StringArray GroupList;


Get Unique Group IDs

We used the following script to get the unique groupIDs 1, 2, 3 into GroupList variable.

Here, we can take advantage of a existing X-function reducedup, with a dump Y data, since our groupID is numeric index number

    range rr = col(1);   // Set a range variable pointing to the Group column
    dataset dsX = rr;    // Setup a temporary loose dataset to hold groupIDs
    dataset dsY = data(1,1,rr.getsize());  // Setup a temporary loose dataset with value 1
    dataset dsX2, dsY2;  // Setup two extra datasets for result
    reducedup iy:=(dsX, dsY) oy:=(dsX2, dsY2) xcount:=<none>;  // Reduce row by duplicated X values
    GroupList.CopyFrom(dsX2);  // Get the clean ID list


Add Filter to 1st Column

Now we can go ahead to add the filter. Notice that although the groupID is numeric, we still want it be like categorical value.

    wks.col1.format = 2;  // Set the GroupID column to be text
    wks.col1.filter = 1;  // Add filter to the column
    wks.col1.filter$ = "x="%(GroupList.GetAt(1)$)"";  // Set filter condition to be the first group in the list
    wks.col1.filterenabled = 1;  // Enable the filter
    wks.runfilter();  // Run the filter

Add the script to execute these above two sections to [Main] section.

    run.section( , ImportExcel);
    StringArray GroupList;
    run.section( , GetGroupList);
    run.section( , AddFilter);


Create Rank Model Analysis

The idea is to keep updating the input of the existing rank model analysis and extract the result everytime, so we need to setup it first. You can use Generate Script method and no need to worry about those function index number.

    range rr = 3;     // Setup a range variable pointing to the first Y
    funcRank -r 2 data:=rr funsel.category:=Exponential funsel.funclist:={20, 21, 22} adjrsq:=1 ssr:=1 rcs:=1;  // Rank Model Analysis
    run -p au;        // Wait for it to calculate = 1;  // Go back to data sheet

Add script to execute this section to  [Main] section.

    run.section( , ImportExcel);
    StringArray GroupList;
    run.section( , GetGroupList);
    run.section( , AddFilter);
    run.section( , CreateRankModel);


Loop through Groups and Y columns

Now it’s time to build a loop to go through all Y datasets in each group.

// ... 
for (int group = 1; group <= GroupList.GetSize(); group++) {  // Loop through all groups
     // Outer Loop: Do something 1
     for (int col = 3; col <= wks.ncols; col++) {  // Loop through all Ys
         // Inner Loop: Do something2 2

Set up a new section called [UpdateFilter] and put it into the outside loop. Make two more sections [UpdateSourceCol] and [UpdateSummary] and add to the inner loop. Thus our analysis routine in [Main] section would become:

    run.section( , ImportExcel);
    StringArray GroupList;
    run.section( , GetGroupList);
    run.section( , AddFilter);
    run.section( , CreateRankModel);
    for (int group = 1; group <= GroupList.GetSize(); group++) {
        run.section( , UpdateFilter);
        for (int col = 3; col <= wks.ncols; col++) {
            run.section( , UpdateSourceCol);
            run.section( , UpdateSummary);
 window -a Summary;  // Activate Summary sheet when finished.


Update Worksheet Filter Condition

In the outside loop, we need to update the worksheet filter every time.

    wks.col2.filter$ = "x="%(GroupList.GetAt($(group))$)"";  // Change filter condition
    wks.runfilter();  // Run the filter
    run -p au;  // Wait for recalculating


Change Analysis Parameter

In the inner loop, we need to replace the input of rank model with current Y.

    run.section( , DeleteLastNLFit);  // Delete the previous NLFit, will talk about this in a sec.
    range r1 = 2!1;  // Point to a result column that have green lock on it, in order to get the analysis setting tree
    range r2 = $(col);  // Current Y
    Tree tr;
    op_change ir:=r1 tr:=tr op:=get;  // Get previous tree$ = %(r2.GetLayer()$)%($);  // Update tree node
    op_change ir:=r1 tr:=tr op:=run;  // Load the updated tree
    run -p au;  // Wait for recalculating

Since every time the Rank Model analysis updates, a new NLFit calculation sheet will be added, we need to delete the previous fitting result before doing the updating, to avoid too many result sheets being added in the workbook.

    for (int wp = page.nlayers; wp > 2; wp--) {  // Loop through all sheet with index number bigger than 2 = wp;  // Activate the worksheet
       layer -duk;  // Delete it
    }; = 1;


Create and Update Summary Sheet

First we need to create a summary sheet if it does not exist yet.

    range RawData = [%H]$(!;  // Point to the data sheet
    string DataBookname$ = %H;  // Remember current workbook's name
    newbook name:="Summary" option:=lsname;  // New a workbook called "Summary"
    wks.ncols = RawData.ncols - 1;  // Summary sheet will have one less column than in data sheet
    wks.col1.lname$ = "Group#";  // Set longname label row over
    wks.col2.lname$ = "Y1";
    wks.col3.lname$ = "Y2";
    wks.col4.lname$ = "Y3";
    GroupList.CopyTo(wcol(1));   // Assign the 1st column with group name list
    window -a %(DataBookname$);  // Go back to data sheet

Then in [UpdateSummary] section, check the existence of Summary book first, if not, call the above section.

    if (exist(Summary, 2) != 2) {  // If no Summary existing
        run.section( , CreateSummary)
    range rR2 = [%H]2!5;    // The column with adj. R-square values
    range rName = [%H]2!1;  // The column with function names
    int pos = list(max(rR2),rR2);  // Find the function with highest R^2 value
    if (pos != 0) {  // It is possible that none of the models converges
        string Name$ = rName[$(pos)]$;
        %(Summary, $(col - 1), $(group)) = "%(Name$)";  // Put function name into summary sheet
    else {
        %(Summary, $(col - 1), $(group)) = "None";    // If no luck, then put <none>

About S Z

Male, single, alive.

View all posts by S Z →

Leave a Reply

Your email address will not be published. Required fields are marked *