06.1 - Combine county population estimates.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_2000 = pd.read_csv(\"data/raw/co-est00int-tot.csv\", encoding=\"latin-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_2000[\"FIPS\"] = pop_2000[[\"STATE\", \"COUNTY\"]].apply(lambda row: \"%0.2d%0.3d\" % (row[\"STATE\"], row[\"COUNTY\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_2000.set_index(\"FIPS\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_2010 = pd.read_csv(\"data/raw/co-est2017-alldata.csv\", encoding=\"latin-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_2010[\"FIPS\"] = pop_2010[[\"STATE\", \"COUNTY\"]].apply(lambda row: \"%0.2d%0.3d\" % (row[\"STATE\"], row[\"COUNTY\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_2010.set_index(\"FIPS\", inplace=True)"
   ]
  },
  {
   "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>SUMLEV</th>\n",
       "      <th>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>...</th>\n",
       "      <th>RDOMESTICMIG2015</th>\n",
       "      <th>RDOMESTICMIG2016</th>\n",
       "      <th>RDOMESTICMIG2017</th>\n",
       "      <th>RNETMIG2011</th>\n",
       "      <th>RNETMIG2012</th>\n",
       "      <th>RNETMIG2013</th>\n",
       "      <th>RNETMIG2014</th>\n",
       "      <th>RNETMIG2015</th>\n",
       "      <th>RNETMIG2016</th>\n",
       "      <th>RNETMIG2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIPS</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>01000</th>\n",
       "      <td>40</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4779736</td>\n",
       "      <td>4780135</td>\n",
       "      <td>4785579</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.317205</td>\n",
       "      <td>-0.404473</td>\n",
       "      <td>0.788882</td>\n",
       "      <td>0.450741</td>\n",
       "      <td>0.939393</td>\n",
       "      <td>1.364296</td>\n",
       "      <td>0.694271</td>\n",
       "      <td>0.678575</td>\n",
       "      <td>0.558931</td>\n",
       "      <td>1.708218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01001</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>54571</td>\n",
       "      <td>54571</td>\n",
       "      <td>54750</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.950739</td>\n",
       "      <td>4.831269</td>\n",
       "      <td>1.047102</td>\n",
       "      <td>5.911832</td>\n",
       "      <td>-6.102101</td>\n",
       "      <td>-4.050282</td>\n",
       "      <td>2.099325</td>\n",
       "      <td>-1.659040</td>\n",
       "      <td>5.103709</td>\n",
       "      <td>1.317904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01003</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>182265</td>\n",
       "      <td>182265</td>\n",
       "      <td>183110</td>\n",
       "      <td>...</td>\n",
       "      <td>17.047872</td>\n",
       "      <td>20.493601</td>\n",
       "      <td>22.383175</td>\n",
       "      <td>16.285940</td>\n",
       "      <td>17.196786</td>\n",
       "      <td>22.615285</td>\n",
       "      <td>20.380904</td>\n",
       "      <td>17.903749</td>\n",
       "      <td>21.317244</td>\n",
       "      <td>23.163873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01005</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>27457</td>\n",
       "      <td>27457</td>\n",
       "      <td>27332</td>\n",
       "      <td>...</td>\n",
       "      <td>-16.222436</td>\n",
       "      <td>-18.755525</td>\n",
       "      <td>-19.042395</td>\n",
       "      <td>0.256021</td>\n",
       "      <td>-6.822433</td>\n",
       "      <td>-8.018920</td>\n",
       "      <td>-5.549762</td>\n",
       "      <td>-16.411069</td>\n",
       "      <td>-18.947692</td>\n",
       "      <td>-19.159940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01007</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>22915</td>\n",
       "      <td>22919</td>\n",
       "      <td>22872</td>\n",
       "      <td>...</td>\n",
       "      <td>0.931388</td>\n",
       "      <td>-1.416117</td>\n",
       "      <td>-0.882983</td>\n",
       "      <td>-5.041980</td>\n",
       "      <td>-4.096646</td>\n",
       "      <td>-5.890038</td>\n",
       "      <td>1.243450</td>\n",
       "      <td>1.818424</td>\n",
       "      <td>-0.531044</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 132 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       SUMLEV  REGION  DIVISION  STATE  COUNTY   STNAME         CTYNAME  \\\n",
       "FIPS                                                                      \n",
       "01000      40       3         6      1       0  Alabama         Alabama   \n",
       "01001      50       3         6      1       1  Alabama  Autauga County   \n",
       "01003      50       3         6      1       3  Alabama  Baldwin County   \n",
       "01005      50       3         6      1       5  Alabama  Barbour County   \n",
       "01007      50       3         6      1       7  Alabama     Bibb County   \n",
       "\n",
       "       CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010     ...       \\\n",
       "FIPS                                                         ...        \n",
       "01000        4779736            4780135          4785579     ...        \n",
       "01001          54571              54571            54750     ...        \n",
       "01003         182265             182265           183110     ...        \n",
       "01005          27457              27457            27332     ...        \n",
       "01007          22915              22919            22872     ...        \n",
       "\n",
       "       RDOMESTICMIG2015  RDOMESTICMIG2016  RDOMESTICMIG2017  RNETMIG2011  \\\n",
       "FIPS                                                                       \n",
       "01000         -0.317205         -0.404473          0.788882     0.450741   \n",
       "01001         -1.950739          4.831269          1.047102     5.911832   \n",
       "01003         17.047872         20.493601         22.383175    16.285940   \n",
       "01005        -16.222436        -18.755525        -19.042395     0.256021   \n",
       "01007          0.931388         -1.416117         -0.882983    -5.041980   \n",
       "\n",
       "       RNETMIG2012  RNETMIG2013  RNETMIG2014  RNETMIG2015  RNETMIG2016  \\\n",
       "FIPS                                                                     \n",
       "01000     0.939393     1.364296     0.694271     0.678575     0.558931   \n",
       "01001    -6.102101    -4.050282     2.099325    -1.659040     5.103709   \n",
       "01003    17.196786    22.615285    20.380904    17.903749    21.317244   \n",
       "01005    -6.822433    -8.018920    -5.549762   -16.411069   -18.947692   \n",
       "01007    -4.096646    -5.890038     1.243450     1.818424    -0.531044   \n",
       "\n",
       "       RNETMIG2017  \n",
       "FIPS                \n",
       "01000     1.708218  \n",
       "01001     1.317904  \n",
       "01003    23.163873  \n",
       "01005   -19.159940  \n",
       "01007     0.000000  \n",
       "\n",
       "[5 rows x 132 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_2010.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pop_2000.join(pop_2010, how=\"inner\", rsuffix=\"_2010\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\n",
    "    \"POPESTIMATE%d\" % (year)\n",
    "    for year in range(2004,2015)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df[\"SUMLEV\"] == 50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open(\"data/processed/county_intersection_list_2004_2014.txt\",\"r\")\n",
    "fips = f.read().strip().split(\"\\n\")\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.loc[fips]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"data/processed/county_population_2004_2014.csv\", index_label=\"FIPS\")"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}