I have two data tables. I want to do a rolling join but of a "cummulative kind". For example, here are two tables
d1 <- data.table(starttime = c("2011-01-01 15:29:50", "2011-01-01 15:30:03", "2011-01-01 15:40:20", "2011-01-01 15:50:20" ,"2011-01-01 16:30:00", "2011-01-01 16:40:00"),
endtime = c("2011-01-01 15:30:00", "2011-01-01 15:30:15", "2011-01-01 15:40:28", "2011-01-01 15:50:25", "2011-01-01 16:31:00", "2011-01-01 16:41:00"), v = c("A", "B", "B", "A", "B", "A"), m = c(2,3,5,8,9,9), dur = c(10,12,8,5,60,11))
starttime endtime v m dur
2011-01-01 15:29:50 2011-01-01 15:30:00 A 2 10
2011-01-01 15:30:03 2011-01-01 15:30:15 B 3 12
2011-01-01 15:40:20 2011-01-01 15:40:28 B 5 8
2011-01-01 15:50:20 2011-01-01 15:50:25 A 8 5
2011-01-01 16:30:00 2011-01-01 16:31:00 B 9 60
2011-01-01 16:40:00 2011-01-01 16:41:00 A 9 11
d2 <- data.table(time = c("2011-01-01 16:39:50", "2011-01-01 16:00:03", "2011-01-01 16:50:50"),
v = c("A", "B", "A"), mk = rnorm(3))
time v mk
2011-01-01 16:00:03 B -0.2385093
2011-01-01 16:39:50 A -0.4966836
2011-01-01 16:50:50 A -0.4566836
Now for first row in d2, consider looking back from d2$time of first row, I want to get sum of m given same d2$v in rows of d1 until the Sum of duration (endtime-starttime) > 15
Is there also a way I can count how many rows I used to add > 15 second?
so basically for i should get similar to this
time v mk m rowsUsed
2011-01-01 16:00:03 B -0.2385093 8 2
2011-01-01 16:39:50 A -0.4966836 10 2
2011-01-01 16:50:50 A -0.4566836 17 2
Can anyone help me how such a rolling join can be constructed? I have lots of rows so speed is a concern. Willing to be flexible with XTS.