Add 'total' row for each group in a column in df

user_13349937 0
Viewed 1170

I have a dataframe where the column size can be grouped. When the dataframe is arranged by size, I would like to show the totals of each column for each group as a row in the df.

library(dplyr)
dat <- data.frame(size = c("S", "XS", "L", "M", "L", "L", "XS"), 
           category = c("shirt", "shirt", "shirt", "shirt", "pants", "hat", "pants"),
           store1 = c(22, 3, 52, 10, 5, 37, 21),
           store2 = c(43, 13, 2, 24, 6, 12, 40))

dat %>%
  arrange(size) 

  size category store1 store2
1    L    shirt     52      2
2    L    pants      5      6
3    L      hat     37     12
4    M    shirt     10     24
5    S    shirt     22     43
6   XS    shirt      3     13
7   XS    pants     21     40

I would like to get something like this

  size category store1 store2
1     L    shirt     52      2
2     L    pants      5      6
3     L      hat     37     12
4        Total L     94     20
5     M    shirt     10     24
6        Total M     10     24
7     S    shirt     22     43
8        Total S     22     43
9    XS    shirt      3     13
10   XS    pants     21     40
11      Total XS     24     53

Any suggestions are appreciated!

dataframe r
4 Answers
library( tidyverse )
library( janitor )

dat %>%
  dplyr::arrange( size ) %>%
  split( .[,"size"] ) %>%
  purrr::map_df(., janitor::adorn_totals)

#  size category store1 store2
#     L    shirt     52      2
#     L    pants      5      6
#     L      hat     37     12
# Total        -     94     20
#     M    shirt     10     24
# Total        -     10     24
#     S    shirt     22     43
# Total        -     22     43
#    XS    shirt      3     13
#    XS    pants     21     40
# Total        -     24     53
user_63562780

Using group_modify bind the summary row to the end of the group data and then in the final mutate fix up the size and category fields on the total rows. We use data.frame at the end since the output looks a bit nicer with it but that could be omitted. This could be simplified slightly if the precise form of output shown in the question were not needed. This only uses dplyr.

dat %>% 
  group_by(size) %>%
  group_modify(~ bind_rows(., summarize(., across(where(is.numeric), sum)))) %>%
  ungroup %>%
  mutate(category = coalesce(category, paste("Total", size)),
         size = if_else(startsWith(category, "Total"), "", size)) %>%
  data.frame

giving:

   size category store1 store2
1     L    shirt     52      2
2     L    pants      5      6
3     L      hat     37     12
4        Total L     94     20
5     M    shirt     10     24
6        Total M     10     24
7     S    shirt     22     43
8        Total S     22     43
9    XS    shirt      3     13
10   XS    pants     21     40
11      Total XS     24     53
user_5165480

Note that the request is not tidy data. For this reason, one might want to store the summary statistics version in a different object. That aside, the following creates an object with store totals, and another object where a new category "total" describes store totals for that size.

store_totals <- dat %>%
    group_by(size) %>%
    summarise(store1 = sum(store1),
              store2 = sum(store2))
newdat <- add_row(.data = dat,
        store_totals) %>%
    arrange(size)
newdat$category[is.na(newdat$category)] <- "total"

output of newdat:

 size category store1 store2
1     L    shirt     52      2
2     L    pants      5      6
3     L      hat     37     12
4     L    total     94     20
5     M    shirt     10     24
6     M    total     10     24
7     S    shirt     22     43
8     S    total     22     43
9    XS    shirt      3     13
10   XS    pants     21     40
11   XS    total     24     53
user_148539070

Another option would be using nest, especially if you want to filter the data afterward (after size and/or category)

library(dplyr)
library(janitor)
library(tidyr)
library(purrr)

dat %>% 
  arrange(size) %>% 
  group_by(size) %>% 
  nest() %>%
  mutate(data = map(data, ~ .x %>% adorn_totals("row", name = "Total"))) %>%
  unnest(cols = c(data))

# A tibble: 11 x 4
# Groups:   size [4]
   size  category store1 store2
   <chr> <chr>     <dbl>  <dbl>
 1 L     shirt        52      2
 2 L     pants         5      6
 3 L     hat          37     12
 4 L     Total        94     20
 5 M     shirt        10     24
 6 M     Total        10     24
 7 S     shirt        22     43
 8 S     Total        22     43
 9 XS    shirt         3     13
10 XS    pants        21     40
11 XS    Total        24     53
user_37936660
Related