在R语言中按组清洗填充调查数据的高效方法求助

需求说明 #

处理含拼写错误、缺失值的调查数据，按组执行清洗填充规则：若组内某变量存在唯一有效值（排除指定无效值："Unknown"、"NA"、"N/A"、"null"及拼写错误的类似值），则用该值替换组内该变量的所有其他值。需实现高效批量处理（支持500+变量），同时解决「组内仅单个观测且含缺失值」时的报错问题。

数据示例 #

data <- data.frame(group = c("A1", "A1", "A1", "A1", "A2", "A2", "B4", "B4", "B4", "C1"),
                   number.persons = c("4",NA,NA, "N/A", "unknow", "2", "3", "3", NA,"1"),
                   own.rent = c("own", "own", NA, "N/A", "rent", NA, "own", "N/A", "own", "own"),
                   car      =c("yes", "yes", NA, "unkwon", "no", NA, "no", "no", "unknwon", "no"))

期望清洗结果 #

new.data <- data.frame(group = c("A1", "A1", "A1", "A1", "A2", "A2", "B4", "B4", "B4", "C1"),
                       number.persons = c("4","4","4", "4", "2", "2", "3", "3", "3","1"),
                       own.rent = c("own", "own", "own", "own", "rent", "rent", "own", "own", "own", "own"),
                       car      =c("yes", "yes", "yes", "yes", "no", "no", "no", "no", "no", "no"))

现有方案问题 #

当前手动标记变量的方案需逐个变量编写标记与填充逻辑，面对大量变量时完全不可行：

data.pre <- data %>%
            mutate(flag.number.persons = ifelse(!number.persons %in% c("unknow", "unkwon","unknwon","null", "na", "n/a", "N/A", "NA", "") & !is.na(number.persons), 1, 0),
                   flag.own.rent = ifelse(!own.rent %in% c("unknow", "unkwon","null", "unknwon","na", "n/a", "N/A", "NA", "") & !is.na(own.rent), 1, 0),
                   flag.car      = ifelse(!car %in% c("unknow", "unkwon","null", "unknwon","na", "n/a", "N/A", "NA", "") & !is.na(car), 1, 0)) %>%
            group_by(group) %>%
            mutate(number.persons2 = ifelse(flag.number.persons==0, number.persons[flag.number.persons==1], number.persons),
                   own.rent2      = ifelse(flag.own.rent == 0, own.rent[flag.own.rent==1], own.rent),
                   car2            = ifelse(flag.car == 0 , car[flag.car==1], car))

测试方案的报错问题 #

尝试使用na.locf的批量方案时，会在「组内仅单个观测且变量为缺失值」的场景下报错：

data %>%
 group_by(group) %>%
 mutate(across(everything(), ~ if_else(.x == "N/A", NA_character_, .x)),
 across(everything(), ~ if_else(grepl("^unk", .x), NA_character_, .x))) %>%
 mutate(across(everything(), na.locf))

高效解决方案 #

步骤1：定义可复用的清洗函数 #

clean_group_values <- function(df, group_col, vars_to_clean, invalid_values) {
  # 第一步：将所有无效值转换为NA
  df_clean <- df %>%
    mutate(across(all_of(vars_to_clean), ~ if_else(.x %in% invalid_values | is.na(.x), NA_character_, .x)))
  
  # 第二步：按组批量处理变量
  df_clean %>%
    group_by({{group_col}}) %>%
    mutate(across(all_of(vars_to_clean), ~ {
      # 提取组内非NA的唯一有效值
      valid_vals <- unique(na.omit(.x))
      # 按规则填充：有唯一有效值则全组替换，否则保留原数据（含单观测组的NA）
      if (length(valid_vals) == 1) {
        rep(valid_vals, length(.x))
      } else {
        .x
      }
    })) %>%
    ungroup()
}

步骤2：调用函数执行清洗 #

# 定义无效值列表（可根据实际情况扩展）
invalid_vals <- c("unknow", "unkwon", "unknwon", "null", "na", "n/a", "N/A", "NA", "")

# 指定需要清洗的变量
target_vars <- c("number.persons", "own.rent", "car")

# 执行清洗
result <- clean_group_values(data, group, target_vars, invalid_vals)

# 查看结果
print(result)

方案优势 #

• 批量处理：通过across一次性处理所有目标变量，适配500+变量的场景；
• 灵活配置：可自由指定无效值列表和待清洗变量，适配不同数据格式；
• 边界兼容：自动处理单观测组、无有效值组等特殊场景，避免报错；
• 逻辑严谨：仅当组内存在唯一有效值时才执行替换，符合需求规则。

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