<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Section 2b
In this section we will explore the dataframes individually to understand who is involved in accidents and how.
We'll use the year 2014 as example since we don't expect the accident's properties to change drastically from year to year.
Python imports
Step1:  Who is involved ?
We will start by investigating the Users dataframe to see who is the most, or the least, involved in car accidents. In particular let's look at the age and gender distributions.
Step3:  The best is to have one function drawing the same plots for different input dataframes, i.e. for different conditions.
Step4:  We can see that men are a lot more involved in accidents than women. Let's try to understand this distribution by looking at the distribution for pedestrians, drivers and passagers separately.
Pedestrians involved in accidents
Pedestrians are recorded in two categories in the user type column
Step5:  The ratio men/women displays some unexpected variations below 40. This can maybe be explained by different activities and lifestyles at different ages for men and women. The fact that the ratio is less than one above 60 could be due to women living longer than men in average. Numbers on the population by gender of France would be useful here.
Drivers involved in accidents
The user type is 1 for drivers
Step6:  The dataframe contains all vehicle types, including bicycles which is why we have accidents involving drivers under the age of 16. This analysis of the drivers will be easier to interpret if we separate also by vehicle type. However the vehicle type is not stored in the Users dataframe. We need to import the vehicle type from the Vehicles dataframe.
Step7:  Cars are identified with the number 7 in the vehicle type column.
Step8:  Passengers involved in accidents
<ASSISTANT_TASK:>
Python Code:
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')
# Extract the list of colors from this style for later use
cycl = mpl.rcParams['axes.prop_cycle']
colors = cycl.by_key()['color']
from CSVtoSQLconverter import load_sql_engine
sqlEngine = load_sql_engine()
Explanation: Section 2b
In this section we will explore the dataframes individually to understand who is involved in accidents and how.
We'll use the year 2014 as example since we don't expect the accident's properties to change drastically from year to year.
Python imports
End of explanation
users_df = pd.read_sql_query('''SELECT * FROM safer_roads.users
                            WHERE `accident id` > 2014E+8 AND `accident id` < 2015E+8''',
                            sqlEngine)
users_df.head()
Explanation: Who is involved ?
We will start by investigating the Users dataframe to see who is the most, or the least, involved in car accidents. In particular let's look at the age and gender distributions.
End of explanation
def men_women_dist(in_df, title=''):
    Plot the age distribution for men and women, and the ratio.
    # Extract age for each gender
    women_sr = in_df[in_df['sex']==2]['age'].dropna()
    men_sr = in_df[in_df['sex']==1]['age'].dropna()
    
    # Bin the ages in bins with 2 years of width
    bin_range = range(0,102,2)
    women_bins = pd.cut(women_sr, bin_range,right=False).value_counts().sort_index()
    men_bins = pd.cut(men_sr, bin_range,right=False).value_counts().sort_index()
    
    # Calculate the ratio between men and women
    ratio_bins = men_bins/women_bins
    error_bins = ratio_bins * np.sqrt((men_bins+women_bins)/(men_bins*women_bins))
    
    # Plot the stacked distribution of age for men and women
    plt.subplots(figsize=(14,8))
    plt.subplot(211)
    plt.hist([women_sr,men_sr],bins=50,range=(0,100) , stacked=True, normed = False, histtype='bar',
            rwidth=1.0, color=[colors[1],colors[0]]);
    plt.title(title)
    plt.ylabel('counts')
    leg_men = mpatches.Patch(color=colors[0], label='Men')
    leg_women = mpatches.Patch(color=colors[1], label='Women')
    plt.legend(handles=[leg_men,leg_women],frameon=True)
    # Plot the ratio men/women
    plt.subplot(212)
    plt.errorbar(range(1,100,2),ratio_bins,xerr=1.0,yerr=error_bins, fmt='none')
    plt.xlim([0,100])
    plt.xlabel('age in years')
    plt.ylabel('ratio men/women')
men_women_dist(users_df, 'All accidents, all users')
Explanation: The best is to have one function drawing the same plots for different input dataframes, i.e. for different conditions.
End of explanation
men_women_dist(users_df[(users_df['user type'] == 3) | (users_df['user type'] == 4)] , 'All accidents, pedestrians')
Explanation: We can see that men are a lot more involved in accidents than women. Let's try to understand this distribution by looking at the distribution for pedestrians, drivers and passagers separately.
Pedestrians involved in accidents
Pedestrians are recorded in two categories in the user type column:
- id 3 is for pure pedestrian
- id 4 is for pedestrians using rollerskates or rollerblades
We will combine them here because we are not interested in the different cases
End of explanation
men_women_dist(users_df[users_df['user type'] == 1] , 'All accidents, drivers')
Explanation: The ratio men/women displays some unexpected variations below 40. This can maybe be explained by different activities and lifestyles at different ages for men and women. The fact that the ratio is less than one above 60 could be due to women living longer than men in average. Numbers on the population by gender of France would be useful here.
Drivers involved in accidents
The user type is 1 for drivers
End of explanation
vehicles_df = pd.read_sql_query('''SELECT * FROM safer_roads.vehicles
                            WHERE `accident id` > 2014E+8 AND `accident id` < 2015E+8''',
                            sqlEngine)
users_vt_df = users_df.merge(vehicles_df[['vehicle type','accident id','vehicle id']],
                             on=['accident id','vehicle id'],how='inner')
users_vt_df.head()
Explanation: The dataframe contains all vehicle types, including bicycles which is why we have accidents involving drivers under the age of 16. This analysis of the drivers will be easier to interpret if we separate also by vehicle type. However the vehicle type is not stored in the Users dataframe. We need to import the vehicle type from the Vehicles dataframe.
End of explanation
men_women_dist(users_df[(users_df['user type'] == 1) & (users_vt_df['vehicle type'] == 7)] ,
               'All accidents, drivers of cars')
Explanation: Cars are identified with the number 7 in the vehicle type column.
End of explanation
men_women_dist(users_df[(users_df['user type'] == 2) & (users_vt_df['vehicle type'] == 7)] ,
               'All accidents, passengers of cars')
Explanation: Passengers involved in accidents
End of explanation
<END_TASK>