notebooks/02.2-311.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bfe8a332-8b79-47ff-bc1c-c7efccf52325",
   "metadata": {},
   "source": [
    "# 311 Processing\n",
    "\n",
    "This is a serious hack to get some specific 311 request data sets from a **very large** [dataset](https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9/data).\n",
    "\n",
    "There are different types of 311 requests, serviced by different agencies.  For starters I am processing the large data set into a selection of 311 requests by agency.  The agencies I'm including are:\n",
    "\n",
    "  - Department of Environmental Protection (DEP)\n",
    "  - Housing Preservation & Development (HPD)\n",
    "  - Department of Sanitation (DSNY)\n",
    "  - Department of Parks and Recreation (DPR)\n",
    "  - Department of Buildings (DOB)\n",
    "  - Department of Transporation (DOT)\n",
    "  \n",
    "The mechanics of this process are:\n",
    "\n",
    "  1. Download the csv file.\n",
    "  2. On my linux machine I use `split -d -l 5000000 <fname>.csv 311_split_part.` to create multiple files.\n",
    "  3. I read these (5) files, from the `raw directory`, into seperate pandas dataframes.\n",
    "  4. Perform various hacks to create subseted dataframes.\n",
    "  5. Use location info from the df to create a (geopandas) geodataframe.\n",
    "  6. Save, in the `processed directory`, each department specific df and gdf in parquet format.\n",
    "  \n",
    "As I said, **this is a seriously hacky approach**.\n",
    "\n",
    "In keeping with the hackyness, I am going to ignore warnings (1 - appending df's, and 2 - gpd.to_parquet).  What can I say!\n",
    "\n",
    "**Note:** Some of the pandas df's have NoneType's for Location so it passes right through so there are not valid geometries.  I will keep them and filter when needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "823c766a-db76-425b-887e-edfc511841c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def min_max(df):\n",
    "    print(df['Created Date'].max())\n",
    "    print(df['Created Date'].min())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "200e353a-062b-4b92-9d25-129c8201e4e2",
   "metadata": {},
   "source": [
    "So it takes 38 minutes to read (and convert dt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8b95c50-de14-4a04-8691-456fe62c816e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df00 = pd.read_csv('../data/raw/311/311_split_part.00', parse_dates=['Created Date'], low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94434838-cdf8-4544-9c44-71adc56a6cb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "col = list(df00.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5abfea42-c6f1-4983-874c-d760af056abb",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "#df00 = pd.read_csv('../data/raw/311/311_split_part.00', parse_dates=['Created Date'], low_memory=False)\n",
    "\n",
    "df01 = pd.read_csv('../data/raw/311/311_split_part.01', names=col, parse_dates=['Created Date'], low_memory=False)\n",
    "\n",
    "df02 = pd.read_csv('../data/raw/311/311_split_part.02', names=col, parse_dates=['Created Date'], low_memory=False)\n",
    "\n",
    "df03 = pd.read_csv('../data/raw/311/311_split_part.03', names=col, parse_dates=['Created Date'], low_memory=False)\n",
    "\n",
    "df04 = pd.read_csv('../data/raw/311/311_split_part.04', names=col, parse_dates=['Created Date'], low_memory=False)\n",
    "\n",
    "df05 = pd.read_csv('../data/raw/311/311_split_part.05', names=col, parse_dates=['Created Date'], low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "652ec9f1-40cc-4797-9e09-6853bd43fe72",
   "metadata": {},
   "outputs": [],
   "source": [
    "sum([len(x) for x in [df00, df01, df02, df03, df04, df05]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b722aea-d717-4519-9d2e-6adbd745d5d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_max(df00)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0237aff6-f279-49c3-83dc-846c03017896",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_max(df01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56af9309-499e-4266-a270-cd93759a2ae0",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_max(df02)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a740259-2bdb-46d4-a82e-4f273dc02313",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_max(df03)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c5fa6b7-0019-44ae-abaa-631c7326dc91",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_max(df04)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d6664a6-7576-4c0c-8658-fa5a5c7e2c56",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_max(df05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbd626c5-1264-4b94-a4f5-8777b75c3dab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7622ec7a-bdfc-49f4-b2e9-a15e98cd55d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df00.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60e34fad-940c-43a1-adc5-dd30b5746247",
   "metadata": {},
   "outputs": [],
   "source": [
    "df00['Agency'].value_counts()[:40]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23ea7d2c-170c-435d-bb78-bd44f42f2d9d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "059b812b-237e-42e4-903e-7427b34da45e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_by_agency(agency):\n",
    "    \"\"\"\n",
    "    Hard code this hummer!  Yikes!\n",
    "    \"\"\"\n",
    "    df0 = df00.loc[df00['Agency'] == agency]\n",
    "    df1 = df01.loc[df01['Agency'] == agency]\n",
    "    df2 = df02.loc[df02['Agency'] == agency]\n",
    "    df3 = df03.loc[df03['Agency'] == agency]\n",
    "    df4 = df04.loc[df04['Agency'] == agency]\n",
    "    df5 = df05.loc[df05['Agency'] == agency]\n",
    "    \n",
    "    return df0.append(df1, ignore_index=True).append(df2, ignore_index=True).append(df3, ignore_index=True).append(df4, ignore_index=True).append(df5, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed94956c-c4cc-4426-b150-300a6021cbac",
   "metadata": {},
   "source": [
    "# Dept of Environmental Protection (DEP)\n",
    "\n",
    "https://www1.nyc.gov/site/dep/index.page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db52f870-f906-4a54-bcdc-31b032116115",
   "metadata": {},
   "outputs": [],
   "source": [
    "dep_df = df_by_agency('DEP')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67281fbe-737d-4a90-a663-51673fe0fe96",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dep_df.info(verbose=True, show_counts=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1515a4e2-a2f8-46a5-bba7-5925b4e835bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "dep_df.to_parquet('../data/processed/311/dep-full.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0bca75a-a511-4e79-9f4c-6fdd3d8b7c83",
   "metadata": {},
   "outputs": [],
   "source": [
    "dep_311_gdf = gpd.GeoDataFrame(dep_df,\n",
    "                               geometry = [Point(x, y) for x, y in zip(dep_df.Longitude, dep_df.Latitude)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b3c2971-a1ec-4f91-a4ba-0b237c4f37f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "dep_311_gdf.to_parquet('../data/processed/311/dep-clean-geo.parq')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e764bda1-06c2-4c35-861a-5215032fd6d0",
   "metadata": {},
   "source": [
    "# Housing Preservation & Development (HPD)\n",
    "\n",
    "https://www1.nyc.gov/site/hpd/index.page "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0648aa4-7bd3-4c33-ba51-c1038b2c82e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "hpd_df = df_by_agency('HPD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b428367-2ecb-4ab6-be6a-991ef260458f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#hpd_df.info(verbose=True, show_counts=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c0af063-d762-412f-b88b-e82c1650b53c",
   "metadata": {},
   "outputs": [],
   "source": [
    "hpd_df.to_parquet('../data/processed/311/hpd-full.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c988843d-a463-47c1-9805-5a4659823a39",
   "metadata": {},
   "outputs": [],
   "source": [
    "hpd_311_gdf = gpd.GeoDataFrame(hpd_df,\n",
    "                               geometry = [Point(x, y) for x, y in zip(hpd_df.Longitude, hpd_df.Latitude)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d43aa9ce-8bed-48f3-8591-ec192768ec56",
   "metadata": {},
   "outputs": [],
   "source": [
    "hpd_311_gdf.to_parquet('../data/processed/311/hpd-clean-geo.parq')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c61f2ec7-0804-4809-b1b4-15575e5c40d8",
   "metadata": {},
   "source": [
    "# Department of Sanitation (DSNY)\n",
    "\n",
    "https://www1.nyc.gov/assets/dsny/site/home"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "327a8e26-c489-4cef-ba6e-eea71289ec4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "dsny_df = df_by_agency('DSNY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d404eae2-8f48-4422-bc67-e5923a4d8da2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dsny_df.info(verbose=True, show_counts=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ea2dc14-ab8c-412d-a2c4-8b28780df53d",
   "metadata": {},
   "outputs": [],
   "source": [
    "dsny_df.to_parquet('../data/processed/311/dsny-full.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b87e925-d45c-408d-8399-ed62afa2cc16",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dsny_df.info(verbose=True, show_counts=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e942d902-e473-414e-8a69-1a2e9ada356d",
   "metadata": {},
   "outputs": [],
   "source": [
    "dsny_311_gdf = gpd.GeoDataFrame(dsny_df,\n",
    "                               geometry = [Point(x, y) for x, y in zip(dsny_df.Longitude, dsny_df.Latitude)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ca7ec34-cd65-41ca-ac32-5f346a6520f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "dsny_311_gdf.to_parquet('../data/processed/311/dsny-clean-geo.parq')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dbece6f-ced1-4483-825c-b76b38804e60",
   "metadata": {},
   "source": [
    "# Department of Parks and Recreation (DPR)\n",
    "\n",
    "https://www.nycgovparks.org/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fba0fd02-86ac-4a12-bf82-489ca826e6ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "dpr_df = df_by_agency('DPR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1702e8df-dad2-4f76-8a1b-b05a329d117c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dpr_df.to_parquet('../data/processed/311/dpr-full.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96a7978e-3d55-4f8a-b4c2-ede6d438417b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dpr_311_gdf = gpd.GeoDataFrame(dpr_df,\n",
    "                               geometry = [Point(x, y) for x, y in zip(dpr_df.Longitude, dpr_df.Latitude)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "110016e6-c878-4b55-97da-8d1069153af4",
   "metadata": {},
   "outputs": [],
   "source": [
    "dpr_311_gdf.to_parquet('../data/processed/311/dpr-clean-geo.parq')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6ddb672-f34d-41b1-9039-34683840a873",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Department of Buildings (DOB)\n",
    "\n",
    "https://www1.nyc.gov/site/buildings/index.page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c41acb03-1372-45b0-8be7-7d18dfb81d73",
   "metadata": {},
   "outputs": [],
   "source": [
    "dob_df = df_by_agency('DOB')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5deb7af-199e-46c2-8eb0-5c43333c7334",
   "metadata": {},
   "outputs": [],
   "source": [
    "dob_df.to_parquet('../data/processed/311/dob-full.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e01efad3-1795-41b7-ac7d-f7993c49378b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dob_311_gdf = gpd.GeoDataFrame(dob_df,\n",
    "                               geometry = [Point(x, y) for x, y in zip(dob_df.Longitude, dob_df.Latitude)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26f1ee0e-87ef-4564-9cbd-a78c0f525bd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "dob_311_gdf.to_parquet('../data/processed/311/dob-clean-geo.parq')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43140d22-b78b-4343-bbfe-0f7c0f9a05b0",
   "metadata": {},
   "source": [
    "# Department of Transportation (DOT)\n",
    "\n",
    "https://www1.nyc.gov/html/dot/html/home/home.shtml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac8b824c-a08e-4c3a-9acd-2659af579fd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "dot_df = df_by_agency('DOT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89a8412b-c915-4a51-ab02-d896b6431b28",
   "metadata": {},
   "outputs": [],
   "source": [
    "dot_df.to_parquet('../data/processed/311/dot-full.parq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "083dfff4-74a6-4f6c-81b7-f689b7e97936",
   "metadata": {},
   "outputs": [],
   "source": [
    "dot_311_gdf = gpd.GeoDataFrame(dot_df,\n",
    "                               geometry = [Point(x, y) for x, y in zip(dot_df.Longitude, dot_df.Latitude)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "458b83da-1215-46a1-885a-7512e8d15c2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dot_311_gdf.to_parquet('../data/processed/311/dot-clean-geo.parq')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28042f1a-a1e8-4cc3-a0aa-626d3261eb56",
   "metadata": {},
   "source": [
    "# Summary\n",
    "\n",
    "This noteboook implements a very mechanical process to process 311 data.\n",
    "\n",
    "The most recent dataset I've used was pulled on April 25, 2022.\n",
    "\n",
    "Subsequently in my analysis I noticed a department (DEP) dataset.  Not sure if all departments have them?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa5fa3db-2661-4972-84be-af36baa8e14f",
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}