r - Rolling joins: roll forwards and backwards -

data.table awesome, because can rolling joins, , rolling joins within groups!

library(data.table) set.seed(42) metrics <- data.frame(   id=c(rep(1, 10), rep(2,5), rep(3,5)),   time=c(1:10, 4:8, 8:12),   val1=runif(20),   val2=runif(20),   val3=runif(20),   val4=runif(20)   ) metrics <- data.table(metrics[sample(1:nrow(metrics), 15),], key=c('id', 'time')) calendar <- data.table(expand.grid(id=1:3, time=1:12), key=c('id', 'time'))  metrics[calendar,roll=true] 

however, isn't awesome enough me. data.table still has nas:

> metrics[calendar,roll=true]     id time      val1      val2      val3        val4  1:  1    1 0.9148060 0.9040314 0.3795592 0.675607275  2:  1    2 0.9370754 0.1387102 0.4357716 0.982817198  3:  1    3 0.9370754 0.1387102 0.4357716 0.982817198  4:  1    4 0.8304476 0.9466682 0.9735399 0.566488424  5:  1    5 0.8304476 0.9466682 0.9735399 0.566488424  6:  1    6 0.5190959 0.5142118 0.9575766 0.189473935  7:  1    7 0.7365883 0.3902035 0.8877549 0.271286615  8:  1    8 0.7365883 0.3902035 0.8877549 0.271286615  9:  1    9 0.6569923 0.4469696 0.9709666 0.693204820 10:  1   10 0.7050648 0.8360043 0.6188382 0.240544740 11:  1   11 0.7050648 0.8360043 0.6188382 0.240544740 12:  1   12 0.7050648 0.8360043 0.6188382 0.240544740 13:  2    1        na        na        na          na 14:  2    2        na        na        na          na 15:  2    3        na        na        na          na 16:  2    4 0.4577418 0.7375956 0.3334272 0.042988796 17:  2    5 0.7191123 0.8110551 0.3467482 0.140479094 18:  2    6 0.9346722 0.3881083 0.3984854 0.216385415 19:  2    7 0.2554288 0.6851697 0.7846928 0.479398564 20:  2    8 0.2554288 0.6851697 0.7846928 0.479398564 21:  2    9 0.2554288 0.6851697 0.7846928 0.479398564 22:  2   10 0.2554288 0.6851697 0.7846928 0.479398564 23:  2   11 0.2554288 0.6851697 0.7846928 0.479398564 24:  2   12 0.2554288 0.6851697 0.7846928 0.479398564 25:  3    1        na        na        na          na 26:  3    2        na        na        na          na 27:  3    3        na        na        na          na 28:  3    4        na        na        na          na 29:  3    5        na        na        na          na 30:  3    6        na        na        na          na 31:  3    7        na        na        na          na 32:  3    8 0.9400145 0.8329161 0.7487954 0.719355838 33:  3    9 0.9400145 0.8329161 0.7487954 0.719355838 34:  3   10 0.1174874 0.2076590 0.1712643 0.375489965 35:  3   11 0.4749971 0.9066014 0.2610880 0.514407708 36:  3   12 0.5603327 0.6117786 0.5144129 0.001570554     id time      val1      val2      val3        val4 

i fill these na's using zoo:::na.locf, fromlast=true, that's not fun. can think of elegant way can roll na's backward, (after rolling them forward), during data.table join?

this possible in data.table version 1.8.8 released march 2013:

metrics[calendar, roll=true, rollends=c(true, true)] 

from data.table news file:

in addition true/false, 'roll' may positive number (roll forwards/locf) or negative number (roll backwards/nocb). finite number limits distance value rolled (limited staleness). roll=true , roll=+inf equivalent. 'rollends' new parameter holding 2 logicals. first observation rolled backwards if first value of rollends true. last observation rolled forwards if second value of rollends true. if roll finite number, same limit applies ends. new value roll='nearest' joins nearest value (either backwards or forwards) when value falls in gap, , end value according 'rollends'. 'rolltolast' has been deprecated. backwards compatibility converted {roll=true;rollends=c(false,false)}.

as always, download up-to-date version of data.table, see installation.