CODEWITHSUNDEEP
rdata.table
Kasitru
Kasitru

Reputation: 55

R Data.Table Copy value to another row

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

Upvotes: 0

Views: 160

Answers (1)

r2evans
r2evans

Reputation: 161110

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

Upvotes: 1

Related Questions