CODEWITHSUNDEEP
rdata.table
Zachary
Zachary

Reputation: 319

Join two data tables to override values by date range

I want to correct one table based of of the overrides in another table. I want to change the value in dt_current when dt_override has that unit and the date ranges overlap with 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")

It seems like I should use some form of .EACHI when joining the two data tables, coded something like the following, thought it doesn't work or course.

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

Upvotes: 5

Views: 174

Answers (3)

David Arenburg
David Arenburg

Reputation: 92282

Using foverlaps one could do 

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

First option, create and index and update by reference

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

Alernatively, you could run foverlaps the other way around and avoid creating indx but while creating a whole new data set

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

Upvotes: 6

eddi
eddi

Reputation: 49448

Another alternative, using rolling joins:

setkey(dt_current, unit, date)
setkey(dt_override, unit, start_date)

dt_current[, num := dt_override[dt_current, roll = T][end_date >= start_date,
                                                      num := value]$num]

# another version of the above, but using ifelse (unclear to me which one is faster)
dt_current[, num := dt_override[dt_current,
                                ifelse(end_date >= start_date, value, num), roll = T]]

Upvotes: 4

Frank
Frank

Reputation: 66819

Here's one way, enumerating the date sequences:

dt_override[,value:=as.integer(value)]
# It's necessary to convert to integer because `NA` is logical unless otherwise specified.

dto = dt_override[,.(
    unit,
    date = seq.Date(start_date,end_date,by="day"),
    value
),by=seq_along(dt_override)][,seq_along:=NULL]

setkey(dt_current,unit,date)
dt_current[dto,num:=i.value]

Now that foverlaps is in, there's probably a much better way.

Upvotes: 2

Related Questions