create sequence by repeated joins

Question

Suppose I have the following data:

library(data.table)
set.seed(42)
t <- data.table(time=1001:2000, period=round(runif(1000,1,5)), a=round(rnorm(1000)))
p <- data.table(id=1:10, time=sample(1000:1100,5), a=round(rnorm(10)))


 > t[27:38]
    time period  a
 1: 1027      3 -1
 2: 1028      5 -1
 3: 1029      3  0
 4: 1030      4 -2
 5: 1031      4 -2
 6: 1032      4 -1
 7: 1033      3  0
 8: 1034      4  1
 9: 1035      1  0
10: 1036      4  0
11: 1037      1  0
12: 1038      2 -1

> head(p)
   id time  a
1:  1 1027  1
2:  2 1094  1
3:  3 1044 -1
4:  4 1053  1
5:  5 1015  1
6:  6 1027 -1

which is similar to data I have posted before as concatenate periods to get time sequences, simultaneously for different starting points but now has the additional variable a that is carried over from t.

In contrast to my earlier question, my goal is to create sequences right into p by concatenating n of the periods in t. For n=4, the result would ideally look like this

> head(p)
   id time  a
1:  1 1027  1
2:  1 1030 -1 
3:  1 1034 -2
4:  1 1038  1
5:  1 1040 -1
6:  2 1094  1

because for id 1, starting at 1027, the sequence is 1027, 1027+3=1030, 1030+4=1034, 1034+4=1038 and 1038+2=1040, where the increments are taken from t. In addition, t$a is "taken along" to fill in for p$a.

In my earlier question, Jaap has given a fantastic solution to obtain a two-dimensional output with one line per id. I wonder whether this can be achieved directly in p. Perhaps this can be done using joins of t into p repeatedly or perhaps there is a more efficient solution (because efficiency is key here).

The desired output is already given. However, I have changed the text and (hopefully) made it clearer. — bumblebee, Jul 02 '19 at 21:06

score 0 · Answer 1 · answered Jul 03 '19 at 11:53

I'm not 100% sure about what you want to do with a to "take it along", but maybe this recursion does what you want, although I don't know if it's efficient enough:

create_sequences <- function(p, n, acc = p) {
  if (n == 0L) return(setkey(acc, id, time))

  next_p <- t[p, .(id, time = time + period, a = x.a), on = "time"]

  create_sequences(next_p, n - 1L, rbindlist(list(acc, next_p)))
}

ans <- create_sequences(p, 4L)

create sequence by repeated joins

1 Answers1

Linked