data_district.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df = pd.read_csv(\"data_district_crime.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CMPLNT_NUM</th>\n",
       "      <th>ADDR_PCT_CD</th>\n",
       "      <th>BORO_NM</th>\n",
       "      <th>CMPLNT_FR_DT</th>\n",
       "      <th>CMPLNT_FR_TM</th>\n",
       "      <th>CMPLNT_TO_DT</th>\n",
       "      <th>CMPLNT_TO_TM</th>\n",
       "      <th>CRM_ATPT_CPTD_CD</th>\n",
       "      <th>HADEVELOPT</th>\n",
       "      <th>HOUSING_PSA</th>\n",
       "      <th>...</th>\n",
       "      <th>SUSP_SEX</th>\n",
       "      <th>TRANSIT_DISTRICT</th>\n",
       "      <th>VIC_AGE_GROUP</th>\n",
       "      <th>VIC_RACE</th>\n",
       "      <th>VIC_SEX</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Lat_Lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>831526991</td>\n",
       "      <td>67</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:30:00</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:37:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>E</td>\n",
       "      <td>1003227.0</td>\n",
       "      <td>177460.0</td>\n",
       "      <td>40.653751</td>\n",
       "      <td>-73.931609</td>\n",
       "      <td>(40.653751263, -73.931609227)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>496736340</td>\n",
       "      <td>120</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:20:00</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:30:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>RICHMOND TERRACE</td>\n",
       "      <td>10143.0</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;18</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>962748.0</td>\n",
       "      <td>174174.0</td>\n",
       "      <td>40.644726</td>\n",
       "      <td>-74.077483</td>\n",
       "      <td>(40.644726131, -74.077483159)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>399593692</td>\n",
       "      <td>105</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:15:00</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:20:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18-24</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>1056932.0</td>\n",
       "      <td>200034.0</td>\n",
       "      <td>40.715434</td>\n",
       "      <td>-73.737816</td>\n",
       "      <td>(40.715433534, -73.737816163)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>282376455</td>\n",
       "      <td>110</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:10:00</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:20:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25-44</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>1014990.0</td>\n",
       "      <td>210503.0</td>\n",
       "      <td>40.744414</td>\n",
       "      <td>-73.889065</td>\n",
       "      <td>(40.744413503, -73.889065158)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201598299</td>\n",
       "      <td>78</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:10:00</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:19:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>572 WARREN STREET</td>\n",
       "      <td>2379.0</td>\n",
       "      <td>...</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25-44</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>989141.0</td>\n",
       "      <td>187733.0</td>\n",
       "      <td>40.681967</td>\n",
       "      <td>-73.982367</td>\n",
       "      <td>(40.681967344, -73.982367054)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CMPLNT_NUM  ADDR_PCT_CD        BORO_NM CMPLNT_FR_DT CMPLNT_FR_TM  \\\n",
       "0   831526991           67       BROOKLYN   03/31/2018     23:30:00   \n",
       "1   496736340          120  STATEN ISLAND   03/31/2018     23:20:00   \n",
       "2   399593692          105         QUEENS   03/31/2018     23:15:00   \n",
       "3   282376455          110         QUEENS   03/31/2018     23:10:00   \n",
       "4   201598299           78       BROOKLYN   03/31/2018     23:10:00   \n",
       "\n",
       "  CMPLNT_TO_DT CMPLNT_TO_TM CRM_ATPT_CPTD_CD         HADEVELOPT  HOUSING_PSA  \\\n",
       "0   03/31/2018     23:37:00        COMPLETED                NaN          NaN   \n",
       "1   03/31/2018     23:30:00        COMPLETED   RICHMOND TERRACE      10143.0   \n",
       "2   03/31/2018     23:20:00        COMPLETED                NaN          NaN   \n",
       "3   03/31/2018     23:20:00        COMPLETED                NaN          NaN   \n",
       "4   03/31/2018     23:19:00        COMPLETED  572 WARREN STREET       2379.0   \n",
       "\n",
       "   ...  SUSP_SEX TRANSIT_DISTRICT  VIC_AGE_GROUP        VIC_RACE VIC_SEX  \\\n",
       "0  ...       NaN              NaN        UNKNOWN         UNKNOWN       E   \n",
       "1  ...         M              NaN            <18           BLACK       F   \n",
       "2  ...         M              NaN          18-24           BLACK       F   \n",
       "3  ...         M              NaN          25-44  WHITE HISPANIC       M   \n",
       "4  ...         F              NaN          25-44           BLACK       F   \n",
       "\n",
       "  X_COORD_CD Y_COORD_CD   Latitude  Longitude                        Lat_Lon  \n",
       "0  1003227.0   177460.0  40.653751 -73.931609  (40.653751263, -73.931609227)  \n",
       "1   962748.0   174174.0  40.644726 -74.077483  (40.644726131, -74.077483159)  \n",
       "2  1056932.0   200034.0  40.715434 -73.737816  (40.715433534, -73.737816163)  \n",
       "3  1014990.0   210503.0  40.744414 -73.889065  (40.744413503, -73.889065158)  \n",
       "4   989141.0   187733.0  40.681967 -73.982367  (40.681967344, -73.982367054)  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CMPLNT_NUM', 'ADDR_PCT_CD', 'BORO_NM', 'CMPLNT_FR_DT', 'CMPLNT_FR_TM',\n",
       "       'CMPLNT_TO_DT', 'CMPLNT_TO_TM', 'CRM_ATPT_CPTD_CD', 'HADEVELOPT',\n",
       "       'HOUSING_PSA', 'JURISDICTION_CODE', 'JURIS_DESC', 'KY_CD', 'LAW_CAT_CD',\n",
       "       'LOC_OF_OCCUR_DESC', 'OFNS_DESC', 'PARKS_NM', 'PATROL_BORO', 'PD_CD',\n",
       "       'PD_DESC', 'PREM_TYP_DESC', 'RPT_DT', 'STATION_NAME', 'SUSP_AGE_GROUP',\n",
       "       'SUSP_RACE', 'SUSP_SEX', 'TRANSIT_DISTRICT', 'VIC_AGE_GROUP',\n",
       "       'VIC_RACE', 'VIC_SEX', 'X_COORD_CD', 'Y_COORD_CD', 'Latitude',\n",
       "       'Longitude', 'Lat_Lon'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check for NULL values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CMPLNT_NUM                0\n",
       "ADDR_PCT_CD               0\n",
       "BORO_NM                  58\n",
       "CMPLNT_FR_DT              0\n",
       "CMPLNT_FR_TM              0\n",
       "CMPLNT_TO_DT          16016\n",
       "CMPLNT_TO_TM          15966\n",
       "CRM_ATPT_CPTD_CD          0\n",
       "HADEVELOPT           103721\n",
       "HOUSING_PSA          100562\n",
       "JURISDICTION_CODE        57\n",
       "JURIS_DESC                0\n",
       "KY_CD                     0\n",
       "LAW_CAT_CD                0\n",
       "LOC_OF_OCCUR_DESC     19392\n",
       "OFNS_DESC                 4\n",
       "PARKS_NM             109082\n",
       "PATROL_BORO              57\n",
       "PD_CD                    57\n",
       "PD_DESC                  57\n",
       "PREM_TYP_DESC           433\n",
       "RPT_DT                    0\n",
       "STATION_NAME         106411\n",
       "SUSP_AGE_GROUP        28133\n",
       "SUSP_RACE             28124\n",
       "SUSP_SEX              28124\n",
       "TRANSIT_DISTRICT     106411\n",
       "VIC_AGE_GROUP             0\n",
       "VIC_RACE                  0\n",
       "VIC_SEX                   0\n",
       "X_COORD_CD                2\n",
       "Y_COORD_CD                2\n",
       "Latitude                  2\n",
       "Longitude                 2\n",
       "Lat_Lon                   2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cleaning the dataset by dopping certain rows with NA values. Out of many records since only 58 of Borough name are missing, dropping 58 rows. Similar step to few other columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CMPLNT_NUM                0\n",
       "ADDR_PCT_CD               0\n",
       "BORO_NM                   0\n",
       "CMPLNT_FR_DT              0\n",
       "CMPLNT_FR_TM              0\n",
       "CMPLNT_TO_DT          15957\n",
       "CMPLNT_TO_TM          15907\n",
       "CRM_ATPT_CPTD_CD          0\n",
       "HADEVELOPT           103664\n",
       "HOUSING_PSA          100499\n",
       "JURISDICTION_CODE         0\n",
       "JURIS_DESC                0\n",
       "KY_CD                     0\n",
       "LAW_CAT_CD                0\n",
       "LOC_OF_OCCUR_DESC     19391\n",
       "OFNS_DESC                 0\n",
       "PARKS_NM             109018\n",
       "PATROL_BORO               0\n",
       "PD_CD                     0\n",
       "PD_DESC                   0\n",
       "PREM_TYP_DESC           376\n",
       "RPT_DT                    0\n",
       "STATION_NAME         106348\n",
       "SUSP_AGE_GROUP        28109\n",
       "SUSP_RACE             28109\n",
       "SUSP_SEX              28109\n",
       "TRANSIT_DISTRICT     106348\n",
       "VIC_AGE_GROUP             0\n",
       "VIC_RACE                  0\n",
       "VIC_SEX                   0\n",
       "X_COORD_CD                0\n",
       "Y_COORD_CD                0\n",
       "Latitude                  0\n",
       "Longitude                 0\n",
       "Lat_Lon                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 226,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df = data_df.dropna(subset = ['BORO_NM'])\n",
    "data_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df = data_df.dropna(subset = ['OFNS_DESC', 'X_COORD_CD', 'Y_COORD_CD', 'Latitude','Longitude','Lat_Lon'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting only few columns from the data that seem to be important and necessary for the analysis. \n",
    "\n",
    "1. Removing CMPLT_TO_DT and CMPLT_TO_TM columns, as they seem to be less important when considered CMPLT_FR_DT and CMPLT_FR_TM \n",
    "2. Based on the data description, HADEVELOPT and  HOUSINNG_PSA also seem to be less important. \n",
    "3. Even removing PARKS_NM because of two reasons. 1. Variable with highest missing values and assuming not all crimes take place in parks 2. PREM_TYPE_DESC has values where crime has occurred "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = data_df[['CMPLNT_NUM', 'ADDR_PCT_CD', 'BORO_NM', 'CMPLNT_FR_DT', 'CMPLNT_FR_TM', 'CRM_ATPT_CPTD_CD', 'JURISDICTION_CODE', 'JURIS_DESC','KY_CD','LAW_CAT_CD', 'OFNS_DESC', \n",
    "'PD_CD','PD_DESC','PREM_TYP_DESC','RPT_DT','STATION_NAME','SUSP_AGE_GROUP','SUSP_RACE','SUSP_SEX', 'VIC_AGE_GROUP',\n",
    "       'VIC_RACE', 'VIC_SEX', 'X_COORD_CD','Y_COORD_CD','Latitude','Longitude','Lat_Lon']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CMPLNT_NUM</th>\n",
       "      <th>ADDR_PCT_CD</th>\n",
       "      <th>BORO_NM</th>\n",
       "      <th>CMPLNT_FR_DT</th>\n",
       "      <th>CMPLNT_FR_TM</th>\n",
       "      <th>CRM_ATPT_CPTD_CD</th>\n",
       "      <th>JURISDICTION_CODE</th>\n",
       "      <th>JURIS_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>...</th>\n",
       "      <th>SUSP_RACE</th>\n",
       "      <th>SUSP_SEX</th>\n",
       "      <th>VIC_AGE_GROUP</th>\n",
       "      <th>VIC_RACE</th>\n",
       "      <th>VIC_SEX</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Lat_Lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>831526991</td>\n",
       "      <td>67</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:30:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N.Y. POLICE DEPT</td>\n",
       "      <td>236</td>\n",
       "      <td>MISDEMEANOR</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>E</td>\n",
       "      <td>1003227.0</td>\n",
       "      <td>177460.0</td>\n",
       "      <td>40.653751</td>\n",
       "      <td>-73.931609</td>\n",
       "      <td>(40.653751263, -73.931609227)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>496736340</td>\n",
       "      <td>120</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:20:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N.Y. HOUSING POLICE</td>\n",
       "      <td>104</td>\n",
       "      <td>FELONY</td>\n",
       "      <td>...</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>M</td>\n",
       "      <td>&lt;18</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>962748.0</td>\n",
       "      <td>174174.0</td>\n",
       "      <td>40.644726</td>\n",
       "      <td>-74.077483</td>\n",
       "      <td>(40.644726131, -74.077483159)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>399593692</td>\n",
       "      <td>105</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:15:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N.Y. POLICE DEPT</td>\n",
       "      <td>361</td>\n",
       "      <td>MISDEMEANOR</td>\n",
       "      <td>...</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>M</td>\n",
       "      <td>18-24</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>1056932.0</td>\n",
       "      <td>200034.0</td>\n",
       "      <td>40.715434</td>\n",
       "      <td>-73.737816</td>\n",
       "      <td>(40.715433534, -73.737816163)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>282376455</td>\n",
       "      <td>110</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:10:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N.Y. POLICE DEPT</td>\n",
       "      <td>105</td>\n",
       "      <td>FELONY</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>25-44</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>1014990.0</td>\n",
       "      <td>210503.0</td>\n",
       "      <td>40.744414</td>\n",
       "      <td>-73.889065</td>\n",
       "      <td>(40.744413503, -73.889065158)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201598299</td>\n",
       "      <td>78</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:10:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N.Y. HOUSING POLICE</td>\n",
       "      <td>578</td>\n",
       "      <td>VIOLATION</td>\n",
       "      <td>...</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>F</td>\n",
       "      <td>25-44</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>989141.0</td>\n",
       "      <td>187733.0</td>\n",
       "      <td>40.681967</td>\n",
       "      <td>-73.982367</td>\n",
       "      <td>(40.681967344, -73.982367054)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CMPLNT_NUM  ADDR_PCT_CD        BORO_NM CMPLNT_FR_DT CMPLNT_FR_TM  \\\n",
       "0   831526991           67       BROOKLYN   03/31/2018     23:30:00   \n",
       "1   496736340          120  STATEN ISLAND   03/31/2018     23:20:00   \n",
       "2   399593692          105         QUEENS   03/31/2018     23:15:00   \n",
       "3   282376455          110         QUEENS   03/31/2018     23:10:00   \n",
       "4   201598299           78       BROOKLYN   03/31/2018     23:10:00   \n",
       "\n",
       "  CRM_ATPT_CPTD_CD  JURISDICTION_CODE           JURIS_DESC  KY_CD  \\\n",
       "0        COMPLETED                0.0     N.Y. POLICE DEPT    236   \n",
       "1        COMPLETED                2.0  N.Y. HOUSING POLICE    104   \n",
       "2        COMPLETED                0.0     N.Y. POLICE DEPT    361   \n",
       "3        COMPLETED                0.0     N.Y. POLICE DEPT    105   \n",
       "4        COMPLETED                2.0  N.Y. HOUSING POLICE    578   \n",
       "\n",
       "    LAW_CAT_CD  ...       SUSP_RACE  SUSP_SEX VIC_AGE_GROUP        VIC_RACE  \\\n",
       "0  MISDEMEANOR  ...             NaN       NaN       UNKNOWN         UNKNOWN   \n",
       "1       FELONY  ...         UNKNOWN         M           <18           BLACK   \n",
       "2  MISDEMEANOR  ...           BLACK         M         18-24           BLACK   \n",
       "3       FELONY  ...  WHITE HISPANIC         M         25-44  WHITE HISPANIC   \n",
       "4    VIOLATION  ...  WHITE HISPANIC         F         25-44           BLACK   \n",
       "\n",
       "  VIC_SEX X_COORD_CD Y_COORD_CD   Latitude  Longitude  \\\n",
       "0       E  1003227.0   177460.0  40.653751 -73.931609   \n",
       "1       F   962748.0   174174.0  40.644726 -74.077483   \n",
       "2       F  1056932.0   200034.0  40.715434 -73.737816   \n",
       "3       M  1014990.0   210503.0  40.744414 -73.889065   \n",
       "4       F   989141.0   187733.0  40.681967 -73.982367   \n",
       "\n",
       "                         Lat_Lon  \n",
       "0  (40.653751263, -73.931609227)  \n",
       "1  (40.644726131, -74.077483159)  \n",
       "2  (40.715433534, -73.737816163)  \n",
       "3  (40.744413503, -73.889065158)  \n",
       "4  (40.681967344, -73.982367054)  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CMPLNT_NUM                0\n",
       "ADDR_PCT_CD               0\n",
       "BORO_NM                   0\n",
       "CMPLNT_FR_DT              0\n",
       "CMPLNT_FR_TM              0\n",
       "CRM_ATPT_CPTD_CD          0\n",
       "JURISDICTION_CODE         0\n",
       "JURIS_DESC                0\n",
       "KY_CD                     0\n",
       "LAW_CAT_CD                0\n",
       "OFNS_DESC                 0\n",
       "PD_CD                     0\n",
       "PD_DESC                   0\n",
       "PREM_TYP_DESC           376\n",
       "RPT_DT                    0\n",
       "STATION_NAME         106348\n",
       "SUSP_AGE_GROUP        28109\n",
       "SUSP_RACE             28109\n",
       "SUSP_SEX              28109\n",
       "VIC_AGE_GROUP             0\n",
       "VIC_RACE                  0\n",
       "VIC_SEX                   0\n",
       "X_COORD_CD                0\n",
       "Y_COORD_CD                0\n",
       "Latitude                  0\n",
       "Longitude                 0\n",
       "Lat_Lon                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 230,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Filling NA values with mode in cases of SUSP_RACE and SUSP_AGE_GROUP. \n",
    "2. Assuming that N.Y. POLICE DEPT as a central point, filling na values of STATION_NAME with the same. \n",
    "3. SUSP_SEX is more distributed towards Male and less towards Female. To get a basic or equal distribution of values, I assumed missing values to be Female. \n",
    "4. For PREM_TYPE_DESC, assuming the most crimes happen at a similar place or type of place, I used mode of PREM_TYPE_DESC to fill missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/sarathchandrikak/opt/anaconda3/envs/mysqlconnect/lib/python3.6/site-packages/pandas/core/series.py:4536: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  downcast=downcast,\n"
     ]
    }
   ],
   "source": [
    "df['SUSP_RACE'].fillna(df['SUSP_RACE'].mode()[0], inplace=True)\n",
    "df['SUSP_AGE_GROUP'].fillna(df['SUSP_AGE_GROUP'].mode()[0], inplace=True)\n",
    "df['STATION_NAME'].fillna('N.Y. POLICE DEPT', inplace=True)\n",
    "df['SUSP_SEX'].fillna('F', inplace=True)\n",
    "df['PREM_TYP_DESC'].fillna(df['PREM_TYP_DESC'].mode()[0], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CMPLNT_NUM           0\n",
       "ADDR_PCT_CD          0\n",
       "BORO_NM              0\n",
       "CMPLNT_FR_DT         0\n",
       "CMPLNT_FR_TM         0\n",
       "CRM_ATPT_CPTD_CD     0\n",
       "JURISDICTION_CODE    0\n",
       "JURIS_DESC           0\n",
       "KY_CD                0\n",
       "LAW_CAT_CD           0\n",
       "OFNS_DESC            0\n",
       "PD_CD                0\n",
       "PD_DESC              0\n",
       "PREM_TYP_DESC        0\n",
       "RPT_DT               0\n",
       "STATION_NAME         0\n",
       "SUSP_AGE_GROUP       0\n",
       "SUSP_RACE            0\n",
       "SUSP_SEX             0\n",
       "VIC_AGE_GROUP        0\n",
       "VIC_RACE             0\n",
       "VIC_SEX              0\n",
       "X_COORD_CD           0\n",
       "Y_COORD_CD           0\n",
       "Latitude             0\n",
       "Longitude            0\n",
       "Lat_Lon              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "M    50864\n",
       "F    43633\n",
       "U    14982\n",
       "Name: SUSP_SEX, dtype: int64"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['SUSP_SEX'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "F    42823\n",
       "M    35336\n",
       "E    15699\n",
       "D    15621\n",
       "Name: VIC_SEX, dtype: int64"
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['VIC_SEX'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "VIC_SEX has E and D which is not mentioned anywhere in Data description. So, I am assuming them as Female. Since it might be a mistype to F because both E and D are adjacent to F on the keyboard. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/sarathchandrikak/opt/anaconda3/envs/mysqlconnect/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "df['VIC_SEX'] = df['VIC_SEX'].replace(['E', 'D'],'F')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25-44      36445\n",
       "UNKNOWN    34025\n",
       "45-64      19623\n",
       "18-24       9956\n",
       "<18         4949\n",
       "65+         4473\n",
       "-974           2\n",
       "972            1\n",
       "-972           1\n",
       "951            1\n",
       "-942           1\n",
       "-958           1\n",
       "-43            1\n",
       "Name: VIC_AGE_GROUP, dtype: int64"
      ]
     },
     "execution_count": 236,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['VIC_AGE_GROUP'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upon observing the data selecting few age groups from the data. Following are the selected age groups for victims"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [],
   "source": [
    "correct_age = ['25-44', 'UNKNOWN', '45-64', '18-24', '<18', '64+']\n",
    "df = df[df['VIC_AGE_GROUP'].isin(correct_age)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25-44      36445\n",
       "UNKNOWN    34025\n",
       "45-64      19623\n",
       "18-24       9956\n",
       "<18         4949\n",
       "Name: VIC_AGE_GROUP, dtype: int64"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_group_count = df['VIC_AGE_GROUP'].value_counts()\n",
    "age_group_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25-44      0.347102\n",
       "UNKNOWN    0.324054\n",
       "45-64      0.186889\n",
       "18-24      0.094821\n",
       "<18        0.047134\n",
       "Name: VIC_AGE_GROUP, dtype: float64"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_group_percent = df['VIC_AGE_GROUP'].value_counts(normalize=True)\n",
    "age_group_percent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25-44      54222\n",
       "UNKNOWN    26128\n",
       "18-24      10439\n",
       "45-64      10111\n",
       "<18         3353\n",
       "65+          732\n",
       "2018           3\n",
       "928            2\n",
       "1018           2\n",
       "952            1\n",
       "954            1\n",
       "920            1\n",
       "-2             1\n",
       "1967           1\n",
       "938            1\n",
       "Name: SUSP_AGE_GROUP, dtype: int64"
      ]
     },
     "execution_count": 240,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['SUSP_AGE_GROUP'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar way selecting valid age group for suspects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [],
   "source": [
    "correct_age_susp = ['25-44', 'UNKNOWN', '18-24', '45-64', '<18', '65+']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['SUSP_AGE_GROUP'].isin(correct_age_susp)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25-44      54222\n",
       "UNKNOWN    26128\n",
       "18-24      10439\n",
       "45-64      10111\n",
       "<18         3353\n",
       "65+          732\n",
       "Name: SUSP_AGE_GROUP, dtype: int64"
      ]
     },
     "execution_count": 243,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['SUSP_AGE_GROUP'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CMPLNT_NUM             int64\n",
       "ADDR_PCT_CD            int64\n",
       "BORO_NM               object\n",
       "CMPLNT_FR_DT          object\n",
       "CMPLNT_FR_TM          object\n",
       "CRM_ATPT_CPTD_CD      object\n",
       "JURISDICTION_CODE    float64\n",
       "JURIS_DESC            object\n",
       "KY_CD                  int64\n",
       "LAW_CAT_CD            object\n",
       "OFNS_DESC             object\n",
       "PD_CD                float64\n",
       "PD_DESC               object\n",
       "PREM_TYP_DESC         object\n",
       "RPT_DT                object\n",
       "STATION_NAME          object\n",
       "SUSP_AGE_GROUP        object\n",
       "SUSP_RACE             object\n",
       "SUSP_SEX              object\n",
       "VIC_AGE_GROUP         object\n",
       "VIC_RACE              object\n",
       "VIC_SEX               object\n",
       "X_COORD_CD           float64\n",
       "Y_COORD_CD           float64\n",
       "Latitude             float64\n",
       "Longitude            float64\n",
       "Lat_Lon               object\n",
       "dtype: object"
      ]
     },
     "execution_count": 244,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Validating date values and cleaning the anomalies "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['CMPLNT_FR_DT_TM'] = df['CMPLNT_FR_DT'] + ' ' + df['CMPLNT_FR_TM'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    03/31/2018 23:30:00\n",
      "1    03/31/2018 23:20:00\n",
      "2    03/31/2018 23:15:00\n",
      "3    03/31/2018 23:10:00\n",
      "4    03/31/2018 23:10:00\n",
      "Name: CMPLNT_FR_DT_TM, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df['CMPLNT_FR_DT_TM'].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "datetime_list = []\n",
    "for d in df['CMPLNT_FR_DT_TM']:\n",
    "    datetime_list.append(datetime.strptime(d, '%m/%d/%Y %H:%M:%S'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Getting all the year values that are less than 1900"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1018\n",
      "1028\n",
      "1018\n",
      "1028\n",
      "1018\n",
      "1018\n",
      "1016\n",
      "1018\n"
     ]
    }
   ],
   "source": [
    "for i in datetime_list:\n",
    "    if i.year < 1900:\n",
    "        print(i.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(datetime_list)):\n",
    "    if datetime_list[i].year < 1900:\n",
    "        x = datetime_list[i].year + 1000\n",
    "        updated = datetime(x, datetime_list[i].month, datetime_list[i].day, 0, 0, 0)\n",
    "        datetime_list[i] = updated        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking if there are any more values less than 1900"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in datetime_list:\n",
    "    if i.year < 1900:\n",
    "        print(i.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['CMPLNT_FR_DT_TM'] = datetime_list\n",
    "df['CMPLNT_FR_DT_TM'] = pd.to_datetime(df['CMPLNT_FR_DT_TM'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CMPLNT_NUM                    int64\n",
      "ADDR_PCT_CD                   int64\n",
      "BORO_NM                      object\n",
      "CMPLNT_FR_DT                 object\n",
      "CMPLNT_FR_TM                 object\n",
      "CRM_ATPT_CPTD_CD             object\n",
      "JURISDICTION_CODE           float64\n",
      "JURIS_DESC                   object\n",
      "KY_CD                         int64\n",
      "LAW_CAT_CD                   object\n",
      "OFNS_DESC                    object\n",
      "PD_CD                       float64\n",
      "PD_DESC                      object\n",
      "PREM_TYP_DESC                object\n",
      "RPT_DT                       object\n",
      "STATION_NAME                 object\n",
      "SUSP_AGE_GROUP               object\n",
      "SUSP_RACE                    object\n",
      "SUSP_SEX                     object\n",
      "VIC_AGE_GROUP                object\n",
      "VIC_RACE                     object\n",
      "VIC_SEX                      object\n",
      "X_COORD_CD                  float64\n",
      "Y_COORD_CD                  float64\n",
      "Latitude                    float64\n",
      "Longitude                   float64\n",
      "Lat_Lon                      object\n",
      "CMPLNT_FR_DT_TM      datetime64[ns]\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/sarathchandrikak/opt/anaconda3/envs/mysqlconnect/lib/python3.6/site-packages/ipykernel_launcher.py:1: FutureWarning: The pandas.datetime class is deprecated and will be removed from pandas in a future version. Import from datetime instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "df = df[df['CMPLNT_FR_DT_TM'].dt.year < pd.datetime.now().year]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-03-31 23:30:00\n"
     ]
    }
   ],
   "source": [
    "print(max(df['CMPLNT_FR_DT_TM']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CMPLNT_NUM</th>\n",
       "      <th>ADDR_PCT_CD</th>\n",
       "      <th>BORO_NM</th>\n",
       "      <th>CMPLNT_FR_DT</th>\n",
       "      <th>CMPLNT_FR_TM</th>\n",
       "      <th>CRM_ATPT_CPTD_CD</th>\n",
       "      <th>JURISDICTION_CODE</th>\n",
       "      <th>JURIS_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>...</th>\n",
       "      <th>SUSP_SEX</th>\n",
       "      <th>VIC_AGE_GROUP</th>\n",
       "      <th>VIC_RACE</th>\n",
       "      <th>VIC_SEX</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Lat_Lon</th>\n",
       "      <th>CMPLNT_FR_DT_TM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>831526991</td>\n",
       "      <td>67</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:30:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N.Y. POLICE DEPT</td>\n",
       "      <td>236</td>\n",
       "      <td>MISDEMEANOR</td>\n",
       "      <td>...</td>\n",
       "      <td>F</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>F</td>\n",
       "      <td>1003227.0</td>\n",
       "      <td>177460.0</td>\n",
       "      <td>40.653751</td>\n",
       "      <td>-73.931609</td>\n",
       "      <td>(40.653751263, -73.931609227)</td>\n",
       "      <td>2018-03-31 23:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>496736340</td>\n",
       "      <td>120</td>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:20:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N.Y. HOUSING POLICE</td>\n",
       "      <td>104</td>\n",
       "      <td>FELONY</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>&lt;18</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>962748.0</td>\n",
       "      <td>174174.0</td>\n",
       "      <td>40.644726</td>\n",
       "      <td>-74.077483</td>\n",
       "      <td>(40.644726131, -74.077483159)</td>\n",
       "      <td>2018-03-31 23:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>399593692</td>\n",
       "      <td>105</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:15:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N.Y. POLICE DEPT</td>\n",
       "      <td>361</td>\n",
       "      <td>MISDEMEANOR</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>18-24</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>1056932.0</td>\n",
       "      <td>200034.0</td>\n",
       "      <td>40.715434</td>\n",
       "      <td>-73.737816</td>\n",
       "      <td>(40.715433534, -73.737816163)</td>\n",
       "      <td>2018-03-31 23:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>282376455</td>\n",
       "      <td>110</td>\n",
       "      <td>QUEENS</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:10:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N.Y. POLICE DEPT</td>\n",
       "      <td>105</td>\n",
       "      <td>FELONY</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>25-44</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>1014990.0</td>\n",
       "      <td>210503.0</td>\n",
       "      <td>40.744414</td>\n",
       "      <td>-73.889065</td>\n",
       "      <td>(40.744413503, -73.889065158)</td>\n",
       "      <td>2018-03-31 23:10:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201598299</td>\n",
       "      <td>78</td>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>03/31/2018</td>\n",
       "      <td>23:10:00</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N.Y. HOUSING POLICE</td>\n",
       "      <td>578</td>\n",
       "      <td>VIOLATION</td>\n",
       "      <td>...</td>\n",
       "      <td>F</td>\n",
       "      <td>25-44</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>F</td>\n",
       "      <td>989141.0</td>\n",
       "      <td>187733.0</td>\n",
       "      <td>40.681967</td>\n",
       "      <td>-73.982367</td>\n",
       "      <td>(40.681967344, -73.982367054)</td>\n",
       "      <td>2018-03-31 23:10:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CMPLNT_NUM  ADDR_PCT_CD        BORO_NM CMPLNT_FR_DT CMPLNT_FR_TM  \\\n",
       "0   831526991           67       BROOKLYN   03/31/2018     23:30:00   \n",
       "1   496736340          120  STATEN ISLAND   03/31/2018     23:20:00   \n",
       "2   399593692          105         QUEENS   03/31/2018     23:15:00   \n",
       "3   282376455          110         QUEENS   03/31/2018     23:10:00   \n",
       "4   201598299           78       BROOKLYN   03/31/2018     23:10:00   \n",
       "\n",
       "  CRM_ATPT_CPTD_CD  JURISDICTION_CODE           JURIS_DESC  KY_CD  \\\n",
       "0        COMPLETED                0.0     N.Y. POLICE DEPT    236   \n",
       "1        COMPLETED                2.0  N.Y. HOUSING POLICE    104   \n",
       "2        COMPLETED                0.0     N.Y. POLICE DEPT    361   \n",
       "3        COMPLETED                0.0     N.Y. POLICE DEPT    105   \n",
       "4        COMPLETED                2.0  N.Y. HOUSING POLICE    578   \n",
       "\n",
       "    LAW_CAT_CD  ... SUSP_SEX  VIC_AGE_GROUP        VIC_RACE VIC_SEX  \\\n",
       "0  MISDEMEANOR  ...        F        UNKNOWN         UNKNOWN       F   \n",
       "1       FELONY  ...        M            <18           BLACK       F   \n",
       "2  MISDEMEANOR  ...        M          18-24           BLACK       F   \n",
       "3       FELONY  ...        M          25-44  WHITE HISPANIC       M   \n",
       "4    VIOLATION  ...        F          25-44           BLACK       F   \n",
       "\n",
       "  X_COORD_CD Y_COORD_CD   Latitude  Longitude                        Lat_Lon  \\\n",
       "0  1003227.0   177460.0  40.653751 -73.931609  (40.653751263, -73.931609227)   \n",
       "1   962748.0   174174.0  40.644726 -74.077483  (40.644726131, -74.077483159)   \n",
       "2  1056932.0   200034.0  40.715434 -73.737816  (40.715433534, -73.737816163)   \n",
       "3  1014990.0   210503.0  40.744414 -73.889065  (40.744413503, -73.889065158)   \n",
       "4   989141.0   187733.0  40.681967 -73.982367  (40.681967344, -73.982367054)   \n",
       "\n",
       "      CMPLNT_FR_DT_TM  \n",
       "0 2018-03-31 23:30:00  \n",
       "1 2018-03-31 23:20:00  \n",
       "2 2018-03-31 23:15:00  \n",
       "3 2018-03-31 23:10:00  \n",
       "4 2018-03-31 23:10:00  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['CMPLNT_FR_DT', 'CMPLNT_FR_TM'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CMPLNT_NUM', 'ADDR_PCT_CD', 'BORO_NM', 'CRM_ATPT_CPTD_CD',\n",
       "       'JURISDICTION_CODE', 'JURIS_DESC', 'KY_CD', 'LAW_CAT_CD', 'OFNS_DESC',\n",
       "       'PD_CD', 'PD_DESC', 'PREM_TYP_DESC', 'RPT_DT', 'STATION_NAME',\n",
       "       'SUSP_AGE_GROUP', 'SUSP_RACE', 'SUSP_SEX', 'VIC_AGE_GROUP', 'VIC_RACE',\n",
       "       'VIC_SEX', 'X_COORD_CD', 'Y_COORD_CD', 'Latitude', 'Longitude',\n",
       "       'Lat_Lon', 'CMPLNT_FR_DT_TM'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grouping timings as morning, afternoon, evening and night"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['CMPLNT_FR_DT_TM_SESSION'] = df['CMPLNT_FR_DT_TM']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CMPLNT_NUM                          int64\n",
       "ADDR_PCT_CD                         int64\n",
       "BORO_NM                            object\n",
       "CRM_ATPT_CPTD_CD                   object\n",
       "JURISDICTION_CODE                 float64\n",
       "JURIS_DESC                         object\n",
       "KY_CD                               int64\n",
       "LAW_CAT_CD                         object\n",
       "OFNS_DESC                          object\n",
       "PD_CD                             float64\n",
       "PD_DESC                            object\n",
       "PREM_TYP_DESC                      object\n",
       "RPT_DT                             object\n",
       "STATION_NAME                       object\n",
       "SUSP_AGE_GROUP                     object\n",
       "SUSP_RACE                          object\n",
       "SUSP_SEX                           object\n",
       "VIC_AGE_GROUP                      object\n",
       "VIC_RACE                           object\n",
       "VIC_SEX                            object\n",
       "X_COORD_CD                        float64\n",
       "Y_COORD_CD                        float64\n",
       "Latitude                          float64\n",
       "Longitude                         float64\n",
       "Lat_Lon                            object\n",
       "CMPLNT_FR_DT_TM            datetime64[ns]\n",
       "CMPLNT_FR_DT_TM_SESSION    datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['CMPLNT_FR_DT_TM_SESSION'] = (df['CMPLNT_FR_DT_TM_SESSION'].dt.hour % 24 + 4) // 4\n",
    "df['CMPLNT_FR_DT_TM_SESSION'].replace({1: 'Late Night',\n",
    "                      2: 'Early Morning',\n",
    "                      3: 'Morning',\n",
    "                      4: 'Noon',\n",
    "                      5: 'Evening',\n",
    "                      6: 'Night'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0              Night\n",
      "1              Night\n",
      "2              Night\n",
      "3              Night\n",
      "4              Night\n",
      "             ...    \n",
      "109538          Noon\n",
      "109539         Night\n",
      "109540          Noon\n",
      "109541       Morning\n",
      "109542    Late Night\n",
      "Name: CMPLNT_FR_DT_TM_SESSION, Length: 104983, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df['CMPLNT_FR_DT_TM_SESSION'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('nyc_crimes_modified.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Descriptive Analysis\n",
    "\n",
    "1. Try looking at precincts? (police district areas)\n",
    "2. CRM_ATPT_CPTD_CD...look at the data dictionary and see if this is interesting\n",
    "3. Weekend vs Weekday (would take some data cleaning)\n",
    "4. LAW_CAT_CD? \n",
    "5. PARKS_NM?\n",
    "6. STATION_NAME?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CMPLNT_NUM', 'ADDR_PCT_CD', 'BORO_NM', 'CMPLNT_FR_DT', 'CMPLNT_FR_TM',\n",
       "       'CMPLNT_TO_DT', 'CMPLNT_TO_TM', 'CRM_ATPT_CPTD_CD', 'HADEVELOPT',\n",
       "       'HOUSING_PSA', 'JURISDICTION_CODE', 'JURIS_DESC', 'KY_CD', 'LAW_CAT_CD',\n",
       "       'LOC_OF_OCCUR_DESC', 'OFNS_DESC', 'PARKS_NM', 'PATROL_BORO', 'PD_CD',\n",
       "       'PD_DESC', 'PREM_TYP_DESC', 'RPT_DT', 'STATION_NAME', 'SUSP_AGE_GROUP',\n",
       "       'SUSP_RACE', 'SUSP_SEX', 'TRANSIT_DISTRICT', 'VIC_AGE_GROUP',\n",
       "       'VIC_RACE', 'VIC_SEX', 'X_COORD_CD', 'Y_COORD_CD', 'Latitude',\n",
       "       'Longitude', 'Lat_Lon'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 307,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n"
     ]
    }
   ],
   "source": [
    "print(len(df['BORO_NM'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BROOKLYN         30957\n",
       "MANHATTAN        26331\n",
       "BRONX            23518\n",
       "QUEENS           19572\n",
       "STATEN ISLAND     4605\n",
       "Name: BORO_NM, dtype: int64"
      ]
     },
     "execution_count": 311,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['BORO_NM'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A total of 5 different Police districts with BROOKLYN recording the highest number of crimes and STATEN ISLAND recording the lowest number of crimes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "COMPLETED    103196\n",
       "ATTEMPTED      1787\n",
       "Name: CRM_ATPT_CPTD_CD, dtype: int64"
      ]
     },
     "execution_count": 267,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['CRM_ATPT_CPTD_CD'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Value counts function indicated that there are in total of around 100k completed incidents and 1800 of attempted incidents. From this it can be stated that most of the crimes are successfully completed (which is pretty sad)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To compare weekday vs weekend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DAY_TYPE'] = df['CMPLNT_FR_DT_TM']\n",
    "df['DAY_TYPE'] = df['DAY_TYPE'].dt.day_name()\n",
    "df['DAY_TYPE'].replace({'Saturday': 'weekend',\n",
    "                        'Sunday' : 'weekend',\n",
    "                        'Monday' : 'weekday',\n",
    "                        'Tuesday' : 'weekday',\n",
    "                        'Wednesday' : 'weekday', \n",
    "                        'Thursday' : 'weekday', \n",
    "                        'Friday': 'weekday'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0         weekend\n",
      "1         weekend\n",
      "2         weekend\n",
      "3         weekend\n",
      "4         weekend\n",
      "           ...   \n",
      "109538    weekday\n",
      "109539    weekday\n",
      "109540    weekday\n",
      "109541    weekday\n",
      "109542    weekday\n",
      "Name: DAY_TYPE, Length: 104983, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df['DAY_TYPE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "weekday    76694\n",
       "weekend    28289\n",
       "Name: DAY_TYPE, dtype: int64"
      ]
     },
     "execution_count": 290,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['DAY_TYPE'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the observations, it is seen that more number of crimes occured during weekdays rather than weekends. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MISDEMEANOR    58885\n",
       "FELONY         30705\n",
       "VIOLATION      15393\n",
       "Name: LAW_CAT_CD, dtype: int64"
      ]
     },
     "execution_count": 291,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['LAW_CAT_CD'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the data it is seen that Misdemeanor has more count Level followed by felony and violation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "109018"
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['PARKS_NM'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FLUSHING MEADOWS CORONA PARK       32\n",
       "CENTRAL PARK                       24\n",
       "WILLIAMSBRIDGE OVAL                17\n",
       "RIVERSIDE PARK                     15\n",
       "UNION SQUARE PARK                  13\n",
       "                                   ..\n",
       "CASTLE HILL LITTLE LEAGUE FIELD     1\n",
       "WINGATE PARK                        1\n",
       "BRONX RIVER PARKWAY                 1\n",
       "MCCAFFREY PLAYGROUND                1\n",
       "PLAYGROUND 286                      1\n",
       "Name: PARKS_NM, Length: 201, dtype: int64"
      ]
     },
     "execution_count": 295,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['PARKS_NM'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since there are many null values in PARKS_NM column, I removed the column in my subset dataframe. But as per the exploration, Flushing Meadows corona park has highest number of crimes reported and many gave just reported 1 crime. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "328\n"
     ]
    }
   ],
   "source": [
    "n = len(pd.unique(df['STATION_NAME']))\n",
    "print(n)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "N.Y. POLICE DEPT                  101898\n",
       "125 STREET                           131\n",
       "42 ST.-PORT AUTHORITY BUS TERM        98\n",
       "14 STREET                             84\n",
       "59 ST.-COLUMBUS CIRCLE                73\n",
       "                                   ...  \n",
       "36 AVENUE                              1\n",
       "CHRISTOPHER ST.-SHERIDAN SQ.           1\n",
       "DISTRICT 20 OFFICE                     1\n",
       "BUHRE AVENUE                           1\n",
       "HEWES STREET                           1\n",
       "Name: STATION_NAME, Length: 328, dtype: int64"
      ]
     },
     "execution_count": 305,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['STATION_NAME'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Total crimes have been reported in 328 different station names and out of then N.Y. POLICE DEPT has highest number of crimes reported."
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "a4bb9d5083a993bc69e057ab1f5fd3c7c40fe50ef9ecba54949f93cdaf8d51ec"
  },
  "kernelspec": {
   "display_name": "Python 3.6.13 64-bit ('mysqlconnect': conda)",
   "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.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}