Home Trouble converting long to wide by ID with many columns with repeating rows within ID

Questions

Trouble converting long to wide by ID with many columns with repeating rows within ID

July 15, 2022

I am having a difficult time going from long to wide on my dataframe.
I have posted a dput() of the first 10 lines below.

structure(list(PREGID = structure(c(5133081, 5133081, 5133151, 
5133151, 5133261, 5133261, 5133281, 5133281, 5133581, 5133581
), label = "pregnancy ID", format.sas = "Z"), AliquotID = c(4776236, 
4776237, 4791547, 4791548, 4770026, 5239746, 4778145, 4778146, 
4748325, 4748326), Shelf = c("Shelf 19 (00010022)", "Shelf 19 (00010022)", 
"Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", 
"Shelf 44 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", 
"Shelf 20 (00010022)", "Shelf 20 (00010022)"), Rack = c("Rack 05224 ( ) - R1C4", 
"Rack 05224 ( ) - R1C4", "Rack 05224 ( ) - R1C5", "Rack 05224 ( ) - R1C5", 
"Rack 05224 ( ) - R1C3", "Rack 05511 ( ) - R4C1", "Rack 05224 ( ) - R1C3", 
"Rack 05224 ( ) - R1C3", "Rack 05230 ( ) - R3C4", "Rack 05230 ( ) - R3C4"
), Row = c(3, 4, 7, 8, 9, 3, 8, 9, 5, 6), Column = c(7, 7, 4, 
4, 5, 7, 3, 3, 7, 7), `Stock Number` = c(305349, 305350, 305403, 
305404, 305255, 760854, 305234, 305235, 315751, 315752)), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"))

What I am after is something such as this

 PREGID    AliquotID_1  AliquotID_2    Shelf_1     Shelf_2         Rack_1           Rack_2              Row_1   Row_2 Column_1 Column_2 Stock Number_1 Stock Number_2
 5133081   4776236      4776327      "Shelf 19"    "Shelf 19"  "Rack 05224()-R1C4" "Rack 05224()-R1C4"      3       4      7       7        305349         305350
 5133151   4791547      4791548      "Shlef 19"    "Shelf 19"  "Rack 05224()-R1C5" "Rack 05224()-R1C5"      7       8      4       4        305403         305404

I appreciate all the help I have tried different versions of spread() and pivot_wider() to no good it seems to make a huge matrix with values along the diagonal
such as

Inc.tmp.1 %>% select( PREGID, AliquotID, SA, Shelf, Rack, Row, Column, Stock Number) %>% 
pivot_wider(names_from = PREGID , values_from = c("AliquotID", "SA", "Shelf", "Rack", "Row", "Column","Draw_Date","Stock Number"))

>Solution :

df %>%
  group_by(PREGID) %>%
  mutate(name = row_number())%>%
  pivot_wider(PREGID, values_from = -PREGID)

# Groups:   PREGID [5]
   PREGID AliquotID_1 AliquotID_2 Shelf_1   Shelf_2   Rack_1  Rack_2  Row_1 Row_2
    <dbl>       <dbl>       <dbl> <chr>     <chr>     <chr>   <chr>   <dbl> <dbl>
1 5133081     4776236     4776237 Shelf 19~ Shelf 19~ Rack 0~ Rack 0~     3     4
2 5133151     4791547     4791548 Shelf 19~ Shelf 19~ Rack 0~ Rack 0~     7     8
3 5133261     4770026     5239746 Shelf 19~ Shelf 44~ Rack 0~ Rack 0~     9     3
4 5133281     4778145     4778146 Shelf 19~ Shelf 19~ Rack 0~ Rack 0~     8     9
5 5133581     4748325     4748326 Shelf 20~ Shelf 20~ Rack 0~ Rack 0~     5     6
# ... with 6 more variables: Column_1 <dbl>, Column_2 <dbl>,
#   Stock Number_1 <dbl>, Stock Number_2 <dbl>, name_1 <int>, name_2 <int>