Data_Cleaning_for_Modeling.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-NqUmf1vFSEx"
      },
      "outputs": [],
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BAcPzASXFSE0"
      },
      "outputs": [],
      "source": [
        "df= pd.read_csv(\"dataset_time_difference.csv\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_oZHkwsnFSE0"
      },
      "outputs": [],
      "source": [
        "#removing case ID\n",
        "\n",
        "df = df.drop(columns= [\"caseID\"])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FNIQXD_RFSE1",
        "outputId": "80892083-210d-4cb1-d92f-99d5458b09c4"
      },
      "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>opened</th>\n",
              "      <th>closed</th>\n",
              "      <th>difference_mins</th>\n",
              "      <th>responsible_agency</th>\n",
              "      <th>category</th>\n",
              "      <th>sup_district</th>\n",
              "      <th>neighborhood</th>\n",
              "      <th>source</th>\n",
              "      <th>pop_den</th>\n",
              "      <th>pop</th>\n",
              "      <th>households</th>\n",
              "      <th>median_hInc</th>\n",
              "      <th>percent_pov</th>\n",
              "      <th>eduHS</th>\n",
              "      <th>vehicles</th>\n",
              "      <th>num_business</th>\n",
              "      <th>num_facilities</th>\n",
              "      <th>month</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2020-01-01 00:07:00</td>\n",
              "      <td>1/1/2020 9:44</td>\n",
              "      <td>577</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>8</td>\n",
              "      <td>Castro</td>\n",
              "      <td>Integrated Agency</td>\n",
              "      <td>36.9</td>\n",
              "      <td>68,200</td>\n",
              "      <td>34,190</td>\n",
              "      <td>$121,250</td>\n",
              "      <td>7%</td>\n",
              "      <td>9%</td>\n",
              "      <td>36,910</td>\n",
              "      <td>19485</td>\n",
              "      <td>64</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2020-01-01 00:42:00</td>\n",
              "      <td>1/1/2020 1:02</td>\n",
              "      <td>20</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>5</td>\n",
              "      <td>Panhandle</td>\n",
              "      <td>Phone</td>\n",
              "      <td>56.0</td>\n",
              "      <td>84,030</td>\n",
              "      <td>40,460</td>\n",
              "      <td>$91,055</td>\n",
              "      <td>13%</td>\n",
              "      <td>14%</td>\n",
              "      <td>33,550</td>\n",
              "      <td>20027</td>\n",
              "      <td>135</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2020-01-01 00:51:00</td>\n",
              "      <td>1/1/2020 7:55</td>\n",
              "      <td>424</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>1</td>\n",
              "      <td>Inner Richmond</td>\n",
              "      <td>Phone</td>\n",
              "      <td>24.5</td>\n",
              "      <td>79,970</td>\n",
              "      <td>32,240</td>\n",
              "      <td>$83,215</td>\n",
              "      <td>11%</td>\n",
              "      <td>22%</td>\n",
              "      <td>38,790</td>\n",
              "      <td>13684</td>\n",
              "      <td>162</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020-01-01 00:56:00</td>\n",
              "      <td>1/8/2020 8:26</td>\n",
              "      <td>10530</td>\n",
              "      <td>DPW Ops Queue</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>4</td>\n",
              "      <td>Outer Sunset</td>\n",
              "      <td>Phone</td>\n",
              "      <td>31.0</td>\n",
              "      <td>77,000</td>\n",
              "      <td>26,680</td>\n",
              "      <td>$88,801</td>\n",
              "      <td>10%</td>\n",
              "      <td>28%</td>\n",
              "      <td>41,520</td>\n",
              "      <td>10562</td>\n",
              "      <td>60</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2020-01-01 01:00:00</td>\n",
              "      <td>1/1/2020 6:46</td>\n",
              "      <td>346</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>10</td>\n",
              "      <td>Silver Terrace</td>\n",
              "      <td>Phone</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>1</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452160</th>\n",
              "      <td>2020-12-31 22:46:00</td>\n",
              "      <td>1/1/2021 7:13</td>\n",
              "      <td>507</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>10</td>\n",
              "      <td>Potrero Hill</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452161</th>\n",
              "      <td>2020-12-31 23:04:00</td>\n",
              "      <td>1/2/2021 8:40</td>\n",
              "      <td>2016</td>\n",
              "      <td>Recology_Overflowing</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>6</td>\n",
              "      <td>Rincon Hill</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>30.5</td>\n",
              "      <td>69,360</td>\n",
              "      <td>37,280</td>\n",
              "      <td>$54,819</td>\n",
              "      <td>23%</td>\n",
              "      <td>29%</td>\n",
              "      <td>18,410</td>\n",
              "      <td>40514</td>\n",
              "      <td>201</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452162</th>\n",
              "      <td>2020-12-31 23:07:00</td>\n",
              "      <td>1/25/2021 1:30</td>\n",
              "      <td>34703</td>\n",
              "      <td>RPD TMA Queue</td>\n",
              "      <td>Rec and Park Requests</td>\n",
              "      <td>10</td>\n",
              "      <td>Bayview</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452163</th>\n",
              "      <td>2020-12-31 23:22:00</td>\n",
              "      <td>1/1/2021 8:57</td>\n",
              "      <td>575</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>9</td>\n",
              "      <td>Mission</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>38.6</td>\n",
              "      <td>86,430</td>\n",
              "      <td>30,580</td>\n",
              "      <td>$83,839</td>\n",
              "      <td>12%</td>\n",
              "      <td>35%</td>\n",
              "      <td>35,380</td>\n",
              "      <td>18107</td>\n",
              "      <td>89</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452164</th>\n",
              "      <td>2020-12-31 23:28:00</td>\n",
              "      <td>12/31/2020 23:38</td>\n",
              "      <td>10</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>8</td>\n",
              "      <td>Buena Vista</td>\n",
              "      <td>Phone</td>\n",
              "      <td>36.9</td>\n",
              "      <td>68,200</td>\n",
              "      <td>34,190</td>\n",
              "      <td>$121,250</td>\n",
              "      <td>7%</td>\n",
              "      <td>9%</td>\n",
              "      <td>36,910</td>\n",
              "      <td>19485</td>\n",
              "      <td>64</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>452165 rows × 18 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                    opened            closed  difference_mins  \\\n",
              "0      2020-01-01 00:07:00     1/1/2020 9:44              577   \n",
              "1      2020-01-01 00:42:00     1/1/2020 1:02               20   \n",
              "2      2020-01-01 00:51:00     1/1/2020 7:55              424   \n",
              "3      2020-01-01 00:56:00     1/8/2020 8:26            10530   \n",
              "4      2020-01-01 01:00:00     1/1/2020 6:46              346   \n",
              "...                    ...               ...              ...   \n",
              "452160 2020-12-31 22:46:00     1/1/2021 7:13              507   \n",
              "452161 2020-12-31 23:04:00     1/2/2021 8:40             2016   \n",
              "452162 2020-12-31 23:07:00    1/25/2021 1:30            34703   \n",
              "452163 2020-12-31 23:22:00     1/1/2021 8:57              575   \n",
              "452164 2020-12-31 23:28:00  12/31/2020 23:38               10   \n",
              "\n",
              "                        responsible_agency                      category  \\\n",
              "0                       Recology_Abandoned  Street and Sidewalk Cleaning   \n",
              "1       Parking Enforcement Dispatch Queue           Parking Enforcement   \n",
              "2       Parking Enforcement Dispatch Queue           Parking Enforcement   \n",
              "3                            DPW Ops Queue  Street and Sidewalk Cleaning   \n",
              "4       Parking Enforcement Dispatch Queue           Parking Enforcement   \n",
              "...                                    ...                           ...   \n",
              "452160  Parking Enforcement Dispatch Queue           Parking Enforcement   \n",
              "452161                Recology_Overflowing  Street and Sidewalk Cleaning   \n",
              "452162                       RPD TMA Queue         Rec and Park Requests   \n",
              "452163                  Recology_Abandoned  Street and Sidewalk Cleaning   \n",
              "452164  Parking Enforcement Dispatch Queue           Parking Enforcement   \n",
              "\n",
              "        sup_district    neighborhood             source  pop_den     pop  \\\n",
              "0                  8          Castro  Integrated Agency     36.9  68,200   \n",
              "1                  5       Panhandle              Phone     56.0  84,030   \n",
              "2                  1  Inner Richmond              Phone     24.5  79,970   \n",
              "3                  4    Outer Sunset              Phone     31.0  77,000   \n",
              "4                 10  Silver Terrace              Phone     15.2  75,250   \n",
              "...              ...             ...                ...      ...     ...   \n",
              "452160            10    Potrero Hill     Mobile/Open311     15.2  75,250   \n",
              "452161             6     Rincon Hill     Mobile/Open311     30.5  69,360   \n",
              "452162            10         Bayview     Mobile/Open311     15.2  75,250   \n",
              "452163             9         Mission     Mobile/Open311     38.6  86,430   \n",
              "452164             8     Buena Vista              Phone     36.9  68,200   \n",
              "\n",
              "       households median_hInc percent_pov eduHS vehicles  num_business  \\\n",
              "0          34,190   $121,250           7%    9%   36,910         19485   \n",
              "1          40,460    $91,055          13%   14%   33,550         20027   \n",
              "2          32,240    $83,215          11%   22%   38,790         13684   \n",
              "3          26,680    $88,801          10%   28%   41,520         10562   \n",
              "4          23,910    $69,915          16%   42%   34,520         20132   \n",
              "...           ...         ...         ...   ...      ...           ...   \n",
              "452160     23,910    $69,915          16%   42%   34,520         20132   \n",
              "452161     37,280    $54,819          23%   29%   18,410         40514   \n",
              "452162     23,910    $69,915          16%   42%   34,520         20132   \n",
              "452163     30,580    $83,839          12%   35%   35,380         18107   \n",
              "452164     34,190   $121,250           7%    9%   36,910         19485   \n",
              "\n",
              "        num_facilities  month  \n",
              "0                   64      1  \n",
              "1                  135      1  \n",
              "2                  162      1  \n",
              "3                   60      1  \n",
              "4                  300      1  \n",
              "...                ...    ...  \n",
              "452160             300     12  \n",
              "452161             201     12  \n",
              "452162             300     12  \n",
              "452163              89     12  \n",
              "452164              64     12  \n",
              "\n",
              "[452165 rows x 18 columns]"
            ]
          },
          "execution_count": 4,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "#extract the month from open columns\n",
        "\n",
        "df[\"opened\"] = df['opened'].map(lambda date: pd.Timestamp(date))\n",
        "df[\"month\"] = df['opened'].map(lambda dm: dm.month)\n",
        "df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pCPm09S8FSE1",
        "outputId": "e6ef80de-d37f-4d10-c028-c7f66490bb67"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "8     42962\n",
              "9     42382\n",
              "10    41930\n",
              "1     41384\n",
              "7     39708\n",
              "12    38613\n",
              "2     38440\n",
              "11    37608\n",
              "6     36585\n",
              "5     32975\n",
              "3     32958\n",
              "4     26620\n",
              "Name: month, dtype: int64"
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df.month.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Y1cPneoQFSE2"
      },
      "outputs": [],
      "source": [
        "#delete the open and closed columns\n",
        "\n",
        "df = df.drop(columns= [\"opened\",\"closed\"])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MCVhZ70qFSE2"
      },
      "outputs": [],
      "source": [
        "#Reduce the data to top 5 Categories\n",
        "new_df = df[df.category.isin(['Street and Sidewalk Cleaning','Parking Enforcement','Graffiti','General Request - PUBLIC WORKS','Illegal Postings'])]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "TajDBDlRFSE2",
        "outputId": "979631a9-4fd2-4539-a40e-1e2e484f15cd"
      },
      "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>difference_mins</th>\n",
              "      <th>responsible_agency</th>\n",
              "      <th>category</th>\n",
              "      <th>sup_district</th>\n",
              "      <th>neighborhood</th>\n",
              "      <th>source</th>\n",
              "      <th>pop_den</th>\n",
              "      <th>pop</th>\n",
              "      <th>households</th>\n",
              "      <th>median_hInc</th>\n",
              "      <th>percent_pov</th>\n",
              "      <th>eduHS</th>\n",
              "      <th>vehicles</th>\n",
              "      <th>num_business</th>\n",
              "      <th>num_facilities</th>\n",
              "      <th>month</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>577</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>8</td>\n",
              "      <td>Castro</td>\n",
              "      <td>Integrated Agency</td>\n",
              "      <td>36.9</td>\n",
              "      <td>68,200</td>\n",
              "      <td>34,190</td>\n",
              "      <td>$121,250</td>\n",
              "      <td>7%</td>\n",
              "      <td>9%</td>\n",
              "      <td>36,910</td>\n",
              "      <td>19485</td>\n",
              "      <td>64</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>20</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>5</td>\n",
              "      <td>Panhandle</td>\n",
              "      <td>Phone</td>\n",
              "      <td>56.0</td>\n",
              "      <td>84,030</td>\n",
              "      <td>40,460</td>\n",
              "      <td>$91,055</td>\n",
              "      <td>13%</td>\n",
              "      <td>14%</td>\n",
              "      <td>33,550</td>\n",
              "      <td>20027</td>\n",
              "      <td>135</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>424</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>1</td>\n",
              "      <td>Inner Richmond</td>\n",
              "      <td>Phone</td>\n",
              "      <td>24.5</td>\n",
              "      <td>79,970</td>\n",
              "      <td>32,240</td>\n",
              "      <td>$83,215</td>\n",
              "      <td>11%</td>\n",
              "      <td>22%</td>\n",
              "      <td>38,790</td>\n",
              "      <td>13684</td>\n",
              "      <td>162</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10530</td>\n",
              "      <td>DPW Ops Queue</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>4</td>\n",
              "      <td>Outer Sunset</td>\n",
              "      <td>Phone</td>\n",
              "      <td>31.0</td>\n",
              "      <td>77,000</td>\n",
              "      <td>26,680</td>\n",
              "      <td>$88,801</td>\n",
              "      <td>10%</td>\n",
              "      <td>28%</td>\n",
              "      <td>41,520</td>\n",
              "      <td>10562</td>\n",
              "      <td>60</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>346</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>10</td>\n",
              "      <td>Silver Terrace</td>\n",
              "      <td>Phone</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>1</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452159</th>\n",
              "      <td>732</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>10</td>\n",
              "      <td>Little Hollywood</td>\n",
              "      <td>Web</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452160</th>\n",
              "      <td>507</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>10</td>\n",
              "      <td>Potrero Hill</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452161</th>\n",
              "      <td>2016</td>\n",
              "      <td>Recology_Overflowing</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>6</td>\n",
              "      <td>Rincon Hill</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>30.5</td>\n",
              "      <td>69,360</td>\n",
              "      <td>37,280</td>\n",
              "      <td>$54,819</td>\n",
              "      <td>23%</td>\n",
              "      <td>29%</td>\n",
              "      <td>18,410</td>\n",
              "      <td>40514</td>\n",
              "      <td>201</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452163</th>\n",
              "      <td>575</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>9</td>\n",
              "      <td>Mission</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>38.6</td>\n",
              "      <td>86,430</td>\n",
              "      <td>30,580</td>\n",
              "      <td>$83,839</td>\n",
              "      <td>12%</td>\n",
              "      <td>35%</td>\n",
              "      <td>35,380</td>\n",
              "      <td>18107</td>\n",
              "      <td>89</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452164</th>\n",
              "      <td>10</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>8</td>\n",
              "      <td>Buena Vista</td>\n",
              "      <td>Phone</td>\n",
              "      <td>36.9</td>\n",
              "      <td>68,200</td>\n",
              "      <td>34,190</td>\n",
              "      <td>$121,250</td>\n",
              "      <td>7%</td>\n",
              "      <td>9%</td>\n",
              "      <td>36,910</td>\n",
              "      <td>19485</td>\n",
              "      <td>64</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>378842 rows × 16 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "        difference_mins                  responsible_agency  \\\n",
              "0                   577                  Recology_Abandoned   \n",
              "1                    20  Parking Enforcement Dispatch Queue   \n",
              "2                   424  Parking Enforcement Dispatch Queue   \n",
              "3                 10530                       DPW Ops Queue   \n",
              "4                   346  Parking Enforcement Dispatch Queue   \n",
              "...                 ...                                 ...   \n",
              "452159              732                  Recology_Abandoned   \n",
              "452160              507  Parking Enforcement Dispatch Queue   \n",
              "452161             2016                Recology_Overflowing   \n",
              "452163              575                  Recology_Abandoned   \n",
              "452164               10  Parking Enforcement Dispatch Queue   \n",
              "\n",
              "                            category  sup_district      neighborhood  \\\n",
              "0       Street and Sidewalk Cleaning             8            Castro   \n",
              "1                Parking Enforcement             5         Panhandle   \n",
              "2                Parking Enforcement             1    Inner Richmond   \n",
              "3       Street and Sidewalk Cleaning             4      Outer Sunset   \n",
              "4                Parking Enforcement            10    Silver Terrace   \n",
              "...                              ...           ...               ...   \n",
              "452159  Street and Sidewalk Cleaning            10  Little Hollywood   \n",
              "452160           Parking Enforcement            10      Potrero Hill   \n",
              "452161  Street and Sidewalk Cleaning             6       Rincon Hill   \n",
              "452163  Street and Sidewalk Cleaning             9           Mission   \n",
              "452164           Parking Enforcement             8       Buena Vista   \n",
              "\n",
              "                   source  pop_den     pop households median_hInc percent_pov  \\\n",
              "0       Integrated Agency     36.9  68,200     34,190   $121,250           7%   \n",
              "1                   Phone     56.0  84,030     40,460    $91,055          13%   \n",
              "2                   Phone     24.5  79,970     32,240    $83,215          11%   \n",
              "3                   Phone     31.0  77,000     26,680    $88,801          10%   \n",
              "4                   Phone     15.2  75,250     23,910    $69,915          16%   \n",
              "...                   ...      ...     ...        ...         ...         ...   \n",
              "452159                Web     15.2  75,250     23,910    $69,915          16%   \n",
              "452160     Mobile/Open311     15.2  75,250     23,910    $69,915          16%   \n",
              "452161     Mobile/Open311     30.5  69,360     37,280    $54,819          23%   \n",
              "452163     Mobile/Open311     38.6  86,430     30,580    $83,839          12%   \n",
              "452164              Phone     36.9  68,200     34,190   $121,250           7%   \n",
              "\n",
              "       eduHS vehicles  num_business  num_facilities  month  \n",
              "0         9%   36,910         19485              64      1  \n",
              "1        14%   33,550         20027             135      1  \n",
              "2        22%   38,790         13684             162      1  \n",
              "3        28%   41,520         10562              60      1  \n",
              "4        42%   34,520         20132             300      1  \n",
              "...      ...      ...           ...             ...    ...  \n",
              "452159   42%   34,520         20132             300     12  \n",
              "452160   42%   34,520         20132             300     12  \n",
              "452161   29%   18,410         40514             201     12  \n",
              "452163   35%   35,380         18107              89     12  \n",
              "452164    9%   36,910         19485              64     12  \n",
              "\n",
              "[378842 rows x 16 columns]"
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "new_df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RkKoYUCeFSE3",
        "outputId": "0d3f63f5-2af6-4a6b-ebf9-7b287869acc4"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "C:\\Users\\kimbe\\AppData\\Local\\Temp/ipykernel_28068/3099546329.py:14: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame.\n",
            "Try using .loc[row_indexer,col_indexer] = value instead\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "  new_df[\"neighborhood\"] = new_df[\"neighborhood\"].map(smooth)\n"
          ]
        }
      ],
      "source": [
        "# performing mean target encoding on neighborhood to prevent curse of dimensionality\n",
        "# including smoothing (to reduce overfitting and data leakage)\n",
        "\n",
        "target_mean = new_df[\"difference_mins\"].mean()\n",
        "agg = new_df.groupby(\"neighborhood\")[\"difference_mins\"].agg([\"count\", \"mean\"])\n",
        "counts = agg[\"count\"]\n",
        "means = agg[\"mean\"]\n",
        "weight = 100\n",
        "\n",
        "# compute smoothed means\n",
        "smooth = (counts * means + weight * target_mean)/ (counts + weight)\n",
        "\n",
        "# replace neighborhood values with smoothed mean\n",
        "new_df[\"neighborhood\"] = new_df[\"neighborhood\"].map(smooth)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "B65H8GvJFSE4",
        "outputId": "b734ffaf-4a55-4f62-be7a-51847a156cd9"
      },
      "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>difference_mins</th>\n",
              "      <th>responsible_agency</th>\n",
              "      <th>category</th>\n",
              "      <th>sup_district</th>\n",
              "      <th>neighborhood</th>\n",
              "      <th>source</th>\n",
              "      <th>pop_den</th>\n",
              "      <th>pop</th>\n",
              "      <th>households</th>\n",
              "      <th>median_hInc</th>\n",
              "      <th>percent_pov</th>\n",
              "      <th>eduHS</th>\n",
              "      <th>vehicles</th>\n",
              "      <th>num_business</th>\n",
              "      <th>num_facilities</th>\n",
              "      <th>month</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>577</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>8</td>\n",
              "      <td>5845.922391</td>\n",
              "      <td>Integrated Agency</td>\n",
              "      <td>36.9</td>\n",
              "      <td>68,200</td>\n",
              "      <td>34,190</td>\n",
              "      <td>$121,250</td>\n",
              "      <td>7%</td>\n",
              "      <td>9%</td>\n",
              "      <td>36,910</td>\n",
              "      <td>19485</td>\n",
              "      <td>64</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>20</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>5</td>\n",
              "      <td>7057.553221</td>\n",
              "      <td>Phone</td>\n",
              "      <td>56.0</td>\n",
              "      <td>84,030</td>\n",
              "      <td>40,460</td>\n",
              "      <td>$91,055</td>\n",
              "      <td>13%</td>\n",
              "      <td>14%</td>\n",
              "      <td>33,550</td>\n",
              "      <td>20027</td>\n",
              "      <td>135</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>424</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>1</td>\n",
              "      <td>5644.767837</td>\n",
              "      <td>Phone</td>\n",
              "      <td>24.5</td>\n",
              "      <td>79,970</td>\n",
              "      <td>32,240</td>\n",
              "      <td>$83,215</td>\n",
              "      <td>11%</td>\n",
              "      <td>22%</td>\n",
              "      <td>38,790</td>\n",
              "      <td>13684</td>\n",
              "      <td>162</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10530</td>\n",
              "      <td>DPW Ops Queue</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>4</td>\n",
              "      <td>7495.758072</td>\n",
              "      <td>Phone</td>\n",
              "      <td>31.0</td>\n",
              "      <td>77,000</td>\n",
              "      <td>26,680</td>\n",
              "      <td>$88,801</td>\n",
              "      <td>10%</td>\n",
              "      <td>28%</td>\n",
              "      <td>41,520</td>\n",
              "      <td>10562</td>\n",
              "      <td>60</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>346</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>10</td>\n",
              "      <td>2147.127059</td>\n",
              "      <td>Phone</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>1</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452159</th>\n",
              "      <td>732</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>10</td>\n",
              "      <td>6679.329413</td>\n",
              "      <td>Web</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452160</th>\n",
              "      <td>507</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>10</td>\n",
              "      <td>5178.746303</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>15.2</td>\n",
              "      <td>75,250</td>\n",
              "      <td>23,910</td>\n",
              "      <td>$69,915</td>\n",
              "      <td>16%</td>\n",
              "      <td>42%</td>\n",
              "      <td>34,520</td>\n",
              "      <td>20132</td>\n",
              "      <td>300</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452161</th>\n",
              "      <td>2016</td>\n",
              "      <td>Recology_Overflowing</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>6</td>\n",
              "      <td>3107.342771</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>30.5</td>\n",
              "      <td>69,360</td>\n",
              "      <td>37,280</td>\n",
              "      <td>$54,819</td>\n",
              "      <td>23%</td>\n",
              "      <td>29%</td>\n",
              "      <td>18,410</td>\n",
              "      <td>40514</td>\n",
              "      <td>201</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452163</th>\n",
              "      <td>575</td>\n",
              "      <td>Recology_Abandoned</td>\n",
              "      <td>Street and Sidewalk Cleaning</td>\n",
              "      <td>9</td>\n",
              "      <td>4699.990582</td>\n",
              "      <td>Mobile/Open311</td>\n",
              "      <td>38.6</td>\n",
              "      <td>86,430</td>\n",
              "      <td>30,580</td>\n",
              "      <td>$83,839</td>\n",
              "      <td>12%</td>\n",
              "      <td>35%</td>\n",
              "      <td>35,380</td>\n",
              "      <td>18107</td>\n",
              "      <td>89</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>452164</th>\n",
              "      <td>10</td>\n",
              "      <td>Parking Enforcement Dispatch Queue</td>\n",
              "      <td>Parking Enforcement</td>\n",
              "      <td>8</td>\n",
              "      <td>7578.971266</td>\n",
              "      <td>Phone</td>\n",
              "      <td>36.9</td>\n",
              "      <td>68,200</td>\n",
              "      <td>34,190</td>\n",
              "      <td>$121,250</td>\n",
              "      <td>7%</td>\n",
              "      <td>9%</td>\n",
              "      <td>36,910</td>\n",
              "      <td>19485</td>\n",
              "      <td>64</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>378842 rows × 16 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "        difference_mins                  responsible_agency  \\\n",
              "0                   577                  Recology_Abandoned   \n",
              "1                    20  Parking Enforcement Dispatch Queue   \n",
              "2                   424  Parking Enforcement Dispatch Queue   \n",
              "3                 10530                       DPW Ops Queue   \n",
              "4                   346  Parking Enforcement Dispatch Queue   \n",
              "...                 ...                                 ...   \n",
              "452159              732                  Recology_Abandoned   \n",
              "452160              507  Parking Enforcement Dispatch Queue   \n",
              "452161             2016                Recology_Overflowing   \n",
              "452163              575                  Recology_Abandoned   \n",
              "452164               10  Parking Enforcement Dispatch Queue   \n",
              "\n",
              "                            category  sup_district  neighborhood  \\\n",
              "0       Street and Sidewalk Cleaning             8   5845.922391   \n",
              "1                Parking Enforcement             5   7057.553221   \n",
              "2                Parking Enforcement             1   5644.767837   \n",
              "3       Street and Sidewalk Cleaning             4   7495.758072   \n",
              "4                Parking Enforcement            10   2147.127059   \n",
              "...                              ...           ...           ...   \n",
              "452159  Street and Sidewalk Cleaning            10   6679.329413   \n",
              "452160           Parking Enforcement            10   5178.746303   \n",
              "452161  Street and Sidewalk Cleaning             6   3107.342771   \n",
              "452163  Street and Sidewalk Cleaning             9   4699.990582   \n",
              "452164           Parking Enforcement             8   7578.971266   \n",
              "\n",
              "                   source  pop_den     pop households median_hInc percent_pov  \\\n",
              "0       Integrated Agency     36.9  68,200     34,190   $121,250           7%   \n",
              "1                   Phone     56.0  84,030     40,460    $91,055          13%   \n",
              "2                   Phone     24.5  79,970     32,240    $83,215          11%   \n",
              "3                   Phone     31.0  77,000     26,680    $88,801          10%   \n",
              "4                   Phone     15.2  75,250     23,910    $69,915          16%   \n",
              "...                   ...      ...     ...        ...         ...         ...   \n",
              "452159                Web     15.2  75,250     23,910    $69,915          16%   \n",
              "452160     Mobile/Open311     15.2  75,250     23,910    $69,915          16%   \n",
              "452161     Mobile/Open311     30.5  69,360     37,280    $54,819          23%   \n",
              "452163     Mobile/Open311     38.6  86,430     30,580    $83,839          12%   \n",
              "452164              Phone     36.9  68,200     34,190   $121,250           7%   \n",
              "\n",
              "       eduHS vehicles  num_business  num_facilities  month  \n",
              "0         9%   36,910         19485              64      1  \n",
              "1        14%   33,550         20027             135      1  \n",
              "2        22%   38,790         13684             162      1  \n",
              "3        28%   41,520         10562              60      1  \n",
              "4        42%   34,520         20132             300      1  \n",
              "...      ...      ...           ...             ...    ...  \n",
              "452159   42%   34,520         20132             300     12  \n",
              "452160   42%   34,520         20132             300     12  \n",
              "452161   29%   18,410         40514             201     12  \n",
              "452163   35%   35,380         18107              89     12  \n",
              "452164    9%   36,910         19485              64     12  \n",
              "\n",
              "[378842 rows x 16 columns]"
            ]
          },
          "execution_count": 12,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "new_df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "plQaV7SfFSE4"
      },
      "outputs": [],
      "source": []
    }
  ],
  "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.8.11"
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}