NYPD Motor Collision Data Prep.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "NYPD_Motor_Collision_data_Prep.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "aZSVF-T0SIDg"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data = pd.read_csv(\"/content/nypd-motor-vehicle-collisions.csv\")\n",
        "data.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 525
        },
        "id": "8yUzKzyZSQGb",
        "outputId": "b918f5ec-3c2e-4833-da38-8d94d13a5cb3"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   CRASH DATE CRASH TIME   BOROUGH  ZIP CODE  LATITUDE  LONGITUDE  \\\n",
              "0  04/14/2021       5:32       NaN       NaN       NaN        NaN   \n",
              "1  04/13/2021      21:35  BROOKLYN   11217.0  40.68358  -73.97617   \n",
              "2  04/15/2021      16:15       NaN       NaN       NaN        NaN   \n",
              "3  04/13/2021      16:00  BROOKLYN   11222.0       NaN        NaN   \n",
              "4  04/12/2021       8:25       NaN       NaN   0.00000    0.00000   \n",
              "\n",
              "                LOCATION                    ON STREET NAME CROSS STREET NAME  \\\n",
              "0                    NaN           BRONX WHITESTONE BRIDGE               NaN   \n",
              "1  (40.68358, -73.97617)                               NaN               NaN   \n",
              "2                    NaN          HUTCHINSON RIVER PARKWAY               NaN   \n",
              "3                    NaN                 VANDERVORT AVENUE    ANTHONY STREET   \n",
              "4             (0.0, 0.0)  EDSON AVENUE                                   NaN   \n",
              "\n",
              "                            OFF STREET NAME  ...  \\\n",
              "0                                       NaN  ...   \n",
              "1  620       ATLANTIC AVENUE                 ...   \n",
              "2                                       NaN  ...   \n",
              "3                                       NaN  ...   \n",
              "4                                       NaN  ...   \n",
              "\n",
              "   CONTRIBUTING FACTOR VEHICLE 2  CONTRIBUTING FACTOR VEHICLE 3  \\\n",
              "0                    Unspecified                            NaN   \n",
              "1                            NaN                            NaN   \n",
              "2                            NaN                            NaN   \n",
              "3                    Unspecified                            NaN   \n",
              "4                    Unspecified                            NaN   \n",
              "\n",
              "   CONTRIBUTING FACTOR VEHICLE 4  CONTRIBUTING FACTOR VEHICLE 5  COLLISION_ID  \\\n",
              "0                            NaN                            NaN     4407480.0   \n",
              "1                            NaN                            NaN     4407147.0   \n",
              "2                            NaN                            NaN     4407665.0   \n",
              "3                            NaN                            NaN     4407811.0   \n",
              "4                            NaN                            NaN     4406885.0   \n",
              "\n",
              "                   VEHICLE TYPE CODE 1  VEHICLE TYPE CODE 2  \\\n",
              "0                                Sedan                Sedan   \n",
              "1                                Sedan                  NaN   \n",
              "2  Station Wagon/Sport Utility Vehicle                  NaN   \n",
              "3                                Sedan                  NaN   \n",
              "4  Station Wagon/Sport Utility Vehicle                Sedan   \n",
              "\n",
              "   VEHICLE TYPE CODE 3 VEHICLE TYPE CODE 4 VEHICLE TYPE CODE 5  \n",
              "0                  NaN                 NaN                 NaN  \n",
              "1                  NaN                 NaN                 NaN  \n",
              "2                  NaN                 NaN                 NaN  \n",
              "3                  NaN                 NaN                 NaN  \n",
              "4                  NaN                 NaN                 NaN  \n",
              "\n",
              "[5 rows x 29 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b640bbab-071d-4272-9265-1d965ff8c8fa\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>CRASH DATE</th>\n",
              "      <th>CRASH TIME</th>\n",
              "      <th>BOROUGH</th>\n",
              "      <th>ZIP CODE</th>\n",
              "      <th>LATITUDE</th>\n",
              "      <th>LONGITUDE</th>\n",
              "      <th>LOCATION</th>\n",
              "      <th>ON STREET NAME</th>\n",
              "      <th>CROSS STREET NAME</th>\n",
              "      <th>OFF STREET NAME</th>\n",
              "      <th>...</th>\n",
              "      <th>CONTRIBUTING FACTOR VEHICLE 2</th>\n",
              "      <th>CONTRIBUTING FACTOR VEHICLE 3</th>\n",
              "      <th>CONTRIBUTING FACTOR VEHICLE 4</th>\n",
              "      <th>CONTRIBUTING FACTOR VEHICLE 5</th>\n",
              "      <th>COLLISION_ID</th>\n",
              "      <th>VEHICLE TYPE CODE 1</th>\n",
              "      <th>VEHICLE TYPE CODE 2</th>\n",
              "      <th>VEHICLE TYPE CODE 3</th>\n",
              "      <th>VEHICLE TYPE CODE 4</th>\n",
              "      <th>VEHICLE TYPE CODE 5</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>04/14/2021</td>\n",
              "      <td>5:32</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>BRONX WHITESTONE BRIDGE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>Unspecified</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4407480.0</td>\n",
              "      <td>Sedan</td>\n",
              "      <td>Sedan</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>04/13/2021</td>\n",
              "      <td>21:35</td>\n",
              "      <td>BROOKLYN</td>\n",
              "      <td>11217.0</td>\n",
              "      <td>40.68358</td>\n",
              "      <td>-73.97617</td>\n",
              "      <td>(40.68358, -73.97617)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>620       ATLANTIC AVENUE</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4407147.0</td>\n",
              "      <td>Sedan</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>04/15/2021</td>\n",
              "      <td>16:15</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>HUTCHINSON RIVER PARKWAY</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>4407665.0</td>\n",
              "      <td>Station Wagon/Sport Utility Vehicle</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>04/13/2021</td>\n",
              "      <td>16:00</td>\n",
              "      <td>BROOKLYN</td>\n",
              "      <td>11222.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>VANDERVORT AVENUE</td>\n",
              "      <td>ANTHONY STREET</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>Unspecified</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4407811.0</td>\n",
              "      <td>Sedan</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>04/12/2021</td>\n",
              "      <td>8:25</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.00000</td>\n",
              "      <td>0.00000</td>\n",
              "      <td>(0.0, 0.0)</td>\n",
              "      <td>EDSON AVENUE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>Unspecified</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4406885.0</td>\n",
              "      <td>Station Wagon/Sport Utility Vehicle</td>\n",
              "      <td>Sedan</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 29 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b640bbab-071d-4272-9265-1d965ff8c8fa')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-b640bbab-071d-4272-9265-1d965ff8c8fa button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-b640bbab-071d-4272-9265-1d965ff8c8fa');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data.shape"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lu80IbtvS1_w",
        "outputId": "f794fa90-b748-4eed-d090-5a607d2242b1"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(574027, 29)"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data.isna().sum()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FG4UmGN9Sr7O",
        "outputId": "cb383a3f-c1af-4f6e-f1d1-305897d29aca"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "CRASH DATE                            0\n",
              "CRASH TIME                            0\n",
              "BOROUGH                          200973\n",
              "ZIP CODE                         201061\n",
              "LATITUDE                          46277\n",
              "LONGITUDE                         46277\n",
              "LOCATION                          46277\n",
              "ON STREET NAME                   144769\n",
              "CROSS STREET NAME                297682\n",
              "OFF STREET NAME                  429429\n",
              "NUMBER OF PERSONS INJURED             2\n",
              "NUMBER OF PERSONS KILLED              9\n",
              "NUMBER OF PEDESTRIANS INJURED         0\n",
              "NUMBER OF PEDESTRIANS KILLED          0\n",
              "NUMBER OF CYCLIST INJURED             0\n",
              "NUMBER OF CYCLIST KILLED              0\n",
              "NUMBER OF MOTORIST INJURED            0\n",
              "NUMBER OF MOTORIST KILLED             0\n",
              "CONTRIBUTING FACTOR VEHICLE 1      2303\n",
              "CONTRIBUTING FACTOR VEHICLE 2    107806\n",
              "CONTRIBUTING FACTOR VEHICLE 3    527496\n",
              "CONTRIBUTING FACTOR VEHICLE 4    562624\n",
              "CONTRIBUTING FACTOR VEHICLE 5    570750\n",
              "COLLISION_ID                          1\n",
              "VEHICLE TYPE CODE 1                4912\n",
              "VEHICLE TYPE CODE 2              149809\n",
              "VEHICLE TYPE CODE 3              530133\n",
              "VEHICLE TYPE CODE 4              563130\n",
              "VEHICLE TYPE CODE 5              570861\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data.columns"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7JRERvApSxGo",
        "outputId": "5a9e405f-6e30-46c2-8dca-b0c402b4a2c1"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['CRASH DATE', 'CRASH TIME', 'BOROUGH', 'ZIP CODE', 'LATITUDE',\n",
              "       'LONGITUDE', 'LOCATION', 'ON STREET NAME', 'CROSS STREET NAME',\n",
              "       'OFF STREET NAME', 'NUMBER OF PERSONS INJURED',\n",
              "       'NUMBER OF PERSONS KILLED', 'NUMBER OF PEDESTRIANS INJURED',\n",
              "       'NUMBER OF PEDESTRIANS KILLED', 'NUMBER OF CYCLIST INJURED',\n",
              "       'NUMBER OF CYCLIST KILLED', 'NUMBER OF MOTORIST INJURED',\n",
              "       'NUMBER OF MOTORIST KILLED', 'CONTRIBUTING FACTOR VEHICLE 1',\n",
              "       'CONTRIBUTING FACTOR VEHICLE 2', 'CONTRIBUTING FACTOR VEHICLE 3',\n",
              "       'CONTRIBUTING FACTOR VEHICLE 4', 'CONTRIBUTING FACTOR VEHICLE 5',\n",
              "       'COLLISION_ID', 'VEHICLE TYPE CODE 1', 'VEHICLE TYPE CODE 2',\n",
              "       'VEHICLE TYPE CODE 3', 'VEHICLE TYPE CODE 4', 'VEHICLE TYPE CODE 5'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data.drop(['CONTRIBUTING FACTOR VEHICLE 2', 'CONTRIBUTING FACTOR VEHICLE 3',\n",
        "       'CONTRIBUTING FACTOR VEHICLE 4', 'CONTRIBUTING FACTOR VEHICLE 5','VEHICLE TYPE CODE 2',\n",
        "       'VEHICLE TYPE CODE 3', 'VEHICLE TYPE CODE 4', 'VEHICLE TYPE CODE 5','CROSS STREET NAME',\n",
        "       'OFF STREET NAME' ],axis = 1, inplace = True)\n",
        "data.shape"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cNyHwp35S5VW",
        "outputId": "0aae6ff8-6c44-40e4-c157-ed93d770e58d"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(574027, 21)"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data.isna().sum()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QDPS2LfyTTQK",
        "outputId": "caea6d66-7089-4e9b-be7d-ce0f595dbf22"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "CRASH DATE                            0\n",
              "CRASH TIME                            0\n",
              "BOROUGH                          200973\n",
              "ZIP CODE                         201061\n",
              "LATITUDE                          46277\n",
              "LONGITUDE                         46277\n",
              "LOCATION                          46277\n",
              "ON STREET NAME                   144769\n",
              "NUMBER OF PERSONS INJURED             2\n",
              "NUMBER OF PERSONS KILLED              9\n",
              "NUMBER OF PEDESTRIANS INJURED         0\n",
              "NUMBER OF PEDESTRIANS KILLED          0\n",
              "NUMBER OF CYCLIST INJURED             0\n",
              "NUMBER OF CYCLIST KILLED              0\n",
              "NUMBER OF MOTORIST INJURED            0\n",
              "NUMBER OF MOTORIST KILLED             0\n",
              "CONTRIBUTING FACTOR VEHICLE 1      2303\n",
              "COLLISION_ID                          1\n",
              "VEHICLE TYPE CODE 1                4912\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data1 = data.copy()\n",
        "data1"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 905
        },
        "id": "e2gYZ15GUJPF",
        "outputId": "15e2b2f4-4df8-4f68-87ae-c676419f18f3"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "        CRASH DATE CRASH TIME    BOROUGH  ZIP CODE   LATITUDE  LONGITUDE  \\\n",
              "0       04/14/2021       5:32        NaN       NaN        NaN        NaN   \n",
              "1       04/13/2021      21:35   BROOKLYN   11217.0  40.683580 -73.976170   \n",
              "2       04/15/2021      16:15        NaN       NaN        NaN        NaN   \n",
              "3       04/13/2021      16:00   BROOKLYN   11222.0        NaN        NaN   \n",
              "4       04/12/2021       8:25        NaN       NaN   0.000000   0.000000   \n",
              "...            ...        ...        ...       ...        ...        ...   \n",
              "574022  08/01/2018      17:30     QUEENS   11435.0  40.713604 -73.818360   \n",
              "574023  07/31/2018      20:50  MANHATTAN   10033.0  40.854168 -73.933710   \n",
              "574024  07/31/2018      12:13        NaN       NaN  40.608757 -74.038086   \n",
              "574025  08/01/2018       8:30        NaN       NaN  40.788055 -73.944330   \n",
              "574026  07/31/2018      15:00  MANHATTAN   10017.0  40.751120 -73.971270   \n",
              "\n",
              "                       LOCATION                    ON STREET NAME  \\\n",
              "0                           NaN           BRONX WHITESTONE BRIDGE   \n",
              "1         (40.68358, -73.97617)                               NaN   \n",
              "2                           NaN          HUTCHINSON RIVER PARKWAY   \n",
              "3                           NaN                 VANDERVORT AVENUE   \n",
              "4                    (0.0, 0.0)  EDSON AVENUE                       \n",
              "...                         ...                               ...   \n",
              "574022   (40.713604, -73.81836)  82 DRIVE                           \n",
              "574023   (40.854168, -73.93371)  WEST 187 STREET                    \n",
              "574024  (40.608757, -74.038086)  VERRAZANO BRIDGE UPPER             \n",
              "574025   (40.788055, -73.94433)  2 AVENUE                           \n",
              "574026    (40.75112, -73.97127)  2 AVENUE                           \n",
              "\n",
              "        NUMBER OF PERSONS INJURED  NUMBER OF PERSONS KILLED  \\\n",
              "0                             0.0                       0.0   \n",
              "1                             1.0                       0.0   \n",
              "2                             0.0                       0.0   \n",
              "3                             0.0                       0.0   \n",
              "4                             0.0                       0.0   \n",
              "...                           ...                       ...   \n",
              "574022                        0.0                       0.0   \n",
              "574023                        0.0                       0.0   \n",
              "574024                        0.0                       0.0   \n",
              "574025                        0.0                       0.0   \n",
              "574026                        0.0                       0.0   \n",
              "\n",
              "        NUMBER OF PEDESTRIANS INJURED  NUMBER OF PEDESTRIANS KILLED  \\\n",
              "0                                   0                             0   \n",
              "1                                   1                             0   \n",
              "2                                   0                             0   \n",
              "3                                   0                             0   \n",
              "4                                   0                             0   \n",
              "...                               ...                           ...   \n",
              "574022                              0                             0   \n",
              "574023                              0                             0   \n",
              "574024                              0                             0   \n",
              "574025                              0                             0   \n",
              "574026                              0                             0   \n",
              "\n",
              "        NUMBER OF CYCLIST INJURED  NUMBER OF CYCLIST KILLED  \\\n",
              "0                               0                         0   \n",
              "1                               0                         0   \n",
              "2                               0                         0   \n",
              "3                               0                         0   \n",
              "4                               0                         0   \n",
              "...                           ...                       ...   \n",
              "574022                          0                         0   \n",
              "574023                          0                         0   \n",
              "574024                          0                         0   \n",
              "574025                          0                         0   \n",
              "574026                          0                         0   \n",
              "\n",
              "        NUMBER OF MOTORIST INJURED  NUMBER OF MOTORIST KILLED  \\\n",
              "0                                0                          0   \n",
              "1                                0                          0   \n",
              "2                                0                          0   \n",
              "3                                0                          0   \n",
              "4                                0                          0   \n",
              "...                            ...                        ...   \n",
              "574022                           0                          0   \n",
              "574023                           0                          0   \n",
              "574024                           0                          0   \n",
              "574025                           0                          0   \n",
              "574026                           0                          0   \n",
              "\n",
              "         CONTRIBUTING FACTOR VEHICLE 1  COLLISION_ID  \\\n",
              "0                Following Too Closely     4407480.0   \n",
              "1                          Unspecified     4407147.0   \n",
              "2                    Pavement Slippery     4407665.0   \n",
              "3                Following Too Closely     4407811.0   \n",
              "4                          Unspecified     4406885.0   \n",
              "...                                ...           ...   \n",
              "574022  Driver Inattention/Distraction     3952924.0   \n",
              "574023              Turning Improperly     3952712.0   \n",
              "574024  Driver Inattention/Distraction     3952647.0   \n",
              "574025            Unsafe Lane Changing     3953644.0   \n",
              "574026                   Driver Inatte           NaN   \n",
              "\n",
              "                        VEHICLE TYPE CODE 1  \n",
              "0                                     Sedan  \n",
              "1                                     Sedan  \n",
              "2       Station Wagon/Sport Utility Vehicle  \n",
              "3                                     Sedan  \n",
              "4       Station Wagon/Sport Utility Vehicle  \n",
              "...                                     ...  \n",
              "574022  Station Wagon/Sport Utility Vehicle  \n",
              "574023                                Sedan  \n",
              "574024  Station Wagon/Sport Utility Vehicle  \n",
              "574025  Station Wagon/Sport Utility Vehicle  \n",
              "574026                                  NaN  \n",
              "\n",
              "[574027 rows x 19 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-339b93fb-cecf-4bb2-9a80-9f62c1466c28\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>CRASH DATE</th>\n",
              "      <th>CRASH TIME</th>\n",
              "      <th>BOROUGH</th>\n",
              "      <th>ZIP CODE</th>\n",
              "      <th>LATITUDE</th>\n",
              "      <th>LONGITUDE</th>\n",
              "      <th>LOCATION</th>\n",
              "      <th>ON STREET NAME</th>\n",
              "      <th>NUMBER OF PERSONS INJURED</th>\n",
              "      <th>NUMBER OF PERSONS KILLED</th>\n",
              "      <th>NUMBER OF PEDESTRIANS INJURED</th>\n",
              "      <th>NUMBER OF PEDESTRIANS KILLED</th>\n",
              "      <th>NUMBER OF CYCLIST INJURED</th>\n",
              "      <th>NUMBER OF CYCLIST KILLED</th>\n",
              "      <th>NUMBER OF MOTORIST INJURED</th>\n",
              "      <th>NUMBER OF MOTORIST KILLED</th>\n",
              "      <th>CONTRIBUTING FACTOR VEHICLE 1</th>\n",
              "      <th>COLLISION_ID</th>\n",
              "      <th>VEHICLE TYPE CODE 1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>04/14/2021</td>\n",
              "      <td>5:32</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>BRONX WHITESTONE BRIDGE</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Following Too Closely</td>\n",
              "      <td>4407480.0</td>\n",
              "      <td>Sedan</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>04/13/2021</td>\n",
              "      <td>21:35</td>\n",
              "      <td>BROOKLYN</td>\n",
              "      <td>11217.0</td>\n",
              "      <td>40.683580</td>\n",
              "      <td>-73.976170</td>\n",
              "      <td>(40.68358, -73.97617)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Unspecified</td>\n",
              "      <td>4407147.0</td>\n",
              "      <td>Sedan</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>04/15/2021</td>\n",
              "      <td>16:15</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>HUTCHINSON RIVER PARKWAY</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Pavement Slippery</td>\n",
              "      <td>4407665.0</td>\n",
              "      <td>Station Wagon/Sport Utility Vehicle</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>04/13/2021</td>\n",
              "      <td>16:00</td>\n",
              "      <td>BROOKLYN</td>\n",
              "      <td>11222.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>VANDERVORT AVENUE</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Following Too Closely</td>\n",
              "      <td>4407811.0</td>\n",
              "      <td>Sedan</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>04/12/2021</td>\n",
              "      <td>8:25</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>(0.0, 0.0)</td>\n",
              "      <td>EDSON AVENUE</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Unspecified</td>\n",
              "      <td>4406885.0</td>\n",
              "      <td>Station Wagon/Sport Utility Vehicle</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>574022</th>\n",
              "      <td>08/01/2018</td>\n",
              "      <td>17:30</td>\n",
              "      <td>QUEENS</td>\n",
              "      <td>11435.0</td>\n",
              "      <td>40.713604</td>\n",
              "      <td>-73.818360</td>\n",
              "      <td>(40.713604, -73.81836)</td>\n",
              "      <td>82 DRIVE</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Driver Inattention/Distraction</td>\n",
              "      <td>3952924.0</td>\n",
              "      <td>Station Wagon/Sport Utility Vehicle</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>574023</th>\n",
              "      <td>07/31/2018</td>\n",
              "      <td>20:50</td>\n",
              "      <td>MANHATTAN</td>\n",
              "      <td>10033.0</td>\n",
              "      <td>40.854168</td>\n",
              "      <td>-73.933710</td>\n",
              "      <td>(40.854168, -73.93371)</td>\n",
              "      <td>WEST 187 STREET</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Turning Improperly</td>\n",
              "      <td>3952712.0</td>\n",
              "      <td>Sedan</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>574024</th>\n",
              "      <td>07/31/2018</td>\n",
              "      <td>12:13</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>40.608757</td>\n",
              "      <td>-74.038086</td>\n",
              "      <td>(40.608757, -74.038086)</td>\n",
              "      <td>VERRAZANO BRIDGE UPPER</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Driver Inattention/Distraction</td>\n",
              "      <td>3952647.0</td>\n",
              "      <td>Station Wagon/Sport Utility Vehicle</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>574025</th>\n",
              "      <td>08/01/2018</td>\n",
              "      <td>8:30</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>40.788055</td>\n",
              "      <td>-73.944330</td>\n",
              "      <td>(40.788055, -73.94433)</td>\n",
              "      <td>2 AVENUE</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Unsafe Lane Changing</td>\n",
              "      <td>3953644.0</td>\n",
              "      <td>Station Wagon/Sport Utility Vehicle</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>574026</th>\n",
              "      <td>07/31/2018</td>\n",
              "      <td>15:00</td>\n",
              "      <td>MANHATTAN</td>\n",
              "      <td>10017.0</td>\n",
              "      <td>40.751120</td>\n",
              "      <td>-73.971270</td>\n",
              "      <td>(40.75112, -73.97127)</td>\n",
              "      <td>2 AVENUE</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Driver Inatte</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>574027 rows × 19 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-339b93fb-cecf-4bb2-9a80-9f62c1466c28')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-339b93fb-cecf-4bb2-9a80-9f62c1466c28 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-339b93fb-cecf-4bb2-9a80-9f62c1466c28');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data1.dropna(axis=0, inplace = True)\n",
        "data1.shape"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ThPbS_6MT0uL",
        "outputId": "53cf2acc-77ea-4121-c0fd-cde619207c2e"
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(223101, 19)"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data1.to_csv(\"nypd.csv\")"
      ],
      "metadata": {
        "id": "RdPSypk_T4yh"
      },
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "uw5bEszBUEiy"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}