UCLA-DSI-Capstone.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UCLA Extension Data Science Intensive 2022 Q4 Fall\n",
    "Instructor: William Yu\n",
    "\n",
    "Project: Capstone\n",
    "\n",
    "Author: Patrick McBride"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!/usr/bin/env python\n",
    "\n",
    "# !pip3 install pandas\n",
    "# !pip3 install sodapy\n",
    "# !pip3 install autocensus\n",
    "\n",
    "# Install Packages\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sodapy import Socrata   # https://dev.socrata.com/consumers/getting-started.html\n",
    "from autocensus import Query # https://pypi.org/project/autocensus/0.2.1/\n",
    "\n",
    "# Set working directory\n",
    "os.chdir('/Users/patrick/Desktop/2022 Q4 Fall MSBA/UCLA/data/')  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Source Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Downloads can take up to 10 minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Data - Police Department Incident Reports: 2018 to Present\n",
    "# \n",
    "\n",
    "# https://data.sfgov.org/Public-Safety/Police-Department-Incident-Reports-2018-to-Present/wg3w-h783\n",
    "\n",
    "# Unauthenticated client only works with public data sets. Note 'None'\n",
    "# in place of application token, and no username or password:\n",
    "client = Socrata(\"data.sfgov.org\", None)\n",
    "\n",
    "# Example authenticated client (needed for non-public datasets):\n",
    "# client = Socrata(data.sfgov.org,\n",
    "#                  MyAppToken,\n",
    "#                  username=\"user@example.com\",\n",
    "#                  password=\"AFakePassword\")\n",
    "\n",
    "# First 2000 results, returned as JSON from API / converted to Python list of\n",
    "# dictionaries by sodapy.\n",
    "# results = client.get(\"wg3w-h783\", limit=2000)\n",
    "results = client.get_all(\"wg3w-h783\")\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "sfcrime = pd.DataFrame.from_records(results)\n",
    "\n",
    "# Rename Neighborhood\n",
    "sfcrime = sfcrime.rename(columns = {'analysis_neighborhood' : 'nhood'})\n",
    "\n",
    "# Save locally\n",
    "sfcrime.to_csv('sfcrime.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Data - San Francisco Analysis Neighborhoods\n",
    "# \n",
    "\n",
    "# https://data.sfgov.org/dataset/San-Francisco-Analysis-Neighborhoods/xfcw-9evu\n",
    "\n",
    "client = Socrata(\"data.sfgov.org\", None)\n",
    "\n",
    "# Download data\n",
    "results = client.get_all(\"xfcw-9evu\")\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "sfnhood = pd.DataFrame.from_records(results)\n",
    "\n",
    "# Save locally\n",
    "sfnhood.to_csv('sfnhood.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Data - Analysis Neighborhoods - 2020 census tracts assigned to neighborhoods\n",
    "# \n",
    "\n",
    "# Mapping of Census ACS Tract to SF Police Analysis Neighborhoods\n",
    "\n",
    "# https://data.sfgov.org/Geographic-Locations-and-Boundaries/Analysis-Neighborhoods-2020-census-tracts-assigned/sevw-6tgi\n",
    "\n",
    "client = Socrata(\"data.sfgov.org\", None)\n",
    "\n",
    "# Download data\n",
    "results = client.get_all(\"sevw-6tgi\")\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "sfnhood_tract = pd.DataFrame.from_records(results)\n",
    "\n",
    "# Rename Neighborhood Column\n",
    "sfnhood_tract = sfnhood_tract.rename(columns = {'neighborhoods_analysis_boundaries' : 'nhood'})\n",
    "\n",
    "# Convert column data type\n",
    "sfnhood_tract['tractce'] = sfnhood_tract['tractce'].astype(np.int64)\n",
    "\n",
    "# Save locally\n",
    "sfnhood_tract.to_csv('sfnhood_tract.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# US Census American Community Survey (ACS)\n",
    "#\n",
    "\n",
    "# San Francisco County demographic data by US Census Tract\n",
    "\n",
    "#\n",
    "# State (06): California\n",
    "# County (075): San Francisco County\n",
    "#\n",
    "\n",
    "# Query ACS 5 Year 2020 Data\n",
    "query = Query(\n",
    "    estimate = 5,\n",
    "    years = [2020],\n",
    "    variables=[\n",
    "        \"DP02_0060PE\", # Less than 9th grade\n",
    "        \"DP02_0061PE\", # 9th to 12th grade, no diploma\n",
    "        \"DP02_0062PE\", # High school graduate (includes equivalency)\n",
    "        \"DP02_0063PE\", # Some college, no degree\n",
    "        \"DP02_0064PE\", # Associate's degree\n",
    "        \"DP02_0065PE\", # Bachelor's degree\n",
    "        \"DP02_0066PE\", # Graduate or professional degree\n",
    "        \"DP02_0067PE\", # High school graduate or higher\n",
    "        \"DP02_0068PE\", # Bachelor's degree or higher\n",
    "        \"DP03_0003PE\", # Percent!!EMPLOYMENT STATUS!!Population 16 years and over!!In labor force!!Civilian labor force\n",
    "        \"DP03_0009PE\", # Percent!!EMPLOYMENT STATUS!!Civilian labor force!!Unemployment Rate\n",
    "        \"DP03_0062E\",  # Median household income (dollars) Estimate\n",
    "        \"DP03_0119PE\", # Percentage of families and people whose income in the past 12 months is below poverty level\n",
    "        \"DP04_0037E\",  # Estimate!!ROOMS!!Total housing units!!Median rooms\n",
    "        \"DP04_0047PE\", # Percent!!HOUSING TENURE!!Occupied housing units!!Renter-occupied\n",
    "        \"DP04_0049E\",  # Estimate!!HOUSING TENURE!!Occupied housing units!!Average household size of renter-occupied unit\n",
    "        \"DP04_0134E\",  # Estimate!!GROSS RENT!!Occupied units paying rent!!Median (dollars)\n",
    "        \"DP04_0089E\",  # Estimate!!VALUE!!Owner-occupied units!!Median (dollars)\n",
    "        \"DP05_0001E\",  # Total population\n",
    "        \"DP05_0071PE\", # Hispanic or Latino (of any race)\n",
    "        \"DP05_0077PE\", # Not Hispanic or Latino; White alone\n",
    "        \"DP05_0078PE\", # Not Hispanic or Latino; Black or African American alone\n",
    "        \"DP05_0079PE\", # Not Hispanic or Latino; American Indian and Alaska Native alone\n",
    "        \"DP05_0080PE\", # Not Hispanic or Latino; Asian alone\n",
    "        \"DP05_0081PE\", # Not Hispanic or Latino; Native Hawaiian and Other Pacific Islander\n",
    "        \"DP05_0082PE\", # Not Hispanic or Latino; Some other race alone\n",
    "        \"DP05_0083PE\", # Not Hispanic or Latino; Two or more races\n",
    "        \"DP05_0005PE\", # Under 5 years\n",
    "        \"DP05_0006PE\", # 5 to 9 years\n",
    "        \"DP05_0007PE\", # 10 to 14 years\n",
    "        \"DP05_0008PE\", # 15 to 19 years\n",
    "        \"DP05_0009PE\", # 20 to 24 years\n",
    "        \"DP05_0010PE\", # 25 to 34 years\n",
    "        \"DP05_0011PE\", # 35 to 44 years\n",
    "        \"DP05_0012PE\", # 45 to 54 years\n",
    "        \"DP05_0013PE\", # 55 to 59 years\n",
    "        \"DP05_0014PE\", # 60 to 64 years\n",
    "        \"DP05_0015PE\", # 65 to 74 years\n",
    "        \"DP05_0016PE\", # 75 to 84 years\n",
    "        \"DP05_0017PE\", # 85 years and over\n",
    "        \"DP05_0018E\"], # Median age (years)\n",
    "    for_geo = ['tract:*'],\n",
    "    in_geo = ['state:06', 'county:075'],\n",
    "    # Fill in the following with your own Census API key; https://api.census.gov/data/key_signup.html\n",
    "    census_api_key = 'a792cb3572562c0f7d01d3eb0ecea17bf00f977c'\n",
    ")\n",
    "\n",
    "# Run query and collect output in dataframe\n",
    "sfacs5y2020 = query.run()\n",
    "\n",
    "# Parse State, County, and Tract Geography ID from geo_id for mapping with SF Neighborhoods\n",
    "sfacs5y2020['state_fp'] = sfacs5y2020['geo_id'].str[9:11]\n",
    "sfacs5y2020['county_fp'] = sfacs5y2020['geo_id'].str[11:14]\n",
    "sfacs5y2020['tractce'] = sfacs5y2020['geo_id'].str[14:len(sfacs5y2020['geo_id'])].astype(np.int64)\n",
    "\n",
    "# Save locally\n",
    "sfacs5y2020.to_csv('sfacs5y2020.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Data - Reference: Police Department Incident Code Crosswalk\n",
    "# \n",
    "\n",
    "#################################################################################\n",
    "# Not used!!!!                                                                  #\n",
    "#################################################################################\n",
    "\n",
    "# https://data.sfgov.org/Public-Safety/Reference-Police-Department-Incident-Code-Crosswal/ci9u-8awy\n",
    "\n",
    "# client = Socrata(\"data.sfgov.org\", None)\n",
    "\n",
    "# # Download data\n",
    "# results = client.get_all(\"ci9u-8awy\")\n",
    "# # Convert to pandas DataFrame\n",
    "# sfcrime_code = pd.DataFrame.from_records(results)\n",
    "\n",
    "# # Save locally\n",
    "# sfcrime_code.to_csv('sfcrime_code.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Data - Police Department Incident Reports: Historical 2003 to May 2018\n",
    "# \n",
    "\n",
    "#################################################################################\n",
    "# Not used!!!!                                                                  #\n",
    "#################################################################################\n",
    "\n",
    "# https://data.sfgov.org/Public-Safety/Police-Department-Incident-Reports-Historical-2003/tmnf-yvry\n",
    "\n",
    "# client = Socrata(\"data.sfgov.org\", None)\n",
    "\n",
    "# # Download data\n",
    "# results = client.get_all(\"tmnf-yvry\")\n",
    "\n",
    "# # Convert to pandas DataFrame\n",
    "# sfcrime2003 = pd.DataFrame.from_records(results)\n",
    "\n",
    "# # Save locally\n",
    "# sfcrime2003.to_csv('sfcrime2003.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# US Census Data Profile Codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# \n",
    "# US Census America Community Survey (ACS) Overview\n",
    "#\n",
    "\n",
    "# https://data.census.gov/table?tid=ACSDP5Y2020.DP05&g=0400000US06_0500000US06075\n",
    "\n",
    "# Topics currently included with autocensus are population, race, education, income, and housing.\n",
    "\n",
    "# query = Query(\n",
    "#     estimate=5,\n",
    "#     years=[2013, 2014, 2015, 2016, 2017],\n",
    "#     # Housing variables: B25035_001E, B25064_001E, B25077_001E\n",
    "#     variables=autocensus.topics.housing,\n",
    "#     for_geo='tract:*',\n",
    "#     in_geo=['state:06', 'county:075']\n",
    "# )\n",
    "\n",
    "\n",
    "#\n",
    "# DP02 - Social\n",
    "#\n",
    "# Education Attainment; Percentage Population 25 years and over\n",
    "\"DP02_0060PE\" : \"ed_below9\" # Less than 9th grade\n",
    "\"DP02_0061PE\" : \"ed_g912\" # 9th to 12th grade, no diploma\n",
    "\"DP02_0062PE\" : \"ed_hs\" # High school graduate (includes equivalency)\n",
    "\"DP02_0063PE\" : \"ed_scollege\" # Some college, no degree\n",
    "\"DP02_0064PE\" : \"ed_associate\" # Associate's degree\n",
    "\"DP02_0065PE\" : \"ed_bachelor\" # Bachelor's degree\n",
    "\"DP02_0066PE\" : \"ed_higher\" # Graduate or professional degree\n",
    "\"DP02_0067PE\" : \"ed_hs_higher\" # High school graduate or higher\n",
    "\"DP02_0068PE\" : \"ed_college_higher\" # Bachelor's degree or higher\n",
    "\n",
    "\n",
    "#\n",
    "# DP03 - Economic\n",
    "#\n",
    "\"DP03_0003PE\" : \"eco_clf\" # Percent!!EMPLOYMENT STATUS!!Population 16 years and over!!In labor force!!Civilian labor force\n",
    "\"DP03_0009PE\" : \"eco_unemp\" # Percent!!EMPLOYMENT STATUS!!Civilian labor force!!Unemployment Rate\n",
    "\"DP03_0062E\"  : \"eco_med_hincome\" # Median household income (dollars) Estimate\n",
    "\"DP03_0119PE\" : \"eco_poverty\"     # Percentage of families and people whose income in the past 12 months is below poverty level\n",
    "\"DP03_0032PE\" : \"eco_emp\" # Civilian employed population 16 years and over\n",
    "\n",
    "\n",
    "#\n",
    "# DP04 - Housing\n",
    "#\n",
    "\"DP04_0037E\" : \"h_med_rooms\"     # Estimate!!ROOMS!!Total housing units!!Median rooms\n",
    "\"DP04_0047PE\" : \"h_rental_units\" # Percent!!HOUSING TENURE!!Occupied housing units!!Renter-occupied\n",
    "\"DP04_0049E\" : \"h_rentel_size\"   # Estimate!!HOUSING TENURE!!Occupied housing units!!Average household size of renter-occupied unit\n",
    "\"DP04_0134E\" : \"h_med_rent\"      # Estimate!!GROSS RENT!!Occupied units paying rent!!Median (dollars)\n",
    "\"DP04_0089E\" : \"h_med_homeprice\" # Estimate!!VALUE!!Owner-occupied units!!Median (dollars)\n",
    "\n",
    "\n",
    "#\n",
    "# DP05 - Demographic\n",
    "#\n",
    "# https://api.census.gov/data/2020/acs/acs5/profile/groups/DP05.html\n",
    "\"DP05_0001E\"  : \"pop\"         # Total Population\n",
    "\"DP05_0018E\"  : \"mage\" # Median age (years)\n",
    "\n",
    "# Total Population; Race Percentage (Tally 100%)\n",
    "\"DP05_0037PE\" : \"pop_white\"   # White\n",
    "\"DP05_0038PE\" : \"pop_black\"   # Black or African American\n",
    "\"DP05_0039PE\" : \"pop_aindina\" # American Indian and Alaska Native\n",
    "\"DP05_0044PE\" : \"pop_asian\"   # Asian\n",
    "\"DP05_0052PE\" : \"pop_pacific\" # Native Hawaiian and Other Pacific Islander\n",
    "\"DP05_0057PE\" : \"pop_other\"   # Some other race\n",
    "\"DP05_0058PE\" : \"pop_twoplus\" # Two or more races\n",
    "\n",
    "# Total Population; Race Hispanic or Latino Percentage (Tally 100%)\n",
    "\"DP05_0071PE\" : \"poph_latino\"  # Hispanic or Latino (of any race)\n",
    "\"DP05_0077PE\" : \"poph_white\"   # Not Hispanic or Latino; White alone\n",
    "\"DP05_0078PE\" : \"poph_black\"   # Not Hispanic or Latino; Black or African American alone\n",
    "\"DP05_0079PE\" : \"poph_aindina\" # Not Hispanic or Latino; American Indian and Alaska Native alone\n",
    "\"DP05_0080PE\" : \"poph_asian\"   # Not Hispanic or Latino; Asian alone\n",
    "\"DP05_0081PE\" : \"poph_pacific\" # Not Hispanic or Latino; Native Hawaiian and Other Pacific Islander\n",
    "\"DP05_0082PE\" : \"poph_other\"   # Not Hispanic or Latino; Some other race alone\n",
    "\"DP05_0083PE\" : \"poph_twoplus\" # Not Hispanic or Latino; Two or more races\n",
    "\n",
    "# Total Population; Age Percentage (Tally 100%)\n",
    "\"DP05_0005PE\" : \"ab5\"   # Under 5 years\n",
    "\"DP05_0006PE\" : \"a59\"   # 5 to 9 years\n",
    "\"DP05_0007PE\" : \"a1014\" # 10 to 14 years\n",
    "\"DP05_0008PE\" : \"a1519\" # 15 to 19 years\n",
    "\"DP05_0009PE\" : \"a2024\" # 20 to 24 years\n",
    "\"DP05_0010PE\" : \"a2534\" # 25 to 34 years\n",
    "\"DP05_0011PE\" : \"a3544\" # 35 to 44 years\n",
    "\"DP05_0012PE\" : \"a4554\" # 45 to 54 years\n",
    "\"DP05_0013PE\" : \"a5559\" # 55 to 59 years\n",
    "\"DP05_0014PE\" : \"a6064\" # 60 to 64 years\n",
    "\"DP05_0015PE\" : \"a6574\" # 65 to 74 years\n",
    "\"DP05_0016PE\" : \"a7584\" # 75 to 84 years\n",
    "\"DP05_0017PE\" : \"a85a\" # 85 years and over"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Data (no need to download each time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Quickly start by loading instead of downloading from source\n",
    "# \n",
    "sfcrime = pd.read_csv('sfcrime.csv')\n",
    "sfnhood = pd.read_csv('sfnhood.csv')\n",
    "sfnhood_tract = pd.read_csv('sfnhood_tract.csv')\n",
    "sfacs5y2020 = pd.read_csv('sfacs5y2020.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploratory Data Analysis (EDA)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleanup- SF Crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing Data - SF Crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip3 install missingno\n",
    "import missingno as msno\n",
    "\n",
    "msno.matrix(sfcrime.sample(1000))\n",
    "# msno.heatmap(sfcrime)\n",
    "# msno.dendrogram(sfcrime)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SF Map by Neighborhood, Police District, Supervisor District, Tract"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Key Variables - SF Crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime = sfcrime[['incident_datetime',\n",
    "    'incident_id',\n",
    "    'incident_number',\n",
    "    'incident_code',\n",
    "    'incident_category',\n",
    "    'incident_subcategory',\n",
    "    'incident_description',\n",
    "    'nhood',\n",
    "    'police_district',\n",
    "    'supervisor_district',\n",
    "    'intersection',\n",
    "    'longitude',\n",
    "    'latitude']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove Duplicates - SF Crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime = sfcrime.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove NaN - SF Crime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Incident (Sub)Category (~500) or Location (Out of SF) (~35k) with NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime = sfcrime.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "msno.matrix(sfcrime.sample(1000))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean up Misspellings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sfcrime.loc[(sfcrime['incident_category'] == 'Weapons Offence') ,'incident_category'] = 'Weapons Offense'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove Case Closures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(sfcrime[sfcrime['incident_category'] == 'Case Closure'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime = sfcrime[sfcrime['incident_category'] != 'Case Closure']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Multiple Incident Codes\n",
    "# \n",
    "# https://datasf.gitbook.io/datasf-dataset-explainers/sfpd-incident-report-2018-to-present\n",
    "\n",
    "# Incident reports can have one or more associated Incident Codes\n",
    "# For example, an officer may have a warrant for an arrest and while making the arrest, \n",
    "# discovers narcotics in the individual’s possession. \n",
    "# The officer would record two Incident Codes: (1) for the warrant and (2) for the discovery of narcotics.\n",
    "sfcrime.groupby(by = ['incident_number']).filter(lambda x: len(x) > 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfcrime.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Introduce Time Series - SF Crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "\n",
    "sfcrime['incident_datetime'] = pd.to_datetime(sfcrime['incident_datetime'])\n",
    "sfcrime['incident_date'] = pd.to_datetime(sfcrime['incident_datetime'].dt.date)\n",
    "sfcrime['incident_year'] = pd.to_datetime(sfcrime['incident_datetime'].dt.strftime('%Y-01-01'))\n",
    "sfcrime['incident_yearmonth'] = pd.to_datetime(sfcrime['incident_datetime'].dt.strftime('%Y-%m-01'))\n",
    "sfcrime['incident_monthofyear'] = sfcrime['incident_datetime'].dt.month\n",
    "sfcrime['incident_dayofmonth'] = sfcrime['incident_datetime'].dt.day\n",
    "sfcrime['incident_dayofweek'] = sfcrime['incident_datetime'].dt.dayofweek\n",
    "sfcrime['incident_hour'] = sfcrime['incident_datetime'].dt.hour\n",
    "sfcrime['incident_minute'] = sfcrime['incident_datetime'].dt.minute\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleanup - SF ACS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfacs5y2020.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing Data - SF ACS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "msno.matrix(sfacs5y2020.sample(1000))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove Duplicates - SF ACS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Remove annotation column\n",
    "# \n",
    "sfacs5y2020 = sfacs5y2020.drop(columns = 'annotation')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfacs5y2020.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfacs5y2020 = sfacs5y2020.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove NaN - SF ACS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfacs5y2020.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfacs5y2020 = sfacs5y2020.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "msno.matrix(sfacs5y2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfacs5y2020.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfacs5y2020.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfnhood_tract.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Map US Census Tract Demographic to SF Crime Analysis Neighborhood"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Merge SF Crime Analysis Neighborhoods with Census Tract demograpics\n",
    "#\n",
    "sfnhood_acs5y2020 = pd.merge(\n",
    "    sfnhood_tract[['nhood', 'tractce']], \n",
    "    sfacs5y2020[['variable_code', 'value', 'tractce']], \n",
    "    on = [\"tractce\"]\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tally Neighborhood Incidents by Demographic Census Code\n",
    "sfnhood_acs5y2020 = sfnhood_acs5y2020.groupby(\n",
    "    by = ['nhood', 'variable_code']\n",
    "    ).aggregate({'value' : 'mean'}\n",
    "    ).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot from long (demographic code) to wide (neighborhood)\n",
    "sfnhood_acs5y2020 = sfnhood_acs5y2020.pivot(\n",
    "    index = 'nhood', \n",
    "    columns = 'variable_code', \n",
    "    values = 'value'\n",
    "    ).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Calculate Crime Incident Categories Numbers by Neighborhood\n",
    "#\n",
    "sfcrime_nhood = sfcrime.groupby(\n",
    "    by = ['nhood', 'incident_category']\n",
    "    ).aggregate(incidents = ('incident_category', 'count')\n",
    "    ).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot long (incident category) to wide (neighborhood)\n",
    "sfcrime_nhood = sfcrime_nhood.pivot(\n",
    "    index = 'nhood', \n",
    "    columns = 'incident_category', \n",
    "    values = 'incidents'\n",
    "    ).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace NaN with zero (0)\n",
    "sfcrime_nhood = sfcrime_nhood.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Merge Neighborhood Crime\n",
    "# \n",
    "sfnhood_acs5y2020 = pd.merge(\n",
    "    sfnhood_acs5y2020, \n",
    "    sfcrime_nhood, \n",
    "    on = ['nhood'])\n",
    "\n",
    "# Introduce Neighborhood Identifier\n",
    "sfnhood_acs5y2020[\"nhood_id\"] = sfnhood_acs5y2020.index + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Calculate Crime Incidents by Neighborhood\n",
    "# \n",
    "sfnhood_acs5y2020 = pd.merge(\n",
    "    sfnhood_acs5y2020, \n",
    "    sfcrime.groupby(by = ['nhood']).aggregate(incidents = ('incident_code', 'count')),\n",
    "    on = ['nhood'])  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Merge SF Analysis Neighborhood with Geographic Data\n",
    "#\n",
    "sfnhood_acs5y2020 = pd.merge(\n",
    "    sfnhood_acs5y2020, \n",
    "    sfnhood[['the_geom', 'nhood']], \n",
    "    on = ['nhood'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfnhood_acs5y2020 = sfnhood_acs5y2020.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename US Census ACS Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfnhood_acs5y2020 = sfnhood_acs5y2020.rename(columns = {\n",
    "    \"DP02_0060PE\" : \"ed_below9\", # Less than 9th grade\n",
    "    \"DP02_0061PE\" : \"ed_g912\", # 9th to 12th grade, no diploma\n",
    "    \"DP02_0062PE\" : \"ed_hs\", # High school graduate (includes equivalency)\n",
    "    \"DP02_0063PE\" : \"ed_scollege\", # Some college, no degree\n",
    "    \"DP02_0064PE\" : \"ed_associate\", # Associate's degree\n",
    "    \"DP02_0065PE\" : \"ed_bachelor\", # Bachelor's degree\n",
    "    \"DP02_0066PE\" : \"ed_higher\", # Graduate or professional degree\n",
    "    \"DP02_0067PE\" : \"ed_hs_higher\", # High school graduate or higher\n",
    "    \"DP02_0068PE\" : \"ed_college_higher\", # Bachelor's degree or higher\n",
    "    \"DP03_0003PE\" : \"eco_clf\", # Percent!!EMPLOYMENT STATUS!!Population 16 years and over!!In labor force!!Civilian labor force\n",
    "    \"DP03_0009PE\" : \"eco_unemp\", # Percent!!EMPLOYMENT STATUS!!Civilian labor force!!Unemployment Rate\n",
    "    \"DP03_0062E\"  : \"eco_med_hincome\", # Median household income (dollars) Estimate\n",
    "    \"DP03_0119PE\" : \"eco_poverty\",     # Percentage of families and people whose income in the past 12 months is below poverty level\n",
    "    \"DP04_0037E\" : \"h_med_rooms\", # Estimate!!ROOMS!!Total housing units!!Median rooms\n",
    "    \"DP04_0047PE\" : \"h_rental_units\", # Percent!!HOUSING TENURE!!Occupied housing units!!Renter-occupied\n",
    "    \"DP04_0049E\" : \"h_rentel_size\", # Estimate!!HOUSING TENURE!!Occupied housing units!!Average household size of renter-occupied unit\n",
    "    \"DP04_0134E\" : \"h_med_rent\", # Estimate!!GROSS RENT!!Occupied units paying rent!!Median (dollars)\n",
    "    \"DP04_0089E\" : \"h_med_homeprice\", # Estimate!!VALUE!!Owner-occupied units!!Median (dollars)\n",
    "    \"DP05_0001E\"  : \"pop\",              # Total Population\n",
    "    \"DP05_0071PE\" : \"poph_latino\",  # Hispanic or Latino (of any race)\n",
    "    \"DP05_0077PE\" : \"poph_white\",   # Not Hispanic or Latino; White alone\n",
    "    \"DP05_0078PE\" : \"poph_black\",   # Not Hispanic or Latino; Black or African American alone\n",
    "    \"DP05_0079PE\" : \"poph_aindian\", # Not Hispanic or Latino; American Indian and Alaska Native alone\n",
    "    \"DP05_0080PE\" : \"poph_asian\",   # Not Hispanic or Latino; Asian alone\n",
    "    \"DP05_0081PE\" : \"poph_pacific\", # Not Hispanic or Latino; Native Hawaiian and Other Pacific Islander\n",
    "    \"DP05_0082PE\" : \"poph_other\",   # Not Hispanic or Latino; Some other race alone\n",
    "    \"DP05_0083PE\" : \"poph_twoplus\", # Not Hispanic or Latino; Two or more races\n",
    "    \"DP05_0005PE\" : \"ab5\",   # Under 5 years\n",
    "    \"DP05_0006PE\" : \"a59\",   # 5 to 9 years\n",
    "    \"DP05_0007PE\" : \"a1014\", # 10 to 14 years\n",
    "    \"DP05_0008PE\" : \"a1519\", # 15 to 19 years\n",
    "    \"DP05_0009PE\" : \"a2024\", # 20 to 24 years\n",
    "    \"DP05_0010PE\" : \"a2534\", # 25 to 34 years\n",
    "    \"DP05_0011PE\" : \"a3544\", # 35 to 44 years\n",
    "    \"DP05_0012PE\" : \"a4554\", # 45 to 54 years\n",
    "    \"DP05_0013PE\" : \"a5559\", # 55 to 59 years\n",
    "    \"DP05_0014PE\" : \"a6064\", # 60 to 64 years\n",
    "    \"DP05_0015PE\" : \"a6574\", # 65 to 74 years\n",
    "    \"DP05_0016PE\" : \"a7584\", # 75 to 84 years\n",
    "    \"DP05_0017PE\" : \"a85a\", # 85 years and over\n",
    "    \"DP05_0018E\"  : \"mage\" # Median age (years)\n",
    "})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate City Human Capital Index (CHCI)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Calculate CHCI\n",
    "# \n",
    "# https://www.anderson.ucla.edu/about/centers/ucla-anderson-forecast/projects-and-partnerships/city-human-capital-index\n",
    "\n",
    "sfnhood_acs5y2020['chci'] = ((1 / 100) * \n",
    "    (50 * sfnhood_acs5y2020['ed_below9'] + \n",
    "    100 * sfnhood_acs5y2020['ed_g912'] +\n",
    "    120 * sfnhood_acs5y2020['ed_hs'] +\n",
    "    130 * sfnhood_acs5y2020['ed_scollege'] +\n",
    "    140 * sfnhood_acs5y2020['ed_associate'] +\n",
    "    190 * sfnhood_acs5y2020['ed_bachelor'] +\n",
    "    230 * sfnhood_acs5y2020['ed_higher']))\n",
    "\n",
    "#\n",
    "# Calculate Education (Tally 100%)\n",
    "#\n",
    "# sfnhood_acs5y2020['ed'] = (sfnhood_acs5y2020['ed_below9'] +\n",
    "#     sfnhood_acs5y2020['ed_g912'] +\n",
    "#     sfnhood_acs5y2020['ed_hs'] +\n",
    "#     sfnhood_acs5y2020['ed_scollege'] +\n",
    "#     sfnhood_acs5y2020['ed_associate'] +\n",
    "#     sfnhood_acs5y2020['ed_bachelor'] +\n",
    "#     sfnhood_acs5y2020['ed_higher'])\n",
    "\n",
    "#\n",
    "# Calculate Population (Tally 100%)\n",
    "#\n",
    "# sfnhood_acs5y2020['pop%'] = (sfnhood_acs5y2020['poph_latino'] +\n",
    "#     sfnhood_acs5y2020['poph_white'] +\n",
    "#     sfnhood_acs5y2020['poph_black'] +\n",
    "#     sfnhood_acs5y2020['poph_aindina'] +\n",
    "#     sfnhood_acs5y2020['poph_asian'] +\n",
    "#     sfnhood_acs5y2020['poph_pacific'] +\n",
    "#     sfnhood_acs5y2020['poph_other'] +\n",
    "#     sfnhood_acs5y2020['poph_twoplus'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyze Crime Data by Category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import seaborn as sns # Import seaborn\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Set seaboard theme\n",
    "ax = sns.set(style='darkgrid')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Category Hierarchy:\n",
    "#\n",
    "#  Category \n",
    "#      - Subcategory \n",
    "#            - Description\n",
    "\n",
    "sfcrime.groupby(by = ['incident_category','incident_subcategory','incident_description']).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Category\n",
    "# \n",
    "sfcrime_cat_freq = sfcrime.groupby(by = ['incident_category']\n",
    "    ).aggregate(incidents = ('incident_code', 'count')\n",
    "    ).sort_values(by = 'incidents', ascending = False\n",
    "    ).reset_index()\n",
    "sfcrime_cat_freq['frequency'] = sfcrime_cat_freq['incidents']/sfcrime_cat_freq['incidents'].sum()\n",
    "sfcrime_cat_freq['cum_frequency'] = sfcrime_cat_freq['frequency'].cumsum()\n",
    "\n",
    "#\n",
    "# Plot - All Categories\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_cat_freq,\n",
    "    y = 'incident_category',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Category',fontsize=20)\n",
    "ax.set_title('2018 to Present: Incidents by Category', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Plot - Top X Categories makes up X% of incidents\n",
    "#\n",
    "topn = 10\n",
    "\n",
    "#\n",
    "# Plot - All Categories\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_cat_freq.iloc[:topn],\n",
    "    y = 'incident_category',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Category',fontsize=20)\n",
    "ax.set_title('2018 to Present: {:0.0f}% Incidents by Top {} Categories'.format(\n",
    "    sfcrime_cat_freq['cum_frequency'].iloc[topn] * 100,\n",
    "    topn), \n",
    "    fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Subategory\n",
    "# \n",
    "sfcrime_subcat_freq = sfcrime.groupby(by = ['incident_subcategory']\n",
    "    ).aggregate(incidents = ('incident_code', 'count')\n",
    "    ).sort_values(by = 'incidents', ascending = False\n",
    "    ).reset_index()\n",
    "sfcrime_subcat_freq['frequency'] = sfcrime_subcat_freq['incidents']/sfcrime_subcat_freq['incidents'].sum()\n",
    "sfcrime_subcat_freq['cum_frequency'] = sfcrime_subcat_freq['frequency'].cumsum()\n",
    "\n",
    "#\n",
    "# Plot\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_subcat_freq,\n",
    "    y = 'incident_subcategory',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Subcategory',fontsize=20)\n",
    "ax.set_title('2018 to Present: Incidents by Subcategory', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Subategory - Top 17 / 71 (24%) makes up 82% of incidents\n",
    "# \n",
    "topn = 17\n",
    "\n",
    "#\n",
    "# Plot\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_subcat_freq.iloc[:topn],\n",
    "    y = 'incident_subcategory',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Subcategory',fontsize=20)\n",
    "ax.set_title('2018 to Present: {:0.0f}% Incidents by Top {} Subcategories'.format(\n",
    "    sfcrime_subcat_freq['cum_frequency'].iloc[topn] * 100,\n",
    "    topn), \n",
    "    fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Description\n",
    "# \n",
    "sfcrime_desc_freq = sfcrime.groupby(by = ['incident_description']\n",
    "    ).aggregate(incidents = ('incident_code', 'count')\n",
    "    ).sort_values(by = 'incidents', ascending = False\n",
    "    ).reset_index()\n",
    "sfcrime_desc_freq['frequency'] = sfcrime_desc_freq['incidents']/sfcrime_desc_freq['incidents'].sum()\n",
    "sfcrime_desc_freq['cum_frequency'] = sfcrime_desc_freq['frequency'].cumsum()\n",
    "\n",
    "#\n",
    "# Plot\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_desc_freq,\n",
    "    y = 'incident_description',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Description',fontsize=20)\n",
    "ax.set_title('2018 to Present: Incidents by Description', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Description - Top 20 Descriptions\n",
    "# \n",
    "topn = 20\n",
    "\n",
    "#\n",
    "# Plot\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_desc_freq.iloc[:topn],\n",
    "    y = 'incident_description',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Description (Code)',fontsize=20)\n",
    "ax.set_title('2018 to Present: {:0.0f}% Incidents by Top {} Descriptions'.format(\n",
    "    sfcrime_desc_freq['cum_frequency'].iloc[topn] * 100,\n",
    "    topn), \n",
    "    fontsize=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyze Crime Data by Location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Intersection - Top 31 / 6371 (3%) makes up 10% of incidents\n",
    "# \n",
    "sfcrime_intersecation_freq = sfcrime.groupby(\n",
    "    by = ['intersection']\n",
    "    ).aggregate(incidents = ('incident_code', 'count')\n",
    "    ).sort_values(by = 'incidents', ascending = False\n",
    "    ).reset_index()\n",
    "sfcrime_intersecation_freq['frequency'] = sfcrime_intersecation_freq['incidents']/sfcrime_intersecation_freq['incidents'].sum()\n",
    "sfcrime_intersecation_freq['cum_frequency'] = sfcrime_intersecation_freq['frequency'].cumsum()\n",
    "\n",
    "topn = 30\n",
    "\n",
    "#\n",
    "# Plot\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_intersecation_freq.iloc[:topn],\n",
    "    y = 'intersection',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Intersections',fontsize=20)\n",
    "ax.set_title('2018 to Present: {:0.0f}% Incidents by Top {} Intersections'.format(\n",
    "    sfcrime_intersecation_freq['cum_frequency'].iloc[topn] * 100,\n",
    "    topn), \n",
    "    fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Analysis Neighborhood\n",
    "# \n",
    "sfcrime_nhood_freq = sfcrime.groupby(by = ['nhood']\n",
    "    ).aggregate(incidents = ('incident_code', 'count')\n",
    "    ).sort_values(by = 'incidents', ascending = False\n",
    "    ).reset_index()\n",
    "sfcrime_nhood_freq['frequency'] = sfcrime_nhood_freq['incidents']/sfcrime_nhood_freq['incidents'].sum()\n",
    "sfcrime_nhood_freq['cum_frequency'] = sfcrime_nhood_freq['frequency'].cumsum()\n",
    "\n",
    "# Plot\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_nhood_freq,\n",
    "    y = 'nhood',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Neighborhood',fontsize=20)\n",
    "ax.set_title('2018 to Present: Incidents by Neighborhood', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Analysis Neighborhood - Top 6 / 41 (15%) makes up 50% of incidents\n",
    "# \n",
    "topn = 6\n",
    "\n",
    "#\n",
    "# Plot\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = sfcrime_nhood_freq.iloc[:topn],\n",
    "    y = 'nhood',\n",
    "    x = 'incidents',\n",
    "    palette=\"Reds_r\")\n",
    "\n",
    "ax.set_xlabel('Number of Incidents',fontsize=20)\n",
    "ax.set_ylabel('Incident Neighborhood',fontsize=20)\n",
    "ax.set_title('2018 to Present: {:0.0f}% Incidents by Top {} Neighborhood'.format(\n",
    "    sfcrime_nhood_freq['cum_frequency'].iloc[topn] * 100,\n",
    "    topn), \n",
    "    fontsize=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyze Crime Data by Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.dates as mdates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Visualize Crime Incidents by Time \n",
    "#\n",
    "\n",
    "#######################################################################\n",
    "# Skewed due to incident count not mean; Need to standardized over time\n",
    "#######################################################################\n",
    "\n",
    "fig, axes = plt.subplots(2, 3, figsize=(16, 9))\n",
    " \n",
    "fig.suptitle('SF Crime Incident by Time')\n",
    "  \n",
    "sns.barplot(ax=axes[0, 0], data = sfcrime.groupby(by = ['incident_year'])['incident_code'].count().reset_index(), x='incident_year', y='incident_code', palette=\"dark:blue\")\n",
    "axes[0, 0].set(ylabel='Number of Incidents', xlabel='Year')\n",
    "axes[0, 0].set_xticklabels(axes[0, 0].get_xticklabels(), rotation=45, horizontalalignment='right')\n",
    "sns.barplot(ax=axes[0, 1], data = sfcrime.groupby(by = ['incident_monthofyear'])['incident_code'].count().reset_index(), x='incident_monthofyear', y='incident_code', palette=\"dark:blue\")\n",
    "axes[0, 1].set(ylabel='', xlabel='Month of Year')\n",
    "sns.barplot(ax=axes[0, 2], data = sfcrime.groupby(by = ['incident_dayofmonth'])['incident_code'].count().reset_index(), x='incident_dayofmonth', y='incident_code', palette=\"dark:blue\")\n",
    "axes[0, 2].set(ylabel='', xlabel='Day of Month')\n",
    "sns.barplot(ax=axes[1, 0], data = sfcrime.groupby(by = ['incident_dayofweek'])['incident_code'].count().reset_index(), x='incident_dayofweek', y='incident_code', palette=\"dark:blue\")\n",
    "axes[1, 0].set(ylabel='', xlabel='Day of Week')\n",
    "sns.barplot(ax=axes[1, 1], data = sfcrime.groupby(by = ['incident_hour'])['incident_code'].count().reset_index(), x='incident_hour', y='incident_code', palette=\"dark:blue\")\n",
    "axes[1, 1].set(ylabel='', xlabel='Hour')\n",
    "sns.barplot(ax=axes[1, 2], data = sfcrime.groupby(by = ['incident_minute'])['incident_code'].count().reset_index(), x='incident_minute', y='incident_code', palette=\"dark:blue\")\n",
    "axes[1, 2].set(ylabel='', xlabel='Minute')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Visualize Crime Incidents by Minute of Day\n",
    "#\n",
    "df = sfcrime.groupby(by = ['incident_datetime']).aggregate(incidents = ('incident_datetime', 'count')).rename_axis('date')\n",
    "\n",
    "df = df.resample(\"Min\").mean()\n",
    "\n",
    "#\n",
    "# Crime Incidents by Minute of Day\n",
    "# \n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = df,\n",
    "    y = 'incidents',\n",
    "    x = (df.index).minute,\n",
    "    errorbar = 'sd',\n",
    "    capsize = 0.2,\n",
    "    palette=\"ch:start = .2, rot = -.2, dark = 0.4, light = 0.8\"\n",
    "    )\n",
    "              \n",
    "ax.set_xlabel('Time (Minute of Hour)',fontsize=20)\n",
    "ax.set_ylabel('Average Incidents by Minute',fontsize=20)\n",
    "ax.set_title('2018 to Present: Average Incidents by Minute', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Visualize Crime Incidents by Hour of Day\n",
    "#\n",
    "df = sfcrime.groupby(by = ['incident_datetime']).aggregate(incidents = ('incident_datetime', 'count')).rename_axis('date')\n",
    "\n",
    "df = df.resample(\"H\").mean()\n",
    "\n",
    "#\n",
    "# Crime Incidents by Hour of Day\n",
    "# \n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "    data = df,\n",
    "    y = 'incidents',\n",
    "    x = (df.index).hour,\n",
    "    errorbar = 'sd',\n",
    "    capsize = 0.2,\n",
    "    palette=\"ch:start = .2, rot = -.2, dark = 0.4, light = 0.8\"\n",
    "    )\n",
    "              \n",
    "ax.set_xlabel('Time (Hour of the day)',fontsize=20)\n",
    "ax.set_ylabel('Average Hourly Incidents',fontsize=20)\n",
    "ax.set_title('2018 to Present: Average Hourly Incidents by Hour of Day', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Visualize Crime Incidents by Day of Week\n",
    "#\n",
    "df = sfcrime.groupby(by = ['incident_date']).aggregate(incidents = ('incident_datetime', 'count')).rename_axis('date')\n",
    "\n",
    "df = df.resample(\"D\").mean()\n",
    "\n",
    "#\n",
    "# Crime Incidents by Day of Week\n",
    "# \n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "   data = df,\n",
    "   y = 'incidents',\n",
    "   x = (df.index).strftime('%A'),\n",
    "   errorbar = 'sd',\n",
    "   capsize = 0.2,\n",
    "   palette=\"ch:start = .2, rot = -.2, dark = 0.4, light = 0.8\"\n",
    "   )\n",
    "\n",
    "ax.set_xlabel('Time (Day of Week)',fontsize=20)\n",
    "ax.set_ylabel('Average Daily Incidents',fontsize=20)\n",
    "ax.set_title('2018 to Present: Average Daily Incidents by Day of Week', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Visualize Crime Incidents by Day of Month\n",
    "#\n",
    "df = sfcrime.groupby(by = ['incident_date']).aggregate(incidents = ('incident_datetime', 'count')).rename_axis('date')\n",
    "\n",
    "df = df.resample(\"D\").mean()\n",
    "\n",
    "#\n",
    "# Crime Incidents by Day of Month\n",
    "# \n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "   data = df,\n",
    "   y = 'incidents',\n",
    "   x = (df.index).day,\n",
    "   errorbar = 'sd',\n",
    "   capsize = 0.2,\n",
    "   palette=\"ch:start = .2, rot = -.2, dark = 0.4, light = 0.8\"\n",
    "   )\n",
    "              \n",
    "ax.set_xlabel('Time (Day of Month)',fontsize=20)\n",
    "ax.set_ylabel('Average Daily Incidents',fontsize=20)\n",
    "ax.set_title('2018 to Present: Average Daily Incidents by Day of Month', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Visualize Crime Incidents by Day\n",
    "#\n",
    "df = sfcrime.groupby(by = ['incident_date']).aggregate(incidents = ('incident_datetime', 'count')).rename_axis('date')\n",
    "\n",
    "df = df.resample(\"M\").mean()\n",
    "\n",
    "#\n",
    "# Crime Incidents by Month of Year\n",
    "# \n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "   data = df,\n",
    "   y = 'incidents',\n",
    "   x = (df.index).strftime('%B'),\n",
    "   errorbar = 'sd',\n",
    "   capsize = 0.2,\n",
    "   palette=\"ch:start = .2, rot = -.2, dark = 0.4, light = 0.8\"\n",
    "   )\n",
    "              \n",
    "ax.set_xlabel('Time (Month of Year)',fontsize=20)\n",
    "ax.set_ylabel('Average Daily Incidents',fontsize=20)\n",
    "ax.set_title('2018 to Present: Average Daily Incidents by Month of Year', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Visualize Crime Incidents by Year\n",
    "#\n",
    "df = sfcrime.groupby(by = ['incident_date']).aggregate(incidents = ('incident_datetime', 'count')).rename_axis('date')\n",
    "\n",
    "df = df.resample(\"D\").mean()\n",
    "\n",
    "#\n",
    "# Average Daily Crime Incidents by Year\n",
    "# \n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.barplot(\n",
    "   data = df,\n",
    "   y = 'incidents',\n",
    "   x = (df.index).year,\n",
    "   errorbar = 'sd',\n",
    "   capsize = 0.2,\n",
    "   palette=\"ch:start = .2, rot = -.2, dark = 0.4, light = 0.8\"\n",
    "   )\n",
    "              \n",
    "ax.set_xlabel('Time (Year)',fontsize=20)\n",
    "ax.set_ylabel('Average Daily Incidents',fontsize=20)\n",
    "ax.set_title('2018 to Present: Average Daily Incidents by Year', fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Crime Time Series by Day\n",
    "# \n",
    "\n",
    "#  Plot\n",
    "fig, ax = plt.subplots(figsize=(30, 10))\n",
    "\n",
    "ax = sns.lineplot(\n",
    "  data = sfcrime.groupby(by = ['incident_date'])['incident_code'].count().reset_index(),\n",
    "  x = 'incident_date', \n",
    "  y = 'incident_code'\n",
    "  )\n",
    "\n",
    "ax.set(title = 'SF Crime Incidents - 2018 to Present', ylabel='Number of Incidents', xlabel='Time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Zoom into specific dates\n",
    "#\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "ax = sns.lineplot(\n",
    "  data = sfcrime[\n",
    "    (sfcrime['incident_date'] > '2021-02') & \n",
    "    (sfcrime['incident_date'] < '2021-06')\n",
    "    ].groupby(by = ['incident_date']\n",
    "    )['incident_code'].count().reset_index(),\n",
    "  x = 'incident_date',\n",
    "  y = 'incident_code'\n",
    "  )\n",
    "\n",
    "ax.set(title = 'SF Crime Incidents - 2018 to Present', ylabel='Number of Incidents', xlabel='Time')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Seasonality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from statsmodels.tsa.seasonal import seasonal_decompose"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Time Series Decomposition with Daily Incidents - Additive v Multiplicative (same?)\n",
    "# \n",
    "\n",
    "df = sfcrime.groupby(by = ['incident_date']).aggregate(value = ('incident_datetime', 'count'))\n",
    "\n",
    "decomposition_mul = seasonal_decompose(df, model='multiplicative')\n",
    "decomposition_add = seasonal_decompose(df, model='additive')\n",
    "\n",
    "def plotseasonal(res, axes, title):\n",
    "    axes[0].title.set_text(title)\n",
    "\n",
    "    res.observed.plot(ax=axes[0], legend=False)\n",
    "    axes[0].set_ylabel('Observed')\n",
    "    \n",
    "    res.trend.plot(ax=axes[1], legend=False)\n",
    "    axes[1].set_ylabel('Trend')\n",
    "    \n",
    "    res.seasonal.plot(ax=axes[2], legend=False)\n",
    "    axes[2].set_ylabel('Seasonal')\n",
    "    \n",
    "    res.resid.plot(ax=axes[3], legend=False)\n",
    "    axes[3].set_ylabel('Residual')\n",
    "\n",
    "fig, axes = plt.subplots(ncols=2, nrows=4, sharex=False, figsize=(30,15))\n",
    "plotseasonal(decomposition_mul, axes[:,0], title=\"Multiplicative\")\n",
    "plotseasonal(decomposition_add, axes[:,1], title=\"Additive\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Additive Time Series Decomposition with Daily Incidents\n",
    "# \n",
    "df = sfcrime.groupby(by = ['incident_date']).aggregate(value = ('incident_datetime', 'count'))\n",
    "\n",
    "plt.rc(\"figure\",figsize=(16, 9))\n",
    "analysis = df[['value']].copy()\n",
    "\n",
    "decompose_result = seasonal_decompose(analysis, model=\"additive\")\n",
    "\n",
    "observed = decompose_result.observed\n",
    "trend = decompose_result.trend\n",
    "seasonal = decompose_result.seasonal\n",
    "residual = decompose_result.resid\n",
    "\n",
    "decompose_result.plot();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Additive Time Series Decomposition with Monthly Incidents\n",
    "# \n",
    "df = sfcrime.groupby(by = ['incident_yearmonth']).aggregate(value = ('incident_datetime', 'count'))\n",
    "\n",
    "plt.rc(\"figure\",figsize=(16,9))\n",
    "analysis = df[['value']].copy()\n",
    "\n",
    "decompose_result_mult = seasonal_decompose(analysis, model=\"additive\")\n",
    "\n",
    "trend = decompose_result_mult.trend\n",
    "seasonal = decompose_result_mult.seasonal\n",
    "residual = decompose_result_mult.resid\n",
    "\n",
    "decompose_result_mult.plot();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Multiplicative Time Series Decomposition with Monthly Incidents\n",
    "# \n",
    "df = sfcrime.groupby(by = ['incident_yearmonth']).aggregate(value = ('incident_datetime', 'count'))\n",
    "\n",
    "plt.rc(\"figure\",figsize=(16,9))\n",
    "analysis = df[['value']].copy()\n",
    "\n",
    "decompose_result = seasonal_decompose(analysis, model=\"multiplicative\")\n",
    "\n",
    "trend = decompose_result.trend\n",
    "seasonal = decompose_result.seasonal\n",
    "residual = decompose_result.resid\n",
    "\n",
    "decompose_result.plot();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crime Incident Distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = sns.displot(sfcrime[['incident_date']].groupby(by = ['incident_date']).value_counts(), kde = True)\n",
    "ax.set_axis_labels(\"Number of Incidents Daily\", \"Density\")\n",
    "plt.title(\"Crime Distribution\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = sns.color_palette()\n",
    "\n",
    "plt.figure(figsize=(16, 9))\n",
    "data = sfcrime.groupby('incident_date').count().iloc[:, 0]\n",
    "sns.kdeplot(data=data, shade=True)\n",
    "plt.axvline(x=data.median(), ymax=0.95, linestyle='--', color=col[1])\n",
    "plt.annotate(\n",
    "    'Median: ' + str(data.median()),\n",
    "    xy=(data.median(), 0.004),\n",
    "    xytext=(200, 0.005),\n",
    "    arrowprops=dict(arrowstyle='->', color=col[1], shrinkB=10))\n",
    "plt.title('Distribution of number of incidents per day', fontdict={'fontsize': 16})\n",
    "plt.xlabel('Incidents')\n",
    "plt.ylabel('Density')\n",
    "plt.legend().remove()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyze Crime Data - Mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip3 install folium\n",
    " \n",
    "import folium\n",
    "from folium.plugins import HeatMap\n",
    "from folium.plugins import HeatMapWithTime\n",
    "from folium import plugins\n",
    "\n",
    "import json\n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Source Geography"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Source Geographies and save locally\n",
    "# \n",
    "\n",
    "# San Francisco Analysis Neighborhood Geography\n",
    "sfnhood_geojson = r\"https://data.sfgov.org/api/geospatial/p5b7-5n3h?method=export&format=GeoJSON\"\n",
    "# Save locally\n",
    "sfnhood_geojson_f = requests.get(sfnhood_geojson).json()\n",
    "with open('sfnhood_geojson.json', 'w') as f:\n",
    "    json.dump(sfnhood_geojson_f, f)\n",
    "\n",
    "# San Francisco Police Disctrict Geography\n",
    "sfpdistrict_geojson = r\"https://data.sfgov.org/api/geospatial/wkhw-cjsf?method=export&format=GeoJSON\"\n",
    "# Save locally\n",
    "sfpdistrict_geojson_f = requests.get(sfpdistrict_geojson).json()\n",
    "with open('sfpdistrict_geojson.json', 'w') as f:\n",
    "    json.dump(sfpdistrict_geojson_f, f)\n",
    "\n",
    "# San Francisco Supervisor District Geography\n",
    "sfsdistrict_geojson = r\"https://data.sfgov.org/api/geospatial/f2zs-jevy?method=export&format=GeoJSON\"\n",
    "# Save locally\n",
    "sfsdistrict_geojson_f = requests.get(sfsdistrict_geojson).json()\n",
    "with open('sfsdistrict_geojson.json', 'w') as f:\n",
    "    json.dump(sfsdistrict_geojson_f, f)\n",
    "\n",
    "# San Francisco US Census Tract Geography\n",
    "sftract_geojson = r\"https://data.sfgov.org/api/geospatial/tmph-tgz9?method=export&format=GeoJSON\"\n",
    "# Save locally\n",
    "sftract_geojson_f = requests.get(sftract_geojson).json()\n",
    "with open('sftract_geojson.json', 'w') as f:\n",
    "    json.dump(sftract_geojson_f, f)\n",
    "\n",
    "# San Francisco US Census Tract Geography to SF Analysis Neighborhood\n",
    "sfnhood_tract_geojson = r\"https://data.sfgov.org/api/geospatial/sevw-6tgi?method=export&format=GeoJSON\"\n",
    "# Save locally\n",
    "sfnhood_tract_geojson_f = requests.get(sfnhood_tract_geojson).json()\n",
    "with open('sfnhood_tract_geojson.json', 'w') as f:\n",
    "    json.dump(sfnhood_tract_geojson_f, f)\n",
    "\n",
    "# Folium Colorscale\n",
    "# https://github.com/python-visualization/folium/blob/v0.2.0/folium/utilities.py#L104"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load (Saved) Geography"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Load Geography from Local source\n",
    "#\n",
    "sfnhood_geojson = 'sfnhood_geojson.json'\n",
    "sfpdistrict_geojson = 'sfpdistrict_geojson.json'\n",
    "sfsdistrict_geojson = 'sfsdistrict_geojson.json'\n",
    "sftract_geojson = 'sftract_geojson.json'\n",
    "sfnhood_tract_geojson = 'sfnhood_tract_geojson.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# San Francisco\n",
    "# \n",
    "sfmap = folium.Map(\n",
    "    location=[37.77,-122.42],\n",
    "    tiles=\"Cartodb Positron\",     \n",
    "    zoom_start=13,\n",
    "    width=\"%100\",\n",
    "    height=\"%100\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Map Style\n",
    "#\n",
    "style_function = lambda x: {'color':'black',\n",
    "                            'weight':0.2, \n",
    "                            'fillColor':'lightblue', \n",
    "                            'fillOpacity':0.2\n",
    "                            }\n",
    "\n",
    "highlight_function = lambda x: {'color':'black',\n",
    "                                'weight': 0.7,\n",
    "                                'fillColor': 'lightgreen',\n",
    "                                'fillOpacity': 0.50\n",
    "                                }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Neighborhoods\n",
    "# \n",
    "# Layers:   Crime Analysis Neighborhoods\n",
    "#           Police Districts\n",
    "#           Supervisor Districts\n",
    "#           Census Tracts\n",
    "#\n",
    "sfmap = folium.Map(\n",
    "                location=[37.763,-122.42],\n",
    "                tiles=\"Cartodb Positron\",     \n",
    "                zoom_start=12,\n",
    "                width=\"%100\",\n",
    "                height=\"%100\")\n",
    "                \n",
    "folium.GeoJson(\n",
    "            data = sfnhood_geojson, \n",
    "            style_function = style_function,\n",
    "            highlight_function = highlight_function,\n",
    "            name=\"Analysis Neighborhood\").add_to(sfmap)\n",
    "\n",
    "folium.GeoJson(\n",
    "            data = sfpdistrict_geojson, \n",
    "            style_function = style_function,\n",
    "            highlight_function = highlight_function,\n",
    "            name=\"Police District\").add_to(sfmap)\n",
    "\n",
    "folium.GeoJson(\n",
    "            data = sfsdistrict_geojson, \n",
    "            style_function = style_function,\n",
    "            highlight_function = highlight_function,\n",
    "            name=\"Supervisor District\").add_to(sfmap)\n",
    "\n",
    "folium.GeoJson(\n",
    "            data = sfnhood_tract_geojson, \n",
    "            style_function = style_function,\n",
    "            highlight_function = highlight_function,\n",
    "            name=\"Census Tract\").add_to(sfmap)\n",
    "\n",
    "folium.LayerControl().add_to(sfmap)\n",
    "\n",
    "sfmap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Neighborhood Population\n",
    "#\n",
    "sfmap = folium.Map(\n",
    "    location=[37.77,-122.42],\n",
    "    tiles=\"Cartodb Positron\",     \n",
    "    zoom_start=12,\n",
    "    width=\"%100\",\n",
    "    height=\"%100\")\n",
    "    \n",
    "folium.Choropleth(\n",
    "    geo_data=sfnhood_geojson,\n",
    "    data=sfnhood_acs5y2020,\n",
    "    columns=['nhood', 'pop'],\n",
    "    key_on='feature.properties.nhood',\n",
    "    fill_color ='YlGnBu',\n",
    "    fill_opacity = 0.7,\n",
    "    line_opacity = 0.2,\n",
    "    name ='Population',     \n",
    "    legend_name = \"Population Scale\"\n",
    ").add_to(sfmap)\n",
    "\n",
    "sfmap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Neighborhood CHCI\n",
    "#\n",
    "sfmap = folium.Map(\n",
    "    location=[37.77,-122.42],\n",
    "    tiles=\"Cartodb Positron\",     \n",
    "    zoom_start=12,\n",
    "    width=\"%100\",\n",
    "    height=\"%100\")\n",
    "    \n",
    "folium.Choropleth(\n",
    "    geo_data=sfnhood_geojson,\n",
    "    data=sfnhood_acs5y2020,\n",
    "    columns=['nhood', 'chci'],\n",
    "    key_on='feature.properties.nhood',\n",
    "    fill_color ='YlGnBu',\n",
    "    fill_opacity = 0.7,\n",
    "    line_opacity = 0.2,\n",
    "    name ='CHCI',     \n",
    "    legend_name = \"CHCI Scale\"\n",
    ").add_to(sfmap)\n",
    "\n",
    "sfmap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# SF Neighborhood Crime Incidents\n",
    "#\n",
    "sfmap = folium.Map(\n",
    "    location=[37.77,-122.42],\n",
    "    tiles=\"Cartodb Positron\",     \n",
    "    zoom_start=12,\n",
    "    width=\"%100\",\n",
    "    height=\"%100\")\n",
    "    \n",
    "folium.Choropleth(\n",
    "    geo_data=sfnhood_geojson,\n",
    "    data=sfnhood_acs5y2020,\n",
    "    columns=['nhood', 'incidents'],\n",
    "    key_on='feature.properties.nhood',\n",
    "    fill_color ='YlOrRd',\n",
    "    fill_opacity = 0.7,\n",
    "    line_opacity = 0.2,\n",
    "    name ='Crime Incidents',     \n",
    "    legend_name = \"Crime Scale\"\n",
    ").add_to(sfmap)\n",
    "\n",
    "sfmap"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SF Crime Heatmap by Time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modeling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "from statsmodels.formula.api import ols\n",
    "import statsmodels.api as sm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Crime Rate - Dependant Variable\n",
    "#\n",
    "sfnhood_acs5y2020['crime_rate'] = sfnhood_acs5y2020['incidents'] / sfnhood_acs5y2020['pop']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Age Variables\n",
    "#\n",
    "sfnhood_acs5y2020['a75a'] = sfnhood_acs5y2020['a7584'] + sfnhood_acs5y2020['a85a']\n",
    "sfnhood_acs5y2020['a65a'] = sfnhood_acs5y2020['a6574'] + sfnhood_acs5y2020['a75a']\n",
    "sfnhood_acs5y2020['a60a'] = sfnhood_acs5y2020['a6064'] + sfnhood_acs5y2020['a65a']\n",
    "sfnhood_acs5y2020['a5564'] = sfnhood_acs5y2020['a5559'] + sfnhood_acs5y2020['a6064']\n",
    "sfnhood_acs5y2020['a3554'] = sfnhood_acs5y2020['a3544'] + sfnhood_acs5y2020['a4554']\n",
    "sfnhood_acs5y2020['a2034'] = sfnhood_acs5y2020['a2024'] + sfnhood_acs5y2020['a2534']\n",
    "sfnhood_acs5y2020['ab19'] = sfnhood_acs5y2020['ab5'] + sfnhood_acs5y2020['a59'] + sfnhood_acs5y2020['a1014'] + sfnhood_acs5y2020['a1519']\n",
    "sfnhood_acs5y2020['ab15'] = sfnhood_acs5y2020['ab5'] + sfnhood_acs5y2020['a59'] + sfnhood_acs5y2020['a1014']\n",
    "sfnhood_acs5y2020['ab10'] = sfnhood_acs5y2020['ab5'] + sfnhood_acs5y2020['a59']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Identify Variables\n",
    "# \n",
    "df = sfnhood_acs5y2020\n",
    "\n",
    "y = df['crime_rate']\n",
    "x = df[[\n",
    "    'chci',\n",
    "    # \"ab5\",   # Under 5 years\n",
    "    # \"a59\",   # 5 to 9 years\n",
    "    # \"a1014\", # 10 to 14 years\n",
    "    # \"a1519\", # 15 to 19 years\n",
    "    \"a2024\", # 20 to 24 years\n",
    "    \"a2534\", # 25 to 34 years\n",
    "    \"a3544\", # 35 to 44 years\n",
    "    \"a4554\", # 45 to 54 years\n",
    "    # \"a5559\", # 55 to 59 years\n",
    "    # \"a6064\", # 60 to 64 years\n",
    "    # \"a6574\", # 65 to 74 years\n",
    "    # \"a7584\", # 75 to 84 years\n",
    "    # \"a85a\", # 85 years and over\n",
    "    # 'a2034',\n",
    "    # 'a3554',\n",
    "    'a5564',\n",
    "    'a6574',\n",
    "    'a7584',\n",
    "    'a85a',\n",
    "    # 'a75a',\n",
    "    # \"mage\", # Median age (years)\n",
    "    'pop', \n",
    "    # 'poph_white', \n",
    "    'poph_black', \n",
    "    'poph_asian', \n",
    "    'poph_latino',\n",
    "    'poph_aindian', \n",
    "    'poph_pacific', \n",
    "    'poph_other', \n",
    "    'poph_twoplus',\n",
    "    'h_med_rooms',\n",
    "    'h_rental_units',\n",
    "    'h_rentel_size',\n",
    "    'h_med_rent',\n",
    "    'h_med_homeprice',\n",
    "    'eco_clf', \n",
    "    'eco_unemp', \n",
    "    'eco_med_hincome', \n",
    "    'eco_poverty'\n",
    "    ]] \n",
    "X = sm.add_constant(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from statsmodels.stats.outliers_influence import variance_inflation_factor\n",
    "#\n",
    "# Determine Variance\n",
    "#\n",
    "\n",
    "vif = pd.DataFrame()\n",
    "vif[\"VIF Factor\"] = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]\n",
    "vif[\"Variables\"] = X.columns\n",
    "vif.round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Linear Regression Model\n",
    "# \n",
    "eq01 = sm.OLS(y,X).fit()\n",
    "print(eq01.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Linear Regression Plots - Race\n",
    "#\n",
    "df = sfnhood_acs5y2020\n",
    "\n",
    "# Remove outliers\n",
    "# dindex = sfnhood_acs5y2020[sfnhood_acs5y2020['nhood'].isin(['Lincoln Park', 'Golden Gate Park', 'McLaren Park', 'The Farallones'])].index\n",
    "# df = sfnhood_acs5y2020.drop(dindex)\n",
    "\n",
    "fig, axes = plt.subplots(ncols = 4, nrows = 2, sharey = True, figsize=(16,9))\n",
    "\n",
    "fig.suptitle('Linear Regression Race Variables')\n",
    "\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_latino\", ax = axes[0, 0])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_white\", ax = axes[0,1 ])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_black\", ax = axes[0, 2])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_aindian\", ax = axes[0, 3])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_asian\", ax = axes[1, 0])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_pacific\", ax = axes[1, 1])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_other\", ax = axes[1, 2])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"poph_twoplus\", ax = axes[1, 3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Linear Regression Plots - Age\n",
    "#\n",
    "df = sfnhood_acs5y2020\n",
    "\n",
    "# Remove outliers\n",
    "# dindex = sfnhood_acs5y2020[sfnhood_acs5y2020['nhood'].isin(['Lincoln Park', 'Golden Gate Park', 'McLaren Park', 'The Farallones'])].index\n",
    "# df = sfnhood_acs5y2020.drop(dindex)\n",
    "\n",
    "fig, axes = plt.subplots(ncols = 4, nrows = 3, sharey = True, figsize=(16,12), )\n",
    "\n",
    "fig.suptitle('Linear Regression Age Variables')\n",
    "\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"ab5\", ax = axes[0, 0])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a59\", ax = axes[0,1 ])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a1014\", ax = axes[0, 2])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a1519\", ax = axes[0, 3])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a2024\", ax = axes[1, 0])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a2534\", ax = axes[1, 1])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a3544\", ax = axes[1, 2])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a4554\", ax = axes[1, 3])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a5559\", ax = axes[2, 0])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a6064\", ax = axes[2, 1])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a6574\", ax = axes[2, 2])\n",
    "sns.regplot(data = df, y=\"crime_rate\", x=\"a7584\", ax = axes[2, 3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Golden Gate Park\n",
    "# \n",
    "sfnhood_acs5y2020[['nhood','pop','incidents','crime_rate']].sort_values(['crime_rate'], ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Identify Variables - Without Outlier Neighborhood (Parks)\n",
    "# \n",
    "dindex = sfnhood_acs5y2020[sfnhood_acs5y2020['nhood'].isin(['Lincoln Park', 'Golden Gate Park', 'McLaren Park', 'The Farallones'])].index\n",
    "df = sfnhood_acs5y2020.drop(dindex)\n",
    "\n",
    "y = df['crime_rate']\n",
    "x = df[[\n",
    "    'chci',\n",
    "    # \"ab5\",   # Under 5 years\n",
    "    # \"a59\",   # 5 to 9 years\n",
    "    # \"a1014\", # 10 to 14 years\n",
    "    # \"a1519\", # 15 to 19 years\n",
    "    \"a2024\", # 20 to 24 years\n",
    "    \"a2534\", # 25 to 34 years\n",
    "    \"a3544\", # 35 to 44 years\n",
    "    \"a4554\", # 45 to 54 years\n",
    "    # \"a5559\", # 55 to 59 years\n",
    "    # \"a6064\", # 60 to 64 years\n",
    "    # \"a6574\", # 65 to 74 years\n",
    "    # \"a7584\", # 75 to 84 years\n",
    "    # \"a85a\", # 85 years and over\n",
    "    # 'a2034',\n",
    "    # 'a3554',\n",
    "    'a5564',\n",
    "    'a6574',\n",
    "    'a7584',\n",
    "    'a85a',\n",
    "    # 'a75a',\n",
    "    # \"mage\", # Median age (years)\n",
    "    'pop', \n",
    "    # 'poph_white', \n",
    "    'poph_black', \n",
    "    'poph_asian', \n",
    "    'poph_latino',\n",
    "    'poph_aindian', \n",
    "    'poph_pacific', \n",
    "    'poph_other', \n",
    "    'poph_twoplus',\n",
    "    'h_med_rooms',\n",
    "    'h_rental_units',\n",
    "    'h_rentel_size',\n",
    "    'h_med_rent',\n",
    "    'h_med_homeprice',\n",
    "    'eco_clf', \n",
    "    'eco_unemp', \n",
    "    'eco_med_hincome', \n",
    "    'eco_poverty'\n",
    "    ]] \n",
    "X = sm.add_constant(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Linear Regression Model - Without Outlier Neighborhood (Parks)\n",
    "# \n",
    "eq01 = sm.OLS(y,X).fit()\n",
    "print(eq01.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Next Steps;\n",
    "# Review other models to chose best performing\n",
    "# Review crime forecasting by time and location\n",
    "# Investigate Time Series forcasting\n",
    "# ..."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.8 ('.venv': venv)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "34c286749390f8c21bb8b05f488dea97bfcef8fa84c482cf1126481e686d0fd3"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}