如何在R中连接含两个候选ID列且存在大量缺失值的两个data.frame

我来帮你搞定这个带缺失ID列的数据框关联需求，按照你提供的示例，咱们一步步实现想要的结果：

1. 先准备示例数据 #

首先加载dplyr包（要用到里面的连接和数据处理函数），然后定义你给出的两个混乱数据集：

library(dplyr)

# 示例混乱数据集
messy1 <- data.frame(id1 = c(11, NA, 13, 11, 12, NA), id2 = c(NA, 22, 23, 21, NA, 23), time = rep(1:2, each = 3))
messy2 <- data.frame(id1 = c(12, NA, 14, 14), id2 = c(22, 23, 24, NA), time = c(1, 1, 1, 2))

2. 构建完整的ID映射表 #

关键一步是先把两个数据集中非缺失的ID对提取出来，构建一个id1和id2的完整映射关系，确保每个ID都能找到对应的另一个ID：

# 从两个数据集中提取非缺失的ID对，合并后去重
id_mapping <- bind_rows(
  messy1 %>% filter(!is.na(id1) & !is.na(id2)) %>% select(id1, id2),
  messy2 %>% filter(!is.na(id1) & !is.na(id2)) %>% select(id1, id2)
) %>% distinct()

# 补全每个ID对应的唯一配对值（保证id1和id2一一对应）
id_mapping <- id_mapping %>%
  group_by(id1) %>%
  mutate(id2 = first(id2)) %>%
  ungroup() %>%
  group_by(id2) %>%
  mutate(id1 = first(id1)) %>%
  ungroup() %>%
  distinct()

3. 补全两个数据集中的缺失ID #

有了映射表，就可以把两个混乱数据集中的缺失ID补全了：

# 补全messy1的缺失ID
clean1 <- messy1 %>%
  left_join(id_mapping, by = "id1") %>%
  mutate(id2 = coalesce(id2.x, id2.y)) %>%  # 用映射表的值替换原缺失的id2
  select(-id2.x, -id2.y) %>%
  left_join(id_mapping, by = "id2") %>%
  mutate(id1 = coalesce(id1.x, id1.y)) %>%  # 用映射表的值替换原缺失的id1
  select(-id1.x, -id1.y) %>%
  distinct()  # 去除重复行

# 补全messy2的缺失ID
clean2 <- messy2 %>%
  left_join(id_mapping, by = "id1") %>%
  mutate(id2 = coalesce(id2.x, id2.y)) %>%
  select(-id2.x, -id2.y) %>%
  left_join(id_mapping, by = "id2") %>%
  mutate(id1 = coalesce(id1.x, id1.y)) %>%
  select(-id1.x, -id1.y) %>%
  distinct()

4. 全连接得到最终结果 #

最后把补全后的两个数据集做全连接，再按time和id1排序，就得到了你想要的结果：

desired_joined <- full_join(clean1, clean2) %>% arrange(time, id1)

# 查看最终结果
desired_joined

运行后输出的结果和你给出的期望结果完全一致：

id1 id2 time
1  11  21    1
2  12  22    1
3  13  23    1
4  14  24    1
5  11  21    2
6  12  22    2
7  13  23    2
8  14  24    2

思路说明 #

核心逻辑是先通过现有非缺失的ID对建立一一对应的映射关系，解决ID缺失的问题，再对补全后的数据集进行关联，这样就能避免因为缺失值导致的关联失败或数据遗漏。

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