CODEWITHSUNDEEP
rdateoverlapping
NeReiS
NeReiS

Reputation: 97

Compare date intervals within the same data frame

I have search around and find similar questions but can make it work for my data.

I have a data frame with start and end dates, as well as several other factors. Ideally, the start date of a row should be posterior to the end date of any previous row, but the data has duplicated starts or ends, and sometimes the interval of the dates overlap.

I tried to make a reproducible example:

df = data.frame(start=c("2018/04/15 9:00:00","2018/04/15 9:00:00","2018/04/16 10:20:00","2018/04/16 15:30:00",
                   "2018/04/17 12:40:00","2018/04/17 18:50:00"),
                end=c("2018/04/16 8:00:00","2018/04/16 7:10:00","2018/04/17 18:20:00","2018/04/16 16:30:00",
                   "2018/04/17 16:40:00","2018/04/17 19:50:00"),
                value=c(10,15,11,13,14,12))

I was able to remove the duplicated (end or start dates), but I can't remove the overlapping intervals. I want to create a loop that "cleans" the intervals contained within any larger interval. So the results looks like this:

result = df[c(1,3,6),]

I thought I could make a loop that would "clean" both duplicates and overlapping intervals, but I can't make it work.

Any suggestions?

Upvotes: 2

Views: 482

Answers (2)

Ekatef
Ekatef

Reputation: 1061

Alternative approach is to use %within% of the lubridate() package:

library(lubridate)
# transform characters to dates
start_time <- as_datetime(df[ , "start"], tz = "UTC")
end_time <- as_datetime(df[ , "end"], tz = "UTC")
# construct intervals
start_end_intrvls <- interval(start_time, end_time)
# find indices of the non-within intervals
not_within <- !(sapply(FUN = function(i) any(start_end_intrvls[i] %within% start_end_intrvls[-i]), 
    X = seq(along.with = df[ , "start"])))
df[not_within, ]
#                 start                 end value
# 1  2018/04/15 9:00:00  2018/04/16 8:00:00    10
# 3 2018/04/16 10:20:00 2018/04/17 18:20:00    11
# 6 2018/04/17 18:50:00 2018/04/17 19:50:00    12

Update

The as_datetime() function causes an error when being applied to a tibble:

as_datetime(tibble("2018/04/15 9:00:00"), tz = "UTC")

Error in as.POSIXct.default(x) : 
  do not know how to convert 'x' to class “POSIXct”

The solution above may be modified to resolve this issue with substitution of the as_datetime() with the as.POSIXlt():

df_tibble <- tibble(start=c("2018/04/15 9:00:00","2018/04/15 9:00:00","2018/04/16 10:20:00",
    "2018/04/16 15:30:00", "2018/04/17 12:40:00","2018/04/17 18:50:00"),
     end=c("2018/04/16 8:00:00","2018/04/16 7:10:00","2018/04/17 18:20:00","2018/04/16 16:30:00",
     "2018/04/17 16:40:00","2018/04/17 19:50:00"), value=c(10,15,11,13,14,12))

start_time_lst <- lapply(FUN = function(i) as.POSIXlt(as.character(df_tibble[i , "start"]),
    tz = "UTC"),
    X = seq(along.with = unlist(df_tibble[ , "start"])))
end_time_lst <- lapply(FUN = function(i) as.POSIXlt(as.character(df_tibble[ i, "end"]),
    tz = "UTC"),
    X = seq(along.with = unlist(df_tibble[ , "end"])))
start_end_intrvls <- lapply(function(i) interval(start_time_lst[[i]] , end_time_lst[[i]]), 
    X = seq(along.with = unlist(df_tibble[ , "start"])))
not_within <- sapply(function(i) !(any(unlist(Map(`%within%`, 
    start_end_intrvls[[i]], start_end_intrvls[-i])))), 
    X = seq(along.with = unlist(df_tibble[ , "start"])))

Upvotes: 1

chinsoon12
chinsoon12

Reputation: 25225

The data.table package is suited for this kind of problem using the overlapping join function foverlaps (inspired by findOverlaps function from the Bioconductor package IRanges) and then an anti-join (data.table syntax is B[!A, on]) to remove those inner intervals.

library(data.table)
cols <- c("start", "end")
setDT(df)
df[, (cols) := lapply(.SD, function(x) as.POSIXct(x, format="%Y/%m/%d %H:%M:%S")), .SDcols=cols]
setkeyv(df, cols)
anti <- foverlaps(df, df, type="within")[start!=i.start | end!=i.end | value!=i.value]
df[!anti, on=.(start=i.start, end=i.end, value=i.value)]

#                  start                 end value
# 1: 2018-04-15 09:00:00 2018-04-16 08:00:00    10
# 2: 2018-04-16 10:20:00 2018-04-17 18:20:00    11
# 3: 2018-04-17 18:50:00 2018-04-17 19:50:00    12

Upvotes: 1

Related Questions