r - 以最有效的方式获取间隔期间的事件数
问题描述
样本数据
我有一个包含事件 ( dt) 的 data.table 和一个包含特定时间段内所有分钟数的 data.table ( dt.minutes)。
dt <- data.table( id = 1:3,
start = c("2019-01-01 18:00:00", "2019-01-01 19:00:00", "2019-01-01 20:00:00"),
end = c("2019-01-01 21:00:00", "2019-01-01 20:15:00", "2019-01-01 20:30:00") )
dt[, c("start", "end") := lapply( .SD,
as.POSIXct,
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
.SDcols = c("start", "end")]
dt.minutes <- data.table( from = seq( from = as.POSIXct( "2019-01-01 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
to = as.POSIXct( "2019-01-05 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
by = "1 min") )
dt.minutes[, to := from + 59 ][]
setkey( dt, start, end)
setkey( dt.minutes, from, to )
看起来像这样
> dt
id start end
1: 1 2019-01-01 18:00:00 2019-01-01 21:00:00
2: 2 2019-01-01 19:00:00 2019-01-01 20:15:00
3: 3 2019-01-01 20:00:00 2019-01-01 20:30:00
> dt.minutes
from to
1: 2019-01-01 00:00:00 2019-01-01 00:00:59
2: 2019-01-01 00:01:00 2019-01-01 00:01:59
3: 2019-01-01 00:02:00 2019-01-01 00:02:59
4: 2019-01-01 00:03:00 2019-01-01 00:03:59
5: 2019-01-01 00:04:00 2019-01-01 00:04:59
---
5757: 2019-01-04 23:56:00 2019-01-04 23:56:59
5758: 2019-01-04 23:57:00 2019-01-04 23:57:59
5759: 2019-01-04 23:58:00 2019-01-04 23:58:59
5760: 2019-01-04 23:59:00 2019-01-04 23:59:59
5761: 2019-01-05 00:00:00 2019-01-05 00:00:59
问题
对于 中的每一行（=分钟） ，我想知道在这一分钟内发生dt.minutes了多少事件。dt
我可以提出两种可能的 data.table 解决方案：
setkey( dt, start, end)
setkey( dt.minutes, from, to )
#method 1: non-equi join
ans1 <- dt.minutes[ dt.minutes, N := {
num = dt[ start <= i.to & end >= i.from ]
list( nrow(num) )
}, by = .EACHI ][]
#method 2: use foverlaps, summarise on `from` and then update-join
ans2 <- dt.minutes[, N:=0L][ foverlaps( dt, copy(dt.minutes) )[, .(N =.N), by = .(from)], N := i.N, on = .(from)]
两种方法都有效并提供了我需要的答案
all.equal( ans1, ans2 )
# [1] TRUE
但是当我查看基准时，foverlaps()以压倒性优势获胜..
# Unit: milliseconds
# expr min lq mean median uq max neval
# non_equi_join 2074.0594 2097.3363 2111.87762 2100.1306 2116.6965 2171.1653 5
# foverlaps 10.5716 10.8999 10.93622 10.9011 10.9479 11.3606 5
#
microbenchmark::microbenchmark(
non_equi_join = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT2, from, to )
DT2[ DT2, N := {
num = DT[ start <= i.to & end >= i.from ]
list( nrow(num) )
}, by = .EACHI ][]
},
foverlaps = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := 0L][ foverlaps( DT, copy(DT2) )[, .( N = .N ), by = .(from)], N := i.N, on = .(from)]
}, times = 5L
)
问题）
本着更好地理解 data.table 连接的精神，我正在寻找与foverlaps()(ans2) 相比，我的连接 (ans1) 花费这么长时间（慢 200 倍）的原因。
有没有办法提高连接的性能？或者foverlaps()只是这项工作的优化工具？
还是有更快的方法来实现我的目标？
解决方案
首先，我不确定默认type的foverlaps是否是你想要的。举个例子：
> foverlaps(dt.minutes, dt)[1368]
id start end from to
1: 1 2019-01-01 18:00:00 2019-01-01 21:00:00 2019-01-01 21:00:00 2019-01-01 21:00:59
这确实像文档指定的那样，但它似乎不是你所追求的（id应该是NA）。你可能需要type = "within".
我不熟悉 的内部结构data.table，因此以下一些内容是有根据的猜测。
使用时加入时的总结by = .EACHI是为了优化内存使用，而不是速度。如果连接中的每个结果组都非常大，那么每次只实现其中的一部分可能是值得的，但是您传递给的任何代码j都是 R 代码（通常，请参见下面的注释），即不是编译代码。连接的基本代码可能完全在 C 中进行评估，但如果您使用by = .EACHI，查找连接的匹配行可能会很快，但评估j本质上成为 R 中跨组的循环，并且如果存在相关的时间开销会增加很多小组（比如你的问题）。
我想出了另外 2 个替代方案（并稍微修改了设置），我系统中的基准测试如下所示：
library(data.table)
dt <- data.table( id = 1:3,
start = c("2019-01-01 18:00:00", "2019-01-01 19:00:00", "2019-01-01 20:00:00"),
end = c("2019-01-01 21:00:00", "2019-01-01 20:15:00", "2019-01-01 20:30:00") )
dt[, c("start", "end") := lapply( .SD,
as.POSIXct,
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
.SDcols = c("start", "end")]
dt.minutes <- data.table( from = seq( from = as.POSIXct( "2019-01-01 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
to = as.POSIXct( "2019-01-05 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
by = "1 min") )
dt.minutes[, to := from + 59 ]
library(microbenchmark)
microbenchmark::microbenchmark(
times = 5L,
non_equi_join = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[ DT2, N := {
num = DT[ start <= i.to & end >= i.from ]
list( nrow(num) )
}, by = .EACHI ]
},
foverlaps = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := 0L][ foverlaps( DT, copy(DT2) )[, .( N = .N ), by = .(from)], N := i.N, on = .(from)]
},
nej = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := DT[.SD, .(id, start), on = .(start <= from, end >= to), allow.cartesian = TRUE
][, sum(!is.na(id)), by = "start"]$V1]
},
fo = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := foverlaps(DT2, DT, type="within", which=TRUE)[, sum(!is.na(yid)), by="xid"]$V1]
}
)
Unit: milliseconds
expr min lq mean median uq max neval
non_equi_join 2506.3448 2535.3132 2597.71440 2565.4727 2647.7538 2733.6875 5
foverlaps 13.8878 14.3945 14.66726 14.9400 15.0491 15.0649 5
nej 11.6391 12.0179 13.89408 13.2644 13.3602 19.1888 5
fo 11.4082 12.7889 13.77820 12.9216 13.0430 18.7293 5
*我的版本的结果与你的不匹配，因为我在开头提到了关于type.
我们可以看到它们并没有比你的快多少，但值得注意的是nej版本。也使用了非 equi 连接，但没有by = .EACHI. 连接的整个结果首先被具体化，然后才聚合结果，在这种情况下更快。不幸的是，我不能告诉你确切的原因（同样，不熟悉内部结构），但一般的经验法则应该是，by = .EACHI只有当你期望结果中有几个大组，或者如果代码j可以通过以下方式优化时，才应该使用data.table.
顺便说一句，在fo我which = TRUE用来避免从连接返回所有列的版本中，只返回索引。由于条目的数量很重要，因此返回具有匹配项的索引的工作方式类似。在这种情况下并没有太大的不同。
*请注意foverlaps' 文档中提到通常应在x.
编辑：弗兰克的版本似乎是最快的：
dt.minutes[, n := dt[.SD, on=.(start <= from, end >= to), allow.cartesian=TRUE, .N, by=.EACHI]$N]
推荐阅读
- php - 如何使用mysql计算满足两个条件的列的所有值？
- visual-studio - 安装 Microsoft Viual Studio 社区版 v2017 时出现问题
- android - 为每个 GridLayout 正方形添加分隔线和波纹效果
- spring - java - 如何在java spring中获取自定义对象？
- python - Flask Redirect 仅在刷新页面时有效
- c# - c# 队列值无意中改变
- angular - angular 5 httpclient ，在 post 上，响应不返回自定义标头，仅返回响应对象
- reactjs - 重置而不清除 syncErrors
- vue.js - 在 Vue 单元测试中单击按钮时触发表单提交
- excel - 将彭博公式插入单元格