如何基于精确匹配+子串匹配关联两个R DataFrame？

示例数据 #

先定义你提到的示例数据集：

df1 <- data.frame(
  Last_Name = c("Smith", "Johnson", "Williams", "Brown"),
  Contains_First_Name = c("John Smith", "Emily Johnson", "Mike Williams", "Sarah Brown"),
  Account_Number = c("A123", "B456", "C789", "D012")
)

df2 <- data.frame(
  Last_Name = c("Smith", "Johnson", "Williams", "Davis"),
  First_Name = c("John", "Emma", "Mike", "Lisa")
)

方法一：dplyr 精确关联+子串过滤 #

先通过Last_Name做精确内连接，再用str_detect筛选符合子串条件的行：

library(dplyr)
library(stringr)

df3 <- df1 %>%
  inner_join(df2, by = "Last_Name") %>%
  filter(str_detect(Contains_First_Name, fixed(First_Name)))

注：使用fixed()可以避免First_Name中的特殊字符被当作正则表达式解析，确保是精确子串匹配。

方法二：fuzzyjoin 直接实现复合匹配 #

用fuzzy_inner_join同时指定两个匹配规则：Last_Name精确相等，First_Name是Contains_First_Name的子串：

library(fuzzyjoin)
library(stringr)

df3 <- fuzzy_inner_join(
  df1, df2,
  by = c("Last_Name" = "Last_Name", "Contains_First_Name" = "First_Name"),
  match_fun = list(`==`, str_detect)
)

期望输出 #

最终得到的df3结果如下：

#   Last_Name Contains_First_Name Account_Number First_Name
# 1     Smith          John Smith           A123       John
# 2  Williams        Mike Williams           C789       Mike

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