1

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?

Louis
  • 55
  • 5
  • How are values stored in your data.frame currently? Are they character values? Or some other object type? Be sure to share data in a [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) rather than just the printed output. – MrFlick Aug 10 '20 at 01:42

1 Answers1

5

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))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Thanks! this is helpful. I tried to run this but got an error. Saying that 'origin' must be supplied: Problem with `mutate()` input `..1`. x 'origin' must be supplied i Input `..1` is `across(...)`. – Louis Aug 13 '20 at 17:54
  • @Louis Can you try running this with the data I have provided in my post? Does it work for you then? If it does and you have problems running it on your data I would suggest to add `dput(head(df))` of your data similar to what I have shared so that I would know how is your data different than the one I am using. – Ronak Shah Aug 13 '20 at 23:42
  • Thanks for the help! I had to put origin, and added group_by(ID) and the code runs well. – Louis Aug 20 '20 at 18:07