R语言基于列拆分数据框及解决重复类别标签混淆问题

核心解决思路 #

通过识别数据中全大写的顶层分组标题，自动为所有子项拼接所属分组前缀，支持多层嵌套场景，无需手动修改标签即可完全区分类别相同的子项。 #

步骤1：加载依赖包

library(dplyr)
library(tidyr)
library(writexl)

步骤2：为所有变量生成带层级前缀的唯一标识

poverty_labeled <- poverty %>%
  mutate(
    # 标记顶层分组：所有字符均为大写、空格、&或:的行即为分组标题
    is_top_group = grepl("^[A-Z\\s&:]+$", Variable),
    # 生成分组ID，每遇到一个顶层分组就自增1
    group_id = cumsum(is_top_group)
  ) %>%
  # 按分组处理组内层级
  group_by(group_id) %>%
  mutate(
    # 取当前组的顶层分组名称
    top_group_name = first(Variable),
    # 组内行号，顶层分组自身为第0行
    row_in_group = row_number() - 1,
    # 标记二级分组：非顶层分组，且下一行是Male/Female这类重复标签的行
    is_sub_group = lead(Variable) %in% c("Male", "Female"),
    # 生成二级分组ID
    sub_group_id = cumsum(is_sub_group),
    # 生成带全层级前缀的唯一变量名
    full_variable = case_when(
      # 顶层分组保留原名
      row_in_group == 0 ~ Variable,
      # 二级分组下的子项拼接顶层+二级分组名
      sub_group_id > 0 & !is_sub_group ~ paste0(top_group_name, ".", first(Variable[sub_group_id == cur_group()$sub_group_id]), ".", Variable),
      # 普通子项只拼接顶层分组名
      TRUE ~ paste0(top_group_name, ".", Variable)
    )
  ) %>%
  ungroup()

处理后重复的Male/Female标签会自动变为SEX.Male、EMPLOYMENT STATUS.Employed.Male、EMPLOYMENT STATUS.Unemployed.Male，完全不会混淆。

步骤3：按分组提取数据并转宽表

以提取SEX分组、EMPLOYMENT STATUS分组为例，可封装通用函数批量处理：

# 通用转换函数，输入分组名即可输出对应宽表
get_group_wide <- function(group_name) {
  poverty_labeled %>%
    # 筛选对应分组，排除顶层分组自身的行
    filter(top_group_name == group_name, row_in_group > 0) %>%
    # 用带前缀的变量名替换原Variable列
    select(-Variable) %>%
    rename(Variable = full_variable) %>%
    # 转宽表逻辑和原有逻辑一致
    gather(metric, value, -(Variable:State)) %>%
    unite(col_name, Variable, metric) %>%
    spread(col_name, value)
}

# 调用函数生成对应分组的宽表
sex_wide <- get_group_wide("SEX")
edu_wide <- get_group_wide("EDUCATIONAL ATTAINMENT")
emp_wide <- get_group_wide("EMPLOYMENT STATUS")
workexp_wide <- get_group_wide("WORK EXPERIENCE")

步骤4：导出Excel

注意Windows路径下需要用正斜杠/或者双反斜杠\\避免转义问题：

write_xlsx(sex_wide, "A:/Data Repository clean/2. Social Indicators/Poverty/Poverty by sex 2010-2021.xlsx")
write_xlsx(edu_wide, "A:/Data Repository clean/2. Social Indicators/Poverty/Poverty by educational att 2010-2021.xlsx")

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