R语言full_join设置备选连接列及合并匹配行的技术问询

要实现优先按IRB_NO连接，IRB_NO为空时 fallback 到NCT编号连接的需求，可以分阶段处理连接逻辑，确保匹配准确且合并正确的行。以下是具体实现步骤：

1. 加载依赖包 #

library(dplyr)
library(tibble)

2. 定义原始数据框 #

df <- tibble::tribble(
  ~IRB_NO,       ~Oncore_NCT_Number, ~Oncore_Protocol_No,
  NA_character_, "NCT03150693",      "A041501",
  "0002-20",     NA_character_,      "NHPCC",
  NA_character_, "NCT05091567",      "LS-P-GO43104",
  "0003-21",     "NCT04546399",      "AALL1821",
  "0005-14",     "NCT01824836",      "CTSU-E1Z11",
  "0005-21",     NA_character_,      "LS-P-ONC-1879",
)

df2 <- tibble::tribble(
  ~IRB_NO,       ~Epic_NCT_Number, ~Epic_Protocol_No,
  "0001-21",     "NCT03150693",    "A041501",
  "0002-20",     NA_character_,    "NHPCC",
  NA_character_, "NCT05091567",    "LS-P-GO43104",
  NA_character_, "NCT04546399",    "AALL1821",
  "0005-14",     "NCT01824836",    "CTSU-E1Z11",
  "0005-21",     NA_character_,    "LS-P-ONC-1879",
)

3. 分阶段执行优先级连接 #

第一步：优先按IRB_NO全连接并标记匹配状态 #

先完成IRB_NO非空行的匹配，同时保留所有行并标记哪些行通过IRB_NO成功匹配：

merged_irb <- df %>%
  full_join(df2, by = "IRB_NO", suffix = c("_oncore", "_epic")) %>%
  mutate(matched_by_irb = !is.na(Oncore_NCT_Number) & !is.na(Epic_NCT_Number))

第二步：提取未通过IRB_NO匹配的行 #

拆分出在第一次连接中未匹配的行，分别来自两个数据框：

# df中未匹配到df2的行（Epic相关列全为NA）
unmatched_df <- merged_irb %>%
  filter(is.na(Epic_NCT_Number)) %>%
  select(IRB_NO, Oncore_NCT_Number, Oncore_Protocol_No)

# df2中未匹配到df的行（Oncore相关列全为NA）
unmatched_df2 <- merged_irb %>%
  filter(is.na(Oncore_NCT_Number)) %>%
  select(IRB_NO, Epic_NCT_Number, Epic_Protocol_No)

第三步：用NCT编号连接未匹配行并合并IRB_NO #

对未匹配的行，通过NCT编号进行二次连接，同时用coalesce合并IRB_NO列（优先保留非NA值）：

merged_nct <- unmatched_df %>%
  full_join(unmatched_df2, by = c("Oncore_NCT_Number" = "Epic_NCT_Number"), suffix = c("_oncore", "_epic")) %>%
  mutate(IRB_NO = coalesce(IRB_NO_oncore, IRB_NO_epic)) %>%
  select(IRB_NO, Oncore_NCT_Number, Oncore_Protocol_No, Epic_NCT_Number, Epic_Protocol_No)

第四步：合并所有结果并整理 #

将两次连接的结果合并，去除重复行，并调整列顺序方便人工对比：

final_merged <- merged_irb %>%
  filter(matched_by_irb) %>%
  bind_rows(merged_nct) %>%
  distinct(IRB_NO, Oncore_NCT_Number, Oncore_Protocol_No, Epic_NCT_Number, Epic_Protocol_No, .keep_all = TRUE) %>%
  select(IRB_NO, Oncore_NCT_Number, Epic_NCT_Number, Oncore_Protocol_No, Epic_Protocol_No)

最终结果说明 #

执行后final_merged会实现：

• IRB_NO非空的行优先按IRB_NO合并
• IRB_NO为空的行通过NCT编号匹配合并
• 合并后的行保留所有非NA的IRB_NO、NCT编号和协议号，方便人工对比差异

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