| Author |
 Topic  |
|
|
Mekis90
Germany
8 Posts |
 Posted - 12/04/2013 : 11:01:32 AM
|
Origin Ver. and Service Release (Select Help-->About Origin):
Operating System:
Hi,
I have given a table with some columns and some rows. In the last column is the number of the Cluster Membership (for example 1, 2 or 3). Now I want to separate this Clusters in a new worksheet. That means that all rows with the Clusternumber 1 are one below the other. Between the different Clusters should be 2 free rows. I also want the average value of every column in the upper row.
I tried it with: range kCC= [kleine]Cluster_Membership1!col(10);
if (kCC= 1) copy -b row(i) Cluster_Membership1 Sheet1! -b 1 ;
but it doesnt´t work.
I hope somebody can help me because I´m a beginner in LabTalk
Cheers,
Mekis |
|
|
greg
USA
1378 Posts |
Posted - 12/05/2013 : 11:07:35 AM
|
It isn't clear what you are trying to do and I do not understand what "average value of every column in the upper row" means.
Have you tried unstacking this data?
range raD = (1:9);
range raCL = 10;
wunstackcol -r 2 irng1:=raD irng2:=raCL; |
 |
|
|
Mekis90
Germany
8 Posts |
Posted - 12/10/2013 : 11:26:05 AM
|
Hi greg,
thanks for your answer.
Sorry for my bad problem definition.
Maybe I write a little example for better understanding:
Name | Age | Cluster
Louis | 23 | 1
Sue | 15 | 2
Amy | 45 | 2
John | 75 | 1
This is my given table (just an example). Now I want to copy all rows in an other worksheet and sort the rows after the Number of the Cluster. After all rows with Cluster "1" I want to insert some emtpy rows, following by the next group of rows (Cluster 2). After that I want to calculate the average value of some columns (for example for the Age) in the empty rows.
I have to automate this procedure, because I have to analyse a lot of tables.
In addition I have another question. Is it possible to link the Origin C- Code in the code-builder with the Custom Routine button? My plan is that the whole procedure will be run automatically after pressing the Custom Routine button. I only did it with Labtalk functions, but I need Origin-C functions, too.
I hope my problem is better to understand now.
Cheers,
Markus
|
|
|
|
greg
USA
1378 Posts |
Posted - 12/10/2013 : 2:19:47 PM
|
Not sure what averages you wanted so I assumed average of cluster.
A little quicker to use LabTalk which would look something like this (run while your data sheet is active):
// BEGIN SCRIPT
// Duplicate the worksheet data
wcopy iw:=<active> ow:=<new name:=Sorted> copydata:=1 script:=0 keep:=0;
// Sort by Cluster
wsort bycol:=3;
// Point to Age and Cluster columns
range raCl = 3;
range raAge = 2;
// Get first cluster value
Cluster = raCL[1];
// Now loop over all the data
for ( done = 0, row = 2, start = 1 ; done == 0 ; row++)
{
// What cluster is next?
nxtCl = raCL[row];
if( nxtCl != 0/0 )
{ // Compare last cluster value to this one
if(Cluster != nxtCl)
{ // Cluste value has changed
// Calculate average age
stats raAge[start:row-1];
// Insert a row
wo -s 0 row 0 row;
domenu 36441;
// Store the average
raAge[row] = stats.mean;
Cluster = nxtCl;
row++;
start = row;
}
}
else // This was the last cluster
{
// Calculate average age
stats raAge[start:row-1];
// Store the average
raAge[row] = stats.mean;
done = 1; // so we exit 'for' loop
}
}
wo -s;
// END SCRIPT
You can use the run.loadoc( ) method to compile your C code so all its functions are ready to use. Put your source code in your User Files Folder Origin C folder (otherwise you will need a relative or absolute path to the code):
// BEGIN SCRIPT
if(run.loadoc(my_code.c))
ty Problem compiling source code;
else
{
ty Put your script here. Functions in C code are ready;
}// END SCRIPT
|
|
|
|
Mekis90
Germany
8 Posts |
Posted - 12/11/2013 : 04:45:38 AM
|
Wow thank´s for your big answer. It helps me a lot!!!
But the script doesn´t work completely. After copying and sorting the rows, Origin doesn´t give back a response. I must close Origin and restart it. Is it possible that the script loops endlessly???
Can you also explain me please the meaning of the first and second expression in the for loop (done = 0, row = 2, start = 1 ; done == 0).
Sorry about that. I´m realy new in Origin 
Cheers,
Markus |
|
|
|
greg
USA
1378 Posts |
Posted - 12/11/2013 : 09:08:06 AM
|
Which version of Origin do you have?
It's quite possible you are trapped in an endless loop, because that's exactly what I set up ...
The for loop is initialized with:
done = 0
row = 2
start = 1
The completion check is:
done == 0
which is true, and since there is no post-loop expression the loop does become infinite, except that when no more cluster values are being read the code should execute:
done = 1;
which will complete the loop.
It appears your version may not execute that. If I know the version, I can check it there. |
Edited by - greg on 12/11/2013 09:09:09 AM |
|
|
|
Mekis90
Germany
8 Posts |
Posted - 12/11/2013 : 09:18:51 AM
|
| I have version OriginPro 8.6G 32Bit. |
|
|
|
greg
USA
1378 Posts |
Posted - 12/11/2013 : 2:18:14 PM
|
Version 8.6 does have an odd bug.
When assigning to a variable by reading beyond the last row in a column, version 8.6 (and version 9.0 as well) do not change the value of the variable to missing.
The code can be re-worked to accomodate the bug:
// BEGIN SCRIPT
// Duplicate the worksheet data
wcopy iw:=<active> ow:=<new name:=Sorted> copydata:=1 script:=0 keep:=0;
// Sort by Cluster
wsort bycol:=3;
// Point to Age and Cluster columns
range raCl = 3;
range raAge = 2;
// Get first cluster value
Cluster = raCL[1];
// Now loop over all the data
for ( done = 0, row = 2, start = 1 ; done == 0 ; row++)
{
// What cluster is next?
nxtCl = raCL[row];
{ // Compare last cluster value to this one
if(Cluster != nxtCl)
{ // Cluster value has changed
// Calculate average age
stats raAge[start:row-1];
// Insert a row
wo -s 0 row 0 row;
domenu 36441;
// Store the average
raAge[row] = stats.mean;
Cluster = nxtCl;
row++;
start = row;
}
}
if(row == raAge.GetSize()) // We are done at this point
{
// Calculate average age
stats raAge[start:row];
// Store the average;
row++;
raAge[row] = stats.mean;
done = 1; // so we exit 'for' loop
}
}
wo -s;
// END SCRIPT |
|
|
|
Mekis90
Germany
8 Posts |
Posted - 01/06/2014 : 02:48:40 AM
|
Now it runs perfect, thank you!!!
I have one question left. I have to calculate the average for more than just one column.
Name | Age | Height in m | Weight in kg | Cluster
Louis | 23 | 1,83 |80 |1
Sue | 15 | 1,58 |50 |2
Amy | 45 | 1,65 |59 |2
John | 75 | 1,90 |92 |1
In the example I also want to calculate the average of Height and Weight.
I tried it with defining some ranges:
The code can be re-worked to accomodate the bug:
// BEGIN SCRIPT
// Duplicate the worksheet data
wcopy iw:=<active> ow:=<new name:=Sorted> copydata:=1 script:=0 keep:=0;
// Sort by Cluster
wsort bycol:=3;
// Point to Age and Cluster columns
range raCl = 5;
range raAge = 2;
range raHeight=3;
range raWeight=4;
// Get first cluster value
Cluster = raCL[1];
// Now loop over all the data
for ( done = 0, row = 2, start = 1 ; done == 0 ; row++)
{
// What cluster is next?
nxtCl = raCL[row];
{ // Compare last cluster value to this one
if(Cluster != nxtCl)
{ // Cluster value has changed
// Calculate average age
stats raAge[start:row-1];
stats raHeight[start:row-1];
stats raWeight[start:row-1];
// Insert a row
wo -s 0 row 0 row;
domenu 36441;
// Store the average
raAge[row] = stats.mean;
raHeight[row] = stats.mean;
raWeight[row] = stats.mean;
Cluster = nxtCl;
row++;
start = row;
}
}
if(row == raAge.GetSize()) // We are done at this point
{
// Calculate average age
stats raAge[start:row];
stats raHeight[start:row];
stats raWeight[start:row];
// Store the average;
row++;
raAge[row] = stats.mean;
raHeight[row] = stats.mean;
raWeight[row] = stats.mean;
done = 1; // so we exit 'for' loop
}
}
wo -s;
// END SCRIPT
It shows me in each column the average of the last column (Weight).
How can I fix the problem, that under every column is the right average???
Thanks in advance!
Mekis |
|
|
|
greg
USA
1378 Posts |
Posted - 01/06/2014 : 11:12:10 AM
|
The stats X-Function keeps its results in a tree which always contains the latest results.
When you execute stats three times in a row, the stats tree will only contain that last values, so you you need to remember them:
stats raAge[start:row-1];
AgeMean = stats.mean;
stats raHeight[start:row-1];
HeightMean = stats.mean;
stats raWeight[start:row-1];
...
...
raAge[row] = agemean;
raHeight[row] = heightmean;
raWeight[row] = stats.mean;
Those changes are needed in two places.
Also, be careful to sort by cluster:
wsort bycol:=5;
Note that I have not used any Origin C so maybe this is more appropriate to our LabTalk Forum:
http://www.originlab.com/forum/forum.asp?FORUM_ID=10 |
|
|
| |
Topic |
|
Copying rows in new worksheet