如何将多个data.frame生成汇总表格并导出至同一Excel工作表

首先生成汇总数据：我们需要将两个数据框按TYPE分组，对COLUMN A和COLUMN B分别求和，你可以用以下两种方式实现：

方式1：Base R生成汇总表 #

# 合并两个数据框
combined_df <- rbind(DATAFRAME1, DATAFRAME2)
# 按TYPE分组求和
summary_df <- aggregate(. ~ TYPE, data = combined_df, sum)
# 重命名列以体现汇总属性
names(summary_df) <- c("TYPE", "COLUMN A 汇总", "COLUMN B 汇总")

方式2：tidyverse生成汇总表 #

如果习惯使用dplyr工具包，代码如下：

library(dplyr)
summary_df <- bind_rows(DATAFRAME1, DATAFRAME2) %>%
  group_by(TYPE) %>%
  summarize(
    `COLUMN A 汇总` = sum(` COLUMN A`),
    `COLUMN B 汇总` = sum(` COLUMN B`)
  ) %>%
  ungroup()

接下来把汇总表整合到你现有的Excel导出代码中，在写完两个原始数据框后，继续写入汇总表：

完整代码 #

library(openxlsx)

# 定义原始数据框
DATAFRAME1= data.frame(TYPE =c("X","Y"), 
                       ` COLUMN A`=c(1000, 4000),
                       ` COLUMN B`=c(9000,2000))

DATAFRAME2 = data.frame(TYPE =c("X","Y"), 
                        ` COLUMN A`=c(8000,2000),
                        ` COLUMN B`=c(5000,10000))

# 生成汇总表（此处采用Base R方式）
combined_df <- rbind(DATAFRAME1, DATAFRAME2)
summary_df <- aggregate(. ~ TYPE, data = combined_df, sum)
names(summary_df) <- c("TYPE", "COLUMN A 汇总", "COLUMN B 汇总")

# 导出到Excel
df_list <- list("DATAFRAME 1" = DATAFRAME1,
                "DATAFRAME 2" = DATAFRAME2)

wb <- createWorkbook()
addWorksheet(wb, "Foo")

curr_row <- 1
for(i in seq_along(df_list)) {
  writeData(wb, "Foo", names(df_list)[i], startCol = 1, startRow = curr_row)
  writeData(wb, "Foo", df_list[[i]], startCol = 1, startRow = curr_row+1)
  curr_row <- curr_row + nrow(df_list[[i]]) + 2
}

# 写入汇总表
writeData(wb, "Foo", "汇总表", startCol = 1, startRow = curr_row)
writeData(wb, "Foo", summary_df, startCol = 1, startRow = curr_row+1)

saveWorkbook(wb, "WB.xlsx", overwrite=TRUE)

这段代码会在两个原始数据框下方生成按TYPE汇总的表格，匹配你需要的效果。

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