Author: Geoff Boeing
Original: pandas-to-geojson
import pandas as pd, requests, json
First download data from the city of Berkeley's API. You can use Socrata's $limit parameter to specify how many rows to grab (otherwise the default is 1,000 rows of data): https://dev.socrata.com/docs/paging.html
Example request: https://data.cityofberkeley.info/resource/k489-uv4i.json?$limit=5
# API endpoint for city of Berkeley's 311 calls
endpoint_url = 'https://data.cityofberkeley.info/resource/bscu-qpbu.json?$limit=20&$where=latitude%20%3C%3E%20%22%22'
# fetch the URL and load the data
response = requests.get(endpoint_url)
data = response.json()
Next, turn the json data into a dataframe and clean it up a bit: drop unnecessary columns and any rows that lack lat-long data. We want to make our json file as small as possible (prefer under 5 mb) so that it can be loaded over the Internet to anyone viewing your map, without taking forever to download a huge file.
# turn the json data into a dataframe and see how many rows and what columns we have
df = pd.DataFrame(data)
print('We have {} rows'.format(len(df)))
str(df.columns.tolist())
We have 20 rows
"['case_id', 'date_opened', 'case_status', 'date_closed', 'request_category', 'request_subcategory', 'request_detail', 'object_type', 'apn', 'street_address', 'city', 'state', 'neighborhood', 'latitude', 'longitude', 'location']"
# convert lat-long to floats and change address from ALL CAPS to regular capitalization
df['latitude'] = df['latitude'].astype(float)
df['longitude'] = df['longitude'].astype(float)
df['street_address'] = df['street_address'].str.title()
# we don't need all those columns - only keep useful ones
cols = ['request_detail', 'request_subcategory', 'latitude', 'longitude', 'street_address', 'case_status']
df_subset = df[cols]
# drop any rows that lack lat/long data
df_geo = df_subset.dropna(subset=['latitude', 'longitude'], axis=0, inplace=False)
print('We have {} geotagged rows'.format(len(df_geo)))
df_geo.tail()
We have 20 geotagged rows
| request_detail | request_subcategory | latitude | longitude | street_address | case_status | |
|---|---|---|---|---|---|---|
| 15 | Commercial Reminder | Commercial | 37.880719 | -122.268930 | 1475 Shattuck Ave | Closed |
| 16 | Commercial Reminder | Commercial | 37.851141 | -122.271171 | 3132 M L King Jr Way | Closed |
| 17 | Recycling - City of Berkeley | Request | 37.878899 | -122.297101 | 1005 Camelia St | Closed |
| 18 | Roll Off Bin | Request | 37.853874 | -122.291220 | 2840 Eighth St | Closed |
| 19 | Encampment Complaint | Inquiry | 37.882101 | -122.302594 | 1102 Sixth St | Closed |
# what is the distribution of issue types?
df_geo['request_subcategory'].value_counts()
Miscellaneous 4 Request 4 Commercial 4 Residential 4 Clean City Program 2 Parking 1 Inquiry 1 Name: request_subcategory, dtype: int64
Finally, convert each row in the dataframe to a geojson-formatted feature and save the result as a file. The format is pretty simple and you can see it here: http://geojson.org/
def df_to_geojson(df, properties, lat='latitude', lon='longitude'):
# create a new python dict to contain our geojson data, using geojson format
geojson = {'type':'FeatureCollection', 'features':[]}
# loop through each row in the dataframe and convert each row to geojson format
for _, row in df.iterrows():
# create a feature template to fill in
feature = {'type':'Feature',
'properties':{},
'geometry':{'type':'Point',
'coordinates':[]}}
# fill in the coordinates
feature['geometry']['coordinates'] = [row[lon],row[lat]]
# for each column, get the value and add it as a new feature property
for prop in properties:
feature['properties'][prop] = row[prop]
# add this feature (aka, converted dataframe row) to the list of features inside our dict
geojson['features'].append(feature)
return geojson
cols = [
'street_address',
'request_detail',
'request_subcategory',
'case_status'
]
geojson = df_to_geojson(df_geo, cols)
"{'type': 'FeatureCollection', 'features': [{'type': 'Feature', 'properties': {'street_address': '1611 Sixty-Second St', 'request_detail': 'Blue or Red Zone Install', 'request_subcategory': 'Parking', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.27500398, 37.84668714]}}, {'type': 'Feature', 'properties': {'street_address': '1533 Harmon St', 'request_detail': 'Illegal Dumping - City Property', 'request_subcategory': 'Clean City Program', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.27659745, 37.84927276]}}, {'type': 'Feature', 'properties': {'street_address': '1235 Carrison St', 'request_detail': 'Residential Service Stop', 'request_subcategory': 'Residential', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.28482558, 37.8519337]}}, {'type': 'Feature', 'properties': {'street_address': '2515 Mathews St', 'request_detail': 'Residential Missed Pickup Integration', 'request_subcategory': 'Residential', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.28693591, 37.86095284]}}, {'type': 'Feature', 'properties': {'street_address': '2315 Durant Ave Parkg', 'request_detail': 'Miscellaneous Service Request', 'request_subcategory': 'Miscellaneous', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.26229585, 37.86767849]}}, {'type': 'Feature', 'properties': {'street_address': '2905 Shattuck Ave', 'request_detail': 'Miscellaneous Service Request', 'request_subcategory': 'Miscellaneous', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.26633357, 37.8564289]}}, {'type': 'Feature', 'properties': {'street_address': '3300 M L King Jr Way', 'request_detail': 'Illegal Dumping - City Property', 'request_subcategory': 'Clean City Program', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.27096909, 37.84816597]}}, {'type': 'Feature', 'properties': {'street_address': '3079 Bateman St', 'request_detail': 'Miscellaneous Internet Request', 'request_subcategory': 'Miscellaneous', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.25558886, 37.85382983]}}, {'type': 'Feature', 'properties': {'street_address': '2635 Ashby Ave', 'request_detail': 'Miscellaneous Internet Request', 'request_subcategory': 'Miscellaneous', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.25364902, 37.85722757]}}, {'type': 'Feature', 'properties': {'street_address': '2921 Fulton St', 'request_detail': 'Cart Repair', 'request_subcategory': 'Request', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.2632558, 37.85632234]}}, {'type': 'Feature', 'properties': {'street_address': '1530 Bancroft Way', 'request_detail': 'Recycling - City of Berkeley', 'request_subcategory': 'Request', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.28011788, 37.86586124]}}, {'type': 'Feature', 'properties': {'street_address': '1709 Alcatraz Ave', 'request_detail': 'Commercial Site Inspection', 'request_subcategory': 'Commercial', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.27327879, 37.84896425]}}, {'type': 'Feature', 'properties': {'street_address': '1744 Alcatraz Ave', 'request_detail': 'Residential Site Inspection', 'request_subcategory': 'Residential', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.27217761, 37.84846343]}}, {'type': 'Feature', 'properties': {'street_address': '1850 Fourth St', 'request_detail': 'Commercial Lost or Stolen Cart', 'request_subcategory': 'Commercial', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.30061859, 37.86903492]}}, {'type': 'Feature', 'properties': {'street_address': '1031 Bancroft Way', 'request_detail': 'Residential Bulky Pickup', 'request_subcategory': 'Residential', 'case_status': 'Open'}, 'geometry': {'type': 'Point', 'coordinates': [-122.29146406, 37.86468716]}}, {'type': 'Feature', 'properties': {'street_address': '1475 Shattuck Ave', 'request_detail': 'Commercial Reminder', 'request_subcategory': 'Commercial', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.26893005, 37.88071914]}}, {'type': 'Feature', 'properties': {'street_address': '3132 M L King Jr Way', 'request_detail': 'Commercial Reminder', 'request_subcategory': 'Commercial', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.27117109, 37.85114072]}}, {'type': 'Feature', 'properties': {'street_address': '1005 Camelia St', 'request_detail': 'Recycling - City of Berkeley', 'request_subcategory': 'Request', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.29710072, 37.87889918]}}, {'type': 'Feature', 'properties': {'street_address': '2840 Eighth St', 'request_detail': 'Roll Off Bin', 'request_subcategory': 'Request', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.29122018, 37.85387368]}}, {'type': 'Feature', 'properties': {'street_address': '1102 Sixth St', 'request_detail': 'Encampment Complaint', 'request_subcategory': 'Inquiry', 'case_status': 'Closed'}, 'geometry': {'type': 'Point', 'coordinates': [-122.30259371, 37.88210128]}}]}"
In nteract, we can display geojson directly with the built-in leaflet renderer.
import IPython
IPython.display.display({'application/geo+json': geojson}, raw=True)
Known temporary issue: leaflet fails to render