如何自动处理跨两行的表格列标签？R语言数据清洗求助

以下是自动化处理原始表格、生成目标格式的步骤，全程无需手动编辑列标签：

1. 加载依赖包 #

使用tidyverse工具集处理数据：

library(tidyverse)

2. 读取原始数据 #

用你提供的代码读取CSV，这里模拟数据方便演示：

# 实际使用：reprex <- read_csv("reprex.csv", col_names = FALSE)
reprex <- tibble(
  X1 = c(NA, NA, "SiteA", "SiteB", "SiteC"),
  X2 = c(NA, "Total", "180", NA, "237"),
  X3 = c("2014", "F", "92", NA, "194"),
  X4 = c(NA, "M", "88", NA, "43"),
  X5 = c(NA, "Total", "134", "247", "220"),
  X6 = c("2015", "F", "40", "143", "95"),
  X7 = c(NA, "M", "94", "104", "125"),
  X8 = c(NA, "Total", "34", "8", "62"),
  X9 = c("2016", "F", "20", "8", "45"),
  X10 = c(NA, "M", "14", "0", "17")
)

3. 生成目标列名 #

提取前两行的年份和类别信息，自动组合成2014 Total这类格式的列名：

# 提取前两行作为列名模板并转置
col_template <- reprex %>% 
  slice(1:2) %>% 
  t() %>% 
  as_tibble() %>% 
  rename(year = V1, category = V2)

# 填充每组缺失的年份（年份出现在每组中间列，用前后填充补全）
col_template <- col_template %>%
  mutate(year = ifelse(is.na(year), lag(year), year)) %>%
  mutate(year = ifelse(is.na(year), lead(year), year))

# 组合年份和类别，第一列命名为Location
col_names <- col_template %>%
  mutate(col_name = case_when(
    row_number() == 1 ~ "Location",
    TRUE ~ paste(year, category)
  )) %>%
  pull(col_name)

4. 整理最终数据 #

提取有效数据行，设置新列名并转换数值类型：

clean_data <- reprex %>%
  slice(-1:-2) %>%  # 移除前两行的列标签行
  set_names(col_names) %>%  # 应用新列名
  mutate(across(-Location, as.numeric))  # 将数据列转为数值型

执行后clean_data就是你需要的目标格式：

> clean_data
# A tibble: 3 × 10
  Location `2014 Total` `2014 F` `2014 M` `2015 Total` `2015 F` `2015 M` `2016 Total` `2016 F` `2016 M`
  <chr>           <dbl>    <dbl>    <dbl>        <dbl>    <dbl>    <dbl>        <dbl>    <dbl>    <dbl>
1 SiteA             180       92       88          134       40       94           34       20       14
2 SiteB              NA       NA       NA          247      143      104            8        8        0
3 SiteC             237      194       43          220       95      125           62       45       17

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