| Author |
 Topic  |
|
|
kevincklapf
Germany
3 Posts |
 Posted - 01/18/2022 : 09:47:17 AM
|
Hello everybody!
I am totally new to the programming world of OriginPro, which is why I hope to find some help in this LabTalk Forum. Even though this might take some time to read, here's the issue:
I have a worksheet with 9 columns and 93.036 rows (rows =i). In order to reduce this data to about 1.000 rows, I want to write a script that extracts certain rows into new columns, depending on wether or not my given conditions are true or false. The conditions depend on 2 columns: the values of one column (let's call it A) go from 0 to 1,0037552E+07, the values of the other column (B) from 0 to 2842,6759. For every of the 93.036 rows there's one value for each column. Here's what I want to achieve with my script:
"Whenever the value in Column A is a multiple of 10.000, respectively the first value to be bigger than a multiple of 10.000, extract all the values from this row (i) into 9 NEW columns. BUT whenever the value of Column B reaches 5 or a multiple of 5, then extract this row instead and reset the counter for the 10.000 steps from column A to 0."
To make it a little bit clearer, here's an example:
Let's say we are in row 500 of our Worksheet(i=500). Therefore, our row "counter" i is set to 500 at the moment. The belonging value in Col(A) is 9999.5, the value in Col(B) is 4.5. The script should not extract any data now, since value(Col(A)[i])!>=n*10000 and value(Col(B)[i])!>m*5. So it needs to jump into the next row (i++). Now i is set to 501, which again, is the row that we are currently in. Here, the value for col(A) is 10.010 and the value for Col(B) is 4.7. Now the script should be able to extract all the values of the 9 given columns in this row into 9 NEW columns, since value(Col(A)[I])>=n*10000. In this case, n was set to 1, since it was the first interval for col(A). Now n needs to go from 1 to 2 (n++). The next time the script should extract a row over the 9 columns, is when either col(A) reaches a value of 20.000 (or the first value bigger than 20.000) OR when the value of col(B) reaches 5 or the first value bigger than 5. Another Example: Let's now say we are in line 2000 (i=2000). The value of col(A) in this row is 16.000, the belonging value of Col(B) is 5.05. In this case, it's the first time for the value of Col(B) to be 5 or bigger (value(Col(B)[i])=>m*5). So the script needs to extract all values over the 9 columns in this row once again, even if the value in col(A) has not reached 20.000 yet. The "counter" for col(A) must now be reset, so that the script doesn't extract the values for value(col(A))=20.000, but for 26.000, because we need to now count 10.000 from the value of col(A) in line 2000, which was 16.000. If the value in col(B) reaches 10 (2*5, next multiple of 5 after m++) earlier than col(A) reaches 26.000, it's the same procedure again.
I know this is kind of a tricky topic to discuss on a forum, but since I have no clue how I could possibly program this for Origin, I really hope that there's someone who could help me solve this problem. My main issue is that I'm lacking knowledge on Origin specified commands with loops and general operators, so even if I know the structure behind the program and how it should process each step, there's no chance I could possibly bring this into Origin, but maybe someone can help me out with that. For everyone who made it this far into my post, thank you and I hope to hear from you! 
Best regards
Origin Ver. and Service Release (Select Help-->About Origin):
Operating System:
Klapf |
|
|
YimingChen
1664 Posts |
Posted - 01/18/2022 : 12:22:16 PM
|
Hello Klapf,
Please check the attached project file. In Set Column Value dialog of col(C), please check the Labtalk script. The Labtalk function sets the col(C) value to 1 if the row is going to be extracted. Let me know if you have any questions.
function dataset queryIndex(dataset dsA, dataset dsB)
{
dataset dsResult;
dsResult.SetSize(dsA.GetSize());
double stepA = 10000;
double stepB = 5;
double compareA = stepA;
double compareB = stepB;
for (int i = 1; i <= dsA.GetSize(); i++) {
if (dsA[i] < compareA && dsB[i] < compareB)
{
dsResult[i] = 0;
continue;
}
if (dsA[i] >= compareA)
{
compareA = compareA + stepA;
}
if (dsB[i] >= compareB)
{
compareA = dsA[i] + stepA;
compareB = compareB + stepB;
}
dsResult[i] = 1;
}
return dsResult;
}
https://my.originlab.com/ftp/forum_and_kbase/Images/LabtalkFunction.opju
James |
 |
|
|
kevincklapf
Germany
3 Posts |
Posted - 01/19/2022 : 07:52:40 AM
|
Hello James!
Thank you so much for your response. Unfortunately I can‘t open the project file.
As you might have read in my opening question, I am totally new to Origin programming, so I might have to go through this function step by step:
First of all: in the ‚function dataset…‘ row, do I have to change ‚dataset dsA‘ to ‚dataset Col(A)‘ if that‘s my column in the affected worksheet I‘m working in? (Same goes for dsB of course) If that‘s the case I would logically change all dsA variables in the function to Col(A). Do I also have to make any adjustments to ‚dsResult‘?
Also interesting to know would be (I‘m sorry if that‘s something that I should already know ) how I can run this function within my worksheet? Is it by right clicking on a new column and selecting ‚calculate column value‘? Or do I first have to paste the function to the code builder, compile it, then save it and go from there to importing it as an userdefined function for the new column?
Still in the learning process, hope you can once again help me out
quote:
Originally posted by YimingChen
Hello Klapf,
Please check the attached project file. In Set Column Value dialog of col(C), please check the Labtalk script. The Labtalk function sets the col(C) value to 1 if the row is going to be extracted. Let me know if you have any questions.
function dataset queryIndex(dataset dsA, dataset dsB)
{
dataset dsResult;
dsResult.SetSize(dsA.GetSize());
double stepA = 10000;
double stepB = 5;
double compareA = stepA;
double compareB = stepB;
for (int i = 1; i <= dsA.GetSize(); i++) {
if (dsA[i] < compareA && dsB[i] < compareB)
{
dsResult[i] = 0;
continue;
}
if (dsA[i] >= compareA)
{
compareA = compareA + stepA;
}
if (dsB[i] >= compareB)
{
compareA = dsA[i] + stepA;
compareB = compareB + stepB;
}
dsResult[i] = 1;
}
return dsResult;
}
https://my.originlab.com/ftp/forum_and_kbase/Images/LabtalkFunction.opju
James
Klapf |
|
|
|
YimingChen
1664 Posts |
Posted - 01/19/2022 : 08:46:16 AM
|
You can't open the opju file probably because you are running an old version of Origin. I would suggest you upgrade your Origin. I saved the project to an old format and attach here and you can take a look.
https://my.originlab.com/ftp/forum_and_kbase/Images/LabtalkFunction.opj
This workbook contains three columns, col(C) calculates the rows to be extracted (indicated by 1) based on the condition on col(A) and col(B). You can highlight col(C) and select menu Column->Set Column Values... to open the dialog. I define the queryIndex() function then call the function to set values of col(C).
Extract the rows to a new workbook: Select from menu Worksheet->Worksheet Query..., enter the condition C = 1. And check the radio Extract to new worksheet. And click OK.
James |
Edited by - YimingChen on 01/19/2022 08:57:19 AM |
|
|
|
kevincklapf
Germany
3 Posts |
Posted - 02/16/2022 : 2:48:53 PM
|
Hey James! I know it's been a while, but I didn't forget you! I'm only here because I wanted to say thank you for your help. I just finished the project that included the script that you helped me out with, and I think I wouldn't have been able to do it without your response (or at least it would've taken quite a while longer to figure things out  )
When it comes to conditional data reduction, I guess you made me a bit smarter 
Best regards
Kevin
quote:
Originally posted by YimingChen
You can't open the opju file probably because you are running an old version of Origin. I would suggest you upgrade your Origin. I saved the project to an old format and attach here and you can take a look.
https://my.originlab.com/ftp/forum_and_kbase/Images/LabtalkFunction.opj
This workbook contains three columns, col(C) calculates the rows to be extracted (indicated by 1) based on the condition on col(A) and col(B). You can highlight col(C) and select menu Column->Set Column Values... to open the dialog. I define the queryIndex() function then call the function to set values of col(C).
Extract the rows to a new workbook: Select from menu Worksheet->Worksheet Query..., enter the condition C = 1. And check the radio Extract to new worksheet. And click OK.
James
Klapf |
|
|
| |
Topic |
|
|
|
Conditional Data Extraction into new Columns