R: Frequency count but every category in a separate column

user_12955598 0
Viewed 97

With the following code I assign a quantile rank y (from 1 to 4) for every value of x. 

df$y <- ntile(df$x, 4)

Then, I would like to have four separate columns for absolute frequency count of every quantile rank, grouped also by variable z. With the following code, it does the calculation but I get all calculations in the same column.

df <-
  df %>% 
  group_by(z, y) %>% 
  mutate(Freq = n())

example:

z    y(quartile)  n_quartile_4    n_quartile 3 n_quartile 2
1         4               2             1           0
1         3               2             1           0
1         4               2             1           0
2         2               0             0           3
2         2               0             0           3
2         2               0             0           3
dplyr dataframe r
1 Answers

We could create the count column with add_count, then pivot to 'wide' format with pivot_wider, fill the NA elements with the non-NA value in the column for each group and finally replace the rest of the NAs with 0

library(dplyr)
library(tidyr)
library(stringr)
df %>% 
   add_count(z, y) %>%
   mutate(new = str_c('n_quartile_', y), rn = row_number()) %>%
   pivot_wider(names_from = new, values_from = n) %>% 
   group_by(z) %>% 
   fill(starts_with('n_quartile'), .direction = 'downup') %>% 
   ungroup %>%
   select(-rn) %>%
   mutate_at(vars(starts_with('n_quartile')), replace_na, 0)
# A tibble: 6 x 5
#      z     y n_quartile_4 n_quartile_3 n_quartile_2
#  <int> <dbl>        <dbl>        <dbl>        <dbl>
#1     1     4            2            1            0
#2     1     3            2            1            0
#3     1     4            2            1            0
#4     2     2            0            0            3
#5     2     2            0            0            3
#6     2     2            0            0            3

data

df <- structure(list(z = c(1L, 1L, 1L, 2L, 2L, 2L), y = c(4, 3, 4, 
2, 2, 2)), class = "data.frame", row.names = c(NA, -6L))
user_37322710
Related