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 、 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.
[ImportExcel]
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.
[Main]
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
[GetGroupList]
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.
[AddFilter]
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.
[Main]
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.
[CreateRankModel]
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
page.active = 1; // Go back to data sheet
Add script to execute this section to [Main] section.
[Main]
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.
[Main]
// ...
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] 、 [UpdateSummary] and add to the inner loop. Thus our analysis routine in [Main] section would become:
[main]
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.
[UpdateFilter]
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.
[UpdateSourceCol]
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
tr.xfGetN.data.Range1.Y$ = %(r2.GetLayer()$)%(r2.name$); // 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.
[DeleteLastNLFit]
for (int wp = page.nlayers; wp > 2; wp--) { // Loop through all sheet with index number bigger than 2
page.active = wp; // Activate the worksheet
layer -duk; // Delete it
};
page.active = 1;
Create and Update Summary Sheet
First we need to create a summary sheet if it does not exist yet.
[CreateSummary]
range RawData = [%H]$(page.active)!; // 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.
[UpdateSummary]
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>
};
Japanese 
English