R-使用data.table进行DateTime比较

Question

我有两个data.Tables，我希望Xdata的数据大于Ydata的启动时间，而小于Ydata的末期。

我试图写一个练习，但似乎丢失了数据。

library(data.table)
xdata=data.table(First=c("X1","X2","X3","X1","X3","X2"),
             Second=c("A1","A2","B3","A1","B3","C4"),
             Time=c("2018-09-01 09:21:03","2018-10-15 20:24:59","2018-10-15 12:06:46",
                "2018-10-16 18:21:11","2018-10-16 21:21:12","2018-10-17 00:00:01"))

ydata=data.table(ID=c("YY","ZZ","AA","HH"),
             StartTime=c("2018-08-21 08:00:00","2018-09-01 08:00:00",
                "2018-10-15 08:00:00","2018-10-18 08:00:00"),
             EndTime=c("2018-08-21 21:20:00","2018-09-01 21:20:00",
                     "2018-10-15 21:20:00","2018-10-18 21:20:00"))
library(dplyr)
outputXY <- xdata %>% filter(Time > ydata$StartTime & Time < ydata$EndTime)

仅此输出

1 X3  B3  2018-10-15 12:06:46

但是我需要的是

1 X1  A1  2018-09-01 09:21:03
2 X3  B3  2018-10-15 12:06:46

我试图修改代码，但结果是相同的

outputXY <- xdata[Time > ydata$StartTime & Time < ydata$EndTime]

如何修改它并做我想做的事？

Answer 1

如果我正确理解，则OP希望在 xdata中找到 all 行，其中 Time位于给定间隔的任何中（ StartTime），EndTime）在ydata中。

为此目的构建了来自数据的inrange()函数。由于OP已要求使用打开间隔（Time > ydata$StartTime & Time < ydata$EndTime），我们需要告诉inrange()以排除终点。

library(data.table)
# coerce to POSIXct to allow for comparison operations
xdata[, Time := as.POSIXct(Time)]
tcols <- c("StartTime", "EndTime")
ydata[, (tcols) := lapply(.SD, as.POSIXct), .SDcols = tcols]
# subsetting with open intervals
xdata[inrange(Time, ydata$StartTime, ydata$EndTime, incbounds = FALSE)]

   First Second                Time
1:    X1     A1 2018-09-01 09:21:03
2:    X2     A2 2018-10-15 20:24:59
3:    X3     B3 2018-10-15 12:06:46

so，3行xdata符合条件。

---

如果OP会要求封闭间隔（Time >= ydata$StartTime & Time <= ydata$EndTime），我们可以使用 inline inrange()的版本：

# subsetting with closed intervals
xdata[Time %inrange% ydata[, .(StartTime, EndTime)]]

Answer 2

您需要考虑如何加入这两个数据集。现在，我最好的猜测是，您想要YDATA开始和结束时间的任何组合之间的所有XDATA时间。但是您的代码在向量上工作，因此它正在检查每个矢量元素是否通过比测试大且少于测试。

让我们展示数据如何按照您的方式排列：

xdata$Time              ydata$StartTime        ydata$EndTime
"2018-09-01 09:21:03"   "2018-08-21 08:00:00"  "2018-08-21 21:20:00"
"2018-10-15 20:24:59"   "2018-09-01 08:00:00"  "2018-09-01 21:20:00"
"2018-10-15 12:06:46"   "2018-10-15 08:00:00"  "2018-10-15 21:20:00"
"2018-10-16 18:21:11"   "2018-10-18 08:00:00"  "2018-10-18 21:20:00"
"2018-10-16 21:21:12"   "2018-08-21 08:00:00"  "2018-08-21 21:20:00"  # recycled                     
"2018-10-17 00:00:01"   "2018-09-01 08:00:00"  "2018-09-01 21:20:00"  # recycled  

请注意，当数据向矢量元素并排显示给您时，您可以看到满足条件的唯一行是用于"2018-10-15 12:06:46" "2018-10-15 08:00:00" "2018-10-15 21:20:00" ...

做到这一点的一种方法是使用CJ函数创建数据。然后，我们可以为是否在任何可能的时间范围内创建一个查找。

# Create a table with all combinations to Time and StartTime
timecheck <- CJ(Time = xdata$Time,StartTime = ydata$StartTime)
# Join in the EndTime
timecheck <- merge(timecheck,ydata,by = "StartTime")
# Use vector math to check if the Time is between StartTime and EndTime
# for every comination of possibilities.
timecheck[,in_range := (Time > StartTime & Time < EndTime)]
# group_by Time and create a summary of whether or not that time is in
# any range
timecheck <- timecheck[,any(in_range),.(Time)]
outputXY <- xdata %>% filter(timecheck$V1)

这为您提供了：

的输出

  First Second                Time
1    X1     A1 2018-09-01 09:21:03
2    X2     A2 2018-10-15 20:24:59
3    X3     B3 2018-10-15 12:06:46

我建议您运行代码的每个步骤，并查看每个中介步骤中要存储的内容。同样，还有其他方法可以使用循环进行此操作，这些循环可能会减少内存，但不要利用向量操作。

Answer 3

也许是这样的？ - 假设每天都有相同的时间范围：

编辑：仅考虑ydata中存在的日期

library(data.table)
xdata=data.table(First=c("X1","X2","X3","X1","X3","X2"),
                 Second=c("A1","A2","B3","A1","B3","C4"),
                 Time=c("2018-09-01 09:21:03","2018-10-15 20:24:59","2018-10-15 12:06:46",
                        "2018-10-16 18:21:11","2018-10-16 21:21:12","2018-10-17 00:00:01"))
ydata=data.table(ID=c("YY","ZZ","AA","HH"),
                 StartTime=c("2018-08-21 08:00:00","2018-09-01 08:00:00",
                             "2018-10-15 08:00:00","2018-10-18 08:00:00"),
                 EndTime=c("2018-08-21 21:20:00","2018-09-01 21:20:00",
                           "2018-10-15 21:20:00","2018-10-18 21:20:00"))
xdata[, Date := as.Date(Time)]
ydata[, Date := as.Date(StartTime)]
xdata <- xdata[ydata, on = "Date", nomatch = 0]
outputXY <- xdata[Time > StartTime & Time < EndTime]
outputXY[, c("Date", "StartTime", "EndTime", "ID") := NULL]
print(outputXY)

---

，但结果是：

   First Second                Time
1:    X1     A1 2018-09-01 09:21:03
2:    X2     A2 2018-10-15 20:24:59
3:    X3     B3 2018-10-15 12:06:46