如何为含数值与非数值数据的DataFrame添加总计行？

我有多个包含数值与非数值数据的DataFrame，需要在使用kable输出前为它们添加总计行。目前通过创建临时数据框并使用add_row的方式实现，但代码冗长，希望找到更优雅的解决方案。

示例数据 #

apps_combined <- data.frame(
  UAA_mau = rep("UAA", 4L),
  UAA_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAA_2022 = c(65L, 2476L, 894L, 150L),
  UAA_2023 = c(68L, 2691L, 1145L, 164L),
  UAA_percent = c(4.61538461538462, 8.68336025848142, 28.076062639821, 9.33333333333333),
  UAF_mau = rep("UAF", 4L),
  UAF_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAF_2022 = c(19L, 1165L, 281L, 304L),
  UAF_2023 = c(38L, 1161L, 318L, 269L),
  UAF_percent = c(100, -0.343347639484979, 13.1672597864769, -11.5131578947368),
  UAS_mau = rep("UAS", 4L),
  UAS_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAS_2022 = c(12L, 328L, 110L, 39L),
  UAS_2023 = c(8L, 327L, 133L, 33L),
  UAS_percent = c(-33.3333333333333, -0.304878048780488, 20.9090909090909, -15.3846153846154),
  UAT_mau = rep("UAT", 4L),
  UAT_type_ind = c(
    "All UA Scholars", "All Undergraduates", "First-Time Freshmen",
    "Graduates"
  ),
  UAT_2022 = c(96L, 3969L, 1285L, 493L),
  UAT_2023 = c(114L, 4179L, 1596L, 466L),
  UAT_percent = c(18.75, 5.29100529100529, 24.2023346303502, -5.47667342799189)
)

原冗长实现 #

temp <- colSums(apps_combined[,c("UAA_2022", "UAA_2023", "UAF_2022", "UAF_2023", "UAS_2022", "UAS_2023", "UAT_2022", "UAT_2023")])
apps_combined_test <- apps_combined %>%
    add_row("UAA_mau" = "UAA","UAA_type_ind" = "total", "UAA_2022" = temp[[1]], "UAA_2023" = temp[[2]], "UAA_percent" = ((temp[[2]]-temp[[1]])/temp[[1]]) * 100, 
                 "UAF_mau" = "UAF","UAF_type_ind" = "total", "UAF_2022" = temp[[3]], "UAF_2023" = temp[[4]], "UAF_percent" = ((temp[[4]]-temp[[3]])/temp[[3]]) * 100,
                 "UAS_mau" = "UAS","UAS_type_ind" = "total", "UAS_2022" = temp[[5]], "UAS_2023" = temp[[6]], "UAS_percent" = ((temp[[6]]-temp[[5]])/temp[[5]]) * 100,
                 "UAT_mau" = "UAT","UAT_type_ind" = "total", "UAT_2022" = temp[[7]], "UAT_2023" = temp[[8]], "UAT_percent" = ((temp[[8]]-temp[[7]])/temp[[7]]) * 100)

优化方案1：利用列名规律批量生成总计行 #

基于列名的{学校前缀}_{字段}规律，批量生成总计行内容，无需手动逐个编写字段：

library(dplyr)
library(purrr)

# 提取所有学校前缀
schools <- unique(sub("_.*", "", colnames(apps_combined)))

# 批量构建总计行的每个字段
total_row <- map_dfc(schools, function(school) {
  col_2022 <- paste0(school, "_2022")
  col_2023 <- paste0(school, "_2023")
  
  sum_2022 <- sum(apps_combined[[col_2022]])
  sum_2023 <- sum(apps_combined[[col_2023]])
  percent <- ((sum_2023 - sum_2022)/sum_2022)*100
  
  tibble(
    !!paste0(school, "_mau") := school,
    !!paste0(school, "_type_ind") := "total",
    !!col_2022 := sum_2022,
    !!col_2023 := sum_2023,
    !!paste0(school, "_percent") := percent
  )
})

# 添加总计行到原数据框
apps_combined_optimized <- apps_combined %>%
  add_row(total_row)

优化方案2：重塑数据结构（长格式处理再转回宽格式） #

如果后续需要多次统计操作，将宽格式数据转为长格式处理会更灵活：

library(dplyr)
library(tidyr)

# 转成长格式：每个学校的信息单独成行
apps_long <- apps_combined %>%
  pivot_longer(
    cols = everything(),
    names_to = c("school", ".value"),
    names_pattern = "(UAA|UAF|UAS|UAT)_(.*)"
  )

# 计算总计行
total_long <- apps_long %>%
  group_by(school) %>%
  summarise(
    mau = first(mau),
    type_ind = "total",
    `2022` = sum(`2022`),
    `2023` = sum(`2023`),
    percent = ((`2023` - `2022`)/`2022`)*100
  ) %>%
  ungroup()

# 合并数据并转回宽格式
apps_combined_optimized <- bind_rows(apps_long, total_long) %>%
  pivot_wider(
    names_from = school,
    values_from = c(mau, type_ind, `2022`, `2023`, percent),
    names_glue = "{school}_{.value}"
  )

封装成可复用函数 #

如果需要多次使用该逻辑，可以将优化方案1封装为函数：

add_total_row <- function(df, school_prefixes) {
  total_row <- purrr::map_dfc(school_prefixes, function(school) {
    col_2022 <- paste0(school, "_2022")
    col_2023 <- paste0(school, "_2023")
    
    sum_2022 <- sum(df[[col_2022]])
    sum_2023 <- sum(df[[col_2023]])
    percent <- ((sum_2023 - sum_2022)/sum_2022)*100
    
    tibble(
      !!paste0(school, "_mau") := school,
      !!paste0(school, "_type_ind") := "total",
      !!col_2022 := sum_2022,
      !!col_2023 := sum_2023,
      !!paste0(school, "_percent") := percent
    )
  })
  
  df %>% add_row(total_row)
}

# 调用函数
apps_combined_optimized <- add_total_row(apps_combined, c("UAA", "UAF", "UAS", "UAT"))

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