r - 连接两个数据表以按日期范围覆盖值

Question

我想根据另一个表中的覆盖来更正一个表。当 dt_override 具有该单位并且日期范围与 dt_current 重叠时，我想更改 dt_current 中的值。

dt_current <- data.table( unit = c(rep("a",10), rep("b", 10)), 
    date = seq(as.Date("2015-1-1"), by = "day", length.out = 10), 
    num = 1:10, key = "unit")
dt_override <- data.table( unit = c("a", "a", "b", "zed" ), start_date = as.Date(c("2015-01-03", "1492-12-25", "2015-01-02", "2015-01-11")), 
    end_date = as.Date(c("2015-01-05", "1492-12-26", "2015-01-04", "2015-01-14")), 
    value = NA, key = "unit")

似乎我应该在加入两个数据表时使用某种形式的 .EACHI，编码如下，认为它不起作用或当然。

dt_current[dt_override, 
    num := if(i.start_date <= date & i.end_date >= date) i.value, 
    by = .EACHI]

Answer 1

使用 foverlaps一个可以做

dt_current[, date2 := date] # define end date
setkey(dt_current, unit, date, date2) # key by unit, start and end dates
setkey(dt_override, unit, start_date, end_date) # same

第一个选项，通过引用创建、索引和更新

indx <- foverlaps(dt_override, dt_current, which = TRUE) # run foverlaps and get indices
dt_current[indx$yid, num := dt_override[indx$xid, value]] # adjust by reference

或者，您可以运行 foverlaps反过来，避免创建 indx但是在创建一个全新的数据集时

foverlaps(dt_current, dt_override)[!is.na(start_date), num := value
                                   ][, .SD, .SDcols = names(dt_current)]