| Author |
 Topic  |
|
|
dgillespie
United Kingdom
5 Posts |
 Posted - 04/03/2017 : 11:03:39 AM
|
Hi,
I have the following list of values
What I need to do is calculate the maximum value of each successive 10 values in column A and then output this to a second column or new worksheet.
i.e. the output would look like
max of Col(A) row 1-10
max of Col(A) row 11-20
max of Col(A) row 21-30
max of Col(A) row 31-40
etc.
I've been trying to write function to do this but I'm having problems. All of the lines seem to work when typed in individually, but when I attempt to run the function it fails and the only error code I get is "Command Error!"
What I have so far is the following
function dataset foo(int i,int t)
{
for(int i=1;i<=t)
{
range rr=[Book1]Sheet1!col(A)[i:i+9];
//set range - 10 rows
double stat = max(rr);
//get maximum value within set range
stat_out[i2] = stat
i2 = i2+1
i = i+10
}
[Book1]Sheet2!col(A) = stat_out
//put array of data into new worksheet
};
newsheet;
dataset stat_out;
i2 = 1;
foo(1,100);
//set function to run over 100 rows
Thanks in advance for any help 
|
|
|
Hideo Fujii
USA
1582 Posts |
Posted - 04/03/2017 : 6:15:04 PM
|
Hi dgillespie,
Although it is a different approach from your original, it is more natural way in Origin to have a group variable in each block;
then you can unstack, and make statistics using corresponding x-functions. How about the following snippet?Loop(ii,1,wks.maxRows) {col(E)[ii]=int((ii-1)/10)+1;} //Add group# 1,2,... with every 10 rows in col(E)
wunstackcol -r 2 irng1:=1!A irng2:=1!E; //unstack col(A) with col(E) as the group ID
nc=wks.ncols; //number of unstacked cols
Loop(ii,1,nc) {
range rs = 2!wcol(ii); //input col
range rx = 2!wcol(nc+ii); //output col
colstats irng:=rs col:=<optional> mean:=<optional> sd:=<optional> n:=<optional> median:=<optional> max:=rx; //output max in new cols
}Hope this helps.
--Hideo Fujii
OriginLab |
Edited by - Hideo Fujii on 04/03/2017 6:15:43 PM |
 |
|
|
dgillespie
United Kingdom
5 Posts |
Posted - 04/13/2017 : 09:06:21 AM
|
Hi Hideo,
Thank you very much for the advice, it seems to work. One follow up question, having unstacked the column into a new worksheet I have modified the code in order to try to get the maximum value of each column and then put the result of that into a single column. i.e. max of column 1 into column x row 1, max of column 2 in column x row 2 and so on. It seems to work, however when it seems to delete/remove values as it goes through the worksheet, I only end up with a value in the final cell.
Loop(ii,1,wks.maxRows) {col(E)[ii]=int((ii-1)/10)+1;} //Add group# 1,2,... with every 10 rows in col(E)
wunstackcol -r 2 irng1:=1!A irng2:=1!E; //unstack col(A) with col(E) as the group ID
nc=wks.ncols; //number of unstacked cols
Loop(ii,1,nc) {
range rs = 2!wcol(ii); //input col
range rx = 2!wcol(E)[ii]; //output cell
colstats irng:=rs col:=<optional> mean:=<optional> sd:=<optional> n:=<optional> median:=<optional> max:=rx; //output max in new cols
}
Thanks,
Dave |
|
|
|
Hideo Fujii
USA
1582 Posts |
Posted - 04/17/2017 : 11:15:31 AM
|
Hi Dave,
Once you unstack the original data to generate columns for groups in a new sheet, you can simply run colstats
x-function which can takes multiple columns (=datasets) as input (1:%(nc) in the sample code below), and
output to a column (wcol(nc+1) in the sample code below). Thus, no need of looping there.
/////////////////////////////////////////////
gs=10; //group size
ng=wks.ncols+1; //group column#
Loop(ii,1,wks.maxRows) {wcol(ng)[ii]=int((ii-1)/gs)+1;} //Add group# 1,2,... with every 10 rows in col(E)
wunstackcol -r 2 irng1:=1!1 irng2:=1!%(ng); //unstack col(A) with col(ng) as the group ID
nc=wks.ncols; //number of unstacked cols
colstats -r 2 irng:=1:%(nc) col:=<optional> mean:=<optional> sd:=<optional> n:=<optional> median:=<optional> max:=wcol(nc+1);
/////////////////////////////////////////////So, this is a good example to learn Origin which handles the data chiefly based on a dataset (a.k.a. range, column,
vector) rather than cell-based. Your life would be easier with datasets.
I hope this helps.
--Hideo Fujii
OriginLab
P.S. I have improved the code a little bit to accept any number of input columns (plus one) by having a variable ng. |
Edited by - Hideo Fujii on 04/17/2017 11:46:42 AM |
|
|
|
dgillespie
United Kingdom
5 Posts |
Posted - 04/26/2017 : 09:48:25 AM
|
Hi Hideo,
That seems to be working quite well, but I'm still having problems calculating the maximum value.
If i use the code then I get a column of values which are all equal to the maximum value across all columns I ran the stats on. i.e. for 140 columns I get a column with 140 rows, all of which are the same value.
If I use the "descriptive statistics" from the menu bar then it seems to return the correct output. i.e. max of column 1 in row 1, max of column 2 in row 2 etc.
Do you have any idea what I'm doing wrong?
Thanks,
Dave |
|
|
|
Hideo Fujii
USA
1582 Posts |
Posted - 04/27/2017 : 5:38:01 PM
|
Hi Dave,
Sorry, it is my mistake... the input range irng:=1:%(nc) takes every "bunch" of columns, not every column. The following code using Max function should work://///////////////////////////////////////////
gs=10; //group size
ng=wks.ncols+1; //group column#
Loop(ii,1,wks.maxRows) {wcol(ng)[ii]=int((ii-1)/gs)+1;} //Add group# 1,2,... with every 10 rows in col(E)
wunstackcol -r 2 irng1:=1!1 irng2:=1!%(ng); //unstack col(A) with col(ng) as the group ID
nc=wks.ncols; //number of unstacked cols
Loop(ii,1,nc) {wcol(nc+1)[ii]=max(wcol(ii))};
/////////////////////////////////////////////Could you please try this?
Anyway, let me consider a bit further if there is a smarter way...
--Hideo Fujii
OriginLab |
|
|
|
Hideo Fujii
USA
1582 Posts |
Posted - 04/27/2017 : 5:50:38 PM
|
Hi Dave,
My coworker, Snow gave a good suggestion! The following x-function can do everything at once!wreducerows irng:=col(1) method:=merge nrows:=10 merge:=max orng:=<new>; I hope you like this.
--Hideo Fujii
OriginLab |
|
|
| |
Topic |
|
|
|
data manipulation and extraction