import requests
import matplotlib.pyplot as py_plot
import pandas as pd
from IPython.display import display, HTML
from pyproj import Proj, transform
import folium
from folium import plugins
import math
#url from api explorer tab in open data platform
api_url = 'https://services5.arcgis.com/54falWtcpty3V47Z/arcgis/rest/services/cad_calls_year3/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json'
#function to return a list of column headers and rows of data from the json api call
def opendata_api(api_call):
n = 0
#build empty list for all data
all_data = []
#get a count of total records in api request
call_count = requests.get(api_call,"&returnCountOnly=true").json()
count = call_count['count']
#calculate number of requests to make due to 1000 max record count
loop_count = math.ceil(count/1000)
for count in range(loop_count):
call_json = requests.get(api_call,'&resultOffset='+str(n)).json()
#build a list of dictionaries by getting the values of the features key
calls_feats = call_json["features"]
#loop to get the values of the attributes dictionary
for line in calls_feats:
all_data.append(line["attributes"])
n+=1000
#print("Number of requests is ", count)
#return the keys of the first dictionary get column names
fields = list(all_data[0].keys())
#a list comprehension to populate a list of attributes into the list we are building called rows
rows = [list(item.values()) for item in all_data]
return rows,fields
make_request = opendata_api(api_url)
data = make_request[0]
columns = make_request[1]
print(columns)
['OBJECTID', 'Record_ID', 'Call_Type', 'Description', 'Reporting_Officer', 'Unit_ID', 'Report_Created', 'Location', 'Police_District', 'Beat', 'Grid', 'X_Coordinate', 'Y_Coordinate', 'Day_of_Week', 'Occurence_Date', 'Occurence_Time', 'Received_Date', 'Received_Time', 'Dispatch_Date', 'Dispatch_Time', 'Enroute_Date', 'Enroute_Time', 'At_Scene_Date', 'At_Scene_Time', 'Clear_Date', 'Clear_Time']
#print the first 5 records of data
print(data[0:5])
[[1, '6360891', '415E', 'DISTURBANCE-NOISE', '0883', '2C14', 'N', '4101 INNOVATOR DR', '1', '1A', '0336', 6700606, 1996674, 'Sun', 1483228800000, '18:40:50', 1483228800000, '18:40:50', 1483228800000, '18:49:57', 1483228800000, '18:50:12', 1483228800000, '18:57:57', 1483228800000, '19:06:44'], [2, '6360930', '927P', 'SUSPICIOUS SUBJECT/CIRCUMSTANCE-IN PROGRESS', '0694', '1C12', 'N', '2941 ADVANTAGE WAY', '1', '1A', '0313', 6696606, 2000630, 'Sun', 1483228800000, '19:45:17', 1483228800000, '19:45:17', 1483228800000, '19:45:17', 1483228800000, '19:45:17', 1483228800000, '19:45:17', 1483228800000, '19:47:44'], [3, '6361374', 'TSTOP', 'TRAFFIC STOP', '552', 'XRY12', 'N', '2ND ST / NEASHAM CIR', '3', '3M', '0751', 6703083, 1973628, 'Sun', 1483228800000, '19:14:48', 1483228800000, '19:14:48', 1483228800000, '19:14:48', 1483228800000, '19:14:48', 1483228800000, '19:14:48', 1483228800000, '19:20:08'], [4, '6361293', 'AU', 'ALL UNITS BROADCAST', '', '', 'N', '636 TENAYA AVE', '1', '1C', '0418', 6710884, 1988977, 'Sun', 1483228800000, '00:02:11', 1483228800000, '00:02:11', -2208988800000, ' ', -2208988800000, ' ', -2208988800000, ' ', 1483228800000, '00:13:48'], [5, '6360110', '927S', 'SHOTS FIRED - LESS THAN 15 AGO', '', '', 'N', '635 KESNER AVE', '2', '2A', '0512', 6718588, 1993201, 'Sun', 1483228800000, '00:00:34', 1483228800000, '00:00:34', -2208988800000, ' ', -2208988800000, ' ', -2208988800000, ' ', 1483228800000, '00:01:25']]
#building a pandas dataframe called "calls"
calls = pd.DataFrame.from_records(data, columns=columns)
#returns the counts for each unique value in the "Description" field
display(calls['Description'].value_counts())
DISTURBANCE-CLARIFY 33343 TRAFFIC STOP 23475 SUSPICIOUS SUBJECT/CIRCUMSTANCE-IN PROGRESS 17511 ALL UNITS BROADCAST 17369 SUSPICIOUS VEHICLE-OCCUPIED 14087 WELFARE CHECK 13259 SUBJECT STOP 11366 INCOMPLETE CALL FOR POLICE 10333 ALARM-SECURE NO EVID OF CRIME 7108 MISDEMEANOR ASSAULT-IN PROGRESS 5439 REPORT NUMBER ASSIGNMENT 5316 ERRAND 5078 ALARM-CANCEL 4721 VEHICLE ACCIDENT-NO OR UNKNOWN INJURIES 4289 BUSINESS CHECK 3869 CHECK ON HAZARD 3850 DISTURBANCE-FAMILY 3841 ALARM-ACCIDENTAL 3767 PARK CHECK 3622 ALARM ADVISED-FULL RESPONSE 3232 VEHICLE ACCIDENT-INJURIES 3226 MEDICAL AID-CLARIFY IN TEXT 2874 STOLEN VEHICLE-REPORT 2837 SHOTS FIRED - LESS THAN 15 AGO 2705 SUPPLEMENTAL EMPLOYMENT 2374 DISTURBANCE-NOISE 2311 SUSPICIOUS VEHICLE-UNOCCUPIED 2150 DOMESTIC VIOLENCE-IN PROGRESS 2123 DISTURBANCE-WEAPON 1764 ATTEMPT PICKUP/WARRANT SERVICE 1757 ... CARJACKING-REPORT 20 SHOOTING INTO UNOCCUPIED VEHICLE-LESS THAN 15 AGO 20 LOCAL GOVERNMENT-FLOODING 20 CAR CLOUT-CSI 20 GRAND THEFT-REPORT REFERRAL 17 GRAND THEFT-IN PROGRESS 16 NARCOTIC ACTIVITY-REPORT 16 BOMB THREAT 16 GRAND THEFT-LESS THAN 5 AGO 12 MEDICAL AID-SUSPECT ON SCENE 12 DEAD BODY-CSI 11 MISDEMEANOR HIT & RUN-CSI 10 GRAND THEFT-CSI 7 KIDNAPPING-CSI 7 DIRECTED PATROL-AVAILABLE 6 DIRECTED PATROL-PRIORITY 1,2,3 6 CARJACKING-IN PROGRESS 6 CAL ID 5 ARSON-CSI 4 HOMICIDE 4 POSSESSION OF STOLEN PROPERTY-CSI 3 DIRE EMERGENCY 3 NARCOTIC ACTIVITY-CSI 2 EVIDENCE LAB 2 REFER TO OUTSIDE AGENCY 2 EXPLOSIVE DEVICE-CSI 2 PETTY THEFT-CSI 2 DIRECTED PATROL-POP-NOT AVAIL 1 SHOOTING INTO INHABITED DWELLING-IN PROGRESS 1 CARJACKING-LESS THAN 15 AGO-INJURIES 1 Name: Description, dtype: int64
#describe method on the "description" field
calls['Description'].describe()
count 267065 unique 189 top DISTURBANCE-CLARIFY freq 33343 Name: Description, dtype: object
#using the display() function to view calls dataframe as a table
display(calls[0:5])
OBJECTID | Record_ID | Call_Type | Description | Reporting_Officer | Unit_ID | Report_Created | Location | Police_District | Beat | ... | Received_Date | Received_Time | Dispatch_Date | Dispatch_Time | Enroute_Date | Enroute_Time | At_Scene_Date | At_Scene_Time | Clear_Date | Clear_Time | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 6360891 | 415E | DISTURBANCE-NOISE | 0883 | 2C14 | N | 4101 INNOVATOR DR | 1 | 1A | ... | 1483228800000 | 18:40:50 | 1483228800000 | 18:49:57 | 1483228800000 | 18:50:12 | 1483228800000 | 18:57:57 | 1483228800000 | 19:06:44 |
1 | 2 | 6360930 | 927P | SUSPICIOUS SUBJECT/CIRCUMSTANCE-IN PROGRESS | 0694 | 1C12 | N | 2941 ADVANTAGE WAY | 1 | 1A | ... | 1483228800000 | 19:45:17 | 1483228800000 | 19:45:17 | 1483228800000 | 19:45:17 | 1483228800000 | 19:45:17 | 1483228800000 | 19:47:44 |
2 | 3 | 6361374 | TSTOP | TRAFFIC STOP | 552 | XRY12 | N | 2ND ST / NEASHAM CIR | 3 | 3M | ... | 1483228800000 | 19:14:48 | 1483228800000 | 19:14:48 | 1483228800000 | 19:14:48 | 1483228800000 | 19:14:48 | 1483228800000 | 19:20:08 |
3 | 4 | 6361293 | AU | ALL UNITS BROADCAST | N | 636 TENAYA AVE | 1 | 1C | ... | 1483228800000 | 00:02:11 | -2208988800000 | -2208988800000 | -2208988800000 | 1483228800000 | 00:13:48 | |||||
4 | 5 | 6360110 | 927S | SHOTS FIRED - LESS THAN 15 AGO | N | 635 KESNER AVE | 2 | 2A | ... | 1483228800000 | 00:00:34 | -2208988800000 | -2208988800000 | -2208988800000 | 1483228800000 | 00:01:25 |
5 rows × 26 columns
#count of incidents by day of week
calls['Day_of_Week'].value_counts()
Fri 40600 Wed 39900 Thu 38994 Tue 38194 Sat 37713 Mon 36651 Sun 35013 Name: Day_of_Week, dtype: int64
#convert x,y coords to float data type if they are string or int
def parse_float(x):
try:
x = float(x)
except Exception:
x = 0
return x
calls['X_Coordinate'] = calls['X_Coordinate'].apply(parse_float)
calls['Y_Coordinate'] = calls['Y_Coordinate'].apply(parse_float)
#create a variable that contains only accident Vehicle Reports from "Description" field
accident = calls[calls['Description'] == 'VEHICLE ACCIDENT-INJURIES']
display(accident[0:5])
OBJECTID | Record_ID | Call_Type | Description | Reporting_Officer | Unit_ID | Report_Created | Location | Police_District | Beat | ... | Received_Date | Received_Time | Dispatch_Date | Dispatch_Time | Enroute_Date | Enroute_Time | At_Scene_Date | At_Scene_Time | Clear_Date | Clear_Time | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
424 | 425 | 6360868 | 901A | VEHICLE ACCIDENT-INJURIES | N | GRAND AVE / I 80 EB | 2 | 2A | ... | 1483228800000 | 18:35:58 | -2208988800000 | -2208988800000 | -2208988800000 | 1483228800000 | 18:39:50 | |||||
730 | 731 | 6361248 | 901A | VEHICLE ACCIDENT-INJURIES | 811 | IV94 | N | I 80 E / HWY 65 ON N | UI | UI | ... | 1483315200000 | 05:45:35 | 1483315200000 | 05:47:10 | 1483315200000 | 05:47:10 | 1483315200000 | 05:47:10 | 1483315200000 | 06:04:08 |
837 | 838 | 6361853 | 901A | VEHICLE ACCIDENT-INJURIES | N | 3500 FLORIN RD | 5 | 5B | ... | 1483315200000 | 18:10:40 | -2208988800000 | -2208988800000 | -2208988800000 | 1483315200000 | 18:16:30 | |||||
846 | 847 | 6361996 | 901A | VEHICLE ACCIDENT-INJURIES | 0954 | 2C57 | N | CENTER PKWY / MACK RD | 5 | 5C | ... | 1483315200000 | 20:14:11 | 1483315200000 | 20:15:15 | 1483315200000 | 20:17:12 | 1483315200000 | 20:31:09 | 1483315200000 | 21:15:18 |
1385 | 1386 | 6362865 | 901A | VEHICLE ACCIDENT-INJURIES | N | RIO LINDA BLVD / RIVERA DR | 2 | 2B | ... | 1483401600000 | 17:25:10 | -2208988800000 | -2208988800000 | -2208988800000 | 1483401600000 | 17:25:51 |
5 rows × 26 columns
#using matplotlib magic to plot a bar chart of incidents by day of the week
%matplotlib notebook
calls['Day_of_Week'].value_counts().plot(kind='bar')
<matplotlib.axes._subplots.AxesSubplot at 0x7fa63597e4e0>
#using the pyproj library to reproject x,y coordinates from state plane zone II to WGS84 lat/long
inProj = Proj(init='epsg:2226', preserve_units = True)
outProj = Proj(init='epsg:4326')
x1,y1 = 6722570,1992314
x2,y2 = transform(inProj,outProj,x1,y1)
print(x2,y2)
-121.43672656313065 38.63153998649104
calls_map = folium.Map(location=[38.58, -121.5], tiles=("https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/256/{z}/{x}/{y}?access_token=pk.eyJ1IjoiZGFyYW9iZWlybmUiLCJhIjoidlV2WXZKRSJ9.EwVOY6b8UeyIz3N2UAu5pw"),
zoom_start=11,
attr='Mapbox attribution')
marker_cluster = folium.MarkerCluster().add_to(calls_map)
coords_list = []
#to save on memory/time we only call the first 1,000 records to map
for name, row in accident.iloc[:1000].iterrows():
x1, y1 =row['X_Coordinate'],row['Y_Coordinate']
#converts each set coordinates from state plane to lat/long
long, lat = transform(inProj,outProj,x1,y1)
coords = (lat,long)
#add the set of coordinates to the list
coords_list.append(coords)
folium.Marker([lat, long], popup="Day of the week was " + row['Day_of_Week']).add_to(marker_cluster)
#saves callsnew.html in the working directory
calls_map.save('callsnew.html')
calls_map
calls_heatmap = folium.Map(location=[38.58, -121.5], tiles='Stamen Toner', zoom_start=13)
#create a heatmap using the same data as above
calls_heatmap.add_child(plugins.HeatMap([[item[0], item[1]] for item in coords_list]))
calls_heatmap.save("heatmap.html")
calls_heatmap
stamen_watercolor = folium.Map(location=[38.58, -121.5], tiles='Stamen WaterColor', zoom_start=13)
stamen_watercolor.save("watermap.html")
stamen_watercolor