Notebooks/Compile population data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combine data files with state populations\n",
    "The first data file has 2000-2010"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:34:12.487554Z",
     "start_time": "2017-10-30T16:34:10.713622Z"
    }
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "from os.path import join\n",
    "cwd = os.getcwd()\n",
    "data_directory = join(cwd, '..', 'Data storage')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import 2000-2010 data\n",
    "The sex, origin, race, and age columns are ALL when they have values of 0\n",
    "\n",
    "Not clear if these are beginning or end of year values.\n",
    "\n",
    "https://www2.census.gov/programs-surveys/popest/datasets/2000-2010/intercensal/state/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:34:16.891879Z",
     "start_time": "2017-10-30T16:34:16.674736Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = os.path.join(data_directory, 'Population data',\n",
    "                    'st-est00int-alldata.csv')\n",
    "pop1 = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:34:18.341992Z",
     "start_time": "2017-10-30T16:34:18.316636Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>NAME</th>\n",
       "      <th>SEX</th>\n",
       "      <th>ORIGIN</th>\n",
       "      <th>RACE</th>\n",
       "      <th>AGEGRP</th>\n",
       "      <th>ESTIMATESBASE2000</th>\n",
       "      <th>POPESTIMATE2000</th>\n",
       "      <th>...</th>\n",
       "      <th>POPESTIMATE2002</th>\n",
       "      <th>POPESTIMATE2003</th>\n",
       "      <th>POPESTIMATE2004</th>\n",
       "      <th>POPESTIMATE2005</th>\n",
       "      <th>POPESTIMATE2006</th>\n",
       "      <th>POPESTIMATE2007</th>\n",
       "      <th>POPESTIMATE2008</th>\n",
       "      <th>POPESTIMATE2009</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>United States</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>281424600</td>\n",
       "      <td>282162411</td>\n",
       "      <td>...</td>\n",
       "      <td>287625193</td>\n",
       "      <td>290107933</td>\n",
       "      <td>292805298</td>\n",
       "      <td>295516599</td>\n",
       "      <td>298379912</td>\n",
       "      <td>301231207</td>\n",
       "      <td>304093966</td>\n",
       "      <td>306771529</td>\n",
       "      <td>308745538</td>\n",
       "      <td>309349689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>United States</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>19176154</td>\n",
       "      <td>19178293</td>\n",
       "      <td>...</td>\n",
       "      <td>19429192</td>\n",
       "      <td>19592446</td>\n",
       "      <td>19785885</td>\n",
       "      <td>19917400</td>\n",
       "      <td>19938883</td>\n",
       "      <td>20125962</td>\n",
       "      <td>20271127</td>\n",
       "      <td>20244518</td>\n",
       "      <td>20201362</td>\n",
       "      <td>20200529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>United States</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>20549855</td>\n",
       "      <td>20463852</td>\n",
       "      <td>...</td>\n",
       "      <td>19872417</td>\n",
       "      <td>19620851</td>\n",
       "      <td>19454237</td>\n",
       "      <td>19389067</td>\n",
       "      <td>19544688</td>\n",
       "      <td>19714611</td>\n",
       "      <td>19929602</td>\n",
       "      <td>20182499</td>\n",
       "      <td>20348657</td>\n",
       "      <td>20382409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>United States</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>20528425</td>\n",
       "      <td>20637696</td>\n",
       "      <td>...</td>\n",
       "      <td>21261421</td>\n",
       "      <td>21415353</td>\n",
       "      <td>21411680</td>\n",
       "      <td>21212579</td>\n",
       "      <td>21033138</td>\n",
       "      <td>20841042</td>\n",
       "      <td>20706655</td>\n",
       "      <td>20660564</td>\n",
       "      <td>20677194</td>\n",
       "      <td>20694011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>United States</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>20218782</td>\n",
       "      <td>20294955</td>\n",
       "      <td>...</td>\n",
       "      <td>20610370</td>\n",
       "      <td>20797166</td>\n",
       "      <td>21102552</td>\n",
       "      <td>21486214</td>\n",
       "      <td>21807709</td>\n",
       "      <td>22067816</td>\n",
       "      <td>22210880</td>\n",
       "      <td>22192810</td>\n",
       "      <td>22040343</td>\n",
       "      <td>21959087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   REGION  DIVISION  STATE           NAME  SEX  ORIGIN  RACE  AGEGRP  \\\n",
       "0       0         0      0  United States    0       0     0       0   \n",
       "1       0         0      0  United States    0       0     0       1   \n",
       "2       0         0      0  United States    0       0     0       2   \n",
       "3       0         0      0  United States    0       0     0       3   \n",
       "4       0         0      0  United States    0       0     0       4   \n",
       "\n",
       "   ESTIMATESBASE2000  POPESTIMATE2000       ...         POPESTIMATE2002  \\\n",
       "0          281424600        282162411       ...               287625193   \n",
       "1           19176154         19178293       ...                19429192   \n",
       "2           20549855         20463852       ...                19872417   \n",
       "3           20528425         20637696       ...                21261421   \n",
       "4           20218782         20294955       ...                20610370   \n",
       "\n",
       "   POPESTIMATE2003  POPESTIMATE2004  POPESTIMATE2005  POPESTIMATE2006  \\\n",
       "0        290107933        292805298        295516599        298379912   \n",
       "1         19592446         19785885         19917400         19938883   \n",
       "2         19620851         19454237         19389067         19544688   \n",
       "3         21415353         21411680         21212579         21033138   \n",
       "4         20797166         21102552         21486214         21807709   \n",
       "\n",
       "   POPESTIMATE2007  POPESTIMATE2008  POPESTIMATE2009  CENSUS2010POP  \\\n",
       "0        301231207        304093966        306771529      308745538   \n",
       "1         20125962         20271127         20244518       20201362   \n",
       "2         19714611         19929602         20182499       20348657   \n",
       "3         20841042         20706655         20660564       20677194   \n",
       "4         22067816         22210880         22192810       22040343   \n",
       "\n",
       "   POPESTIMATE2010  \n",
       "0        309349689  \n",
       "1         20200529  \n",
       "2         20382409  \n",
       "3         20694011  \n",
       "4         21959087  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:34:40.634078Z",
     "start_time": "2017-10-30T16:34:40.613118Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pop1 = pop1.loc[(pop1['SEX'] == 0) &\n",
    "                (pop1['ORIGIN'] == 0) &\n",
    "                (pop1['RACE'] == 0) &\n",
    "                (pop1['AGEGRP'] == 0), :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:35:15.240643Z",
     "start_time": "2017-10-30T16:35:15.236029Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Column names for population estimate\n",
    "est_cols = ['POPESTIMATE{}'.format(x) for x in range(2000, 2011)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:35:32.342354Z",
     "start_time": "2017-10-30T16:35:32.332791Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Melt the wide-form data into a tidy dataframe\n",
    "pop1_tidy = pd.melt(pop1, id_vars='NAME',\n",
    "                    value_vars=est_cols, var_name='Year',\n",
    "                    value_name='Population')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:35:35.480574Z",
     "start_time": "2017-10-30T16:35:35.470689Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>Year</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>POPESTIMATE2000</td>\n",
       "      <td>282162411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>POPESTIMATE2000</td>\n",
       "      <td>4452173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>POPESTIMATE2000</td>\n",
       "      <td>627963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>POPESTIMATE2000</td>\n",
       "      <td>5160586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>POPESTIMATE2000</td>\n",
       "      <td>2678588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            NAME             Year  Population\n",
       "0  United States  POPESTIMATE2000   282162411\n",
       "1        Alabama  POPESTIMATE2000     4452173\n",
       "2         Alaska  POPESTIMATE2000      627963\n",
       "3        Arizona  POPESTIMATE2000     5160586\n",
       "4       Arkansas  POPESTIMATE2000     2678588"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop1_tidy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:04.134970Z",
     "start_time": "2017-10-30T16:36:04.126266Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def map_year(x):\n",
    "    'Return last 4 characters (the year)'\n",
    "    year = x[-4:]\n",
    "    return int(year)\n",
    "\n",
    "pop1_tidy['Year'] = pop1_tidy['Year'].map(map_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The values shown below are ever slightly different than those listed in the later dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:10.619297Z",
     "start_time": "2017-10-30T16:36:10.606318Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>Year</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>United States</td>\n",
       "      <td>2010</td>\n",
       "      <td>309349689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2010</td>\n",
       "      <td>713985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2010</td>\n",
       "      <td>6413737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2010</td>\n",
       "      <td>2921606</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              NAME  Year  Population\n",
       "520  United States  2010   309349689\n",
       "521        Alabama  2010     4785298\n",
       "522         Alaska  2010      713985\n",
       "523        Arizona  2010     6413737\n",
       "524       Arkansas  2010     2921606"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop1_tidy.loc[pop1_tidy['Year'] == 2010].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:29.041098Z",
     "start_time": "2017-10-30T16:36:29.031243Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>Year</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>2000</td>\n",
       "      <td>282162411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2000</td>\n",
       "      <td>4452173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2000</td>\n",
       "      <td>627963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2000</td>\n",
       "      <td>5160586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2000</td>\n",
       "      <td>2678588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            NAME  Year  Population\n",
       "0  United States  2000   282162411\n",
       "1        Alabama  2000     4452173\n",
       "2         Alaska  2000      627963\n",
       "3        Arizona  2000     5160586\n",
       "4       Arkansas  2000     2678588"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop1_tidy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:29.457716Z",
     "start_time": "2017-10-30T16:36:29.447824Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>Year</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>2010</td>\n",
       "      <td>8024617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>568</th>\n",
       "      <td>Washington</td>\n",
       "      <td>2010</td>\n",
       "      <td>6744496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>2010</td>\n",
       "      <td>1853973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>2010</td>\n",
       "      <td>5691047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2010</td>\n",
       "      <td>564460</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              NAME  Year  Population\n",
       "567       Virginia  2010     8024617\n",
       "568     Washington  2010     6744496\n",
       "569  West Virginia  2010     1853973\n",
       "570      Wisconsin  2010     5691047\n",
       "571        Wyoming  2010      564460"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop1_tidy.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:29.850270Z",
     "start_time": "2017-10-30T16:36:29.846689Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pop1_tidy.columns = ['State', 'Year', 'Population']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import 2010-2016 data\n",
    "https://www.census.gov/data/tables/2016/demo/popest/state-total.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:43.458638Z",
     "start_time": "2017-10-30T16:36:43.408276Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = os.path.join(data_directory, 'Population data', 'nst-est2016-01.xlsx')\n",
    "pop2 = pd.read_excel(path, header=3, parse_cols='A, D:J', skip_footer=7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:45.940900Z",
     "start_time": "2017-10-30T16:36:45.928511Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>309348193</td>\n",
       "      <td>311663358</td>\n",
       "      <td>313998379</td>\n",
       "      <td>316204908</td>\n",
       "      <td>318563456</td>\n",
       "      <td>320896618</td>\n",
       "      <td>323127513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northeast</th>\n",
       "      <td>55388056</td>\n",
       "      <td>55632766</td>\n",
       "      <td>55829059</td>\n",
       "      <td>55988771</td>\n",
       "      <td>56116791</td>\n",
       "      <td>56184737</td>\n",
       "      <td>56209510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Midwest</th>\n",
       "      <td>66978602</td>\n",
       "      <td>67153331</td>\n",
       "      <td>67332320</td>\n",
       "      <td>67543948</td>\n",
       "      <td>67726368</td>\n",
       "      <td>67838387</td>\n",
       "      <td>67941429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>114863114</td>\n",
       "      <td>116061801</td>\n",
       "      <td>117299171</td>\n",
       "      <td>118424320</td>\n",
       "      <td>119696311</td>\n",
       "      <td>121039206</td>\n",
       "      <td>122319574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>72118421</td>\n",
       "      <td>72815460</td>\n",
       "      <td>73537829</td>\n",
       "      <td>74247869</td>\n",
       "      <td>75023986</td>\n",
       "      <td>75834288</td>\n",
       "      <td>76657000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    2010       2011       2012       2013       2014  \\\n",
       "United States  309348193  311663358  313998379  316204908  318563456   \n",
       "Northeast       55388056   55632766   55829059   55988771   56116791   \n",
       "Midwest         66978602   67153331   67332320   67543948   67726368   \n",
       "South          114863114  116061801  117299171  118424320  119696311   \n",
       "West            72118421   72815460   73537829   74247869   75023986   \n",
       "\n",
       "                    2015       2016  \n",
       "United States  320896618  323127513  \n",
       "Northeast       56184737   56209510  \n",
       "Midwest         67838387   67941429  \n",
       "South          121039206  122319574  \n",
       "West            75834288   76657000  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:36:55.381981Z",
     "start_time": "2017-10-30T16:36:55.369945Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>.Virginia</th>\n",
       "      <td>8025773</td>\n",
       "      <td>8110035</td>\n",
       "      <td>8192048</td>\n",
       "      <td>8262692</td>\n",
       "      <td>8317372</td>\n",
       "      <td>8367587</td>\n",
       "      <td>8411808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>.Washington</th>\n",
       "      <td>6743226</td>\n",
       "      <td>6822520</td>\n",
       "      <td>6895226</td>\n",
       "      <td>6968006</td>\n",
       "      <td>7054196</td>\n",
       "      <td>7160290</td>\n",
       "      <td>7288000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>.West Virginia</th>\n",
       "      <td>1854230</td>\n",
       "      <td>1854972</td>\n",
       "      <td>1856560</td>\n",
       "      <td>1853231</td>\n",
       "      <td>1848514</td>\n",
       "      <td>1841053</td>\n",
       "      <td>1831102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>.Wisconsin</th>\n",
       "      <td>5690263</td>\n",
       "      <td>5709640</td>\n",
       "      <td>5726177</td>\n",
       "      <td>5742854</td>\n",
       "      <td>5758377</td>\n",
       "      <td>5767891</td>\n",
       "      <td>5778708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>.Wyoming</th>\n",
       "      <td>564513</td>\n",
       "      <td>567725</td>\n",
       "      <td>576765</td>\n",
       "      <td>582684</td>\n",
       "      <td>583642</td>\n",
       "      <td>586555</td>\n",
       "      <td>585501</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   2010     2011     2012     2013     2014     2015     2016\n",
       ".Virginia       8025773  8110035  8192048  8262692  8317372  8367587  8411808\n",
       ".Washington     6743226  6822520  6895226  6968006  7054196  7160290  7288000\n",
       ".West Virginia  1854230  1854972  1856560  1853231  1848514  1841053  1831102\n",
       ".Wisconsin      5690263  5709640  5726177  5742854  5758377  5767891  5778708\n",
       ".Wyoming         564513   567725   576765   582684   583642   586555   585501"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop2.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:37:04.068511Z",
     "start_time": "2017-10-30T16:37:04.063956Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "drop_rows = ['Northeast', 'Midwest', 'South', 'West']\n",
    "pop2.drop(drop_rows, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:37:41.861566Z",
     "start_time": "2017-10-30T16:37:41.857935Z"
    }
   },
   "outputs": [],
   "source": [
    "pop2.index = pop2.index.str.strip('.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:37:42.857641Z",
     "start_time": "2017-10-30T16:37:42.844396Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>309348193</td>\n",
       "      <td>311663358</td>\n",
       "      <td>313998379</td>\n",
       "      <td>316204908</td>\n",
       "      <td>318563456</td>\n",
       "      <td>320896618</td>\n",
       "      <td>323127513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama</th>\n",
       "      <td>4785492</td>\n",
       "      <td>4799918</td>\n",
       "      <td>4815960</td>\n",
       "      <td>4829479</td>\n",
       "      <td>4843214</td>\n",
       "      <td>4853875</td>\n",
       "      <td>4863300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>714031</td>\n",
       "      <td>722713</td>\n",
       "      <td>731089</td>\n",
       "      <td>736879</td>\n",
       "      <td>736705</td>\n",
       "      <td>737709</td>\n",
       "      <td>741894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>6408312</td>\n",
       "      <td>6467163</td>\n",
       "      <td>6549634</td>\n",
       "      <td>6624617</td>\n",
       "      <td>6719993</td>\n",
       "      <td>6817565</td>\n",
       "      <td>6931071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>2921995</td>\n",
       "      <td>2939493</td>\n",
       "      <td>2950685</td>\n",
       "      <td>2958663</td>\n",
       "      <td>2966912</td>\n",
       "      <td>2977853</td>\n",
       "      <td>2988248</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    2010       2011       2012       2013       2014  \\\n",
       "United States  309348193  311663358  313998379  316204908  318563456   \n",
       "Alabama          4785492    4799918    4815960    4829479    4843214   \n",
       "Alaska            714031     722713     731089     736879     736705   \n",
       "Arizona          6408312    6467163    6549634    6624617    6719993   \n",
       "Arkansas         2921995    2939493    2950685    2958663    2966912   \n",
       "\n",
       "                    2015       2016  \n",
       "United States  320896618  323127513  \n",
       "Alabama          4853875    4863300  \n",
       "Alaska            737709     741894  \n",
       "Arizona          6817565    6931071  \n",
       "Arkansas         2977853    2988248  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:37:49.158398Z",
     "start_time": "2017-10-30T16:37:49.152225Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([2010, 2011, 2012, 2013, 2014, 2015, 2016], dtype='int64')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:37:54.388262Z",
     "start_time": "2017-10-30T16:37:54.377205Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pop2_tidy = pd.melt(pop2.reset_index(), id_vars='index',\n",
    "                    value_vars=range(2010, 2017), value_name='Population',\n",
    "                    var_name='Year')\n",
    "pop2_tidy.columns = ['State', 'Year', 'Population']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:37:58.078817Z",
     "start_time": "2017-10-30T16:37:58.073653Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pop_total = pd.concat([pop1_tidy, pop2_tidy])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The overlapping 2010 values are different, but just barely. I'm going to re-combine the datasets and keep values from the second dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:38:17.265430Z",
     "start_time": "2017-10-30T16:38:17.239274Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2010</td>\n",
       "      <td>713985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2010</td>\n",
       "      <td>714031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2010</td>\n",
       "      <td>6413737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2010</td>\n",
       "      <td>6408312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2010</td>\n",
       "      <td>2921606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2010</td>\n",
       "      <td>2921995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>California</td>\n",
       "      <td>2010</td>\n",
       "      <td>37349363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>California</td>\n",
       "      <td>2010</td>\n",
       "      <td>37332685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>2010</td>\n",
       "      <td>5049071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>2010</td>\n",
       "      <td>5048644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>527</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>2010</td>\n",
       "      <td>3577073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>2010</td>\n",
       "      <td>3579899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>2010</td>\n",
       "      <td>899769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>2010</td>\n",
       "      <td>899816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>2010</td>\n",
       "      <td>604453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>2010</td>\n",
       "      <td>605183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>530</th>\n",
       "      <td>Florida</td>\n",
       "      <td>2010</td>\n",
       "      <td>18843326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Florida</td>\n",
       "      <td>2010</td>\n",
       "      <td>18849098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>2010</td>\n",
       "      <td>9713521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>531</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>2010</td>\n",
       "      <td>9712587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>2010</td>\n",
       "      <td>1363621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>2010</td>\n",
       "      <td>1363945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>2010</td>\n",
       "      <td>1571010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>533</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>2010</td>\n",
       "      <td>1571450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>2010</td>\n",
       "      <td>12841578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>2010</td>\n",
       "      <td>12843166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>2010</td>\n",
       "      <td>6490528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>2010</td>\n",
       "      <td>6490621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>2010</td>\n",
       "      <td>3838957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>2010</td>\n",
       "      <td>3838048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>2010</td>\n",
       "      <td>12712343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>2010</td>\n",
       "      <td>12709630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>2010</td>\n",
       "      <td>1053337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>2010</td>\n",
       "      <td>1052886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>2010</td>\n",
       "      <td>4635943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>2010</td>\n",
       "      <td>4636312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>2010</td>\n",
       "      <td>816325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>2010</td>\n",
       "      <td>816463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>2010</td>\n",
       "      <td>6356671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>2010</td>\n",
       "      <td>6356897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>564</th>\n",
       "      <td>Texas</td>\n",
       "      <td>2010</td>\n",
       "      <td>25257114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Texas</td>\n",
       "      <td>2010</td>\n",
       "      <td>25244310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>2010</td>\n",
       "      <td>309348193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>United States</td>\n",
       "      <td>2010</td>\n",
       "      <td>309349689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Utah</td>\n",
       "      <td>2010</td>\n",
       "      <td>2775326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>Utah</td>\n",
       "      <td>2010</td>\n",
       "      <td>2776469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>2010</td>\n",
       "      <td>625960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>2010</td>\n",
       "      <td>625982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>2010</td>\n",
       "      <td>8025773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>2010</td>\n",
       "      <td>8024617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Washington</td>\n",
       "      <td>2010</td>\n",
       "      <td>6743226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>568</th>\n",
       "      <td>Washington</td>\n",
       "      <td>2010</td>\n",
       "      <td>6744496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>2010</td>\n",
       "      <td>1854230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>2010</td>\n",
       "      <td>1853973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>2010</td>\n",
       "      <td>5690263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>2010</td>\n",
       "      <td>5691047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2010</td>\n",
       "      <td>564460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2010</td>\n",
       "      <td>564513</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>104 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    State  Year  Population\n",
       "1                 Alabama  2010     4785492\n",
       "521               Alabama  2010     4785298\n",
       "522                Alaska  2010      713985\n",
       "2                  Alaska  2010      714031\n",
       "523               Arizona  2010     6413737\n",
       "3                 Arizona  2010     6408312\n",
       "524              Arkansas  2010     2921606\n",
       "4                Arkansas  2010     2921995\n",
       "525            California  2010    37349363\n",
       "5              California  2010    37332685\n",
       "526              Colorado  2010     5049071\n",
       "6                Colorado  2010     5048644\n",
       "527           Connecticut  2010     3577073\n",
       "7             Connecticut  2010     3579899\n",
       "528              Delaware  2010      899769\n",
       "8                Delaware  2010      899816\n",
       "529  District of Columbia  2010      604453\n",
       "9    District of Columbia  2010      605183\n",
       "530               Florida  2010    18843326\n",
       "10                Florida  2010    18849098\n",
       "11                Georgia  2010     9713521\n",
       "531               Georgia  2010     9712587\n",
       "532                Hawaii  2010     1363621\n",
       "12                 Hawaii  2010     1363945\n",
       "13                  Idaho  2010     1571010\n",
       "533                 Idaho  2010     1571450\n",
       "14               Illinois  2010    12841578\n",
       "534              Illinois  2010    12843166\n",
       "15                Indiana  2010     6490528\n",
       "535               Indiana  2010     6490621\n",
       "..                    ...   ...         ...\n",
       "558                Oregon  2010     3838957\n",
       "38                 Oregon  2010     3838048\n",
       "39           Pennsylvania  2010    12712343\n",
       "559          Pennsylvania  2010    12709630\n",
       "40           Rhode Island  2010     1053337\n",
       "560          Rhode Island  2010     1052886\n",
       "41         South Carolina  2010     4635943\n",
       "561        South Carolina  2010     4636312\n",
       "42           South Dakota  2010      816325\n",
       "562          South Dakota  2010      816463\n",
       "43              Tennessee  2010     6356671\n",
       "563             Tennessee  2010     6356897\n",
       "564                 Texas  2010    25257114\n",
       "44                  Texas  2010    25244310\n",
       "0           United States  2010   309348193\n",
       "520         United States  2010   309349689\n",
       "45                   Utah  2010     2775326\n",
       "565                  Utah  2010     2776469\n",
       "566               Vermont  2010      625960\n",
       "46                Vermont  2010      625982\n",
       "47               Virginia  2010     8025773\n",
       "567              Virginia  2010     8024617\n",
       "48             Washington  2010     6743226\n",
       "568            Washington  2010     6744496\n",
       "49          West Virginia  2010     1854230\n",
       "569         West Virginia  2010     1853973\n",
       "50              Wisconsin  2010     5690263\n",
       "570             Wisconsin  2010     5691047\n",
       "571               Wyoming  2010      564460\n",
       "51                Wyoming  2010      564513\n",
       "\n",
       "[104 rows x 3 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_total.loc[pop_total['Year']==2010].sort_values('State')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:38:23.689337Z",
     "start_time": "2017-10-30T16:38:23.682891Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pop_total = pd.concat([pop1_tidy.loc[pop1_tidy['Year'] < 2010], pop2_tidy])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:38:26.926979Z",
     "start_time": "2017-10-30T16:38:26.915337Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>2000</td>\n",
       "      <td>282162411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2000</td>\n",
       "      <td>4452173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2000</td>\n",
       "      <td>627963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2000</td>\n",
       "      <td>5160586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2000</td>\n",
       "      <td>2678588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           State  Year  Population\n",
       "0  United States  2000   282162411\n",
       "1        Alabama  2000     4452173\n",
       "2         Alaska  2000      627963\n",
       "3        Arizona  2000     5160586\n",
       "4       Arkansas  2000     2678588"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_total.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-10-30T16:38:27.256796Z",
     "start_time": "2017-10-30T16:38:27.242016Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>2016</td>\n",
       "      <td>8411808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>Washington</td>\n",
       "      <td>2016</td>\n",
       "      <td>7288000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>2016</td>\n",
       "      <td>1831102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>2016</td>\n",
       "      <td>5778708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2016</td>\n",
       "      <td>585501</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             State  Year  Population\n",
       "359       Virginia  2016     8411808\n",
       "360     Washington  2016     7288000\n",
       "361  West Virginia  2016     1831102\n",
       "362      Wisconsin  2016     5778708\n",
       "363        Wyoming  2016      585501"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_total.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-08-03T17:56:17.320409Z",
     "start_time": "2017-08-03T17:56:17.312010Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = os.path.join('Data storage', 'Derived data', 'State population.csv')\n",
    "pop_total.to_csv(path, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py36",
   "language": "python",
   "name": "py36"
  },
  "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.2"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}