| Author |
 Topic  |
|
|
Thomas.E.Walter
Germany
Posts |
 Posted - 04/03/2006 : 04:13:22 AM
|
Origin Version (Select Help-->About Origin): 7.5
Operating System: Windows XP
Hi there!
I am quite new to Origin, and Origin C. I have quite a lot of data to process: it is a list of results of about 150 000 experiments. Each three of these experiments are replicates (i.e. they concern the same gene, they are control experiments), and must be evaluated together. I have calculated a ranking score for each of these experiments, i.e. my list looks like:
Gene K, 5.4
Gene A, 26.5
Gene K, 66.4
Gene J, 5.29
Gene J, 9.63
Gene I, 12.6
Gene A, 15.3
Gene A, 22.3
Gene J, 1.23
Gene I, 16.4
Gene K, 70.6
Gene I, 11.9
etc.
Now, I want to sort this list in the following way:
- Groups of replicates have to stay together
- These groups are then sorted in accordance with the maximum score out of the three replicate experiments.
For the example above, this would give:
Gene K, 70.6
Gene K, 66.4
Gene K, 5.4
Gene A, 26.5
Gene A, 22.3
Gene A, 15.3
Gene I, 16.4
Gene I, 12.6
Gene I, 11.9
Gene J, 5.29
Gene J, 9.63
Gene J, 1.23
I really do not know how to do this; I even do not know, if it is better to use Labtalk or Origin C ...
Thomas. |
|
|
Deanna
China
Posts |
Posted - 04/03/2006 : 06:10:02 AM
|
Hi, Thomas. You certainly can sort your data with Origin C. The following function should be capable of sorting the active worksheet in the way you have stated.
void SortWks()
{
Layer lay=Project.ActiveLayer();
Worksheet wks(lay);
if (!wks)
{
out_str("The worksheet cannot be create!");
return;
}
//Sort worksheet by the group first
if (!wks.Sort(0))
{
out_str("1# Sort error!");
return;
}
int iIndexNewCol = wks.AddCol(); //Add a new column for mean storage
int iNumRows = wks.Columns(0).GetNumRows();
Column colB = wks.Columns(1);
for (int ii=0; ii<iNumRows; ii+=3)
{
double dSum, dAvg;
vector vv(colB, ii, ii+2);
vv.Sum(dSum);
dAvg = dSum/3;
wks.SetCell(ii,iIndexNewCol, dAvg);
wks.SetCell(ii+1,iIndexNewCol, dAvg);
wks.SetCell(ii+2,iIndexNewCol, dAvg);
}
if (!wks.Sort(iIndexNewCol, SORT_DESCENDING))
{
out_str("2# Sort error!");
return;
}
wks.DeleteCol(iIndexNewCol);
}
I hope this will help.
Deanna
OriginLab GZ Office |
 |
|
|
Mike Buess
USA
3037 Posts |
Posted - 04/03/2006 : 1:18:11 PM
|
Hi Thomas,
This somewhat shorter LabTalk script uses the sort object which provides multiple sort orders...
sort.wksName$=%H; // sort the active wks
%A=wks.col1.name$; // name of 1st column
%B=wks.col2.name$; // name of 2nd column
sort.c1=0; // sort all columns
sort.r1=1; // start with 1st row
sort.r2=wks.maxrows; // stop with last row
sort.cName1$=A: %A; // primary order: ascending w.r.t. 1st column
sort.cName2$=D: %B; // secondary order: descending w.r.t. 2nd column
sort.wks();
You can also use it from Origin C like this...
void SortWks2()
{
Worksheet wks = Project.ActiveLayer();
if (!wks)
return;
Dataset dd(wks,0);
using sort = LabTalk.sort;
sort.wksName$ = wks.GetPage().GetName();
sort.c1 = 0;
sort.r1 = 1;
sort.r2 = dd.GetSize();
sort.cName1$ = "A: " + wks.Columns(0).GetName();
sort.cName2$ = "D: " + wks.Columns(1).GetName();
sort.wks();
}
All of the methods suggested so far will sort the first column alphabetically (Gene A, Gene I, Gene J, Gene K) but it shouldn't be difficult to rearrange if necessary.
Mike Buess
Origin WebRing Member |
|
|
|
Thomas.E.Walter
Germany
Posts |
Posted - 04/04/2006 : 10:48:16 AM
|
Hi Deanna !
Thank you very much for your quick response. I tried it out, and it worked fine. Actually, it was a little more complicated than I told you, because we do not always have 3 replicates, but up to 9 for each gene, so finally a nested sorting in the end is helpful. However, this I could do by a slight modification. Great. Thanks!
Thomas.
P.S.: This is what it finally looked like:
void MySortWks(string columnName = "GenName", int iDefaultValColumn = 1)
{
Layer lay=Project.ActiveLayer();
Worksheet wks(lay);
if (!wks) {
out_str("The worksheet cannot be create!");
return;
}
// default values for the gene name column and the value column.
int iValCol = 1;
int iGeneCol = wks.Columns(columnName).GetIndex();
// retrieve the selected column
vector<int> v;
BOOL bOK = wks.GetSelectedColumns(v);
if(bOK && (v.GetSize() !=0) )
iValCol = v[0];
else
iValCol = iDefaultValColumn;
// Sort worksheet by the group first
if (!wks.Sort(iGeneCol, SORT_ASCENDING)) {
out_str("1# Sort error!");
return;
}
int iIndexNewCol = wks.AddCol(); //Add a new column for mean (or max) storage
int iNumRows = wks.Columns(iValCol).GetNumRows();
Column colB = wks.Columns(iValCol);
int ii = 0;
while (ii < iNumRows) {
string actualGene;
string nextGene;
wks.GetCell(ii, iGeneCol, actualGene);
int next = ii;
wks.GetCell(next, iGeneCol, nextGene);
while((actualGene == nextGene) && (next < iNumRows) ) {
// First, we have to determine the end index for this gene (or siRNA)
next++;
wks.GetCell(next, iGeneCol, nextGene);
}
double dMin, dMax;
uint iMin, iMax;
vector vv(colB, ii, next - 1);
vv.GetMinMax(dMin, dMax, &iMin, &iMax);
for (int kk = ii; kk < next; kk++) {
wks.SetCell(kk,iIndexNewCol, dMax);
}
ii = next;
}
// Nested sorting
vector<int> vCol = {0, 1};
vCol[0] = iIndexNewCol;
vCol[1] = iValCol;
vector<int> vSort = {SORT_DESCENDING, SORT_DESCENDING};
if (!wks.Sort(vCol, vSort)) {
out_str("2# Sort error!");
return;
}
wks.DeleteCol(iIndexNewCol);
} |
|
|
|
Thomas.E.Walter
Germany
Posts |
Posted - 04/04/2006 : 10:52:33 AM
|
Hi Mike!
Thank you very much for your answer. Finally I used Deanna's code, because I am more familiar with C++ than with Labtalk. The problem has not been nested sorting however; it was more, that I wanted certain groups in my list (all experiments concerning the same gene, actually) not being separated by the sorting.
Thank you anyway!
Thomas. |
|
|
| |
Topic |
|
|
|
Advanced Sorting