如何使用data.table在日期范围内执行连接？

Question

如何使用data.table完成以下操作（使用sqldf非常简单）并获得完全相同的结果：

library(data.table)

whatWasMeasured <- data.table(start=as.POSIXct(seq(1, 1000, 100),
    origin="1970-01-01 00:00:00"),
    end=as.POSIXct(seq(10, 1000, 100), origin="1970-01-01 00:00:00"),
    x=1:10,
    y=letters[1:10])

measurments <- data.table(time=as.POSIXct(seq(1, 2000, 1),
    origin="1970-01-01 00:00:00"),
    temp=runif(2000, 10, 100))

## Alternative short names for data.tables
dt1 <- whatWasMeasured
dt2 <- measurments

## Straightforward with sqldf    
library(sqldf)

sqldf("select * from measurments m, whatWasMeasured wwm
where m.time between wwm.start and wwm.end")

- Samo

---

这个解决了你的问题吗？http://stackoverflow.com/questions/5123197/matching-time-a-time-in-the-interval-between-a-start-and-end-time - David Robinson

@DavidRobinson 谢谢。我确实看过这个问题和答案，但不幸的是我的数据量很大，我真的希望有一个快速的data.table解决方案... - Samo

我知道这有点不相关，但是只有我一个人收到1969年12月31日的日期吗？它不应该是1970年1月1日吗？ - Serban Tanasa

@SerbanTanasa 我猜是时区问题... - Samo

Answer 1

您可以使用foverlaps()函数，它能有效地实现区间连接。在您的情况下，我们只需要为measurments添加一个虚拟列。

注意1：您应该安装data.table的开发版本 - v1.9.5，因为那里修复了foverlaps()的一个错误。您可以在这里找到安装说明。

注意2：我在这里为方便起见称whatWasMeasured=dt1和measurments=dt2。

require(data.table) ## 1.9.5+
dt2[, dummy := time]

setkey(dt1, start, end)
ans = foverlaps(dt2, dt1, by.x=c("time", "dummy"), nomatch=0L)[, dummy := NULL]

查看 ?foverlaps 获取更多信息以及这篇文章获取性能比较。

- Arun

---

有没有什么办法可以使用某种%between%语句来解决问题？我尝试了很久，但是无法让dt2[time %between% dt1[, list(start,end)]]按照我的期望工作。 - Serban Tanasa

@SerbanTanasa，请看一下我在答案中链接的帖子。它还比较了使用 between 的解决方案的性能。 - Arun

谢谢你的回答。太好了，它有效了。非常快。唯一的小问题是，它不喜欢开始或结束或时间的缺失值。也许有一个na.rm处理会很好。此外，在对真实数据进行此练习时，最终结果会得到两个额外的不需要的列，分别命名为“start”和“end”，这些列既不在输入数据中，也不是键的名称或其他任何东西... - Samo

@Samo，如果你使用的是1.9.5版本，请使用na.omit()和cols参数。我会考虑na.rm参数，谢谢。我不明白 - 你的data.table whatWasMeasured有两列start和end，而sqldf输出在你的帖子示例中也有相同的列。 - Arun

@Arun 谢谢。是的，请忽略我关于幻影启动和意外出现的评论。实际上它们并没有出现。感谢您的理解。我正在对“真实”数据进行测试，而不是上面的玩具示例，显然我没有刷新所有对象... - Samo