我已经多次回到这个问题,不断寻找最有效的解决方法。
之前我使用过map-reduce方法,但发现它不能很好地处理具有广泛日期间隔的大型数据框。我最近尝试使用lubridate
包中的interval
类,并发现这是迄今为止最快的实现。
以下是最终代码:
library(tidyverse)
library(lubridate)
N = 1000
id_dates = tibble(id = 1 : N) %>%
mutate(
start = sample(seq(as.Date('2018-1-1'), as.Date('2019-1-1'), by = "day"), size = N, replace = TRUE),
end = start + sample(7 : 100, size = N, replace = TRUE),
interval = interval(start, end))
queue_history = tibble(Date = seq(min(id_dates$start), max(id_dates$end), by = "1 day")) %>%
rowwise() %>%
mutate(numInWIP = sum(Date %within% id_dates$interval)) %>%
ungroup()
以下是一些基准测试结果,表明lubridate解决方案比当前答案和map-reduce方法都要快得多
library(tidyverse)
library(lubridate)
N = 1000
id_dates = tibble(id = 1 : N) %>%
mutate(
start = sample(seq(as.Date('2018-1-1'), as.Date('2019-1-1'), by = "day"), size = N, replace = TRUE),
end = start + sample(7 : 100, size = N, replace = TRUE),
interval = interval(start, end))
method_mapreduce = function() {
queue_history = as.tibble(table(reduce(map2(id_dates$start, id_dates$end, seq, by = 1), c)))
queue_history = queue_history %>%
rename(Date = Var1, numInWIP = Freq) %>%
mutate(Date = as_date(Date))
return (queue_history)
}
method_intervals = function() {
date_df = tibble(Date = seq(min(id_dates$start), max(id_dates$end), by = "1 day"))
queue_history = date_df %>%
rowwise() %>%
mutate(numInWIP = sum(Date %within% id_dates$interval))
return (queue_history)
}
method_currentsolution = function() {
date_df = tibble(Date = seq(min(id_dates$start), max(id_dates$end), by = "1 day"))
queue_history = merge(id_dates, date_df, all=TRUE) %>%
filter(Date >= start, Date <= end) %>%
group_by(Date) %>%
summarise(n())
}
tst = microbenchmark::microbenchmark(
method_mapreduce(),
method_intervals(),
method_currentsolution(),
times = 5)
microbenchmark::autoplot.microbenchmark(tst) +
scale_y_log10(
name = sprintf("Time [%s]", attr(summary(tst), "unit")),
breaks = scales::trans_breaks("log10", function(x) round(10^x)))
![](https://i.imgur.com/Wmwslf0.png)
data.table
包中的foverlaps()
函数。或者在这里在SO上搜索它。 - Arunwhere d.date is between startdate and enddate
。 - G. Grothendieck