如何用R处理含重复/变更表头的不规则Excel数据集并生成整洁表格

我有多个公共Excel数据集，这些数据集的表头会以不规则的行数间隔重复出现或变更，示例如下：

[原示例表格结构]
第一列中的省略号(...)代表更多行。

我需要将此类Excel文件整理为整洁表格，格式如下：

[目标整洁表格结构]

我尝试在unpivotr和tidyxl包中查找相关处理案例，但没找到合适的示例，恳请提供解决方案，谢谢！

补充：原始数据集片段 #

structure(list(`Industry and company size` = c(NA, "All industries", 
"Manufacturing industries", "Food", "Beverage and tobacco products", 
"Textiles, apparel, and leather products", "Wood products", "Paper", 
"Printing and related support activities", "Petroleum and coal products", 
"Chemicals", "Basic chemicals", "Resin, synthetic rubber, fibers, and filaments", 
"Pesticide, fertilizer, and other agricultural chemical", "Pharmaceuticals and medicines", 
"Soap, cleaning compound, and toilet preparation", "Paint, coating, adhesive, and other chemical", 
"Plastics and rubber products", "Nonmetallic mineral products", 
"Primary metals", "100–249", "250–499", "Medium and large companies (number of employees)", 
"500–999", "1,000–4,999", "5,000–9,999", "10,000–24,999", 
"25,000 or more", "Industry and company size", NA, "All industries", 
"Manufacturing industries", "Food", "Beverage and tobacco products", 
"Textiles, apparel, and leather products", "Wood products", "Paper", 
"Printing and related support activities", "Petroleum and coal products", 
"Chemicals", "Basic chemicals", "Resin, synthetic rubber, fibers, and filaments"
), `NAICS code` = c(NA, "21–23, 31–33, 42–81", "31–33", 
"311", "312", "313–316", "321", "322", "323", "324", "325", 
"3251", "3252", "3253", "3254", "3256", "3255, 3259", "326", 
"327", "331", "–", "–", NA, "–", "–", "–", "–", "–", 
"NAICS code", NA, "21–23, 31–33, 42–81", "31–33", "311", 
"312", "313–316", "321", "322", "323", "324", "325", "3251", 
"3252"), Companies = c(NA, "48393", "25408", "1704", "83", "1097", 
"255", "166", "637", "131", "3064", "416", "136", "37", "1471", 
"373", "631", "1253", "617", "226", "4516", "1551", NA, "794", 
"912", "187", "134", "105", "Colorado", NA, "3270", "2344", "43", 
"5", "*", "*", "*", "5", "10", "245", "22", "*"), ...4 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, "e", "e", "e", NA, NA, NA, NA, "e"), `United States` = c(NA, 
"221706", "159579", "3659", "717", "478", "229", "1247", "201", 
"1104", "53555", "1718", "807", "760", "45398", "2383", "2491", 
"1809", "1246", "579", "9435", "8413", NA, "8640", "39468", "21940", 
"36688", "74148", "Connecticut", NA, "5482", "5117", "9", "*", 
"D", "*", "D", "D", "D", "3690", "60", "3"), ...6 = c(NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "i", NA, NA, NA, 
NA, "i", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, "e", NA, "e", NA, NA, NA, NA, NA, NA), Alabama = c(NA, "628", 
"358", "5", "*", "2", "4", "4", "*", "*", "89", "2", "3", "3", 
"80", "*", "2", "10", "2", "8", "73", "56", NA, "D", "187", "59", 
"26", "D", "Delaware", NA, "D", "D", "D", "D", "7", "D", "D", 
"D", "D", "177", "7", "D"), ...8 = c(NA, NA, NA, "e", "e", "e", 
NA, NA, "e", "e", NA, "e", NA, NA, NA, "e", NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "e", NA, 
NA, NA, NA, NA, NA, NA), Alaska = c(NA, "D", "D", "2", "D", "*", 
"D", "*", "*", "*", "*", "0", "0", "0", "*", "*", "*", "*", "*", 
"*", "3", "D", NA, "D", "5", "*", "*", "1", "District of Columbia", 
NA, "84", "20", "*", "1", "*", "0", "0", "*", "0", "9", "0", 
"0"), ...10 = c(NA, NA, NA, "e", NA, "e", NA, "e", "e", "e", 
NA, NA, NA, NA, NA, "e", NA, "e", "e", "e", "e", NA, NA, NA, 
NA, "e", NA, NA, NA, NA, NA, NA, NA, "e", NA, "e", "e", "e", 
"e", NA, NA, NA), Arizona = c(NA, "2841", "2442", "4", "*", "*", 
"*", "2", "1", "*", "153", "*", "*", "5", "116", "29", "2", "158", 
"1", "2", "69", "66", NA, "94", "437", "347", "198", "1352", 
"Florida", NA, "3045", "1697", "16", "3", "4", "1", "1", "2", 
"1", "455", "7", "12"), ...12 = c(NA, NA, NA, "e", "e", "e", 
"e", NA, NA, "e", NA, "e", "e", "i", NA, NA, "e", NA, "e", NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "e", 
NA, "e", NA, NA, NA, NA), Arkansas = c(NA, "245", "166", "40", 
"*", "*", "2", "D", "D", "D", "14", "4", "*", "3", "6", "1", 
"*", "5", "1", "6", "D", "D", NA, "D", "18", "34", "D", "D", 
"Georgia", NA, "2931", "1599", "D", "D", "D", "D", "84", "D", 
"D", "249", "40", "26"), ...14 = c(NA, NA, NA, NA, "e", "e", 
NA, NA, NA, NA, NA, NA, "e", NA, NA, NA, "e", NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA), California = c(NA, "53327", "35918", "152", 
"14", "70", "14", "19", "11", "72", "10050", "125", "5", "36", 
"9626", "171", "85", "97", "21", "12", "2422", "2469", NA, "2574", 
"11456", "6609", "9948", "12525", "Hawaii", NA, "173", "119", 
"D", "D", "*", "D", "D", "*", "2", "45", "0", "0"), ...16 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, "i", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, "e", NA, NA, "e", NA, "i", NA, NA)), row.names = c(NA, 
-42L), class = c("tbl_df", "tbl", "data.frame"))

用tidyverse工具链可以高效处理这类不规则结构的数据，步骤如下：

1. 标记分段表头行 #

观察数据可知，重复表头的第一列值为Industry and company size，以此为标记拆分数据段：

library(tidyverse)

# 加载数据集（替换为你的数据框）
df <- structure(...)

# 为每个数据段分配唯一ID
df <- df %>%
  mutate(segment_id = cumsum(`Industry and company size` == "Industry and company size"))

2. 提取每个分段的地区表头 #

每个表头行包含对应地区名称（如United States、Colorado），提取后映射到对应数据列：

# 提取各分段的地区表头信息
header_map <- df %>%
  filter(`Industry and company size` == "Industry and company size") %>%
  select(segment_id, everything()) %>%
  pivot_longer(cols = -segment_id, names_to = "orig_col", values_to = "region") %>%
  filter(!is.na(region))

# 将地区信息合并到原数据
df_merged <- df %>%
  filter(`Industry and company size` != "Industry and company size") %>%
  left_join(header_map, by = "segment_id")

3. 转换为整洁长格式 #

把宽格式数据转为标准长格式，同时清理无效标记：

# 整理成整洁格式
df_tidy <- df_merged %>%
  select(segment_id, `Industry and company size`, `NAICS code`, region, value = orig_col) %>%
  # 过滤空行和无效列
  filter(!is.na(`Industry and company size`) & !is.na(value)) %>%
  # 清理特殊标记（*、D、e、i等，按需调整）
  mutate(value = case_when(
    value %in% c("*", "D", "e", "i", "–") ~ NA_character_,
    TRUE ~ value
  )) %>%
  # 转换数值类型（可选）
  mutate(value = as.numeric(value)) %>%
  # 重命名列名并移除临时ID
  rename(industry = `Industry and company size`, naics_code = `NAICS code`) %>%
  select(-segment_id)

4. 处理分组行（可选） #

针对Medium and large companies (number of employees)这类分组行，可添加标记区分：

df_tidy <- df_tidy %>%
  mutate(is_group = str_detect(industry, "Medium and large companies"))

最终效果 #

处理后的数据结构如下（示例）：

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