How to join 2 dataframes on mutiple optional keys in R i.e, (key1 or Key2) either or both matches

Question

Have 2 data-frames, need to join with multiple optional keys i.e, if t1.col1=t2.col1 OR t1.col3=t2.cold3

library(dplyr)

d1 <- data_frame(
    x = letters[1:3],
    y = LETTERS[2:4],
    a = rnorm(3)
)

d2 <- data_frame(
    x2 = letters[5:3],
    y2 = LETTERS[3:1],
    b = rnorm(3)
)

left_join(d1, d2, by = c("x" = "x2", "y" = "y2"))

#OUTPUT d1

      x     y         a
  <chr> <chr>     <dbl>
1     a     B  1.349394
2     b     C -1.364727
3     c     D  1.697234

#OUTPUT d2
     x2    y2          b
  <chr> <chr>      <dbl>
1     e     C  0.6587823
2     d     B -1.2001558
3     c     A  0.6175364

#OUTPUT joinresult : All NA in the B field

      x     y         a     b
  <chr> <chr>     <dbl> <dbl>
1     a     B  1.349394    NA
2     b     C -1.364727    NA
3     c     D  1.697234    NA

#EXPECTATION : d1:x =d2:x2 for value "c" and d1:y=d2:y2 for value "B" & "C"
hence all B matching values should populated in JOIN

above sample join dataframe, when both key matches, I need to join when either or both key matches.

Any help would be greatly appreciated.

Answer 1

Your phrasing makes me think you know SQL, so the easiest answer might be to use sqldf, which lets you do SQL joins on dataframes as if they were tables:

library(sqldf)
sqldf('select x,y,a,b from d1 join d2 on d1.x = d2.x2 or d1.y = d2.y2')

  x y           a          b
1 a B -0.62688156 -0.6449346
2 b C  0.04378374 -0.3865766
3 c D -0.23755237 -1.6633351