Getting Started with rollup Package

true

rollup: A Tidy implementation of GROUPING SETS, WITH ROLLUP, and WITH CUBE, which are powerful extensions of the GROUP BY clause that compute multiple group-by clauses in a single statement in SQL. This package operates on top of the dplyr and performs the same functions as SQL.

Installation

# From CRAN
install.packages("rollup")
 
# From Github
library(devtools)
devtools::install_github("JuYoungAhn/rollup")

In a Nutshell

The functions of rollup package allow you to simplify multiple group_by operations into a single, concise statement.
This makes data aggregation easier and more efficient.

mtcars %>% group_by(vs, am) %>% grouping_sets("vs","am",c("vs","am"), NA) %>% 
  summarize(n=n(), avg_mpg=mean(mpg))
#> # A tibble: 9 × 4
#>      vs    am     n avg_mpg
#>   <dbl> <dbl> <int>   <dbl>
#> 1     0    NA    18    16.6
#> 2     1    NA    14    24.6
#> 3    NA     0    19    17.1
#> 4    NA     1    13    24.4
#> 5     0     0    12    15.0
#> 6     0     1     6    19.8
#> 7     1     0     7    20.7
#> 8     1     1     7    28.4
#> 9    NA    NA    32    20.1

mtcars %>% group_by(vs, am) %>% with_rollup() %>% 
  summarize(n=n(), avg_mpg=mean(mpg))
#> # A tibble: 7 × 4
#> # Groups:   vs [3]
#>      vs    am     n avg_mpg
#>   <dbl> <dbl> <int>   <dbl>
#> 1     0     0    12    15.0
#> 2     0     1     6    19.8
#> 3     1     0     7    20.7
#> 4     1     1     7    28.4
#> 5     0    NA    18    16.6
#> 6     1    NA    14    24.6
#> 7    NA    NA    32    20.1

mtcars %>% group_by(vs, am) %>% with_cube() %>% 
  summarize(n=n(), avg_mpg=mean(mpg))
#> # A tibble: 9 × 4
#>      vs    am     n avg_mpg
#>   <dbl> <dbl> <int>   <dbl>
#> 1     0    NA    18    16.6
#> 2     1    NA    14    24.6
#> 3    NA     0    19    17.1
#> 4    NA     1    13    24.4
#> 5     0     0    12    15.0
#> 6     0     1     6    19.8
#> 7     1     0     7    20.7
#> 8     1     1     7    28.4
#> 9    NA    NA    32    20.1

Practical example

This example shows how to compute the average pageview count grouped by various combinations of gender and age.
In this section, you will see why the rollup package is useful by exploring practical data examples.

Web service data

Description of data

date_id : yyyy-mm-dd
id : user unique id
gender : male(M), female(F)
age : age band (categorical)
page_view_cnt : pageview count of user on date_id
product_view_cnt_cat : decile category of the product view count for a user on date_id.

library(dplyr)
library(rollup)
data("web_service_data") # web_service_data of rollup package
web_service_data %>% head
#> # A tibble: 6 × 6
#>   date_id       id gender age   page_view_cnt product_view_cnt_cat
#>   <chr>      <dbl> <chr>  <fct>         <dbl> <fct>               
#> 1 2024-06-24    19 M      40                0 60%                 
#> 2 2024-06-24    34 M      40                5 70%                 
#> 3 2024-06-24    44 F      50               12 100%                
#> 4 2024-06-24    57 M      60               87 20%                 
#> 5 2024-06-24    65 F      50                1 100%                
#> 6 2024-06-24    86 F      40                3 90%

grouping_sets

grouping_sets() allows you to perform multiple group_by operations simultaneously, producing combined results in a single output.
grouping_sets('a') is equivalent to the single grouping set operation group_by(a).
grouping_sets('a','b') is equivalent to row binding of group_by(a) and group_by(b).
grouping_sets(c('a','b'),'a','b', NA) is equivalent to row binding of group_by(a,b), group_by(a), group_by(b) and without group_by operation.

library(tidyr)
# compute average of `page_view_cnt` group by "gender", "age", and "gender & age", along with the overall average. NA in the output table represents overall aggregates.
web_service_data %>% filter(date_id == '2024-06-30' & gender != "N") %>% 
  group_by(gender, age) %>% grouping_sets('gender', 'age', c('gender','age'), NA) %>% 
  summarize(avg_pv_cnt = mean(page_view_cnt))
#> # A tibble: 21 × 3
#>    gender age   avg_pv_cnt
#>    <chr>  <fct>      <dbl>
#>  1 F      <NA>        2.28
#>  2 M      <NA>        1.92
#>  3 <NA>   10          1.61
#>  4 <NA>   20          3.01
#>  5 <NA>   30          2.23
#>  6 <NA>   40          1.77
#>  7 <NA>   50          1.44
#>  8 <NA>   60          2.30
#>  9 F      10          2.33
#> 10 F      20          2.86
#> # ℹ 11 more rows

# compute average of `page_view_cnt` group by "gender & age & product_view_cnt_cat" along with the marginal average with regard to "product_view_cnt_cat".
web_service_data %>% filter(date_id == '2024-06-30' & gender != "N") %>% 
  group_by(gender, age, product_view_cnt_cat) %>% 
  grouping_sets('product_view_cnt_cat', c('product_view_cnt_cat', 'gender','age')) %>% 
  summarize(avg_pv_cnt = mean(page_view_cnt)) %>% 
  pivot_wider(names_from = product_view_cnt_cat, values_from = avg_pv_cnt)
#> # A tibble: 13 × 11
#>    gender age       X `20%` `40%` `50%` `60%` `70%` `80%` `90%` `100%`
#>    <chr>  <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
#>  1 <NA>   <NA>  1.46  1.84   2.02 2.31   2.72  2.89  2.8   3.79   2.82
#>  2 F      10    1.4   2      1.4  2.67   4    NA    NA     4     NA   
#>  3 F      20    0     3.5    2.08 2.29   3.83  2.57  3.45  4.83   2.25
#>  4 F      30    0.833 2.5    4.5  2.88   3     1.75  3.5   3      3.17
#>  5 F      40    1.33  1.9    2.7  2.2    1.22  3     3.38  4      2   
#>  6 F      50    0.462 1.5    2    2.5    1.2   4     2.5   5.33   3.5 
#>  7 F      60    1.19  1.71   1    1.33   3     3     1.5   2      3   
#>  8 M      10    0.375 0.833  1.14 3      1     0    NA    NA     NA   
#>  9 M      20    1.14  3.17   3.16 3.55   4.5   3    NA     3.5    7   
#> 10 M      30    0.824 1.62   1.31 2.7    3.38  2.5   1.86  3.5   NA   
#> 11 M      40    0.889 0.933  2.06 0.833  1.88  3.25  1.6   1.67  NA   
#> 12 M      50    0.562 1.07   1.06 2.6    2     0     0.5   0     NA   
#> 13 M      60    3.06  2.69   4    3.5    0     8     2     1     NA

with_cube

with_cube() automatically generates all possible combinations of specified variables in group_by clause.
with_cube() function is a simplified way of expressing grouping_sets().
with_cube() is equivalent to using grouping_sets() with all combinations of the specified columns.
For example, group_by(a,b,c) followed by with_cube() equals to grouping_sets(c('a','b','c'), c('a','b'), c('a','c'), c('b','c'), 'a', 'b', 'c', NA).
with_cube() is particularly useful when you want to include total aggregates of both rows and columns in a cross table.

# This produces a table with average page view counts grouped by gender and age, including total aggregates across all combinations.
web_service_data %>% filter(date_id == '2024-06-30' & gender != "N") %>% 
  group_by(gender, age) %>% with_cube() %>% 
  summarize(avg_pv_cnt = mean(page_view_cnt)) %>% 
  pivot_wider(names_from = age, values_from = avg_pv_cnt)
#> # A tibble: 3 × 8
#>   gender  `NA`  `10`  `20`  `30`  `40`  `50`  `60`
#>   <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 F       2.28  2.33  2.86  2.67  2.33 2.24   1.48
#> 2 M       1.92  0.92  3.19  1.91  1.31 0.907  2.99
#> 3 <NA>    2.08  1.61  3.01  2.23  1.77 1.44   2.30

with_rollup

with_rollup() creates hierarchical aggregations by progressively reducing the number of grouping variables.
with_rollup() is particulary useful when variables have a hierarchy, because all possible combinations are not necessary.
group_by(a,b) followed by with_rollup() equals to grouping_sets(c('a','b'), 'a', NA).
group_by(a,b,c) followed by with_rollup() equals to grouping_sets(c('a','b','c'), ('a','b'), ('a'), NA).

# The variables "age_big" and "age" have a hierarchy. 
web_service_data_processed <- web_service_data %>% mutate(
  age_big = case_when(
    age %in% c(10,20,30) ~ 'young',
    age %in% c(40,50,60) ~ 'old'  
  )
)

# If there are aggregates "age_big & age", marginal aggregates for "age" are not necessary.
# The following code computes aggregates for "age_big & age", "age_big", and entire data set.
web_service_data_processed %>% group_by(age_big, age) %>% 
  with_rollup() %>% summarize(
  user_cnt = n_distinct(id),
  avg_pv_cnt = mean(page_view_cnt)
)
#> # A tibble: 9 × 4
#> # Groups:   age_big [3]
#>   age_big age   user_cnt avg_pv_cnt
#>   <chr>   <fct>    <int>      <dbl>
#> 1 old     40         196       2.52
#> 2 old     50         178       1.99
#> 3 old     60         204       2.32
#> 4 young   10         132       1.57
#> 5 young   20         140       3.69
#> 6 young   30         150       3.77
#> 7 old     <NA>       578       2.29
#> 8 young   <NA>       422       3.06
#> 9 <NA>    <NA>      1000       2.61