311_data_indexing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports & Installs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Installs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install geopandas\n",
    "!pip install geopy\n",
    "!pip install shapely\n",
    "!pip install pyproj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import math\n",
    "import random\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import geopandas as gpd\n",
    "import geopy\n",
    "from geopy.distance import distance, Distance, great_circle\n",
    "import shapely\n",
    "from shapely.geometry import Polygon\n",
    "import pyproj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 311 data Indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### shapefile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# shapefile used to determine indexing grid variables\n",
    "shp = gpd.read_file('/Users/ernestvmo/Downloads/Borough Boundaries/geo_export_c4a7f77b-811d-4998-b675-a83cf42208e0.shp')\n",
    "shp['geometry'] = shp['geometry'].values.to_crs(2263)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "min_x = min(shp.bounds['minx'].values)\n",
    "max_x = max(shp.bounds['maxx'].values)\n",
    "\n",
    "min_y = min(shp.bounds['miny'].values)\n",
    "max_y = max(shp.bounds['maxy'].values)\n",
    "\n",
    "grid_00 = (min_x, max_y)\n",
    "grid_01 = (max_x, max_y)\n",
    "grid_10 = (min_x, min_y)\n",
    "grid_11 = (max_x, min_y)\n",
    "\n",
    "cell_x_diff = 6579.3\n",
    "cell_y_diff = -6574.5\n",
    "\n",
    "# calculated using great_circle distance\n",
    "height = 46.63508344453982 # height of NYC in km\n",
    "width = 46.68408524309399 # width of NYC in km"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "913175.1090087884 1067382.508422852\n",
      "120128.36999505223 272844.2938231766\n"
     ]
    }
   ],
   "source": [
    "print(min_x, max_x)\n",
    "print(min_y, max_y)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/IPython/core/interactiveshell.py:3444: DtypeWarning: Columns (5) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n",
      "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/numpy/lib/arraysetops.py:580: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    }
   ],
   "source": [
    "_311_filtered_file = \"/Users/ernestvmo/OneDrive - Universiteit Leiden/Q1/Urban Computing/Project/data/311filtered.csv\"\n",
    "_311_df = pd.read_csv(_311_filtered_file, index_col=[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assign Index using Modulo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "_311_df['Created Date'] = pd.to_datetime(_311_df['Created Date']).dt.date\n",
    "# _311_df['Created Date'] = pd.to_datetime(_311_df['Created Date']).dt.strftime('%Y/%m/%d')\n",
    "# _311_df['Closed Date'] = pd.to_datetime(_311_df['Closed Date'])\n",
    "# _311_df['Closed Date'] = pd.to_datetime(_311_df['Closed Date']).dt.strftime('%Y/%m/%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Created Date</th>\n",
       "      <th>Closed Date</th>\n",
       "      <th>Complaint Type</th>\n",
       "      <th>Descriptor</th>\n",
       "      <th>Incident Zip</th>\n",
       "      <th>X Coordinate (State Plane)</th>\n",
       "      <th>Y Coordinate (State Plane)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>07/05/2012 12:00:00 AM</td>\n",
       "      <td>NONCONST</td>\n",
       "      <td>RUBBISH</td>\n",
       "      <td>10454</td>\n",
       "      <td>1005484.0</td>\n",
       "      <td>233852.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>07/09/2012 12:00:00 AM</td>\n",
       "      <td>PLUMBING</td>\n",
       "      <td>TOILET</td>\n",
       "      <td>10467</td>\n",
       "      <td>1021274.0</td>\n",
       "      <td>257092.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>07/17/2012 12:00:00 AM</td>\n",
       "      <td>NONCONST</td>\n",
       "      <td>VERMIN</td>\n",
       "      <td>11221</td>\n",
       "      <td>1004475.0</td>\n",
       "      <td>189909.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>07/09/2012 12:00:00 AM</td>\n",
       "      <td>PAINT - PLASTER</td>\n",
       "      <td>CEILING</td>\n",
       "      <td>10454</td>\n",
       "      <td>1005205.0</td>\n",
       "      <td>234836.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>07/10/2012 12:00:00 AM</td>\n",
       "      <td>PAINT - PLASTER</td>\n",
       "      <td>CEILING</td>\n",
       "      <td>11361</td>\n",
       "      <td>1046844.0</td>\n",
       "      <td>217017.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1853020</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>07/08/2013 12:00:00 AM</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Condition Attracting Rodents</td>\n",
       "      <td>11221.0</td>\n",
       "      <td>1003028.0</td>\n",
       "      <td>190880.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1853021</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>06/30/2013 12:00:00 AM</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Mouse Sighting</td>\n",
       "      <td>10040.0</td>\n",
       "      <td>1003928.0</td>\n",
       "      <td>251236.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1853022</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>07/09/2013 04:16:44 PM</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Condition Attracting Rodents</td>\n",
       "      <td>11369.0</td>\n",
       "      <td>1018302.0</td>\n",
       "      <td>218807.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1853023</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>07/22/2013 12:00:00 AM</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Rat Sighting</td>\n",
       "      <td>10314.0</td>\n",
       "      <td>949019.0</td>\n",
       "      <td>165690.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1853024</th>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>06/30/2013 12:00:00 AM</td>\n",
       "      <td>Rodent</td>\n",
       "      <td>Rat Sighting</td>\n",
       "      <td>10024.0</td>\n",
       "      <td>989836.0</td>\n",
       "      <td>226065.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1853025 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Created Date             Closed Date   Complaint Type  \\\n",
       "0         2012-07-01  07/05/2012 12:00:00 AM         NONCONST   \n",
       "1         2012-07-01  07/09/2012 12:00:00 AM         PLUMBING   \n",
       "2         2012-07-01  07/17/2012 12:00:00 AM         NONCONST   \n",
       "3         2012-07-01  07/09/2012 12:00:00 AM  PAINT - PLASTER   \n",
       "4         2012-07-01  07/10/2012 12:00:00 AM  PAINT - PLASTER   \n",
       "...              ...                     ...              ...   \n",
       "1853020   2013-06-30  07/08/2013 12:00:00 AM           Rodent   \n",
       "1853021   2013-06-30  06/30/2013 12:00:00 AM           Rodent   \n",
       "1853022   2013-06-30  07/09/2013 04:16:44 PM           Rodent   \n",
       "1853023   2013-06-30  07/22/2013 12:00:00 AM           Rodent   \n",
       "1853024   2013-06-30  06/30/2013 12:00:00 AM           Rodent   \n",
       "\n",
       "                           Descriptor Incident Zip  \\\n",
       "0                             RUBBISH        10454   \n",
       "1                              TOILET        10467   \n",
       "2                              VERMIN        11221   \n",
       "3                             CEILING        10454   \n",
       "4                             CEILING        11361   \n",
       "...                               ...          ...   \n",
       "1853020  Condition Attracting Rodents      11221.0   \n",
       "1853021                Mouse Sighting      10040.0   \n",
       "1853022  Condition Attracting Rodents      11369.0   \n",
       "1853023                  Rat Sighting      10314.0   \n",
       "1853024                  Rat Sighting      10024.0   \n",
       "\n",
       "         X Coordinate (State Plane)  Y Coordinate (State Plane)  \n",
       "0                         1005484.0                    233852.0  \n",
       "1                         1021274.0                    257092.0  \n",
       "2                         1004475.0                    189909.0  \n",
       "3                         1005205.0                    234836.0  \n",
       "4                         1046844.0                    217017.0  \n",
       "...                             ...                         ...  \n",
       "1853020                   1003028.0                    190880.0  \n",
       "1853021                   1003928.0                    251236.0  \n",
       "1853022                   1018302.0                    218807.0  \n",
       "1853023                    949019.0                    165690.0  \n",
       "1853024                    989836.0                    226065.0  \n",
       "\n",
       "[1853025 rows x 7 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_311_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "_311_df = _311_df.rename(columns={'X Coordinate (State Plane)':'xcoord', 'Y Coordinate (State Plane)':'ycoord'})\n",
    "_311_df = _311_df[['Created Date', 'xcoord', 'ycoord']]\n",
    "_311_df = _311_df[~_311_df['xcoord'].isna()]\n",
    "_311_df = _311_df[~_311_df['ycoord'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "_311_df['nyc_index_x'], _311_df['nyc_index_y'] = np.floor((_311_df['xcoord'].values - min_x) / cell_x_diff), np.floor(((width // 2) - (_311_df['ycoord'].values - min_y) / abs(cell_y_diff)))\n",
    "_311_df = _311_df.astype({'nyc_index_x':'int64', 'nyc_index_y':'int64'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Created Date     object\n",
       "xcoord          float64\n",
       "ycoord          float64\n",
       "nyc_index_x       int64\n",
       "nyc_index_y       int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_311_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "_311_df = _311_df.rename(columns={'Created Date':'datestop'})\n",
    "_311_df['coordinates'] = list(zip(_311_df.nyc_index_x, _311_df.nyc_index_y))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2012, 7, 1)"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_311_df['datestop'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_311_df_1 = _311_df.groupby(['datestop', 'coordinates']).size().reset_index()\n",
    "grouped_311_df_2 = _311_df.groupby(['datestop', 'nyc_index_x', 'nyc_index_y']).size().reset_index()\n",
    "\n",
    "grouped_311_df_1 = grouped_311_df_1.rename(columns={0:'311_complaints_COUNT'})\n",
    "grouped_311_df_2 = grouped_311_df_2.rename(columns={0:'311_compaints_COUNT'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_location = '/Users/ernestvmo/OneDrive - Universiteit Leiden/Q1/Urban Computing/Project/data/grouped_csv/'\n",
    "\n",
    "grouped_311_df_1.to_csv(save_location + 'grouped_311.csv')\n",
    "# grouped_nypd_complaints_2.to_csv('grouped_nypd_complaints.csv')"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3.9.4 64-bit",
   "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.9.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}