Summarize in dplyr and insert 0 for categories with no values

user_12242807 0
Viewed 395

Imagine that you have data like this:

set.seed(2021)

age <- floor(runif(35, min = 20, max = 25))

dat <- data.frame(age)

dat %>%
  mutate(education = sample(c("Low", "Mid-level", "High"), 
                           size = nrow(dat), prob = c(0.55, 0.2, 0.25), replace = TRUE)) %>%
  group_by(age, education) %>%
  summarise(n = n())

Results in:

     age education     n
   <dbl> <chr>     <int>
 1    20 High          1
 2    20 Low           2
 3    21 Low           3
 4    21 Mid-level     2
 5    22 High          2
 6    22 Low           4
 7    23 Low           4
 8    23 Mid-level     2
 9    24 High          1
10    24 Low          10
11    24 Mid-level     4

As you can see, for example, there are no counts for "Mid-level" education at age 20, so that category is excluded from the data frame. Is it possible to show that value as 0 instead?

E.g.

# A tibble: 11 x 3
# Groups:   age [5]
     age education     n
   <dbl> <chr>     <int>
 1    20 High          1
 2    20 Low           2
 3    20 Mid-level     0
dplyr r
3 Answers

Instead of group_by and summarise, you can use count with .drop = FALSE as an argument. You will need to make the education column factors first, so you can try adding at the last:

  count(age, as.factor(education), .drop = FALSE)

EDIT: Put factors in order for a cleaner result

dat %>%
  mutate(education = sample(
    c("Low", "Mid-level", "High"),
    size = nrow(dat),
    prob = c(0.55, 0.2, 0.25),
    replace = TRUE
  )) %>%
# convert to factor with levels in specified order
  mutate(education = factor(education, levels = c("Low", "Mid-level", "High"))) %>%
  count(age, education, .drop = FALSE)

Results in:

   age education  n
1   20       Low  2
2   20 Mid-level  0
3   20      High  1
4   21       Low  3
5   21 Mid-level  2
6   21      High  0
7   22       Low  4
8   22 Mid-level  0
9   22      High  2
10  23       Low  4
11  23 Mid-level  2
12  23      High  0
13  24       Low 10
14  24 Mid-level  4
15  24      High  1
user_153697220

Since the combination of age = 20 and education = "Mid Level" does not exist in the data frame - there is no way for summarise() to guess it.

One way to do this is to explicitly specify all possible combinations and join with the output like so:

join_df <- expand.grid(age = unique(age), 
                       education = c("Low", "Mid-level", "High"))

dat %>%
  mutate(education = sample(c("Low", "Mid-level", "High"), 
                            size = nrow(dat), prob = c(0.55, 0.2, 0.25), replace = TRUE)) %>%
  group_by(age, education) %>%
  summarise(n = n()) %>% 
  full_join(join_df, by = c("age", "education")) %>% 
  tidyr::replace_na(list(n = 0)) %>% 
  arrange(age, education)
user_59036620

Using tidyr::complete

library(dplyr)
library(tidyr)
set.seed(2021)

age <- floor(runif(35, min = 20, max = 25))

dat <- data.frame(age)

incomplete_data <- dat %>%
  mutate(education = sample(c("Low", "Mid-level", "High"), 
    size = nrow(dat), prob = c(0.55, 0.2, 0.25), replace = TRUE)) %>%
  group_by(age, education) %>%
  summarise(n = n(), .groups = "drop")

incomelpete data

# A tibble: 11 x 3
     age education     n
 * <dbl> <chr>     <int>
 1    20 High          1
 2    20 Low           2
 3    21 Low           3
 4    21 Mid-level     2
 5    22 High          2
 6    22 Low           4
 7    23 Low           4
 8    23 Mid-level     2
 9    24 High          1
10    24 Low          10
11    24 Mid-level     4

Using complete function

complete_data <- incomplete_data %>% 
  complete(age, education, fill = list(n = 0))

Output

# A tibble: 15 x 3
     age education     n
   <dbl> <chr>     <dbl>
 1    20 High          1
 2    20 Low           2
 3    20 Mid-level     0
 4    21 High          0
 5    21 Low           3
 6    21 Mid-level     2
 7    22 High          2
 8    22 Low           4
 9    22 Mid-level     0
10    23 High          0
11    23 Low           4
12    23 Mid-level     2
13    24 High          1
14    24 Low          10
15    24 Mid-level     4
user_42096570
Related