Update two columns that are interdependent row-wise using data.table

Question

I want to create a data.table with the departure and arrival times between bus stops. This is the format of my data.table. (reproducible dataset below)

    trip_id stop_sequence arrival_time departure_time travel_time
 1:       a             1     07:00:00       07:00:00    00:00:00
 2:       a             2     00:00:00       00:00:00    00:02:41
 3:       a             3     00:00:00       00:00:00    00:01:36
 4:       a             4     00:00:00       00:00:00    00:02:39
 5:       a             5     00:00:00       00:00:00    00:02:28
 6:       b             1     07:00:00       07:00:00    00:00:00
 7:       b             2     00:00:00       00:00:00    00:00:00
 8:       b             3     00:00:00       00:00:00    00:01:36
 9:       b             4     00:00:00       00:00:00    00:00:37
10:       b             5     00:00:00       00:00:00    00:03:00

Here is how it should work. The idea is that a vehicle travels following the stop sequence. In trip a, for example, it takes 00:02:41 for the vehicle to travel from stop 1 to stop 2. Given a fixed time of 40 seconds for passangers to enter/leave the vehicle at each stop, the bus would departure from stop 2 at "07:03:21"

The thing here is that this is a row-wise iterative process between two columns. Intuitively, I would would a for set loop in data.table but I couldn't get my head around this. Help?

reproducible dataset:

library(data.table)
library(chron)

dt <- structure(list(trip_id = c("a", "a", "a", "a", "a", "b", "b", 
      "b", "b", "b"), stop_sequence = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 
      3L, 4L, 5L), arrival_time = structure(c(0.291666666666667, 0, 
      0, 0, 0, 0.291666666666667, 0, 0, 0, 0), format = "h:m:s", class = "times"), 
      departure_time = structure(c(0.291666666666667, 0, 0, 0, 
      0, 0.291666666666667, 0, 0, 0, 0), format = "h:m:s", class = "times"), 
      travel_time = structure(c(0, 0.00186598685444013, 0.00110857958406301, 
      0.00183749407361369, 0.00171664297781446, 0, 0.000522388450578203, 
      0.00111473367541453, 0.000427755975518318, 0.00207918951573377
      ), format = "h:m:s", class = "times")), .Names = c("trip_id", 
      "stop_sequence", "arrival_time", "departure_time", "travel_time"
      ), class = c("data.table", "data.frame"), row.names = c(NA, -10L
      ))

expected output: first four rows

   trip_id stop_sequence arrival_time departure_time travel_time
1:       a             1     07:00:00       07:00:00    00:00:00
2:       a             2     07:02:41       07:03:21    00:02:41
3:       a             3     07:04:57       07:05:37    00:01:36
4:       a             4     07:08:16       07:08:56    00:02:39

Mike H. · Accepted Answer · 2017-12-01T21:50:38.947

I think it might be possible to do it without looping. I think you can calculate the departure_time without looping and then once you have that, the arrival_time is just departure_time - 40 seconds:

dt2 <- copy(dt)
dt2[,c("arrival_time", "departure_time") := .(cumsum(arrival_time + ifelse(travel_time==0, 0, travel_time + times("00:00:40"))) - ifelse(travel_time == 0 , 0, times("00:00:40")),
                                              cumsum(arrival_time + ifelse(travel_time==0, 0, travel_time + times("00:00:40")))),
    by = trip_id]

dt2

 #   trip_id stop_sequence arrival_time departure_time travel_time
 #1:       a             1     07:00:00       07:00:00    00:00:00
 #2:       a             2     07:02:41       07:03:21    00:02:41
 #3:       a             3     07:04:57       07:05:37    00:01:36
 #4:       a             4     07:08:16       07:08:56    00:02:39
 #5:       a             5     07:11:24       07:12:04    00:02:28
 #6:       b             1     07:00:00       07:00:00    00:00:00
 #7:       b             2     07:00:45       07:01:25    00:00:45
 #8:       b             3     07:03:01       07:03:41    00:01:36
 #9:       b             4     07:04:18       07:04:58    00:00:37
#10:       b             5     07:07:58       07:08:38    00:03:00

Alternatively, so you don't have to repeat the long cumsum for departure_time to get arrival_time you could do:

dt2[,departure_time := cumsum(arrival_time + ifelse(travel_time==0, 0, travel_time + times("00:00:40"))), by = trip_id]
dt2[, arrival_time := departure_time - ifelse(travel_time == 0 , 0, times("00:00:40"))]

A third option posted by @eddi:

dt[, departure_time := arrival_time[1] + cumsum(travel_time) + (0:(.N-1))*times('00:00:40'), by = trip_id]
dt[, arrival_time := c(arrival_time[1], tail(departure_time, -1) - times('00:00:40')), by = trip_id]

or `dt[, departure_time := arrival_time[1] + cumsum(travel_time) + (0:(.N-1))*times('00:00:40'), by = trip_id]; dt[, arrival_time := c(arrival_time[1], tail(departure_time, -1) - times('00:00:40')), by = trip_id]` — eddi, Dec 01 '17 at 21:27

Update two columns that are interdependent row-wise using data.table

1 Answers1