R语言：按讲师分组汇总课时的数据透视表创建问题

问题 #

需要将.xlsx文件中的列数据转换为按讲师分组汇总ContactHours的数据透视表，原始数据结构如下：

| Class   | Instructor  | ContactHours|
| --------| ------------| ------------|
| Class A | Instructor A| 7.2         |
| Class B | Instructor A| 5           |
| Class C | Instructor A| 3           |
| Class D | Instructor B| 3           |
| Class E | Instructor B| 3           |
| Class F | Instructor C| 7.4         |
| Class G | Instructor D| 7.4         |
| Class H | Instructor D| 5           |

期望得到的透视表格式：

| Instructor   | Class  | CH  | 
| -------------| -------| --- |
| Instructor A |        | 15.2|
|              | Class A| 7.2 |
|              | Class B| 5   |
|              | Class C| 3   |
| Instructor B |        | 6   |
|              | Class D| 3   |
|              | Class E| 3   |
| Instructor C |        | 7.4 |
|              | Class F| 7.4 | 
| Instructor D |        | 12.4|
|              | Class G| 7.4 | 
|              | Class H| 5   |

尝试pivottabler包时，TotalCH列全为0，代码如下：

pat <- read_csv("test.csv", skip=2, col_names = TRUE)
pt <- PivotTable$new()
pt$addData(pat)
pt$addRowDataGroups("Instructor", addTotal=TRUE)
pt$addRowDataGroups("Class", addTotal=FALSE)
pt$addRowDataGroups("ContactHours", addTotal=FALSE)
pt$defineCalculation(calculationName="TotalCH", summariseExpression="sum()")
pt$renderPivot()

输出结果：

| Instructor   | Class  | CH  | TotalCH |
| -------------| -------| --- |---------|
| Instructor A | Class A| 7.2 | 0       |
|              | Class B| 5   | 0       |
|              | Class C| 3   | 0       |
| Instructor B | Class D| 3   | 0       |
|              | Class E| 3   | 0       |
| Instructor C | Class F| 7.4 | 0       | 
| Instructor D | Class G| 7.4 | 0       | 
|              | Class H| 5   | 0       |

使用dplyr得到了数值正确的结果，但格式不符合要求，代码如下：

pat %>%
  group_by(Class) %>%
  group_by(Instructor) %>%
  mutate(sum_ContactHours =  sum(ContactHours)) %>%
  ungroup()

输出结果：

# A tibble: 8 × 4
  Class   Instructor   ContactHours sum_ContactHours
  <chr>   <chr>               <dbl>            <dbl>
1 Class A Instructor A          7.2             15.2
2 Class B Instructor A          5               15.2
3 Class C Instructor A          3               15.2
4 Class D Instructor B          3                6  
5 Class E Instructor B          3                6  
6 Class F Instructor C          7.4              7.4
7 Class G Instructor D          7.4             12.4
8 Class H Instructor D          5               12.4

解决方案 #

方法1：修正pivottabler代码 #

问题出在计算定义和行分组的设置上，无需将ContactHours设为行分组，需明确指定汇总字段并调整分组层级的汇总逻辑：

library(pivottabler)
library(readr)

# 读取数据（若为xlsx格式，可改用readxl::read_excel）
pat <- read_csv("test.csv", skip=2, col_names = TRUE)

pt <- PivotTable$new()
pt$addData(pat)
# 设置行分组：讲师（不添加默认汇总行，后续自定义）、班级
pt$addRowDataGroups("Instructor", addTotal=FALSE)
pt$addRowDataGroups("Class", addTotal=FALSE)
# 定义计算：汇总ContactHours，指定在讲师分组顶部显示汇总值
pt$defineCalculation(
  calculationName = "CH",
  summariseExpression = "sum(ContactHours)",
  format = "%.1f",
  totalCaption = ""
)
# 渲染透视表
pt$renderPivot()

方法2：dplyr + gt实现格式化输出 #

若需要更灵活的格式控制，可先用dplyr构造包含汇总行的数据集，再用gt生成带合并行的表格：

library(dplyr)
library(gt)
library(readr)

pat <- read_csv("test.csv", skip=2, col_names = TRUE)

# 生成包含讲师汇总行的数据集
summary_data <- pat %>%
  group_by(Instructor) %>%
  mutate(total_ch = sum(ContactHours)) %>%
  ungroup() %>%
  group_by(Instructor) %>%
  group_modify(~{
    bind_rows(
      tibble(Class = "", CH = .x$total_ch[1]),
      .x %>% select(Class, CH = ContactHours)
    )
  }) %>%
  ungroup()

# 生成带合并行的可视化表格
summary_data %>%
  gt(groupname_col = "Instructor") %>%
  tab_options(row_group.as_column = TRUE) %>%
  fmt_number(columns = CH, decimals = 1) %>%
  cols_label(
    Instructor = "Instructor",
    Class = "Class",
    CH = "CH"
  )

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