how to bulid a (proportion) table of multiple vars in dplyr?

September 15, 2022

let’s say I have the following data:

A <- rep(c("A1", "A2", "A3"), 10)
B <- sample(1:5, size=30, replace=TRUE)
C <- sample(c("yes", "no"), size=30, replace=TRUE)

I want to create a table of all three vars, where "A" defines the colums and B and C define the rows.

In rbase, I would do it like this:

data.frame(rbind(table(B, A),
                 table(C, A)))

This would give the following output:

    A1 A2 A3
1    0  3  3
2    2  1  0
3    2  1  2
4    2  4  3
5    4  1  2
no   5  7  3
yes  5  3  7

Variable "A" defines the columns, and variables "B" and "C" define the rows.

My questions are:

How would I do this in tidyverse with dplyr, group_by, summarize?
Is it possible to add the proportions to every cell, so that it would look like this:
```
  A1      A2      A3
```
1 0 (0%) 3 (1%) 3 (10%)

2 2 (1%) 1 (0,5) 0 (0%)

3 2 (1%) 1 (0,5) 2 (5%)

4 2 (2%) 4 (2%) 3 (3%)

5 4 (6%) 1 (1%) 2 (2%)

no 5 (7%) 7 (4%) 3 (3%)

yes 5 (3%) 3 (2%) 7 (10%)

Thx in advance….

>Solution :

A <- rep(c("A1", "A2", "A3"), 10)
B <- sample(1:5, size=30, replace=TRUE)
C <- sample(c("yes", "no"), size=30, replace=TRUE)
total_n <- 30

library(tidyverse)
tibble(A,B = as.character(B),C) %>% 
  pivot_longer(c(B,C), names_to = "original_col", values_to = "label") %>% 
  count(A, label, name = "n") %>% 
  complete(A, label, fill = list(n=0)) %>% 
  transmute(A, label, val = sprintf("%s (%s%%)", n, formatC(100* n/total_n, digits = 2))) %>% 
  pivot_wider(names_from = A, values_from = val)
#> # A tibble: 7 × 4
#>   label A1       A2       A3      
#>   <chr> <chr>    <chr>    <chr>   
#> 1 1     0 (  0%) 2 (6.7%) 2 (6.7%)
#> 2 2     3 ( 10%) 2 (6.7%) 2 (6.7%)
#> 3 3     4 ( 13%) 1 (3.3%) 2 (6.7%)
#> 4 4     3 ( 10%) 1 (3.3%) 4 ( 13%)
#> 5 5     0 (  0%) 4 ( 13%) 0 (  0%)
#> 6 no    2 (6.7%) 4 ( 13%) 5 ( 17%)
#> 7 yes   8 ( 27%) 6 ( 20%) 5 ( 17%)

^{Created on 2022-09-15 by the reprex package (v2.0.1)}