如何在R语言中实现多数据框的模糊匹配合并？

解决思路 #

由于机构名称存在不一致（如前缀差异、拼写细微不同），直接用dplyr::inner_join精确匹配会导致无结果。可以通过两种核心方法解决：字符串标准化后精确匹配，或借助模糊匹配工具包实现近似匹配。

方法一：先标准化机构名称，再精确合并 #

先统一所有数据框的机构名称格式（去除冗余前缀、统一大小写等），再用常规inner_join合并，适合名称差异有规律的场景。

步骤代码 #

1. 加载依赖包

library(dplyr)

2. 定义标准化函数

# 标准化机构名称：转小写、去除"The State "前缀、去首尾空格
standardize_agency <- function(name) {
  name %>%
    tolower() %>%
    gsub("the state ", "", .) %>%
    trimws()
}

3. 对每个数据框生成标准化列

first_clean <- first %>% mutate(agency_std = standardize_agency(agency))
second_clean <- second %>% mutate(agency_std = standardize_agency(agency))
third_clean <- third %>% mutate(agency_std = standardize_agency(agency))

4. 基于标准化列合并

merged_df <- first_clean %>%
  inner_join(second_clean, by = "agency_std") %>%
  inner_join(third_clean, by = "agency_std")

5. 筛选目标机构并整理结果

target_agencies <- c("Board of Accountancy", "Board of Economists", "Board of Cosmetology", "Board of Homeopathic Medicine", "The State Board of Contractors")

# 将标准化后的名称映射回目标名称
final_result <- merged_df %>%
  mutate(target_agency = case_when(
    agency_std == "board of accountancy" ~ "Board of Accountancy",
    agency_std == "board of economists" ~ "Board of Economists",
    agency_std == "board of cosmetology" ~ "Board of Cosmetology",
    agency_std == "board of homeopathic medicine" ~ "Board of Homeopathic Medicine",
    agency_std == "board of contractors" ~ "The State Board of Contractors"
  )) %>%
  filter(target_agency %in% target_agencies) %>%
  select(target_agency, everything()) # 优先保留目标机构列

方法二：用模糊匹配工具包直接合并 #

适合名称差异无规律（如拼写错误、简称）的场景，借助fuzzyjoin包结合字符串距离算法实现近似匹配。

步骤代码 #

1. 安装并加载依赖包

install.packages(c("fuzzyjoin", "stringdist"))
library(dplyr)
library(fuzzyjoin)
library(stringdist)

2. 逐步模糊合并三个数据框

# 先合并first和second，用Jaccard距离判断相似度（阈值<0.2表示高度相似）
merged_first_second <- fuzzy_inner_join(
  first,
  second,
  by = "agency",
  match_fun = function(x, y) stringdist(x, y, method = "jaccard") < 0.2
)

# 再合并third，注意合并后的列名变化
merged_final <- fuzzy_inner_join(
  merged_first_second,
  third,
  by = c("agency.x" = "agency"), # 对应前一次合并的机构列
  match_fun = function(x, y) stringdist(x, y, method = "jaccard") < 0.2
)

3. 筛选目标机构

target_agencies <- c("Board of Accountancy", "Board of Economists", "Board of Cosmetology", "Board of Homeopathic Medicine", "The State Board of Contractors")

final_result <- merged_final %>%
  filter(agency.x %in% target_agencies | agency.y %in% target_agencies | agency %in% target_agencies) %>%
  select(matches("agency"), everything())

说明 #

• stringdist的method参数可选择不同距离算法：jaccard适合短文本，levenshtein适合拼写差异，可根据实际情况调整。
• 匹配阈值（如0.2）可灵活调整：阈值越小，匹配越严格；阈值越大，匹配范围越广。

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