How to merge 2 df by 2 non-unique columns

Question

I have 2 dataframes:

df1=data.frame(col1=c('A', 'A', 'B', 'B', 'B'), 
               col2a=c(1, 4, 3, 2, 5))
df2=data.frame(col1=c('A', 'A', 'A', 'B', 'B'), 
               col2b=c('a', 'd', 'c', 'b', 'e'))

I want to have a resulted df like this:

df3=data.frame(col1=c('A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B'),
               col2b=c('a', 'a', 'd', 'd', 'c', 'c', 'b', 'b', 'b', 'e', 'e', 'e'),
               col2a=c(1, 4, 1, 4, 1, 4, 3, 2, 5, 3, 2, 5))

I have tried to use merge() for df1 and df2 by column col1 but it doesn't work because col1 is a non-unique column. Can anyone help me with this?

Do you want a full join? `merge(df1, df2, by = 'col1', all = TRUE)` — Ronak Shah, May 21 '20 at 06:54
@RonakShah I can't do it because col1 is a non-unique column. I got an error of 'formal argument "y" matched by multiple actual arguments'. — Liselotte, May 21 '20 at 07:14
@RonakShah thank you so much, I can do it already with your solution :) — Liselotte, May 21 '20 at 07:23

score 2 · Accepted Answer · answered May 21 '20 at 07:18

One dplyr solution is

dplyr::full_join(df1, df2)

Output

#    col1 col2a col2b
# 1     A     1     a
# 2     A     1     d
# 3     A     1     c
# 4     A     4     a
# 5     A     4     d
# 6     A     4     c
# 7     B     3     b
# 8     B     3     e
# 9     B     2     b
# 10    B     2     e
# 11    B     5     b
# 12    B     5     e

In base R, to obtain the same output you can use

merge(df1, df2)

How to merge 2 df by 2 non-unique columns

1 Answers1