CODEWITHSUNDEEP
rcrosstab
Daniel Sossa
Daniel Sossa

Reputation: 11

How could I add column and row percentages to a crosstab in R (like in an excel Pivot Table)?

I have the following code:

alldata2019_v2 %>% tabyl(agerange,gender) %>% 
                adorn_totals(c("row","col")) %>% 
                mutate_if(is.numeric, format, big.mark = ",")

And it provides the expected table:

agerange Female Male Total
17 or less 5 0 5
18 to 24 41,486 84,444 125,930
25 to 29 239,969 543,021 782,990
30 to 34 245,211 604,262 849,473
35 to 39 120,084 397,880 517,964
40 to 44 63,102 235,768 298,870
45 to 49 38,889 154,196 193,085
50 to 54 36,612 134,244 170,856
55 to 59 36,810 115,891 152,701
60 or more 35,690 130,044 165,734
Total 857,858 2,399,750 3,257,608

The problem is I cant find a way to add a columns for percentages by row and by column. Is there a way to do it?

agerange Female Male Total Female_pct Male_pct Age_pct
17 or less 5 0 5 100% 0% 0%
18 to 24 41,486 84,444 125,930 33% 67% 4%
... ... ... ... ... ... ...

the input data has this strcture:

gender usertype agerange
Male Subscriber 30 to 34
Female Subscriber 30 to 34
Female Subscriber 25 to 29
Male Subscriber 25 to 29
Male Subscriber 25 to 29
Female Subscriber 35 to 39

Upvotes: 1

Views: 698

Answers (1)

akrun
akrun

Reputation: 887681

We may use

library(dplyr)
library(janitor)
library(stringr)
out %>%
    mutate(across(-1, readr::parse_number)) %>% 
   adorn_percentages() %>%
   select(-agerange) %>% 
   rename_with(~ str_c(., 'pct'), everything()) %>%
   bind_cols(out, .)

Reproducible example

data(mtcars)
mtcars %>% 
   tabyl(am, cyl) %>%
    magrittr::multiply_by(1000) %>%
    adorn_totals(c("row", "col")) %>% 
    mutate(across(where(is.numeric), 
      ~ formattable::comma(., digits = 2, big.mark = ","))) -> out

out %>% 
   adorn_percentages() %>% 
   select(-am, -Total) %>%
   rename_with(~ str_c(., 'pct'), everything()) %>%   
    bind_cols(out, .)
    am         4        6         8     Total 4pct 6pct 8pct
     0  3,000.00 4,000.00 12,000.00 19,000.00 0.16 0.21 0.63
  1000  8,000.00 3,000.00  2,000.00 13,000.00 0.62 0.23 0.15
 Total 11,000.00 7,000.00 14,000.00 32,000.00 0.34 0.22 0.44

Upvotes: 1

Related Questions