Geek Answers Handbook

Add margin totals in dplyr chain

I would like to add general summary lines, as well as calculate resumes by group using dplyr. I found several questions asking how to do this, for example. here , here , and here , but there is no clear solution. One possible approach is to execute count twice and string binding:

 mtcars %>% count(cyl, gear) %>% bind_rows( count(mtcars, gear) )

which almost creates what I need (in the leftmost column is NA, not "Total" or similar):

  cyl gear n <dbl> <dbl> <int> 1 4 3 1 2 4 4 8 3 4 5 2 4 6 3 2 5 6 4 4 6 6 5 1 7 8 3 12 8 8 5 2 9 NA 3 15 10 NA 4 12 11 NA 5 5

Am I missing a simple / inline solution?

+6
source share
3 answers

One option: do

 mtcars %>% count(cyl, gear) %>% ungroup() %>% mutate(cyl=as.character(cyl)) %>% do(bind_rows(., data.frame(cyl="Total", count(mtcars, gear)))) #or replace the last 'do' step with #bind_rows(cbind(cyl='Total', count(mtcars, gear))) #from @JonnyPolonsky comments # cyl gear n # <chr> <dbl> <int> #1 4 3 1 #2 4 4 8 #3 4 5 2 #4 6 3 2 #5 6 4 4 #6 6 5 1 #7 8 3 12 #8 8 5 2 #9 Total 3 15 #10 Total 4 12 #11 Total 5 5
+6
source

With adorn_totals () from janitor package:

 library(janitor) mtcars %>% tabyl(cyl, gear) %>% adorn_totals("row") cyl 3 4 5 4 1 8 2 6 2 4 1 8 12 0 2 Total 15 12 5

To get from there to the "long" form in your message, add tidyr::gather() to the pipeline:

 mtcars %>% tabyl(cyl, gear) %>% adorn_totals("row") %>% tidyr::gather(gear, n, 2:ncol(.), convert = TRUE) cyl gear n 1 4 3 1 2 6 3 2 3 8 3 12 4 Total 3 15 5 4 4 8 6 6 4 4 7 8 4 0 8 Total 4 12 9 4 5 2 10 6 5 1 11 8 5 2 12 Total 5 5

Warning about self-promotion, I created this package - adding this b / c answer, this is a really effective solution here.

+2
source

Adding to @arkrun an answer that is not so easy to add as a comment:

Although this is a bit more complicated, this format allows for previous changes to the data frame. Useful if a longer chain of verbs exists before creating the table. (Do you want to change the names or select only certain variables)

 mtcars %>% count(cyl, gear) %>% ungroup() %>% mutate(cyl=as.character(cyl)) bind_rows(group_by(.,gear) %>% summarise(n=sum(n)) %>% mutate(cyl='Total')) %>% spread(cyl) ## A tibble: 3 x 5 # gear `4` `6` `8` Total #* <dbl> <dbl> <dbl> <dbl> <dbl> #1 3 1 2 12 15 #2 4 8 4 0 12 #3 5 2 1 2 5

It can also be doubled to generate a common string for distribution.

 mtcars %>% count(cyl, gear) %>% ungroup() %>% mutate(cyl=as.character(cyl), gear = as.character(gear)) %>% bind_rows(group_by(.,gear) %>% summarise(n=sum(n)) %>% mutate(cyl='Total')) %>% bind_rows(group_by(.,cyl) %>% summarise(n=sum(n)) %>% mutate(gear='Total')) %>% spread(cyl,n,fill=0) # A tibble: 4 x 5 gear `4` `6` `8` Total * <chr> <dbl> <dbl> <dbl> <dbl> 1 3 1 2 12 15 2 4 8 4 0 12 3 5 2 1 2 5 4 Total 11 7 14 32
+1
source

More articles:


All Articles