Add baseline/grand total with group_by() in dplyr

Question

When I've grouped my data by certain attributes, I want to add a "grand total" line that gives a baseline of comparison. Let's group mtcars by cylinders and carburetors, for example:

by_cyl_carb <- mtcars %>%
  group_by(cyl, carb) %>%
  summarize(median_mpg = median(mpg),
        avg_mpg = mean(mpg),
        count = n())

...yields these results:

> by_cyl_carb
# A tibble: 9 x 5
# Groups:   cyl [?]
    cyl  carb median_mpg avg_mpg count
   <dbl> <dbl>      <dbl>   <dbl> <int>
1     4     1       27.3    27.6     5
2     4     2       25.2    25.9     6
3     6     1       19.8    19.8     2
4     6     4       20.1    19.8     4
5     6     6       19.7    19.7     1
6     8     2       17.1    17.2     4
7     8     3       16.4    16.3     3
8     8     4       13.8    13.2     6
9     8     8       15      15       1

What is the code I need to make it provide a baseline or grand total that would sum (or mean or median) over all of the data? The desired data would be something like this:

    cyl  carb median_mpg avg_mpg count
   <chr> <chr>      <dbl>   <dbl> <int>
1     4     1       27.3    27.6     5
2     4     2       25.2    25.9     6
3     6     1       19.8    19.8     2
4     6     4       20.1    19.8     4
5     6     6       19.7    19.7     1
6     8     2       17.1    17.2     4
7     8     3       16.4    16.3     3
8     8     4       13.8    13.2     6
9     8     8       15      15       1
10   ttl   ttl      19.2    20.1    32

A twist on this would be able to manipulate the output so that the sub-grouped data would be rolled up. For example:

11   ttl    1       13.8    13.2     6
12   ttl    2       15      15       1
13   ttl    3       19.3    20.4    32
14 ... etc ...

The real-life example I am using this for is median sale price of homes by geography by year. Hence I want to report out the median sale price for each geography-year I'm interested in, but I want a baseline comparison for each year regardless of geography.

Edit: Solved with two solutions

@camille referenced this link, which solved the problem, as well as @MKR offering a solution. Here is one code that might work:

by_cyl_carb <- mtcars %>%
  mutate_at(vars(c(cyl,carb)), funs(as.character(.))) %>%
  bind_rows(mutate(., cyl = "All cylinders")) %>%
  bind_rows(mutate(., carb = "All carburetors")) %>%
  group_by(cyl, carb) %>%
  summarize(median_mpg = median(mpg),
            avg_mpg = mean(mpg),
            count = n())

> by_cyl_carb
# A tibble: 19 x 5
# Groups:   cyl [?]
   cyl           carb            median_mpg avg_mpg count
   <chr>         <chr>                <dbl>   <dbl> <int>
 1 4             1                     27.3    27.6     5
 2 4             2                     25.2    25.9     6
 3 4             All carburetors       26      26.7    11
 4 6             1                     19.8    19.8     2
 5 6             4                     20.1    19.8     4
 6 6             6                     19.7    19.7     1
 7 6             All carburetors       19.7    19.7     7
 8 8             2                     17.1    17.2     4
 9 8             3                     16.4    16.3     3
10 8             4                     13.8    13.2     6
11 8             8                     15      15       1
12 8             All carburetors       15.2    15.1    14
13 All cylinders 1                     22.8    25.3     7
14 All cylinders 2                     22.1    22.4    10
15 All cylinders 3                     16.4    16.3     3
16 All cylinders 4                     15.2    15.8    10
17 All cylinders 6                     19.7    19.7     1
18 All cylinders 8                     15      15       1
19 All cylinders All carburetors       19.2    20.1    32

Not a duplicate, but I posted a question recently that dealt with something similar: stackoverflow.com/questions/50729045/… Mine was more about the piping syntax, but it might help — camille

MKR MKR · Accepted Answer · 2018-06-28T21:56:52

A solution using dplyr::bind_rows and mutate_at can be achieved as:

library(tidyverse)
mtcars %>%
  group_by(cyl, carb) %>%
  summarize(median_mpg = median(mpg),
            avg_mpg = mean(mpg),
            count = n()) %>% 
  ungroup() %>%
  mutate_at(vars(cyl:carb), funs(as.character(.))) %>%
  bind_rows(summarise(cyl = "ttl", carb = "ttl", mtcars, median_mpg = median(mpg),
                      avg_mpg = mean(mpg),
                      count = n()))


# # A tibble: 10 x 5
#   cyl   carb  median_mpg avg_mpg count
#   <chr> <chr>      <dbl>   <dbl> <int>
# 1 4     1           27.3    27.6     5
# 2 4     2           25.2    25.9     6
# 3 6     1           19.8    19.8     2
# 4 6     4           20.1    19.8     4
# 5 6     6           19.7    19.7     1
# 6 8     2           17.1    17.2     4
# 7 8     3           16.4    16.3     3
# 8 8     4           13.8    13.2     6
# 9 8     8           15.0    15.0     1
#10 ttl   ttl         19.2    20.1    32

Add baseline/grand total with group_by() in dplyr

Edit: Solved with two solutions

2 Answers