r - Using roll=TRUE with allow.cartesian=TRUE -

what best way cartesian join , use roll forward feature, applying roll feature each alternative series joining table, rather whole series.

best explained example:

library(data.table) = data.table(x = c(1,2,3,4,5), y = letters[1:5]) b = data.table(x = c(1,2,3,1,4), f = c("alice","alice","alice", "bob","bob"), z = 101:105) setkey(b,x) c = b[a, roll = true, allow.cartesian=true, rollends = false]  b c[f == "alice"] c[f == "bob"] c 

so have 2 starting tables:

>    x y 1: 1 2: 2 b 3: 3 c 4: 4 d 5: 5 e > b    x     f   z 1: 1 alice 101 2: 1   bob 104 3: 2 alice 102 4: 3 alice 103 5: 4   bob 105 

and want join these have for each x value in a have both , alice , bob row, rolling forwards if either missing (but not rolling past end). doesn't quite work i've got it:

> c[f == "alice"]    x     f   z y 1: 1 alice 101 2: 2 alice 102 b 3: 3 alice 103 c > c[f == "bob"]    x   f   z y 1: 1 bob 104 2: 4 bob 105 d > c    x     f   z y 1: 1 alice 101 2: 1   bob 104 3: 2 alice 102 b 4: 3 alice 103 c 5: 4   bob 105 d 6: 5    na  na e 

because alice there 2 , 3, doesn't roll bob's data forwards. need rows bob want get:

> c[f == "alice"]    x     f   z y 1: 1 alice 101 2: 2 alice 102 b 3: 3 alice 103 c > c[f == "bob"]    x   f   z y 1: 1 bob 104 2: 2 bob 104 b  # these rows missing 3: 3 bob 104 c  # these rows missing 4: 4 bob 105 d > c    x     f   z y 1: 1 alice 101 2: 1   bob 104 3: 2 alice 102 b 4: 2   bob 104 b  # these rows missing 5: 3 alice 103 c 6: 3   bob 104 c  # these rows missing 7: 4   bob 105 d 8: 5    na  na e 

here go:

setkey(b, f, x)  setkey(b[cj(unique(f), unique(x)), allow.cartesian = t,          roll = t, rollends = c(f,f)], x)[a, allow.cartesian = t] #   x     f   z y #1: 1 alice 101 #2: 1   bob 104 #3: 2 alice 102 b #4: 2   bob 104 b #5: 3 alice 103 c #6: 3   bob 104 c #7: 4 alice  na d #8: 4   bob 105 d #9: 5    na  na e 

and can filter out na's suit needs.