08- IndexingDataFrame_ed.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we've seen, both Series and DataFrames can have indices applied to them. The index is essentially a row level\n",
    "label, and in pandas the rows correspond to axis zero. Indices can either be either autogenerated, such as when \n",
    "we create a new Series without an index, in which case we get numeric values, or they can be set explicitly, like\n",
    "when we use the dictionary object to create the series, or when we loaded data from the CSV file and set \n",
    "appropriate parameters. Another option for setting an index is to use the set_index() function. This function \n",
    "takes a list of columns and promotes those columns to an index. In this lecture we'll explore more about how \n",
    "indexes work in pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>GRE Score</th>\n",
       "      <th>TOEFL Score</th>\n",
       "      <th>University Rating</th>\n",
       "      <th>SOP</th>\n",
       "      <th>LOR</th>\n",
       "      <th>CGPA</th>\n",
       "      <th>Research</th>\n",
       "      <th>Chance of Admit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Serial No.</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>1</th>\n",
       "      <td>337</td>\n",
       "      <td>118</td>\n",
       "      <td>4</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>9.65</td>\n",
       "      <td>1</td>\n",
       "      <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>324</td>\n",
       "      <td>107</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.87</td>\n",
       "      <td>1</td>\n",
       "      <td>0.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>316</td>\n",
       "      <td>104</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>322</td>\n",
       "      <td>110</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>8.67</td>\n",
       "      <td>1</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>314</td>\n",
       "      <td>103</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.21</td>\n",
       "      <td>0</td>\n",
       "      <td>0.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            GRE Score  TOEFL Score  University Rating  SOP  LOR   CGPA  \\\n",
       "Serial No.                                                               \n",
       "1                 337          118                  4  4.5   4.5  9.65   \n",
       "2                 324          107                  4  4.0   4.5  8.87   \n",
       "3                 316          104                  3  3.0   3.5  8.00   \n",
       "4                 322          110                  3  3.5   2.5  8.67   \n",
       "5                 314          103                  2  2.0   3.0  8.21   \n",
       "\n",
       "            Research  Chance of Admit   \n",
       "Serial No.                              \n",
       "1                  1              0.92  \n",
       "2                  1              0.76  \n",
       "3                  1              0.72  \n",
       "4                  1              0.80  \n",
       "5                  0              0.65  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The set_index() function is a destructive process, and it doesn't keep the current index. \n",
    "# If you want to keep the current index, you need to manually create a new column and copy into \n",
    "# it values from the index attribute.\n",
    "\n",
    "# Lets import pandas and our admissions dataset\n",
    "import pandas as pd\n",
    "df = pd.read_csv(\"datasets/Admission_Predict.csv\", index_col=0)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>GRE Score</th>\n",
       "      <th>TOEFL Score</th>\n",
       "      <th>University Rating</th>\n",
       "      <th>SOP</th>\n",
       "      <th>LOR</th>\n",
       "      <th>CGPA</th>\n",
       "      <th>Research</th>\n",
       "      <th>Serial Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chance of Admit</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>0.92</th>\n",
       "      <td>337</td>\n",
       "      <td>118</td>\n",
       "      <td>4</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>9.65</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.76</th>\n",
       "      <td>324</td>\n",
       "      <td>107</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.87</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.72</th>\n",
       "      <td>316</td>\n",
       "      <td>104</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.80</th>\n",
       "      <td>322</td>\n",
       "      <td>110</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>8.67</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.65</th>\n",
       "      <td>314</td>\n",
       "      <td>103</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.21</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  GRE Score  TOEFL Score  University Rating  SOP  LOR   CGPA  \\\n",
       "Chance of Admit                                                                \n",
       "0.92                    337          118                  4  4.5   4.5  9.65   \n",
       "0.76                    324          107                  4  4.0   4.5  8.87   \n",
       "0.72                    316          104                  3  3.0   3.5  8.00   \n",
       "0.80                    322          110                  3  3.5   2.5  8.67   \n",
       "0.65                    314          103                  2  2.0   3.0  8.21   \n",
       "\n",
       "                  Research  Serial Number  \n",
       "Chance of Admit                            \n",
       "0.92                     1              1  \n",
       "0.76                     1              2  \n",
       "0.72                     1              3  \n",
       "0.80                     1              4  \n",
       "0.65                     0              5  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's say that we don't want to index the DataFrame by serial numbers, but instead by the\n",
    "# chance of admit. But lets assume we want to keep the serial number for later. So, lets\n",
    "# preserve the serial number into a new column. We can do this using the indexing operator \n",
    "# on the string that has the column label. Then we can use the set_index to set index \n",
    "# of the column to chance of admit\n",
    "\n",
    "# So we copy the indexed data into its own column\n",
    "df['Serial Number'] = df.index\n",
    "# Then we set the index to another column\n",
    "df = df.set_index('Chance of Admit ')\n",
    "df.head()"
   ]
  },
  {
   "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>Chance of Admit</th>\n",
       "      <th>GRE Score</th>\n",
       "      <th>TOEFL Score</th>\n",
       "      <th>University Rating</th>\n",
       "      <th>SOP</th>\n",
       "      <th>LOR</th>\n",
       "      <th>CGPA</th>\n",
       "      <th>Research</th>\n",
       "      <th>Serial Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.92</td>\n",
       "      <td>337</td>\n",
       "      <td>118</td>\n",
       "      <td>4</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>9.65</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.76</td>\n",
       "      <td>324</td>\n",
       "      <td>107</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.87</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.72</td>\n",
       "      <td>316</td>\n",
       "      <td>104</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.80</td>\n",
       "      <td>322</td>\n",
       "      <td>110</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>8.67</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.65</td>\n",
       "      <td>314</td>\n",
       "      <td>103</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.21</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Chance of Admit   GRE Score  TOEFL Score  University Rating  SOP  LOR   \\\n",
       "0              0.92        337          118                  4  4.5   4.5   \n",
       "1              0.76        324          107                  4  4.0   4.5   \n",
       "2              0.72        316          104                  3  3.0   3.5   \n",
       "3              0.80        322          110                  3  3.5   2.5   \n",
       "4              0.65        314          103                  2  2.0   3.0   \n",
       "\n",
       "   CGPA  Research  Serial Number  \n",
       "0  9.65         1              1  \n",
       "1  8.87         1              2  \n",
       "2  8.00         1              3  \n",
       "3  8.67         1              4  \n",
       "4  8.21         0              5  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You'll see that when we create a new index from an existing column the index has a name, \n",
    "# which is the original name of the column.\n",
    "\n",
    "# We can get rid of the index completely by calling the function reset_index(). This promotes the \n",
    "# index into a column and creates a default numbered index.\n",
    "df = df.reset_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>RDOMESTICMIG2011</th>\n",
       "      <th>RDOMESTICMIG2012</th>\n",
       "      <th>RDOMESTICMIG2013</th>\n",
       "      <th>RDOMESTICMIG2014</th>\n",
       "      <th>RDOMESTICMIG2015</th>\n",
       "      <th>RNETMIG2011</th>\n",
       "      <th>RNETMIG2012</th>\n",
       "      <th>RNETMIG2013</th>\n",
       "      <th>RNETMIG2014</th>\n",
       "      <th>RNETMIG2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>4780127</td>\n",
       "      <td>4785161</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002295</td>\n",
       "      <td>-0.193196</td>\n",
       "      <td>0.381066</td>\n",
       "      <td>0.582002</td>\n",
       "      <td>-0.467369</td>\n",
       "      <td>1.030015</td>\n",
       "      <td>0.826644</td>\n",
       "      <td>1.383282</td>\n",
       "      <td>1.724718</td>\n",
       "      <td>0.712594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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>54660</td>\n",
       "      <td>...</td>\n",
       "      <td>7.242091</td>\n",
       "      <td>-2.915927</td>\n",
       "      <td>-3.012349</td>\n",
       "      <td>2.265971</td>\n",
       "      <td>-2.530799</td>\n",
       "      <td>7.606016</td>\n",
       "      <td>-2.626146</td>\n",
       "      <td>-2.722002</td>\n",
       "      <td>2.592270</td>\n",
       "      <td>-2.187333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>183193</td>\n",
       "      <td>...</td>\n",
       "      <td>14.832960</td>\n",
       "      <td>17.647293</td>\n",
       "      <td>21.845705</td>\n",
       "      <td>19.243287</td>\n",
       "      <td>17.197872</td>\n",
       "      <td>15.844176</td>\n",
       "      <td>18.559627</td>\n",
       "      <td>22.727626</td>\n",
       "      <td>20.317142</td>\n",
       "      <td>18.293499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>27341</td>\n",
       "      <td>...</td>\n",
       "      <td>-4.728132</td>\n",
       "      <td>-2.500690</td>\n",
       "      <td>-7.056824</td>\n",
       "      <td>-3.904217</td>\n",
       "      <td>-10.543299</td>\n",
       "      <td>-4.874741</td>\n",
       "      <td>-2.758113</td>\n",
       "      <td>-7.167664</td>\n",
       "      <td>-3.978583</td>\n",
       "      <td>-10.543299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>22861</td>\n",
       "      <td>...</td>\n",
       "      <td>-5.527043</td>\n",
       "      <td>-5.068871</td>\n",
       "      <td>-6.201001</td>\n",
       "      <td>-0.177537</td>\n",
       "      <td>0.177258</td>\n",
       "      <td>-5.088389</td>\n",
       "      <td>-4.363636</td>\n",
       "      <td>-5.403729</td>\n",
       "      <td>0.754533</td>\n",
       "      <td>1.107861</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUMLEV  REGION  DIVISION  STATE  COUNTY   STNAME         CTYNAME  \\\n",
       "0      40       3         6      1       0  Alabama         Alabama   \n",
       "1      50       3         6      1       1  Alabama  Autauga County   \n",
       "2      50       3         6      1       3  Alabama  Baldwin County   \n",
       "3      50       3         6      1       5  Alabama  Barbour County   \n",
       "4      50       3         6      1       7  Alabama     Bibb County   \n",
       "\n",
       "   CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  ...  RDOMESTICMIG2011  \\\n",
       "0        4779736            4780127          4785161  ...          0.002295   \n",
       "1          54571              54571            54660  ...          7.242091   \n",
       "2         182265             182265           183193  ...         14.832960   \n",
       "3          27457              27457            27341  ...         -4.728132   \n",
       "4          22915              22919            22861  ...         -5.527043   \n",
       "\n",
       "   RDOMESTICMIG2012  RDOMESTICMIG2013  RDOMESTICMIG2014  RDOMESTICMIG2015  \\\n",
       "0         -0.193196          0.381066          0.582002         -0.467369   \n",
       "1         -2.915927         -3.012349          2.265971         -2.530799   \n",
       "2         17.647293         21.845705         19.243287         17.197872   \n",
       "3         -2.500690         -7.056824         -3.904217        -10.543299   \n",
       "4         -5.068871         -6.201001         -0.177537          0.177258   \n",
       "\n",
       "   RNETMIG2011  RNETMIG2012  RNETMIG2013  RNETMIG2014  RNETMIG2015  \n",
       "0     1.030015     0.826644     1.383282     1.724718     0.712594  \n",
       "1     7.606016    -2.626146    -2.722002     2.592270    -2.187333  \n",
       "2    15.844176    18.559627    22.727626    20.317142    18.293499  \n",
       "3    -4.874741    -2.758113    -7.167664    -3.978583   -10.543299  \n",
       "4    -5.088389    -4.363636    -5.403729     0.754533     1.107861  \n",
       "\n",
       "[5 rows x 100 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One nice feature of Pandas is multi-level indexing. This is similar to composite keys in \n",
    "# relational database systems. To create a multi-level index, we simply call set index and \n",
    "# give it a list of columns that we're interested in promoting to an index.\n",
    "\n",
    "# Pandas will search through these in order, finding the distinct data and form composite indices.\n",
    "# A good example of this is often found when dealing with geographical data which is sorted by \n",
    "# regions or demographics.\n",
    "\n",
    "# Let's change data sets and look at some census data for a better example. This data is stored in \n",
    "# the file census.csv and comes from the United States Census Bureau. In particular, this is a \n",
    "# breakdown of the population level data at the US county level. It's a great example of how \n",
    "# different kinds of data sets might be formatted when you're trying to clean them.\n",
    "\n",
    "# Let's import and see what the data looks like\n",
    "df = pd.read_csv('datasets/census.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([40, 50])"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# In this data set there are two summarized levels, one that contains summary \n",
    "# data for the whole country. And one that contains summary data for each state.\n",
    "# I want to see a list of all the unique values in a given column. In this \n",
    "# DataFrame, we see that the possible values for the sum level are using the \n",
    "# unique function on the DataFrame. This is similar to the SQL distinct operator\n",
    "\n",
    "# Here we can run unique on the sum level of our current DataFrame \n",
    "df['SUMLEV'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We see that there are only two different values, 40 and 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>RDOMESTICMIG2011</th>\n",
       "      <th>RDOMESTICMIG2012</th>\n",
       "      <th>RDOMESTICMIG2013</th>\n",
       "      <th>RDOMESTICMIG2014</th>\n",
       "      <th>RDOMESTICMIG2015</th>\n",
       "      <th>RNETMIG2011</th>\n",
       "      <th>RNETMIG2012</th>\n",
       "      <th>RNETMIG2013</th>\n",
       "      <th>RNETMIG2014</th>\n",
       "      <th>RNETMIG2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</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>54660</td>\n",
       "      <td>...</td>\n",
       "      <td>7.242091</td>\n",
       "      <td>-2.915927</td>\n",
       "      <td>-3.012349</td>\n",
       "      <td>2.265971</td>\n",
       "      <td>-2.530799</td>\n",
       "      <td>7.606016</td>\n",
       "      <td>-2.626146</td>\n",
       "      <td>-2.722002</td>\n",
       "      <td>2.592270</td>\n",
       "      <td>-2.187333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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>183193</td>\n",
       "      <td>...</td>\n",
       "      <td>14.832960</td>\n",
       "      <td>17.647293</td>\n",
       "      <td>21.845705</td>\n",
       "      <td>19.243287</td>\n",
       "      <td>17.197872</td>\n",
       "      <td>15.844176</td>\n",
       "      <td>18.559627</td>\n",
       "      <td>22.727626</td>\n",
       "      <td>20.317142</td>\n",
       "      <td>18.293499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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>27341</td>\n",
       "      <td>...</td>\n",
       "      <td>-4.728132</td>\n",
       "      <td>-2.500690</td>\n",
       "      <td>-7.056824</td>\n",
       "      <td>-3.904217</td>\n",
       "      <td>-10.543299</td>\n",
       "      <td>-4.874741</td>\n",
       "      <td>-2.758113</td>\n",
       "      <td>-7.167664</td>\n",
       "      <td>-3.978583</td>\n",
       "      <td>-10.543299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>22861</td>\n",
       "      <td>...</td>\n",
       "      <td>-5.527043</td>\n",
       "      <td>-5.068871</td>\n",
       "      <td>-6.201001</td>\n",
       "      <td>-0.177537</td>\n",
       "      <td>0.177258</td>\n",
       "      <td>-5.088389</td>\n",
       "      <td>-4.363636</td>\n",
       "      <td>-5.403729</td>\n",
       "      <td>0.754533</td>\n",
       "      <td>1.107861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>57322</td>\n",
       "      <td>57322</td>\n",
       "      <td>57373</td>\n",
       "      <td>...</td>\n",
       "      <td>1.807375</td>\n",
       "      <td>-1.177622</td>\n",
       "      <td>-1.748766</td>\n",
       "      <td>-2.062535</td>\n",
       "      <td>-1.369970</td>\n",
       "      <td>1.859511</td>\n",
       "      <td>-0.848580</td>\n",
       "      <td>-1.402476</td>\n",
       "      <td>-1.577232</td>\n",
       "      <td>-0.884411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUMLEV  REGION  DIVISION  STATE  COUNTY   STNAME         CTYNAME  \\\n",
       "1      50       3         6      1       1  Alabama  Autauga County   \n",
       "2      50       3         6      1       3  Alabama  Baldwin County   \n",
       "3      50       3         6      1       5  Alabama  Barbour County   \n",
       "4      50       3         6      1       7  Alabama     Bibb County   \n",
       "5      50       3         6      1       9  Alabama   Blount County   \n",
       "\n",
       "   CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  ...  RDOMESTICMIG2011  \\\n",
       "1          54571              54571            54660  ...          7.242091   \n",
       "2         182265             182265           183193  ...         14.832960   \n",
       "3          27457              27457            27341  ...         -4.728132   \n",
       "4          22915              22919            22861  ...         -5.527043   \n",
       "5          57322              57322            57373  ...          1.807375   \n",
       "\n",
       "   RDOMESTICMIG2012  RDOMESTICMIG2013  RDOMESTICMIG2014  RDOMESTICMIG2015  \\\n",
       "1         -2.915927         -3.012349          2.265971         -2.530799   \n",
       "2         17.647293         21.845705         19.243287         17.197872   \n",
       "3         -2.500690         -7.056824         -3.904217        -10.543299   \n",
       "4         -5.068871         -6.201001         -0.177537          0.177258   \n",
       "5         -1.177622         -1.748766         -2.062535         -1.369970   \n",
       "\n",
       "   RNETMIG2011  RNETMIG2012  RNETMIG2013  RNETMIG2014  RNETMIG2015  \n",
       "1     7.606016    -2.626146    -2.722002     2.592270    -2.187333  \n",
       "2    15.844176    18.559627    22.727626    20.317142    18.293499  \n",
       "3    -4.874741    -2.758113    -7.167664    -3.978583   -10.543299  \n",
       "4    -5.088389    -4.363636    -5.403729     0.754533     1.107861  \n",
       "5     1.859511    -0.848580    -1.402476    -1.577232    -0.884411  \n",
       "\n",
       "[5 rows x 100 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's exclue all of the rows that are summaries \n",
    "# at the state level and just keep the county data. \n",
    "df=df[df['SUMLEV'] == 50]\n",
    "df.head()"
   ]
  },
  {
   "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>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>BIRTHS2010</th>\n",
       "      <th>BIRTHS2011</th>\n",
       "      <th>BIRTHS2012</th>\n",
       "      <th>BIRTHS2013</th>\n",
       "      <th>BIRTHS2014</th>\n",
       "      <th>BIRTHS2015</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</th>\n",
       "      <th>POPESTIMATE2013</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "      <th>POPESTIMATE2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>151</td>\n",
       "      <td>636</td>\n",
       "      <td>615</td>\n",
       "      <td>574</td>\n",
       "      <td>623</td>\n",
       "      <td>600</td>\n",
       "      <td>54660</td>\n",
       "      <td>55253</td>\n",
       "      <td>55175</td>\n",
       "      <td>55038</td>\n",
       "      <td>55290</td>\n",
       "      <td>55347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>517</td>\n",
       "      <td>2187</td>\n",
       "      <td>2092</td>\n",
       "      <td>2160</td>\n",
       "      <td>2186</td>\n",
       "      <td>2240</td>\n",
       "      <td>183193</td>\n",
       "      <td>186659</td>\n",
       "      <td>190396</td>\n",
       "      <td>195126</td>\n",
       "      <td>199713</td>\n",
       "      <td>203709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>70</td>\n",
       "      <td>335</td>\n",
       "      <td>300</td>\n",
       "      <td>283</td>\n",
       "      <td>260</td>\n",
       "      <td>269</td>\n",
       "      <td>27341</td>\n",
       "      <td>27226</td>\n",
       "      <td>27159</td>\n",
       "      <td>26973</td>\n",
       "      <td>26815</td>\n",
       "      <td>26489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>44</td>\n",
       "      <td>266</td>\n",
       "      <td>245</td>\n",
       "      <td>259</td>\n",
       "      <td>247</td>\n",
       "      <td>253</td>\n",
       "      <td>22861</td>\n",
       "      <td>22733</td>\n",
       "      <td>22642</td>\n",
       "      <td>22512</td>\n",
       "      <td>22549</td>\n",
       "      <td>22583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>183</td>\n",
       "      <td>744</td>\n",
       "      <td>710</td>\n",
       "      <td>646</td>\n",
       "      <td>618</td>\n",
       "      <td>603</td>\n",
       "      <td>57373</td>\n",
       "      <td>57711</td>\n",
       "      <td>57776</td>\n",
       "      <td>57734</td>\n",
       "      <td>57658</td>\n",
       "      <td>57673</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    STNAME         CTYNAME  BIRTHS2010  BIRTHS2011  BIRTHS2012  BIRTHS2013  \\\n",
       "1  Alabama  Autauga County         151         636         615         574   \n",
       "2  Alabama  Baldwin County         517        2187        2092        2160   \n",
       "3  Alabama  Barbour County          70         335         300         283   \n",
       "4  Alabama     Bibb County          44         266         245         259   \n",
       "5  Alabama   Blount County         183         744         710         646   \n",
       "\n",
       "   BIRTHS2014  BIRTHS2015  POPESTIMATE2010  POPESTIMATE2011  POPESTIMATE2012  \\\n",
       "1         623         600            54660            55253            55175   \n",
       "2        2186        2240           183193           186659           190396   \n",
       "3         260         269            27341            27226            27159   \n",
       "4         247         253            22861            22733            22642   \n",
       "5         618         603            57373            57711            57776   \n",
       "\n",
       "   POPESTIMATE2013  POPESTIMATE2014  POPESTIMATE2015  \n",
       "1            55038            55290            55347  \n",
       "2           195126           199713           203709  \n",
       "3            26973            26815            26489  \n",
       "4            22512            22549            22583  \n",
       "5            57734            57658            57673  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Also while this data set is interesting for a number of different reasons,\n",
    "# let's reduce the data that we're going to look at to just the total population \n",
    "# estimates and the total number of births. We can do this by creating \n",
    "# a list of column names that we want to keep then project those and \n",
    "# assign the resulting DataFrame to our df variable.\n",
    "\n",
    "columns_to_keep = ['STNAME','CTYNAME','BIRTHS2010','BIRTHS2011','BIRTHS2012','BIRTHS2013',\n",
    "                   'BIRTHS2014','BIRTHS2015','POPESTIMATE2010','POPESTIMATE2011',\n",
    "                   'POPESTIMATE2012','POPESTIMATE2013','POPESTIMATE2014','POPESTIMATE2015']\n",
    "df = df[columns_to_keep]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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></th>\n",
       "      <th>BIRTHS2010</th>\n",
       "      <th>BIRTHS2011</th>\n",
       "      <th>BIRTHS2012</th>\n",
       "      <th>BIRTHS2013</th>\n",
       "      <th>BIRTHS2014</th>\n",
       "      <th>BIRTHS2015</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</th>\n",
       "      <th>POPESTIMATE2013</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "      <th>POPESTIMATE2015</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</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 rowspan=\"5\" valign=\"top\">Alabama</th>\n",
       "      <th>Autauga County</th>\n",
       "      <td>151</td>\n",
       "      <td>636</td>\n",
       "      <td>615</td>\n",
       "      <td>574</td>\n",
       "      <td>623</td>\n",
       "      <td>600</td>\n",
       "      <td>54660</td>\n",
       "      <td>55253</td>\n",
       "      <td>55175</td>\n",
       "      <td>55038</td>\n",
       "      <td>55290</td>\n",
       "      <td>55347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Baldwin County</th>\n",
       "      <td>517</td>\n",
       "      <td>2187</td>\n",
       "      <td>2092</td>\n",
       "      <td>2160</td>\n",
       "      <td>2186</td>\n",
       "      <td>2240</td>\n",
       "      <td>183193</td>\n",
       "      <td>186659</td>\n",
       "      <td>190396</td>\n",
       "      <td>195126</td>\n",
       "      <td>199713</td>\n",
       "      <td>203709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barbour County</th>\n",
       "      <td>70</td>\n",
       "      <td>335</td>\n",
       "      <td>300</td>\n",
       "      <td>283</td>\n",
       "      <td>260</td>\n",
       "      <td>269</td>\n",
       "      <td>27341</td>\n",
       "      <td>27226</td>\n",
       "      <td>27159</td>\n",
       "      <td>26973</td>\n",
       "      <td>26815</td>\n",
       "      <td>26489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bibb County</th>\n",
       "      <td>44</td>\n",
       "      <td>266</td>\n",
       "      <td>245</td>\n",
       "      <td>259</td>\n",
       "      <td>247</td>\n",
       "      <td>253</td>\n",
       "      <td>22861</td>\n",
       "      <td>22733</td>\n",
       "      <td>22642</td>\n",
       "      <td>22512</td>\n",
       "      <td>22549</td>\n",
       "      <td>22583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blount County</th>\n",
       "      <td>183</td>\n",
       "      <td>744</td>\n",
       "      <td>710</td>\n",
       "      <td>646</td>\n",
       "      <td>618</td>\n",
       "      <td>603</td>\n",
       "      <td>57373</td>\n",
       "      <td>57711</td>\n",
       "      <td>57776</td>\n",
       "      <td>57734</td>\n",
       "      <td>57658</td>\n",
       "      <td>57673</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        BIRTHS2010  BIRTHS2011  BIRTHS2012  BIRTHS2013  \\\n",
       "STNAME  CTYNAME                                                          \n",
       "Alabama Autauga County         151         636         615         574   \n",
       "        Baldwin County         517        2187        2092        2160   \n",
       "        Barbour County          70         335         300         283   \n",
       "        Bibb County             44         266         245         259   \n",
       "        Blount County          183         744         710         646   \n",
       "\n",
       "                        BIRTHS2014  BIRTHS2015  POPESTIMATE2010  \\\n",
       "STNAME  CTYNAME                                                   \n",
       "Alabama Autauga County         623         600            54660   \n",
       "        Baldwin County        2186        2240           183193   \n",
       "        Barbour County         260         269            27341   \n",
       "        Bibb County            247         253            22861   \n",
       "        Blount County          618         603            57373   \n",
       "\n",
       "                        POPESTIMATE2011  POPESTIMATE2012  POPESTIMATE2013  \\\n",
       "STNAME  CTYNAME                                                             \n",
       "Alabama Autauga County            55253            55175            55038   \n",
       "        Baldwin County           186659           190396           195126   \n",
       "        Barbour County            27226            27159            26973   \n",
       "        Bibb County               22733            22642            22512   \n",
       "        Blount County             57711            57776            57734   \n",
       "\n",
       "                        POPESTIMATE2014  POPESTIMATE2015  \n",
       "STNAME  CTYNAME                                           \n",
       "Alabama Autauga County            55290            55347  \n",
       "        Baldwin County           199713           203709  \n",
       "        Barbour County            26815            26489  \n",
       "        Bibb County               22549            22583  \n",
       "        Blount County             57658            57673  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The US Census data breaks down population estimates by state and county. We can load the data and \n",
    "# set the index to be a combination of the state and county values and see how pandas handles it in \n",
    "# a DataFrame. We do this by creating a list of the column identifiers we want to have indexed. And then \n",
    "# calling set index with this list and assigning the output as appropriate. We see here that we have \n",
    "# a dual index, first the state name and second the county name.\n",
    "\n",
    "df = df.set_index(['STNAME', 'CTYNAME'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BIRTHS2010            977\n",
       "BIRTHS2011           3826\n",
       "BIRTHS2012           3780\n",
       "BIRTHS2013           3662\n",
       "BIRTHS2014           3683\n",
       "BIRTHS2015           3709\n",
       "POPESTIMATE2010    345563\n",
       "POPESTIMATE2011    349048\n",
       "POPESTIMATE2012    351213\n",
       "POPESTIMATE2013    354289\n",
       "POPESTIMATE2014    357029\n",
       "POPESTIMATE2015    358880\n",
       "Name: (Michigan, Washtenaw County), dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# An immediate question which comes up is how we can query this DataFrame. We saw previously that \n",
    "# the loc attribute of the DataFrame can take multiple arguments. And it could query both the \n",
    "# row and the columns. When you use a MultiIndex, you must provide the arguments in order by the \n",
    "# level you wish to query. Inside of the index, each column is called a level and the outermost \n",
    "# column is level zero. \n",
    "\n",
    "# If we want to see the population results from Washtenaw County in Michigan the state, which is \n",
    "# where I live, the first argument would be Michigan and the second would be Washtenaw County\n",
    "df.loc['Michigan', 'Washtenaw County']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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></th>\n",
       "      <th>BIRTHS2010</th>\n",
       "      <th>BIRTHS2011</th>\n",
       "      <th>BIRTHS2012</th>\n",
       "      <th>BIRTHS2013</th>\n",
       "      <th>BIRTHS2014</th>\n",
       "      <th>BIRTHS2015</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</th>\n",
       "      <th>POPESTIMATE2013</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "      <th>POPESTIMATE2015</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</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 rowspan=\"2\" valign=\"top\">Michigan</th>\n",
       "      <th>Washtenaw County</th>\n",
       "      <td>977</td>\n",
       "      <td>3826</td>\n",
       "      <td>3780</td>\n",
       "      <td>3662</td>\n",
       "      <td>3683</td>\n",
       "      <td>3709</td>\n",
       "      <td>345563</td>\n",
       "      <td>349048</td>\n",
       "      <td>351213</td>\n",
       "      <td>354289</td>\n",
       "      <td>357029</td>\n",
       "      <td>358880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wayne County</th>\n",
       "      <td>5918</td>\n",
       "      <td>23819</td>\n",
       "      <td>23270</td>\n",
       "      <td>23377</td>\n",
       "      <td>23607</td>\n",
       "      <td>23586</td>\n",
       "      <td>1815199</td>\n",
       "      <td>1801273</td>\n",
       "      <td>1792514</td>\n",
       "      <td>1775713</td>\n",
       "      <td>1766008</td>\n",
       "      <td>1759335</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           BIRTHS2010  BIRTHS2011  BIRTHS2012  BIRTHS2013  \\\n",
       "STNAME   CTYNAME                                                            \n",
       "Michigan Washtenaw County         977        3826        3780        3662   \n",
       "         Wayne County            5918       23819       23270       23377   \n",
       "\n",
       "                           BIRTHS2014  BIRTHS2015  POPESTIMATE2010  \\\n",
       "STNAME   CTYNAME                                                     \n",
       "Michigan Washtenaw County        3683        3709           345563   \n",
       "         Wayne County           23607       23586          1815199   \n",
       "\n",
       "                           POPESTIMATE2011  POPESTIMATE2012  POPESTIMATE2013  \\\n",
       "STNAME   CTYNAME                                                               \n",
       "Michigan Washtenaw County           349048           351213           354289   \n",
       "         Wayne County              1801273          1792514          1775713   \n",
       "\n",
       "                           POPESTIMATE2014  POPESTIMATE2015  \n",
       "STNAME   CTYNAME                                             \n",
       "Michigan Washtenaw County           357029           358880  \n",
       "         Wayne County              1766008          1759335  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If you are interested in comparing two counties, for example, Washtenaw and Wayne County, we can \n",
    "# pass a list of tuples describing the indices we wish to query into loc. Since we have a MultiIndex \n",
    "# of two values, the state and the county, we need to provide two values as each element of our \n",
    "# filtering list. Each tuple should have two elements, the first element being the first index and \n",
    "# the second element being the second index.\n",
    "\n",
    "# Therefore, in this case, we will have a list of two tuples, in each tuple, the first element is \n",
    "# Michigan, and the second element is either Washtenaw County or Wayne County\n",
    "\n",
    "df.loc[ [('Michigan', 'Washtenaw County'),\n",
    "         ('Michigan', 'Wayne County')] ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay so that's how hierarchical indices work in a nutshell. They're a special part of the pandas library which I \n",
    "think can make management and reasoning about data easier. Of course hierarchical labeling isn't just for rows. \n",
    "For example, you can transpose this matrix and now have hierarchical column labels. And projecting a single \n",
    "column which has these labels works exactly the way you would expect it to. Now, in reality, I don't tend to use \n",
    "hierarchical indicies very much, and instead just keep everything as columns and manipulate those. But, it's a \n",
    "unique and sophisticated aspect of pandas that is useful to know, especially if viewing your data in a tabular \n",
    "form."
   ]
  }
 ],
 "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
}