Merge returns duplicate rows

Question

I have a data frame df1:

Column1   Column2
 A            B
 A            C      

---

I want to merge with df2:

Code      Country    Continent
   A         x           e
   B         y           f
   C         z           g

To obtain:

Column1   Column2    Country1    Continent1
   A         B          x           e
   A         C          x           e

And do this again for the country in column2, to obtain country and continent for the column2.

Column1   Column2    Country1    Continent1   Country2    Continent2
   A         B          x           e             y            f
   A         C          x           e             z            g

Right now I am using the merge function:

df1<- merge(df1,df2,by.x="Column1",by.y="Code")

But I obtain duplicate columns:

Column1   Column2    Country    Continent
   A         B          x           e
   A         B          x           e
   A         C          x           e
   A         C          x           e

I have tried all combinations of all.x =TRUE, etc.

Is there a way to obtain the output I want? preferably with merge function or plyr...

Thanks in advance

Answer 1

With merge(), I think it's a two step operation... "a" is your first ask, and "c" is your second ask.

df1 <- read.csv(text="Column1,Column2
A,B
A,C", stringsAsFactors=F)
df2 <- read.csv(text="Code,Country,Continent
A,x,e
B,y,f
C,z,g", stringsAsFactors=F)

a <- merge(df1,df2,by.x="Column1",by.y="Code")
b <- merge(df1,df2,by.x="Column2",by.y="Code")
c <- merge(a,b, by.x=c("Column1", "Column2"), by.y=c("Column1", "Column2"), all=TRUE)
> a
  Column1 Column2 Country Continent
1       A       B       x         e
2       A       C       x         e
> c
  Column1 Column2 Country.x Continent.x Country.y Continent.y
1       A       B         x           e         y           f
2       A       C         x           e         z           g