Unstacking a column into several columns with differing number of rows

Question

I have a data frame with two columns:

dat_test <- data.frame(code     = c("A", "A", "A", "A", "B", "B", "B", "C", "C"),
                       response = c("A", "AA", "AB", "ABC", "BC", "AB", "B", "CDE", "BC"),
                       stringsAsFactors = FALSE)

dat_test

  code response  n
1    A        A 69
2    A       AA 85
3    A       AB 10
4    A      ABC 87
5    B       BC  3
6    B       AB 79
7    B        B 50
8    C      CDE 81
9    C       BC 32

I now want to unstack/pivot_wider this data in a way (ideally in the tidyverse), where the code column is reshaped into three columns and rows would just be values from the response column. There is no logical link between the three codes, i.e. there is no identifier that would tell me which response of code "A" would belong to the same row for code "B" and "C", respectively.

So basically I just want three columns that lists all the responses as rows with everything else filled as NAs.

I tried tidyr's pivot_wider, but failed. I saw that there are some parameters in the pivot_wider function, likes values_fill and values_fn, but I don#t know if I could use these to get the desired behaviour.

Expected outcome would be:

# A tibble: 4 x 3
  A     B     C    
  <chr> <chr> <chr>
1 A     BC    CDE  
2 AA    AB    BC   
3 AB    B     NA   
4 ABC   NA    NA

Any ideas?

I guess I could first create an additional id column that goes from 1:4 for code "A", 1:3 for code "B" and 1:2 for code "C", but not sure how I could do this dynamically for any other data set (i.e. with a different number of codes and responses per code). But once I have such an id column, the pivoting should work.

Related : https://stackoverflow.com/questions/58837773/pivot-wider-issue-values-in-values-from-are-not-uniquely-identified-output-w/ — Ronak Shah, May 29 '20 at 12:04

score 0 · Answer 1 · answered May 29 '20 at 12:00

Oh my, nevermind (can be closed).

Creating the id was simple enough. This code works like a charme:

dat_test %>%
  group_by(code) %>%
  mutate(id = 1:n()) %>%
  ungroup() %>%
  pivot_wider(names_from = "code", values_from = "response")

and gives:

# A tibble: 4 x 4
     id A     B     C    
  <int> <chr> <chr> <chr>
1     1 A     BC    CDE  
2     2 AA    AB    BC   
3     3 AB    B     NA   
4     4 ABC   NA    NA

score 0 · Answer 2 · answered May 29 '20 at 12:23

You gave me the answer yourself. :)

answer <- dat_test %>% 
            group_by(code) %>% 
            mutate(id=row_number()) %>% 
            pivot_wider(names_from=code, values_from=response) %>% 
            ungroup()

Gives

# A tibble: 4 x 4
     id A     B     C    
  <int> <chr> <chr> <chr>
1     1 A     BC    CDE  
2     2 AA    AB    BC   
3     3 AB    B     NA   
4     4 ABC   NA    NA

Unstacking a column into several columns with differing number of rows

2 Answers2