Referencing data in leading rows

Question

I have a sample dataset which tracks the trajectory of a bike to different stations. My objective is to find the intervals that the bike remains at a particular station with difftime(), in this case, station B.

> test
   bikeid start_station           starttime end_station             endtime
1       1             A 2017-09-25 01:00:00           B 2017-09-25 01:30:00
2       1             B 2017-09-25 07:30:00           C 2017-09-25 08:00:00
3       1             C 2017-09-25 10:00:00           A 2017-09-25 10:30:00
4       1             A 2017-09-25 13:00:00           C 2017-09-25 13:30:00
5       1             C 2017-09-25 15:30:00           B 2017-09-25 16:00:00
6       1             B 2017-09-25 18:00:00           B 2017-09-25 18:30:00
7       1             B 2017-09-25 19:00:00           A 2017-09-25 19:30:00
8       1             А 2017-09-25 20:00:00           B 2017-09-25 20:30:00
9       1             C 2017-09-25 22:00:00           C 2017-09-25 22:30:00
10      1             B 2017-09-25 23:00:00           C 2017-09-25 23:30:00

Sometimes, the bikes do not start at the same station that they ended, and these cases should be ignored. In the above dataset, we can see that 360 minutes lapsed between 01:30:00 and 07:30:00, that 120 minutes lapsed between 16:00:00 and 18:00:00, and that 30 minutes lapsed between 18:30:00 and 19:00:00. Row 8 and 10 are ignored because the bike does not start at the same station at which it ended. Therefore, the output vector should be:

[1] 360 120  30

The following code using does not produce the desired output:

sapply(test$starttime[test$end_station == "B"], function(x, et) difftime(et[x < et][1], x, units = "mins"), et = test$endtime[test$start_station == "B"])

How would one take into account the next row and calculate difftime() only when the end_station and start_station in the following row are equal? Using lead() in dplyr? Any suggestion would be appreciated

Here is the sample data:

> dput(test)
structure(list(bikeid = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1), start_station = c("A", 
"B", "C", "A", "C", "B", "B", "А", "C", "B"), starttime = structure(c(1506315600, 
1506339000, 1506348000, 1506358800, 1506367800, 1506376800, 1506380400, 
1506384000, 1506391200, 1506394800), class = c("POSIXct", "POSIXt"
), tzone = ""), end_station = c("B", "C", "A", "C", "B", "B", 
"A", "B", "C", "C"), endtime = structure(c(1506317400, 1506340800, 
1506349800, 1506360600, 1506369600, 1506378600, 1506382200, 1506385800, 
1506393000, 1506396600), class = c("POSIXct", "POSIXt"), tzone = "")), .Names = c("bikeid", 
"start_station", "starttime", "end_station", "endtime"), row.names = c(NA, 
-10L), class = "data.frame")

user_1191259 · Answer

Reshaping as suggested last time...

library(data.table)

mtest = melt(setDT(test), id="bikeid", 
  meas = patterns("_station", "time"), 
  variable.name = "event", 
  value.name = c("station", "time"))
mtest[.(factor(1:2), c("start", "end")), on=.(event), event := i.V2]
setkey(mtest, bikeid, time)

Then back to wide for the spells while the bike is idle...

idleDT = dcast(mtest[-c(1,.N)][, g := rep(1:.N, each=2, length.out=.N)], 
  g ~ rowid(g), value.var=c("station", "time"))

   g station_1 station_2              time_1              time_2
1: 1         B         B 2017-09-25 01:30:00 2017-09-25 07:30:00
2: 2         C         C 2017-09-25 08:00:00 2017-09-25 10:00:00
3: 3         A         A 2017-09-25 10:30:00 2017-09-25 13:00:00
4: 4         C         C 2017-09-25 13:30:00 2017-09-25 15:30:00
5: 5         B         B 2017-09-25 16:00:00 2017-09-25 18:00:00
6: 6         B         B 2017-09-25 18:30:00 2017-09-25 19:00:00
7: 7         A   2017-09-25 19:30:00 2017-09-25 20:00:00
8: 8         B         C 2017-09-25 20:30:00 2017-09-25 22:00:00
9: 9         C         B 2017-09-25 22:30:00 2017-09-25 23:00:00

Then join or filter and compute...

idleDT[.("B", "B"), on=.(station_1, station_2), time_2 - time_1 ]

Time differences in mins
[1] 360 120  30

Comment

I should probably explain why I prefer long-format mtest over the OP's test, even though I go right back to wide format for the analysis (thanks @Henrik)...

Stations could/should arguably be a factor, and if you have it split over two columns in the core data, it's a burden to ensure that both factors have the same levels.
The data is presumably recorded in terms of events (like "the bike left" and "the bike arrived"), not in terms of trips. If someone steals the bike or it gets lost, for example, the endtime and end_station should logically be missing, but this is easier to keep track of in long format, in my opinion.
The measured data could even have two "the bike arrived" events in a row, even though it doesn't logically make sense, anything that can go wrong with data will go wrong, in my experience. If this happened, you'd have a hard time figuring out how to record it in wide format in terms of trips.

Generally, I'm just applying my (perhaps overzealous or wrong) understanding of tidy data, riffing on Hadley's complaint in the link about data layouts where "[c]olumn headers are values, not variable names."

user_5150629 · Answer

A dplyr solution:

library(dplyr)
df %>%
  mutate(lag_end_station = lag(end_station),
         lag_end_time = lag(endtime)) %>%
  filter(start_station == "B" & lag_end_station == "B") %>%
  transmute(interval = difftime(starttime, lag_end_time))

Result:

  interval
1 360 mins
2 120 mins
3  30 mins

Referencing data in leading rows

2 Answers