基于R的历史Excel数据集高级整理：处理异常格式与空白值

核心步骤 #

• 批量读取所有采样点的Excel文件及年度工作表
• 向下填充重复信息的空值
• 识别并标记5/10分钟采样时段
• 整合所有数据为规范单表

代码实现 #

先加载所需工具包：

library(tidyverse)
library(readxl)

1. 批量读取所有文件和工作表 #

假设所有采样点的Excel文件都存在./sampling_data/目录下，批量读取所有文件的所有工作表：

# 获取所有Excel文件路径
file_paths <- list.files("./sampling_data/", pattern = "*.xlsx", full.names = TRUE)

# 定义读取单个文件所有工作表的函数
read_all_sheets <- function(file_path) {
  sheet_names <- excel_sheets(file_path)
  map_dfr(sheet_names, function(sheet) {
    read_excel(file_path, sheet = sheet) %>%
      # 新增采样点（从文件名提取）和年度（从工作表名提取）列
      mutate(
        采样点 = str_remove(basename(file_path), ".xlsx"),
        年度 = as.integer(sheet),
        .before = 1
      )
  })
}

# 读取所有原始数据
raw_data <- map_dfr(file_paths, read_all_sheets)

2. 填充重复信息的空值 #

原数据中同一日期/样方的重复信息留空，用fill()向下补全：

filled_data <- raw_data %>%
  # 按采样点、年度分组，填充日期、样方ID等空值（根据你的实际列名调整）
  group_by(采样点, 年度) %>%
  fill(日期, 样方ID, .direction = "down") %>%
  ungroup()

3. 标记采样时段 #

根据“10 mins”行标记区分时段，先定位标记行的位置，再给前后行打标签：

final_data <- filled_data %>%
  group_by(采样点, 年度, 日期, 样方ID) %>%
  mutate(
    # 找到"10 mins"所在行的索引
    ten_min_pos = which(str_detect(行标记列名, "10 mins")),
    # 标记时段：标记行之前是5分钟，之后是10分钟
    采样时段 = case_when(
      row_number() < ten_min_pos ~ "5分钟时段",
      row_number() >= ten_min_pos ~ "10分钟时段",
      TRUE ~ NA_character_
    )
  ) %>%
  # 移除"10 mins"标记行（不需要保留的话）
  filter(!str_detect(行标记列名, "10 mins")) %>%
  ungroup()

4. 整理为规范单表 #

调整列顺序，清理冗余列：

final_data <- final_data %>%
  select(采样点, 年度, 日期, 样方ID, 采样时段, 观测列1, 观测列2) # 替换为你的实际观测列名

注意事项 #

• 请把代码中的行标记列名、观测列1等替换成你数据里的真实列名
• 如果“10 mins”的标记格式有变动，修改str_detect()的匹配规则即可
• 若部分文件格式不一致，可在read_all_sheets里加tryCatch做错误处理

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