Frequency table and proportions stored in different dataframes

Question

I am having a dataframe that has around 20 variables which are numeric and 1 which is categorical. For simplicity I made a very small one. I want to calculate the frequency of each number per category and also the proportion (%) of each number per category.

Then, those tables should be stored in a different dataframe for each variable from the original dataframe.

My data look like that:

df <- data.frame(categories = c(1,2,3,3,4,1),
             X1 = c(0,11,13,2,0,5),
             X2 = c(2,0,NA,1,3,7))

And I want something like that stored in two different dataframes:

For X1

      1  2  3  4
 0    1  0  0  0
 2    1  0  0  0
 5    0  0  1  0
 11   0  1  0  0
 13   0  0  1  0

for X2

      1  2  3  4
  0   0  1  0  0
  1   0  0  1  0
  2   1  0  0  0 
  3   0  0  0  1
  7   1  0  0  0

Also, if possible they should include a (%) next to each category.

Can we achieve the following with a loop as well?

Thank you very much.

user_12025483 · Answer

base

df <- data.frame(categories = c(1,2,3,3,4,1),
                 X1 = c(0,11,13,2,0,5),
                 X2 = c(2,0,NA,1,3,7))

lapply(df[grep("^X\d+", names(df))], function(x) table(x, df$categories))

#> $X1
#>     
#> x    1 2 3 4
#>   0  1 0 0 1
#>   2  0 0 1 0
#>   5  1 0 0 0
#>   11 0 1 0 0
#>   13 0 0 1 0
#> 
#> $X2
#>    
#> x   1 2 3 4
#>   0 0 1 0 0
#>   1 0 0 1 0
#>   2 1 0 0 0
#>   3 0 0 0 1
#>   7 1 0 0 0

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

tidyverse

df <- data.frame(categories = c(1,2,3,3,4,1),
                 X1 = c(0,11,13,2,0,5),
                 X2 = c(2,0,NA,1,3,7))

library(tidyverse)

map(
  .x = str_subset(names(df), pattern = "^X\d+"),
  .f = ~ pivot_wider(
    data = df,
    id_cols = all_of(.x),
    names_from = categories,
    values_from = categories,
    values_fn = length,
    values_fill = 0
  ) %>%
    arrange(!!sym(.x)) %>%
    drop_na
)
#> [[1]]
#> # A tibble: 5 x 5
#>      X1   `1`   `2`   `3`   `4`
#>       
#> 1     0     1     0     0     1
#> 2     2     0     0     1     0
#> 3     5     1     0     0     0
#> 4    11     0     1     0     0
#> 5    13     0     0     1     0
#> 
#> [[2]]
#> # A tibble: 5 x 5
#>      X2   `1`   `2`   `3`   `4`
#>       
#> 1     0     0     1     0     0
#> 2     1     0     0     1     0
#> 3     2     1     0     0     0
#> 4     3     0     0     0     1
#> 5     7     1     0     0     0

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

Frequency table and proportions stored in different dataframes

1 Answers