import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
import seaborn as sns
import altair as alt
dat = pd.read_csv("crash_data_collision_crash_2007_2017.csv")
dat.columns
Index(['objectid_1', 'objectid', 'crn', 'district', 'county', 'municipality',
'crash_year', 'crash_month', 'day_of_week', 'time_of_day',
'hour_of_day', 'illumination', 'weather', 'road_condition',
'collision_type', 'relation_to_road', 'work_zone_type', 'work_zone_loc',
'work_zone_ind', 'intersect_type', 'tcd_type', 'location_type',
'urban_rural', 'fatal_count', 'injury_count', 'person_count',
'total_units', 'sch_bus_ind', 'sch_zone_ind', 'latitude', 'longitude',
'arrival_tm', 'cons_zone_spd_lim', 'dispatch_tm', 'est_hrs_closed',
'lane_closed', 'ln_close_dir', 'ntfy_hiwy_maint', 'spec_juris_cd',
'tcd_func_cd', 'tfc_detour_ind', 'workers_pres', 'wz_close_detour',
'wz_flagger', 'wz_law_offcr_ind', 'wz_ln_closure', 'wz_moving',
'wz_other', 'wz_shlder_mdn', 'vehicle_count', 'automobile_count',
'motorcycle_count', 'bus_count', 'small_truck_count',
'heavy_truck_count', 'suv_count', 'van_count', 'bicycle_count',
'maj_inj_count', 'mod_inj_count', 'min_inj_count', 'tot_inj_count',
'unk_inj_deg_count', 'unk_inj_per_count', 'driver_count_16yr',
'driver_count_17yr', 'driver_count_18yr', 'driver_count_19yr',
'driver_count_20yr', 'driver_count_50_64yr', 'driver_count_65_74yr',
'driver_count_75plus', 'unbelted_occ_count', 'unb_death_count',
'unb_maj_inj_count', 'belted_death_count', 'belted_maj_inj_count',
'mcycle_death_count', 'mcycle_maj_inj_count', 'bicycle_death_count',
'bicycle_maj_inj_count', 'ped_count', 'ped_death_count',
'ped_maj_inj_count', 'max_severity_level', 'comm_veh_count', 'dec_lat',
'dec_long', 'rdwy_surf_type_cd', 'county_name'],
dtype='object')
# create a new column that represents if the accident is associated with intersection
# data dictionary referenced dataset's metadata
dict ={0:'no',
1:'yes',
2:'yes',
3:'yes',
4:'no',
5:'yes',
6:'no',
7:'no',
8:'yes',
9:'no',
10:'no',
11:'no'
}
dat['intersection'] = dat['intersect_type'].replace(dict)
dict ={1:"weekday",
2:"weekday",
3:"weekday",
4:"weekday",
5:"weekday",
6:"weekend",
7:"weekend"}
dat['weekend'] = dat['day_of_week'].replace(dict)
# select only columns we are interested in
wanted = ['crash_year', 'crash_month', 'weekend', 'intersection', "ped_count", "ped_death_count", "ped_maj_inj_count", "hour_of_day", "weather", "tcd_type"]
df_intersection = dat[wanted]
Interested in the traffic crash patterns across time (year) and their location on the road (at or not at intersection), I picked stacked bar plot to demonstrate the change of number of crashes over time as well as whether a lot of crashes happen at intersections. With the stacking, people can see the total amount of crashes over time, as well as the number of crashes at intersection fluctuating over time. I picked the diverging color to highlight the contrast of the grouping for intersection.
Specifically, I used matplotlib but not seaborn or altair for this plot because with matplotlib, I have to configure two times (top and bottom of the stack) and for Seaborn, I also have to undergo similar process. Altair and matplotlib allow me to access a number of matplotlib’s methods with less code, but this particular data of interest that I am looking at is fairly simple and straightforward with matplotlib - since it does not have too many observations, it is easy to iterate the process to retrieve the arrays for "year", "yes" and "no" while ensuring accuracy. Therefore, matplotlib is good enough to support what I would like to visualize.
# Calculate the mean fatal crash by intersection type
df_intersection_size = df_intersection.groupby(['crash_year', 'intersection']).size().reset_index(name='counts')
year = df_intersection_size['crash_year'].unique()
no = list(df_intersection_size.loc[df_intersection_size['intersection']=='no']['counts'])
yes = list(df_intersection_size.loc[df_intersection_size['intersection']=='yes']['counts'])
fig, ax = plt.subplots()
pa = ax.bar(year, yes, label='at intersection', color="#d8b365")
pb = ax.bar(year, no,
bottom=yes, label='not at intersection', color="#5ab4ac")
plt.suptitle('Traffic Crashes 2011-2017 in Philadelphia, PA')
ax.set_title('at or not at intersection', fontsize=10)
ax.legend()
ax.bar_label(pa, label_type='center')
ax.bar_label(pb, label_type='center')
ax.set_ylabel('Count of Crashes')
ax.set_xlabel('Year')
Text(0.5, 0, 'Year')
Despite efforts to improve road safety, the number of crashes in Philadelphia has not been decreasing. The number of intersection accidents and non-intersection accidents are roughly equal and the pattern is consistent throughout years. This is interesting because media has framed intersection accidents as more frequent than non-intersection accidents, but the graph illustrates that the difference is pretty small, and that both the number of intersection and non-intersection accidents have not been reduced.
Time (hour of day and weekend/weekday) might affect the occurance of traffic crashes. I picked a faceted line plot to demonstrate the change of number of crashes over time as time is continuous and the change is also continuous, but weekday/weekend is categorical. Also interested in seeing whether time affects the location of crashes (at intersection or not), I picked this kind of plot as it could plot the complicated data of multiple groups together.
Specifically, I used seaborn but not matplotlib for this plot because Seaborn's syntax is more declarative and it is good for visualizing complex relationships between variables. With Seaborn, I don't have to extract a list of values from the dataset anymore. Because the variables are more diverse (hour_of_day has 24 values) than last one (year only has 7 values), I don't want to risk messing up their order while extracting them from dataframe to the list format - that could lead to telling the wrong story. I also tried transforming (grouping and calculating count) within seaborn but the dataset is too large. Hence I did some grouping to make the dataset smaller before being put into seaborn.
df_hour = df_intersection.groupby(["hour_of_day", "intersection", "crash_year", "weekend"]).size().reset_index(name='counts')
df_hour = df_hour.loc[df_hour["hour_of_day"] != 99]
mypalette = sns.color_palette("Set2")
g = sns.catplot(x="hour_of_day",
y="counts",
col="weekend",
hue="intersection",
ci=None,
palette=mypalette,
markers=["^", "o"],
linestyles=["-", "--"],
kind="point",
theme=sns.set_theme(style="darkgrid"),
data=df_hour)
g.set_titles("Total Traffic Crashes 2011-2017 in Philadelphia PA \n across hour of the day \n on {col_name}")
g.set_axis_labels("Hour of day", "Count of crash")
line_position = [7, 7]
for ax, pos in zip(g.axes.flat, line_position):
ax.axvline(x=pos, color='y', linestyle=':'),
line_position = [22, 22]
for ax, pos in zip(g.axes.flat, line_position):
ax.axvline(x=pos, color='b', linestyle=':'),
The plot shows up that the number of crashes is related to hour of the day and whether it is a weekday or a weekend. Generally, after 7 am, more crashes happen at intersections while after 22 pm, less crashes occur at intersections; however this trend varies slighly between weekday and weekend as the curve for weekend is flatter. This is interesting because one would expect more pedestrian on street and more traffic activities during the peak hours of the weekday, and this might be related to the higher amount of intersection accidents around 8am and 16pm. On the weekend, traffic acitivities are more steady throughout the day. Note that the number of crashes presented is a high level summary rather than a daily average, so we cannot say that fewer crashes happen during the weekend.
import altair as alt
Knowing that the at-intersection crashes are more frequent between 7am and 22pm, I wonder if this is related to pedestrain activities on the street. I also wonder if this trend is consistent for different years. Therefore, I picked the dash board that shows a bar chart depicting the distribution of at-intersection and non-intersection crashes for each year, and also a line plot that shows the average amount of pedestrians associated in the accident for hours of the day.
Compared to matplotlib and seaborn, altair is the most suitable to display complicated relationships and data because it requires the least amount of code. For example, I did a calculation of the daily value from the yearly value in the altair syntax in one line (byDay='( datum.ped_count / 365)'). It also supports interactive features. For example, here it allows people to examine the hourly trend of pedestrian-involved crashes for each year.
# average # of ped associated with hour of day
df_hour = df_intersection.groupby(["hour_of_day", "intersection", "crash_year"]).sum().reset_index()
df_hour = df_hour.loc[df_hour["hour_of_day"] != 99]
# Setup a brush selection
brush = alt.selection(type='interval')
# The top line plot: average daily accident each year
bars = (
alt.Chart(title="Average daily pedestrian involved crash by year").mark_bar().transform_calculate(
byDay='( datum.ped_count / 365)',
) .encode(
x=alt.X('crash_year:O', scale=alt.Scale(zero=False), axis=alt.Axis(title='year')),
y=alt.Y('sum(byDay):Q', scale=alt.Scale(zero=False), axis=alt.Axis(title='average number')),
color=alt.condition(brush, 'intersection:O', alt.value('lightgray'), scale=alt.Scale(scheme="Set2")),
tooltip=["crash_year:O", "sum(byDay):Q", "intersection:O"],
).properties(
selection=brush,
width=800
)
)
# the bottom line plot: average accident by hour of day
# Create a selection that chooses the nearest point & selects based on x-value
lines = (
alt.Chart(title="Pedestrian involved by hour of the day")
.mark_line().transform_calculate(
byDay='( datum.ped_count / 365)',
)
.encode(
x=alt.X('hour_of_day:O', axis=alt.Axis(title='hour of the day')),
y=alt.Y('sum(byDay):Q', axis=alt.Axis(title='average number')),
color='intersection:O',
).transform_filter(
brush.ref()
).properties(width=800)
)
chart = alt.vconcat(bars, lines, data=df_hour) # vertical stacking
chart
Similar to the total amount of crashes, the average number of pedestrians involved in crashes has not been decreasing over the years. Crashes that involve pedestrian happen more at intersections than non-intersections throughout the years, especilly for the morning and evening peak times. This pattern is generally consistent throughout 2011 - 2017.
From the previous charts, we learned that crashes are more likely to happen during the day and at intersections, where pedestrians are likely to be involved. Could crashes be related to the visibility of the surroundings, which is influenced by weather? To find out, I picked the heat map to illustrate the relationship between weather, month, and the amount of crashes.
df_month = df_intersection.groupby(["crash_month", "weather"]).size().reset_index(name='counts')
#df_month = df_month.loc[df_month["crash_month"] != 99]
# data dictionary referenced dataset's metadata
dict ={0:'Unknown',
1:'No adverse conditions',
2:'Rain',
3:'Sleet (hail)',
4:'Snow',
5:'Fog',
6:'Rain and fog',
7:'Sleet and fog',
8:'Other',
9:'Unknown',
}
df_month['weather'] = df_month['weather'].replace(dict)
chart = (
alt.Chart(df_month, title="Crash and weather")
.mark_rect()
.encode(
x=alt.X("crash_month:O", axis=alt.Axis(title=None, ticks=False)),
y=alt.Y("weather:O", axis=alt.Axis(title=None, ticks=False)),
color=alt.Color("counts:Q", sort="ascending", scale=alt.Scale(scheme="oranges")),
tooltip=["crash_month", "weather", "counts"],
)
.properties(width=700, height=500)
)
chart
We can see that although rainy days and snowy days in the colder months are associated with higher amount of crashes, most of the crashes happen when there is no adverse conditions. This suggest that crashes generally occur under normal weathers, probably because of reasons such as visibility of pedestrains or careless driving behaviors. Note that there are a number of "snow" during the warmer months, so this signify that there might be some errors in the data.
Traffic control devices are common measures to help direct traffic and ensure safety. There are different kinds of them on different types of street. To investigate their relationship with crashes, I chose to use a stacked bar chart to show if there is a pattern.
df_tcd = df_intersection.groupby(["crash_year", "tcd_type", "intersection"]).size().reset_index(name='counts')
#df_month = df_month.loc[df_month["crash_month"] != 99]
# data dictionary referenced dataset's metadata
dict ={0: "Not applicable",
1: "Flashing traffic signal",
2: "Traffic signal",
3: "Stop sign",
4: "Yield sign",
5: "Active RR crossing controls",
6: "Passive RR crossing controls",
7: "Police officer or flagman",
8: "Other Type TCD",
9: "Unknown"
}
df_tcd['tcd_type'] = df_tcd['tcd_type'].replace(dict)
alt.Chart(df_tcd, title="Crash and transportation control device type").mark_bar(
cornerRadiusTopLeft=3,
cornerRadiusTopRight=3
).encode(
x='crash_year:O',
y=alt.X('sum(counts):Q', stack="normalize", axis=alt.Axis(title='share of counts')),
color=alt.Color('tcd_type:O', scale=alt.Scale(scheme="accent")),
column='intersection:N',
tooltip=["crash_year", "counts", "tcd_type"],
)
As shown on the grouped chart, most intersections employ the control device and a lot of crashes happened at intersections with traffic signal. However, because we do not have data on the whole traffic performence, we do not have a comparison group of when traffic devices were not associated with crashes. However, by looking at the data across time, we can see that the share of crashes with control devices has not decreased over year. Although the relationship could not be deemed causal, this suggests that the number of crashes at signalled intersections have not been improving, and the measures out there to mitigate such crashes have not been as effective. Interestingly, the share of crashes related to traffic signal for non-intersections has decreased from 2011 to 2017. This could be for railroad crossing, on and off ramps, or roundabouts - these have not been classified as intersections. However, we could not make definite conclusions with the information we have due to limitations from the data.