检查重叠的时间间隔开始和结束时间

问题描述：

df = data.frame(ID= c(1,1,1,1,1,1,1), NumberInSequence= c(1,2,3,4,5,6,7), 
       StartTime = as.POSIXct(c("2016-01-15 18:02:11 GMT","2016-01-15 18:10:33 GMT","2016-01-15 18:25:08 GMT", 
               "2016-01-15 18:33:56 GMT","2016-01-15 18:21:03 GMT","2016-01-15 19:55:09 GMT","2016-01-15 19:57:03 GMT")) , 
         EndTime = as.POSIXct(c("2016-01-15 18:02:17 GMT","2016-01-15 18:10:39 GMT","2016-01-15 18:25:14 GMT", 
               "2016-01-15 18:34:02 GMT","2016-01-15 19:53:17 GMT","2016-01-15 19:56:15 GMT","2016-01-15 19:58:17 GMT")) 
         )

每一行是具有开始时间和结束时间的时间间隔

df 

ID NumberInSequence   StartTime    EndTime 
1 1    1 2016-01-15 18:02:11 2016-01-15 18:02:17 
2 1    2 2016-01-15 18:10:33 2016-01-15 18:10:39 
3 1    3 2016-01-15 18:25:08 2016-01-15 18:25:14 
4 1    4 2016-01-15 18:33:56 2016-01-15 18:34:02 
5 1    5 2016-01-15 18:21:03 2016-01-15 19:53:17 
6 1    6 2016-01-15 19:55:09 2016-01-15 19:56:15 
7 1    7 2016-01-15 19:57:03 2016-01-15 19:58:17

然后我使用dplyr添加计算下一个开始时间的几个字段以及NextStartTime和EndTime之间的差异的等待时间。这会创建“WaitTime”列，它在大多数情况下都适用，除非存在重叠的Inverals。

df %>% group_by(ID) %>% 
     mutate(
     NextStartTime = lead(StartTime)[ifelse(lead(NumberInSequence) == (NumberInSequence + 1), TRUE, NA)] , 
     WaitTime = difftime(NextStartTime,EndTime, units = 's') 
     #max_s = max(StartTime) #, 
    # cum_max_s = as.POSIXct(cummin(as.numeric(StartTime)),origin="1970-01-01") 
    ) 


    ID NumberInSequence   StartTime    EndTime  NextStartTime WaitTime 
1 1    1 2016-01-15 18:02:11 2016-01-15 18:02:17 2016-01-15 18:10:33 496 secs 
2 1    2 2016-01-15 18:10:33 2016-01-15 18:10:39 2016-01-15 18:25:08 869 secs 
3 1    3 2016-01-15 18:25:08 2016-01-15 18:25:14 2016-01-15 18:33:56 522 secs 
4 1    4 2016-01-15 18:33:56 2016-01-15 18:34:02 2016-01-15 18:21:03 -779 secs 
5 1    5 2016-01-15 18:21:03 2016-01-15 19:53:17 2016-01-15 19:55:09 112 secs 
6 1    6 2016-01-15 19:55:09 2016-01-15 19:56:15 2016-01-15 19:57:03 48 secs 
7 1    7 2016-01-15 19:57:03 2016-01-15 19:58:17    <NA> NA secs

现在我需要添加称为 “FLAG” 与值是OK或NOT OK柱其中

“OK”指间隔不是enitrely OR部分另一间隔内任一。因此，“OK”的间隔与其他间隔没有重叠。

“NOT OK”表示间隔IS部分地或完全地以另一间隔为间隔。因此，“不好”的间隔与其他间隔重叠。

我有以下的间隔和什么旗柱的结果应该是一个简短的描述

StartTime    EndTime    FLAG 
2016-01-15 18:02:11 2016-01-15 18:02:17  OK - this interval does not overlap with other intervals 
2016-01-15 18:10:33 2016-01-15 18:10:39  OK - this interval does not overlap with other intervals 
2016-01-15 18:25:08 2016-01-15 18:25:14  NOT OK - this inerval is within the 18:21:03 start time interval 
2016-01-15 18:33:56 2016-01-15 18:34:02  NOT OK - this inerval is within the 18:21:03 start time interval 
2016-01-15 18:21:03 2016-01-15 19:53:17  NOT OK - this interval contains other intervals 
2016-01-15 19:55:09 2016-01-15 19:56:15  OK - this interval does not overlap with other intervals 
2016-01-15 19:57:03 2016-01-15 19:58:17  OK - this interval does not overlap with other intervals

我一直在寻找在dplyr使用芹菜或cummax .....也许...... 。

cum_max_s = as.POSIXct(cummin(as.numeric(StartTime)),origin="1970-01-01")

答

这是我的尝试。我认为在data.table包中的foverlaps()是我们这种情况下的朋友。你可以在SO上找到一些例子。您想检查它们以了解功能。您需要创建一个包含开始和结束时间的虚拟data.table。在你的情况下，你有他们。我用最少的信息创建了dummy。然后，您使用setkey()并利用foverlaps()。

# Create a dummy dt for hoverlaps. 
dummy <- setDT(df2)[, 1:4, with = FALSE] 

# Use foverlaps(). 
setkey(setDT(df2), StartTime, EndTime) 
foo <- foverlaps(dummy, setDT(df2), by.x = c("StartTime", "EndTime"))

现在，该清理数据了。对于每个NumberInSequence，如果有超过1个重叠间隔（n> 1），请移除具有相同开始和结束时间（StartTime == i.StartTime & EndTime == i.EndTime）的行。然后，删除每个NumberInSequence的重复行。如果你只有一行表示与另一个区间重叠，那就够了，对吗？最后，如果StartTime == i.StartTime & EndTime == i.EndTime是TRUE，那意味着没有其他区间与区间重叠。所以，你说OK。否则，NOT OK。如有必要，稍后删除多余的列。

foo[,.SD[!(StartTime == i.StartTime & EndTime == i.EndTime & .N > 1)], 
     by = c("ID","NumberInSequence")][!duplicated(NumberInSequence)][, 
      check := ifelse(StartTime == i.StartTime & EndTime == i.EndTime, 
          "OK", "NOT OK")] -> out  
print(out) 

# ID NumberInSequence   StartTime    EndTime  NextStartTime WaitTime i.ID i.NumberInSequence 
#1: 1    1 2016-01-15 18:02:11 2016-01-15 18:02:17 2016-01-15 18:10:33 496 secs 1     1 
#2: 1    2 2016-01-15 18:10:33 2016-01-15 18:10:39 2016-01-15 18:25:08 869 secs 1     2 
#3: 1    5 2016-01-15 18:21:03 2016-01-15 19:53:17 2016-01-15 19:55:09 112 secs 1     3 
#4: 1    3 2016-01-15 18:25:08 2016-01-15 18:25:14 2016-01-15 18:33:56 522 secs 1     5 
#5: 1    4 2016-01-15 18:33:56 2016-01-15 18:34:02 2016-01-15 18:21:03 -779 secs 1     5 
#6: 1    6 2016-01-15 19:55:09 2016-01-15 19:56:15 2016-01-15 19:57:03 48 secs 1     6 
#7: 1    7 2016-01-15 19:57:03 2016-01-15 19:58:17    <NA> NA secs 1     7 

#   i.StartTime   i.EndTime check 
#1: 2016-01-15 18:02:11 2016-01-15 18:02:17  OK 
#2: 2016-01-15 18:10:33 2016-01-15 18:10:39  OK 
#3: 2016-01-15 18:25:08 2016-01-15 18:25:14 NOT OK 
#4: 2016-01-15 18:21:03 2016-01-15 19:53:17 NOT OK 
#5: 2016-01-15 18:21:03 2016-01-15 19:53:17 NOT OK 
#6: 2016-01-15 19:55:09 2016-01-15 19:56:15  OK 
#7: 2016-01-15 19:57:03 2016-01-15 19:58:17  OK

检查重叠的时间间隔开始和结束时间

相关推荐