using data.table to create sequence from starting points and increments

Question

I would like to use data.table to repeatedly add an increment to a starting point.

library(data.table)
dat <- data.table(time=seq(from=as.POSIXct("2018-01-01 01:00:01"),to=as.POSIXct("2018-01-01 01:00:10"), by="secs"), int=c(2,3,3,1,10,10,10,10,10,10), x=2*1:10)

> dat
                  time inc  x
 1: 2018-01-01 01:00:01   2  2
 2: 2018-01-01 01:00:02   3  4
 3: 2018-01-01 01:00:03   3  6
 4: 2018-01-01 01:00:04   1  8
 5: 2018-01-01 01:00:05  10 10
 6: 2018-01-01 01:00:06  10 12
 7: 2018-01-01 01:00:07  10 14
 8: 2018-01-01 01:00:08  10 16
 9: 2018-01-01 01:00:09  10 18
10: 2018-01-01 01:00:10  10 20

That is, starting in row 1, I would like to add the value of inc to time, yielding a new time. I then need to add the value of inc at that new time, to arrive at a third time. The result would then be

> res
                  time inc  x
1: 2018-01-01 01:00:00   2  2
2: 2018-01-01 01:00:02   3  6
3: 2018-01-01 01:00:05  10 12

I would probably know how to do this in a loop, but I wonder whether data.table can handle these sorts of problems as well.

Since the values in time are continuous, my ideas was to use the cumulative values of inc to index, along the lines of

index <- dat[...,cumsum(...inc...),...]
dat[index]

but I cannot get cumsum() to ignore the values in between the points of interest. Perhaps this can be done in the i part of data.table but I would not know how. Anyone an idea?

You may need to specify the `i` for subsetting the rows. It is also better to show your method in the post just to understand the issue better — akrun, Dec 14 '18 at 09:51
Isn't this the same as [this one?](https://stackoverflow.com/questions/53766676/data-table-and-shifts-of-varying-length) — Sotos, Dec 14 '18 at 09:58
@bumblebee if it is a duplicate post, please delete either one of the posts. the time frame of posting both is within a day and the old post have some more info regarding the calculation. — akrun, Dec 14 '18 at 09:59
Yes, it is the same because I felt I had a better way of explaining the issue. Sometimes I find it hard to describe the problem and post it with different descriptions. I then delete the old ones. — bumblebee, Dec 14 '18 at 10:22
@akrun I specified my approach now and It hink the `i` part is exactly the key of my question. — bumblebee, Dec 14 '18 at 12:27

score 0 · Answer 1 · answered Dec 14 '18 at 19:28

# start with finding the next time
dat[, next.time := time + int][!dat, on = .(next.time = time), next.time := NA]

# do this in a loop for the actual problem, and stop when final column is all NA
dat[dat, on = .(next.time = time), t1 := i.next.time]
dat[dat, on = .(t1 = time), t2 := i.next.time]

dat
#                   time int  x           next.time                  t1   t2
# 1: 2018-01-01 01:00:01   2  2 2018-01-01 01:00:03 2018-01-01 01:00:06 <NA>
# 2: 2018-01-01 01:00:02   3  4 2018-01-01 01:00:05                <NA> <NA>
# 3: 2018-01-01 01:00:03   3  6 2018-01-01 01:00:06                <NA> <NA>
# 4: 2018-01-01 01:00:04   1  8 2018-01-01 01:00:05                <NA> <NA>
# 5: 2018-01-01 01:00:05  10 10                <NA>                <NA> <NA>
# 6: 2018-01-01 01:00:06  10 12                <NA>                <NA> <NA>
# 7: 2018-01-01 01:00:07  10 14                <NA>                <NA> <NA>
# 8: 2018-01-01 01:00:08  10 16                <NA>                <NA> <NA>
# 9: 2018-01-01 01:00:09  10 18                <NA>                <NA> <NA>
#10: 2018-01-01 01:00:10  10 20                <NA>                <NA> <NA>

using data.table to create sequence from starting points and increments

1 Answers1