Philadelphia Vehicle Collisions Report

import os
os.environ['QT_QPA_PLATFORM_PLUGIN_PATH'] = 'c:/users/evere/Anaconda3/Library/plugins/platforms'

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import warnings

warnings.filterwarnings("ignore")

path = "C:/Users/evere/OneDrive/Desktop/ANACONDA PYTHON DIRECTORY/data/"

filename = path + 'collision_crash_2020_2024.csv'

df = pd.read_csv(filename)
df2 = df[['day_of_week','crash_month','crash_year',
    'hour_of_day','injury_count','fatal_count',
    'alcohol_related','drug_related']]

Introduction

The visualizations below depict information regarding vehicular collisions that took place in Philadelphia, PA. The time frame of recorded incidents spans from 2020-2024. With the visualizations we are seeking to learn which hours of the day hold the most recorded collisions, what time of year these incidents took place, information on injuries or deaths, and the proportion of collisions involving one or more impaired drivers.

Data set

The visualizations below were created from an extensive data set which included information regarding injuries or deaths, whether the driver was under the influence, age of the driver, location of the collision, as well as time and date of the collision. Under the hours column of the data set there were missing values, but there were still enough instances present to make meaningful observations. The unfiltered data set has 36303 rows and 231 columns. When filtered for the use of these visualizations, we observed the 36303 rows within 8 columns. When factoring for the missing values in the hours column, we had 1962 rows within the 8 columns.

Findings

Visualization 1: Scatter plot

#data preparation:
df_inj_or_fat = df2[['crash_year', 'crash_month','injury_count','fatal_count']]
df_inj_or_fat['total_injuries_or_deaths'] = df_inj_or_fat['injury_count'] + df_inj_or_fat['fatal_count']
df_inj_or_fat = df_inj_or_fat.groupby(['crash_year','crash_month'])['total_injuries_or_deaths'].sum().reset_index(name='i_or_f_count')

#Scatterplot code
plt.figure(figsize=(18,10))
plt.scatter(df_inj_or_fat['crash_month'], df_inj_or_fat['crash_year'], marker = 'o',cmap = 'viridis', 
            c=df_inj_or_fat['i_or_f_count'],s=(df_inj_or_fat['i_or_f_count']**3)/3e5, edgecolors='black')
plt.title('Count of Injuries or Fatalities as a Result of Collision by Month and Year', fontsize=18)
plt.xlabel('Month',fontsize=14)
plt.ylabel('Year',fontsize=14)

plt.yticks(sorted(df_inj_or_fat['crash_year'].unique()))

plt.xticks(sorted(df_inj_or_fat['crash_month'].unique()))

cbar = plt.colorbar()
cbar.set_label('Count of Injuries or Fatalities', rotation = 270, 
               fontsize=14,color='black',labelpad=30)

plt.show()

Visualization 2: Bar Chart

#data preparation:
df3 = df2
df3['hour_of_day'] = df3['hour_of_day'].fillna(100, inplace=True)
badhours = df3[df3['hour_of_day'] == 100]
df3 = df3[ -df3['hour_of_day'].isin(badhours.hour_of_day)].reset_index()
hourdf = df3[['hour_of_day']]
hourdf = hourdf[(hourdf['hour_of_day'] >= 0) & (hourdf['hour_of_day'] <= 23)]
hourdf = hourdf.groupby('hour_of_day')['hour_of_day'].count().reset_index(name = 'count')

def pick_colors(data):
    colors=[]
    avg = data['count'].mean()
    for each in data['count']:
        if each > avg*1.01:
            colors.append('orange')
        elif each < avg*.99:
            colors.append('green')
        else:
            colors.append('teal')
    return colors
  
#Bar chart code:
import matplotlib.patches as mpatches

topvalue = int(hourdf['hour_of_day'].max())
bottomvalue = int(hourdf['hour_of_day'].min())

my_colors1 = pick_colors(hourdf)
my_colors1

above = mpatches.Patch(color='orange', label = 'Above Average')
at = mpatches.Patch(color='teal', label = 'Within 1% of Average')
below = mpatches.Patch(color='green', label = 'Below Average')

fig = plt.figure(figsize=(18,16))
ax1 = fig.add_subplot(2,1,1)
ax1.bar(hourdf.hour_of_day,hourdf['count'],label='Count', color = my_colors1)

ax1.legend(handles = [above, at, below], fontsize=14)

ax1.spines['right'].set_visible(False)
ax1.spines['top'].set_visible(False)

plt.axhline(hourdf['count'].mean(), color = 'black', linestyle = 'dashed')

ax1.set_xticks(range(bottomvalue,topvalue +1))

ax1.set_title('Frequency of Traffic Incidents by Hour of Day', size = 20)


plt.show()

Visualization 3: Pie Chart

#data preparation:
pie_df = df2.groupby(['alcohol_related','drug_related']).size().reset_index(name='Sober or Impaired Drivers')
number_of_colors = len(pie_df.index.unique())
ref_number = np.arange(number_of_colors)*4

grouped_data = pie_df.groupby(['alcohol_related', 'drug_related'])['Sober or Impaired Drivers'].sum()

label_mapping = {
    (0, 0): 'Sober / No Drugs',
    (1, 0): 'Alcohol Only',
    (0, 1): 'Drugs Only',
    (1, 1): 'Alcohol & Drugs'
}

grouped_data = grouped_data.rename(index=label_mapping)

pie_df['alcohol_related'] = pie_df['alcohol_related'].replace({0: 'No alcohol', 1: 'Drinking alcohol'})
pie_df['drug_related'] = pie_df['drug_related'].replace({0: 'No drugs', 1: 'Drug impairment'})

#Pie chart code:
fig = plt.figure(figsize = (10,10))
ax = fig.add_subplot(1,1,1)

colormap = plt.get_cmap("tab20c")
outer_colors = colormap(ref_number)

total_crashes = pie_df['Sober or Impaired Drivers'].sum()
pie_df.groupby(['alcohol_related','drug_related'])['Sober or Impaired Drivers'].sum().plot(
        kind = 'pie', radius=1, colors = outer_colors, pctdistance = 1.04,
        labeldistance = None, wedgeprops = dict(edgecolor = 'w'), textprops = {'fontsize':10},
        autopct = lambda p: '{:.2f}%\n({:,})'.format(p, round((p/100)*total_crashes),2), startangle = 90,
legend = True)

hole = plt.Circle((0,0),0.3, fc='white')
fig1 = plt.gcf()
fig1.gca().add_artist(hole)

ax.yaxis.set_visible(False)
plt.title('Proportion of Sober to Impaired Vehicle Collisions', fontsize=18)

ax.text(0,0, 'Total Collisions\n' + str(total_crashes), size = 18, ha='center',va='center')

ax.axis('equal')

plt.tight_layout()
plt.show()

Visualization 4: Multiple Line Graph

#data preparation:
df_days = df3.groupby(['day_of_week','hour_of_day'])['hour_of_day'].count().reset_index(name='count')
range_of_valid_hours = range(0,24)
range_of_valid_hours

df_days = df_days[df_days['hour_of_day'].isin(range_of_valid_hours)]
df_days

df_days['day_of_week'] = df_days['day_of_week'].replace({1: 'Mon',
                                                                2: 'Tue',
                                                               3: 'Wed',
                                                               4: 'Thu',
                                                               5: 'Fri',
                                                                6: 'Sat',
                                                                7: 'Sun'})
#Multiple line chart code:

from matplotlib.ticker import FuncFormatter

fig = plt.figure(figsize = (18, 10))
ax = fig.add_subplot(1,1,1)
my_colors = {'Mon':'red',
             'Tue':'green',
             'Wed':'yellow',
             'Thu':'blue',
             'Fri':'purple',
             'Sat':'orange',
             'Sun':'brown'}

for key, grp in df_days.groupby('day_of_week'):
    grp.plot(ax = ax, kind='line', x='hour_of_day', y='count', color = my_colors[key], label=key, marker =8)



plt.title('Total Collisions by Hour Each Day', fontsize=18)
ax.set_xlabel('Hour', fontsize = 18)
ax.set_ylabel('Amount of Collisions', fontsize = 18, labelpad = 20)
ax.tick_params(axis='x', labelsize=14, rotation = 0)
ax.tick_params(axis='y', labelsize=14, rotation = 0)
ax.set_xticks(np.arange(24))
handles, labels = ax.get_legend_handles_labels()
handles = [ handles[1], handles[5], handles[6], handles[4], handles[0], handles[2], handles[3] ]
labels = [ labels[1], labels[5], labels[6], labels[4], labels[0], labels[2],labels[3] ]
plt.legend(handles, labels, loc = 'upper center', fontsize=14, ncol=1)
plt.tight_layout

plt.show()

Visualization 5: Heatmap

#data preparation:
df_my = df2[['crash_year', 'crash_month']]
df_my = df_my.groupby(['crash_year','crash_month'])['crash_year'].count().reset_index(name='count')
df_my = pd.pivot_table(df_my, index='crash_year', columns = 'crash_month',values = 'count')

#Heatmap code:
import seaborn as sns
from matplotlib.ticker import FuncFormatter

fig = plt.figure(figsize=(18,10))
ax = fig.add_subplot(1,1,1)

comma_fmt = FuncFormatter(lambda x, p:format(int(x), ','))

ax= sns.heatmap(df_my, linewidth = 0.2, annot = True, cmap = 'YlOrRd', fmt=',.0f',
                square = True, annot_kws={'size':11},
                cbar_kws = {'format':comma_fmt, 'orientation':'vertical'})
plt.title('Count of Recorded Collisions By Year and Month', fontsize=18,pad=15)
plt.xlabel('Month', fontsize=18, labelpad=10)
plt.ylabel('Year', fontsize=18, labelpad=10)

plt.yticks(rotation=0,size=14)

plt.xticks(size=14)

ax.invert_yaxis()

cbar= ax.collections[0].colorbar
max_count = int(df_my.to_numpy().max())

cbar.set_label('Number of Collisions', rotation =  270, fontsize=14, color ='black', labelpad = 20)

plt.show()

Conclusion

With the assistance of the visualizations above, we are able to draw several interesting conclusions. When analyzing the bar chart, one can notice an upwards trend in recorded collisions after 3pm. This doesn’t come to a surprise as this marks the beginning of rush hour. By analyzing the scatter plot we can note that most recorded injuries and fatalities within the data set took place between 2020. One can also notice the amount of injuries or fatalities seemed to decline in the winter months of these years. By analyzing the pie chart one can observe that of the 36303 recorded collisions, 1.52% were reportedly using drugs, 2.88% were under the influence of alcohol, and .98% were reported to be on both drugs and alcohol. By observing the multiple line chart, one can still notice the increase in collisions around the time of day that rush hour takes place, but there doesn’t seem to be any particular day of the week that stands out as the worst for collisions. Lastly, the heat map visualization shows that August 2020 was the month with the highest amount of collisions. This corroborates what the scatter plot depicted, as there was a heavier concentration of injuries and fatalities during this time frame in that visualization as well.