Pivot_wider and count number of occurrences retaining all other columns-CodePudding

I want to pivot_wider to spread the column issue. The value should be the number of occurences of each issue per product:

library(dplyr)
library(tidyr)

df = data.frame(product=  c(1,1,1,2,2,2,3,3,3),
                type = c(rep("a",3),rep("b", 3),rep("a",3)),
                issue = c("fall","fall","trap",
                          "trap", "jump", "fall",
                          "trap", "jump", "open"))

Output should be

  product type fall trap jump open
1       1    a    2    1   NA   NA
2       2    b    1    1    1   NA
3       3    a   NA    1    1    1

I have tried the answer provided here: pivot_wider, count number of occurrences but this does not retain other columns:

df%>%
  group_by(product)%>%
  pivot_wider(id_cols = "product", names_from = "issue", values_from = type, values_fn = list(type = length))


 product  fall  trap  jump  open
    <dbl> <int> <int> <int> <int>
1       1     2     1    NA    NA
2       2     1     1     1    NA
3       3    NA     1     1     1

Is there another way to do this?

CodePudding user response：

You can count them first:

library(dplyr)
library(tidyr)

df = data.frame(product=  c(1,1,1,2,2,2,3,3,3),
                type = c(rep("a",3),rep("b", 3),rep("a",3)),
                issue = c("fall","fall","trap",
                          "trap", "jump", "fall",
                          "trap", "jump", "open"))
df %>% 
  count(product, type, issue) %>% 
  pivot_wider(names_from = issue,
              values_from = n)
#> # A tibble: 3 × 6
#>   product type   fall  trap  jump  open
#>     <dbl> <chr> <int> <int> <int> <int>
#> 1       1 a         2     1    NA    NA
#> 2       2 b         1     1     1    NA
#> 3       3 a        NA     1     1     1

^{Created on 2022-05-25 by the reprex package (v2.0.1)}