R: Many-to-Many to one observation per row

mapleleaf 0
Viewed 20

I have a large data set and I want to make sure I am doing this correctly

library(dplyr)

ID <- c("1", "1", "1", "2", "2", "2", "3", "3", "3")
response <- c("yes", "yes", "yes", "no", "no", "no", "no", "no", "no")
rule <- c(1, 2, 3, 4, 2, 3, 5, 1, 4)
color <- c("red", "red", "red", "blue", "blue", "blue", "pink", "pink", "pink")
age <- c(1,1,1,2,2,2,3,3,3)

df <- data.frame(ID, response, rule, color, age)
df

ID response rule color age
1  1      yes    1   red   1
2  1      yes    2   red   1
3  1      yes    3   red   1
4  2       no    4  blue   2
5  2       no    2  blue   2
6  2       no    3  blue   2
7  3       no    5  pink   3
8  3       no    1  pink   3
9  3       no    4  pink   3

I need a column for each rule. So I pivot wider, and I think I have to ungroup()

df %>%
  mutate(n = 1) %>%
  tidyr::pivot_wider(names_from = rule,
                     values_from = n,
                     values_fill = list(n = 0)) %>%
  group_by(ID,
           response,
           color,
           age) %>%
  summarise(across(`1`:`5`, sum)) %>%
  ungroup()

  ID    response color   age   `1`   `2`   `3`   `4`   `5`
  <chr> <chr>    <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1     yes      red       1     1     1     1     0     0
2 2     no       blue      2     0     1     1     1     0
3 3     no       pink      3     1     0     0     1     1

I am pretty sure I have to ungroup(). Can anyone confirm that I am doing this correctly? Or, does anyone have a better idea?

data-wrangling dplyr r
2 Answers

Easier way:

df %>% mutate(value = 1)  %>% spread(rule, value,  fill = 0 )

Output:

  ID response color age 1 2 3 4 5
1  1      yes   red   1 1 1 1 0 0
2  2       no  blue   2 0 1 1 1 0
3  3       no  pink   3 1 0 0 1 1
Juan C0

We could use the values_fn in pivot_wider and skip the mutate step

library(tidyr)
pivot_wider(df, names_from = rule, values_from = rule, 
    values_fn = length, values_fill = 0)

-output

# A tibble: 3 × 9
  ID    response color   age   `1`   `2`   `3`   `4`   `5`
  <chr> <chr>    <chr> <dbl> <int> <int> <int> <int> <int>
1 1     yes      red       1     1     1     1     0     0
2 2     no       blue      2     0     1     1     1     0
3 3     no       pink      3     1     0     0     1     1
akrun0
Related