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

[ejlatour]

I want to combine duplicate rows into a one with multiple columns for the unique info. I was able to figure out a couple of ways using the tidyverse, but I'm wondering if there is a better way than what I've come up with.

I could use another set of eyes on this problem.

It is sort of the reverse of what was done in Tidy way to split a column.

data.frame(
  id = c(1, 1, 2, 2, 3), 
  code = c("A", "B", "C", "D", "E")
)
#>   id code
#> 1  1    A
#> 2  1    B
#> 3  2    C
#> 4  2    D
#> 5  3    E

library(tidyverse)

(df <- tibble::tibble(
  id = c(1, 1, 2, 2, 3, 4, 4), 
  code = c("A", "B", "C", "D", "E", NA, NA)))
#> # A tibble: 7 x 2
#>      id code 
#>   <dbl> <chr>
#> 1     1 A    
#> 2     1 B    
#> 3     2 C    
#> 4     2 D    
#> 5     3 E    
#> 6     4 <NA> 
#> 7     4 <NA>

(max_dups <- df %>% 
  dplyr::count(id) %>% 
  dplyr::pull(n) %>% 
  max(.))
#> [1] 2

df %>% 
  group_by(id) %>% 
  summarise(combo_code = paste0(code, collapse = "~~~~")) %>% 
  tidyr::separate(., 
                  col = combo_code, 
                  into = paste("new_code", 1:max_dups, sep = "_"), 
                  sep = "~~~~", 
                  remove = TRUE, 
                  fill = "right") %>% 
  mutate_at(.vars = vars(paste("new_code", 1:max_dups, sep = "_")), 
            .funs = funs(dplyr::na_if(., "NA")))
#> # A tibble: 4 x 3
#>      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>

Created on 2018-06-25 by the reprex package (v0.2.0).

I figured out a way to do it with tidyr::spread() but it does't seem as fool-proof.

## Another way ---------------- 

(df <- tibble::tibble(
  id = c(1, 1, 2, 2, 3, 4, 4), 
  code = c("A", "B", "C", "D", "E", NA, NA)))
#> # A tibble: 7 x 2
#>      id code 
#>   <dbl> <chr>
#> 1     1 A    
#> 2     1 B    
#> 3     2 C    
#> 4     2 D    
#> 5     3 E    
#> 6     4 <NA> 
#> 7     4 <NA>

library(tidyverse)

df %>% 
  group_by(id) %>% 
  mutate(dup_count = sequence(dplyr::n()), 
         key = paste("new_code", dup_count, sep = "_")) %>% 
  tidyr::spread(., 
                key = key,
                value = code) %>% 
  tidyr::fill(dplyr::starts_with("new_code"), .direction = "up") %>% 
  dplyr::distinct(., id, .keep_all = TRUE)
#> # A tibble: 4 x 4
#> # Groups:   id [4]
#>      id dup_count new_code_1 new_code_2
#>   <dbl>     <int> <chr>      <chr>     
#> 1     1         1 A          B         
#> 2     2         1 C          D         
#> 3     3         1 E          <NA>      
#> 4     4         1 <NA>       <NA>

Created on 2018-06-25 by the reprex package (v0.2.0).

[alistaire]

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:

How to spread columns with duplicate identifiers?

[ejlatour]

@alistaire Thank you so much! The method you show is very direct and much cleaner than what I was trying. I think that my logic was there, but wasn't quite there with the code yet. You examples in the link are very helpful also; I did not come across that in my searching into this problem. Thanks again for the help with this one!

[cderv]

Hi,

If your question has been answered, can you mark your question as solved

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …

It will help other knows the status of the question right from the list of question or by searching for related topic.

Thank you.