如何为首次left_join未匹配记录执行补充左连接（R语言）

已有三个DataFrame：df_beats、df_cmdb1、df_cmdb2。通过s_name/s_name1字段完成df_beats与df_cmdb1的左连接后，得到结果left1，其中第5-8行未匹配成功。需仅对这些未匹配行，通过s_ip_address/s_ip_address2字段与df_cmdb2做补充左连接，且不修改已匹配的第1-4行。

原数据定义与首次连接代码 #

# df_beats定义
s_name       <- c("john.lennon",  "paul.mccartney", "george.harrison", "ringo.starr",  "mick.jagger",  "keith.richards", "charlie.watts", "ron.wood")
s_ip_address <- c("192.9.208.161","170.70.24.32",   "180.169.22.12",   "170.70.68.56", "192.9.208.14", "10.10.10.5",     "22.250.32.14",  "22.24.9.3")
n_port       <- c(22,    21,   80,       123,    22,     8080,   8088,    411)
s_protocol   <- c("tcp", "tcp","tcp",    "udp",  "tcp",  "tcp",  "tcp",  "tcp")
n_severity   <- c(4,2,5,1, 3,1,2,2)
df_beats <- data.frame(s_name,s_ip_address,n_port,s_protocol,n_severity)

# df_cmdb1定义
s_asset_tag     <- c("CMDB1009","CMDB0618","CMDB0225","CMDB0707","CMDB0919","CMDB0103")
s_name1          <- c("john.lennon","paul.mccartney","george.harrison","ringo.starr","brian.epstein","george.martin")
s_used_for      <- c("Production","Development","Certification","Pre-Production","Production","Development")
s_os_model      <- c("Windows Server 2012","Windows Server 2019 Datacenter","Windows Server 2008","Windows Server 2016","Windows Server 2012","Windows Server 2012")
df_cmdb1 <- data.frame(s_asset_tag,s_name1,s_used_for,s_os_model)

# df_cmdb2定义
s_asset_tag     <- c("CMDB0726","CMDB1218","CMDB0602","CMDB0601","CMDB1024","CMDB0228")
s_ip_address2    <- c("192.9.208.14","10.10.10.5",    "22.250.32.14",  "22.24.9.3", "180.169.22.8",  "180.181.21.25")
s_used_for      <- c("Production","Test","Production","Pre-Production","Contingency","Production")
s_os_model      <- c("Red Hat Linux","Red Hat Linux","Oracle Solaris","Qualys Appliance","IBM AIX","VMWare vRealize")
df_cmdb2 <- data.frame(s_asset_tag,s_ip_address2,s_used_for,s_os_model)

# 首次left_join代码
library(dplyr)
left1 <- left_join(df_beats, df_cmdb1, by = c("s_name" = "s_name1"))

补充连接实现代码 #

# 1. 拆分已匹配与未匹配行
matched_rows <- left1 %>% filter(!is.na(s_asset_tag))
unmatched_rows <- left1 %>% filter(is.na(s_asset_tag))

# 2. 对未匹配行执行补充左连接，并整理列名
supplemented_rows <- left_join(unmatched_rows, df_cmdb2, by = c("s_ip_address" = "s_ip_address2")) %>%
  select(-s_asset_tag.x, -s_used_for.x, -s_os_model.x) %>%
  rename(
    s_asset_tag = s_asset_tag.y,
    s_used_for = s_used_for.y,
    s_os_model = s_os_model.y
  )

# 3. 合并结果，保留原行顺序
final_result <- bind_rows(matched_rows, supplemented_rows)

# 查看最终结果
print(final_result)

代码说明 #

• 拆分已匹配/未匹配行：通过is.na(s_asset_tag)判断是否匹配成功，将left1拆分为两部分，确保已匹配行不受后续操作影响。
• 补充连接：仅对未匹配行与df_cmdb2按IP字段连接，移除原连接产生的NA列并重命名新列，避免列名冲突。
• 合并结果：用bind_rows按原顺序合并两部分，保证最终结果的行顺序与原始df_beats一致。

内容的提问来源于stack exchange，提问作者Alejandro Bermúdez