00_AllData_CleanandCombine.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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",
    "\n",
    "0. Prepare HOLC data (for some duplicate geometries and mis-labeled cities)\n",
    "1. Read in the cleaned HOLC data\n",
    "2. Read in relevant Decennial Census data (1930 - 2016, 2016 is 5yr ACS)\n",
    "3. OPTIONAL: For population-weighted HOLC areas.\n",
    "4. OPTIONAL: Write the results from 3. to shapefiles, postgis tables, upload to mapbox or carto.\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. Prepare HOLC Data\n",
    "Note: Since 2018 there are new polgons. \n",
    "\n",
    "It seems like some  cities are all `map_id`=0. I made the following changes in the Map IDs: \n",
    "- Phoenix = 1\n",
    "- Pueblo, CO = 2\n",
    "- Rochester = 3\n",
    "- Davenport, IA = 4\n",
    "- Covington, KY = 5\n",
    "- Hartford, CT = 6\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 'holc_polygons_update.shp' is the latest version of the HOLC dataset\n",
    "\n",
    "holc_all = gpd.read_file('/data/holc_polygons_update/holc_polygons_update.shp').to_crs('epsg:4326')\n",
    "\n",
    "### remove Buffalo, Oklahoma City, because it's in there twice\n",
    "holc_all = holc_all[holc_all.map_id.isin([111,235])==False]\n",
    "holc_all1 = holc_all.groupby(['map_id','HOLC_Grade','SHAPE_Leng']).first().reset_index()\n",
    "holc_all1.to_file('/data/holc_polygons_update/holc_polygons_update_noZ_dedup.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "holc_bronx = gpd.read_file('data/holc_polygons_update/holc_polygons_update_noZ.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "holc_bronx1 = holc_bronx[holc_bronx.map_id==98].to_crs('epsg:4326')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "holc_all1 = gpd.GeoDataFrame( pd.concat( [holc_all,holc_bronx1], ignore_index=True) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert Map IDs to City name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "id_to_city = {27:'New Haven',\n",
    "             0:'San Antonio',\n",
    "              1: 'Phoenix',\n",
    "              2:'Pueblo',\n",
    "              3:'Rochester',\n",
    "              4:'Davenport',\n",
    "              5:'Covington',\n",
    "              6:'Hartford',\n",
    "              10:'Birmingham',\n",
    "              11:'Mobile',\n",
    "              12:'Montgomery',\n",
    "              13:'Little Rock',\n",
    "              15:'Fresno',\n",
    "              16:'Los Angeles',\n",
    "              17:'Oakland',\n",
    "              18:'Sacramento',\n",
    "              19:'San Diego',\n",
    "              20:'San Francisco',\n",
    "              21:'San Jose',\n",
    "              22:'Stockton',\n",
    "              23:'Denver',\n",
    "              27:'New Haven',\n",
    "              28:'Stamford',\n",
    "              29:'Jacksonville',\n",
    "              30:\"Miami\",\n",
    "              31:'St. Petersburg',\n",
    "              32:'Tampa',\n",
    "              33:'Atlanta',\n",
    "              34:'Augusta',\n",
    "              35:'Columbus',\n",
    "              36:'Macon',\n",
    "              38:'Council Buffs',\n",
    "              40:'Des Moines',\n",
    "              41:'Dubuque',\n",
    "              42:'Sioux City',\n",
    "              43:'Waterloo',\n",
    "              44:'Aurora',\n",
    "              45:'Chicago',\n",
    "              46:'Decatur',\n",
    "              47:'East St. Louis',\n",
    "              48:\"Jolie\",\n",
    "              49:\"Rockford\",\n",
    "              50:'Springfield',\n",
    "              51:'Evansville',\n",
    "              52:'Fort Wayne',\n",
    "              53:'Indianapolis',\n",
    "              54:'Calumet City',\n",
    "              55:'Muncie',\n",
    "              56:'South Bend',\n",
    "              57:\"Terre Haute\",\n",
    "              58:'Topeka',\n",
    "              59:'Wichita',\n",
    "              61:'Lexington',\n",
    "              62:'Louisville',\n",
    "              63:'New Orleans',\n",
    "              64:'Shreveport',\n",
    "              68:'Baltimore',\n",
    "              69:'Battlecreek',\n",
    "              70:'Bay City',\n",
    "              71:'Detroit',\n",
    "              72:'Flint',\n",
    "              73:'Grand Rapids',\n",
    "              75:'Kalamazoo',\n",
    "              76:'Lansing',\n",
    "              77:'Muskegon',\n",
    "              78:'Pontiac',\n",
    "              79:'Sagninaw',\n",
    "              80:'Duluth',\n",
    "              81:'Minneapolis',\n",
    "              83:'St. Paul',\n",
    "              85:'St. Louis',\n",
    "              86:'Springfield',\n",
    "              87:'Saint Joseph',\n",
    "              90:'Asheville',\n",
    "              91:'Charlotte',\n",
    "              92:'Durham',\n",
    "              93:'Greensboro',\n",
    "              94:'Winston-Salem',\n",
    "              95:'Hoboken-Jersey City-East Newark',\n",
    "              96:'Newark',\n",
    "              98:'Bronx',\n",
    "              99:'Brooklyn',\n",
    "              101:'Manhattan',\n",
    "              102:'Niagara Falls',\n",
    "              103:'Poughkeepsie',\n",
    "              104:'Queens',\n",
    "              105:'Rochester',\n",
    "              106:'Schenechtady',\n",
    "              107:'Staten Island',\n",
    "              108:'Syracuse',\n",
    "              109:'Troy',\n",
    "              110:'Utica',\n",
    "              111:'Buffalo',\n",
    "              114:'Akron',\n",
    "              115:'Cleveland',\n",
    "              116:'Columbus',\n",
    "              117:'Dayton',\n",
    "              118:'Hamilton',\n",
    "              119:'Lima',\n",
    "              120:'Lorain',\n",
    "              121:'Portsmouth',\n",
    "              122:'Springfield',\n",
    "              123:'Toledo',\n",
    "              124:'Warren',\n",
    "              125:'Youngstown',\n",
    "              127:'Tulsa',\n",
    "              128:'Portland',\n",
    "              132:'Erie',\n",
    "              134:'Johnstown',\n",
    "              137:'New Castle',\n",
    "              138:'Philadelphia',\n",
    "              139:'Pittsburgh',\n",
    "              147:'Chattanooga',\n",
    "              148:'Knoxville',\n",
    "              154:'Dallas',\n",
    "              158:'Houston',\n",
    "              164:'Lynchburg',\n",
    "              165:'Newport News',\n",
    "              166:'Norfolk',\n",
    "              168:'Richmond',\n",
    "              169:'Roanoke',\n",
    "              170:'Seattle',\n",
    "              171:'Spokane',\n",
    "              172:'Tacoma',\n",
    "              173:'Kenosha',\n",
    "              174:'Madison',\n",
    "              175:'Oshkosh',\n",
    "              176:'Racine',\n",
    "              177:'Charleston',\n",
    "              179:'Wheeling',\n",
    "              181:'Hartford',\n",
    "              182:'New Britain',\n",
    "              183:'Waterbury',\n",
    "              184:'Brockton',\n",
    "              185:'Haverhill',\n",
    "              186:'Holyoke',\n",
    "              188:'Manchester',\n",
    "              189:'Atlantic City',\n",
    "              190:'Hackensack',\n",
    "              191:'Camden',\n",
    "              192:'Trenton',\n",
    "              193:'Albany',\n",
    "              194:'Binghamton',\n",
    "              195:'Buffalo',\n",
    "              196:'Elmira',\n",
    "              197:'Yonkers',\n",
    "              198:'Canton',\n",
    "              201:'Milwaukee',\n",
    "              224:'Jackson',\n",
    "              226:'Kansas City',\n",
    "              236:'Oklahoma City',\n",
    "              237:'Altoona',\n",
    "              265:'Arlington',\n",
    "              266:'Belmont',\n",
    "              267:'Boston',\n",
    "              268:'Braintree',\n",
    "              269:'Brookline',\n",
    "              270:'Cambridge',\n",
    "              271:'Chelsea',\n",
    "              272:'Dedham',\n",
    "              273:'Everett',\n",
    "              274:'Lexington',\n",
    "              275:'Malden',\n",
    "              276:'Medford',\n",
    "              277:'Melrose',\n",
    "              278:'Milton',\n",
    "              279:'Needham',\n",
    "              280:'Newton',\n",
    "              281:'Quincy',\n",
    "              282:'Revere',\n",
    "              283:'Saugus',\n",
    "              284:'Somerville',\n",
    "              285:'Waltham',\n",
    "              286:'Watertown',\n",
    "              287:'Winchester',\n",
    "              288:'Winthrop',\n",
    "              319:'Nashville'\n",
    "             }\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "holc_all1['map_name'] = holc_all1['map_id'].apply(lambda x:id_to_city[x] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "holc_all1.to_postgis(name=\"holc_all_dump_4326\",con= engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "****"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(If re-running, start here)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Read cleaned HOLC Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/wenfeixu/anaconda3/envs/gds/lib/python3.7/site-packages/pyproj/crs/crs.py:53: FutureWarning: '+init=<authority>:<code>' syntax is deprecated. '<authority>:<code>' is the preferred initialization method. When making the change, be mindful of axis order changes: https://pyproj4.github.io/pyproj/stable/gotchas.html#axis-order-changes-in-proj-6\n",
      "  return _prepare_from_string(\" \".join(pjargs))\n"
     ]
    }
   ],
   "source": [
    "sql = '''\n",
    "select * from holc_all_dump_4326\n",
    "'''\n",
    "holc_all = gpd.GeoDataFrame.from_postgis(sql, conn,crs={'init':'epsg:4326'}, geom_col='geometry' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>map_id</th>\n",
       "      <th>HOLC_Grade</th>\n",
       "      <th>SHAPE_Leng</th>\n",
       "      <th>HOLC_ID</th>\n",
       "      <th>HOLC_Lette</th>\n",
       "      <th>polygon_id</th>\n",
       "      <th>SHAPE_Area</th>\n",
       "      <th>sheets</th>\n",
       "      <th>name</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>map_name</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>17623.230575</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>5.861855e+06</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>POLYGON ((-98.48526 29.45325, -98.49215 29.453...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>21230.295569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>9.326094e+06</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>POLYGON ((-98.52972 29.46838, -98.52966 29.468...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>B</td>\n",
       "      <td>3763.973058</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "      <td>7.605122e+05</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>POLYGON ((-98.48805 29.41147, -98.48800 29.411...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>B</td>\n",
       "      <td>5404.821964</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>1.072495e+06</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>POLYGON ((-98.51525 29.45301, -98.51520 29.452...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>B</td>\n",
       "      <td>6670.183791</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>13</td>\n",
       "      <td>1.747458e+06</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>POLYGON ((-98.49289 29.48830, -98.49218 29.488...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   map_id HOLC_Grade    SHAPE_Leng HOLC_ID HOLC_Lette  polygon_id  \\\n",
       "0       0          A  17623.230575    None       None          14   \n",
       "1       0          A  21230.295569    None       None           1   \n",
       "2       0          B   3763.973058    None       None          10   \n",
       "3       0          B   5404.821964    None       None           2   \n",
       "4       0          B   6670.183791    None       None          13   \n",
       "\n",
       "     SHAPE_Area  sheets  name  OBJECTID     map_name  \\\n",
       "0  5.861855e+06       0  None         0  San Antonio   \n",
       "1  9.326094e+06       0  None         0  San Antonio   \n",
       "2  7.605122e+05       0  None         0  San Antonio   \n",
       "3  1.072495e+06       0  None         0  San Antonio   \n",
       "4  1.747458e+06       0  None         0  San Antonio   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((-98.48526 29.45325, -98.49215 29.453...  \n",
       "1  POLYGON ((-98.52972 29.46838, -98.52966 29.468...  \n",
       "2  POLYGON ((-98.48805 29.41147, -98.48800 29.411...  \n",
       "3  POLYGON ((-98.51525 29.45301, -98.51520 29.452...  \n",
       "4  POLYGON ((-98.49289 29.48830, -98.49218 29.488...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holc_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Get All Census Data\n",
    "From 1930 - 1980 we use census tracts, 1990 onwards are block groups.\n",
    "\n",
    "\n",
    "Also make sure to prep all the data here. This includes: \n",
    "- creating the 'other' (non-white, non-black) \n",
    "- adjusting the median income levels for inflation to 2016 levels.\n",
    "- aggregating categories when necessary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1930 \n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each of the cities have their own file, so we'll need to join them together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1930_1 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds58_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_1 = census_1930_1.rename(columns={'BHI001':'population',\n",
    "                                          'BIQ001':'white_native',\n",
    "                                          'BIQ002':'white_foreignparents',\n",
    "                                          'BIQ003':'white_foreign',\n",
    "                                          'BIQ004':'black',\n",
    "                                        'BIQ005':'other'})\n",
    "\n",
    "census_1930_2 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds60_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_2 = census_1930_2.rename(columns={'BJW001':'population',\n",
    "                                          'BK1001':'white_native',\n",
    "                                          'BK1002':'white_foreignparents',\n",
    "                                          'BK1003':'white_foreign',\n",
    "                                          'BK1004':'black',\n",
    "                                          'BK1005':'other' })\n",
    "\n",
    "census_1930_3 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds62_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_3 = census_1930_3.rename(columns={'BLO001':'population',\n",
    "                                          'BLR001':'white_native',\n",
    "                                          'BLR002':'white_foreignparents',\n",
    "                                          'BLR003':'white_foreign',\n",
    "                                          'BLR004':'black',\n",
    "                                        'BLR005':'other'})\n",
    "\n",
    "census_1930_4 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds63_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_4 = census_1930_4.rename(columns={'BLW001':'population',\n",
    "                                           'BM4001':'white_native',\n",
    "                                          'BM4002':'white_foreignparents',\n",
    "                                          'BM4003':'white_foreign',\n",
    "                                          'BM4004':'black',\n",
    "                                        'BM4005':'other'})\n",
    "\n",
    "census_1930_5 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds64_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_5 = census_1930_5.rename(columns={'BND001':'population'})\n",
    "\n",
    "census_1930_6 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds65_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_6 = census_1930_6.rename(columns={'BNE001':'population',\n",
    "                                          'BNP001':'white_native',\n",
    "                                          'BNP002':'white_foreignparents',\n",
    "                                          'BNP003':'white_foreign',\n",
    "                                          'BNP004':'black',\n",
    "                                        'BNP005':'other'})\n",
    "\n",
    "census_1930_7 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds66_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_7 = census_1930_7.rename(columns={'BOI001':'population'})\n",
    "\n",
    "census_1930_8 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds67_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_8 = census_1930_8.rename(columns={'BOJ001':'population',\n",
    "                                          'BOK001':'white_native',\n",
    "                                          'BOK002':'white_foreignparents',\n",
    "                                          'BOK003':'white_foreign',\n",
    "                                          'BOK004':'black',\n",
    "                                        'BOK005':'other'})\n",
    "\n",
    "census_1930_9 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds68_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_9 = census_1930_9.rename(columns={'BOO001':'population',\n",
    "                                          'BPW001':'white_native',\n",
    "                                          'BPW002':'white_foreignparents',\n",
    "                                          'BPW003':'white_foreign',\n",
    "                                          'BPW004':'black',\n",
    "                                        'BPW005':'other'})\n",
    "\n",
    "census_1930_10 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds69_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_10 = census_1930_10.rename(columns={'BQM001':'population'})\n",
    "\n",
    "census_1930_11 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds70_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_11 = census_1930_11.rename(columns={'BQQ001':'population',\n",
    "                                          'BRP001':'white_native',\n",
    "                                          'BRP002':'white_foreignparents',\n",
    "                                          'BRP003':'white_foreign',\n",
    "                                          'BRP004':'black',\n",
    "                                        'BRP005':'other'})\n",
    "\n",
    "census_1930_12 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds71_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_12 = census_1930_12.rename(columns={'BRQ001':'population',\n",
    "                                          'BRT001':'white_native',\n",
    "                                          'BRT002':'white_foreignparents',\n",
    "                                          'BRT003':'white_foreign',\n",
    "                                          'BRT004':'black',\n",
    "                                        'BRT005':'other'})\n",
    "census_1930_13 = pd.read_csv('data/NHGIS/census_1930/nhgis0059_ds59_1930_tract.csv',index_col=None, header=0)\n",
    "census_1930_13 = census_1930_13.rename(columns={'BI1001':'population',\n",
    "                                          'BJR001':'white_native',\n",
    "                                          'BJR002':'white_foreign',\n",
    "                                          'BJR003':'black'}) ### no other"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregate all the cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1930 = pd.concat([census_1930_1,census_1930_2,census_1930_3,census_1930_4,census_1930_5,\n",
    "                       census_1930_6,census_1930_7,census_1930_8,census_1930_9,census_1930_10,\n",
    "                       census_1930_11,census_1930_12,census_1930_13])\n",
    "#### Cleanup\n",
    "census_1930 = census_1930.drop_duplicates()\n",
    "census_1930 = census_1930.fillna(0)\n",
    "\n",
    "census_1930=census_1930[census_1930.population>0]\n",
    "census_1930=census_1930.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1930['white']=census_1930['white_foreign']\\\n",
    "                        +census_1930['white_foreignparents']\\\n",
    "                        +census_1930['white_native']\n",
    "        \n",
    "census_1930['white_perc']=census_1930['white']/census_1930['population']\n",
    "census_1930['black_perc']=census_1930['black']/census_1930['population']\n",
    "census_1930['other_perc']=census_1930['other']/census_1930['population']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1930.to_sql('census_1930', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1940\n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- College education"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1940 = pd.read_csv('data/NHGIS/census_1940/nhgis0003_ds76_1940_tract.csv',index_col=None, header=0)\n",
    "census_1940 = census_1940.rename(columns={'BUB001':'population',\n",
    "                                          'BUQ001':'white',\n",
    "                                          'BUQ002':'black',\n",
    "                                          'BUH007':'college_male_1_3',\n",
    "                                          'BUH016':'college_female_1_3',\n",
    "                                          'BUH008':'college_male_4',\n",
    "                                          'BUH017':'college_female_4',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "census_1940['college'] = census_1940['college_male_1_3']+census_1940['college_female_1_3']\\\n",
    "                        +census_1940['college_male_4']+census_1940['college_female_4']\n",
    "census_1940 = census_1940.drop_duplicates()\n",
    "census_1940 = census_1940.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1940['white_perc']=census_1940['white']/census_1940['population']\n",
    "census_1940['black_perc']=census_1940['black']/census_1940['population']\n",
    "\n",
    "### No \"other\" category\n",
    "census_1940['college_perc'] = (census_1940['college'])/ census_1940['population']\n",
    "\n",
    "#### Cleanup\n",
    "census_1940=census_1940[census_1940.population>0]\n",
    "census_1940=census_1940.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1940.to_sql('census_1940', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1950\n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- College education, \n",
    "- Median income\n",
    "- Median value of home"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1950 = pd.read_csv('data/NHGIS/census_1950/nhgis0021_ds82_1950_tract.csv',index_col=None, header=0)\n",
    "census_1950 = census_1950.rename(columns={'BZ8001':'population',\n",
    "                                          'B0J001':'white',\n",
    "                                          'B0J002':'black',\n",
    "                                          'B0J003':'other',\n",
    "                                          'B0B008':'college_1_3',\n",
    "                                          'B0B009':'college_4',\n",
    "                                          'B0F001':'median_income',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "census_1950['college'] = census_1950['college_1_3']+census_1950['college_4']\n",
    "\n",
    "### Adjust for inflation from 1949\n",
    "### from: http://www.in2013dollars.com/1949-dollars-in-2016\n",
    "infl_factor_1950= 10.08433\n",
    "census_1950['median_income_adj'] = infl_factor_1950*census_1950['median_income']\n",
    "census_1950 = census_1950.drop_duplicates()\n",
    "census_1950 = census_1950.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1950['white_perc']=census_1950['white']/(census_1950['white']+census_1950['black']+census_1950['other'])\n",
    "census_1950['black_perc']=census_1950['black']/(census_1950['white']+census_1950['black']+census_1950['other'])\n",
    "census_1950['other_perc']=census_1950['other']/(census_1950['white']+census_1950['black']+census_1950['other'])\n",
    "\n",
    "census_1950['college_perc'] = (census_1950['college'] )/ census_1950['population']\n",
    "\n",
    "### Adjust for inflation from 1949\n",
    "### from: http://www.in2013dollars.com/1949-dollars-in-2016\n",
    "infl_factor_1950= 10.08433\n",
    "census_1950['median_income_adj']= infl_factor_1950*census_1950['median_income'].astype(float)\n",
    "\n",
    "#### Cleanup\n",
    "census_1950=census_1950[census_1950.population>0]\n",
    "census_1950=census_1950[(census_1950['white']+census_1950['black']+census_1950['other'])>0]\n",
    "census_1950=census_1950.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28250.96270985663"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_1950['median_income_adj'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1950.to_sql('census_1950', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1960\n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- Education\n",
    "- Income broken down by bins\n",
    "\n",
    "For 1960, since there are only median income clases, we find the median income by simulating incomes based on income ranges and getting the median."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/wenfeixu/anaconda2/envs/geo_wenfei/lib/python3.6/site-packages/numpy/core/fromnumeric.py:2957: RuntimeWarning: Mean of empty slice.\n",
      "  out=out, **kwargs)\n",
      "/Users/wenfeixu/anaconda2/envs/geo_wenfei/lib/python3.6/site-packages/numpy/core/_methods.py:80: RuntimeWarning: invalid value encountered in double_scalars\n",
      "  ret = ret.dtype.type(ret / rcount)\n"
     ]
    }
   ],
   "source": [
    "census_1960 = pd.read_csv('data/NHGIS/census_1960/nhgis0021_ds92_1960_tract.csv',index_col=None, header=0)\n",
    "census_1960 = census_1960.rename(columns={'CA4001':'population',\n",
    "                                          'B7B001':'white',\n",
    "                                          'B7B002':'black',\n",
    "                                          'B7B003':'other',\n",
    "                                          'CA7001':'spanish_origin',\n",
    "                                          'B8R007':'college_1_3',\n",
    "                                          'B8R008':'college_4',\n",
    "                                          'B8W001':'less_1000',\n",
    "                                          'B8W002':'1000_1999',\n",
    "                                          'B8W003':'2000_2999',\n",
    "                                          'B8W004':'3000_3999',\n",
    "                                          'B8W005':'4000_4999',\n",
    "                                          'B8W006':'5000_5999',\n",
    "                                          'B8W007':'6000_6999',\n",
    "                                          'B8W008':'7000_7999',\n",
    "                                          'B8W009':'8000_8999',\n",
    "                                          'B8W010':'9000_9999',\n",
    "                                          'B8W011':'10000_14999',\n",
    "                                          'B8W012':'15000_24999',\n",
    "                                          'B8W013':'25000_over',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "census_1960['hispanic']=census_1960['spanish_origin']\n",
    "census_1960['college']=(census_1960['college_1_3']+ census_1960['college_4'] )\n",
    "\n",
    "### Since median income is measured by households instead of population, recreate a households category\n",
    "census_1960['households']=(census_1960['less_1000']+census_1960['1000_1999']+census_1960['2000_2999']\\\n",
    "+census_1960['3000_3999']+census_1960['4000_4999'] +census_1960['5000_5999']\\\n",
    "+census_1960['6000_6999']+census_1960['7000_7999']+census_1960['8000_8999']\\\n",
    "+census_1960['9000_9999'] +census_1960['10000_14999']+census_1960['15000_24999']\\\n",
    "+census_1960['25000_over'])\n",
    "\n",
    "census_1960 = census_1960.drop_duplicates()\n",
    "census_1960 = census_1960.fillna(0)\n",
    "\n",
    "### Adjust for inflation in 1959\n",
    "### from: http://www.in2013dollars.com/1960-dollars-in-2016\n",
    "infl_factor_1960=8.24766\n",
    "\n",
    "census_1960['median_income']=census_1960.apply(lambda x:np.median(np.sort(np.concatenate([np.random.randint(0,999, size=int(x['less_1000'])),\n",
    "        np.random.randint(1000,1999, size=int(x['1000_1999'])),\n",
    "        np.random.randint(2000,2999, size=int(x['2000_2999'])),\n",
    "        np.random.randint(3000,3999, size=int(x['3000_3999'])),\n",
    "        np.random.randint(4000,4999, size=int(x['4000_4999'])),\n",
    "        np.random.randint(5000,5999, size=int(x['5000_5999'])),\n",
    "        np.random.randint(6000,6999, size=int(x['6000_6999'])),\n",
    "        np.random.randint(7000,7999, size=int(x['7000_7999'])),\n",
    "        np.random.randint(8000,8999, size=int(x['8000_8999'])),\n",
    "        np.random.randint(9000,9999, size=int(x['9000_9999'])),\n",
    "        np.random.randint(10000,14999, size=int(x['10000_14999'])),\n",
    "        np.random.randint(15000,24999, size=int(x['15000_24999'])),\n",
    "                                                             np.random.randint(25000,50000, size=int(x['25000_over']))\n",
    "                                                            ]))),axis=1)\n",
    "\n",
    "\n",
    "census_1960['median_income_adj']= infl_factor_1960*census_1960['median_income']\n",
    "\n",
    "census_1960 = census_1960.drop_duplicates()\n",
    "census_1960 = census_1960.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51312.81669"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_1960['median_income_adj'].median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### Population and race population don't add up\n",
    "census_1960['white_perc']=census_1960['white']/(census_1960['white']+census_1960['black']+census_1960['other'])\n",
    "\n",
    "census_1960['black_perc']=census_1960['black']/(census_1960['white']+census_1960['black']+census_1960['other'])\n",
    "census_1960['hispanic_perc']=census_1960['spanish_origin']/(census_1960['population'])\n",
    "census_1960['other_perc']=census_1960['other']/(census_1960['white']+census_1960['black']+census_1960['other'])\n",
    "\n",
    "census_1960['college_perc'] = (census_1960['college'] )/ census_1960['population']\n",
    "\n",
    "#### Cleanup\n",
    "census_1960=census_1960[census_1960.population>0]\n",
    "census_1960=census_1960[(census_1960['white']+census_1960['black']+census_1960['other'])>0]\n",
    "census_1960=census_1960.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1960.to_sql('census_1960', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1970\n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- Education\n",
    "- Median Income\n",
    "- Employment Status\n",
    "\n",
    "For 1970, since the aggregate median incomes don't look correct. We use the median income clases and find the median income by simulating incomes based on income ranges and getting the median."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/wenfeixu/anaconda2/envs/geo_wenfei/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (9) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "/Users/wenfeixu/anaconda2/envs/geo_wenfei/lib/python3.6/site-packages/numpy/core/fromnumeric.py:2957: RuntimeWarning: Mean of empty slice.\n",
      "  out=out, **kwargs)\n",
      "/Users/wenfeixu/anaconda2/envs/geo_wenfei/lib/python3.6/site-packages/numpy/core/_methods.py:80: RuntimeWarning: invalid value encountered in double_scalars\n",
      "  ret = ret.dtype.type(ret / rcount)\n"
     ]
    }
   ],
   "source": [
    "census_1970_1 = pd.read_csv('data/NHGIS/census_1970/nhgis0027_ds98_1970_tract.csv',index_col=None, header=0)\n",
    "census_1970_2 = pd.read_csv('data/NHGIS/census_1970/nhgis0027_ds99_1970_tract.csv',index_col=None, header=0)\n",
    "census_1970_3 = pd.read_csv('data/NHGIS/census_1970/nhgis0027_ds95_1970_tract.csv',index_col=None, header=0)\n",
    "census_1970_4 = pd.read_csv('data/NHGIS/census_1970/nhgis0027_ds97_1970_tract.csv',index_col=None, header=0)\n",
    "census_1970_5 = pd.read_csv('data/NHGIS/census_1970/nhgis0029_ds99_1970_tract.csv',index_col=None, header=0)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "census_1970_1 = census_1970_1.rename(columns={'C1I001':'population',\n",
    "                                          'C0X001':'white',\n",
    "                                          'C0X002':'black',\n",
    "                                          'C0X003':'other',\n",
    "                                          'C06008':'college_1_3',\n",
    "                                          'C06009':'college_4',\n",
    "                                          'C06010':'college_5_more',\n",
    "                                          'C07003':'unemployed',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "\n",
    "census_1970_2 = census_1970_2.rename(columns={\n",
    "                                          'C3T001':'less_1000',\n",
    "                                          'C3T002':'1000_1999',\n",
    "                                          'C3T003':'2000_2999',\n",
    "                                          'C3T004':'3000_3999',\n",
    "                                          'C3T005':'4000_4999',\n",
    "                                          'C3T006':'5000_5999',\n",
    "                                          'C3T007':'6000_6999',\n",
    "                                          'C3T008':'7000_7999',\n",
    "                                          'C3T009':'8000_8999',\n",
    "                                          'C3T010':'9000_9999',\n",
    "                                          'C3T011':'10000_11999',\n",
    "                                          'C3T012':'12000_14999',\n",
    "                                          'C3T013':'15000_24999',\n",
    "                                          'C3T014':'25000_49999',\n",
    "                                          'C3T015':'50000_over',\n",
    "                                            'GISJOIN':'gisjoin'})\n",
    "\n",
    "\n",
    "\n",
    "census_1970_2 = census_1970_2.fillna(0)\n",
    "census_1970_2 = census_1970_2.replace(-1,0)\n",
    "\n",
    "census_1970_2['median_income']=census_1970_2.apply(lambda x:np.median(np.sort(np.concatenate([np.random.randint(0,999, size=int(x['less_1000'])),\n",
    "        np.random.randint(1000,1999, size=int(x['1000_1999'])),\n",
    "        np.random.randint(2000,2999, size=int(x['2000_2999'])),\n",
    "        np.random.randint(3000,3999, size=int(x['3000_3999'])),\n",
    "        np.random.randint(4000,4999, size=int(x['4000_4999'])),\n",
    "        np.random.randint(5000,5999, size=int(x['5000_5999'])),\n",
    "        np.random.randint(6000,6999, size=int(x['6000_6999'])),\n",
    "        np.random.randint(7000,7999, size=int(x['7000_7999'])),\n",
    "        np.random.randint(8000,8999, size=int(x['8000_8999'])),\n",
    "        np.random.randint(9000,9999, size=int(x['9000_9999'])),\n",
    "        np.random.randint(10000,11999, size=int(x['10000_11999'])),\n",
    "        np.random.randint(12000,14999, size=int(x['12000_14999'])),\n",
    "        np.random.randint(15000,24999, size=int(x['15000_24999'])),\n",
    "        np.random.randint(25000,49999, size=int(x['25000_49999'])),\n",
    "        np.random.randint(50000,80000, size=int(x['50000_over']))\n",
    "                                                            ]))),axis=1)\n",
    "\n",
    "\n",
    "census_1970_3 = census_1970_3.rename(columns={'CEB003':'indian_m',\n",
    "                                          'CEB004':'japanese_m',\n",
    "                                          'CEB005':'chinese_m',\n",
    "                                          'CEB006':'filipino_m',\n",
    "                                          'CEB007':'hawaiian_m',\n",
    "                                          'CEB008':'korean_m',\n",
    "                                          'CEB012':'indian_f',\n",
    "                                          'CEB013':'japanese_f',\n",
    "                                          'CEB014':'chinese_f',\n",
    "                                          'CEB015':'filipino_f',\n",
    "                                          'CEB016':'hawaiian_f',\n",
    "                                          'CEB017':'korean_f',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "\n",
    "census_1970_3['asian']=census_1970_3['indian_m']+census_1970_3['japanese_m']+\\\n",
    "                                          census_1970_3['chinese_m']+\\\n",
    "                                          census_1970_3['filipino_m']+\\\n",
    "                                          census_1970_3['hawaiian_m']+\\\n",
    "                                          census_1970_3['korean_m']+\\\n",
    "                                          census_1970_3['indian_f']+\\\n",
    "                                          census_1970_3['japanese_f']+\\\n",
    "                                          census_1970_3['chinese_f']+\\\n",
    "                                          census_1970_3['filipino_f']+\\\n",
    "                                          census_1970_3['hawaiian_f']+\\\n",
    "                                          census_1970_3['korean_f']\n",
    "census_1970_4 = census_1970_4.rename(columns={\n",
    "                                          'CY9001':'spanish_origin',\n",
    "                                            'GISJOIN':'gisjoin'})\n",
    "census_1970_5 = census_1970_5.rename(columns={\n",
    "                                          'C1K001':'median_income_agg',\n",
    "                                            'GISJOIN':'gisjoin'})\n",
    "\n",
    "\n",
    "census_1970 =census_1970_1.set_index('gisjoin')\\\n",
    "                        .join(census_1970_2[['gisjoin','median_income']].set_index('gisjoin'))\\\n",
    "                        .join(census_1970_3[['gisjoin','asian']].set_index('gisjoin'))\\\n",
    "                        .join(census_1970_4[['gisjoin','spanish_origin']].set_index('gisjoin'))\\\n",
    "                        .join(census_1970_5[['gisjoin','median_income_agg']].set_index('gisjoin')).reset_index()\n",
    "\n",
    "            \n",
    "census_1970['hispanic']=census_1970['spanish_origin']\n",
    "census_1970['college']=census_1970['college_1_3']+census_1970['college_4']+census_1970['college_5_more']\n",
    "\n",
    "### Adjust for inflation in 1969\n",
    "### from: http://www.in2013dollars.com/1969-dollars-in-2016\n",
    "infl_factor_1970=6.53970\n",
    "\n",
    "census_1970['median_income_adj']= infl_factor_1970*census_1970['median_income']\n",
    "\n",
    "\n",
    "\n",
    "#### Cleanup\n",
    "census_1970 = census_1970.drop_duplicates()\n",
    "census_1970 = census_1970.fillna(0)\n",
    "census_1970=census_1970[census_1970.population>0]\n",
    "census_1970=census_1970[(census_1970['white']+census_1970['black']+census_1970['other'])>0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "66005.1921"
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_1970['median_income_adj'].median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Using the sume of races here becaucse I think population was a little wonky? \n",
    "census_1970['white_perc']=census_1970['white']/(census_1970['white']+census_1970['black']+census_1970['other'])\n",
    "census_1970['asian_perc']=census_1970['asian']/(census_1970['white']+census_1970['black']+census_1970['other'])\n",
    "                                              \n",
    "census_1970['black_perc'] = census_1970['black']/(census_1970['white']+census_1970['black']+census_1970['other'])\n",
    "\n",
    "census_1970['hispanic_perc']=census_1970['hispanic']/(census_1970['white']+census_1970['black']+census_1970['other'])\n",
    "census_1970['other_perc']=census_1970['other']/(census_1970['white']+census_1970['black']+census_1970['other'])\n",
    "census_1970['college_perc'] = (census_1970['college'] )/ (census_1970['white']+census_1970['black']+census_1970['other'])\n",
    "census_1970['unemployed_perc'] = (census_1970['unemployed'] )/ (census_1970['white']+census_1970['black']+census_1970['other'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1970.to_sql('census_1970', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1980\n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other types of asian\n",
    "- Education\n",
    "- Median Income\n",
    "- Employment Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1980_1 = pd.read_csv('data/NHGIS/census_1980/nhgis0027_ds104_1980_tract.csv',index_col=None, header=0)\n",
    "census_1980_2 = pd.read_csv('data/NHGIS/census_1980/nhgis0027_ds107_1980_tract.csv',index_col=None, header=0)\n",
    "\n",
    "\n",
    "\n",
    "census_1980_1 = census_1980_1.rename(columns={'C7L001':'population',\n",
    "                                          'C9D001':'white',\n",
    "                                          'C9D002':'black',\n",
    "                                          'C9D003':'american_indian',\n",
    "                                          'C9D004':'eskimo',\n",
    "                                          'C9D005':'aleut',\n",
    "                                          'C9D006':'japanese',\n",
    "                                          'C9D007':'chinese',\n",
    "                                          'C9D008':'filipino',\n",
    "                                          'C9D009':'korean',\n",
    "                                          'C9D010':'asian_indian',\n",
    "                                          'C9D011':'vietnamese',\n",
    "                                          'C9D012':'hawaiian',\n",
    "                                          'C9D013':'guamian',\n",
    "                                          'C9D014':'samoan',\n",
    "                                          'C9D015':'other',\n",
    "                                          'C9E001':\"non_spanish\",\n",
    "                                          'C9E002':'mexican',\n",
    "                                           'C9E003':'puerto_rican',\n",
    "                                              'C9E004':'cuban',\n",
    "                                              'C9E005':'other_spanish',\n",
    "                                          'GISJOIN':'gisjoin'})\n",
    "census_1980_2 = census_1980_2.rename(columns={'DHM004':'college_1_3',\n",
    "                                              'DHM005':'college_4',\n",
    "                                              'DHX003':'unemployed_male',\n",
    "                                              'DHX007':'unemployed_female',\n",
    "                                              'DIE001':'median_income',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "\n",
    "census_1980 =census_1980_1.set_index('gisjoin').join(census_1980_2[['gisjoin',\n",
    "                                                                    'college_1_3',\n",
    "                                                                    'college_4',\n",
    "                                                                    'unemployed_male',\n",
    "                                                                    'unemployed_female',\n",
    "                                                                    'median_income']].set_index('gisjoin')).reset_index()\n",
    "census_1980['other']=(census_1980['american_indian']+census_1980['eskimo']+census_1980['aleut']+census_1980['japanese']+\\\n",
    "                    census_1980['chinese']+census_1980['filipino']+census_1980['korean']+census_1980['asian_indian']+\\\n",
    "                    census_1980['vietnamese']+census_1980['hawaiian']+census_1980['guamian']+census_1980['samoan']+census_1980['other'])\n",
    "\n",
    "census_1980['hispanic']= (census_1980['mexican']+census_1980['puerto_rican']+census_1980['cuban']+census_1980['other_spanish'])\n",
    "census_1980['college'] =census_1980['college_1_3']+census_1980['college_4']\n",
    "census_1980['unemployed'] =census_1980['unemployed_male']+census_1980['unemployed_female']\n",
    "\n",
    "### Adjust for inflation in 1979\n",
    "### from: http://www.in2013dollars.com/1950-dollars-in-2016\n",
    "infl_factor_1980=3.30588\n",
    "census_1980['median_income_adj']= infl_factor_1980*(census_1980['median_income']).astype(float)\n",
    "\n",
    "#### Cleanup\n",
    "census_1980=census_1980[census_1980.population>0]\n",
    "census_1980 = census_1980.drop_duplicates()\n",
    "census_1980 = census_1980.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1980['white_perc']=census_1980['white']/census_1980['population']\n",
    "census_1980['black_perc']=census_1980['black']/census_1980['population']\n",
    "census_1980['hispanic_perc'] = (census_1980['hispanic'])/(census_1980['population'])\n",
    "census_1980['other_perc']=(census_1980['other']) /census_1980['population']\n",
    "census_1980['college_perc'] = (census_1980['college'] )/ census_1980['population']\n",
    "census_1980['unemployed_perc'] = (census_1980['unemployed'] )/ census_1980['population']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1980.to_sql('census_1980', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Starting 1990 we use block groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1990\n",
    "\n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- Education\n",
    "- Median Income\n",
    "- Employment Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1990_1 = pd.read_csv('/data/NHGIS/census_1990/nhgis0023_ds120_1990_blck_grp.csv',index_col=None, header=0)\n",
    "census_1990_2 = pd.read_csv('/data/NHGIS/census_1990/nhgis0023_ds123_1990_blck_grp.csv',index_col=None, header=0)\n",
    "census_1990_1['PMSAA']=census_1990_1['PMSAA'].astype(str)\n",
    "census_1990_1['MSA_CMSAA']=census_1990_1['MSA_CMSAA'].astype(str)\n",
    "census_1990_2['PMSAA']=census_1990_2['PMSAA'].astype(str)\n",
    "census_1990_2['MSA_CMSAA']=census_1990_2['MSA_CMSAA'].astype(str)\n",
    "\n",
    "census_1990_1 = census_1990_1.rename(columns={'ET1001':'population',\n",
    "                                          'ET2001':'white',\n",
    "                                          'ET2002':'black',\n",
    "                                          'ET2003':'native_american',\n",
    "                                          'ET2004':'asian',\n",
    "                                          'ET2005':'other',\n",
    "                                          'ET2006':'white_hispanic',\n",
    "                                          'ET2007':'black_hispanic',\n",
    "                                          'ET2008':'indian_hispanic',\n",
    "                                          'ET2009':'asian_hispanic',\n",
    "                                          'ET2010':'other_hispanic',\n",
    "                                          'EST001':'median_value',\n",
    "                                          'GISJOIN':'gisjoin'})\n",
    "census_1990_1['hispanic']=census_1990_1['white_hispanic']+census_1990_1['black_hispanic']\\\n",
    "                            +census_1990_1['indian_hispanic']+census_1990_1['asian_hispanic']+census_1990_1['other_hispanic']\n",
    "census_1990_1['other']=census_1990_1['other']+census_1990_1['asian']+census_1990_1['native_american']\n",
    "census_1990_2 = census_1990_2.rename(columns={'E33004':'college_notcomplete',\n",
    "                                              'E33005':'college_1_3',\n",
    "                                              'E33006':'college_4',\n",
    "                                              'E33007':'graduate',\n",
    "                                              'E4I003':'unemployed_male',\n",
    "                                              'E4I007':'unemployed_female',\n",
    "                                              'E4U001':'median_income',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "census_1990_2['college']=census_1990_2['college_notcomplete']+census_1990_2['college_1_3']\\\n",
    "                            + census_1990_2['college_4']+census_1990_2['graduate']\n",
    "census_1990_2['unemployed']=census_1990_2['unemployed_female']+census_1990_2['unemployed_male']\n",
    "\n",
    "### Adjust for inflation in 1989\n",
    "### from: http://www.in2013dollars.com/1950-dollars-in-2016\n",
    "infl_factor_1990=1.93554\n",
    "census_1990_2['median_income_adj']= infl_factor_1990*(census_1990_2['median_income']).astype(float)\n",
    "\n",
    "census_1990 =census_1990_1.set_index('gisjoin').join(census_1990_2[['gisjoin',\n",
    "                                                                    \"college\",\n",
    "                                                                    'unemployed',\n",
    "                                                                    'median_income_adj',\n",
    "                                                                    'median_income']].set_index('gisjoin')).reset_index()\n",
    "\n",
    "#### Cleanup \n",
    "census_1990=census_1990[census_1990.population>0]\n",
    "census_1990 = census_1990.drop_duplicates()\n",
    "census_1990 = census_1990.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# census_1990[census_1990['unemployed_perc']>1][['unemployed','population']]\n",
    "# [['college_perc','college','population']].sort_values('college_perc',ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1990['white_perc']=census_1990['white']/census_1990['population']\n",
    "census_1990['black_perc']=census_1990['black']/census_1990['population']\n",
    "census_1990['other_perc']=(census_1990['other']) /census_1990['population']\n",
    "census_1990['hispanic_perc']=(census_1990['hispanic']) /census_1990['population']\n",
    "\n",
    "census_1990['college_perc'] = (census_1990['college'])/ census_1990['population']\n",
    "census_1990['unemployed_perc']= (census_1990['unemployed'])/census_1990['population'].astype(float)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_1990.to_sql('census_1990', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2000\n",
    "Using census from that year. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- Education\n",
    "- Median Income\n",
    "- Employment Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_2000_1 = pd.read_csv('data/NHGIS/census_2000/nhgis0023_ds147_2000_blck_grp.csv',index_col=None, header=0)\n",
    "census_2000_2 = pd.read_csv('data/NHGIS/census_2000/nhgis0023_ds152_2000_blck_grp.csv',index_col=None, header=0)\n",
    "\n",
    "\n",
    "census_2000_1 = census_2000_1.rename(columns={'FXS001':'population',\n",
    "                                          'FYF001':'white',\n",
    "                                          'FYF002':'black',\n",
    "                                          'FYF003':'native_american',\n",
    "                                          'FYF004':'asian',\n",
    "                                          'FYF005':'pacific_islander',\n",
    "                                          'FYF008':'white_hispanic',\n",
    "                                          'FYF009':'black_hispanic',\n",
    "                                          'FYF010':'indian_hispanic',\n",
    "                                          'FYF011':'asian_hispanic',\n",
    "                                          'FYF012':'islander_hispanic',\n",
    "                                          'FYF013':'other_hispanic',\n",
    "                                          'FYF014':'twoother_hispanic',\n",
    "                                          'FYE006':'one_other',\n",
    "                                          'FYE007':'two_other',\n",
    "                                          'GISJOIN':'gisjoin'})\n",
    "\n",
    "census_2000_1['hispanic']=census_2000_1['white_hispanic']+census_2000_1['black_hispanic']+\\\n",
    "                        census_2000_1['indian_hispanic']+census_2000_1['asian_hispanic']+\\\n",
    "                        census_2000_1['islander_hispanic']+census_2000_1['other_hispanic']+census_2000_1['twoother_hispanic']\n",
    "\n",
    "census_2000_1['other']=census_2000_1['native_american']+census_2000_1['asian']+census_2000_1['pacific_islander']+census_2000_1['one_other']+census_2000_1['two_other']\n",
    "\n",
    "\n",
    "census_2000_2 = census_2000_2.rename(columns={'HD1011':'somecollege_male',\n",
    "                                              'HD1012':'college_1_3_male',\n",
    "                                              'HD1013':'college_4_male',\n",
    "                                              'HD1014':'masters_male',\n",
    "                                              'HD1015':'professional_male',\n",
    "                                              'HD1016':'doctorate_male',\n",
    "                                              'HD1027':'somecollege_female',\n",
    "                                              'HD1028':'college_1_3_female',\n",
    "                                              'HD1029':'college_4_female',\n",
    "                                              'HD1030':'masters_female',\n",
    "                                              'HD1031':'professional_female',\n",
    "                                              'HD1032':'doctorate_female',\n",
    "                                              'HEZ002':'unemployed_male',\n",
    "                                              'HEZ004':'unemployed_female',\n",
    "                                              'HF6001':'median_income',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "census_2000_2['college'] = census_2000_2['somecollege_male']+ census_2000_2['college_1_3_male']+census_2000_2['college_4_male']\\\n",
    "                            +census_2000_2['masters_male']+census_2000_2['professional_male']+census_2000_2['doctorate_male']\\\n",
    "                            +census_2000_2['somecollege_female']+ census_2000_2['college_1_3_female']+census_2000_2['college_4_female']\\\n",
    "                            +census_2000_2['masters_female']+census_2000_2['professional_female']+census_2000_2['doctorate_female']\\\n",
    "\n",
    "census_2000_2['unemployed']=census_2000_2['unemployed_male']+census_2000_2['unemployed_female']\n",
    "census_2000 =census_2000_1.set_index('gisjoin').join(census_2000_2[['gisjoin',\n",
    "                                             'college',\n",
    "                                              'unemployed',\n",
    "                                             'median_income']].set_index('gisjoin')).reset_index()\n",
    "\n",
    "### Adjust for inflation in 1999\n",
    "### from: http://www.in2013dollars.com/1950-dollars-in-2016\n",
    "infl_factor_2000=1.44062\n",
    "census_2000['median_income_adj']= infl_factor_2000*(census_2000['median_income']).astype(float)\n",
    "\n",
    "### Cleanup\n",
    "census_2000=census_2000[census_2000.population>0]\n",
    "census_2000 = census_2000.drop_duplicates()\n",
    "census_2000= census_2000.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44531.14644084084"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_2000['median_income'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_2000['white_perc']=census_2000['white']/census_2000['population']\n",
    "census_2000['black_perc']=census_2000['black']/census_2000['population']\n",
    "census_2000['other_perc']=(census_2000['other']) /census_2000['population']\n",
    "census_2000['hispanic_perc']=(census_2000['hispanic']) /census_2000['population']\n",
    "\n",
    "census_2000['college_perc'] = (census_2000['college'] )/ census_2000['population']\n",
    "census_2000['unemployed_perc']= (census_2000['unemployed'])/census_2000['population'].astype(float)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to Postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_2000.to_sql('census_2000', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2010\n",
    "**Starting 2010, longform census no longer exists - replaced with ACS data. **\n",
    "\n",
    "Using census from that year and 2008-2012 ACS. Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- Education\n",
    "- Median Income\n",
    "- Employment Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Had to change encoding to 'latin-1'\n",
    "census_2010_1 = pd.read_csv('data/NHGIS/census_2010/nhgis0023_ds171_2010_blck_grp.csv',index_col=None, header=0,encoding='latin-1')\n",
    "census_2010_2 = pd.read_csv('data/NHGIS/census_2010/nhgis0023_ds172_2010_blck_grp.csv',index_col=None, header=0,encoding='latin-1')\n",
    "census_2010_3 = pd.read_csv('data/NHGIS/census_2010/nhgis0023_ds191_20125_2012_blck_grp.csv',index_col=None, header=0,encoding='latin-1')\n",
    "\n",
    "census_2010_1 = census_2010_1.rename(columns={\n",
    "                                            'H7R001':'population',\n",
    "                                            'H7R002':'hispanic',\n",
    "                                          'H7R005':'white',\n",
    "                                          'H7R006':'black',\n",
    "                                          'H7R007':'native_american',\n",
    "                                          'H7R008':'asian',\n",
    "                                          'H7R009':'pacific_islander',\n",
    "                                          'H7R010':'one_other',\n",
    "                                          'H7R011':'two_other',\n",
    "                                            'H7R028':'three_other',\n",
    "                                          'H7R049':'four_other',\n",
    "                                            'H7R065':'five_other',\n",
    "                                            'H7R072':'six_other',\n",
    "                                          'GISJOIN':'gisjoin'})\n",
    "census_2010_1['other']=census_2010_1['native_american']+census_2010_1['asian']+\\\n",
    "                        census_2010_1['pacific_islander']+census_2010_1['one_other']+\\\n",
    "                        census_2010_1['two_other']+census_2010_1['three_other']+census_2010_1['four_other']+\\\n",
    "                        census_2010_1['five_other']+census_2010_1['six_other']\n",
    "\n",
    "\n",
    "census_2010_3 = census_2010_3.rename(columns={'QUSE020':'somecollege',\n",
    "                                              'QUSE021':'college_1_3',\n",
    "                                              'QUSE022':'college_4',\n",
    "                                              'QUSE023':'masters',\n",
    "                                              'QUSE024':'professional',\n",
    "                                              'QUSE025':'doctorate',\n",
    "                                              'QU1E001':'median_income',\n",
    "                                              'QXSE005':'unemployed',\n",
    "                                              'QUSE001':'edu_total',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "census_2010_3['college'] =census_2010_3['somecollege'] +census_2010_3['college_1_3'] +census_2010_3['college_4']\\\n",
    "                        +census_2010_3['masters'] +census_2010_3['professional'] +census_2010_3['doctorate']\n",
    "census_2010 =census_2010_1[['population','white','black','hispanic','other','gisjoin']].set_index('gisjoin').join(census_2010_3[['gisjoin',\n",
    "                                            'college','edu_total',\n",
    "                                            'unemployed',\n",
    "                                             'median_income']].set_index('gisjoin')).reset_index()\n",
    "\n",
    "\n",
    "\n",
    "### Adjust for inflation in 2012, the values are in 2012 inflation adjusted dollars\n",
    "infl_factor_2012=1.04535\n",
    "census_2010['median_income_adj']= infl_factor_2012*(census_2010['median_income']).astype(float)\n",
    "\n",
    "### Cleanup\n",
    "census_2010=census_2010[census_2010.population>0]\n",
    "census_2010=census_2010.fillna(0)\n",
    "\n",
    "census_2010 = census_2010.drop_duplicates()\n",
    "census_2010 = census_2010.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_2010['white_perc']=census_2010['white']/census_2010['population']\n",
    "census_2010['black_perc']=census_2010['black']/census_2010['population']\n",
    "census_2010['other_perc']=(census_2010['other']) /census_2010['population']\n",
    "census_2010['hispanic_perc']=(census_2010['hispanic']) /census_2010['population']\n",
    "census_2010['college_perc'] = (census_2010['college'])/ census_2010['edu_total']\n",
    "census_2010['unemployed_perc']= (census_2010['unemployed'])/census_2010['population'].astype(float)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_2010.to_sql('census_2010', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2016\n",
    "**2016 1yr ACS data. **\n",
    "\n",
    "Has the following columns: \n",
    "- Population\n",
    "- Race by white, black, other\n",
    "- Education\n",
    "- Median Income\n",
    "- Employment Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Had to change encoding to 'latin-1'\n",
    "census_2016 = pd.read_csv('data/NHGIS/census_2016/nhgis0024_ds225_20165_2016_blck_grp.csv',index_col=None, header=0,encoding='latin-1')\n",
    "\n",
    "census_2016 = census_2016.rename(columns={'AF2LE001':'population',\n",
    "                                          'AF2UE003':'white',\n",
    "                                          'AF2UE004':'black',\n",
    "                                          'AF2UE005':'native_american',\n",
    "                                          'AF2UE006':'asian',\n",
    "                                          'AF2UE007':'pacific_islander',\n",
    "                                          'AF2ME007':'one_other',\n",
    "                                          'AF2ME008':'two_other',\n",
    "                                          'AF2UE013':'hispanic_white',\n",
    "                                          'AF2UE014':'hispanic_black',\n",
    "                                          'AF2UE015':'hispanic_indian',\n",
    "                                          'AF2UE016':'hispanic_asian',\n",
    "                                          'AF2UE017':'hispanic_islander',\n",
    "                                          'AF2UE018':'hispanic_other',\n",
    "                                          'AF2UE019':'hispanic_twoother',\n",
    "                                          'AF2UE020':'hispanic_twootherother',\n",
    "                                          'AF4OE020':'somecollege',\n",
    "                                              'AF4OE021':'college_1_3',\n",
    "                                              'AF4OE022':'college_4',\n",
    "                                              'AF4OE023':'masters',\n",
    "                                              'AF4OE024':'professional',\n",
    "                                              'AF4OE025':'doctorate',\n",
    "                                              'AF49E001':'median_income',\n",
    "                                              'AF67E005':'unemployed',\n",
    "                                              'AF9LE001':'median_value',\n",
    "                                         'GISJOIN':'gisjoin'})\n",
    "census_2016['hispanic']=census_2016['hispanic_white']+census_2016['hispanic_black']+census_2016['hispanic_indian']+census_2016['hispanic_asian']\\\n",
    "                        +census_2016['hispanic_islander']+census_2016['hispanic_other']+census_2016['hispanic_twoother']+census_2016['hispanic_twootherother']\n",
    "census_2016['other']=census_2016['native_american']+census_2016['asian']+census_2016['pacific_islander']+census_2016['one_other']+census_2016['two_other']\n",
    "census_2016['college']= census_2016['somecollege']+census_2016['college_1_3']+census_2016['college_4']+census_2016['masters']\\\n",
    "                        + census_2016['professional']+census_2016['doctorate']\n",
    "\n",
    "census_2016['median_income_adj']= (census_2016['median_income']).astype(float)\n",
    "\n",
    "### Cleanup\n",
    "census_2016=census_2016[census_2016.population>0]\n",
    "census_2016 = census_2016.drop_duplicates()\n",
    "census_2016 = census_2016.fillna(0)\n",
    "cols_keep = ['gisjoin','population','white','black','other','native_american','asian','pacific_islander','hispanic','college','median_income_adj','unemployed']\n",
    "census_2016 = census_2016[cols_keep]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create percentages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_2016['white_perc']=census_2016['white']/census_2016['population']\n",
    "census_2016['black_perc']=census_2016['black']/census_2016['population']\n",
    "census_2016['other_perc']=(census_2016['other']) /census_2016['population']\n",
    "census_2016['hispanic_perc']=(census_2016['hispanic']) /census_2016['population']\n",
    "census_2016['college_perc'] = (census_2016['college'])/ census_2016['population']\n",
    "census_2016['unemployed_perc']= (census_2016['unemployed'])/census_2016['population'].astype(float)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload to postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_2016.to_sql('census_2016', engine,if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*****"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## There are some duplicates in the census data\n",
    "They look like this: \n",
    "```\n",
    "152\tTract   K0001     in Boston MA\t4258\t**1389**\t**1414**\t1200\tC\tBoston\t4806\t4003\t0.9401127289807421\t0.0577736026303429\t0.00211366838891498\tC\t0.233629061563406\t0.234096988926357\n",
    "\n",
    "233\tTract   K0001     in Boston MA\t4258\t**1388**\t**1413**\t1200\tC\tBoston\t4806\t4001\t0.9396430248943169\t0.0577736026303429\t0.00211366838891498\tC\t0.233629061563406\t0.234096988926357\n",
    "```\n",
    "We'll run for example:\n",
    "```\n",
    "select distinct on (\"AREANAME\")\n",
    "* from census_1930;\n",
    "```\n",
    "\n",
    "We'll remove these in the weighted area joins"
   ]
  }
 ],
 "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
}