from CSVtoSQLconverter import load_sql_engine
sqlEngine = load_sql_engine()
import pandas as pd
import numpy as np
# Provides better color palettes
import seaborn as sns

from pandas import DataFrame,Series

import matplotlib as mpl
import matplotlib.pyplot as plt
# Command to display the plots in the iPython Notebook
%matplotlib inline
import matplotlib.patches as mpatches

mpl.style.use('seaborn-whitegrid')
plt.style.use('seaborn-talk')

"""
Explanation: Section 2a
Our first look at the data will be focused on the time variations of the accidents:
- What is the evolution of the number of accidents over the years ?
- Are there more accidents certain months of the year ? Certain days of the week ?
End of explanation
"""


PerMonth = pd.read_sql_query('''SELECT YEAR(datetime), MONTH(datetime), DAY(LAST_DAY(datetime)), 
                            COUNT(`accident id`) FROM characteristics
                            GROUP BY YEAR(datetime), MONTH(datetime);''',
                            sqlEngine)
PerMonth.head()

PerMonth.rename(columns={'YEAR(datetime)':'year','MONTH(datetime)':'month',
                         'DAY(LAST_DAY(datetime))':'number days',
                         'COUNT(`accident id`)':'accident count'},inplace=True)
PerMonth.head()

"""
Explanation: Read the dataframe
We have loaded in the SQL database the years 2010 to 2014. We can directly extract from the database the counts for each month of each year and this throughout this whole section.
We use the Characteristics table since it each accident corresponds to one entry in this table. Furthermore this table contains a column for the data and time of the accident stored in the convenient datetime format.
End of explanation
"""


PerYear = PerMonth.groupby(['year'],as_index=False).sum()
PerYear

g = sns.factorplot(x="year",y='accident count',
                   data=PerYear, kind='bar', size=5, aspect=2.0)

"""
Explanation: Evolution over the years
End of explanation
"""


PerMonthNorm = PerMonth.copy()
PerMonthNorm['per day'] = PerMonthNorm['accident count'] / PerMonthNorm['number days']
PerMonthNorm.head()

sns.factorplot(x='month', y='per day', hue="year", data=PerMonthNorm, size=5,aspect=2.0)

"""
Explanation: It is pleasing to see that the number of accidents has been overall decreasing since 2010, although 2014 is slightly higher than 2013.
Number of accidents for each month
Let's see if certain months have more accidents than others. We need to normalize per days this time since the difference between the longest and shortest months corresponds to ~10% of the length of a month.
End of explanation
"""


PerMonthMean = PerMonth.groupby('month', as_index=False).mean()
PerMonthMean['per day'] = PerMonthMean['accident count'] / PerMonthMean['number days']
PerMonthMean

sns.factorplot(x='month', y='per day', data=PerMonthMean, size=5,aspect=2.0)

"""
Explanation: The 5 years we have follow the same trends overall. We can safely average over them.
End of explanation
"""


