R语言批量处理多Excel工作簿多工作表后按原结构导出方法

需求说明 #

• 指定路径下存储2个Excel工作簿，每个工作簿包含2个工作表
• 读取文件后完成两项处理：将所有数值0替换为NA、计算每列平均值
• 处理后保持原结构（2个工作簿、每个工作簿含2个工作表）导出回原路径

处理规则 #

• 所有工作表内的数值0全部替换为NA
• 每列末尾新增一行，填入该列0转NA后的平均值（即样例中黄色高亮行）
  

注：输出结果中黄色高亮行即为各列完成0转NA操作后计算得到的均值

现有问题 #

已编写初步读取代码，但无法将多个工作表按导入时的顺序导出回对应的原工作簿，现有代码如下：

library(tidyverse)
library(readxl)

path <-  "C:/Users/tme/Desktop/"
file_name <- list.files(pattern = "*.xlsx")

for (i in seq_along(file_name)){
  sheet_nm <- excel_sheets(file_name[i])
  for (j in seq_along(sheet_nm)){
    assign(x = paste0(file_name[i],"_",sheet_nm[j]), value = read_xlsx(path = file_name[i], sheet = sheet_nm[j],col_names=FALSE), envir = .GlobalEnv)
  }
}

实现代码 #

原有代码用assign往全局环境零散创建变量，后续匹配变量和对应工作表、文件的逻辑很容易出错，直接用嵌套列表按原读取顺序存储文件和工作表即可解决顺序错乱问题，完整可运行代码如下：

# 加载依赖，未安装请先运行 install.packages(c("tidyverse","readxl","writexl"))
library(tidyverse)
library(readxl)
library(writexl)

# 配置目标文件夹路径
file_path <- "C:/Users/tme/Desktop/"
# 获取路径下所有xlsx格式文件的完整路径
all_files <- list.files(path = file_path, pattern = "*.xlsx", full.names = TRUE)

# 遍历处理每个文件
walk(all_files, ~{
  # 按原文件顺序读取所有工作表名称
  current_sheet_names <- excel_sheets(.x)
  # 逐表处理，结果按原工作表顺序存入列表
  processed_data <- map(current_sheet_names, function(sheet){
    # 读入单个工作表
    raw_df <- read_xlsx(path = .x, sheet = sheet, col_names = FALSE)
    # 将所有值为0的单元格替换为NA
    raw_df[raw_df == 0] <- NA
    # 计算每列均值（自动忽略NA），追加到表末尾
    col_mean <- map_dfr(raw_df, ~mean(.x, na.rm = TRUE))
    final_df <- bind_rows(raw_df, col_mean)
    return(final_df)
  })
  # 给处理后的工作表绑定原表名，保证导出时名称、顺序完全匹配原文件
  names(processed_data) <- current_sheet_names
  # 导出文件：直接写回原路径会覆盖原文件，测试阶段建议修改输出文件名，比如加_processed后缀
  write_xlsx(x = processed_data, path = .x)
  # 测试阶段可用下行代码替代上一行，生成带后缀的新文件避免覆盖原数据
  # write_xlsx(x = processed_data, path = str_replace(.x, "\\.xlsx$", "_processed.xlsx"))
})

注意事项 #

• 首次运行建议使用带_processed后缀的输出路径，确认处理结果符合预期后再开启覆盖原文件的逻辑
• 均值计算默认自动忽略NA值，符合0转NA后计算列均值的要求
• 列表存储全程保留原文件、原工作表的读取顺序，不会出现工作表和文件匹配错乱的问题

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