| Author |
 Topic  |
|
|
aplotnikov
Germany
169 Posts |
 Posted - 02/15/2017 : 11:50:45 AM
|
Origin Ver. and Service Release (Select Help-->About Origin): 8.6Pro 64bit SR3 b99
Operating System: Win7
I would like to merge two tables by common column like database "join" operation
SELECT t1.col1, t2.col2, t2.col3 FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.col1=t2.col1
or R-function merge() in {base}
merge(t1,t2,by="col1",all.x=TRUE)
where t1,t2 – data frames ("tables") with the first column named "col1"
Thus, the columns 2, 3, etc. of the table t1 should contain data from the corresponding columns of the table t2 only in rows, where t1.col1-values occur in the column 1 of the table t2. The rest of t1-rows should be empty or NA (except the col1, of course).
A trivial (but surely not optimal) LT solution:
range rgT1=[Table1]1!1;
range rgT2=[Table2]1!1;
nc=[Table2]1!wks.ncols;
StringArray saTmp;
saTmp.CopyFrom(rgT2);
loop(ii,1,rgT1.GetSize()) {
nn=saTmp.Find(rgT1[ii]$);
if (nn>0) wrcopy iw:=[Table2]1! ow:=[Table2]1! r1:=nn r2:=nn c1:=2 c2:=nc dc1:=3 dr1:=ii format:=1;
}
I would appreciate for any suggestion how to implement the same procedure in OriginC in an optimal way (e.g., avoiding an explicit loop). Supposedly Worksheet::SelectRows() can be employed for this purpose.
Thanks in advance,
Alexei |
Edited by - aplotnikov on 02/15/2017 11:55:30 AM |
|
|
cpyang
USA
1406 Posts |
Posted - 02/15/2017 : 8:43:40 PM
|
Origin C code can certainly be written and will be faster then LabTalk since using x-function wcopy will be slow, but it will loop in Origin C nevertheless.
Best will be that we extend the Query mechanism that was developed for sheet and book and graph to allow the data query, and we can try to get this into the upcoming SR1 release.
CP
|
 |
|
|
aplotnikov
Germany
169 Posts |
Posted - 02/16/2017 : 03:45:36 AM
|
Thank you for your reply.
Unfortunately any modifications of SQL queries could not be helpful in this particular case: I try to get rid of join operation in the SQL query due to extremely long response time if join is employed to collect data from different servers (_not_ simply different tables on the same server). It is much faster do divide the query and collect the data from each server separately with subsequent merging in Origin.
So, now I can obtain row indices for both tables using SelectRows():
vector<uint> vnRI1, vnRI2;
vector<uint> vnCI={1,2};
Worksheet wks1("DT1");
Worksheet wks2("DT2");
string strCond="saTmp.Find(rgT2[i]$)>0";
string strPre="range rgT1=[DT1]1!1;range rgT2=[DT2]1!1;StringArray saTmp;saTmp.CopyFrom(rgT1)";
int nn2=wks2.SelectRows(strCond, vnRI2, 0, -1, -1, strPre);
strCond="saTmp.Find(rgT1[i]$)>0";
strPre="range rgT1=[DT1]1!1;range rgT2=[DT2]1!1;StringArray saTmp;saTmp.CopyFrom(rgT2)";
int nn1=wks1.SelectRows(strCond, vnRI1, 0, -1, -1, strPre);
I can also use Extract() to get selected rows from DT2. Can I put them into corresponding (selected) rows of DT1 without explicit loop? Both tables are sorted by common column filled by unique values (no duplicates).
Thank you in advance,
Alexei |
|
|
|
minimax
355 Posts |
Posted - 02/16/2017 : 05:45:42 AM
|
Hi Alexei,
In the latest Origin version (2017 SR0), there is a new OC method Worksheet:JoinWorksheet(), which may be suitable to your case, see following example.
#include <origin.h>
#include <Array.h>
void JoinWorksheet_ex()
{
string str1 = "[Table1]1!";
Worksheet wksSrc1(str1);
vector<uint> vnSrc1ColIndices = {0, 1};
string str2 = "[Table2]1!";
Worksheet wksSrc2(str2);
vector<uint> vnSrc2ColIndices;
vnSrc2ColIndices.Data(1, wksSrc2.GetNumCols()-1);
Array<JoinWksInfo&> arrJoinWksInfo(true);
JoinWksInfo *pWksInfo1 = new JoinWksInfo;
pWksInfo1->wks = wksSrc1;
pWksInfo1->dwCntrl = 0;
pWksInfo1->vJoinRowcolIndices = vnSrc1ColIndices;
arrJoinWksInfo.Add(*pWksInfo1);
JoinWksInfo *pWksInfo2 = new JoinWksInfo;
pWksInfo2->wks = wksSrc2;
pWksInfo2->dwCntrl = JWIOPT_MATCH_ROWCOL_DROP_MULTIPLES | JWIOPT_MATCH_ROWCOL_DROP_MISMATCHES;
pWksInfo2->vJoinRowcolIndices = vnSrc2ColIndices;
arrJoinWksInfo.Add(*pWksInfo2);
Array<JoinWksCond&> arrJoinWksCond(true);
JoinWksCond *pCond = new JoinWksCond;
Array<JoinWksRowcol&> *parrRowcols = &(pCond->arrRowcol);
parrRowcols->SetAsOwner(true);
for(int ii = 0; ii < 2; ii++)
{
JoinWksRowcol *pWksRowcol = new JoinWksRowcol;
pWksRowcol->wks = ii == 0? wksSrc1 : wksSrc2;
pWksRowcol->nIndex = 0;
pWksRowcol->bLabel = false;
parrRowcols->Add(*pWksRowcol);
}
arrJoinWksCond.Add(*pCond);
DWORD dwCntrl = JWOPT_DIRECTION_HORIZONTAL | JWOPT_SKIP_HIDDEN_ROWCOLS | JWOPT_OUTPUT_KEEP_SRC_WKS_ORDER;
int nRet = wksSrc1.JoinWorksheet(&arrJoinWksInfo, &arrJoinWksCond, dwCntrl);
out_int("nRet=", nRet);
}
You may download the demo to take a try?
PS1: The method is lack of documentation, we will try to improve it in the coming SR1.
PS2: Worksheet::SelectRows() is probably not applicable to your case.
It loops overs row index, that is to say, it can only compare the data in a same row of different sheets.
PS3: I suppose there is typo in your script?
ow:=[Table2]1!
should be
ow:=[Table1]1! |
|
|
|
aplotnikov
Germany
169 Posts |
Posted - 02/16/2017 : 09:47:08 AM
|
Hi minimax,
thanks for your efforts.
Unfortunately we are not going to buy the latest version of Origin in the nearest future (at least, until the screen representation of graphs containing raster images will be significantly improved) and I have to solve the problem using the available one. Nevertheless it was really interesting to learn a new feature of the latest version - I will keep it in mind.
quote:
Worksheet::SelectRows() is probably not applicable to your case
Hmmm... It provides row indices - I can use them (theoretically) in a loop to copy data row by row:
int iRet;
DWORD dwCtrl = CPYT_COPY_COLUMN_FORMAT | CPYT_COPY_COLUMN_DESIGNATIONS;
for (ii=0;ii<nn1;ii++) {
iRet=wks2.CopyTo(wks1, 1, 2, vnRI2[ii], vnRI2[ii], 3, -1, dwCtrl, vnRI1[ii]);
}
There is just a small obstacle - this code does not work. I would appreciate for any suggest how to get it working.
quote:
I suppose there is typo in your script?
Yes, it is. Sorry for this mistype.
Kind regards,
Alexei |
|
|
|
minimax
355 Posts |
Posted - 02/17/2017 : 03:58:05 AM
|
The argument for CopyTo() might be a bit tricky and you may need to read more.
nC2
[input] ending column to copy, -1 to copy all columns (Inclusive)
nR2
[input] ending data row, -1 to last row (Exclusive)
nR2 is exclusive.
Therefore, following change is needed.
iRet=wks2.CopyTo(wks1, 1, 2, vnRI2[ii], vnRI2[ii]+1, 3, -1, dwCtrl, vnRI1[ii]);
Most important, as you mentioned at the beginning, looping in OC (or LT) is not efficient.
PS:
As to
quote:
the screen representation of graphs containing raster images will be significantly improved
Would you mind to show more details?
We are not aware of what issue you mean.
It would be appreciated you can send us some sample opj/screenshot to show the problem. |
|
|
|
aplotnikov
Germany
169 Posts |
Posted - 02/17/2017 : 05:13:55 AM
|
Hi minimax,
thank you very much for your helpful hint! I am sorry for my inattention.
I know, that explicit loops are less efficient in comparison to well optimized object methods - that was the reason of my question (or one of the reasons). However it works fast enough - as I already tested - in this particular case. Thanks once again.
quote:
It would be appreciated you can send us some sample opj/screenshot to show the problem.
I have already sent the screenshot with explanations to Easwar. Should I send you a copy?
Regards,
Alexei |
|
|
|
easwar
USA
1965 Posts |
Posted - 02/17/2017 : 11:31:46 AM
|
Hi Alexei,
We received your reply and attachment regarding your resolution comment. Will share with the team here.
Best regards,
Easwar |
|
|
|
snowli
USA
1414 Posts |
Posted - 02/21/2017 : 12:00:18 PM
|
Hello,
We have improved the screen representation of raster images in graph in internal build and the improvements will be in the coming Origin 2017 SR1.
Thanks, Snow
|
|
|
| |
Topic |
|
|
|
Merge two tables by common column