I also have a look-up table that give additional information about stops keyed by tag:
"5239","King St & 4th St","15239","Outbound",37.776270,-122.394170
"5240","King St & 4th St","15240","Inbound",37.776270,-122.394080
Each physical stop actually has two stop tags: one for "Inbound" and another for "Outbound," for example, the "King St & 4th St" stop shown. The direction is given by stop_direction.
Hence, given a log entry such as the first, at 17:37:05, vehicle 1011 is at stop 5239 --- which is King St & 4th St outbound. Then, nearly 9 minutes later, the same vehicle is at stop 5240 --- the same stop, but inbound. Hence, the vehicle had a "layover" of approximately 9 minutes.
For each vehicle, when it changes direction from outbound to inbound (or vice versa) as given by the direction of the stop tag it's at, I want to chart both the average and maximum layover as the Y-axis and either:
All vehicles taken together --- where the X-axis would be time.
By individual vehicle -- where the X-axis would be the vehicle_id.
base search vehicle data | lookup lookupfile.csv stop_tag | reverse | streamstats count by vehicle_id stop_direction | stats earliest(_time) as start latest(_time) as end earliest(stop_direction) as dir1 latest(stop_direction) as dir2 by vehicle_id count | where NOT (dir1=dir2) | eval duration=end-stop
base search | reverse | streamstats count by vehicle_id stop_direction | stats earliest(_time) as start latest(_time) as end earliest(stop_direction) as dir1 latest(stop_direction) as dir2 by vehicle_id count | where dir1="Outbound" AND dir2="Inbound" | eval layover=(end-start)/60 | chart min(layover) as Min avg(layover) AS Avg max(layover) as Max by vehicle_id
What about part 1 of my request? All vehicles taken together?