在R中合并多份Excel文件：按诊断汇总各周数据

我有3个诊所站点的.xlsx格式Excel文件，需要合并为一个可分析所有站点每周诊断总数的DataFrame。具体情况：

• 每个文件对应一个站点，列代表标准化的诊所活动周，但各站点启动时间不同，并非所有周都有数据
• 行代表诊断，各站点有核心相同的诊断，部分站点还有额外诊断

示例数据 #

site1 #

diagnosis     wk2   wk3   wk 4
condition1    1     4     10
condition2          10    4

site2 #

diagnosis     wk1   wk2   wk 3  wk4
condition1    6           4     7
condition2          8     9

site3 #

diagnosis       wk1   wk2   wk 3  wk4
condition1      6           4     7
condition2      8     9
condition3      3     11    11    12

期望结果 #

diagnosis       wk1   wk2   wk 3  wk4
condition1      12    1     12    24
condition2      8     17    19    4
condition3      3     11    11    12

已尝试的代码 #

我试过merge、lapply、rbind、join等组合，最接近的代码如下，但它会让相同诊断按站点重复行：

files <- list.files(path ="data", pattern = "*.xlsx", full.names= T) %>%
  lapply(read_xlsx, sheet =1) %>%
  bind_rows()

解决方案 #

要实现按诊断分组、每周汇总总数的需求，可按以下步骤操作：

完整代码 #

library(tidyverse)
library(readxl)

files <- list.files(path = "data", pattern = "*.xlsx", full.names = TRUE) %>%
  lapply(read_xlsx, sheet = 1) %>%
  bind_rows() %>%
  # 统一周列名，去除空格（解决示例中wk 4/wk4这类命名不一致问题）
  rename_with(~str_replace_all(., "\\s", ""), -diagnosis) %>%
  # 将缺失值替换为0，避免求和时遗漏数据
  mutate(across(-diagnosis, ~replace_na(.x, 0))) %>%
  # 按诊断分组，对所有周列求和
  group_by(diagnosis) %>%
  summarise(across(everything(), sum), .groups = "drop")

关键步骤说明 #

1. 统一列名：rename_with(~str_replace_all(., "\\s", ""), -diagnosis) 会去除周列名中的空格，避免wk 4和wk4被识别为不同列。
2. 处理缺失值：mutate(across(-diagnosis, ~replace_na(.x, 0))) 将空值替换为0，确保未启动站点的周数据在求和时不会被忽略。
3. 分组求和：group_by(diagnosis) %>% summarise(across(everything(), sum)) 按诊断分组，对各周的数值求和，得到所有站点的诊断总数。

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