如何自动化处理列不规整的Excel数据并导入R语言环境

所需依赖包 #

• tidyverse：包含dplyr、tidyr、purrr等核心数据处理工具
• readxl：用于读取Excel文件内容
• writexl：用于导出合并后的单一Excel文件

单工作表数据规整函数 #

该函数可将你给出的非结构化输入数据转换为符合要求的tidy格式：

clean_pop_sheet <- function(df, file_name, sheet_name) {
  # 移除全空行
  df <- df %>% filter(!if_all(everything(), is.na))
  
  df %>%
    # 识别区域行：第一列为区域名、年份列全为空的行
    mutate(
      region = if_else(is.na(`2011`) & is.na(`2012`), `Age, Sex, Region`, NA_character_),
      # 识别性别：可根据实际数据扩展男性判断逻辑
      sex = if_else(str_detect(`Age, Sex, Region`, "Females"), "F", NA_character_),
      age_group = `Age, Sex, Region`
    ) %>%
    # 向下填充区域、性别到对应数据行
    fill(region, sex, .direction = "down") %>%
    # 过滤掉仅用于标注的区域行
    filter(!is.na(`2011`)) %>%
    # 保留目标字段，补充文件、工作表标识
    select(age_group, region, sex, `2011`, `2012`) %>%
    mutate(
      file_id = str_remove(file_name, "\\.xlsx$"), # 取文件名（去掉后缀）作为file_id
      sheet_id = sheet_name
    )
}

函数效果测试 #

# 示例输入数据
pop <- tibble::tibble(
  'Age, Sex, Region' = c("Rural", "Total (Females)", "0-4", "5-9", NA, "Urban", "Total (Females)", "0-4", "5-9"),
  "2011" = c(NA, 104, 55, 49, NA,NA, 175, 100, 75),
  "2012" = c(NA, 108, 57, 51, NA,NA,181, 104, 77)
)

# 调用函数验证输出
clean_pop_sheet(pop, "regionA.xlsx", "population")

输出结果和你给出的预期格式完全一致。

批量处理所有Excel文件 #

# 1. 提取当前目录下所有区域类Excel文件路径
excel_files <- list.files(pattern = "^region.*\\.xlsx$")

# 2. 定义单个Excel文件的处理逻辑：遍历文件内所有工作表
process_one_file <- function(file_path) {
  sheet_names <- excel_sheets(file_path)
  # 逐个读取工作表并清洗，合并为单文件的总数据
  map_dfr(sheet_names, function(sheet) {
    df <- read_excel(file_path, sheet = sheet)
    clean_pop_sheet(df, file_path, sheet)
  })
}

# 3. 批量处理所有文件，合并为全量数据集
all_data <- map_dfr(excel_files, process_one_file)

导出为单一Excel文件 #

# 按工作表类型拆分全量数据，生成命名列表
output_list <- all_data %>% 
  group_split(sheet_id) %>%
  setNames(unique(all_data$sheet_id))

# 导出为单个Excel文件，原同类工作表合并为新文件的一个sheet
write_xlsx(output_list, "合并后总数据.xlsx")

适配扩展说明 #

• 若存在男性数据，可修改sex字段的判断逻辑，新增str_detect(age_group, "Males") ~ "M"分支即可
• 若年份列不固定，可将select中的固定列名替换为正则匹配，比如matches("^\\d{4}$")自动匹配所有年份列
• 若区域识别规则有变动，调整region字段的判断条件即可适配其他输入格式

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