Search code examples
rdataframesubtotal

Subtotal depending on multiple columns in r

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)

enter image description here

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)

enter image description here

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:

enter image description here

Would appreciate any help

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

Solution

  • 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)))))