如何在R语言中按相同ID列合并两个文件？

需求说明 #

需要将两个文件按ID列合并：

• File1的ID列包含多值（如A,B,C），需匹配File2中的单个ID，取第一个匹配项对应合并
• 保留两边所有行，缺失值用*填充

示例数据 #

File1 #

file1 <- data.frame(
  ID = c("A,B,C", "D,F", "G,R", "H", "S"),
  feature1 = c(1, 2, 2, 6, 8),
  feature2 = c(100, 200, 200, 500, 600),
  feature3 = c(150, 500, 600, 800, 700),
  stringsAsFactors = FALSE
)

File2 #

file2 <- data.frame(
  ID = c("A", "F", "G", "H", "P"),
  feature4 = c(5, 6, 4, 8, 2),
  feature5 = c(4, 7, 3, 2, 1),
  stringsAsFactors = FALSE
)

实现代码 #

需要用到dplyr和tidyr包，若未安装先执行install.packages(c("dplyr", "tidyr"))

library(dplyr)
library(tidyr)

# 给File1添加行号，用于后续匹配后恢复原行
file1$row_num <- 1:nrow(file1)

# 拆分File1的多值ID为单行
file1_split <- separate_rows(file1, ID, sep = ",")

# 匹配File2的ID，取每个原行的第一个匹配项
matched <- left_join(file1_split, file2, by = "ID") %>%
  group_by(row_num) %>%
  slice(1) %>%
  ungroup()

# 合并匹配结果与原File1，恢复原ID列
result_part1 <- left_join(file1, matched %>% select(row_num, ID.y = ID, feature4, feature5), by = "row_num") %>%
  select(-row_num)

# 处理File2中未匹配的行，填充左侧缺失值为*
unmatched_file2 <- file2 %>%
  filter(!ID %in% file1_split$ID) %>%
  mutate(
    ID = "*",
    feature1 = "*",
    feature2 = "*",
    feature3 = "*"
  ) %>%
  select(ID, feature1, feature2, feature3, ID.y = ID, feature4, feature5)

# 合并所有行，将NA替换为*
final_result <- bind_rows(result_part1, unmatched_file2) %>%
  mutate(across(everything(), ~ifelse(is.na(.), "*", .))) %>%
  rename(ID.x = ID)

# 查看最终结果
print(final_result, row.names = FALSE)

最终输出 #

ID.x feature1 feature2 feature3 ID.y feature4 feature5
A,B,C        1      100      150    A        5        4
  D,F        2      200      500    F        6        7
  G,R        2      200      600    G        4        3
    H        6      500      800    H        8        2
    S        8      600      700    *        *        *
    *        *        *        *    P        2        1

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