R: Counting dates within time intervals

Question

Assume we have data input:

df.in <- data.frame(event = c(1,2,3,4,5), 
                    start = c("2015-01-01", "2015-01-01", "2015-01-02",
                              "2015-01-02", "2015-01-03"),
                    end = c("2015-01-03", "2015-01-04", "2015-01-03",
                            "2015-01-05", "2015-01-05"))
df.in$start <- as.Date(df.in$start, "%Y-%m-%d")
df.in$end <- as.Date(df.in$end, "%Y-%m-%d")

> df.in
  event      start        end
1     1 2015-01-01 2015-01-03
2     2 2015-01-01 2015-01-04
3     3 2015-01-02 2015-01-03
4     4 2015-01-02 2015-01-05
5     5 2015-01-03 2015-01-05

Goal is to count date occurrences for all events (including start, excluding end). To fill out this data frame:

df.out <- data.frame(date = c("2015-01-01", "2015-01-02", "2015-01-03", 
                              "2015-01-04", "2015-01-05"),
                     count = 0)
df.out$date <- as.Date(df.out$date, "%Y-%m-%d")
> df.out
        date count
1 2015-01-01     0
2 2015-01-02     0
3 2015-01-03     0
4 2015-01-04     0
5 2015-01-05     0

Conceptually it would look something like this:

#1 **
#2 ****
#3 ***
#4 **
#5 

So, my current idea is a loop:

for(i in seq_along(df.out$date)){
  temp.df <- df.in[df.in$start <= df.out$date[i],]
  df.out$count[i] <- nrow(temp.df) - nrow(temp.df[temp.df$end <= df.out$date[i],])
}
> df.out
        date count
1 2015-01-01     2
2 2015-01-02     4
3 2015-01-03     3
4 2015-01-04     2
5 2015-01-05     0

It works, but I am sort of afraid that this temp.df that I am invoking can potentially snowball into something very large. Given that count of events can easily go into tens or even hundreds of thousands.

So my question is - can there be a more efficient way? Perhaps by using some date packages such as lubridate where I can somehow vectorize the whole thing?

Answer 1

So I've done my research on data.table::foverlaps(). I'll leave my findings to whoever might find it useful as I honestly didn't really find these little things in searching similar posts.

Given that we are comparing intervals and we have interval only on y argument which is df.in in this particular case - we have to artificially make one. As in df.out$date2 <- df.out$date for example. Also, there is no straightforward (or I couldn't find any) way to set inclusion or exclusion of set interval endpoints. Given that we want to exclude endpoint in df.in$end we'll have to do it manually on the data table itself with plain simple df.in$end <- df.in$end - 1.

Long story short, here is a working example:

require(data.table)
df.out <- data.table(date = c("2015-01-01", "2015-01-02", "2015-01-03", 
                              "2015-01-04", "2015-01-05"),
                     count = 0)
df.out$date <- as.Date(df.out$date, "%Y-%m-%d")

df.in <- data.table(event = c(1,2,3,4,5), 
                    start = c("2015-01-01", "2015-01-01", "2015-01-02",
                              "2015-01-02", "2015-01-03"),
                    end = c("2015-01-03", "2015-01-04", "2015-01-03",
                            "2015-01-05", "2015-01-05"))
df.in$start <- as.Date(df.in$start, "%Y-%m-%d")
df.in$end <- as.Date(df.in$end, "%Y-%m-%d") - 1

setkey(df.in, start, end)
df.out$date2 <- df.out$date
df.test <- foverlaps(x = df.out, y = df.in, type = "within", by.x = c("date", "date2"), by.y = c("start", "end"))
df.test$count[!is.na(df.test$event)] <- 1
aggregate(count ~ date, data = df.test, sum)

        date count
1 2015-01-01     2
2 2015-01-02     4
3 2015-01-03     3
4 2015-01-04     2
5 2015-01-05     0

---

Alternatively, you could do

Data

df.out <- data.table(date = as.Date(c("2015-01-01", "2015-01-02", "2015-01-03", 
                              "2015-01-04", "2015-01-05")))

df.in <- data.table(event = 1:5, 
                    start = as.Date(c("2015-01-01", "2015-01-01", "2015-01-02",
                              "2015-01-02", "2015-01-03")),
                    end = as.Date(c("2015-01-03", "2015-01-04", "2015-01-03",
                            "2015-01-05", "2015-01-05")))

Solution

df.out[, `:=`(start = date, end = date)]
df.in[, end := end - 1L]
setkey(df.out, start, end)
foverlaps(df.in, df.out)[, .(count = .N), by = date]
#          date count
# 1: 2015-01-01     2
# 2: 2015-01-02     4
# 3: 2015-01-03     3
# 4: 2015-01-04     2

Or, if you want to update df.out, you could also do

res <- foverlaps(df.in, df.out, which = TRUE)[, .N, by = yid]
df.out[res$yid, Count := res$N]
df.out[is.na(Count), Count := 0L]