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. 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-tp1018811p1018811.html Sent from the R help mailing list archive at Nabble.com.
> -----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-tp1018811p1018811.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. >
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