Tidy way to collapse rows when a spread/pivot won't work

tidyverse
2

Your last approach is close, but by adding two columns (dup_count and key), you're stopping it from collapsing. Add a single column and it's clean:

library(tidyverse)

df <- tibble::tibble(
    id = c(1, 1, 2, 2, 3, 4, 4), 
    code = c("A", "B", "C", "D", "E", NA, NA)
)

df %>% 
    group_by(id) %>% 
    mutate(key = paste0('new_code_', row_number())) %>% 
    spread(key, code)
#> # A tibble: 4 x 3
#> # Groups:   id [4]
#>      id new_code_1 new_code_2
#>   <dbl> <chr>      <chr>     
#> 1     1 A          B         
#> 2     2 C          D         
#> 3     3 E          <NA>      
#> 4     4 <NA>       <NA>

See more examples here:

4 Likes