r - 查找日期间隔包含准时日期的值

Question

我有一个给定间隔的理论值数据表:

firstDate   lastDate    theoric
2017-01-01  2017-01-03  10
2017-01-05  2017-01-25  20
2017-02-01  2017-08-31  30

另一方面，我有准时的测量值:

datetime      measured
2017-01-02       11
2017-01-08       22
2017-01-09       19
2017-01-26       25
2017-03-02       32

我想为每个测量值提供相应的理论值(其间隔包括测量日期)。

注意事项: 1、理论区间不能重叠。 2. 如果测量结果不在任何理论区间内，则返回 NA。

预期输出:

datetime    measured  theoric
2017-01-02  11        10
2017-01-08  22        20
2017-01-09  19        20
2017-01-26  25        NA
2017-03-02  32        30

可重现的数据集:

theoricDt <- structure(list(firstDate = structure(c(1483228800, 1483574400, 1485907200), class = c("POSIXct", "POSIXt"), tzone = "GMT"),     lastDate = structure(c(1483401600, 1485302400, 1504137600 ), class = c("POSIXct", "POSIXt"), tzone = "GMT"), theoric = c(10, 20, 30)), .Names = c("firstDate", "lastDate", "theoric"), row.names = c(NA, -3L), class = c("data.table", "data.frame"))
measureDt <- structure(list(datetime = structure(c(1483315200, 1483833600, 1483920000, 1485388800, 1488412800), class = c("POSIXct", "POSIXt"), tzone = "GMT"), measured = c(11, 22, 19, 25, 32)), .Names = c("datetime", "measured"), row.names = c(NA, -5L), class = c("data.table","data.frame"))

Answer 1

您可以使用非等值连接:

measureDt[theoricDt, on = .(datetime >= firstDate, datetime <= lastDate),
          theoric := i.theoric]

measureDt
#     datetime measured theoric
#1: 2017-01-02       11      10
#2: 2017-01-08       22      20
#3: 2017-01-09       19      20
#4: 2017-01-26       25      NA
#5: 2017-03-02       32      30