data_cleaning.ipynb

{
 "cells": [
  {
   "source": [
    "# Data Cleaning"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.options.display.max_columns = 50\n",
    "import matplotlib.pyplot as pyplot\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "import sklearn\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading `.csv` File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/extracted/crime-data-from-2010-to-present.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   DR Number        Date Reported        Date Occurred  Time Occurred  \\\n",
       "0  102005556  2010-01-25T00:00:00  2010-01-22T00:00:00           2300   \n",
       "1  101822289  2010-11-11T00:00:00  2010-11-10T00:00:00           1800   \n",
       "2  101105609  2010-01-28T00:00:00  2010-01-27T00:00:00           2230   \n",
       "3  101620051  2010-11-11T00:00:00  2010-11-07T00:00:00           1600   \n",
       "4  101910498  2010-04-07T00:00:00  2010-04-07T00:00:00           1600   \n",
       "\n",
       "   Area ID  Area Name  Reporting District  Crime Code Crime Code Description  \\\n",
       "0       20    Olympic                2071         510       VEHICLE - STOLEN   \n",
       "1       18  Southeast                1803         510       VEHICLE - STOLEN   \n",
       "2       11  Northeast                1125         510       VEHICLE - STOLEN   \n",
       "3       16   Foothill                1641         510       VEHICLE - STOLEN   \n",
       "4       19    Mission                1902         510       VEHICLE - STOLEN   \n",
       "\n",
       "  MO Codes  Victim Age Victim Sex Victim Descent  Premise Code  \\\n",
       "0      NaN           0        NaN            NaN         101.0   \n",
       "1      NaN           0        NaN            NaN         101.0   \n",
       "2      NaN           0        NaN            NaN         108.0   \n",
       "3      NaN           0        NaN            NaN         101.0   \n",
       "4      NaN           0        NaN            NaN         101.0   \n",
       "\n",
       "  Premise Description  Weapon Used Code Weapon Description Status Code  \\\n",
       "0              STREET               NaN                NaN          IC   \n",
       "1              STREET               NaN                NaN          IC   \n",
       "2         PARKING LOT               NaN                NaN          IC   \n",
       "3              STREET               NaN                NaN          IC   \n",
       "4              STREET               NaN                NaN          IC   \n",
       "\n",
       "  Status Description  Crime Code 1  Crime Code 2  Crime Code 3  Crime Code 4  \\\n",
       "0        Invest Cont         510.0           NaN           NaN           NaN   \n",
       "1        Invest Cont         510.0           NaN           NaN           NaN   \n",
       "2        Invest Cont         510.0           NaN           NaN           NaN   \n",
       "3        Invest Cont         510.0           NaN           NaN           NaN   \n",
       "4        Invest Cont         510.0           NaN           NaN           NaN   \n",
       "\n",
       "     Address Cross Street                                          Location   \n",
       "0   VAN NESS         15TH  {'latitude': '34.0454', 'needs_recoding': Fals...  \n",
       "1       88TH         WALL  {'latitude': '33.9572', 'needs_recoding': Fals...  \n",
       "2       YORK    AVENUE 51  {'latitude': '34.1211', 'needs_recoding': Fals...  \n",
       "3  EL DORADO    TRUESDALE  {'latitude': '34.241', 'needs_recoding': False...  \n",
       "4   GLENOAKS        DRELL  {'latitude': '34.3147', 'needs_recoding': Fals...  "
      ],
      "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>DR Number</th>\n      <th>Date Reported</th>\n      <th>Date Occurred</th>\n      <th>Time Occurred</th>\n      <th>Area ID</th>\n      <th>Area Name</th>\n      <th>Reporting District</th>\n      <th>Crime Code</th>\n      <th>Crime Code Description</th>\n      <th>MO Codes</th>\n      <th>Victim Age</th>\n      <th>Victim Sex</th>\n      <th>Victim Descent</th>\n      <th>Premise Code</th>\n      <th>Premise Description</th>\n      <th>Weapon Used Code</th>\n      <th>Weapon Description</th>\n      <th>Status Code</th>\n      <th>Status Description</th>\n      <th>Crime Code 1</th>\n      <th>Crime Code 2</th>\n      <th>Crime Code 3</th>\n      <th>Crime Code 4</th>\n      <th>Address</th>\n      <th>Cross Street</th>\n      <th>Location</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>102005556</td>\n      <td>2010-01-25T00:00:00</td>\n      <td>2010-01-22T00:00:00</td>\n      <td>2300</td>\n      <td>20</td>\n      <td>Olympic</td>\n      <td>2071</td>\n      <td>510</td>\n      <td>VEHICLE - STOLEN</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>510.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>VAN NESS</td>\n      <td>15TH</td>\n      <td>{'latitude': '34.0454', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>101822289</td>\n      <td>2010-11-11T00:00:00</td>\n      <td>2010-11-10T00:00:00</td>\n      <td>1800</td>\n      <td>18</td>\n      <td>Southeast</td>\n      <td>1803</td>\n      <td>510</td>\n      <td>VEHICLE - STOLEN</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>510.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>88TH</td>\n      <td>WALL</td>\n      <td>{'latitude': '33.9572', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>101105609</td>\n      <td>2010-01-28T00:00:00</td>\n      <td>2010-01-27T00:00:00</td>\n      <td>2230</td>\n      <td>11</td>\n      <td>Northeast</td>\n      <td>1125</td>\n      <td>510</td>\n      <td>VEHICLE - STOLEN</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>108.0</td>\n      <td>PARKING LOT</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>510.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>YORK</td>\n      <td>AVENUE 51</td>\n      <td>{'latitude': '34.1211', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>101620051</td>\n      <td>2010-11-11T00:00:00</td>\n      <td>2010-11-07T00:00:00</td>\n      <td>1600</td>\n      <td>16</td>\n      <td>Foothill</td>\n      <td>1641</td>\n      <td>510</td>\n      <td>VEHICLE - STOLEN</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>510.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>EL DORADO</td>\n      <td>TRUESDALE</td>\n      <td>{'latitude': '34.241', 'needs_recoding': False...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>101910498</td>\n      <td>2010-04-07T00:00:00</td>\n      <td>2010-04-07T00:00:00</td>\n      <td>1600</td>\n      <td>19</td>\n      <td>Mission</td>\n      <td>1902</td>\n      <td>510</td>\n      <td>VEHICLE - STOLEN</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>510.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>GLENOAKS</td>\n      <td>DRELL</td>\n      <td>{'latitude': '34.3147', 'needs_recoding': Fals...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1993259, 26)"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting `date occured` to Time Series Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing empty time stamp from Date Reported and Date Ocurred\n",
    "df[\"Date Reported\"] = df[\"Date Reported\"].str.replace('T00:00:00', '')\n",
    "df[\"Date Occurred\"] = df[\"Date Occurred\"].str.replace('T00:00:00', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting 'date occured' column as datetime object\n",
    "df['Date Occurred'] = pd.to_datetime(df['Date Occurred'], format= '%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index('Date Occurred').sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               DR Number Date Reported  Time Occurred  Area ID Area Name  \\\n",
       "Date Occurred                                                              \n",
       "2010-01-01     102120693    2010-11-17           1117       21   Topanga   \n",
       "2010-01-01     100504041    2010-01-02           2130        5    Harbor   \n",
       "2010-01-01     100504042    2010-01-02           1600        5    Harbor   \n",
       "\n",
       "               Reporting District  Crime Code  \\\n",
       "Date Occurred                                   \n",
       "2010-01-01                   2156         354   \n",
       "2010-01-01                    563         420   \n",
       "2010-01-01                    519         510   \n",
       "\n",
       "                                        Crime Code Description   MO Codes  \\\n",
       "Date Occurred                                                               \n",
       "2010-01-01                                   THEFT OF IDENTITY  0928 1822   \n",
       "2010-01-01     THEFT FROM MOTOR VEHICLE - PETTY ($950 & UNDER)        NaN   \n",
       "2010-01-01                                    VEHICLE - STOLEN        NaN   \n",
       "\n",
       "               Victim Age Victim Sex Victim Descent  Premise Code  \\\n",
       "Date Occurred                                                       \n",
       "2010-01-01             21          M              H         404.0   \n",
       "2010-01-01              0        NaN            NaN         104.0   \n",
       "2010-01-01              0        NaN            NaN         101.0   \n",
       "\n",
       "              Premise Description  Weapon Used Code Weapon Description  \\\n",
       "Date Occurred                                                            \n",
       "2010-01-01       DEPARTMENT STORE               NaN                NaN   \n",
       "2010-01-01               DRIVEWAY               NaN                NaN   \n",
       "2010-01-01                 STREET               NaN                NaN   \n",
       "\n",
       "              Status Code Status Description  Crime Code 1  Crime Code 2  \\\n",
       "Date Occurred                                                              \n",
       "2010-01-01             IC        Invest Cont         354.0           NaN   \n",
       "2010-01-01             IC        Invest Cont         420.0           NaN   \n",
       "2010-01-01             IC        Invest Cont         510.0           NaN   \n",
       "\n",
       "               Crime Code 3  Crime Code 4  \\\n",
       "Date Occurred                               \n",
       "2010-01-01              NaN           NaN   \n",
       "2010-01-01              NaN           NaN   \n",
       "2010-01-01              NaN           NaN   \n",
       "\n",
       "                                                Address Cross Street  \\\n",
       "Date Occurred                                                          \n",
       "2010-01-01     21800    VICTORY                      BL          NaN   \n",
       "2010-01-01       900 W  8TH                          ST          NaN   \n",
       "2010-01-01      1400 E  O                            ST          NaN   \n",
       "\n",
       "                                                       Location   \n",
       "Date Occurred                                                     \n",
       "2010-01-01     {'latitude': '34.1875', 'needs_recoding': Fals...  \n",
       "2010-01-01     {'latitude': '33.737', 'needs_recoding': False...  \n",
       "2010-01-01     {'latitude': '33.7926', 'needs_recoding': Fals...  "
      ],
      "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>DR Number</th>\n      <th>Date Reported</th>\n      <th>Time Occurred</th>\n      <th>Area ID</th>\n      <th>Area Name</th>\n      <th>Reporting District</th>\n      <th>Crime Code</th>\n      <th>Crime Code Description</th>\n      <th>MO Codes</th>\n      <th>Victim Age</th>\n      <th>Victim Sex</th>\n      <th>Victim Descent</th>\n      <th>Premise Code</th>\n      <th>Premise Description</th>\n      <th>Weapon Used Code</th>\n      <th>Weapon Description</th>\n      <th>Status Code</th>\n      <th>Status Description</th>\n      <th>Crime Code 1</th>\n      <th>Crime Code 2</th>\n      <th>Crime Code 3</th>\n      <th>Crime Code 4</th>\n      <th>Address</th>\n      <th>Cross Street</th>\n      <th>Location</th>\n    </tr>\n    <tr>\n      <th>Date Occurred</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2010-01-01</th>\n      <td>102120693</td>\n      <td>2010-11-17</td>\n      <td>1117</td>\n      <td>21</td>\n      <td>Topanga</td>\n      <td>2156</td>\n      <td>354</td>\n      <td>THEFT OF IDENTITY</td>\n      <td>0928 1822</td>\n      <td>21</td>\n      <td>M</td>\n      <td>H</td>\n      <td>404.0</td>\n      <td>DEPARTMENT STORE</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>354.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>21800    VICTORY                      BL</td>\n      <td>NaN</td>\n      <td>{'latitude': '34.1875', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100504041</td>\n      <td>2010-01-02</td>\n      <td>2130</td>\n      <td>5</td>\n      <td>Harbor</td>\n      <td>563</td>\n      <td>420</td>\n      <td>THEFT FROM MOTOR VEHICLE - PETTY ($950 &amp; UNDER)</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>104.0</td>\n      <td>DRIVEWAY</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>420.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>900 W  8TH                          ST</td>\n      <td>NaN</td>\n      <td>{'latitude': '33.737', 'needs_recoding': False...</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100504042</td>\n      <td>2010-01-02</td>\n      <td>1600</td>\n      <td>5</td>\n      <td>Harbor</td>\n      <td>519</td>\n      <td>510</td>\n      <td>VEHICLE - STOLEN</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>510.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1400 E  O                            ST</td>\n      <td>NaN</td>\n      <td>{'latitude': '33.7926', 'needs_recoding': Fals...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "# checking that it all worked\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the dataframe converted to a format for time series analysis, we can do basic data cleaning steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "DR Number                       0\n",
       "Date Reported                   0\n",
       "Time Occurred                   0\n",
       "Area ID                         0\n",
       "Area Name                       0\n",
       "Reporting District              0\n",
       "Crime Code                      0\n",
       "Crime Code Description          0\n",
       "MO Codes                   215872\n",
       "Victim Age                      0\n",
       "Victim Sex                 185960\n",
       "Victim Descent             186006\n",
       "Premise Code                   50\n",
       "Premise Description           135\n",
       "Weapon Used Code          1325641\n",
       "Weapon Description        1325642\n",
       "Status Code                     3\n",
       "Status Description              0\n",
       "Crime Code 1                    9\n",
       "Crime Code 2              1863098\n",
       "Crime Code 3              1990024\n",
       "Crime Code 4              1993163\n",
       "Address                         0\n",
       "Cross Street              1659507\n",
       "Location                        0\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "# checking for null values\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dropping columns with a majority of missing values\n",
    "df = df.drop(axis=1, columns=['MO Codes', \n",
    "                              'Weapon Used Code', \n",
    "                              'Weapon Description', \n",
    "                              'Crime Code 1', # same as feature 'Crime Code', can be easily dropped\n",
    "                              'Crime Code 2', \n",
    "                              'Crime Code 3', \n",
    "                              'Crime Code 4', \n",
    "                              'Cross Street',\n",
    "                             'Date Reported'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "victim_demo = ['Victim Age', 'Victim Sex', 'Victim Descent']\n",
    "geog_area= ['Area ID', 'Area Name']\n",
    "Crime = ['Crime Code', 'Crime Description']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "DR Number                      0\n",
       "Time Occurred                  0\n",
       "Area ID                        0\n",
       "Area Name                      0\n",
       "Reporting District             0\n",
       "Crime Code                     0\n",
       "Crime Code Description         0\n",
       "Victim Age                     0\n",
       "Victim Sex                185960\n",
       "Victim Descent            186006\n",
       "Premise Code                  50\n",
       "Premise Description          135\n",
       "Status Code                    3\n",
       "Status Description             0\n",
       "Address                        0\n",
       "Location                       0\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nDatetimeIndex: 1993259 entries, 2010-01-01 to 2019-06-22\nData columns (total 16 columns):\nDR Number                 int64\nTime Occurred             int64\nArea ID                   int64\nArea Name                 object\nReporting District        int64\nCrime Code                int64\nCrime Code Description    object\nVictim Age                int64\nVictim Sex                object\nVictim Descent            object\nPremise Code              float64\nPremise Description       object\nStatus Code               object\nStatus Description        object\nAddress                   object\nLocation                  object\ndtypes: float64(1), int64(6), object(9)\nmemory usage: 258.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like the 2019 Date reported values have been mistyped and input. May end up dropping this feature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Premise Code'].fillna(0, inplace = True)\n",
    "df['Premise Description'].fillna('UNKNOWN', inplace = True)\n",
    "df['Status Code'].fillna('UNK', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "             DR Number  Time Occurred        Area ID Reporting District  \\\n",
       "count    1993259.00000  1993259.00000  1993259.00000      1993259.00000   \n",
       "mean   145268085.51049     1361.52226       11.10423         1156.81916   \n",
       "std     27591418.13650      646.73725        6.00733          600.73388   \n",
       "min          210.00000        1.00000        1.00000          100.00000   \n",
       "25%    121204059.50000      930.00000        6.00000          644.00000   \n",
       "50%    150206637.00000     1430.00000       12.00000         1203.00000   \n",
       "75%    170710205.00000     1900.00000       16.00000         1672.00000   \n",
       "max    910220366.00000     2359.00000       21.00000         2199.00000   \n",
       "\n",
       "          Crime Code     Victim Age   Premise Code  \n",
       "count  1993259.00000  1993259.00000  1993259.00000  \n",
       "mean       506.92094       31.78082      311.15603  \n",
       "std        210.47090       20.60810      211.03603  \n",
       "min        110.00000       -9.00000        0.00000  \n",
       "25%        330.00000       20.00000      102.00000  \n",
       "50%        441.00000       32.00000      210.00000  \n",
       "75%        626.00000       46.00000      501.00000  \n",
       "max        956.00000      118.00000      971.00000  "
      ],
      "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>DR Number</th>\n      <th>Time Occurred</th>\n      <th>Area ID</th>\n      <th>Reporting District</th>\n      <th>Crime Code</th>\n      <th>Victim Age</th>\n      <th>Premise Code</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>1993259.00000</td>\n      <td>1993259.00000</td>\n      <td>1993259.00000</td>\n      <td>1993259.00000</td>\n      <td>1993259.00000</td>\n      <td>1993259.00000</td>\n      <td>1993259.00000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>145268085.51049</td>\n      <td>1361.52226</td>\n      <td>11.10423</td>\n      <td>1156.81916</td>\n      <td>506.92094</td>\n      <td>31.78082</td>\n      <td>311.15603</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>27591418.13650</td>\n      <td>646.73725</td>\n      <td>6.00733</td>\n      <td>600.73388</td>\n      <td>210.47090</td>\n      <td>20.60810</td>\n      <td>211.03603</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>210.00000</td>\n      <td>1.00000</td>\n      <td>1.00000</td>\n      <td>100.00000</td>\n      <td>110.00000</td>\n      <td>-9.00000</td>\n      <td>0.00000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>121204059.50000</td>\n      <td>930.00000</td>\n      <td>6.00000</td>\n      <td>644.00000</td>\n      <td>330.00000</td>\n      <td>20.00000</td>\n      <td>102.00000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>150206637.00000</td>\n      <td>1430.00000</td>\n      <td>12.00000</td>\n      <td>1203.00000</td>\n      <td>441.00000</td>\n      <td>32.00000</td>\n      <td>210.00000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>170710205.00000</td>\n      <td>1900.00000</td>\n      <td>16.00000</td>\n      <td>1672.00000</td>\n      <td>626.00000</td>\n      <td>46.00000</td>\n      <td>501.00000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>910220366.00000</td>\n      <td>2359.00000</td>\n      <td>21.00000</td>\n      <td>2199.00000</td>\n      <td>956.00000</td>\n      <td>118.00000</td>\n      <td>971.00000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "source": [
    "df.describe().apply(lambda s: s.apply('{0:.5f}'.format))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "M    920405\n",
       "F    840397\n",
       "X     46409\n",
       "H        70\n",
       "N        17\n",
       "-         1\n",
       "Name: Victim Sex, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "source": [
    "df['Victim Sex'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Victim Sex'].replace(to_replace=['H', '-', 'N'], value='X', inplace = True)\n",
    "df['Victim Sex'].fillna('X', inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "array(['M', 'X', 'F'], dtype=object)"
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "df['Victim Sex'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "465"
      ]
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "# df[(df['Victim Age'] < 0) | (df['Victim Age'] >= 80)]\n",
    "df[df['Victim Age'] < 0]['Victim Age'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Victim Age'] = np.where((df['Victim Age'] < 0), 0,df['Victim Age'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "df['Victim Age'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "H    685420\n",
       "W    483035\n",
       "B    316944\n",
       "O    191600\n",
       "X     68234\n",
       "A     47941\n",
       "K      8703\n",
       "F      2358\n",
       "C       884\n",
       "I       872\n",
       "J       352\n",
       "P       326\n",
       "U       183\n",
       "V       150\n",
       "Z       102\n",
       "G        79\n",
       "S        31\n",
       "D        21\n",
       "L        15\n",
       "-         3\n",
       "Name: Victim Descent, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "source": [
    "df['Victim Descent'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "descent_dict ={'A': 'Other Asian', 'B': 'Black', 'C': 'Chinese',\n",
    "               'D': 'Cambodian', 'F': 'Filipino', 'G': 'Guamanian',\n",
    "               'H': 'Latinx', 'I': 'Native', 'J': 'Japanese', \n",
    "               'K': 'Korean', 'L': 'Laotian', 'O': 'Other',\n",
    "               'P': 'Pacific Islander', 'S': 'Samoan', 'U': 'Hawaiian',\n",
    "               'V': 'Vietnamese', 'W': 'White',  'X': 'Unknown', 'Z':'Asian Indian'}\n",
    "df['Victim Descent'].fillna('X', inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Victim Descent'].replace(to_replace=['-'], value='X', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "DR Number                 0\n",
       "Time Occurred             0\n",
       "Area ID                   0\n",
       "Area Name                 0\n",
       "Reporting District        0\n",
       "Crime Code                0\n",
       "Crime Code Description    0\n",
       "Victim Age                0\n",
       "Victim Sex                0\n",
       "Victim Descent            0\n",
       "Premise Code              0\n",
       "Premise Description       0\n",
       "Status Code               0\n",
       "Status Description        0\n",
       "Address                   0\n",
       "Location                  0\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nDatetimeIndex: 1993259 entries, 2010-01-01 to 2019-06-22\nData columns (total 16 columns):\nDR Number                 int64\nTime Occurred             int64\nArea ID                   int64\nArea Name                 object\nReporting District        int64\nCrime Code                int64\nCrime Code Description    object\nVictim Age                int64\nVictim Sex                object\nVictim Descent            object\nPremise Code              float64\nPremise Description       object\nStatus Code               object\nStatus Description        object\nAddress                   object\nLocation                  object\ndtypes: float64(1), int64(6), object(9)\nmemory usage: 258.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               DR Number  Time Occurred  Area ID  Area Name  \\\n",
       "Date Occurred                                                 \n",
       "2010-01-01     100204011             50        2    Rampart   \n",
       "2010-01-01     100204018             30        2    Rampart   \n",
       "2010-01-01     100204019             10        2    Rampart   \n",
       "2010-01-01     100708906              1        7   Wilshire   \n",
       "2010-01-01     100708442              1        7   Wilshire   \n",
       "...                  ...            ...      ...        ...   \n",
       "2019-06-22     191610620             20       16   Foothill   \n",
       "2019-06-22     190811604              1        8    West LA   \n",
       "2019-06-22     191610688             10       16   Foothill   \n",
       "2019-06-22     191814725             15       18  Southeast   \n",
       "2019-06-22     190315339             30        3  Southwest   \n",
       "\n",
       "               Reporting District  Crime Code  \\\n",
       "Date Occurred                                   \n",
       "2010-01-01                    256         220   \n",
       "2010-01-01                    249         624   \n",
       "2010-01-01                    257         740   \n",
       "2010-01-01                    743         354   \n",
       "2010-01-01                    745         956   \n",
       "...                           ...         ...   \n",
       "2019-06-22                   1613         626   \n",
       "2019-06-22                    817         354   \n",
       "2019-06-22                   1659         330   \n",
       "2019-06-22                   1829         626   \n",
       "2019-06-22                    361         310   \n",
       "\n",
       "                                          Crime Code Description  Victim Age  \\\n",
       "Date Occurred                                                                  \n",
       "2010-01-01                                     ATTEMPTED ROBBERY          16   \n",
       "2010-01-01                              BATTERY - SIMPLE ASSAULT          29   \n",
       "2010-01-01     VANDALISM - FELONY ($400 & OVER, ALL CHURCH VA...          44   \n",
       "2010-01-01                                     THEFT OF IDENTITY          68   \n",
       "2010-01-01               LETTERS, LEWD  -  TELEPHONE CALLS, LEWD          26   \n",
       "...                                                          ...         ...   \n",
       "2019-06-22                     INTIMATE PARTNER - SIMPLE ASSAULT          36   \n",
       "2019-06-22                                     THEFT OF IDENTITY          30   \n",
       "2019-06-22                                 BURGLARY FROM VEHICLE          21   \n",
       "2019-06-22                     INTIMATE PARTNER - SIMPLE ASSAULT          33   \n",
       "2019-06-22                                              BURGLARY          23   \n",
       "\n",
       "              Victim Sex Victim Descent  Premise Code  \\\n",
       "Date Occurred                                           \n",
       "2010-01-01             M              H         102.0   \n",
       "2010-01-01             F              H         203.0   \n",
       "2010-01-01             F              B         502.0   \n",
       "2010-01-01             F              A         501.0   \n",
       "2010-01-01             M              W         502.0   \n",
       "...                  ...            ...           ...   \n",
       "2019-06-22             F              H         502.0   \n",
       "2019-06-22             F              O         101.0   \n",
       "2019-06-22             F              H         101.0   \n",
       "2019-06-22             F              B         502.0   \n",
       "2019-06-22             M              H         707.0   \n",
       "\n",
       "                                        Premise Description Status Code  \\\n",
       "Date Occurred                                                             \n",
       "2010-01-01                                         SIDEWALK          AA   \n",
       "2010-01-01                                   OTHER BUSINESS          IC   \n",
       "2010-01-01     MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)          IC   \n",
       "2010-01-01                           SINGLE FAMILY DWELLING          IC   \n",
       "2010-01-01     MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)          IC   \n",
       "...                                                     ...         ...   \n",
       "2019-06-22     MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)          IC   \n",
       "2019-06-22                                           STREET          IC   \n",
       "2019-06-22                                           STREET          IC   \n",
       "2019-06-22     MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)          IC   \n",
       "2019-06-22                                   GARAGE/CARPORT          IC   \n",
       "\n",
       "              Status Description                                   Address  \\\n",
       "Date Occurred                                                                \n",
       "2010-01-01          Adult Arrest           8TH                          ST   \n",
       "2010-01-01           Invest Cont   1200 W  5TH                          ST   \n",
       "2010-01-01           Invest Cont   1300    INGRAHAM                     ST   \n",
       "2010-01-01           Invest Cont   5800    SAN VICENTE                  BL   \n",
       "2010-01-01           Invest Cont   5600    WILSHIRE                     BL   \n",
       "...                          ...                                       ...   \n",
       "2019-06-22           Invest Cont  12700    VAN NUYS                     BL   \n",
       "2019-06-22           Invest Cont    900    HILGARD                      AV   \n",
       "2019-06-22           Invest Cont   9700    AMANITA                      AV   \n",
       "2019-06-22           Invest Cont   2100 E  97TH                         ST   \n",
       "2019-06-22           Invest Cont   4900    COLISEUM                     ST   \n",
       "\n",
       "                                                       Location   \n",
       "Date Occurred                                                     \n",
       "2010-01-01     {'latitude': '34.0549', 'needs_recoding': Fals...  \n",
       "2010-01-01     {'latitude': '34.0541', 'needs_recoding': Fals...  \n",
       "2010-01-01     {'latitude': '34.0531', 'needs_recoding': Fals...  \n",
       "2010-01-01     {'latitude': '34.0552', 'needs_recoding': Fals...  \n",
       "2010-01-01     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "...                                                          ...  \n",
       "2019-06-22     {'latitude': '34.2753', 'needs_recoding': Fals...  \n",
       "2019-06-22     {'latitude': '34.0617', 'needs_recoding': Fals...  \n",
       "2019-06-22     {'latitude': '34.2438', 'needs_recoding': Fals...  \n",
       "2019-06-22     {'latitude': '33.9482', 'needs_recoding': Fals...  \n",
       "2019-06-22     {'latitude': '34.0183', 'needs_recoding': Fals...  \n",
       "\n",
       "[82191 rows x 16 columns]"
      ],
      "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>DR Number</th>\n      <th>Time Occurred</th>\n      <th>Area ID</th>\n      <th>Area Name</th>\n      <th>Reporting District</th>\n      <th>Crime Code</th>\n      <th>Crime Code Description</th>\n      <th>Victim Age</th>\n      <th>Victim Sex</th>\n      <th>Victim Descent</th>\n      <th>Premise Code</th>\n      <th>Premise Description</th>\n      <th>Status Code</th>\n      <th>Status Description</th>\n      <th>Address</th>\n      <th>Location</th>\n    </tr>\n    <tr>\n      <th>Date Occurred</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100204011</td>\n      <td>50</td>\n      <td>2</td>\n      <td>Rampart</td>\n      <td>256</td>\n      <td>220</td>\n      <td>ATTEMPTED ROBBERY</td>\n      <td>16</td>\n      <td>M</td>\n      <td>H</td>\n      <td>102.0</td>\n      <td>SIDEWALK</td>\n      <td>AA</td>\n      <td>Adult Arrest</td>\n      <td>8TH                          ST</td>\n      <td>{'latitude': '34.0549', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100204018</td>\n      <td>30</td>\n      <td>2</td>\n      <td>Rampart</td>\n      <td>249</td>\n      <td>624</td>\n      <td>BATTERY - SIMPLE ASSAULT</td>\n      <td>29</td>\n      <td>F</td>\n      <td>H</td>\n      <td>203.0</td>\n      <td>OTHER BUSINESS</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>1200 W  5TH                          ST</td>\n      <td>{'latitude': '34.0541', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100204019</td>\n      <td>10</td>\n      <td>2</td>\n      <td>Rampart</td>\n      <td>257</td>\n      <td>740</td>\n      <td>VANDALISM - FELONY ($400 &amp; OVER, ALL CHURCH VA...</td>\n      <td>44</td>\n      <td>F</td>\n      <td>B</td>\n      <td>502.0</td>\n      <td>MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>1300    INGRAHAM                     ST</td>\n      <td>{'latitude': '34.0531', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100708906</td>\n      <td>1</td>\n      <td>7</td>\n      <td>Wilshire</td>\n      <td>743</td>\n      <td>354</td>\n      <td>THEFT OF IDENTITY</td>\n      <td>68</td>\n      <td>F</td>\n      <td>A</td>\n      <td>501.0</td>\n      <td>SINGLE FAMILY DWELLING</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>5800    SAN VICENTE                  BL</td>\n      <td>{'latitude': '34.0552', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100708442</td>\n      <td>1</td>\n      <td>7</td>\n      <td>Wilshire</td>\n      <td>745</td>\n      <td>956</td>\n      <td>LETTERS, LEWD  -  TELEPHONE CALLS, LEWD</td>\n      <td>26</td>\n      <td>M</td>\n      <td>W</td>\n      <td>502.0</td>\n      <td>MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>5600    WILSHIRE                     BL</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>2019-06-22</th>\n      <td>191610620</td>\n      <td>20</td>\n      <td>16</td>\n      <td>Foothill</td>\n      <td>1613</td>\n      <td>626</td>\n      <td>INTIMATE PARTNER - SIMPLE ASSAULT</td>\n      <td>36</td>\n      <td>F</td>\n      <td>H</td>\n      <td>502.0</td>\n      <td>MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>12700    VAN NUYS                     BL</td>\n      <td>{'latitude': '34.2753', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2019-06-22</th>\n      <td>190811604</td>\n      <td>1</td>\n      <td>8</td>\n      <td>West LA</td>\n      <td>817</td>\n      <td>354</td>\n      <td>THEFT OF IDENTITY</td>\n      <td>30</td>\n      <td>F</td>\n      <td>O</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900    HILGARD                      AV</td>\n      <td>{'latitude': '34.0617', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2019-06-22</th>\n      <td>191610688</td>\n      <td>10</td>\n      <td>16</td>\n      <td>Foothill</td>\n      <td>1659</td>\n      <td>330</td>\n      <td>BURGLARY FROM VEHICLE</td>\n      <td>21</td>\n      <td>F</td>\n      <td>H</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>9700    AMANITA                      AV</td>\n      <td>{'latitude': '34.2438', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2019-06-22</th>\n      <td>191814725</td>\n      <td>15</td>\n      <td>18</td>\n      <td>Southeast</td>\n      <td>1829</td>\n      <td>626</td>\n      <td>INTIMATE PARTNER - SIMPLE ASSAULT</td>\n      <td>33</td>\n      <td>F</td>\n      <td>B</td>\n      <td>502.0</td>\n      <td>MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>2100 E  97TH                         ST</td>\n      <td>{'latitude': '33.9482', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2019-06-22</th>\n      <td>190315339</td>\n      <td>30</td>\n      <td>3</td>\n      <td>Southwest</td>\n      <td>361</td>\n      <td>310</td>\n      <td>BURGLARY</td>\n      <td>23</td>\n      <td>M</td>\n      <td>H</td>\n      <td>707.0</td>\n      <td>GARAGE/CARPORT</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>4900    COLISEUM                     ST</td>\n      <td>{'latitude': '34.0183', 'needs_recoding': Fals...</td>\n    </tr>\n  </tbody>\n</table>\n<p>82191 rows × 16 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "source": [
    "df[df['Time Occurred'] < 100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "140"
      ]
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "df['Crime Code Description'].value_counts().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "DR Number                 1993259\n",
       "Time Occurred                1439\n",
       "Area ID                        21\n",
       "Area Name                      21\n",
       "Reporting District           1294\n",
       "Crime Code                    140\n",
       "Crime Code Description        140\n",
       "Victim Age                    101\n",
       "Victim Sex                      3\n",
       "Victim Descent                 19\n",
       "Premise Code                  325\n",
       "Premise Description           320\n",
       "Status Code                    10\n",
       "Status Description              6\n",
       "Address                     74330\n",
       "Location                    63249\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "df.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dictionary_maker(key_feature, value_feature):\n",
    "    '''\n",
    "    this function takes in two features\n",
    "    and returns a dictionary with the unique values\n",
    "    from the two features as key and values\n",
    "    '''\n",
    "    # dictionary key\n",
    "    key= list(df[key_feature].value_counts().index)\n",
    "    \n",
    "    #dictionary value\n",
    "    value= list(df[value_feature].value_counts().index)\n",
    "    \n",
    "    #dictionary\n",
    "    dictionary = {key[i]:value[i] for i in range(len(key))}\n",
    "    \n",
    "    return dictionary\n",
    "\n",
    "# def pickle_dictionary(dict_to_pickle, pickled_dict_name):\n",
    "#     '''\n",
    "#     Pickles a dictionary object \n",
    "#     '''\n",
    "#     pickle_out = open(str(pickled_dict_name),\"wb\")\n",
    "#     pickle.dump(dict_to_pickle, pickle_out)\n",
    "#     pickle_out.close()\n",
    "\n",
    "# pickle.dump( status_dict, open( \"status_dict.p\", \"wb\" ) )\n",
    "# pickle_in = open(\"dict.pickle\",\"rb\")\n",
    "# example_dict = pickle.load(pickle_in)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "area_dict = dictionary_maker('Area ID', 'Area Name')\n",
    "crime_dict = dictionary_maker('Crime Code', 'Crime Code Description')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "source": [
    "df['Premise Description'].isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "IC     1538964\n",
       "AO      229649\n",
       "AA      204515\n",
       "JA       15193\n",
       "JO        4907\n",
       "CC          25\n",
       "UNK          3\n",
       "19           1\n",
       "TH           1\n",
       "13           1\n",
       "Name: Status Code, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 33
    }
   ],
   "source": [
    "df['Status Code'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Status Code'].replace(to_replace=['CC', 'TH', '19', '13'], value='UNK', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Invest Cont     1538964\n",
       "Adult Other      229649\n",
       "Adult Arrest     204515\n",
       "Juv Arrest        15193\n",
       "Juv Other          4907\n",
       "UNK                  31\n",
       "Name: Status Description, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 35
    }
   ],
   "source": [
    "df['Status Description'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'IC': 'Invest Cont',\n",
       " 'AO': 'Adult Other',\n",
       " 'AA': 'Adult Arrest',\n",
       " 'JA': 'Juv Arrest',\n",
       " 'JO': 'Juv Other',\n",
       " 'UNK': 'UNK'}"
      ]
     },
     "metadata": {},
     "execution_count": 36
    }
   ],
   "source": [
    "status_dict = dictionary_maker('Status Code', 'Status Description')\n",
    "status_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pickle_dictionary(area_dict, area_dict1)\n",
    "# pickle.dump( status_dict, open( \"status_dict.p\", \"wb\" ) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               DR Number  Time Occurred  Area ID  Area Name  \\\n",
       "Date Occurred                                                 \n",
       "2017-06-02     170115839            120        1    Central   \n",
       "2017-07-03     170118828           1930        1    Central   \n",
       "2017-10-27     170128107           2315        1    Central   \n",
       "2017-11-22     170130221           2100        1    Central   \n",
       "2017-11-30     170130744           1600        1    Central   \n",
       "2017-12-26     170132653           1705        1    Central   \n",
       "2018-02-16     180307134           1930        3  Southwest   \n",
       "2018-02-28     180108707           1730        1    Central   \n",
       "2018-05-09     180113331            715        1    Central   \n",
       "2018-07-16     180119129           1200        1    Central   \n",
       "2018-08-17     180121605           1950        1    Central   \n",
       "2018-12-03     180129460           1735        1    Central   \n",
       "2019-02-04     190106657           2200        1    Central   \n",
       "\n",
       "               Reporting District  Crime Code  \\\n",
       "Date Occurred                                   \n",
       "2017-06-02                    111         350   \n",
       "2017-07-03                    111         440   \n",
       "2017-10-27                    111         624   \n",
       "2017-11-22                    111         480   \n",
       "2017-11-30                    111         624   \n",
       "2017-12-26                    111         624   \n",
       "2018-02-16                    334         902   \n",
       "2018-02-28                    111         740   \n",
       "2018-05-09                    111         860   \n",
       "2018-07-16                    118         740   \n",
       "2018-08-17                    111         745   \n",
       "2018-12-03                    111         624   \n",
       "2019-02-04                    111         624   \n",
       "\n",
       "                                          Crime Code Description  Victim Age  \\\n",
       "Date Occurred                                                                  \n",
       "2017-06-02                                         THEFT, PERSON          26   \n",
       "2017-07-03                    THEFT PLAIN - PETTY ($950 & UNDER)          37   \n",
       "2017-10-27                              BATTERY - SIMPLE ASSAULT          41   \n",
       "2017-11-22                                         BIKE - STOLEN          52   \n",
       "2017-11-30                              BATTERY - SIMPLE ASSAULT          56   \n",
       "2017-12-26                              BATTERY - SIMPLE ASSAULT          18   \n",
       "2018-02-16              VIOLATION OF TEMPORARY RESTRAINING ORDER          51   \n",
       "2018-02-28     VANDALISM - FELONY ($400 & OVER, ALL CHURCH VA...           0   \n",
       "2018-05-09                           BATTERY WITH SEXUAL CONTACT          48   \n",
       "2018-07-16     VANDALISM - FELONY ($400 & OVER, ALL CHURCH VA...          33   \n",
       "2018-08-17              VANDALISM - MISDEAMEANOR ($399 OR UNDER)           0   \n",
       "2018-12-03                              BATTERY - SIMPLE ASSAULT          51   \n",
       "2019-02-04                              BATTERY - SIMPLE ASSAULT          48   \n",
       "\n",
       "              Victim Sex Victim Descent  Premise Code  \\\n",
       "Date Occurred                                           \n",
       "2017-06-02             M              H         967.0   \n",
       "2017-07-03             M              H         967.0   \n",
       "2017-10-27             M              H         967.0   \n",
       "2017-11-22             M              H         967.0   \n",
       "2017-11-30             M              W         967.0   \n",
       "2017-12-26             M              H         967.0   \n",
       "2018-02-16             F              B         967.0   \n",
       "2018-02-28             M              X         967.0   \n",
       "2018-05-09             F              H         967.0   \n",
       "2018-07-16             M              H         967.0   \n",
       "2018-08-17             X              X         967.0   \n",
       "2018-12-03             F              W         967.0   \n",
       "2019-02-04             M              H         967.0   \n",
       "\n",
       "                       Premise Description Status Code Status Description  \\\n",
       "Date Occurred                                                               \n",
       "2017-06-02     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2017-07-03     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2017-10-27     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2017-11-22     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2017-11-30     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2017-12-26     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2018-02-16     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2018-02-28     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2018-05-09     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2018-07-16     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2018-08-17     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2018-12-03     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "2019-02-04     MTA - GOLD LINE - CHINATOWN          IC        Invest Cont   \n",
       "\n",
       "                                               Address  \\\n",
       "Date Occurred                                            \n",
       "2017-06-02                                     ALAMEDA   \n",
       "2017-07-03      900 N  SPRING                       ST   \n",
       "2017-10-27             1ST                          ST   \n",
       "2017-11-22      900 N  SPRING                       ST   \n",
       "2017-11-30      900 N  SPRING                       ST   \n",
       "2017-12-26      900 N  SPRING                       ST   \n",
       "2018-02-16     3400 S  BRONSON                      AV   \n",
       "2018-02-28      900 N  SPRING                       ST   \n",
       "2018-05-09      900 N  SPRING                       ST   \n",
       "2018-07-16                                     ALAMEDA   \n",
       "2018-08-17      900 N  SPRING                       ST   \n",
       "2018-12-03      900 N  SPRING                       ST   \n",
       "2019-02-04             COLLEGE                      ST   \n",
       "\n",
       "                                                       Location   \n",
       "Date Occurred                                                     \n",
       "2017-06-02     {'latitude': '34.0615', 'needs_recoding': Fals...  \n",
       "2017-07-03     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2017-10-27     {'latitude': '34.0615', 'needs_recoding': Fals...  \n",
       "2017-11-22     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2017-11-30     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2017-12-26     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2018-02-16     {'latitude': '34.0256', 'needs_recoding': Fals...  \n",
       "2018-02-28     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2018-05-09     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2018-07-16     {'latitude': '34.0635', 'needs_recoding': Fals...  \n",
       "2018-08-17     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2018-12-03     {'latitude': '34.0624', 'needs_recoding': Fals...  \n",
       "2019-02-04     {'latitude': '34.0637', 'needs_recoding': Fals...  "
      ],
      "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>DR Number</th>\n      <th>Time Occurred</th>\n      <th>Area ID</th>\n      <th>Area Name</th>\n      <th>Reporting District</th>\n      <th>Crime Code</th>\n      <th>Crime Code Description</th>\n      <th>Victim Age</th>\n      <th>Victim Sex</th>\n      <th>Victim Descent</th>\n      <th>Premise Code</th>\n      <th>Premise Description</th>\n      <th>Status Code</th>\n      <th>Status Description</th>\n      <th>Address</th>\n      <th>Location</th>\n    </tr>\n    <tr>\n      <th>Date Occurred</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2017-06-02</th>\n      <td>170115839</td>\n      <td>120</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>350</td>\n      <td>THEFT, PERSON</td>\n      <td>26</td>\n      <td>M</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>ALAMEDA</td>\n      <td>{'latitude': '34.0615', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2017-07-03</th>\n      <td>170118828</td>\n      <td>1930</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>440</td>\n      <td>THEFT PLAIN - PETTY ($950 &amp; UNDER)</td>\n      <td>37</td>\n      <td>M</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2017-10-27</th>\n      <td>170128107</td>\n      <td>2315</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>624</td>\n      <td>BATTERY - SIMPLE ASSAULT</td>\n      <td>41</td>\n      <td>M</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>1ST                          ST</td>\n      <td>{'latitude': '34.0615', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2017-11-22</th>\n      <td>170130221</td>\n      <td>2100</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>480</td>\n      <td>BIKE - STOLEN</td>\n      <td>52</td>\n      <td>M</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2017-11-30</th>\n      <td>170130744</td>\n      <td>1600</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>624</td>\n      <td>BATTERY - SIMPLE ASSAULT</td>\n      <td>56</td>\n      <td>M</td>\n      <td>W</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2017-12-26</th>\n      <td>170132653</td>\n      <td>1705</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>624</td>\n      <td>BATTERY - SIMPLE ASSAULT</td>\n      <td>18</td>\n      <td>M</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2018-02-16</th>\n      <td>180307134</td>\n      <td>1930</td>\n      <td>3</td>\n      <td>Southwest</td>\n      <td>334</td>\n      <td>902</td>\n      <td>VIOLATION OF TEMPORARY RESTRAINING ORDER</td>\n      <td>51</td>\n      <td>F</td>\n      <td>B</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>3400 S  BRONSON                      AV</td>\n      <td>{'latitude': '34.0256', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2018-02-28</th>\n      <td>180108707</td>\n      <td>1730</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>740</td>\n      <td>VANDALISM - FELONY ($400 &amp; OVER, ALL CHURCH VA...</td>\n      <td>0</td>\n      <td>M</td>\n      <td>X</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2018-05-09</th>\n      <td>180113331</td>\n      <td>715</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>860</td>\n      <td>BATTERY WITH SEXUAL CONTACT</td>\n      <td>48</td>\n      <td>F</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2018-07-16</th>\n      <td>180119129</td>\n      <td>1200</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>118</td>\n      <td>740</td>\n      <td>VANDALISM - FELONY ($400 &amp; OVER, ALL CHURCH VA...</td>\n      <td>33</td>\n      <td>M</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>ALAMEDA</td>\n      <td>{'latitude': '34.0635', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2018-08-17</th>\n      <td>180121605</td>\n      <td>1950</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>745</td>\n      <td>VANDALISM - MISDEAMEANOR ($399 OR UNDER)</td>\n      <td>0</td>\n      <td>X</td>\n      <td>X</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2018-12-03</th>\n      <td>180129460</td>\n      <td>1735</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>624</td>\n      <td>BATTERY - SIMPLE ASSAULT</td>\n      <td>51</td>\n      <td>F</td>\n      <td>W</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 N  SPRING                       ST</td>\n      <td>{'latitude': '34.0624', 'needs_recoding': Fals...</td>\n    </tr>\n    <tr>\n      <th>2019-02-04</th>\n      <td>190106657</td>\n      <td>2200</td>\n      <td>1</td>\n      <td>Central</td>\n      <td>111</td>\n      <td>624</td>\n      <td>BATTERY - SIMPLE ASSAULT</td>\n      <td>48</td>\n      <td>M</td>\n      <td>H</td>\n      <td>967.0</td>\n      <td>MTA - GOLD LINE - CHINATOWN</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>COLLEGE                      ST</td>\n      <td>{'latitude': '34.0637', 'needs_recoding': Fals...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 38
    }
   ],
   "source": [
    "pd.set_option('display.max_rows', None)\n",
    "# df['Premise Code'].value_counts()\n",
    "df[df['Premise Code']== 967]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                                                  2627\n",
       "LIBRARY                                                              2525\n",
       "NURSING/CONVALESCENT/RETIREMENT HOME                                 2424\n",
       "AUTO REPAIR SHOP                                                     2290\n",
       "STORAGE SHED                                                         2188\n",
       "COFFEE SHOP (STARBUCKS, COFFEE BEAN, PEET'S, ETC.)                   2110\n",
       "SPECIALTY SCHOOL/OTHER                                               2084\n",
       "WAREHOUSE                                                            1932\n",
       "BEAUTY/BARBER SHOP                                                   1906\n",
       "GROUP HOME                                                           1772\n",
       "MTA BUS                                                              1712\n",
       "DISCOUNT STORE (99 CENT,DOLLAR,ETC.                                  1613\n",
       "MOBILE HOME/TRAILERS/CONSTRUCTION TRAILERS/RV'S/MOTORHOME            1487\n",
       "HARDWARE/BUILDING SUPPLY                                             1462\n",
       "MISSIONS/SHELTERS                                                    1393\n",
       "BEACH                                                                1373\n",
       "PROJECT/TENEMENT/PUBLIC HOUSING                                      1286\n",
       "BAR/SPORTS BAR (OPEN DAY & NIGHT)                                    1236\n",
       "CHECK CASHING*                                                       1199\n",
       "MEMBERSHIP STORE (COSTCO,SAMS CLUB)*                                 1126\n",
       "AUTO SALES LOT                                                       1077\n",
       "THEATRE/MOVIE                                                         974\n",
       "JEWELRY STORE                                                         945\n",
       "MEDICAL MARIJUANA FACILITIES/BUSINESSES                               932\n",
       "PRIVATE SCHOOL/PRESCHOOL                                              905\n",
       "FREEWAY                                                               898\n",
       "CAR WASH                                                              897\n",
       "AUTOMATED TELLER MACHINE (ATM)                                        892\n",
       "AUTO SUPPLY STORE*                                                    795\n",
       "PATIO*                                                                727\n",
       "BEAUTY SUPPLY STORE                                                   646\n",
       "APARTMENT/CONDO COMMON LAUNDRY ROOM                                   629\n",
       "PHARMACY INSIDE STORE OR SUPERMARKET*                                 607\n",
       "TOBACCO SHOP                                                          594\n",
       "NAIL SALON                                                            590\n",
       "MAIL BOX                                                              573\n",
       "VACANT LOT                                                            552\n",
       "DODGER STADIUM                                                        545\n",
       "BUS STOP/LAYOVER (ALSO QUERY 124)                                     536\n",
       "PUBLIC RESTROOM(INDOORS-INSIDE)                                       536\n",
       "THE GROVE                                                             517\n",
       "ELECTRONICS STORE (IE:RADIO SHACK, ETC.)                              515\n",
       "DETENTION/JAIL FACILITY                                               487\n",
       "THE BEVERLY CONNECTION                                                464\n",
       "SLIPS/DOCK/MARINA/BOAT                                                462\n",
       "BUS-CHARTER/PRIVATE                                                   453\n",
       "FRAT HOUSE/SORORITY/DORMITORY                                         451\n",
       "SWAP MEET                                                             444\n",
       "TRANSITIONAL HOUSING/HALFWAY HOUSE                                    441\n",
       "PUBLIC RESTROOM/OUTSIDE*                                              439\n",
       "CONVENTION CENTER                                                     421\n",
       "POST OFFICE                                                           420\n",
       "CLEANER/LAUNDROMAT                                                    417\n",
       "RECYCLING CENTER                                                      391\n",
       "LA UNION STATION (NOT LINE SPECIFIC)                                  382\n",
       "MANUFACTURING COMPANY                                                 377\n",
       "BUS, SCHOOL, CHURCH                                                   371\n",
       "THE BEVERLY CENTER                                                    361\n",
       "SYNAGOGUE/TEMPLE                                                      353\n",
       "SINGLE RESIDENCE OCCUPANCY (SRO'S) LOCATIONS                          352\n",
       "TOW YARD*                                                             349\n",
       "TRADE SCHOOL (MEDICAL-TECHNICAL-BUSINESS)*                            340\n",
       "MASSAGE PARLOR                                                        339\n",
       "AUTO DEALERSHIP (CHEVY, FORD, BMW, MERCEDES, ETC.)                    320\n",
       "TV/RADIO/APPLIANCE                                                    312\n",
       "ENTERTAINMENT/COMEDY CLUB (OTHER)                                     310\n",
       "COMPUTER SERVICES/REPAIRS/SALES                                       292\n",
       "AIRCRAFT                                                              286\n",
       "MTA PROPERTY OR PARKING LOT                                           285\n",
       "STUDIO (FILM/PHOTOGRAPHIC/MUSIC)                                      283\n",
       "ABANDONED BUILDING ABANDONED HOUSE                                    273\n",
       "BALCONY*                                                              272\n",
       "NURSERY/FLOWER SHOP                                                   270\n",
       "BUS DEPOT/TERMINAL, OTHER THAN MTA                                    269\n",
       "SEX ORIENTED/BOOK STORE/STRIP CLUB/GENTLEMAN'S CLUB                   267\n",
       "PET STORE                                                             263\n",
       "PEDESTRIAN OVERCROSSING                                               256\n",
       "STAIRWELL*                                                            248\n",
       "GUN/SPORTING GOODS                                                    247\n",
       "POOL-PUBLIC/OUTDOOR OR INDOOR*                                        236\n",
       "GOLF COURSE*                                                          232\n",
       "DAY CARE/CHILDREN*                                                    230\n",
       "PAWN SHOP                                                             224\n",
       "ELEVATOR                                                              219\n",
       "TERMINAL, OTHER THAN MTA                                              215\n",
       "STAPLES CENTER *                                                      214\n",
       "FACTORY                                                               210\n",
       "VISION CARE FACILITY*                                                 203\n",
       "TAXI                                                                  198\n",
       "FIRE STATION                                                          196\n",
       "TRANSIENT ENCAMPMENT                                                  189\n",
       "BOWLING ALLEY*                                                        179\n",
       "COLISEUM                                                              176\n",
       "TRAIN DEPOT/TERMINAL, OTHER THAN MTA                                  175\n",
       "CEMETARY*                                                             170\n",
       "DAY CARE/ADULTS*                                                      166\n",
       "MTA - RED LINE - UNION STATION                                        159\n",
       "TATTOO PARLOR*                                                        155\n",
       "DELIVERY SERVICE (FED EX, UPS, COURIERS,COURIER SERVICE)*             154\n",
       "MUSEUM                                                                153\n",
       "MTA - RED LINE - WESTLAKE/MACARTHUR PARK                              152\n",
       "DRIVE THRU*                                                           151\n",
       "METROLINK TRAIN                                                       147\n",
       "FOSTER HOME BOYS OR GIRLS*                                            146\n",
       "MTA - RED LINE - NORTH HOLLYWOOD                                      145\n",
       "FURNITURE STORE                                                       142\n",
       "MUNICIPAL BUS LINE INCLUDES LADOT/DASH                                138\n",
       "EQUIPMENT RENTAL                                                      135\n",
       "UNKNOWN                                                               135\n",
       "TRAIN TRACKS                                                          132\n",
       "MTA - RED LINE - 7TH AND METRO CENTER                                 131\n",
       "OPTICAL OFFICE INSIDE STORE OR SUPERMARKET*                           131\n",
       "UNDERPASS/BRIDGE*                                                     131\n",
       "RIVER BED*                                                            123\n",
       "SKATEBOARD FACILITY/SKATEBOARD PARK*                                  120\n",
       "CREDIT UNION                                                          120\n",
       "MTA - RED LINE - HOLLYWOOD/VINE                                       118\n",
       "TRAIN, OTHER THAN MTA (ALSO QUERY 809/810/811)                        104\n",
       "MTA - RED LINE - HOLLYWOOD/HIGHLAND                                   100\n",
       "SPORTS VENUE, OTHER                                                    99\n",
       "MTA - EXPO LINE - EXPO/WESTERN                                         99\n",
       "CYBERSPACE                                                             98\n",
       "VALET                                                                  89\n",
       "MOSQUE*                                                                89\n",
       "MTA - RED LINE - PERSHING SQUARE                                       87\n",
       "GREYHOUND OR INTERSTATE BUS                                            84\n",
       "TOOL SHED*                                                             83\n",
       "SAVINGS & LOAN                                                         83\n",
       "SPORTS ARENA                                                           82\n",
       "VIDEO RENTAL STORE                                                     80\n",
       "ARCADE,GAME ROOM/VIDEO GAMES (EXAMPLE CHUCKIE CHEESE)*                 78\n",
       "VEHICLE STORAGE LOT (CARS, TRUCKS, RV'S, BOATS, TRAILERS, ETC.)        74\n",
       "VETERINARIAN/ANIMAL HOSPITAL                                           73\n",
       "OTHER RR TRAIN (UNION PAC, SANTE FE ETC                                72\n",
       "TELECOMMUNICATION FACILITY/LOCATION                                    72\n",
       "OTHER PLACE OF WORSHIP                                                 71\n",
       "WEBSITE                                                                67\n",
       "MTA - BLUE LINE - 7TH AND METRO CENTER                                 66\n",
       "MTA - RED LINE - WILSHIRE/VERMONT                                      64\n",
       "MTA - RED LINE - VERMONT/SANTA MONICA                                  62\n",
       "GARMENT MANUFACTURER                                                   61\n",
       "MTA - RED LINE - VERMONT/BEVERLY                                       61\n",
       "7TH AND METRO CENTER (NOT LINE SPECIFIC)                               60\n",
       "FINANCE COMPANY                                                        60\n",
       "RECORD-CD MUSIC/COMPUTER GAME STORE                                    60\n",
       "MTA - EXPO LINE - EXPO/VERMONT                                         58\n",
       "HOSPICE                                                                58\n",
       "CATERING/ICE CREAM TRUCK                                               57\n",
       "SHORT-TERM VACATION RENTAL                                             56\n",
       "MTA - EXPO LINE - EXPO/CRENSHAW                                        56\n",
       "MORTUARY                                                               55\n",
       "MTA - RED LINE - VERMONT/SUNSET                                        55\n",
       "BOOK STORE                                                             54\n",
       "TUNNEL                                                                 53\n",
       "MTA - EXPO LINE - EXPO/LA BREA                                         45\n",
       "MTA - EXPO LINE - LA CIENEGA/JEFFERSON                                 45\n",
       "BASKETBALL COURTS                                                      44\n",
       "MTA - EXPO LINE - 7TH AND METRO CENTER                                 44\n",
       "MTA - RED LINE - HOLLYWOOD/WESTERN                                     44\n",
       "MTA - BLUE LINE - 103RD/WATTS TOWERS                                   43\n",
       "MTA - EXPO LINE - FARMDALE                                             43\n",
       "MASS GATHERING LOCATION                                                42\n",
       "MTA - GREEN LINE - AVALON                                              39\n",
       "OIL REFINERY                                                           39\n",
       "MTA - GREEN LINE - HARBOR FWY                                          39\n",
       "MTA - RED LINE - CIVIC CENTER/GRAND PARK                               38\n",
       "MTA - BLUE LINE - WASHINGTON                                           36\n",
       "MTA - RED LINE - UNIVERSAL CITY/STUDIO CITY                            36\n",
       "MTA - EXPO LINE - EXPO/BUNDY                                           36\n",
       "MTA - EXPO LINE - JEFFERSON/USC                                        35\n",
       "WATER FACILITY                                                         35\n",
       "MTA - EXPO LINE - EXPO/SEPULVEDA                                       34\n",
       "SKATING RINK*                                                          34\n",
       "MTA - EXPO LINE - EXPO PARK/USC                                        34\n",
       "REDLINE SUBWAY PLATFORM                                                32\n",
       "MTA - ORANGE LINE - NORTH HOLLYWOOD                                    30\n",
       "MTA - GOLD LINE - UNION STATION                                        30\n",
       "PAY PHONE                                                              30\n",
       "MTA - BLUE LINE - PICO                                                 30\n",
       "MTA - GOLD LINE - MARIACHI PLAZA                                       28\n",
       "TRUCK, COMMERICAL                                                      27\n",
       "BANKING INSIDE MARKET-STORE *                                          27\n",
       "ABORTION CLINIC/ABORTION FACILITY*                                     26\n",
       "REDLINE ENTRANCE/EXIT                                                  26\n",
       "AMUSEMENT PARK*                                                        26\n",
       "MTA - PURPLE LINE - UNION STATION                                      25\n",
       "MTA - ORANGE LINE - VAN NUYS                                           25\n",
       "MTA - PURPLE LINE - WILSHIRE/VERMONT                                   24\n",
       "MTA - EXPO LINE - LATTC/ORTHO INSTITUTE                                23\n",
       "MTA - PURPLE LINE - WILSHIRE/WESTERN                                   23\n",
       "ENERGY PLANT/FACILITY                                                  22\n",
       "MTA - EXPO LINE - WESTWOOD/RANCHO PARK                                 22\n",
       "MTA - EXPO LINE - PALMS                                                22\n",
       "AMTRAK TRAIN                                                           22\n",
       "MTA - EXPO LINE - PICO                                                 22\n",
       "MTA - ORANGE LINE - CANOGA                                             21\n",
       "MTA - GOLD LINE - SOTO                                                 21\n",
       "MTA - GREEN LINE - AVIATION/LAX                                        21\n",
       "MTA - PURPLE LINE - WILSHIRE/NORMANDIE                                 20\n",
       "ABATEMENT LOCATION                                                     20\n",
       "DAM/RESERVOIR                                                          20\n",
       "HIGH-RISE BUILDING                                                     19\n",
       "MTA - GOLD LINE - HIGHLAND PARK                                        19\n",
       "RETIRED (DUPLICATE) DO NOT USE THIS CODE                               18\n",
       "MTA - PURPLE LINE - PERSHING SQUARE                                    18\n",
       "MTA - BLUE LINE - SAN PEDRO                                            18\n",
       "OTHER INTERSTATE, CHARTER BUS                                          17\n",
       "MTA - ORANGE LINE - BALBOA                                             16\n",
       "DRIVE THRU BANKING (WINDOW)*                                           16\n",
       "METHADONE CLINIC                                                       16\n",
       "TRASH CAN/TRASH DUMPSTER                                               15\n",
       "MTA - GOLD LINE - LINCOLN/CYPRESS                                      15\n",
       "MTA - BLUE LINE - GRAND/LATTC                                          14\n",
       "MTA - BLUE LINE - VERNON                                               14\n",
       "MTA - GOLD LINE - CHINATOWN                                            13\n",
       "CHEMICAL STORAGE/MANUFACTURING PLANT                                   13\n",
       "CULTURAL SIGNIFICANCE/MONUMENT                                         12\n",
       "MTA - ORANGE LINE - RESEDA                                             12\n",
       "MTA - GOLD LINE - SOUTHWEST MUSEUM                                     11\n",
       "MTA - PURPLE LINE - 7TH AND METRO CENTER                               11\n",
       "MTA - SILVER LINE - HARBOR GATEWAY TRANSIT CTR                         11\n",
       "REDLINE (SUBWAY TRAIN)                                                 10\n",
       "ESCALATOR*                                                             10\n",
       "MTA - ORANGE LINE - CHATSWORTH                                          9\n",
       "MUSCLE BEACH                                                            9\n",
       "MTA - GOLD LINE - LITTLE TOKYO/ARTS DISTRICT                            9\n",
       "MTA - GOLD LINE - PICO/ALISO                                            8\n",
       "MTA - PURPLE LINE - WESTLAKE/MACARTHUR PARK                             8\n",
       "MTA - GOLD LINE - INDIANA                                               8\n",
       "MTA - GOLD LINE - HERITAGE SQ                                           8\n",
       "MTA - SILVER LINE - HARBOR FWY                                          7\n",
       "HORSE RACING/SANTA ANITA PARK*                                          7\n",
       "SEWAGE FACILITY/PIPE                                                    6\n",
       "HOCKEY RINK/ICE HOCKEY                                                  6\n",
       "BLUE LINE (ABOVE GROUND SURFACE TRAIN)                                  6\n",
       "MTA - PURPLE LINE - CIVIC CENTER/GRAND PARK                             6\n",
       "ORANGE LINE PARKING LOT                                                 6\n",
       "REDLINE SUBWAY RAIL CAR (INSIDE TRAIN)                                  6\n",
       "MTA - ORANGE LINE - SHERMAN WAY                                         5\n",
       "BANK DROP BOX/MONEY DROP-OUTSIDE OF BANK*                               5\n",
       "REDLINE SUBWAY MEZZANINE                                                5\n",
       "MTA - SILVER LINE - UNION STATION                                       4\n",
       "MTA - SILVER LINE - DOWNTOWN STREET STOPS                               4\n",
       "TRAM/STREETCAR(BOXLIKE WAG ON RAILS)*                                   4\n",
       "MTA - SILVER LINE - 37TH ST/USC                                         4\n",
       "SURPLUS SURVIVAL STORE                                                  4\n",
       "MTA - ORANGE LINE - PIERCE COLLEGE                                      4\n",
       "MTA - ORANGE LINE - TAMPA                                               4\n",
       "MTA - SILVER LINE - MANCHESTER                                          3\n",
       "MTA - SILVER LINE - PACIFIC COAST HWY                                   3\n",
       "MTA - ORANGE LINE - WOODLEY                                             3\n",
       "MTA - ORANGE LINE - DE SOTO                                             3\n",
       "MTA - ORANGE LINE - SEPULVEDA                                           3\n",
       "DEPT OF DEFENSE FACILITY                                                2\n",
       "MTA - SILVER LINE - SAN PEDRO STREET STOPS                              2\n",
       "MTA - SILVER LINE - ROSECRANS                                           2\n",
       "HANDBALL COURTS                                                         2\n",
       "MTA - ORANGE LINE - WOODMAN                                             2\n",
       "HARBOR FRWY STATION (NOT LINE SPECIFIC)                                 2\n",
       "MTA - SILVER LINE - CAL STATE LA                                        2\n",
       "MTA - SILVER LINE - SLAUSON                                             2\n",
       "MTA - ORANGE LINE - LAUREL CANYON                                       2\n",
       "MTA - ORANGE LINE - WARNER CTR                                          1\n",
       "NUCLEAR FACILITY                                                        1\n",
       "MTA - ORANGE LINE - VALLEY COLLEGE                                      1\n",
       "GREEN LINE (I-105 FWY LEVEL TRAIN)                                      1\n",
       "MTA - ORANGE LINE - ROSCOE                                              1\n",
       "MTA - ORANGE LINE - NORDHOFF                                            1\n",
       "REDLINE SUBWAY TUNNEL                                                   1\n",
       "TACTICAL SIGNIFICANCE                                                   1\n",
       "Name: Premise Description, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 39
    }
   ],
   "source": [
    "df['Premise Description'].value_counts()\n",
    "# df[df['Premise Description']=='UNKNOWN']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Premise Code'] = np.where(df['Premise Description']== 'UNKNOWN', 0.0, df['Premise Code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Index(['DR Number', 'Time Occurred', 'Area ID', 'Area Name',\n",
       "       'Reporting District', 'Crime Code', 'Crime Code Description',\n",
       "       'Victim Age', 'Victim Sex', 'Victim Descent', 'Premise Code',\n",
       "       'Premise Description', 'Status Code', 'Status Description', 'Address',\n",
       "       'Location '],\n",
       "      dtype='object')"
      ]
     },
     "metadata": {},
     "execution_count": 41
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing whitespace from column name\n",
    "df = df.rename(columns={'Location ': 'Location'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dictionary_maker('Premise Code', 'Premise Description')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Index(['DR Number', 'Time Occurred', 'Area ID', 'Area Name',\n",
       "       'Reporting District', 'Crime Code', 'Crime Code Description',\n",
       "       'Victim Age', 'Victim Sex', 'Victim Descent', 'Premise Code',\n",
       "       'Premise Description', 'Status Code', 'Status Description', 'Address',\n",
       "       'Location'],\n",
       "      dtype='object')"
      ]
     },
     "metadata": {},
     "execution_count": 44
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Location Column Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pulling out whole latitude dict\n",
    "df['latitude'] = df['Location'].str.extract('(\\'latitude\\'\\:\\s\\'\\-?\\d+\\.?\\d+\\')', expand=False).str.strip()\n",
    "# pulling out only coordinate\n",
    "df['latitude'] = df['latitude'].str.extract('(-?\\d+\\.?\\d+)', expand=False).str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pulling out whole latitude dict\n",
    "df['longitude'] = df['Location'].str.extract('(\\'longitude\\'\\:\\s\\'\\-?\\d+\\.?\\d+\\')', expand=False).str.strip()\n",
    "# pulling out only coordinate\n",
    "df['longitude'] = df['longitude'].str.extract('(-?\\d+\\.?\\d+)', expand=False).str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               DR Number  Time Occurred  Area ID Area Name  \\\n",
       "Date Occurred                                                \n",
       "2010-01-01     102120693           1117       21   Topanga   \n",
       "2010-01-01     100504041           2130        5    Harbor   \n",
       "2010-01-01     100504042           1600        5    Harbor   \n",
       "2010-01-01     100504046           1800        5    Harbor   \n",
       "2010-01-01     100504048           1425        5    Harbor   \n",
       "\n",
       "               Reporting District  Crime Code  \\\n",
       "Date Occurred                                   \n",
       "2010-01-01                   2156         354   \n",
       "2010-01-01                    563         420   \n",
       "2010-01-01                    519         510   \n",
       "2010-01-01                    506         330   \n",
       "2010-01-01                    503         624   \n",
       "\n",
       "                                        Crime Code Description  Victim Age  \\\n",
       "Date Occurred                                                                \n",
       "2010-01-01                                   THEFT OF IDENTITY          21   \n",
       "2010-01-01     THEFT FROM MOTOR VEHICLE - PETTY ($950 & UNDER)           0   \n",
       "2010-01-01                                    VEHICLE - STOLEN           0   \n",
       "2010-01-01                               BURGLARY FROM VEHICLE          49   \n",
       "2010-01-01                            BATTERY - SIMPLE ASSAULT          52   \n",
       "\n",
       "              Victim Sex Victim Descent  Premise Code     Premise Description  \\\n",
       "Date Occurred                                                                   \n",
       "2010-01-01             M              H         404.0        DEPARTMENT STORE   \n",
       "2010-01-01             X              X         104.0                DRIVEWAY   \n",
       "2010-01-01             X              X         101.0                  STREET   \n",
       "2010-01-01             M              H         501.0  SINGLE FAMILY DWELLING   \n",
       "2010-01-01             F              H         101.0                  STREET   \n",
       "\n",
       "              Status Code Status Description  \\\n",
       "Date Occurred                                  \n",
       "2010-01-01             IC        Invest Cont   \n",
       "2010-01-01             IC        Invest Cont   \n",
       "2010-01-01             IC        Invest Cont   \n",
       "2010-01-01             IC        Invest Cont   \n",
       "2010-01-01             AO        Adult Other   \n",
       "\n",
       "                                                Address  \\\n",
       "Date Occurred                                             \n",
       "2010-01-01     21800    VICTORY                      BL   \n",
       "2010-01-01       900 W  8TH                          ST   \n",
       "2010-01-01      1400 E  O                            ST   \n",
       "2010-01-01      1500 W  216TH                        ST   \n",
       "2010-01-01                                        257TH   \n",
       "\n",
       "                                                        Location latitude  \\\n",
       "Date Occurred                                                               \n",
       "2010-01-01     {'latitude': '34.1875', 'needs_recoding': Fals...  34.1875   \n",
       "2010-01-01     {'latitude': '33.737', 'needs_recoding': False...   33.737   \n",
       "2010-01-01     {'latitude': '33.7926', 'needs_recoding': Fals...  33.7926   \n",
       "2010-01-01     {'latitude': '33.8325', 'needs_recoding': Fals...  33.8325   \n",
       "2010-01-01     {'latitude': '33.7909', 'needs_recoding': Fals...  33.7909   \n",
       "\n",
       "               longitude  \n",
       "Date Occurred             \n",
       "2010-01-01      -118.603  \n",
       "2010-01-01     -118.2967  \n",
       "2010-01-01     -118.2468  \n",
       "2010-01-01     -118.3039  \n",
       "2010-01-01      -118.296  "
      ],
      "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>DR Number</th>\n      <th>Time Occurred</th>\n      <th>Area ID</th>\n      <th>Area Name</th>\n      <th>Reporting District</th>\n      <th>Crime Code</th>\n      <th>Crime Code Description</th>\n      <th>Victim Age</th>\n      <th>Victim Sex</th>\n      <th>Victim Descent</th>\n      <th>Premise Code</th>\n      <th>Premise Description</th>\n      <th>Status Code</th>\n      <th>Status Description</th>\n      <th>Address</th>\n      <th>Location</th>\n      <th>latitude</th>\n      <th>longitude</th>\n    </tr>\n    <tr>\n      <th>Date Occurred</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2010-01-01</th>\n      <td>102120693</td>\n      <td>1117</td>\n      <td>21</td>\n      <td>Topanga</td>\n      <td>2156</td>\n      <td>354</td>\n      <td>THEFT OF IDENTITY</td>\n      <td>21</td>\n      <td>M</td>\n      <td>H</td>\n      <td>404.0</td>\n      <td>DEPARTMENT STORE</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>21800    VICTORY                      BL</td>\n      <td>{'latitude': '34.1875', 'needs_recoding': Fals...</td>\n      <td>34.1875</td>\n      <td>-118.603</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100504041</td>\n      <td>2130</td>\n      <td>5</td>\n      <td>Harbor</td>\n      <td>563</td>\n      <td>420</td>\n      <td>THEFT FROM MOTOR VEHICLE - PETTY ($950 &amp; UNDER)</td>\n      <td>0</td>\n      <td>X</td>\n      <td>X</td>\n      <td>104.0</td>\n      <td>DRIVEWAY</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>900 W  8TH                          ST</td>\n      <td>{'latitude': '33.737', 'needs_recoding': False...</td>\n      <td>33.737</td>\n      <td>-118.2967</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100504042</td>\n      <td>1600</td>\n      <td>5</td>\n      <td>Harbor</td>\n      <td>519</td>\n      <td>510</td>\n      <td>VEHICLE - STOLEN</td>\n      <td>0</td>\n      <td>X</td>\n      <td>X</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>1400 E  O                            ST</td>\n      <td>{'latitude': '33.7926', 'needs_recoding': Fals...</td>\n      <td>33.7926</td>\n      <td>-118.2468</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100504046</td>\n      <td>1800</td>\n      <td>5</td>\n      <td>Harbor</td>\n      <td>506</td>\n      <td>330</td>\n      <td>BURGLARY FROM VEHICLE</td>\n      <td>49</td>\n      <td>M</td>\n      <td>H</td>\n      <td>501.0</td>\n      <td>SINGLE FAMILY DWELLING</td>\n      <td>IC</td>\n      <td>Invest Cont</td>\n      <td>1500 W  216TH                        ST</td>\n      <td>{'latitude': '33.8325', 'needs_recoding': Fals...</td>\n      <td>33.8325</td>\n      <td>-118.3039</td>\n    </tr>\n    <tr>\n      <th>2010-01-01</th>\n      <td>100504048</td>\n      <td>1425</td>\n      <td>5</td>\n      <td>Harbor</td>\n      <td>503</td>\n      <td>624</td>\n      <td>BATTERY - SIMPLE ASSAULT</td>\n      <td>52</td>\n      <td>F</td>\n      <td>H</td>\n      <td>101.0</td>\n      <td>STREET</td>\n      <td>AO</td>\n      <td>Adult Other</td>\n      <td>257TH</td>\n      <td>{'latitude': '33.7909', 'needs_recoding': Fals...</td>\n      <td>33.7909</td>\n      <td>-118.296</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 47
    }
   ],
   "source": [
    "# crime_dict\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dropping description columns and features not relevant to modeling\n",
    "cleaned_features=['DR Number', 'Time Occurred','Area ID', 'Area Name',\n",
    "                  'Reporting District', \n",
    "                  'Crime Code', 'Crime Code Description',\n",
    "                  'Victim Age',\n",
    "                  'Victim Sex', \n",
    "                  'Victim Descent', \n",
    "                  'Premise Code', 'Premise Description',\n",
    "                  'Status Code', 'Status Description',\n",
    "                  'latitude', 'longitude']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pickling Final Cleaned Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_df = df[cleaned_features]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_df.to_pickle(\"data/pickled/cleaned_df.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to load pickled dataframe:\n",
    "`name_for_the_df = pd.read_pickle('data/pickled/cleaned_df.pkl')`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The remaining variables are:\n",
    "* When the crime occurred - given by date and time\n",
    "* When the crime was reported - date\n",
    "* What was the crime - crime code and crime description\n",
    "* Who was the victim - victim sex, age and descent\n",
    "* What weapon was involved - weapon code and description\n",
    "* Where the crime took place - given by location, address, cross street, premise code and description"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}