In [2]:
import param
import pandas as pd 
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import panel as pn

# configure seaborn settings
sns.set()
sns.set(style="ticks", color_codes=True)
sns.set_context("notebook")
sns.set({ "figure.figsize": (12/1.5,8/1.5) })
sns.set_style("whitegrid", {'axes.edgecolor':'gray'})
plt.rcParams['figure.dpi'] = 360

# make plots static
%matplotlib inline

# add a grid to the plots 
plt.rcParams['axes.grid'] = True
plt.rcParams['axes.axisbelow'] = True

pn.extension()

# import the road traffic accident data from the csv files
accidents = pd.read_csv('STATS19AccData20052018.csv')
casualties = pd.read_csv('STATS19CasData20052018.csv')
vehicles = pd.read_csv('STATS19VehData20052018.csv')

# get list of duplicated accident indexes
dupes = pd.DataFrame(accidents['Accident Index'].value_counts().sort_values(ascending=False)).reset_index()
dupes.columns=['Accident Index', 'Number of Instances']
duplicated_acc_indexes = dupes.loc[dupes['Number of Instances']>1, 'Accident Index']

# drop rows with duplicated accident indexes from all 3 data sets
accidents.drop(accidents[accidents['Accident Index'].isin(duplicated_acc_indexes)].index, inplace = True) 
casualties.drop(casualties[casualties['Accident Index'].isin(duplicated_acc_indexes)].index, inplace = True) 
vehicles.drop(vehicles[vehicles['Accident Index'].isin(duplicated_acc_indexes)].index, inplace = True) 

# Combine data and select key columns of interest
acc_and_cas = casualties.merge(accidents, on=['Accident Index', 'Year'])
df = acc_and_cas.merge(vehicles, on=['Accident Index', 'Year', 'VehicleReferenceNumber'])
key_cols = df.loc[:, ['Accident Index','Year','CasTypeCode','Severity',
                      'LocalAuthority', 'Road1Class', 'VehicleType']]

## map Severity levels
severity_mapping ={1 : 'Fatal', 2 : 'Serious', 3 : 'Slight'} 
  
# apply the mapping to change the numbers to the labels
key_cols['Severity'] = key_cols['Severity'].map(severity_mapping) 

## map CasTypeCode 
cas_type_mapping ={0:'Pedestrian', 1 : 'Cyclist/pillion passenger', 2 : 'Motorcycle 50cc & under rider/passenger', 
                   3 : 'Motorcycle 50cc to 125cc rider/passenger', 4 : 'Motorcycle 125cc to 500cc rider/passenger', 
                   5 : 'Motorcycle over 500cc rider/passenger', 8 : 'Taxi/Private hire car occupant', 
                   9 : 'Car occupant', 10 : 'Minibus (8 - 16 passenger seats) occupant', 
                   11 : 'Bus or coach occupant (17 or more pass seats)', 
                   14 : 'Other motor vehicle occupant', 15 : 'Other non-motor vehicle occupant', 
                   16 : 'Horse rider', 17 : 'Agricultural vehicle occupant', 18 : 'Tram/Light rail occupant', 
                   19 : 'Goods vehicle (3.5 tonnes mgw or under) occupant', 
                   20 : 'Goods vehicle (over 3.5t. and under 7.5t.) occupant', 
                   21 : 'Goods vehicle (7.5 tonnes mgw and over) occupant', 
                   96 : 'Motorcycle over 125cc rider/passenger', 97 : 'Motorcycle - unknown cc rider/passenger', 
                   98 : 'Goods vehicle (unknown weight) occupant', 99 : 'Unknown', 255 : 'OoR'} 
  
# apply the mapping to change the numbers to the labels
key_cols['CasType'] = key_cols['CasTypeCode'].map(cas_type_mapping) 

## map VehicleType
v_type_mapping ={1 : 'Pedal cycle', 
                 2 : 'Motorcycle 50cc and under', 
                 3 : 'Motorcycle 50cc to 125cc', 
                 4 : 'Motorcycle 125cc to 500cc', 
                 5 : 'Motorcycle over 500cc', 
                 8 : 'Taxi/Private hire car', 
                 9 : 'Car', 
                 10 : 'Minibus (8-16 seats)', 
                 11 : 'Bus/coach (17+ seats)', 
                 14 : 'Other motor vehicle', 
                 15 : 'Other non-motor vehicle', 
                 16 : 'Ridden horse', 
                 17 : 'Agricultural vehicle', 
                 18 : 'Tram/Light rail', 
                 19 : 'Goods vehicle 3.5 tonnes mgw & under', 
                 20 : 'Goods vehicle 3.5 tonnes to 7.5 tonnes mgw', 
                 21 : 'Goods vehicle 7.5+ tonnes mgw'} 
  
# apply the mapping to change the numbers to the labels
key_cols['VehicleType'] = key_cols['VehicleType'].map(v_type_mapping) 

## map LocalAuthority
la_mapping ={100 : 'Bolton', 101 : 'Bury', 102 : 'Manchester',
            104:'Oldham', 106:'Rochdale', 107:'Salford', 109:'Stockport', 
            110:'Tameside', 112:'Trafford', 114:'Wigan'} 
  
# apply the mapping to change the numbers to the labels
key_cols['LocalAuthority'] = key_cols['LocalAuthority'].map(la_mapping) 

## map Road1Class
rc_mapping ={1 : 'Motorway', 
            2:'A(M)',
            3:'A',
            4:'B',
            5:'C',
            6:"Unclass'd",
            7:'Unknown'} 
  
# apply the mapping to change the numbers to the labels
key_cols['Road1Class'] = key_cols['Road1Class'].map(rc_mapping) 

# Add vehicle and casualty groups, and road

## CasTypeGroup
cas_type_grp_mapping ={'Pedestrian':'Pedestrian', 'Cyclist/pillion passenger':'Cycle', 
                     'Motorcycle 50cc & under rider/passenger':'Motorcycle', 
                     'Motorcycle 50cc to 125cc rider/passenger':'Motorcycle', 
                     'Motorcycle 125cc to 500cc rider/passenger':'Motorcycle', 
                     'Motorcycle over 500cc rider/passenger':'Motorcycle', 
                     'Taxi/Private hire car occupant':'Other', 
                     'Car occupant':'Car', 
                     'Minibus (8 - 16 passenger seats) occupant':'Other', 
                     'Bus or coach occupant (17 or more pass seats)':'Other', 
                     'Other motor vehicle occupant':'Other', 
                     'Other non-motor vehicle occupant':'Other', 
                     'Horse rider':'Other', 
                     'Agricultural vehicle occupant':'Other', 
                     'Tram/Light rail occupant':'Other', 
                     'Goods vehicle (3.5 tonnes mgw or under) occupant':'Other', 
                     'Goods vehicle (over 3.5t. and under 7.5t.) occupant':'Other', 
                     'Goods vehicle (7.5 tonnes mgw and over) occupant':'Other', 
                     'Motorcycle over 125cc rider/passenger':'Motorcycle', 
                     'Motorcycle - unknown cc rider/passenger':'Motorcycle', 
                     'Goods vehicle (unknown weight) occupant':'Other', 
                     'Unknown':'Unknown', 
                     'OoR':'OoR'} 
  
# apply the mapping to change the numbers to the labels
key_cols['CasTypeGroup'] = key_cols['CasType'].map(cas_type_grp_mapping) 


## Create a vehicle type group feature
vg_type_mapping ={'Pedal cycle':'Pedal cycle', 
                 'Motorcycle 50cc and under':'Motorcycle', 
                 'Motorcycle 50cc to 125cc':'Motorcycle', 
                 'Motorcycle 125cc to 500cc':'Motorcycle', 
                 'Motorcycle over 500cc':'Motorcycle', 
                 'Taxi/Private hire car':'Taxi/Private hire car', 
                 'Car':'Car', 
                 'Minibus (8-16 seats)':'Minibus (8-16 seats)', 
                 'Bus/coach (17+ seats)':'Bus/coach (17+ seats)', 
                 'Other motor vehicle':'Other motor vehicle', 
                 'Other non-motor vehicle':'Other non-motor vehicle', 
                 'Ridden horse':'Ridden horse', 
                 'Agricultural vehicle':'Agricultural vehicle', 
                 'Tram/Light rail':'Tram/Light rail', 
                 'Goods vehicle 3.5 tonnes mgw & under':'Goods vehicle', 
                 'Goods vehicle 3.5 tonnes to 7.5 tonnes mgw':'Goods vehicle', 
                 'Goods vehicle 7.5+ tonnes mgw':'Goods vehicle'} 
  
# apply the mapping to change the numbers to the labels
key_cols['VehicleGrp'] = key_cols['VehicleType'].map(vg_type_mapping) 

# drop CasTypeCode and CasType
key_cols = key_cols.drop(['CasTypeCode', 'CasType'], axis='columns')

class DashboardDataElements(param.Parameterized):
    
    # widget containing the list of areas
    Area = param.ObjectSelector(default='Manchester', objects=list(key_cols['LocalAuthority'].unique()))
    
    # seaborn line plot for casualties for Greater Manchester as a whole
    def timeline_view_all(self):
        casualties_by_year = pd.DataFrame(key_cols.groupby(['Year']).size())
        casualties_by_year.columns=['Casualties']
        casualties_by_year = casualties_by_year.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.lineplot(x='Year',y='Casualties', data=casualties_by_year, lw=3)
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.xlabel('', size=14)
        plt.ylabel('Casualties', size=14)
        plt.title('Number Casualties 2005 to 2018 \n(Whole of Greater Manchester)', size=14)
        plt.xlim(2005, 2020)
        plt.ylim(0, 14000)
        plt.subplots_adjust(left=0.2, bottom=0.2)
        plt.axvline(x=2016, ls='--', lw=2, color='#ebae34')
        plt.text(x=2016.2, y=8000, s='New reporting \nsystems \nintroduced \nin 2016', 
                 color='#c9952c', fontsize=14)
        sns.despine()
        plt.close()
        return ax.figure
    
    # seaborn bar plot for severity for Greater Manchester as a whole for 2018
    def severity_view_gm_2018(self):
        data = key_cols[(key_cols.Year==2018)].copy()
        casualties_by_severity_2018_gm = pd.DataFrame(data.groupby(['Severity']).size())
        casualties_by_severity_2018_gm.columns=['Casualties']
        casualties_by_severity_2018_gm = casualties_by_severity_2018_gm.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.barplot(x='Severity',y='Casualties', data=casualties_by_severity_2018_gm, lw=3,
                        palette=['#eb3434', '#eb7a34', '#ebae34'])
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.xlabel('', size=14)
        plt.ylabel('Casualties \n(log scale)', size=14)
        plt.title('Number Casualties By Severity in 2018 \n(Whole of Greater Manchester)', size=14)
        plt.yscale('log')
        plt.ylim( (10**0,10**4) )
        plt.subplots_adjust(left=0.2, bottom=0.2)
        sns.despine()
        plt.close()
        return ax.figure
    
    # seaborn bar plot for casualty type for Greater Manchester as a whole for 2018
    def cas_view_gm_2018(self):
        data = key_cols[(key_cols.Year==2018)].copy()
        casualties_by_casgrp_2018_gm = pd.DataFrame(data.groupby(['CasTypeGroup']).size())
        casualties_by_casgrp_2018_gm.columns=['Casualties']
        casualties_by_casgrp_2018_gm = casualties_by_casgrp_2018_gm.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.barplot(y='CasTypeGroup', x='Casualties', data=casualties_by_casgrp_2018_gm, lw=3,
                        color=sns.color_palette()[0])
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.ylabel('Casualty Type', size=14)
        plt.xlabel('Casualties', size=14)
        plt.title('Number Casualties By Casualty Vehicle Type in 2018 \n(Whole of Greater Manchester)', size=14)
        plt.xlim(0, 3000)
        plt.subplots_adjust(left=0.2, bottom=0.2)
        sns.despine()
        plt.close()
        return ax.figure
    
    # seaborn bar plot for road type for whole of Greater Manchester in 2018
    def road_view_gm_2018(self):
        data = key_cols[(key_cols.Year==2018)].copy()
        casualties_by_road_2018_gm = pd.DataFrame(data.groupby(['Road1Class']).size())
        casualties_by_road_2018_gm.columns=['Casualties']
        casualties_by_road_2018_gm = casualties_by_road_2018_gm.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.barplot(y='Road1Class',x='Casualties', data=casualties_by_road_2018_gm, lw=3,
                        color=sns.color_palette()[0])
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.ylabel('Road Type', size=14)
        plt.xlabel('Casualties', size=14)
        plt.title('Number Casualties By Road Type in 2018 \n(Whole of Greater Manchester)', size=14)
        plt.xlim(0, 3000)
        plt.subplots_adjust(left=0.2, bottom=0.2)
        sns.despine()
        plt.close()
        return ax.figure
    
    # create data set containing only the data applicable to the chosen area
    def get_data(self):
        class_df = key_cols[(key_cols.LocalAuthority==self.Area)].copy()
        return class_df
    
    # create data set containing only the data applicable to the chosen area for 2018
    def get_data_2018(self):
        class_df_2018 = key_cols[(key_cols.LocalAuthority==self.Area) & (key_cols.Year==2018)].copy()
        return class_df_2018
    
    # seaborn line plot for casualties for chosen area
    def timeline_view_area(self):
        data = self.get_data()
        casualties_by_year_la = pd.DataFrame(data.groupby(['Year']).size())
        casualties_by_year_la.columns=['Casualties']
        casualties_by_year_la = casualties_by_year_la.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.lineplot(x='Year',y='Casualties', data=casualties_by_year_la, lw=3, color=sns.color_palette()[0])
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.xlabel('', size=14)
        plt.ylabel('Casualties', size=14)
        plt.title('Number Casualties 2005 to 2018 \n(' + self.Area + ')', size=14)
        plt.ylim(0, 4000)
        plt.xlim(2005, 2020)
        plt.subplots_adjust(left=0.2, bottom=0.2)
        plt.axvline(x=2016, ls='--', lw=2, color='#ebae34')
        plt.text(x=2016.2, y=2500, s='New reporting \nsystems \nintroduced \nin 2016', 
                 color='#c9952c', fontsize=14)
        sns.despine()
        plt.close()
        return ax.figure
    
    # seaborn bar plot for severity for chosen area
    def severity_view_area_2018(self):
        data = self.get_data_2018()
        casualties_by_severity_2018_la = pd.DataFrame(data.groupby(['Severity']).size())
        casualties_by_severity_2018_la.columns=['Casualties']
        casualties_by_severity_2018_la = casualties_by_severity_2018_la.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.barplot(x='Severity',y='Casualties', data=casualties_by_severity_2018_la, lw=3,
                        palette=['#eb3434', '#eb7a34', '#ebae34'])
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.xlabel('', size=14)
        plt.ylabel('Casualties (log scale)', size=14)
        plt.title('Number Casualties By Severity in 2018 \n(' + self.Area + ')', size=14)
        plt.yscale('log')
        plt.ylim( (10**0,10**4) )
        plt.subplots_adjust(left=0.2, bottom=0.2)
        sns.despine()
        plt.close()
        return ax.figure
    
    # seaborn bar plot for casualty type for chosen area
    def cas_view_area_2018(self):
        data = self.get_data_2018()
        casualties_by_casgrp_2018_la = pd.DataFrame(data.groupby(['CasTypeGroup']).size())
        casualties_by_casgrp_2018_la.columns=['Casualties']
        casualties_by_casgrp_2018_la = casualties_by_casgrp_2018_la.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.barplot(y='CasTypeGroup',x='Casualties', data=casualties_by_casgrp_2018_la, lw=3,
                        color=sns.color_palette()[0])
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.ylabel('Casualty Type', size=14)
        plt.xlabel('Casualties', size=14)
        plt.title('Number Casualties By Casualty Vehicle Type in 2018 \n(' + self.Area + ')', size=14)
        plt.xlim(0, 750)
        plt.subplots_adjust(left=0.2, bottom=0.2)
        sns.despine()
        plt.close()
        return ax.figure
    
    # seaborn bar plot for road type for chosen area
    def road_view_area_2018(self):
        data = self.get_data_2018()
        casualties_by_road_2018_la = pd.DataFrame(data.groupby(['Road1Class']).size())
        casualties_by_road_2018_la.columns=['Casualties']
        casualties_by_road_2018_la = casualties_by_road_2018_la.reset_index()
        
        plt.figure(figsize=(7,4))
        ax = sns.barplot(y='Road1Class',x='Casualties', data=casualties_by_road_2018_la, lw=3,
                        color=sns.color_palette()[0])
        plt.xticks(size=14)
        plt.yticks(size=14)
        plt.ylabel('Road Type', size=14)
        plt.xlabel('Casualties', size=14)
        plt.title('Number Casualties By Road Type in 2018 \n(' + self.Area + ')', size=14)
        plt.xlim(0, 600)
        plt.subplots_adjust(left=0.2, bottom=0.2)
        sns.despine()
        plt.close()
        return ax.figure
    
# create an instance of the class
dde = DashboardDataElements(name='')

dashboard_title = '### Greater Manchester Road Traffic Accident Casualties (2005-2018)'

# define contents of a tab to show plots for a specific local authority
tab_la = pn.Column(dde.param,
                  pn.Row(dde.timeline_view_area, dde.severity_view_area_2018),
                  pn.Row(dde.cas_view_area_2018, dde.road_view_area_2018))

# define contents of a tab to show plots for all of Greater Manchester
tab_all_yrs_gm = pn.Column(pn.Row(dde.timeline_view_all, dde.severity_view_gm_2018),
                          pn.Row(dde.cas_view_gm_2018, dde.road_view_gm_2018))

# define contents of a tab for important notes about the data
tab_notes = pn.Column('#### Notes',
            'This data is available publicly from\
            [data.gov.uk](https://data.gov.uk/dataset/25170a92-0736-4090-baea-bf6add82d118/gm-road-casualty-accidents-full-stats19\
            -data). ',
            "The population and population density is much higher in Manchester than in the other \
            Greater Manchester local authorities. Based on data from the \
            [ONS](https://www.ons.gov.uk/peoplepopulationandcommunity/populationandmigration/populationestimates/datasets/populationestimatesforukenglandandwalesscotlandandnorthernireland), \
            Manchesters 2018 population density was estimated to be 4735 people per square kilometre. \
            The other local authorities range from 1391 to 2617.",
            'The mechanism for defining casualty severity changed in 2016 according to [this 2017 report from the Department for Transport about casualties in Great Britain](https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/744077/reported-road-casualties-annual-report-2017.pdf):',
            '"_From 2016 onwards, figures on the severity of injury have been affected by a large \
            number of police forces changing their reporting systems. It is likely that the recording \
            of injury severity is more accurate for forces using these new reporting systems. \
            This has had a large impact on the number of serious injuries recorded in 2016 (24,101) \
            and 2017 (24,831) compared with 2015 (22,144). Some of these serious injuries may previously \
            have been classified as slight injuries which means that the 2016 and 2017 serious injury figures \
            are not comparable to previous years._""')

# define the conten ts and layout of the deshboard
dashboard = pn.Column(dashboard_title, pn.Tabs(('By Local Authority',tab_la), 
                                               ('All Greater Mcr',tab_all_yrs_gm),
                                               ('Notes', tab_notes)))

# display the dashboard, with all elements and data embedded so 
# no 'calls' to a data source are required
dashboard.embed(max_opts = 10)