Skip to content

Instantly share code, notes, and snippets.

@abfo

abfo/great-highway.ipynb

Last active July 21, 2024 00:20
Show Gist options
  • Save abfo/fe346fdeda4a37f3f7e3873e1240f0cb to your computer and use it in GitHub Desktop.
Save abfo/fe346fdeda4a37f3f7e3873e1240f0cb to your computer and use it in GitHub Desktop.
Download ZIP
Analysis of traffic accidents and the closure of the Great Highway in San Francisco. See https://ithoughthecamewithyou.com/post/does-closing-the-great-highway-cause-an-increase-in-traffic-accidents for details.
Display the source blob
Display the rendered blob
Raw
great-highway.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Traffic Accidents and Great Highway\n",
"\n",
"Use the [data set](https://data.sfgov.org/Public-Safety/Traffic-Crashes-Resulting-in-Injury/) from Data SF to understand what impact closing the Great Highway has on traffic accidents and deaths. The data set convers the entire city, so filtering to 37.763814, -122.512532 (top left) to 37.736261, -122.493263 (bottom right) to cover the area with the most impact. \n",
"\n",
"The great highway was [closed](https://en.wikipedia.org/wiki/Great_Highway) from April 2020 due to the pandemic, and then opened again to cars starting in August 2021. It remaings closed on wekeends, Friday afternoons and holidays. \n",
"\n",
"Pre period = 2017-05-28 to 2020-02-29\n",
"Post period = 2021-09-01 to 2024-06-04\n",
"\n",
"Both 1,008 days (36 28 day periods, calculated [here](https://docs.google.com/spreadsheets/d/1S92g33eZFrFHkxnITt4n64qkG3uls3RVP2ks3MFaKXk/edit?usp=sharing)).\n",
"\n",
"Note for deaths, 0 in pre period, 2 in post period, one Sat and one Mon so both open and not open"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import plotly.express as px"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\rob\\AppData\\Local\\Temp\\ipykernel_9504\\1758319835.py:1: DtypeWarning:\n",
"\n",
"Columns (3,15) have mixed types. Specify dtype option on import or set low_memory=False.\n",
"\n"
]
},
{
"data": {
"application/vnd.plotly.v1+json": {
"config": {
"plotlyServerURL": "https://plot.ly"
},
"data": [
{
"alignmentgroup": "True",
"hovertemplate": "Period=2017-05-28 to 2020-02-29<br>day_of_week=%{x}<br>number_injured=%{y}<extra></extra>",
"legendgroup": "2017-05-28 to 2020-02-29",
"marker": {
"color": "#636efa",
"pattern": {
"shape": ""
}
},
"name": "2017-05-28 to 2020-02-29",
"offsetgroup": "2017-05-28 to 2020-02-29",
"orientation": "v",
"showlegend": true,
"textposition": "auto",
"type": "bar",
"x": [
"Monday",
"Tuesday",
"Wednesday",
"Thursday",
"Friday",
"Saturday",
"Sunday"
],
"xaxis": "x",
"y": [
32,
23,
16,
19,
24,
30,
22
],
"yaxis": "y"
},
{
"alignmentgroup": "True",
"hovertemplate": "Period=2021-09-01 to 2024-06-04<br>day_of_week=%{x}<br>number_injured=%{y}<extra></extra>",
"legendgroup": "2021-09-01 to 2024-06-04",
"marker": {
"color": "#EF553B",
"pattern": {
"shape": ""
}
},
"name": "2021-09-01 to 2024-06-04",
"offsetgroup": "2021-09-01 to 2024-06-04",
"orientation": "v",
"showlegend": true,
"textposition": "auto",
"type": "bar",
"x": [
"Monday",
"Tuesday",
"Wednesday",
"Thursday",
"Friday",
"Saturday",
"Sunday"
],
"xaxis": "x",
"y": [
19,
14,
14,
16,
21,
20,
9
],
"yaxis": "y"
}
],
"layout": {
"barmode": "group",
"legend": {
"title": {
"text": "Period"
},
"tracegroupgap": 0
},
"template": {
"data": {
"bar": [
{
"error_x": {
"color": "#2a3f5f"
},
"error_y": {
"color": "#2a3f5f"
},
"marker": {
"line": {
"color": "#E5ECF6",
"width": 0.5
},
"pattern": {
"fillmode": "overlay",
"size": 10,
"solidity": 0.2
}
},
"type": "bar"
}
],
"barpolar": [
{
"marker": {
"line": {
"color": "#E5ECF6",
"width": 0.5
},
"pattern": {
"fillmode": "overlay",
"size": 10,
"solidity": 0.2
}
},
"type": "barpolar"
}
],
"carpet": [
{
"aaxis": {
"endlinecolor": "#2a3f5f",
"gridcolor": "white",
"linecolor": "white",
"minorgridcolor": "white",
"startlinecolor": "#2a3f5f"
},
"baxis": {
"endlinecolor": "#2a3f5f",
"gridcolor": "white",
"linecolor": "white",
"minorgridcolor": "white",
"startlinecolor": "#2a3f5f"
},
"type": "carpet"
}
],
"choropleth": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"type": "choropleth"
}
],
"contour": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"colorscale": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
],
"type": "contour"
}
],
"contourcarpet": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"type": "contourcarpet"
}
],
"heatmap": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"colorscale": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
],
"type": "heatmap"
}
],
"heatmapgl": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"colorscale": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
],
"type": "heatmapgl"
}
],
"histogram": [
{
"marker": {
"pattern": {
"fillmode": "overlay",
"size": 10,
"solidity": 0.2
}
},
"type": "histogram"
}
],
"histogram2d": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"colorscale": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
],
"type": "histogram2d"
}
],
"histogram2dcontour": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"colorscale": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
],
"type": "histogram2dcontour"
}
],
"mesh3d": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"type": "mesh3d"
}
],
"parcoords": [
{
"line": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "parcoords"
}
],
"pie": [
{
"automargin": true,
"type": "pie"
}
],
"scatter": [
{
"fillpattern": {
"fillmode": "overlay",
"size": 10,
"solidity": 0.2
},
"type": "scatter"
}
],
"scatter3d": [
{
"line": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scatter3d"
}
],
"scattercarpet": [
{
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scattercarpet"
}
],
"scattergeo": [
{
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scattergeo"
}
],
"scattergl": [
{
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scattergl"
}
],
"scattermapbox": [
{
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scattermapbox"
}
],
"scatterpolar": [
{
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scatterpolar"
}
],
"scatterpolargl": [
{
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scatterpolargl"
}
],
"scatterternary": [
{
"marker": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"type": "scatterternary"
}
],
"surface": [
{
"colorbar": {
"outlinewidth": 0,
"ticks": ""
},
"colorscale": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
],
"type": "surface"
}
],
"table": [
{
"cells": {
"fill": {
"color": "#EBF0F8"
},
"line": {
"color": "white"
}
},
"header": {
"fill": {
"color": "#C8D4E3"
},
"line": {
"color": "white"
}
},
"type": "table"
}
]
},
"layout": {
"annotationdefaults": {
"arrowcolor": "#2a3f5f",
"arrowhead": 0,
"arrowwidth": 1
},
"autotypenumbers": "strict",
"coloraxis": {
"colorbar": {
"outlinewidth": 0,
"ticks": ""
}
},
"colorscale": {
"diverging": [
[
0,
"#8e0152"
],
[
0.1,
"#c51b7d"
],
[
0.2,
"#de77ae"
],
[
0.3,
"#f1b6da"
],
[
0.4,
"#fde0ef"
],
[
0.5,
"#f7f7f7"
],
[
0.6,
"#e6f5d0"
],
[
0.7,
"#b8e186"
],
[
0.8,
"#7fbc41"
],
[
0.9,
"#4d9221"
],
[
1,
"#276419"
]
],
"sequential": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
],
"sequentialminus": [
[
0,
"#0d0887"
],
[
0.1111111111111111,
"#46039f"
],
[
0.2222222222222222,
"#7201a8"
],
[
0.3333333333333333,
"#9c179e"
],
[
0.4444444444444444,
"#bd3786"
],
[
0.5555555555555556,
"#d8576b"
],
[
0.6666666666666666,
"#ed7953"
],
[
0.7777777777777778,
"#fb9f3a"
],
[
0.8888888888888888,
"#fdca26"
],
[
1,
"#f0f921"
]
]
},
"colorway": [
"#636efa",
"#EF553B",
"#00cc96",
"#ab63fa",
"#FFA15A",
"#19d3f3",
"#FF6692",
"#B6E880",
"#FF97FF",
"#FECB52"
],
"font": {
"color": "#2a3f5f"
},
"geo": {
"bgcolor": "white",
"lakecolor": "white",
"landcolor": "#E5ECF6",
"showlakes": true,
"showland": true,
"subunitcolor": "white"
},
"hoverlabel": {
"align": "left"
},
"hovermode": "closest",
"mapbox": {
"style": "light"
},
"paper_bgcolor": "white",
"plot_bgcolor": "#E5ECF6",
"polar": {
"angularaxis": {
"gridcolor": "white",
"linecolor": "white",
"ticks": ""
},
"bgcolor": "#E5ECF6",
"radialaxis": {
"gridcolor": "white",
"linecolor": "white",
"ticks": ""
}
},
"scene": {
"xaxis": {
"backgroundcolor": "#E5ECF6",
"gridcolor": "white",
"gridwidth": 2,
"linecolor": "white",
"showbackground": true,
"ticks": "",
"zerolinecolor": "white"
},
"yaxis": {
"backgroundcolor": "#E5ECF6",
"gridcolor": "white",
"gridwidth": 2,
"linecolor": "white",
"showbackground": true,
"ticks": "",
"zerolinecolor": "white"
},
"zaxis": {
"backgroundcolor": "#E5ECF6",
"gridcolor": "white",
"gridwidth": 2,
"linecolor": "white",
"showbackground": true,
"ticks": "",
"zerolinecolor": "white"
}
},
"shapedefaults": {
"line": {
"color": "#2a3f5f"
}
},
"ternary": {
"aaxis": {
"gridcolor": "white",
"linecolor": "white",
"ticks": ""
},
"baxis": {
"gridcolor": "white",
"linecolor": "white",
"ticks": ""
},
"bgcolor": "#E5ECF6",
"caxis": {
"gridcolor": "white",
"linecolor": "white",
"ticks": ""
}
},
"title": {
"x": 0.05
},
"xaxis": {
"automargin": true,
"gridcolor": "white",
"linecolor": "white",
"ticks": "",
"title": {
"standoff": 15
},
"zerolinecolor": "white",
"zerolinewidth": 2
},
"yaxis": {
"automargin": true,
"gridcolor": "white",
"linecolor": "white",
"ticks": "",
"title": {
"standoff": 15
},
"zerolinecolor": "white",
"zerolinewidth": 2
}
}
},
"title": {
"text": "Great Highway Area Injuries, Pre and Post-Pandemic"
},
"xaxis": {
"anchor": "y",
"domain": [
0,
1
],
"title": {
"text": "Day of the Week"
}
},
"yaxis": {
"anchor": "x",
"domain": [
0,
1
],
"title": {
"text": "Number of Injuries"
}
}
}
}
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"df = pd.read_csv('Traffic_Crashes_Resulting_in_Injury_20240720.csv')\n",
"\n",
"# the dataset has latitude and longitude in tb_latitude and tb_longitude columns,\n",
"# filter to only include records in the bounding box from 37.763814, -122.512532 (top left) to 37.736261, -122.493263 (bottom right)\n",
"df = df[(df['tb_latitude'] >= 37.736261) & (df['tb_latitude'] <= 37.763814) & (df['tb_longitude'] >= -122.512532) & (df['tb_longitude'] <= -122.493263)]\n",
"\n",
"# now filter to only include dates between 2017-05-28 to 2020-02-29 and 2021-09-01 to 2024-06-04 using the collision_date column\n",
"df['collision_date'] = pd.to_datetime(df['collision_date'])\n",
"df = df[(df['collision_date'] >= '2017-05-28') & (df['collision_date'] <= '2020-02-29') | (df['collision_date'] >= '2021-09-01') & (df['collision_date'] <= '2024-06-04')]\n",
"\n",
"# Add a column that is set to Pre-Pandemic between 2017-05-28 to 2020-02-29 and Post-Pandemic between 2021-09-01 to 2024-06-04\n",
"df['Period'] = '2017-05-28 to 2020-02-29'\n",
"df.loc[(df['collision_date'] >= '2021-09-01') & (df['collision_date'] <= '2024-06-04'), 'Period'] = '2021-09-01 to 2024-06-04'\n",
"\n",
"# group by the pandemic and day_of_week columns and sum the number_injured column divided by 1008\n",
"df = df.groupby(['Period', 'day_of_week'])['number_injured'].sum() \n",
"\n",
"# chart in plotly showing the number of injuries by day of the week for the Pre-Pandemic and Post-Pandemic periods, order day_of_week by Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday\n",
"df = df.reindex(['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'], level=1)\n",
"\n",
"fig = px.bar(df.reset_index(), x='day_of_week', y='number_injured', color='Period', barmode='group', title='Great Highway Area Injuries, Pre and Post-Pandemic')\n",
"\n",
"# set x axis title to Day of the Week, y axis title to Number of Injuries, and legend title to Period\n",
"fig.update_xaxes(title_text='Day of the Week')\n",
"fig.update_yaxes(title_text='Number of Injuries')\n",
"\n",
"\n",
"fig.show()\n",
"\n",
"# save the chart to a file\n",
"fig.write_html('injuries.html')\n"
]
}
],
"metadata": {
"kernelspec": {
"display_name": ".venv",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.11.6"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment