用一列连接两列

Question

我正在寻找一个dplyr解决方案，使用一行将数据帧的两列与另一数据帧的一列连接起来。例如：

要合并的数据帧：

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

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

预期结果：

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  

如何使用left-join一次将a和b与c合并

我试过：

df %>%
  left_join(df2, by = c('a' = 'c',
                        'b' = 'c'))

Answer 1

以长格式获取数据，以便在一个左联接中连接多个列。

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    

Answer 2

另一种使用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