IN_CORE_2dv2_Lumberton_AddressPointInventory.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "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.7.10"
    },
    "colab": {
      "name": "IN-CORE_2dv2_Lumberton_AddressPointInventory_2021-04-27.ipynb",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/npr99/IN-CORE_notebooks/blob/main/IN_CORE_2dv2_Lumberton_AddressPointInventory.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P1LR-ILVugSk"
      },
      "source": [
        "## Read in and clean Lumberton Address Point file\n",
        "This program reads in the Address Point Inventory and makes sure it is ready for IN-CORE.\n",
        "\n",
        "Cleaning steps include:\n",
        "1. Check Unique ID - non-missing primary key\n",
        "2. Check projection\n",
        "3. Save as CSV file with Well Know Text (WKT) polygon information"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EMyE_CYPugSl",
        "outputId": "c9044a3a-40d6-482f-fb63-dd9b303520b6"
      },
      "source": [
        "%matplotlib inline\n",
        "\n",
        "import matplotlib.pyplot as plt\n",
        "import math as math\n",
        "import numpy as np\n",
        "import geopandas as gpd\n",
        "import pandas as pd\n",
        "import shapely\n",
        "import folium as fm # folium has more dynamic maps - but requires internet connection\n",
        "import os # For saving output to path"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "C:\\Users\\nathanael99\\AppData\\Roaming\\Python\\Python37\\site-packages\\requests\\__init__.py:91: RequestsDependencyWarning: urllib3 (1.26.4) or chardet (3.0.4) doesn't match a supported version!\n",
            "  RequestsDependencyWarning)\n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3Fs0kAxTugSn",
        "outputId": "fe453f36-b4de-4ec3-dc7b-120026cf3bc0"
      },
      "source": [
        "# Display versions being used - important information for replication\n",
        "import sys\n",
        "print(\"Python Version     \", sys.version)\n",
        "print(\"numpy version:     \", np.__version__)\n",
        "print(\"geopandas version: \", gpd.__version__)\n",
        "print(\"pandas version:    \", pd.__version__)\n",
        "print(\"shapely version:   \", shapely.__version__)\n",
        "print(\"folium version:    \", fm.__version__)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Python Version      3.7.10 | packaged by conda-forge | (default, Feb 19 2021, 15:37:01) [MSC v.1916 64 bit (AMD64)]\n",
            "numpy version:      1.20.2\n",
            "geopandas version:  0.9.0\n",
            "pandas version:     0.24.2\n",
            "shapely version:    1.7.1\n",
            "folium version:     0.9.1\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iOCkN9QougSn"
      },
      "source": [
        "# Store Program Name for output files to have the same name\n",
        "programname = \"IN-CORE_2dv2_Lumberton_AddressPointInventory_2021-04-27\"\n",
        "# Make directory to save output\n",
        "if not os.path.exists(programname):\n",
        "    os.mkdir(programname)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3MNz0LL7ugSn"
      },
      "source": [
        "## Read in Building Inventory File\n",
        "Read in CSV file created by the program \"IN-CORE_1dv2_[]_EstimateAddressPoints_[]\"\n",
        "\n",
        "This file contains each building with an estimate for the number of housing units in each building. \n",
        "\n",
        "The estimate of housing units provides some information to deterine the number of address points in each building.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gFyVAwxrugSo",
        "outputId": "8d33167b-3ca6-44cc-cfae-27e5cf91e8ea"
      },
      "source": [
        "source_program = \"IN-CORE_1dv2_Lumberton_EstimateAddressPoints_2021-04-15\"\n",
        "building_csv = source_program+\"/\"+source_program+\"_huestimate_EPSG4326.csv\"\n",
        "building_df = pd.read_csv(building_csv, dtype={'blockid': object})\n",
        "building_df.head()"
      ],
      "execution_count": null,
      "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>Unnamed: 0</th>\n",
              "      <th>Unnamed: 0.1</th>\n",
              "      <th>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>STATEFP10</th>\n",
              "      <th>COUNTYFP10</th>\n",
              "      <th>TRACTCE10</th>\n",
              "      <th>PUMGEOID10</th>\n",
              "      <th>PUMNAME10</th>\n",
              "      <th>PLCGEOID10</th>\n",
              "      <th>...</th>\n",
              "      <th>ErrorCheck1</th>\n",
              "      <th>Res_Area</th>\n",
              "      <th>Sum_Res_Area</th>\n",
              "      <th>residentialAP2</th>\n",
              "      <th>residentialAP2v2</th>\n",
              "      <th>residentialAP2v2_sum</th>\n",
              "      <th>bldgcountv2_sum</th>\n",
              "      <th>DiffCount2</th>\n",
              "      <th>ErrorCheck2</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>2d32aeff-7b75-47e6-b7a5-4f4adca4b021</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>37.0</td>\n",
              "      <td>155.0</td>\n",
              "      <td>961301.0</td>\n",
              "      <td>3705100.0</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "      <td>3739700.0</td>\n",
              "      <td>...</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>0</td>\n",
              "      <td>60596</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>0.0</td>\n",
              "      <td>82.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>11462</td>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>37.0</td>\n",
              "      <td>155.0</td>\n",
              "      <td>961301.0</td>\n",
              "      <td>3705100.0</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "      <td>3739700.0</td>\n",
              "      <td>...</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>3434</td>\n",
              "      <td>60596</td>\n",
              "      <td>3.910258</td>\n",
              "      <td>5.0</td>\n",
              "      <td>82.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>6.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>14415</td>\n",
              "      <td>b7b5e4ce-431f-4e7d-8a8a-619a9c205571</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>37.0</td>\n",
              "      <td>155.0</td>\n",
              "      <td>961301.0</td>\n",
              "      <td>3705100.0</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "      <td>3739700.0</td>\n",
              "      <td>...</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>5685</td>\n",
              "      <td>60596</td>\n",
              "      <td>6.473447</td>\n",
              "      <td>7.0</td>\n",
              "      <td>82.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>14416</td>\n",
              "      <td>47fcaa3f-8590-4f7c-8764-405acb671b19</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>37.0</td>\n",
              "      <td>155.0</td>\n",
              "      <td>961301.0</td>\n",
              "      <td>3705100.0</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "      <td>3739700.0</td>\n",
              "      <td>...</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>2412</td>\n",
              "      <td>60596</td>\n",
              "      <td>2.746518</td>\n",
              "      <td>4.0</td>\n",
              "      <td>82.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>14417</td>\n",
              "      <td>63d1d4df-db7b-40f6-bace-a70fbb0466b1</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>37.0</td>\n",
              "      <td>155.0</td>\n",
              "      <td>961301.0</td>\n",
              "      <td>3705100.0</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "      <td>3739700.0</td>\n",
              "      <td>...</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>2976</td>\n",
              "      <td>60596</td>\n",
              "      <td>3.388739</td>\n",
              "      <td>4.0</td>\n",
              "      <td>82.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>4. HU&gt;AP</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 63 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "   Unnamed: 0  Unnamed: 0.1                                  guid  \\\n",
              "0           0             0  2d32aeff-7b75-47e6-b7a5-4f4adca4b021   \n",
              "1           1         11462  400586f1-a265-4454-ba08-30eda28b974c   \n",
              "2           2         14415  b7b5e4ce-431f-4e7d-8a8a-619a9c205571   \n",
              "3           3         14416  47fcaa3f-8590-4f7c-8764-405acb671b19   \n",
              "4           4         14417  63d1d4df-db7b-40f6-bace-a70fbb0466b1   \n",
              "\n",
              "         BLOCKID10  STATEFP10  COUNTYFP10  TRACTCE10  PUMGEOID10  \\\n",
              "0  371559613011113       37.0       155.0   961301.0   3705100.0   \n",
              "1  371559613011113       37.0       155.0   961301.0   3705100.0   \n",
              "2  371559613011113       37.0       155.0   961301.0   3705100.0   \n",
              "3  371559613011113       37.0       155.0   961301.0   3705100.0   \n",
              "4  371559613011113       37.0       155.0   961301.0   3705100.0   \n",
              "\n",
              "                                    PUMNAME10  PLCGEOID10  ... ErrorCheck1  \\\n",
              "0  Robeson County (West)--Lumberton City PUMA   3739700.0  ...    4. HU>AP   \n",
              "1  Robeson County (West)--Lumberton City PUMA   3739700.0  ...    4. HU>AP   \n",
              "2  Robeson County (West)--Lumberton City PUMA   3739700.0  ...    4. HU>AP   \n",
              "3  Robeson County (West)--Lumberton City PUMA   3739700.0  ...    4. HU>AP   \n",
              "4  Robeson County (West)--Lumberton City PUMA   3739700.0  ...    4. HU>AP   \n",
              "\n",
              "   Res_Area  Sum_Res_Area  residentialAP2  residentialAP2v2  \\\n",
              "0         0         60596        0.000000               0.0   \n",
              "1      3434         60596        3.910258               5.0   \n",
              "2      5685         60596        6.473447               7.0   \n",
              "3      2412         60596        2.746518               4.0   \n",
              "4      2976         60596        3.388739               4.0   \n",
              "\n",
              "   residentialAP2v2_sum  bldgcountv2_sum  DiffCount2  ErrorCheck2  \\\n",
              "0                  82.0             25.0         2.0     4. HU>AP   \n",
              "1                  82.0             25.0         2.0     4. HU>AP   \n",
              "2                  82.0             25.0         2.0     4. HU>AP   \n",
              "3                  82.0             25.0         2.0     4. HU>AP   \n",
              "4                  82.0             25.0         2.0     4. HU>AP   \n",
              "\n",
              "   residentialAP3v1  \n",
              "0               0.0  \n",
              "1               6.0  \n",
              "2               8.0  \n",
              "3               5.0  \n",
              "4               5.0  \n",
              "\n",
              "[5 rows x 63 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iyC4SKA5ugSo"
      },
      "source": [
        "## Check Unique ID\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aX21jDcSugSo",
        "outputId": "5a144786-6719-4ff1-b318-a494786fb30f"
      },
      "source": [
        "# Confirm Primary Key is Unique and Non-Missing\n",
        "building_df.guid.describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count                                    20091\n",
              "unique                                   20091\n",
              "top       853ae178-f224-4c96-a127-cf5af89396bd\n",
              "freq                                         1\n",
              "Name: guid, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2bFn2qrougSp"
      },
      "source": [
        "## Keep primary columns\n",
        "The building inventory with estimates of housing units has many columns but only a few are needed to generate the address point inventory. The variable residentialAP3v1 provides the estimate for the number of housing units in the building."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kjdTcUQvugSp",
        "outputId": "f87b45e0-523d-4439-ed92-0b25a3acf8a4"
      },
      "source": [
        "select_cols = ['guid','BLOCKID10','geometry','residentialAP3v1']\n",
        "building_df_cols = building_df[select_cols]\n",
        "building_df_cols.head()"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2d32aeff-7b75-47e6-b7a5-4f4adca4b021</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99633432765292 34.6543576103806)</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>b7b5e4ce-431f-4e7d-8a8a-619a9c205571</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.9964649803694 34.65469315140241)</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>47fcaa3f-8590-4f7c-8764-405acb671b19</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99692677846114 34.65433561263469)</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>63d1d4df-db7b-40f6-bace-a70fbb0466b1</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99688850436857 34.65469345430336)</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                   guid        BLOCKID10  \\\n",
              "0  2d32aeff-7b75-47e6-b7a5-4f4adca4b021  371559613011113   \n",
              "1  400586f1-a265-4454-ba08-30eda28b974c  371559613011113   \n",
              "2  b7b5e4ce-431f-4e7d-8a8a-619a9c205571  371559613011113   \n",
              "3  47fcaa3f-8590-4f7c-8764-405acb671b19  371559613011113   \n",
              "4  63d1d4df-db7b-40f6-bace-a70fbb0466b1  371559613011113   \n",
              "\n",
              "                                       geometry  residentialAP3v1  \n",
              "0   POINT (-78.99633432765292 34.6543576103806)               0.0  \n",
              "1  POINT (-78.99711777506781 34.65410219535778)               6.0  \n",
              "2   POINT (-78.9964649803694 34.65469315140241)               8.0  \n",
              "3  POINT (-78.99692677846114 34.65433561263469)               5.0  \n",
              "4  POINT (-78.99688850436857 34.65469345430336)               5.0  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fT5hEKw8ugSp"
      },
      "source": [
        "# Read in Residential Address Point Count File\n",
        "The residential address point count file is based on the 2010 Census count of housing units and group quarters. This file provides the basis for estimating the number of housing units in buildings.\n",
        "\n",
        "The address point inventory will be a combination of the Building Inventory and the Residenital Address Point Count file."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L50wlcaNugSq",
        "outputId": "66cbcde5-afc8-49e9-92c6-2fabcb6619aa"
      },
      "source": [
        "# Read in Census Block Data\n",
        "source_program = 'IN-CORE_1av2_Lumberton_CleanBlockData_2021-04-15'\n",
        "census_blocks_csv = source_program+\"/\"+source_program+\"EPSG4269.csv\"\n",
        "census_blocks_df = pd.read_csv(census_blocks_csv, dtype={'BLOCKID10': object,'COUNTYFP10': str})\n",
        "census_blocks_df.head()"
      ],
      "execution_count": null,
      "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>Unnamed: 0</th>\n",
              "      <th>STATEFP10</th>\n",
              "      <th>COUNTYFP10</th>\n",
              "      <th>TRACTCE10</th>\n",
              "      <th>BLOCKCE</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>PARTFLG</th>\n",
              "      <th>HOUSING10</th>\n",
              "      <th>POP10</th>\n",
              "      <th>geometry</th>\n",
              "      <th>...</th>\n",
              "      <th>blockid</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>popcount</th>\n",
              "      <th>HU100</th>\n",
              "      <th>POP100</th>\n",
              "      <th>popdiff</th>\n",
              "      <th>PLCGEOID10</th>\n",
              "      <th>PLCNAME10</th>\n",
              "      <th>PUMGEOID10</th>\n",
              "      <th>PUMNAME10</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>37</td>\n",
              "      <td>155</td>\n",
              "      <td>961900</td>\n",
              "      <td>2028</td>\n",
              "      <td>371559619002028</td>\n",
              "      <td>N</td>\n",
              "      <td>14</td>\n",
              "      <td>52</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>...</td>\n",
              "      <td>371559619002028</td>\n",
              "      <td>14</td>\n",
              "      <td>51</td>\n",
              "      <td>14</td>\n",
              "      <td>52</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3705100</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>37</td>\n",
              "      <td>155</td>\n",
              "      <td>961900</td>\n",
              "      <td>2054</td>\n",
              "      <td>371559619002054</td>\n",
              "      <td>N</td>\n",
              "      <td>1</td>\n",
              "      <td>3</td>\n",
              "      <td>POLYGON ((-79.179851 34.40192, -79.180036 34.4...</td>\n",
              "      <td>...</td>\n",
              "      <td>371559619002054</td>\n",
              "      <td>1</td>\n",
              "      <td>3</td>\n",
              "      <td>1</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3705100</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>37</td>\n",
              "      <td>155</td>\n",
              "      <td>961700</td>\n",
              "      <td>2069</td>\n",
              "      <td>371559617002069</td>\n",
              "      <td>N</td>\n",
              "      <td>41</td>\n",
              "      <td>99</td>\n",
              "      <td>POLYGON ((-79.172814 34.480917, -79.172749 34....</td>\n",
              "      <td>...</td>\n",
              "      <td>371559617002069</td>\n",
              "      <td>41</td>\n",
              "      <td>99</td>\n",
              "      <td>41</td>\n",
              "      <td>99</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3705100</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>37</td>\n",
              "      <td>155</td>\n",
              "      <td>961700</td>\n",
              "      <td>2065</td>\n",
              "      <td>371559617002065</td>\n",
              "      <td>N</td>\n",
              "      <td>6</td>\n",
              "      <td>22</td>\n",
              "      <td>POLYGON ((-79.15764299999999 34.503279, -79.15...</td>\n",
              "      <td>...</td>\n",
              "      <td>371559617002065</td>\n",
              "      <td>6</td>\n",
              "      <td>21</td>\n",
              "      <td>6</td>\n",
              "      <td>22</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3705100</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>37</td>\n",
              "      <td>155</td>\n",
              "      <td>961700</td>\n",
              "      <td>2058</td>\n",
              "      <td>371559617002058</td>\n",
              "      <td>N</td>\n",
              "      <td>19</td>\n",
              "      <td>55</td>\n",
              "      <td>POLYGON ((-79.15830299999999 34.497355, -79.15...</td>\n",
              "      <td>...</td>\n",
              "      <td>371559617002058</td>\n",
              "      <td>19</td>\n",
              "      <td>55</td>\n",
              "      <td>19</td>\n",
              "      <td>55</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3705100</td>\n",
              "      <td>Robeson County (West)--Lumberton City PUMA</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 23 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "   Unnamed: 0  STATEFP10 COUNTYFP10  TRACTCE10  BLOCKCE        BLOCKID10  \\\n",
              "0           0         37        155     961900     2028  371559619002028   \n",
              "1           1         37        155     961900     2054  371559619002054   \n",
              "2           2         37        155     961700     2069  371559617002069   \n",
              "3           3         37        155     961700     2065  371559617002065   \n",
              "4           4         37        155     961700     2058  371559617002058   \n",
              "\n",
              "  PARTFLG  HOUSING10  POP10  \\\n",
              "0       N         14     52   \n",
              "1       N          1      3   \n",
              "2       N         41     99   \n",
              "3       N          6     22   \n",
              "4       N         19     55   \n",
              "\n",
              "                                            geometry  ...          blockid  \\\n",
              "0  POLYGON ((-79.22246 34.458838, -79.222528 34.4...  ...  371559619002028   \n",
              "1  POLYGON ((-79.179851 34.40192, -79.180036 34.4...  ...  371559619002054   \n",
              "2  POLYGON ((-79.172814 34.480917, -79.172749 34....  ...  371559617002069   \n",
              "3  POLYGON ((-79.15764299999999 34.503279, -79.15...  ...  371559617002065   \n",
              "4  POLYGON ((-79.15830299999999 34.497355, -79.15...  ...  371559617002058   \n",
              "\n",
              "  tothupoints popcount  HU100  POP100  popdiff  PLCGEOID10  PLCNAME10  \\\n",
              "0          14       51     14      52        1         NaN        NaN   \n",
              "1           1        3      1       3        0         NaN        NaN   \n",
              "2          41       99     41      99        0         NaN        NaN   \n",
              "3           6       21      6      22        1         NaN        NaN   \n",
              "4          19       55     19      55        0         NaN        NaN   \n",
              "\n",
              "   PUMGEOID10                                   PUMNAME10  \n",
              "0     3705100  Robeson County (West)--Lumberton City PUMA  \n",
              "1     3705100  Robeson County (West)--Lumberton City PUMA  \n",
              "2     3705100  Robeson County (West)--Lumberton City PUMA  \n",
              "3     3705100  Robeson County (West)--Lumberton City PUMA  \n",
              "4     3705100  Robeson County (West)--Lumberton City PUMA  \n",
              "\n",
              "[5 rows x 23 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sSAQ8RyiugSq"
      },
      "source": [
        "## Keep primary columns\n",
        "The address point county file has many columns but only a few are needed to generate the address point inventory."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nolQ20G8ugSq",
        "outputId": "873db273-4e59-4c7b-8ad5-0bb241580952"
      },
      "source": [
        "# Check Columns\n",
        "cols = [col for col in census_blocks_df]\n",
        "cols"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Unnamed: 0',\n",
              " 'STATEFP10',\n",
              " 'COUNTYFP10',\n",
              " 'TRACTCE10',\n",
              " 'BLOCKCE',\n",
              " 'BLOCKID10',\n",
              " 'PARTFLG',\n",
              " 'HOUSING10',\n",
              " 'POP10',\n",
              " 'geometry',\n",
              " 'CountySelect',\n",
              " 'rppnt4269',\n",
              " 'blk104269',\n",
              " 'blockid',\n",
              " 'tothupoints',\n",
              " 'popcount',\n",
              " 'HU100',\n",
              " 'POP100',\n",
              " 'popdiff',\n",
              " 'PLCGEOID10',\n",
              " 'PLCNAME10',\n",
              " 'PUMGEOID10',\n",
              " 'PUMNAME10']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rQBn2jbBugSq",
        "outputId": "4ca190da-352c-4ee5-fc86-f0a653a5211c"
      },
      "source": [
        "select_cols = ['blockid','geometry','rppnt4269','tothupoints']\n",
        "census_blocks_df_cols = census_blocks_df[select_cols]\n",
        "census_blocks_df_cols.head()"
      ],
      "execution_count": null,
      "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>blockid</th>\n",
              "      <th>geometry</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>371559619002054</td>\n",
              "      <td>POLYGON ((-79.179851 34.40192, -79.180036 34.4...</td>\n",
              "      <td>POINT (-79.18141042208816 34.4060815)</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>371559617002069</td>\n",
              "      <td>POLYGON ((-79.172814 34.480917, -79.172749 34....</td>\n",
              "      <td>POINT (-79.16201616013601 34.4876855)</td>\n",
              "      <td>41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>371559617002065</td>\n",
              "      <td>POLYGON ((-79.15764299999999 34.503279, -79.15...</td>\n",
              "      <td>POINT (-79.16259555882354 34.5013035)</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>371559617002058</td>\n",
              "      <td>POLYGON ((-79.15830299999999 34.497355, -79.15...</td>\n",
              "      <td>POINT (-79.14702005235881 34.497883)</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "           blockid                                           geometry  \\\n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "1  371559619002054  POLYGON ((-79.179851 34.40192, -79.180036 34.4...   \n",
              "2  371559617002069  POLYGON ((-79.172814 34.480917, -79.172749 34....   \n",
              "3  371559617002065  POLYGON ((-79.15764299999999 34.503279, -79.15...   \n",
              "4  371559617002058  POLYGON ((-79.15830299999999 34.497355, -79.15...   \n",
              "\n",
              "                               rppnt4269  tothupoints  \n",
              "0  POINT (-79.22459088452018 34.4587885)           14  \n",
              "1  POINT (-79.18141042208816 34.4060815)            1  \n",
              "2  POINT (-79.16201616013601 34.4876855)           41  \n",
              "3  POINT (-79.16259555882354 34.5013035)            6  \n",
              "4   POINT (-79.14702005235881 34.497883)           19  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6iKUFyTYugSr"
      },
      "source": [
        "# Prepare Building Inventory to Expand Based on Housing Unit Estimate\n",
        "For the address point inventory to work there needs to be one observation for each possible housing unit. This means that for buildings that have muliple housing units there will be one address point for each housing unit.\n",
        "\n",
        "For places that do not have buildings but have people the address point inventory will provide details on housing units impacted outside of the study area."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8wvi8gEpugSr",
        "outputId": "ea69fe95-5f8e-4a0b-bf0e-8cebd796fa6c"
      },
      "source": [
        "# The residential address point estimate provides information on how many housing units are in each building.\n",
        "building_df_cols['residentialAP3v1'].describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    20090.000000\n",
              "mean         1.302140\n",
              "std          1.009192\n",
              "min          0.000000\n",
              "25%          1.000000\n",
              "50%          1.000000\n",
              "75%          2.000000\n",
              "max         41.000000\n",
              "Name: residentialAP3v1, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yaYLIDXuugSr",
        "outputId": "ebe51d03-8bcf-4d21-e7de-eccdbda8deab"
      },
      "source": [
        "# If the residentialAP3v1 is used to expand the dataset observations without residential address points will be lost.\n",
        "# To keep all buildings add an expand variable\n",
        "building_df_cols.loc[(building_df_cols['residentialAP3v1']==0),'expandvar'] = 1\n",
        "building_df_cols.loc[(building_df_cols['residentialAP3v1']>0),'expandvar'] = building_df_cols['residentialAP3v1']\n",
        "# Check to make sure expand variable was generated correctly\n",
        "pd.crosstab(building_df_cols['expandvar'].loc[building_df_cols['expandvar']<=3],\n",
        "            building_df_cols['residentialAP3v1'], margins=True, margins_name=\"Total\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "C:\\Users\\nathanael99\\AppData\\Roaming\\Python\\Python37\\site-packages\\pandas\\core\\indexing.py:362: 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: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
            "  self.obj[key] = _infer_fill_value(value)\n",
            "C:\\Users\\nathanael99\\AppData\\Roaming\\Python\\Python37\\site-packages\\pandas\\core\\indexing.py:543: 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: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
            "  self.obj[item] = s\n"
          ],
          "name": "stderr"
        },
        {
          "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>residentialAP3v1</th>\n",
              "      <th>0.0</th>\n",
              "      <th>1.0</th>\n",
              "      <th>2.0</th>\n",
              "      <th>3.0</th>\n",
              "      <th>Total</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>expandvar</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1.0</th>\n",
              "      <td>2209</td>\n",
              "      <td>11171</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>13380</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2.0</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>5983</td>\n",
              "      <td>0</td>\n",
              "      <td>5983</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3.0</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>468</td>\n",
              "      <td>468</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Total</th>\n",
              "      <td>2209</td>\n",
              "      <td>11171</td>\n",
              "      <td>5983</td>\n",
              "      <td>468</td>\n",
              "      <td>19831</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "residentialAP3v1   0.0    1.0   2.0  3.0  Total\n",
              "expandvar                                      \n",
              "1.0               2209  11171     0    0  13380\n",
              "2.0                  0      0  5983    0   5983\n",
              "3.0                  0      0     0  468    468\n",
              "Total             2209  11171  5983  468  19831"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dvDIkQDYugSs"
      },
      "source": [
        "## Expand Building Inventory\n",
        "Using the expand variable expand building inventory."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PNFp9dvWugSs",
        "outputId": "4f3b6b9d-628e-4abf-b689-f99371e6bfd7"
      },
      "source": [
        "building_df_cols['expandvar'].describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    20090.000000\n",
              "mean         1.412096\n",
              "std          0.911027\n",
              "min          1.000000\n",
              "25%          1.000000\n",
              "50%          1.000000\n",
              "75%          2.000000\n",
              "max         41.000000\n",
              "Name: expandvar, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a7kLnVG4ugSs",
        "outputId": "eb80939e-5ab3-4456-bb99-24391af8441a"
      },
      "source": [
        "building_df_cols.loc[(building_df_cols['expandvar']<0)]"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: [guid, BLOCKID10, geometry, residentialAP3v1, expandvar]\n",
              "Index: []"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uY0gVzG-ugSt",
        "outputId": "96c97082-f8c0-4d73-ac3b-c576b70d5493"
      },
      "source": [
        "building_df_cols.loc[(building_df_cols.expandvar.isna())]"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>33</th>\n",
              "      <td>6b481629-e0c6-48f6-b1ce-d57f65d35cb6</td>\n",
              "      <td>371559608021059</td>\n",
              "      <td>POINT (-79.0284731543055 34.60277281976215)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    guid        BLOCKID10  \\\n",
              "33  6b481629-e0c6-48f6-b1ce-d57f65d35cb6  371559608021059   \n",
              "\n",
              "                                       geometry  residentialAP3v1  expandvar  \n",
              "33  POINT (-79.0284731543055 34.60277281976215)               NaN        NaN  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XvXPkB3qugSt",
        "outputId": "86e67856-7332-4f63-b7b7-8dbee78db58e"
      },
      "source": [
        "building_df_cols.loc[(building_df_cols.expandvar.isna(),'expandvar')] = 1\n",
        "building_df_cols.loc[(building_df_cols.expandvar.isna())]"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: [guid, BLOCKID10, geometry, residentialAP3v1, expandvar]\n",
              "Index: []"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rLMloGqmugSt"
      },
      "source": [
        "# The housing unit data frame is the expanded building dataframe\n",
        "building_df_cols_expand = building_df_cols.reindex(\n",
        "    building_df_cols.index.repeat(building_df_cols['expandvar']))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "l6y1tcb3ugSt",
        "outputId": "7172349e-649f-425b-f28e-c7a7656eef1b"
      },
      "source": [
        " building_df_cols_expand.guid.describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count                                    28370\n",
              "unique                                   20091\n",
              "top       ffa72530-7ac7-46f1-9cc9-7950b250e870\n",
              "freq                                        41\n",
              "Name: guid, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AJgkrwSbugSu",
        "outputId": "c622ab0d-3b1c-4990-80a1-d95fffed6059"
      },
      "source": [
        "building_df_cols_expand.loc[building_df_cols_expand.guid == \"86823d05-10ce-4073-a8dd-68ee6917e8b0\"]"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    guid        BLOCKID10  \\\n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "14  86823d05-10ce-4073-a8dd-68ee6917e8b0  371559613011113   \n",
              "\n",
              "                                        geometry  residentialAP3v1  expandvar  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  \n",
              "14  POINT (-78.99834381070323 34.65525992502589)               8.0        8.0  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0JwjUPkyugSu"
      },
      "source": [
        "## Expand Residential Address Point Count File\n",
        "Using the count of address point variable expand residential address point count file"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "028MWgpNugSu",
        "outputId": "b7d0165e-a7ee-4bf3-80e0-640d80587c90"
      },
      "source": [
        "census_blocks_df_cols['blockid'].describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    5.799000e+03\n",
              "mean     3.715596e+14\n",
              "std      6.468066e+06\n",
              "min      3.715596e+14\n",
              "25%      3.715596e+14\n",
              "50%      3.715596e+14\n",
              "75%      3.715596e+14\n",
              "max      3.715596e+14\n",
              "Name: blockid, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ADaC20JbugSu",
        "outputId": "0027092c-4c8c-4530-c643-03e390d3ed5d"
      },
      "source": [
        "census_blocks_df_cols['tothupoints'].describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    5799.000000\n",
              "mean        9.105191\n",
              "std        16.497869\n",
              "min         0.000000\n",
              "25%         0.000000\n",
              "50%         3.000000\n",
              "75%        11.000000\n",
              "max       191.000000\n",
              "Name: tothupoints, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OlO4_zPuugSv",
        "outputId": "e4c6c75f-b6aa-4b31-8dc2-0c853a133461"
      },
      "source": [
        "# The expand variable can not have missing values\n",
        "census_blocks_df_cols.loc[(census_blocks_df_cols['tothupoints'].isna()),'expandvar'] = 0\n",
        "census_blocks_df_cols.loc[(census_blocks_df_cols['tothupoints']>=0),'expandvar'] = census_blocks_df_cols['tothupoints']\n",
        "# Check to make sure expand variable was generated correctly\n",
        "census_blocks_df_cols['expandvar'].describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    5799.000000\n",
              "mean        9.105191\n",
              "std        16.497869\n",
              "min         0.000000\n",
              "25%         0.000000\n",
              "50%         3.000000\n",
              "75%        11.000000\n",
              "max       191.000000\n",
              "Name: expandvar, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8aImCGVrugSv",
        "outputId": "e6aceda2-8325-4edf-bf4a-679eb983808e"
      },
      "source": [
        "census_blocks_df_cols.loc[(census_blocks_df_cols.expandvar.isna())]"
      ],
      "execution_count": null,
      "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>blockid</th>\n",
              "      <th>geometry</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>expandvar</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: [blockid, geometry, rppnt4269, tothupoints, expandvar]\n",
              "Index: []"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qBb121ORugSv"
      },
      "source": [
        "census_blocks_df_cols_expand = census_blocks_df_cols.reindex(\n",
        "    census_blocks_df_cols.index.repeat(census_blocks_df_cols['expandvar']))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sFdLracRugSv",
        "outputId": "82595738-7208-424e-ea32-757c34dfa6c0"
      },
      "source": [
        "census_blocks_df_cols_expand.blockid.describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    5.280100e+04\n",
              "mean     3.715596e+14\n",
              "std      6.280271e+06\n",
              "min      3.715596e+14\n",
              "25%      3.715596e+14\n",
              "50%      3.715596e+14\n",
              "75%      3.715596e+14\n",
              "max      3.715596e+14\n",
              "Name: blockid, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3eT8Yvc2ugSv",
        "outputId": "8ae30e1a-64ea-4609-f811-d5d365baea08"
      },
      "source": [
        "census_blocks_df_cols_expand.head()"
      ],
      "execution_count": null,
      "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>blockid</th>\n",
              "      <th>geometry</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>expandvar</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "           blockid                                           geometry  \\\n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "\n",
              "                               rppnt4269  tothupoints  expandvar  \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14  \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14  \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14  \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14  \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "At9JQDqrugSw"
      },
      "source": [
        "## Merge Two Address Point Files \n",
        "Combing the address points based on building inventory and the address points based on the 2010 Census will create one file that has address points for the entire county. \n",
        "\n",
        "The compbined file will show where the building inventory may be missing information within the study community. The combined file will also help to show the populations impacted both inside the study community and in neighboring areas.\n",
        "\n",
        "To merge the two files need to add a counter to each file by blockid."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tuguwG-wugSw"
      },
      "source": [
        "# Add counter by block id - use cummulative count method\n",
        "census_blocks_df_cols_expand['blockidcounter'] = census_blocks_df_cols_expand.groupby('blockid').cumcount()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UmFF22yRugSw",
        "outputId": "db2b5db5-1bd1-48be-9be8-4f594a873278"
      },
      "source": [
        "census_blocks_df_cols_expand.head()"
      ],
      "execution_count": null,
      "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>blockid</th>\n",
              "      <th>geometry</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>expandvar</th>\n",
              "      <th>blockidcounter</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>POLYGON ((-79.22246 34.458838, -79.222528 34.4...</td>\n",
              "      <td>POINT (-79.22459088452018 34.4587885)</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "           blockid                                           geometry  \\\n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "0  371559619002028  POLYGON ((-79.22246 34.458838, -79.222528 34.4...   \n",
              "\n",
              "                               rppnt4269  tothupoints  expandvar  \\\n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14   \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14   \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14   \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14   \n",
              "0  POINT (-79.22459088452018 34.4587885)           14         14   \n",
              "\n",
              "   blockidcounter  \n",
              "0               0  \n",
              "0               1  \n",
              "0               2  \n",
              "0               3  \n",
              "0               4  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Mu6bgnmFugSw",
        "outputId": "07fb78e2-b647-4e9b-8ec2-01aa2ab3cf2f"
      },
      "source": [
        "# Add counter by block id - use cummulative count method\n",
        "building_df_cols_expand['blockidcounter'] = building_df_cols_expand.groupby('BLOCKID10').cumcount()\n",
        "building_df_cols_expand.head()"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar</th>\n",
              "      <th>blockidcounter</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2d32aeff-7b75-47e6-b7a5-4f4adca4b021</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99633432765292 34.6543576103806)</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>371559613011113</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                   guid        BLOCKID10  \\\n",
              "0  2d32aeff-7b75-47e6-b7a5-4f4adca4b021  371559613011113   \n",
              "1  400586f1-a265-4454-ba08-30eda28b974c  371559613011113   \n",
              "1  400586f1-a265-4454-ba08-30eda28b974c  371559613011113   \n",
              "1  400586f1-a265-4454-ba08-30eda28b974c  371559613011113   \n",
              "1  400586f1-a265-4454-ba08-30eda28b974c  371559613011113   \n",
              "\n",
              "                                       geometry  residentialAP3v1  expandvar  \\\n",
              "0   POINT (-78.99633432765292 34.6543576103806)               0.0        1.0   \n",
              "1  POINT (-78.99711777506781 34.65410219535778)               6.0        6.0   \n",
              "1  POINT (-78.99711777506781 34.65410219535778)               6.0        6.0   \n",
              "1  POINT (-78.99711777506781 34.65410219535778)               6.0        6.0   \n",
              "1  POINT (-78.99711777506781 34.65410219535778)               6.0        6.0   \n",
              "\n",
              "   blockidcounter  \n",
              "0               0  \n",
              "1               1  \n",
              "1               2  \n",
              "1               3  \n",
              "1               4  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D8P1nuedugSx"
      },
      "source": [
        "# Merge 2 files based on blockid and blockid counter - keep all observations from both files with full outer join\n",
        "address_point_inventory = pd.merge(building_df_cols_expand, census_blocks_df_cols_expand,\n",
        "                                  left_on=['BLOCKID10','blockidcounter'], right_on=['blockid','blockidcounter'], how='outer')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "g5Q8LGwEugSx",
        "outputId": "3b88a21f-c0cd-4043-9fb4-549068414427"
      },
      "source": [
        "# Check merge - examples were Building Id is missing\n",
        "displaycols = ['guid','BLOCKID10']\n",
        "condition = address_point_inventory['guid'].isna()\n",
        "address_point_inventory[displaycols].loc[condition].head()"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>28370</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28371</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28372</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28373</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28374</th>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      guid  BLOCKID10\n",
              "28370  NaN        NaN\n",
              "28371  NaN        NaN\n",
              "28372  NaN        NaN\n",
              "28373  NaN        NaN\n",
              "28374  NaN        NaN"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "An-NiGaMugSx",
        "outputId": "a481dd3b-9570-41f1-ec6f-7ee63ddf1d96"
      },
      "source": [
        "# Check merge - examples were there is no census data\n",
        "displaycols = ['guid','BLOCKID10']\n",
        "condition = address_point_inventory['tothupoints'].isnull()\n",
        "address_point_inventory[displaycols].loc[condition].head()"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>84</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>85</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>86</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>87</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>88</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    guid     BLOCKID10\n",
              "84  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14\n",
              "85  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14\n",
              "86  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14\n",
              "87  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14\n",
              "88  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZoV8C2l_ugSx"
      },
      "source": [
        "### Fix issue with missing blockid vs BLOCKID10"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Sihu1eCXugSx",
        "outputId": "b177a8ae-6407-4e1a-968c-723c6b540769"
      },
      "source": [
        "address_point_inventory.loc[address_point_inventory.guid == \"86823d05-10ce-4073-a8dd-68ee6917e8b0\"]"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry_x</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar_x</th>\n",
              "      <th>blockidcounter</th>\n",
              "      <th>blockid</th>\n",
              "      <th>geometry_y</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>expandvar_y</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>82</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>82</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POLYGON ((-78.997281 34.656738, -78.996881 34....</td>\n",
              "      <td>POINT (-78.99912009206406 34.65508)</td>\n",
              "      <td>84.0</td>\n",
              "      <td>84.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>83</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>83</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POLYGON ((-78.997281 34.656738, -78.996881 34....</td>\n",
              "      <td>POINT (-78.99912009206406 34.65508)</td>\n",
              "      <td>84.0</td>\n",
              "      <td>84.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>84</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>84</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>85</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>85</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>86</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>86</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>87</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>87</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>88</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>88</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>89</th>\n",
              "      <td>86823d05-10ce-4073-a8dd-68ee6917e8b0</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99834381070323 34.65525992502589)</td>\n",
              "      <td>8.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>89</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    guid     BLOCKID10  \\\n",
              "82  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "83  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "84  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "85  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "86  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "87  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "88  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "89  86823d05-10ce-4073-a8dd-68ee6917e8b0  3.715596e+14   \n",
              "\n",
              "                                      geometry_x  residentialAP3v1  \\\n",
              "82  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "83  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "84  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "85  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "86  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "87  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "88  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "89  POINT (-78.99834381070323 34.65525992502589)               8.0   \n",
              "\n",
              "    expandvar_x  blockidcounter       blockid  \\\n",
              "82          8.0              82  3.715596e+14   \n",
              "83          8.0              83  3.715596e+14   \n",
              "84          8.0              84           NaN   \n",
              "85          8.0              85           NaN   \n",
              "86          8.0              86           NaN   \n",
              "87          8.0              87           NaN   \n",
              "88          8.0              88           NaN   \n",
              "89          8.0              89           NaN   \n",
              "\n",
              "                                           geometry_y  \\\n",
              "82  POLYGON ((-78.997281 34.656738, -78.996881 34....   \n",
              "83  POLYGON ((-78.997281 34.656738, -78.996881 34....   \n",
              "84                                                NaN   \n",
              "85                                                NaN   \n",
              "86                                                NaN   \n",
              "87                                                NaN   \n",
              "88                                                NaN   \n",
              "89                                                NaN   \n",
              "\n",
              "                              rppnt4269  tothupoints  expandvar_y  \n",
              "82  POINT (-78.99912009206406 34.65508)         84.0         84.0  \n",
              "83  POINT (-78.99912009206406 34.65508)         84.0         84.0  \n",
              "84                                  NaN          NaN          NaN  \n",
              "85                                  NaN          NaN          NaN  \n",
              "86                                  NaN          NaN          NaN  \n",
              "87                                  NaN          NaN          NaN  \n",
              "88                                  NaN          NaN          NaN  \n",
              "89                                  NaN          NaN          NaN  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "o0TyhPg1ugSy",
        "outputId": "5423fcc6-22dd-458b-92b4-a549c38d3af0"
      },
      "source": [
        "address_point_inventory['blockid'].describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    5.280100e+04\n",
              "mean     3.715596e+14\n",
              "std      6.280271e+06\n",
              "min      3.715596e+14\n",
              "25%      3.715596e+14\n",
              "50%      3.715596e+14\n",
              "75%      3.715596e+14\n",
              "max      3.715596e+14\n",
              "Name: blockid, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "n-BiEBzFugSy",
        "outputId": "aff55c1e-4522-423d-9d6d-c031d50e8c9c"
      },
      "source": [
        "address_point_inventory['BLOCKID10'].describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    2.837000e+04\n",
              "mean     3.715596e+14\n",
              "std      3.536915e+06\n",
              "min      3.715596e+14\n",
              "25%      3.715596e+14\n",
              "50%      3.715596e+14\n",
              "75%      3.715596e+14\n",
              "max      3.715596e+14\n",
              "Name: BLOCKID10, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 34
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "n8sByzJLugSy"
      },
      "source": [
        "address_point_inventory.loc[address_point_inventory.blockid.isna(),\n",
        "                                                                'blockid'] = address_point_inventory['BLOCKID10']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SYq3lOKcugSy"
      },
      "source": [
        "## Create Unique ID for each Address Point\n",
        "The address point id can be a combination of the block id a counter"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MyRufC4PugSy",
        "outputId": "34b7af8f-840e-4f12-d71e-87843a62b896"
      },
      "source": [
        "cols = [col for col in address_point_inventory]\n",
        "cols"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['guid',\n",
              " 'BLOCKID10',\n",
              " 'geometry_x',\n",
              " 'residentialAP3v1',\n",
              " 'expandvar_x',\n",
              " 'blockidcounter',\n",
              " 'blockid',\n",
              " 'geometry_y',\n",
              " 'rppnt4269',\n",
              " 'tothupoints',\n",
              " 'expandvar_y']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KXGXAiSfugSz"
      },
      "source": [
        "### The  Address Point ID is based on the building id first then the block id\n",
        "In the best case scenario every address point is connected to the a building but in cases where the building id is missing then the address point is based on the Census Block ID."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hr5YMpSgugSz",
        "outputId": "8e578a3d-ef55-46b9-b8fa-03825a81b10d"
      },
      "source": [
        "address_point_inventory.loc[(address_point_inventory['guid'].isna()),\n",
        "                            'strctid'] = address_point_inventory.apply(lambda x: \"CB\"+ str(x['blockid']).zfill(36), axis=1)\n",
        "address_point_inventory.loc[(address_point_inventory['guid'].notna()),\n",
        "                            'strctid'] = address_point_inventory.apply(lambda x: \"ST\"+ str(x['guid']).zfill(36), axis=1)\n",
        "# Confirm Primary Key is Unique and Non-Missing\n",
        "address_point_inventory[['strctid']].head(10)"
      ],
      "execution_count": null,
      "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>strctid</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>STb7b5e4ce-431f-4e7d-8a8a-619a9c205571</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>STb7b5e4ce-431f-4e7d-8a8a-619a9c205571</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>STb7b5e4ce-431f-4e7d-8a8a-619a9c205571</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                  strctid\n",
              "0  ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021\n",
              "1  ST400586f1-a265-4454-ba08-30eda28b974c\n",
              "2  ST400586f1-a265-4454-ba08-30eda28b974c\n",
              "3  ST400586f1-a265-4454-ba08-30eda28b974c\n",
              "4  ST400586f1-a265-4454-ba08-30eda28b974c\n",
              "5  ST400586f1-a265-4454-ba08-30eda28b974c\n",
              "6  ST400586f1-a265-4454-ba08-30eda28b974c\n",
              "7  STb7b5e4ce-431f-4e7d-8a8a-619a9c205571\n",
              "8  STb7b5e4ce-431f-4e7d-8a8a-619a9c205571\n",
              "9  STb7b5e4ce-431f-4e7d-8a8a-619a9c205571"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 37
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_T4k8_BhugSz",
        "outputId": "0070b0a7-4f6f-4501-e377-53cc9e8580cf"
      },
      "source": [
        "# Sort Address Points by The first part of the address point \n",
        "address_point_inventory.sort_values(by=['strctid'])\n",
        "# Add Counter by Building\n",
        "address_point_inventory['apcounter'] = address_point_inventory.groupby('strctid').cumcount()\n",
        "\n",
        "# Are there any examples were the block building counter does not equal the blockid counter?\n",
        "displaycols = ['guid','blockid','tothupoints','blockidcounter','apcounter']\n",
        "condition = address_point_inventory['blockidcounter']!=address_point_inventory['apcounter']\n",
        "address_point_inventory[displaycols].loc[condition].head()"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>blockid</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>blockidcounter</th>\n",
              "      <th>apcounter</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>84.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>84.0</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>84.0</td>\n",
              "      <td>3</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>84.0</td>\n",
              "      <td>4</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>84.0</td>\n",
              "      <td>5</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                   guid       blockid  tothupoints  \\\n",
              "1  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14         84.0   \n",
              "2  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14         84.0   \n",
              "3  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14         84.0   \n",
              "4  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14         84.0   \n",
              "5  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14         84.0   \n",
              "\n",
              "   blockidcounter  apcounter  \n",
              "1               1          0  \n",
              "2               2          1  \n",
              "3               3          2  \n",
              "4               4          3  \n",
              "5               5          4  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TYU_WELsugS0"
      },
      "source": [
        "To make a unique id for the address points need to have a combination of unique values. The first part of the address point id is based on either the building id or the block id.  Within each Building or Census Block the counter variable provides a way to identify address points within a block."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dkIa32YfugS0",
        "outputId": "4a89a96d-9d21-4e21-b817-25cb621b66b0"
      },
      "source": [
        "address_point_inventory['addrptid'] = address_point_inventory.apply(lambda x: x['strctid'] + \"AP\" +\n",
        "                                                                 str(int(x['apcounter'])).zfill(6), axis=1)\n",
        "# Move Primary Key Column to first Column\n",
        "cols = ['addrptid']  + [col for col in address_point_inventory if col != 'addrptid']\n",
        "address_point_inventory = address_point_inventory[cols]\n",
        "address_point_inventory[['addrptid','blockid', 'apcounter']].head(6)"
      ],
      "execution_count": null,
      "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>addrptid</th>\n",
              "      <th>blockid</th>\n",
              "      <th>apcounter</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021AP000000</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000000</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000001</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000002</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000003</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000004</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                         addrptid       blockid  apcounter\n",
              "0  ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021AP000000  3.715596e+14          0\n",
              "1  ST400586f1-a265-4454-ba08-30eda28b974cAP000000  3.715596e+14          0\n",
              "2  ST400586f1-a265-4454-ba08-30eda28b974cAP000001  3.715596e+14          1\n",
              "3  ST400586f1-a265-4454-ba08-30eda28b974cAP000002  3.715596e+14          2\n",
              "4  ST400586f1-a265-4454-ba08-30eda28b974cAP000003  3.715596e+14          3\n",
              "5  ST400586f1-a265-4454-ba08-30eda28b974cAP000004  3.715596e+14          4"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 39
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oTk7tUsLugS0",
        "outputId": "c9320d69-5c73-47f1-d29f-36c50e60ebd4"
      },
      "source": [
        "# Confirm Primary Key is Unique and Non-Missing\n",
        "address_point_inventory.addrptid.describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count                                              61505\n",
              "unique                                             61505\n",
              "top       CB0000000000000000000371559618012006.0AP000019\n",
              "freq                                                   1\n",
              "Name: addrptid, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 40
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "taLDazuOugS0"
      },
      "source": [
        "## Generate Flag Variables \n",
        "For the merged dataset identify cases where either building or census data is missing."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kzBlMEXNugS1",
        "outputId": "56848bd5-615a-4f86-9cea-2637ee02451b"
      },
      "source": [
        "# Create Address Poing Flag Variable\n",
        "address_point_inventory['flag_ap'] = 0\n",
        "address_point_inventory.loc[(address_point_inventory['tothupoints'].isnull()),'flag_ap'] = 1\n",
        "address_point_inventory.loc[(address_point_inventory['guid'].isna()),'flag_ap'] = 2\n",
        "address_point_inventory.loc[(address_point_inventory['blockid'].isnull()),'flag_ap'] = 3\n",
        "# Check to make sure expand variable was generated correctly\n",
        "address_point_inventory.groupby(['flag_ap']).count()"
      ],
      "execution_count": null,
      "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>addrptid</th>\n",
              "      <th>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry_x</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar_x</th>\n",
              "      <th>blockidcounter</th>\n",
              "      <th>blockid</th>\n",
              "      <th>geometry_y</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>expandvar_y</th>\n",
              "      <th>strctid</th>\n",
              "      <th>apcounter</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>flag_ap</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19665</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>33135</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         addrptid   guid  BLOCKID10  geometry_x  residentialAP3v1  \\\n",
              "flag_ap                                                             \n",
              "0           19666  19666      19666       19666             19665   \n",
              "1            8704   8704       8704        8704              8704   \n",
              "2           33135      0          0           0                 0   \n",
              "\n",
              "         expandvar_x  blockidcounter  blockid  geometry_y  rppnt4269  \\\n",
              "flag_ap                                                                \n",
              "0              19666           19666    19666       19666      19666   \n",
              "1               8704            8704     8704           0          0   \n",
              "2                  0           33135    33135       33135      33135   \n",
              "\n",
              "         tothupoints  expandvar_y  strctid  apcounter  \n",
              "flag_ap                                                \n",
              "0              19666        19666    19666      19666  \n",
              "1                  0            0     8704       8704  \n",
              "2              33135        33135    33135      33135  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LCPyfuzCugS1"
      },
      "source": [
        "## Set Geometry for Address Points\n",
        "The location of the address point will be important for identifying the hazard impact. There are two options for the address point location.\n",
        "1. If there is a building representative point use the building representative point \n",
        "2. If there building data is missing use the representative point from the census block"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WCoVGcagugS1",
        "outputId": "70ed4fd0-8c78-4aa2-d056-2d58df123e16"
      },
      "source": [
        "address_point_inventory[['geometry_x','geometry_y','rppnt4269']].head()"
      ],
      "execution_count": null,
      "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>geometry_x</th>\n",
              "      <th>geometry_y</th>\n",
              "      <th>rppnt4269</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>POINT (-78.99633432765292 34.6543576103806)</td>\n",
              "      <td>POLYGON ((-78.997281 34.656738, -78.996881 34....</td>\n",
              "      <td>POINT (-78.99912009206406 34.65508)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>POLYGON ((-78.997281 34.656738, -78.996881 34....</td>\n",
              "      <td>POINT (-78.99912009206406 34.65508)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>POLYGON ((-78.997281 34.656738, -78.996881 34....</td>\n",
              "      <td>POINT (-78.99912009206406 34.65508)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>POLYGON ((-78.997281 34.656738, -78.996881 34....</td>\n",
              "      <td>POINT (-78.99912009206406 34.65508)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>POLYGON ((-78.997281 34.656738, -78.996881 34....</td>\n",
              "      <td>POINT (-78.99912009206406 34.65508)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                     geometry_x  \\\n",
              "0   POINT (-78.99633432765292 34.6543576103806)   \n",
              "1  POINT (-78.99711777506781 34.65410219535778)   \n",
              "2  POINT (-78.99711777506781 34.65410219535778)   \n",
              "3  POINT (-78.99711777506781 34.65410219535778)   \n",
              "4  POINT (-78.99711777506781 34.65410219535778)   \n",
              "\n",
              "                                          geometry_y  \\\n",
              "0  POLYGON ((-78.997281 34.656738, -78.996881 34....   \n",
              "1  POLYGON ((-78.997281 34.656738, -78.996881 34....   \n",
              "2  POLYGON ((-78.997281 34.656738, -78.996881 34....   \n",
              "3  POLYGON ((-78.997281 34.656738, -78.996881 34....   \n",
              "4  POLYGON ((-78.997281 34.656738, -78.996881 34....   \n",
              "\n",
              "                             rppnt4269  \n",
              "0  POINT (-78.99912009206406 34.65508)  \n",
              "1  POINT (-78.99912009206406 34.65508)  \n",
              "2  POINT (-78.99912009206406 34.65508)  \n",
              "3  POINT (-78.99912009206406 34.65508)  \n",
              "4  POINT (-78.99912009206406 34.65508)  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 42
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "s6v1UTtDugS1"
      },
      "source": [
        "# Set Address Point Geometry\n",
        "# The default geometry is the building representative point\n",
        "address_point_inventory['geometry'] = address_point_inventory['geometry_x']\n",
        "# When the building representative point is missing use the Census Block Representative Point\n",
        "address_point_inventory.loc[(address_point_inventory['geometry'].isnull()),'geometry'] = address_point_inventory['rppnt4269']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PYydIST_ugS2"
      },
      "source": [
        "## Identify Residential Address Points\n",
        "For Address Points that have an estimate for the number of housing units, or if the building data is missing then the address point is likely to be a residential address point.\n",
        "\n",
        "The knowledge that an address point is residential will help prioritize the allocation of housing units to address points.\n",
        "\n",
        "For address points in buildings with more than housing unit the number of housing units also provides a way to prioritize renters and owners. With renters more likely to be allocated to buildings with greater numbers of housing units."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7dw6QnJmugS2",
        "outputId": "6a3d5c74-5a7a-4232-ed17-97215fb6016f"
      },
      "source": [
        "address_point_inventory['residential'] = 0\n",
        "# If the building id is missing then the address point is residential\n",
        "address_point_inventory.loc[(address_point_inventory['guid'].isna()),'residential'] = 1\n",
        "# The the variable residentialAP3v1 is greater than 0 then the address point is residential\n",
        "address_point_inventory.loc[(address_point_inventory['residentialAP3v1']>0),'residential'] = 1\n",
        "# Check new variable\n",
        "address_point_inventory.groupby(['flag_ap']).count()"
      ],
      "execution_count": null,
      "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>addrptid</th>\n",
              "      <th>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry_x</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar_x</th>\n",
              "      <th>blockidcounter</th>\n",
              "      <th>blockid</th>\n",
              "      <th>geometry_y</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>expandvar_y</th>\n",
              "      <th>strctid</th>\n",
              "      <th>apcounter</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residential</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>flag_ap</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19665</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "      <td>19666</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "      <td>8704</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>33135</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "      <td>33135</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         addrptid   guid  BLOCKID10  geometry_x  residentialAP3v1  \\\n",
              "flag_ap                                                             \n",
              "0           19666  19666      19666       19666             19665   \n",
              "1            8704   8704       8704        8704              8704   \n",
              "2           33135      0          0           0                 0   \n",
              "\n",
              "         expandvar_x  blockidcounter  blockid  geometry_y  rppnt4269  \\\n",
              "flag_ap                                                                \n",
              "0              19666           19666    19666       19666      19666   \n",
              "1               8704            8704     8704           0          0   \n",
              "2                  0           33135    33135       33135      33135   \n",
              "\n",
              "         tothupoints  expandvar_y  strctid  apcounter  geometry  residential  \n",
              "flag_ap                                                                       \n",
              "0              19666        19666    19666      19666     19666        19666  \n",
              "1                  0            0     8704       8704      8704         8704  \n",
              "2              33135        33135    33135      33135     33135        33135  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 44
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eHoYQcKCugS2"
      },
      "source": [
        "## Identify observations that represent the primary building\n",
        "In some future exploration cases it would be of interest to run cross tabulations on just the buildings, instead of all of the address points. To identify the buildings it is possible to use the address point counter (apcounter) and the address point flag (flag_ap). If the counter is 0 and the flag is 0 or 1 then the address point observation is the first address point in a building."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kHy3j6tBugS2",
        "outputId": "591b04f2-061f-43fc-d6de-3c20a9e051b7"
      },
      "source": [
        "# create a binary variable 0 - not the primary building observation, 1 - use to count buildings\n",
        "address_point_inventory['bldgobs'] = 0\n",
        "# If the ap count is 0 and the flag is 0 or 1 then the bldgobs should be 1\n",
        "address_point_inventory.loc[(address_point_inventory['apcounter'] == 0) &\n",
        "                            (address_point_inventory['flag_ap'] <= 1), 'bldgobs'] = 1\n",
        "# Check new variable\n",
        "address_point_inventory.groupby(['bldgobs']).count()"
      ],
      "execution_count": null,
      "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>addrptid</th>\n",
              "      <th>guid</th>\n",
              "      <th>BLOCKID10</th>\n",
              "      <th>geometry_x</th>\n",
              "      <th>residentialAP3v1</th>\n",
              "      <th>expandvar_x</th>\n",
              "      <th>blockidcounter</th>\n",
              "      <th>blockid</th>\n",
              "      <th>geometry_y</th>\n",
              "      <th>rppnt4269</th>\n",
              "      <th>tothupoints</th>\n",
              "      <th>expandvar_y</th>\n",
              "      <th>strctid</th>\n",
              "      <th>apcounter</th>\n",
              "      <th>flag_ap</th>\n",
              "      <th>geometry</th>\n",
              "      <th>residential</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>bldgobs</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>41414</td>\n",
              "      <td>8279</td>\n",
              "      <td>8279</td>\n",
              "      <td>8279</td>\n",
              "      <td>8279</td>\n",
              "      <td>8279</td>\n",
              "      <td>41414</td>\n",
              "      <td>41414</td>\n",
              "      <td>38572</td>\n",
              "      <td>38572</td>\n",
              "      <td>38572</td>\n",
              "      <td>38572</td>\n",
              "      <td>41414</td>\n",
              "      <td>41414</td>\n",
              "      <td>41414</td>\n",
              "      <td>41414</td>\n",
              "      <td>41414</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>20090</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>14229</td>\n",
              "      <td>14229</td>\n",
              "      <td>14229</td>\n",
              "      <td>14229</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "      <td>20091</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         addrptid   guid  BLOCKID10  geometry_x  residentialAP3v1  \\\n",
              "bldgobs                                                             \n",
              "0           41414   8279       8279        8279              8279   \n",
              "1           20091  20091      20091       20091             20090   \n",
              "\n",
              "         expandvar_x  blockidcounter  blockid  geometry_y  rppnt4269  \\\n",
              "bldgobs                                                                \n",
              "0               8279           41414    41414       38572      38572   \n",
              "1              20091           20091    20091       14229      14229   \n",
              "\n",
              "         tothupoints  expandvar_y  strctid  apcounter  flag_ap  geometry  \\\n",
              "bldgobs                                                                    \n",
              "0              38572        38572    41414      41414    41414     41414   \n",
              "1              14229        14229    20091      20091    20091     20091   \n",
              "\n",
              "         residential  \n",
              "bldgobs               \n",
              "0              41414  \n",
              "1              20091  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f_gcztgJugS2"
      },
      "source": [
        "The cross tabulation confirms that the building observations equal the number of building observations in the inventory."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vc4QOGp3ugS3"
      },
      "source": [
        "## Keep primary columns\n",
        "The address point county file has many columns but only a few are needed to generate the address point inventory."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "y8ZbclqvugS3"
      },
      "source": [
        "# Rename residentialAP3v1 housing unit estiamte (huestimate)\n",
        "address_point_inventory['huestimate'] = address_point_inventory['residentialAP3v1']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a2tcqhi8ugS3",
        "outputId": "151bcc27-7010-44b9-d41a-bd997ee84ee6"
      },
      "source": [
        "cols = [col for col in address_point_inventory]\n",
        "cols"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['addrptid',\n",
              " 'guid',\n",
              " 'BLOCKID10',\n",
              " 'geometry_x',\n",
              " 'residentialAP3v1',\n",
              " 'expandvar_x',\n",
              " 'blockidcounter',\n",
              " 'blockid',\n",
              " 'geometry_y',\n",
              " 'rppnt4269',\n",
              " 'tothupoints',\n",
              " 'expandvar_y',\n",
              " 'strctid',\n",
              " 'apcounter',\n",
              " 'flag_ap',\n",
              " 'geometry',\n",
              " 'residential',\n",
              " 'bldgobs',\n",
              " 'huestimate']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 47
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QFzy2vipugS3",
        "outputId": "ec8b9649-a06f-4ea8-968f-f1c5c8106de4"
      },
      "source": [
        "select_cols = ['addrptid','strctid','guid','blockid','geometry','huestimate','residential','bldgobs','flag_ap']\n",
        "address_point_inventory_cols = address_point_inventory[select_cols]\n",
        "address_point_inventory_cols.head()"
      ],
      "execution_count": null,
      "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>addrptid</th>\n",
              "      <th>strctid</th>\n",
              "      <th>guid</th>\n",
              "      <th>blockid</th>\n",
              "      <th>geometry</th>\n",
              "      <th>huestimate</th>\n",
              "      <th>residential</th>\n",
              "      <th>bldgobs</th>\n",
              "      <th>flag_ap</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021AP000000</td>\n",
              "      <td>ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021</td>\n",
              "      <td>2d32aeff-7b75-47e6-b7a5-4f4adca4b021</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99633432765292 34.6543576103806)</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000000</td>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000001</td>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000002</td>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974cAP000003</td>\n",
              "      <td>ST400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>3.715596e+14</td>\n",
              "      <td>POINT (-78.99711777506781 34.65410219535778)</td>\n",
              "      <td>6.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                         addrptid  \\\n",
              "0  ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021AP000000   \n",
              "1  ST400586f1-a265-4454-ba08-30eda28b974cAP000000   \n",
              "2  ST400586f1-a265-4454-ba08-30eda28b974cAP000001   \n",
              "3  ST400586f1-a265-4454-ba08-30eda28b974cAP000002   \n",
              "4  ST400586f1-a265-4454-ba08-30eda28b974cAP000003   \n",
              "\n",
              "                                  strctid  \\\n",
              "0  ST2d32aeff-7b75-47e6-b7a5-4f4adca4b021   \n",
              "1  ST400586f1-a265-4454-ba08-30eda28b974c   \n",
              "2  ST400586f1-a265-4454-ba08-30eda28b974c   \n",
              "3  ST400586f1-a265-4454-ba08-30eda28b974c   \n",
              "4  ST400586f1-a265-4454-ba08-30eda28b974c   \n",
              "\n",
              "                                   guid       blockid  \\\n",
              "0  2d32aeff-7b75-47e6-b7a5-4f4adca4b021  3.715596e+14   \n",
              "1  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14   \n",
              "2  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14   \n",
              "3  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14   \n",
              "4  400586f1-a265-4454-ba08-30eda28b974c  3.715596e+14   \n",
              "\n",
              "                                       geometry  huestimate  residential  \\\n",
              "0   POINT (-78.99633432765292 34.6543576103806)         0.0            0   \n",
              "1  POINT (-78.99711777506781 34.65410219535778)         6.0            1   \n",
              "2  POINT (-78.99711777506781 34.65410219535778)         6.0            1   \n",
              "3  POINT (-78.99711777506781 34.65410219535778)         6.0            1   \n",
              "4  POINT (-78.99711777506781 34.65410219535778)         6.0            1   \n",
              "\n",
              "   bldgobs  flag_ap  \n",
              "0        1        0  \n",
              "1        1        0  \n",
              "2        0        0  \n",
              "3        0        0  \n",
              "4        0        0  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TS0wmnniugS3"
      },
      "source": [
        "## Merge Address Point inventory with Building and Census Data\n",
        "To analyize the impact of the hazard the address point inventory needs to include building information and census place information. The building information will include building type, year built, and appraised values (when available). The Census information will include city name and count information."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2CuFnzTnugS3",
        "outputId": "a597ca6e-5a3f-45b1-e166-8b28a41185f8"
      },
      "source": [
        "# Keep coloumns for merge\n",
        "merge_cols = ['guid','archetype']\n",
        "building_df_merge_cols = building_df[merge_cols]\n",
        "building_df_merge_cols.head()"
      ],
      "execution_count": null,
      "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>guid</th>\n",
              "      <th>archetype</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2d32aeff-7b75-47e6-b7a5-4f4adca4b021</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>400586f1-a265-4454-ba08-30eda28b974c</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>b7b5e4ce-431f-4e7d-8a8a-619a9c205571</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>47fcaa3f-8590-4f7c-8764-405acb671b19</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>63d1d4df-db7b-40f6-bace-a70fbb0466b1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                   guid  archetype\n",
              "0  2d32aeff-7b75-47e6-b7a5-4f4adca4b021          0\n",
              "1  400586f1-a265-4454-ba08-30eda28b974c          2\n",
              "2  b7b5e4ce-431f-4e7d-8a8a-619a9c205571          2\n",
              "3  47fcaa3f-8590-4f7c-8764-405acb671b19          2\n",
              "4  63d1d4df-db7b-40f6-bace-a70fbb0466b1          2"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 49
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Sp5Bn4P-ugS4",
        "outputId": "0e3648a4-98c2-4dc8-85f9-112ef499efce"
      },
      "source": [
        "# Confirm Primary Key is Unique and Non-Missing\n",
        "building_df_merge_cols.guid.describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count                                    20091\n",
              "unique                                   20091\n",
              "top       853ae178-f224-4c96-a127-cf5af89396bd\n",
              "freq                                         1\n",
              "Name: guid, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8x7p10nQugS4"
      },
      "source": [
        "# merge selected columns from building inventory to address point inventory\n",
        "address_point_inventory_cols_bldg = pd.merge(address_point_inventory_cols, building_df_merge_cols,\n",
        "                                  left_on='guid', right_on='guid', how='left')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vq7AE-_pugS4"
      },
      "source": [
        "### Merge Select Columns from Census Block Data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e4PUW9squgS4",
        "outputId": "b731806c-c724-4c3e-8397-21f8de6db1b3"
      },
      "source": [
        "# For the merge only need a select number of columns\n",
        "merge_cols = ['blockid','PLCGEOID10','PLCNAME10','COUNTYFP10']\n",
        "census_blocks_df_merge_cols = census_blocks_df[merge_cols]\n",
        "census_blocks_df_merge_cols.head()"
      ],
      "execution_count": null,
      "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>blockid</th>\n",
              "      <th>PLCGEOID10</th>\n",
              "      <th>PLCNAME10</th>\n",
              "      <th>COUNTYFP10</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>371559619002028</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>371559619002054</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>371559617002069</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>371559617002065</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>371559617002058</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "           blockid  PLCGEOID10 PLCNAME10 COUNTYFP10\n",
              "0  371559619002028         NaN       NaN        155\n",
              "1  371559619002054         NaN       NaN        155\n",
              "2  371559617002069         NaN       NaN        155\n",
              "3  371559617002065         NaN       NaN        155\n",
              "4  371559617002058         NaN       NaN        155"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ucHpfCSbugS4",
        "outputId": "3e8ca940-c688-456f-fa84-24b2138b9b94"
      },
      "source": [
        "# Confirm Primary Key is Unique and Non-Missing\n",
        "census_blocks_df_merge_cols.blockid.describe()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "count    5.799000e+03\n",
              "mean     3.715596e+14\n",
              "std      6.468066e+06\n",
              "min      3.715596e+14\n",
              "25%      3.715596e+14\n",
              "50%      3.715596e+14\n",
              "75%      3.715596e+14\n",
              "max      3.715596e+14\n",
              "Name: blockid, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-pWuVrntugS5"
      },
      "source": [
        "# merge selected columns from building inventory to address point inventory\n",
        "address_point_inventory_cols_bldg_block = pd.merge(address_point_inventory_cols_bldg, census_blocks_df_merge_cols,\n",
        "                                  left_on='blockid', right_on='blockid', how='left')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P77yGxyQugS5"
      },
      "source": [
        "### Identify Unicorporated Areas with Place Name\n",
        "There are many address points that fall just outside of city limits in unincorprated places. For these areas use the county inforamation to label the place names as the County Name."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Du_X179KugS5",
        "outputId": "3854fa07-c0e0-4c95-bf77-95599a25c88c"
      },
      "source": [
        "address_point_inventory_cols_bldg_block.loc[(address_point_inventory_cols_bldg_block['PLCNAME10'].isna()) &\n",
        "                                            (address_point_inventory_cols_bldg_block['COUNTYFP10'] == '155'),\n",
        "                                             'PLCNAME10'] = \"Unincorporated Robeson County\"\n",
        "# Check new variable\n",
        "pd.crosstab(address_point_inventory_cols_bldg_block['PLCNAME10'], \n",
        "            address_point_inventory_cols_bldg_block['COUNTYFP10'], margins=True, margins_name=\"Total\")"
      ],
      "execution_count": null,
      "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>COUNTYFP10</th>\n",
              "      <th>155</th>\n",
              "      <th>Total</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>PLCNAME10</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Barker Ten Mile</th>\n",
              "      <td>499</td>\n",
              "      <td>499</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Elrod</th>\n",
              "      <td>192</td>\n",
              "      <td>192</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Fairmont</th>\n",
              "      <td>1255</td>\n",
              "      <td>1255</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Lumber Bridge</th>\n",
              "      <td>51</td>\n",
              "      <td>51</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Lumberton</th>\n",
              "      <td>13294</td>\n",
              "      <td>13294</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Marietta</th>\n",
              "      <td>79</td>\n",
              "      <td>79</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Maxton</th>\n",
              "      <td>1041</td>\n",
              "      <td>1041</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>McDonald</th>\n",
              "      <td>49</td>\n",
              "      <td>49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Orrum</th>\n",
              "      <td>50</td>\n",
              "      <td>50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Parkton</th>\n",
              "      <td>209</td>\n",
              "      <td>209</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Pembroke</th>\n",
              "      <td>1271</td>\n",
              "      <td>1271</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Proctorville</th>\n",
              "      <td>56</td>\n",
              "      <td>56</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Prospect</th>\n",
              "      <td>364</td>\n",
              "      <td>364</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Raemon</th>\n",
              "      <td>113</td>\n",
              "      <td>113</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Raynham</th>\n",
              "      <td>30</td>\n",
              "      <td>30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Red Springs</th>\n",
              "      <td>1607</td>\n",
              "      <td>1607</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Rennert</th>\n",
              "      <td>139</td>\n",
              "      <td>139</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Rex</th>\n",
              "      <td>35</td>\n",
              "      <td>35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Rowland</th>\n",
              "      <td>535</td>\n",
              "      <td>535</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Shannon</th>\n",
              "      <td>92</td>\n",
              "      <td>92</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>St. Pauls</th>\n",
              "      <td>865</td>\n",
              "      <td>865</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Unincorporated Robeson County</th>\n",
              "      <td>39636</td>\n",
              "      <td>39636</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Wakulla</th>\n",
              "      <td>43</td>\n",
              "      <td>43</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Total</th>\n",
              "      <td>61505</td>\n",
              "      <td>61505</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "COUNTYFP10                       155  Total\n",
              "PLCNAME10                                  \n",
              "Barker Ten Mile                  499    499\n",
              "Elrod                            192    192\n",
              "Fairmont                        1255   1255\n",
              "Lumber Bridge                     51     51\n",
              "Lumberton                      13294  13294\n",
              "Marietta                          79     79\n",
              "Maxton                          1041   1041\n",
              "McDonald                          49     49\n",
              "Orrum                             50     50\n",
              "Parkton                          209    209\n",
              "Pembroke                        1271   1271\n",
              "Proctorville                      56     56\n",
              "Prospect                         364    364\n",
              "Raemon                           113    113\n",
              "Raynham                           30     30\n",
              "Red Springs                     1607   1607\n",
              "Rennert                          139    139\n",
              "Rex                               35     35\n",
              "Rowland                          535    535\n",
              "Shannon                           92     92\n",
              "St. Pauls                        865    865\n",
              "Unincorporated Robeson County  39636  39636\n",
              "Wakulla                           43     43\n",
              "Total                          61505  61505"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 55
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T4x35fnjugS5"
      },
      "source": [
        "## Add X Y variables \n",
        "To be consistent with previous address point inventories add X and Y variables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HmY-YnvHugS5"
      },
      "source": [
        "# Convert Data Frame to Geodataframe\n",
        "address_point_inventory_cols_bldg_block_gdf = gpd.GeoDataFrame(address_point_inventory_cols_bldg_block)\n",
        "\n",
        "# Use shapely.wkt loads to convert WKT to GeoSeries\n",
        "from shapely.wkt import loads\n",
        "\n",
        "address_point_inventory_cols_bldg_block_gdf['geometry'] = address_point_inventory_cols_bldg_block_gdf['geometry'].apply(lambda x: loads(x))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jDat1fA6ugS6",
        "outputId": "a64d47c8-e19f-4ec3-915b-c874b50ea5a9"
      },
      "source": [
        "address_point_inventory_cols_bldg_block_gdf['x'] = address_point_inventory_cols_bldg_block_gdf['geometry'].x\n",
        "address_point_inventory_cols_bldg_block_gdf['y'] = address_point_inventory_cols_bldg_block_gdf['geometry'].y\n",
        "address_point_inventory_cols_bldg_block_gdf[['geometry','x','y']].head(10)"
      ],
      "execution_count": null,
      "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>geometry</th>\n",
              "      <th>x</th>\n",
              "      <th>y</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>POINT (-78.99633 34.65436)</td>\n",
              "      <td>-78.996334</td>\n",
              "      <td>34.654358</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>POINT (-78.99712 34.65410)</td>\n",
              "      <td>-78.997118</td>\n",
              "      <td>34.654102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>POINT (-78.99712 34.65410)</td>\n",
              "      <td>-78.997118</td>\n",
              "      <td>34.654102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>POINT (-78.99712 34.65410)</td>\n",
              "      <td>-78.997118</td>\n",
              "      <td>34.654102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>POINT (-78.99712 34.65410)</td>\n",
              "      <td>-78.997118</td>\n",
              "      <td>34.654102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>POINT (-78.99712 34.65410)</td>\n",
              "      <td>-78.997118</td>\n",
              "      <td>34.654102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>POINT (-78.99712 34.65410)</td>\n",
              "      <td>-78.997118</td>\n",
              "      <td>34.654102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>POINT (-78.99646 34.65469)</td>\n",
              "      <td>-78.996465</td>\n",
              "      <td>34.654693</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>POINT (-78.99646 34.65469)</td>\n",
              "      <td>-78.996465</td>\n",
              "      <td>34.654693</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>POINT (-78.99646 34.65469)</td>\n",
              "      <td>-78.996465</td>\n",
              "      <td>34.654693</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                     geometry          x          y\n",
              "0  POINT (-78.99633 34.65436) -78.996334  34.654358\n",
              "1  POINT (-78.99712 34.65410) -78.997118  34.654102\n",
              "2  POINT (-78.99712 34.65410) -78.997118  34.654102\n",
              "3  POINT (-78.99712 34.65410) -78.997118  34.654102\n",
              "4  POINT (-78.99712 34.65410) -78.997118  34.654102\n",
              "5  POINT (-78.99712 34.65410) -78.997118  34.654102\n",
              "6  POINT (-78.99712 34.65410) -78.997118  34.654102\n",
              "7  POINT (-78.99646 34.65469) -78.996465  34.654693\n",
              "8  POINT (-78.99646 34.65469) -78.996465  34.654693\n",
              "9  POINT (-78.99646 34.65469) -78.996465  34.654693"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 57
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mXZIzGGxugS6"
      },
      "source": [
        "## Save Work as CSV\n",
        "A CSV file with the Well Known Text (WKT) geometry provides flexiblity for saving and working with files."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KAoQMYa5ugS6"
      },
      "source": [
        "address_point_inventory_cols_bldg_block.loc[address_point_inventory_cols.index, 'ap4326'] = address_point_inventory_cols['geometry']\n",
        "address_point_inventory_cols_bldg_block['ap4326'].label = \"Address Point Location EPSG 4326 (WKT)\"\n",
        "address_point_inventory_cols_bldg_block['ap4326'].notes = \"Address Point Location in EPSG 4326\""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qJBatHZJugS6"
      },
      "source": [
        "# Move Foriegn Key Columns Block ID State, County, Tract to first Columns\n",
        "first_columns = ['addrptid','guid','strctid','blockid','PLCNAME10','PLCGEOID10','COUNTYFP10']\n",
        "cols = first_columns + [col for col in address_point_inventory_cols_bldg_block if col not in first_columns]\n",
        "address_point_inventory_cols_bldg_block = address_point_inventory_cols_bldg_block[cols]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YwGXgY6pugS6"
      },
      "source": [
        "# Save Work at this point as CSV\n",
        "savefile = sys.path[0]+\"/\"+programname+\"/\"+programname+\"_EPSG4326.csv\"\n",
        "address_point_inventory_cols_bldg_block.to_csv(savefile, index=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6MDL0j5RugS6"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}