如何用R将含行式表头的多表Excel转换为整洁数据格式

步骤1：加载所需包 #

先处理Excel和数据清洗的核心依赖包：

library(tidyverse)
library(readxl)

步骤2：读取原始数据 #

读取时保留所有行（包括空行），不自动设置列名，确保能识别表格间的空行分隔符：

# 替换为你的文件路径
raw_data <- read_excel("your_dataset.xlsx", col_names = FALSE)

步骤3：识别并拆分独立表格 #

通过空行标记每个表格的边界，为每个表格分配唯一组ID，实现批量拆分：

# 标记非空行（空行定义为所有列均为NA的行）
raw_data <- raw_data %>%
  mutate(is_non_empty = rowSums(!is.na(.)) > 0)

# 为每个连续非空行块分配组ID，拆分出独立表格
raw_data <- raw_data %>%
  mutate(group_id = cumsum(is_non_empty & lag(is_non_empty, default = FALSE) == FALSE)) %>%
  filter(is_non_empty) # 移除空行，只保留表格内容

步骤4：批量处理每个表格 #

针对不同的行式表头结构，选择对应处理逻辑：

场景A：表格为「表头行 + 多行数据」结构

如果每个表格的第一行是Name/Date等表头，后续是数据行，用以下代码：

tidy_result <- raw_data %>%
  group_by(group_id) %>%
  group_modify(function(df, grp) {
    # 提取组内第一行作为列名
    col_names <- df %>% slice(1) %>% unlist() %>% as.character()
    # 提取剩余行作为数据内容
    data_rows <- df %>% slice(-1)
    # 设置列名并转换为规范格式，自动识别数据类型
    data_rows %>% 
      `colnames<-`(col_names) %>%
      as_tibble() %>%
      type_convert()
  }) %>%
  ungroup() %>%
  select(-group_id) # 移除临时组ID列

场景B：表格为「字段名行 + 对应值行」结构

如果每个表格是每行对应一个字段（第一列是字段名，第二列是对应值），用以下代码转成宽表：

tidy_result <- raw_data %>%
  group_by(group_id) %>%
  group_modify(function(df, grp) {
    # 将行式字段-值对转换为标准宽表
    df %>%
      pivot_wider(names_from = X1, values_from = X2) %>%
      type_convert() # 自动转换日期、数值等数据类型
  }) %>%
  ungroup() %>%
  select(-group_id)

步骤5：按需执行Pivot转换 #

如果需要将日期列转换为长格式（匹配你提到的pivot_longer需求），以日期为维度展开数据：

final_tidy <- tidy_result %>%
  pivot_longer(
    cols = matches("\\d{4}-\\d{2}-\\d{2}"), # 匹配所有日期格式的列名
    names_to = "Date",
    values_to = "Count" # 根据实际字段修改，比如Dead/Alive等
  ) %>%
  mutate(Date = as.Date(Date)) # 确保日期格式正确

注意事项 #

• 确保Excel中的表格分隔空行是完全空白的（所有列无内容），否则分组逻辑会出错
• type_convert()自动识别数据类型，若识别有误可手动用mutate()指定（如mutate(Date = as.Date(Date))）
• 若原始表格表头存在拼写不一致，需先修正原始数据或在代码中统一列名

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