How to merge 2 columns on 1 column

5
votes

I would like to merge 2 df's where in df1 contains 2 columns and df2 1 column, how to apply function merge in this case?

Here is sample case:

df1 <- data.frame(var1=letters[1:5],var2=letters[6:10])
df2 <- data.frame(var3=letters[1:10])

False attempt:

merge(df1,df2,by.x=c("var1","var2"),by.y="var3",all.y=TRUE)

How to merge these two df's so that the matching search uses both columns of df1 (var1 & var2) and operates on df2 (var3)?

Desired output:

    var1  var2   var3 
1     a    f       a
2     b    g       b
3     c    h       c
4     d    i       d
5     e    j       e
6     <NA> <NA>    f
7     <NA> <NA>    g
8     <NA> <NA>    h
9     <NA> <NA>    i 
10    <NA> <NA>    j

EDIT: Improved data (I hope):

df1 <- data.frame(var1=c(letters[1:5],rep("x",5)),var2=c(letters[6:10],rep("x",5)))
df2 <- data.frame(var3=letters[1:10])

Desired output:

     var1  var2   var3 
 1     a    f       a
 2     b    g       b
 3     c    h       c
 4     d    i       d
 5     e    j       e
 6     x    x       f
 7     x    x       g
 8     x    x       h
 9     x    x       i 
10     x    x       j
3
rdataframe
merge twice may be one possible solution. – Metrics
yes, I thought about it, but may be messy with my real data. – Maximilian
by messy, I mean messing up with data :) I thought that, that may be rather prone to error – Maximilian
Can you clarify "so that the matching search uses both columns of df1 (var1 & var2)"? In your desired outputs, it looks like var1 is matching to var3; does var2 have a role to play in the join besides being included in the resulting table? – Sam Firke

3 Answers

5
votes

Here's a possible data.table solution as per first desired output

library(data.table)
setkey(setDT(df2), var3)
df2[df1, `:=`(var1 = i.var1, var2 = i.var2)][]
#     var3 var1 var2
#  1:    a    a    f
#  2:    b    b    g
#  3:    c    c    h
#  4:    d    d    i
#  5:    e    e    j
#  6:    f   NA   NA
#  7:    g   NA   NA
#  8:    h   NA   NA
#  9:    i   NA   NA
# 10:    j   NA   NA
5
votes

You really just need to reorder df2 according to df1 and cbind them:

cbind(df1, df2[order(match(df2$var3, df1$var1)),, drop=FALSE])

If df2 has more than one column, you don't need drop=FALSE.

#    var1 var2 var3
# 1     a    f    a
# 2     b    g    b
# 3     c    h    c
# 4     d    i    d
# 5     e    j    e
# 6     x    x    f
# 7     x    x    g
# 8     x    x    h
# 9     x    x    i
# 10    x    x    j

Keeping with this approach, for the first data set without the xs, you could use:

cbind(lapply(df1, `length<-`, nrow(df2)), df2[order(match(df2$var3, df1$var1)),, drop=FALSE])

#    var1 var2 var3
# 1     a    f    a
# 2     b    g    b
# 3     c    h    c
# 4     d    i    d
# 5     e    j    e
# 6  <NA> <NA>    f
# 7  <NA> <NA>    g
# 8  <NA> <NA>    h
# 9  <NA> <NA>    i
# 10 <NA> <NA>    j

Or in a more readable fashion:

df1 <- lapply(df1, `length<-`, nrow(df2))
df2 <- df2[order(match(df2$var3, df1$var1)),, drop=FALSE]
cbind(df1, df2)
5
votes

You can use merge with argument by='row.names' and sort=F (as pointed out by Matthew Plourde) to not let merge mess up the order:

> merge(df1, df2, by='row.names', sort=FALSE, all=TRUE)[c("var1", "var2", "var3")]
   var1 var2 var3
1     a    f    a
2     b    g    b
3     c    h    c
4     d    i    d
5     e    j    e
6  <NA> <NA>    i
7  <NA> <NA>    f
8  <NA> <NA>    g
9  <NA> <NA>    h
10 <NA> <NA>    j