Flip group_by variable to columns, and flip columns to rows dplyr

Question

thank you in advance for your response! I am working in Rstudio, trying to create a specific table format that my customer is looking for. Specifically, I would like to show each metric as a row and the group_by variable, in this case application type, as a column. I'm using group_by to consolidate all my data by application type, and I'm using the summarise function to create the new variables.

subs <- data.frame(
  App_type = c('A','A','A','B','B','B','C','C','C','C'), 
  Has_error = c(1,1,1,0,0,1,1,0,1,1), 
  Has_critical_error = c(1,0,1,0,0,1,0,0,1,1)
)

I'm able to group the submissions together by application type to see total submissions with errors and total with critical errors. Here's what I've done -

subs %>% 
group_by(App_type) %>% 
summarise(
  total_sub = n(), 
  total_error = sum(Has_error), 
  total_critical_error = sum(Has_critical_error)
)
# A tibble: 3 x 4
  App_type total_sub total_error total_critical_error
  <fct>        <int>       <dbl>                <dbl>
1 A                3           3                    2
2 B                3           1                    1
3 C                4           3                    2

However, my customer would like to see it this way with application totals.


                          A          B           C           TOTAL
1 total_sub               3          3           4           10
2 total_error             3          1           3           7
3 total_critical_error    2          1           2           5

akrun akrun · Accepted Answer · 2020-04-06T21:55:55

We can pivot to 'wide' format after reshaping to 'long' and then change the column name 'name' to rowname

library(dplyr)
library(tidyr)
library(tibble)
subs %>% 
  group_by(App_type) %>% 
  summarise(
     total_sub = n(), 
     total_error = sum(Has_error), 
     total_critical_error = sum(Has_critical_error))     %>% 
  pivot_longer(cols = -App_type) %>% 
  pivot_wider(names_from = App_type, values_from = value) %>%
  mutate(TOTAL =  A + B + C) %>%
  column_to_rownames("name")
#                      A B C TOTAL
#total_sub            3 3 4    10
#total_error          3 1 3     7
#total_critical_error 2 1 2     5

Or another option is transpose from data.table

library(data.table)
data.table::transpose(setDT(out), make.names = 'App_type',
      keep.names = 'name')[, TOTAL := A + B + C][]

where out is the OP's summarised output

out <- subs %>% 
        group_by(App_type) %>% 
        summarise(
          total_sub = n(), 
          total_error = sum(Has_error), 
          total_critical_error = sum(Has_critical_error)
          )

Or with base R

addmargins(t(cbind(total_sub = as.integer(table(subs$App_type)),
      rowsum(subs[-1], subs$App_type))), 2)
#                   A B C Sum
#total_sub          3 3 4  10
#Has_error          3 1 3   7
#Has_critical_error 2 1 2   5

Flip group_by variable to columns, and flip columns to rows dplyr

1 Answers