Transform long data into wide using frequency

Question

I would like to know a practical way to transform dat in the table below

dat <- data.frame('city' = c('A','A','B','C','A','B','B','C','C','C'), 
                  'color' = c('red', 'green', 'blue', 'red', 'green', 'blue', 'green', 'blue', 'red', 'red'),
                  'sex' = c('M','F','F','M','F','F','F','M','F','M'))


city red green blue F M
A    1   2     0    2 1
B    0   1     2    3 0
C    3   0     1    1 3

Answer 1

With tidyr, you can pivot_longer and then pivot_wider:

library(tidyr)

dat %>%
  pivot_longer(c(color, sex)) %>%
  pivot_wider(id_cols = city, names_from = c(name, value), names_sort = TRUE,
              values_from = value, values_fn = length, values_fill = 0)

# # A tibble: 3 × 6
#   city  color_blue color_green color_red sex_F sex_M
#   <chr>      <int>       <int>     <int> <int> <int>
# 1 A              0           2         1     2     1
# 2 B              2           1         0     3     0
# 3 C              1           0         3     1     3

Answer 2

With sapply, create multiple tables and bind them:

sapply(dat[2:3], \(x) as.data.frame.matrix(table(dat$city, x))) |>
  do.call(what = 'cbind.data.frame')

  color.blue color.green color.red sex.F sex.M
A          0           2         1     2     1
B          2           1         0     3     0
C          1           0         3     1     3