<jupyter_start><jupyter_text>Crime Analysis of Seattle Neighborhoods@infoxiaoMarch 2021  Load package and raw data<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
import matplotlib.ticker as mtick
import datetime
data_path = 'data/'
# SPD Crime Data: 2008-Present
# Accessed March 14, 2021
# https://data.seattle.gov/Public-Safety/SPD-Crime-Data-2008-Present/tazs-3rd5
crime_dataset = 'SPD_Crime_Data__2008-Present.csv'

# City Annual Stats
# https://data.seattle.gov/dataset/City-Annual-Stats/4as8-nphm
annual_stats_dataset = 'City_Annual_Stats.csv'

# An Urban Village Demographic Area Profile ACS 5-year 2013-2017
# https://data.seattle.gov/dataset/An-Urban-Village-Demographic-Area-Profile-ACS-5-ye/6zth-dtbf
population_dataset_2017 = 'An_Urban_Village_Demographic_Area_Profile_ACS_5-year_2013-2017.csv'

# Urban Village Demographic Area Profile ACS 5-year 2009-2013
# https://data.seattle.gov/dataset/Urban-Village-Demographic-Area-Profile-ACS-5-year-/napx-9dfy
population_dataset_2013 = 'Urban_Village_Demographic_Area_Profile_ACS_5-year_2009-2013.csv'

dfc = pd.read_csv(data_path + crime_dataset)
dfs = pd.read_csv(data_path + annual_stats_dataset)
dfp17 = pd.read_csv(data_path + population_dataset_2017)
dfp13 = pd.read_csv(data_path + population_dataset_2013)

len(dfc), len(dfs), len(dfp17), len(dfp13)<jupyter_output><empty_output><jupyter_text>City Annual Stats<jupyter_code># remove weird years: 1995 and years with na data

dfs = dfs[(dfs.Year_ !=2035) & 
          (dfs.Year_ !=1995) & 
          (dfs.Year_ !=2020)].reset_index(drop=True)

len(dfs)
dfs.head()
# calculate yoy growth up to 2019
dfs['Total_Population_pct_change'] = dfs['Total_Population'].pct_change()
dfs['Housing_Units_pct_change'] = dfs['Housing_Units'].pct_change()
dfs['Total_Jobs_pct_change'] = dfs['Total_Jobs'].pct_change()
# plot overall stats growth
plt.rcParams["figure.figsize"] = (10,5)
ax = dfs.plot(x='Year_Display', y=[
    'Total_Population_pct_change', 'Housing_Units_pct_change', 'Total_Jobs_pct_change'], kind="line")
plt.xticks(np.arange(2000, 2020, step=2))
# Put a legend to the right of the current axis
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5))
ax.yaxis.set_major_formatter(mtick.PercentFormatter(1.0))
plt.ylabel('YoY Change')
plt.xlabel('Year')
plt.title('Seattle Annual Stats\n')
sns.set_context("paper", font_scale=2)    
# dfs[['Year_Display', 'Housing_Units_pct_change']]
# Total population from 2013 to 2019 grew by 20%
dfs.loc[dfs['Year_Display'] == 2019].Total_Population.item() \
/ dfs.loc[dfs['Year_Display'] == 2013].Total_Population.item()
# Housing units grew by 16% in the same time period
# (not much lower than population growth than I imagined actually)
dfs.loc[dfs['Year_Display'] == 2019].Housing_Units.item() \
/ dfs.loc[dfs['Year_Display'] == 2013].Housing_Units.item()<jupyter_output><empty_output><jupyter_text>Population by Neighborhood<jupyter_code>pcols = ['UV_AREA', 'TOTAL_POPULATION', 'MEDIAN_AGE']
dfp = dfp17[pcols].merge(dfp13[pcols], 
                         on='UV_AREA', 
                         suffixes=('_17', '_13'))
dfp.head()
dfp['TOTAL_POPULATION_GROWTH_17_13'] = \
    dfp.TOTAL_POPULATION_17 / dfp.TOTAL_POPULATION_13 - 1
dfp = dfp.sort_values(by='TOTAL_POPULATION_GROWTH_17_13')
plt.rcParams["figure.figsize"] = (6,10)
ax = dfp.plot(x='UV_AREA', y=[
    'TOTAL_POPULATION_GROWTH_17_13'], kind='barh')
ax.legend(loc='upper center', bbox_to_anchor=(1, -0.05))
ax.xaxis.set_major_formatter(mtick.PercentFormatter(1.0))
plt.title('Seattle Population Growth 2017 v.s. 2013\n')
plt.ylabel('Neighborhood')
dfp['MEDIAN_AGE_17_13'] = \
    dfp.MEDIAN_AGE_17 - dfp.MEDIAN_AGE_13
dfp = dfp.sort_values(by='MEDIAN_AGE_17_13', ascending=False)
plt.rcParams["figure.figsize"] = (6,10)
ax = dfp.plot(x='UV_AREA', y=[
    'MEDIAN_AGE_17_13'], kind='barh')
ax.legend(loc='upper center', bbox_to_anchor=(1, -0.05))
plt.title('Seattle Median Age Change 2017 v.s. 2013\n')
plt.ylabel('Neighborhood')<jupyter_output><empty_output><jupyter_text>Crime Rate Analysis  Overall Count<jupyter_code>len(dfc)
## correct a typo in the official dataset
#  "DOWNTOWN COMMERICAL" -> "DOWNTOWN COMMERCIAL"
dfc.replace(to_replace='DOWNTOWN COMMERICAL', value='DOWNTOWN COMMERCIAL', inplace=True)
len(dfc[dfc.MCPP == 'DOWNTOWN COMMERCIAL'])
# dfp.UV_AREA
# dfc.MCPP.value_counts()
date_time_format = '%m/%d/%Y %H:%M:%S %p'
# missing offense start time
dfc['Offense Start DateTime'].isnull().sum()
dfc = dfc[~dfc['Offense Start DateTime'].isnull()].reset_index(drop=True)
# throw out 'FRAUD OFFENSES', which has weird reporting time spikes
len(dfc[dfc['Offense Parent Group'] == 'FRAUD OFFENSES'])/len(dfc)
dfc = dfc[dfc['Offense Parent Group'] != 'FRAUD OFFENSES'].reset_index(drop=True)
dfc['time'] = dfc['Report DateTime'].apply(
    lambda x : datetime.datetime.strptime(str(x), date_time_format))
dfc['year'] = dfc.time.apply(lambda x: x.year)
dfc['date'] = dfc.time.apply(lambda x: str(x.date()))
dfc['month'] = dfc.time.apply(lambda x: str(x.date())[:-3])
dfc.head()
dfc.Offense.value_counts()
dfc[dfc.year==2021].month.value_counts()
# exclude March data because the month is not complete
dfc = dfc[dfc.month != '2021-03'].reset_index(drop=True)
dfc = dfc[dfc['Crime Against Category'] != 'NOT_A_CRIME'].reset_index(drop=True)
dfc_flat = dfc.groupby('Crime Against Category').month.value_counts().unstack().T.reset_index().set_index('month').stack().reset_index()
dfc_flat = dfc_flat.rename(columns={0: 'crime_count'})
plt.figure(figsize=(10,5))
sns.set_style("white")
plt.xticks(rotation=45)
sns.set(font_scale = 1)
plot_= sns.lineplot(data=dfc_flat, x='month', y='crime_count', hue='Crime Against Category')
for ind, label in enumerate(plot_.get_xticklabels()):
    if ind % 6 == 0:  # every x label is kept
        label.set_visible(True)
    else:
        label.set_visible(False)
plt.xlabel('Month')        
plt.ylabel('SPD Crime Count')
plt.title('Seattle Overall Crime Count by Month')
plot_.legend(loc='upper center', bbox_to_anchor=(1.15, 0.5), framealpha=0)<jupyter_output><empty_output><jupyter_text>Neighborhood Deep Dives<jupyter_code>years_of_interest = [2019, 2020, 2021]
print(len(dfc[dfc.year.isin(years_of_interest)]))
dfc = dfc[dfc.year.isin(years_of_interest)].reset_index(drop=True)
# dfc[dfc.year == 2020].MCPP.value_counts()
tmp = dfc.groupby(['MCPP', 'year'])['Report Number'].count().pct_change().reset_index()
tmp = tmp.rename(columns={'Report Number': 'YoY'})
tmp = tmp[tmp.year == 2020].reset_index(drop=True).sort_values(by='YoY')
tmp
# narrow down the neighborhood and year of interest
neighborhoods_of_interest = [
    'DOWNTOWN COMMERCIAL',
    'BELLTOWN',
    'CAPITOL HILL',
    'UNIVERSITY',
    'WALLINGFORD', 
    'FREMONT',
    'BALLARD SOUTH',  
    'BALLARD NORTH',
    'QUEEN ANNE',
]
print(len(dfc[dfc.MCPP.isin(neighborhoods_of_interest)]))
dfc = dfc[dfc.MCPP.isin(neighborhoods_of_interest)].reset_index(drop=True)
# stack and flatten dataframe for crime count per month per neighborhood
dfcn = dfc.groupby(['MCPP', 'Crime Against Category']).month.value_counts(
    ).unstack().reset_index()
dfcn = dfcn.fillna(0)
dfcn = dfcn.set_index(
    ['MCPP', 'Crime Against Category']).stack().reset_index()
dfcn = dfcn.rename(columns={0: 'crime_count'})
dfcn = dfcn[~dfcn.crime_count.isnull()].reset_index(drop=True)
# plot total crime by neighborhood
sns.set_style('white')
g = sns.FacetGrid(dfcn.groupby(['month','MCPP']).crime_count.sum().unstack().reset_index().set_index('month').stack().reset_index().rename(columns={0: 'crime_count'}), col="MCPP", col_wrap=5, height=5)
g = g.map_dataframe(sns.lineplot, "month", "crime_count")
plt.xticks([0,6,12,18,24])
g.add_legend()
# plot crime by type by neighborhood
sns.set(font_scale = 1.1)
sns.set_style('white')
g = sns.FacetGrid(dfcn, col="MCPP", col_wrap=3, height=4)
g = g.map_dataframe(sns.lineplot, "month", "crime_count", hue='Crime Against Category')
plt.xticks([0,6,12,18,24])
g.add_legend()<jupyter_output><empty_output><jupyter_text>YoY<jupyter_code>def process_dfc_flat(dfc):
    dfc_covid = dfc[dfc.year >= 2019].reset_index(drop=True)
    dfc_covid_flat = dfc_covid.groupby('Crime Against Category').month.value_counts().unstack().T.reset_index().set_index('month').stack().reset_index()
    dfc_covid_flat = dfc_covid_flat.rename(columns={0: 'crime_count'})
    dfc_covid_flat.sort_values(by=['month', 'Crime Against Category'])
    dfc_covid_flat['year'] = dfc_covid_flat.month.apply(lambda x : int(x.split('-')[0]))
    dfc_covid_flat['cal_month'] = dfc_covid_flat.month.apply(lambda x : int(x.split('-')[1]))
    dfc_covid_flat = dfc_covid_flat.set_index('month')
    dfc_covid_flat['YoY'] = (dfc_covid_flat.crime_count/dfc_covid_flat.shift(12*3).crime_count) - 1
    dfc_covid_flat = dfc_covid_flat.reset_index()
    return dfc_covid_flat
def plot_overall_crime(MCPP):
    if not MCPP:
        dfc_covid_flat = process_dfc_flat(dfc)
    else:
        dfc_covid_flat = process_dfc_flat(dfc[dfc.MCPP == MCPP].reset_index(drop=True))    
    plt.figure(figsize=(10,5))
    sns.set_style("white")
    plt.xticks(rotation=45)
    sns.set(font_scale = 1)
    plot_= sns.lineplot(data=dfc_covid_flat, 
                        x='month', y='crime_count', hue='Crime Against Category')
    for ind, label in enumerate(plot_.get_xticklabels()):
        if ind % 1 == 0:  # every x label is kept
            label.set_visible(True)
        else:
            label.set_visible(False)
    plt.xlabel('Month')        
    plt.ylabel('SPD Crime Count')
    if not MCPP:
        MCPP_format = ''
    else:
        MCPP_format = MCPP    
    plt.title('Seattle Overall Crime Count by Month {}'.format(MCPP_format))
    plot_.legend(loc='upper center', bbox_to_anchor=(1.15, 0.5), framealpha=0)
    return dfc_covid_flat
def plot_yoy(MCPP=None):
    if not MCPP:
        dfc_covid_flat = process_dfc_flat(dfc)
    else:
        dfc_covid_flat = process_dfc_flat(dfc[dfc.MCPP == MCPP].reset_index(drop=True))
    plt.figure(figsize=(10,5))
    sns.set_style("white")
    plt.xticks(rotation=45)
    sns.set(font_scale = 1)
    plot_= sns.barplot(data=dfc_covid_flat[dfc_covid_flat.year > 2019], 
                        x='month', y='YoY', hue='Crime Against Category')
    for ind, label in enumerate(plot_.get_xticklabels()):
        if ind % 1 == 0:  # every x label is kept
            label.set_visible(True)
        else:
            label.set_visible(False)
    plt.xlabel('Month')        
    plt.ylabel('SPD Crime Count')
    plt.title('Seattle Overall Crime Count by Month')
    plot_.legend(loc='upper center', bbox_to_anchor=(1.15, 0.5), framealpha=0)
    plot_.yaxis.set_major_formatter(mtick.PercentFormatter(1.0))
    plt.ylabel('YoY Change')
    plt.xlabel('Year')
    if not MCPP:
        MCPP_format = ''
    else:
        MCPP_format = MCPP
    plt.title('SPD Crime YoY Change {}\n'.format(MCPP_format))
    sns.set_context("paper", font_scale=2)
    return dfc_covid_flat
# MCPP = 'CAPITOL HILL'
MCPP = 'DOWNTOWN COMMERCIAL'
# MCPP = 'QUEEN ANNE'
# MCPP = 'FREMONT'
# MCPP = None
plot_overall_crime(MCPP)
dfc_covid_flat = plot_yoy(MCPP)
print(len(dfc_covid_flat))
def print_dfc_covid_flat_stats(dfc_covid_flat):
    print('Overall Crime')
    print(dfc_covid_flat.groupby('year').crime_count.sum().pct_change())
    
    print('Property')
    print(dfc_covid_flat[
    dfc_covid_flat['Crime Against Category'] == 'PROPERTY'
        ].groupby('year').crime_count.sum().pct_change())
    
    print('Person')
    print(dfc_covid_flat[
    dfc_covid_flat['Crime Against Category'] == 'PERSON'
        ].groupby('year').crime_count.sum().pct_change())
    
    print('Society')
    print(dfc_covid_flat[
    dfc_covid_flat['Crime Against Category'] == 'SOCIETY'
        ].groupby('year').crime_count.sum().pct_change())
    
#     print('Murder')
#     print(dfc[
#     (dfc['Crime Against Category'] == 'PERSON') &
#     (dfc['Offense'] == 'Murder & Nonnegligent Manslaughter')
#         ].year.value_counts())
print_dfc_covid_flat_stats(dfc_covid_flat)
crimes = dfc[(dfc.MCPP == MCPP) 
    & (dfc.year != 2021) 
#     & (dfc['Crime Against Category'] == 'PROPERTY')
].groupby(['year', 'Offense'])['Report Number'].count().reset_index()
crimes = crimes.rename(columns={'Report Number': 'cnt'})
left = crimes[crimes['year'] == 2019].set_index('Offense')
right = crimes[crimes['year'] == 2020].set_index('Offense')
merged = left.join(right, lsuffix='_2019', rsuffix='_2020')
merged['YoY'] = merged.cnt_2020/merged.cnt_2019 - 1
merged.sort_values(by='YoY', ascending=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15576892556737026, 'fraction_numerical': 0.029757181399777812, 'mean_word_length': 5.100193610842207, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 28, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '112024', 'license': 'MIT', 'n_tokens_mistral': 5176, 'n_tokens_neox': 4797, 'n_words': 790, 'path': 'seattle/crime_seattle.ipynb', 'repo_name': 'infoxiao/crime', 'size': '12602'}