在R中批量导入Excel：动态提取住院及ICU患者总计值求助

问题背景 #

作为R中高级用户，需要批量读取数百份Excel文件，提取「Total Inpatients」和「Inpatients in ICU Wards」的总计值。但每日医院数量变化导致总计值所在单元格不固定，现有代码仅提取到单家医院的数值（示例中返回4和0，正确值应为102和5），需修正代码逻辑。

原代码核心问题 #

原代码使用which.max(rowSums(sapply(df[, c(3,5)], as.numeric), na.rm = TRUE) > 0)，该逻辑会返回第一个满足行和大于0的行索引，即第一家医院的数据行，而非所有医院数据之后的总计行。

修正后的代码 #

library(readxl)
library(dplyr)
library(purrr)
library(stringr)

# 定义目录路径和文件模式
directory <- "My path"
file_pattern <- 'My file pattern'

# 列出符合模式的文件
files <- list.files(path = directory, pattern = file_pattern, full.names = TRUE, recursive = TRUE)

# 提取总计值的函数
extract_totals <- function(file_path) {
  # 读取Excel文件，不设置列名
  df <- readxl::read_excel(file_path, col_names = FALSE)
  
  # 提取日期：从第3行第1列提取，转成字符后匹配日期格式
  date_string <- as.character(df[3, 1])
  date_value <- str_extract(date_string, "\\d{2}/\\d{2}/\\d{4}")
  
  # 定位Total Inpatients的表头位置
  total_inp_loc <- which(df == "Total Inpatients", arr.ind = TRUE)
  if (nrow(total_inp_loc) == 0) {
    return(data.frame(Date = date_value, Total_Inpatients = NA, Inpatients_ICU_Wards = NA))
  }
  total_inp_row <- total_inp_loc[,1]
  total_inp_col <- total_inp_loc[,2]
  
  # 定位Inpatients in ICU Wards的表头位置（处理换行符差异）
  icu_loc <- which(str_detect(df, "^Inpatients in.*ICU Wards$"), arr.ind = TRUE)
  if (nrow(icu_loc) == 0) {
    return(data.frame(Date = date_value, Total_Inpatients = NA, Inpatients_ICU_Wards = NA))
  }
  icu_col <- icu_loc[,2]
  
  # 在Total Inpatients列中，找到表头之后最后一个非NA数值行
  total_values <- as.numeric(df[(total_inp_row + 1):nrow(df), total_inp_col])
  valid_total_rows <- which(!is.na(total_values))
  if (length(valid_total_rows) == 0) {
    total_inpatients <- NA
    inpatients_icu <- NA
  } else {
    total_row <- max(valid_total_rows) + total_inp_row
    total_inpatients <- as.numeric(df[total_row, total_inp_col])
    inpatients_icu <- as.numeric(df[total_row, icu_col])
  }
  
  return(data.frame(Date = date_value, Total_Inpatients = total_inpatients, Inpatients_ICU_Wards = inpatients_icu))
}

# 批量处理所有文件并合并结果
results <- map_dfr(files, extract_totals) |>
  arrange(Date)

关键修正点 #

• 动态表头定位：不再依赖固定列索引（如原代码的第3、5列），通过精准匹配表头文本定位列位置，适配表格结构变化
• 总计行精准提取：在表头下方的数值区域中，找到最后一个非NA数值行，确保提取的是所有医院数据后的总计值
• 异常处理：加入表头缺失、无有效数值的判断逻辑，避免函数报错，返回NA便于后续数据排查

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