[R] Question about many-to-one merge

[David Winsemius]

On Jan 20, 2010, at 5:37 PM, hongwei wrote:

>
> 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.

Please abandon the phrase "doesn't work". Even if modfied by an adverb  
it remains basically meaningless.

 > merge(x, y, by.x="ToID", by.y="ID")
   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

Works fine.

If you want it sorted and arranged the way you specify there are  
couple of rearrangements possible:

 > merge(x,y,by.x="ToID", by.y="ID")[order(x$ToID),] # re-sorts
   ToID FromID   X
3    2      1 150
5    3      1 130
1    1      2 100
6    3      2 130
2    1      3 100
4    2      3 150
 > merge(x,y,by.x="ToID", by.y="ID")[order(x$ToID), c(2,1,3)]  # swaps  
col1 and col2
   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

> 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
>
--

David Winsemius, MD
Heritage Laboratories
West Hartford, CT