Code/03 - Yelp Adding IRS Income-draft for publishing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yelp Adding IRS Income"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to see if Yelp business price ratings ($) correlate with the affluence of a given ZIP code. We'll compare to both median gross income and mean gross income. This data was collected from [https://factfinder.census.gov/](https://factfinder.census.gov/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>alias</th>\n",
       "      <th>category</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>price</th>\n",
       "      <th>rating</th>\n",
       "      <th>review_count</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>lucilles-smokehouse-bar-b-que-brea</td>\n",
       "      <td>['bbq', 'smokehouse', 'southern']</td>\n",
       "      <td>Brea</td>\n",
       "      <td>zzvkb4J_sQAk8N20OWdZhg</td>\n",
       "      <td>33.911154</td>\n",
       "      <td>-117.881259</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1257.0</td>\n",
       "      <td>92821.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>gen-korean-bbq-house-northridge-2</td>\n",
       "      <td>['korean', 'bbq', 'asianfusion']</td>\n",
       "      <td>Northridge</td>\n",
       "      <td>zzcBtsGd7uFQ7AwIu_GcQQ</td>\n",
       "      <td>34.255146</td>\n",
       "      <td>-118.537373</td>\n",
       "      <td>2</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3533.0</td>\n",
       "      <td>91324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>mariscos-camarena-estilo-nayarit-san-fernando</td>\n",
       "      <td>['seafood', 'bars']</td>\n",
       "      <td>San Fernando</td>\n",
       "      <td>zzLBuLz4ORQF0V_QwgHtBA</td>\n",
       "      <td>34.299215</td>\n",
       "      <td>-118.438575</td>\n",
       "      <td>2</td>\n",
       "      <td>2.5</td>\n",
       "      <td>91.0</td>\n",
       "      <td>91340.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>sub-king-signal-hill</td>\n",
       "      <td>['sandwiches', 'breakfast_brunch']</td>\n",
       "      <td>Signal Hill</td>\n",
       "      <td>zzJJxXDkai8rR2tBX0Ggrw</td>\n",
       "      <td>33.804625</td>\n",
       "      <td>-118.165310</td>\n",
       "      <td>1</td>\n",
       "      <td>4.5</td>\n",
       "      <td>76.0</td>\n",
       "      <td>90755.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>bardonna-los-angeles-3</td>\n",
       "      <td>['coffee', 'breakfast_brunch', 'cafes']</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>zz0zctGWZ4vkxyx00LOIKw</td>\n",
       "      <td>34.074060</td>\n",
       "      <td>-118.323819</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>174.0</td>\n",
       "      <td>90004.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           alias  \\\n",
       "0             lucilles-smokehouse-bar-b-que-brea   \n",
       "1              gen-korean-bbq-house-northridge-2   \n",
       "2  mariscos-camarena-estilo-nayarit-san-fernando   \n",
       "3                           sub-king-signal-hill   \n",
       "4                         bardonna-los-angeles-3   \n",
       "\n",
       "                                  category          city  \\\n",
       "0        ['bbq', 'smokehouse', 'southern']          Brea   \n",
       "1         ['korean', 'bbq', 'asianfusion']    Northridge   \n",
       "2                      ['seafood', 'bars']  San Fernando   \n",
       "3       ['sandwiches', 'breakfast_brunch']   Signal Hill   \n",
       "4  ['coffee', 'breakfast_brunch', 'cafes']   Los Angeles   \n",
       "\n",
       "                       id   latitude   longitude  price  rating  review_count  \\\n",
       "0  zzvkb4J_sQAk8N20OWdZhg  33.911154 -117.881259      2     3.5        1257.0   \n",
       "1  zzcBtsGd7uFQ7AwIu_GcQQ  34.255146 -118.537373      2     4.5        3533.0   \n",
       "2  zzLBuLz4ORQF0V_QwgHtBA  34.299215 -118.438575      2     2.5          91.0   \n",
       "3  zzJJxXDkai8rR2tBX0Ggrw  33.804625 -118.165310      1     4.5          76.0   \n",
       "4  zz0zctGWZ4vkxyx00LOIKw  34.074060 -118.323819      2     3.5         174.0   \n",
       "\n",
       "   zip_code  \n",
       "0   92821.0  \n",
       "1   91324.0  \n",
       "2   91340.0  \n",
       "3   90755.0  \n",
       "4   90004.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('../data/master.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(27427, 10)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll extract the ZIP codes in our dataframe and save to a variable for easy reference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[90001,\n",
       " 90002,\n",
       " 90003,\n",
       " 90004,\n",
       " 90005,\n",
       " 90006,\n",
       " 90007,\n",
       " 90008,\n",
       " 90009,\n",
       " 90010,\n",
       " 90011,\n",
       " 90012,\n",
       " 90013,\n",
       " 90014,\n",
       " 90015,\n",
       " 90016,\n",
       " 90017,\n",
       " 90018,\n",
       " 90019,\n",
       " 90020,\n",
       " 90021,\n",
       " 90022,\n",
       " 90023,\n",
       " 90024,\n",
       " 90025,\n",
       " 90026,\n",
       " 90027,\n",
       " 90028,\n",
       " 90029,\n",
       " 90031,\n",
       " 90032,\n",
       " 90033,\n",
       " 90034,\n",
       " 90035,\n",
       " 90036,\n",
       " 90037,\n",
       " 90038,\n",
       " 90039,\n",
       " 90040,\n",
       " 90041,\n",
       " 90042,\n",
       " 90043,\n",
       " 90044,\n",
       " 90045,\n",
       " 90046,\n",
       " 90047,\n",
       " 90048,\n",
       " 90049,\n",
       " 90052,\n",
       " 90056,\n",
       " 90057,\n",
       " 90058,\n",
       " 90059,\n",
       " 90060,\n",
       " 90061,\n",
       " 90062,\n",
       " 90063,\n",
       " 90064,\n",
       " 90065,\n",
       " 90066,\n",
       " 90067,\n",
       " 90068,\n",
       " 90069,\n",
       " 90071,\n",
       " 90073,\n",
       " 90077,\n",
       " 90078,\n",
       " 90079,\n",
       " 90089,\n",
       " 90094,\n",
       " 90095,\n",
       " 90101,\n",
       " 90189,\n",
       " 90201,\n",
       " 90209,\n",
       " 90210,\n",
       " 90211,\n",
       " 90212,\n",
       " 90220,\n",
       " 90221,\n",
       " 90222,\n",
       " 90230,\n",
       " 90232,\n",
       " 90240,\n",
       " 90241,\n",
       " 90242,\n",
       " 90245,\n",
       " 90247,\n",
       " 90248,\n",
       " 90249,\n",
       " 90250,\n",
       " 90254,\n",
       " 90255,\n",
       " 90260,\n",
       " 90261,\n",
       " 90262,\n",
       " 90263,\n",
       " 90265,\n",
       " 90266,\n",
       " 90270,\n",
       " 90272,\n",
       " 90274,\n",
       " 90275,\n",
       " 90277,\n",
       " 90278,\n",
       " 90280,\n",
       " 90290,\n",
       " 90291,\n",
       " 90292,\n",
       " 90293,\n",
       " 90301,\n",
       " 90302,\n",
       " 90303,\n",
       " 90304,\n",
       " 90305,\n",
       " 90312,\n",
       " 90401,\n",
       " 90402,\n",
       " 90403,\n",
       " 90404,\n",
       " 90405,\n",
       " 90407,\n",
       " 90421,\n",
       " 90501,\n",
       " 90502,\n",
       " 90503,\n",
       " 90504,\n",
       " 90505,\n",
       " 90506,\n",
       " 90510,\n",
       " 90601,\n",
       " 90602,\n",
       " 90603,\n",
       " 90604,\n",
       " 90605,\n",
       " 90606,\n",
       " 90609,\n",
       " 90620,\n",
       " 90621,\n",
       " 90623,\n",
       " 90630,\n",
       " 90631,\n",
       " 90633,\n",
       " 90637,\n",
       " 90638,\n",
       " 90639,\n",
       " 90640,\n",
       " 90650,\n",
       " 90651,\n",
       " 90660,\n",
       " 90670,\n",
       " 90680,\n",
       " 90701,\n",
       " 90703,\n",
       " 90706,\n",
       " 90707,\n",
       " 90710,\n",
       " 90711,\n",
       " 90712,\n",
       " 90713,\n",
       " 90715,\n",
       " 90716,\n",
       " 90717,\n",
       " 90720,\n",
       " 90723,\n",
       " 90731,\n",
       " 90732,\n",
       " 90740,\n",
       " 90744,\n",
       " 90745,\n",
       " 90746,\n",
       " 90747,\n",
       " 90755,\n",
       " 90801,\n",
       " 90802,\n",
       " 90803,\n",
       " 90804,\n",
       " 90805,\n",
       " 90806,\n",
       " 90807,\n",
       " 90808,\n",
       " 90810,\n",
       " 90813,\n",
       " 90814,\n",
       " 90815,\n",
       " 90822,\n",
       " 90831,\n",
       " 90840,\n",
       " 90846,\n",
       " 90895,\n",
       " 90899,\n",
       " 91001,\n",
       " 91003,\n",
       " 91006,\n",
       " 91007,\n",
       " 91010,\n",
       " 91011,\n",
       " 91012,\n",
       " 91016,\n",
       " 91020,\n",
       " 91023,\n",
       " 91024,\n",
       " 91030,\n",
       " 91031,\n",
       " 91040,\n",
       " 91042,\n",
       " 91066,\n",
       " 91101,\n",
       " 91102,\n",
       " 91103,\n",
       " 91104,\n",
       " 91105,\n",
       " 91106,\n",
       " 91107,\n",
       " 91108,\n",
       " 91109,\n",
       " 91124,\n",
       " 91125,\n",
       " 91199,\n",
       " 91201,\n",
       " 91202,\n",
       " 91203,\n",
       " 91204,\n",
       " 91205,\n",
       " 91206,\n",
       " 91207,\n",
       " 91208,\n",
       " 91209,\n",
       " 91210,\n",
       " 91214,\n",
       " 91301,\n",
       " 91302,\n",
       " 91303,\n",
       " 91304,\n",
       " 91306,\n",
       " 91307,\n",
       " 91308,\n",
       " 91309,\n",
       " 91310,\n",
       " 91311,\n",
       " 91312,\n",
       " 91316,\n",
       " 91321,\n",
       " 91322,\n",
       " 91324,\n",
       " 91325,\n",
       " 91326,\n",
       " 91328,\n",
       " 91329,\n",
       " 91330,\n",
       " 91331,\n",
       " 91335,\n",
       " 91337,\n",
       " 91340,\n",
       " 91342,\n",
       " 91343,\n",
       " 91344,\n",
       " 91345,\n",
       " 91350,\n",
       " 91351,\n",
       " 91352,\n",
       " 91353,\n",
       " 91354,\n",
       " 91355,\n",
       " 91356,\n",
       " 91357,\n",
       " 91361,\n",
       " 91362,\n",
       " 91364,\n",
       " 91366,\n",
       " 91367,\n",
       " 91371,\n",
       " 91377,\n",
       " 91381,\n",
       " 91384,\n",
       " 91387,\n",
       " 91390,\n",
       " 91399,\n",
       " 91401,\n",
       " 91402,\n",
       " 91403,\n",
       " 91404,\n",
       " 91405,\n",
       " 91406,\n",
       " 91407,\n",
       " 91411,\n",
       " 91423,\n",
       " 91433,\n",
       " 91436,\n",
       " 91501,\n",
       " 91502,\n",
       " 91504,\n",
       " 91505,\n",
       " 91506,\n",
       " 91507,\n",
       " 91520,\n",
       " 91521,\n",
       " 91522,\n",
       " 91601,\n",
       " 91602,\n",
       " 91604,\n",
       " 91605,\n",
       " 91606,\n",
       " 91607,\n",
       " 91608,\n",
       " 91617,\n",
       " 91701,\n",
       " 91702,\n",
       " 91706,\n",
       " 91707,\n",
       " 91709,\n",
       " 91710,\n",
       " 91711,\n",
       " 91722,\n",
       " 91723,\n",
       " 91724,\n",
       " 91731,\n",
       " 91732,\n",
       " 91733,\n",
       " 91735,\n",
       " 91740,\n",
       " 91741,\n",
       " 91744,\n",
       " 91745,\n",
       " 91746,\n",
       " 91747,\n",
       " 91748,\n",
       " 91749,\n",
       " 91750,\n",
       " 91754,\n",
       " 91755,\n",
       " 91759,\n",
       " 91762,\n",
       " 91763,\n",
       " 91764,\n",
       " 91765,\n",
       " 91766,\n",
       " 91767,\n",
       " 91768,\n",
       " 91769,\n",
       " 91770,\n",
       " 91773,\n",
       " 91775,\n",
       " 91776,\n",
       " 91778,\n",
       " 91780,\n",
       " 91784,\n",
       " 91785,\n",
       " 91786,\n",
       " 91787,\n",
       " 91788,\n",
       " 91789,\n",
       " 91790,\n",
       " 91791,\n",
       " 91792,\n",
       " 91801,\n",
       " 91803,\n",
       " 91804,\n",
       " 92092,\n",
       " 92397,\n",
       " 92501,\n",
       " 92602,\n",
       " 92606,\n",
       " 92612,\n",
       " 92621,\n",
       " 92626,\n",
       " 92647,\n",
       " 92649,\n",
       " 92683,\n",
       " 92692,\n",
       " 92701,\n",
       " 92703,\n",
       " 92704,\n",
       " 92706,\n",
       " 92707,\n",
       " 92801,\n",
       " 92802,\n",
       " 92804,\n",
       " 92806,\n",
       " 92820,\n",
       " 92821,\n",
       " 92822,\n",
       " 92823,\n",
       " 92832,\n",
       " 92833,\n",
       " 92835,\n",
       " 92841,\n",
       " 92843,\n",
       " 92845,\n",
       " 92856,\n",
       " 92886,\n",
       " 93010,\n",
       " 93063,\n",
       " 93150,\n",
       " 93436,\n",
       " 93453,\n",
       " 93510,\n",
       " 93534,\n",
       " 93535,\n",
       " 93536,\n",
       " 93543,\n",
       " 93550,\n",
       " 93551,\n",
       " 93552,\n",
       " 93553,\n",
       " 93591]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_list = list(df['zip_code'].sort_values().unique())\n",
    "zip_list = [int(z) for z in zip_list]\n",
    "zip_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import IRS `.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Id</th>\n",
       "      <th>Id2</th>\n",
       "      <th>Geography</th>\n",
       "      <th>Households; Estimate; Total</th>\n",
       "      <th>Households; Margin of Error; Total</th>\n",
       "      <th>Families; Estimate; Total</th>\n",
       "      <th>Families; Margin of Error; Total</th>\n",
       "      <th>Married-couple families; Estimate; Total</th>\n",
       "      <th>Married-couple families; Margin of Error; Total</th>\n",
       "      <th>Nonfamily households; Estimate; Total</th>\n",
       "      <th>...</th>\n",
       "      <th>Nonfamily households; Estimate; PERCENT ALLOCATED - Family income in the past 12 months</th>\n",
       "      <th>Nonfamily households; Margin of Error; PERCENT ALLOCATED - Family income in the past 12 months</th>\n",
       "      <th>Households; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "      <th>Households; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "      <th>Families; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "      <th>Families; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "      <th>Married-couple families; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "      <th>Married-couple families; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "      <th>Nonfamily households; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "      <th>Nonfamily households; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>8600000US89010</td>\n",
       "      <td>89010</td>\n",
       "      <td>ZCTA5 89010</td>\n",
       "      <td>208</td>\n",
       "      <td>54</td>\n",
       "      <td>122</td>\n",
       "      <td>33</td>\n",
       "      <td>109</td>\n",
       "      <td>31</td>\n",
       "      <td>86</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>43.0</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>8600000US89019</td>\n",
       "      <td>89019</td>\n",
       "      <td>ZCTA5 89019</td>\n",
       "      <td>788</td>\n",
       "      <td>203</td>\n",
       "      <td>354</td>\n",
       "      <td>143</td>\n",
       "      <td>211</td>\n",
       "      <td>105</td>\n",
       "      <td>434</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>28.8</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8600000US89046</td>\n",
       "      <td>89046</td>\n",
       "      <td>ZCTA5 89046</td>\n",
       "      <td>212</td>\n",
       "      <td>56</td>\n",
       "      <td>85</td>\n",
       "      <td>35</td>\n",
       "      <td>58</td>\n",
       "      <td>29</td>\n",
       "      <td>127</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>64.6</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>8600000US89060</td>\n",
       "      <td>89060</td>\n",
       "      <td>ZCTA5 89060</td>\n",
       "      <td>3874</td>\n",
       "      <td>279</td>\n",
       "      <td>2245</td>\n",
       "      <td>302</td>\n",
       "      <td>1838</td>\n",
       "      <td>238</td>\n",
       "      <td>1629</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>37.0</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>8600000US89061</td>\n",
       "      <td>89061</td>\n",
       "      <td>ZCTA5 89061</td>\n",
       "      <td>2332</td>\n",
       "      <td>231</td>\n",
       "      <td>1588</td>\n",
       "      <td>227</td>\n",
       "      <td>1382</td>\n",
       "      <td>227</td>\n",
       "      <td>744</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>27.3</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>8600000US89439</td>\n",
       "      <td>89439</td>\n",
       "      <td>ZCTA5 89439</td>\n",
       "      <td>667</td>\n",
       "      <td>84</td>\n",
       "      <td>446</td>\n",
       "      <td>81</td>\n",
       "      <td>365</td>\n",
       "      <td>88</td>\n",
       "      <td>221</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>35.3</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>8600000US90001</td>\n",
       "      <td>90001</td>\n",
       "      <td>ZCTA5 90001</td>\n",
       "      <td>13807</td>\n",
       "      <td>276</td>\n",
       "      <td>11345</td>\n",
       "      <td>270</td>\n",
       "      <td>6126</td>\n",
       "      <td>329</td>\n",
       "      <td>2462</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>33.7</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>8600000US90002</td>\n",
       "      <td>90002</td>\n",
       "      <td>ZCTA5 90002</td>\n",
       "      <td>12694</td>\n",
       "      <td>271</td>\n",
       "      <td>10294</td>\n",
       "      <td>291</td>\n",
       "      <td>5009</td>\n",
       "      <td>334</td>\n",
       "      <td>2400</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>41.0</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>8600000US90003</td>\n",
       "      <td>90003</td>\n",
       "      <td>ZCTA5 90003</td>\n",
       "      <td>16581</td>\n",
       "      <td>368</td>\n",
       "      <td>13378</td>\n",
       "      <td>411</td>\n",
       "      <td>6418</td>\n",
       "      <td>349</td>\n",
       "      <td>3203</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>32.7</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>8600000US90004</td>\n",
       "      <td>90004</td>\n",
       "      <td>ZCTA5 90004</td>\n",
       "      <td>22140</td>\n",
       "      <td>370</td>\n",
       "      <td>12942</td>\n",
       "      <td>456</td>\n",
       "      <td>8156</td>\n",
       "      <td>435</td>\n",
       "      <td>9198</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>30.1</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 131 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Id    Id2    Geography  Households; Estimate; Total  \\\n",
       "0  8600000US89010  89010  ZCTA5 89010                          208   \n",
       "1  8600000US89019  89019  ZCTA5 89019                          788   \n",
       "2  8600000US89046  89046  ZCTA5 89046                          212   \n",
       "3  8600000US89060  89060  ZCTA5 89060                         3874   \n",
       "4  8600000US89061  89061  ZCTA5 89061                         2332   \n",
       "5  8600000US89439  89439  ZCTA5 89439                          667   \n",
       "6  8600000US90001  90001  ZCTA5 90001                        13807   \n",
       "7  8600000US90002  90002  ZCTA5 90002                        12694   \n",
       "8  8600000US90003  90003  ZCTA5 90003                        16581   \n",
       "9  8600000US90004  90004  ZCTA5 90004                        22140   \n",
       "\n",
       "   Households; Margin of Error; Total  Families; Estimate; Total  \\\n",
       "0                                  54                        122   \n",
       "1                                 203                        354   \n",
       "2                                  56                         85   \n",
       "3                                 279                       2245   \n",
       "4                                 231                       1588   \n",
       "5                                  84                        446   \n",
       "6                                 276                      11345   \n",
       "7                                 271                      10294   \n",
       "8                                 368                      13378   \n",
       "9                                 370                      12942   \n",
       "\n",
       "   Families; Margin of Error; Total  Married-couple families; Estimate; Total  \\\n",
       "0                                33                                       109   \n",
       "1                               143                                       211   \n",
       "2                                35                                        58   \n",
       "3                               302                                      1838   \n",
       "4                               227                                      1382   \n",
       "5                                81                                       365   \n",
       "6                               270                                      6126   \n",
       "7                               291                                      5009   \n",
       "8                               411                                      6418   \n",
       "9                               456                                      8156   \n",
       "\n",
       "   Married-couple families; Margin of Error; Total  \\\n",
       "0                                               31   \n",
       "1                                              105   \n",
       "2                                               29   \n",
       "3                                              238   \n",
       "4                                              227   \n",
       "5                                               88   \n",
       "6                                              329   \n",
       "7                                              334   \n",
       "8                                              349   \n",
       "9                                              435   \n",
       "\n",
       "   Nonfamily households; Estimate; Total  ...  \\\n",
       "0                                     86  ...   \n",
       "1                                    434  ...   \n",
       "2                                    127  ...   \n",
       "3                                   1629  ...   \n",
       "4                                    744  ...   \n",
       "5                                    221  ...   \n",
       "6                                   2462  ...   \n",
       "7                                   2400  ...   \n",
       "8                                   3203  ...   \n",
       "9                                   9198  ...   \n",
       "\n",
       "   Nonfamily households; Estimate; PERCENT ALLOCATED - Family income in the past 12 months  \\\n",
       "0                                                (X)                                         \n",
       "1                                                (X)                                         \n",
       "2                                                (X)                                         \n",
       "3                                                (X)                                         \n",
       "4                                                (X)                                         \n",
       "5                                                (X)                                         \n",
       "6                                                (X)                                         \n",
       "7                                                (X)                                         \n",
       "8                                                (X)                                         \n",
       "9                                                (X)                                         \n",
       "\n",
       "  Nonfamily households; Margin of Error; PERCENT ALLOCATED - Family income in the past 12 months  \\\n",
       "0                                                (X)                                               \n",
       "1                                                (X)                                               \n",
       "2                                                (X)                                               \n",
       "3                                                (X)                                               \n",
       "4                                                (X)                                               \n",
       "5                                                (X)                                               \n",
       "6                                                (X)                                               \n",
       "7                                                (X)                                               \n",
       "8                                                (X)                                               \n",
       "9                                                (X)                                               \n",
       "\n",
       "  Households; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \\\n",
       "0                                                (X)                                 \n",
       "1                                                (X)                                 \n",
       "2                                                (X)                                 \n",
       "3                                                (X)                                 \n",
       "4                                                (X)                                 \n",
       "5                                                (X)                                 \n",
       "6                                                (X)                                 \n",
       "7                                                (X)                                 \n",
       "8                                                (X)                                 \n",
       "9                                                (X)                                 \n",
       "\n",
       "  Households; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \\\n",
       "0                                                (X)                                        \n",
       "1                                                (X)                                        \n",
       "2                                                (X)                                        \n",
       "3                                                (X)                                        \n",
       "4                                                (X)                                        \n",
       "5                                                (X)                                        \n",
       "6                                                (X)                                        \n",
       "7                                                (X)                                        \n",
       "8                                                (X)                                        \n",
       "9                                                (X)                                        \n",
       "\n",
       "  Families; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \\\n",
       "0                                                (X)                               \n",
       "1                                                (X)                               \n",
       "2                                                (X)                               \n",
       "3                                                (X)                               \n",
       "4                                                (X)                               \n",
       "5                                                (X)                               \n",
       "6                                                (X)                               \n",
       "7                                                (X)                               \n",
       "8                                                (X)                               \n",
       "9                                                (X)                               \n",
       "\n",
       "  Families; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \\\n",
       "0                                                (X)                                      \n",
       "1                                                (X)                                      \n",
       "2                                                (X)                                      \n",
       "3                                                (X)                                      \n",
       "4                                                (X)                                      \n",
       "5                                                (X)                                      \n",
       "6                                                (X)                                      \n",
       "7                                                (X)                                      \n",
       "8                                                (X)                                      \n",
       "9                                                (X)                                      \n",
       "\n",
       "  Married-couple families; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \\\n",
       "0                                                (X)                                              \n",
       "1                                                (X)                                              \n",
       "2                                                (X)                                              \n",
       "3                                                (X)                                              \n",
       "4                                                (X)                                              \n",
       "5                                                (X)                                              \n",
       "6                                                (X)                                              \n",
       "7                                                (X)                                              \n",
       "8                                                (X)                                              \n",
       "9                                                (X)                                              \n",
       "\n",
       "  Married-couple families; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \\\n",
       "0                                                (X)                                                     \n",
       "1                                                (X)                                                     \n",
       "2                                                (X)                                                     \n",
       "3                                                (X)                                                     \n",
       "4                                                (X)                                                     \n",
       "5                                                (X)                                                     \n",
       "6                                                (X)                                                     \n",
       "7                                                (X)                                                     \n",
       "8                                                (X)                                                     \n",
       "9                                                (X)                                                     \n",
       "\n",
       "  Nonfamily households; Estimate; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \\\n",
       "0                                               43.0                                           \n",
       "1                                               28.8                                           \n",
       "2                                               64.6                                           \n",
       "3                                               37.0                                           \n",
       "4                                               27.3                                           \n",
       "5                                               35.3                                           \n",
       "6                                               33.7                                           \n",
       "7                                               41.0                                           \n",
       "8                                               32.7                                           \n",
       "9                                               30.1                                           \n",
       "\n",
       "  Nonfamily households; Margin of Error; PERCENT ALLOCATED - Nonfamily income in the past 12 months  \n",
       "0                                                (X)                                                 \n",
       "1                                                (X)                                                 \n",
       "2                                                (X)                                                 \n",
       "3                                                (X)                                                 \n",
       "4                                                (X)                                                 \n",
       "5                                                (X)                                                 \n",
       "6                                                (X)                                                 \n",
       "7                                                (X)                                                 \n",
       "8                                                (X)                                                 \n",
       "9                                                (X)                                                 \n",
       "\n",
       "[10 rows x 131 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acs = pd.read_csv('../Assets/2017irs.csv', header=1)\n",
    "acs.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>id</th>\n",
       "      <th>id2</th>\n",
       "      <th>geography</th>\n",
       "      <th>households__estimate__total</th>\n",
       "      <th>households__margin_of_error__total</th>\n",
       "      <th>families__estimate__total</th>\n",
       "      <th>families__margin_of_error__total</th>\n",
       "      <th>married-couple_families__estimate__total</th>\n",
       "      <th>married-couple_families__margin_of_error__total</th>\n",
       "      <th>nonfamily_households__estimate__total</th>\n",
       "      <th>...</th>\n",
       "      <th>nonfamily_households__estimate__percent_allocated_-_family_income_in_the_past_12_months</th>\n",
       "      <th>nonfamily_households__margin_of_error__percent_allocated_-_family_income_in_the_past_12_months</th>\n",
       "      <th>households__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "      <th>households__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "      <th>families__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "      <th>families__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "      <th>married-couple_families__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "      <th>married-couple_families__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "      <th>nonfamily_households__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "      <th>nonfamily_households__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>8600000US89010</td>\n",
       "      <td>89010</td>\n",
       "      <td>ZCTA5 89010</td>\n",
       "      <td>208</td>\n",
       "      <td>54</td>\n",
       "      <td>122</td>\n",
       "      <td>33</td>\n",
       "      <td>109</td>\n",
       "      <td>31</td>\n",
       "      <td>86</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>43.0</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>8600000US89019</td>\n",
       "      <td>89019</td>\n",
       "      <td>ZCTA5 89019</td>\n",
       "      <td>788</td>\n",
       "      <td>203</td>\n",
       "      <td>354</td>\n",
       "      <td>143</td>\n",
       "      <td>211</td>\n",
       "      <td>105</td>\n",
       "      <td>434</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>28.8</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8600000US89046</td>\n",
       "      <td>89046</td>\n",
       "      <td>ZCTA5 89046</td>\n",
       "      <td>212</td>\n",
       "      <td>56</td>\n",
       "      <td>85</td>\n",
       "      <td>35</td>\n",
       "      <td>58</td>\n",
       "      <td>29</td>\n",
       "      <td>127</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>64.6</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>8600000US89060</td>\n",
       "      <td>89060</td>\n",
       "      <td>ZCTA5 89060</td>\n",
       "      <td>3874</td>\n",
       "      <td>279</td>\n",
       "      <td>2245</td>\n",
       "      <td>302</td>\n",
       "      <td>1838</td>\n",
       "      <td>238</td>\n",
       "      <td>1629</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>37.0</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>8600000US89061</td>\n",
       "      <td>89061</td>\n",
       "      <td>ZCTA5 89061</td>\n",
       "      <td>2332</td>\n",
       "      <td>231</td>\n",
       "      <td>1588</td>\n",
       "      <td>227</td>\n",
       "      <td>1382</td>\n",
       "      <td>227</td>\n",
       "      <td>744</td>\n",
       "      <td>...</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>(X)</td>\n",
       "      <td>27.3</td>\n",
       "      <td>(X)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 131 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id    id2    geography  households__estimate__total  \\\n",
       "0  8600000US89010  89010  ZCTA5 89010                          208   \n",
       "1  8600000US89019  89019  ZCTA5 89019                          788   \n",
       "2  8600000US89046  89046  ZCTA5 89046                          212   \n",
       "3  8600000US89060  89060  ZCTA5 89060                         3874   \n",
       "4  8600000US89061  89061  ZCTA5 89061                         2332   \n",
       "\n",
       "   households__margin_of_error__total  families__estimate__total  \\\n",
       "0                                  54                        122   \n",
       "1                                 203                        354   \n",
       "2                                  56                         85   \n",
       "3                                 279                       2245   \n",
       "4                                 231                       1588   \n",
       "\n",
       "   families__margin_of_error__total  married-couple_families__estimate__total  \\\n",
       "0                                33                                       109   \n",
       "1                               143                                       211   \n",
       "2                                35                                        58   \n",
       "3                               302                                      1838   \n",
       "4                               227                                      1382   \n",
       "\n",
       "   married-couple_families__margin_of_error__total  \\\n",
       "0                                               31   \n",
       "1                                              105   \n",
       "2                                               29   \n",
       "3                                              238   \n",
       "4                                              227   \n",
       "\n",
       "   nonfamily_households__estimate__total  ...  \\\n",
       "0                                     86  ...   \n",
       "1                                    434  ...   \n",
       "2                                    127  ...   \n",
       "3                                   1629  ...   \n",
       "4                                    744  ...   \n",
       "\n",
       "   nonfamily_households__estimate__percent_allocated_-_family_income_in_the_past_12_months  \\\n",
       "0                                                (X)                                         \n",
       "1                                                (X)                                         \n",
       "2                                                (X)                                         \n",
       "3                                                (X)                                         \n",
       "4                                                (X)                                         \n",
       "\n",
       "  nonfamily_households__margin_of_error__percent_allocated_-_family_income_in_the_past_12_months  \\\n",
       "0                                                (X)                                               \n",
       "1                                                (X)                                               \n",
       "2                                                (X)                                               \n",
       "3                                                (X)                                               \n",
       "4                                                (X)                                               \n",
       "\n",
       "  households__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months  \\\n",
       "0                                                (X)                                 \n",
       "1                                                (X)                                 \n",
       "2                                                (X)                                 \n",
       "3                                                (X)                                 \n",
       "4                                                (X)                                 \n",
       "\n",
       "  households__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months  \\\n",
       "0                                                (X)                                        \n",
       "1                                                (X)                                        \n",
       "2                                                (X)                                        \n",
       "3                                                (X)                                        \n",
       "4                                                (X)                                        \n",
       "\n",
       "  families__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months  \\\n",
       "0                                                (X)                               \n",
       "1                                                (X)                               \n",
       "2                                                (X)                               \n",
       "3                                                (X)                               \n",
       "4                                                (X)                               \n",
       "\n",
       "  families__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months  \\\n",
       "0                                                (X)                                      \n",
       "1                                                (X)                                      \n",
       "2                                                (X)                                      \n",
       "3                                                (X)                                      \n",
       "4                                                (X)                                      \n",
       "\n",
       "  married-couple_families__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months  \\\n",
       "0                                                (X)                                              \n",
       "1                                                (X)                                              \n",
       "2                                                (X)                                              \n",
       "3                                                (X)                                              \n",
       "4                                                (X)                                              \n",
       "\n",
       "  married-couple_families__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months  \\\n",
       "0                                                (X)                                                     \n",
       "1                                                (X)                                                     \n",
       "2                                                (X)                                                     \n",
       "3                                                (X)                                                     \n",
       "4                                                (X)                                                     \n",
       "\n",
       "  nonfamily_households__estimate__percent_allocated_-_nonfamily_income_in_the_past_12_months  \\\n",
       "0                                               43.0                                           \n",
       "1                                               28.8                                           \n",
       "2                                               64.6                                           \n",
       "3                                               37.0                                           \n",
       "4                                               27.3                                           \n",
       "\n",
       "  nonfamily_households__margin_of_error__percent_allocated_-_nonfamily_income_in_the_past_12_months  \n",
       "0                                                (X)                                                 \n",
       "1                                                (X)                                                 \n",
       "2                                                (X)                                                 \n",
       "3                                                (X)                                                 \n",
       "4                                                (X)                                                 \n",
       "\n",
       "[5 rows x 131 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acs.columns = [col.lower() for col in acs.columns.str.replace(' ', '_').str.replace(';', '_')]\n",
    "acs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract median / mean household income"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>zip_code</th>\n",
       "      <th>med_agi</th>\n",
       "      <th>avg_agi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>89010</td>\n",
       "      <td>46111</td>\n",
       "      <td>56505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>89019</td>\n",
       "      <td>35856</td>\n",
       "      <td>55862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>89046</td>\n",
       "      <td>31389</td>\n",
       "      <td>48033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>89060</td>\n",
       "      <td>38370</td>\n",
       "      <td>47664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>89061</td>\n",
       "      <td>54375</td>\n",
       "      <td>65354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1770</td>\n",
       "      <td>97603</td>\n",
       "      <td>46125</td>\n",
       "      <td>58900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1771</td>\n",
       "      <td>97623</td>\n",
       "      <td>42448</td>\n",
       "      <td>53052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1772</td>\n",
       "      <td>97632</td>\n",
       "      <td>40536</td>\n",
       "      <td>53140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1773</td>\n",
       "      <td>97633</td>\n",
       "      <td>42611</td>\n",
       "      <td>57167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1774</td>\n",
       "      <td>97635</td>\n",
       "      <td>29318</td>\n",
       "      <td>36831</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1775 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      zip_code med_agi avg_agi\n",
       "0        89010   46111   56505\n",
       "1        89019   35856   55862\n",
       "2        89046   31389   48033\n",
       "3        89060   38370   47664\n",
       "4        89061   54375   65354\n",
       "...        ...     ...     ...\n",
       "1770     97603   46125   58900\n",
       "1771     97623   42448   53052\n",
       "1772     97632   40536   53140\n",
       "1773     97633   42611   57167\n",
       "1774     97635   29318   36831\n",
       "\n",
       "[1775 rows x 3 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acs.rename(columns={'id2': 'zip_code',\n",
    "                    'households__estimate__median_income_(dollars)': 'med_agi',\n",
    "                    'households__estimate__mean_income_(dollars)': 'avg_agi'}, inplace=True)\n",
    "columns = ['zip_code', \n",
    "           'med_agi', \n",
    "           'avg_agi']\n",
    "acs[columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zip_code     int64\n",
       "med_agi     object\n",
       "avg_agi     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acs[columns].dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Only use ZIP codes that are present in our DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zip_code</th>\n",
       "      <th>med_agi</th>\n",
       "      <th>avg_agi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>90001</td>\n",
       "      <td>35660</td>\n",
       "      <td>45650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>90002</td>\n",
       "      <td>34000</td>\n",
       "      <td>44463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>90003</td>\n",
       "      <td>34397</td>\n",
       "      <td>43342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>90004</td>\n",
       "      <td>46581</td>\n",
       "      <td>82091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>90005</td>\n",
       "      <td>32461</td>\n",
       "      <td>49310</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    zip_code med_agi avg_agi\n",
       "6      90001   35660   45650\n",
       "7      90002   34000   44463\n",
       "8      90003   34397   43342\n",
       "9      90004   46581   82091\n",
       "10     90005   32461   49310"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_agi = acs.loc[acs['zip_code'].isin(zip_list), columns]\n",
    "zip_agi.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine income data with our DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>alias</th>\n",
       "      <th>category</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>price</th>\n",
       "      <th>rating</th>\n",
       "      <th>review_count</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>med_agi</th>\n",
       "      <th>avg_agi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>lucilles-smokehouse-bar-b-que-brea</td>\n",
       "      <td>['bbq', 'smokehouse', 'southern']</td>\n",
       "      <td>Brea</td>\n",
       "      <td>zzvkb4J_sQAk8N20OWdZhg</td>\n",
       "      <td>33.911154</td>\n",
       "      <td>-117.881259</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1257.0</td>\n",
       "      <td>92821.0</td>\n",
       "      <td>86070</td>\n",
       "      <td>106163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>subway-restaurants-brea-4</td>\n",
       "      <td>['sandwiches']</td>\n",
       "      <td>Brea</td>\n",
       "      <td>zWs68lBY2PyNOTRJhxXapg</td>\n",
       "      <td>33.911959</td>\n",
       "      <td>-117.867374</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>92821.0</td>\n",
       "      <td>86070</td>\n",
       "      <td>106163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>nekter-juice-bar-brea</td>\n",
       "      <td>['juicebars', 'acaibowls', 'icecream']</td>\n",
       "      <td>Brea</td>\n",
       "      <td>zOYNwoDjpg6Ehwn0RKX9qA</td>\n",
       "      <td>33.911283</td>\n",
       "      <td>-117.867449</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>367.0</td>\n",
       "      <td>92821.0</td>\n",
       "      <td>86070</td>\n",
       "      <td>106163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>arbys-brea-2</td>\n",
       "      <td>['sandwiches', 'hotdogs']</td>\n",
       "      <td>Brea</td>\n",
       "      <td>zNNLl821AlzApHYqRxNALw</td>\n",
       "      <td>33.911431</td>\n",
       "      <td>-117.865941</td>\n",
       "      <td>1</td>\n",
       "      <td>2.5</td>\n",
       "      <td>84.0</td>\n",
       "      <td>92821.0</td>\n",
       "      <td>86070</td>\n",
       "      <td>106163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>portofino-trattoria-brea</td>\n",
       "      <td>['italian']</td>\n",
       "      <td>Brea</td>\n",
       "      <td>zJ4pvxtpo3Y5LvylLXmCzA</td>\n",
       "      <td>33.932860</td>\n",
       "      <td>-117.916253</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>92821.0</td>\n",
       "      <td>86070</td>\n",
       "      <td>106163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27422</td>\n",
       "      <td>northridge-hospital-cafeteria-northridge</td>\n",
       "      <td>['tradamerican']</td>\n",
       "      <td>Northridge</td>\n",
       "      <td>3OnkDJAW9MMef5VkbFWY8w</td>\n",
       "      <td>34.219970</td>\n",
       "      <td>-118.532930</td>\n",
       "      <td>1</td>\n",
       "      <td>4.5</td>\n",
       "      <td>7.0</td>\n",
       "      <td>91328.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27423</td>\n",
       "      <td>sbarro-brea</td>\n",
       "      <td>['italian', 'pizza']</td>\n",
       "      <td>Brea</td>\n",
       "      <td>2xrnvWPB0VTcSxMDL4Dqzw</td>\n",
       "      <td>33.916132</td>\n",
       "      <td>-117.885852</td>\n",
       "      <td>1</td>\n",
       "      <td>2.5</td>\n",
       "      <td>43.0</td>\n",
       "      <td>92621.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27424</td>\n",
       "      <td>jojo-cakes-santa-ana-3</td>\n",
       "      <td>['bakeries', 'desserts', 'cupcakes']</td>\n",
       "      <td>Santa Ana</td>\n",
       "      <td>2V4BH14_2hLxRshiYKdpmQ</td>\n",
       "      <td>33.717411</td>\n",
       "      <td>-117.914160</td>\n",
       "      <td>2</td>\n",
       "      <td>4.5</td>\n",
       "      <td>59.0</td>\n",
       "      <td>92704.0</td>\n",
       "      <td>63056</td>\n",
       "      <td>77776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27425</td>\n",
       "      <td>cosmic-cafe-mt-wilson</td>\n",
       "      <td>['cafes']</td>\n",
       "      <td>Mt Wilson</td>\n",
       "      <td>1RshW3QfxW0gtaXajWZyQw</td>\n",
       "      <td>34.223577</td>\n",
       "      <td>-118.061963</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>91023.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27426</td>\n",
       "      <td>matsu-sushi-castaic</td>\n",
       "      <td>['sushi']</td>\n",
       "      <td>Castaic</td>\n",
       "      <td>0qX0H5VraOWI1Ec0NuMMKg</td>\n",
       "      <td>34.452913</td>\n",
       "      <td>-118.616239</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>146.0</td>\n",
       "      <td>91310.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27427 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          alias  \\\n",
       "0            lucilles-smokehouse-bar-b-que-brea   \n",
       "1                     subway-restaurants-brea-4   \n",
       "2                         nekter-juice-bar-brea   \n",
       "3                                  arbys-brea-2   \n",
       "4                      portofino-trattoria-brea   \n",
       "...                                         ...   \n",
       "27422  northridge-hospital-cafeteria-northridge   \n",
       "27423                               sbarro-brea   \n",
       "27424                    jojo-cakes-santa-ana-3   \n",
       "27425                     cosmic-cafe-mt-wilson   \n",
       "27426                       matsu-sushi-castaic   \n",
       "\n",
       "                                     category        city  \\\n",
       "0           ['bbq', 'smokehouse', 'southern']        Brea   \n",
       "1                              ['sandwiches']        Brea   \n",
       "2      ['juicebars', 'acaibowls', 'icecream']        Brea   \n",
       "3                   ['sandwiches', 'hotdogs']        Brea   \n",
       "4                                 ['italian']        Brea   \n",
       "...                                       ...         ...   \n",
       "27422                        ['tradamerican']  Northridge   \n",
       "27423                    ['italian', 'pizza']        Brea   \n",
       "27424    ['bakeries', 'desserts', 'cupcakes']   Santa Ana   \n",
       "27425                               ['cafes']   Mt Wilson   \n",
       "27426                               ['sushi']     Castaic   \n",
       "\n",
       "                           id   latitude   longitude  price  rating  \\\n",
       "0      zzvkb4J_sQAk8N20OWdZhg  33.911154 -117.881259      2     3.5   \n",
       "1      zWs68lBY2PyNOTRJhxXapg  33.911959 -117.867374      1     3.0   \n",
       "2      zOYNwoDjpg6Ehwn0RKX9qA  33.911283 -117.867449      2     3.5   \n",
       "3      zNNLl821AlzApHYqRxNALw  33.911431 -117.865941      1     2.5   \n",
       "4      zJ4pvxtpo3Y5LvylLXmCzA  33.932860 -117.916253      2     4.0   \n",
       "...                       ...        ...         ...    ...     ...   \n",
       "27422  3OnkDJAW9MMef5VkbFWY8w  34.219970 -118.532930      1     4.5   \n",
       "27423  2xrnvWPB0VTcSxMDL4Dqzw  33.916132 -117.885852      1     2.5   \n",
       "27424  2V4BH14_2hLxRshiYKdpmQ  33.717411 -117.914160      2     4.5   \n",
       "27425  1RshW3QfxW0gtaXajWZyQw  34.223577 -118.061963      1     4.0   \n",
       "27426  0qX0H5VraOWI1Ec0NuMMKg  34.452913 -118.616239      2     3.5   \n",
       "\n",
       "       review_count  zip_code med_agi avg_agi  \n",
       "0            1257.0   92821.0   86070  106163  \n",
       "1              22.0   92821.0   86070  106163  \n",
       "2             367.0   92821.0   86070  106163  \n",
       "3              84.0   92821.0   86070  106163  \n",
       "4             231.0   92821.0   86070  106163  \n",
       "...             ...       ...     ...     ...  \n",
       "27422           7.0   91328.0     NaN     NaN  \n",
       "27423          43.0   92621.0     NaN     NaN  \n",
       "27424          59.0   92704.0   63056   77776  \n",
       "27425          29.0   91023.0     NaN     NaN  \n",
       "27426         146.0   91310.0     NaN     NaN  \n",
       "\n",
       "[27427 rows x 12 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_income = pd.merge(df, zip_agi, how = 'outer' )\n",
    "df_income"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deal with erroneous (or new) ZIP codes\n",
    "\n",
    "Some ZIP codes do not have income data from 2017. We suspect these ZIP codes are either new or erroneously entered into the Yelp website."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(27311, 12)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_income.dropna(inplace = True)\n",
    "df_income.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(27119, 12)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dash = list(df_income[df_income['med_agi'].str.contains('-')].index)\n",
    "df_income.drop(index=dash, inplace=True)\n",
    "df_income.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alias            object\n",
       "category         object\n",
       "city             object\n",
       "id               object\n",
       "latitude        float64\n",
       "longitude       float64\n",
       "price             int64\n",
       "rating          float64\n",
       "review_count    float64\n",
       "zip_code        float64\n",
       "med_agi          object\n",
       "avg_agi          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_income.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_income['avg_agi'] = df_income['avg_agi'].astype(int)\n",
    "df_income['med_agi'] = df_income['med_agi'].astype(int)\n",
    "df_income['zip_code'] = df_income['zip_code'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alias            object\n",
       "category         object\n",
       "city             object\n",
       "id               object\n",
       "latitude        float64\n",
       "longitude       float64\n",
       "price             int64\n",
       "rating          float64\n",
       "review_count    float64\n",
       "zip_code         object\n",
       "med_agi           int64\n",
       "avg_agi           int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_income.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll form a `count` column for later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_income['count'] = df_income.groupby('zip_code')['zip_code'].transform('count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save the combined data to a `.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_income.to_csv('../data/data_income.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combined data organized by zipcode for Flask App"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_income['zip_code'] = df_income['zip_code'].astype(str)\n",
    "df_zipcode = df_income.pivot_table(index = ['zip_code']).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_zipcode.to_csv('../data/data_zipcode.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Move on to 04 - Yelp Model Selection"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}