如何按区域汇总生成全行业总计行？（R语言tidyverse）

示例数据代码 #

library(tidyverse)

area <- c("003","003","003","003","003","003","003","003","017","017","017","017","017","017","017","017")
year <- c("2022","2022","2022","2022","2022","2022","2022","2022","2022","2022","2022","2022","2022","2022","2022","2022")
period <- c("01","01","01","01","02","02","02","02","01","01","01","01","02","02","02","02")
naics <- c("231","331","341","421","231","331","341","421","231","331","341","421","231","331","341","421")
m1 <- c(100,105,110,152,102,107,112,155,42,45,52,61,39,47,55,100)
m2 <- c(101,106,111,153,103,108,111,156,40,44,53,62,40,48,56,98)
m3 <- c(102,107,112,155,104,109,112,157,43,46,55,63,41,49,57,95)

first <- data.frame(area,year,period, naics,m1,m2,m3)

# 注：原代码里的qtr是笔误，应该用period；且mean(m1:m3)写法错误，计算行均值需用rowMeans
first <- first %>% group_by(area,year,period, naics) %>% mutate(avgemp = rowMeans(select(., m1:m3)))

需求与疑问 #

要给每个area、year、period的组合新增一行全行业总计记录：将该行的naics设为000000，同时汇总m1、m2、m3的数值并计算对应的avgemp。

问：必须用pivot_longer才能实现吗？

期望结果 #

area      year      period     naics     m1     m2     m3     avgemp
  003       2022       01       000000    467    471    476      471
  003       2022       02       000000    476    478    482      479
  017       2022       01       000000    200    199    207      202
  ...

解决方案 #

完全不需要用pivot_longer，直接通过分组汇总+合并数据就能搞定：

步骤1：生成总计行 #

按area、year、period分组，计算每组的汇总值，同时将naics设为000000：

summary_rows <- first %>%
  group_by(area, year, period) %>%
  summarize(
    naics = "000000",
    m1 = sum(m1),
    m2 = sum(m2),
    m3 = sum(m3),
    avgemp = (m1 + m2 + m3)/3, # 和mean(c(m1,m2,m3))效果一致，写法更直观
    .groups = "drop"
  )

步骤2：合并原始数据与总计行 #

把原始数据和刚生成的总计行合并，再按需求排序：

final_data <- bind_rows(first, summary_rows) %>%
  arrange(area, year, period, naics)

这样得到的结果就包含了所有原始数据行，以及每个区域-年份-周期对应的全行业总计行，完全符合需求。

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