如何在R中基于不同列的相同值合并两个数据框（base/dplyr）

我有两个R语言的tibble数据框A和B：

数据框A #

cat = rep("xx",5)
first = c("a","a","a","h","h")
second = c("b","c","d","b","c")
val1 = c(1,2,3,10,20)
A = tibble(cat,first,second,val1)

结构如下：

# A tibble: 5 x 4
  cat   first second  val1
  <chr> <chr> <chr>  <dbl>
1 xx    a     b          1
2 xx    a     c          2
3 xx    a     d          3
4 xx    h     b         10
5 xx    h     c         20

数据框B #

cat = rep("xx",5)
first = c("a","a","a","b","c")
second = c("b","c","d","h","h")
val2 = c(100,200,300,400,500)
B = tibble(cat,first,second,val2)

结构如下：

# A tibble: 5 x 4
  cat   first second  val2
  <chr> <chr> <chr>  <dbl>
1 xx    a     b        100
2 xx    a     c        200
3 xx    a     d        300
4 xx    b     h        400
5 xx    c     h        500

合并问题 #

直接使用left_join(A,B,by=c("cat","first","second"))合并时，由于A中first=h的行对应的second值（b、c）在B中是first列的值，导致这两行匹配不到，结果val2为NA：

# A tibble: 5 x 5
  cat   first second  val1  val2
  <chr> <chr> <chr>  <dbl> <dbl>
1 xx    a     b          1   100
2 xx    a     c          2   200
3 xx    a     d          3   300
4 xx    h     b         10    NA
5 xx    h     c         20    NA

理想合并结果 #

我已经实现了一种可行方法，但希望更高效：

Aa = left_join(A,B,by=c("cat","first","second"))
BD = B%>%rename(second=first,first=second)
full_join(Aa,BD,by=c("cat","first","second"))%>%
  dplyr::mutate(Val2 = coalesce(val2.x,val2.y))%>%
  dplyr::select(-c(val2.x,val2.y))%>%
  tidyr::drop_na()

可以通过预处理B，同时保留原匹配模式和交换first/second的匹配模式，再与A做一次left_join即可，无需多次合并和后续清理：

library(dplyr)

# 生成包含两种匹配模式的B版本，去重避免重复匹配
B_expanded <- bind_rows(
  B,  # 原模式：cat + first + second 匹配
  B %>% rename(first = second, second = first)  # 交换模式：cat + second + first 匹配
) %>% distinct(cat, first, second, .keep_all = TRUE)

# 直接与A左连接
ideal_result <- A %>% left_join(B_expanded, by = c("cat", "first", "second"))

这样处理后，ideal_result直接得到目标结果，步骤更简洁，避免了多次合并和列名清理的操作。

内容的提问来源于stack exchange，提问作者Homer Jay Simpson