##### Description: How to get startet with travel time data generated by Verkehrsbetriebe
#     Zürich (VBZ) from the open data platform of the City of Zurich
#     https://data.stadt-zuerich.ch/dataset/vbz_fahrzeiten_ogd
#     There you can also find further explanations (only in German)


##### Author: Verkehrsbetriebe Zürich (Y. Yankova)

##### Date: 15.09.2020


###################################################
############      Setup Section     ###############
###################################################


# Load packages
import os
import pandas as pd


# Set working directory (where the ogd-data is to be found on your disk)
wd = os.chdir("*\\01_Daten\\01_Input\\")

###################################################
############      Main Section     ################
###################################################


#### Load the data ####
# You will find the datasets at
# https://data.stadt-zuerich.ch/dataset/vbz_fahrzeiten_ogd

#### Matching tables
haltepunkt = pd.read_csv("haltepunkt.csv")
haltestelle = pd.read_csv("haltestelle.csv")


#### Load table "fahrzeiten": the travel time data is stored there
fahrz = pd.read_csv('fahrzeiten_soll_ist_20200809_20200815.csv')

#### Matching ###
# In order to get the full stops information, you need to match fahrz with haltepunkt and haltestelle

#### match fahrz with the stop points from haltepunkt according to the departure stop point
#### ("from"; german: "von")
fahrz_haltepkt_from = pd.merge(fahrz,haltepunkt,how="left",
                         left_on=["halt_punkt_id_von","halt_punkt_diva_von","halt_id_von"],
                         right_on=["halt_punkt_id","halt_punkt_diva","halt_id"])

# adjust variables names
fahrz_haltepkt_from.rename(columns={'GPS_Latitude': 'GPS_Latitude_von', 'GPS_Longitude': 'GPS_Longitude_von',
                               'GPS_Bearing': 'GPS_Bearing_von', 'halt_punkt_ist_aktiv': 'halt_punkt_ist_aktiv_von'},
                      inplace=True)

#### match fahrz with the stop points from haltepunkt according to the destination stop point
#### ("to"; german: "nach")
fahrz_haltepkt = pd.merge(fahrz_haltepkt_from,haltepunkt,how="left",
                         left_on=["halt_punkt_id_nach","halt_punkt_diva_nach","halt_id_nach"],
                         right_on=["halt_punkt_id","halt_punkt_diva","halt_id"])

# adjust variables names
fahrz_haltepkt.rename(columns={'GPS_Latitude': 'GPS_Latitude_nach', 'GPS_Longitude': 'GPS_Longitude_nach',
                               'GPS_Bearing': 'GPS_Bearing_nach', 'halt_punkt_ist_aktiv': 'halt_punkt_ist_aktiv_nach'},
                      inplace=True)


#### match fahrz_haltepkt with the stop names from haltestelle according to the stop point of departure
#### ("from"; german: "von")
fahrz_haltepkt_haltestelle_from = pd.merge(fahrz_haltepkt,haltestelle,how="left",
                         left_on=["halt_id_von","halt_diva_von","halt_kurz_von1"],
                         right_on=["halt_id","halt_diva","halt_kurz"])

# adjust variables names
fahrz_haltepkt_haltestelle_from.rename(columns={'halt_lang': 'halt_lang_von', 'halt_ist_aktiv': 'halt_ist_aktiv_von'},
                      inplace=True)

#### match fahrz_haltepkt_haltestelle_from with the stop names from haltestelle according to the destination stop point
#### ("to"; german: "nach")
fahrz_haltepkt_haltestelle = pd.merge(fahrz_haltepkt_haltestelle_from,haltestelle,how="left",
                         left_on=["halt_id_nach","halt_diva_nach","halt_kurz_nach1"],
                         right_on=["halt_id","halt_diva","halt_kurz"])

# adjust variables names
fahrz_haltepkt_haltestelle.rename(columns={'halt_lang': 'halt_lang_nach', 'halt_ist_aktiv': 'halt_ist_aktiv_nach'},
                      inplace=True)


#### Calculate the punctuality per line ####
# According to the punctuality definition of VBZ, a ride is considered on time (punctual) when the actual arrival time at the stop
# does not exceed the scheduled arrival time by more than 2 minutes (otherwise defined as "delayed") or the actual
# departure at a stop does not happen more than 1 minute earlier than the scheduled departure (otherwise defined as
# "too early")

# first calculate the difference between actual ("ist") and scheduled ("soll") arrival ("an") / departure ("ab")
# and then assign the punctuality categories accordingly
fahrz_haltepkt_haltestelle['punct_cat'] = fahrz_haltepkt_haltestelle.apply(lambda x:
                                            'delay' if x["ist_an_nach1"] - x["soll_an_nach"] >= 120 else 'too early'
                                            if x["ist_ab_nach"] - x["soll_ab_nach"]<= -60 else "punctual", axis=1)

# count the occurrences per line of each category
count_punct_cat = fahrz_haltepkt_haltestelle.groupby(['linie', 'punct_cat']).size().rename('count')

# calculate the proportions
percent_punct = 100 * (count_punct_cat / count_punct_cat.groupby(level=0).sum())

# transform to data frame and name percentage column
punctuality = percent_punct.to_frame(name='percent')

# add count column to punctuality
punctuality = pd.merge(count_punct_cat,punctuality,how="left",
                         left_on=["linie","punct_cat"],
                         right_on=["linie","punct_cat"])





