基于多列匹配填充DataFrame缺失ID列（R语言）

核心逻辑 #

要给d2填充缺失的ID_COLUMN，需按以下规则从d中匹配：

• column2与column3完全一致
• 两个表的Number_of_pupils相对误差不超过±10%（即比值在0.9~1.1之间）
• 若存在多个匹配结果，选取学生数差值最小的记录对应的ID

代码实现 #

先加载tidyverse工具包（未安装则先执行install.packages("tidyverse")）：

library(tidyverse)

# 示例数据集
d <- data.frame(ID_COLUMN = c(45777,45778,45779,45780), 
                column2 = c("PRIMARY","SECONDARY","COLLEGE","COLLEGE"),  
                column3 = c("RURAL","URBAN","RURAL","RURAL"),  
                Number_of_pupils = c(101, 170,180,2000) )

d2 <- data.frame(ID_COLUMN = c(NA,NA,NA,NA), 
                 column2 = c("PRIMARY","COLLEGE","PRIMARY","COLLEGE"),  
                 column3 = c("URBAN","RURAL","RURAL","RURAL"),  
                 Number_of_pupils = c(110, 300,200,2000))

# 定义通用填充函数
fill_missing_id <- function(ref_data, target_data, id_col, match_cols, pupil_col) {
  target_data %>%
    # 按匹配列连接参考数据集
    left_join(ref_data, by = match_cols, suffix = c("_target", "_ref")) %>%
    # 筛选学生数相对误差在±10%内的记录
    filter(between({{pupil_col}}_ref / {{pupil_col}}_target, 0.9, 1.1)) %>%
    # 计算学生数差值的绝对值
    mutate(pupil_diff = abs({{pupil_col}}_ref - {{pupil_col}}_target)) %>%
    # 按目标数据的原始行分组
    group_by(row_number()) %>%
    # 选取差值最小的匹配记录（多值时仅取第一条）
    slice_min(pupil_diff, n = 1, with_ties = FALSE) %>%
    ungroup() %>%
    # 提取并回填ID
    select(all_of(names(target_data)), {{id_col}}) %>%
    rename(ID_COLUMN = {{id_col}}) %>%
    # 合并回原目标数据，保留未匹配行的NA
    right_join(target_data, by = names(target_data)) %>%
    mutate(ID_COLUMN = coalesce(ID_COLUMN.x, ID_COLUMN.y)) %>%
    select(-ID_COLUMN.x, -ID_COLUMN.y)
}

# 调用函数填充d2
filled_d2 <- fill_missing_id(
  ref_data = d,
  target_data = d2,
  id_col = ID_COLUMN,
  match_cols = c("column2", "column3"),
  pupil_col = Number_of_pupils
)

# 查看结果
print(filled_d2)

关键步骤说明 #

1. 数据连接：通过left_join按column2和column3关联两个数据集，确保保留d2的所有行
2. 误差筛选：用between函数判断学生数的比值是否在0.9~1.1之间，满足±10%的误差要求
3. 最优匹配：按d2的原始行分组，用slice_min选取学生数差值最小的记录，保证匹配结果唯一
4. 结果回填：通过right_join和coalesce将匹配到的ID回填到d2，未匹配的行保留原NA值

示例输出 #

运行代码后得到的filled_d2结果：

ID_COLUMN  column2 column3 Number_of_pupils
1        NA  PRIMARY    URBAN              110
2        NA   COLLEGE    RURAL              300
3        NA  PRIMARY    RURAL              200
4     45780   COLLEGE    RURAL             2000

• 仅第4行满足所有匹配规则，成功填充ID；其余行因无符合条件的匹配项，保留NA

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