Crimes in Los Angeles_Data_Processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1986a979-48f4-43aa-a4b8-41471f7d470e",
   "metadata": {},
   "source": [
    "# Some Pre-processing Processes"
   ]
  },
  {
   "cell_type": "raw",
   "id": "83400c15-e616-4787-9bc0-1c1607080f1d",
   "metadata": {},
   "source": [
    "pip install kaggle"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f71a6ad3-3a8c-46c0-b388-1552fe9ee6ce",
   "metadata": {},
   "source": [
    "import json\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c4d740a3-1b9d-4471-97f6-36c4bbe440d8",
   "metadata": {},
   "source": [
    "with open(r'C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\kaggle.json') as json_file:\n",
    "    credentials = json.load(json_file)\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "9b768659-c56a-416e-abe7-8b50192bb101",
   "metadata": {},
   "source": [
    "with open(r'C:\\Users\\youss\\.kaggle\\kaggle.json', 'w') as file:\n",
    "    json.dump(credentials, file)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "933ab7aa-1c59-4dd3-9047-abd429e90596",
   "metadata": {},
   "source": [
    "from kaggle.api.kaggle_api_extended import KaggleApi\n",
    "\n",
    "api = KaggleApi()\n",
    "api.authenticate()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d89ddd3f-01a7-4ef4-ac5d-1c46235124ef",
   "metadata": {},
   "source": [
    "#https://www.kaggle.com/datasets/sudhanvahg/crimes-in-las-angeles\n",
    "api.dataset_download_file('sudhanvahg/crimes-in-las-angeles', #The publisher_user_name/Dataframe name\n",
    "                          file_name='data2.csv')#Filename with extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e2dc474d-2291-4f05-8702-9b8d98867802",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pytest-shutil in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (1.7.0)\n",
      "Requirement already satisfied: six in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest-shutil) (1.16.0)\n",
      "Requirement already satisfied: execnet in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest-shutil) (2.1.1)\n",
      "Requirement already satisfied: contextlib2 in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest-shutil) (21.6.0)\n",
      "Requirement already satisfied: pytest in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest-shutil) (8.2.2)\n",
      "Requirement already satisfied: path.py in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest-shutil) (12.5.0)\n",
      "Requirement already satisfied: mock in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest-shutil) (5.1.0)\n",
      "Requirement already satisfied: termcolor in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest-shutil) (2.4.0)\n",
      "Requirement already satisfied: path in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from path.py->pytest-shutil) (16.14.0)\n",
      "Requirement already satisfied: iniconfig in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest->pytest-shutil) (2.0.0)\n",
      "Requirement already satisfied: packaging in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest->pytest-shutil) (24.0)\n",
      "Requirement already satisfied: pluggy<2.0,>=1.5 in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest->pytest-shutil) (1.5.0)\n",
      "Requirement already satisfied: colorama in c:\\users\\youss\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pytest->pytest-shutil) (0.4.6)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install pytest-shutil"
   ]
  },
  {
   "cell_type": "raw",
   "id": "daa1bbe6-393c-4d7e-8a5c-10b10eb08a5d",
   "metadata": {},
   "source": [
    "import shutil\n",
    "shutil.unpack_archive(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\data2.csv.zip\", r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db7db772-abd5-41d3-8417-26c0dc741bbc",
   "metadata": {},
   "source": [
    "# Data Processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "29e183b0-86a5-4900-9f4d-763c7fcccadc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5b78a38e-dc05-46d4-aaff-4a3cd1204660",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\archive\\data2.csv\" , low_memory=False , encoding='utf-8', delimiter=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fb111bd5-2e11-482f-a51b-01100e82ed39",
   "metadata": {
    "scrolled": true
   },
   "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>DR_NO</th>\n",
       "      <th>Date Rptd</th>\n",
       "      <th>DATE OCC</th>\n",
       "      <th>TIME OCC</th>\n",
       "      <th>AREA</th>\n",
       "      <th>AREA NAME</th>\n",
       "      <th>Rpt Dist No</th>\n",
       "      <th>Part 1-2</th>\n",
       "      <th>Crm Cd</th>\n",
       "      <th>Crm Cd Desc</th>\n",
       "      <th>...</th>\n",
       "      <th>Status</th>\n",
       "      <th>Status Desc</th>\n",
       "      <th>Crm Cd 1</th>\n",
       "      <th>Crm Cd 2</th>\n",
       "      <th>Crm Cd 3</th>\n",
       "      <th>Crm Cd 4</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>Cross Street</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>190326475</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Wilshire</td>\n",
       "      <td>784.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>VEHICLE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>AA</td>\n",
       "      <td>Adult Arrest</td>\n",
       "      <td>510.0</td>\n",
       "      <td>998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900 S  LONGWOOD                     AV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0375</td>\n",
       "      <td>-118.3506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200106753</td>\n",
       "      <td>02/09/2020 12:00:00 AM</td>\n",
       "      <td>02/08/2020 12:00:00 AM</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Central</td>\n",
       "      <td>182.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>330.0</td>\n",
       "      <td>BURGLARY FROM VEHICLE</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>330.0</td>\n",
       "      <td>998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000 S  FLOWER                       ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0444</td>\n",
       "      <td>-118.2628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200320258</td>\n",
       "      <td>11/11/2020 12:00:00 AM</td>\n",
       "      <td>11/04/2020 12:00:00 AM</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>356.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>BIKE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>480.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1400 W  37TH                         ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0210</td>\n",
       "      <td>-118.3002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200907217</td>\n",
       "      <td>05/10/2023 12:00:00 AM</td>\n",
       "      <td>03/10/2020 12:00:00 AM</td>\n",
       "      <td>2037.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Van Nuys</td>\n",
       "      <td>964.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>SHOPLIFTING-GRAND THEFT ($950.01 &amp; OVER)</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>343.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14000    RIVERSIDE                    DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.1576</td>\n",
       "      <td>-118.4387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>220614831</td>\n",
       "      <td>08/18/2022 12:00:00 AM</td>\n",
       "      <td>08/17/2020 12:00:00 AM</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Hollywood</td>\n",
       "      <td>666.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>THEFT OF IDENTITY</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>354.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900    TRANSIENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0944</td>\n",
       "      <td>-118.3277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698667</th>\n",
       "      <td>230715091</td>\n",
       "      <td>09/26/2023 12:00:00 AM</td>\n",
       "      <td>09/18/2023 12:00:0{</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698668</th>\n",
       "      <td>\"error\" : true</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698669</th>\n",
       "      <td>\"message\" : \"Internal error\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698670</th>\n",
       "      <td>\"status\" : 500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698671</th>\n",
       "      <td>}</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>698672 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 DR_NO               Date Rptd  \\\n",
       "0                            190326475  03/01/2020 12:00:00 AM   \n",
       "1                            200106753  02/09/2020 12:00:00 AM   \n",
       "2                            200320258  11/11/2020 12:00:00 AM   \n",
       "3                            200907217  05/10/2023 12:00:00 AM   \n",
       "4                            220614831  08/18/2022 12:00:00 AM   \n",
       "...                                ...                     ...   \n",
       "698667                       230715091  09/26/2023 12:00:00 AM   \n",
       "698668                  \"error\" : true                     NaN   \n",
       "698669    \"message\" : \"Internal error\"                     NaN   \n",
       "698670                  \"status\" : 500                     NaN   \n",
       "698671                               }                     NaN   \n",
       "\n",
       "                      DATE OCC  TIME OCC  AREA  AREA NAME  Rpt Dist No  \\\n",
       "0       03/01/2020 12:00:00 AM    2130.0   7.0   Wilshire        784.0   \n",
       "1       02/08/2020 12:00:00 AM    1800.0   1.0    Central        182.0   \n",
       "2       11/04/2020 12:00:00 AM    1700.0   3.0  Southwest        356.0   \n",
       "3       03/10/2020 12:00:00 AM    2037.0   9.0   Van Nuys        964.0   \n",
       "4       08/17/2020 12:00:00 AM    1200.0   6.0  Hollywood        666.0   \n",
       "...                        ...       ...   ...        ...          ...   \n",
       "698667     09/18/2023 12:00:0{       NaN   NaN        NaN          NaN   \n",
       "698668                     NaN       NaN   NaN        NaN          NaN   \n",
       "698669                     NaN       NaN   NaN        NaN          NaN   \n",
       "698670                     NaN       NaN   NaN        NaN          NaN   \n",
       "698671                     NaN       NaN   NaN        NaN          NaN   \n",
       "\n",
       "        Part 1-2  Crm Cd                               Crm Cd Desc  ...  \\\n",
       "0            1.0   510.0                          VEHICLE - STOLEN  ...   \n",
       "1            1.0   330.0                     BURGLARY FROM VEHICLE  ...   \n",
       "2            1.0   480.0                             BIKE - STOLEN  ...   \n",
       "3            1.0   343.0  SHOPLIFTING-GRAND THEFT ($950.01 & OVER)  ...   \n",
       "4            2.0   354.0                         THEFT OF IDENTITY  ...   \n",
       "...          ...     ...                                       ...  ...   \n",
       "698667       NaN     NaN                                       NaN  ...   \n",
       "698668       NaN     NaN                                       NaN  ...   \n",
       "698669       NaN     NaN                                       NaN  ...   \n",
       "698670       NaN     NaN                                       NaN  ...   \n",
       "698671       NaN     NaN                                       NaN  ...   \n",
       "\n",
       "       Status   Status Desc Crm Cd 1 Crm Cd 2  Crm Cd 3 Crm Cd 4  \\\n",
       "0          AA  Adult Arrest    510.0    998.0       NaN      NaN   \n",
       "1          IC   Invest Cont    330.0    998.0       NaN      NaN   \n",
       "2          IC   Invest Cont    480.0      NaN       NaN      NaN   \n",
       "3          IC   Invest Cont    343.0      NaN       NaN      NaN   \n",
       "4          IC   Invest Cont    354.0      NaN       NaN      NaN   \n",
       "...       ...           ...      ...      ...       ...      ...   \n",
       "698667    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698668    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698669    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698670    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698671    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "\n",
       "                                        LOCATION Cross Street      LAT  \\\n",
       "0        1900 S  LONGWOOD                     AV          NaN  34.0375   \n",
       "1        1000 S  FLOWER                       ST          NaN  34.0444   \n",
       "2        1400 W  37TH                         ST          NaN  34.0210   \n",
       "3       14000    RIVERSIDE                    DR          NaN  34.1576   \n",
       "4                              1900    TRANSIENT          NaN  34.0944   \n",
       "...                                          ...          ...      ...   \n",
       "698667                                       NaN          NaN      NaN   \n",
       "698668                                       NaN          NaN      NaN   \n",
       "698669                                       NaN          NaN      NaN   \n",
       "698670                                       NaN          NaN      NaN   \n",
       "698671                                       NaN          NaN      NaN   \n",
       "\n",
       "             LON  \n",
       "0      -118.3506  \n",
       "1      -118.2628  \n",
       "2      -118.3002  \n",
       "3      -118.4387  \n",
       "4      -118.3277  \n",
       "...          ...  \n",
       "698667       NaN  \n",
       "698668       NaN  \n",
       "698669       NaN  \n",
       "698670       NaN  \n",
       "698671       NaN  \n",
       "\n",
       "[698672 rows x 28 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4eda761b-6a40-4b00-81b6-b49eba3aa79a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698672, 28)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c8884904-be6d-41cf-8a70-214c2ffe70c2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 698672 entries, 0 to 698671\n",
      "Data columns (total 28 columns):\n",
      " #   Column          Non-Null Count   Dtype  \n",
      "---  ------          --------------   -----  \n",
      " 0   DR_NO           698672 non-null  object \n",
      " 1   Date Rptd       698668 non-null  object \n",
      " 2   DATE OCC        698668 non-null  object \n",
      " 3   TIME OCC        698667 non-null  float64\n",
      " 4   AREA            698667 non-null  float64\n",
      " 5   AREA NAME       698667 non-null  object \n",
      " 6   Rpt Dist No     698667 non-null  float64\n",
      " 7   Part 1-2        698667 non-null  float64\n",
      " 8   Crm Cd          698667 non-null  float64\n",
      " 9   Crm Cd Desc     698667 non-null  object \n",
      " 10  Mocodes         602604 non-null  object \n",
      " 11  Vict Age        698667 non-null  float64\n",
      " 12  Vict Sex        607450 non-null  object \n",
      " 13  Vict Descent    607446 non-null  object \n",
      " 14  Premis Cd       698658 non-null  float64\n",
      " 15  Premis Desc     698301 non-null  object \n",
      " 16  Weapon Used Cd  242948 non-null  float64\n",
      " 17  Weapon Desc     242948 non-null  object \n",
      " 18  Status          698667 non-null  object \n",
      " 19  Status Desc     698667 non-null  object \n",
      " 20  Crm Cd 1        698659 non-null  float64\n",
      " 21  Crm Cd 2        52153 non-null   float64\n",
      " 22  Crm Cd 3        1769 non-null    float64\n",
      " 23  Crm Cd 4        49 non-null      float64\n",
      " 24  LOCATION        698667 non-null  object \n",
      " 25  Cross Street    113295 non-null  object \n",
      " 26  LAT             698667 non-null  float64\n",
      " 27  LON             698667 non-null  float64\n",
      "dtypes: float64(14), object(14)\n",
      "memory usage: 149.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c9a94c7f-7acb-4da9-997a-ac7d7d2ac850",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DR_NO                  0\n",
       "Date Rptd              4\n",
       "DATE OCC               4\n",
       "TIME OCC               5\n",
       "AREA                   5\n",
       "AREA NAME              5\n",
       "Rpt Dist No            5\n",
       "Part 1-2               5\n",
       "Crm Cd                 5\n",
       "Crm Cd Desc            5\n",
       "Mocodes            96068\n",
       "Vict Age               5\n",
       "Vict Sex           91222\n",
       "Vict Descent       91226\n",
       "Premis Cd             14\n",
       "Premis Desc          371\n",
       "Weapon Used Cd    455724\n",
       "Weapon Desc       455724\n",
       "Status                 5\n",
       "Status Desc            5\n",
       "Crm Cd 1              13\n",
       "Crm Cd 2          646519\n",
       "Crm Cd 3          696903\n",
       "Crm Cd 4          698623\n",
       "LOCATION               5\n",
       "Cross Street      585377\n",
       "LAT                    5\n",
       "LON                    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c55d502a-d028-4261-bee2-dd234b4a75c0",
   "metadata": {},
   "source": [
    "### The Total number of  null cells "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bb5fd576-14af-4e33-a77b-8f149e3895ce",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3817857"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbc7d03c-588b-4a86-834b-bc2ad97fdebe",
   "metadata": {},
   "source": [
    "### Drop Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "96fe39f7-0cc0-4945-ac99-e6eed5a0417d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.dropna(axis = 0 , how = 'all'  )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7abeb45b-b489-4cac-9e35-7368d1bdd05e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DR_NO                  0\n",
       "Date Rptd              4\n",
       "DATE OCC               4\n",
       "TIME OCC               5\n",
       "AREA                   5\n",
       "AREA NAME              5\n",
       "Rpt Dist No            5\n",
       "Part 1-2               5\n",
       "Crm Cd                 5\n",
       "Crm Cd Desc            5\n",
       "Mocodes            96068\n",
       "Vict Age               5\n",
       "Vict Sex           91222\n",
       "Vict Descent       91226\n",
       "Premis Cd             14\n",
       "Premis Desc          371\n",
       "Weapon Used Cd    455724\n",
       "Weapon Desc       455724\n",
       "Status                 5\n",
       "Status Desc            5\n",
       "Crm Cd 1              13\n",
       "Crm Cd 2          646519\n",
       "Crm Cd 3          696903\n",
       "Crm Cd 4          698623\n",
       "LOCATION               5\n",
       "Cross Street      585377\n",
       "LAT                    5\n",
       "LON                    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "363f5d30-50f4-4f32-a0be-af8f02d9bd9d",
   "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>DR_NO</th>\n",
       "      <th>Date Rptd</th>\n",
       "      <th>DATE OCC</th>\n",
       "      <th>TIME OCC</th>\n",
       "      <th>AREA</th>\n",
       "      <th>AREA NAME</th>\n",
       "      <th>Rpt Dist No</th>\n",
       "      <th>Part 1-2</th>\n",
       "      <th>Crm Cd</th>\n",
       "      <th>Crm Cd Desc</th>\n",
       "      <th>...</th>\n",
       "      <th>Status</th>\n",
       "      <th>Status Desc</th>\n",
       "      <th>Crm Cd 1</th>\n",
       "      <th>Crm Cd 2</th>\n",
       "      <th>Crm Cd 3</th>\n",
       "      <th>Crm Cd 4</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>Cross Street</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>190326475</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Wilshire</td>\n",
       "      <td>784.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>VEHICLE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>AA</td>\n",
       "      <td>Adult Arrest</td>\n",
       "      <td>510.0</td>\n",
       "      <td>998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900 S  LONGWOOD                     AV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0375</td>\n",
       "      <td>-118.3506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200106753</td>\n",
       "      <td>02/09/2020 12:00:00 AM</td>\n",
       "      <td>02/08/2020 12:00:00 AM</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Central</td>\n",
       "      <td>182.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>330.0</td>\n",
       "      <td>BURGLARY FROM VEHICLE</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>330.0</td>\n",
       "      <td>998.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000 S  FLOWER                       ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0444</td>\n",
       "      <td>-118.2628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200320258</td>\n",
       "      <td>11/11/2020 12:00:00 AM</td>\n",
       "      <td>11/04/2020 12:00:00 AM</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>356.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>BIKE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>480.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1400 W  37TH                         ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0210</td>\n",
       "      <td>-118.3002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200907217</td>\n",
       "      <td>05/10/2023 12:00:00 AM</td>\n",
       "      <td>03/10/2020 12:00:00 AM</td>\n",
       "      <td>2037.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Van Nuys</td>\n",
       "      <td>964.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>SHOPLIFTING-GRAND THEFT ($950.01 &amp; OVER)</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>343.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14000    RIVERSIDE                    DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.1576</td>\n",
       "      <td>-118.4387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>220614831</td>\n",
       "      <td>08/18/2022 12:00:00 AM</td>\n",
       "      <td>08/17/2020 12:00:00 AM</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Hollywood</td>\n",
       "      <td>666.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>THEFT OF IDENTITY</td>\n",
       "      <td>...</td>\n",
       "      <td>IC</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>354.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900    TRANSIENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0944</td>\n",
       "      <td>-118.3277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698667</th>\n",
       "      <td>230715091</td>\n",
       "      <td>09/26/2023 12:00:00 AM</td>\n",
       "      <td>09/18/2023 12:00:0{</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698668</th>\n",
       "      <td>\"error\" : true</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698669</th>\n",
       "      <td>\"message\" : \"Internal error\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698670</th>\n",
       "      <td>\"status\" : 500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698671</th>\n",
       "      <td>}</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>698672 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 DR_NO               Date Rptd  \\\n",
       "0                            190326475  03/01/2020 12:00:00 AM   \n",
       "1                            200106753  02/09/2020 12:00:00 AM   \n",
       "2                            200320258  11/11/2020 12:00:00 AM   \n",
       "3                            200907217  05/10/2023 12:00:00 AM   \n",
       "4                            220614831  08/18/2022 12:00:00 AM   \n",
       "...                                ...                     ...   \n",
       "698667                       230715091  09/26/2023 12:00:00 AM   \n",
       "698668                  \"error\" : true                     NaN   \n",
       "698669    \"message\" : \"Internal error\"                     NaN   \n",
       "698670                  \"status\" : 500                     NaN   \n",
       "698671                               }                     NaN   \n",
       "\n",
       "                      DATE OCC  TIME OCC  AREA  AREA NAME  Rpt Dist No  \\\n",
       "0       03/01/2020 12:00:00 AM    2130.0   7.0   Wilshire        784.0   \n",
       "1       02/08/2020 12:00:00 AM    1800.0   1.0    Central        182.0   \n",
       "2       11/04/2020 12:00:00 AM    1700.0   3.0  Southwest        356.0   \n",
       "3       03/10/2020 12:00:00 AM    2037.0   9.0   Van Nuys        964.0   \n",
       "4       08/17/2020 12:00:00 AM    1200.0   6.0  Hollywood        666.0   \n",
       "...                        ...       ...   ...        ...          ...   \n",
       "698667     09/18/2023 12:00:0{       NaN   NaN        NaN          NaN   \n",
       "698668                     NaN       NaN   NaN        NaN          NaN   \n",
       "698669                     NaN       NaN   NaN        NaN          NaN   \n",
       "698670                     NaN       NaN   NaN        NaN          NaN   \n",
       "698671                     NaN       NaN   NaN        NaN          NaN   \n",
       "\n",
       "        Part 1-2  Crm Cd                               Crm Cd Desc  ...  \\\n",
       "0            1.0   510.0                          VEHICLE - STOLEN  ...   \n",
       "1            1.0   330.0                     BURGLARY FROM VEHICLE  ...   \n",
       "2            1.0   480.0                             BIKE - STOLEN  ...   \n",
       "3            1.0   343.0  SHOPLIFTING-GRAND THEFT ($950.01 & OVER)  ...   \n",
       "4            2.0   354.0                         THEFT OF IDENTITY  ...   \n",
       "...          ...     ...                                       ...  ...   \n",
       "698667       NaN     NaN                                       NaN  ...   \n",
       "698668       NaN     NaN                                       NaN  ...   \n",
       "698669       NaN     NaN                                       NaN  ...   \n",
       "698670       NaN     NaN                                       NaN  ...   \n",
       "698671       NaN     NaN                                       NaN  ...   \n",
       "\n",
       "       Status   Status Desc Crm Cd 1 Crm Cd 2  Crm Cd 3 Crm Cd 4  \\\n",
       "0          AA  Adult Arrest    510.0    998.0       NaN      NaN   \n",
       "1          IC   Invest Cont    330.0    998.0       NaN      NaN   \n",
       "2          IC   Invest Cont    480.0      NaN       NaN      NaN   \n",
       "3          IC   Invest Cont    343.0      NaN       NaN      NaN   \n",
       "4          IC   Invest Cont    354.0      NaN       NaN      NaN   \n",
       "...       ...           ...      ...      ...       ...      ...   \n",
       "698667    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698668    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698669    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698670    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "698671    NaN           NaN      NaN      NaN       NaN      NaN   \n",
       "\n",
       "                                        LOCATION Cross Street      LAT  \\\n",
       "0        1900 S  LONGWOOD                     AV          NaN  34.0375   \n",
       "1        1000 S  FLOWER                       ST          NaN  34.0444   \n",
       "2        1400 W  37TH                         ST          NaN  34.0210   \n",
       "3       14000    RIVERSIDE                    DR          NaN  34.1576   \n",
       "4                              1900    TRANSIENT          NaN  34.0944   \n",
       "...                                          ...          ...      ...   \n",
       "698667                                       NaN          NaN      NaN   \n",
       "698668                                       NaN          NaN      NaN   \n",
       "698669                                       NaN          NaN      NaN   \n",
       "698670                                       NaN          NaN      NaN   \n",
       "698671                                       NaN          NaN      NaN   \n",
       "\n",
       "             LON  \n",
       "0      -118.3506  \n",
       "1      -118.2628  \n",
       "2      -118.3002  \n",
       "3      -118.4387  \n",
       "4      -118.3277  \n",
       "...          ...  \n",
       "698667       NaN  \n",
       "698668       NaN  \n",
       "698669       NaN  \n",
       "698670       NaN  \n",
       "698671       NaN  \n",
       "\n",
       "[698672 rows x 28 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "98dffd14-ee60-4a6c-b277-1c37a13a8052",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.drop([\"Cross Street\" , \"Crm Cd 4\" , \"Crm Cd 3\" , \"Crm Cd 2\" , \"Crm Cd 1\"] , axis = 1 , inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d74b2e12-04b0-4e7c-aa77-fd31a1231d14",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.drop([ \"LOCATION\" ,\"Status\"] , axis =1 , inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "52b5d759-b333-48fe-bbe5-3bb2aad687be",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df2.drop([ \"Weapon Used Cd\" ] , axis =1 , inplace = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6ffe7a32-f8eb-4ef8-b51b-08d49796fad0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698672, 21)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b55ecd02-59e6-4254-8729-e82c83daa9e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  \"error\" : true\n"
     ]
    }
   ],
   "source": [
    "print(df2[\"DR_NO\"][698668])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fc646ea9-29fe-4187-b688-b0266ac5ff16",
   "metadata": {},
   "outputs": [],
   "source": [
    "import janitor "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "db9ea66c-5b21-4d6d-8be9-026ef6c1201b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\youss\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\pandas_flavor\\register.py:157: FutureWarning: This function will be deprecated in a 1.x release. Please use `pd.DataFrame.rename` instead.\n",
      "  return method(self._obj, *args, **kwargs)\n"
     ]
    }
   ],
   "source": [
    "df2 = df2.rename_column(\"DR_NO\" ,\"Division of records\" ).rename_column(\"Date Rptd\" , \"date reported\"). rename_column(\"DATE OCC\" , \"date occured\").rename_column(\"TIME OCC\" , \"Occurence time\").rename_column(\"Crm Cd\" , \"Crime Code\").rename_column(\"Crm Cd Desc\" , \"Crime Code Identifier\").rename_column(\"Status Desc\" , \"status\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "68e8992d-d434-4dcd-996d-6c9c8a5ffaa4",
   "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>Division of records</th>\n",
       "      <th>date reported</th>\n",
       "      <th>date occured</th>\n",
       "      <th>Occurence time</th>\n",
       "      <th>AREA</th>\n",
       "      <th>AREA NAME</th>\n",
       "      <th>Rpt Dist No</th>\n",
       "      <th>Part 1-2</th>\n",
       "      <th>Crime Code</th>\n",
       "      <th>Crime Code Identifier</th>\n",
       "      <th>...</th>\n",
       "      <th>Vict Age</th>\n",
       "      <th>Vict Sex</th>\n",
       "      <th>Vict Descent</th>\n",
       "      <th>Premis Cd</th>\n",
       "      <th>Premis Desc</th>\n",
       "      <th>Weapon Used Cd</th>\n",
       "      <th>Weapon Desc</th>\n",
       "      <th>status</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>190326475</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Wilshire</td>\n",
       "      <td>784.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>VEHICLE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M</td>\n",
       "      <td>O</td>\n",
       "      <td>101.0</td>\n",
       "      <td>STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adult Arrest</td>\n",
       "      <td>34.0375</td>\n",
       "      <td>-118.3506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200106753</td>\n",
       "      <td>02/09/2020 12:00:00 AM</td>\n",
       "      <td>02/08/2020 12:00:00 AM</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Central</td>\n",
       "      <td>182.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>330.0</td>\n",
       "      <td>BURGLARY FROM VEHICLE</td>\n",
       "      <td>...</td>\n",
       "      <td>47.0</td>\n",
       "      <td>M</td>\n",
       "      <td>O</td>\n",
       "      <td>128.0</td>\n",
       "      <td>BUS STOP/LAYOVER (ALSO QUERY 124)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.0444</td>\n",
       "      <td>-118.2628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200320258</td>\n",
       "      <td>11/11/2020 12:00:00 AM</td>\n",
       "      <td>11/04/2020 12:00:00 AM</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>356.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>BIKE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>X</td>\n",
       "      <td>X</td>\n",
       "      <td>502.0</td>\n",
       "      <td>MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.0210</td>\n",
       "      <td>-118.3002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200907217</td>\n",
       "      <td>05/10/2023 12:00:00 AM</td>\n",
       "      <td>03/10/2020 12:00:00 AM</td>\n",
       "      <td>2037.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Van Nuys</td>\n",
       "      <td>964.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>SHOPLIFTING-GRAND THEFT ($950.01 &amp; OVER)</td>\n",
       "      <td>...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>M</td>\n",
       "      <td>O</td>\n",
       "      <td>405.0</td>\n",
       "      <td>CLOTHING STORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.1576</td>\n",
       "      <td>-118.4387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>220614831</td>\n",
       "      <td>08/18/2022 12:00:00 AM</td>\n",
       "      <td>08/17/2020 12:00:00 AM</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Hollywood</td>\n",
       "      <td>666.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>THEFT OF IDENTITY</td>\n",
       "      <td>...</td>\n",
       "      <td>28.0</td>\n",
       "      <td>M</td>\n",
       "      <td>H</td>\n",
       "      <td>102.0</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.0944</td>\n",
       "      <td>-118.3277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Division of records           date reported            date occured  \\\n",
       "0           190326475  03/01/2020 12:00:00 AM  03/01/2020 12:00:00 AM   \n",
       "1           200106753  02/09/2020 12:00:00 AM  02/08/2020 12:00:00 AM   \n",
       "2           200320258  11/11/2020 12:00:00 AM  11/04/2020 12:00:00 AM   \n",
       "3           200907217  05/10/2023 12:00:00 AM  03/10/2020 12:00:00 AM   \n",
       "4           220614831  08/18/2022 12:00:00 AM  08/17/2020 12:00:00 AM   \n",
       "\n",
       "   Occurence time  AREA  AREA NAME  Rpt Dist No  Part 1-2  Crime Code  \\\n",
       "0          2130.0   7.0   Wilshire        784.0       1.0       510.0   \n",
       "1          1800.0   1.0    Central        182.0       1.0       330.0   \n",
       "2          1700.0   3.0  Southwest        356.0       1.0       480.0   \n",
       "3          2037.0   9.0   Van Nuys        964.0       1.0       343.0   \n",
       "4          1200.0   6.0  Hollywood        666.0       2.0       354.0   \n",
       "\n",
       "                      Crime Code Identifier  ... Vict Age  Vict Sex  \\\n",
       "0                          VEHICLE - STOLEN  ...      0.0         M   \n",
       "1                     BURGLARY FROM VEHICLE  ...     47.0         M   \n",
       "2                             BIKE - STOLEN  ...     19.0         X   \n",
       "3  SHOPLIFTING-GRAND THEFT ($950.01 & OVER)  ...     19.0         M   \n",
       "4                         THEFT OF IDENTITY  ...     28.0         M   \n",
       "\n",
       "  Vict Descent Premis Cd                                   Premis Desc  \\\n",
       "0            O     101.0                                        STREET   \n",
       "1            O     128.0             BUS STOP/LAYOVER (ALSO QUERY 124)   \n",
       "2            X     502.0  MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)   \n",
       "3            O     405.0                                CLOTHING STORE   \n",
       "4            H     102.0                                      SIDEWALK   \n",
       "\n",
       "  Weapon Used Cd  Weapon Desc        status      LAT       LON  \n",
       "0            NaN          NaN  Adult Arrest  34.0375 -118.3506  \n",
       "1            NaN          NaN   Invest Cont  34.0444 -118.2628  \n",
       "2            NaN          NaN   Invest Cont  34.0210 -118.3002  \n",
       "3            NaN          NaN   Invest Cont  34.1576 -118.4387  \n",
       "4            NaN          NaN   Invest Cont  34.0944 -118.3277  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d979c985-a5ee-472b-a360-5c6e3860fd29",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2  = df2.clean_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "db361b84-4893-45d0-a7d0-3cbe968190a1",
   "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>division_of_records</th>\n",
       "      <th>date_reported</th>\n",
       "      <th>date_occured</th>\n",
       "      <th>occurence_time</th>\n",
       "      <th>area</th>\n",
       "      <th>area_name</th>\n",
       "      <th>rpt_dist_no</th>\n",
       "      <th>part_1_2</th>\n",
       "      <th>crime_code</th>\n",
       "      <th>crime_code_identifier</th>\n",
       "      <th>...</th>\n",
       "      <th>vict_age</th>\n",
       "      <th>vict_sex</th>\n",
       "      <th>vict_descent</th>\n",
       "      <th>premis_cd</th>\n",
       "      <th>premis_desc</th>\n",
       "      <th>weapon_used_cd</th>\n",
       "      <th>weapon_desc</th>\n",
       "      <th>status</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>190326475</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>03/01/2020 12:00:00 AM</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Wilshire</td>\n",
       "      <td>784.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>VEHICLE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>M</td>\n",
       "      <td>O</td>\n",
       "      <td>101.0</td>\n",
       "      <td>STREET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adult Arrest</td>\n",
       "      <td>34.0375</td>\n",
       "      <td>-118.3506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200106753</td>\n",
       "      <td>02/09/2020 12:00:00 AM</td>\n",
       "      <td>02/08/2020 12:00:00 AM</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Central</td>\n",
       "      <td>182.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>330.0</td>\n",
       "      <td>BURGLARY FROM VEHICLE</td>\n",
       "      <td>...</td>\n",
       "      <td>47.0</td>\n",
       "      <td>M</td>\n",
       "      <td>O</td>\n",
       "      <td>128.0</td>\n",
       "      <td>BUS STOP/LAYOVER (ALSO QUERY 124)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.0444</td>\n",
       "      <td>-118.2628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200320258</td>\n",
       "      <td>11/11/2020 12:00:00 AM</td>\n",
       "      <td>11/04/2020 12:00:00 AM</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>356.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>BIKE - STOLEN</td>\n",
       "      <td>...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>X</td>\n",
       "      <td>X</td>\n",
       "      <td>502.0</td>\n",
       "      <td>MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.0210</td>\n",
       "      <td>-118.3002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200907217</td>\n",
       "      <td>05/10/2023 12:00:00 AM</td>\n",
       "      <td>03/10/2020 12:00:00 AM</td>\n",
       "      <td>2037.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Van Nuys</td>\n",
       "      <td>964.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>SHOPLIFTING-GRAND THEFT ($950.01 &amp; OVER)</td>\n",
       "      <td>...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>M</td>\n",
       "      <td>O</td>\n",
       "      <td>405.0</td>\n",
       "      <td>CLOTHING STORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.1576</td>\n",
       "      <td>-118.4387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>220614831</td>\n",
       "      <td>08/18/2022 12:00:00 AM</td>\n",
       "      <td>08/17/2020 12:00:00 AM</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Hollywood</td>\n",
       "      <td>666.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>THEFT OF IDENTITY</td>\n",
       "      <td>...</td>\n",
       "      <td>28.0</td>\n",
       "      <td>M</td>\n",
       "      <td>H</td>\n",
       "      <td>102.0</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Invest Cont</td>\n",
       "      <td>34.0944</td>\n",
       "      <td>-118.3277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  division_of_records           date_reported            date_occured  \\\n",
       "0           190326475  03/01/2020 12:00:00 AM  03/01/2020 12:00:00 AM   \n",
       "1           200106753  02/09/2020 12:00:00 AM  02/08/2020 12:00:00 AM   \n",
       "2           200320258  11/11/2020 12:00:00 AM  11/04/2020 12:00:00 AM   \n",
       "3           200907217  05/10/2023 12:00:00 AM  03/10/2020 12:00:00 AM   \n",
       "4           220614831  08/18/2022 12:00:00 AM  08/17/2020 12:00:00 AM   \n",
       "\n",
       "   occurence_time  area  area_name  rpt_dist_no  part_1_2  crime_code  \\\n",
       "0          2130.0   7.0   Wilshire        784.0       1.0       510.0   \n",
       "1          1800.0   1.0    Central        182.0       1.0       330.0   \n",
       "2          1700.0   3.0  Southwest        356.0       1.0       480.0   \n",
       "3          2037.0   9.0   Van Nuys        964.0       1.0       343.0   \n",
       "4          1200.0   6.0  Hollywood        666.0       2.0       354.0   \n",
       "\n",
       "                      crime_code_identifier  ... vict_age  vict_sex  \\\n",
       "0                          VEHICLE - STOLEN  ...      0.0         M   \n",
       "1                     BURGLARY FROM VEHICLE  ...     47.0         M   \n",
       "2                             BIKE - STOLEN  ...     19.0         X   \n",
       "3  SHOPLIFTING-GRAND THEFT ($950.01 & OVER)  ...     19.0         M   \n",
       "4                         THEFT OF IDENTITY  ...     28.0         M   \n",
       "\n",
       "  vict_descent premis_cd                                   premis_desc  \\\n",
       "0            O     101.0                                        STREET   \n",
       "1            O     128.0             BUS STOP/LAYOVER (ALSO QUERY 124)   \n",
       "2            X     502.0  MULTI-UNIT DWELLING (APARTMENT, DUPLEX, ETC)   \n",
       "3            O     405.0                                CLOTHING STORE   \n",
       "4            H     102.0                                      SIDEWALK   \n",
       "\n",
       "  weapon_used_cd  weapon_desc        status      lat       lon  \n",
       "0            NaN          NaN  Adult Arrest  34.0375 -118.3506  \n",
       "1            NaN          NaN   Invest Cont  34.0444 -118.2628  \n",
       "2            NaN          NaN   Invest Cont  34.0210 -118.3002  \n",
       "3            NaN          NaN   Invest Cont  34.1576 -118.4387  \n",
       "4            NaN          NaN   Invest Cont  34.0944 -118.3277  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e1297914-0aa6-4f38-8374-dbaaafeef28c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Condition: Keep rows where the specific column is not NaN and other columns are all NaN\n",
    "condition = df2[\"division_of_records\"].notna() & df2.drop(columns=[\"division_of_records\"]).isna().all(axis=1)\n",
    "\n",
    "# Apply the condition to filter the DataFrame\n",
    "df3 = df2[condition]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7e3800fe-cd69-494d-9dfc-a3af42063b2b",
   "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>division_of_records</th>\n",
       "      <th>date_reported</th>\n",
       "      <th>date_occured</th>\n",
       "      <th>occurence_time</th>\n",
       "      <th>area</th>\n",
       "      <th>area_name</th>\n",
       "      <th>rpt_dist_no</th>\n",
       "      <th>part_1_2</th>\n",
       "      <th>crime_code</th>\n",
       "      <th>crime_code_identifier</th>\n",
       "      <th>...</th>\n",
       "      <th>vict_age</th>\n",
       "      <th>vict_sex</th>\n",
       "      <th>vict_descent</th>\n",
       "      <th>premis_cd</th>\n",
       "      <th>premis_desc</th>\n",
       "      <th>weapon_used_cd</th>\n",
       "      <th>weapon_desc</th>\n",
       "      <th>status</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>698668</th>\n",
       "      <td>\"error\" : true</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698669</th>\n",
       "      <td>\"message\" : \"Internal error\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698670</th>\n",
       "      <td>\"status\" : 500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698671</th>\n",
       "      <td>}</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   division_of_records date_reported date_occured  \\\n",
       "698668                  \"error\" : true           NaN          NaN   \n",
       "698669    \"message\" : \"Internal error\"           NaN          NaN   \n",
       "698670                  \"status\" : 500           NaN          NaN   \n",
       "698671                               }           NaN          NaN   \n",
       "\n",
       "        occurence_time  area area_name  rpt_dist_no  part_1_2  crime_code  \\\n",
       "698668             NaN   NaN       NaN          NaN       NaN         NaN   \n",
       "698669             NaN   NaN       NaN          NaN       NaN         NaN   \n",
       "698670             NaN   NaN       NaN          NaN       NaN         NaN   \n",
       "698671             NaN   NaN       NaN          NaN       NaN         NaN   \n",
       "\n",
       "       crime_code_identifier  ... vict_age  vict_sex vict_descent premis_cd  \\\n",
       "698668                   NaN  ...      NaN       NaN          NaN       NaN   \n",
       "698669                   NaN  ...      NaN       NaN          NaN       NaN   \n",
       "698670                   NaN  ...      NaN       NaN          NaN       NaN   \n",
       "698671                   NaN  ...      NaN       NaN          NaN       NaN   \n",
       "\n",
       "        premis_desc weapon_used_cd  weapon_desc status lat  lon  \n",
       "698668          NaN            NaN          NaN    NaN NaN  NaN  \n",
       "698669          NaN            NaN          NaN    NaN NaN  NaN  \n",
       "698670          NaN            NaN          NaN    NaN NaN  NaN  \n",
       "698671          NaN            NaN          NaN    NaN NaN  NaN  \n",
       "\n",
       "[4 rows x 21 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e85f2874-0def-4ec7-8485-97f573b93dd7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\youss\\AppData\\Local\\Temp\\ipykernel_7388\\443866691.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df2.drop([698668 , 698669 , 698670  , 698671 , 698667 ] , inplace = True)\n"
     ]
    }
   ],
   "source": [
    "df2.drop([698668 , 698669 , 698670  , 698671 , 698667 ] , inplace = True)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c2a5399e-52b3-480b-b8ed-8c89f626cb9f",
   "metadata": {},
   "source": [
    "convert_dict = {'division_of_record': int}\n",
    "df2 = df2.astype(convert_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "258c1e6c-2c7c-497e-949b-c70964e01dad",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0d8d3724-d231-4a7c-9a7f-cf136b9b3e1a",
   "metadata": {},
   "source": [
    "### Drop Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d7d01751-8d7c-449c-bd60-7f6d3cb45d59",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\youss\\AppData\\Local\\Temp\\ipykernel_7388\\4093079307.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df2.drop_duplicates(keep='first' , inplace = True , ignore_index = True)\n"
     ]
    }
   ],
   "source": [
    "# Drop all duplicates, keep none\n",
    "df2.drop_duplicates(keep='first' , inplace = True , ignore_index = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "b7202dd8-e427-4b2b-b140-5f4bc67054a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698667, 21)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccaa4448-73a1-4649-86ae-05aace1d412c",
   "metadata": {},
   "source": [
    "### Using Sketch for optimized EDA"
   ]
  },
  {
   "cell_type": "raw",
   "id": "291341a5-b31a-41e8-971d-da9f5c7fd528",
   "metadata": {},
   "source": [
    "df3 = df2.drop(['division_of_records'] , axis =1) # Thw drop for this additional column because the max. num of columns accepted by sketch is 19 not 20 so I found that the case number is not necessary alot for analysing it with sketch  "
   ]
  },
  {
   "cell_type": "raw",
   "id": "dd1f2396-2775-4ec0-ba72-205a6c1a8d25",
   "metadata": {},
   "source": [
    "df3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "461bc8de-5ef2-46f8-bdba-c128c394f824",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sketch"
   ]
  },
  {
   "cell_type": "raw",
   "id": "62423b9b-8b12-439d-8a3f-9e0b514eb3a0",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "df3.sketch.ask(\"provide a detailed list of potential data insights and visualizations that can be included in an Exploratory Data Analysis (EDA). The response should include any patterns or trends to look for also add all realtions of data to each other.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "015e3cf7-f2e4-46ac-822c-eaa34db6ecb4",
   "metadata": {},
   "source": [
    "### Seketch Responses"
   ]
  },
  {
   "cell_type": "raw",
   "id": "af1bf12d-49f9-49f8-b3c0-2a6ab2cc42c1",
   "metadata": {},
   "source": [
    "1. Time Series Analysis: The data contains information about the date reported and date occurred, which can be used to analyze trends over time. This can include looking at the frequency of reported crimes, seasonal patterns, and changes in crime rates over the years. 2. Geographic Analysis: The data includes information about the area and area name where the crime occurred. This can be used to create visualizations such as heat maps or choropleth maps to show the distribution of crimes across different areas. It can also be used to identify high crime areas and potential patterns or correlations with other variables. 3. Crime Code Analysis: The data contains information about the type of crime (crime code) and its corresponding description. This can be used to analyze the frequency of different types of crimes and their distribution across different areas or time periods. 4. Age, Sex, and Descent Analysis: The data includes information about the age, sex, and descent of the victim. This can be used to analyze any potential patterns or correlations between these variables and the type of crime, location, or time period. 5. Premise Analysis: The data contains information about the premise code and description where the crime occurred. This can be used to analyze the most common locations for different types of crimes and any potential relationships between premises and other variables. 6. Weapon Description Analysis: The data includes information about the weapon used in the crime (if applicable). This can be used to analyze the frequency of different weapons used in crimes and any potential relationships with other variables such as type of crime or location. 7. Modus Operandi (MOCODES) Analysis: The data contains information about the modus operandi (method of operation) for each crime. This can be used to identify common patterns or techniques used by criminals and any potential relationships with other variables. 8. Status Analysis: The data includes information about the status of each reported crime. This can be used to analyze the outcomes of reported crimes and any potential relationships with other variables. 9. Correlation Analysis: The data contains multiple numerical variables, which can be used to perform correlation analysis and identify any potential relationships between variables. 10. Outlier Detection: The data can be analyzed for outliers, which are data points that deviate significantly from the rest of the data. This can help identify any unusual or extreme cases that may require further investigation. 11. Missing Data Analysis: The data may contain missing values, which can be analyzed to determine if there are any\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7939816a-65df-48a6-887a-e6c5112482f3",
   "metadata": {},
   "source": [
    "1. Time Series Analysis: The data contains information about the date reported and date occurred, which can be used to analyze trends over time. This can include looking at the frequency of reported crimes, seasonal patterns, and changes in crime rates over the years. 2. Geographic Analysis: The data includes information about the area and area name where the crime occurred. This can be used to create visualizations such as heat maps or choropleth maps to identify high crime areas and patterns in different regions. 3. Crime Code Analysis: The data contains information about the type of crime (crime code) and its corresponding description. This can be used to identify the most common types of crimes and their frequency, as well as any relationships between different types of crimes. 4. Age, Sex, and Descent Analysis: The data includes information about the age, sex, and descent of the victim. This can be used to analyze any patterns or trends in victim demographics and their relationship to different types of crimes. 5. Premise Analysis: The data contains information about the location (premise) where the crime occurred. This can be used to identify common locations for different types of crimes and any relationships between location and crime type. 6. Weapon Description Analysis: The data includes information about the weapon used (if any) in the crime. This can be used to analyze any relationships between weapon type and crime type, as well as identifying common weapons used in different types of crimes. 7. Status Analysis: The data contains information about the status of the crime (e.g. adult arrest, investigation ongoing). This can be used to analyze the outcomes of reported crimes and any relationships between status and other variables such as crime type or victim demographics. 8. MoCodes Analysis: The data includes information about multiple codes (mocodes) associated with each crime. This can be used to identify any relationships between different codes and their corresponding crime types. 9. Correlation Analysis: The data can be used to identify any correlations between different variables, such as the relationship between crime type and location, or victim demographics and crime type. 10. Data Visualization: The data can be visualized in various ways, such as bar charts, pie charts, scatter plots, and histograms, to identify patterns and trends in the data. This can also help in identifying outliers and any relationships between different variables. 11. Machine Learning: The data can be used to train machine learning models to predict crime rates or identify patterns in crime data.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "059e05f8-7340-4abf-9e44-2efdee35c9d9",
   "metadata": {},
   "source": [
    "# Exporting sub-csv files for instant display on my Dash Board"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "500dfdbd-657a-42bc-a602-eb909a6e7f38",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "03/01/2020 12:00:00 AM\n",
      "02/08/2020 12:00:00 AM\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "date_reported = df2[\"date_reported\"].to_list()\n",
    "print(date_reported[0])\n",
    "date_occured = df2[\"date_occured\"].to_list()\n",
    "print(date_occured[1])\n",
    "\n",
    "cases_numbers = []\n",
    "differences = []\n",
    "count = 0\n",
    "for reported, occured in zip(date_reported, date_occured):\n",
    "            if reported != \"nan\" and occured != \"nan\":\n",
    "\n",
    "                try:\n",
    "                    date_time_obj_r = datetime.strptime(reported, \"%m/%d/%Y %I:%M:%S %p\")\n",
    "\n",
    "                    date_time_obj_o = datetime.strptime(occured, \"%m/%d/%Y %I:%M:%S %p\")\n",
    "\n",
    "                    year_diff = abs(date_time_obj_r.year - date_time_obj_o.year)\n",
    "\n",
    "                    month_diff = abs(date_time_obj_r.month - date_time_obj_o.month)\n",
    "\n",
    "                    day_diff = abs(date_time_obj_r.day - date_time_obj_o.day)\n",
    "\n",
    "                    total_diff_in_days = day_diff + (month_diff * 30) + (year_diff * 365)\n",
    "\n",
    "                    differences.append(total_diff_in_days)\n",
    "                    \n",
    "                    count+=1\n",
    "                    \n",
    "                except:\n",
    "                    count+=1\n",
    "                    print(f\"bad format {count}\")\n",
    "\n",
    "\n",
    "for index, row in df2.iterrows():\n",
    "            case_num = row[\"division_of_records\"]\n",
    "            case_num = str(case_num)\n",
    "            case_num = case_num[4:9]\n",
    "            cases_numbers.append(case_num)\n",
    "\n",
    "diff_date_bet_report_and_occurence_in_days_df = pd.DataFrame({'case_nuber': cases_numbers, 'differences': differences})\n",
    "diff_date_bet_report_and_occurence_in_days_df.to_csv(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\Los-Angeles-Crimes-Analysis\\report_occurence.csv\" , index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e1213bae-f978-46ed-acd7-2aa07b95d947",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['sex', 'age', 'victim_race', 'crime_against_the_victim'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "vict_ages = []\n",
    "vict_sex_list = []\n",
    "vict_descent_list = []\n",
    "crime_code_identifier_list = []\n",
    "for index , row in df2.iterrows():\n",
    "    if str(row[\"vict_age\"]).lower() != \"nan\"  and str(row[\"vict_sex\"]).lower() != \"nan\" and str(row[\"vict_descent\"]).lower() != \"nan\" and  str(row[\"crime_code_identifier\"]).lower() != \"nan\" :\n",
    "        if float(row[\"vict_age\"]) > 0 or  float(row[\"vict_age\"]) < 0:\n",
    "            vict_ages.append(abs(row[\"vict_age\"]))\n",
    "            vict_sex_list.append(row[\"vict_sex\"])\n",
    "            vict_descent_list.append(row[\"vict_descent\"])\n",
    "            crime_code_identifier_list.append(row[\"crime_code_identifier\"])\n",
    "\n",
    "Age_Descent_Sex_crime_code = pd.DataFrame({'sex': vict_sex_list, 'age': vict_ages , 'victim Race': vict_descent_list , 'Crime against the victim' : crime_code_identifier_list})\n",
    "\n",
    "Age_Descent_Sex_crime_code = Age_Descent_Sex_crime_code.clean_names()\n",
    "\n",
    "print(Age_Descent_Sex_crime_code.columns)\n",
    "\n",
    "types_convert_dict = {'sex': str, 'age': float , 'victim_race': str , 'crime_against_the_victim' : str}\n",
    "\n",
    "Age_Descent_Sex_crime_code = Age_Descent_Sex_crime_code.astype(types_convert_dict)\n",
    "\n",
    "Age_Descent_Sex_crime_code.to_csv(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\Los-Angeles-Crimes-Analysis\\vict_age_sex_descent_crime.csv\" , index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "84d13b5e-264f-4103-b5d9-41309c4596ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(698667, 2)\n"
     ]
    }
   ],
   "source": [
    "crimes_area_codes_df = df2[[\"area\" , \"area_name\"]]\n",
    "crimes_area_codes_df = crimes_area_codes_df.dropna(axis = 0 , how = 'any'  )\n",
    "print(crimes_area_codes_df.shape)\n",
    "crimes_area_codes_df.to_csv(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\Los-Angeles-Crimes-Analysis\\crimes_area_codes_df.csv\" , index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "52c716b7-134a-4713-aa48-7caac5e80c95",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(698667, 3)\n"
     ]
    }
   ],
   "source": [
    "crimes_locations_df = df2[[\"crime_code_identifier\" , \"lat\" , \"lon\"]]\n",
    "crimes_locations_df = crimes_locations_df.dropna(axis = 0 , how = 'any'  )\n",
    "types_convert_dict = {'lon': float  , 'lat': float , 'crime_code_identifier' : str}\n",
    "crimes_locations_df = crimes_locations_df.astype(types_convert_dict)\n",
    "print(crimes_locations_df.shape)\n",
    "crimes_locations_df.to_csv(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\Los-Angeles-Crimes-Analysis\\crimes_locations_df.csv\" , index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "cc09e12d-4044-4bb0-9f26-9e703bebffff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(242948, 3)\n"
     ]
    }
   ],
   "source": [
    "weapon_code_df =df2[[\"crime_code_identifier\" , \"weapon_used_cd\" ,\"weapon_desc\"]]\n",
    "weapon_code_df = weapon_code_df.dropna(axis = 0 , how = 'any'  )\n",
    "print(weapon_code_df.shape)\n",
    "weapon_code_df.to_csv(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\Los-Angeles-Crimes-Analysis\\weapon_code_df.csv\" , index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "fee9b66a-d08c-4a4d-87e3-fcb88e55c693",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['division_of_records', 'date_reported', 'date_occured',\n",
      "       'occurence_time', 'area', 'area_name', 'rpt_dist_no', 'part_1_2',\n",
      "       'crime_code', 'crime_code_identifier', 'mocodes', 'vict_age',\n",
      "       'vict_sex', 'vict_descent', 'premis_cd', 'premis_desc',\n",
      "       'weapon_used_cd', 'weapon_desc', 'status', 'lat', 'lon'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "print(df2.columns)\n",
    "#print(df2[\"occurence_time\"])\n",
    "time_list = ((df2[\"occurence_time\"]).dropna(axis = 0 , how = 'any'  )).to_list()\n",
    "str_time_list = []\n",
    "\n",
    "for time in time_list:\n",
    "    iterator = str(int(time))\n",
    "#    print(iterator)\n",
    "    if len(iterator) == 3 :\n",
    "        iterator = \"000\" + iterator\n",
    "    elif len(iterator) == 2 :\n",
    "        iterator = \"00\" + iterator\n",
    "    elif len(iterator) == 1:\n",
    "        iterator = \"000\" + iterator\n",
    "    str_time_list.append(iterator)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "748d729a-3d36-4857-931a-c79f078efbf8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "698667\n",
      "698667\n"
     ]
    }
   ],
   "source": [
    "hour_time_list = []\n",
    "    \n",
    "print(len(str_time_list))\n",
    "\n",
    "for time in str_time_list:\n",
    "        date_time_obj = datetime.strptime(time, \"%H%M%S\")\n",
    "        formatted_time = date_time_obj.strftime(\"%H:%M\")\n",
    "        #print(formatted_time)\n",
    "        hour_time_list.append(formatted_time)\n",
    "print(len(hour_time_list))\n",
    "precise_time_df = pd.DataFrame({'hr_min_time' : hour_time_list})\n",
    "precise_time_df = precise_time_df.clean_names()\n",
    "precise_time_df.to_csv(r\"C:\\Users\\youss\\OneDrive\\سطح المكتب\\Kaggle\\Los-Angeles-Crimes-Analysis\\precise_time_df.csv\" , index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f0ee3e9-4f16-4ead-a10d-32d86df69575",
   "metadata": {},
   "source": [
    "# Exporting The cleansed Data Frame "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "27ef35cd-c5c6-4182-8452-bfbb6df5073f",
   "metadata": {},
   "outputs": [],
   "source": [
    "convert_dict = {'date_reported': str ,\n",
    "                'date_occured' : str , \n",
    "                'division_of_records' : str}\n",
    "df2 = df2.astype(convert_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "a93c8b97-f7ea-40f8-bfdd-f3cd0aa16f3c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 698667 entries, 0 to 698666\n",
      "Data columns (total 20 columns):\n",
      " #   Column                 Non-Null Count   Dtype  \n",
      "---  ------                 --------------   -----  \n",
      " 0   division_of_records    698667 non-null  object \n",
      " 1   date_reported          698667 non-null  object \n",
      " 2   date_occured           698667 non-null  object \n",
      " 3   occurence_time         698667 non-null  float64\n",
      " 4   area                   698667 non-null  float64\n",
      " 5   area_name              698667 non-null  object \n",
      " 6   rpt_dist_no            698667 non-null  float64\n",
      " 7   part_1_2               698667 non-null  float64\n",
      " 8   crime_code             698667 non-null  float64\n",
      " 9   crime_code_identifier  698667 non-null  object \n",
      " 10  mocodes                602604 non-null  object \n",
      " 11  vict_age               698667 non-null  float64\n",
      " 12  vict_sex               607450 non-null  object \n",
      " 13  vict_descent           607446 non-null  object \n",
      " 14  premis_cd              698658 non-null  float64\n",
      " 15  premis_desc            698301 non-null  object \n",
      " 16  weapon_desc            242948 non-null  object \n",
      " 17  status                 698667 non-null  object \n",
      " 18  lat                    698667 non-null  float64\n",
      " 19  lon                    698667 non-null  float64\n",
      "dtypes: float64(9), object(11)\n",
      "memory usage: 111.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "aecbeeb9-e430-4b56-b07c-9fb0ebbb7e11",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv(\"cleaned_df.csv\" , index = False )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "830fc909-f320-433a-b4c3-d61f9620882a",
   "metadata": {},
   "source": [
    "# DashBoard Visualization"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa608150-c139-416f-a746-a0686054bb74",
   "metadata": {},
   "source": [
    "## Unreliable approach of using an llm to help in EDA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0516e1a8-39cf-4ae4-95be-be075cdb1469",
   "metadata": {},
   "source": [
    "### LLm deployment for optimized EDA"
   ]
  },
  {
   "cell_type": "raw",
   "id": "08976ffc-ea2c-451c-a90d-a2c65d49a231",
   "metadata": {},
   "source": [
    "from langchain_community.llms import HuggingFaceHub\n",
    "huggingfacehub_api_token = ''\n",
    "llm = HuggingFaceHub(repo_id='tiiuae/falcon-7b-instruct' , huggingfacehub_api_token=huggingfacehub_api_token , model_kwargs={\"temperature\": 0.95, \"max_length\": 10000})\n",
    "input = f\"\"\"\n",
    "I have a dataset in a pandas DataFrame with the following columns and sample data:\n",
    "{df2.head()}\n",
    "\n",
    "Please provide a detailed list of potential data insights and visualizations that can be included in an Exploratory Data Analysis (EDA). The response should include any patterns or trends to look for also add all realtions of data to each other. \n",
    "\"\"\"\n",
    "output = llm.invoke(input)\n",
    "print(output)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}