crime_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "import json\n",
    "import os\n",
    "from config import geoapify_key\n",
    "import zipfile\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Processing (collection and manipulation of data)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Collection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Zip File Extraction\n",
    "    # ? Insead of extracting one zip file at a given time, we will extract all the zip files using a for loop\n",
    "def unzip_files(zip_path, extract_path):\n",
    "    for zip_path in zip_paths:\n",
    "        with zipfile.ZipFile (zip_path, 'r') as zObject:\n",
    "        # * Extracting all memebters of the zip files into a specific location\n",
    "            zObject.extractall(\n",
    "                path = extract_path\n",
    "            )\n",
    "# * Usage zip function - Variables:\n",
    "zip_paths = [\"/Users/galbeeir/Desktop/git/crime_analysis/Data source zips/Datasets_1.zip\", \"/Users/galbeeir/Desktop/git/crime_analysis/Data source zips/Datasets_2.zip\"]\n",
    "extract_path = \"/Users/galbeeir/Desktop/git/crime_analysis/\"\n",
    "\n",
    "# * Executing the zip function\n",
    "unzip_files(zip_paths, extract_path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>ID</th>\n",
       "      <th>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Block</th>\n",
       "      <th>IUCR</th>\n",
       "      <th>Primary Type</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location Description</th>\n",
       "      <th>Arrest</th>\n",
       "      <th>Domestic</th>\n",
       "      <th>Beat</th>\n",
       "      <th>District</th>\n",
       "      <th>Ward</th>\n",
       "      <th>Community Area</th>\n",
       "      <th>Year</th>\n",
       "      <th>Updated On</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>11553168</td>\n",
       "      <td>JC100745</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>008XX N MICHIGAN AVE</td>\n",
       "      <td>0890</td>\n",
       "      <td>THEFT</td>\n",
       "      <td>FROM BUILDING</td>\n",
       "      <td>RESTAURANT</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1833</td>\n",
       "      <td>18</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>01/10/2019 03:16:50 PM</td>\n",
       "      <td>41.89877</td>\n",
       "      <td>-87.624115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>11895528</td>\n",
       "      <td>JC515003</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>059XX W LELAND AVE</td>\n",
       "      <td>1153</td>\n",
       "      <td>DECEPTIVE PRACTICE</td>\n",
       "      <td>FINANCIAL IDENTITY THEFT OVER $ 300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1622</td>\n",
       "      <td>16</td>\n",
       "      <td>45.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>11/19/2019 03:57:06 PM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>12456410</td>\n",
       "      <td>JE341674</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>012XX W JACKSON BLVD</td>\n",
       "      <td>2825</td>\n",
       "      <td>OTHER OFFENSE</td>\n",
       "      <td>HARASSMENT BY TELEPHONE</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1231</td>\n",
       "      <td>12</td>\n",
       "      <td>28.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>08/19/2021 04:51:33 PM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>12367811</td>\n",
       "      <td>JE232794</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>008XX W AGATITE AVE</td>\n",
       "      <td>1537</td>\n",
       "      <td>OFFENSE INVOLVING CHILDREN</td>\n",
       "      <td>POSSESSION OF PORNOGRAPHIC PRINT</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>1914</td>\n",
       "      <td>19</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>05/18/2021 05:27:50 PM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>12368777</td>\n",
       "      <td>JE234183</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>035XX S COTTAGE GROVE AVE</td>\n",
       "      <td>0281</td>\n",
       "      <td>CRIMINAL SEXUAL ASSAULT</td>\n",
       "      <td>NON-AGGRAVATED</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>212</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>05/19/2021 06:08:48 PM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0        ID Case Number                 Date  \\\n",
       "0           0  11553168    JC100745  2019-01-01 00:00:00   \n",
       "1           1  11895528    JC515003  2019-01-01 00:00:00   \n",
       "2           2  12456410    JE341674  2019-01-01 00:00:00   \n",
       "3           3  12367811    JE232794  2019-01-01 00:00:00   \n",
       "4           4  12368777    JE234183  2019-01-01 00:00:00   \n",
       "\n",
       "                       Block  IUCR                Primary Type  \\\n",
       "0       008XX N MICHIGAN AVE  0890                       THEFT   \n",
       "1         059XX W LELAND AVE  1153          DECEPTIVE PRACTICE   \n",
       "2       012XX W JACKSON BLVD  2825               OTHER OFFENSE   \n",
       "3        008XX W AGATITE AVE  1537  OFFENSE INVOLVING CHILDREN   \n",
       "4  035XX S COTTAGE GROVE AVE  0281     CRIMINAL SEXUAL ASSAULT   \n",
       "\n",
       "                           Description Location Description  Arrest  Domestic  \\\n",
       "0                        FROM BUILDING           RESTAURANT   False     False   \n",
       "1  FINANCIAL IDENTITY THEFT OVER $ 300                  NaN   False     False   \n",
       "2              HARASSMENT BY TELEPHONE            RESIDENCE   False     False   \n",
       "3     POSSESSION OF PORNOGRAPHIC PRINT            APARTMENT    True     False   \n",
       "4                       NON-AGGRAVATED            RESIDENCE   False     False   \n",
       "\n",
       "   Beat  District  Ward  Community Area  Year              Updated On  \\\n",
       "0  1833        18   2.0             8.0  2019  01/10/2019 03:16:50 PM   \n",
       "1  1622        16  45.0            15.0  2019  11/19/2019 03:57:06 PM   \n",
       "2  1231        12  28.0            28.0  2019  08/19/2021 04:51:33 PM   \n",
       "3  1914        19  46.0             3.0  2019  05/18/2021 05:27:50 PM   \n",
       "4   212         2   4.0            36.0  2019  05/19/2021 06:08:48 PM   \n",
       "\n",
       "   Latitude  Longitude  \n",
       "0  41.89877 -87.624115  \n",
       "1       NaN        NaN  \n",
       "2       NaN        NaN  \n",
       "3       NaN        NaN  \n",
       "4       NaN        NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# * List of directories\n",
    "datasets_1 = \"/Users/galbeeir/Desktop/git/crime_analysis/Datasets_1/\"\n",
    "datasets_2 = \"/Users/galbeeir/Desktop/git/crime_analysis/Datasets_2/\"\n",
    "\n",
    "# * List all the files from the directory\n",
    "file_list_1 = os.listdir(datasets_1)\n",
    "file_list_2 = os.listdir(datasets_2)\n",
    "\n",
    "# * Merge all files stored in file_list\n",
    "crime_df = pd.DataFrame()\n",
    "\n",
    "# * Merge all files stored in file_list_1\n",
    "for file in file_list_1:\n",
    "    if file.endswith(\".csv\"):\n",
    "        file_path = os.path.join(datasets_1, file)\n",
    "        df = pd.read_csv(file_path, delimiter=',', encoding='utf-8')\n",
    "        crime_df = pd.concat([crime_df, df])\n",
    "\n",
    "# * Merge all files stored in file_list_2\n",
    "for file in file_list_2:\n",
    "    if file.endswith(\".csv\"):\n",
    "        file_path = os.path.join(datasets_2, file)\n",
    "        df = pd.read_csv(file_path, delimiter=',', encoding='utf-8')\n",
    "        crime_df = pd.concat([crime_df, df])\n",
    "    \n",
    "     \n",
    "crime_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Key definitions\n",
    "1. Ward = City council (There are **50 in Chicago**) \n",
    "2. Community Areas = There are **77 commnuity areas** in Chicago \n",
    "    * => [Chicago - Community Areas & Wards](https://www.chicago.gov/city/en/depts/dgs/supp_info/citywide_maps.html)\n",
    "3. District = There are **25 police districts** in Chicago => [Police Districts](https://home.chicagopolice.org/about/police-districts/)\n",
    "\n",
    "**Data processing actions:**\n",
    "Convert the community areas numbers to names=>\n",
    "- Method = using pd.read_html - using the following link => [Wikipadia - community areas in Chicago](https://en.wikipedia.org/wiki/Community_areas_in_Chicago)\n",
    "\n",
    "*Note:*<br>\n",
    "To find out more about *Ward, Community Areas, and Police Distrits*, please click here ->[`resources`](https://github.com/Kokolipa/crime_analysis/tree/main/Resources)\n",
    "<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Name</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>No.</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>Rogers Park</td>\n",
       "      <td>55628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02</th>\n",
       "      <td>West Ridge</td>\n",
       "      <td>77122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03</th>\n",
       "      <td>Uptown</td>\n",
       "      <td>57182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04</th>\n",
       "      <td>Lincoln Square</td>\n",
       "      <td>40494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05</th>\n",
       "      <td>North Center</td>\n",
       "      <td>35114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Name  Population\n",
       "No.                            \n",
       "01      Rogers Park       55628\n",
       "02       West Ridge       77122\n",
       "03           Uptown       57182\n",
       "04   Lincoln Square       40494\n",
       "05     North Center       35114"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Importing Chicago community area names and codes from wikipedia\n",
    "chi_community_areas = (pd.read_html(\"https://en.wikipedia.org/wiki/Community_areas_in_Chicago\")[0]\n",
    " .droplevel(level=0, axis=1)\n",
    " .set_index('No.')\n",
    " .drop(['(sq mi.)', '(/sq mi.)', '(km2)', '(/km2)'], axis=1)\n",
    " .rename(columns={'.mw-parser-output .nobold{font-weight:normal}(2020)[10]': 'Population'}))\n",
    "\n",
    "chi_community_areas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removing the Total row an the button of the dataframe\n",
    "chi_community_areas = chi_community_areas.iloc[:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Name</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>No.</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rogers Park</td>\n",
       "      <td>55628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>West Ridge</td>\n",
       "      <td>77122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Uptown</td>\n",
       "      <td>57182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lincoln Square</td>\n",
       "      <td>40494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>North Center</td>\n",
       "      <td>35114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Name  Population\n",
       "No.                            \n",
       "1       Rogers Park       55628\n",
       "2        West Ridge       77122\n",
       "3            Uptown       57182\n",
       "4    Lincoln Square       40494\n",
       "5      North Center       35114"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Changing the dtype of \n",
    "chi_community_areas.index = chi_community_areas.index.astype('Int8')\n",
    "chi_community_areas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transforming the dtype of Community area from crime_df\n",
    "crime_df['Community Area'] = crime_df['Community Area'].astype('Int8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int8Dtype()"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Validation transformation\n",
    "crime_df['Community Area'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merging both of the dataframes\n",
    "crime_df = pd.merge(\n",
    "    left = crime_df,\n",
    "    right = chi_community_areas,\n",
    "    how = 'left',\n",
    "    left_on= ['Community Area'],\n",
    "    right_on= ['No.'],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'ID', 'Case Number', 'Date', 'Block', 'IUCR',\n",
       "       'Primary Type', 'Description', 'Location Description', 'Arrest',\n",
       "       'Domestic', 'Beat', 'District', 'Ward', 'Community Area', 'Year',\n",
       "       'Updated On', 'Latitude', 'Longitude', 'Name', 'Population'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming columns\n",
    "crime_df.drop(['Unnamed: 0', 'Community Area', 'Population', 'Description', 'IUCR', 'Beat'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reorginising the columns of the dataset\n",
    "crime_df = crime_df.reindex(labels=['ID', 'Case Number', 'Date', 'Year','Updated On','Block', 'Primary Type', 'Location Description', 'Arrest', 'Domestic', 'District', 'Ward', 'Name', 'Latitude', 'Longitude'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dropping irrelevant columns\n",
    "crime_df.rename(columns={'Name': 'Community Area', 'District': 'Police District'}, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CRIMINAL DAMAGE                      118306\n",
       "CRIMINAL TRESPASS                     20967\n",
       "CRIMINAL SEXUAL ASSAULT                5716\n",
       "CRIM SEXUAL ASSAULT                     983\n",
       "CONCEALED CARRY LICENSE VIOLATION       808\n",
       "Name: Primary Type, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Search key method (ABC)\n",
    "crime_df.loc[crime_df['Primary Type'].str.startswith('C')]['Primary Type'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df['Primary Type'].replace({'CRIM SEXUAL ASSAULT': 'CRIMINAL SEXUAL ASSAULT'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replacing NaN values in the Location Description column with 'BLANK' value\n",
    "crime_df['Location Description'].fillna('Blank', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: Location Description, dtype: int64)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Search key method (ABC)\n",
    "crime_df.loc[crime_df['Location Description'].str.startswith('Z')]['Location Description'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replacing all duplicated / look alike values\n",
    "crime_df['Location Description'].replace({'AIRPORT TERMINAL UPPER LEVEL - SECURE AREA': 'AIRPORT',\n",
    "                                          'AIRPORT TERMINAL LOWER LEVEL - NON-SECURE AREA': 'AIRPORT',\n",
    "                                          'AIRPORT PARKING LOT': 'AIRPORT',\n",
    "                                          'AIRPORT BUILDING NON-TERMINAL - NON-SECURE AREA': 'AIRPORT',\n",
    "                                          'AIRPORT TERMINAL UPPER LEVEL - NON-SECURE AREA': 'AIRPORT',\n",
    "                                          'AIRPORT EXTERIOR - NON-SECURE AREA': 'AIRPORT',\n",
    "                                          'AIRPORT BUILDING NON-TERMINAL - SECURE AREA': 'AIRPORT',\n",
    "                                          'AIRPORT EXTERIOR - SECURE AREA': 'AIRPORT',\n",
    "                                          'AIRPORT/AIRCRAFT': 'AIRPORT',\n",
    "                                          'AIRPORT VENDING ESTABLISHMENT': 'AIRPORT',\n",
    "                                          'AIRPORT TRANSPORTATION SYSTEM (ATS)': 'AIRPORT',\n",
    "                                          'AIRPORT TERMINAL MEZZANINE - NON-SECURE AREA ': 'AIRPORT',\n",
    "                                          'BOAT / WATERCRAFT': 'BOAT/WATERCRAFT',\n",
    "                                          'CTA TRAIN': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA BUS': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA PLATFORM': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA STATION': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA BUS STOP': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA PARKING LOT / GARAGE / OTHER PROPERTY': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA GARAGE / OTHER PROPERTY': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA TRACKS - RIGHT OF WAY': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA \"L\" TRAIN': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA \"L\" PLATFORM': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA PROPERTY': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CTA SUBWAY STATION': 'CRIMINAL TRIBES ACT',\n",
    "                                          'CHA APARTMENT': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA PARKING LOT / GROUNDS': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA HALLWAY / STAIRWELL / ELEVATOR': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA HALLWAY/STAIRWELL/ELEVATOR': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA PARKING LOT': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA GROUNDS': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA LOBBY': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA PLAY LOT': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA HALLWAY': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHA ELEVATOR': 'CHICAGO HOUSING AUTHORITY',\n",
    "                                          'CHURCH/SYNAGOGUE/PLACE OF WORSHIP': 'CHURCH',\n",
    "                                          'CHURCH / SYNAGOGUE / PLACE OF WORSHIP': 'CHURCH',\n",
    "                                          'COLLEGE / UNIVERSITY - GROUNDS': 'COLLEGE/UNIVERSITY',\n",
    "                                          'COLLEGE/UNIVERSITY GROUNDS': 'COLLEGE/UNIVERSITY',\n",
    "                                          'FACTORY / MANUFACTURING BUILDING':'FACTORY/MANUFACTURING BUILDING',\n",
    "                                          'GAS STATION DRIVE/PROP.': 'GAS STATION',\n",
    "                                          'GOVERNMENT BUILDING / PROPERTY':'GOVERNMENT BUILDING/PROPERTY',\n",
    "                                          'HOTEL / MOTEL': 'HOTEL/MOTEL',\n",
    "                                          'HOSPITAL BUILDING / GROUNDS': 'HOSPITAL',\n",
    "                                          'HOSPITAL BUILDING/GROUNDS': 'HOSPITAL',\n",
    "                                          'LAKEFRONT / WATERFRONT / RIVERBANK':'LAKEFRONT/WATERFRONT/RIVERBANK',\n",
    "                                          'MOVIE HOUSE / THEATER': 'MOVIE HOUSE/THEATER',\n",
    "                                          'MEDICAL / DENTAL OFFICE':'MEDICAL/DENTAL OFFICE',\n",
    "                                          'NURSING / RETIREMENT HOME':'NURSING HOME/RETIREMENT HOME',\n",
    "                                          'NURSING HOME':'NURSING HOME/RETIREMENT HOME',\n",
    "                                          'OTHER (SPECIFY)':'OTHER',\n",
    "                                          'OTHER RAILROAD PROPERTY / TRAIN DEPOT':'OTHER RAILROAD PROP/TRAIN DEPOT',\n",
    "                                          'OTHER RAILROAD PROP / TRAIN DEPOT':'OTHER RAILROAD PROP/TRAIN DEPOT',\n",
    "                                          'PARKING LOT / GARAGE (NON RESIDENTIAL)': 'PARKING LOT/GARAGE',\n",
    "                                          'PARKING LOT/GARAGE(NON.RESID.)': 'PARKING LOT/GARAGE',\n",
    "                                          'POLICE FACILITY / VEHICLE PARKING LOT': 'POLICE FACILITY',\n",
    "                                          'POLICE FACILITY/VEH PARKING LOT':'POLICE FACILITY',\n",
    "                                          'RESIDENCE - PORCH / HALLWAY': 'RESIDENCE',\n",
    "                                          'RESIDENCE - YARD (FRONT / BACK)': 'RESIDENCE',\n",
    "                                          'RESIDENCE - GARAGE': 'RESIDENCE',\n",
    "                                          'RESIDENCE PORCH/HALLWAY': 'RESIDENCE',\n",
    "                                          'RESIDENTIAL YARD (FRONT/BACK)': 'RESIDENCE',\n",
    "                                          'RESIDENCE-GARAGE':'RESIDENCE', \n",
    "                                          'SCHOOL - PUBLIC BUILDING': 'SCHOOL',\n",
    "                                          'SCHOOL, PUBLIC, BUILDING': 'SCHOOL',\n",
    "                                          'SCHOOL - PUBLIC GROUNDS': 'SCHOOL',\n",
    "                                          'SCHOOL, PUBLIC, GROUNDS': 'SCHOOL',\n",
    "                                          'SCHOOL - PRIVATE GROUNDS': 'SCHOOL',\n",
    "                                          'SCHOOL, PRIVATE, BUILDING': 'SCHOOL',\n",
    "                                          'SCHOOL - PRIVATE BUILDING': 'SCHOOL',\n",
    "                                          'SCHOOL, PRIVATE, GROUNDS': 'SCHOOL',\n",
    "                                          'SCHOOL YARD': 'SCHOOL',\n",
    "                                          'SPORTS ARENA / STADIUM': 'SPORTS ARENA/STADIUM',\n",
    "                                          'TAVERN / LIQUOR STORE': 'TAVERN/LIQUOR STORE',\n",
    "                                          'VEHICLE - COMMERCIAL':'VEHICLE-COMMERCIAL',\n",
    "                                          'VEHICLE - COMMERCIAL: ENTERTAINMENT / PARTY BUS':'VEHICLE-COMMERCIAL',\n",
    "                                          'VEHICLE - COMMERCIAL: TROLLEY BUS':'VEHICLE-COMMERCIAL',\n",
    "                                          'VEHICLE-COMMERCIAL - TROLLEY BUS':'VEHICLE-COMMERCIAL',\n",
    "                                          'VEHICLE - COMMERCIAL':'VEHICLE-COMMERCIAL',\n",
    "                                          'VEHICLE-COMMERCIAL - ENTERTAINMENT/PARTY BUS':'VEHICLE-COMMERCIAL',\n",
    "                                          'VEHICLE - OTHER RIDE SHARE SERVICE (LYFT, UBER, ETC.)':'VEHICLE-OTHER RIDE SHARE SERVICE',\n",
    "                                          'VEHICLE - OTHER RIDE SHARE SERVICE (E.G., UBER, LYFT)':'VEHICLE-OTHER RIDE SHARE SERVICE',\n",
    "                                          }, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Updated On</th>\n",
       "      <th>Block</th>\n",
       "      <th>Primary Type</th>\n",
       "      <th>Location Description</th>\n",
       "      <th>Arrest</th>\n",
       "      <th>Domestic</th>\n",
       "      <th>Police District</th>\n",
       "      <th>Ward</th>\n",
       "      <th>Community Area</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11553168</td>\n",
       "      <td>JC100745</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>01/10/2019 03:16:50 PM</td>\n",
       "      <td>008XX N MICHIGAN AVE</td>\n",
       "      <td>THEFT</td>\n",
       "      <td>RESTAURANT</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>18</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Near North Side</td>\n",
       "      <td>41.89877</td>\n",
       "      <td>-87.624115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11895528</td>\n",
       "      <td>JC515003</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>11/19/2019 03:57:06 PM</td>\n",
       "      <td>059XX W LELAND AVE</td>\n",
       "      <td>DECEPTIVE PRACTICE</td>\n",
       "      <td>Blank</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>16</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Portage Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12456410</td>\n",
       "      <td>JE341674</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>08/19/2021 04:51:33 PM</td>\n",
       "      <td>012XX W JACKSON BLVD</td>\n",
       "      <td>OTHER OFFENSE</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>28.0</td>\n",
       "      <td>Near West Side</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12367811</td>\n",
       "      <td>JE232794</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>05/18/2021 05:27:50 PM</td>\n",
       "      <td>008XX W AGATITE AVE</td>\n",
       "      <td>OFFENSE INVOLVING CHILDREN</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>19</td>\n",
       "      <td>46.0</td>\n",
       "      <td>Uptown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12368777</td>\n",
       "      <td>JE234183</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>05/19/2021 06:08:48 PM</td>\n",
       "      <td>035XX S COTTAGE GROVE AVE</td>\n",
       "      <td>CRIMINAL SEXUAL ASSAULT</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Oakland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID Case Number                 Date  Year              Updated On  \\\n",
       "0  11553168    JC100745  2019-01-01 00:00:00  2019  01/10/2019 03:16:50 PM   \n",
       "1  11895528    JC515003  2019-01-01 00:00:00  2019  11/19/2019 03:57:06 PM   \n",
       "2  12456410    JE341674  2019-01-01 00:00:00  2019  08/19/2021 04:51:33 PM   \n",
       "3  12367811    JE232794  2019-01-01 00:00:00  2019  05/18/2021 05:27:50 PM   \n",
       "4  12368777    JE234183  2019-01-01 00:00:00  2019  05/19/2021 06:08:48 PM   \n",
       "\n",
       "                       Block                Primary Type Location Description  \\\n",
       "0       008XX N MICHIGAN AVE                       THEFT           RESTAURANT   \n",
       "1         059XX W LELAND AVE          DECEPTIVE PRACTICE                Blank   \n",
       "2       012XX W JACKSON BLVD               OTHER OFFENSE            RESIDENCE   \n",
       "3        008XX W AGATITE AVE  OFFENSE INVOLVING CHILDREN            APARTMENT   \n",
       "4  035XX S COTTAGE GROVE AVE     CRIMINAL SEXUAL ASSAULT            RESIDENCE   \n",
       "\n",
       "   Arrest  Domestic  Police District  Ward   Community Area  Latitude  \\\n",
       "0   False     False               18   2.0  Near North Side  41.89877   \n",
       "1   False     False               16  45.0     Portage Park       NaN   \n",
       "2   False     False               12  28.0   Near West Side       NaN   \n",
       "3    True     False               19  46.0           Uptown       NaN   \n",
       "4   False     False                2   4.0          Oakland       NaN   \n",
       "\n",
       "   Longitude  \n",
       "0 -87.624115  \n",
       "1        NaN  \n",
       "2        NaN  \n",
       "3        NaN  \n",
       "4        NaN  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Memory Optimization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1045814 entries, 0 to 1045813\n",
      "Data columns (total 15 columns):\n",
      " #   Column                Non-Null Count    Dtype  \n",
      "---  ------                --------------    -----  \n",
      " 0   ID                    1045814 non-null  int64  \n",
      " 1   Case Number           1045814 non-null  object \n",
      " 2   Date                  1045814 non-null  object \n",
      " 3   Year                  1045814 non-null  int64  \n",
      " 4   Updated On            1045814 non-null  object \n",
      " 5   Block                 1045814 non-null  object \n",
      " 6   Primary Type          1045814 non-null  object \n",
      " 7   Location Description  1045814 non-null  object \n",
      " 8   Arrest                1045814 non-null  bool   \n",
      " 9   Domestic              1045814 non-null  bool   \n",
      " 10  Police District       1045814 non-null  int64  \n",
      " 11  Ward                  1045767 non-null  float64\n",
      " 12  Community Area        1045813 non-null  object \n",
      " 13  Latitude              1028014 non-null  float64\n",
      " 14  Longitude             1028014 non-null  float64\n",
      "dtypes: bool(2), float64(3), int64(3), object(7)\n",
      "memory usage: 555.2 MB\n"
     ]
    }
   ],
   "source": [
    "crime_df.info(memory_usage='deep')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Updated On</th>\n",
       "      <th>Block</th>\n",
       "      <th>Primary Type</th>\n",
       "      <th>Location Description</th>\n",
       "      <th>Arrest</th>\n",
       "      <th>Domestic</th>\n",
       "      <th>Police District</th>\n",
       "      <th>Ward</th>\n",
       "      <th>Community Area</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11553168</td>\n",
       "      <td>JC100745</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>01/10/2019 03:16:50 PM</td>\n",
       "      <td>008XX N MICHIGAN AVE</td>\n",
       "      <td>THEFT</td>\n",
       "      <td>RESTAURANT</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>18</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Near North Side</td>\n",
       "      <td>41.89877</td>\n",
       "      <td>-87.624115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11895528</td>\n",
       "      <td>JC515003</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>11/19/2019 03:57:06 PM</td>\n",
       "      <td>059XX W LELAND AVE</td>\n",
       "      <td>DECEPTIVE PRACTICE</td>\n",
       "      <td>Blank</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>16</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Portage Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12456410</td>\n",
       "      <td>JE341674</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>08/19/2021 04:51:33 PM</td>\n",
       "      <td>012XX W JACKSON BLVD</td>\n",
       "      <td>OTHER OFFENSE</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>28.0</td>\n",
       "      <td>Near West Side</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12367811</td>\n",
       "      <td>JE232794</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>05/18/2021 05:27:50 PM</td>\n",
       "      <td>008XX W AGATITE AVE</td>\n",
       "      <td>OFFENSE INVOLVING CHILDREN</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>19</td>\n",
       "      <td>46.0</td>\n",
       "      <td>Uptown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12368777</td>\n",
       "      <td>JE234183</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019</td>\n",
       "      <td>05/19/2021 06:08:48 PM</td>\n",
       "      <td>035XX S COTTAGE GROVE AVE</td>\n",
       "      <td>CRIMINAL SEXUAL ASSAULT</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Oakland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID Case Number                 Date  Year              Updated On  \\\n",
       "0  11553168    JC100745  2019-01-01 00:00:00  2019  01/10/2019 03:16:50 PM   \n",
       "1  11895528    JC515003  2019-01-01 00:00:00  2019  11/19/2019 03:57:06 PM   \n",
       "2  12456410    JE341674  2019-01-01 00:00:00  2019  08/19/2021 04:51:33 PM   \n",
       "3  12367811    JE232794  2019-01-01 00:00:00  2019  05/18/2021 05:27:50 PM   \n",
       "4  12368777    JE234183  2019-01-01 00:00:00  2019  05/19/2021 06:08:48 PM   \n",
       "\n",
       "                       Block                Primary Type Location Description  \\\n",
       "0       008XX N MICHIGAN AVE                       THEFT           RESTAURANT   \n",
       "1         059XX W LELAND AVE          DECEPTIVE PRACTICE                Blank   \n",
       "2       012XX W JACKSON BLVD               OTHER OFFENSE            RESIDENCE   \n",
       "3        008XX W AGATITE AVE  OFFENSE INVOLVING CHILDREN            APARTMENT   \n",
       "4  035XX S COTTAGE GROVE AVE     CRIMINAL SEXUAL ASSAULT            RESIDENCE   \n",
       "\n",
       "   Arrest  Domestic  Police District  Ward   Community Area  Latitude  \\\n",
       "0   False     False               18   2.0  Near North Side  41.89877   \n",
       "1   False     False               16  45.0     Portage Park       NaN   \n",
       "2   False     False               12  28.0   Near West Side       NaN   \n",
       "3    True     False               19  46.0           Uptown       NaN   \n",
       "4   False     False                2   4.0          Oakland       NaN   \n",
       "\n",
       "   Longitude  \n",
       "0 -87.624115  \n",
       "1        NaN  \n",
       "2        NaN  \n",
       "3        NaN  \n",
       "4        NaN  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bits - reduction\n",
    "crime_df = crime_df.astype({'Year': 'Int16',\n",
    "                'Police District': 'Int8',\n",
    "                'Ward': 'category',\n",
    "                'Location Description': 'category',\n",
    "                'Primary Type': 'category',\n",
    "                'ID': 'Int32',\n",
    "                'Latitude': 'float32',\n",
    "                'Longitude': 'float32'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df['Date'] = pd.to_datetime(\n",
    "    crime_df['Date'],\n",
    "    errors = 'coerce',\n",
    "    infer_datetime_format=True, \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df['Updated On'] = pd.to_datetime(\n",
    "    crime_df['Updated On'],\n",
    "    errors = 'coerce',\n",
    "    infer_datetime_format=True, \n",
    "    format ='%T'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1028014 entries, 0 to 1045812\n",
      "Data columns (total 15 columns):\n",
      " #   Column                Non-Null Count    Dtype         \n",
      "---  ------                --------------    -----         \n",
      " 0   ID                    1028014 non-null  Int32         \n",
      " 1   Case Number           1028014 non-null  object        \n",
      " 2   Date                  1028014 non-null  datetime64[ns]\n",
      " 3   Year                  1028014 non-null  Int16         \n",
      " 4   Updated On            1028014 non-null  datetime64[ns]\n",
      " 5   Block                 1028014 non-null  object        \n",
      " 6   Primary Type          1028014 non-null  category      \n",
      " 7   Location Description  1028014 non-null  category      \n",
      " 8   Arrest                1028014 non-null  bool          \n",
      " 9   Domestic              1028014 non-null  bool          \n",
      " 10  Police District       1028014 non-null  Int8          \n",
      " 11  Ward                  1028014 non-null  category      \n",
      " 12  Community Area        1028014 non-null  object        \n",
      " 13  Latitude              1028014 non-null  float32       \n",
      " 14  Longitude             1028014 non-null  float32       \n",
      "dtypes: Int16(1), Int32(1), Int8(1), bool(2), category(3), datetime64[ns](2), float32(2), object(3)\n",
      "memory usage: 251.2 MB\n"
     ]
    }
   ],
   "source": [
    "# Memory consumption reduction = 200 + MB\n",
    "crime_df.info(memory_usage='deep')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                          0\n",
       "Case Number                 0\n",
       "Date                        0\n",
       "Year                        0\n",
       "Updated On                  0\n",
       "Block                       0\n",
       "Primary Type                0\n",
       "Location Description        0\n",
       "Arrest                      0\n",
       "Domestic                    0\n",
       "Police District             0\n",
       "Ward                       47\n",
       "Community Area              1\n",
       "Latitude                17800\n",
       "Longitude               17800\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df['Ward'].fillna('Blank', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df['Community Area'].fillna('Blank', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df.dropna(subset=['Latitude', 'Longitude'], how='any', inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NOTE:\n",
    "Because our crime dataset include 17,800 records of NaN values in both Latitude and Longitude we removed them as the purpose of this analysis is to explore the distribution of crime before and after COVID-19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                      0\n",
       "Case Number             0\n",
       "Date                    0\n",
       "Year                    0\n",
       "Updated On              0\n",
       "Block                   0\n",
       "Primary Type            0\n",
       "Location Description    0\n",
       "Arrest                  0\n",
       "Domestic                0\n",
       "Police District         0\n",
       "Ward                    0\n",
       "Community Area          0\n",
       "Latitude                0\n",
       "Longitude               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1028014 entries, 0 to 1045812\n",
      "Data columns (total 15 columns):\n",
      " #   Column                Non-Null Count    Dtype         \n",
      "---  ------                --------------    -----         \n",
      " 0   ID                    1028014 non-null  Int32         \n",
      " 1   Case Number           1028014 non-null  object        \n",
      " 2   Date                  1028014 non-null  datetime64[ns]\n",
      " 3   Year                  1028014 non-null  Int16         \n",
      " 4   Updated On            1028014 non-null  datetime64[ns]\n",
      " 5   Block                 1028014 non-null  object        \n",
      " 6   Primary Type          1028014 non-null  category      \n",
      " 7   Location Description  1028014 non-null  category      \n",
      " 8   Arrest                1028014 non-null  bool          \n",
      " 9   Domestic              1028014 non-null  bool          \n",
      " 10  Police District       1028014 non-null  Int8          \n",
      " 11  Ward                  1028014 non-null  category      \n",
      " 12  Community Area        1028014 non-null  object        \n",
      " 13  Latitude              1028014 non-null  float32       \n",
      " 14  Longitude             1028014 non-null  float32       \n",
      "dtypes: Int16(1), Int32(1), Int8(1), bool(2), category(3), datetime64[ns](2), float32(2), object(3)\n",
      "memory usage: 69.6+ MB\n"
     ]
    }
   ],
   "source": [
    "crime_df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exporting the data to ZipFile for Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_df.to_csv(\"/Users/galbeeir/Desktop/git/crime_analysis/crime_cleaned.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/06/47q5ms9540g4mnz8nnnyl32m0000gn/T/ipykernel_28915/27589574.py:3: DtypeWarning: Columns (12) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  crime_df = pd.read_csv(csv_path)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "def export_to_zip(csv_path, zip_path_1, zip_path_2, compression_level=9):\n",
    "    # Read the csv file\n",
    "    crime_df = pd.read_csv(csv_path)\n",
    "\n",
    "    # Filter the DataFrame based on the conditions\n",
    "    filtered_1 = crime_df.loc[(crime_df['Year'] == 2019)]\n",
    "    filtered_2 = crime_df.loc[(crime_df['Year'] == 2020)]\n",
    "    filtered_3 = crime_df.loc[(crime_df['Year'] == 2021)]\n",
    "    filtered_4 = crime_df.loc[(crime_df['Year'] == 2022)]\n",
    "    filtered_5 = crime_df.loc[(crime_df['Year'] == 2023)]\n",
    "\n",
    "    # Create a ZipFile and add the filtered data as separate CSV files with compression\n",
    "    with zipfile.ZipFile(zip_path_1, 'w', zipfile.ZIP_DEFLATED, compresslevel=compression_level) as zip_file_1:\n",
    "        zip_file_1.writestr('filtered_data_1.csv', filtered_1.to_csv(index=False))\n",
    "        zip_file_1.writestr('filtered_data_2.csv', filtered_2.to_csv(index=False))\n",
    "    \n",
    "    with zipfile.ZipFile(zip_path_2, 'w', zipfile.ZIP_DEFLATED, compresslevel=compression_level) as zip_file_2:\n",
    "        zip_file_2.writestr('filtered_data_3.csv', filtered_3.to_csv(index=False))\n",
    "        zip_file_2.writestr('filtered_data_4.csv', filtered_4.to_csv(index=False))\n",
    "        zip_file_2.writestr('filtered_data_5.csv', filtered_5.to_csv(index=False))\n",
    "\n",
    "csv_cleaned_path = \"/Users/galbeeir/Desktop/git/crime_analysis/crime_cleaned.csv\"\n",
    "zip_cleaned_path_1 = \"/Users/galbeeir/Desktop/git/crime_analysis/filtered_1.zip\"\n",
    "zip_cleaned_path_2 = \"/Users/galbeeir/Desktop/git/crime_analysis/filtered_2.zip\"\n",
    "\n",
    "export_to_zip(csv_cleaned_path, zip_cleaned_path_1, zip_cleaned_path_2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "dev",
   "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.10.11"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}