CODEWITHSUNDEEP
rdplyr
haimen
haimen

Reputation: 2015

Finding the number of missing times between two time variables

I have two time variables,

time1
6/15/16 8:00
6/15/16 9:00
6/15/16 10:00
6/16/16 8:00
6/16/16 9:00
6/17/16 8:00
6/18/16 8:00
6/18/16 8:30
6/18/16 9:10
6/19/16 8:00
6/20/16 8:00
6/20/16 11:00

time2
6/15/16 7:58
6/16/16 8:03
6/16/16 9:01
6/17/16 8:00
6/18/16 8:02
6/19/16 8:00
6/20/16 8:00

I want to find the closest possible time between the two time variables. I want to keep some boundary for the close say eg, 4 mins. If the time difference between two times are less than or equal to 4 mins, I would like to equate both, else I would like to take count of how many values that are not equating to each other between the limits. My sample output should be like,

time1              time2     time difference    number of values missed
6/15/16 8:00    6/15/16 7:58       2                         2
6/16/16 8:00    6/16/16 8:03       3                         0
6/16/16 9:00    6/16/16 9:01       1                         0
6/17/16 8:00    6/17/16 8:00       0                         0
6/18/16 8:00    6/18/16 8:02       2                         2
6/19/16 8:00    6/19/16 8:00       0                         0
6/20/16 8:00    6/20/16 8:00       0                         1

Where the time1 and time2 are equated and time difference is the mins difference between the two and number of values missed variable would show the count of number of values that didn't have a match between the current row and next row that had a match.

I am finding it difficult to put this into code. Can anybody give some idea to start on this or any way to solve this?

Thanks

Upvotes: 0

Views: 59

Answers (1)

r2evans
r2evans

Reputation: 160407

Your data:

times1 <- structure(c(1466002800, 1466006400, 1466010000, 1466089200, 1466092800, 
1466175600, 1466262000, 1466263800, 1466266200, 1466348400, 1466434800, 
1466445600), class = c("POSIXct", "POSIXt"), tzone = "")

times2 <- structure(c(1466002680, 1466089380, 1466092860, 1466175600, 1466262120, 
1466348400, 1466434800), class = c("POSIXct", "POSIXt"), tzone = "")

Does this do what you want?

library(dplyr)
expand.grid(time1 = times1, time2 = times2) %>%
  mutate(
    diff = abs(difftime(time1, time2, units = "min"))
    ) %>%
  filter(diff <= 4) %>%
  arrange(time1) %>%
  mutate(
    missed = match(time1, times1),
    missed = c(diff(missed) - 1,
               length(times1) - tail(missed, n=1))
  )
#                 time1               time2   diff missed
# 1 2016-06-15 08:00:00 2016-06-15 07:58:00 2 mins      2
# 2 2016-06-16 08:00:00 2016-06-16 08:03:00 3 mins      0
# 3 2016-06-16 09:00:00 2016-06-16 09:01:00 1 mins      0
# 4 2016-06-17 08:00:00 2016-06-17 08:00:00 0 mins      0
# 5 2016-06-18 08:00:00 2016-06-18 08:02:00 2 mins      2
# 6 2016-06-19 08:00:00 2016-06-19 08:00:00 0 mins      0
# 7 2016-06-20 08:00:00 2016-06-20 08:00:00 0 mins      1

Upvotes: 2

Related Questions