Crime simulator.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(878049, 9)\n"
     ]
    }
   ],
   "source": [
    "# Read in SF crime data\n",
    "crime = pd.read_csv('~/Desktop/project1030/SF/SF.csv')\n",
    "print(crime.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    2015-05-13\n",
      "1    2015-05-13\n",
      "2    2015-05-13\n",
      "3    2015-05-13\n",
      "4    2015-05-13\n",
      "Name: datetime, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Create date variable from datetime\n",
    "crime['datetime'] = pd.to_datetime(crime['Dates'])\n",
    "dates_only = crime['datetime'].map(pd.Timestamp.date)\n",
    "print(dates_only[0:5])\n",
    "crime['date'] = dates_only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    2015\n",
      "1    2015\n",
      "2    2015\n",
      "3    2015\n",
      "4    2015\n",
      "Name: year, dtype: int64\n",
      "0    <built-in method time of Timestamp object at 0...\n",
      "1    <built-in method time of Timestamp object at 0...\n",
      "2    <built-in method time of Timestamp object at 0...\n",
      "3    <built-in method time of Timestamp object at 0...\n",
      "4    <built-in method time of Timestamp object at 0...\n",
      "Name: time, dtype: object\n",
      "0    5\n",
      "1    5\n",
      "2    5\n",
      "3    5\n",
      "4    5\n",
      "Name: month, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Extract years, months, and times\n",
    "years_only = crime['datetime'].map(lambda t: t.year)\n",
    "crime['year'] = years_only\n",
    "print(crime['year'].head())\n",
    "\n",
    "time_only = crime['datetime'].map(lambda t: t.time)\n",
    "crime['time'] = time_only\n",
    "print(crime['time'].head())\n",
    "\n",
    "months_only = crime['datetime'].map(lambda t: t.month)\n",
    "crime['month'] = months_only\n",
    "print(crime['month'].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# To simplify initially, keep only crime reports from 2003 to 2005\n",
    "#crime_new = crime[crime['year'].isin([2003, 2004, 2005])]\n",
    "\n",
    "# Confusing, but I kept this because I didn't want to change future references to \"crime_new\"\n",
    "crime_new = crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Sort by date\n",
    "crime_new.sort_values('datetime', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "878048   2003-01-06 00:01:00\n",
      "Name: datetime, dtype: datetime64[ns]\n",
      "0   2015-05-13 23:53:00\n",
      "Name: datetime, dtype: datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "print(crime_new['datetime'].head(1))\n",
    "print(crime_new['datetime'].tail(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import os\n",
    "\n",
    "# Read in csv file containing SF census tracts and corresponding MULTIPOLYGON objects\n",
    "data_path = '/Users/Sam/Desktop/project1030/SF'\n",
    "census_tr = pd.read_csv(os.path.join(data_path, 'Census_2010_Tracts.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   STATEFP10  TRACTCE10                                           the_geom  \\\n",
      "0          6      16500  MULTIPOLYGON (((-122.446471 37.775802, -122.44...   \n",
      "1          6      16400  MULTIPOLYGON (((-122.44033999999999 37.7765799...   \n",
      "2          6      16300  MULTIPOLYGON (((-122.429152 37.778006999999995...   \n",
      "3          6      16100  MULTIPOLYGON (((-122.428909 37.778039, -122.42...   \n",
      "4          6      16000  MULTIPOLYGON (((-122.420425 37.780583, -122.42...   \n",
      "\n",
      "   COUNTYFP10     GEOID10  NAME10        NAMELSAD10 MTFCC10 FUNCSTAT10  \\\n",
      "0          75  6075016500   165.0  Census Tract 165   G5020          S   \n",
      "1          75  6075016400   164.0  Census Tract 164   G5020          S   \n",
      "2          75  6075016300   163.0  Census Tract 163   G5020          S   \n",
      "3          75  6075016100   161.0  Census Tract 161   G5020          S   \n",
      "4          75  6075016000   160.0  Census Tract 160   G5020          S   \n",
      "\n",
      "   ALAND10  AWATER10  INTPTLAT10  INTPTLON10  \n",
      "0   370459         0   37.774196 -122.447788  \n",
      "1   309097         0   37.775099 -122.436973  \n",
      "2   245867         0   37.776046 -122.429551  \n",
      "3   368901         0   37.779983 -122.428663  \n",
      "4   158236         0   37.782336 -122.422484  \n"
     ]
    }
   ],
   "source": [
    "from geopandas import GeoDataFrame\n",
    "import shapely.wkt\n",
    "\n",
    "geometry = census_tr['the_geom'].map(shapely.wkt.loads)\n",
    "print(census_tr.head())\n",
    "\n",
    "crs = {'init': 'epsg:4326'}\n",
    "\n",
    "# Restrict to mainland SF \n",
    "census_tr = census_tr[census_tr['INTPTLON10'] > -122.6]\n",
    "\n",
    "sf_census_tracts = GeoDataFrame(census_tr, crs=crs, geometry=geometry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from shapely.geometry import Point\n",
    "\n",
    "# First convert all crime occurrences to Point objects\n",
    "crime_locs = [Point(xy) for xy in zip(crime_new['X'], crime_new['Y'])]\n",
    "crime_locs_df = GeoDataFrame(crime_locs, crs=crs, geometry=crime_locs)\n",
    "\n",
    "# Add locations to original dataframe\n",
    "crime_new['locs'] = crime_locs\n",
    "\n",
    "# Execute spatial join of crimes with census tract boundaries \n",
    "crime_census = gpd.sjoin(crime_locs_df, sf_census_tracts, how=\"inner\", op='within')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/lib/python3.6/site-packages/ipykernel_launcher.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  import sys\n",
      "/anaconda/lib/python3.6/site-packages/ipykernel_launcher.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "# Extract X and Y coordinates\n",
    "x_coords = crime_census['geometry'].apply(lambda p: p.x)\n",
    "y_coords = crime_census['geometry'].apply(lambda p: p.y)\n",
    "\n",
    "# Create new truncated df containing only Point object and census tract name\n",
    "crime_census_trunc = crime_census[['NAME10', 'the_geom']]\n",
    "crime_census_trunc['X'] = x_coords\n",
    "crime_census_trunc['Y'] = y_coords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(877826, 4)\n",
      "(878049, 15)\n",
      "(34222, 4)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/lib/python3.6/site-packages/ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "print(crime_census_trunc.shape)\n",
    "print(crime_new.shape)\n",
    "\n",
    "# Remove duplicates from spatially merged dataset (crime_census_trunc)\n",
    "crime_census_trunc.drop_duplicates(inplace=True)\n",
    "print(crime_census_trunc.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Now, merge on census tract information to crime_new dataset, joining on the coordinate variables\n",
    "crime_merged = pd.merge(crime_new, crime_census_trunc, how='inner', on=['X', 'Y'])\n",
    "crime_merged.rename(columns= {'NAME10': 'census_tr', 'the_geom': 'census_tr_poly'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Function to calculate difference between two datetimes: datetime_2 (later) and datetime_init (earlier)\n",
    "def calc_time_delta(datetime_2, datetime_init):\n",
    "    delta = datetime_2 - datetime_init\n",
    "    print(type(delta))\n",
    "    mins = delta.astype('timedelta64[m]')\n",
    "    return(mins / np.timedelta64(1, 'm'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/lib/python3.6/site-packages/ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"\n",
      "/anaconda/lib/python3.6/site-packages/ipykernel_launcher.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 Category DayOfWeek            datetime        date  year  \\\n",
      "0  FORGERY/COUNTERFEITING    Monday 2003-01-06 00:01:00  2003-01-06  2003   \n",
      "1           LARCENY/THEFT    Monday 2003-01-06 00:01:00  2003-01-06  2003   \n",
      "2            NON-CRIMINAL    Sunday 2005-07-17 12:00:00  2005-07-17  2005   \n",
      "3               VANDALISM    Monday 2003-01-06 00:01:00  2003-01-06  2003   \n",
      "4           LARCENY/THEFT  Saturday 2003-01-25 12:00:00  2003-01-25  2003   \n",
      "\n",
      "                                                time  month  census_tr  \\\n",
      "0  <built-in method time of Timestamp object at 0...      1     9809.0   \n",
      "1  <built-in method time of Timestamp object at 0...      1      311.0   \n",
      "2  <built-in method time of Timestamp object at 0...      7      311.0   \n",
      "3  <built-in method time of Timestamp object at 0...      1      615.0   \n",
      "4  <built-in method time of Timestamp object at 0...      1      615.0   \n",
      "\n",
      "   non_payment  illegal_use  eviction  \n",
      "0            0            0         0  \n",
      "1            0            0         0  \n",
      "2            0            0         0  \n",
      "3            0            0         0  \n",
      "4            0            0         0  \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/lib/python3.6/site-packages/ipykernel_launcher.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "# Crime dataset with unneccesary features removed where we will engineer new features\n",
    "crime_engin_temp = crime_merged[['Category', 'DayOfWeek', 'datetime', 'date', 'year', \\\n",
    "                            'time', 'month', 'census_tr']]\n",
    "\n",
    "# Add eviction-specific columns (all zeros for crime observations)\n",
    "crime_engin_temp['non_payment'] = 0\n",
    "crime_engin_temp['illegal_use'] = 0\n",
    "crime_engin_temp['eviction'] = 0\n",
    "\n",
    "print(crime_engin_temp.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         date  census_tr  non_payment  illegal_use  eviction    datetime  \\\n",
      "0  2003-01-02     158.02            0            0         1  2003-01-02   \n",
      "1  2003-01-02     228.01            0            0         1  2003-01-02   \n",
      "2  2003-01-02     302.02            0            0         1  2003-01-02   \n",
      "3  2003-01-02     232.00            0            0         1  2003-01-02   \n",
      "4  2003-01-03     204.01            0            0         1  2003-01-03   \n",
      "\n",
      "                                                time  Category  year  month  \\\n",
      "0  <built-in method time of Timestamp object at 0...  EVICTION  2003      1   \n",
      "1  <built-in method time of Timestamp object at 0...  EVICTION  2003      1   \n",
      "2  <built-in method time of Timestamp object at 0...  EVICTION  2003      1   \n",
      "3  <built-in method time of Timestamp object at 0...  EVICTION  2003      1   \n",
      "4  <built-in method time of Timestamp object at 0...  EVICTION  2003      1   \n",
      "\n",
      "   DayOfWeek  \n",
      "0        NaN  \n",
      "1        NaN  \n",
      "2        NaN  \n",
      "3        NaN  \n",
      "4        NaN  \n"
     ]
    }
   ],
   "source": [
    "# Read in processed eviction data\n",
    "evict = pd.read_csv('~/Desktop/project1030/SF/evict_processed.csv')\n",
    "print(evict.head())\n",
    "\n",
    "evict.drop(columns=['datetime'], inplace=True)\n",
    "\n",
    "# Convert date to datetime\n",
    "def date_to_datetime(date):\n",
    "    #return str(date)\n",
    "    return pd.to_datetime(str(date))\n",
    "\n",
    "#date_to_datetime(evict['date'].values[0])\n",
    "evict['datetime'] = evict['date'].apply(lambda x: date_to_datetime(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "crime_engin = crime_engin_temp.append(evict).sort_values(by='datetime')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Two functions that simply define property and violent crimes based on Category variable\n",
    "def is_property_crime(crime_type):\n",
    "    return (crime_type in ['BURGLARY', 'LARCENY/THEFT', 'VEHICLE THEFT', 'RECOVERED VEHICLE', \\\n",
    "                      'ARSON', 'VANDALISM', 'STOLEN PROPERTY', 'EMBEZZLEMENT'])\n",
    "\n",
    "def is_violent_crime(crime_type):\n",
    "    return (crime_type in ['ASSAULT', 'SEX OFFENSES FORCIBLE', 'KIDNAPPING'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# Create categorical variables for crimes by mapping above functions over Category column\n",
    "crime_engin['property_crime'] = crime_engin['Category'].apply(is_property_crime).astype(int)\n",
    "crime_engin['violent_crime'] = crime_engin['Category'].apply(is_violent_crime).astype(int)\n",
    "\n",
    "crime_engin['robbery'] = (crime_engin['Category'] == 'ROBBERY').astype(int)\n",
    "\n",
    "crime_engin['other_crime'] = crime_engin['property_crime'] + crime_engin['violent_crime'] + crime_engin['robbery'] + 1\n",
    "crime_engin['other_crime'].replace(2, 0, inplace=True)\n",
    "\n",
    "crime_engin['any_crime'] = crime_engin['property_crime'] + crime_engin['violent_crime'] + \\\n",
    "                            crime_engin['robbery'] + crime_engin['other_crime']\n",
    "\n",
    "# The total for any row should be 1 (has to fall into one of the categories!)\n",
    "assert(crime_engin['any_crime'].unique() == 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "# Create time index = minutes elapsed since beginning of data\n",
    "crime_engin['min_elapsed'] = np.apply_along_axis(func1d = calc_time_delta, axis = 0, \\\n",
    "                             arr=crime_engin['datetime'].values, datetime_init=crime_engin['datetime'].values[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Combine slice and sum functions\n",
    "def return_sum_over_slice(df, start_time, time_window, census_tract):\n",
    "    \n",
    "    if (start_time-time_window) >= 0:\n",
    "        sliced_df = df[(df['min_elapsed'] < start_time) & (df['min_elapsed'] >= (start_time-time_window))]\n",
    "        \n",
    "    else:\n",
    "        sliced_df = df[(df['min_elapsed'] < start_time)]\n",
    "        \n",
    "    # Restrict only to crimes in that census tract\n",
    "    result = sliced_df[sliced_df['census_tr'] == census_tract]\n",
    "    \n",
    "    if (result.empty==False):\n",
    "        return (result['property_crime'].sum(), result['violent_crime'].sum(), result['robbery'].sum(), \\\n",
    "                result['other_crime'].sum(), result['any_crime'].sum(), result['non_payment'].sum(), \\\n",
    "                result['illegal_use'].sum(), result['eviction'].sum())\n",
    "    else:\n",
    "        return (0, 0, 0, 0, 0, 0, 0, 0)\n",
    "    \n",
    "# Function to convert counts to binary (only using for future crimes as of now)\n",
    "def convert_to_binary(val):\n",
    "    return min(val, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "from dateutil.parser import parse\n",
    "\n",
    "# To calculate features, the user input date must be two weeks after the start date of the \n",
    "# crime data (Jan 6 2003) and an hour before the end of the data (May 13 2015)\n",
    "\n",
    "# datetime_start: 2003-01-06 00:01:00\n",
    "# datetime_end: 2015-05-13 23:53:00\n",
    "\n",
    "min_date = datetime.strptime('2003-01-20 00:01:00', '%Y-%m-%d %H:%M:%S')\n",
    "max_date = datetime.strptime('2015-05-30 22:52:00', '%Y-%m-%d %H:%M:%S') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Idea: Given a datetime within our range (> 2 weeks after data start and reasonably far before the end of the dataset), calculate prior crime/eviction info for each census tract, and use previously trained classifier to predict probabilities of a crime occuring in the next hour for each tract. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**USER API: INPUT A DATE AND NUMBER OF FORECAST PERIODS IN ORDER TO PRODUCE SIMULATED CRIME DATA**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter a year from 2006 to 2015: 2006\n",
      "Enter a month from 1 to 12: 1\n",
      "Enter a day from 1 to 31: 1\n",
      "Enter an hour number from 0 to 23: 0\n",
      "Enter a minute number from 0 to 59: 0\n",
      "How many periods in the future would you like to forecast?72\n",
      "The start date is:  2006-01-01 00:00:00\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime, date, time, timedelta\n",
    "\n",
    "# Parameters for start date\n",
    "year = int(input(\"Enter a year from 2006 to 2015: \"))\n",
    "month = int(input(\"Enter a month from 1 to 12: \"))\n",
    "day = int(input(\"Enter a day from 1 to 31: \"))\n",
    "hour = int(input(\"Enter an hour number from 0 to 23: \"))\n",
    "minute = int(input(\"Enter a minute number from 0 to 59: \"))\n",
    "\n",
    "# Number of periods in the future to forecast\n",
    "forecast_periods = int(input(\"How many periods in the future would you like to forecast?\"))\n",
    "\n",
    "# Arbitary datetime to start off with. \n",
    "d = date(year, month, day)\n",
    "t = time(hour, minute)\n",
    "\n",
    "start_date = datetime.combine(d, t)\n",
    "\n",
    "if (start_date < min_date) or (start_date + timedelta(hours=forecast_periods) > max_date):\n",
    "    print(\"Date or number of periods out of bounds, please try again.\")\n",
    "\n",
    "print('The start date is: ', start_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  date\n",
      "0  2006-01-01 00:00:00\n",
      "1  2006-01-01 01:00:00\n",
      "2  2006-01-01 02:00:00\n",
      "3  2006-01-01 03:00:00\n",
      "4  2006-01-01 04:00:00\n",
      "5  2006-01-01 05:00:00\n",
      "6  2006-01-01 06:00:00\n",
      "7  2006-01-01 07:00:00\n",
      "8  2006-01-01 08:00:00\n",
      "9  2006-01-01 09:00:00\n",
      "10 2006-01-01 10:00:00\n",
      "11 2006-01-01 11:00:00\n",
      "12 2006-01-01 12:00:00\n",
      "13 2006-01-01 13:00:00\n",
      "14 2006-01-01 14:00:00\n",
      "15 2006-01-01 15:00:00\n",
      "16 2006-01-01 16:00:00\n",
      "17 2006-01-01 17:00:00\n",
      "18 2006-01-01 18:00:00\n",
      "19 2006-01-01 19:00:00\n",
      "20 2006-01-01 20:00:00\n",
      "21 2006-01-01 21:00:00\n",
      "22 2006-01-01 22:00:00\n",
      "23 2006-01-01 23:00:00\n",
      "24 2006-01-02 00:00:00\n",
      "25 2006-01-02 01:00:00\n",
      "26 2006-01-02 02:00:00\n",
      "27 2006-01-02 03:00:00\n",
      "28 2006-01-02 04:00:00\n",
      "29 2006-01-02 05:00:00\n",
      "..                 ...\n",
      "42 2006-01-02 18:00:00\n",
      "43 2006-01-02 19:00:00\n",
      "44 2006-01-02 20:00:00\n",
      "45 2006-01-02 21:00:00\n",
      "46 2006-01-02 22:00:00\n",
      "47 2006-01-02 23:00:00\n",
      "48 2006-01-03 00:00:00\n",
      "49 2006-01-03 01:00:00\n",
      "50 2006-01-03 02:00:00\n",
      "51 2006-01-03 03:00:00\n",
      "52 2006-01-03 04:00:00\n",
      "53 2006-01-03 05:00:00\n",
      "54 2006-01-03 06:00:00\n",
      "55 2006-01-03 07:00:00\n",
      "56 2006-01-03 08:00:00\n",
      "57 2006-01-03 09:00:00\n",
      "58 2006-01-03 10:00:00\n",
      "59 2006-01-03 11:00:00\n",
      "60 2006-01-03 12:00:00\n",
      "61 2006-01-03 13:00:00\n",
      "62 2006-01-03 14:00:00\n",
      "63 2006-01-03 15:00:00\n",
      "64 2006-01-03 16:00:00\n",
      "65 2006-01-03 17:00:00\n",
      "66 2006-01-03 18:00:00\n",
      "67 2006-01-03 19:00:00\n",
      "68 2006-01-03 20:00:00\n",
      "69 2006-01-03 21:00:00\n",
      "70 2006-01-03 22:00:00\n",
      "71 2006-01-03 23:00:00\n",
      "\n",
      "[72 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "# Create dataframe\n",
    "sim = pd.DataFrame(columns=['date'])\n",
    "sim['date'] = pd.date_range(start=start_date, periods=forecast_periods, freq='H')\n",
    "print(sim)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Extract day of week, month, year, and hour from each date\n",
    "sim['month'] = sim['date'].map(lambda t: t.month)\n",
    "sim['year'] = sim['date'].map(lambda t: t.year)\n",
    "sim['dow'] = sim['date'].map(lambda t: t.weekday())\n",
    "sim['hour'] = sim['date'].map(lambda t: t.hour)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# One hot encode year, month, and hour\n",
    "sim['dow_1'] = (sim['dow'] == 0).astype(int)\n",
    "sim['dow_2'] = (sim['dow'] == 1).astype(int)\n",
    "sim['dow_3'] = (sim['dow'] == 2).astype(int)\n",
    "sim['dow_4'] = (sim['dow'] == 3).astype(int)\n",
    "sim['dow_5'] = (sim['dow'] == 4).astype(int)\n",
    "sim['dow_6'] = (sim['dow'] == 5).astype(int)\n",
    "sim['dow_7'] = (sim['dow'] == 6).astype(int)\n",
    "\n",
    "# Month\n",
    "sim['month_1'] = (sim['month'] == 1).astype(int)\n",
    "sim['month_2'] = (sim['month'] == 2).astype(int)\n",
    "sim['month_3'] = (sim['month'] == 3).astype(int)\n",
    "sim['month_4'] = (sim['month'] == 4).astype(int)\n",
    "sim['month_5'] = (sim['month'] == 5).astype(int)\n",
    "sim['month_6'] = (sim['month'] == 6).astype(int)\n",
    "sim['month_7'] = (sim['month'] == 7).astype(int)\n",
    "sim['month_8'] = (sim['month'] == 8).astype(int)\n",
    "sim['month_9'] = (sim['month'] == 9).astype(int)\n",
    "sim['month_10'] = (sim['month'] == 10).astype(int)\n",
    "sim['month_11'] = (sim['month'] == 11).astype(int)\n",
    "sim['month_12'] = (sim['month'] == 12).astype(int)\n",
    "\n",
    "# Year\n",
    "sim['year_2003'] = (sim['year'] == 2003).astype(int)\n",
    "sim['year_2004'] = (sim['year'] == 2004).astype(int)\n",
    "sim['year_2005'] = (sim['year'] == 2005).astype(int)\n",
    "sim['year_2006'] = (sim['year'] == 2006).astype(int)\n",
    "sim['year_2007'] = (sim['year'] == 2007).astype(int)\n",
    "sim['year_2008'] = (sim['year'] == 2008).astype(int) \n",
    "sim['year_2009'] = (sim['year'] == 2009).astype(int)\n",
    "sim['year_2010'] = (sim['year'] == 2010).astype(int)\n",
    "sim['year_2011'] = (sim['year'] == 2011).astype(int)\n",
    "sim['year_2012'] = (sim['year'] == 2012).astype(int)\n",
    "sim['year_2013'] = (sim['year'] == 2013).astype(int)\n",
    "sim['year_2014'] = (sim['year'] == 2014).astype(int)\n",
    "sim['year_2015'] = (sim['year'] == 2015).astype(int)\n",
    "\n",
    "sim['hour_1'] = (sim['hour'] == 1).astype(int)\n",
    "sim['hour_2'] = (sim['hour'] == 2).astype(int)\n",
    "sim['hour_3'] = (sim['hour'] == 3).astype(int)\n",
    "sim['hour_4'] = (sim['hour'] == 4).astype(int) \n",
    "sim['hour_5'] = (sim['hour'] == 5).astype(int) \n",
    "sim['hour_6'] = (sim['hour'] == 6).astype(int) \n",
    "sim['hour_7'] = (sim['hour'] == 7).astype(int) \n",
    "sim['hour_8'] = (sim['hour'] == 8).astype(int) \n",
    "sim['hour_9'] = (sim['hour'] == 9).astype(int) \n",
    "sim['hour_10'] = (sim['hour'] == 10).astype(int) \n",
    "sim['hour_11'] = (sim['hour'] == 11).astype(int)\n",
    "sim['hour_12'] = (sim['hour'] == 12).astype(int) \n",
    "sim['hour_13'] = (sim['hour'] == 13).astype(int) \n",
    "sim['hour_14'] = (sim['hour'] == 14).astype(int) \n",
    "sim['hour_15'] = (sim['hour'] == 15).astype(int) \n",
    "sim['hour_16'] = (sim['hour'] == 16).astype(int)\n",
    "sim['hour_17'] = (sim['hour'] == 17).astype(int) \n",
    "sim['hour_18'] = (sim['hour'] == 18).astype(int) \n",
    "sim['hour_19'] = (sim['hour'] == 19).astype(int) \n",
    "sim['hour_20'] = (sim['hour'] == 20).astype(int) \n",
    "sim['hour_21'] = (sim['hour'] == 21).astype(int) \n",
    "sim['hour_22'] = (sim['hour'] == 22).astype(int) \n",
    "sim['hour_23'] = (sim['hour'] == 23).astype(int) \n",
    "sim['hour_24'] = (sim['hour'] == 24).astype(int) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create artificial key for m:m merge\n",
    "sim['key'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "195\n",
      "[ 232.    158.02  228.01  302.02  204.01  477.02  156.    311.    218.\n",
      "  122.02]\n"
     ]
    }
   ],
   "source": [
    "# Now create df of census tracts and artifical key\n",
    "census_tracts_list = crime_engin.census_tr.unique()\n",
    "print(len(census_tracts_list))\n",
    "print(census_tracts_list[0:10])\n",
    "\n",
    "census_df = pd.DataFrame(columns=['census_tract', 'key'])\n",
    "census_df['census_tract'] = census_tracts_list\n",
    "census_df['key'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   census_tract                date  month  year  dow  hour  dow_1  dow_2  \\\n",
      "0         232.0 2006-01-01 00:00:00      1  2006    6     0      0      0   \n",
      "1         232.0 2006-01-01 01:00:00      1  2006    6     1      0      0   \n",
      "2         232.0 2006-01-01 02:00:00      1  2006    6     2      0      0   \n",
      "3         232.0 2006-01-01 03:00:00      1  2006    6     3      0      0   \n",
      "4         232.0 2006-01-01 04:00:00      1  2006    6     4      0      0   \n",
      "\n",
      "   dow_3  dow_4   ...     hour_15  hour_16  hour_17  hour_18  hour_19  \\\n",
      "0      0      0   ...           0        0        0        0        0   \n",
      "1      0      0   ...           0        0        0        0        0   \n",
      "2      0      0   ...           0        0        0        0        0   \n",
      "3      0      0   ...           0        0        0        0        0   \n",
      "4      0      0   ...           0        0        0        0        0   \n",
      "\n",
      "   hour_20  hour_21  hour_22  hour_23  hour_24  \n",
      "0        0        0        0        0        0  \n",
      "1        0        0        0        0        0  \n",
      "2        0        0        0        0        0  \n",
      "3        0        0        0        0        0  \n",
      "4        0        0        0        0        0  \n",
      "\n",
      "[5 rows x 62 columns]\n",
      "(14040, 62)\n"
     ]
    }
   ],
   "source": [
    "# Merge two dfs to get Cartesian product\n",
    "merged = pd.merge(census_df, sim, on='key')\n",
    "merged.drop(columns=['key'], inplace=True)\n",
    "print(merged.head())\n",
    "print(merged.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'>\n",
      "   census_tract  min_elapsed  hour\n",
      "0         232.0    1576800.0     0\n",
      "1         232.0    1576860.0     1\n",
      "2         232.0    1576920.0     2\n",
      "3         232.0    1576980.0     3\n",
      "4         232.0    1577040.0     4\n"
     ]
    }
   ],
   "source": [
    "# Add minutes elapsed since beginning of data\n",
    "begin_time = crime_engin['datetime'].values[0]\n",
    "\n",
    "merged['min_elapsed'] = np.apply_along_axis(func1d = calc_time_delta, axis = 0, \\\n",
    "                             arr=merged['date'].values, datetime_init=crime_engin['datetime'].values[0])\n",
    "print(merged[['census_tract', 'min_elapsed', 'hour']].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "merged['start_and_census'] = list(zip(merged['min_elapsed'], merged['census_tract']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time to run: 654.5396540164948 seconds\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "start = time.time()\n",
    "\n",
    "df_1_hour = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=60.0, census_tract = x[1])[0:5], merged['start_and_census'])])\n",
    "\n",
    "df_2_hours = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=120.0, census_tract = x[1])[0:5], merged['start_and_census'])])\n",
    "\n",
    "df_3_hours = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=180.0, census_tract = x[1])[0:5], merged['start_and_census'])])\n",
    "\n",
    "df_4_hours = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=240.0, census_tract = x[1])[0:5], merged['start_and_census'])])\n",
    "\n",
    "df_5_hours = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=300.0, census_tract = x[1])[0:5], merged['start_and_census'])])\n",
    "\n",
    "df_1_day = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=1440.0, census_tract = x[1]), merged['start_and_census'])])\n",
    "\n",
    "df_2_days = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=2880.0, census_tract = x[1]), merged['start_and_census'])])\n",
    "\n",
    "df_3_days = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=4320.0, census_tract = x[1]), merged['start_and_census'])])\n",
    "\n",
    "df_7_days = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=10080.0, census_tract = x[1]), merged['start_and_census'])])\n",
    "\n",
    "df_14_days = pd.DataFrame([x for x in map(lambda x: return_sum_over_slice(crime_engin, start_time=x[0], \\\n",
    "             time_window=20160.0, census_tract = x[1]), merged['start_and_census'])])\n",
    "\n",
    "# Give names to columns\n",
    "df_1_hour.columns = ['property_last_1_hour', 'violent_last_1_hour', 'robbery_last_1_hour', 'other_last_1_hour', 'any_last_1_hour']\n",
    "df_2_hours.columns = ['property_last_2_hours', 'violent_last_2_hours', 'robbery_last_2_hours', 'other_last_2_hours', 'any_last_2_hours']\n",
    "df_3_hours.columns = ['property_last_3_hours', 'violent_last_3_hours', 'robbery_last_3_hours', 'other_last_3_hours', 'any_last_3_hours']\n",
    "df_4_hours.columns = ['property_last_4_hours', 'violent_last_4_hours', 'robbery_last_4_hours', 'other_last_4_hours', 'any_last_4_hours']\n",
    "df_5_hours.columns = ['property_last_5_hours', 'violent_last_5_hours', 'robbery_last_5_hours', 'other_last_5_hours', 'any_last_5_hours']\n",
    "df_1_day.columns = ['property_last_1_day', 'violent_last_1_day', 'robbery_last_1_day', 'other_last_1_day', 'any_last_1_day', 'nonpay_last_1_day', 'illegal_last_1_day', 'evict_last_1_day']\n",
    "df_2_days.columns = ['property_last_2_days', 'violent_last_2_days', 'robbery_last_2_days', 'other_last_2_days', 'any_last_2_days', 'nonpay_last_2_days', 'illegal_last_2_days', 'evict_last_2_days']\n",
    "df_3_days.columns = ['property_last_3_days', 'violent_last_3_days', 'robbery_last_3_days', 'other_last_3_days', 'any_last_3_days', 'nonpay_last_3_days', 'illegal_last_3_days', 'evict_last_3_days']\n",
    "df_7_days.columns = ['property_last_7_days', 'violent_last_7_days', 'robbery_last_7_days', 'other_last_7_days', 'any_last_7_days', 'nonpay_last_7_days', 'illegal_last_7_days', 'evict_last_7_days']\n",
    "df_14_days.columns = ['property_last_14_days', 'violent_last_14_days', 'robbery_last_14_days', 'other_last_14_days', 'any_last_14_days', 'nonpay_last_14_days', 'illegal_last_14_days', 'evict_last_14_days']\n",
    "\n",
    "end = time.time()\n",
    "print(\"Time to run:\", end-start, \"seconds\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14040, 5)\n"
     ]
    }
   ],
   "source": [
    "print(df_1_hour.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Future crimes\n",
    "merged['future_property_1_hour'] = [convert_to_binary(x) for x in map(lambda x: return_sum_over_slice(df=crime_engin, \\\n",
    "                                        start_time=(x[0]+60.0), time_window=59.99, \\\n",
    "                                        census_tract = x[1])[0], merged['start_and_census'])]\n",
    "\n",
    "merged['future_violent_1_hour'] = [convert_to_binary(x) for x in map(lambda x: return_sum_over_slice(df=crime_engin, \\\n",
    "                                        start_time=(x[0]+60.0), time_window=59.99, \\\n",
    "                                        census_tract = x[1])[1], merged['start_and_census'])]\n",
    "\n",
    "merged['future_robbery_1_hour'] = [convert_to_binary(x) for x in map(lambda x: return_sum_over_slice(df=crime_engin, \\\n",
    "                                        start_time=(x[0]+60.0), time_window=59.99, \\\n",
    "                                        census_tract = x[1])[2], merged['start_and_census'])]\n",
    "\n",
    "merged['future_other_1_hour'] = [convert_to_binary(x) for x in map(lambda x: return_sum_over_slice(df=crime_engin, \\\n",
    "                                        start_time=(x[0]+60.0), time_window=59.99, census_tract = x[1])[3], merged['start_and_census'])]\n",
    "\n",
    "merged['future_any_1_hour'] = [convert_to_binary(x) for x in map(lambda x: return_sum_over_slice(df=crime_engin, \\\n",
    "                                        start_time=(x[0]+60.0), time_window=59.99, \\\n",
    "                                        census_tract = x[1])[4], merged['start_and_census'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Merge on new features \n",
    "merged.reset_index(inplace=True, drop=True)\n",
    "sim_concat = pd.concat([merged, df_1_hour, df_2_hours, df_3_hours, df_4_hours, df_5_hours, \\\n",
    "                          df_1_day, df_2_days, df_3_days, df_7_days, df_14_days], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14040, 134)\n",
      "['census_tract', 'date', 'month', 'year', 'dow', 'hour', 'dow_1', 'dow_2', 'dow_3', 'dow_4', 'dow_5', 'dow_6', 'dow_7', 'month_1', 'month_2', 'month_3', 'month_4', 'month_5', 'month_6', 'month_7', 'month_8', 'month_9', 'month_10', 'month_11', 'month_12', 'year_2003', 'year_2004', 'year_2005', 'year_2006', 'year_2007', 'year_2008', 'year_2009', 'year_2010', 'year_2011', 'year_2012', 'year_2013', 'year_2014', 'year_2015', 'hour_1', 'hour_2', 'hour_3', 'hour_4', 'hour_5', 'hour_6', 'hour_7', 'hour_8', 'hour_9', 'hour_10', 'hour_11', 'hour_12', 'hour_13', 'hour_14', 'hour_15', 'hour_16', 'hour_17', 'hour_18', 'hour_19', 'hour_20', 'hour_21', 'hour_22', 'hour_23', 'hour_24', 'min_elapsed', 'start_and_census', 'future_property_1_hour', 'future_violent_1_hour', 'future_robbery_1_hour', 'future_other_1_hour', 'future_any_1_hour', 'property_last_1_hour', 'violent_last_1_hour', 'robbery_last_1_hour', 'other_last_1_hour', 'any_last_1_hour', 'property_last_2_hours', 'violent_last_2_hours', 'robbery_last_2_hours', 'other_last_2_hours', 'any_last_2_hours', 'property_last_3_hours', 'violent_last_3_hours', 'robbery_last_3_hours', 'other_last_3_hours', 'any_last_3_hours', 'property_last_4_hours', 'violent_last_4_hours', 'robbery_last_4_hours', 'other_last_4_hours', 'any_last_4_hours', 'property_last_5_hours', 'violent_last_5_hours', 'robbery_last_5_hours', 'other_last_5_hours', 'any_last_5_hours', 'property_last_1_day', 'violent_last_1_day', 'robbery_last_1_day', 'other_last_1_day', 'any_last_1_day', 'nonpay_last_1_day', 'illegal_last_1_day', 'evict_last_1_day', 'property_last_2_days', 'violent_last_2_days', 'robbery_last_2_days', 'other_last_2_days', 'any_last_2_days', 'nonpay_last_2_days', 'illegal_last_2_days', 'evict_last_2_days', 'property_last_3_days', 'violent_last_3_days', 'robbery_last_3_days', 'other_last_3_days', 'any_last_3_days', 'nonpay_last_3_days', 'illegal_last_3_days', 'evict_last_3_days', 'property_last_7_days', 'violent_last_7_days', 'robbery_last_7_days', 'other_last_7_days', 'any_last_7_days', 'nonpay_last_7_days', 'illegal_last_7_days', 'evict_last_7_days', 'property_last_14_days', 'violent_last_14_days', 'robbery_last_14_days', 'other_last_14_days', 'any_last_14_days', 'nonpay_last_14_days', 'illegal_last_14_days', 'evict_last_14_days']\n"
     ]
    }
   ],
   "source": [
    "print(sim_concat.shape)\n",
    "print(list(sim_concat))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14040, 329)\n"
     ]
    }
   ],
   "source": [
    "census_one_hot = pd.get_dummies(sim_concat['census_tract'], prefix='ct')\n",
    "sim_2 = pd.concat([sim_concat, census_one_hot], axis=1)\n",
    "print(sim_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14040, 322)\n",
      "['census_tract', 'dow_1', 'dow_2', 'dow_3', 'dow_4', 'dow_5', 'dow_6', 'dow_7', 'month_1', 'month_2', 'month_3', 'month_4', 'month_5', 'month_6', 'month_7', 'month_8', 'month_9', 'month_10', 'month_11', 'month_12', 'year_2003', 'year_2004', 'year_2005', 'year_2006', 'year_2007', 'year_2008', 'year_2009', 'year_2010', 'year_2011', 'year_2012', 'year_2013', 'year_2014', 'year_2015', 'hour_1', 'hour_2', 'hour_3', 'hour_4', 'hour_5', 'hour_6', 'hour_7', 'hour_8', 'hour_9', 'hour_10', 'hour_11', 'hour_12', 'hour_13', 'hour_14', 'hour_15', 'hour_16', 'hour_17', 'hour_18', 'hour_19', 'hour_20', 'hour_21', 'hour_22', 'hour_23', 'hour_24', 'future_property_1_hour', 'future_violent_1_hour', 'future_robbery_1_hour', 'future_other_1_hour', 'future_any_1_hour', 'property_last_1_hour', 'violent_last_1_hour', 'robbery_last_1_hour', 'other_last_1_hour', 'any_last_1_hour', 'property_last_2_hours', 'violent_last_2_hours', 'robbery_last_2_hours', 'other_last_2_hours', 'any_last_2_hours', 'property_last_3_hours', 'violent_last_3_hours', 'robbery_last_3_hours', 'other_last_3_hours', 'any_last_3_hours', 'property_last_4_hours', 'violent_last_4_hours', 'robbery_last_4_hours', 'other_last_4_hours', 'any_last_4_hours', 'property_last_5_hours', 'violent_last_5_hours', 'robbery_last_5_hours', 'other_last_5_hours', 'any_last_5_hours', 'property_last_1_day', 'violent_last_1_day', 'robbery_last_1_day', 'other_last_1_day', 'any_last_1_day', 'nonpay_last_1_day', 'illegal_last_1_day', 'evict_last_1_day', 'property_last_2_days', 'violent_last_2_days', 'robbery_last_2_days', 'other_last_2_days', 'any_last_2_days', 'nonpay_last_2_days', 'illegal_last_2_days', 'evict_last_2_days', 'property_last_3_days', 'violent_last_3_days', 'robbery_last_3_days', 'other_last_3_days', 'any_last_3_days', 'nonpay_last_3_days', 'illegal_last_3_days', 'evict_last_3_days', 'property_last_7_days', 'violent_last_7_days', 'robbery_last_7_days', 'other_last_7_days', 'any_last_7_days', 'nonpay_last_7_days', 'illegal_last_7_days', 'evict_last_7_days', 'property_last_14_days', 'violent_last_14_days', 'robbery_last_14_days', 'other_last_14_days', 'any_last_14_days', 'nonpay_last_14_days', 'illegal_last_14_days', 'evict_last_14_days', 'ct_101.0', 'ct_102.0', 'ct_103.0', 'ct_104.0', 'ct_105.0', 'ct_106.0', 'ct_107.0', 'ct_108.0', 'ct_109.0', 'ct_110.0', 'ct_111.0', 'ct_112.0', 'ct_113.0', 'ct_117.0', 'ct_118.0', 'ct_119.01', 'ct_119.02', 'ct_120.0', 'ct_121.0', 'ct_122.01', 'ct_122.02', 'ct_123.01', 'ct_123.02', 'ct_124.01', 'ct_124.02', 'ct_125.01', 'ct_125.02', 'ct_126.01', 'ct_126.02', 'ct_127.0', 'ct_128.0', 'ct_129.01', 'ct_129.02', 'ct_130.0', 'ct_131.01', 'ct_131.02', 'ct_132.0', 'ct_133.0', 'ct_134.0', 'ct_135.0', 'ct_151.0', 'ct_152.0', 'ct_153.0', 'ct_154.0', 'ct_155.0', 'ct_156.0', 'ct_157.0', 'ct_158.01', 'ct_158.02', 'ct_159.0', 'ct_160.0', 'ct_161.0', 'ct_162.0', 'ct_163.0', 'ct_164.0', 'ct_165.0', 'ct_166.0', 'ct_167.0', 'ct_168.01', 'ct_168.02', 'ct_169.0', 'ct_170.0', 'ct_171.01', 'ct_171.02', 'ct_176.01', 'ct_177.0', 'ct_178.01', 'ct_178.02', 'ct_179.02', 'ct_180.0', 'ct_201.0', 'ct_202.0', 'ct_203.0', 'ct_204.01', 'ct_204.02', 'ct_205.0', 'ct_206.0', 'ct_207.0', 'ct_208.0', 'ct_209.0', 'ct_210.0', 'ct_211.0', 'ct_212.0', 'ct_213.0', 'ct_214.0', 'ct_215.0', 'ct_216.0', 'ct_217.0', 'ct_218.0', 'ct_226.0', 'ct_227.02', 'ct_227.04', 'ct_228.01', 'ct_228.02', 'ct_228.03', 'ct_229.01', 'ct_229.02', 'ct_229.03', 'ct_230.01', 'ct_230.03', 'ct_231.02', 'ct_231.03', 'ct_232.0', 'ct_233.0', 'ct_234.0', 'ct_251.0', 'ct_252.0', 'ct_253.0', 'ct_254.01', 'ct_254.02', 'ct_254.03', 'ct_255.0', 'ct_256.0', 'ct_257.01', 'ct_257.02', 'ct_258.0', 'ct_259.0', 'ct_260.01', 'ct_260.02', 'ct_260.03', 'ct_260.04', 'ct_261.0', 'ct_262.0', 'ct_263.01', 'ct_263.02', 'ct_263.03', 'ct_264.01', 'ct_264.02', 'ct_264.03', 'ct_264.04', 'ct_301.01', 'ct_301.02', 'ct_302.01', 'ct_302.02', 'ct_303.01', 'ct_303.02', 'ct_304.0', 'ct_305.0', 'ct_306.0', 'ct_307.0', 'ct_308.0', 'ct_309.0', 'ct_310.0', 'ct_311.0', 'ct_312.01', 'ct_312.02', 'ct_313.01', 'ct_313.02', 'ct_314.0', 'ct_326.01', 'ct_326.02', 'ct_327.0', 'ct_328.01', 'ct_328.02', 'ct_329.01', 'ct_329.02', 'ct_330.0', 'ct_331.0', 'ct_332.01', 'ct_332.03', 'ct_332.04', 'ct_351.0', 'ct_352.01', 'ct_352.02', 'ct_353.0', 'ct_354.0', 'ct_401.0', 'ct_402.0', 'ct_426.01', 'ct_426.02', 'ct_427.0', 'ct_428.0', 'ct_451.0', 'ct_452.0', 'ct_476.0', 'ct_477.01', 'ct_477.02', 'ct_478.01', 'ct_478.02', 'ct_479.01', 'ct_479.02', 'ct_601.0', 'ct_604.0', 'ct_605.02', 'ct_607.0', 'ct_610.0', 'ct_611.0', 'ct_612.0', 'ct_614.0', 'ct_615.0', 'ct_9802.0', 'ct_9803.0', 'ct_9805.01', 'ct_9806.0', 'ct_9809.0']\n"
     ]
    }
   ],
   "source": [
    "# Run this at the end to restrict to only variables that will be fed to network\n",
    "sim_final = sim_2.drop(['min_elapsed', 'date', 'month', 'year', 'dow', 'hour', 'start_and_census'], axis=1)\n",
    "print(sim_final.shape)\n",
    "print(list(sim_final))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(193159, 321)\n"
     ]
    }
   ],
   "source": [
    "# Stack train/test data (remember we have future data and need to remove!)\n",
    "df_1 = pd.read_csv('~/Desktop/project1030/SF/training data/training data.csv')\n",
    "df_2 = pd.read_csv('~/Desktop/project1030/SF/training data/training data_50_100.csv')\n",
    "df_3 = pd.read_csv('~/Desktop/project1030/SF/training data/training data_100_150.csv')\n",
    "df_4 = pd.read_csv('~/Desktop/project1030/SF/training data/training data_150_200.csv')\n",
    "\n",
    "crime_pred = (df_1.append(df_2, ignore_index=True).append(df_3, ignore_index=True).append(df_4, ignore_index=True))\n",
    "print(crime_pred.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Read in training data (remember to remove future data we're not trying to predict!)\n",
    "crime_pred.drop(['future_other_1_hour', 'future_property_1_hour', \\\n",
    "                 'future_violent_1_hour', 'future_robbery_1_hour',], axis=1, inplace=True)\n",
    "\n",
    "Y = np.array(crime_pred['future_any_1_hour'])\n",
    "X = crime_pred.drop(['future_any_1_hour'], axis=1).as_matrix()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GradientBoostingClassifier(criterion='friedman_mse', init=None,\n",
       "              learning_rate=0.1, loss='deviance', max_depth=3,\n",
       "              max_features=None, max_leaf_nodes=None,\n",
       "              min_impurity_decrease=0.0, min_impurity_split=None,\n",
       "              min_samples_leaf=1, min_samples_split=2,\n",
       "              min_weight_fraction_leaf=0.0, n_estimators=100,\n",
       "              presort='auto', random_state=None, subsample=1.0, verbose=0,\n",
       "              warm_start=False)"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fit gradient boosting classifier on training data\n",
    "from sklearn.ensemble import GradientBoostingClassifier\n",
    "\n",
    "grad_clf = GradientBoostingClassifier()\n",
    "grad_clf.fit(X, Y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pred_probs = pd.DataFrame(columns=['census_tract', 'predicted_prob_crime_1_hr'])\n",
    "tracts = sim_final['census_tract']\n",
    "pred_probs['census_tract'] = tracts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Drop census tract from sim_final: now data is in format that classifier has been trained on\n",
    "sim_final.drop(columns=['census_tract'], inplace=True)\n",
    "\n",
    "# Try predicting general (any) crimes first\n",
    "sim_final.drop(['future_other_1_hour', 'future_property_1_hour', \\\n",
    "                 'future_violent_1_hour', 'future_robbery_1_hour',], axis=1, inplace=True)\n",
    "\n",
    "Y_sim = np.array(sim_final['future_any_1_hour'])\n",
    "X_sim = sim_final.drop(['future_any_1_hour'], axis=1).as_matrix()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Predict probabilities for each census tract\n",
    "pred_probs['predicted_prob_crime_1_hr'] = pd.Series(grad_clf.predict_proba(X_sim)[:, 1])\n",
    "\n",
    "# Draw random uniform from [0, 1] for simulation\n",
    "pred_probs['rand_unif'] = np.random.uniform(size=(195*forecast_periods))\n",
    "\n",
    "# Simulate crimes based on output probs\n",
    "pred_probs['sim_crime'] = (pred_probs['rand_unif'] <= pred_probs['predicted_prob_crime_1_hr']).astype(int)\n",
    "pred_probs['actual_crime'] = Y_sim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    census_tract  predicted_prob_crime_1_hr  rand_unif  sim_crime  \\\n",
      "0          232.0                   0.107517   0.038855          1   \n",
      "1          232.0                   0.107517   0.160850          0   \n",
      "2          232.0                   0.107517   0.979303          0   \n",
      "3          232.0                   0.107517   0.187824          0   \n",
      "4          232.0                   0.107517   0.648799          0   \n",
      "5          232.0                   0.107517   0.862449          0   \n",
      "6          232.0                   0.107517   0.687698          0   \n",
      "7          232.0                   0.107517   0.432777          0   \n",
      "8          232.0                   0.165833   0.490224          0   \n",
      "9          232.0                   0.143261   0.644683          0   \n",
      "10         232.0                   0.132317   0.066694          1   \n",
      "11         232.0                   0.158021   0.401664          0   \n",
      "12         232.0                   0.140339   0.808819          0   \n",
      "13         232.0                   0.124767   0.727967          0   \n",
      "14         232.0                   0.158021   0.994290          0   \n",
      "15         232.0                   0.170529   0.589204          0   \n",
      "16         232.0                   0.144243   0.108816          1   \n",
      "17         232.0                   0.135402   0.101485          1   \n",
      "18         232.0                   0.129419   0.853523          0   \n",
      "19         232.0                   0.159266   0.268891          0   \n",
      "20         232.0                   0.141086   0.114809          1   \n",
      "21         232.0                   0.129357   0.035668          1   \n",
      "22         232.0                   0.178422   0.931259          0   \n",
      "23         232.0                   0.261328   0.441155          0   \n",
      "24         232.0                   0.221420   0.212565          1   \n",
      "\n",
      "    actual_crime  \n",
      "0              0  \n",
      "1              0  \n",
      "2              0  \n",
      "3              0  \n",
      "4              0  \n",
      "5              0  \n",
      "6              0  \n",
      "7              1  \n",
      "8              0  \n",
      "9              0  \n",
      "10             0  \n",
      "11             0  \n",
      "12             0  \n",
      "13             1  \n",
      "14             1  \n",
      "15             0  \n",
      "16             0  \n",
      "17             0  \n",
      "18             1  \n",
      "19             0  \n",
      "20             0  \n",
      "21             1  \n",
      "22             1  \n",
      "23             0  \n",
      "24             0  \n",
      "(14040, 5)\n"
     ]
    }
   ],
   "source": [
    "print(pred_probs.head(25))\n",
    "print(pred_probs.shape)\n",
    "\n",
    "pred_probs.to_csv('~/Desktop/project1030/SF/predicted_probs_72_hrs.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pred_probs_1_hr = pred_probs.groupby('census_tract').first()\n",
    "pred_probs_1_hr.to_csv('~/Desktop/project1030/SF/predicted_probs_1_hr.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.911894586895\n"
     ]
    }
   ],
   "source": [
    "# See how often we got it right (accuracy)\n",
    "print(np.equal(pred_probs['sim_crime'].values, pred_probs['actual_crime'].values).astype(int).mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              predicted_prob_crime_1_hr  rand_unif  sim_crime  actual_crime  \\\n",
      "census_tract                                                                  \n",
      "101.00                         8.596483  33.936317         10             1   \n",
      "102.00                         3.054320  36.634810          4             1   \n",
      "103.00                         2.805899  33.537632          4             0   \n",
      "104.00                         3.016532  34.575684          2             0   \n",
      "105.00                        10.079442  37.790199          8             3   \n",
      "106.00                         8.938286  36.702734         11             1   \n",
      "107.00                         4.619296  35.004563          3             4   \n",
      "108.00                         2.699205  39.333484          1             0   \n",
      "109.00                         3.870238  39.478119          5             0   \n",
      "110.00                         3.160576  40.573331          2             1   \n",
      "111.00                         7.141903  34.723267          5             0   \n",
      "112.00                         2.931981  32.026398          2             1   \n",
      "113.00                         2.765051  36.646911          3             1   \n",
      "117.00                        23.052561  35.329215         28             5   \n",
      "118.00                         2.707446  37.086717          2             0   \n",
      "119.01                         2.945760  37.621740          2             1   \n",
      "119.02                         2.786105  38.876524          1             1   \n",
      "120.00                         6.891619  33.346778          6             1   \n",
      "121.00                         6.351122  37.245796          3             2   \n",
      "122.01                         7.010857  34.884308         13             1   \n",
      "122.02                         9.276018  32.923848          8             2   \n",
      "123.01                        11.327685  32.821509         14             3   \n",
      "123.02                         7.392938  37.129780          6             2   \n",
      "124.01                         5.895616  37.837002          6             2   \n",
      "124.02                        17.477114  34.694370         18             6   \n",
      "\n",
      "              census_tract  \n",
      "census_tract                \n",
      "101.00              101.00  \n",
      "102.00              102.00  \n",
      "103.00              103.00  \n",
      "104.00              104.00  \n",
      "105.00              105.00  \n",
      "106.00              106.00  \n",
      "107.00              107.00  \n",
      "108.00              108.00  \n",
      "109.00              109.00  \n",
      "110.00              110.00  \n",
      "111.00              111.00  \n",
      "112.00              112.00  \n",
      "113.00              113.00  \n",
      "117.00              117.00  \n",
      "118.00              118.00  \n",
      "119.01              119.01  \n",
      "119.02              119.02  \n",
      "120.00              120.00  \n",
      "121.00              121.00  \n",
      "122.01              122.01  \n",
      "122.02              122.02  \n",
      "123.01              123.01  \n",
      "123.02              123.02  \n",
      "124.01              124.01  \n",
      "124.02              124.02  \n"
     ]
    }
   ],
   "source": [
    "grouped_pred_probs = pred_probs.groupby('census_tract').sum()\n",
    "grouped_pred_probs['census_tract'] = grouped_pred_probs.index\n",
    "print(grouped_pred_probs.head(25))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              census_tract  sim_crime  actual_crime  diff\n",
      "census_tract                                             \n",
      "101.00              101.00         10             1     9\n",
      "102.00              102.00          4             1     3\n",
      "103.00              103.00          4             0     4\n",
      "104.00              104.00          2             0     2\n",
      "105.00              105.00          8             3     5\n",
      "106.00              106.00         11             1    10\n",
      "107.00              107.00          3             4     1\n",
      "108.00              108.00          1             0     1\n",
      "109.00              109.00          5             0     5\n",
      "110.00              110.00          2             1     1\n",
      "111.00              111.00          5             0     5\n",
      "112.00              112.00          2             1     1\n",
      "113.00              113.00          3             1     2\n",
      "117.00              117.00         28             5    23\n",
      "118.00              118.00          2             0     2\n",
      "119.01              119.01          2             1     1\n",
      "119.02              119.02          1             1     0\n",
      "120.00              120.00          6             1     5\n",
      "121.00              121.00          3             2     1\n",
      "122.01              122.01         13             1    12\n"
     ]
    }
   ],
   "source": [
    "sim_output = grouped_pred_probs[['census_tract', 'sim_crime', 'actual_crime']]\n",
    "sim_output['diff'] = abs(sim_output['sim_crime'] - sim_output['actual_crime'])\n",
    "print(sim_output.head(20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average difference between # of predicted and actual crimes:\n",
      "4.26153846154\n"
     ]
    }
   ],
   "source": [
    "print('Average difference between # of predicted and actual crimes:')\n",
    "print(sim_output['diff'].values.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   census_tract  sim_crime  actual_crime  diff  \\\n",
      "0         101.0         10             1     9   \n",
      "1         102.0          4             1     3   \n",
      "2         103.0          4             0     4   \n",
      "3         104.0          2             0     2   \n",
      "4         105.0          8             3     5   \n",
      "\n",
      "                                            the_geom  \n",
      "0  MULTIPOLYGON (((-122.421076 37.812889, -122.42...  \n",
      "1  MULTIPOLYGON (((-122.418445 37.80458, -122.418...  \n",
      "2  MULTIPOLYGON (((-122.418445 37.80458, -122.418...  \n",
      "3  MULTIPOLYGON (((-122.402421 37.799382, -122.40...  \n",
      "4  MULTIPOLYGON (((-122.40068099999999 37.796777,...  \n",
      "(195, 5)\n"
     ]
    }
   ],
   "source": [
    "# Merge on census tract boundaries for plotting\n",
    "census_tr_for_merge = census_tr[['NAME10', 'the_geom']].rename(columns={'NAME10': 'census_tract'})\n",
    "\n",
    "sim_output_w_tr = pd.merge(sim_output, census_tr_for_merge, on='census_tract')\n",
    "print(sim_output_w_tr.head())\n",
    "print(sim_output_w_tr.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Output for plotting \n",
    "sim_output_w_tr.to_csv('~/Desktop/project1030/SF/simulations/sim_72hr.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}