-1

I have two data frames:

df1 <- data.frame(x1=c("a","b","z","u"),
                  x2=c("f", "a","d","x"))

df2 <- data.frame(x=letters[1:10],y=1:10,z=11:20)

I now want to merge them by x1,x2 and x, i.e. if the letter x is in either x1 or x2 the corresponding y and z values should be added. If two choices are available x1 should be used as reference.

df1 should be the "master" data set (like all.x = TRUE argument).

the final data frame here would be

x1  x2  y  z
 a   f  1  11
 b   a  2  12
 z   d  4  14
 u   x  NA NA

I'd like to see a second solution that adds columns y1, z1, and y2, z2 like this:

    x1  x2  y1  z1 y2  z2
     a   f  1  11   6  16
     b   a  2  12   1  11
     z   d  NA NA   4  14
     u   x  NA NA   NA NA

would appreciate a merge or dplyr or tidyr solution

spore234
  • 3,550
  • 6
  • 50
  • 76
  • For your second solution, simply left_joining (see `dplyr::left_join`) on x=x1 first then again on x=x2 will do the trick. – asachet Feb 09 '16 at 17:03

2 Answers2

1

For your second solution, you can simply use two consecutive left joins (for example using dplyr::left_join).

left_join(df1, df2, by=c("x1"="x")) %>% 
    rename(y1=y, z1=z) %>% 
    left_join(df2, by=c("x2"="x")) %>%
    rename(y2=y, z2=z)

yields exactly what you want.

asachet
  • 6,620
  • 2
  • 30
  • 74
  • could you please add a solution where I have more than two columns to merge (here y,z). I do not want to rename 100 columns by hand. – spore234 Feb 20 '16 at 13:09
1

For the first solution: 

df1 %>% 
left_join(df2, by = c("x1"="x"))

For the second, see the the solution by @antoine-sac above.

lizzie
  • 606
  • 6
  • 15