0

I'm looking for a dplyr solution to join two columns of a data frame with one column of another data frame using one line. E.G:

Data frames to merge:

df <- tibble(a = 1:5,
             b = 6:10)

df2 <- tibble(c = 1:10,
              d = letters[1:10])

Desired result:

df3 <- df %>%
  left_join(df2, by = c('a' = 'c')) %>% 
  rename(d_a = d) %>% 
  left_join(df2, by = c('b' = 'c')) %>% 
  rename(d_b = d)

      a     b d_a   d_b  
  <int> <int> <chr> <chr>
1     1     6 a     f    
2     2     7 b     g    
3     3     8 c     h    
4     4     9 d     i    
5     5    10 e     j

How can I use left-join once to merge both a and b with c?

I have tried:

df %>%
  left_join(df2, by = c('a' = 'c',
                        'b' = 'c'))
Ali
  • 1,048
  • 8
  • 19
  • 1
    bad idea to name a column `c`. – s_baldur Sep 08 '20 at 10:17
  • I don't believe this is a duplicate question. I've accepted @Ronak Shah answer but my main goal was to simplify my desired result. This question is not asking how to reshape data and how to merge data. It's a very specific case. – Ali Sep 09 '20 at 11:52

2 Answers2

1

Get the data in long format so that you can join multiple columns in one left join

library(dplyr)
library(tidyr)

df %>%
  mutate(row = row_number()) %>%
  pivot_longer(cols = -row) %>%
  left_join(df2, by = c('value' = 'c')) %>%
  pivot_wider(names_from = name, values_from = c(value, d)) %>%
  select(-row)

#  value_a value_b d_a   d_b  
#    <int>   <int> <chr> <chr>
#1       1       6 a     f    
#2       2       7 b     g    
#3       3       8 c     h    
#4       4       9 d     i    
#5       5      10 e     j
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
0

Another way you can try with left_join()

library(dplyr)
df3 <- df %>% 
  left_join(df2, by = c("a" = "c")) %>% 
  left_join(df2, by = c("b" = "c"), suffix = c("_a", "_b"))

#       a     b d_a   d_b  
# <int> <int> <chr> <chr>
# 1     1     6 a     f    
# 2     2     7 b     g    
# 3     3     8 c     h    
# 4     4     9 d     i    
# 5     5    10 e     j
Tho Vu
  • 1,304
  • 2
  • 8
  • 20