在R中如何按组求和列并排除指定类别后添加汇总行

现有如下R数据框：

data <- data.frame(tag = c("01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE"),
                   recorte_niveis = c("_NÃO INFORMADO", "_NÃO INFORMADO", "ARTS & HUMANITIES", "ARTS & HUMANITIES", "ENGINEERING & TECHNOLOGY","ENGINEERING & TECHNOLOGY", "UFRJ", "UFRJ"),
                   ano_base = c(2021, 2021, 2021, 2021, 2021, 2021,2021, 2021),
                   TURNO = c("INTEGRAL", "PARCIAL", "INTEGRAL", "PARCIAL", "INTEGRAL", "PARCIAL", "INTEGRAL", "PARCIAL"),
                   Valor = c(5, 6, 10, 20, 2, 5, 17, 31))

需要实现：

• 按TURNO分组，排除recorte_niveis为_NÃO INFORMADO的记录后对Valor求和
• 添加两行recorte_niveis为UFRJ2的汇总行，最终得到如下结构的数据框：

data <- data.frame(tag = c("01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE", "01 - Number_of_academic_staff_FTE"),
                   recorte_niveis = c("_NÃO INFORMADO", "_NÃO INFORMADO", "ARTS & HUMANITIES", "ARTS & HUMANITIES", "ENGINEERING & TECHNOLOGY","ENGINEERING & TECHNOLOGY", "UFRJ", "UFRJ", "UFRJ2", "UFRJ2"),
                   TURNO = c("INTEGRAL", "PARCIAL", "INTEGRAL", "PARCIAL", "INTEGRAL", "PARCIAL", "INTEGRAL", "PARCIAL", "INTEGRAL", "PARCIAL"), 
                   ano_base = c(2021, 2021, 2021, 2021, 2021, 2021,2021, 2021, 2021, 2021),
                   Valor = c(5, 6, 10, 20, 2, 5, 17, 31, 12, 25))

方法1：使用dplyr包（推荐，语法简洁） #

如果未安装dplyr，先执行install.packages("dplyr")安装：

library(dplyr)

# 生成汇总行
summary_rows <- data %>%
  # 排除不需要的类别
  filter(recorte_niveis != "_NÃO INFORMADO") %>%
  # 按TURNO分组求和
  group_by(TURNO) %>%
  summarise(Valor = sum(Valor), .groups = "drop") %>%
  # 补充其他字段
  mutate(
    tag = unique(data$tag),
    recorte_niveis = "UFRJ2",
    ano_base = unique(data$ano_base)
  ) %>%
  # 匹配原数据的列顺序
  select(tag, recorte_niveis, ano_base, TURNO, Valor)

# 合并原数据与汇总行
result <- bind_rows(data, summary_rows)

# 查看结果
print(result)

方法2：使用基础R（无需额外包） #

# 过滤数据并按TURNO计算求和值
sum_vals <- aggregate(Valor ~ TURNO, data = subset(data, recorte_niveis != "_NÃO INFORMADO"), sum)

# 构造汇总行数据框
summary_rows <- data.frame(
  tag = rep(unique(data$tag), nrow(sum_vals)),
  recorte_niveis = rep("UFRJ2", nrow(sum_vals)),
  ano_base = rep(unique(data$ano_base), nrow(sum_vals)),
  TURNO = sum_vals$TURNO,
  Valor = sum_vals$Valor
)

# 合并数据
result <- rbind(data, summary_rows)

# 查看结果
print(result)

两种方法最终都会生成符合需求的数据框。

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