notebooks/02-analyze-gentrification-and-demographic-changes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analyzing ACS 2017 and standardized decennial Census data from 2000\n",
    "\n",
    "This script combines and analyzes data from the 5-year estimates of the American Community Survey (2013-2017) and standardized decennial Census data from the [US2010 Longitudinal Tract Data Base](https://s4.ad.brown.edu/Projects/Diversity/Researcher/Bridging.htm) to determine the following:\n",
    "* Whether a tract gentrified or not according to [this methodology](http://www.governing.com/gov-data/gentrification-report-methodology.html)\n",
    "* Percent-point changes for six non-overlapping race/ethnicity groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gp\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load data and filter it down to metro level\n",
    "\n",
    "* Load data from the [Longitudinal Tract Data Base](https://s4.ad.brown.edu/Projects/Diversity/Researcher/Bridging.htm). (Column definitions are specified in the LTDB\"s [data dictionary](https://s4.ad.brown.edu/Projects/Diversity/Researcher/LTBDDload/Dfiles/codebooks.pdf).)\n",
    "* Load data from 2017 ACS\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "CSV_OPTIONS_2000 = dict(\n",
    "    encoding = \"ISO-8859-1\",\n",
    "    dtype = {\n",
    "        \"state\": str,\n",
    "        \"county\":str,\n",
    "        \"tract\": str,\n",
    "        \"TRTID10\": str,\n",
    "        \"MHMVAL00\": float,\n",
    "        \"HINC00\": float\n",
    "    },\n",
    "    na_values = [ \" \" ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TRTID10</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>POP00</th>\n",
       "      <th>NHWHT00</th>\n",
       "      <th>NHBLK00</th>\n",
       "      <th>NTV00</th>\n",
       "      <th>ASIAN00</th>\n",
       "      <th>HAW00</th>\n",
       "      <th>HISP00</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001020100</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 201</td>\n",
       "      <td>1920.974695</td>\n",
       "      <td>1722.977295</td>\n",
       "      <td>144.998093</td>\n",
       "      <td>28.999619</td>\n",
       "      <td>7.999895</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.999842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06083002402</td>\n",
       "      <td>CA</td>\n",
       "      <td>Santa Barbara County</td>\n",
       "      <td>Census Tract 24.02</td>\n",
       "      <td>6742.000000</td>\n",
       "      <td>1573.000000</td>\n",
       "      <td>126.000000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>272.000000</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4700.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01001020200</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 202</td>\n",
       "      <td>1892.000000</td>\n",
       "      <td>671.000000</td>\n",
       "      <td>1177.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06083002102</td>\n",
       "      <td>CA</td>\n",
       "      <td>Santa Barbara County</td>\n",
       "      <td>Census Tract 21.02</td>\n",
       "      <td>2041.000000</td>\n",
       "      <td>1204.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>19.000000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>696.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01001020300</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 203</td>\n",
       "      <td>3339.000000</td>\n",
       "      <td>2738.000000</td>\n",
       "      <td>498.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       TRTID10 state                county               tract        POP00  \\\n",
       "0  01001020100    AL        Autauga County    Census Tract 201  1920.974695   \n",
       "1  06083002402    CA  Santa Barbara County  Census Tract 24.02  6742.000000   \n",
       "2  01001020200    AL        Autauga County    Census Tract 202  1892.000000   \n",
       "3  06083002102    CA  Santa Barbara County  Census Tract 21.02  2041.000000   \n",
       "4  01001020300    AL        Autauga County    Census Tract 203  3339.000000   \n",
       "\n",
       "       NHWHT00      NHBLK00      NTV00     ASIAN00  HAW00       HISP00  \n",
       "0  1722.977295   144.998093  28.999619    7.999895    0.0    11.999842  \n",
       "1  1573.000000   126.000000  44.000000  272.000000   11.0  4700.000000  \n",
       "2   671.000000  1177.000000  12.000000   12.000000    0.0    16.000000  \n",
       "3  1204.000000    57.000000  19.000000   60.000000    2.0   696.000000  \n",
       "4  2738.000000   498.000000  16.000000   27.000000    1.0    55.000000  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_count_data = (\n",
    "    pd.read_csv(\n",
    "        \"../data/LTDB_Std_2000_fullcount.csv\",\n",
    "        **CSV_OPTIONS_2000\n",
    "    )\n",
    "    [[\n",
    "        \"TRTID10\",\n",
    "        \"state\", \n",
    "        \"county\", \n",
    "        \"tract\",\n",
    "        \"POP00\",\n",
    "        \"NHWHT00\",\n",
    "        \"NHBLK00\",\n",
    "        \"NTV00\",\n",
    "        \"ASIAN00\",\n",
    "        \"HAW00\",\n",
    "        \"HISP00\",\n",
    "    ]]\n",
    "    .assign(\n",
    "        TRTID10 = lambda df: df[\"TRTID10\"].str.zfill(11)\n",
    "    )\n",
    ")\n",
    "\n",
    "full_count_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TRTID10     object\n",
       "state       object\n",
       "county      object\n",
       "tract       object\n",
       "POP00      float64\n",
       "NHWHT00    float64\n",
       "NHBLK00    float64\n",
       "NTV00      float64\n",
       "ASIAN00    float64\n",
       "HAW00      float64\n",
       "HISP00     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_count_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72693"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(full_count_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TRTID10</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>AG25UP00</th>\n",
       "      <th>HINC00</th>\n",
       "      <th>MHMVAL00</th>\n",
       "      <th>COL00</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001020100</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 201</td>\n",
       "      <td>1226.983887</td>\n",
       "      <td>36685.00080</td>\n",
       "      <td>76600.00185</td>\n",
       "      <td>191.997467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01001020200</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 202</td>\n",
       "      <td>1157.000000</td>\n",
       "      <td>30298.00000</td>\n",
       "      <td>72900.00000</td>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01001020300</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 203</td>\n",
       "      <td>2130.000000</td>\n",
       "      <td>46731.00000</td>\n",
       "      <td>79900.00000</td>\n",
       "      <td>478.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01001020400</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 204</td>\n",
       "      <td>3072.000000</td>\n",
       "      <td>46142.00000</td>\n",
       "      <td>89800.00000</td>\n",
       "      <td>708.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01001020500</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 205</td>\n",
       "      <td>3785.214923</td>\n",
       "      <td>58886.26948</td>\n",
       "      <td>116593.52740</td>\n",
       "      <td>1214.091953</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       TRTID10 state          county             tract     AG25UP00  \\\n",
       "0  01001020100    AL  Autauga County  Census Tract 201  1226.983887   \n",
       "1  01001020200    AL  Autauga County  Census Tract 202  1157.000000   \n",
       "2  01001020300    AL  Autauga County  Census Tract 203  2130.000000   \n",
       "3  01001020400    AL  Autauga County  Census Tract 204  3072.000000   \n",
       "4  01001020500    AL  Autauga County  Census Tract 205  3785.214923   \n",
       "\n",
       "        HINC00      MHMVAL00        COL00  \n",
       "0  36685.00080   76600.00185   191.997467  \n",
       "1  30298.00000   72900.00000   170.000000  \n",
       "2  46731.00000   79900.00000   478.000000  \n",
       "3  46142.00000   89800.00000   708.000000  \n",
       "4  58886.26948  116593.52740  1214.091953  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_data = pd.read_csv(\n",
    "    \"../data/LTDB_Std_2000_Sample.csv\",\n",
    "    **CSV_OPTIONS_2000,\n",
    ")\n",
    "\n",
    "sample_data = sample_data[[\n",
    "    \"TRTID10\", \n",
    "    \"state\", \n",
    "    \"county\", \n",
    "    \"tract\",\n",
    "    \"AG25UP00\",\n",
    "    \"HINC00\", \n",
    "    \"MHMVAL00\", \n",
    "    \"COL00\"\n",
    "]]\n",
    "\n",
    "sample_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "72693 72693 72693\n"
     ]
    },
    {
     "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>GEOID</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>tract</th>\n",
       "      <th>total_population</th>\n",
       "      <th>white_alone</th>\n",
       "      <th>black_alone</th>\n",
       "      <th>native_alone</th>\n",
       "      <th>asian_alone</th>\n",
       "      <th>native_hawaiian_pacific_islander</th>\n",
       "      <th>hispanic_or_latino</th>\n",
       "      <th>total_population_25_over</th>\n",
       "      <th>median_income</th>\n",
       "      <th>median_home_value</th>\n",
       "      <th>educational_attainment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001020100</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 201</td>\n",
       "      <td>1920.974695</td>\n",
       "      <td>1722.977295</td>\n",
       "      <td>144.998093</td>\n",
       "      <td>28.999619</td>\n",
       "      <td>7.999895</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.999842</td>\n",
       "      <td>1226.983887</td>\n",
       "      <td>36685.0008</td>\n",
       "      <td>76600.00185</td>\n",
       "      <td>191.997467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06083002402</td>\n",
       "      <td>CA</td>\n",
       "      <td>Santa Barbara County</td>\n",
       "      <td>Census Tract 24.02</td>\n",
       "      <td>6742.000000</td>\n",
       "      <td>1573.000000</td>\n",
       "      <td>126.000000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>272.000000</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4700.000000</td>\n",
       "      <td>3298.000000</td>\n",
       "      <td>31803.0000</td>\n",
       "      <td>127000.00000</td>\n",
       "      <td>205.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01001020200</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 202</td>\n",
       "      <td>1892.000000</td>\n",
       "      <td>671.000000</td>\n",
       "      <td>1177.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>1157.000000</td>\n",
       "      <td>30298.0000</td>\n",
       "      <td>72900.00000</td>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06083002102</td>\n",
       "      <td>CA</td>\n",
       "      <td>Santa Barbara County</td>\n",
       "      <td>Census Tract 21.02</td>\n",
       "      <td>2041.000000</td>\n",
       "      <td>1204.000000</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>19.000000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>696.000000</td>\n",
       "      <td>1378.000000</td>\n",
       "      <td>44135.0000</td>\n",
       "      <td>144600.00000</td>\n",
       "      <td>244.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01001020300</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Census Tract 203</td>\n",
       "      <td>3339.000000</td>\n",
       "      <td>2738.000000</td>\n",
       "      <td>498.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>2130.000000</td>\n",
       "      <td>46731.0000</td>\n",
       "      <td>79900.00000</td>\n",
       "      <td>478.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID state                county               tract  \\\n",
       "0  01001020100    AL        Autauga County    Census Tract 201   \n",
       "1  06083002402    CA  Santa Barbara County  Census Tract 24.02   \n",
       "2  01001020200    AL        Autauga County    Census Tract 202   \n",
       "3  06083002102    CA  Santa Barbara County  Census Tract 21.02   \n",
       "4  01001020300    AL        Autauga County    Census Tract 203   \n",
       "\n",
       "   total_population  white_alone  black_alone  native_alone  asian_alone  \\\n",
       "0       1920.974695  1722.977295   144.998093     28.999619     7.999895   \n",
       "1       6742.000000  1573.000000   126.000000     44.000000   272.000000   \n",
       "2       1892.000000   671.000000  1177.000000     12.000000    12.000000   \n",
       "3       2041.000000  1204.000000    57.000000     19.000000    60.000000   \n",
       "4       3339.000000  2738.000000   498.000000     16.000000    27.000000   \n",
       "\n",
       "   native_hawaiian_pacific_islander  hispanic_or_latino  \\\n",
       "0                               0.0           11.999842   \n",
       "1                              11.0         4700.000000   \n",
       "2                               0.0           16.000000   \n",
       "3                               2.0          696.000000   \n",
       "4                               1.0           55.000000   \n",
       "\n",
       "   total_population_25_over  median_income  median_home_value  \\\n",
       "0               1226.983887     36685.0008        76600.00185   \n",
       "1               3298.000000     31803.0000       127000.00000   \n",
       "2               1157.000000     30298.0000        72900.00000   \n",
       "3               1378.000000     44135.0000       144600.00000   \n",
       "4               2130.000000     46731.0000        79900.00000   \n",
       "\n",
       "   educational_attainment  \n",
       "0              191.997467  \n",
       "1              205.000000  \n",
       "2              170.000000  \n",
       "3              244.000000  \n",
       "4              478.000000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_data_2000 = (\n",
    "    pd.merge(\n",
    "        full_count_data,\n",
    "        sample_data,\n",
    "        on = [ \"state\", \"county\", \"tract\", \"TRTID10\" ],\n",
    "        how = \"left\",\n",
    "        validate = \"1:1\",\n",
    "    )\n",
    "    .rename(columns = {\n",
    "        \"TRTID10\": \"GEOID\", \n",
    "        \"POP00\": \"total_population\", # total population\n",
    "        \"AG25UP00\": \"total_population_25_over\", # total population 25 and over\n",
    "        \"NHWHT00\": \"white_alone\", # persons of white race, not Hispanic origin -- equivalent of 2010 \"white alone\"\n",
    "        \"NHBLK00\":\"black_alone\", # persons of black race, not Hispanic origin -- equivalent of 2010 \"black alone\"\n",
    "        \"NTV00\": \"native_alone\", #  persons of Native American race\n",
    "        \"ASIAN00\": \"asian_alone\", # Asian and Pacific Islander race\n",
    "        \"HAW00\": \"native_hawaiian_pacific_islander\",  # persons of Hawaiian race\n",
    "        \"HISP00\":\"hispanic_or_latino\", # persons of Hispanic origin \n",
    "        \"HINC00\": \"median_income\", # Median household income, total\n",
    "        \"MHMVAL00\": \"median_home_value\", # Median home value\n",
    "        \"COL00\": \"educational_attainment\" # persons with at least a four-year college degree\n",
    "    })\n",
    ")\n",
    "\n",
    "print(len(census_data_2000), len(sample_data), len(full_count_data))\n",
    "census_data_2000.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Join with 2017 Census data\n",
    "\n",
    "Note: The [Census data dictionary](https://www.census.gov/data/developers/data-sets/acs-5year/data-notes.html) suggests that `-666666666` is a placeholder value for data that is not available:\n",
    "```A \"-\" entry in the estimate column indicates that either no sample observations or too few sample observations were available to compute an estimate, or a ratio of medians cannot be calculated because one or both of the median estimates falls in the lowest interval or upper interval of an open-ended distribution.```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8281\n"
     ]
    },
    {
     "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>GEOID</th>\n",
       "      <th>name</th>\n",
       "      <th>total_population</th>\n",
       "      <th>total_population_25_over</th>\n",
       "      <th>median_income</th>\n",
       "      <th>median_home_value</th>\n",
       "      <th>educational_attainment</th>\n",
       "      <th>white_alone</th>\n",
       "      <th>black_alone</th>\n",
       "      <th>native_alone</th>\n",
       "      <th>asian_alone</th>\n",
       "      <th>native_hawaiian_pacific_islander</th>\n",
       "      <th>some_other_race_alone</th>\n",
       "      <th>two_or_more</th>\n",
       "      <th>hispanic_or_latino</th>\n",
       "      <th>city</th>\n",
       "      <th>metro_area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11001007503</td>\n",
       "      <td>Census Tract 75.03, District of Columbia, Dist...</td>\n",
       "      <td>2454</td>\n",
       "      <td>1425.0</td>\n",
       "      <td>26250.0</td>\n",
       "      <td>345600.0</td>\n",
       "      <td>308.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>2278.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Washington-Arlington-Alexandria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11001007601</td>\n",
       "      <td>Census Tract 76.01, District of Columbia, Dist...</td>\n",
       "      <td>4855</td>\n",
       "      <td>3463.0</td>\n",
       "      <td>34840.0</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>727.0</td>\n",
       "      <td>311.0</td>\n",
       "      <td>4292.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Washington-Arlington-Alexandria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11001007709</td>\n",
       "      <td>Census Tract 77.09, District of Columbia, Dist...</td>\n",
       "      <td>2524</td>\n",
       "      <td>1817.0</td>\n",
       "      <td>33750.0</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>344.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2280.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Washington-Arlington-Alexandria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11001009508</td>\n",
       "      <td>Census Tract 95.08, District of Columbia, Dist...</td>\n",
       "      <td>3691</td>\n",
       "      <td>2838.0</td>\n",
       "      <td>56404.0</td>\n",
       "      <td>356600.0</td>\n",
       "      <td>1008.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>2688.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>587.0</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Washington-Arlington-Alexandria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11001009904</td>\n",
       "      <td>Census Tract 99.04, District of Columbia, Dist...</td>\n",
       "      <td>2979</td>\n",
       "      <td>1526.0</td>\n",
       "      <td>30728.0</td>\n",
       "      <td>298600.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>2375.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>491.0</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Washington-Arlington-Alexandria</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID                                               name  \\\n",
       "0  11001007503  Census Tract 75.03, District of Columbia, Dist...   \n",
       "1  11001007601  Census Tract 76.01, District of Columbia, Dist...   \n",
       "2  11001007709  Census Tract 77.09, District of Columbia, Dist...   \n",
       "3  11001009508  Census Tract 95.08, District of Columbia, Dist...   \n",
       "4  11001009904  Census Tract 99.04, District of Columbia, Dist...   \n",
       "\n",
       "   total_population  total_population_25_over  median_income  \\\n",
       "0              2454                    1425.0        26250.0   \n",
       "1              4855                    3463.0        34840.0   \n",
       "2              2524                    1817.0        33750.0   \n",
       "3              3691                    2838.0        56404.0   \n",
       "4              2979                    1526.0        30728.0   \n",
       "\n",
       "   median_home_value  educational_attainment  white_alone  black_alone  \\\n",
       "0           345600.0                   308.0        122.0       2278.0   \n",
       "1           255000.0                   727.0        311.0       4292.0   \n",
       "2           250000.0                   344.0         20.0       2280.0   \n",
       "3           356600.0                  1008.0        211.0       2688.0   \n",
       "4           298600.0                   252.0         52.0       2375.0   \n",
       "\n",
       "   native_alone  asian_alone  native_hawaiian_pacific_islander  \\\n",
       "0           0.0          0.0                               0.0   \n",
       "1           0.0          0.0                              13.0   \n",
       "2           0.0          0.0                               0.0   \n",
       "3          68.0         71.0                               0.0   \n",
       "4           0.0          0.0                              15.0   \n",
       "\n",
       "   some_other_race_alone  two_or_more  hispanic_or_latino        city  \\\n",
       "0                    0.0         17.0                37.0  Washington   \n",
       "1                    0.0         41.0               198.0  Washington   \n",
       "2                    0.0        130.0                94.0  Washington   \n",
       "3                    0.0         66.0               587.0  Washington   \n",
       "4                    0.0         46.0               491.0  Washington   \n",
       "\n",
       "                        metro_area  \n",
       "0  Washington-Arlington-Alexandria  \n",
       "1  Washington-Arlington-Alexandria  \n",
       "2  Washington-Arlington-Alexandria  \n",
       "3  Washington-Arlington-Alexandria  \n",
       "4  Washington-Arlington-Alexandria  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_data_2017 = pd.read_csv(\n",
    "    \"../output/census_tracts.csv\",\n",
    "    dtype = { \"geoid\": str },\n",
    "    na_values = [ -666666666 ]\n",
    ").rename(\n",
    "    columns = {\"geoid\": \"GEOID\" }\n",
    ")\n",
    "\n",
    "print(len(census_data_2017))\n",
    "census_data_2017.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GEOID</th>\n",
       "      <th>name</th>\n",
       "      <th>total_population_17</th>\n",
       "      <th>total_population_25_over_17</th>\n",
       "      <th>median_income_17</th>\n",
       "      <th>median_home_value_17</th>\n",
       "      <th>educational_attainment_17</th>\n",
       "      <th>white_alone_17</th>\n",
       "      <th>black_alone_17</th>\n",
       "      <th>native_alone_17</th>\n",
       "      <th>...</th>\n",
       "      <th>white_alone_00</th>\n",
       "      <th>black_alone_00</th>\n",
       "      <th>native_alone_00</th>\n",
       "      <th>asian_alone_00</th>\n",
       "      <th>native_hawaiian_pacific_islander_00</th>\n",
       "      <th>hispanic_or_latino_00</th>\n",
       "      <th>total_population_25_over_00</th>\n",
       "      <th>median_income_00</th>\n",
       "      <th>median_home_value_00</th>\n",
       "      <th>educational_attainment_00</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11001007503</td>\n",
       "      <td>Census Tract 75.03, District of Columbia, Dist...</td>\n",
       "      <td>2454</td>\n",
       "      <td>1425.0</td>\n",
       "      <td>26250.0</td>\n",
       "      <td>345600.0</td>\n",
       "      <td>308.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>2278.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2447.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1456.0</td>\n",
       "      <td>21402.0</td>\n",
       "      <td>99200.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11001007601</td>\n",
       "      <td>Census Tract 76.01, District of Columbia, Dist...</td>\n",
       "      <td>4855</td>\n",
       "      <td>3463.0</td>\n",
       "      <td>34840.0</td>\n",
       "      <td>255000.0</td>\n",
       "      <td>727.0</td>\n",
       "      <td>311.0</td>\n",
       "      <td>4292.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>108.0</td>\n",
       "      <td>4393.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>2998.0</td>\n",
       "      <td>32930.0</td>\n",
       "      <td>101300.0</td>\n",
       "      <td>249.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11001007709</td>\n",
       "      <td>Census Tract 77.09, District of Columbia, Dist...</td>\n",
       "      <td>2524</td>\n",
       "      <td>1817.0</td>\n",
       "      <td>33750.0</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>344.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2280.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1899.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1323.0</td>\n",
       "      <td>29332.0</td>\n",
       "      <td>111600.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11001009508</td>\n",
       "      <td>Census Tract 95.08, District of Columbia, Dist...</td>\n",
       "      <td>3691</td>\n",
       "      <td>2838.0</td>\n",
       "      <td>56404.0</td>\n",
       "      <td>356600.0</td>\n",
       "      <td>1008.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>2688.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>...</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2762.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2138.0</td>\n",
       "      <td>41972.0</td>\n",
       "      <td>136300.0</td>\n",
       "      <td>457.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11001009904</td>\n",
       "      <td>Census Tract 99.04, District of Columbia, Dist...</td>\n",
       "      <td>2979</td>\n",
       "      <td>1526.0</td>\n",
       "      <td>30728.0</td>\n",
       "      <td>298600.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>2375.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1826.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1111.0</td>\n",
       "      <td>26745.0</td>\n",
       "      <td>126300.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID                                               name  \\\n",
       "0  11001007503  Census Tract 75.03, District of Columbia, Dist...   \n",
       "1  11001007601  Census Tract 76.01, District of Columbia, Dist...   \n",
       "2  11001007709  Census Tract 77.09, District of Columbia, Dist...   \n",
       "3  11001009508  Census Tract 95.08, District of Columbia, Dist...   \n",
       "4  11001009904  Census Tract 99.04, District of Columbia, Dist...   \n",
       "\n",
       "   total_population_17  total_population_25_over_17  median_income_17  \\\n",
       "0                 2454                       1425.0           26250.0   \n",
       "1                 4855                       3463.0           34840.0   \n",
       "2                 2524                       1817.0           33750.0   \n",
       "3                 3691                       2838.0           56404.0   \n",
       "4                 2979                       1526.0           30728.0   \n",
       "\n",
       "   median_home_value_17  educational_attainment_17  white_alone_17  \\\n",
       "0              345600.0                      308.0           122.0   \n",
       "1              255000.0                      727.0           311.0   \n",
       "2              250000.0                      344.0            20.0   \n",
       "3              356600.0                     1008.0           211.0   \n",
       "4              298600.0                      252.0            52.0   \n",
       "\n",
       "   black_alone_17  native_alone_17  ...  white_alone_00  black_alone_00  \\\n",
       "0          2278.0              0.0  ...            28.0          2447.0   \n",
       "1          4292.0              0.0  ...           108.0          4393.0   \n",
       "2          2280.0              0.0  ...            29.0          1899.0   \n",
       "3          2688.0             68.0  ...            25.0          2762.0   \n",
       "4          2375.0              0.0  ...            12.0          1826.0   \n",
       "\n",
       "   native_alone_00  asian_alone_00  native_hawaiian_pacific_islander_00  \\\n",
       "0              3.0             7.0                                  1.0   \n",
       "1              7.0             5.0                                  0.0   \n",
       "2              7.0             8.0                                  0.0   \n",
       "3              5.0            17.0                                  0.0   \n",
       "4              6.0             2.0                                  0.0   \n",
       "\n",
       "  hispanic_or_latino_00 total_population_25_over_00 median_income_00  \\\n",
       "0                  26.0                      1456.0          21402.0   \n",
       "1                  57.0                      2998.0          32930.0   \n",
       "2                  35.0                      1323.0          29332.0   \n",
       "3                  41.0                      2138.0          41972.0   \n",
       "4                  17.0                      1111.0          26745.0   \n",
       "\n",
       "  median_home_value_00 educational_attainment_00  \n",
       "0              99200.0                      55.0  \n",
       "1             101300.0                     249.0  \n",
       "2             111600.0                     135.0  \n",
       "3             136300.0                     457.0  \n",
       "4             126300.0                      43.0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_merged = pd.merge(\n",
    "    census_data_2017,\n",
    "    census_data_2000, \n",
    "    on = \"GEOID\",\n",
    "    how = \"left\",\n",
    "    suffixes = [ \"_17\", \"_00\" ],\n",
    "    validate = \"1:1\",\n",
    ")\n",
    "\n",
    "census_merged[\"median_home_value_00\"] = census_merged[\"median_home_value_00\"].replace(0, np.nan)\n",
    "\n",
    "census_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 8281 entries, 0 to 8280\n",
      "Data columns (total 31 columns):\n",
      " #   Column                               Non-Null Count  Dtype  \n",
      "---  ------                               --------------  -----  \n",
      " 0   GEOID                                8281 non-null   object \n",
      " 1   name                                 8281 non-null   object \n",
      " 2   total_population_17                  8281 non-null   int64  \n",
      " 3   total_population_25_over_17          8281 non-null   float64\n",
      " 4   median_income_17                     8160 non-null   float64\n",
      " 5   median_home_value_17                 7936 non-null   float64\n",
      " 6   educational_attainment_17            8281 non-null   float64\n",
      " 7   white_alone_17                       8281 non-null   float64\n",
      " 8   black_alone_17                       8281 non-null   float64\n",
      " 9   native_alone_17                      8281 non-null   float64\n",
      " 10  asian_alone_17                       8281 non-null   float64\n",
      " 11  native_hawaiian_pacific_islander_17  8281 non-null   float64\n",
      " 12  some_other_race_alone                8281 non-null   float64\n",
      " 13  two_or_more                          8281 non-null   float64\n",
      " 14  hispanic_or_latino_17                8281 non-null   float64\n",
      " 15  city                                 8281 non-null   object \n",
      " 16  metro_area                           8281 non-null   object \n",
      " 17  state                                8243 non-null   object \n",
      " 18  county                               8243 non-null   object \n",
      " 19  tract                                8243 non-null   object \n",
      " 20  total_population_00                  8243 non-null   float64\n",
      " 21  white_alone_00                       8243 non-null   float64\n",
      " 22  black_alone_00                       8243 non-null   float64\n",
      " 23  native_alone_00                      8243 non-null   float64\n",
      " 24  asian_alone_00                       8243 non-null   float64\n",
      " 25  native_hawaiian_pacific_islander_00  8243 non-null   float64\n",
      " 26  hispanic_or_latino_00                8243 non-null   float64\n",
      " 27  total_population_25_over_00          8243 non-null   float64\n",
      " 28  median_income_00                     8205 non-null   float64\n",
      " 29  median_home_value_00                 8151 non-null   float64\n",
      " 30  educational_attainment_00            8243 non-null   float64\n",
      "dtypes: float64(23), int64(1), object(7)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "census_merged.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "New York City    4700\n",
       "Oakland           980\n",
       "Washington        967\n",
       "Atlanta           951\n",
       "Baltimore         683\n",
       "Name: city, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_merged[\"city\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_merged.to_csv(\n",
    "    \"../output/census_data_metro.csv\",\n",
    "    index = False\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The gentrification measure\n",
    "It is based on two tests detailed [here](http://www.governing.com/gov-data/gentrification-report-methodology.html).\n",
    "\n",
    "#### Test 1: does the tract qualify for gentrification?\n",
    "\n",
    "* The tract had a population of at least 500 residents at the beginning and end of a decade and was located within a central city\n",
    "\n",
    "* The tract’s median household income was in the bottom 40th percentile when compared to all tracts within its metro area at the beginning of the decade.\n",
    "\n",
    "* The tract’s median home value was in the bottom 40th percentile when compared to all tracts within its metro area at the beginning of the decade.\n",
    "\n",
    "#### Test 2: has it gentrified?\n",
    "\n",
    "* An increase in a tract's educational attainment, as measured by the percentage of residents age 25 and over holding bachelor’s degrees, was in the top third of all tracts within a metro area.\n",
    "\n",
    "* A tract’s median home value increased when adjusted for inflation.\n",
    "\n",
    "* The percentage increase in a tract’s inflation-adjusted median home value was in the top third of all tracts within a metro area."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Calculate the metro-level variables needed for these tests on a city-wide level:*\n",
    "\n",
    "* 40th percentile household income threshold for metro level data \n",
    "* 40th percentile median home value for metro level\n",
    "* bachelor's degree — top third for metro level\n",
    "* inflation-adjusted median home value — top third for all metro level"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Educational attainment percentage change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_merged[\"educational_attainment_pct_17\"] = (\n",
    "    100 * census_merged[\"educational_attainment_17\"] /\n",
    "    census_merged[\"total_population_25_over_17\"]\n",
    ")\n",
    "\n",
    "census_merged[\"educational_attainment_pct_00\"] = (\n",
    "    100 * census_merged[\"educational_attainment_00\"] / \n",
    "    census_merged[\"total_population_25_over_00\"]\n",
    ")\n",
    "\n",
    "census_merged[\"educational_attainment_change\"] = (\n",
    "    census_merged[\"educational_attainment_pct_17\"] - \n",
    "    census_merged[\"educational_attainment_pct_00\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Home value percentage change"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Per [BLS CPI inflation rate](https://data.bls.gov/cgi-bin/cpicalc.pl?cost1=1.00&year1=200001&year2=201701), 1 dollar in January 2000 was worth $1.44 in January 2017."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "inflation_rate  = 1.44"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now calculate the varios thresholds for our gentrification tests: \n",
    "* `40th percentile household income threshold for metro level data`\n",
    "* `40th percentile median home value for metro level` \n",
    "* `inflation-adjusted median home value — top third percentile for all metro level`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_merged[\"home_pct_change\"] = (\n",
    "    (\n",
    "        census_merged[\"median_home_value_17\"] - # current median home value\n",
    "        (census_merged[\"median_home_value_00\"] * inflation_rate) # previous median home value adjusted for inflation\n",
    "    ) / \n",
    "    census_merged[\"median_home_value_17\"] # current median home value \n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate percentiles "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>metro_area</th>\n",
       "      <th>metro_median_income_00_q40</th>\n",
       "      <th>metro_median_home_value_00_q40</th>\n",
       "      <th>educational_attainment_change_q66</th>\n",
       "      <th>home_pct_change_q66</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Atlanta-Sandy Springs-Alpharetta</td>\n",
       "      <td>46630.915002</td>\n",
       "      <td>108139.99848</td>\n",
       "      <td>9.109797</td>\n",
       "      <td>0.049062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baltimore-Columbia-Towson</td>\n",
       "      <td>43464.600000</td>\n",
       "      <td>106757.59722</td>\n",
       "      <td>10.744444</td>\n",
       "      <td>0.319395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>New York-Newark-Jersey City</td>\n",
       "      <td>43583.600072</td>\n",
       "      <td>181600.00000</td>\n",
       "      <td>11.164452</td>\n",
       "      <td>0.375903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>San Francisco-Oakland-Berkeley</td>\n",
       "      <td>56636.600000</td>\n",
       "      <td>290200.00416</td>\n",
       "      <td>12.191118</td>\n",
       "      <td>0.346429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Washington-Arlington-Alexandria</td>\n",
       "      <td>54471.999864</td>\n",
       "      <td>147300.17242</td>\n",
       "      <td>10.928943</td>\n",
       "      <td>0.390918</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         metro_area  metro_median_income_00_q40  \\\n",
       "0  Atlanta-Sandy Springs-Alpharetta                46630.915002   \n",
       "1         Baltimore-Columbia-Towson                43464.600000   \n",
       "2       New York-Newark-Jersey City                43583.600072   \n",
       "3    San Francisco-Oakland-Berkeley                56636.600000   \n",
       "4   Washington-Arlington-Alexandria                54471.999864   \n",
       "\n",
       "   metro_median_home_value_00_q40  educational_attainment_change_q66  \\\n",
       "0                    108139.99848                           9.109797   \n",
       "1                    106757.59722                          10.744444   \n",
       "2                    181600.00000                          11.164452   \n",
       "3                    290200.00416                          12.191118   \n",
       "4                    147300.17242                          10.928943   \n",
       "\n",
       "   home_pct_change_q66  \n",
       "0             0.049062  \n",
       "1             0.319395  \n",
       "2             0.375903  \n",
       "3             0.346429  \n",
       "4             0.390918  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_merged_grp = census_merged.groupby(\"metro_area\")\n",
    "\n",
    "metro_metrics = (\n",
    "    pd.DataFrame({\n",
    "        \"metro_median_income_00_q40\": census_merged_grp[\"median_income_00\"].quantile(0.4),\n",
    "        \"metro_median_home_value_00_q40\": census_merged_grp[\"median_home_value_00\"].quantile(0.4),\n",
    "        \"educational_attainment_change_q66\": census_merged_grp[\"educational_attainment_change\"].quantile(2.0/3),\n",
    "        \"home_pct_change_q66\": census_merged_grp[\"home_pct_change\"].quantile(2.0/3)\n",
    "    })\n",
    "    .reset_index()\n",
    ")\n",
    "\n",
    "metro_metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_merged_with_metro = pd.merge(\n",
    "    census_merged,\n",
    "    metro_metrics,\n",
    "    on = \"metro_area\",\n",
    "    how = \"left\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select only tracts in the cities of interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "CITY_CSVS = {\n",
    "    \"Atlanta\": \"../data/city_tracts/atlanta_census_reporter/acs2018_5yr_B03002_14000US13121008201.csv\", \n",
    "    \"Baltimore\": \"../data/city_tracts/baltimore_census_reporter/acs2018_5yr_B03002_14000US24510260700.csv\",\n",
    "    \"New York City\": \"../data/city_tracts/new_york_census_reporter/acs2018_5yr_B03002_14000US36047990100.csv\",\n",
    "    \"Oakland\": \"../data/city_tracts/oakland_census_reporter/acs2018_5yr_B03002_14000US06001403400.csv\",\n",
    "    \"Washington\":\"../data/city_tracts/washington_census_reporter/acs2018_5yr_B03002_14000US11001007403.csv\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2799\n"
     ]
    },
    {
     "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>GEOID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13089020100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13089020200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13089020300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13089020400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13089020500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID\n",
       "0  13089020100\n",
       "1  13089020200\n",
       "2  13089020300\n",
       "3  13089020400\n",
       "4  13089020500"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tracts_in_cities = (\n",
    "    pd.concat([\n",
    "        pd.read_csv(path, usecols = [ \"geoid\" ])\n",
    "        for city, path in CITY_CSVS.items()\n",
    "    ])\n",
    "    .assign(\n",
    "        GEOID = lambda df: df[\"geoid\"].str.slice(7, None)\n",
    "    )\n",
    "    .drop(columns = [ \"geoid\" ])\n",
    ")\n",
    "\n",
    "print(len(tracts_in_cities))\n",
    "\n",
    "tracts_in_cities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2799\n"
     ]
    },
    {
     "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>GEOID</th>\n",
       "      <th>name</th>\n",
       "      <th>total_population_17</th>\n",
       "      <th>total_population_25_over_17</th>\n",
       "      <th>median_income_17</th>\n",
       "      <th>median_home_value_17</th>\n",
       "      <th>educational_attainment_17</th>\n",
       "      <th>white_alone_17</th>\n",
       "      <th>black_alone_17</th>\n",
       "      <th>native_alone_17</th>\n",
       "      <th>...</th>\n",
       "      <th>median_home_value_00</th>\n",
       "      <th>educational_attainment_00</th>\n",
       "      <th>educational_attainment_pct_17</th>\n",
       "      <th>educational_attainment_pct_00</th>\n",
       "      <th>educational_attainment_change</th>\n",
       "      <th>home_pct_change</th>\n",
       "      <th>metro_median_income_00_q40</th>\n",
       "      <th>metro_median_home_value_00_q40</th>\n",
       "      <th>educational_attainment_change_q66</th>\n",
       "      <th>home_pct_change_q66</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13089020100</td>\n",
       "      <td>Census Tract 201, DeKalb County, Georgia</td>\n",
       "      <td>2076</td>\n",
       "      <td>1455.0</td>\n",
       "      <td>174526.0</td>\n",
       "      <td>617300.0</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>1885.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>378900.0000</td>\n",
       "      <td>966.000000</td>\n",
       "      <td>84.604811</td>\n",
       "      <td>71.344165</td>\n",
       "      <td>13.260646</td>\n",
       "      <td>0.116125</td>\n",
       "      <td>46630.915002</td>\n",
       "      <td>108139.99848</td>\n",
       "      <td>9.109797</td>\n",
       "      <td>0.049062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13089020200</td>\n",
       "      <td>Census Tract 202, DeKalb County, Georgia</td>\n",
       "      <td>2045</td>\n",
       "      <td>1657.0</td>\n",
       "      <td>87889.0</td>\n",
       "      <td>548200.0</td>\n",
       "      <td>1271.0</td>\n",
       "      <td>1596.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>395014.9555</td>\n",
       "      <td>1362.201689</td>\n",
       "      <td>76.704888</td>\n",
       "      <td>78.568721</td>\n",
       "      <td>-1.863833</td>\n",
       "      <td>-0.037617</td>\n",
       "      <td>46630.915002</td>\n",
       "      <td>108139.99848</td>\n",
       "      <td>9.109797</td>\n",
       "      <td>0.049062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13089020300</td>\n",
       "      <td>Census Tract 203, DeKalb County, Georgia</td>\n",
       "      <td>4151</td>\n",
       "      <td>2934.0</td>\n",
       "      <td>113088.0</td>\n",
       "      <td>470300.0</td>\n",
       "      <td>2344.0</td>\n",
       "      <td>3612.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>256174.8167</td>\n",
       "      <td>1710.151854</td>\n",
       "      <td>79.890934</td>\n",
       "      <td>68.615498</td>\n",
       "      <td>11.275435</td>\n",
       "      <td>0.215625</td>\n",
       "      <td>46630.915002</td>\n",
       "      <td>108139.99848</td>\n",
       "      <td>9.109797</td>\n",
       "      <td>0.049062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13089020400</td>\n",
       "      <td>Census Tract 204, DeKalb County, Georgia</td>\n",
       "      <td>2574</td>\n",
       "      <td>1891.0</td>\n",
       "      <td>78681.0</td>\n",
       "      <td>433000.0</td>\n",
       "      <td>1584.0</td>\n",
       "      <td>2251.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>252024.7580</td>\n",
       "      <td>1181.242777</td>\n",
       "      <td>83.765204</td>\n",
       "      <td>64.731802</td>\n",
       "      <td>19.033402</td>\n",
       "      <td>0.161858</td>\n",
       "      <td>46630.915002</td>\n",
       "      <td>108139.99848</td>\n",
       "      <td>9.109797</td>\n",
       "      <td>0.049062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13089020500</td>\n",
       "      <td>Census Tract 205, DeKalb County, Georgia</td>\n",
       "      <td>3305</td>\n",
       "      <td>2685.0</td>\n",
       "      <td>55208.0</td>\n",
       "      <td>239600.0</td>\n",
       "      <td>1220.0</td>\n",
       "      <td>1596.0</td>\n",
       "      <td>1286.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>87499.9969</td>\n",
       "      <td>206.659561</td>\n",
       "      <td>45.437616</td>\n",
       "      <td>9.695550</td>\n",
       "      <td>35.742066</td>\n",
       "      <td>0.474124</td>\n",
       "      <td>46630.915002</td>\n",
       "      <td>108139.99848</td>\n",
       "      <td>9.109797</td>\n",
       "      <td>0.049062</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID                                      name  total_population_17  \\\n",
       "0  13089020100  Census Tract 201, DeKalb County, Georgia                 2076   \n",
       "1  13089020200  Census Tract 202, DeKalb County, Georgia                 2045   \n",
       "2  13089020300  Census Tract 203, DeKalb County, Georgia                 4151   \n",
       "3  13089020400  Census Tract 204, DeKalb County, Georgia                 2574   \n",
       "4  13089020500  Census Tract 205, DeKalb County, Georgia                 3305   \n",
       "\n",
       "   total_population_25_over_17  median_income_17  median_home_value_17  \\\n",
       "0                       1455.0          174526.0              617300.0   \n",
       "1                       1657.0           87889.0              548200.0   \n",
       "2                       2934.0          113088.0              470300.0   \n",
       "3                       1891.0           78681.0              433000.0   \n",
       "4                       2685.0           55208.0              239600.0   \n",
       "\n",
       "   educational_attainment_17  white_alone_17  black_alone_17  native_alone_17  \\\n",
       "0                     1231.0          1885.0            36.0              0.0   \n",
       "1                     1271.0          1596.0           187.0              0.0   \n",
       "2                     2344.0          3612.0           146.0             27.0   \n",
       "3                     1584.0          2251.0            74.0              0.0   \n",
       "4                     1220.0          1596.0          1286.0              0.0   \n",
       "\n",
       "   ...  median_home_value_00  educational_attainment_00  \\\n",
       "0  ...           378900.0000                 966.000000   \n",
       "1  ...           395014.9555                1362.201689   \n",
       "2  ...           256174.8167                1710.151854   \n",
       "3  ...           252024.7580                1181.242777   \n",
       "4  ...            87499.9969                 206.659561   \n",
       "\n",
       "   educational_attainment_pct_17  educational_attainment_pct_00  \\\n",
       "0                      84.604811                      71.344165   \n",
       "1                      76.704888                      78.568721   \n",
       "2                      79.890934                      68.615498   \n",
       "3                      83.765204                      64.731802   \n",
       "4                      45.437616                       9.695550   \n",
       "\n",
       "   educational_attainment_change home_pct_change metro_median_income_00_q40  \\\n",
       "0                      13.260646        0.116125               46630.915002   \n",
       "1                      -1.863833       -0.037617               46630.915002   \n",
       "2                      11.275435        0.215625               46630.915002   \n",
       "3                      19.033402        0.161858               46630.915002   \n",
       "4                      35.742066        0.474124               46630.915002   \n",
       "\n",
       "  metro_median_home_value_00_q40 educational_attainment_change_q66  \\\n",
       "0                   108139.99848                          9.109797   \n",
       "1                   108139.99848                          9.109797   \n",
       "2                   108139.99848                          9.109797   \n",
       "3                   108139.99848                          9.109797   \n",
       "4                   108139.99848                          9.109797   \n",
       "\n",
       "  home_pct_change_q66  \n",
       "0            0.049062  \n",
       "1            0.049062  \n",
       "2            0.049062  \n",
       "3            0.049062  \n",
       "4            0.049062  \n",
       "\n",
       "[5 rows x 39 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_only = pd.merge(\n",
    "    tracts_in_cities,\n",
    "    census_merged_with_metro,\n",
    "    on = \"GEOID\",\n",
    "    how = \"left\",\n",
    "    validate = \"1:1\",\n",
    ")\n",
    "\n",
    "print(len(cities_only))\n",
    "\n",
    "cities_only.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run gentrification tests described above\n",
    "\n",
    "Create columns for every test that needs to be passed:\n",
    "\n",
    "1. First whether they qualify\n",
    "2. Whether they gentrified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The tract had a population of at least 500 residents at the beginning and \n",
    "# end of a decade and was located within a central city\n",
    "cities_only[\"low_population\"] = (\n",
    "    (cities_only[\"total_population_00\"] < 500) | \n",
    "    (cities_only[\"total_population_17\"] < 500)\n",
    ")  \n",
    "\n",
    "# The tract’s median household income was in the bottom 40th percentile when compared \n",
    "# to all tracts within its metro area at the beginning of the decade.\n",
    "cities_only[\"median_home_value_00_qualifies\"] = (\n",
    "    cities_only[\"median_home_value_00\"] < cities_only[\"metro_median_home_value_00_q40\"]\n",
    ")\n",
    "\n",
    "# The tract’s median home value was in the bottom 40th percentile when compared \n",
    "# to all tracts within its metro area at the beginning of the decade.\n",
    "cities_only[\"median_40th_income_00_qualifies\"] = (\n",
    "    cities_only[\"median_income_00\"] < cities_only[\"metro_median_income_00_q40\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# An increase in a tract\"s educational attainment, as measured by the \n",
    "# percentage of residents age 25 and over who hold bachelor’s degrees, \n",
    "# was in the top third percentile of all tracts within a metro area.\n",
    "cities_only[\"educational_increase_is_top_3rd\"] = (\n",
    "    cities_only[\"educational_attainment_change\"] > cities_only[\"educational_attainment_change_q66\"]\n",
    ")\n",
    "\n",
    "# The median home value increased, after accounting for inflation\n",
    "cities_only[\"median_home_increased\"] = (\n",
    "    cities_only[\"median_home_value_17\"] > (cities_only[\"median_home_value_00\"] * inflation_rate)\n",
    ")\n",
    "\n",
    "# The percentage increase in a tract’s inflation-adjusted median home \n",
    "# value was in the top third percentile of all tracts within a metro area.\n",
    "cities_only[\"median_home_increase_is_top_3rd\"] = (\n",
    "    cities_only[\"home_pct_change\"] > cities_only[\"home_pct_change_q66\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "882"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_only[\"eligible_for_gentrification\"] = (\n",
    "  (cities_only[\"low_population\"] == False) &\n",
    "  (cities_only[\"median_home_value_00_qualifies\"] == True) &\n",
    "  (cities_only[\"median_40th_income_00_qualifies\"] == True)\n",
    ")\n",
    "\n",
    "cities_only[\"eligible_for_gentrification\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "289"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_only[\"gentrified\"] = (\n",
    "    (cities_only[\"eligible_for_gentrification\"] == True) & \n",
    "    (cities_only[\"educational_increase_is_top_3rd\"] == True) & \n",
    "    (cities_only[\"median_home_increased\"] == True) & \n",
    "    (cities_only[\"median_home_increase_is_top_3rd\"] == True)\n",
    ")\n",
    "\n",
    "cities_only[\"gentrified\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "593"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_only[\"eligible_not_gentrified_highpop\"] = (\n",
    "  (cities_only[\"eligible_for_gentrification\"] == True) &\n",
    "  (cities_only[\"gentrified\"] == False)\n",
    ")\n",
    "\n",
    "cities_only[\"eligible_not_gentrified_highpop\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1831"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_only[\"not_eligible_highpop\"] = (\n",
    "  (cities_only[\"low_population\"] == False) &\n",
    "  (cities_only[\"eligible_for_gentrification\"] == False) \n",
    ")\n",
    "\n",
    "cities_only[\"not_eligible_highpop\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>eligible_for_gentrification</th>\n",
       "      <th>gentrified</th>\n",
       "      <th>not_eligible_highpop</th>\n",
       "      <th>low_population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Atlanta</th>\n",
       "      <td>72</td>\n",
       "      <td>23</td>\n",
       "      <td>64</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Baltimore</th>\n",
       "      <td>169</td>\n",
       "      <td>40</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York City</th>\n",
       "      <td>455</td>\n",
       "      <td>138</td>\n",
       "      <td>1635</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oakland</th>\n",
       "      <td>83</td>\n",
       "      <td>29</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>103</td>\n",
       "      <td>59</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               eligible_for_gentrification  gentrified  not_eligible_highpop  \\\n",
       "city                                                                           \n",
       "Atlanta                                 72          23                    64   \n",
       "Baltimore                              169          40                    30   \n",
       "New York City                          455         138                  1635   \n",
       "Oakland                                 83          29                    27   \n",
       "Washington                             103          59                    75   \n",
       "\n",
       "               low_population  \n",
       "city                           \n",
       "Atlanta                     3  \n",
       "Baltimore                   1  \n",
       "New York City              77  \n",
       "Oakland                     4  \n",
       "Washington                  1  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    cities_only\n",
    "    [[\n",
    "        \"city\",\n",
    "        \"eligible_for_gentrification\",\n",
    "        \"gentrified\",\n",
    "        \"not_eligible_highpop\",\n",
    "        \"low_population\",\n",
    "    ]]\n",
    "    .groupby(\"city\")\n",
    "    .sum()\n",
    "    .astype(int)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate race/ethnicity percentage changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "RACE_ETHNICITY_GROUPS = [\n",
    "    \"white_alone\",\n",
    "    \"black_alone\",\n",
    "    \"asian_alone\",\n",
    "    \"native_alone\",\n",
    "    \"native_hawaiian_pacific_islander\",\n",
    "    \"hispanic_or_latino\",\n",
    "]\n",
    "\n",
    "for group in RACE_ETHNICITY_GROUPS:\n",
    "    cities_only[f\"pct_{group}_17\"] = (100 * cities_only[f\"{group}_17\"] / cities_only[\"total_population_17\"]).round(3)\n",
    "    cities_only[f\"pct_{group}_00\"] = (100 * cities_only[f\"{group}_00\"] / cities_only[\"total_population_00\"]).round(3)\n",
    "    \n",
    "    cities_only[f\"pct_{group}_change\"] = (cities_only[f\"pct_{group}_17\"] - cities_only[f\"pct_{group}_00\"]).round(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_only.to_csv(\n",
    "    \"../output/gentrification.csv\",\n",
    "    index = False\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge data with shapefiles\n",
    "\n",
    "Shapefiles sourced here: https://www.census.gov/cgi-bin/geo/shapefiles/index.php?year=2019&layergroup=Census+Tracts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GEOID</th>\n",
       "      <th>INTPTLAT</th>\n",
       "      <th>INTPTLON</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11001010900</td>\n",
       "      <td>+38.8132364</td>\n",
       "      <td>-077.0238475</td>\n",
       "      <td>POLYGON ((-77.03919 38.80050, -77.03913 38.800...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11001010400</td>\n",
       "      <td>+38.8512514</td>\n",
       "      <td>-077.0009048</td>\n",
       "      <td>POLYGON ((-77.00919 38.83918, -77.00915 38.839...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11001009903</td>\n",
       "      <td>+38.8883180</td>\n",
       "      <td>-076.9212121</td>\n",
       "      <td>POLYGON ((-76.93005 38.88982, -76.92797 38.889...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11001009904</td>\n",
       "      <td>+38.8851712</td>\n",
       "      <td>-076.9311353</td>\n",
       "      <td>POLYGON ((-76.93577 38.88596, -76.93524 38.885...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11001000902</td>\n",
       "      <td>+38.9285125</td>\n",
       "      <td>-077.1077517</td>\n",
       "      <td>POLYGON ((-77.11975 38.93435, -77.11886 38.935...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID     INTPTLAT      INTPTLON  \\\n",
       "0  11001010900  +38.8132364  -077.0238475   \n",
       "1  11001010400  +38.8512514  -077.0009048   \n",
       "2  11001009903  +38.8883180  -076.9212121   \n",
       "3  11001009904  +38.8851712  -076.9311353   \n",
       "4  11001000902  +38.9285125  -077.1077517   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((-77.03919 38.80050, -77.03913 38.800...  \n",
       "1  POLYGON ((-77.00919 38.83918, -77.00915 38.839...  \n",
       "2  POLYGON ((-76.93005 38.88982, -76.92797 38.889...  \n",
       "3  POLYGON ((-76.93577 38.88596, -76.93524 38.885...  \n",
       "4  POLYGON ((-77.11975 38.93435, -77.11886 38.935...  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states = {\n",
    "    \"DC\":\"../data/censusTracts/states/tl_2019_11_DC_tract/tl_2019_11_tract.shp\",\n",
    "    \"Georgia\": \"../data/censusTracts/states/tl_2019_13_georgia_tract/tl_2019_13_tract.shp\", \n",
    "    \"Maryland\": \"../data/censusTracts/states/tl_2019_24_maryland_tract/tl_2019_24_tract.shp\",\n",
    "    \"California\": \"../data/censusTracts/states/tl_2019_06_california_tract/tl_2019_06_tract.shp\",\n",
    "    \"New York\": \"../data/censusTracts/states/tl_2019_36_NY_tract/tl_2019_36_tract.shp\",\n",
    "}\n",
    "\n",
    "\n",
    "shape_files = (\n",
    "        pd.concat([\n",
    "            gp.read_file(path)\n",
    "            .drop(columns = [\n",
    "                \"STATEFP\",\n",
    "                \"COUNTYFP\",\n",
    "                \"TRACTCE\",\n",
    "                \"NAMELSAD\",\n",
    "                \"NAME\",\n",
    "                \"FUNCSTAT\",\n",
    "                \"ALAND\",\n",
    "                \"AWATER\",\n",
    "                \"MTFCC\"\n",
    "\n",
    "            ])\n",
    "        for state, path in states.items()\n",
    "    ])\n",
    ")\n",
    "\n",
    "shape_files.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geopandas.geodataframe.GeoDataFrame"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(shape_files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16529"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(shape_files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2799 2799\n"
     ]
    }
   ],
   "source": [
    "merged_shape_files = gp.GeoDataFrame(\n",
    "    pd.merge(\n",
    "        cities_only,\n",
    "        shape_files, \n",
    "        on = \"GEOID\",\n",
    "        how = \"left\",\n",
    "        validate = \"1:1\", \n",
    "    )\n",
    "    .dropna(subset = [ \"city\" ])\n",
    "    .replace([np.inf, -np.inf], np.nan)\n",
    ")\n",
    "\n",
    "print(len(cities_only), len(merged_shape_files))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geopandas.geodataframe.GeoDataFrame"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(merged_shape_files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GEOID</th>\n",
       "      <th>name</th>\n",
       "      <th>total_population_17</th>\n",
       "      <th>total_population_25_over_17</th>\n",
       "      <th>median_income_17</th>\n",
       "      <th>median_home_value_17</th>\n",
       "      <th>educational_attainment_17</th>\n",
       "      <th>white_alone_17</th>\n",
       "      <th>black_alone_17</th>\n",
       "      <th>native_alone_17</th>\n",
       "      <th>...</th>\n",
       "      <th>pct_native_alone_change</th>\n",
       "      <th>pct_native_hawaiian_pacific_islander_17</th>\n",
       "      <th>pct_native_hawaiian_pacific_islander_00</th>\n",
       "      <th>pct_native_hawaiian_pacific_islander_change</th>\n",
       "      <th>pct_hispanic_or_latino_17</th>\n",
       "      <th>pct_hispanic_or_latino_00</th>\n",
       "      <th>pct_hispanic_or_latino_change</th>\n",
       "      <th>INTPTLAT</th>\n",
       "      <th>INTPTLON</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13089020100</td>\n",
       "      <td>Census Tract 201, DeKalb County, Georgia</td>\n",
       "      <td>2076</td>\n",
       "      <td>1455.0</td>\n",
       "      <td>174526.0</td>\n",
       "      <td>617300.0</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>1885.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.224</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.576</td>\n",
       "      <td>10.768</td>\n",
       "      <td>-6.192</td>\n",
       "      <td>+33.7998709</td>\n",
       "      <td>-084.3432667</td>\n",
       "      <td>POLYGON ((-84.34830 33.79151, -84.34828 33.791...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13089020200</td>\n",
       "      <td>Census Tract 202, DeKalb County, Georgia</td>\n",
       "      <td>2045</td>\n",
       "      <td>1657.0</td>\n",
       "      <td>87889.0</td>\n",
       "      <td>548200.0</td>\n",
       "      <td>1271.0</td>\n",
       "      <td>1596.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.546</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.281</td>\n",
       "      <td>3.092</td>\n",
       "      <td>2.189</td>\n",
       "      <td>+33.7756064</td>\n",
       "      <td>-084.3402667</td>\n",
       "      <td>POLYGON ((-84.34898 33.77168, -84.34897 33.772...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13089020300</td>\n",
       "      <td>Census Tract 203, DeKalb County, Georgia</td>\n",
       "      <td>4151</td>\n",
       "      <td>2934.0</td>\n",
       "      <td>113088.0</td>\n",
       "      <td>470300.0</td>\n",
       "      <td>2344.0</td>\n",
       "      <td>3612.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.589</td>\n",
       "      <td>2.210</td>\n",
       "      <td>1.379</td>\n",
       "      <td>+33.7659856</td>\n",
       "      <td>-084.3295009</td>\n",
       "      <td>POLYGON ((-84.33998 33.76486, -84.33942 33.764...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13089020400</td>\n",
       "      <td>Census Tract 204, DeKalb County, Georgia</td>\n",
       "      <td>2574</td>\n",
       "      <td>1891.0</td>\n",
       "      <td>78681.0</td>\n",
       "      <td>433000.0</td>\n",
       "      <td>1584.0</td>\n",
       "      <td>2251.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.517</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.875</td>\n",
       "      <td>3.341</td>\n",
       "      <td>-0.466</td>\n",
       "      <td>+33.7660727</td>\n",
       "      <td>-084.3443392</td>\n",
       "      <td>POLYGON ((-84.34922 33.76220, -84.34921 33.764...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13089020500</td>\n",
       "      <td>Census Tract 205, DeKalb County, Georgia</td>\n",
       "      <td>3305</td>\n",
       "      <td>2685.0</td>\n",
       "      <td>55208.0</td>\n",
       "      <td>239600.0</td>\n",
       "      <td>1220.0</td>\n",
       "      <td>1596.0</td>\n",
       "      <td>1286.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.062</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.446</td>\n",
       "      <td>2.935</td>\n",
       "      <td>2.511</td>\n",
       "      <td>+33.7526586</td>\n",
       "      <td>-084.3416193</td>\n",
       "      <td>POLYGON ((-84.34921 33.75115, -84.34919 33.752...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 70 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID                                      name  total_population_17  \\\n",
       "0  13089020100  Census Tract 201, DeKalb County, Georgia                 2076   \n",
       "1  13089020200  Census Tract 202, DeKalb County, Georgia                 2045   \n",
       "2  13089020300  Census Tract 203, DeKalb County, Georgia                 4151   \n",
       "3  13089020400  Census Tract 204, DeKalb County, Georgia                 2574   \n",
       "4  13089020500  Census Tract 205, DeKalb County, Georgia                 3305   \n",
       "\n",
       "   total_population_25_over_17  median_income_17  median_home_value_17  \\\n",
       "0                       1455.0          174526.0              617300.0   \n",
       "1                       1657.0           87889.0              548200.0   \n",
       "2                       2934.0          113088.0              470300.0   \n",
       "3                       1891.0           78681.0              433000.0   \n",
       "4                       2685.0           55208.0              239600.0   \n",
       "\n",
       "   educational_attainment_17  white_alone_17  black_alone_17  native_alone_17  \\\n",
       "0                     1231.0          1885.0            36.0              0.0   \n",
       "1                     1271.0          1596.0           187.0              0.0   \n",
       "2                     2344.0          3612.0           146.0             27.0   \n",
       "3                     1584.0          2251.0            74.0              0.0   \n",
       "4                     1220.0          1596.0          1286.0              0.0   \n",
       "\n",
       "   ...  pct_native_alone_change  pct_native_hawaiian_pacific_islander_17  \\\n",
       "0  ...                   -0.224                                      0.0   \n",
       "1  ...                   -0.546                                      0.0   \n",
       "2  ...                    0.128                                      0.0   \n",
       "3  ...                   -0.517                                      0.0   \n",
       "4  ...                   -0.062                                      0.0   \n",
       "\n",
       "   pct_native_hawaiian_pacific_islander_00  \\\n",
       "0                                      0.0   \n",
       "1                                      0.0   \n",
       "2                                      0.0   \n",
       "3                                      0.0   \n",
       "4                                      0.0   \n",
       "\n",
       "   pct_native_hawaiian_pacific_islander_change  pct_hispanic_or_latino_17  \\\n",
       "0                                          0.0                      4.576   \n",
       "1                                          0.0                      5.281   \n",
       "2                                          0.0                      3.589   \n",
       "3                                          0.0                      2.875   \n",
       "4                                          0.0                      5.446   \n",
       "\n",
       "  pct_hispanic_or_latino_00 pct_hispanic_or_latino_change     INTPTLAT  \\\n",
       "0                    10.768                        -6.192  +33.7998709   \n",
       "1                     3.092                         2.189  +33.7756064   \n",
       "2                     2.210                         1.379  +33.7659856   \n",
       "3                     3.341                        -0.466  +33.7660727   \n",
       "4                     2.935                         2.511  +33.7526586   \n",
       "\n",
       "       INTPTLON                                           geometry  \n",
       "0  -084.3432667  POLYGON ((-84.34830 33.79151, -84.34828 33.791...  \n",
       "1  -084.3402667  POLYGON ((-84.34898 33.77168, -84.34897 33.772...  \n",
       "2  -084.3295009  POLYGON ((-84.33998 33.76486, -84.33942 33.764...  \n",
       "3  -084.3443392  POLYGON ((-84.34922 33.76220, -84.34921 33.764...  \n",
       "4  -084.3416193  POLYGON ((-84.34921 33.75115, -84.34919 33.752...  \n",
       "\n",
       "[5 rows x 70 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_shape_files.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare data for maps\n",
    "\n",
    "- drop columns that don\"t need to be visualized\n",
    "- make a geojson file for each city\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GEOID', 'name', 'total_population_17', 'total_population_25_over_17',\n",
       "       'median_income_17', 'median_home_value_17', 'educational_attainment_17',\n",
       "       'white_alone_17', 'black_alone_17', 'native_alone_17', 'asian_alone_17',\n",
       "       'native_hawaiian_pacific_islander_17', 'some_other_race_alone',\n",
       "       'two_or_more', 'hispanic_or_latino_17', 'city', 'metro_area', 'state',\n",
       "       'county', 'tract', 'total_population_00', 'white_alone_00',\n",
       "       'black_alone_00', 'native_alone_00', 'asian_alone_00',\n",
       "       'native_hawaiian_pacific_islander_00', 'hispanic_or_latino_00',\n",
       "       'total_population_25_over_00', 'median_income_00',\n",
       "       'median_home_value_00', 'educational_attainment_00',\n",
       "       'educational_attainment_pct_17', 'educational_attainment_pct_00',\n",
       "       'educational_attainment_change', 'home_pct_change',\n",
       "       'metro_median_income_00_q40', 'metro_median_home_value_00_q40',\n",
       "       'educational_attainment_change_q66', 'home_pct_change_q66',\n",
       "       'low_population', 'median_home_value_00_qualifies',\n",
       "       'median_40th_income_00_qualifies', 'educational_increase_is_top_3rd',\n",
       "       'median_home_increased', 'median_home_increase_is_top_3rd',\n",
       "       'eligible_for_gentrification', 'gentrified',\n",
       "       'eligible_not_gentrified_highpop', 'not_eligible_highpop',\n",
       "       'pct_white_alone_17', 'pct_white_alone_00', 'pct_white_alone_change',\n",
       "       'pct_black_alone_17', 'pct_black_alone_00', 'pct_black_alone_change',\n",
       "       'pct_asian_alone_17', 'pct_asian_alone_00', 'pct_asian_alone_change',\n",
       "       'pct_native_alone_17', 'pct_native_alone_00', 'pct_native_alone_change',\n",
       "       'pct_native_hawaiian_pacific_islander_17',\n",
       "       'pct_native_hawaiian_pacific_islander_00',\n",
       "       'pct_native_hawaiian_pacific_islander_change',\n",
       "       'pct_hispanic_or_latino_17', 'pct_hispanic_or_latino_00',\n",
       "       'pct_hispanic_or_latino_change', 'INTPTLAT', 'INTPTLON', 'geometry'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_shape_files.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_for_viz = [\n",
    "    \"GEOID\",\n",
    "    \"name\",\n",
    "    \"total_population_17\",\n",
    "    \"white_alone_17\",\n",
    "    \"black_alone_17\",\n",
    "    \"native_alone_17\",\n",
    "    \"asian_alone_17\",\n",
    "    \"native_hawaiian_pacific_islander_17\",\n",
    "    \"hispanic_or_latino_17\",\n",
    "    \"city\",\n",
    "    \"gentrified\",\n",
    "    \"pct_white_alone_change\",\n",
    "    \"pct_black_alone_change\",\n",
    "    \"pct_native_alone_change\",\n",
    "    \"pct_asian_alone_change\",\n",
    "    \"pct_hispanic_or_latino_change\",\n",
    "    \"pct_native_hawaiian_pacific_islander_change\",\n",
    "    \"INTPTLAT\", \"INTPTLON\", \"geometry\"\n",
    "]\n",
    "\n",
    "merged_shape_files_for_viz = merged_shape_files[columns_for_viz]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Atlanta\n",
      "Baltimore\n",
      "New York City\n",
      "Oakland\n",
      "Washington\n"
     ]
    }
   ],
   "source": [
    "city_list = merged_shape_files_for_viz[\"city\"].unique().tolist()\n",
    "\n",
    "for city in city_list:\n",
    "    print(city)\n",
    "    output_path = \"../output/for_maps/\" + str(city) + \"_data.csv\"\n",
    "    output_path_geojson = \"../output/for_maps/\" + str(city) + \"_data.geojson\"\n",
    "    \n",
    "    df = merged_shape_files_for_viz[merged_shape_files_for_viz[\"city\"] == city]\n",
    "    df.drop(columns = [ \"geometry\" ]).to_csv(output_path, index = False)\n",
    "    df.to_file(\n",
    "        output_path_geojson,\n",
    "        driver = \"GeoJSON\"\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "---\n",
    "\n",
    "---"
   ]
  }
 ],
 "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}