notebook.community

Edit and run 





In [ ]:



    


Rawb has been collecting data on the Seabus ferries for a while now. He has seab.us up where he puts the current location of the ferry on google maps. He has talked about adding an ETA for ferries in motion which is what we are going to look into doing with this notebook using tensorflow (unless in the course of our earlier investigation into the data there turns out to be a very straightward way to do it).



    











In [1]:



    


import pandas as pd
import sqlite3
con = sqlite3.connect('db_seabus.db')
df = pd.read_sql_query('SELECT * FROM telemetry LIMIT 1', con)
print(df)



    


















    






   id  boat_id  nav_status  pos_accuracy         lon        lat  \
0   2        2           0             0 -123.060852  49.291035   

   speed_over_ground  course_over_ground  true_heading  rate_of_turn  \
0                0.0          283.700012           226           0.0   

   rate_of_turn_over_range  timestamp                    received  
0                        0         56  2016-05-08 02:48:55.831263

There is also a boats table. Makes WAY more sense now.





In [5]:



    


df = pd.read_sql_query('SELECT * FROM boats LIMIT 1', con)
print(df)



    


















    






   id  is_seabus       mmsi           name  dim_to_bow  dim_to_stern  \
0   1          0  316005724  C.H. CATES IV           6             9   

   dim_to_port  dim_to_star  type_and_cargo                 lastseen_on  
0            2            3              52  2017-02-28 14:47:16.037102

Step one is looking at what we have for data. I don't know what nav_status or pos_accuracy do so I'm going to ignore them for the moment. We have data to establish the location (lat and lon) of the ferry, the time the data was gathered, and information about the velocity of the ferry. One would imagine that training something on the velocity and position of a ferry would work well for establishing an ETA. But the data as it exists right now does not distinguish between when the ferry is docked vs underway so the first step will be doing that.

The latitude and longitude coordinates are very specific, down to the foot level. So specific that we can't simply match on equality to a specific coordinate for when the boat is docked - there is variability in where the boat docks and we will have to account for a geographical area of the docks. One thing that may we worth looking at is speed_over_ground and seeing if that is always zero when docked, and non-zero when not.





In [ ]:



    


df = pd.read_sql_query('SELECT lon, lat, speed_over_ground, received FROM telemetry WHERE boat_id = 33 ORDER BY received desc LIMIT 50', con)
print(df)

By popping the lat and long into google maps we can see that this does seem reasonable at first glance. It does leave us with a bit of a tricky bit of data selection - the time we want to register as docked is the first time where speed_over_ground is zero after having velocity at a previous measurement, which is the type of selection that SQL is not awesome at.





In [ ]:



    


print('startings')
#df = pd.read_sql_query(
    """
    SELECT *, t2.id as t2id, t1.id as t1id FROM telemetry as t1
    LEFT JOIN telemetry as t2
    ON t1.boat_id = t2.boat_id
    AND t2.id IN (
        SELECT id FROM telemetry
        WHERE boat_id = t1.boat_id
        AND id < t1.id
        AND lon IS NOT 'NaN'
        ORDER BY id DESC
        LIMIT 1
    )
    WHERE t1.boat_id = 33
    ORDER BY t1.received DESC
    LIMIT 10
    """, con)
print('finished')
print(df)



    











In [73]:



    


import time
import datetime
df = pd.read_sql_query(
    """
    SELECT name, received, speed_over_ground as speed
    FROM boats
    LEFT JOIN telemetry
    ON boats.id = telemetry.boat_id
    WHERE is_seabus = 1
    AND boat_id = 5
    ORDER BY received DESC
    """, con)

arrivals = []
departures = []

trips = []
trip = []
trip.append(None)
trip.append(None)

previous_entry = None
for index, entry in df.iterrows():
    if entry[2] != 0.0:
        if previous_entry is not None and previous_entry[2] == 0.0:
            departures.append(entry)
            trip[0] = entry
    if entry[2] == 0.0:
        if previous_entry is not None and previous_entry[2] != 0.0:
            arrivals.append(entry)
            trip[1] = entry
    if trip[1] is not None:
        if trip[0] is not None:
            trips.append(trip[:])
        trip[0] = None
        trip[1] = None
    previous_entry = entry

times = []

for trip in trips:
    start = datetime.datetime.strptime(trip[0][1].split('.')[0], '%Y-%m-%d %H:%M:%S')
    end = datetime.datetime.strptime(trip[1][1].split('.')[0], '%Y-%m-%d %H:%M:%S')
    time = start - end
    if time.total_seconds() > 3600:
        print('too big, mate')
    elif time.total_seconds() < 60:
        print('too small, mate')
    else:
        times.append(start - end)

times_df = pd.DataFrame(times)
print(times_df.describe())



    


















    






too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too small, mate
too small, mate
too big, mate
too small, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too small, mate
too big, mate
too big, mate
too big, mate
too big, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too big, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too big, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
too small, mate
                            0
count                   13571
mean   0 days 00:11:38.414118
std    0 days 00:04:16.510411
min           0 days 00:01:00
25%           0 days 00:10:40
50%           0 days 00:11:01
75%           0 days 00:11:40
max           0 days 01:00:00

















In [62]:



    


import time
start_string = "2017-02-28 15:13:41.482770".split('.')[0]
start = time.strptime(start_string, '%Y-%m-%d %H:%M:%S')
print(start)



    


















    






time.struct_time(tm_year=2017, tm_mon=2, tm_mday=28, tm_hour=15, tm_min=13, tm_sec=41, tm_wday=1, tm_yday=59, tm_isdst=-1)

















In [ ]:

Content source: rsalmond/seab-us

Similar notebooks:

notebook.community | gallery | about