NYCDOE_Space_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "NYCDOE Space Analysis.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyMeETSQ/U0g0vC4Up02K3HX",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/aankit/nycdoe_space_analysis/blob/master/NYCDOE_Space_Analysis.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YkCxJF4-HIlo",
        "colab_type": "text"
      },
      "source": [
        "##Understanding the Physical Space of the Largest School District in the United States\n",
        "\n",
        "New York City has the largest school district in the country based on the number of students it serves. The COVID-19 pandemic has asked us all to be more cognizant of our physical space whether it be staying 6 feet apart in public or staying sane at home, especially if you live in a tiny NYC apartment.\n",
        "\n",
        "###How much physical space does the largest school district in the country possess to serve its students? Why hasn't this question and the associated analysis been something we are all reading about as the NYC Department of Education plans for the 2020-21 school year?\n",
        "\n",
        "I broke this larger question into four smaller questions and analyses. Feel free to reach out to me at hi@aankit.com if you have questions or thoughts!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lAu71x0Kgn72",
        "colab_type": "text"
      },
      "source": [
        "###Question 1: Does publicly available data about the physical space of schools exist?\n",
        "\n",
        "###Answer: Yes! Below I point out and load up some data sources for my analysis.\n",
        "\n",
        "First I'm going to install and load some helpful tools :)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "j5rN0jVmyO2Q",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!pip install geopandas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "do4265X6gdO2",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from __future__ import print_function\n",
        "import requests\n",
        "import pandas as pd\n",
        "import io\n",
        "import geopandas as gpd"
      ],
      "execution_count": 41,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0WxYNtUMJF4S",
        "colab_type": "text"
      },
      "source": [
        "DOE Building Space Usage is available on the NYC Open Data Portal [here](https://data.cityofnewyork.us/Education/DOE-Building-Space-Usage/wavz-fkw8). I'm using the CSV because the [JSON API endpoint](https://data.cityofnewyork.us/resource/wavz-fkw8.json) only yielded 1000 rows and I'm too lazy to figure out why."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "czk8SWuTgmNy",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "response = requests.get(\"https://data.cityofnewyork.us/api/views/wavz-fkw8/rows.csv?accessType=DOWNLOAD\")\n",
        "school_space = pd.read_csv(io.StringIO(response.text))"
      ],
      "execution_count": 73,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TA_50SmhqKuM",
        "colab_type": "text"
      },
      "source": [
        "We will need more information schools - like what grades they serve and where they are located for a proper analysis. \n",
        "\n",
        "DOE School Information is available via a file called \"LCGMS\" [here](https://data.cityofnewyork.us/Education/LCGMS-DOE-School-Information-Report/3bkj-34v2). I'm using the [CSV with additional geocoded fields](https://data.cityofnewyork.us/api/views/3bkj-34v2/files/56813139-9b9d-44fb-b81d-068553b7a9b7?download=true&filename=LCGMS_SchoolData(additional%20geocoded%20fields%20added).csv)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BlSAvYpcTqoS",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "response = requests.get(\"https://data.cityofnewyork.us/api/views/3bkj-34v2/files/56813139-9b9d-44fb-b81d-068553b7a9b7?download=true&filename=LCGMS_SchoolData(additional geocoded fields added).csv\")\n",
        "lcgms = pd.read_csv(io.StringIO(response.text))"
      ],
      "execution_count": 51,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gmludn04zeyK",
        "colab_type": "text"
      },
      "source": [
        "And finally we will want to look at all of this on a map, so let's load a geospatial file of school zones from [here](https://data.cityofnewyork.us/Education/2019-2020-School-Zones-Elementary-/kuk3-ypca). I'm using the shapefile.\n",
        "\n",
        "This is only relevant for elementary schools (and maybe some middle schools)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uy65wdqlu3sQ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "response = requests.get(\"https://data.cityofnewyork.us/api/geospatial/kuk3-ypca?method=export&format=GeoJSON\")\n",
        "school_zones = pd.DataFrame(response.json())"
      ],
      "execution_count": 52,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UHXlsS1j5PXy",
        "colab_type": "text"
      },
      "source": [
        "###Question 2: What areas of a school are instructional areas?\n",
        "\n",
        "The data dictionary provided by the School Construction Authority on the  NYC Open Data Portal is helpful. It tells us that the DOE Building Space data we pulled in and named `school_space` has a `Room Function` column, or field. Let's get a count of the values in the `Room Function` field."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KSBCCdo06_C8",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 221
        },
        "outputId": "61270990-3304-4c67-85c6-99777f89dd80"
      },
      "source": [
        "school_space[\"Room Function\"].value_counts()"
      ],
      "execution_count": 78,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "REGULAR CLASSROOM                70581\n",
              "STORAGE ROOM                     46699\n",
              "OTHER OFFICE                     36936\n",
              "GENERAL BUILDING SUPPORT         31838\n",
              "REGULAR CLASSROOM - MS GRADES    19807\n",
              "                                 ...  \n",
              "ELEVENTH GRADE                      94\n",
              "GYM/AUD/CAFETERIA                   89\n",
              "DRAFTING ROOM                       73\n",
              "TWELFTH GRADE                       51\n",
              "NEST NINTH-TWELFTH GRADE            13\n",
              "Name: Room Function, Length: 109, dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 78
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8Fp6CN-X_zJw",
        "colab_type": "text"
      },
      "source": [
        "Based on this quick overview, if `Room Function` contains the word \"classroom\" or \"grade\" it can probably be classified as an instructional space."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tHDCIpVepubg",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 544
        },
        "outputId": "1b9663be-e7c4-40db-a884-116d1ee5db30"
      },
      "source": [
        "instructional_school_space = school_space[school_space[\"Room Function\"].str.contains(\"CLASSROOM|GRADE\", na=False)]\n",
        "instructional_school_space[\"Room Function\"].value_counts()"
      ],
      "execution_count": 93,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "REGULAR CLASSROOM                   70581\n",
              "REGULAR CLASSROOM - MS GRADES       19807\n",
              "D75 SPED CLASSROOM                  15482\n",
              "NON-D75 SPED CLASSROOM              15481\n",
              "FIRST GRADE                         13521\n",
              "SECOND GRADE                        12696\n",
              "THIRD GRADE                         12193\n",
              "FOURTH GRADE                        11622\n",
              "FIFTH GRADE                         11369\n",
              "ICT - ELEMENTARY SCHOOL GRADES      10215\n",
              "REGULAR CLASSROOM - HS GRADES        3813\n",
              "MULTI-PURPOSE CLASSROOM              2544\n",
              "SCIENCE CLASSROOM FOR PS             2028\n",
              "SIXTH GRADE                          1644\n",
              "ICT - MIDDLE SCHOOL GRADES           1558\n",
              "SEVENTH GRADE                        1526\n",
              "EIGHTH GRADE                         1482\n",
              "MULTI-PURPOSE NON CLASSROOM          1177\n",
              "NEST FIRST-THIRD GRADE                452\n",
              "ICT - HIGH SCHOOL GRADES              424\n",
              "NEST SIXTH-EIGHTH GRADE               407\n",
              "NEST FOURTH-FIFTH GRADE               248\n",
              "NINTH GRADE                           236\n",
              "HORIZON SECOND-TWELFTH GRADE          216\n",
              "MULTI-PURPOSE (NON CLASSROOM)         178\n",
              "TENTH GRADE                           133\n",
              "HORIZON KINDERGARTEN-FIRST GRADE       95\n",
              "ELEVENTH GRADE                         94\n",
              "TWELFTH GRADE                          51\n",
              "NEST NINTH-TWELFTH GRADE               13\n",
              "Name: Room Function, dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 93
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zt5I9fANEgLd",
        "colab_type": "text"
      },
      "source": [
        "Let's take a look at the square footage of this instructional space. This view is aggregating across schools, however. The next step is to drill down to the school level before we link schools to the communities they serve."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jsRAvvDKEm8L",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "46aec055-8aca-4183-ce0e-619b3d9885b5"
      },
      "source": [
        "instructional_school_space.groupby(\"Room Function\").sum()"
      ],
      "execution_count": 107,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Length</th>\n",
              "      <th>Width</th>\n",
              "      <th>Area</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Room Function</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>D75 SPED CLASSROOM</th>\n",
              "      <td>372679.0</td>\n",
              "      <td>345510.0</td>\n",
              "      <td>8302789.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>EIGHTH GRADE</th>\n",
              "      <td>37973.0</td>\n",
              "      <td>40526.0</td>\n",
              "      <td>1031246.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ELEVENTH GRADE</th>\n",
              "      <td>2529.0</td>\n",
              "      <td>2424.0</td>\n",
              "      <td>64696.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>FIFTH GRADE</th>\n",
              "      <td>281419.0</td>\n",
              "      <td>300719.0</td>\n",
              "      <td>7362002.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>FIRST GRADE</th>\n",
              "      <td>339764.0</td>\n",
              "      <td>352957.0</td>\n",
              "      <td>8773473.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>FOURTH GRADE</th>\n",
              "      <td>287273.0</td>\n",
              "      <td>306075.0</td>\n",
              "      <td>7479172.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>HORIZON KINDERGARTEN-FIRST GRADE</th>\n",
              "      <td>2509.0</td>\n",
              "      <td>2243.0</td>\n",
              "      <td>58305.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>HORIZON SECOND-TWELFTH GRADE</th>\n",
              "      <td>5293.0</td>\n",
              "      <td>5087.0</td>\n",
              "      <td>127132.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ICT - ELEMENTARY SCHOOL GRADES</th>\n",
              "      <td>255937.0</td>\n",
              "      <td>269540.0</td>\n",
              "      <td>6657939.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ICT - HIGH SCHOOL GRADES</th>\n",
              "      <td>11298.0</td>\n",
              "      <td>10321.0</td>\n",
              "      <td>275810.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ICT - MIDDLE SCHOOL GRADES</th>\n",
              "      <td>39847.0</td>\n",
              "      <td>41608.0</td>\n",
              "      <td>1054611.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MULTI-PURPOSE (NON CLASSROOM)</th>\n",
              "      <td>6833.0</td>\n",
              "      <td>5619.0</td>\n",
              "      <td>246310.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MULTI-PURPOSE CLASSROOM</th>\n",
              "      <td>68265.0</td>\n",
              "      <td>66168.0</td>\n",
              "      <td>1848198.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MULTI-PURPOSE NON CLASSROOM</th>\n",
              "      <td>49388.0</td>\n",
              "      <td>38154.0</td>\n",
              "      <td>1819547.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>NEST FIRST-THIRD GRADE</th>\n",
              "      <td>11076.0</td>\n",
              "      <td>11788.0</td>\n",
              "      <td>286340.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>NEST FOURTH-FIFTH GRADE</th>\n",
              "      <td>6080.0</td>\n",
              "      <td>6713.0</td>\n",
              "      <td>163211.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>NEST NINTH-TWELFTH GRADE</th>\n",
              "      <td>326.0</td>\n",
              "      <td>345.0</td>\n",
              "      <td>8582.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>NEST SIXTH-EIGHTH GRADE</th>\n",
              "      <td>10400.0</td>\n",
              "      <td>10289.0</td>\n",
              "      <td>260266.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>NINTH GRADE</th>\n",
              "      <td>6424.0</td>\n",
              "      <td>5925.0</td>\n",
              "      <td>161984.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>NON-D75 SPED CLASSROOM</th>\n",
              "      <td>365367.0</td>\n",
              "      <td>355332.0</td>\n",
              "      <td>8349564.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>REGULAR CLASSROOM</th>\n",
              "      <td>1901768.0</td>\n",
              "      <td>1779814.0</td>\n",
              "      <td>47734113.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>REGULAR CLASSROOM - HS GRADES</th>\n",
              "      <td>103691.0</td>\n",
              "      <td>96231.0</td>\n",
              "      <td>2600965.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>REGULAR CLASSROOM - MS GRADES</th>\n",
              "      <td>506534.0</td>\n",
              "      <td>517670.0</td>\n",
              "      <td>13123663.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SCIENCE CLASSROOM FOR PS</th>\n",
              "      <td>51647.0</td>\n",
              "      <td>54477.0</td>\n",
              "      <td>1365878.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SECOND GRADE</th>\n",
              "      <td>313395.0</td>\n",
              "      <td>332399.0</td>\n",
              "      <td>8118814.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SEVENTH GRADE</th>\n",
              "      <td>39299.0</td>\n",
              "      <td>41212.0</td>\n",
              "      <td>1056142.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SIXTH GRADE</th>\n",
              "      <td>42129.0</td>\n",
              "      <td>44198.0</td>\n",
              "      <td>1125647.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>TENTH GRADE</th>\n",
              "      <td>3694.0</td>\n",
              "      <td>3584.0</td>\n",
              "      <td>100590.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>THIRD GRADE</th>\n",
              "      <td>301141.0</td>\n",
              "      <td>320391.0</td>\n",
              "      <td>7827933.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>TWELFTH GRADE</th>\n",
              "      <td>1292.0</td>\n",
              "      <td>1242.0</td>\n",
              "      <td>31873.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                     Length      Width        Area\n",
              "Room Function                                                     \n",
              "D75 SPED CLASSROOM                 372679.0   345510.0   8302789.0\n",
              "EIGHTH GRADE                        37973.0    40526.0   1031246.0\n",
              "ELEVENTH GRADE                       2529.0     2424.0     64696.0\n",
              "FIFTH GRADE                        281419.0   300719.0   7362002.0\n",
              "FIRST GRADE                        339764.0   352957.0   8773473.0\n",
              "FOURTH GRADE                       287273.0   306075.0   7479172.0\n",
              "HORIZON KINDERGARTEN-FIRST GRADE     2509.0     2243.0     58305.0\n",
              "HORIZON SECOND-TWELFTH GRADE         5293.0     5087.0    127132.0\n",
              "ICT - ELEMENTARY SCHOOL GRADES     255937.0   269540.0   6657939.0\n",
              "ICT - HIGH SCHOOL GRADES            11298.0    10321.0    275810.0\n",
              "ICT - MIDDLE SCHOOL GRADES          39847.0    41608.0   1054611.0\n",
              "MULTI-PURPOSE (NON CLASSROOM)        6833.0     5619.0    246310.0\n",
              "MULTI-PURPOSE CLASSROOM             68265.0    66168.0   1848198.0\n",
              "MULTI-PURPOSE NON CLASSROOM         49388.0    38154.0   1819547.0\n",
              "NEST FIRST-THIRD GRADE              11076.0    11788.0    286340.0\n",
              "NEST FOURTH-FIFTH GRADE              6080.0     6713.0    163211.0\n",
              "NEST NINTH-TWELFTH GRADE              326.0      345.0      8582.0\n",
              "NEST SIXTH-EIGHTH GRADE             10400.0    10289.0    260266.0\n",
              "NINTH GRADE                          6424.0     5925.0    161984.0\n",
              "NON-D75 SPED CLASSROOM             365367.0   355332.0   8349564.0\n",
              "REGULAR CLASSROOM                 1901768.0  1779814.0  47734113.0\n",
              "REGULAR CLASSROOM - HS GRADES      103691.0    96231.0   2600965.0\n",
              "REGULAR CLASSROOM - MS GRADES      506534.0   517670.0  13123663.0\n",
              "SCIENCE CLASSROOM FOR PS            51647.0    54477.0   1365878.0\n",
              "SECOND GRADE                       313395.0   332399.0   8118814.0\n",
              "SEVENTH GRADE                       39299.0    41212.0   1056142.0\n",
              "SIXTH GRADE                         42129.0    44198.0   1125647.0\n",
              "TENTH GRADE                          3694.0     3584.0    100590.0\n",
              "THIRD GRADE                        301141.0   320391.0   7827933.0\n",
              "TWELFTH GRADE                        1292.0     1242.0     31873.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 107
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fzT0UOtDDslZ",
        "colab_type": "text"
      },
      "source": [
        "###Question 3: Can we determine each school's square footage per person?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x6cAhtX1ENmV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gyon7vOsD6vy",
        "colab_type": "text"
      },
      "source": [
        "### Question 4: Can we map each school's square footage per person to get an idea of the impact on different communities? "
      ]
    }
  ]
}