CODEWITHSUNDEEP
rmerge
LoveMeow
LoveMeow

Reputation: 1181

Merging data frames with a non-unique column

I would like to create a new data frame that borrows an ID variable from another data frame. The data frame I would like to merge has repeated observations in the ID column which is causing me some problems. 

DF1<-data.frame(ID1=rep(c("A","B", "C", "D", "E") , 2), X1=rnorm(10))
DF2<-data.frame(ID1=c("A", "B", "C", "D", "E"), ID2=c("V","W","X","Y" ,"Z"), X2=rnorm(5),     X3=rnorm(5))

What I would like to append DF2$ID2 onto DF by the ID1 column. My goal is something that looks like this (I do not want DF2$X2 and DF$X3 in the 'Goal' data frame): 

Goal<-data.frame(ID2=DF2$ID2, DF1)

I have tried merge but it complains because DF1$ID1 is not unique. I know R can goggle this up in 1 line of code but I can't seem to make the functions I know work. Any help would be greatly appreciated!

Upvotes: 4

Views: 9036

Answers (2)

akrun
akrun

Reputation: 886948

You could also use left_join from library(dplyr)

 library(dplyr)
 left_join(DF1, DF2[,c("ID1", "ID2")])
 #    ID1          X1 ID2
 #1    A -1.20927237   V
 #2    B -0.03003128   W
 #3    C -0.75799708   X
 #4    D  0.53946986   Y
 #5    E -0.52009921   Z
 #6    A  1.15822659   V
 #7    B -0.91976194   W
 #8    C  0.74620142   X
 #9    D -2.46452560   Y
#10   E  0.80015219   Z

Upvotes: 1

MrFlick
MrFlick

Reputation: 206187

There should be no problem with a simple merge. Using your sample data

merge(DF1, DF2[,c("ID1","ID2")], by="ID1")

produces

   ID1          X1 ID2
1    A  0.03594331   V
2    A  0.42814900   V
3    B -2.17161263   W
4    B -0.33403550   W
5    C  0.95407844   X
6    C -0.23186723   X
7    D  0.46395514   Y
8    D -1.49919961   Y
9    E -0.20342430   Z
10   E -0.49847569   Z

Upvotes: 3

Related Questions