在R语言中按所有可能ID合并两数据集并保留df1未匹配ID

现有两个R语言数据框：

df1 <- data.frame(id = c(11,22,33,44,55),
                  score = c(20,22,33,22,11))

df2 <- data.frame(id1 = c(11,22,NA,NA,8),
                  id2 = c(98,9,33,NA,24),
                  id3 = c(NA,NA,66,44,88),
                  id4 = c(NA,NA,NA,16,17))

它们的结构如下：

> df1
  id score
1  11    20
2  22    22
3  33    33
4  44    22
5  55    11
> df2
  id1 id2 id3 id4
1   11  98  NA  NA
2   22   9  NA  NA
3  NA   33  66  NA
4  NA  NA   44  16
5   8  24   88  17

需要将这两个数据集通过df1的id与df2的四个id列匹配合并，同时保留df1中所有未匹配的id，最终得到如下目标结果：

df.merged <- data.frame(id = c(11,22,33,44,55),
                        score = c(20,22,33,22,11),
                        id1 = c(11,22,NA,NA, NA),
                        id2 = c(98,9,33,NA,NA),
                        id3 = c(NA,NA,66,44,NA),
                        id4 = c(NA,NA,NA,16,NA))

输出结构：

> df.merged
  id score id1 id2 id3 id4
1  11    20   11  98  NA  NA
2  22    22   22   9  NA  NA
3  33    33  NA   33  66  NA
4  44    22  NA  NA   44  16
5  55    11  NA  NA  NA  NA

方法一：基础R实现（无需额外包） #

直接通过判断匹配关系为df1新增列，逻辑清晰且无需依赖第三方包：

# 初始化合并结果为df1
df.merged <- df1

# 为每个id列生成匹配值，未匹配则填充NA
df.merged$id1 <- ifelse(df1$id %in% df2$id1, df1$id, NA)
df.merged$id2 <- ifelse(df1$id %in% df2$id2, df2$id2[match(df1$id, df2$id2)], NA)
df.merged$id3 <- ifelse(df1$id %in% df2$id3, df2$id3[match(df1$id, df2$id3)], NA)
df.merged$id4 <- ifelse(df1$id %in% df2$id4, df2$id4[match(df1$id, df2$id4)], NA)

# 查看最终结果
print(df.merged)

方法二：tidyverse工具包实现 #

如果习惯使用tidyverse的链式语法，可以通过长-宽格式转换来实现：

# 先安装并加载tidyverse（若未安装）
# install.packages("tidyverse")
library(tidyverse)

# 处理df2：筛选出与df1匹配的id，转换为宽格式
df2_matched <- df2 %>%
  mutate(row_idx = row_number()) %>%
  pivot_longer(cols = starts_with("id"), names_to = "col", values_to = "id_val") %>%
  filter(id_val %in% df1$id) %>%
  pivot_wider(id_cols = row_idx, names_from = "col", values_from = "id_val") %>%
  select(-row_idx)

# 左连接df1与处理后的df2，整理列逻辑
df.merged <- df1 %>%
  left_join(df2_matched, by = c("id" = "id1")) %>%
  left_join(df2_matched, by = c("id" = "id2")) %>%
  left_join(df2_matched, by = c("id" = "id3")) %>%
  left_join(df2_matched, by = c("id" = "id4")) %>%
  mutate(
    id1 = ifelse(id == id1.x, id1.x, NA),
    id2 = ifelse(id == id2.y, id2.y, NA),
    id3 = ifelse(id == id3.y, id3.y, NA),
    id4 = ifelse(id == id4.y, id4.y, NA)
  ) %>%
  select(id, score, id1, id2, id3, id4)

# 查看结果
print(df.merged)

内容的提问来源于stack exchange，提问作者amisos55