2.DC crime EDA1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DATA Cleaning and EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the libraries we will need\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import scipy.stats as stats\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import math\n",
    "\n",
    "# setting the visual\n",
    "sns.set(font_scale=2)\n",
    "cmap = sns.diverging_palette(220, 10, as_cmap=True) # one of the many color mappings\n",
    "sns.set_style('whitegrid')\n",
    "%config InlineBackend.figure_format = 'retina'\n",
    "%matplotlib inline  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('./input/dcrime.csv')\n",
    "ward_data =pd.read_csv('./input/warddata.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "print (df.duplicated().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking Data Integrity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(231502, 25)\n",
      "   Unnamed: 0          X          Y       CCN                REPORT_DAT  \\\n",
      "0           0 -76.999518  38.901924   9074624  2012-04-25T00:00:00.000Z   \n",
      "1           1 -76.995541  38.905032  10123633  2012-02-29T00:00:00.000Z   \n",
      "\n",
      "      SHIFT  METHOD    OFFENSE                                 BLOCK  \\\n",
      "0  MIDNIGHT  OTHERS  SEX ABUSE      900 - 999 BLOCK OF 5TH STREET NE   \n",
      "1  MIDNIGHT  OTHERS  SEX ABUSE  700 - 799 BLOCK OF FLORIDA AVENUE NE   \n",
      "\n",
      "     XBLOCK    ...      NEIGHBORHOOD_CLUSTER  BLOCK_GROUP CENSUS_TRACT  \\\n",
      "0  400042.0    ...                Cluster 25     010600 2      10600.0   \n",
      "1  400387.0    ...                Cluster 25     010600 1      10600.0   \n",
      "\n",
      "   VOTING_PRECINCT   LATITUDE  LONGITUDE  BID                START_DATE  \\\n",
      "0      Precinct 83  38.901916 -76.999516  NaN  2009-05-31T23:00:00.000Z   \n",
      "1      Precinct 83  38.905024 -76.995538  NaN  2010-08-27T02:30:00.000Z   \n",
      "\n",
      "                   END_DATE   OBJECTID  \n",
      "0  2009-06-01T06:00:00.000Z  167253019  \n",
      "1  2010-08-27T07:15:00.000Z  167274031  \n",
      "\n",
      "[2 rows x 25 columns]\n",
      "(157, 4)\n",
      "   year  ANC  Housing_Prices  Unemployment\n",
      "0  1997    1          323000           3.6\n",
      "1  2006    8          289000          17.0\n"
     ]
    }
   ],
   "source": [
    "print(df.shape)\n",
    "print(df.head(2))\n",
    "print(ward_data.shape)\n",
    "print(ward_data.head(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BID                     192953\n",
       "END_DATE                  5647\n",
       "NEIGHBORHOOD_CLUSTER      2843\n",
       "BLOCK_GROUP                676\n",
       "CENSUS_TRACT               676\n",
       "PSA                        135\n",
       "DISTRICT                   129\n",
       "VOTING_PRECINCT             47\n",
       "WARD                         8\n",
       "START_DATE                   4\n",
       "REPORT_DAT                   0\n",
       "CCN                          0\n",
       "OBJECTID                     0\n",
       "METHOD                       0\n",
       "Y                            0\n",
       "X                            0\n",
       "SHIFT                        0\n",
       "ANC                          0\n",
       "OFFENSE                      0\n",
       "BLOCK                        0\n",
       "XBLOCK                       0\n",
       "YBLOCK                       0\n",
       "LATITUDE                     0\n",
       "LONGITUDE                    0\n",
       "Unnamed: 0                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum().sort_values(ascending=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BID                     0.833483\n",
       "END_DATE                0.024393\n",
       "NEIGHBORHOOD_CLUSTER    0.012281\n",
       "BLOCK_GROUP             0.002920\n",
       "CENSUS_TRACT            0.002920\n",
       "PSA                     0.000583\n",
       "DISTRICT                0.000557\n",
       "VOTING_PRECINCT         0.000203\n",
       "WARD                    0.000035\n",
       "START_DATE              0.000017\n",
       "REPORT_DAT              0.000000\n",
       "CCN                     0.000000\n",
       "OBJECTID                0.000000\n",
       "METHOD                  0.000000\n",
       "Y                       0.000000\n",
       "X                       0.000000\n",
       "SHIFT                   0.000000\n",
       "ANC                     0.000000\n",
       "OFFENSE                 0.000000\n",
       "BLOCK                   0.000000\n",
       "XBLOCK                  0.000000\n",
       "YBLOCK                  0.000000\n",
       "LATITUDE                0.000000\n",
       "LONGITUDE               0.000000\n",
       "Unnamed: 0              0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking the null values of the df and sorting them as percentage of the DF shape\n",
    "(df.isnull().sum()/df.shape[0]).sort_values(ascending=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year              0.0\n",
       "ANC               0.0\n",
       "Housing_Prices    0.0\n",
       "Unemployment      0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ward_data.isnull().sum()/len(ward_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Feature Summary Statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Count of crimes committed per duty shift\n",
    "The Evening shift had the highest count as compared to the three categories followed by the Day shift. However, this doesn't take into account the severity of the crime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================================================\n",
      "---===<<< Duty Shift: variable = 'SHIFT' >>>===--\n",
      "SHIFT\n",
      "DAY         88724\n",
      "EVENING     98882\n",
      "MIDNIGHT    43896\n",
      "Name: CCN, dtype: int64\n",
      "================================================\n"
     ]
    }
   ],
   "source": [
    "print('==================================================')\n",
    "print( \"---===<<< Duty Shift: variable = 'SHIFT' >>>===--\")\n",
    "var_count = df.groupby('SHIFT')\n",
    "print( var_count.CCN.count())\n",
    "print('================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Count of crimes committed per Police District\n",
    "The number of crimes is evenly distributed across all the PSA areas ignoring the severity of the crime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=========================================================================\n",
      "---==< Crimes committed per police district: variable = 'DISTRICT' >==---\n",
      "DISTRICT\n",
      "1.0    39865\n",
      "2.0    35581\n",
      "3.0    39055\n",
      "4.0    30492\n",
      "5.0    32821\n",
      "6.0    29807\n",
      "7.0    23752\n",
      "Name: CCN, dtype: int64\n",
      "=========================================================================\n"
     ]
    }
   ],
   "source": [
    "print('=========================================================================')\n",
    "print(\"---==< Crimes committed per police district: variable = 'DISTRICT' >==---\")\n",
    "var_count = df.groupby('DISTRICT')\n",
    "print(var_count.CCN.count())\n",
    "print('=========================================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is a sample output from a single record to get a feel for the type of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=========================================================================\n",
      "---==< Crimes|OFFENSES committed per BLOCK: variable = 'BLOCK' >==---\n",
      "BLOCK                                          OFFENSE                   \n",
      "0 - 0 BLOCK OF 14TH STREET SW                  THEFT/OTHER                    1\n",
      "0 - 0 BLOCK OF ARLINGTON MEMORIAL BRIDGE SW    THEFT F/AUTO                   1\n",
      "                                               THEFT/OTHER                    1\n",
      "0 - 0 BLOCK OF CHEVY CHASE CIRCLE NW           BURGLARY                       3\n",
      "                                               THEFT F/AUTO                   2\n",
      "                                               ROBBERY                        1\n",
      "                                               THEFT/OTHER                    1\n",
      "0 - 0 BLOCK OF COLUMBUS CIRCLE NE              THEFT/OTHER                   11\n",
      "                                               ASSAULT W/DANGEROUS WEAPON     1\n",
      "                                               ROBBERY                        1\n",
      "0 - 0 BLOCK OF HEALY CIRCLE NW                 THEFT/OTHER                    1\n",
      "0 - 0 BLOCK OF LINCOLN MEMORIAL CIRCLE SW      THEFT/OTHER                    6\n",
      "0 - 0 BLOCK OF ROBERT CLIFTON WEAVER WAY NE    THEFT/OTHER                    1\n",
      "0 - 0 BLOCK OF TONDORF ROAD NW                 THEFT/OTHER                    1\n",
      "0 - 61 BLOCK OF OBSERVATORY CIRCLE NW          MOTOR VEHICLE THEFT            1\n",
      "1  - 10 BLOCK OF KENNEDY STREET NW             ROBBERY                        2\n",
      "                                               ASSAULT W/DANGEROUS WEAPON     1\n",
      "                                               BURGLARY                       1\n",
      "                                               THEFT/OTHER                    1\n",
      "1  - 10 BLOCK OF SHERMAN CIRCLE NW             ASSAULT W/DANGEROUS WEAPON     2\n",
      "                                               BURGLARY                       2\n",
      "                                               THEFT/OTHER                    2\n",
      "                                               ROBBERY                        1\n",
      "1  - 19 BLOCK OF MADISON STREET NE             THEFT F/AUTO                   2\n",
      "                                               MOTOR VEHICLE THEFT            1\n",
      "                                               THEFT/OTHER                    1\n",
      "1  - 199 BLOCK OF TUCKERMAN STREET NW          MOTOR VEHICLE THEFT            4\n",
      "                                               THEFT F/AUTO                   3\n",
      "                                               ASSAULT W/DANGEROUS WEAPON     2\n",
      "                                               BURGLARY                       2\n",
      "                                                                             ..\n",
      "WISCONSIN AVENUE NW AND WATER STREET NW        THEFT F/AUTO                   2\n",
      "                                               MOTOR VEHICLE THEFT            1\n",
      "                                               SEX ABUSE                      1\n",
      "WISCONSIN AVENUE NW AND WESTERN AVENUE NW      THEFT/OTHER                    1\n",
      "WISCONSIN AVENUE NW AND WHITEHAVEN PARKWAY NW  MOTOR VEHICLE THEFT            1\n",
      "WISCONSIN CIRCLE NW AND WESTERN AVENUE NW      THEFT/OTHER                    1\n",
      "WOODLAND DRIVE NW AND 29TH STREET NW           THEFT F/AUTO                   1\n",
      "WOODLEY PLACE NW AND CALVERT STREET NW         THEFT F/AUTO                   2\n",
      "                                               ASSAULT W/DANGEROUS WEAPON     1\n",
      "WOODLEY PLACE NW AND WOODLEY ROAD NW           THEFT F/AUTO                   1\n",
      "WOODLEY ROAD NW AND 35TH STREET NW             THEFT F/AUTO                   3\n",
      "WOODLEY ROAD NW AND KLINGLE ROAD NW            THEFT F/AUTO                   1\n",
      "WYOMING AVENUE NW AND 18TH STREET NW           THEFT F/AUTO                   2\n",
      "                                               ASSAULT W/DANGEROUS WEAPON     1\n",
      "                                               ROBBERY                        1\n",
      "                                               THEFT/OTHER                    1\n",
      "WYOMING AVENUE NW AND 24TH STREET NW           THEFT F/AUTO                   1\n",
      "WYOMING AVENUE NW AND COLUMBIA ROAD NW         THEFT F/AUTO                   2\n",
      "WYOMING AVENUE NW AND CONNECTICUT AVENUE NW    THEFT F/AUTO                   2\n",
      "WYOMING AVENUE NW AND THORNTON PLACE NW        THEFT F/AUTO                   3\n",
      "XENIA STREET SE AND MISSISSIPPI AVENUE SE      ASSAULT W/DANGEROUS WEAPON     1\n",
      "                                               THEFT F/AUTO                   1\n",
      "YOUNG STREET SE AND FAIRLAWN AVENUE SE         THEFT F/AUTO                   3\n",
      "                                               MOTOR VEHICLE THEFT            1\n",
      "YUMA STREET NW AND 35TH STREET NW              THEFT F/AUTO                   3\n",
      "YUMA STREET NW AND 42ND STREET NW              THEFT F/AUTO                   1\n",
      "YUMA STREET SE AND 1ST STREET SE               THEFT F/AUTO                   1\n",
      "YUMA STREET SE AND 8TH STREET SE               THEFT F/AUTO                   2\n",
      "                                               THEFT/OTHER                    1\n",
      "YUMA STREET SE AND CONDON TERRACE SE           ASSAULT W/DANGEROUS WEAPON     1\n",
      "Name: OFFENSE, Length: 46408, dtype: int64\n",
      "=========================================================================\n"
     ]
    }
   ],
   "source": [
    "print('=========================================================================')\n",
    "print(\"---==< Crimes|OFFENSES committed per BLOCK: variable = 'BLOCK' >==---\")\n",
    "print(df.groupby('BLOCK').OFFENSE.value_counts())\n",
    "print('=========================================================================')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======================================================\n",
      "--====< Example Record from Data Set>============\n",
      "Unnamed: 0                                        1234\n",
      "X                                             -77.0028\n",
      "Y                                              38.9141\n",
      "CCN                                           12008660\n",
      "REPORT_DAT                    2012-01-18T09:32:00.000Z\n",
      "SHIFT                                              DAY\n",
      "METHOD                                          OTHERS\n",
      "OFFENSE                            MOTOR VEHICLE THEFT\n",
      "BLOCK                   200 - 299 BLOCK OF S STREET NE\n",
      "XBLOCK                                          399759\n",
      "YBLOCK                                          138467\n",
      "WARD                                                 5\n",
      "ANC                                                 5E\n",
      "DISTRICT                                             5\n",
      "PSA                                                502\n",
      "NEIGHBORHOOD_CLUSTER                        Cluster 21\n",
      "BLOCK_GROUP                                   008702 2\n",
      "CENSUS_TRACT                                      8702\n",
      "VOTING_PRECINCT                            Precinct 75\n",
      "LATITUDE                                       38.9141\n",
      "LONGITUDE                                     -77.0028\n",
      "BID                                                NaN\n",
      "START_DATE                    2012-01-17T17:45:00.000Z\n",
      "END_DATE                      2012-01-18T08:00:00.000Z\n",
      "OBJECTID                                     167304064\n",
      "Name: 1234, dtype: object\n",
      "======================================================\n"
     ]
    }
   ],
   "source": [
    "print('======================================================')\n",
    "print('--====< Example Record from Data Set>============')\n",
    "print(df.iloc[1234])\n",
    "print('======================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Verify data quality"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Unique Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======================================================\n",
      "14245\n",
      "451\n",
      "======================================================\n"
     ]
    }
   ],
   "source": [
    "print('======================================================')\n",
    "print (np.count_nonzero(df['BLOCK'].unique()))\n",
    "df.drop('BLOCK', axis=1, inplace=True)\n",
    "\n",
    "print (np.count_nonzero(df['BLOCK_GROUP'].unique()))\n",
    "df.drop('BLOCK_GROUP', axis=1, inplace=True)\n",
    "print('======================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Block has 14,245 unique values indicating the street and block identifier. This value appears to be based on the CENSUS_TRACT variable, but with higher resolution. Mapping these unique values is time consuming and doesn't provide meaningful value for for further Analysis. Therefore, the column will be removed from the working data set.\n",
    "Block Group has 451 unique values indicating the land management tract identifier. Mapping these unique values is time consuming and doesn't provide meaning value for further Analysis. Therefore, the column will be removed from the working data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if END_DATE is NaN, then use START_DATE\n",
    "df['END_DATE'].fillna(df['START_DATE'], inplace=True)\n",
    "\n",
    "# if VOTING_PRECINCT is NaN, then set it to 0\n",
    "df['VOTING_PRECINCT'].fillna(0, inplace=True)\n",
    "\n",
    "# if NEIGHBORHOOD_CLUSTER is NaN, then set it to 0\n",
    "df['NEIGHBORHOOD_CLUSTER'].fillna(0, inplace=True)\n",
    "\n",
    "# if CENSUS_TRACT is NaN, then set it to 0\n",
    "df['CENSUS_TRACT'].fillna(0, inplace=True)\n",
    "# if WARD is NaN, then use ANC\n",
    "# df['WARD'].fillna(df['ANC'], inplace=True)\n",
    "# if WARD is NaN, then set it to 0\n",
    "df['WARD'].fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==========================================================\n",
      "        PSA  DISTRICT  PSA_ID  DistrictID\n",
      "4575    NaN       NaN   708.0           7\n",
      "9560    NaN       1.0   107.0           1\n",
      "11114   NaN       NaN   208.0           2\n",
      "11333   NaN       NaN   102.0           1\n",
      "12890   NaN       NaN   506.0           5\n",
      "14021   NaN       NaN   102.0           1\n",
      "20148   NaN       NaN   704.0           7\n",
      "22443   NaN       NaN   102.0           1\n",
      "26647   NaN       NaN   202.0           2\n",
      "26848   NaN       NaN   102.0           1\n",
      "28337   NaN       NaN   102.0           1\n",
      "29280   NaN       1.0   108.0           1\n",
      "29566   NaN       1.0   108.0           1\n",
      "29979   NaN       1.0   103.0           1\n",
      "32311   NaN       NaN   102.0           1\n",
      "33553   NaN       NaN   403.0           4\n",
      "39115   NaN       NaN   102.0           1\n",
      "46313   NaN       NaN   102.0           1\n",
      "46530   NaN       NaN   102.0           1\n",
      "46860   NaN       NaN   102.0           1\n",
      "50480   NaN       NaN   103.0           1\n",
      "54194   NaN       NaN   105.0           1\n",
      "55129   NaN       NaN   102.0           1\n",
      "55130   NaN       NaN   102.0           1\n",
      "57138   NaN       NaN   102.0           1\n",
      "57949   NaN       NaN   102.0           1\n",
      "57950   NaN       NaN   102.0           1\n",
      "57957   NaN       NaN   102.0           1\n",
      "58161   NaN       NaN   102.0           1\n",
      "58162   NaN       NaN   102.0           1\n",
      "...     ...       ...     ...         ...\n",
      "146764  NaN       NaN   608.0           6\n",
      "146768  NaN       NaN   304.0           3\n",
      "146772  NaN       NaN   101.0           1\n",
      "146783  NaN       NaN   307.0           3\n",
      "146813  NaN       NaN   501.0           5\n",
      "146814  NaN       NaN   707.0           7\n",
      "146821  NaN       NaN   302.0           3\n",
      "146822  NaN       NaN   207.0           2\n",
      "146823  NaN       NaN   409.0           4\n",
      "146825  NaN       NaN   305.0           3\n",
      "154788  NaN       NaN   101.0           1\n",
      "155133  NaN       NaN   101.0           1\n",
      "156570  NaN       NaN   101.0           1\n",
      "157151  NaN       NaN   101.0           1\n",
      "157283  NaN       NaN   101.0           1\n",
      "158605  NaN       NaN   101.0           1\n",
      "161987  NaN       NaN   101.0           1\n",
      "162461  NaN       NaN   101.0           1\n",
      "167101  NaN       NaN   101.0           1\n",
      "167609  NaN       NaN   101.0           1\n",
      "167743  NaN       NaN   101.0           1\n",
      "171444  NaN       NaN   101.0           1\n",
      "173867  NaN       NaN   101.0           1\n",
      "173935  NaN       NaN   101.0           1\n",
      "182012  NaN       NaN   101.0           1\n",
      "199013  NaN       NaN   108.0           1\n",
      "199014  NaN       NaN   208.0           2\n",
      "210959  NaN       NaN   207.0           2\n",
      "229642  NaN       NaN   409.0           4\n",
      "229643  NaN       NaN   306.0           3\n",
      "\n",
      "[135 rows x 4 columns]\n",
      "==========================================================\n"
     ]
    }
   ],
   "source": [
    "#  Create a dataframe that holds the central location of each Police Service Area (PSA)\n",
    "#  The PSAs: according to the information from the DC metropolitan ionformation and opendata DC\n",
    "# PSA's are smaller than the Police Districts, so we should have better accuracy in identifying the associated PSA\n",
    "#  The PSA ID contains the District ID, so we can impute the District from the PSA\n",
    "psa_loc = pd.DataFrame(df[['PSA','XBLOCK','YBLOCK']].groupby('PSA').median())\n",
    "\n",
    "#  ---==< Estimate PSA membership based on proximity to each area's centroid >==---\n",
    "def nearbyPSA(nPSA,dX,dY):\n",
    "    # Default to the current PSA ID\n",
    "    nearbyPSA = nPSA\n",
    "    \n",
    "    # Only operate on missing IDs\n",
    "    if (pd.isnull(nPSA)):\n",
    "        minDist = 9e99  # Set the initial closest distance to be a large value\n",
    "        nearbyPSA = 0\n",
    "        \n",
    "        # Loop through the records in the psa_loc dataframe\n",
    "        for PSA_ID, PSA in psa_loc.iterrows():\n",
    "            # Calculate the distance between the report and the current PSA using the Eucleadian distance\n",
    "            thisDist = math.sqrt((dX - PSA['XBLOCK'])**2 + (dY - PSA['YBLOCK'])**2)\n",
    "            \n",
    "            # If this distance is smaller than the current minimum distance, update the minimum distance\n",
    "            if (thisDist < minDist):\n",
    "                minDist = thisDist # Replace the minimum distance with the current distance\n",
    "                nearbyPSA = PSA_ID # Remember which PSA this is related to\n",
    "                \n",
    "    # Return the ID for the closest PSA\n",
    "    return [nearbyPSA, int(nearbyPSA / 100)]\n",
    "\n",
    "#  Impute the missing PSA\n",
    "df['PSA_ID'] = 0\n",
    "df['DistrictID'] = 0\n",
    "df[['PSA_ID','DistrictID']] = list(map(nearbyPSA,df['PSA'],df['XBLOCK'],df['YBLOCK']))\n",
    "\n",
    "#  Display the results of this imputation method.\n",
    "print('==========================================================')\n",
    "print(df[['PSA','DISTRICT','PSA_ID','DistrictID']][df['PSA'].isnull()])\n",
    "print('==========================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Checking for duplicated  data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# do we have duplicate rows?\n",
    "# the great thing is that i took care of them while concatenating the different values in the initial Data setup\n",
    "print(df.duplicated().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.WARD.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Feature Reeengineering "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# strip 'Precinct ' from VOTING_PRECINCT values\n",
    "# http://stackoverflow.com/questions/13682044/pandas-dataframe-remove-unwanted-parts-from-strings-in-a-column\n",
    "df['VOTING_PRECINCT'] = df['VOTING_PRECINCT'].apply(str).map(lambda x: x.lstrip('Precinct '))\n",
    "\n",
    "# strip 'Cluster ' from NEIGHBORHOOD_CLUSTER values\n",
    "df['NEIGHBORHOOD_CLUSTER'] = df['NEIGHBORHOOD_CLUSTER'].apply(str).map(lambda x: x.lstrip('Cluster '))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======================================================\n",
      "======================================================\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 231502 entries, 0 to 231501\n",
      "Data columns (total 28 columns):\n",
      "Unnamed: 0              231502 non-null int64\n",
      "X                       231502 non-null float64\n",
      "Y                       231502 non-null float64\n",
      "CCN                     231502 non-null int64\n",
      "REPORT_DAT              231502 non-null datetime64[ns]\n",
      "SHIFT                   231502 non-null object\n",
      "METHOD                  231502 non-null object\n",
      "OFFENSE                 231502 non-null object\n",
      "XBLOCK                  231502 non-null float64\n",
      "YBLOCK                  231502 non-null float64\n",
      "WARD                    231502 non-null int64\n",
      "ANC                     231502 non-null category\n",
      "DISTRICT                231373 non-null float64\n",
      "PSA                     231367 non-null float64\n",
      "NEIGHBORHOOD_CLUSTER    231502 non-null int64\n",
      "CENSUS_TRACT            231502 non-null int64\n",
      "VOTING_PRECINCT         231502 non-null int64\n",
      "LATITUDE                231502 non-null float64\n",
      "LONGITUDE               231502 non-null float64\n",
      "BID                     38549 non-null object\n",
      "START_DATE              231498 non-null datetime64[ns]\n",
      "END_DATE                231500 non-null datetime64[ns]\n",
      "OBJECTID                231502 non-null int64\n",
      "PSA_ID                  231502 non-null int64\n",
      "DistrictID              231502 non-null int64\n",
      "SHIFT_Code              231502 non-null category\n",
      "OFFENSE_Code            231502 non-null category\n",
      "METHOD_Code             231502 non-null category\n",
      "dtypes: category(4), datetime64[ns](3), float64(8), int64(9), object(4)\n",
      "memory usage: 43.3+ MB\n",
      "None\n",
      "\n",
      "Unnamed: 0                             1234\n",
      "X                                  -77.0028\n",
      "Y                                   38.9141\n",
      "CCN                                12008660\n",
      "REPORT_DAT              2012-01-18 09:32:00\n",
      "SHIFT                                   DAY\n",
      "METHOD                               OTHERS\n",
      "OFFENSE                 MOTOR VEHICLE THEFT\n",
      "XBLOCK                               399759\n",
      "YBLOCK                               138467\n",
      "WARD                                      5\n",
      "ANC                                      55\n",
      "DISTRICT                                  5\n",
      "PSA                                     502\n",
      "NEIGHBORHOOD_CLUSTER                     21\n",
      "CENSUS_TRACT                           8702\n",
      "VOTING_PRECINCT                          75\n",
      "LATITUDE                            38.9141\n",
      "LONGITUDE                          -77.0028\n",
      "BID                                     NaN\n",
      "START_DATE              2012-01-17 17:45:00\n",
      "END_DATE                2012-01-18 08:00:00\n",
      "OBJECTID                          167304064\n",
      "PSA_ID                                  502\n",
      "DistrictID                                5\n",
      "SHIFT_Code                                1\n",
      "OFFENSE_Code                              6\n",
      "METHOD_Code                               1\n",
      "Name: 1234, dtype: object\n",
      "======================================================\n"
     ]
    }
   ],
   "source": [
    "print('======================================================')\n",
    "# convert REPORT_DAT to datetime\n",
    "df['REPORT_DAT'] = pd.to_datetime(df['REPORT_DAT'])\n",
    "\n",
    "# convert SHIFT to int\n",
    "shift_mapping = {'day':1, 'evening':2, 'midnight':3}\n",
    "df['SHIFT_Code'] = df['SHIFT'].str.lower().map(shift_mapping).astype('category')\n",
    "\n",
    "# convert OFFENSE to numeric\n",
    "# Python for Data Analysis, pg. 279\n",
    "offense_mapping = {'theft/other':1, 'theft f/auto':2, 'burglary':3, 'assault w/dangerous weapon':4, 'robbery':5,\n",
    "                  'motor vehicle theft':6, 'homicide':7, 'sex abuse':8, 'arson':9}\n",
    "df['OFFENSE_Code'] = df['OFFENSE'].str.lower().map(offense_mapping).astype('category')\n",
    "df['OFFENSE'] = df['OFFENSE'].str.replace('DANGEROUS WEAPON', 'DW')\n",
    "\n",
    "# convert METHOD to numeric\n",
    "method_mapping = {'others':1, 'gun':2, 'knife':3}\n",
    "df['METHOD_Code'] = df['METHOD'].str.lower().map(method_mapping).astype('category')\n",
    "\n",
    "# convert DISTRICT to numeric\n",
    "df['DistrictID'] = df['DistrictID'].astype(np.int64)\n",
    "\n",
    "# convert PSA to numeric\n",
    "df['PSA_ID'] = df['PSA_ID'].astype(np.int64)\n",
    "\n",
    "# convert WARD to numeric\n",
    "df['WARD'] = df['WARD'].astype(np.int64)\n",
    "\n",
    "# convert ANC to numeric\n",
    "anc_mapping = {'1B':12, '1D':14, '1A':11, '1C':13, '6E':65, '4C':43, '5E':55, '2B':22, '2D':24, '2F':26, '2C':23,\n",
    "       '2E':25, '2A':21, '3C':33, '3E':35, '3B':32, '3D':34, '3F':36, '3G':37, '4A':41, '4B':42, '4D':44,\n",
    "       '5A':51, '5D':54, '5C':53, '5B':52, '6A':61, '6C':63, '6B':62, '6D':64, '7D':74, '7C':73, '7E':75,\n",
    "       '7B':72, '7F':76, '8A':81, '8B':82, '8C':83, '8D':84, '8E':85}\n",
    "df['ANC'] = df['ANC'].map(anc_mapping).astype('category')\n",
    "\n",
    "# convert NEIGHBORHOOD_CLUSTER to numeric\n",
    "df['NEIGHBORHOOD_CLUSTER'] = df['NEIGHBORHOOD_CLUSTER'].astype(np.int64)\n",
    "\n",
    "# convert CENSUS_TRACT to numeric\n",
    "df['CENSUS_TRACT'] = df['CENSUS_TRACT'].astype(np.int64)\n",
    "\n",
    "# convert VOTING_PRECINCT to numeric\n",
    "df['VOTING_PRECINCT'] = df['VOTING_PRECINCT'].astype(np.int64)\n",
    "\n",
    "# convert CCN to numeric\n",
    "df['CCN'] = df['CCN'].astype(np.int64)\n",
    "\n",
    "# convert XBLOCK, YBLOCK to numeric\n",
    "df['XBLOCK'] = df['XBLOCK'].astype(np.float64)\n",
    "df['YBLOCK'] = df['YBLOCK'].astype(np.float64)\n",
    "\n",
    "# convert START_DATE, END_DATE to dateime\n",
    "df['START_DATE'] = pd.to_datetime(df['START_DATE'])\n",
    "df['END_DATE'] = pd.to_datetime(df['END_DATE'])\n",
    "print('======================================================')\n",
    "print(df.info())\n",
    "print()\n",
    "print(df.iloc[1234])\n",
    "print('======================================================')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### How do we identify Outliers\n",
    "one hundredsix crimes were reported in 2012 which started before 2011. Six of the crimes started in 1915. Are these start dates in error or were these unsolved crimes recently solved through new technology advancements? Is it truly a 100-year-old cold case, or did the person simply enter the wrong century? beacuse the timestamp shows from 01/16/1800 one crime, two crimes from 1912 reported in 2011 and one crime of 1914 reported in 2011."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======================================================\n",
      "---======< Dealing with outliers>=======-------\n",
      "106\n",
      "[Timestamp('1800-01-16 09:51:02'), Timestamp('1912-08-22 21:00:00'), Timestamp('1912-09-16 23:40:00'), Timestamp('1914-09-26 19:00:00'), Timestamp('1915-03-18 16:00:00'), Timestamp('1915-08-30 06:00:35'), Timestamp('1915-09-17 18:00:52'), Timestamp('1915-10-10 22:30:45'), Timestamp('1915-10-16 21:03:03'), Timestamp('1915-10-17 21:00:57')]\n",
      "======================================================\n"
     ]
    }
   ],
   "source": [
    "print('======================================================')\n",
    "print('---======< Dealing with outliers>=======-------')\n",
    "print(df['START_DATE'][df['START_DATE']<'1/1/2011'].count())\n",
    "print(sorted(df['START_DATE'][df['START_DATE']<'1/1/2011'])[:10])\n",
    "print('======================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "four hundred fourty Five crimes were reported in 2013 which started before 2012. Six of the crimes started in 1915. Are these start dates in error or were these unsolved crimes recently solved through new technology advancements? Is it truly a 100-year-old cold case, or did the person simply enter the wrong century? beacuse the timestamp shows from 01/16/1800 one crime, two crimes from 1912 reported in 2011 and one crime of 1914 reported in 2012."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======================================================\n",
      "---======< Dealing with outliers>=======-------\n",
      "445\n",
      "[Timestamp('1800-01-16 09:51:02'), Timestamp('1912-08-22 21:00:00'), Timestamp('1912-09-16 23:40:00'), Timestamp('1914-09-26 19:00:00'), Timestamp('1915-03-18 16:00:00'), Timestamp('1915-08-30 06:00:35'), Timestamp('1915-09-17 18:00:52'), Timestamp('1915-10-10 22:30:45'), Timestamp('1915-10-16 21:03:03'), Timestamp('1915-10-17 21:00:57')]\n",
      "======================================================\n"
     ]
    }
   ],
   "source": [
    "print('======================================================')\n",
    "print('---======< Dealing with outliers>=======-------')\n",
    "print(df['START_DATE'][df['START_DATE']<'1/1/2012'].count())\n",
    "print(sorted(df['START_DATE'][df['START_DATE']<'1/1/2012'])[:10])\n",
    "print('======================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "35633 crimes were reported in 2014 which started before 2013. Six of the crimes started in 1915. Are these start dates in error or were these unsolved crimes recently solved through new technology advancements? Is it truly a 100-year-old cold case, or did the person simply enter the wrong century? beacuse the timestamp shows from 01/16/1800 one crime, two crimes from 1912 reported in 2011 and one crime of 1914 reported in 2013."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======================================================\n",
      "---======< Dealing with outliers>=======-------\n",
      "35633\n",
      "[Timestamp('1800-01-16 09:51:02'), Timestamp('1912-08-22 21:00:00'), Timestamp('1912-09-16 23:40:00'), Timestamp('1914-09-26 19:00:00'), Timestamp('1915-03-18 16:00:00'), Timestamp('1915-08-30 06:00:35'), Timestamp('1915-09-17 18:00:52'), Timestamp('1915-10-10 22:30:45'), Timestamp('1915-10-16 21:03:03'), Timestamp('1915-10-17 21:00:57')]\n",
      "======================================================\n"
     ]
    }
   ],
   "source": [
    "print('======================================================')\n",
    "print('---======< Dealing with outliers>=======-------')\n",
    "print(df['START_DATE'][df['START_DATE']<'1/1/2013'].count())\n",
    "print(sorted(df['START_DATE'][df['START_DATE']<'1/1/2013'])[:10])\n",
    "print('======================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Creating New Features\n",
    "creating new feature CRIME_TYPE to indicate if the crime was violent or nonviolent. This feature will be used for later prediction purposes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating feature for crime type 1 = Violent, 2 = non violent\n",
    "violent_offense = [4, 5, 7, 8]\n",
    "df['CRIME_TYPE'] = np.where(df['OFFENSE_Code'].isin(violent_offense), 1, 2)\n",
    "df['CRIME_TYPE'] = df['CRIME_TYPE'].astype('category')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating additional feature AGE to indicate the timespan between the latest the crime could have been committed and earliest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create age of crime END_DATE - START_DATE in seconds\n",
    "df['AGE'] = (df['END_DATE'] - df['START_DATE'])/np.timedelta64(1, 's')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new feature TIME_TO_REPORT to indicate the timespan between the latest time the crime was committed and\n",
    "# the time it was reported to the MPD. that is  time it took from crime to report it, REPORT_DAT - END_DATE in seconds\n",
    "df['TIME_TO_REPORT'] = (df['REPORT_DAT'] - df['END_DATE'])/np.timedelta64(1, 's')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DATE'] = pd.to_datetime(df['END_DATE'], format = '%d/%m/%Y %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_separate(df):\n",
    "    \"\"\" function that separates the sepecific interest days,weeks from the END_DATE\"\"\"\n",
    "    df = df.copy()\n",
    "    df['Year'] = pd.DatetimeIndex(df['DATE']).year\n",
    "    df['Month'] = pd.DatetimeIndex(df['DATE']).month\n",
    "    df['Day'] = pd.DatetimeIndex(df['DATE']).day\n",
    "    df['hour'] = pd.DatetimeIndex(df['DATE']).hour\n",
    "    df['dayofyear'] =pd.DatetimeIndex(df['DATE']).dayofyear\n",
    "    df['week'] =pd.DatetimeIndex(df['DATE']).week\n",
    "    df['weekofyear'] =pd.DatetimeIndex(df['DATE']).weekofyear\n",
    "    df['dayofweek'] =pd.DatetimeIndex(df['DATE']).dayofweek\n",
    "    df['weekday'] =pd.DatetimeIndex(df['DATE']).weekday\n",
    "    df['quarter'] =pd.DatetimeIndex(df['DATE']).quarter\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# here i have created an additional columns that helps us in analyzing the timing of the crimes happeing either by day\n",
    "# week, month, year as well as the quarters and weekdays and week ends.\n",
    "df['Year'] = pd.DatetimeIndex(df['DATE']).year\n",
    "df['Month'] = pd.DatetimeIndex(df['DATE']).month\n",
    "df['Day'] = pd.DatetimeIndex(df['DATE']).day\n",
    "df['hour'] = pd.DatetimeIndex(df['DATE']).hour\n",
    "df['dayofyear'] =pd.DatetimeIndex(df['DATE']).dayofyear\n",
    "df['week'] =pd.DatetimeIndex(df['DATE']).week\n",
    "df['weekofyear'] =pd.DatetimeIndex(df['DATE']).weekofyear\n",
    "df['dayofweek'] =pd.DatetimeIndex(df['DATE']).dayofweek\n",
    "df['weekday'] =pd.DatetimeIndex(df['DATE']).weekday\n",
    "df['quarter'] =pd.DatetimeIndex(df['DATE']).quarter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MOST IMPORTANT ATTRIBUTTES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### OFFENSE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==============================================================\n",
      "---<Examine the frequency of types of crimes/offenses>--======\n",
      "    __________________________________________________\n",
      " Total Offenses - Count\n",
      "231502\n",
      "\n",
      "_________________________________\n",
      " Offense Type - Count\n",
      "_________________________________\n",
      "OFFENSE\n",
      "ARSON                    127\n",
      "ASSAULT W/DW           14528\n",
      "BURGLARY               16965\n",
      "HOMICIDE                 781\n",
      "MOTOR VEHICLE THEFT    17714\n",
      "ROBBERY                20868\n",
      "SEX ABUSE               1989\n",
      "THEFT F/AUTO           69604\n",
      "THEFT/OTHER            88926\n",
      "Name: CCN, dtype: int64\n",
      "\n",
      "_________________________________\n",
      " Offense as percentage of total \n",
      "_______________________________\n",
      "OFFENSE\n",
      "ARSON                   0.054859\n",
      "ASSAULT W/DW            6.275540\n",
      "BURGLARY                7.328230\n",
      "HOMICIDE                0.337362\n",
      "MOTOR VEHICLE THEFT     7.651770\n",
      "ROBBERY                 9.014177\n",
      "SEX ABUSE               0.859172\n",
      "THEFT F/AUTO           30.066263\n",
      "THEFT/OTHER            38.412627\n",
      "Name: CCN, dtype: float64\n",
      "\n",
      "___________________________\n",
      " Offense Rate per 100,000 \n",
      "__________________________\n",
      "OFFENSE\n",
      "ARSON                     18.300450\n",
      "ASSAULT W/DW            2093.456220\n",
      "BURGLARY                2444.623126\n",
      "HOMICIDE                 112.540564\n",
      "MOTOR VEHICLE THEFT     2552.552553\n",
      "ROBBERY                 3007.037748\n",
      "SEX ABUSE                286.610987\n",
      "THEFT F/AUTO           10029.799473\n",
      "THEFT/OTHER            12814.061662\n",
      "Name: CCN, dtype: float64\n",
      "\n",
      "_____________________________________\n",
      " Odds of being a victim - by offense\n",
      "____________________________________\n",
      "OFFENSE\n",
      "ARSON                  0.000183\n",
      "ASSAULT W/DW           0.020935\n",
      "BURGLARY               0.024446\n",
      "HOMICIDE               0.001125\n",
      "MOTOR VEHICLE THEFT    0.025526\n",
      "ROBBERY                0.030070\n",
      "SEX ABUSE              0.002866\n",
      "THEFT F/AUTO           0.100298\n",
      "THEFT/OTHER            0.128141\n",
      "Name: CCN, dtype: float64\n",
      "==============================================================\n"
     ]
    }
   ],
   "source": [
    "print('==============================================================')\n",
    "print('---<Examine the frequency of types of crimes/offenses>--======')\n",
    "print('    __________________________________________________')\n",
    "print(' Total Offenses - Count')\n",
    "total_crime = df.CCN.count()\n",
    "print (total_crime)\n",
    "print('')\n",
    "crime_rate = df.groupby('OFFENSE')\n",
    "print('_________________________________')\n",
    "print (' Offense Type - Count')\n",
    "print('_________________________________')\n",
    "print (crime_rate.CCN.count())\n",
    "print ('')\n",
    "print('_________________________________')\n",
    "print (' Offense as percentage of total ')\n",
    "print ('_______________________________')\n",
    "print (crime_rate.CCN.count() / total_crime * 100.0)\n",
    "print()\n",
    "print ('___________________________')\n",
    "print (' Offense Rate per 100,000 ')\n",
    "print ('__________________________')\n",
    "print (crime_rate.CCN.count() / 6.93972 )\n",
    "print()\n",
    "print ('_____________________________________')\n",
    "print (' Odds of being a victim - by offense')\n",
    "print ('____________________________________')\n",
    "print (crime_rate.CCN.count() / 693972)\n",
    "\n",
    "print('==============================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### METHOD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==============================================================\n",
      "-<Examine the frequency of types of crimes/offenses by Method>===\n",
      "_____________________________________\n",
      " Total Methods - Count\n",
      "_____________________________________\n",
      "231502\n",
      "\n",
      "_____________________\n",
      "Method Type - Count\n",
      "____________________\n",
      "METHOD\n",
      "GUN        13003\n",
      "KNIFE       7234\n",
      "OTHERS    211265\n",
      "Name: CCN, dtype: int64\n",
      "\n",
      "______________________________\n",
      " Method as percentage of total \n",
      "_____________________________\n",
      "METHOD\n",
      "GUN        5.616798\n",
      "KNIFE      3.124811\n",
      "OTHERS    91.258391\n",
      "Name: CCN, dtype: float64\n",
      "_________________________\n",
      " Method Rate per 100,000 \n",
      "_________________________\n",
      "METHOD\n",
      "GUN        1873.706720\n",
      "KNIFE      1042.405169\n",
      "OTHERS    30442.870894\n",
      "Name: CCN, dtype: float64\n",
      "___________________________________\n",
      " Odds of being a victim - by method\n",
      "_______________________________\n",
      "METHOD\n",
      "GUN       0.018737\n",
      "KNIFE     0.010424\n",
      "OTHERS    0.304429\n",
      "Name: CCN, dtype: float64\n",
      "==============================================================\n"
     ]
    }
   ],
   "source": [
    "print('==============================================================')\n",
    "print('-<Examine the frequency of types of crimes/offenses by Method>===')\n",
    "print ('_____________________________________')\n",
    "print(' Total Methods - Count') \n",
    "print ('_____________________________________')\n",
    "print(df.CCN.count())\n",
    "print ('')\n",
    "\n",
    "method_rate = df.groupby('METHOD')\n",
    "print ('_____________________')\n",
    "print ('Method Type - Count')\n",
    "print ('____________________')\n",
    "print(method_rate.CCN.count())\n",
    "print()\n",
    "print ('______________________________')\n",
    "print (' Method as percentage of total ')\n",
    "print ('_____________________________')\n",
    "print (method_rate.CCN.count() / total_crime * 100.0)\n",
    "\n",
    "print ('_________________________')\n",
    "print (' Method Rate per 100,000 ')\n",
    "print ('_________________________')\n",
    "print( method_rate.CCN.count() / 6.93972)\n",
    "print ('___________________________________')\n",
    "print (' Odds of being a victim - by method')\n",
    "print ('_______________________________')\n",
    "print (method_rate.CCN.count() / 693972)\n",
    "print('==============================================================')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Age of Crime\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==============================================================\n",
      "-<Examine the age of  crimes/offenses by Time >===\n",
      "count    2.314980e+05\n",
      "mean     1.017558e+02\n",
      "std      7.671005e+03\n",
      "min     -3.057575e+04\n",
      "25%      8.333333e-02\n",
      "50%      8.533333e-01\n",
      "75%      7.000556e+00\n",
      "max      1.875912e+06\n",
      "Name: AGE, dtype: float64\n",
      "\n",
      "count    231402.000000\n",
      "mean         32.664778\n",
      "std         348.098907\n",
      "min      -14261.416667\n",
      "25%           0.083333\n",
      "50%           0.851528\n",
      "75%           7.000000\n",
      "max       15335.998056\n",
      "Name: AGE, dtype: float64\n",
      "==============================================================\n"
     ]
    }
   ],
   "source": [
    "print('==============================================================')\n",
    "print('-<Examine the age of  crimes/offenses by Time >===')\n",
    "\n",
    "# hours\n",
    "temp = df['AGE'] / 3600\n",
    "\n",
    "print( temp.describe())\n",
    "print('') \n",
    "\n",
    "# exclude temp two std away from mean\n",
    "#http://stackoverflow.com/questions/23199796/detect-and-exclude-outliers-in-pandas-dataframe\n",
    "print (temp[~(np.abs(temp - temp.mean())>(2*temp.std()))].describe())\n",
    "print('==============================================================')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time to Report a Crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==============================================================\n",
      "-<Examine the frequency of time it takes to report a of crimes/offenses >===\n",
      "count    231500.000000\n",
      "mean         52.911765\n",
      "std        3900.940836\n",
      "min     -788927.866667\n",
      "25%           0.100000\n",
      "50%           0.916667\n",
      "75%           3.033333\n",
      "max      876936.004722\n",
      "Name: TIME_TO_REPORT, dtype: float64\n",
      "-----------------------\n",
      "count    231333.000000\n",
      "mean         25.469048\n",
      "std         198.946217\n",
      "min       -7004.500000\n",
      "25%           0.100000\n",
      "50%           0.916667\n",
      "75%           3.018889\n",
      "max        7810.550000\n",
      "Name: TIME_TO_REPORT, dtype: float64\n",
      "==============================================================\n"
     ]
    }
   ],
   "source": [
    "print('==============================================================')\n",
    "print('-<Examine the frequency of time it takes to report a of crimes/offenses >===')\n",
    "# hours\n",
    "temp = df['TIME_TO_REPORT'] / 3600\n",
    "\n",
    "print(temp.describe())\n",
    "print ('-----------------------')\n",
    "\n",
    "# excluding temp two std away from mean\n",
    "print (temp[~(np.abs(temp - temp.mean())>(2*temp.std()))].describe())\n",
    "print('==============================================================')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('./input/dc_crime2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}