notebooks/311-data-cleaning-part2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ed963dcd-21bf-48ad-a08e-8c513f734098",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "# Extend the 311 Data\n",
    "\n",
    "Extend clean311 data set with:\n",
    "\n",
    "  1.  Read clean311 and certified neighborhood council data\n",
    "  2.  Add service region information to clean311\n",
    "  3.  Save this version\n",
    "  3.  Add columns for ipyleaflet widgetry\n",
    "  4.  Save this final version\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30aa9912-6f60-4a1f-baef-dea36d06a24c",
   "metadata": {},
   "source": [
    "# 1 - Data inputs\n",
    "\n",
    "The two files are new311-shape.zip and Neighborhood-Councils-(Certified)_cleaned (see NC-service-regions.ipynb)\n",
    "\n",
    "**Note** - I'm using the utility function to read and transform column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33784b6b-eea2-4c56-9cdc-b26acbdf5f00",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run start.py\n",
    "\n",
    "import utils\n",
    "from utils import read_new311_shape, marker_color_map, dt_to_object\n",
    "\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4f8c0d6-885f-4954-ad89-6bf270fac5c6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "#new311_gdf = read_new311_shape('../data/311/clean311-geo.shp')\n",
    "new311_gdf = gpd.read_parquet('../data/311/clean311.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0ffe43a-c50c-4221-b739-f51621089b86",
   "metadata": {},
   "outputs": [],
   "source": [
    "new311_gdf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c3b0175-601a-41e5-b41a-23b003401350",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01ff5106-25fc-41d0-b66a-f3e6df3660ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c258c067-6165-461d-8a65-94aa403f5be7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "ncs_gdf = gpd.read_file('../data/neighborhoods/Neighborhood_Councils_(Certified)_cleaned.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83f1cec2-ff93-4a71-9e96-b05ffaaedca7",
   "metadata": {},
   "outputs": [],
   "source": [
    "ncs_gdf.rename(columns={'NAME': 'name',\n",
    "                        'NC_ID': 'nc_id',\n",
    "                        'SERVICE_RE': 'service_region'},\n",
    "              inplace=True);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af23eef4-0895-42ef-b46e-90ff49b2e80c",
   "metadata": {},
   "source": [
    "# 2 - Extend with Service Region Information\n",
    "\n",
    "Nothing real special here.  I am using the \"brute force\" appoach with two separate calls to apply.  I'll use alternative for the next section.\n",
    "\n",
    "You can see that I'm checking for pd.NA since it's possible for NaN value.  Just passing it on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33ba4fc8-0828-4cb1-851b-3908ee8eae54",
   "metadata": {},
   "outputs": [],
   "source": [
    "region_id_dict = dict(zip(ncs_gdf.nc_id, ncs_gdf.region_id))\n",
    "service_region_dict = dict(zip(ncs_gdf.nc_id, ncs_gdf.service_region))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12501650-b43c-4a0b-977e-b01bf283165b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def service_region(nc_id):\n",
    "    if not(nc_id is pd.NA):\n",
    "        return service_region_dict.get(nc_id)\n",
    "    \n",
    "def region_id(nc_id):\n",
    "    if not(nc_id is pd.NA):\n",
    "        return int(region_id_dict.get(nc_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d869eb0-a462-467f-af86-7c939b3ddb2e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "new311_gdf['service_region'] = new311_gdf['nc'].apply(lambda nc_id: service_region(nc_id))\n",
    "new311_gdf['region_id'] = new311_gdf['nc'].apply(lambda nc_id: region_id(nc_id))\n",
    "new311_gdf['region_id'] = new311_gdf['region_id'].astype('Int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5d30c72-7e63-4bcb-8ae5-0690f01919d5",
   "metadata": {},
   "source": [
    "So this is what the gdf looks like now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8901b94-884e-4a7c-ad5b-e626255f1010",
   "metadata": {},
   "outputs": [],
   "source": [
    "new311_gdf.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "782524b4-e904-4d3f-81ee-0d87d08fec7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "new311_regions_gdf = new311_gdf.copy()\n",
    "\n",
    "#new311_regions_gdf = dt_to_object(new311_regions_gdf)\n",
    "\n",
    "#new311_regions_gdf.to_file('../data/311/clean311-regions.shp')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6116bcf0-5377-4526-bd89-210bf4aaf5cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "new311_regions_gdf.to_parquet('../data/311/clean311-regions.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "639f3407-5735-46e4-ae9b-f768aed746b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#new311_regions_gdf.to_file('../data/311/clean311-regions.geojson', driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36afa664-b231-415a-93b0-8b5d07432264",
   "metadata": {},
   "source": [
    "# 3 - ipyleaflet Information\n",
    "\n",
    "This information is used upstream to build markers for the ipyleaflet (and/or folium) map.  The two values I'm adding is color (based on colors from H4LA) and the HTML text string for a popup.\n",
    "\n",
    "**Note** - 1) I'm using a different approach to the apply function for this.  In this one I'm returing a series with the two columns so I only iterate on the gdf once; 2)Need to refactor for the marker function.  Can't save markers so this needs to be a mapping type function on the gdf."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "111d0b49-069f-4951-93c7-eab22b9eb9e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def popup_message(row):\n",
    "    sr_number = row['SRNumber']\n",
    "    request_type = row['request_type']\n",
    "    when = row['created_dt'].strftime(\"%m/%d/%Y\")\n",
    "    \n",
    "    dt = row['closed_dt']\n",
    "    if not(pd.isnull(dt)):\n",
    "        finished = dt.strftime(\"%m/%d/%Y\")\n",
    "    else:\n",
    "        finished = \"Still Open\"\n",
    "    \n",
    "    return f\"Report: {sr_number}<br>Request Type: {request_type}<br>When: {when}<br>Completed: {finished}\"\n",
    "\n",
    "def marker_color(row):\n",
    "    return marker_color_map[row['request_type']]\n",
    "\n",
    "def marker_info(row):\n",
    "    return pd.Series((marker_color(row), popup_message(row)))\n",
    "\n",
    "def marker(row):\n",
    "    marker = CircleMarker(location=(row.geometry.y, row.geometry.x), radius=5, stroke=False, fill_color=row.marker_color, fill_opacity=1.0)\n",
    "    marker.popup = HTML(row.popup_message)\n",
    "    \n",
    "    return marker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37d9ee3e-2e9e-4af9-ae0c-ef33a7b41dbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "new311_gdf.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1c522a8-fab3-4c8f-9660-1b0e02f13eb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "new311_gdf[['marker_color', 'popup_message']] = new311_gdf.apply(lambda row: marker_info(row), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8592cf23-3ea2-4fe7-b751-73988809eab4",
   "metadata": {},
   "outputs": [],
   "source": [
    "new311_gdf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97db3ed2-7b27-4dee-a21b-2b5fb8f84c58",
   "metadata": {},
   "source": [
    "# 4 - Save the Extended Dataframe\n",
    "\n",
    "Since this is a gdf we need to change the datetime because of ESRI driver.  Really need to refactor this approach.  I suspect I want to add a utility."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "343ab098-45bb-4fe7-922c-b5e658726a34",
   "metadata": {},
   "outputs": [],
   "source": [
    "#new311_gdf = dt_to_object(new311_gdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c709aa8-53ba-49b0-a0b0-4f0aad654204",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "new311_gdf.to_parquet('../data/311/extended311-geo-shape.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff9341c9-8783-4e65-aba4-35b1fb8175f1",
   "metadata": {},
   "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.10.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}