如何在R中生成含总计行的分组平均骑行时长透视表

问题背景 #

我有如下格式的共享单车数据集（展示前3行）：

print(as_tibble(working_df), n = 3)

 ride_id          bike_type   started_at          ended_at            start_sta_name                    start_sta_id
  <chr>            <chr>       <dttm>              <dttm>              <chr>                             <chr>       
1 23697816035F9A8F docked_bike 2022-03-05 19:08:00 2022-03-29 15:43:00 Sheffield Ave & Fullerton Ave     TA1306000016
2 DC510E6F98003A94 docked_bike 2022-07-04 18:37:00 2022-07-27 00:32:00 Dusable Lake Shore Dr & Monroe St 13300       
3 578BA30BA1348F18 docked_bike 2022-01-01 01:00:00 2022-01-21 08:51:00 Millennium Park                   13008       
  end_sta_name                    end_sta_id                          start_lat start_lng end_lat end_lng user_type ride_length
  <chr>                           <chr>                                   <dbl>     <dbl>   <dbl>   <dbl> <chr>           <dbl>
1 Base - 2132 W Hubbard Warehouse Hubbard Bike-checking (LBS-WH-TEST)      41.9     -87.7    41.9   -87.7 Casual          34355
2 Green St & Randolph St*         chargingstx3                             41.9     -87.6    41.9   -87.6 Casual          32035
3 Fairfield Ave & Roosevelt Rd    KA1504000102                             41.9     -87.6    41.9   -87.7 Casual          29271
  day_of_week
  <ord>      
1 Saturday   
2 Monday     
3 Saturday  

我需要生成统计结果，展示各user_type组的平均骑行时长，同时包含所有用户的整体平均时长（总计行）。已用SQL实现该功能：

SELECT 
    COALESCE(user_type, 'combined') AS user_type,
    ROUND(AVG(TIMESTAMPDIFF(MINUTE, started_at, ended_at)), 2) AS avg_ride_length_min
FROM 
    bikes.work
GROUP BY
    user_type WITH ROLLUP;

尝试以下R代码但未成功：

working_df %>% 
  select(user_type, ride_length) %>% 
  group_by(user_type) %>% 
  summarize(avg_ride_length = mean(ride_length)) %>% 
  ungroup() %>% 
  mutate(mean(ride_length))

另外，rpivotTable::rpivotTable()虽能实现，但大数据集传入后RStudio Viewer会失效，需要替代方案。

解决方案 #

方法1：用dplyr手动拼接总计行 #

轻量化方案，适合大数据集，无性能压力：

library(dplyr)

# 计算分组平均时长
group_stats <- working_df %>%
  group_by(user_type) %>%
  summarize(avg_ride_length = round(mean(ride_length), 2)) %>%
  ungroup()

# 计算整体平均并生成总计行
total_stats <- tibble(
  user_type = "combined",
  avg_ride_length = round(mean(working_df$ride_length), 2)
)

# 合并结果
final_result <- bind_rows(group_stats, total_stats)

方法2：用janitor包快速添加总计行 #

janitor包专为统计表格的总计/小计需求设计，代码更简洁：

library(dplyr)
library(janitor)

final_result <- working_df %>%
  group_by(user_type) %>%
  summarize(avg_ride_length = round(mean(ride_length), 2)) %>%
  ungroup() %>%
  adorn_totals("row", name = "combined") # 添加行总计，命名为combined

方法3：用data.table处理超大数据集 #

如果数据集规模极大，data.table的计算效率优于dplyr：

library(data.table)

setDT(working_df)

# 计算分组平均
group_stats <- working_df[, .(avg_ride_length = round(mean(ride_length), 2)), by = user_type]
# 计算整体平均并生成总计行
total_stats <- data.table(user_type = "combined", avg_ride_length = round(mean(working_df$ride_length), 2))

# 合并结果
final_result <- rbind(group_stats, total_stats)

补充说明 #

• 以上方案均基于纯数据计算生成结果，完全避免了交互性透视表组件的性能问题，适合大数据场景。
• 如果需要美观的表格展示，可使用knitr::kable()或gt::gt()将结果转换为可视化表格，这两个包对大数据集的处理效率也更高。

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