tables-figures.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Importing packages...\n",
      "ok\n"
     ]
    }
   ],
   "source": [
    "print('Importing packages...')\n",
    "import os\n",
    "import pandas\n",
    "import numpy as np\n",
    "import time\n",
    "from matplotlib import pyplot as plt\n",
    "import requests\n",
    "import json\n",
    "from pprint import pprint\n",
    "from IPython.display import display, HTML\n",
    "pandas.set_option('display.max_colwidth', None)\n",
    "\n",
    "current_year = 2022\n",
    "\n",
    "apikey = '92801b058cccd057649dfe29d55d185387d1363a'\n",
    "\n",
    "thisdir = '/home/idies/workspace/21cc/raddick/community_reinvestment_act/'\n",
    "data_dir = thisdir + 'city_data/'\n",
    "# loans_dir = data_dir + 'Community Reinvestment Act/'\n",
    "# jobs_dir = data_dir + 'LODES8/'\n",
    "census_homedir = '/home/idies/workspace/21cc/Data/Census/ACS5/{0:.0f}/'.format(current_year)\n",
    "census_data_dir = census_homedir + 'data/'\n",
    "census_metadata_dir = census_homedir + 'metadata/'\n",
    "\n",
    "figdir = thisdir + 'figures/'\n",
    "\n",
    "baltimore_shapefile_basedir = '/home/idies/workspace/Storage/raddick/Baltimore/shapefiles/'\n",
    "census_shapefile_tiger_basedir = '/home/idies/workspace/21cc/Data/Census/Shapefiles/TIGER/'\n",
    "\n",
    "print('ok')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking up via census API...\n",
      "loading variables...\n",
      "loading place names...\n",
      "getting estimates from census API...\n",
      "\tNational...\n",
      "\tBaltimore...\n",
      "\tCleveland...\n",
      "\tDetroit...\n",
      "\tNewark...\n",
      "\tPhiladelphia...\n",
      "\tPittsburgh...\n",
      "\tRichmond...\n",
      "\tSt. Louis...\n",
      "\tWashington...\n",
      "calculating summary variables...\n",
      "building table 1...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<p><strong>Table 1</strong></p><table><tr><th></th><th>Total population</th><th>Median home value</th><th>Home ownership rate</th><th>Median family income</th><th>Poverty rate</th><th>Share of people with a bachelors degree or higher</th><th>Unemployment rate</th><th>Percent black</th><tr><th>National</th><td>331,097,593</td><td>$281,900</td><td>67%</td><td>$92,646</td><td>12%</td><td>34%</td><td>3.4%</td><td>12%</td><tr><th>Baltimore</th><td>584,548</td><td>$202,900</td><td>52%</td><td>$72,972</td><td>19%</td><td>35%</td><td>4.3%</td><td>61%</td><tr><th>Cleveland</th><td>370,365</td><td>$87,400</td><td>44%</td><td>$46,784</td><td>31%</td><td>20%</td><td>7.1%</td><td>47%</td><tr><th>Detroit</th><td>636,787</td><td>$66,700</td><td>51%</td><td>$46,424</td><td>31%</td><td>17%</td><td>8.0%</td><td>78%</td><tr><th>Newark</th><td>307,355</td><td>$312,300</td><td>27%</td><td>$56,099</td><td>24%</td><td>17%</td><td>6.7%</td><td>47%</td><tr><th>Philadelphia</th><td>1,593,208</td><td>$215,500</td><td>56%</td><td>$67,168</td><td>22%</td><td>34%</td><td>5.4%</td><td>40%</td><tr><th>Pittsburgh</th><td>303,843</td><td>$171,800</td><td>51%</td><td>$85,816</td><td>19%</td><td>47%</td><td>3.6%</td><td>23%</td><tr><th>Richmond</th><td>227,171</td><td>$308,300</td><td>45%</td><td>$79,453</td><td>19%</td><td>44%</td><td>4.1%</td><td>44%</td><tr><th>St. Louis</th><td>298,018</td><td>$174,100</td><td>50%</td><td>$72,655</td><td>20%</td><td>39%</td><td>3.6%</td><td>44%</td><tr><th>Washington</th><td>670,587</td><td>$705,000</td><td>45%</td><td>$142,328</td><td>14%</td><td>63%</td><td>5.1%</td><td>44%</td>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Baltimore: place = 04000, state = 24\n",
    "\n",
    "#https://api.census.gov/data/2022/acs/acs5?get=NAME,B01001_001E,B02001_003E&for=place:04000&in=state:24\n",
    "# Total population: B01001_001\n",
    "# Median home value: B25077_001\n",
    "# Population in owner-occupied housing units: B25008_002\n",
    "### Total population in housing units: B25008_001\n",
    "# Median family income: B19113_001\n",
    "# Population with income below poverty level: B17101_002\n",
    "### Total population for whom poverty status determined: B17101_001\n",
    "# Population with bachelor degree or higher: B15001_009 + B15001_010\n",
    "### Total population with educational attainment determined: B15001_001\n",
    "# Population unemployed (16 and over): B23025_005\n",
    "### Total population with known employment status: B23025_001\n",
    "# Population black alone: B02001_003\n",
    "### Total population whose race is known: B02001_001\n",
    "\n",
    "print('Looking up via census API...')\n",
    "\n",
    "print('loading variables...')\n",
    "thevars = []\n",
    "thevars += ['B01001_001', 'B25077_001', 'B25008_001', 'B25008_002', 'B19113_001']\n",
    "thevars += ['B17101_002', 'B17101_001', 'B15003_022', 'B15003_023', 'B15003_024', 'B15003_025', 'B15003_001']\n",
    "thevars += ['B23025_005', 'B23025_001', 'B02001_003', 'B02001_001']\n",
    "\n",
    "print('loading place names...')\n",
    "placenames_df = pandas.DataFrame(data=None, columns=['city', 'place', 'state']).set_index('city')\n",
    "\n",
    "placenames_df.loc['National'] = [np.nan, np.nan]\n",
    "placenames_df.loc['Baltimore'] = ['04000', '24']\n",
    "placenames_df.loc['Cleveland'] = ['16000', '39']\n",
    "placenames_df.loc['Detroit'] = ['22000', '26']\n",
    "placenames_df.loc['Newark'] = ['51000', '34']\n",
    "placenames_df.loc['Philadelphia'] = ['60000', '42']\n",
    "placenames_df.loc['Pittsburgh'] = ['61000', '42']\n",
    "placenames_df.loc['Richmond'] = ['67000', '51']\n",
    "placenames_df.loc['St. Louis'] = ['65000', '29']\n",
    "#placenames_df.loc['Washington, DC'] = ['50000', '11']\n",
    "placenames_df.loc['Washington'] = ['50000', '11']\n",
    "\n",
    "\n",
    "print('getting estimates from census API...')\n",
    "table1_data_df = pandas.DataFrame(data=None, columns=['city']+thevars).set_index('city')\n",
    "\n",
    "\n",
    "for thiscity, thisrow in placenames_df[placenames_df.index == 'National'].iterrows():\n",
    "    print('\\t{0:}...'.format(thiscity))\n",
    "    call = 'https://api.census.gov/data/{0:.0f}/acs/acs5?get='.format(current_year)\n",
    "    for x in thevars[:-1]:\n",
    "        call += x + 'E,'\n",
    "    call += thevars[-1] + 'E'\n",
    "    call += '&for=us:*'\n",
    "    call += '&key={0:}'.format(apikey)\n",
    "    #print(call)\n",
    "    \n",
    "    r = requests.get(call)\n",
    "    resp = json.loads(r.text)\n",
    "    table1_data_df.loc[thiscity] = resp[1][:-1]\n",
    "\n",
    "for thiscity, thisrow in placenames_df[placenames_df.index != 'National'].iterrows():\n",
    "    print('\\t{0:}...'.format(thiscity))\n",
    "    call = 'https://api.census.gov/data/{0:.0f}/acs/acs5?get='.format(current_year)\n",
    "    for x in thevars[:-1]:\n",
    "        call += x + 'E,'\n",
    "    call += thevars[-1] + 'E'\n",
    "    call += '&for=place:{0:}&&in=state:{1:}'.format(thisrow['place'], thisrow['state'])\n",
    "    call += '&key={0:}'.format(apikey)\n",
    "    #print(call)\n",
    "    \n",
    "    r = requests.get(call)\n",
    "    resp = json.loads(r.text)\n",
    "    table1_data_df.loc[thiscity] = resp[1][:-2]\n",
    "    \n",
    "final_columns = ['City']\n",
    "final_columns += ['Total population', 'Median home value', 'Home ownership rate', 'Median family income']\n",
    "final_columns += ['Poverty rate', 'Share of people with a bachelors degree or higher']\n",
    "final_columns += ['Unemployment rate', 'Percent black']\n",
    "\n",
    "\n",
    "print('calculating summary variables...')\n",
    "table1_df = pandas.DataFrame(data=None, columns=final_columns).set_index('City')\n",
    "\n",
    "table1_df.loc[:, 'Total population'] = pandas.to_numeric(table1_data_df['B01001_001'], errors='coerce')\n",
    "table1_df.loc[:, 'Median home value'] = pandas.to_numeric(table1_data_df['B25077_001'], errors='coerce')\n",
    "table1_df.loc[:, 'Home ownership rate'] = pandas.to_numeric(table1_data_df['B25008_002'], errors='coerce') / pandas.to_numeric(table1_data_df['B25008_001'], errors='coerce')\n",
    "table1_df.loc[:, 'Median family income'] = pandas.to_numeric(table1_data_df['B19113_001'], errors='coerce')\n",
    "table1_df.loc[:, 'Poverty rate'] = pandas.to_numeric(table1_data_df['B17101_002'], errors='coerce') / pandas.to_numeric(table1_data_df['B17101_001'], errors='coerce')\n",
    "table1_df.loc[:, 'Share of people with a bachelors degree or higher'] = (pandas.to_numeric(table1_data_df['B15003_022'], errors='coerce') + pandas.to_numeric(table1_data_df['B15003_023'], errors='coerce') + pandas.to_numeric(table1_data_df['B15003_024'], errors='coerce') + pandas.to_numeric(table1_data_df['B15003_025'], errors='coerce')) / pandas.to_numeric(table1_data_df['B15003_001'], errors='coerce')\n",
    "table1_df.loc[:, 'Unemployment rate'] = pandas.to_numeric(table1_data_df['B23025_005'], errors='coerce') / pandas.to_numeric(table1_data_df['B23025_001'], errors='coerce')\n",
    "table1_df.loc[:, 'Percent black'] = pandas.to_numeric(table1_data_df['B02001_003'], errors='coerce') / pandas.to_numeric(table1_data_df['B02001_001'], errors='coerce')\n",
    "\n",
    "print('building table 1...')\n",
    "thehtml = '<p><strong>Table 1</strong></p>'\n",
    "thehtml += '<table>'\n",
    "thehtml += '<tr>'\n",
    "thehtml += '<th></th>'\n",
    "for thiscol in table1_df.columns.tolist():\n",
    "    thehtml += '<th>{0:}</th>'.format(thiscol)\n",
    "for ix, thisrow in table1_df.iterrows():\n",
    "    thehtml += '<tr>'\n",
    "    thehtml += '<th>{0:}</th>'.format(ix)\n",
    "    thehtml += '<td>{0:,.0f}</td>'.format(thisrow['Total population'])\n",
    "    thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median home value'])\n",
    "    thehtml += '<td>{0:.0%}</td>'.format(thisrow['Home ownership rate'])\n",
    "    thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median family income'])\n",
    "    thehtml += '<td>{0:.0%}</td>'.format(thisrow['Poverty rate'])\n",
    "    thehtml += '<td>{0:.0%}</td>'.format(thisrow['Share of people with a bachelors degree or higher'])\n",
    "    thehtml += '<td>{0:.1%}</td>'.format(thisrow['Unemployment rate'])\n",
    "    thehtml += '<td>{0:.0%}</td>'.format(thisrow['Percent black'])\n",
    "    \n",
    "display(HTML(thehtml))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking up via census API...\n",
      "loading variables...\n",
      "loading place names...\n",
      "getting estimates from census API...\n",
      "\tNational...\n",
      "\tBaltimore...\n",
      "\tCleveland...\n",
      "\tDetroit...\n",
      "\tNewark...\n",
      "\tPhiladelphia...\n",
      "\tPittsburgh...\n",
      "\tRichmond...\n",
      "\tSt. Louis...\n",
      "\tWashington...\n",
      "calculating summary variables...\n",
      "building table 1...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<p><strong>Table 1 calculated from place (level 160) in Census API</strong></p><table><tr><th></th><th>Total population</th><th>Median home value</th><th>Home ownership rate</th><th>Median family income</th><th>Poverty rate</th><th>Share of people with a bachelors degree or higher</th><th>Unemployment rate</th><th>Percent black</th><tr><th>National</th><td>331,097,593</td><td>$281,900</td><td>67%</td><td>$92,646</td><td>12%</td><td>34%</td><td>3.4%</td><td>12%</td><tr><th>Baltimore</th><td>584,548</td><td>$202,900</td><td>52%</td><td>$72,972</td><td>19%</td><td>35%</td><td>4.3%</td><td>61%</td><tr><th>Cleveland</th><td>370,365</td><td>$87,400</td><td>44%</td><td>$46,784</td><td>31%</td><td>20%</td><td>7.1%</td><td>47%</td><tr><th>Detroit</th><td>636,787</td><td>$66,700</td><td>51%</td><td>$46,424</td><td>31%</td><td>17%</td><td>8.0%</td><td>78%</td><tr><th>Newark</th><td>307,355</td><td>$312,300</td><td>27%</td><td>$56,099</td><td>24%</td><td>17%</td><td>6.7%</td><td>47%</td><tr><th>Philadelphia</th><td>1,593,208</td><td>$215,500</td><td>56%</td><td>$67,168</td><td>22%</td><td>34%</td><td>5.4%</td><td>40%</td><tr><th>Pittsburgh</th><td>303,843</td><td>$171,800</td><td>51%</td><td>$85,816</td><td>19%</td><td>47%</td><td>3.6%</td><td>23%</td><tr><th>Richmond</th><td>227,171</td><td>$308,300</td><td>45%</td><td>$79,453</td><td>19%</td><td>44%</td><td>4.1%</td><td>44%</td><tr><th>St. Louis</th><td>298,018</td><td>$174,100</td><td>50%</td><td>$72,655</td><td>20%</td><td>39%</td><td>3.6%</td><td>44%</td><tr><th>Washington</th><td>670,587</td><td>$705,000</td><td>45%</td><td>$142,328</td><td>14%</td><td>63%</td><td>5.1%</td><td>44%</td>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "getting margins of error from census API...\n",
      "loading variables...\n",
      "\tNational...\n",
      "\tBaltimore...\n",
      "\tCleveland...\n",
      "\tDetroit...\n",
      "\tNewark...\n",
      "\tPhiladelphia...\n",
      "\tPittsburgh...\n",
      "\tRichmond...\n",
      "\tSt. Louis...\n",
      "\tWashington...\n",
      "calculating summary variables...\n",
      "building table 1a...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<p><strong>Table 1a: margins of error calculated from place (level 160) in Census API</strong></p><table><tr><th></th><th>Total population</th><th>Median home value</th><th>Home ownership rate</th><th>Median family income</th><th>Poverty rate</th><th>Share of people with a bachelors degree or higher</th><th>Unemployment rate</th><th>Percent black</th><tr><th>National</th><td>-</td><td>$221</td><td>-0%</td><td>$260</td><td>1870%</td><td>3353%</td><td>188.8%</td><td>-0%</td><tr><th>Baltimore</th><td>-</td><td>$3,756</td><td>1927%</td><td>$1,951</td><td>829%</td><td>10096%</td><td>246.8%</td><td>-0%</td><tr><th>Cleveland</th><td>56</td><td>$1,985</td><td>1099%</td><td>$1,162</td><td>906%</td><td>199%</td><td>93.3%</td><td>5168%</td><tr><th>Detroit</th><td>125</td><td>$1,448</td><td>1803%</td><td>$1,100</td><td>1108%</td><td>183%</td><td>90.6%</td><td>2757%</td><tr><th>Newark</th><td>84</td><td>$10,451</td><td>1053%</td><td>$3,175</td><td>635%</td><td>139%</td><td>61.2%</td><td>3692%</td><tr><th>Philadelphia</th><td>-</td><td>$2,089</td><td>1838%</td><td>$1,394</td><td>922%</td><td>7405%</td><td>290.5%</td><td>-0%</td><tr><th>Pittsburgh</th><td>92</td><td>$3,718</td><td>962%</td><td>$2,393</td><td>723%</td><td>279%</td><td>64.5%</td><td>2504%</td><tr><th>Richmond</th><td>-</td><td>$9,855</td><td>1339%</td><td>$2,613</td><td>574%</td><td>2882%</td><td>235.2%</td><td>-0%</td><tr><th>St. Louis</th><td>-</td><td>$4,165</td><td>1899%</td><td>$2,080</td><td>697%</td><td>4738%</td><td>225.9%</td><td>-0%</td><tr><th>Washington</th><td>-</td><td>$10,380</td><td>-0%</td><td>$4,915</td><td>767%</td><td>5458%</td><td>325.9%</td><td>-0%</td>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Calculating from our data...\n",
      "calculating summary variables...\n",
      "\tBaltimore...\n",
      "\tWashington...\n",
      "building table 1b...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<p><strong>Table 1b: calculated from our tract-based data</strong></p><table><tr><th></th><th>Total population</th><th>Median home value</th><th>Home ownership rate</th><th>Median family income</th><th>Poverty rate</th><th>Share of people with a bachelors degree or higher</th><th>Unemployment rate</th><th>Percent black</th><tr><th>Baltimore</th><td>581,125</td><td>$213,129</td><td>-</td><td>$84,783</td><td>19%</td><td>35%</td><td>6.9%</td><td>61%</td><tr><th>Washington</th><td>670,587</td><td>$705,694</td><td>-</td><td>$147,663</td><td>15%</td><td>63%</td><td>7.0%</td><td>44%</td>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finding discrepancies...\n",
      "building table 1c...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<p><strong>Table 1c: discrepancy</strong></p><table><tr><th></th><th>Total population</th><th>Median home value</th><th>Home ownership rate</th><th>Median family income</th><th>Poverty rate</th><th>Share of people with a bachelors degree or higher</th><th>Unemployment rate</th><th>Percent black</th><tr><th>Baltimore</th><td>3,423 (0.6%)</td><td>$-10,229 (-5.0%)</td><td>-</td><td>$-11,811 (-16.2%)</td><td>-0.1%</td><td>-0.1%</td><td>-2.6%</td><td>0.2%</td><tr><th>Washington</th><td>0 (0.0%)</td><td>$-694 (-0.1%)</td><td>-</td><td>$-5,335 (-3.7%)</td><td>-0.8%</td><td>0.0%</td><td>-2.0%</td><td>0.0%</td>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "\n",
    "# # Baltimore: place = 04000, state = 24\n",
    "\n",
    "# #https://api.census.gov/data/2022/acs/acs5?get=NAME,B01001_001E,B02001_003E&for=place:04000&in=state:24\n",
    "# # Total population: B01001_001\n",
    "# # Median home value: B25077_001\n",
    "# # Population in owner-occupied housing units: B25008_002\n",
    "# ### Total population in housing units: B25008_001\n",
    "# # Median family income: B19113_001\n",
    "# # Population with income below poverty level: B17101_002\n",
    "# ### Total population for whom poverty status determined: B17101_001\n",
    "# # Population with bachelor degree or higher: B15001_009 + B15001_010\n",
    "# ### Total population with educational attainment determined: B15001_001\n",
    "# # Population unemployed (16 and over): B23025_005\n",
    "# ### Total population with known employment status: B23025_001\n",
    "# # Population black alone: B02001_003\n",
    "# ### Total population whose race is known: B02001_001\n",
    "\n",
    "# print('Looking up via census API...')\n",
    "\n",
    "# print('loading variables...')\n",
    "# thevars = []\n",
    "# thevars += ['B01001_001', 'B25077_001', 'B25008_001', 'B25008_002', 'B19113_001']\n",
    "# thevars += ['B17101_002', 'B17101_001', 'B15003_022', 'B15003_023', 'B15003_024', 'B15003_025', 'B15003_001']\n",
    "# thevars += ['B23025_005', 'B23025_001', 'B02001_003', 'B02001_001']\n",
    "\n",
    "# print('loading place names...')\n",
    "# placenames_df = pandas.DataFrame(data=None, columns=['city', 'place', 'state']).set_index('city')\n",
    "\n",
    "# placenames_df.loc['National'] = [np.nan, np.nan]\n",
    "# placenames_df.loc['Baltimore'] = ['04000', '24']\n",
    "# placenames_df.loc['Cleveland'] = ['16000', '39']\n",
    "# placenames_df.loc['Detroit'] = ['22000', '26']\n",
    "# placenames_df.loc['Newark'] = ['51000', '34']\n",
    "# placenames_df.loc['Philadelphia'] = ['60000', '42']\n",
    "# placenames_df.loc['Pittsburgh'] = ['61000', '42']\n",
    "# placenames_df.loc['Richmond'] = ['67000', '51']\n",
    "# placenames_df.loc['St. Louis'] = ['65000', '29']\n",
    "# #placenames_df.loc['Washington, DC'] = ['50000', '11']\n",
    "# placenames_df.loc['Washington'] = ['50000', '11']\n",
    "\n",
    "\n",
    "# print('getting estimates from census API...')\n",
    "# table1_data_df = pandas.DataFrame(data=None, columns=['city']+thevars).set_index('city')\n",
    "\n",
    "\n",
    "# for thiscity, thisrow in placenames_df[placenames_df.index == 'National'].iterrows():\n",
    "#     print('\\t{0:}...'.format(thiscity))\n",
    "#     call = 'https://api.census.gov/data/{0:.0f}/acs/acs5?get='.format(current_year)\n",
    "#     for x in thevars[:-1]:\n",
    "#         call += x + 'E,'\n",
    "#     call += thevars[-1] + 'E'\n",
    "#     call += '&for=us:*'\n",
    "#     call += '&key={0:}'.format(apikey)\n",
    "#     #print(call)\n",
    "    \n",
    "#     r = requests.get(call)\n",
    "#     resp = json.loads(r.text)\n",
    "#     table1_data_df.loc[thiscity] = resp[1][:-1]\n",
    "\n",
    "# for thiscity, thisrow in placenames_df[placenames_df.index != 'National'].iterrows():\n",
    "#     print('\\t{0:}...'.format(thiscity))\n",
    "#     call = 'https://api.census.gov/data/{0:.0f}/acs/acs5?get='.format(current_year)\n",
    "#     for x in thevars[:-1]:\n",
    "#         call += x + 'E,'\n",
    "#     call += thevars[-1] + 'E'\n",
    "#     call += '&for=place:{0:}&&in=state:{1:}'.format(thisrow['place'], thisrow['state'])\n",
    "#     call += '&key={0:}'.format(apikey)\n",
    "#     #print(call)\n",
    "    \n",
    "#     r = requests.get(call)\n",
    "#     resp = json.loads(r.text)\n",
    "#     table1_data_df.loc[thiscity] = resp[1][:-2]\n",
    "    \n",
    "# final_columns = ['City']\n",
    "# final_columns += ['Total population', 'Median home value', 'Home ownership rate', 'Median family income']\n",
    "# final_columns += ['Poverty rate', 'Share of people with a bachelors degree or higher']\n",
    "# final_columns += ['Unemployment rate', 'Percent black']\n",
    "\n",
    "\n",
    "# print('calculating summary variables...')\n",
    "# table1_df = pandas.DataFrame(data=None, columns=final_columns).set_index('City')\n",
    "\n",
    "# table1_df.loc[:, 'Total population'] = pandas.to_numeric(table1_data_df['B01001_001'], errors='coerce')\n",
    "# table1_df.loc[:, 'Median home value'] = pandas.to_numeric(table1_data_df['B25077_001'], errors='coerce')\n",
    "# table1_df.loc[:, 'Home ownership rate'] = pandas.to_numeric(table1_data_df['B25008_002'], errors='coerce') / pandas.to_numeric(table1_data_df['B25008_001'], errors='coerce')\n",
    "# table1_df.loc[:, 'Median family income'] = pandas.to_numeric(table1_data_df['B19113_001'], errors='coerce')\n",
    "# table1_df.loc[:, 'Poverty rate'] = pandas.to_numeric(table1_data_df['B17101_002'], errors='coerce') / pandas.to_numeric(table1_data_df['B17101_001'], errors='coerce')\n",
    "# table1_df.loc[:, 'Share of people with a bachelors degree or higher'] = (pandas.to_numeric(table1_data_df['B15003_022'], errors='coerce') + pandas.to_numeric(table1_data_df['B15003_023'], errors='coerce') + pandas.to_numeric(table1_data_df['B15003_024'], errors='coerce') + pandas.to_numeric(table1_data_df['B15003_025'], errors='coerce')) / pandas.to_numeric(table1_data_df['B15003_001'], errors='coerce')\n",
    "# table1_df.loc[:, 'Unemployment rate'] = pandas.to_numeric(table1_data_df['B23025_005'], errors='coerce') / pandas.to_numeric(table1_data_df['B23025_001'], errors='coerce')\n",
    "# table1_df.loc[:, 'Percent black'] = pandas.to_numeric(table1_data_df['B02001_003'], errors='coerce') / pandas.to_numeric(table1_data_df['B02001_001'], errors='coerce')\n",
    "\n",
    "# print('building table 1...')\n",
    "# thehtml = '<p><strong>Table 1 calculated from place (level 160) in Census API</strong></p>'\n",
    "# thehtml += '<table>'\n",
    "# thehtml += '<tr>'\n",
    "# thehtml += '<th></th>'\n",
    "# for thiscol in table1_df.columns.tolist():\n",
    "#     thehtml += '<th>{0:}</th>'.format(thiscol)\n",
    "# for ix, thisrow in table1_df.iterrows():\n",
    "#     thehtml += '<tr>'\n",
    "#     thehtml += '<th>{0:}</th>'.format(ix)\n",
    "#     thehtml += '<td>{0:,.0f}</td>'.format(thisrow['Total population'])\n",
    "#     thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median home value'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Home ownership rate'])\n",
    "#     thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median family income'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Poverty rate'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Share of people with a bachelors degree or higher'])\n",
    "#     thehtml += '<td>{0:.1%}</td>'.format(thisrow['Unemployment rate'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Percent black'])\n",
    "    \n",
    "# display(HTML(thehtml))\n",
    "\n",
    "\n",
    "# print('getting margins of error from census API...')\n",
    "\n",
    "# print('loading variables...')\n",
    "# thevars = []\n",
    "# thevars += ['B01001_001', 'B25077_001', 'B25008_001', 'B25008_002', 'B19113_001']\n",
    "# thevars += ['B17101_002', 'B17101_001', 'B15003_022', 'B15003_023', 'B15003_024', 'B15003_025', 'B15003_001']\n",
    "# thevars += ['B23025_005', 'B23025_001', 'B02001_003', 'B02001_001']\n",
    "\n",
    "# table1a_data_df = pandas.DataFrame(data=None, columns=['city']+thevars).set_index('city')\n",
    "\n",
    "# for thiscity, thisrow in placenames_df[placenames_df.index == 'National'].iterrows():\n",
    "#     print('\\t{0:}...'.format(thiscity))\n",
    "#     call = 'https://api.census.gov/data/{0:.0f}/acs/acs5?get='.format(current_year)\n",
    "#     for x in thevars[:-1]:\n",
    "#         call += x + 'M,'\n",
    "#     call += thevars[-1] + 'M'\n",
    "#     call += '&for=us:*'\n",
    "#     call += '&key={0:}'.format(apikey)\n",
    "#     #print(call)\n",
    "    \n",
    "#     r = requests.get(call)\n",
    "#     resp = json.loads(r.text)\n",
    "#     table1a_data_df.loc[thiscity] = resp[1][:-1]\n",
    "\n",
    "# for thiscity, thisrow in placenames_df[placenames_df.index != 'National'].iterrows():\n",
    "#     print('\\t{0:}...'.format(thiscity))\n",
    "#     call = 'https://api.census.gov/data/{0:.0f}/acs/acs5?get='.format(current_year)\n",
    "#     for x in thevars[:-1]:\n",
    "#         call += x + 'M,'\n",
    "#     call += thevars[-1] + 'M'\n",
    "#     call += '&for=place:{0:}&&in=state:{1:}'.format(thisrow['place'], thisrow['state'])\n",
    "#     call += '&key={0:}'.format(apikey)\n",
    "#     #print(call)\n",
    "    \n",
    "#     r = requests.get(call)\n",
    "#     resp = json.loads(r.text)\n",
    "#     table1a_data_df.loc[thiscity] = resp[1][:-2]\n",
    "    \n",
    "# final_columns = ['City']\n",
    "# final_columns += ['Total population', 'Median home value', 'Home ownership rate', 'Median family income']\n",
    "# final_columns += ['Poverty rate', 'Share of people with a bachelors degree or higher']\n",
    "# final_columns += ['Unemployment rate', 'Percent black']\n",
    "\n",
    "\n",
    "# print('calculating summary variables...')\n",
    "# table1a_df = pandas.DataFrame(data=None, columns=['City']+final_columns).set_index('City')\n",
    "\n",
    "# table1a_df.loc[:, 'Total population'] = pandas.to_numeric(table1a_data_df['B01001_001'], errors='coerce')\n",
    "# table1a_df.loc[:, 'Median home value'] = pandas.to_numeric(table1a_data_df['B25077_001'], errors='coerce')\n",
    "# table1a_df.loc[:, 'Home ownership rate'] = pandas.to_numeric(table1a_data_df['B25008_002'], errors='coerce') / pandas.to_numeric(table1a_data_df['B25008_001'], errors='coerce')\n",
    "# table1a_df.loc[:, 'Median family income'] = pandas.to_numeric(table1a_data_df['B19113_001'], errors='coerce')\n",
    "# table1a_df.loc[:, 'Poverty rate'] = pandas.to_numeric(table1a_data_df['B17101_002'], errors='coerce') / pandas.to_numeric(table1a_data_df['B17101_001'], errors='coerce')\n",
    "# table1a_df.loc[:, 'Share of people with a bachelors degree or higher'] = (pandas.to_numeric(table1a_data_df['B15003_022'], errors='coerce') + pandas.to_numeric(table1a_data_df['B15003_023'], errors='coerce') + pandas.to_numeric(table1a_data_df['B15003_024'], errors='coerce') + pandas.to_numeric(table1a_data_df['B15003_025'], errors='coerce')) / pandas.to_numeric(table1a_data_df['B15003_001'], errors='coerce')\n",
    "# table1a_df.loc[:, 'Unemployment rate'] = pandas.to_numeric(table1a_data_df['B23025_005'], errors='coerce') / pandas.to_numeric(table1a_data_df['B23025_001'], errors='coerce')\n",
    "# table1a_df.loc[:, 'Percent black'] = pandas.to_numeric(table1a_data_df['B02001_003'], errors='coerce') / pandas.to_numeric(table1a_data_df['B02001_001'], errors='coerce')\n",
    "\n",
    "# print('building table 1a...')\n",
    "# thehtml = '<p><strong>Table 1a: margins of error calculated from place (level 160) in Census API</strong></p>'\n",
    "# thehtml += '<table>'\n",
    "# thehtml += '<tr>'\n",
    "# thehtml += '<th></th>'\n",
    "# for thiscol in table1a_df.columns.tolist():\n",
    "#     thehtml += '<th>{0:}</th>'.format(thiscol)\n",
    "# for ix, thisrow in table1a_df.iterrows():\n",
    "#     thehtml += '<tr>'\n",
    "#     thehtml += '<th>{0:}</th>'.format(ix)\n",
    "#     if (thisrow['Total population'] < 0):\n",
    "#         thehtml += '<td>-</td>';\n",
    "#     else:\n",
    "#         thehtml += '<td>{0:,.0f}</td>'.format(thisrow['Total population'])\n",
    "#     thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median home value'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Home ownership rate'])\n",
    "#     thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median family income'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Poverty rate'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Share of people with a bachelors degree or higher'])\n",
    "#     thehtml += '<td>{0:.1%}</td>'.format(thisrow['Unemployment rate'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Percent black'])\n",
    "    \n",
    "# display(HTML(thehtml))\n",
    "\n",
    "\n",
    "\n",
    "# print('Calculating from our data...')\n",
    "# filenames = sorted([x for x in os.listdir(data_dir) if x[-4:] == '.csv'])\n",
    "\n",
    "# final_columns = ['City']\n",
    "# final_columns += ['Total population', 'Median home value', 'Home ownership rate', 'Median family income']\n",
    "# final_columns += ['Poverty rate', 'Share of people with a bachelors degree or higher']\n",
    "# final_columns += ['Unemployment rate', 'Percent black']\n",
    "\n",
    "\n",
    "# print('calculating summary variables...')\n",
    "# table1b_df = pandas.DataFrame(data=None, columns=final_columns).set_index('City')\n",
    "\n",
    "# for thisfilename in filenames[0:2]:\n",
    "#     thiscity = thisfilename.split('_')[0]\n",
    "#     print('\\t{0:}...'.format(thiscity.title()))\n",
    "#     df = pandas.read_csv(data_dir+thisfilename, low_memory=False, index_col=['GEOID', 'year'])\n",
    "#     demog_df = df.xs(current_year, level='year')\n",
    "\n",
    "#     table1b_df.loc[thiscity.title(), 'Total population'] = demog_df['pop_total'].sum()\n",
    "\n",
    "#     weighted_avg_median_home_value = 0\n",
    "#     for ix, thisrow in demog_df[demog_df['median_home_value'].notnull()].iterrows():\n",
    "#         weighted_avg_median_home_value += (thisrow['pop_total'] / demog_df['pop_total'].sum()) * thisrow['median_home_value']\n",
    "#     table1b_df.loc[thiscity.title(), 'Median home value'] = weighted_avg_median_home_value\n",
    "    \n",
    "#     weighted_avg_mfi = 0\n",
    "#     for ix, thisrow in demog_df[demog_df['mfi'].notnull()].iterrows():\n",
    "#         weighted_avg_mfi += (thisrow['pop_total'] / demog_df['pop_total'].sum()) * thisrow['mfi']\n",
    "#     table1b_df.loc[thiscity.title(), 'Median family income'] = weighted_avg_mfi\n",
    "        \n",
    "#     weighted_poverty = 0\n",
    "#     for ix, thisrow in demog_df[(demog_df['poverty_past_12_months'].notnull()) & (demog_df['poverty_status_known'].notnull())].iterrows():\n",
    "#         weighted_poverty += (thisrow['poverty_past_12_months'])\n",
    "#     table1b_df.loc[thiscity.title(), 'Poverty rate'] = weighted_poverty / demog_df[(demog_df['poverty_past_12_months'].notnull()) & (demog_df['poverty_status_known'].notnull())]['poverty_status_known'].sum()\n",
    "\n",
    "#     weighted_education = 0\n",
    "#     for ix, thisrow in demog_df[(demog_df['educated'].notnull()) & (demog_df['pop_25plus'].notnull())].iterrows():\n",
    "#         weighted_education += (thisrow['educated'])\n",
    "#     table1b_df.loc[thiscity.title(), 'Share of people with a bachelors degree or higher'] = weighted_education / demog_df[(demog_df['educated'].notnull()) & (demog_df['pop_25plus'].notnull())]['pop_25plus'].sum()\n",
    "    \n",
    "#     weighted_unemployment = 0\n",
    "#     for ix, thisrow in demog_df[(demog_df['unemployed_16plus'].notnull()) & (demog_df['labor_force_16plus'].notnull())].iterrows():\n",
    "#         weighted_unemployment += (thisrow['unemployed_16plus'])\n",
    "#     table1b_df.loc[thiscity.title(), 'Unemployment rate'] = weighted_unemployment / demog_df[(demog_df['unemployed_16plus'].notnull()) & (demog_df['labor_force_16plus'].notnull())]['labor_force_16plus'].sum()\n",
    "\n",
    "#     weighted_black = 0\n",
    "#     for ix, thisrow in demog_df[(demog_df['pop_black'].notnull())].iterrows():\n",
    "#         weighted_black += (thisrow['pop_black'])\n",
    "#     table1b_df.loc[thiscity.title(), 'Percent black'] = weighted_black / demog_df['pop_total'].sum()\n",
    "    \n",
    "    \n",
    "# print('building table 1b...')\n",
    "# thehtml = '<p><strong>Table 1b: calculated from our tract-based data</strong></p>'\n",
    "# thehtml += '<table>'\n",
    "# thehtml += '<tr>'\n",
    "# thehtml += '<th></th>'\n",
    "# for thiscol in table1b_df.columns.tolist():\n",
    "#     thehtml += '<th>{0:}</th>'.format(thiscol)\n",
    "# for ix, thisrow in table1b_df.iterrows():\n",
    "#     thehtml += '<tr>'\n",
    "#     thehtml += '<th>{0:}</th>'.format(ix)\n",
    "#     thehtml += '<td>{0:,.0f}</td>'.format(thisrow['Total population'])\n",
    "#     thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median home value'])\n",
    "#     thehtml += '<td>-</td>'\n",
    "#     thehtml += '<td>${0:,.0f}</td>'.format(thisrow['Median family income'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Poverty rate'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Share of people with a bachelors degree or higher'])\n",
    "#     thehtml += '<td>{0:.1%}</td>'.format(thisrow['Unemployment rate'])\n",
    "#     thehtml += '<td>{0:.0%}</td>'.format(thisrow['Percent black'])\n",
    "    \n",
    "# display(HTML(thehtml))\n",
    "\n",
    "\n",
    "# print('Finding discrepancies...')\n",
    "# table1c_df = pandas.DataFrame(data=None, columns=table1b_df.reset_index().columns).set_index('City')\n",
    "\n",
    "# for ix in table1b_df.index.tolist():\n",
    "# #     print(table1_df.loc[ix])\n",
    "# #     print(table1b_df.loc[ix])\n",
    "#     for thiscol in table1c_df.columns:\n",
    "#         table1c_df.loc[ix, thiscol] = table1_df.loc[ix][thiscol] - table1b_df.loc[ix][thiscol]\n",
    "# #table1_df.index\n",
    "# table1c_df\n",
    "\n",
    "# print('building table 1c...')\n",
    "# thehtml = '<p><strong>Table 1c: discrepancy</strong></p>'\n",
    "# thehtml += '<table>'\n",
    "# thehtml += '<tr>'\n",
    "# thehtml += '<th></th>'\n",
    "# for thiscol in table1b_df.columns.tolist():\n",
    "#     thehtml += '<th>{0:}</th>'.format(thiscol)\n",
    "# for ix, thisrow in table1c_df.iterrows():\n",
    "#     thehtml += '<tr>'\n",
    "#     thehtml += '<th>{0:}</th>'.format(ix)\n",
    "#     thehtml += '<td>{0:,.0f} ({1:.1%})</td>'.format(thisrow['Total population'], thisrow['Total population'] / table1_df.loc[ix]['Total population'])\n",
    "#     thehtml += '<td>${0:,.0f} ({1:.1%})</td>'.format(thisrow['Median home value'], thisrow['Median home value'] / table1_df.loc[ix]['Median home value'])\n",
    "#     thehtml += '<td>-</td>'\n",
    "#     thehtml += '<td>${0:,.0f} ({1:.1%})</td>'.format(thisrow['Median family income'], thisrow['Median family income'] / table1_df.loc[ix]['Median family income'])\n",
    "#     thehtml += '<td>{0:.1%}</td>'.format(thisrow['Poverty rate'])\n",
    "#     thehtml += '<td>{0:.1%}</td>'.format(thisrow['Share of people with a bachelors degree or higher'])\n",
    "#     thehtml += '<td>{0:.1%}</td>'.format(thisrow['Unemployment rate'], thisrow['Unemployment rate'])\n",
    "#     thehtml += '<td>{0:.1%}</td>'.format(thisrow['Percent black'], thisrow['Percent black'])\n",
    "    \n",
    "# display(HTML(thehtml))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8 (py38)",
   "language": "python",
   "name": "py38"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}