r long to wide time series data with start and stop time columns

Question

I like to reshape a time series data from long to wide format , with columns such as StartTime and StopTime. All variables measured during the same time interval (StartTime, StopTime) to be in the same line.

For example if this is my dataset

       Id      Time     Status     Col1
       10      2012     4          2

       11      2009     2          5
       11      2010     2          5

       12      2004     2          2
       12      2009     2          3
       12      2011     2          1
       12      2018     2          3

       17      2018     2          3
       17      2020     2          1

Expecting a dataset like this

       Id      From   To      Status      Col1
       10      2012   2012        4          2

       11      2009   2010        2          5
      
       12      2004   2009        2          2
       12      2009   2011        2          3
       12      2011   2018        2          1
       12      2018   2018        2          3
      
       17      2018   2020        2          3
       17      2020   2020        2          1

Thanks in advance for the help.

Answer 1

An option would be to create a lead column after grouping by 'Id'

library(dplyr)
 df1 %>% 
  group_by(Id) %>% mutate(To = if(n() == 1) Time else 
     lead(Time, default = last(Time)), .before = Status) %>% 
  ungroup %>% 
  rename(From = Time) %>%
  filter(!is.na(To))