0

I want to copy a value from a row of a data table into another row and another column of this datatable based on some conditions.

Here is the code I tried but at the point with the ????, I have no idea what I should write there.

dbData[direction == "Coming", GoingTime := (.SD[direction == "Going"][timestamp > ????][order(timestamp)][1])$timestamp, by="eqnr"]

For every row with direction = Coming, I want to get the timestamp of the next row with direction = Going. (for rows with the same value in eqnr)

I hope I could explain my problem. I know, that I can solve the problem with some loops but I want to use the advantages of data.tables

Here some sample data

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kasitru
  • 45
  • 4
  • 4
    Welcome to SO, Kasitru! Please make this question reproducible by including sample data so that we are not completely in the dark. Please read https://stackoverflow.com/q/5963269 and [mcve], and the information that is at the top of the [tag:r] tag page, https://stackoverflow.com/tags/r/info. – r2evans Mar 29 '21 at 14:49
  • ***Please*** don't require that we transcribe data from images that you have *in data form* on your computer. Just post data (in a code-block, preferably). It's just as easy for you (to paste text instead of pasting an image), and it is **far** easier for us. Whether it's because of laziness, hubris, or not-thinking, please realize that while we do not charge, our time is not free. – r2evans Mar 30 '21 at 12:32

1 Answers1

1
D[, nextG := nafill(replace(seq_len(.N), direction == "C", NA), type="nocb"), by=.(eqnr)
  ][, timenextG := timestamp[nextG], by = .(eqnr)][]
#              timestamp direction  eqnr nextG           timenextG
#                 <POSc>    <char> <num> <int>              <POSc>
# 1: 2021-03-30 12:24:16         C    10     2 2021-03-30 13:05:59
# 2: 2021-03-30 13:05:59         G    10     2 2021-03-30 13:05:59
# 3: 2021-03-30 14:57:01         C    17     2 2021-03-30 14:59:29
# 4: 2021-03-30 14:59:29         G    17     2 2021-03-30 14:59:29
# 5: 2021-03-30 15:36:02         C    10     5 2021-03-30 16:25:29
# 6: 2021-03-30 15:41:02         C    10     5 2021-03-30 16:25:29
# 7: 2021-03-30 16:25:29         G    10     5 2021-03-30 16:25:29

Data

set.seed(2021)
D <- data.table(timestamp = sort(Sys.time() + runif(7, max=3600*10)), direction = c("C","G","C","G","C","C","G"), eqnr=c(10,10,17,17,10,10,10))
D
#              timestamp direction  eqnr
#                 <POSc>    <char> <num>
# 1: 2021-03-30 12:24:16         C    10
# 2: 2021-03-30 13:05:59         G    10
# 3: 2021-03-30 14:57:01         C    17
# 4: 2021-03-30 14:59:29         G    17
# 5: 2021-03-30 15:36:02         C    10
# 6: 2021-03-30 15:41:02         C    10
# 7: 2021-03-30 16:25:29         G    10
r2evans
  • 141,215
  • 6
  • 77
  • 149