Transform the frequency count data frame of categories columns in R-CodePudding

I've used the following code to create a frequency count.

df %>% group_by(INCOME, HAPPY) %>% summarise(count=n())

Output:

   INCOME HAPPY count
    <int> <int> <int>
 1      1     1     6
 2      1     2    17
 3      1     3    13
 4      1     8     1
 5      2     1     5
 6      2     2    11
 7      2     3    12
 8      2     8     0
 9      3     1     4
10      3     2    10
11      3     3     5
12      3     8     0

Yet, I would like to have the following frequency format.

    1       2       3
1   6       5       4
2   17      11      10
3   13      12      5
8   1       0       0

CodePudding user response：

Using xtabs from base R

 xtabs(count ~  HAPPY   INCOME, df1)
     INCOME
HAPPY  1  2  3
    1  6  5  4
    2 17 11 10
    3 13 12  5
    8  1  0  0

data

df1 <- structure(list(INCOME = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 
3L, 3L, 3L), HAPPY = c(1L, 2L, 3L, 8L, 1L, 2L, 3L, 8L, 1L, 2L, 
3L, 8L), count = c(6L, 17L, 13L, 1L, 5L, 11L, 12L, 0L, 4L, 10L, 
5L, 0L)), class = "data.frame", row.names = c("1", "2", "3", 
"4", "5", "6", "7", "8", "9", "10", "11", "12"))

CodePudding user response：

After your code: df %>% group_by(INCOME, HAPPY) %>% summarise(count=n())

You could use this code to achieve your task:

library(dplyr)
library(tidyr)
library(tibble)
df %>% 
  mutate(group_id = as.integer(gl(n(), 4, n()))) %>% 
  pivot_wider(
    HAPPY,
    names_from = group_id,
    values_from = count
  ) %>%
  column_to_rownames("HAPPY")

data:

structure(list(INCOME = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 
3L, 3L, 3L), HAPPY = c(1L, 2L, 3L, 8L, 1L, 2L, 3L, 8L, 1L, 2L, 
3L, 8L), count = c(6L, 17L, 13L, 1L, 5L, 11L, 12L, 0L, 4L, 10L, 
5L, 0L)), class = "data.frame", row.names = c("1", "2", "3", 
"4", "5", "6", "7", "8", "9", "10", "11", "12"))