基于R实现两年间学校员工姓名跨年度匹配方案问询

场景说明 #

处理50万行的2021、2022年学校员工数据，已完成单年度内的人员匹配（通过FamilyName、DateOfBirth及拼写相近的GivenName分配person_id）。现需跨年度匹配：假设员工FamilyName和DateOfBirth年度间不变，仅GivenName可能存在拼写微调，为2022年数据添加matched_id字段关联2021年对应person_id，无匹配项则标记为NA。

数据示例 #

2021年度数据：

dat_2021 <- data.frame(GivenName = c("william", "william", "laura", "jessica", "jessicalouise", "james", "greg", "griffin"), 
                       FamilyName = c("smith", "smith", "maxwell", "maxwell", "maxwell", "lead", "jones", "jones"),
                       DateOfBirth = c("2000-01-02", "2000-01-02", "2007-01-02","2007-01-02","2007-01-02","1999-01-02","2004-01-02","2004-01-02"), 
                       school_id = c(2, 3, 4, 5, 6, 7, 8, 9),
                       person_id = c(1, 1, 2, 3, 3, 4, 5, 6))

2022年度数据：

dat_2022 <- data.frame(GivenName = c("william", "wills", "laurra", "jessica", "jessicalouise", "jamie", "geoff", "brian"), 
                        FamilyName = c("smith", "smith", "maxwell", "maxwell", "maxwell", "lead", "jones", "davy"),
                        DateOfBirth = c("2000-01-02", "2000-01-02", "2007-01-02","2007-01-02","2007-01-02","1999-01-02","2004-01-02","2004-01-02"), 
                         school_id = c(2, 3, 4, 5, 6, 7, 8, 9),
                         person_id = c("a", "a", "b", "c", "c", "d", "e", "f"))

目标输出（添加matched_id字段）：

dat_2022_desired <- data.frame(GivenName = c("william", "wills", "laurra", "jessica", "jessicalouise", "jamie", "geoff", "brian"), 
                                FamilyName = c("smith", "smith", "maxwell", "maxwell", "maxwell", "lead", "jones", "davy"),
                                DateOfBirth = c("2000-01-02", "2000-01-02", "2007-01-02","2007-01-02","2007-01-02","1999-01-02","2004-01-02","2004-01-02"), 
                                 school_id = c(2, 3, 4, 5, 6, 7, 8, 9),
                                 person_id = c("a", "a", "b", "c", "c", "d", "e", "f"),
                                 matched_id = c(1, 1, 2, 3, 3, 4, NA, NA))

实现方案 #

1. 依赖包准备

使用dplyr处理数据，stringdist计算姓名拼写相似度（Jaro-Winkler算法适合姓名匹配，更关注前缀一致性）：

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

2. 数据预处理

先对2021年数据去重，保留每个person_id对应的唯一FamilyName+DateOfBirth+GivenName组合（避免同一人多校任职导致重复匹配）：

dat_2021_unique <- dat_2021 %>%
  distinct(person_id, FamilyName, DateOfBirth, GivenName, .keep_all = FALSE)

3. 跨年度匹配逻辑

• 按FamilyName和DateOfBirth关联两年度数据，缩小匹配范围
• 计算GivenName的Jaro-Winkler相似度（取值0-1，越接近1越相似）
• 设定相似度阈值（示例用0.8，可根据实际数据调整）筛选有效匹配
• 将匹配结果关联回2022年原数据，无匹配项设为NA

# 计算相似度并筛选匹配项
match_map <- dat_2022 %>%
  select(person_id, FamilyName, DateOfBirth, GivenName) %>%
  left_join(dat_2021_unique, by = c("FamilyName", "DateOfBirth"), suffix = c("_2022", "_2021")) %>%
  mutate(similarity = 1 - stringdist(GivenName_2022, GivenName_2021, method = "jw")) %>%
  filter(similarity >= 0.8) %>%
  select(person_id, matched_id = person_id) %>%
  distinct(person_id, matched_id)

# 关联回原数据生成最终结果
dat_2022_final <- dat_2022 %>%
  left_join(match_map, by = "person_id") %>%
  mutate(matched_id = ifelse(is.na(matched_id), NA_integer_, matched_id))

4. 结果验证

运行后查看dat_2022_final，与目标输出一致：

print(dat_2022_final)

优化建议 #

• 性能优化：50万行数据建议用data.table替代dplyr提升处理速度，或先按FamilyName+DateOfBirth分组后再计算相似度，减少不必要的计算
• 阈值调整：根据实际姓名拼写差异调整相似度阈值，比如针对缩写、拼写错误等场景微调
• 特殊情况处理：若同一FamilyName+DateOfBirth组内存在多个2021年person_id，可通过group_by(person_id) %>% slice_max(similarity, n=1)取相似度最高的匹配项

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