CODEWITHSUNDEEP
rtidyverselubridate
fschier
fschier

Reputation: 299

How do I calculate the amount of Time overlap between 2 Intervals in a table

I want to calculate the amount of time that lies in between time intervals of differenz rows in a table. Here is an example of what the data.frame would look like:

x <-tibble(name = c("Person1", "Person2", "Person3", "Person4"),
           group = c("A", "A", "B", "B"),
           start = c("2020-10-01-10:00", "2020-10-01-12:00", "2020-10-01-16:00", "2020-10-01-16:00"),
           end = c("2020-10-01-16:00", "2020-10-01-18:00", "2020-10-01-20:00", "2020-10-01-23:00")) %>%
  mutate(start = lubridate::ymd_hm(start),
         end = lubridate::ymd_hm(end)) %>%
  mutate(time_interval = interval(start = start, end = end))

I would like to generate another column that displays the amount of hours that the time_intervals of the Persons in each group overlap. I already looked for similar questions and functions but did not find any. Is there a way in R to calculate the time overlap?

Regards

Upvotes: 0

Views: 565

Answers (1)

AndreasM
AndreasM

Reputation: 942

Isn´t the overlap across the intervals the difference between the latest start time and the earliest end time (if there is any overlap)? This could be calculated like this. If there is no overlap (= "negative" overlap) 0 is returned.

require(lubridate)
x %>%
  group_by(group) %>%
  mutate(overlap = if_else(condition = max(start) < min(end), 
                           true = min(end) - max(start), 
                           false = 0))

# A tibble: 4 x 6
# Groups:   group [2]
  name    group start               end                 time_interval                                    overlap
  <chr>   <chr> <dttm>              <dttm>              <Interval>                                       <drtn> 
1 Person1 A     2020-10-01 10:00:00 2020-10-01 16:00:00 2020-10-01 10:00:00 UTC--2020-10-01 16:00:00 UTC 4 hours
2 Person2 A     2020-10-01 12:00:00 2020-10-01 18:00:00 2020-10-01 12:00:00 UTC--2020-10-01 18:00:00 UTC 4 hours
3 Person3 B     2020-10-01 16:00:00 2020-10-01 20:00:00 2020-10-01 16:00:00 UTC--2020-10-01 20:00:00 UTC 4 hours
4 Person4 B     2020-10-01 16:00:00 2020-10-01 23:00:00 2020-10-01 16:00:00 UTC--2020-10-01 23:00:00 UTC 4 hours

Upvotes: 1

Related Questions