538_race_income_gap.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Black/White income gap in cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I was intrigued by [a FiveThirtyEight.com story](http://fivethirtyeight.com/datalab/how-baltimores-young-black-men-are-boxed-in/) that included this image:\n",
    "\n",
    "![this image](https://espnfivethirtyeight.files.wordpress.com/2015/04/casselman-datalab-baltimore-1.png?w=610&h=521)\n",
    "\n",
    "I wanted to know which were the places towards the bottom right quadrant, where black median income outpaced white so dramatically. I am also always looking for chances to practice Pandas and iPython notebook and see how well our [Census Reporter SQL data](http://censusreporter.tumblr.com/post/73727555158/easier-access-to-acs-data) can be applied to this kind of work, so I made this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# basic setup. You'd do this every time you set out to use pandas with Census Reporter's SQL\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine \n",
    "# for below to work, you must set the PGPASSWORD env variable or have no-password login enabled\n",
    "engine = create_engine('postgresql://census@localhost:5432/census')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After I published this, [Ben Casselman clarified](https://twitter.com/bencasselman/status/595276073077776385) that they used [B19013H Median Household Income (White alone, not Hispanic)](http://censusreporter.org/tables/B19013H/) and [B19013B Median Household Income (Black alone)](http://censusreporter.org/tables/B19013B/) tables from the ACS2013-3 year release."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load in white and black median income for Census places (sumlevel = 160)\n",
    "white = pd.read_sql_query(\"select g.geoid, g.name, d.b19013h001 as white \\\n",
    "                           from acs2013_3yr.geoheader g, \\\n",
    "                                acs2013_3yr.b19013h d \\\n",
    "                                where d.geoid = g.geoid \\\n",
    "                                and g.sumlevel = 160\",engine, index_col='geoid')\n",
    "black = pd.read_sql_query(\"select g.geoid, d.b19013b001 as black \\\n",
    "                           from acs2013_3yr.geoheader g, \\\n",
    "                                acs2013_3yr.b19013b d \\\n",
    "                                where d.geoid = g.geoid \\\n",
    "                                and g.sumlevel = 160\",engine, index_col='geoid')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# put the parts together and compute the gap\n",
    "df = white.join(black)\n",
    "df = df.dropna()\n",
    "df['gap'] = df.white - df.black\n",
    "df.sort_values(by='gap',ascending=True,inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The 538 chart specifies places where blacks make up 10% or more of the population. So, add population of each group. Since the median income above specifies White non-hispanic, we need to use  [B03002 Hispanic or Latino Origin by Race](http://censusreporter.org/tables/B03002/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'white': 'white_income', 'black': 'black_income'}, inplace=True)\n",
    "population = pd.read_sql_query(\"select geoid, b03002001 as total_pop, b03002003 as white_pop, \\\n",
    "                                b03002004+b03002014 as black_pop from acs2013_3yr.b03002 \\\n",
    "                                where geoid like '16000US%%'\",\n",
    "                               engine, index_col='geoid')\n",
    "df = df.join(population)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.dropna(inplace=True)\n",
    "df['black_pop_pct'] = df.black_pop / df.total_pop\n",
    "# I'm running out of creative names for my variables\n",
    "df2 = df[(df.black_pop_pct >=.1) & (df.gap < 0)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>white_income</th>\n",
       "      <th>black_income</th>\n",
       "      <th>gap</th>\n",
       "      <th>total_pop</th>\n",
       "      <th>white_pop</th>\n",
       "      <th>black_pop</th>\n",
       "      <th>black_pop_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geoid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16000US0672520</th>\n",
       "      <td>Soledad city, California</td>\n",
       "      <td>60441.0</td>\n",
       "      <td>147632.0</td>\n",
       "      <td>-87191.0</td>\n",
       "      <td>26251.0</td>\n",
       "      <td>3391.0</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>0.126167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US3676089</th>\n",
       "      <td>Uniondale CDP, New York</td>\n",
       "      <td>36991.0</td>\n",
       "      <td>98148.0</td>\n",
       "      <td>-61157.0</td>\n",
       "      <td>25905.0</td>\n",
       "      <td>2416.0</td>\n",
       "      <td>11635.0</td>\n",
       "      <td>0.449141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US3676705</th>\n",
       "      <td>Valley Stream village, New York</td>\n",
       "      <td>78736.0</td>\n",
       "      <td>122880.0</td>\n",
       "      <td>-44144.0</td>\n",
       "      <td>37618.0</td>\n",
       "      <td>13727.0</td>\n",
       "      <td>6763.0</td>\n",
       "      <td>0.179781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US3624273</th>\n",
       "      <td>Elmont CDP, New York</td>\n",
       "      <td>63913.0</td>\n",
       "      <td>96223.0</td>\n",
       "      <td>-32310.0</td>\n",
       "      <td>39761.0</td>\n",
       "      <td>6260.0</td>\n",
       "      <td>19265.0</td>\n",
       "      <td>0.484520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US0611530</th>\n",
       "      <td>Carson city, California</td>\n",
       "      <td>56728.0</td>\n",
       "      <td>81520.0</td>\n",
       "      <td>-24792.0</td>\n",
       "      <td>92277.0</td>\n",
       "      <td>6527.0</td>\n",
       "      <td>17385.0</td>\n",
       "      <td>0.188400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US4816468</th>\n",
       "      <td>Converse city, Texas</td>\n",
       "      <td>54346.0</td>\n",
       "      <td>74891.0</td>\n",
       "      <td>-20545.0</td>\n",
       "      <td>19705.0</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>3545.0</td>\n",
       "      <td>0.179904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US3627485</th>\n",
       "      <td>Freeport village, New York</td>\n",
       "      <td>70816.0</td>\n",
       "      <td>85997.0</td>\n",
       "      <td>-15181.0</td>\n",
       "      <td>43095.0</td>\n",
       "      <td>10098.0</td>\n",
       "      <td>13717.0</td>\n",
       "      <td>0.318297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US0684144</th>\n",
       "      <td>West Carson CDP, California</td>\n",
       "      <td>53826.0</td>\n",
       "      <td>68750.0</td>\n",
       "      <td>-14924.0</td>\n",
       "      <td>21310.0</td>\n",
       "      <td>4097.0</td>\n",
       "      <td>2438.0</td>\n",
       "      <td>0.114406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US3613552</th>\n",
       "      <td>Central Islip CDP, New York</td>\n",
       "      <td>61287.0</td>\n",
       "      <td>75091.0</td>\n",
       "      <td>-13804.0</td>\n",
       "      <td>36457.0</td>\n",
       "      <td>7244.0</td>\n",
       "      <td>9076.0</td>\n",
       "      <td>0.248951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US0615044</th>\n",
       "      <td>Compton city, California</td>\n",
       "      <td>27885.0</td>\n",
       "      <td>40189.0</td>\n",
       "      <td>-12304.0</td>\n",
       "      <td>97495.0</td>\n",
       "      <td>1294.0</td>\n",
       "      <td>30568.0</td>\n",
       "      <td>0.313534</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           name  white_income  black_income  \\\n",
       "geoid                                                                         \n",
       "16000US0672520         Soledad city, California       60441.0      147632.0   \n",
       "16000US3676089          Uniondale CDP, New York       36991.0       98148.0   \n",
       "16000US3676705  Valley Stream village, New York       78736.0      122880.0   \n",
       "16000US3624273             Elmont CDP, New York       63913.0       96223.0   \n",
       "16000US0611530          Carson city, California       56728.0       81520.0   \n",
       "16000US4816468             Converse city, Texas       54346.0       74891.0   \n",
       "16000US3627485       Freeport village, New York       70816.0       85997.0   \n",
       "16000US0684144      West Carson CDP, California       53826.0       68750.0   \n",
       "16000US3613552      Central Islip CDP, New York       61287.0       75091.0   \n",
       "16000US0615044         Compton city, California       27885.0       40189.0   \n",
       "\n",
       "                    gap  total_pop  white_pop  black_pop  black_pop_pct  \n",
       "geoid                                                                    \n",
       "16000US0672520 -87191.0    26251.0     3391.0     3312.0       0.126167  \n",
       "16000US3676089 -61157.0    25905.0     2416.0    11635.0       0.449141  \n",
       "16000US3676705 -44144.0    37618.0    13727.0     6763.0       0.179781  \n",
       "16000US3624273 -32310.0    39761.0     6260.0    19265.0       0.484520  \n",
       "16000US0611530 -24792.0    92277.0     6527.0    17385.0       0.188400  \n",
       "16000US4816468 -20545.0    19705.0     6999.0     3545.0       0.179904  \n",
       "16000US3627485 -15181.0    43095.0    10098.0    13717.0       0.318297  \n",
       "16000US0684144 -14924.0    21310.0     4097.0     2438.0       0.114406  \n",
       "16000US3613552 -13804.0    36457.0     7244.0     9076.0       0.248951  \n",
       "16000US0615044 -12304.0    97495.0     1294.0    30568.0       0.313534  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_The paragraphs below refer to places which are no longer on the top 10 list after I updated to align with the tables FiveThirtyEight used for their chart. They are places with a large gap in favor of the black population, but they are too small to be included in the ACS 3-year estimates. I don't have time at the moment to investigate the ones which made this top ten list._\n",
    "\n",
    "I don't know much about Maryland, so I don't know what the deal is with [Kingstown](http://censusreporter.org/profiles/16000US2444325). It's only about [30 mi. from Baltimore](http://www.distance-cities.com/search?from=Baltimore%2C+MD&to=Kingstown%2C+MD%2C+United+States) \"as the crow flies,\" but more than 75 by roads because of the Chesapeake Bay.\n",
    "\n",
    "I'm from Ohio, but near Cleveland, so I never knew about [Wilberforce](http://censusreporter.org/profiles/16000US3985092-wilberforce-oh/). As the home of two historically black colleges, I am guessing that the administration and top faculty help account for the gap.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>white_income</th>\n",
       "      <th>black_income</th>\n",
       "      <th>gap</th>\n",
       "      <th>total_pop</th>\n",
       "      <th>white_pop</th>\n",
       "      <th>black_pop</th>\n",
       "      <th>black_pop_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geoid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16000US0624680</th>\n",
       "      <td>Fontana city, California</td>\n",
       "      <td>76469.0</td>\n",
       "      <td>79154.0</td>\n",
       "      <td>-2685.0</td>\n",
       "      <td>201293.0</td>\n",
       "      <td>29249.0</td>\n",
       "      <td>20638.0</td>\n",
       "      <td>0.102527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US0660466</th>\n",
       "      <td>Rialto city, California</td>\n",
       "      <td>46141.0</td>\n",
       "      <td>49056.0</td>\n",
       "      <td>-2915.0</td>\n",
       "      <td>101434.0</td>\n",
       "      <td>11722.0</td>\n",
       "      <td>14618.0</td>\n",
       "      <td>0.144113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US4856348</th>\n",
       "      <td>Pearland city, Texas</td>\n",
       "      <td>86944.0</td>\n",
       "      <td>96335.0</td>\n",
       "      <td>-9391.0</td>\n",
       "      <td>98123.0</td>\n",
       "      <td>45309.0</td>\n",
       "      <td>17236.0</td>\n",
       "      <td>0.175657</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    name  white_income  black_income     gap  \\\n",
       "geoid                                                                          \n",
       "16000US0624680  Fontana city, California       76469.0       79154.0 -2685.0   \n",
       "16000US0660466   Rialto city, California       46141.0       49056.0 -2915.0   \n",
       "16000US4856348      Pearland city, Texas       86944.0       96335.0 -9391.0   \n",
       "\n",
       "                total_pop  white_pop  black_pop  black_pop_pct  \n",
       "geoid                                                           \n",
       "16000US0624680   201293.0    29249.0    20638.0       0.102527  \n",
       "16000US0660466   101434.0    11722.0    14618.0       0.144113  \n",
       "16000US4856348    98123.0    45309.0    17236.0       0.175657  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.sort_values(by='total_pop',ascending=False).head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It turns out that the gap for the biggest places is not too big. Maybe we should filter by some factor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>white_income</th>\n",
       "      <th>black_income</th>\n",
       "      <th>gap</th>\n",
       "      <th>total_pop</th>\n",
       "      <th>white_pop</th>\n",
       "      <th>black_pop</th>\n",
       "      <th>black_pop_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geoid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16000US3624273</th>\n",
       "      <td>Elmont CDP, New York</td>\n",
       "      <td>63913.0</td>\n",
       "      <td>96223.0</td>\n",
       "      <td>-32310.0</td>\n",
       "      <td>39761.0</td>\n",
       "      <td>6260.0</td>\n",
       "      <td>19265.0</td>\n",
       "      <td>0.484520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US3676705</th>\n",
       "      <td>Valley Stream village, New York</td>\n",
       "      <td>78736.0</td>\n",
       "      <td>122880.0</td>\n",
       "      <td>-44144.0</td>\n",
       "      <td>37618.0</td>\n",
       "      <td>13727.0</td>\n",
       "      <td>6763.0</td>\n",
       "      <td>0.179781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US0672520</th>\n",
       "      <td>Soledad city, California</td>\n",
       "      <td>60441.0</td>\n",
       "      <td>147632.0</td>\n",
       "      <td>-87191.0</td>\n",
       "      <td>26251.0</td>\n",
       "      <td>3391.0</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>0.126167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16000US3676089</th>\n",
       "      <td>Uniondale CDP, New York</td>\n",
       "      <td>36991.0</td>\n",
       "      <td>98148.0</td>\n",
       "      <td>-61157.0</td>\n",
       "      <td>25905.0</td>\n",
       "      <td>2416.0</td>\n",
       "      <td>11635.0</td>\n",
       "      <td>0.449141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           name  white_income  black_income  \\\n",
       "geoid                                                                         \n",
       "16000US3624273             Elmont CDP, New York       63913.0       96223.0   \n",
       "16000US3676705  Valley Stream village, New York       78736.0      122880.0   \n",
       "16000US0672520         Soledad city, California       60441.0      147632.0   \n",
       "16000US3676089          Uniondale CDP, New York       36991.0       98148.0   \n",
       "\n",
       "                    gap  total_pop  white_pop  black_pop  black_pop_pct  \n",
       "geoid                                                                    \n",
       "16000US3624273 -32310.0    39761.0     6260.0    19265.0       0.484520  \n",
       "16000US3676705 -44144.0    37618.0    13727.0     6763.0       0.179781  \n",
       "16000US0672520 -87191.0    26251.0     3391.0     3312.0       0.126167  \n",
       "16000US3676089 -61157.0    25905.0     2416.0    11635.0       0.449141  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df2[df2.gap*-1 > df2.white_income/2]\n",
    "df3.sort_values(by='total_pop',ascending=False).head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Always clean up your database\n",
    "engine.dispose()"
   ]
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}