Step 1: Download Project Data

Most of the data features from the project are available as a GeoJSON online. The exception is the 2023 311 data, which we have to use an API to access.

Import Packages

Code

import geopandas as gpd
import numpy as np
import pandas as pd
import requests
from datetime import datetime

Scrape 2023 311 Pothole Data

Code

r = requests.get(
    "https://vc0.publicstuff.com/api/2.0/requests_list",
    params={"client_id": 242, "page": 1, "limit": 2},
)
json = r.json()

request_list = json["response"]["requests"]

data = pd.DataFrame([r["request"] for r in request_list])

# Store the data we request
data = []

# Total number of pages
total_pages = 400

# Loop over each page
for page_num in range(1, total_pages + 1):
    # Print out the page number

    # Make the request
    r = requests.get(
        "https://vc0.publicstuff.com/api/2.0/requests_list",
        params={
            "client_id": 242,  # Unique identifier for Philadelphia
            "page": page_num,  # What page of data to pull
            "limit": 700,  # How many rows per page
        },
    )

    # Get the json
    d = r.json()

    # Add the new data to our list and filter for pothole requests
    data += [r["request"] for r in d["response"]["requests"] if r["request"]["title"] == "Pothole Repair"]

# Convert the Unix timestamp to a readable date format
for entry in data:
    entry['date_created'] = datetime.utcfromtimestamp(entry['date_created']).strftime('%Y-%m-%d %H:%M:%S')

# Create a dataframe
data_df = pd.DataFrame(data)

#Convert to a .csv
data_df.to_csv("2023_311.csv", index=False)
data_df.drop('count_comments', axis = 1,inplace=True)data_df.drop('count_comments', axis = 1,inplace=True)

data_df.head()

	primary_attachment	id	image_thumbnail	title	description	status	address	location	zipcode	foreign_id	date_created	count_followers	count_supporters	lat	lon	user_follows	user_comments	user_request	rank	user
0	{'id': 4692570, 'extension': 'msg', 'content_t...	14672150	None	Pothole Repair	This is the second request for this issue	submitted	10475 Drummond Rd, Philadelphia, PA 19154, USA		None	16419274	2023-12-01 15:16:02	0	0	40.086141	-74.994741	0	0	0	1
1	{'id': 4692545, 'extension': 'jpg', 'content_t...	14672086	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	None	submitted	801–809 E Passyunk Ave,Philadelphia, PA 19147	Philadelphia, Pennsylvania	19147	16419240	2023-12-01 15:09:05	0	0	39.938665	-75.152811	0	0	0	1	kirkpatrick1
2	NaN	14671596		Pothole Repair	massive and deep sinkhole with scrape marks an...	submitted	633 McKean St, Philadelphia, PA 19148, USA		None	16418815	2023-12-01 13:14:10	0	0	39.924178	-75.157464	0	0	0	1
3	NaN	14671586		Pothole Repair	large hole in the middle of the street.	submitted	5617 Sansom St, Philadelphia, PA 19139, USA		None	16418807	2023-12-01 13:11:57	1	1	39.958902	-75.233801	0	0	0	1
4	{'id': 4692370, 'extension': 'jpg', 'content_t...	14671578	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	Pothole continues to sink and get worse. Also,...	submitted	1231 S 13th St,Philadelphia, PA 19147	Philadelphia, Pennsylvania	19147	16418799	2023-12-01 13:09:48	0	0	39.934306	-75.165147	0	0	0	1	HoumanZiai55a1c7df4ee5b
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
7785	NaN	13040205		Pothole Repair	None	submitted	234 S Melville St,Philadelphia, PA 19139	Philadelphia, Pennsylvania	19139	15388999	2022-12-16 20:06:27	0	0	39.953839	-75.213336	0	0	0	1
7786	NaN	13039942		Pothole Repair	None	submitted	W Penn St & Henry Ave,Philadelphia, PA 19129	Philadelphia, Pennsylvania	19129	15388616	2022-12-16 19:19:39	0	0	40.016399	-75.187699	0	0	0	1	marksoffa
7787	NaN	13039867		Pothole Repair	huge pot holes between langdon and adams multi...	submitted	Langdon St & E Roosevelt Blvd,Philadelphia, PA...	Philadelphia, Pennsylvania	19124	15388513	2022-12-16 19:07:17	0	0	40.027391	-75.099160	0	0	0	1	sami.smithestock
7788	{'id': 4052191, 'extension': 'jpeg', 'content_...	13039559	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	undefined	submitted	15023 Barcalow Ave, Feasterville-Trevose, PA 1...		None	15388122	2022-12-16 18:17:42	0	0	40.135118	-75.010646	0	0	0	1
7789	NaN	13039546		Pothole Repair		submitted	2413 Panama St, Philadelphia, PA 19103, USA		None	15388112	2022-12-16 18:16:42	0	0	39.947961	-75.180752	0	0	0	1	patrick.oconnell

7790 rows × 20 columns

Filter the 311 data for 2023 only

Code

data_df['date_created'] = pd.to_datetime(data_df['date_created'])

# Filter rows based on the year 2023
data_df_2023 = data_df[data_df['date_created'].dt.year == 2023]
data_df_2023

	primary_attachment	id	image_thumbnail	title	description	status	address	location	zipcode	foreign_id	date_created	count_followers	count_supporters	lat	lon	user_follows	user_comments	user_request	rank	user
0	{'id': 4692570, 'extension': 'msg', 'content_t...	14672150	None	Pothole Repair	This is the second request for this issue	submitted	10475 Drummond Rd, Philadelphia, PA 19154, USA		None	16419274	2023-12-01 15:16:02	0	0	40.086141	-74.994741	0	0	0	1
1	{'id': 4692545, 'extension': 'jpg', 'content_t...	14672086	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	None	submitted	801–809 E Passyunk Ave,Philadelphia, PA 19147	Philadelphia, Pennsylvania	19147	16419240	2023-12-01 15:09:05	0	0	39.938665	-75.152811	0	0	0	1	kirkpatrick1
2	NaN	14671596		Pothole Repair	massive and deep sinkhole with scrape marks an...	submitted	633 McKean St, Philadelphia, PA 19148, USA		None	16418815	2023-12-01 13:14:10	0	0	39.924178	-75.157464	0	0	0	1
3	NaN	14671586		Pothole Repair	large hole in the middle of the street.	submitted	5617 Sansom St, Philadelphia, PA 19139, USA		None	16418807	2023-12-01 13:11:57	1	1	39.958902	-75.233801	0	0	0	1
4	{'id': 4692370, 'extension': 'jpg', 'content_t...	14671578	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	Pothole continues to sink and get worse. Also,...	submitted	1231 S 13th St,Philadelphia, PA 19147	Philadelphia, Pennsylvania	19147	16418799	2023-12-01 13:09:48	0	0	39.934306	-75.165147	0	0	0	1	HoumanZiai55a1c7df4ee5b
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
7497	NaN	13090746		Pothole Repair	NO IT NEEDS TO BE FIXED	submitted	1830 W Ruscomb St, Philadelphia, PA 19141, USA		None	15411212	2023-01-01 16:23:24	0	0	40.029932	-75.154299	0	0	0	1
7498	{'id': 4070204, 'extension': 'jpg', 'content_t...	13090739	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	Major pothole across travel lane and bike lane...	submitted	505 Pine St,Philadelphia, PA 19106	Philadelphia, Pennsylvania	19106	15411208	2023-01-01 16:16:45	0	0	39.943770	-75.150598	0	0	0	1	mpporvaznik
7499	NaN	13090516		Pothole Repair		submitted	4800 Roosevelt Blvd, Philadelphia, PA 19124, USA		None	15411135	2023-01-01 08:43:47	0	0	40.026669	-75.097155	0	0	0	1	tajineaaa
7500	{'id': 4070100, 'extension': 'jpeg', 'content_...	13090441	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	My car got hit and another car total due to an...	submitted	1845 N 6th St, Philadelphia, PA 19122, USA		None	15411125	2023-01-01 06:05:23	0	0	39.979723	-75.144417	0	0	0	1
7501	{'id': 4070020, 'extension': 'jpg', 'content_t...	13090178	https://d17aqltn7cihbm.cloudfront.net/uploads/...	Pothole Repair	The cover for this pipe is missing.	submitted	717 S Christopher Columbus Blvd,Philadelphia, ...	Philadelphia, Pennsylvania	19147	15411089	2023-01-01 00:20:30	0	0	39.939686	-75.142069	0	0	0	1	rsuero

7502 rows × 20 columns

Convert the data to a .csv

Code

data_df_2023.to_csv("2023_311.csv", index=False)