A few random Pandas commands from the SFPD.CSV File¶
In [1]:
import pandas as pd
In [2]:
# read in the csv file that was created in the Python Example
sfpd = pd.read_csv('sfpd.csv', sep='\t')
In [3]:
# review size and shape
sfpd.shape
Out[3]:
In [4]:
# obtain summary statistics
sfpd.groupby(['category']).size()
Out[4]:
In [5]:
# show column names
sfpd.columns
Out[5]:
In [6]:
# Create variale to filter df
family = sfpd['category'] == 'FAMILY OFFENSES'
In [7]:
# Create variale to filter df
suicide= sfpd['category'] == 'SUICIDE'
In [8]:
# Create DF
family_db = sfpd[family]
In [9]:
# Create DF
suicide_db = sfpd[suicide]
In [10]:
# Drop columns
ColList = ['descript', 'dayofweek','location', 'resolution','Unnamed: 0','unique_key','pdid','timestamp']
family_db.drop(ColList, axis = 1, inplace = True)
In [11]:
ColList = ['descript', 'dayofweek','location', 'resolution','Unnamed: 0','unique_key','pdid','timestamp']
suicide_db.drop(ColList, axis = 1, inplace = True)
In [12]:
# Append the two DF's together
sf_db = suicide_db.append(family_db)
In [22]:
sf_db.to_csv("../sf_db.csv", index=False)
In [31]:
# How many times did Loitering take place on Monday's
df = len(sfpd.groupby(['category','dayofweek']).groups['LOITERING','Sunday'])
df
Out[31]:
In [32]:
# What are the top three categories sorted by count of occurences
df = sfpd.groupby(['category','dayofweek'])['unique_key'].count().sort_values(ascending=False)
df[:21]
Out[32]:
In [33]:
# filter the data to Larceny and sort it by day of week
df = sfpd[sfpd['category']=='LARCENY/THEFT'].sort_values(by='dayofweek')
df.head()
Out[33]:
In [34]:
# what are all the crimes that occured on Monday or Friday
df = sfpd[(sfpd['dayofweek'] =='Monday') | (sfpd['dayofweek'] == 'Friday')]
df.head()
Out[34]:
In [35]:
# count the number of occurences per crime per district
df = sfpd.groupby(['category','dayofweek','pddistrict'])['dayofweek'].count().unstack(0)
df.head()
Out[35]:
In [36]:
from mapboxgl.utils import create_color_stops, df_to_geojson
from mapboxgl.viz import CircleViz
In [37]:
category_color_stops = [['SUICIDE', 'rgb(211,47,47)'],
['FAMILY OFFENSES', 'rgb(34,139,34)']]
In [39]:
df_to_geojson(sf_db,
filename='markers.geojson',
lat='latitude', lon='longitude', precision=3)
token= "pk.Removed for Privacy "
viz = CircleViz('markers.geojson', access_token=token,
radius=2, center = (sf_db.longitude.mean(), sf_db.latitude.mean()),
zoom=12,
style='mapbox://styles/mapbox/streets-v11',
color_property='category',
color_function_type='match',
color_stops=category_color_stops,
label_color= 'blue',
color_default = 'red',
stroke_color = 'black')
viz.show()
MapBox Map Image From Code Above¶
