01_Census_LabelingandCrosswalk.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from geopandas import GeoDataFrame\n",
    "import glob\n",
    "import requests\n",
    "# from geoalchemy2 import Geometry, WKTElement\n",
    "\n",
    "import psycopg2  # (if it is postgres/postgis)\n",
    "conn = psycopg2.connect(database=\"postgres\", user=\"wenfeixu\", password=\"\",\n",
    "    host=\"localhost\")\n",
    "cursor = conn.cursor()\n",
    "\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('postgresql://wenfeixu:@localhost:5432/postgres')\n",
    "    \n",
    "from geoalchemy2 import Geometry, WKTElement\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Goal of the Notebook\n",
    "1. For 1940s tract boundaries: find the dominant HOLC label\n",
    "1. For all tracts across all years: retain those that intersect with HOLC boundaries\n",
    "2. For all tracts across all years: Label each year's tract based on their dominant HOLC grade\n",
    "\n",
    "# Datasets Used\n",
    "\n",
    "- HOLC polygons from University of Richmond Digital Scholarship Lab\n",
    "- Historical Census Data from IPUMS National Historical GIS database\n",
    "****"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 0. Get Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1940s tract boundaries and data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql= '''\n",
    "    select * from us_tract_1940_conflated_4326\n",
    "    '''\n",
    "us_tract_1940_conflated_4326 = gpd.GeoDataFrame.from_postgis(sql, conn,geom_col='geom',crs={'init':'epsg:4326'} )\n",
    "us_tract_1940_conflated_4326['tract_area'] = us_tract_1940_conflated_4326.to_crs({'init':'epsg:3857'}).area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_conflated_4326.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0.2 HOLC Boundaries and Labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql= '''\n",
    "    select * from holc_all_dump_4326\n",
    "    '''\n",
    "holc = gpd.GeoDataFrame.from_postgis(sql, conn,geom_col='geometry',crs={'init':'epsg:4326'} )\n",
    "holc['holc_area'] = holc.to_crs({'init':'epsg:3857'}).area\n",
    "\n",
    "holc['HOLC_Grade']  = holc['HOLC_Grade'].replace({'Green':'A','Blue':'B','Yellow':'C','Red':'D'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "FHA Boundaries and Labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fha = gpd.read_file('../data/FHA Map/FHA Map.shp')\n",
    "fha['id']=fha.index.values\n",
    "fha = fha[fha['geometry'].isna()==False]\n",
    "fha =fha.rename(columns={'grade':'fha_grade'})\n",
    "fha['fha_area'] = fha.to_crs({'init':'epsg:3857'}).area\n",
    "\n",
    "fha1= fha.copy()\n",
    "fha1['geom'] = fha1['geometry'].apply(lambda x: WKTElement(x.wkt, srid=4326))\n",
    "fha1 = fha1.drop(columns=['geometry'])\n",
    "fha1.to_sql('fha_chicago', engine, if_exists='replace', index=False, \n",
    "                         dtype={'geom': Geometry('Polygon', srid= 4326)})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All Census data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = 'select * from us_tract_1930_conflated_4326'\n",
    "df_1930 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_1940_conflated_4326'\n",
    "df_1940 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_1950_conflated_4326'\n",
    "df_1950 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_1960_conflated_4326'\n",
    "df_1960 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_1970_conflated_4326'\n",
    "df_1970 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_1980_conflated_4326'\n",
    "df_1980 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_1990_conflated_4326'\n",
    "df_1990 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_2000_conflated_4326'\n",
    "df_2000 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "sql = 'select * from us_tract_2010_conflated_4326'\n",
    "df_2010 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Optional read a version already intersected with HOLC and reducing total tracts to crosswalk later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sql = 'select a.* from us_tract_1930_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_1930 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_1940_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_1940 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_1950_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_1950 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_1960_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_1960 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_1970_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_1970 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_1980_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_1980 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_1990_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_1990 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_2000_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_2000 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "# sql = 'select a.* from us_tract_2010_conflated_4326 a, holc_updated_4326 b where st_intersects(a.geom,b.geom)'\n",
    "# df_2010 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. 1940s Tracts - HOLC Labels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1 Create an overlay btwn 1940 Tracts and HOLC\n",
    "- Find all the \"pieces\" of tracts that overlap with an HOLC area\n",
    "- Find the biggest piece for each tract\n",
    "- If this piece has more than 25% tract coverage, it will be the main grade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_pieces = gpd.overlay(us_tract_1940_conflated_4326[['geom', 'id', 'gisjoin','tract_area']], holc, how='intersection')\n",
    "\n",
    "us_tract_1940_pieces = us_tract_1940_pieces[us_tract_1940_pieces.HOLC_Grade.isin(['C', 'D', 'B', 'A', 'E', 'Yellow', 'Green', 'Blue','Red'])]\n",
    "us_tract_1940_pieces['perc_tract'] = us_tract_1940_pieces.to_crs({'init':'epsg:3857'}).area/us_tract_1940_pieces['tract_area']\n",
    "us_tract_1940_pieces['perc_holc'] = us_tract_1940_pieces.to_crs({'init':'epsg:3857'}).area/us_tract_1940_pieces['holc_area']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 For each tract we want to find the dominant group\n",
    "For each tract, we want to keep the label that has the largest percentage overlap with the trac. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_pieces_tracts  =us_tract_1940_pieces.sort_values(['gisjoin','perc_tract']).groupby('gisjoin').tail(1)\n",
    "us_tract_1940_pieces_holc  =us_tract_1940_pieces.sort_values(['gisjoin','perc_holc']).groupby('gisjoin').tail(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_pieces1 = us_tract_1940_pieces.sort_values(['gisjoin','perc_tract'],ascending=False).groupby('gisjoin').first().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_pieces1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 Final Tract Labeling\n",
    "We only want to keep those tracts that have at least 25% overlap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tract_thres = 0.25\n",
    "us_tract_1940_holc_final = us_tract_1940_conflated_4326[us_tract_1940_conflated_4326.gisjoin.isin(\n",
    "                                        us_tract_1940_pieces1[us_tract_1940_pieces1['perc_tract']>tract_thres].gisjoin)].merge(us_tract_1940_pieces1[['gisjoin','map_id','HOLC_ID','map_name','HOLC_Grade','HOLC_Lette','name']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.4 Export to postgis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_holc_final['geom'] = us_tract_1940_holc_final['geom'].apply(lambda x: WKTElement(x.wkt, srid=4326))\n",
    "\n",
    "us_tract_1940_holc_final.to_sql('us_tract_1940_holc_final', engine, if_exists='replace', index=False, \n",
    "                         dtype={'geom': Geometry('MultiPolygon', srid= 4326)})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. 1940s Tracts - FHA Labels\n",
    "This only applies for Chicago for now. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_pieces_fha = gpd.overlay(us_tract_1940_conflated_4326[['geom', 'id', 'gisjoin','tract_area']], fha, how='intersection')\n",
    "\n",
    "us_tract_1940_pieces_fha['perc_tract'] = us_tract_1940_pieces_fha.to_crs({'init':'epsg:3857'}).area/us_tract_1940_pieces_fha['tract_area']\n",
    "us_tract_1940_pieces_fha['perc_fha'] = us_tract_1940_pieces_fha.to_crs({'init':'epsg:3857'}).area/us_tract_1940_pieces_fha['fha_area']\n",
    "\n",
    "\n",
    "us_tract_1940_pieces_tracts_fha  =us_tract_1940_pieces_fha.sort_values(['gisjoin','perc_tract']).groupby('gisjoin').tail(1)\n",
    "us_tract_1940_pieces_fha1  =us_tract_1940_pieces_tracts_fha.sort_values(['gisjoin','perc_fha']).groupby('gisjoin').tail(1)\n",
    "us_tract_1940_pieces1_fha = us_tract_1940_pieces_fha1.sort_values(['gisjoin','perc_tract'],ascending=False).groupby('gisjoin').first().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tract_thres = 0.25\n",
    "us_tract_1940_fha_final = us_tract_1940_conflated_4326[us_tract_1940_conflated_4326.gisjoin.isin(\n",
    "                                        us_tract_1940_pieces1_fha[us_tract_1940_pieces1_fha['perc_tract']>tract_thres].gisjoin)].merge(us_tract_1940_pieces1_fha[['gisjoin','id_2','fha_area','fha_grade','perc_fha']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_tract_1940_fha_final['geom'] = us_tract_1940_fha_final['geom'].apply(lambda x: WKTElement(x.wkt, srid=4326))\n",
    "\n",
    "us_tract_1940_fha_final.to_sql('us_tract_1940_fha_final', engine, if_exists='replace', index=False, \n",
    "                         dtype={'geom': Geometry('MultiPolygon', srid= 4326)})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Create a crosswalk back to 1940s census tracts and weighted census values\n",
    "Because we want to compare each decade's tract data to each other, we need to create a decade by decade cross walk back to 1940s. Using 1940s census tract levels so that we are mostly aggregating the data rather than splitting it up. Ultimately, we want a list of corresponding tract numbers for each 1940 tract. This can be one or many tracts. We're going to assume a population weighted sum for all the cross-walks.\n",
    "\n",
    "\n",
    "This will involve: \n",
    "- Doing a spatial join between decade YYYY and census tract from 1940\n",
    "- For each 1940 tract, we want to create a set of weights for other census tracts that intersect with the 1940s tract: \n",
    "    $$w_{i_{j,t}} = \\frac{a_{i_{j,t} \\cap t={1940}}}{a_{i_{j,t}}}$$ for 1940s tract $i$ and tract pieces $j$ if $\\frac{a_{i_{j,t} \\cap t={1940}}}{a_{i_{j,t}}}>.01$ In other words, the overlap has to be at least 1%\n",
    "- Find the population contribution that each region makes to the HOLC zone $$p_{i_{j,t}}w_{i_{j,t}}$$\n",
    "- Find the weighted average of each zone based on population  $$ \\frac{x_{i_{j,t}} p_{i_{j,t}}w_{i_{j,t}}}{\\sum_j p_{i_{j,t}}w_{i_{j,t}} }$$ \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# features=['population','white','white_perc','black','black_perc','other',\n",
    "#  'other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    "#  'median_income_adj','median_income','hispanic_perc','hispanic','unemployed','unemployed_perc']\n",
    "\n",
    "def get_weighted_values(year1,year2, perc=0.01):\n",
    "    ## 2016 ACS data uses 2010 Census boundaries, though 2016 data not ultimately used in analysis. \n",
    "    \n",
    "    sql ='''\n",
    "    \n",
    "    select * from (select x.*, y.gisjoin_1940, y.geom as geom_using,\n",
    "    st_area(st_intersection(x.geom,y.geom))/st_area(y.geom)::decimal as tract_1940_perc,\n",
    "    st_area(st_intersection(x.geom,y.geom))/st_area(x.geom)::decimal as tract_perc\n",
    "    from\n",
    "    (select distinct a.*,b.geom from (select distinct * from census_{} as a) as a, (select distinct * from us_tract_{}_conflated_4326 as b)  as b \n",
    "    where a.\"GISJOIN\" = b.gisjoin) as x, \n",
    "    (select distinct gisjoin as gisjoin_1940, geom from (\n",
    "        select distinct * from us_tract_1940_conflated_4326) as y\n",
    "        ) as y\n",
    "    where st_intersects(x.geom, y.geom)) as t where tract_perc >.01\n",
    "    '''.format(year1,year2)\n",
    "  \n",
    "        \n",
    "    ### Get all the census units who's area overlap is greater than the min threshold\n",
    "    df_overlay_1 = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geom_using' )\n",
    "    print(df_overlay_1.shape)\n",
    "    \n",
    "    ### Create a df that groups by the 1940s tracts\n",
    "    df_overlay=df_overlay_1.groupby(['gisjoin_1940']).count()[['index']]\n",
    "    df_overlay=df_overlay.rename(columns={'index':'count'})\n",
    "    \n",
    "    df_overlay_2 = df_overlay_1[df_overlay_1['tract_1940_perc']>perc]\n",
    "    for each in features[year1]: \n",
    "\n",
    "        ### for the other features, use a population-weighted average\n",
    "        weighted_sum = df_overlay_2.groupby(['gisjoin_1940']).apply(lambda x: np.sum(x[each]*x['population']*x['tract_perc'])/ np.sum(x['population']*x['tract_perc']))\n",
    "        df_overlay[each]=weighted_sum\n",
    "    df_overlay=df_overlay.fillna(0)\n",
    "    print(df_overlay.shape)\n",
    "    return df_overlay.reset_index(),df_overlay_1.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1940 = gpd.GeoDataFrame.from_postgis(\"select distinct * from us_tract_1940_conflated_4326\", conn,crs={'init':'epsg:4326'}, geom_col='geom' )\n",
    "\n",
    "def merge_overlay_geom(df,name):\n",
    "    df_merge = df_1940[['gisjoin','geom']].merge(df.reset_index(),\n",
    "                   left_on='gisjoin',\n",
    "                   right_on='gisjoin_1940')\n",
    "\n",
    "    #### Create the population density meausure\n",
    "    #### Update: Going to convert to geography and gonna do this in postgis \n",
    "    df_merge['population_density']=df_merge['population']/df_merge.geometry.to_crs({'init': 'epsg:3857'}).area\n",
    "    df_merge['geom'] = df_merge['geom'].apply(lambda x: WKTElement(x.wkt, srid=4326))\n",
    "    df_merge.to_sql('{}'.format(name), engine, if_exists='replace', index=False, \n",
    "                             dtype={'geom': Geometry('MultiPolygon', srid= 4326)})\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Features using"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "features = {1930:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','owned_perc'],\n",
    "            1940:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    "                  'owned_perc'],\n",
    "            1950:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    " 'median_income_adj','median_income','owned_perc'],\n",
    "            1960:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    " 'median_income_adj','median_income','hispanic_perc','hispanic','owned_perc'],\n",
    "            1970:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    " 'median_income_adj','median_income','hispanic_perc','hispanic','asian_perc','asian','unemployed','unemployed_perc','owned_perc'],\n",
    "            1980:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    " 'median_income_adj','median_income','hispanic_perc','hispanic','asian_perc','asian','unemployed','unemployed','unemployed_perc','owned_perc'],\n",
    "            1990:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    " 'median_income_adj','median_income','hispanic_perc','hispanic','asian_perc','asian','unemployed','unemployed','unemployed_perc','owned_perc'],\n",
    "            2000:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    " 'median_income_adj','median_income','hispanic_perc','hispanic','asian_perc','asian','unemployed','unemployed','unemployed_perc','owned_perc'],\n",
    "            2010:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    " 'median_income_adj','median_income','hispanic_perc','hispanic','asian_perc','asian','unemployed','asian_perc','asian','unemployed','unemployed','unemployed_perc','owned_perc'],\n",
    "#             2016:['population','white','white_perc','black','black_perc','other','other_perc','median_homevalue','median_homevalue_adj','homes','college','college_perc',\n",
    "#  'median_income_adj','median_income','hispanic_perc','hispanic','asian_perc','asian','unemployed','unemployed_perc','owned_perc']}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for y in list(features.keys()):\n",
    "    print (y)\n",
    "    if y !=2016:\n",
    "        y1 = y\n",
    "        y2 = y   \n",
    "    else: \n",
    "        y1 = y\n",
    "        y2= 2010\n",
    "    df,df_1 = get_weighted_values(y1,y2)\n",
    "    print(df.shape)\n",
    "    merge_overlay_geom(df,'census_1940_overlay_{}_new'.format(y1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Concat all the Census data and save as table\n",
    "Here reloading to doublecheck"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = [1930,1940,1950,1960,1970,1980,1990,2000,2010,2016]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get data for all the years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_crosswalked_data(year):\n",
    "\n",
    "    sql= f'''\n",
    "    select * from census_1940_overlay_{year}_new\n",
    "    '''\n",
    "    df = gpd.GeoDataFrame.from_postgis(sql, conn,geom_col='geom',crs={'init':'epsg:4326'} )\n",
    "    return df\n",
    "\n",
    "df = pd.concat([get_crosswalked_data(y) for y in years])\n",
    "df=df.fillna(0)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HOLC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t =  df[df.gisjoin_1940.isin(us_tract_1940_holc_final.gisjoin)]\n",
    "t = df.merge(us_tract_1940_holc_final[['gisjoin','map_id', 'HOLC_ID', 'map_name', 'HOLC_Grade',\n",
    "       'HOLC_Lette', 'name']],on='gisjoin')\n",
    "\n",
    "t.to_postgis('all_tracts_holc_crosswalked_FINAL', engine, if_exists='replace')\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "FHA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f =  df[df.gisjoin_1940.isin(us_tract_1940_fha_final.gisjoin)]\n",
    "f= df.merge(us_tract_1940_fha_final[['gisjoin','id_2', 'fha_area', 'fha_grade', 'perc_fha']],on='gisjoin')\n",
    "\n",
    "f.to_postgis('all_tracts_fha_crosswalked_FINAL', engine, if_exists='replace')\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.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}