ML-Model-and-Analysis-Crime+RealEstate.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ML Model and Analysis - Crime + Real Estate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load required libraries\n",
    "import pandas as pd\n",
    "# import geopandas as gpd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import sklearn\n",
    "import datetime\n",
    "from scipy import stats\n",
    "import seaborn as sns\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "area_names = \"\"\"\n",
    "01\tRogers Park\t\n",
    "40\tWashington Park\n",
    "02\tWest Ridge\t\n",
    "41\tHyde Park\n",
    "03\tUptown\t\n",
    "42\tWoodlawn\n",
    "04\tLincoln Square\t\n",
    "43\tSouth Shore\n",
    "05\tNorth Center\t\n",
    "44\tChatham\n",
    "06\tLakeview\t\n",
    "45\tAvalon Park\n",
    "07\tLincoln Park\t\n",
    "46\tSouth Chicago\n",
    "08\tNear North Side\t\n",
    "47\tBurnside\n",
    "09\tEdison Park\t\n",
    "48\tCalumet Heights\n",
    "10\tNorwood Park\t\n",
    "49\tRoseland\n",
    "11\tJefferson Park\t\n",
    "50\tPullman\n",
    "12\tForest Glen\t\n",
    "51\tSouth Deering\n",
    "13\tNorth Park\t\n",
    "52\tEast Side\n",
    "14\tAlbany Park\t\n",
    "53\tWest Pullman\n",
    "15\tPortage Park\t\n",
    "54\tRiverdale\n",
    "16\tIrving Park\n",
    "55\tHegewisch\n",
    "17\tDunning\t\n",
    "56\tGarfield Ridge\n",
    "18\tMontclare\t\n",
    "57\tArcher Heights\n",
    "19\tBelmont Cragin\t\n",
    "58\tBrighton Park\n",
    "20\tHermosa\t\n",
    "59\tMcKinley Park\n",
    "21\tAvondale\t\n",
    "60\tBridgeport\n",
    "22\tLogan Square\t\n",
    "61\tNew City\n",
    "23\tHumboldt Park\t\n",
    "62\tWest Elsdon\n",
    "24\tWest Town\t\n",
    "63\tGage Park\n",
    "25\tAustin\t\n",
    "64\tClearing\n",
    "26\tWest Garfield Park \t\n",
    "65\tWest Lawn\n",
    "27\tEast Garfield Park\t\n",
    "66\tChicago Lawn\n",
    "28\tNear West Side\t\n",
    "67\tWest Englewood\n",
    "29\tNorth Lawndale\t\n",
    "68\tEnglewood\n",
    "30\tSouth Lawndale\t\n",
    "69\tGreater Grand Crossing\n",
    "31\tLower West Side\t\n",
    "70\tAshburn\n",
    "32\tLoop\t\n",
    "71\tAuburn Gresham\n",
    "33\tNear South Side\t\n",
    "72\tBeverly\n",
    "34\tArmour Square\t\n",
    "73\tWashington Heights\n",
    "35\tDouglas\t\n",
    "74\tMount Greenwood\n",
    "36\tOakland\t\n",
    "75\tMorgan Park\n",
    "37\tFuller Park\t\n",
    "76\tO'Hare\n",
    "38\tGrand Boulevard\t\n",
    "77\tEdgewater\n",
    "39\tKenwood\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "neighborhood_Map = \"\"\"\n",
    "Cabrini–Green\tNear North Side\t\n",
    "The Gold Coast\tNear North Side\t\n",
    "Goose Island\tNear North Side\t\n",
    "Magnificent Mile\tNear North Side\t\n",
    "Old Town\tNear North Side\t\n",
    "River North\tNear North Side\t\n",
    "River West\tNear North Side\t\n",
    "Streeterville\tNear North Side\t\n",
    "Loop\tLoop\t\n",
    "Near East Side\tLoop\t\n",
    "South Loop\tLoop\t\n",
    "West Loop Gate\tLoop\t\n",
    "Dearborn Park\tNear South Side\t\n",
    "Printer's Row\tNear South Side\t\n",
    "South Loop\tNear South Side\t\n",
    "Prairie Avenue Historic District\tNear South Side\t\n",
    "Horner Park\tNorth Center\t\n",
    "Roscoe Village\tNorth Center\t\n",
    "Boystown\tLake View\t\n",
    "Lake View East\tLake View\t\n",
    "Graceland West\tLake View\t\n",
    "South East Ravenswood\tLake View\t\n",
    "Wrigleyville\tLake View\t\n",
    "Old Town Triangle\tLincoln Park\n",
    "Park West\tLincoln Park\t\n",
    "Ranch Triangle\tLincoln Park\t\n",
    "Sheffield Neighbors\tLincoln Park\t\n",
    "Wrightwood Neighbors\tLincoln Park\t\n",
    "Belmont Gardens\tAvondale\t\n",
    "Chicago's Polish Village\tAvondale\t\n",
    "Kosciuszko Park\tAvondale\t\n",
    "Belmont Gardens\tLogan Square\t\n",
    "Bucktown\tLogan Square\t\n",
    "Kosciuszko Park\tLogan Square\t\n",
    "Palmer Square\tLogan Square\t\n",
    "East Rogers Park\tRogers Park\t\n",
    "Arcadia Terrace\tWest Ridge\t\n",
    "Peterson Park\tWest Ridge\t\n",
    "West Rogers Park\tWest Ridge\t\n",
    "Buena Park\tUptown\t\n",
    "Argyle Street\tUptown\t\n",
    "Margate Park\tUptown\t\n",
    "Sheridan Park\tUptown\t\n",
    "Ravenswood\tLincoln Square\t\n",
    "Ravenswood Gardens\tLincoln Square\t\n",
    "Rockwell Crossing\tLincoln Square\t\n",
    "Edison Park\tEdison Park\t\n",
    "Big Oaks\tNorwood Park\t\n",
    "Old Norwood Park\tNorwood Park\t\n",
    "Oriole Park\tNorwood Park\t\n",
    "Union Ridge\tNorwood Park\t\n",
    "Gladstone Park\tJefferson Park\t\n",
    "Edgebrook\tForest Glen\n",
    "Old Edgebrook\tForest Glen\t\n",
    "South Edgebrook\tForest Glen\t\n",
    "Sauganash\tForest Glen\t\n",
    "Wildwood\tForest Glen\t\n",
    "Brynford Park\tNorth Park\t\n",
    "Hollywood Park\tNorth Park\t\n",
    "River's Edge\tNorth Park\t\n",
    "Sauganash Woods\tNorth Park\t\n",
    "Mayfair\tAlbany Park\t\n",
    "North Mayfair\tAlbany Park\t\n",
    "Ravenswood Manor\tAlbany Park\t\n",
    "Schorsch Forest View\tO'Hare\t\n",
    "Andersonville\tEdgewater\t\n",
    "Edgewater Beach\tEdgewater\t\n",
    "Magnolia Glen\tEdgewater\t\n",
    "Lakewood/Balmoral\tEdgewater\t\n",
    "Belmont Central\tPortage Park\t\n",
    "Władysławowo\tPortage Park\t\n",
    "Six Corners\tPortage Park\t\n",
    "Avondale Gardens\tIrving Park\t\n",
    "Independence Park\tIrving Park\t\n",
    "Kilbourn Park\tIrving Park\t\n",
    "Little Cassubia\tIrving Park\t\n",
    "Old Irving Park\tIrving Park\t\n",
    "West Walker\tIrving Park\t\n",
    "The Villa\tIrving Park\t\n",
    "Belmont Heights\tDunning\t\n",
    "Belmont Terrace\tDunning\t\n",
    "Irving Woods\tDunning\t\n",
    "Schorsch Village\tDunning\t\n",
    "Montclare\tMontclare\t\n",
    "Belmont Central\tBelmont Cragin\t\n",
    "Hanson Park\tBelmont Cragin\n",
    "Belmont Gardens\tHermosa\t\n",
    "Kelvyn Park\tHermosa\t\n",
    "East Village\tWest Town\t\n",
    "Noble Square\tWest Town\t\n",
    "Polish Downtown\tWest Town\t\n",
    "Pulaski Park\tWest Town\t\n",
    "Smith Park\tWest Town\t\n",
    "Ukrainian Village\tWest Town\t\n",
    "Wicker Park\tWest Town\t\n",
    "Galewood\tAustin\t\n",
    "The Island\tAustin\t\n",
    "West Garfield Park\tWest Garfield Park\t\n",
    "Fifth City\tEast Garfield Park\t\n",
    "Greektown\tNear West Side\t\n",
    "Little Italy\tNear West Side\t\n",
    "Tri-Taylor\tNear West Side\t\n",
    "Lawndale\tNorth Lawndale\t\n",
    "Homan Square\tNorth Lawndale\t\n",
    "Douglas Park\tNorth Lawndale\t\n",
    "Little Village\tSouth Lawndale\t\n",
    "Heart of Chicago\tLower West Side\t\n",
    "Heart of Italy\tLower West Side\t\n",
    "Pilsen\tLower West Side\t\n",
    "East Pilsen\tLower West Side\t\n",
    "Chinatown\tArmour Square\n",
    "Wentworth Gardens\tArmour Square\t\n",
    "Bridgeport, Chicago\tArmour Square\t\n",
    "Groveland Park\tDouglas\t\n",
    "Lake Meadows\tDouglas\t\n",
    "the Gap\tDouglas\t\n",
    "Prairie Shores\tDouglas\t\n",
    "South Commons\tDouglas\t\n",
    "Oakland\tOakland\t\n",
    "Fuller Park\tFuller Park\t\n",
    "Bronzeville\tGrand Boulevard\t\n",
    "Kenwood\tKenwood\t\n",
    "South Kenwood\tKenwood\t\n",
    "Washington Park\tWashington Park\t\n",
    "East Hyde Park\tHyde Park\t\n",
    "Hyde Park\tHyde Park\t\n",
    "West Woodlawn\tWoodlawn\t\n",
    "Jackson Park Highlands\tSouth Shore\t\n",
    "Bridgeport\tBridgeport\t\n",
    "Grand Crossing\tGreater Grand Crossing\t\n",
    "Parkway Gardens\tGreater Grand Crossing\t\n",
    "Park Manor\tGreater Grand Crossing\t\n",
    "LeClaire Courts\tGarfield Ridge\t\n",
    "Sleepy Hollow\tGarfield Ridge\t\n",
    "Vittum Park\tGarfield Ridge\t\n",
    "Archer Heights\tArcher Heights\t\n",
    "Brighton Park\tBrighton Park\t\n",
    "McKinley Park \tMcKinley Park\t\n",
    "Back of the Yards\tNew City\t\n",
    "Canaryville\tNew City\t\n",
    "West Elsdon\tWest Elsdon\t\n",
    "Gage Park\tGage Park\t\n",
    "Chrysler Village\tClearing\n",
    "Ford City\tWest Lawn\t\n",
    "West Lawn\tWest Lawn\t\n",
    "Lithuanian Plaza\tChicago Lawn\t\n",
    "Marquette Park\tChicago Lawn\t\n",
    "West Englewood\tWest Englewood\t\n",
    "Englewood\tEnglewood\t\n",
    "East Chatham\tChatham\t\n",
    "West Chatham\tChatham\t\n",
    "West Chesterfield\tChatham\t\n",
    "Avalon Park\tAvalon Park\t\n",
    "Marynook\tAvalon Park\t\n",
    "Stony Island Park\tAvalon Park\t\n",
    "The Bush\tSouth Chicago\t\n",
    "Burnside\tBurnside\t\n",
    "Pill Hill\tCalumet Heights\t\n",
    "Fernwood\tRoseland\t\n",
    "Rosemoor\tRoseland\t\n",
    "Cottage Grove Heights\tPullman\t\n",
    "London Towne\tPullman\t\n",
    "Jeffrey Manor\tSouth Deering\t\n",
    "Trumbull Park\tSouth Deering\t\n",
    "Altgeld Gardens\tRiverdale\t\n",
    "Eden Green\tRiverdale\t\n",
    "Golden Gate\tRiverdale\t\n",
    "East Side\tEast Side\t\n",
    "West Pullman\tWest Pullman\t\n",
    "Hegewisch\tHegewisch\n",
    "Beverly View\tAshburn\t\n",
    "Mary Crest\tAshburn\t\n",
    "Parkview\tAshburn\t\n",
    "Scottsdale\tAshburn\t\n",
    "Wrightwood\tAshburn\t\n",
    "Auburn Gresham\tAuburn Gresham\t\n",
    "Beverly\tBeverly\t\n",
    "Brainerd\tWashington Heights\t\n",
    "Longwood Manor\tWashington Heights\t\n",
    "Princeton Park\tWashington Heights\t\n",
    "Mount Greenwood Heights\tMount Greenwood\t\n",
    "Talley's Corner\tMount Greenwood\t\n",
    "Beverly Woods\tMorgan Park\t\n",
    "Kennedy Park\tMorgan Park\t\n",
    "West Morgan Park\tMorgan Park\t\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Realestate data import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Avg_Price_2012</th>\n",
       "      <th>Avg_Price_2013</th>\n",
       "      <th>Avg_Price_2014</th>\n",
       "      <th>Avg_Price_2015</th>\n",
       "      <th>Avg_Price_2016</th>\n",
       "      <th>Avg_Price_2017</th>\n",
       "      <th>Avg_Price_2018</th>\n",
       "      <th>Avg_Price_2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chicago, IL metro area</td>\n",
       "      <td>100.454545</td>\n",
       "      <td>108.383333</td>\n",
       "      <td>117.558333</td>\n",
       "      <td>123.616667</td>\n",
       "      <td>130.658333</td>\n",
       "      <td>137.991667</td>\n",
       "      <td>142.316667</td>\n",
       "      <td>137.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>125.400000</td>\n",
       "      <td>143.016667</td>\n",
       "      <td>161.600000</td>\n",
       "      <td>172.333333</td>\n",
       "      <td>183.283333</td>\n",
       "      <td>189.691667</td>\n",
       "      <td>192.891667</td>\n",
       "      <td>177.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chicago, IL - Albany Park</td>\n",
       "      <td>91.727273</td>\n",
       "      <td>114.033333</td>\n",
       "      <td>134.991667</td>\n",
       "      <td>159.850000</td>\n",
       "      <td>165.791667</td>\n",
       "      <td>175.666667</td>\n",
       "      <td>178.425000</td>\n",
       "      <td>182.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chicago, IL - Andersonville</td>\n",
       "      <td>186.100000</td>\n",
       "      <td>204.116667</td>\n",
       "      <td>217.925000</td>\n",
       "      <td>229.183333</td>\n",
       "      <td>248.016667</td>\n",
       "      <td>246.391667</td>\n",
       "      <td>253.733333</td>\n",
       "      <td>241.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chicago, IL - Arcadia Terrace</td>\n",
       "      <td>75.490909</td>\n",
       "      <td>115.900000</td>\n",
       "      <td>138.283333</td>\n",
       "      <td>123.700000</td>\n",
       "      <td>148.900000</td>\n",
       "      <td>142.650000</td>\n",
       "      <td>168.691667</td>\n",
       "      <td>160.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Region  Avg_Price_2012  Avg_Price_2013  \\\n",
       "0         Chicago, IL metro area      100.454545      108.383333   \n",
       "1                    Chicago, IL      125.400000      143.016667   \n",
       "2      Chicago, IL - Albany Park       91.727273      114.033333   \n",
       "3    Chicago, IL - Andersonville      186.100000      204.116667   \n",
       "4  Chicago, IL - Arcadia Terrace       75.490909      115.900000   \n",
       "\n",
       "   Avg_Price_2014  Avg_Price_2015  Avg_Price_2016  Avg_Price_2017  \\\n",
       "0      117.558333      123.616667      130.658333      137.991667   \n",
       "1      161.600000      172.333333      183.283333      189.691667   \n",
       "2      134.991667      159.850000      165.791667      175.666667   \n",
       "3      217.925000      229.183333      248.016667      246.391667   \n",
       "4      138.283333      123.700000      148.900000      142.650000   \n",
       "\n",
       "   Avg_Price_2018  Avg_Price_2019  \n",
       "0      142.316667          137.15  \n",
       "1      192.891667          177.85  \n",
       "2      178.425000          182.50  \n",
       "3      253.733333          241.15  \n",
       "4      168.691667          160.75  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_df = pd.read_excel('real_eastate_data_main.xlsx')\n",
    "house_df.columns=house_df.columns.astype(str)\n",
    "house_df=house_df.set_index('Region')\n",
    "house_df.columns=pd.to_datetime(house_df.columns)\n",
    "house_ann= house_df.T.groupby(house_df.columns.year).agg('mean').T\n",
    "house_ann.reset_index(inplace=True)\n",
    "house_ann =  house_ann.rename(columns={2012: 'Avg_Price_2012', 2013: 'Avg_Price_2013', \n",
    "                                       2014: 'Avg_Price_2014', 2015: 'Avg_Price_2015',\n",
    "                                      2016: 'Avg_Price_2016', 2017: 'Avg_Price_2017', \n",
    "                                       2018: 'Avg_Price_2018', 2019: 'Avg_Price_2019'})\n",
    "\n",
    "house_ann.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "regions = []\n",
    "for r in house_df.index:\n",
    "    if '-' in r:\n",
    "        index = r.find('-')\n",
    "        new_reg = r[index+2:]\n",
    "        regions.append(new_reg)\n",
    "    else:\n",
    "        regions.append(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Avg_Price_2012</th>\n",
       "      <th>Avg_Price_2013</th>\n",
       "      <th>Avg_Price_2014</th>\n",
       "      <th>Avg_Price_2015</th>\n",
       "      <th>Avg_Price_2016</th>\n",
       "      <th>Avg_Price_2017</th>\n",
       "      <th>Avg_Price_2018</th>\n",
       "      <th>Avg_Price_2019</th>\n",
       "      <th>Area_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chicago, IL metro area</td>\n",
       "      <td>100.454545</td>\n",
       "      <td>108.383333</td>\n",
       "      <td>117.558333</td>\n",
       "      <td>123.616667</td>\n",
       "      <td>130.658333</td>\n",
       "      <td>137.991667</td>\n",
       "      <td>142.316667</td>\n",
       "      <td>137.15</td>\n",
       "      <td>Chicago, IL metro area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>125.400000</td>\n",
       "      <td>143.016667</td>\n",
       "      <td>161.600000</td>\n",
       "      <td>172.333333</td>\n",
       "      <td>183.283333</td>\n",
       "      <td>189.691667</td>\n",
       "      <td>192.891667</td>\n",
       "      <td>177.85</td>\n",
       "      <td>Chicago, IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chicago, IL - Albany Park</td>\n",
       "      <td>91.727273</td>\n",
       "      <td>114.033333</td>\n",
       "      <td>134.991667</td>\n",
       "      <td>159.850000</td>\n",
       "      <td>165.791667</td>\n",
       "      <td>175.666667</td>\n",
       "      <td>178.425000</td>\n",
       "      <td>182.50</td>\n",
       "      <td>Albany Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chicago, IL - Andersonville</td>\n",
       "      <td>186.100000</td>\n",
       "      <td>204.116667</td>\n",
       "      <td>217.925000</td>\n",
       "      <td>229.183333</td>\n",
       "      <td>248.016667</td>\n",
       "      <td>246.391667</td>\n",
       "      <td>253.733333</td>\n",
       "      <td>241.15</td>\n",
       "      <td>Andersonville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chicago, IL - Arcadia Terrace</td>\n",
       "      <td>75.490909</td>\n",
       "      <td>115.900000</td>\n",
       "      <td>138.283333</td>\n",
       "      <td>123.700000</td>\n",
       "      <td>148.900000</td>\n",
       "      <td>142.650000</td>\n",
       "      <td>168.691667</td>\n",
       "      <td>160.75</td>\n",
       "      <td>Arcadia Terrace</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Region  Avg_Price_2012  Avg_Price_2013  \\\n",
       "0         Chicago, IL metro area      100.454545      108.383333   \n",
       "1                    Chicago, IL      125.400000      143.016667   \n",
       "2      Chicago, IL - Albany Park       91.727273      114.033333   \n",
       "3    Chicago, IL - Andersonville      186.100000      204.116667   \n",
       "4  Chicago, IL - Arcadia Terrace       75.490909      115.900000   \n",
       "\n",
       "   Avg_Price_2014  Avg_Price_2015  Avg_Price_2016  Avg_Price_2017  \\\n",
       "0      117.558333      123.616667      130.658333      137.991667   \n",
       "1      161.600000      172.333333      183.283333      189.691667   \n",
       "2      134.991667      159.850000      165.791667      175.666667   \n",
       "3      217.925000      229.183333      248.016667      246.391667   \n",
       "4      138.283333      123.700000      148.900000      142.650000   \n",
       "\n",
       "   Avg_Price_2018  Avg_Price_2019               Area_Name  \n",
       "0      142.316667          137.15  Chicago, IL metro area  \n",
       "1      192.891667          177.85             Chicago, IL  \n",
       "2      178.425000          182.50             Albany Park  \n",
       "3      253.733333          241.15           Andersonville  \n",
       "4      168.691667          160.75         Arcadia Terrace  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "house_ann['Area_Name'] = regions\n",
    "house_ann.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "code = []\n",
    "for row in house_ann.itertuples(index=True,name='Pandas'):\n",
    "    #convert neighborhoods to community areas\n",
    "    comm_area = None\n",
    "    if row.Area_Name in neighborhood_name_dic.keys():\n",
    "        comm_area = neighborhood_name_dic[row.Area_Name]\n",
    "    else:\n",
    "        comm_area = row.Area_Name\n",
    "    \n",
    "    if comm_area in area_toCode_dic.keys():\n",
    "        code.append(area_toCode_dic[comm_area])\n",
    "    else:\n",
    "        code.append(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Avg_Price_2012</th>\n",
       "      <th>Avg_Price_2013</th>\n",
       "      <th>Avg_Price_2014</th>\n",
       "      <th>Avg_Price_2015</th>\n",
       "      <th>Avg_Price_2016</th>\n",
       "      <th>Avg_Price_2017</th>\n",
       "      <th>Avg_Price_2018</th>\n",
       "      <th>Avg_Price_2019</th>\n",
       "      <th>Area_Name</th>\n",
       "      <th>community_area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chicago, IL metro area</td>\n",
       "      <td>100.454545</td>\n",
       "      <td>108.383333</td>\n",
       "      <td>117.558333</td>\n",
       "      <td>123.616667</td>\n",
       "      <td>130.658333</td>\n",
       "      <td>137.991667</td>\n",
       "      <td>142.316667</td>\n",
       "      <td>137.15</td>\n",
       "      <td>Chicago, IL metro area</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>125.400000</td>\n",
       "      <td>143.016667</td>\n",
       "      <td>161.600000</td>\n",
       "      <td>172.333333</td>\n",
       "      <td>183.283333</td>\n",
       "      <td>189.691667</td>\n",
       "      <td>192.891667</td>\n",
       "      <td>177.85</td>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chicago, IL - Albany Park</td>\n",
       "      <td>91.727273</td>\n",
       "      <td>114.033333</td>\n",
       "      <td>134.991667</td>\n",
       "      <td>159.850000</td>\n",
       "      <td>165.791667</td>\n",
       "      <td>175.666667</td>\n",
       "      <td>178.425000</td>\n",
       "      <td>182.50</td>\n",
       "      <td>Albany Park</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chicago, IL - Andersonville</td>\n",
       "      <td>186.100000</td>\n",
       "      <td>204.116667</td>\n",
       "      <td>217.925000</td>\n",
       "      <td>229.183333</td>\n",
       "      <td>248.016667</td>\n",
       "      <td>246.391667</td>\n",
       "      <td>253.733333</td>\n",
       "      <td>241.15</td>\n",
       "      <td>Andersonville</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chicago, IL - Arcadia Terrace</td>\n",
       "      <td>75.490909</td>\n",
       "      <td>115.900000</td>\n",
       "      <td>138.283333</td>\n",
       "      <td>123.700000</td>\n",
       "      <td>148.900000</td>\n",
       "      <td>142.650000</td>\n",
       "      <td>168.691667</td>\n",
       "      <td>160.75</td>\n",
       "      <td>Arcadia Terrace</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Region  Avg_Price_2012  Avg_Price_2013  \\\n",
       "0         Chicago, IL metro area      100.454545      108.383333   \n",
       "1                    Chicago, IL      125.400000      143.016667   \n",
       "2      Chicago, IL - Albany Park       91.727273      114.033333   \n",
       "3    Chicago, IL - Andersonville      186.100000      204.116667   \n",
       "4  Chicago, IL - Arcadia Terrace       75.490909      115.900000   \n",
       "\n",
       "   Avg_Price_2014  Avg_Price_2015  Avg_Price_2016  Avg_Price_2017  \\\n",
       "0      117.558333      123.616667      130.658333      137.991667   \n",
       "1      161.600000      172.333333      183.283333      189.691667   \n",
       "2      134.991667      159.850000      165.791667      175.666667   \n",
       "3      217.925000      229.183333      248.016667      246.391667   \n",
       "4      138.283333      123.700000      148.900000      142.650000   \n",
       "\n",
       "   Avg_Price_2018  Avg_Price_2019               Area_Name  community_area  \n",
       "0      142.316667          137.15  Chicago, IL metro area             NaN  \n",
       "1      192.891667          177.85             Chicago, IL             NaN  \n",
       "2      178.425000          182.50             Albany Park            14.0  \n",
       "3      253.733333          241.15           Andersonville            77.0  \n",
       "4      168.691667          160.75         Arcadia Terrace             2.0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Addubf community area column to real estate data to match that of crime data.\n",
    "house_ann['community_area'] = code\n",
    "house_ann.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "crimes_2012 = pd.read_csv('Chicago_Crimes_2012_to_2017.csv', sep=',', error_bad_lines=False)\n",
    "crimes_2012.drop_duplicates(subset=['ID', 'Case Number', 'Date'], inplace=True)\n",
    "crimes_2012.drop(['Unnamed: 0','Case Number','IUCR','FBI Code','Updated On',\n",
    "                  'X Coordinate','Y Coordinate'], inplace = True, axis = 1)\n",
    "crimes_2012 = crimes_2012.dropna(axis = 0, how = 'any')\n",
    "\n",
    "## date conversion\n",
    "crimes_2012.Date = pd.to_datetime(crimes_2012.Date, format = '%m/%d/%Y %I:%M:%S %p')\n",
    "# crimes_2012.index = pd.DatetimeIndex(crimes_2012.Date)\n",
    "crimes_2012['Primary Type'] = pd.Categorical(crimes_2012['Primary Type'])\n",
    "crimes_2012['Description'] = pd.Categorical(crimes_2012['Description'])\n",
    "crimes_2012['Location Description'] = pd.Categorical(crimes_2012['Location Description'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Date</th>\n",
       "      <th>Block</th>\n",
       "      <th>Primary Type</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location Description</th>\n",
       "      <th>Arrest</th>\n",
       "      <th>Domestic</th>\n",
       "      <th>Beat</th>\n",
       "      <th>District</th>\n",
       "      <th>Ward</th>\n",
       "      <th>Community Area</th>\n",
       "      <th>Year</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10508693</td>\n",
       "      <td>2016-05-03 23:40:00</td>\n",
       "      <td>013XX S SAWYER AVE</td>\n",
       "      <td>battery</td>\n",
       "      <td>DOMESTIC BATTERY SIMPLE</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>1022</td>\n",
       "      <td>10.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>41.864073</td>\n",
       "      <td>-87.706819</td>\n",
       "      <td>(41.864073157, -87.706818608)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10508695</td>\n",
       "      <td>2016-05-03 21:40:00</td>\n",
       "      <td>061XX S DREXEL AVE</td>\n",
       "      <td>battery</td>\n",
       "      <td>DOMESTIC BATTERY SIMPLE</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>313</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>41.782922</td>\n",
       "      <td>-87.604363</td>\n",
       "      <td>(41.782921527, -87.60436317)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10508697</td>\n",
       "      <td>2016-05-03 23:31:00</td>\n",
       "      <td>053XX W CHICAGO AVE</td>\n",
       "      <td>public_peace_violation</td>\n",
       "      <td>RECKLESS CONDUCT</td>\n",
       "      <td>STREET</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1524</td>\n",
       "      <td>15.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>41.894908</td>\n",
       "      <td>-87.758372</td>\n",
       "      <td>(41.894908283, -87.758371958)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10508698</td>\n",
       "      <td>2016-05-03 22:10:00</td>\n",
       "      <td>049XX W FULTON ST</td>\n",
       "      <td>battery</td>\n",
       "      <td>SIMPLE</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1532</td>\n",
       "      <td>15.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>41.885687</td>\n",
       "      <td>-87.749516</td>\n",
       "      <td>(41.885686845, -87.749515983)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10508699</td>\n",
       "      <td>2016-05-03 22:00:00</td>\n",
       "      <td>003XX N LOTUS AVE</td>\n",
       "      <td>theft</td>\n",
       "      <td>$500 AND UNDER</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>1523</td>\n",
       "      <td>15.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>41.886297</td>\n",
       "      <td>-87.761751</td>\n",
       "      <td>(41.886297242, -87.761750709)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID                Date                Block            Primary Type  \\\n",
       "0  10508693 2016-05-03 23:40:00   013XX S SAWYER AVE                 battery   \n",
       "1  10508695 2016-05-03 21:40:00   061XX S DREXEL AVE                 battery   \n",
       "2  10508697 2016-05-03 23:31:00  053XX W CHICAGO AVE  public_peace_violation   \n",
       "3  10508698 2016-05-03 22:10:00    049XX W FULTON ST                 battery   \n",
       "4  10508699 2016-05-03 22:00:00    003XX N LOTUS AVE                   theft   \n",
       "\n",
       "               Description Location Description  Arrest  Domestic  Beat  \\\n",
       "0  DOMESTIC BATTERY SIMPLE            APARTMENT    True      True  1022   \n",
       "1  DOMESTIC BATTERY SIMPLE            RESIDENCE   False      True   313   \n",
       "2         RECKLESS CONDUCT               STREET   False     False  1524   \n",
       "3                   SIMPLE             SIDEWALK   False     False  1532   \n",
       "4           $500 AND UNDER            RESIDENCE   False      True  1523   \n",
       "\n",
       "   District  Ward  Community Area  Year   Latitude  Longitude  \\\n",
       "0      10.0  24.0            29.0  2016  41.864073 -87.706819   \n",
       "1       3.0  20.0            42.0  2016  41.782922 -87.604363   \n",
       "2      15.0  37.0            25.0  2016  41.894908 -87.758372   \n",
       "3      15.0  28.0            25.0  2016  41.885687 -87.749516   \n",
       "4      15.0  28.0            25.0  2016  41.886297 -87.761751   \n",
       "\n",
       "                        Location  \n",
       "0  (41.864073157, -87.706818608)  \n",
       "1   (41.782921527, -87.60436317)  \n",
       "2  (41.894908283, -87.758371958)  \n",
       "3  (41.885686845, -87.749515983)  \n",
       "4  (41.886297242, -87.761750709)  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crimes_2012['Primary Type']=crimes_2012['Primary Type'].apply(lambda x: re.sub(' ', '_', str(x)).lower())\n",
    "crimes_2012.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#matching neighborhood with community name\n",
    "code_pairs = [[float(p[0]), p[1]] for p in [pair.strip().split('\\t') for pair in area_names.strip().split('\\n')]]\n",
    "area_name_dic = {float(k[0]):k[1] for k in code_pairs} # code -> community area\n",
    "area_toCode_dic = {k[1] : float(k[0]) for k in code_pairs} # community area -> code\n",
    "\n",
    "code_pairs_neighborhoods = [[p[0], p[1]] for p in [pair.strip().split('\\t') for pair in neighborhood_Map.strip().split('\\n')]]\n",
    "neighborhood_name_dic = {k[0]:k[1] for k in code_pairs_neighborhoods} #neighborhood -> community area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>block</th>\n",
       "      <th>primary_type</th>\n",
       "      <th>description</th>\n",
       "      <th>location_description</th>\n",
       "      <th>arrest</th>\n",
       "      <th>domestic</th>\n",
       "      <th>beat</th>\n",
       "      <th>district</th>\n",
       "      <th>ward</th>\n",
       "      <th>community_area</th>\n",
       "      <th>year</th>\n",
       "      <th>community_name</th>\n",
       "      <th>neighbordhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>013XX S SAWYER AVE</td>\n",
       "      <td>battery</td>\n",
       "      <td>DOMESTIC BATTERY SIMPLE</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>1022</td>\n",
       "      <td>10.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>North Lawndale</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>061XX S DREXEL AVE</td>\n",
       "      <td>battery</td>\n",
       "      <td>DOMESTIC BATTERY SIMPLE</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>313</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>Woodlawn</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>053XX W CHICAGO AVE</td>\n",
       "      <td>public_peace_violation</td>\n",
       "      <td>RECKLESS CONDUCT</td>\n",
       "      <td>STREET</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1524</td>\n",
       "      <td>15.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>Austin</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>049XX W FULTON ST</td>\n",
       "      <td>battery</td>\n",
       "      <td>SIMPLE</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1532</td>\n",
       "      <td>15.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>Austin</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>003XX N LOTUS AVE</td>\n",
       "      <td>theft</td>\n",
       "      <td>$500 AND UNDER</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>1523</td>\n",
       "      <td>15.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>Austin</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 block            primary_type              description  \\\n",
       "0   013XX S SAWYER AVE                 battery  DOMESTIC BATTERY SIMPLE   \n",
       "1   061XX S DREXEL AVE                 battery  DOMESTIC BATTERY SIMPLE   \n",
       "2  053XX W CHICAGO AVE  public_peace_violation         RECKLESS CONDUCT   \n",
       "3    049XX W FULTON ST                 battery                   SIMPLE   \n",
       "4    003XX N LOTUS AVE                   theft           $500 AND UNDER   \n",
       "\n",
       "  location_description  arrest  domestic  beat  district  ward  \\\n",
       "0            APARTMENT    True      True  1022      10.0  24.0   \n",
       "1            RESIDENCE   False      True   313       3.0  20.0   \n",
       "2               STREET   False     False  1524      15.0  37.0   \n",
       "3             SIDEWALK   False     False  1532      15.0  28.0   \n",
       "4            RESIDENCE   False      True  1523      15.0  28.0   \n",
       "\n",
       "   community_area  year  community_name neighbordhood  \n",
       "0            29.0  2016  North Lawndale           NaN  \n",
       "1            42.0  2016        Woodlawn           NaN  \n",
       "2            25.0  2016          Austin           NaN  \n",
       "3            25.0  2016          Austin           NaN  \n",
       "4            25.0  2016          Austin           NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df =  crimes_2012.drop(['Latitude', 'Longitude', 'Location', 'ID','Date'], axis=1)\n",
    "df.columns = [str(re.sub(' ', '_', str(x))).lower() for x in df.columns]\n",
    "df['community_name'] = df['community_area'].map(area_name_dic)\n",
    "df['neighbordhood'] = df['community_name'].map(neighborhood_name_dic)\n",
    "df.dropna()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Avg_Price_2012</th>\n",
       "      <th>Avg_Price_2013</th>\n",
       "      <th>Avg_Price_2014</th>\n",
       "      <th>Avg_Price_2015</th>\n",
       "      <th>Avg_Price_2016</th>\n",
       "      <th>Avg_Price_2017</th>\n",
       "      <th>Avg_Price_2018</th>\n",
       "      <th>Avg_Price_2019</th>\n",
       "      <th>Area_Name</th>\n",
       "      <th>community_area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chicago, IL metro area</td>\n",
       "      <td>100.454545</td>\n",
       "      <td>108.383333</td>\n",
       "      <td>117.558333</td>\n",
       "      <td>123.616667</td>\n",
       "      <td>130.658333</td>\n",
       "      <td>137.991667</td>\n",
       "      <td>142.316667</td>\n",
       "      <td>137.15</td>\n",
       "      <td>Chicago, IL metro area</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>125.400000</td>\n",
       "      <td>143.016667</td>\n",
       "      <td>161.600000</td>\n",
       "      <td>172.333333</td>\n",
       "      <td>183.283333</td>\n",
       "      <td>189.691667</td>\n",
       "      <td>192.891667</td>\n",
       "      <td>177.85</td>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chicago, IL - Albany Park</td>\n",
       "      <td>91.727273</td>\n",
       "      <td>114.033333</td>\n",
       "      <td>134.991667</td>\n",
       "      <td>159.850000</td>\n",
       "      <td>165.791667</td>\n",
       "      <td>175.666667</td>\n",
       "      <td>178.425000</td>\n",
       "      <td>182.50</td>\n",
       "      <td>Albany Park</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chicago, IL - Andersonville</td>\n",
       "      <td>186.100000</td>\n",
       "      <td>204.116667</td>\n",
       "      <td>217.925000</td>\n",
       "      <td>229.183333</td>\n",
       "      <td>248.016667</td>\n",
       "      <td>246.391667</td>\n",
       "      <td>253.733333</td>\n",
       "      <td>241.15</td>\n",
       "      <td>Andersonville</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chicago, IL - Arcadia Terrace</td>\n",
       "      <td>75.490909</td>\n",
       "      <td>115.900000</td>\n",
       "      <td>138.283333</td>\n",
       "      <td>123.700000</td>\n",
       "      <td>148.900000</td>\n",
       "      <td>142.650000</td>\n",
       "      <td>168.691667</td>\n",
       "      <td>160.75</td>\n",
       "      <td>Arcadia Terrace</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Region  Avg_Price_2012  Avg_Price_2013  \\\n",
       "0         Chicago, IL metro area      100.454545      108.383333   \n",
       "1                    Chicago, IL      125.400000      143.016667   \n",
       "2      Chicago, IL - Albany Park       91.727273      114.033333   \n",
       "3    Chicago, IL - Andersonville      186.100000      204.116667   \n",
       "4  Chicago, IL - Arcadia Terrace       75.490909      115.900000   \n",
       "\n",
       "   Avg_Price_2014  Avg_Price_2015  Avg_Price_2016  Avg_Price_2017  \\\n",
       "0      117.558333      123.616667      130.658333      137.991667   \n",
       "1      161.600000      172.333333      183.283333      189.691667   \n",
       "2      134.991667      159.850000      165.791667      175.666667   \n",
       "3      217.925000      229.183333      248.016667      246.391667   \n",
       "4      138.283333      123.700000      148.900000      142.650000   \n",
       "\n",
       "   Avg_Price_2018  Avg_Price_2019               Area_Name  community_area  \n",
       "0      142.316667          137.15  Chicago, IL metro area             NaN  \n",
       "1      192.891667          177.85             Chicago, IL             NaN  \n",
       "2      178.425000          182.50             Albany Park            14.0  \n",
       "3      253.733333          241.15           Andersonville            77.0  \n",
       "4      168.691667          160.75         Arcadia Terrace             2.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_catg = df.groupby(['community_name','primary_type'])['primary_type'].count().unstack()\n",
    "crime_catg = crime_catg[['theft','battery', 'criminal_damage', 'narcotics', 'assault', 'other_offense',\n",
    "                        'burglary', 'deceptive_practice', 'motor_vehicle_theft', 'robbery']]\n",
    "crime_catg = crime_catg.dropna()\n",
    "crime_catg.columns = crime_catg.columns.add_categories(['community_name'])\n",
    "crime_catg.columns = pd.Index(list(crime_catg.columns))\n",
    "crime_catg.reset_index(inplace=True)\n",
    "crime_catg.head(5)\n",
    "# crime_catg.rename(columns=str).reset_index()\n",
    "house_ann.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>community_name</th>\n",
       "      <th>theft</th>\n",
       "      <th>battery</th>\n",
       "      <th>criminal_damage</th>\n",
       "      <th>narcotics</th>\n",
       "      <th>assault</th>\n",
       "      <th>other_offense</th>\n",
       "      <th>burglary</th>\n",
       "      <th>deceptive_practice</th>\n",
       "      <th>motor_vehicle_theft</th>\n",
       "      <th>robbery</th>\n",
       "      <th>Avg_Price_2012</th>\n",
       "      <th>Avg_Price_2013</th>\n",
       "      <th>Avg_Price_2014</th>\n",
       "      <th>Avg_Price_2015</th>\n",
       "      <th>Avg_Price_2016</th>\n",
       "      <th>Avg_Price_2017</th>\n",
       "      <th>Avg_Price_2018</th>\n",
       "      <th>Avg_Price_2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albany Park</td>\n",
       "      <td>2782.0</td>\n",
       "      <td>2417.0</td>\n",
       "      <td>1825.0</td>\n",
       "      <td>666.0</td>\n",
       "      <td>736.0</td>\n",
       "      <td>758.0</td>\n",
       "      <td>841.0</td>\n",
       "      <td>465.0</td>\n",
       "      <td>792.0</td>\n",
       "      <td>564.0</td>\n",
       "      <td>91.727273</td>\n",
       "      <td>114.033333</td>\n",
       "      <td>134.991667</td>\n",
       "      <td>159.850000</td>\n",
       "      <td>165.791667</td>\n",
       "      <td>175.666667</td>\n",
       "      <td>178.425000</td>\n",
       "      <td>182.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Archer Heights</td>\n",
       "      <td>1295.0</td>\n",
       "      <td>746.0</td>\n",
       "      <td>809.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>277.0</td>\n",
       "      <td>265.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>415.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>91.954545</td>\n",
       "      <td>111.183333</td>\n",
       "      <td>112.866667</td>\n",
       "      <td>117.991667</td>\n",
       "      <td>119.541667</td>\n",
       "      <td>128.791667</td>\n",
       "      <td>146.058333</td>\n",
       "      <td>156.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ashburn</td>\n",
       "      <td>2769.0</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1864.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>934.0</td>\n",
       "      <td>1156.0</td>\n",
       "      <td>683.0</td>\n",
       "      <td>794.0</td>\n",
       "      <td>542.0</td>\n",
       "      <td>86.954545</td>\n",
       "      <td>92.608333</td>\n",
       "      <td>102.333333</td>\n",
       "      <td>112.333333</td>\n",
       "      <td>121.966667</td>\n",
       "      <td>133.483333</td>\n",
       "      <td>145.916667</td>\n",
       "      <td>140.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Auburn Gresham</td>\n",
       "      <td>6467.0</td>\n",
       "      <td>8895.0</td>\n",
       "      <td>4455.0</td>\n",
       "      <td>4008.0</td>\n",
       "      <td>3051.0</td>\n",
       "      <td>2617.0</td>\n",
       "      <td>2630.0</td>\n",
       "      <td>1437.0</td>\n",
       "      <td>1527.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>32.454545</td>\n",
       "      <td>30.900000</td>\n",
       "      <td>34.066667</td>\n",
       "      <td>40.391667</td>\n",
       "      <td>49.375000</td>\n",
       "      <td>52.025000</td>\n",
       "      <td>61.025000</td>\n",
       "      <td>57.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austin</td>\n",
       "      <td>13376.0</td>\n",
       "      <td>18996.0</td>\n",
       "      <td>8051.0</td>\n",
       "      <td>18228.0</td>\n",
       "      <td>5834.0</td>\n",
       "      <td>5769.0</td>\n",
       "      <td>3372.0</td>\n",
       "      <td>2732.0</td>\n",
       "      <td>3529.0</td>\n",
       "      <td>4070.0</td>\n",
       "      <td>33.781818</td>\n",
       "      <td>42.591667</td>\n",
       "      <td>51.466667</td>\n",
       "      <td>56.958333</td>\n",
       "      <td>63.708333</td>\n",
       "      <td>78.608333</td>\n",
       "      <td>93.916667</td>\n",
       "      <td>103.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   community_name    theft  battery  criminal_damage  narcotics  assault  \\\n",
       "0     Albany Park   2782.0   2417.0           1825.0      666.0    736.0   \n",
       "1  Archer Heights   1295.0    746.0            809.0      250.0    277.0   \n",
       "2         Ashburn   2769.0   1932.0           1864.0      608.0    880.0   \n",
       "3  Auburn Gresham   6467.0   8895.0           4455.0     4008.0   3051.0   \n",
       "4          Austin  13376.0  18996.0           8051.0    18228.0   5834.0   \n",
       "\n",
       "   other_offense  burglary  deceptive_practice  motor_vehicle_theft  robbery  \\\n",
       "0          758.0     841.0               465.0                792.0    564.0   \n",
       "1          265.0     410.0               215.0                415.0    218.0   \n",
       "2          934.0    1156.0               683.0                794.0    542.0   \n",
       "3         2617.0    2630.0              1437.0               1527.0   2012.0   \n",
       "4         5769.0    3372.0              2732.0               3529.0   4070.0   \n",
       "\n",
       "   Avg_Price_2012  Avg_Price_2013  Avg_Price_2014  Avg_Price_2015  \\\n",
       "0       91.727273      114.033333      134.991667      159.850000   \n",
       "1       91.954545      111.183333      112.866667      117.991667   \n",
       "2       86.954545       92.608333      102.333333      112.333333   \n",
       "3       32.454545       30.900000       34.066667       40.391667   \n",
       "4       33.781818       42.591667       51.466667       56.958333   \n",
       "\n",
       "   Avg_Price_2016  Avg_Price_2017  Avg_Price_2018  Avg_Price_2019  \n",
       "0      165.791667      175.666667      178.425000          182.50  \n",
       "1      119.541667      128.791667      146.058333          156.50  \n",
       "2      121.966667      133.483333      145.916667          140.35  \n",
       "3       49.375000       52.025000       61.025000           57.20  \n",
       "4       63.708333       78.608333       93.916667          103.60  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### joint crime data with real estate data\n",
    "final_df = pd.merge(crime_catg, house_ann, left_on='community_name', right_on='Area_Name')\n",
    "final_df.drop(['Area_Name', 'community_area', 'Region'], axis=1, inplace=True)\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Annual crime count for each crime type for each region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>assault_2012</th>\n",
       "      <th>assault_2013</th>\n",
       "      <th>assault_2014</th>\n",
       "      <th>assault_2015</th>\n",
       "      <th>assault_2016</th>\n",
       "      <th>battery_2012</th>\n",
       "      <th>battery_2013</th>\n",
       "      <th>battery_2014</th>\n",
       "      <th>battery_2015</th>\n",
       "      <th>battery_2016</th>\n",
       "      <th>...</th>\n",
       "      <th>robbery_2013</th>\n",
       "      <th>robbery_2014</th>\n",
       "      <th>robbery_2015</th>\n",
       "      <th>robbery_2016</th>\n",
       "      <th>theft_2012</th>\n",
       "      <th>theft_2013</th>\n",
       "      <th>theft_2014</th>\n",
       "      <th>theft_2015</th>\n",
       "      <th>theft_2016</th>\n",
       "      <th>community_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>North Lawndale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>340</td>\n",
       "      <td>303</td>\n",
       "      <td>257</td>\n",
       "      <td>234</td>\n",
       "      <td>227</td>\n",
       "      <td>991</td>\n",
       "      <td>866</td>\n",
       "      <td>776</td>\n",
       "      <td>666</td>\n",
       "      <td>724</td>\n",
       "      <td>...</td>\n",
       "      <td>136</td>\n",
       "      <td>113</td>\n",
       "      <td>106</td>\n",
       "      <td>154</td>\n",
       "      <td>1043</td>\n",
       "      <td>1004</td>\n",
       "      <td>811</td>\n",
       "      <td>845</td>\n",
       "      <td>851</td>\n",
       "      <td>Woodlawn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>254</td>\n",
       "      <td>225</td>\n",
       "      <td>201</td>\n",
       "      <td>166</td>\n",
       "      <td>198</td>\n",
       "      <td>658</td>\n",
       "      <td>669</td>\n",
       "      <td>574</td>\n",
       "      <td>544</td>\n",
       "      <td>534</td>\n",
       "      <td>...</td>\n",
       "      <td>151</td>\n",
       "      <td>110</td>\n",
       "      <td>90</td>\n",
       "      <td>122</td>\n",
       "      <td>976</td>\n",
       "      <td>991</td>\n",
       "      <td>794</td>\n",
       "      <td>669</td>\n",
       "      <td>694</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>244</td>\n",
       "      <td>277</td>\n",
       "      <td>210</td>\n",
       "      <td>233</td>\n",
       "      <td>241</td>\n",
       "      <td>736</td>\n",
       "      <td>667</td>\n",
       "      <td>593</td>\n",
       "      <td>661</td>\n",
       "      <td>653</td>\n",
       "      <td>...</td>\n",
       "      <td>122</td>\n",
       "      <td>102</td>\n",
       "      <td>113</td>\n",
       "      <td>139</td>\n",
       "      <td>1338</td>\n",
       "      <td>1134</td>\n",
       "      <td>952</td>\n",
       "      <td>879</td>\n",
       "      <td>968</td>\n",
       "      <td>Austin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>124</td>\n",
       "      <td>111</td>\n",
       "      <td>85</td>\n",
       "      <td>99</td>\n",
       "      <td>93</td>\n",
       "      <td>354</td>\n",
       "      <td>352</td>\n",
       "      <td>319</td>\n",
       "      <td>288</td>\n",
       "      <td>288</td>\n",
       "      <td>...</td>\n",
       "      <td>45</td>\n",
       "      <td>29</td>\n",
       "      <td>42</td>\n",
       "      <td>50</td>\n",
       "      <td>691</td>\n",
       "      <td>689</td>\n",
       "      <td>507</td>\n",
       "      <td>499</td>\n",
       "      <td>514</td>\n",
       "      <td>Austin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   assault_2012  assault_2013  assault_2014  assault_2015  assault_2016  \\\n",
       "0             0             0             0             0             0   \n",
       "1           340           303           257           234           227   \n",
       "2           254           225           201           166           198   \n",
       "3           244           277           210           233           241   \n",
       "4           124           111            85            99            93   \n",
       "\n",
       "   battery_2012  battery_2013  battery_2014  battery_2015  battery_2016  \\\n",
       "0             0             0             0             0             0   \n",
       "1           991           866           776           666           724   \n",
       "2           658           669           574           544           534   \n",
       "3           736           667           593           661           653   \n",
       "4           354           352           319           288           288   \n",
       "\n",
       "        ...        robbery_2013  robbery_2014  robbery_2015  robbery_2016  \\\n",
       "0       ...                   0             0             0             0   \n",
       "1       ...                 136           113           106           154   \n",
       "2       ...                 151           110            90           122   \n",
       "3       ...                 122           102           113           139   \n",
       "4       ...                  45            29            42            50   \n",
       "\n",
       "   theft_2012  theft_2013  theft_2014  theft_2015  theft_2016  community_name  \n",
       "0           0           0           0           0           0  North Lawndale  \n",
       "1        1043        1004         811         845         851        Woodlawn  \n",
       "2         976         991         794         669         694             NaN  \n",
       "3        1338        1134         952         879         968          Austin  \n",
       "4         691         689         507         499         514          Austin  \n",
       "\n",
       "[5 rows x 51 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crimes_2012['Date'] = pd.to_datetime(crimes_2012['Date'], format='%m/%d/%Y %H:%M:%S %p')\n",
    "top_occured_crimes = ['theft','battery', 'criminal_damage', 'narcotics', 'assault', 'other_offense',\n",
    "                        'burglary', 'deceptive_practice', 'motor_vehicle_theft', 'robbery']\n",
    "df = crimes_2012[crimes_2012['Primary Type'].isin(top_occured_crimes)]\n",
    "df_cross = (pd.crosstab(index=df['Community Area'],\n",
    "                        columns=[df['Date'].dt.year, df['Primary Type']]).sort_index(axis=1, level=[1, 0]))\n",
    "df_cross.columns = ['{}_{}'.format(crime, year) for year, crime in df_cross.columns]\n",
    "df_cross.reset_index(inplace=True)\n",
    "df_cross['community_name'] = df['Community Area'].map(area_name_dic)\n",
    "df_cross.drop('Community Area', axis=1, inplace=True)\n",
    "df_cross.dropna()\n",
    "df_cross.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>assault_2012</th>\n",
       "      <th>assault_2013</th>\n",
       "      <th>assault_2014</th>\n",
       "      <th>assault_2015</th>\n",
       "      <th>assault_2016</th>\n",
       "      <th>battery_2012</th>\n",
       "      <th>battery_2013</th>\n",
       "      <th>battery_2014</th>\n",
       "      <th>battery_2015</th>\n",
       "      <th>battery_2016</th>\n",
       "      <th>...</th>\n",
       "      <th>Avg_Price_2012</th>\n",
       "      <th>Avg_Price_2013</th>\n",
       "      <th>Avg_Price_2014</th>\n",
       "      <th>Avg_Price_2015</th>\n",
       "      <th>Avg_Price_2016</th>\n",
       "      <th>Avg_Price_2017</th>\n",
       "      <th>Avg_Price_2018</th>\n",
       "      <th>Avg_Price_2019</th>\n",
       "      <th>Area_Name</th>\n",
       "      <th>community_area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>14.563636</td>\n",
       "      <td>16.333333</td>\n",
       "      <td>19.700000</td>\n",
       "      <td>21.750000</td>\n",
       "      <td>26.475000</td>\n",
       "      <td>38.183333</td>\n",
       "      <td>51.416667</td>\n",
       "      <td>64.75</td>\n",
       "      <td>North Lawndale</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>340</td>\n",
       "      <td>303</td>\n",
       "      <td>257</td>\n",
       "      <td>234</td>\n",
       "      <td>227</td>\n",
       "      <td>991</td>\n",
       "      <td>866</td>\n",
       "      <td>776</td>\n",
       "      <td>666</td>\n",
       "      <td>724</td>\n",
       "      <td>...</td>\n",
       "      <td>38.063636</td>\n",
       "      <td>42.183333</td>\n",
       "      <td>50.325000</td>\n",
       "      <td>50.891667</td>\n",
       "      <td>68.975000</td>\n",
       "      <td>93.475000</td>\n",
       "      <td>114.616667</td>\n",
       "      <td>119.80</td>\n",
       "      <td>Woodlawn</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>244</td>\n",
       "      <td>277</td>\n",
       "      <td>210</td>\n",
       "      <td>233</td>\n",
       "      <td>241</td>\n",
       "      <td>736</td>\n",
       "      <td>667</td>\n",
       "      <td>593</td>\n",
       "      <td>661</td>\n",
       "      <td>653</td>\n",
       "      <td>...</td>\n",
       "      <td>33.781818</td>\n",
       "      <td>42.591667</td>\n",
       "      <td>51.466667</td>\n",
       "      <td>56.958333</td>\n",
       "      <td>63.708333</td>\n",
       "      <td>78.608333</td>\n",
       "      <td>93.916667</td>\n",
       "      <td>103.60</td>\n",
       "      <td>Austin</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>124</td>\n",
       "      <td>111</td>\n",
       "      <td>85</td>\n",
       "      <td>99</td>\n",
       "      <td>93</td>\n",
       "      <td>354</td>\n",
       "      <td>352</td>\n",
       "      <td>319</td>\n",
       "      <td>288</td>\n",
       "      <td>288</td>\n",
       "      <td>...</td>\n",
       "      <td>33.781818</td>\n",
       "      <td>42.591667</td>\n",
       "      <td>51.466667</td>\n",
       "      <td>56.958333</td>\n",
       "      <td>63.708333</td>\n",
       "      <td>78.608333</td>\n",
       "      <td>93.916667</td>\n",
       "      <td>103.60</td>\n",
       "      <td>Austin</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>236</td>\n",
       "      <td>192</td>\n",
       "      <td>182</td>\n",
       "      <td>198</td>\n",
       "      <td>208</td>\n",
       "      <td>533</td>\n",
       "      <td>506</td>\n",
       "      <td>444</td>\n",
       "      <td>459</td>\n",
       "      <td>475</td>\n",
       "      <td>...</td>\n",
       "      <td>33.781818</td>\n",
       "      <td>42.591667</td>\n",
       "      <td>51.466667</td>\n",
       "      <td>56.958333</td>\n",
       "      <td>63.708333</td>\n",
       "      <td>78.608333</td>\n",
       "      <td>93.916667</td>\n",
       "      <td>103.60</td>\n",
       "      <td>Austin</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 60 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   assault_2012  assault_2013  assault_2014  assault_2015  assault_2016  \\\n",
       "0             0             0             0             0             0   \n",
       "1           340           303           257           234           227   \n",
       "2           244           277           210           233           241   \n",
       "3           124           111            85            99            93   \n",
       "4           236           192           182           198           208   \n",
       "\n",
       "   battery_2012  battery_2013  battery_2014  battery_2015  battery_2016  \\\n",
       "0             0             0             0             0             0   \n",
       "1           991           866           776           666           724   \n",
       "2           736           667           593           661           653   \n",
       "3           354           352           319           288           288   \n",
       "4           533           506           444           459           475   \n",
       "\n",
       "        ...        Avg_Price_2012  Avg_Price_2013  Avg_Price_2014  \\\n",
       "0       ...             14.563636       16.333333       19.700000   \n",
       "1       ...             38.063636       42.183333       50.325000   \n",
       "2       ...             33.781818       42.591667       51.466667   \n",
       "3       ...             33.781818       42.591667       51.466667   \n",
       "4       ...             33.781818       42.591667       51.466667   \n",
       "\n",
       "   Avg_Price_2015  Avg_Price_2016  Avg_Price_2017  Avg_Price_2018  \\\n",
       "0       21.750000       26.475000       38.183333       51.416667   \n",
       "1       50.891667       68.975000       93.475000      114.616667   \n",
       "2       56.958333       63.708333       78.608333       93.916667   \n",
       "3       56.958333       63.708333       78.608333       93.916667   \n",
       "4       56.958333       63.708333       78.608333       93.916667   \n",
       "\n",
       "   Avg_Price_2019       Area_Name  community_area  \n",
       "0           64.75  North Lawndale            29.0  \n",
       "1          119.80        Woodlawn            42.0  \n",
       "2          103.60          Austin            25.0  \n",
       "3          103.60          Austin            25.0  \n",
       "4          103.60          Austin            25.0  \n",
       "\n",
       "[5 rows x 60 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_finalDF = pd.merge(df_cross, house_ann, left_on='community_name', right_on='Area_Name')\n",
    "crime_finalDF.dropna()\n",
    "crime_finalDF.drop(['Region', 'community_name'], 1, inplace=True)\n",
    "crime_finalDF.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Logistic Regression and linear regression models"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Experiment: just try it with combined features by year eg: 2012 creating target dataframe with x and y labels\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.preprocessing import PolynomialFeatures\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.model_selection import ShuffleSplit\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.feature_selection import RFE, RFECV\n",
    "import re\n",
    "\n",
    "cols_2012 = crime_finalDF.filter(regex='_2012').columns\n",
    "crime_finalDF['Area_Name']=crime_finalDF['Area_Name'].apply(lambda x: re.sub(' ', '_', str(x)))\n",
    "regDF_2012 = crime_finalDF[cols_2012]\n",
    "regDF_2012 = regDF_2012.assign(community_code=crime_finalDF['community_area'])\n",
    "regDF_2012.dropna(inplace=True)\n",
    "\n",
    "X_feats = regDF_2012.drop(['Avg_Price_2012'], axis=1)\n",
    "y_label = regDF_2012['Avg_Price_2012'].values\n",
    "\n",
    "# poly = PolynomialFeatures(degree=2)\n",
    "# # # sc_x = StandardScaler()\n",
    "# sc_y = StandardScaler()\n",
    "# X = poly.fit_transform(X_feats)\n",
    "# y= sc_y.fit_transform(y_label.reshape(-1,1)).flatten()\n",
    "# # # # y= sc_y.fit_transform(y_label)\n",
    "# regModel = LinearRegression()\n",
    "# regModel.fit(X, y)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Splitting dataset into train and test "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.45559602746384875"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seed=np.random.seed(234)\n",
    "\n",
    "X_train, X_test, y_train, y_test = train_test_split(X_feats, y_label, test_size = 0.2, random_state = seed)\n",
    "cv = ShuffleSplit(n_splits=10, test_size=0.3, random_state=seed)\n",
    "\n",
    "## try with linear regression\n",
    "estimator = LinearRegression()\n",
    "selector = RFECV(estimator, step=1, cv=cv, scoring = 'r2')\n",
    "linReg = selector.fit(X_train, y_train)\n",
    "linReg.score(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>assault_2012</th>\n",
       "      <th>battery_2012</th>\n",
       "      <th>burglary_2012</th>\n",
       "      <th>criminal_damage_2012</th>\n",
       "      <th>deceptive_practice_2012</th>\n",
       "      <th>motor_vehicle_theft_2012</th>\n",
       "      <th>narcotics_2012</th>\n",
       "      <th>other_offense_2012</th>\n",
       "      <th>robbery_2012</th>\n",
       "      <th>theft_2012</th>\n",
       "      <th>community_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>579</td>\n",
       "      <td>1873</td>\n",
       "      <td>529</td>\n",
       "      <td>873</td>\n",
       "      <td>349</td>\n",
       "      <td>392</td>\n",
       "      <td>1056</td>\n",
       "      <td>436</td>\n",
       "      <td>514</td>\n",
       "      <td>1331</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>81</td>\n",
       "      <td>175</td>\n",
       "      <td>38</td>\n",
       "      <td>100</td>\n",
       "      <td>153</td>\n",
       "      <td>60</td>\n",
       "      <td>124</td>\n",
       "      <td>46</td>\n",
       "      <td>89</td>\n",
       "      <td>251</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>210</td>\n",
       "      <td>462</td>\n",
       "      <td>161</td>\n",
       "      <td>351</td>\n",
       "      <td>60</td>\n",
       "      <td>85</td>\n",
       "      <td>136</td>\n",
       "      <td>123</td>\n",
       "      <td>83</td>\n",
       "      <td>410</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>93</td>\n",
       "      <td>279</td>\n",
       "      <td>152</td>\n",
       "      <td>248</td>\n",
       "      <td>79</td>\n",
       "      <td>109</td>\n",
       "      <td>69</td>\n",
       "      <td>100</td>\n",
       "      <td>122</td>\n",
       "      <td>559</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>183</td>\n",
       "      <td>515</td>\n",
       "      <td>337</td>\n",
       "      <td>383</td>\n",
       "      <td>126</td>\n",
       "      <td>332</td>\n",
       "      <td>171</td>\n",
       "      <td>193</td>\n",
       "      <td>113</td>\n",
       "      <td>698</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>455</td>\n",
       "      <td>1297</td>\n",
       "      <td>420</td>\n",
       "      <td>717</td>\n",
       "      <td>363</td>\n",
       "      <td>332</td>\n",
       "      <td>675</td>\n",
       "      <td>338</td>\n",
       "      <td>473</td>\n",
       "      <td>1614</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>637</td>\n",
       "      <td>1792</td>\n",
       "      <td>627</td>\n",
       "      <td>1029</td>\n",
       "      <td>323</td>\n",
       "      <td>369</td>\n",
       "      <td>1086</td>\n",
       "      <td>404</td>\n",
       "      <td>470</td>\n",
       "      <td>1434</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>333</td>\n",
       "      <td>1202</td>\n",
       "      <td>284</td>\n",
       "      <td>483</td>\n",
       "      <td>440</td>\n",
       "      <td>278</td>\n",
       "      <td>1658</td>\n",
       "      <td>249</td>\n",
       "      <td>318</td>\n",
       "      <td>841</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>129</td>\n",
       "      <td>339</td>\n",
       "      <td>188</td>\n",
       "      <td>376</td>\n",
       "      <td>60</td>\n",
       "      <td>128</td>\n",
       "      <td>75</td>\n",
       "      <td>157</td>\n",
       "      <td>44</td>\n",
       "      <td>511</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>70</td>\n",
       "      <td>131</td>\n",
       "      <td>113</td>\n",
       "      <td>165</td>\n",
       "      <td>73</td>\n",
       "      <td>67</td>\n",
       "      <td>27</td>\n",
       "      <td>83</td>\n",
       "      <td>43</td>\n",
       "      <td>355</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>202</td>\n",
       "      <td>627</td>\n",
       "      <td>355</td>\n",
       "      <td>532</td>\n",
       "      <td>163</td>\n",
       "      <td>331</td>\n",
       "      <td>227</td>\n",
       "      <td>287</td>\n",
       "      <td>109</td>\n",
       "      <td>1068</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51</td>\n",
       "      <td>181</td>\n",
       "      <td>116</td>\n",
       "      <td>203</td>\n",
       "      <td>36</td>\n",
       "      <td>97</td>\n",
       "      <td>67</td>\n",
       "      <td>50</td>\n",
       "      <td>67</td>\n",
       "      <td>253</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>263</td>\n",
       "      <td>733</td>\n",
       "      <td>283</td>\n",
       "      <td>450</td>\n",
       "      <td>143</td>\n",
       "      <td>200</td>\n",
       "      <td>385</td>\n",
       "      <td>256</td>\n",
       "      <td>223</td>\n",
       "      <td>737</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>105</td>\n",
       "      <td>275</td>\n",
       "      <td>204</td>\n",
       "      <td>288</td>\n",
       "      <td>91</td>\n",
       "      <td>141</td>\n",
       "      <td>74</td>\n",
       "      <td>156</td>\n",
       "      <td>36</td>\n",
       "      <td>449</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>508</td>\n",
       "      <td>1287</td>\n",
       "      <td>380</td>\n",
       "      <td>872</td>\n",
       "      <td>486</td>\n",
       "      <td>523</td>\n",
       "      <td>467</td>\n",
       "      <td>418</td>\n",
       "      <td>351</td>\n",
       "      <td>3363</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>313</td>\n",
       "      <td>899</td>\n",
       "      <td>187</td>\n",
       "      <td>431</td>\n",
       "      <td>108</td>\n",
       "      <td>187</td>\n",
       "      <td>497</td>\n",
       "      <td>259</td>\n",
       "      <td>208</td>\n",
       "      <td>713</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>36</td>\n",
       "      <td>130</td>\n",
       "      <td>159</td>\n",
       "      <td>122</td>\n",
       "      <td>39</td>\n",
       "      <td>64</td>\n",
       "      <td>49</td>\n",
       "      <td>71</td>\n",
       "      <td>23</td>\n",
       "      <td>327</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>114</td>\n",
       "      <td>258</td>\n",
       "      <td>70</td>\n",
       "      <td>153</td>\n",
       "      <td>50</td>\n",
       "      <td>75</td>\n",
       "      <td>81</td>\n",
       "      <td>75</td>\n",
       "      <td>35</td>\n",
       "      <td>205</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>654</td>\n",
       "      <td>2228</td>\n",
       "      <td>743</td>\n",
       "      <td>975</td>\n",
       "      <td>187</td>\n",
       "      <td>328</td>\n",
       "      <td>1372</td>\n",
       "      <td>435</td>\n",
       "      <td>449</td>\n",
       "      <td>1531</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>128</td>\n",
       "      <td>303</td>\n",
       "      <td>113</td>\n",
       "      <td>196</td>\n",
       "      <td>50</td>\n",
       "      <td>83</td>\n",
       "      <td>77</td>\n",
       "      <td>134</td>\n",
       "      <td>84</td>\n",
       "      <td>509</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>55</td>\n",
       "      <td>152</td>\n",
       "      <td>51</td>\n",
       "      <td>123</td>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "      <td>40</td>\n",
       "      <td>52</td>\n",
       "      <td>11</td>\n",
       "      <td>143</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>461</td>\n",
       "      <td>1251</td>\n",
       "      <td>892</td>\n",
       "      <td>1171</td>\n",
       "      <td>302</td>\n",
       "      <td>639</td>\n",
       "      <td>485</td>\n",
       "      <td>382</td>\n",
       "      <td>385</td>\n",
       "      <td>3139</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>63</td>\n",
       "      <td>150</td>\n",
       "      <td>29</td>\n",
       "      <td>124</td>\n",
       "      <td>126</td>\n",
       "      <td>69</td>\n",
       "      <td>198</td>\n",
       "      <td>343</td>\n",
       "      <td>10</td>\n",
       "      <td>580</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>306</td>\n",
       "      <td>989</td>\n",
       "      <td>683</td>\n",
       "      <td>751</td>\n",
       "      <td>194</td>\n",
       "      <td>625</td>\n",
       "      <td>339</td>\n",
       "      <td>319</td>\n",
       "      <td>231</td>\n",
       "      <td>1946</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>51</td>\n",
       "      <td>161</td>\n",
       "      <td>39</td>\n",
       "      <td>106</td>\n",
       "      <td>17</td>\n",
       "      <td>24</td>\n",
       "      <td>30</td>\n",
       "      <td>66</td>\n",
       "      <td>27</td>\n",
       "      <td>162</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>393</td>\n",
       "      <td>1106</td>\n",
       "      <td>335</td>\n",
       "      <td>566</td>\n",
       "      <td>111</td>\n",
       "      <td>143</td>\n",
       "      <td>777</td>\n",
       "      <td>293</td>\n",
       "      <td>220</td>\n",
       "      <td>834</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>640</td>\n",
       "      <td>1970</td>\n",
       "      <td>691</td>\n",
       "      <td>986</td>\n",
       "      <td>252</td>\n",
       "      <td>389</td>\n",
       "      <td>1294</td>\n",
       "      <td>518</td>\n",
       "      <td>428</td>\n",
       "      <td>1425</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>111</td>\n",
       "      <td>239</td>\n",
       "      <td>89</td>\n",
       "      <td>157</td>\n",
       "      <td>113</td>\n",
       "      <td>69</td>\n",
       "      <td>51</td>\n",
       "      <td>91</td>\n",
       "      <td>65</td>\n",
       "      <td>726</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>128</td>\n",
       "      <td>348</td>\n",
       "      <td>200</td>\n",
       "      <td>323</td>\n",
       "      <td>166</td>\n",
       "      <td>96</td>\n",
       "      <td>251</td>\n",
       "      <td>147</td>\n",
       "      <td>51</td>\n",
       "      <td>547</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>389</td>\n",
       "      <td>1128</td>\n",
       "      <td>463</td>\n",
       "      <td>805</td>\n",
       "      <td>191</td>\n",
       "      <td>492</td>\n",
       "      <td>512</td>\n",
       "      <td>438</td>\n",
       "      <td>204</td>\n",
       "      <td>1210</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>60</td>\n",
       "      <td>212</td>\n",
       "      <td>83</td>\n",
       "      <td>198</td>\n",
       "      <td>42</td>\n",
       "      <td>112</td>\n",
       "      <td>75</td>\n",
       "      <td>78</td>\n",
       "      <td>26</td>\n",
       "      <td>372</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>119</td>\n",
       "      <td>225</td>\n",
       "      <td>135</td>\n",
       "      <td>223</td>\n",
       "      <td>115</td>\n",
       "      <td>93</td>\n",
       "      <td>43</td>\n",
       "      <td>88</td>\n",
       "      <td>100</td>\n",
       "      <td>660</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>111</td>\n",
       "      <td>326</td>\n",
       "      <td>197</td>\n",
       "      <td>324</td>\n",
       "      <td>102</td>\n",
       "      <td>152</td>\n",
       "      <td>71</td>\n",
       "      <td>142</td>\n",
       "      <td>94</td>\n",
       "      <td>797</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>632</td>\n",
       "      <td>2015</td>\n",
       "      <td>585</td>\n",
       "      <td>1013</td>\n",
       "      <td>186</td>\n",
       "      <td>623</td>\n",
       "      <td>2704</td>\n",
       "      <td>490</td>\n",
       "      <td>447</td>\n",
       "      <td>1562</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>166</td>\n",
       "      <td>433</td>\n",
       "      <td>201</td>\n",
       "      <td>317</td>\n",
       "      <td>133</td>\n",
       "      <td>144</td>\n",
       "      <td>213</td>\n",
       "      <td>171</td>\n",
       "      <td>93</td>\n",
       "      <td>638</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>201</td>\n",
       "      <td>625</td>\n",
       "      <td>291</td>\n",
       "      <td>457</td>\n",
       "      <td>83</td>\n",
       "      <td>273</td>\n",
       "      <td>160</td>\n",
       "      <td>199</td>\n",
       "      <td>121</td>\n",
       "      <td>509</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>493</td>\n",
       "      <td>1412</td>\n",
       "      <td>449</td>\n",
       "      <td>697</td>\n",
       "      <td>147</td>\n",
       "      <td>304</td>\n",
       "      <td>1013</td>\n",
       "      <td>296</td>\n",
       "      <td>219</td>\n",
       "      <td>1139</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>158</td>\n",
       "      <td>312</td>\n",
       "      <td>135</td>\n",
       "      <td>338</td>\n",
       "      <td>54</td>\n",
       "      <td>97</td>\n",
       "      <td>114</td>\n",
       "      <td>107</td>\n",
       "      <td>28</td>\n",
       "      <td>270</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>532</td>\n",
       "      <td>1601</td>\n",
       "      <td>692</td>\n",
       "      <td>902</td>\n",
       "      <td>233</td>\n",
       "      <td>411</td>\n",
       "      <td>1129</td>\n",
       "      <td>418</td>\n",
       "      <td>415</td>\n",
       "      <td>1339</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>124</td>\n",
       "      <td>354</td>\n",
       "      <td>214</td>\n",
       "      <td>282</td>\n",
       "      <td>94</td>\n",
       "      <td>143</td>\n",
       "      <td>111</td>\n",
       "      <td>132</td>\n",
       "      <td>54</td>\n",
       "      <td>691</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>295</td>\n",
       "      <td>596</td>\n",
       "      <td>115</td>\n",
       "      <td>277</td>\n",
       "      <td>981</td>\n",
       "      <td>65</td>\n",
       "      <td>213</td>\n",
       "      <td>244</td>\n",
       "      <td>201</td>\n",
       "      <td>4131</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>48</td>\n",
       "      <td>107</td>\n",
       "      <td>20</td>\n",
       "      <td>116</td>\n",
       "      <td>36</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>68</td>\n",
       "      <td>10</td>\n",
       "      <td>210</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>236</td>\n",
       "      <td>533</td>\n",
       "      <td>135</td>\n",
       "      <td>443</td>\n",
       "      <td>80</td>\n",
       "      <td>332</td>\n",
       "      <td>281</td>\n",
       "      <td>178</td>\n",
       "      <td>78</td>\n",
       "      <td>669</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>649</td>\n",
       "      <td>2276</td>\n",
       "      <td>528</td>\n",
       "      <td>1005</td>\n",
       "      <td>159</td>\n",
       "      <td>371</td>\n",
       "      <td>2342</td>\n",
       "      <td>499</td>\n",
       "      <td>446</td>\n",
       "      <td>1392</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>245</td>\n",
       "      <td>977</td>\n",
       "      <td>705</td>\n",
       "      <td>745</td>\n",
       "      <td>433</td>\n",
       "      <td>353</td>\n",
       "      <td>301</td>\n",
       "      <td>252</td>\n",
       "      <td>345</td>\n",
       "      <td>2875</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    assault_2012  battery_2012  burglary_2012  criminal_damage_2012  \\\n",
       "54           579          1873            529                   873   \n",
       "42            81           175             38                   100   \n",
       "16           210           462            161                   351   \n",
       "47            93           279            152                   248   \n",
       "22           183           515            337                   383   \n",
       "43           455          1297            420                   717   \n",
       "45           637          1792            627                  1029   \n",
       "24           333          1202            284                   483   \n",
       "30           129           339            188                   376   \n",
       "40            70           131            113                   165   \n",
       "18           202           627            355                   532   \n",
       "5             51           181            116                   203   \n",
       "55           263           733            283                   450   \n",
       "20           105           275            204                   288   \n",
       "29           508          1287            380                   872   \n",
       "44           313           899            187                   431   \n",
       "9             36           130            159                   122   \n",
       "27           114           258             70                   153   \n",
       "53           654          2228            743                   975   \n",
       "13           128           303            113                   196   \n",
       "50            55           152             51                   123   \n",
       "15           461          1251            892                  1171   \n",
       "49            63           150             29                   124   \n",
       "23           306           989            683                   751   \n",
       "41            51           161             39                   106   \n",
       "46           393          1106            335                   566   \n",
       "0              0             0              0                     1   \n",
       "32           640          1970            691                   986   \n",
       "36           111           239             89                   157   \n",
       "7            128           348            200                   323   \n",
       "21           389          1128            463                   805   \n",
       "51            60           212             83                   198   \n",
       "19           119           225            135                   223   \n",
       "28           111           326            197                   324   \n",
       "26           632          2015            585                  1013   \n",
       "57           166           433            201                   317   \n",
       "34           201           625            291                   457   \n",
       "52           493          1412            449                   697   \n",
       "48           158           312            135                   338   \n",
       "39           532          1601            692                   902   \n",
       "3            124           354            214                   282   \n",
       "33           295           596            115                   277   \n",
       "56            48           107             20                   116   \n",
       "4            236           533            135                   443   \n",
       "31           649          2276            528                  1005   \n",
       "8            245           977            705                   745   \n",
       "\n",
       "    deceptive_practice_2012  motor_vehicle_theft_2012  narcotics_2012  \\\n",
       "54                      349                       392            1056   \n",
       "42                      153                        60             124   \n",
       "16                       60                        85             136   \n",
       "47                       79                       109              69   \n",
       "22                      126                       332             171   \n",
       "43                      363                       332             675   \n",
       "45                      323                       369            1086   \n",
       "24                      440                       278            1658   \n",
       "30                       60                       128              75   \n",
       "40                       73                        67              27   \n",
       "18                      163                       331             227   \n",
       "5                        36                        97              67   \n",
       "55                      143                       200             385   \n",
       "20                       91                       141              74   \n",
       "29                      486                       523             467   \n",
       "44                      108                       187             497   \n",
       "9                        39                        64              49   \n",
       "27                       50                        75              81   \n",
       "53                      187                       328            1372   \n",
       "13                       50                        83              77   \n",
       "50                       25                        26              40   \n",
       "15                      302                       639             485   \n",
       "49                      126                        69             198   \n",
       "23                      194                       625             339   \n",
       "41                       17                        24              30   \n",
       "46                      111                       143             777   \n",
       "0                         0                         0               0   \n",
       "32                      252                       389            1294   \n",
       "36                      113                        69              51   \n",
       "7                       166                        96             251   \n",
       "21                      191                       492             512   \n",
       "51                       42                       112              75   \n",
       "19                      115                        93              43   \n",
       "28                      102                       152              71   \n",
       "26                      186                       623            2704   \n",
       "57                      133                       144             213   \n",
       "34                       83                       273             160   \n",
       "52                      147                       304            1013   \n",
       "48                       54                        97             114   \n",
       "39                      233                       411            1129   \n",
       "3                        94                       143             111   \n",
       "33                      981                        65             213   \n",
       "56                       36                        16              17   \n",
       "4                        80                       332             281   \n",
       "31                      159                       371            2342   \n",
       "8                       433                       353             301   \n",
       "\n",
       "    other_offense_2012  robbery_2012  theft_2012  community_code  \n",
       "54                 436           514        1331            11.0  \n",
       "42                  46            89         251            21.0  \n",
       "16                 123            83         410             8.0  \n",
       "47                 100           122         559            60.0  \n",
       "22                 193           113         698            41.0  \n",
       "43                 338           473        1614            21.0  \n",
       "45                 404           470        1434            43.0  \n",
       "24                 249           318         841             7.0  \n",
       "30                 157            44         511            58.0  \n",
       "40                  83            43         355            49.0  \n",
       "18                 287           109        1068            66.0  \n",
       "5                   50            67         253            25.0  \n",
       "55                 256           223         737            69.0  \n",
       "20                 156            36         449            40.0  \n",
       "29                 418           351        3363            58.0  \n",
       "44                 259           208         713            43.0  \n",
       "9                   71            23         327            35.0  \n",
       "27                  75            35         205            10.0  \n",
       "53                 435           449        1531            45.0  \n",
       "13                 134            84         509            67.0  \n",
       "50                  52            11         143            59.0  \n",
       "15                 382           385        3139             8.0  \n",
       "49                 343            10         580            68.0  \n",
       "23                 319           231        1946             7.0  \n",
       "41                  66            27         162            61.0  \n",
       "46                 293           220         834            56.0  \n",
       "0                    0             0           0            29.0  \n",
       "32                 518           428        1425            71.0  \n",
       "36                  91            65         726             6.0  \n",
       "7                  147            51         547            44.0  \n",
       "21                 438           204        1210            39.0  \n",
       "51                  78            26         372            22.0  \n",
       "19                  88           100         660            66.0  \n",
       "28                 142            94         797            10.0  \n",
       "26                 490           447        1562            10.0  \n",
       "57                 171            93         638             3.0  \n",
       "34                 199           121         509            23.0  \n",
       "52                 296           219        1139            26.0  \n",
       "48                 107            28         270            68.0  \n",
       "39                 418           415        1339            49.0  \n",
       "3                  132            54         691            25.0  \n",
       "33                 244           201        4131            23.0  \n",
       "56                  68            10         210            31.0  \n",
       "4                  178            78         669            25.0  \n",
       "31                 499           446        1392            71.0  \n",
       "8                  252           345        2875            35.0  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#trying with logistic regression\n",
    "seed=np.random.seed(234)\n",
    "\n",
    "X_train, X_test, y_train, y_test = train_test_split(X_feats, y_label, test_size = 0.2, random_state = seed)\n",
    "cv = ShuffleSplit(n_splits=10, test_size=0.3, random_state=seed)\n",
    "#trying with logistic regression\n",
    "logReg = LogisticRegression(random_state=0, solver='liblinear', C=0.01).fit(X_train, y_train)\n",
    "#logistic regression doesn't work here because output variable is continous. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[-4.44168081e-01 -1.38598650e+01  9.90824126e-01 -3.15277065e+01\n",
      " -4.39777966e+00  5.26165061e-01  3.33434464e-03  8.39320831e-01\n",
      "  9.36301401e-01 -2.05878368e+00 -2.65103373e+01  8.00625110e-01\n",
      " -4.16216060e+00  9.72253387e-01 -2.43820555e+03 -3.32804137e+00\n",
      " -2.80855636e+00  5.72922778e-01 -2.02299001e+00  9.89600189e-01]\n"
     ]
    }
   ],
   "source": [
    "### try with ensemble tree\n",
    "from sklearn import ensemble\n",
    "from sklearn.model_selection import cross_val_score\n",
    "ensemble_reg = ensemble.GradientBoostingRegressor(n_estimators = 400, max_depth = 5, min_samples_split = 2, learning_rate = 0.1, loss = 'ls')\n",
    "ens_reg = ensemble_reg.fit(X_train, y_train)\n",
    "ens_reg.score(X_train, y_train)\n",
    "CV_score = cross_val_score(ens_reg, X_train, y_train, cv=20)\n",
    "print(CV_score)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#RandomForest regressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 1.70047869e-01 -1.40817939e+01  9.37207610e-01 -1.44799398e+01\n",
      " -1.24857676e+00  8.96175645e-01 -2.49454806e+00  6.73261063e-01\n",
      "  9.27415576e-01 -3.67233123e-01 -1.27725537e+01  9.81373292e-01\n",
      " -3.06697904e+00  9.72332284e-01 -1.18320004e+03 -1.43754382e+00\n",
      " -2.26310182e+01 -1.44785557e+00 -1.28663407e-01  7.00849189e-01]\n"
     ]
    }
   ],
   "source": [
    "from sklearn import ensemble\n",
    "ensemble_reg = ensemble.RandomForestRegressor(max_depth=2, random_state=0, n_estimators=400)\n",
    "ens_reg = ensemble_reg.fit(X_train, y_train)\n",
    "ens_reg.score(X_train, y_train)\n",
    "CV_score = cross_val_score(ens_reg, X_train, y_train, cv=20)\n",
    "print(CV_score)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[-5.59300536e+00 -5.32548266e-01 -1.11074890e+03 -7.96041573e-01\n",
      " -2.12965166e-01 -1.62628845e+00 -4.48594293e+00 -5.14142153e+00\n",
      " -2.05922882e+02 -7.99747193e+01]\n"
     ]
    }
   ],
   "source": [
    "#MLP Regressor - \n",
    "from sklearn.neural_network import MLPRegressor\n",
    "mlp_reg = MLPRegressor(hidden_layer_sizes=(50,50), max_iter=10000).fit(X_train, y_train)\n",
    "CV_score = cross_val_score(mlp_reg, X_train, y_train, cv=10)\n"
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}