0

I am trying to move a long to wide format.

  ID     Col1                    Index  Value   Date
  1333   Lateral_mm               0     5.1     1998-04-19 
  1333   Lateral_mm               1     3.4     1998-04-19
  1333   ap_mm_axial              0     4.2     1998-04-19
  1333   ap_mm_axial              1     4.7     1998-04-19
  1333   Lateral_mm               0     NA      2001-12-31 
  1333   Lateral_mm               1     NA      2001-12-31
  1333   ap_mm_axial              0     NA      2001-12-31  
  1333   ap_mm_axial              1     NA      2001-12-31
  9876   central_star_six_mm      0     5.3     1996-09-18
  9876   central_star_six_mm      1     NA      1996-09-18
  9876   central_star_six_mm      2     NA      1996-09-18
  9876   central_star_six_mm      3     NA      1996-09-18

The final dataset I am expecting is to be like this

  ID     Lateral_mm  ap_mm_axial  central_star_six_mm  Date        Index 
  1333   5.1         4.2          NA                   1998-04-19  0
  1333   3.4         4.7          NA                   1998-04-19  1
  1333   NA          NA           NA                   2001-12-31  0
  1333   NA          NA           NA                   2001-12-31  1
  9876   NA          NA           5.3                  1996-09-18  0
  9876   NA          NA           NA                   1996-09-18  1
  9876   NA          NA           NA                   1996-09-18  2
  9876   NA          NA           NA                   1996-09-18  3

I tried spread(Col1, value) but then I am seeing Errors like Each row of output must be identified by a unique combination of keys. Keys are shared for 541 rows. I was not expect. Any suggestions are much appreciated. Thank you!

Rilo Dinga
  • 79
  • 8
  • 1
    `tidyr::pivot_wider(df, names_from = Col1, values_from = Value)` work fine and so does `tidyr::spread(df,Col1,Value)` for the dataset that you have shared. For your actual dataset try : `df %>% group_by(Col1) %>% mutate(row = row_number()) %>%pivot_wider(names_from = Col1, values_from = Value)`. – Ronak Shah Jan 29 '21 at 03:45
  • @RonakShah, thank you Superman, `mutate(row = row_number()) ` seem to make a huge difference. Please post this as an answer and I will accept it. – Rilo Dinga Jan 29 '21 at 03:51

0 Answers0