Subtotal depending on multiple columns in r

Question

Here is a test table:

df <- read.table(text="
           str1 str2    name    t   y   x
             a  yes bas 23  323 21
             b  no  aasd    23  54  33
             a  no  asd 2   43  23
             b  yes hggf    43  123 55
             b  no  jgd 1   12  11
             b  yes qw  32  12  12
             a  yes rrrr    45  22  32
             a  no  ggg 121 11  43
             ",
             header = TRUE)

With help here we can get such subtotals

library(janitor)
library(purrr)
library(dplyr)
df<-df %>% 
  split(.[,"str1"]) %>% ## splits each change in cyl into a list of dataframes 
  map_df(., janitor::adorn_totals)

But my question is how to get also sub totals inside each group of column str1 depending on group inside of str2. It's needed a dataframe like this:

Would appreciate any help

P.S it is vital x column to be in descending order in each group

akrun · Accepted Answer · 2019-12-17T16:00:39.167

2

We can do the split by two columns and then change the name of the 'Total' based on the values in 'str1', 'str2'

library(dplyr)
library(janitor)
library(purrr)
library(stringr)
df %>% 
   group_split(str1, str2) %>% 
   map_dfr(~ .x %>% 
        janitor::adorn_totals(.) %>% 
        mutate(str1 = replace(str1, n(), str_c(str1[n()], "_", 
           first(str1), "_", first(str2)))))

edited Dec 17 '19 at 16:00

answered Dec 17 '19 at 15:40

akrun

874,273
37
540
662

score 1 · Answer 2 · answered Dec 17 '19 at 15:44

Alternatively, using the same syntax than for your first split, you can do:

library(janitor)
library(purrr)
library(dplyr)
df %>% arrange(x) %>%
  split(.[,c("str2","str1")]) %>% 
  map_df(., janitor::adorn_totals)

  str1 str2 name   t   y  x
     a   no  asd   2  43 23
     a   no  ggg 121  11 43
 Total    -    - 123  54 66
     a  yes  bas  23 323 21
     a  yes rrrr  45  22 32
 Total    -    -  68 345 53
     b   no  jgd   1  12 11
     b   no aasd  23  54 33
 Total    -    -  24  66 44
     b  yes   qw  32  12 12
     b  yes hggf  43 123 55
 Total    -    -  75 135 67

Is it possible to name those totals as total1, total2, total3, total4 depending on how much we have of totals, to operate then with them as i want? — Maksym Moroz, Dec 17 '19 at 15:55

IceCreamToucan · Answer 3 · 2021-06-06T00:44:08.023

If you don't mind the location of the "total" rows being a little different, you can use data.table::rollup. Rows with NA are totals for the group identified by the values of the non-NA columns.

library(data.table)
setDT(df)

group_vars <- head(names(df), 3)
df_ru <- 
  rollup(df, j = lapply(.SD, sum), by = group_vars, 
         .SDcols = tail(names(df), 3))

setorderv(df_ru, group_vars)[-1]
#>     str1 str2 name   t   y   x
#>  1:    a <NA> <NA> 191 399 119
#>  2:    a   no <NA> 123  54  66
#>  3:    a   no  asd   2  43  23
#>  4:    a   no  ggg 121  11  43
#>  5:    a  yes <NA>  68 345  53
#>  6:    a  yes  bas  23 323  21
#>  7:    a  yes rrrr  45  22  32
#>  8:    b <NA> <NA>  99 201 111
#>  9:    b   no <NA>  24  66  44
#> 10:    b   no aasd  23  54  33
#> 11:    b   no  jgd   1  12  11
#> 12:    b  yes <NA>  75 135  67
#> 13:    b  yes hggf  43 123  55
#> 14:    b  yes   qw  32  12  12

^{Created on 2021-06-05 by the reprex package (v2.0.0)}

Subtotal depending on multiple columns in r

3 Answers3

Linked