rreshapeminute

How do you change time interval data to minute by minute data in R?

My data has information on time interval and activity during that time (indicated by 1,0). For example,

  ID#   Time_Start  Time_End   Activity 
   1     10:05:58   10:07:11      1
   1     10:07:12   10:10:01      0

I would like to round the time to nearest minute and convert this data to minute by minute data. For example,

  ID#   Time    Activity 
   1    10:05       1
   1    10:06       1      
   1    10:07       1
   1    10:08       0
   1    10:09       0

How would I convert this in R?

Solution

  • We can change Time_Start and Time_End values to POSIXct and round it down to nearest minute using floor_date. Then create a sequence between start and end values with an interval of every minute, keep only unique values and get Time in required format.

    library(dplyr)

df %>%
  mutate(across(starts_with('Time_'), ~lubridate::floor_date(
                                 as.POSIXct(.,format = "%T"), 'minute')), 
         Time = purrr::map2(Time_Start, Time_End, seq, by = 'min')) %>%
  tidyr::unnest(Time) %>%
  select(-starts_with('Time_')) %>%
  distinct(ID, Time, .keep_all = TRUE) %>%
  mutate(Time = format(Time, '%H:%M'))

#     ID Activity Time 
#  <int>    <int> <chr>
#1     1        1 10:05
#2     1        1 10:06
#3     1        1 10:07
#4     1        0 10:08
#5     1        0 10:09
#6     1        0 10:10

    data

    df <- structure(list(ID = c(1L, 1L), Time_Start = c("10:05:58", "10:07:12"
), Time_End = c("10:07:11", "10:10:01"), Activity = 1:0), 
class = "data.frame", row.names = c(NA, -2L))