r - 计算某个时间有多少报表正在运行

Question

我正在尝试计算在特定时间运行了多少报告。

数据是这样的:

ReportID    StartTime   Duration  
   1 2018-11-02 13:00:00  240 seconds  
   2 2018-11-02 14:00:00  300 seconds  
   3 2018-11-02 14:01:15  300 seconds  
   4 2018-11-02 14:00:00 5000 seconds

理想的输出将是:

Time #ReportsRunning
2018-11-01 13:00:00 0
2018-11-02 13:00:00 1  
2018-11-02 14:00:00 2
2018-11-02 15:00:00 1

有没有办法做这样的事情？我正在考虑向我要检查的每个时间戳添加列。但这会使表格变得非常宽。

---

可重现形式的数据:

df1 <- data.frame(
  ReportID = 1:4,
  StartTime = as.POSIXct(c("2018-11-02 13:00:00", "2018-11-02 14:00:00",
                           "2018-11-02 14:01:15", "2018-11-02 14:00:00")),
  Duration = as.difftime(c(240, 300, 300, 5000), units = "secs")
)

df2 <- data.frame(
  Time = as.POSIXct(c("2018-11-01 13:00:00", "2018-11-02 13:00:00",
                      "2018-11-02 14:00:00", "2018-11-02 15:00:00"))
)

Answer 1

这是一个基本的 R 解决方案:

df2$`#ReportsRunning` <- sapply(
  df2$Time,
  function(x) sum(x >= df1$StartTime & x <= df1$StartTime + df1$Duration)
)

df2
#                  Time #ReportsRunning
# 1 2018-11-01 13:00:00               0
# 2 2018-11-02 13:00:00               1
# 3 2018-11-02 14:00:00               2
# 4 2018-11-02 15:00:00               1

但是如果你的数据很大，使用 IRanges package 应该会更有效率。来自 BioConductor:

library(IRanges)

ranges <- IRanges(as.integer(df1$StartTime), width = as.integer(df1$Duration))
values <- as.integer(df2$Time)

df2$`#ReportsRunning` <- countOverlaps(values, ranges)

df2
#                  Time #ReportsRunning
# 1 2018-11-01 13:00:00               0
# 2 2018-11-02 13:00:00               1
# 3 2018-11-02 14:00:00               2
# 4 2018-11-02 15:00:00               1