notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Census \"hard to count\" analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By [Ben Welsh](https://palewi.re/who-is-ben-welsh/)\n",
    "\n",
    "This data preparation routine was developed for the April 29, 2019, Los Angeles Times story [\"A census undercount could cost California billions — and L.A. is famously hard to track\"](https://www.latimes.com/local/lanow/la-me-la-county-census-hard-to-count-20190429-htmlstory.html).\n",
    "\n",
    "It combines the California Department of Finance's \"hard to count\" estimates with the tract maps published by the U.S. Census Bureau. Together they were used to make a graphic to accompany the story."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How we did it"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download the state's \"hard to count\" esimates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "!python download.py htc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download the Census Bureau's tract maps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "!python download.py tracts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import Python tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in the hard-to-count data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"./data/htc/tracts.xlsx\", dtype={\"GEOID\": str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>GEOID</th>\n",
       "      <th>CA HTC Index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06001400100</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06001400200</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06001400300</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06001400400</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06001400500</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         GEOID  CA HTC Index\n",
       "0  06001400100            20\n",
       "1  06001400200            16\n",
       "2  06001400300            31\n",
       "3  06001400400            35\n",
       "4  06001400500            47"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean it up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_trimmed = df.rename(columns={\n",
    "    \"GEOID\": \"geoid\",\n",
    "    \"CA HTC Index\": \"htc_index\"\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>geoid</th>\n",
       "      <th>htc_index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06001400100</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06001400200</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06001400300</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06001400400</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06001400500</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         geoid  htc_index\n",
       "0  06001400100         20\n",
       "1  06001400200         16\n",
       "2  06001400300         31\n",
       "3  06001400400         35\n",
       "4  06001400500         47"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trimmed.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in the tract maps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf = gpd.read_file(\"data/tracts/tl_2010_06_tract10.shp\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>STATEFP10</th>\n",
       "      <th>COUNTYFP10</th>\n",
       "      <th>TRACTCE10</th>\n",
       "      <th>GEOID10</th>\n",
       "      <th>NAME10</th>\n",
       "      <th>NAMELSAD10</th>\n",
       "      <th>MTFCC10</th>\n",
       "      <th>FUNCSTAT10</th>\n",
       "      <th>ALAND10</th>\n",
       "      <th>AWATER10</th>\n",
       "      <th>INTPTLAT10</th>\n",
       "      <th>INTPTLON10</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06</td>\n",
       "      <td>083</td>\n",
       "      <td>002103</td>\n",
       "      <td>06083002103</td>\n",
       "      <td>21.03</td>\n",
       "      <td>Census Tract 21.03</td>\n",
       "      <td>G5020</td>\n",
       "      <td>S</td>\n",
       "      <td>2838200</td>\n",
       "      <td>7603</td>\n",
       "      <td>+34.9306689</td>\n",
       "      <td>-120.4270588</td>\n",
       "      <td>POLYGON ((-120.417938 34.938341, -120.417658 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06</td>\n",
       "      <td>083</td>\n",
       "      <td>002402</td>\n",
       "      <td>06083002402</td>\n",
       "      <td>24.02</td>\n",
       "      <td>Census Tract 24.02</td>\n",
       "      <td>G5020</td>\n",
       "      <td>S</td>\n",
       "      <td>16288573</td>\n",
       "      <td>44468</td>\n",
       "      <td>+34.9287963</td>\n",
       "      <td>-120.4780833</td>\n",
       "      <td>POLYGON ((-120.473893 34.920814, -120.474285 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06</td>\n",
       "      <td>083</td>\n",
       "      <td>002102</td>\n",
       "      <td>06083002102</td>\n",
       "      <td>21.02</td>\n",
       "      <td>Census Tract 21.02</td>\n",
       "      <td>G5020</td>\n",
       "      <td>S</td>\n",
       "      <td>1352551</td>\n",
       "      <td>0</td>\n",
       "      <td>+34.9421111</td>\n",
       "      <td>-120.4267767</td>\n",
       "      <td>POLYGON ((-120.417658 34.938345, -120.417938 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06</td>\n",
       "      <td>083</td>\n",
       "      <td>002010</td>\n",
       "      <td>06083002010</td>\n",
       "      <td>20.10</td>\n",
       "      <td>Census Tract 20.10</td>\n",
       "      <td>G5020</td>\n",
       "      <td>S</td>\n",
       "      <td>2417990</td>\n",
       "      <td>0</td>\n",
       "      <td>+34.8714281</td>\n",
       "      <td>-120.4100285</td>\n",
       "      <td>POLYGON ((-120.411468 34.879619, -120.411413 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06</td>\n",
       "      <td>083</td>\n",
       "      <td>002009</td>\n",
       "      <td>06083002009</td>\n",
       "      <td>20.09</td>\n",
       "      <td>Census Tract 20.09</td>\n",
       "      <td>G5020</td>\n",
       "      <td>S</td>\n",
       "      <td>2603281</td>\n",
       "      <td>0</td>\n",
       "      <td>+34.8722878</td>\n",
       "      <td>-120.4277159</td>\n",
       "      <td>POLYGON ((-120.423524 34.879283, -120.422856 3...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STATEFP10 COUNTYFP10 TRACTCE10      GEOID10 NAME10          NAMELSAD10  \\\n",
       "0        06        083    002103  06083002103  21.03  Census Tract 21.03   \n",
       "1        06        083    002402  06083002402  24.02  Census Tract 24.02   \n",
       "2        06        083    002102  06083002102  21.02  Census Tract 21.02   \n",
       "3        06        083    002010  06083002010  20.10  Census Tract 20.10   \n",
       "4        06        083    002009  06083002009  20.09  Census Tract 20.09   \n",
       "\n",
       "  MTFCC10 FUNCSTAT10   ALAND10  AWATER10   INTPTLAT10    INTPTLON10  \\\n",
       "0   G5020          S   2838200      7603  +34.9306689  -120.4270588   \n",
       "1   G5020          S  16288573     44468  +34.9287963  -120.4780833   \n",
       "2   G5020          S   1352551         0  +34.9421111  -120.4267767   \n",
       "3   G5020          S   2417990         0  +34.8714281  -120.4100285   \n",
       "4   G5020          S   2603281         0  +34.8722878  -120.4277159   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((-120.417938 34.938341, -120.417658 3...  \n",
       "1  POLYGON ((-120.473893 34.920814, -120.474285 3...  \n",
       "2  POLYGON ((-120.417658 34.938345, -120.417938 3...  \n",
       "3  POLYGON ((-120.411468 34.879619, -120.411413 3...  \n",
       "4  POLYGON ((-120.423524 34.879283, -120.422856 3...  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean it up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_trimmed = gdf[[\n",
    "    'GEOID10',\n",
    "    'geometry'\n",
    "]].rename(columns={\n",
    "    \"GEOID10\": \"geoid\",\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>geoid</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06083002103</td>\n",
       "      <td>POLYGON ((-120.417938 34.938341, -120.417658 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06083002402</td>\n",
       "      <td>POLYGON ((-120.473893 34.920814, -120.474285 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06083002102</td>\n",
       "      <td>POLYGON ((-120.417658 34.938345, -120.417938 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06083002010</td>\n",
       "      <td>POLYGON ((-120.411468 34.879619, -120.411413 3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06083002009</td>\n",
       "      <td>POLYGON ((-120.423524 34.879283, -120.422856 3...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         geoid                                           geometry\n",
       "0  06083002103  POLYGON ((-120.417938 34.938341, -120.417658 3...\n",
       "1  06083002402  POLYGON ((-120.473893 34.920814, -120.474285 3...\n",
       "2  06083002102  POLYGON ((-120.417658 34.938345, -120.417938 3...\n",
       "3  06083002010  POLYGON ((-120.411468 34.879619, -120.411413 3...\n",
       "4  06083002009  POLYGON ((-120.423524 34.879283, -120.422856 3..."
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_trimmed.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge the data and the map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_gdf = gdf_trimmed.merge(df_trimmed, on=\"geoid\", how=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output the merged file for a graphic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_gdf.to_file(\"data/processed/tracts.shp\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many of the hardest to count are here in LA County?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_gdf['county_fips'] = merged_gdf.geoid.str.slice(2, 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "037    2345\n",
       "073     628\n",
       "059     583\n",
       "065     453\n",
       "085     372\n",
       "Name: county_fips, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_gdf.county_fips.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "top_100 = merged_gdf.sort_values(\"htc_index\", ascending=False).head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>geoid</th>\n",
       "      <th>geometry</th>\n",
       "      <th>htc_index</th>\n",
       "      <th>county_fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3473</th>\n",
       "      <td>06077000100</td>\n",
       "      <td>POLYGON ((-121.292051 37.95407, -121.293315 37...</td>\n",
       "      <td>136</td>\n",
       "      <td>077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7102</th>\n",
       "      <td>06037212305</td>\n",
       "      <td>POLYGON ((-118.2998 34.057707, -118.29871 34.0...</td>\n",
       "      <td>128</td>\n",
       "      <td>037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6966</th>\n",
       "      <td>06037209300</td>\n",
       "      <td>POLYGON ((-118.271663 34.053097, -118.2714 34....</td>\n",
       "      <td>127</td>\n",
       "      <td>037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7270</th>\n",
       "      <td>06037231710</td>\n",
       "      <td>POLYGON ((-118.287222 34.010102, -118.28722 34...</td>\n",
       "      <td>123</td>\n",
       "      <td>037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3472</th>\n",
       "      <td>06077000300</td>\n",
       "      <td>POLYGON ((-121.292051 37.95407, -121.291901 37...</td>\n",
       "      <td>123</td>\n",
       "      <td>077</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            geoid                                           geometry  \\\n",
       "3473  06077000100  POLYGON ((-121.292051 37.95407, -121.293315 37...   \n",
       "7102  06037212305  POLYGON ((-118.2998 34.057707, -118.29871 34.0...   \n",
       "6966  06037209300  POLYGON ((-118.271663 34.053097, -118.2714 34....   \n",
       "7270  06037231710  POLYGON ((-118.287222 34.010102, -118.28722 34...   \n",
       "3472  06077000300  POLYGON ((-121.292051 37.95407, -121.291901 37...   \n",
       "\n",
       "      htc_index county_fips  \n",
       "3473        136         077  \n",
       "7102        128         037  \n",
       "6966        127         037  \n",
       "7270        123         037  \n",
       "3472        123         077  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_100.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "037    57\n",
       "077     7\n",
       "019     5\n",
       "075     5\n",
       "071     5\n",
       "073     4\n",
       "025     3\n",
       "067     3\n",
       "029     3\n",
       "053     2\n",
       "001     2\n",
       "059     1\n",
       "095     1\n",
       "047     1\n",
       "099     1\n",
       "Name: county_fips, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_100.county_fips.value_counts()"
   ]
  }
 ],
 "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}