如何在R中实现SQL CASE WHEN式的多列分组平均统计

数据集结构 #

我的数据集结构如下：

working_df %>% 
  select(user_type, ride_length, day_of_week) %>%
  as.tibble() %>% 
  print(n = 3)

输出：

# A tibble: 4,324,766 × 3
  user_type ride_length day_of_week
  <chr>           <dbl> <ord>      
1 Casual          34355 Saturday   
2 Member          32035 Monday     
3 Casual          29271 Saturday

需求说明 #

我需要生成一张表格，展示不同用户组在每周各天的平均骑行时长。此前我用SQL的CASE WHEN结合GROUP BY WITH ROLLUP实现了该需求，代码如下：

原SQL实现代码 #

SELECT 
    COALESCE(user_type,'combined') AS user_type,
    AVG(CASE WHEN DAYNAME(started_at) = 'Monday' THEN TIMESTAMPDIFF(MINUTE,started_at, ended_at) ELSE NULL END) AS avg_ride_length_monday,
    AVG(CASE WHEN DAYNAME(started_at) = 'Tuesday' THEN TIMESTAMPDIFF(MINUTE,started_at, ended_at) ELSE NULL END) AS avg_ride_length_tuesday,
    AVG(CASE WHEN DAYNAME(started_at) = 'Wednesday' THEN TIMESTAMPDIFF(MINUTE,started_at, ended_at) ELSE NULL END) AS avg_ride_length_wednesday,
    AVG(CASE WHEN DAYNAME(started_at) = 'Thursday' THEN TIMESTAMPDIFF(MINUTE,started_at, ended_at) ELSE NULL END) AS avg_ride_length_thursday,
    AVG(CASE WHEN DAYNAME(started_at) = 'Friday' THEN TIMESTAMPDIFF(MINUTE,started_at, ended_at) ELSE NULL END) AS avg_ride_length_friday,
    AVG(CASE WHEN DAYNAME(started_at) = 'Saturday' THEN TIMESTAMPDIFF(MINUTE,started_at, ended_at) ELSE NULL END) AS avg_ride_length_saturday,
    AVG(CASE WHEN DAYNAME(started_at) = 'Sunday' THEN TIMESTAMPDIFF(MINUTE,started_at, ended_at) ELSE NULL END) AS avg_ride_length_sunday,
    AVG(TIMESTAMPDIFF(MINUTE,started_at, ended_at)) AS grand_total
FROM 
    bikes.work
GROUP BY 
    user_type WITH ROLLUP;

疑问 #

我了解R中的dplyr::case_when()函数，但只见过单列统计的示例，想知道R是否支持生成这类分列为每周各天的多列统计结果？

已找到的解决方案 #

根据建议，我找到了可行的实现方式，代码如下：

working_df %>%
  mutate_at(vars(c(user_type, day_of_week)), funs(as.character(.))) %>% 
  bind_rows(mutate(., user_type = "Combined")) %>% 
  bind_rows(mutate(., day_of_week = "Grand_Total")) %>% 
  group_by(user_type, day_of_week) %>% 
  summarize(avg_ride_length_min = mean(ride_length)) %>% 
  pivot_wider(names_from = day_of_week,
              values_from = avg_ride_length_min,
              names_prefix = "avg_ride_length_")

输出结果：

user_type avg_ride_length_Friday avg_ride_length_Grand_Total avg_ride_length_Monday avg_ride_length_Saturday avg_ride_length_Sunday
  <chr>                      <dbl>                       <dbl>                  <dbl>                    <dbl>                  <dbl>
1 Casual                      22.6                        24.2                   25.0                     27.0                   27.5
2 Combined                    16.5                        17.3                   16.8                     20.9                   20.8
3 Member                      12.4                        12.6                   12.2                     14.2                   14.0
  avg_ride_length_Thursday avg_ride_length_Tuesday avg_ride_length_Wednesday
                     <dbl>                   <dbl>                     <dbl>
1                     21.6                    21.6                      20.9
2                     15.5                    15.1                      14.9
3                     12.2                    11.9                      12.0

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