Spilt a column into multiple columns using R-CodePudding

I have actin polymerization movies to analyse. For this I used the TSOAX software which segments and measures the length of all filaments at each frame (750 timepoints). I have a data frame DF_T2_100522_IDonly which contains 1 column and many rows :

ID_filament 
2937    
2937    
2937
55546
55546
7789
99096
Frame 1
65439
65439
65439
65439
8764
8764
Frame 2
...
776543
776543
776543
2345
2345
Frame 750

I would like to split the colomn into several colums according to the frame number to get something like this :

Frame 1  Frame 2 ... Frame 750
2937     65439       776543 
2937     65439       776543
2937     65439       776543
55546    65439       2345
55546    8764        2345
7789     8764 
99096

It's a bit trickier than I had hoped. Any suggestions would be appreciated :)

A desperate biologist

CodePudding user response：

A possible solution:

library(tidyverse)

df %>% 
  mutate(name = if_else(str_detect(ID_filament, "Frame"), ID_filament, NA_character_)) %>% 
  fill(name, .direction = "up") %>% 
  filter(ID_filament != name) %>% 
  mutate(id = data.table::rowid(name)) %>% 
  pivot_wider(id_cols = id, values_from = ID_filament, values_fill = "0")

#> # A tibble: 7 × 4
#>      id `Frame 1` `Frame 2` `Frame 750`
#>   <int> <chr>     <chr>     <chr>      
#> 1     1 2937      65439     776543     
#> 2     2 2937      65439     776543     
#> 3     3 2937      65439     776543     
#> 4     4 55546     65439     2345       
#> 5     5 55546     8764      2345       
#> 6     6 7789      8764      0          
#> 7     7 99096     0         0