如何用R语言Janitor包的adorn_totals()定制列计算汇总行

要实现汇总行中pct列为总cost1与总cost2的比值，而不是各行列pct的求和，你可以通过两种方式实现：

方法1：手动构建汇总行（逻辑清晰，可控性强） #

先处理原始数据得到各行列的计算结果，再单独构建符合需求的汇总行，最后合并两者：

library(tidyverse)
library(janitor)

df.1 <- tribble(
  ~customer, ~period, ~cost1, ~cost2,
  'cust1',  '202201', 5, 10,
  'cust1',  '202202', 5, 10,
  'cust1',  '202203', 5, 10,
  'cust1',  '202204', 5, 10,
)

# 1. 处理原始数据，计算各行列的cost1、cost2、total和pct
df_processed <- df.1 %>% 
  group_by(customer, period) %>% 
  summarise(
    cost1 = sum(cost1, na.rm = TRUE),
    cost2 = sum(cost2, na.rm = TRUE),
    total = cost1 + cost2,
    pct = cost1 / cost2
  ) %>% 
  ungroup()

# 2. 构建自定义汇总行
total_row <- tibble(
  customer = "Total",
  period = "", # 对应期望输出中空的period列
  cost1 = sum(df_processed$cost1),
  cost2 = sum(df_processed$cost2),
  total = sum(df_processed$total),
  pct = sum(df_processed$cost1) / sum(df_processed$cost2) # 总cost1/总cost2
)

# 3. 合并数据并格式化pct列
final_df <- bind_rows(df_processed, total_row) %>% 
  mutate(pct = round(pct, 5))

# 查看结果
final_df

方法2：先使用adorn_totals再修正汇总行（代码更简洁） #

利用janitor::adorn_totals生成基础汇总行，再修改pct列的汇总值为需求的比值：

library(tidyverse)
library(janitor)

df.1 <- tribble(
  ~customer, ~period, ~cost1, ~cost2,
  'cust1',  '202201', 5, 10,
  'cust1',  '202202', 5, 10,
  'cust1',  '202203', 5, 10,
  'cust1',  '202204', 5, 10,
)

final_df <- df.1 %>% 
  group_by(customer, period) %>% 
  summarise(
    cost1 = sum(cost1, na.rm = TRUE),
    cost2 = sum(cost2, na.rm = TRUE),
    total = cost1 + cost2,
    pct = cost1 / cost2
  ) %>% 
  ungroup() %>% 
  adorn_totals(where = "row") %>% # 生成基础汇总行
  # 修正汇总行的pct值和period列
  mutate(
    pct = if_else(customer == "Total", cost1 / cost2, pct),
    period = if_else(customer == "Total", "", period)
  ) %>% 
  mutate(pct = round(pct, 5)) # 格式化小数位数

# 查看结果
final_df

两种方法最终都会得到你期望的输出：

# A tibble: 5 × 6
  customer period cost1 cost2 total    pct
  <chr>    <chr>  <dbl> <dbl> <dbl>  <dbl>
1 cust1    202201     5    10    15 0.33333
2 cust1    202202     5    10    15 0.33333
3 cust1    202203     5    10    15 0.33333
4 cust1    202204     5    10    15 0.33333
5 Total    ""        20    40    60 0.33333

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