import pandas as pd
import matplotlib.pyplot as plt
plt.style.use("ggplot")
%matplotlib inline

"""
Explanation: Swiss road accidents 2011 - 2015
Data source: Federal Roads Office (FEDRO)
Analysing every road accident in Switzerland from 2011 to 2015 using Pandas.
End of explanation
"""


unfaelle2011 = pd.read_csv("Export_1_Unfallblatt_2011.csv", delimiter = ";", encoding = "latin-1")
unfaelle2012 = pd.read_csv("Export_1_Unfallblatt_2012.csv", delimiter = ";", encoding = "latin-1")
unfaelle2013 = pd.read_csv("Export_1_Unfallblatt_2013.csv", delimiter = ";", encoding = "latin-1")
unfaelle2014 = pd.read_csv("Export_1_Unfallblatt_2014.csv", delimiter = ";", encoding = "latin-1")
unfaelle2015 = pd.read_csv("Export_1_Unfallblatt_2015.csv", delimiter = "\t", encoding = "UTF-8")

"""
Explanation: Importing files on accidents, .csv Files, organised by year
End of explanation
"""


personen2011 = pd.read_csv("Export_3_Personenblatt_2011.csv", delimiter = ";", encoding = "latin-1")
personen2012 = pd.read_csv("Export_3_Personenblatt_2012.csv", delimiter = ";", encoding = "latin-1")
personen2013 = pd.read_csv("Export_3_Personenblatt_2013.csv", delimiter = ";", encoding = "latin-1")
personen2014 = pd.read_csv("Export_3_Personenblatt_2014.csv", delimiter = ";", encoding = "latin-1")
personen2015 = pd.read_csv("Export_3_Personenblatt_2015.csv", delimiter = "\t", encoding = "UTF-8")

"""
Explanation: Importing files on people involved in accidents, .csv Files, organised by year
End of explanation
"""


fahrzeuge2011 = pd.read_csv("Export_2_Objektblatt_2011.csv", delimiter = ";", encoding = "latin-1")
fahrzeuge2012 = pd.read_csv("Export_2_Objektblatt_2012.csv", delimiter = ";", encoding = "latin-1")
fahrzeuge2013 = pd.read_csv("Export_2_Objektblatt_2013.csv", delimiter = ";", encoding = "latin-1")
fahrzeuge2014 = pd.read_csv("Export_2_Objektblatt_2014.csv", delimiter = ";", encoding = "latin-1")
fahrzeuge2015 = pd.read_csv("Export_2_Objektblatt_2015.csv", delimiter = "\t", encoding = "UTF-8")

"""
Explanation: Importing files on vehicles involved in accidents, .csv Files, organised by year
End of explanation
"""


df_unfaelle = pd.concat([unfaelle2011, unfaelle2012, unfaelle2013, unfaelle2014, unfaelle2015], ignore_index=True)
df_personen = pd.concat([personen2011, personen2012, personen2013, personen2014, personen2015], ignore_index=True)
df_fahrzeuge = pd.concat([fahrzeuge2011, fahrzeuge2012, fahrzeuge2013, fahrzeuge2014, fahrzeuge2015], ignore_index=True)

#df_unfaelle.columns

#df_personen.columns

#df_fahrzeuge.columns
#contains the "Hauptverursacher UAP" (ja/nein) category

"""
Explanation: Concatenating the files
End of explanation
"""


df_unfaelle['Unfall-UID'].count()

df_unfaelle.groupby('Jahr')['Unfall-UID'].count()

df_unfaelle.groupby('Jahr')['Unfall-UID'].count().plot(kind='bar')
plt.savefig("Unfaelle_pro_Jahr.svg")
plt.savefig("Unfaelle_pro_Jahr.png")

"""
Explanation: Contents
OVERVIEW
1. Total number of road accidents in Switzerland since 2011? How have they developed over the years? Make a bar graph. 
2. On which day of the week do the most accidents occur? And at what time of day? Make a graph of that as well.
3. Compare the various vehicle categories involved in accidents. Total and by year.
4. What was the most expensive crash in the past five years? Where was it? And which crash one was the most deadly?
5. How many accidents occured on roads without ligthing?
6. How have drug and alcohol related accidents developed in the past 5 years?
7. Combine as much information about Breatheliser record as possible.
8. What ist the average age of crash drivers? Overall? And how has it changed over the years?
9. What about female vs. male drivers?
10. List the 10 accident hotspots in Switzerland?
 1. Total number of road accidents in Switzerland since 2011? How have they developed over the years? Make a bar graph.
End of explanation
"""


df_unfaelle['Wochentag-Nr'].value_counts()

df_unfaelle['Wochentag-Nr'].astype(str).str.replace('6', "Saturday").str.replace('4', "Thursday").str.replace('1', "Monday").str.replace('2', "Tuesday").str.replace('3', "Wednesday").str.replace('5', "Friday").str.replace('7', "Sunday").value_counts(ascending=True).plot(kind='barh')
plt.savefig("Unfaelle_pro_Tag.svg")
plt.savefig("Unfaelle_pro_Tag.png")

plt.savefig("Unfaelle_pro_Tag.svg")
plt.savefig("Unfaelle_pro_Tag.png")

"""
Explanation: Most recently there has been a slight increase in road accidents in Switzerland.
 2. On which day of the week do the most accidents occur? And at what time of day? Make graph of that as well.
End of explanation
"""


#df_unfaelle['Unfallzeit'].value_counts(ascending=False).hist(kind='scatter', x=)
#df_unfaelle['Unfallzeit'].value_counts().plot(kind='barh', x=['Unfallzeit'])
#df_unfaelle['Unfallzeit_Double'] = df_unfaelle['Unfallzeit']
df_unfaelle['Unfallzeit'].value_counts().head(4)

"""
Explanation: Transforming the data into times is proving a problem. They need to be transformed, so I can make a histogram of the times. With time as X-axis and count of accidents corresponding to the time on Y-xis. This post on timestamps in Pandas may be of some help.
End of explanation
"""


df_fahrzeuge['Fahrzeugart UAP'].value_counts().head(5)
#money_players = nba[nba['2013 $'] != 'n/a']

"""
Explanation: 3. Compare the top ten various vehicle categories involved in accidents. Total and by year.
End of explanation
"""


#df_fahrzeuge['Fahrzeugart UAP'].astype(str).str.replace('|', "").value_counts(ascending=False)
#vehicles_without_pipes.astype(str).str.replace('710', "Cars").value_counts(ascending=False).head(10)
test = df_fahrzeuge['Fahrzeugart UAP'].astype(str).str.replace('|', "").value_counts(ascending=False)
pd.DataFrame(test).head(10)
#test['Fahrzeugart UAP']
#test['Fahrzeugart UAP'].astype(str).str.replace('712', "Vans")
#test.columns()

#.str.replace('712', "Vans").str.replace('733', "n/a").str.replace('730', "Bicycles").str.replace('725', "Motorbikes, above 25kw").str.replace('718', "Trucks, above 7,4t").str.replace('722', "Mopeds").str.replace('724', "Motorbike, to 25kw").str.replace('720', "Semi trailer, above 7,5t").

"""
Explanation: The "|" sign ist causing a lot of problems as it doesn't allow me to change the names of the numbers in one go. And I haven't figures out how to do it in two goes.
End of explanation
"""


df_unfaelle.sort_values('Total geschätzter Sachschaden (in 1000 CHF)', ascending=False)[['Total geschätzter Sachschaden (in 1000 CHF)', 'Datum', 'x-Koordinate', 'y-Koordinate', 'Kanton Kürzel', 'Aktuelle BFS Gemeinde-Nr']].head(5)

"""
Explanation: 4. What was the most expensive crash in the past five years? Where was it? And which crash was the most deadly?
End of explanation
"""


df_unfaelle.sort_values('Getötete', ascending=False)[['Getötete', 'Datum', 'x-Koordinate', 'y-Koordinate', 'Kanton Kürzel', 'Aktuelle BFS Gemeinde-Nr']].head(5)

"""
Explanation: The accident causing the greatest damage, 3 Million Swiss Francs, happened on 26 October 2012 in Canton Thurgau, here, or here on Google Maps.
End of explanation
"""


Strassenbeleuchtung UAP 640 keine 641 ausser Betrieb

df_unfaelle[(df_unfaelle['Lichtverhältnis UAP'] == 622) & (df_unfaelle['Strassenbeleuchtung UAP'] == 640)]


#df[(df['animal'] == 'cat') & (df['inches'] > 12)]

#df[df['feet'] > 6.5]

df_unfaelle[(df_unfaelle['Lichtverhältnis UAP'] == 622) & (df_unfaelle['Strassenbeleuchtung UAP'] == 641)]

"""
Explanation: The most deadly accident was a bus accident involving mostly school kids from Belgien. And this one more recently. 
5. How many accidents occured on roads without ligthing? (Maybe even work out an accident hotspot?
End of explanation
"""


df_unfaelle[df_unfaelle['Hauptursache UAP'] == 1101].groupby("Jahr")["Hauptursache UAP"].value_counts().plot(kind='bar')

"""
Explanation: Including the over 1000 locations were the lights were out of order, over 10% of accidents happened in poorly lit locations. 
 6. How have drug and alcohol related accidents developed in the past 5 years?
ALCOHOL
End of explanation
"""


df_unfaelle[df_unfaelle['Hauptursache UAP'] == 1102].groupby("Jahr")["Hauptursache UAP"].value_counts().plot(kind='bar')

"""
Explanation: DRUGS
End of explanation
"""


df_fahrzeuge["Atemtest"].describe()

df_fahrzeuge["Atemtest"].value_counts()

Alcohol_record = df_fahrzeuge[df_fahrzeuge['Atemtest'] == 9.0]

Alcohol_record[['BUM Probe angeordnet UAP', 'Blutalkoholtest', 'Datum', 'Vertrautheit Strecke UAP', 'Kennzeichen Fahrzeug Kanton']]

df_merged = df_personen.merge(df_fahrzeuge, how = 'left', left_on = 'Unfall-UID', right_on ='Unfall-UID')

df = df_merged.merge(df_unfaelle, how = 'left', left_on = 'Unfall-UID', right_on ='Unfall-UID')

df_fahrzeuge.info()

df_unfaelle.info()

df_personen.info()

"""
Explanation: 7. Combine as much information about breatheliser record as possible.
MERGING: Look at this again!!!*
End of explanation
"""


