R导入带前置元数据表头的Excel文件并规整为规范数据集的方法咨询

规则适配逻辑 #

所有处理逻辑完全匹配文件结构特性，无需硬编码固定行数，自动适配不同文件的内容差异：

• 自动识别开头键值对形式的元数据（GROUP、MONTH），提取后匹配到每一条人员数据上
• 动态定位正式列名行（即第一列为PERSON的行），不受开头空行、元数据行数差异的影响
• 自动跳过数据区的空行
• 动态定位末尾Total开头的汇总行，无论汇总行和数据区间隔多少空行，都不会被读入数据集
• 适配不同文件人员行数不固定的场景

代码实现 #

首先加载需要的依赖包：

# 安装依赖（首次运行执行）
# install.packages(c("readxl", "dplyr"))
library(readxl)
library(dplyr)

编写单文件解析函数：

parse_excel_file <- function(file_path) {
  # 无表头读入全表内容，定位关键行位置
  full_content <- read_excel(
    path = file_path,
    col_names = FALSE,
    .name_repair = "unique"
  ) |> pull(1)
  
  # 去掉单元格前后空格再匹配，避免格式误差
  clean_content <- trimws(full_content)
  # 动态计算关键行索引
  header_line_idx <- which(clean_content == "PERSON")[1]
  total_line_idx <- which(clean_content == "Total")[1]

  # 提取开头的键值对元数据
  meta_info <- full_content[1:(header_line_idx - 1)] |>
    strsplit(split = ";", fixed = TRUE) |>
    lapply(\(item) setNames(trimws(item[2]), trimws(item[1]))) |>
    unlist()

  # 读取有效数据区域
  valid_data <- read_excel(
    path = file_path,
    skip = header_line_idx - 1,
    n_max = total_line_idx - header_line_idx - 1,
    col_names = c("PERSON", "SEX", "AGE", "INCOME"),
    na = c("", "NA")
  )

  # 拼接元数据并调整列顺序
  valid_data |>
    mutate(
      GROUP = meta_info["GROUP"],
      MONTH = meta_info["MONTH"]
    ) |>
    select(GROUP, MONTH, PERSON, SEX, AGE, INCOME)
}

批量读取同文件夹下所有同结构文件并合并：

# 替换为你的文件存放路径
file_path_dir <- "你的Excel文件所在文件夹路径"
all_files <- list.files(
  path = file_path_dir,
  pattern = "\\.(xlsx|xls)$",
  full.names = TRUE
)

# 合并所有文件的解析结果
final_clean_dataset <- lapply(all_files, parse_excel_file) |>
  bind_rows()

适配说明 #

• 如果你的文件本质是分号分隔的CSV格式，仅需将read_excel替换为readr::read_delim，指定delim = ";"即可，核心逻辑完全不变
• 读入时自动将空单元格识别为NA，全空的行不会进入最终数据集
• 元数据通过键值名匹配，哪怕不同文件的元数据顺序调换，也能正确对应取值

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