[R] Question about many-to-one merge

[William Dunlap]

> -----Original Message-----
> From: r-help-bounces at r-project.org 
> [mailto:r-help-bounces at r-project.org] On Behalf Of hongwei
> Sent: Wednesday, January 20, 2010 2:38 PM
> To: r-help at r-project.org
> Subject: [R] Question about many-to-one merge
> 
> 
> I have spent a whole afternoon searching for the solution, 
> but got nothing so
> far. It should be a simple problem.
> I have two datasets to merge. The first one just contains two 
> ID columns and
> it looks like:
> 
> FromID  ToID
> 1           2
> 1           3
> 2           1
> 2           3
> 3           1
> 3           2
> 
> The second one contains a ID column and a variable:
> ID      X
> 1       100
> 2       150
> 3       130
> 
> What I want is to merge the two datasets together using the 
> ToID and ID. The
> final merged dataset should look like:
> 
> FromID   ToID   X
> 1            2      150
> 1            3      130
> 2            1      100
> 2            3      130
> 3            1      100
> 3            2      150
> 
> The Merge command doesn't work well. 

It always helps to see what you did and to
know what you consider "working well".  Does
the following do what you want?

   > xy <- merge(x,y,by.x="ToID",by.y="ID")
   > xy
     ToID FromID   X
   1    1      2 100
   2    1      3 100
   3    2      1 150
   4    2      3 150
   5    3      1 130
   6    3      2 130

The numbers are all there, but if you want the
rows and columns ordered the way you showed them
then use subscripting to reorder them:
   > xy[order(xy$FromID),c("FromID", "ToID", "X")]
     FromID ToID   X
   3      1    2 150
   5      1    3 130
   1      2    1 100
   6      2    3 130
   2      3    1 100
   4      3    2 150

Use 'rownames(xy)<-...' if you want new row names.

Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com  

> I also don't want to use 
> for loop. I
> feel that there must be some subscripting or other tricks to 
> use, but I
> couldn't figure it out.
> 
> Any help is appreciated!!
> 
> -Hongwei
> 
> -- 
> View this message in context: 
> http://n4.nabble.com/Question-about-many-to-one-merge-tp101881
1p1018811.html
> Sent from the R help mailing list archive at Nabble.com.
> 
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide 
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>