如何拆分美国人口普查局HHP数据中的特征与特征类别列？

我使用美国人口普查局家庭脉搏调查（Household Pulse Survey）2020-2022年的最新数据：

• 2022年：wk51的food1_week51.xlsx
• 2021年：wk40的food1_week40.xlsx
• 2020年：wk20的food1_week20.xlsx

已完成的处理步骤：将三个文件的所有工作表合并为一个包含三年数据的DataFrame，同时添加了区域和年份标识列，使用的代码如下：

# 列出文件名
file_names <- c("food_sufficiency_2020.xlsx", "food_sufficiency_2021.xlsx", "food_sufficiency_2022.xlsx")

# 读取并清洗每个工作表的函数
read_and_clean_sheet <- function(file) {

  # 从文件名提取年份
  year <- as.numeric(gsub("\\D", "", file))

  # 读取Excel文件中的所有工作表
  sheets <- readxl::excel_sheets(file)

  # 读取每个工作表并清洗
  dfs <- lapply(sheets, function(sheet) {

    # 读取工作表
    df <- readxl::read_excel(file, sheet = sheet)

    # 删除前5行说明性内容
    df <- df[-(1:5), ]

    # 重命名列
    names(df)[1] <- "Select Characteristics"
    names(df)[2] <- "Total"
    names(df)[3] <- "Enough of the kinds of food wanted"
    names(df)[4] <- "Enough food, but not always the kinds wanted"
    names(df)[5] <- "Sometimes not enough to eat"
    names(df)[6] <- "Often not enough to eat"
    names(df)[7] <- "Did not report"

    # 添加区域列
    df$Region <- sheet

    # 添加年份列
    df$Year <- year

    return(df)
  })

  # 合并同文件下的所有工作表数据
  combined_df <- dplyr::bind_rows(dfs)

  return(combined_df)
}

# 处理所有文件
all_data <- lapply(file_names, read_and_clean_sheet)

# 合并为最终DataFrame
final_df <- dplyr::bind_rows(all_data)

View(final_df)

当前问题：运行代码后，Select Characteristics列同时包含特征类别（如“年龄”）和对应的具体特征（如“18-24岁”），需要将其拆分为特征类别和具体特征两列，明确每行数据的分组归属。

相关数据情况：

• 整理后的数据：Select Characteristics列混合显示类别名称和具体特征值
• 原始Excel数据：类别名称为粗体，对应的具体特征行在类别行下方
• 期望结果：拆分后两列清晰区分，例如“特征类别”为“年龄”时，“具体特征”为“18-24岁”

可以通过dplyr和tidyr包实现列的拆分，核心逻辑是先识别类别行，再向下填充类别值，最后过滤掉无数据的类别行：

library(dplyr)
library(tidyr)

# 拆分特征列并清洗数据
final_df_cleaned <- final_df %>%
  # 识别特征类别行：原始数据中类别行的Total列无数值
  mutate(特征类别 = ifelse(is.na(Total) | Total == "", `Select Characteristics`, NA)) %>%
  # 向下填充类别值，让每个具体特征对应所属类别
  fill(特征类别, .direction = "down") %>%
  # 过滤掉仅包含类别名称的空数据行
  filter(!(is.na(Total) | Total == "")) %>%
  # 重命名原列为具体特征
  rename(具体特征 = `Select Characteristics`) %>%
  # 调整列顺序，让核心信息靠前
  select(Year, Region, 特征类别, 具体特征, everything())

View(final_df_cleaned)

代码说明： #

1. 识别类别行：利用原始数据中“类别行无统计数值”的特点，通过Total列是否为空判断类别行，将类别名称存入新列特征类别
2. 填充类别值：使用fill函数向下传递类别名称，确保每个具体特征行都有对应的类别归属
3. 过滤无效行：移除仅包含类别名称的空数据行，保留有统计数据的特征行
4. 调整结构：重命名列并调整顺序，让数据结构更符合分析需求

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