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

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.

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.


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


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.

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


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.

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


Loop through Groups and Y columns

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

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:


Update Worksheet Filter Condition

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


Change Analysis Parameter

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

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.


Create and Update Summary Sheet

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

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

Leave a Reply