R Find overlap among time periods

Question

after a lot fo thinking and googling I could not find the solution to my problem, I hope you can help me.

I have a large data frame with an ID column that can repeat more than 2 times, a start and and end date column that would make up a time period. I would like to find out, grouping by ID, if any of the time periods for that ID overlap with another one, and if so, flag it by creating a new column for example, saying if that ID has overlaps or not.

Here is an example data frame already with the desired new column:

structure(list(ID= c(34L, 34L, 80L, 80L, 81L, 81L, 81L, 94L, 
94L), Start = structure(c(1072911600, 1262300400, 1157061600, 
1277935200, 1157061600, 1277935200, 1157061600, 1075590000, 1285891200
), class = c("POSIXct", "POSIXt"), tzone = ""), End = structure(c(1262214000, 
1409436000, 1251669600, 1404079200, 1251669600, 1404079200, 1251669600, 
1264892400, 1475193600), class = c("POSIXct", "POSIXt"), tzone = ""), 
    Overlap = c(FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, 
    FALSE, FALSE)), .Names = c("ID", "Start", "End", "Overlap"
), row.names = c(NA, -9L), class = "data.frame")


 ID               Start                 End Overlap
 34 2004-01-01 00:00:00 2009-12-31 00:00:00   FALSE
 34 2010-01-01 00:00:00 2014-08-31 00:00:00   FALSE
 80 2006-09-01 00:00:00 2009-08-31 00:00:00   FALSE
 80 2010-07-01 00:00:00 2014-06-30 00:00:00   FALSE
 81 2006-09-01 00:00:00 2009-08-31 00:00:00    TRUE
 81 2010-07-01 00:00:00 2014-06-30 00:00:00    TRUE
 81 2006-09-01 00:00:00 2009-08-31 00:00:00    TRUE
 94 2004-02-01 00:00:00 2010-01-31 00:00:00   FALSE
 94 2010-10-01 02:00:00 2016-09-30 02:00:00   FALSE

In this case, for ID "81" there is an overlap between two time periods, so I would like to flag all rows with ID = 81 as TRUE, meaning that an overlap in at least two rows of that ID was found. This is just a desired solution, but in general, all I want to do is find out the overlaps when grouping by ID, so the way of flagging it can be flexible, in case it simplifies things.

Thanks in advance for any help.

Answer 1

Another option - assuming df contains your data frame, then:

library(data.table)
dt <- data.table(df, key=c("Start", "End"))[, `:=`(Overlap=NULL, row=1:nrow(df))]
overlapping <- unique(foverlaps(dt, dt)[ID==i.ID & row!=i.row, ID])
dt[, `:=`(Overlap=FALSE, row=NULL)][ID %in% overlapping, Overlap:=TRUE][order(ID, Start)]
#    ID               Start                 End Overlap
# 1: 34 2004-01-01 00:00:00 2009-12-31 00:00:00   FALSE
# 2: 34 2010-01-01 00:00:00 2014-08-31 00:00:00   FALSE
# 3: 80 2006-09-01 00:00:00 2009-08-31 00:00:00   FALSE
# 4: 80 2010-07-01 00:00:00 2014-06-30 00:00:00   FALSE
# 5: 81 2006-09-01 00:00:00 2009-08-31 00:00:00    TRUE
# 6: 81 2006-09-01 00:00:00 2009-08-31 00:00:00    TRUE
# 7: 81 2010-07-01 00:00:00 2014-06-30 00:00:00    TRUE
# 8: 94 2004-02-01 00:00:00 2010-01-31 00:00:00   FALSE
# 9: 94 2010-10-01 02:00:00 2016-09-30 02:00:00   FALSE

Answer 2

I think this is the code that you are looking for? Let me know.

data<- structure(list(ID= c(34L, 34L, 80L, 80L, 81L, 81L, 81L, 94L, 
                            94L), Start = structure(c(1072911600, 1262300400, 1157061600, 
                                                      1277935200, 1157061600, 1277935200, 1157061600, 1075590000, 1285891200
                            ), class = c("POSIXct", "POSIXt"), tzone = ""), End = structure(c(1262214000, 
                                                                                              1409436000, 1251669600, 1404079200, 1251669600, 1404079200, 1251669600, 
                                                                                              1264892400, 1475193600), class = c("POSIXct", "POSIXt"), tzone = ""), 
                      Overlap = c(FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, 
                                  FALSE, FALSE)), .Names = c("ID", "Start", "End", "Overlap"
                                  ), row.names = c(NA, -9L), class = "data.frame")

library("dplyr")
library("lubridate")

overlaps<- function(intervals){
        for(i in 1:(length(intervals)-1)){
                for(j in (i+1):length(intervals)){
                        if(int_overlaps(intervals[i],intervals[j])){
                                return(TRUE)
                        }
                }
        }
        return(FALSE)
}

data %>%
        mutate(Interval=interval(Start,End))%>%
        group_by(ID) %>% 
       do({
               df<-.
               ovl<- overlaps(df$Interval)
               return(data.frame(ID=df$ID[1], ovl))
       })

Also, I hope that someone comes up with a more elegant solution to my overlaps function..