Adding a column of total n for each group in a stacked frequency table-CodePudding

I have the following data:

id    animal    color     shape
1      bear     orange    circle
2.     dog      NA        triangle
3.     NA       yellow    square
4.     cat      yellow    square
5.     NA       yellow    rectangle

If I run this code:

df1 <- df %>% 
  pivot_longer(
    -id,
    names_to = "Variable",
    values_to = "Level"
  ) %>% 
  group_by(Variable, Level) %>% 
  summarise(freq = n()) %>% 
  mutate(percent = freq/sum(freq)*100) %>% 
  mutate(Variable = ifelse(duplicated(Variable), NA, Variable)) %>% 
  ungroup()

I can get the following output:

Variable     Level       freq(n=5)   percent

animal        bear          1           33.3
              dog           1           33.3
              cat           1           33.3
              

color         orange        1           25.0
              yellow        3           75.0
             

shape         circle        1           20.0
              triangle      1           20.0
              square        2           40.0
              rectangle     1           20.0

However I also want to add a row after each variable with the totals:

Variable     Level       freq(n=5)   percent

animal        bear          1           33.3
              dog           1           33.3
              cat           1           33.3
              total         3           100.0

color         orange        1           25.0
              yellow        3           75.0
              total         4           100.0

shape         circle        1           20.0
              triangle      1           20.0
              square        2           40.0
              rectangle     1           20.0
              total         5           100.0

I've tried different variations of mutate and summarize but keep getting the error " invalid 'type' (closure) of argument".

CodePudding user response：

If we stop one step short when defining df1,

df1 <- df %>%
  pivot_longer( -id, names_to = "Variable", values_to = "Level" ) %>%
  group_by(Variable, Level) %>%
  summarise(freq = n()) %>%
  mutate(percent = freq/sum(freq)*100)

df1
# # A tibble: 11 x 4
# # Groups:   Variable [3]
#    Variable Level      freq percent
#    <chr>    <chr>     <int>   <dbl>
#  1 animal   bear          1      20
#  2 animal   cat           1      20
#  3 animal   dog           1      20
#  4 animal   <NA>          2      40
#  5 color    orange        1      20
#  6 color    yellow        3      60
#  7 color    <NA>          1      20
#  8 shape    circle        1      20
#  9 shape    rectangle     1      20
# 10 shape    square        2      40
# 11 shape    triangle      1      20

Then we can augment it (and re-sort) with group summaries:

df1 %>%
  group_by(Variable) %>%
  summarize(Level = "total", across(freq:percent, sum)) %>%
  bind_rows(df1) %>%
  arrange(Variable, !is.na(Level), Level == "total", Level) %>%
  mutate(Variable = ifelse(duplicated(Variable), NA, Variable))
# # A tibble: 14 x 4
#    Variable Level      freq percent
#    <chr>    <chr>     <int>   <dbl>
#  1 animal   <NA>          2      40
#  2 <NA>     bear          1      20
#  3 <NA>     cat           1      20
#  4 <NA>     dog           1      20
#  5 <NA>     total         5     100
#  6 color    <NA>          1      20
#  7 <NA>     orange        1      20
#  8 <NA>     yellow        3      60
#  9 <NA>     total         5     100
# 10 shape    circle        1      20
# 11 <NA>     rectangle     1      20
# 12 <NA>     square        2      40
# 13 <NA>     triangle      1      20
# 14 <NA>     total         5     100

CodePudding user response：


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

df1 %>% 
  pivot_longer(
    -id,
    names_to = "Variable",
    values_to = "Level"
  ) %>% 
  group_by(Variable, Level) %>% 
  summarise(freq = n()) %>% 
  mutate(percent = freq/sum(freq)*100) %>% 
  group_split() %>% 
  map_dfr(. , ~.x %>% 
            adorn_totals(name = "total")) %>% 
  mutate(Variable = ifelse(duplicated(Variable) & Variable != "total", NA, Variable)) %>% 
  ungroup()

#>  Variable     Level freq percent
#>    animal      bear    1      20
#>      <NA>       cat    1      20
#>      <NA>       dog    1      20
#>      <NA>      <NA>    2      40
#>     total         -    5     100
#>     color    orange    1      20
#>      <NA>    yellow    3      60
#>      <NA>      <NA>    1      20
#>     total         -    5     100
#>     shape    circle    1      20
#>      <NA> rectangle    1      20
#>      <NA>    square    2      40
#>      <NA>  triangle    1      20
#>     total         -    5     100

Data:

read.table(text = "id    animal    color     shape
1      bear     orange    circle
2     dog      NA        triangle
3     NA       yellow    square
4     cat      yellow    square
5     NA       yellow    rectangle", header = T, stringsAsFactors =  F) -> df1

CodePudding user response：

Here is one way to achieve the task:

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

df %>% 
  pivot_longer(
    -id,
    names_to = "Variable",
    values_to = "Level"
  ) %>% 
  group_by(Variable, Level) %>% 
  summarise(freq = n()) %>% 
  mutate(percent = freq/sum(freq)*100) %>% 
  ungroup() %>% 
  group_by(Variable) %>% 
  group_split() %>% 
  adorn_totals() %>% 
  bind_rows() %>% 
  mutate(Level = ifelse(Level == last(Level), last(Variable), Level)) %>% 
  mutate(Variable = ifelse(duplicated(Variable) |
                             Variable == "Total", NA, Variable))

 Variable     Level freq percent
   animal      bear    1      20
     <NA>       cat    1      20
     <NA>       dog    1      20
     <NA>      <NA>    2      40
     <NA>     Total    5     100
    color    orange    1      20
     <NA>    yellow    3      60
     <NA>      <NA>    1      20
     <NA>     Total    5     100
    shape    circle    1      20
     <NA> rectangle    1      20
     <NA>    square    2      40
     <NA>  triangle    1      20
     <NA>     Total    5     100