R语言如何基于列内字符串子集双向匹配合并数据框

这是此前帖子的延伸问题。

需求说明 #

需要合并两个测试数据框，测试数据构造如下：

year <- c("2002", "2002", "1999", "1999", "1997", "2002")
state <- c("TN", "TN", "AL", "AL", "CA", "TN")
name <- c("Molly Homes, Jane Doe", "Sally", "David", "Laura", "John", "Kate")
df1 <- data.frame(year, state, name)

year <- c("2002", "1999")
state <- c("TN", "AL")
versus <- c("Homes (v. Vista)", "@laura v. dAvid")
df2 <- data.frame(year, state, versus)

期望输出结构参考：

year <- c("2002", "2002", "1999", "1999", "1997", "2002")
state <- c("TN", "TN", "AL", "AL", "CA", "TN")
name <- c("Molly Homes, Jane Doe", "Sally", "David", "Laura", "John", "Kate")
versus <- c("Homes (v. Vista)", NA, "@laura v. dAvid", "@laura v. dAvid", NA, NA)
df4 <- data.frame(year, state, name, versus)

注：原示例中Sally对应的versus值在df2中无匹配来源，属于手敲笔误，按实际匹配逻辑应为NA

原有方案问题 #

之前给出的参考实现如下：

library(dplyr)

df3 <- left_join(df1,df2, by=c("year","state")) %>% 
  rowwise() %>% 
  mutate(versus:=if_else(grepl(name,versus,ignore.case=T), versus,as.character(NA)))

该方案仅做了单向匹配：仅判断name是否是versus的子串，无法覆盖反向匹配场景。实际需要的匹配规则为：忽略大小写的前提下，只要任意一个字段是另一个字段的子串，就判定为匹配成功，保留对应的versus值。

修正后代码 #

library(dplyr)

df_result <- left_join(df1, df2, by = c("year", "state")) %>%
  rowwise() %>%
  mutate(
    versus = if_else(
      # 双向子串判断，加fixed = TRUE避免正则特殊字符干扰匹配结果
      grepl(name, versus, ignore.case = TRUE, fixed = TRUE) | 
      grepl(versus, name, ignore.case = TRUE, fixed = TRUE),
      versus,
      NA_character_
    )
  ) %>%
  ungroup()

逻辑说明 #

• 第一步先按year、state两个字段做左连接，把同年同州的versus值关联到df1对应行
• 逐行做双向子串匹配，两个匹配条件任意一个满足就保留versus值，不满足则置为缺失值
• 匹配时加入fixed = TRUE参数，按字符串字面量匹配，避免括号、点号等正则特殊字符导致匹配错误
• 最后用ungroup()取消行级分组，回到普通数据框结构，方便后续操作

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