R语言循环批量处理100份Excel文件生成并导出柱状图

问题背景 #

手上有100份格式一致的Excel文件，对应100家不同医院，需要为每家医院生成柱状图，以医院ID命名后导出到指定文件夹。现有代码只能手动修改医院名称后执行，希望通过循环实现批量自动生成。

用户现有参数定义代码：

# Define my list 
OHT_list <- c("Hospital 1")  #I would need to change this for each hospital and run the below code
OHT_ <- c("H1") ##I would need to change this for each hospital and run the below code
SegmentType <- "Segment A" #can be segment A/B/C
Indicator <- "Indicator A"  
Years <- "2022/23"

生成柱状图的核心代码：

if (SegmentType == "Segment A" || SegmentType == "Segment B" || SegmentType=="Segment C") {
  combined_data <- list()
  
  # Find Folder Names to access based on OHT_list elements (numeric values)
  for (OHT in OHT_list) {
    
    ending <- sub(".*?(\\d+).*", "\\1", OHT)
    
    file_name <- paste0("SegmentationResults_", SegmentType, "_", ending, ".xlsx")
    file_path <- file.path("C:\downloads", OHT, file_name)

  
   data <- read_excel(file_path, sheet = "xx")
  
  # Filter out "all" row and save relevant years
  filtered_data <- all_data %>%
    filter(`Reporting Period` == Years & `Segment Label` != "")

  # Colors
  colors_sequence <- c("#0A1A31", "#C7EA95", "#2E80BC", "#EE654F", "#7BB4A9")
  color_map <- rep(colors_sequence, length.out = length(unique(filtered_data$OHT)))
  names(color_map) <- unique(filtered_data$OHT)

  file_name <- paste0("fig_", SegmentType, "_", ending, ".png")
  file_path <- file.path ("C:/downloads/IndicatorA", file_name)
  
  png(file_path, width = 1400, height = 900)

  plot <- filtered_data %>%
   ggplot(aes(x = yy, y = `xx`)) +
  geom_bar(stat="identity", fill="#0A1A31")+
    scale_y_continuous(expand = expansion(mult = c(0, .10)))+
  scale_fill_manual(values = c())+
  coord_flip() +
  theme_minimal(base_size = 35) +
  theme(plot.title = element_text(hjust = 0.5))+
  theme(legend.title = element_blank(), legend.position = "bottom", legend.spacing.y = unit(-0.5, "cm"), legend.text = element_text(size=25))
  
  plot
    
}

  dev.off()

批量处理修改方案 #

1. 先整理完整的医院列表 #

把100家医院的名称和对应ID统一管理，避免手动修改出错：

# 替换成你实际的100家医院名称和ID，保证顺序一一对应
hospital_df <- data.frame(
  name = c("Hospital 1", "Hospital 2", "Hospital 3", ..., "Hospital 100"),
  id = c("H1", "H2", "H3", ..., "H100")
)

2. 重构循环逻辑（完整可运行代码） #

修正原代码中的小问题，改成遍历所有医院的批量处理逻辑：

library(readxl)
library(tidyverse)
library(ggplot2)

# 配置固定参数
SegmentType <- "Segment A" # 可按需改成Segment B/C
Indicator <- "Indicator A"  
Years <- "2022/23"

# 医院列表（替换为你的实际数据）
hospital_df <- data.frame(
  name = c("Hospital 1", "Hospital 2", "Hospital 3"),
  id = c("H1", "H2", "H3")
)

# 校验SegmentType合法性
if (SegmentType %in% c("Segment A", "Segment B", "Segment C")) {
  # 遍历每家医院
  for (i in 1:nrow(hospital_df)) {
    # 提取当前医院的名称和ID
    current_hospital <- hospital_df$name[i]
    current_id <- hospital_df$id[i]
    
    # 从医院名称中提取数字编号
    hospital_num <- sub(".*?(\\d+).*", "\\1", current_hospital)
    
    # 拼接Excel文件路径
    excel_file <- paste0("SegmentationResults_", SegmentType, "_", hospital_num, ".xlsx")
    excel_path <- file.path("C:/downloads", current_hospital, excel_file)
    
    # 读取Excel数据
    raw_data <- read_excel(excel_path, sheet = "xx")
    
    # 过滤数据（修正原代码的all_data为读取的raw_data）
    filtered_data <- raw_data %>%
      filter(`Reporting Period` == Years & `Segment Label` != "")
    
    # 拼接图片输出路径（用医院ID命名）
    fig_file <- paste0("fig_", SegmentType, "_", current_id, ".png")
    fig_path <- file.path("C:/downloads/IndicatorA", fig_file)
    
    # 生成并保存柱状图
    png(fig_path, width = 1400, height = 900)
    
    filtered_data %>%
      ggplot(aes(x = yy, y = `xx`)) +
      geom_bar(stat = "identity", fill = "#0A1A31") +
      scale_y_continuous(expand = expansion(mult = c(0, .10))) +
      coord_flip() +
      theme_minimal(base_size = 35) +
      theme(plot.title = element_text(hjust = 0.5)) +
      theme(legend.title = element_blank(), legend.position = "bottom", 
            legend.spacing.y = unit(-0.5, "cm"), legend.text = element_text(size=25))
    
    dev.off() # 放在循环内，确保每个图生成后关闭绘图设备
    cat("已完成：", fig_file, "\n") # 输出进度，方便跟踪
  }
} else {
  stop("SegmentType参数错误，只能是Segment A/B/C")
}

关键调整说明 #

• 统一医院管理：用数据框绑定医院名称和ID，避免手动修改时的顺序错误
• 修正数据引用：原代码中all_data是笔误，改成实际读取的raw_data
• 路径优化：用file.path()自动适配系统路径分隔符，避免Windows下的转义问题
• 绘图设备管理：把dev.off()移到循环内部，防止多个图共用一个绘图设备导致异常
• 进度提示：添加cat()输出已完成的文件名，批量处理时能实时看到进度

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