在R语言中基于指定数量列匹配识别疑似重复行

需求说明 #

我们需要在R的DataFrame中识别疑似重复行，规则如下：

• 按FirstName+LastName组合分组，同一姓名为一组
• 若组内某行和组内其他行在至少X个非姓名列数据一致，则标记为疑似重复
• 支持为不同列设置权重，计算重复指数，同时适配NA值的情况

示例数据集 #

先构造测试用的数据集：

df <- data.frame(
  FirstName = c("Daniel", "Daniel", "Daniel", "Daniel", "Daniel", "Daniel", "Daniel", "Daniel", "Daniel", "Daniel"),
  LastName = c("White",   "White",    "Adams",    "Adams",    "Andrews",  "Andrews",  "Thomas",   "Thomas", "Brown", "Brown"),
  City = c("Phoenix", "MESA", "Denver",   "Albany",   "Washington",   "Washington",   "Tampa", "Taos",    "Orlando", NA),
  State = c("AZ", "AZ",   "CO",   "NY",   "DC",   "VA",   "FL",   "NM",   "FL", NA),
  Group1 = c("ABC", "ABC", "XWZ", "XWZ", "LMN", "LMN", "XWZ", "MMM", NA, NA),
  Group2 = c("D001", "D001", "A444", "A444", "M555", "M555", "E333", "F222", "Q777", "Q777"),
  Group3 = c("FORT", "FORT", "SETS", "SETS", "SETS", "SETS", "FORT", "FORT", "SETS", "SETS")
)

比如Daniel White的两行，虽然City列不同，但State、Group1等4列数据一致，应标记为疑似重复。

优化实现方案 #

下面的方案会保留原数据行，同时添加匹配列数和加权重复指数两个字段，支持自定义列权重，自动处理NA值：

# 1. 指定需要检查的列及对应权重
Check_Col_Names <- c("City" ,"State", "Group1", "Group2", "Group3")
Check_Col_Weights <- c(3,2,3,1,1)

# 2. 复制检查列并添加前缀用于后续计算
df <- cbind(df, 
            rename_with(df[,Check_Col_Names], 
                        .fn = ~paste0("Du_", .), 
                        .cols = Check_Col_Names)
            )

# 3. 分组计算列匹配情况、匹配列数及加权重复指数
df <- df %>% 
  group_by(FirstName, LastName) %>% # 按姓名分组
  mutate(  # 标记每组内该列是否所有值都一致
    across(
      .cols = starts_with("Du_"),
      .fns = ~ length(unique(.x)) == 1
    )
  ) %>%
  ungroup() %>% # 取消姓名分组
  rowwise() %>% # 按行重新分组
  mutate(
    Dupl_QtyCols = sum(c_across(cols = starts_with("Du_")))  # 统计当前行匹配的列数
  ) %>%
  mutate(
    # 计算加权重复指数，权重越高的列匹配对重复度贡献越大
    Dupl_CheckIndex = (across(.cols = starts_with("Du_")) %>% as.matrix()) %*% Check_Col_Weights  
  ) %>%
  ungroup()

# 4. 移除临时计算列
df <- df %>% select(-starts_with("Du_"))
df

方案说明

• 列权重设置：通过Check_Col_Weights可以给更重要的列（比如City、Group1）设置更高权重，这些列匹配时对重复指数的影响更大
• NA值处理：unique()函数会自动识别NA，若组内某列全为NA，也会被标记为匹配列
• 结果字段：
  • Dupl_QtyCols：当前行在组内完全匹配的列数量
  • Dupl_CheckIndex：加权后的重复指数，数值越高说明该行是重复行的可能性越大

内容的提问来源于stack exchange，提问作者J.C.