<jupyter_start><jupyter_text>In this notebook, I will try get an understanding of the given dataset.<jupyter_code># Importing the modules that might be used
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import dask.dataframe as dd
import sqlite3
from sqlalchemy import create_engine
from pprint import pprint

# ignore warnings
import warnings
warnings.filterwarnings('ignore')<jupyter_output><empty_output><jupyter_text>Exploring the datasetWe are given three datasets:* Leave times* Trips * VehiclesIn this notebook:* Understand each dataset* Look for inconsistencies* Start looking at trips and get a better understanding how the datasets work together<jupyter_code># Using error_bad_lines so that lines with too many commas will be returned in the dataframe

trips = pd.read_csv('/home/faye/data/rt_trips_DB_2018.txt', sep=';',error_bad_lines=False)
vehicles = pd.read_csv('/home/faye/data/rt_vehicles_DB_2018.txt', sep=';',error_bad_lines=False)
# leave_times_db = pd.read_csv('/home/faye/data/rt_leavetimes_DB_2018.txt', sep=';',error_bad_lines=False)
leave_times = pd.read_feather('/home/faye/data/leavetimes_cleaned_1.feather')
# refer to this link for more info https://docs.sqlalchemy.org/en/14/core/engines.html
leave_times_db = create_engine('sqlite:///rt_leavetimes_DB_2018.db')
con = sqlite3.connect('/home/faye/notebooks/rt_leavetimes_DB_2018.db')
leave_times_db
con
vehicles.head(1)
leave_times.loc[leave_times['VEHICLEID']==3303848]<jupyter_output><empty_output><jupyter_text>Leave Times Data  Understanding the features:* DATASOURCE: Unique Bus Operator Code* DAYOFSERVICE: day of attendance. One day of service could last more than 24 hours.* TRIPID: Refers to a unique Trip. Will be investigated further.* PROGNUMBER:  Sequential position of the stop point of the trip.* STOPPOINTID: Unique stop point code* PLANNEDTIME_ARR: Planned arrival time at the stop point, in seconds* PLANNEDTIME_DEP: Planned departure time from the stop point, in seconds* ACTUALTIME_ARR: Actual arrival time at the stop point, in seconds* ACTUALTIME_DEP: Actual departure time from the stop point, in seconds* VEHICLEID: Unique vehicle code arriving at this stop point* PASSENGERS: Number of passengers on board (previous link)* PASSENGERSIN: Number of boarded passengers* PASSENGERSOUT: Number of descended passengers* DISTANCE: Distance measured from the beginning of the trip* SUPPRESSED: When the trip is partially suppressed it says that the previous link is suppressed (0 =achieved, 1 = suppressed)* JUSTIFICATIONID: Fault code* LASTUPDATE: Time of the last record update* NOTE: Free note -> document states that the data type is string type but here states that it's float64.  Exploring Leave Times Data* check the first 5 rows* last 5 rows* check rows and columns* check data types* check missing data<jupyter_code>def sql_fetch(con, query):
    cursorObj = con.cursor()
    cursorObj.execute(query)
    pprint(cursorObj.fetchall())
    return cursorObj.fetchall()
# list info about the table in the database
sql_fetch(con, 'select * from sqlite_master;')
# Read into a csv file?

leave_times.head(5)
leave_times.tail(5)
leave_times.dtypes<jupyter_output><empty_output><jupyter_text>Trips Data Each row represents one trip(route) Understanding the features:* DATASOURCE: Unique Bus Operator Code* DAYOFSERVICE: Day of service. One day of service could last more than 24 hours* TRIPID: Unique Trip code* LINEID: Unique Line code* ROUTEID: Unique route code* DIRECTION: Route direction: IB = inbound / going / northbound / eastbound, OB = outbound / back / southbound / westbound* PLANNEDTIME_ARR: Planned arrival time of the trip, in seconds* PLANNEDTIME_DEP: Planned departure time of the trip, in seconds* ACTUALTIME_ARR: Actual arrival time of the trip, in seconds* ACTUALTIME_DEP: Actual departure time of the trip, in seconds* BASIN: basin code* TENDERLOT: tender lot* SUPPRESSED: The whole trip has been supressed (0 = achieved, 1 = suppressed)* JUSTIFICATIONID: Fault code* LASTUPDATE: Time of the last record update * NOTE: Free note<jupyter_code>trips.shape
trips.head(20)
trips.tail(5)
trips_sortby_trip = trips.sort_values(by='TRIPID')
trips_sortby_trip.head(40)<jupyter_output><empty_output><jupyter_text>So if the TRIPID is sorted in order, look at 77A. The dates aren't in order. But I counted 6 buses for one day. 3 buses inbound and 3 buses outbound. Idea for filtering:* Filter by Dates > we get all of the buses than ran for that day > Filter by specific route (ROUTEID) > we get a list of all of the TRIPID.* To link to leave_times - passing on the TRIPIDs.* So different TRIPIDs represent different trips in the same day at different times.<jupyter_code>trips.dtypes
trips.dtypes
# Unique values for each feature
trips.nunique()
# Missing values for each feature
trips.isnull().sum()<jupyter_output><empty_output><jupyter_text>* There are three constant features that will be dropped: BASIN, SUPPRESSED and DATASOURCE.* TENDERLOT's rows are 100% missing so this feature will be dropped. Trying to relate tender lot to business terms. This may refer to whether or not the trip is paid for a service. Makes sense that it's hidden for privacy if this is the case. It doesn't relate much to the project. * ACTUALTIME_ARR has over 100k rows missing but only accounts for 6% of the data. * ACTUALTIME_DEP - the same observation. * LASTUPDATE - in the documentation it states that it detects new data availability. * SUPPRESSED doesn't look like it gives any meaningful data. Not sure why it has to be suppressed. There are no 1.0 values at all, which indicates that there are no trips/rows within the trips dataset where a the information is suppressed/hidden. Demonstrated above, we see the LINEID, TRIPID, etc don't have any null values. From the documentation, it looks like the NaN values just means it's partially suppressed which may explain TENDERLOT feature being 100% missing.<jupyter_code># Seeing what the non missing rows looked like. 0 represents that it 
non_missing_trips_suppressed = trips['SUPPRESSED'].notna()
for row in range(len(non_missing_trips_suppressed)):
    if non_missing_trips_suppressed[row] == True:
        if trips['SUPPRESSED'][row] == 1.0:
            print(trips['SUPPRESSED'][row])
        else:
            print("No 1.0")
trips.loc[1783]<jupyter_output><empty_output><jupyter_text>Vehicles Data Each record of this table represents the service of one vehicle for one day of service and tells aboutthe overall distance and time worked by the associated vehicle in that specific day. Understanding the features:* DATASOURCE: Unique Bus Operator Code* DAYOFSERVICE: Day of service* VEHICLEID: Unique vehicle code arriving at this stop point* DISTANCE: Distance travelled by the vehicle in the corresponding day* MINUTES: Time worked by the veihcle in the corresponding day* LASTUPDATE: Time of the last record update* NOTE: Free note<jupyter_code>vehicles.shape
vehicles.head(5)
vehicles.tail(5)
vehicles.dtypes
#  Unique values for each feature
vehicles.nunique()
# Missing values for each feature
vehicles.isnull().sum()<jupyter_output><empty_output><jupyter_text>* DATASOURCE only has 1 unique value so this will probably be dropped.* NOTE has no values so this will be dropped.* All rows of NOTE is missing. Will be dropped.* Other than that, other features have no missing values.* MINUTES - Think this was meant to refer to the meaning of a period of time. Still should have been named something else. Represents in SECONDS.* DISTANCE refers to either km or miles. What unit of measurement will be investigated below when I start investigating individual trips. But probably km.   Investigating datasets together* See if DISTANCE is measured in km or miles.* See if I can figure out if the trip is inbound or outbound.* Get a better understanding of the STOPPOINTID feature. If it really relates to the stop no.<jupyter_code>trip25 = trips.loc[trips['TRIPID'] == 6259460]
trip25
trip25.shape
import datetime
str(datetime.timedelta(seconds=30517))
# Going to attempt to convert seconds into times
time_columns = ['PLANNEDTIME_ARR', 'PLANNEDTIME_DEP', 'ACTUALTIME_ARR', 'ACTUALTIME_DEP']

for row in trip25['PLANNEDTIME_ARR']:
    trip25['PLANNEDTIME_ARR'] = trip25['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trip25['PLANNEDTIME_DEP']:
    trip25['PLANNEDTIME_DEP'] = trip25['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trip25['ACTUALTIME_ARR']:
    trip25['ACTUALTIME_ARR'] = trip25['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trip25['ACTUALTIME_DEP']:
    trip25['ACTUALTIME_DEP'] = trip25['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
trip25
# Let's look at a specific date
trip25.loc[trip25['DAYOFSERVICE']=='16-FEB-18 00:00:00']<jupyter_output><empty_output><jupyter_text>For this TRIPID all of the departure left around 3pm and arrived at 4pm to the last stop. Google map directions states it takes around 50 minutes. So this is about right. This probably represents the number of buses that ran 25A in different days. They all just go in one direction though. Going to investigate whether 1 represents inbound or outbound. This was a Friday, based on the departure the timetable matches so the route of this bus(es) are coming from Merrion Sq in town. Not sure if it matters, just assuming the buses are coming from town?? idk.1 represents going westbound which is the way to Lucan from town so feature DIRECTION: 1 = outbound & 2 = inbound.<jupyter_code>leave_times<jupyter_output><empty_output><jupyter_text>Going to attempt to convert seconds into times for leave_times<jupyter_code># Making new df in order of PROGRNUMBER
leavetimes16th = leave_times.loc[leave_times["DAYOFSERVICE"]=="16-FEB-18 00:00:00"]
leavetimes16th = leavetimes16th.sort_values(by='PROGRNUMBER')
for row in leavetimes16th['PLANNEDTIME_ARR']:
    leavetimes16th['PLANNEDTIME_ARR'] = leavetimes16th['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leavetimes16th['PLANNEDTIME_DEP']:
    leavetimes16th['PLANNEDTIME_DEP'] = leavetimes16th['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leavetimes16th['ACTUALTIME_ARR']:
    leavetimes16th['ACTUALTIME_ARR'] = leavetimes16th['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leavetimes16th['ACTUALTIME_DEP']:
    leavetimes16th['ACTUALTIME_DEP'] = leavetimes16th['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
leavetimes16th<jupyter_output><empty_output><jupyter_text>Already knew this but wanted to see for myself. For one day it only shows a specific route. Maybe there are other TRIPIDs that would illustrate earlier times that route 25A. Might investigate that.<jupyter_code>vehicles16th = vehicles.loc[vehicles['DAYOFSERVICE']=='16-FEB-18 00:00:00']
vehicles16th.loc[vehicles16th['VEHICLEID']==1000499]<jupyter_output><empty_output><jupyter_text>So, the DISTANCE is not suppressed here compared to the leavetimes table.<jupyter_code>for row in vehicles16th['MINUTES']:
    vehicles16th['MINUTES'] = vehicles16th['MINUTES'].replace(row, str(datetime.timedelta(seconds=row)))
vehicles16th.loc[vehicles16th['VEHICLEID']==1000499]<jupyter_output><empty_output><jupyter_text>So far, we know that some different TRIPIDs represent the same route with a lot of them being sequential. For instance we see above that route 77A has different TRIPIDs so I want to investigate the difference in leavetimes and trips dataset using the first instance of the 77A and the last. I want to see leave_times will provide different times.We see the number of hours the vehicle ran for. But depending on what time the bus began running it's going to be difficult to see what time the service ended at.<jupyter_code># Selecting routes for 77A first and last for leave times and trips
leave_times_first = pd.read_sql('select * from chunk_sql where TRIPID = 5955225;', con)
leave_times_last = pd.read_sql('select * from chunk_sql where TRIPID = 5955232;', con)
trips_first = trips.loc[trips['TRIPID']==5955225]
trips_last = trips.loc[trips['TRIPID']==5955232]
leave_times_first = leave_times_first.sort_values(by='PROGRNUMBER')
leave_times_last = leave_times_last.sort_values(by='PROGRNUMBER')
# Converting minutes to datetime for leavetimes first, last and trips first, last
for row in leave_times_first['PLANNEDTIME_ARR']:
    leave_times_first['PLANNEDTIME_ARR'] = leave_times_first['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_first['PLANNEDTIME_DEP']:
    leave_times_first['PLANNEDTIME_DEP'] = leave_times_first['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_first['ACTUALTIME_ARR']:
    leave_times_first['ACTUALTIME_ARR'] = leave_times_first['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_first['ACTUALTIME_DEP']:
    leave_times_first['ACTUALTIME_DEP'] = leave_times_first['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
for row in leave_times_last['PLANNEDTIME_ARR']:
    leave_times_last['PLANNEDTIME_ARR'] = leave_times_last['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_last['PLANNEDTIME_DEP']:
    leave_times_last['PLANNEDTIME_DEP'] = leave_times_last['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_last['ACTUALTIME_ARR']:
    leave_times_last['ACTUALTIME_ARR'] = leave_times_last['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_last['ACTUALTIME_DEP']:
    leave_times_last['ACTUALTIME_DEP'] = leave_times_last['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
for row in trips_first['PLANNEDTIME_ARR']:
    trips_first['PLANNEDTIME_ARR'] = trips_first['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trips_first['PLANNEDTIME_DEP']:
    trips_first['PLANNEDTIME_DEP'] = trips_first['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trips_first['ACTUALTIME_ARR']:
    trips_first['ACTUALTIME_ARR'] = trips_first['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trips_first['ACTUALTIME_DEP']:
    trips_first['ACTUALTIME_DEP'] = trips_first['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
for row in trips_last['PLANNEDTIME_ARR']:
    trips_last['PLANNEDTIME_ARR'] = trips_last['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trips_last['PLANNEDTIME_DEP']:
    trips_last['PLANNEDTIME_DEP'] = trips_last['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trips_last['ACTUALTIME_ARR']:
    trips_last['ACTUALTIME_ARR'] = trips_last['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in trips_last['ACTUALTIME_DEP']:
    trips_last['ACTUALTIME_DEP'] = trips_last['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
leave_times_first.head(40)
leave_times_last.head(40)<jupyter_output><empty_output><jupyter_text>Some patterns I see here so far is that it is only showing times during rush hour (5pm). So the theory that the first instance 77A appeared would present a time for an early trip is not going to be shown here. As we can see, the times for both first and last instance of 77A is around the same time. It seems they want to capture only the rush hour times. Also noticed that the plannedtime_arr and plannedtime_dep are the same. So the buses plan to never stop at all? Might be worth discussing about.To conclude, they only capture rush hour times which is ideal because then we can probably work with this data. Now, for data cleaning and preparation, it might be worth looking at the times during a holiday or something.<jupyter_code>vehicles.loc[vehicles['VEHICLEID']==1001060	]<jupyter_output><empty_output><jupyter_text>Going to see the rest of 77A routes times for leavetimes table.<jupyter_code># Making new df in order of PROGRNUMBER
leave_times_77a1 = pd.read_sql('select * from chunk_sql where TRIPID = 5955226;', con)
leave_times_77a1 = leave_times_77a1.loc[leave_times["DAYOFSERVICE"]=="03-JAN-18 00:00:00"]
leave_times_77a1 = leave_times_77a1.sort_values(by='PROGRNUMBER')

# Converting minutes to datetime for leavetimes first, last and trips first, last
for row in leave_times_77a1['PLANNEDTIME_ARR']:
    leave_times_77a1['PLANNEDTIME_ARR'] = leave_times_77a1['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a1['PLANNEDTIME_DEP']:
    leave_times_77a1['PLANNEDTIME_DEP'] = leave_times_77a1['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a1['ACTUALTIME_ARR']:
    leave_times_77a1['ACTUALTIME_ARR'] = leave_times_77a1['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a1['ACTUALTIME_DEP']:
    leave_times_77a1['ACTUALTIME_DEP'] = leave_times_77a1['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
# Making new df in order of PROGRNUMBER
leave_times_77a2 = pd.read_sql('select * from chunk_sql where TRIPID = 5955227;', con)
leave_times_77a2  = leave_times_77a2.loc[leave_times_77a2["DAYOFSERVICE"]=="03-JAN-18 00:00:00"]
leave_times_77a2  = leave_times_77a2.sort_values(by='PROGRNUMBER')

# Converting minutes to datetime for leavetimes first, last and trips first, last
for row in leave_times_77a2['PLANNEDTIME_ARR']:
    leave_times_77a2['PLANNEDTIME_ARR'] = leave_times_77a2['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a2['PLANNEDTIME_DEP']:
    leave_times_77a2['PLANNEDTIME_DEP'] = leave_times_77a2['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a2['ACTUALTIME_ARR']:
    leave_times_77a2['ACTUALTIME_ARR'] = leave_times_77a2['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a2['ACTUALTIME_DEP']:
    leave_times_77a2['ACTUALTIME_DEP'] = leave_times_77a2['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
# Making new df in order of PROGRNUMBER
leave_times_77a3 = pd.read_sql('select * from chunk_sql where TRIPID = 5955228;', con)
leave_times_77a3 = leave_times_77a3.loc[leave_times_77a3["DAYOFSERVICE"]=="03-JAN-18 00:00:00"]
leave_times_77a3 = leave_times_77a3.sort_values(by='PROGRNUMBER')

# Converting minutes to datetime for leavetimes first, last and trips first, last
for row in leave_times_77a3['PLANNEDTIME_ARR']:
    leave_times_77a3['PLANNEDTIME_ARR'] = leave_times_77a3['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a3['PLANNEDTIME_DEP']:
    leave_times_77a3['PLANNEDTIME_DEP'] = leave_times_77a3['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a3['ACTUALTIME_ARR']:
    leave_times_77a3['ACTUALTIME_ARR'] = leave_times_77a3['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in leave_times_77a3['ACTUALTIME_DEP']:
    leave_times_77a3['ACTUALTIME_DEP'] = leave_times_77a3['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
leave_times_77a1
leave_times_77a2
leave_times_77a3<jupyter_output><empty_output><jupyter_text>Next question: **how do we find the trip in leavetimes table for every hour for the same route??**Get rows for all of leavetimes trips according to a specific date (Jan 3, 2018).<jupyter_code>leave_times_77 = pd.read_sql('select * from chunk_sql where DAYOFSERVICE = "03-JAN-18 00:00:00";', con)
leave_times_77.shape
stopid395 = leave_times_77.loc[leave_times_77['STOPPOINTID']==395]
stopid395 = stopid395.loc[stopid395['PROGRNUMBER']==1]
for row in stopid395['PLANNEDTIME_ARR']:
    stopid395['PLANNEDTIME_ARR'] = stopid395['PLANNEDTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in stopid395['PLANNEDTIME_DEP']:
    stopid395['PLANNEDTIME_DEP'] = stopid395['PLANNEDTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))

for row in stopid395['ACTUALTIME_ARR']:
    stopid395['ACTUALTIME_ARR'] = stopid395['ACTUALTIME_ARR'].replace(row, str(datetime.timedelta(seconds=row)))

for row in stopid395['ACTUALTIME_DEP']:
    stopid395['ACTUALTIME_DEP'] = stopid395['ACTUALTIME_DEP'].replace(row, str(datetime.timedelta(seconds=row)))
stopid395 = stopid395.sort_values(by='PLANNEDTIME_ARR')
stopid395.tail(50)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0965320158868546, 'fraction_numerical': 0.028576964060835028, 'mean_word_length': 6.8446048632218845, 'pattern_counts': {'":': 0, '<': 65, '<?xml version=': 0, '>': 69, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '122657', 'license': 'FTL', 'n_tokens_mistral': 7268, 'n_tokens_neox': 6596, 'n_words': 1932, 'path': 'Understanding/Faye - Data Understanding.ipynb', 'repo_name': 'CityRoute/Data-Analytics-CityRoute', 'size': '20646'}