190925_querying_dataframes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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": [
    "import pandas as pd\n",
    "# Then we'll load in our CSV file\n",
    "df = pd.read_csv('datasets/Admission_Predict.csv', index_col=0)\n",
    "# And we'll clean up a couple of poorly named columns like we did in a previous lecture\n",
    "df.columns = [x.lower().strip() for x in df.columns]\n",
    "# And we'll take a look at the results\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": true,
    "hidePrompt": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* querying dataframes is all about boolean masking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Serial No.\n",
       "1       True\n",
       "2       True\n",
       "3       True\n",
       "4       True\n",
       "5      False\n",
       "       ...  \n",
       "396     True\n",
       "397     True\n",
       "398     True\n",
       "399    False\n",
       "400     True\n",
       "Name: chance of admit, Length: 400, dtype: bool"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "admit_mask=df['chance of admit'] > 0.7\n",
    "admit_mask"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": true,
    "hidePrompt": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* we can apply a mask in a couple of ways"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>6</th>\n",
       "      <td>330</td>\n",
       "      <td>115</td>\n",
       "      <td>5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.34</td>\n",
       "      <td>1</td>\n",
       "      <td>0.90</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",
       "6                 330          115                  5  4.5  3.0  9.34   \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",
       "6                  1             0.90  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[admit_mask].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": true,
    "hidePrompt": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* of course, you don't have to make the mask object (and likely won't)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>6</th>\n",
       "      <td>330</td>\n",
       "      <td>115</td>\n",
       "      <td>5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.34</td>\n",
       "      <td>1</td>\n",
       "      <td>0.90</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",
       "6                 330          115                  5  4.5  3.0  9.34   \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",
       "6                  1             0.90  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['chance of admit'] > 0.7].head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": true,
    "hidePrompt": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* we can also use the `where()` function, a subtle issue is that NaN's are left in for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>9.65</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>324.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.87</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>316.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>322.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>8.67</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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.0        118.0                4.0  4.5  4.5  9.65   \n",
       "2               324.0        107.0                4.0  4.0  4.5  8.87   \n",
       "3               316.0        104.0                3.0  3.0  3.5  8.00   \n",
       "4               322.0        110.0                3.0  3.5  2.5  8.67   \n",
       "5                 NaN          NaN                NaN  NaN  NaN   NaN   \n",
       "\n",
       "            research  chance of admit  \n",
       "Serial No.                             \n",
       "1                1.0             0.92  \n",
       "2                1.0             0.76  \n",
       "3                1.0             0.72  \n",
       "4                1.0             0.80  \n",
       "5                NaN              NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.where(admit_mask).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": true,
    "hidePrompt": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* the nice thing about `where()` is that it's easy to read\n",
    "* often you mix it together with `dropna()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.5</td>\n",
       "      <td>9.65</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>324.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.87</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>316.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>322.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>8.67</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>330.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.34</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.90</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.0        118.0                4.0  4.5  4.5  9.65   \n",
       "2               324.0        107.0                4.0  4.0  4.5  8.87   \n",
       "3               316.0        104.0                3.0  3.0  3.5  8.00   \n",
       "4               322.0        110.0                3.0  3.5  2.5  8.67   \n",
       "6               330.0        115.0                5.0  4.5  3.0  9.34   \n",
       "\n",
       "            research  chance of admit  \n",
       "Serial No.                             \n",
       "1                1.0             0.92  \n",
       "2                1.0             0.76  \n",
       "3                1.0             0.72  \n",
       "4                1.0             0.80  \n",
       "6                1.0             0.90  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.where(admit_mask).dropna().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": true,
    "hidePrompt": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* masks can be composites, and made up of several conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-7-cab0a6fdc0e7>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;34m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'chance of admit'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m0.7\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'chance of admit'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m0.9\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m__nonzero__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1554\u001b[0m             \u001b[0;34m\"The truth value of a {0} is ambiguous. \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1555\u001b[0m             \"Use a.empty, a.bool(), a.item(), a.any() or a.all().\".format(\n\u001b[0;32m-> 1556\u001b[0;31m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__class__\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__name__\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1557\u001b[0m             )\n\u001b[1;32m   1558\u001b[0m         )\n",
      "\u001b[0;31mValueError\u001b[0m: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()."
     ]
    }
   ],
   "source": [
    "(df['chance of admit'] > 0.7) and (df['chance of admit'] < 0.9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": true,
    "hidePrompt": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* the problem is, pandas doesn't know how to `and` two `Series` objects together. But it does know how to `&` them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "(df['chance of admit'] > 0.7) & (df['chance of admit'] < 0.9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* But, you need to watch out for order of operations!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df[df['chance of admit'] > 0.7 & df['chance of admit'] < 0.9]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* finally, there are additional helped functions on dataframes to be aware of"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.where(df['chance of admit'].gt(0.7).lt(0.9)).dropna().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Indexing\n",
    "* Let's go back to indexing dataframes, there's some neat stuff there\n",
    "* Remember that the index are row level labels, and the column names are the column level labels\n",
    "* We can swap columns and rows trivially"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* we saw that we can set the index with `set_index()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.set_index('lor').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# of course, this didn't actually change our previous dataframe, right?\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Multilevel indexing\n",
    "* we can have heirarchical indicies, which is pretty cool\n",
    "* let's look at some census data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "df=pd.read_csv(\"datasets/census.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n"
     ]
    },
    {
     "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# in this data there are only two sumlevels\n",
    "print(len(df['SUMLEV'].unique()))\n",
    "# so lets just get rid of anything except state level data\n",
    "df=df[df['SUMLEV'] == 50]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>SUMLEV</th>\n",
       "      <th>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</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",
       "    <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",
       "      <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>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>54571</td>\n",
       "      <td>54571</td>\n",
       "      <td>54660</td>\n",
       "      <td>55253</td>\n",
       "      <td>55175</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>Baldwin County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>182265</td>\n",
       "      <td>182265</td>\n",
       "      <td>183193</td>\n",
       "      <td>186659</td>\n",
       "      <td>190396</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>Barbour County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>27457</td>\n",
       "      <td>27457</td>\n",
       "      <td>27341</td>\n",
       "      <td>27226</td>\n",
       "      <td>27159</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>Bibb County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>22915</td>\n",
       "      <td>22919</td>\n",
       "      <td>22861</td>\n",
       "      <td>22733</td>\n",
       "      <td>22642</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>Blount County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>57322</td>\n",
       "      <td>57322</td>\n",
       "      <td>57373</td>\n",
       "      <td>57711</td>\n",
       "      <td>57776</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 × 98 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        SUMLEV  REGION  DIVISION  STATE  COUNTY  \\\n",
       "STNAME  CTYNAME                                                   \n",
       "Alabama Autauga County      50       3         6      1       1   \n",
       "        Baldwin County      50       3         6      1       3   \n",
       "        Barbour County      50       3         6      1       5   \n",
       "        Bibb County         50       3         6      1       7   \n",
       "        Blount County       50       3         6      1       9   \n",
       "\n",
       "                        CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  \\\n",
       "STNAME  CTYNAME                                                             \n",
       "Alabama Autauga County          54571              54571            54660   \n",
       "        Baldwin County         182265             182265           183193   \n",
       "        Barbour County          27457              27457            27341   \n",
       "        Bibb County             22915              22919            22861   \n",
       "        Blount County           57322              57322            57373   \n",
       "\n",
       "                        POPESTIMATE2011  POPESTIMATE2012  ...  \\\n",
       "STNAME  CTYNAME                                           ...   \n",
       "Alabama Autauga County            55253            55175  ...   \n",
       "        Baldwin County           186659           190396  ...   \n",
       "        Barbour County            27226            27159  ...   \n",
       "        Bibb County               22733            22642  ...   \n",
       "        Blount County             57711            57776  ...   \n",
       "\n",
       "                        RDOMESTICMIG2011  RDOMESTICMIG2012  RDOMESTICMIG2013  \\\n",
       "STNAME  CTYNAME                                                                \n",
       "Alabama Autauga County          7.242091         -2.915927         -3.012349   \n",
       "        Baldwin County         14.832960         17.647293         21.845705   \n",
       "        Barbour County         -4.728132         -2.500690         -7.056824   \n",
       "        Bibb County            -5.527043         -5.068871         -6.201001   \n",
       "        Blount County           1.807375         -1.177622         -1.748766   \n",
       "\n",
       "                        RDOMESTICMIG2014  RDOMESTICMIG2015  RNETMIG2011  \\\n",
       "STNAME  CTYNAME                                                           \n",
       "Alabama Autauga County          2.265971         -2.530799     7.606016   \n",
       "        Baldwin County         19.243287         17.197872    15.844176   \n",
       "        Barbour County         -3.904217        -10.543299    -4.874741   \n",
       "        Bibb County            -0.177537          0.177258    -5.088389   \n",
       "        Blount County          -2.062535         -1.369970     1.859511   \n",
       "\n",
       "                        RNETMIG2012  RNETMIG2013  RNETMIG2014  RNETMIG2015  \n",
       "STNAME  CTYNAME                                                             \n",
       "Alabama Autauga County    -2.626146    -2.722002     2.592270    -2.187333  \n",
       "        Baldwin County    18.559627    22.727626    20.317142    18.293499  \n",
       "        Barbour County    -2.758113    -7.167664    -3.978583   -10.543299  \n",
       "        Bibb County       -4.363636    -5.403729     0.754533     1.107861  \n",
       "        Blount County     -0.848580    -1.402476    -1.577232    -0.884411  \n",
       "\n",
       "[5 rows x 98 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can set a multilevel index just by passing a list of things we want to index on\n",
    "df=df.set_index(['STNAME', 'CTYNAME'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* Querying gets, frankly, complex\n",
    "* Someone remind me, how does df.loc work again?\n",
    "  * df.loc[X,Y]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "* `df.loc[row, column]`\n",
    "* But with a multiindex we can do\n",
    "  * `df.loc[row index1, row index2]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "df.loc['Michigan', 'Washtenaw County']['REGION']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.loc['Michigan', 'REGION']['Washtenaw County']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# It's a bit of ambiguous, I recommend passing keys as tuple instead\n",
    "df.loc[('Michigan', 'Washtenaw County')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.loc[['Michigan', 'Washtenaw County']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# In class activity time!\n",
    "## Question 0: Which county has the largest population in Michigan?\n",
    "* (Easy)\n",
    "\n",
    "## Question 1: Generate a new column for which is the largest absolute change in population within the period 2010-2015?\n",
    "* (Hint: population values are stored in columns POPESTIMATE2010 through POPESTIMATE2015, you need to consider all six columns.)"
   ]
  },
  {
   "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></th>\n",
       "      <th>SUMLEV</th>\n",
       "      <th>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</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",
       "    <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",
       "      <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>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>54571</td>\n",
       "      <td>54571</td>\n",
       "      <td>54660</td>\n",
       "      <td>55253</td>\n",
       "      <td>55175</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>Baldwin County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>182265</td>\n",
       "      <td>182265</td>\n",
       "      <td>183193</td>\n",
       "      <td>186659</td>\n",
       "      <td>190396</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>Barbour County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>27457</td>\n",
       "      <td>27457</td>\n",
       "      <td>27341</td>\n",
       "      <td>27226</td>\n",
       "      <td>27159</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>Bibb County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>22915</td>\n",
       "      <td>22919</td>\n",
       "      <td>22861</td>\n",
       "      <td>22733</td>\n",
       "      <td>22642</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>Blount County</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>57322</td>\n",
       "      <td>57322</td>\n",
       "      <td>57373</td>\n",
       "      <td>57711</td>\n",
       "      <td>57776</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 × 98 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        SUMLEV  REGION  DIVISION  STATE  COUNTY  \\\n",
       "STNAME  CTYNAME                                                   \n",
       "Alabama Autauga County      50       3         6      1       1   \n",
       "        Baldwin County      50       3         6      1       3   \n",
       "        Barbour County      50       3         6      1       5   \n",
       "        Bibb County         50       3         6      1       7   \n",
       "        Blount County       50       3         6      1       9   \n",
       "\n",
       "                        CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  \\\n",
       "STNAME  CTYNAME                                                             \n",
       "Alabama Autauga County          54571              54571            54660   \n",
       "        Baldwin County         182265             182265           183193   \n",
       "        Barbour County          27457              27457            27341   \n",
       "        Bibb County             22915              22919            22861   \n",
       "        Blount County           57322              57322            57373   \n",
       "\n",
       "                        POPESTIMATE2011  POPESTIMATE2012  ...  \\\n",
       "STNAME  CTYNAME                                           ...   \n",
       "Alabama Autauga County            55253            55175  ...   \n",
       "        Baldwin County           186659           190396  ...   \n",
       "        Barbour County            27226            27159  ...   \n",
       "        Bibb County               22733            22642  ...   \n",
       "        Blount County             57711            57776  ...   \n",
       "\n",
       "                        RDOMESTICMIG2011  RDOMESTICMIG2012  RDOMESTICMIG2013  \\\n",
       "STNAME  CTYNAME                                                                \n",
       "Alabama Autauga County          7.242091         -2.915927         -3.012349   \n",
       "        Baldwin County         14.832960         17.647293         21.845705   \n",
       "        Barbour County         -4.728132         -2.500690         -7.056824   \n",
       "        Bibb County            -5.527043         -5.068871         -6.201001   \n",
       "        Blount County           1.807375         -1.177622         -1.748766   \n",
       "\n",
       "                        RDOMESTICMIG2014  RDOMESTICMIG2015  RNETMIG2011  \\\n",
       "STNAME  CTYNAME                                                           \n",
       "Alabama Autauga County          2.265971         -2.530799     7.606016   \n",
       "        Baldwin County         19.243287         17.197872    15.844176   \n",
       "        Barbour County         -3.904217        -10.543299    -4.874741   \n",
       "        Bibb County            -0.177537          0.177258    -5.088389   \n",
       "        Blount County          -2.062535         -1.369970     1.859511   \n",
       "\n",
       "                        RNETMIG2012  RNETMIG2013  RNETMIG2014  RNETMIG2015  \n",
       "STNAME  CTYNAME                                                             \n",
       "Alabama Autauga County    -2.626146    -2.722002     2.592270    -2.187333  \n",
       "        Baldwin County    18.559627    22.727626    20.317142    18.293499  \n",
       "        Barbour County    -2.758113    -7.167664    -3.978583   -10.543299  \n",
       "        Bibb County       -4.363636    -5.403729     0.754533     1.107861  \n",
       "        Blount County     -0.848580    -1.402476    -1.577232    -0.884411  \n",
       "\n",
       "[5 rows x 98 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Wayne County'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['Michigan']['CENSUS2010POP'].sort_values().index[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CTYNAME\n",
       "Keweenaw County          2156\n",
       "Luce County              6631\n",
       "Ontonagon County         6780\n",
       "Schoolcraft County       8485\n",
       "Oscoda County            8640\n",
       "                       ...   \n",
       "Genesee County         425790\n",
       "Kent County            602622\n",
       "Macomb County          840978\n",
       "Oakland County        1202362\n",
       "Wayne County          1820584\n",
       "Name: CENSUS2010POP, Length: 83, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['Michigan']['CENSUS2010POP'].sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "55347\n"
     ]
    }
   ],
   "source": [
    "print(new_df.iloc[0,-2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  if __name__ == '__main__':\n",
      "/opt/conda/lib/python3.7/site-packages/pandas/core/indexing.py:480: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self.obj[item] = s\n"
     ]
    },
    {
     "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>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</th>\n",
       "      <th>POPESTIMATE2013</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "      <th>POPESTIMATE2015</th>\n",
       "      <th>abs</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Alabama</th>\n",
       "      <th>Autauga County</th>\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",
       "      <td>687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Baldwin County</th>\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",
       "      <td>20516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barbour County</th>\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",
       "      <td>852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bibb County</th>\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",
       "      <td>349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blount County</th>\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",
       "      <td>403</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        POPESTIMATE2010  POPESTIMATE2011  POPESTIMATE2012  \\\n",
       "STNAME  CTYNAME                                                             \n",
       "Alabama Autauga County            54660            55253            55175   \n",
       "        Baldwin County           183193           186659           190396   \n",
       "        Barbour County            27341            27226            27159   \n",
       "        Bibb County               22861            22733            22642   \n",
       "        Blount County             57373            57711            57776   \n",
       "\n",
       "                        POPESTIMATE2013  POPESTIMATE2014  POPESTIMATE2015  \\\n",
       "STNAME  CTYNAME                                                             \n",
       "Alabama Autauga County            55038            55290            55347   \n",
       "        Baldwin County           195126           199713           203709   \n",
       "        Barbour County            26973            26815            26489   \n",
       "        Bibb County               22512            22549            22583   \n",
       "        Blount County             57734            57658            57673   \n",
       "\n",
       "                          abs  \n",
       "STNAME  CTYNAME                \n",
       "Alabama Autauga County    687  \n",
       "        Baldwin County  20516  \n",
       "        Barbour County    852  \n",
       "        Bibb County       349  \n",
       "        Blount County     403  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "popcol=[\"POPESTIMATE2010\",\n",
    "        \"POPESTIMATE2011\",\n",
    "        \"POPESTIMATE2012\",\n",
    "        \"POPESTIMATE2013\",\n",
    "        \"POPESTIMATE2014\",\n",
    "        \"POPESTIMATE2015\"]\n",
    "new_df=df[popcol]\n",
    "new_df[\"abs\"]=None\n",
    "for i in range(0,len(new_df)):\n",
    "    new_df.iloc[i,-1]=np.max(new_df.iloc[i][popcol]) - np.min(new_df.iloc[i][popcol]) \n",
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "52\n"
     ]
    }
   ],
   "source": [
    "df.columns\n",
    "i=0\n",
    "for j in df.columns:\n",
    "    if j == 'NETMIG2013':\n",
    "        break\n",
    "    i=i+1\n",
    "print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "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>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</th>\n",
       "      <th>POPESTIMATE2013</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "      <th>POPESTIMATE2015</th>\n",
       "      <th>abs</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"30\" valign=\"top\">Alabama</th>\n",
       "      <th>Autauga County</th>\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",
       "      <td>687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Baldwin County</th>\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",
       "      <td>20516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barbour County</th>\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",
       "      <td>852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bibb County</th>\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",
       "      <td>349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blount County</th>\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",
       "      <td>403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bullock County</th>\n",
       "      <td>10887</td>\n",
       "      <td>10629</td>\n",
       "      <td>10606</td>\n",
       "      <td>10628</td>\n",
       "      <td>10829</td>\n",
       "      <td>10696</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Butler County</th>\n",
       "      <td>20944</td>\n",
       "      <td>20673</td>\n",
       "      <td>20408</td>\n",
       "      <td>20261</td>\n",
       "      <td>20276</td>\n",
       "      <td>20154</td>\n",
       "      <td>790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Calhoun County</th>\n",
       "      <td>118437</td>\n",
       "      <td>117768</td>\n",
       "      <td>117286</td>\n",
       "      <td>116575</td>\n",
       "      <td>115993</td>\n",
       "      <td>115620</td>\n",
       "      <td>2817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chambers County</th>\n",
       "      <td>34098</td>\n",
       "      <td>33993</td>\n",
       "      <td>34075</td>\n",
       "      <td>34153</td>\n",
       "      <td>34052</td>\n",
       "      <td>34123</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cherokee County</th>\n",
       "      <td>25976</td>\n",
       "      <td>26080</td>\n",
       "      <td>26023</td>\n",
       "      <td>26084</td>\n",
       "      <td>25995</td>\n",
       "      <td>25859</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chilton County</th>\n",
       "      <td>43665</td>\n",
       "      <td>43739</td>\n",
       "      <td>43697</td>\n",
       "      <td>43795</td>\n",
       "      <td>43921</td>\n",
       "      <td>43943</td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Choctaw County</th>\n",
       "      <td>13841</td>\n",
       "      <td>13593</td>\n",
       "      <td>13543</td>\n",
       "      <td>13378</td>\n",
       "      <td>13289</td>\n",
       "      <td>13170</td>\n",
       "      <td>671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Clarke County</th>\n",
       "      <td>25767</td>\n",
       "      <td>25570</td>\n",
       "      <td>25144</td>\n",
       "      <td>25116</td>\n",
       "      <td>24847</td>\n",
       "      <td>24675</td>\n",
       "      <td>1092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Clay County</th>\n",
       "      <td>13880</td>\n",
       "      <td>13670</td>\n",
       "      <td>13456</td>\n",
       "      <td>13467</td>\n",
       "      <td>13538</td>\n",
       "      <td>13555</td>\n",
       "      <td>424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cleburne County</th>\n",
       "      <td>14973</td>\n",
       "      <td>14971</td>\n",
       "      <td>14921</td>\n",
       "      <td>15028</td>\n",
       "      <td>15072</td>\n",
       "      <td>15018</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coffee County</th>\n",
       "      <td>50177</td>\n",
       "      <td>50448</td>\n",
       "      <td>51173</td>\n",
       "      <td>50755</td>\n",
       "      <td>50831</td>\n",
       "      <td>51211</td>\n",
       "      <td>1034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colbert County</th>\n",
       "      <td>54514</td>\n",
       "      <td>54443</td>\n",
       "      <td>54472</td>\n",
       "      <td>54471</td>\n",
       "      <td>54480</td>\n",
       "      <td>54354</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Conecuh County</th>\n",
       "      <td>13208</td>\n",
       "      <td>13121</td>\n",
       "      <td>12996</td>\n",
       "      <td>12875</td>\n",
       "      <td>12662</td>\n",
       "      <td>12672</td>\n",
       "      <td>546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coosa County</th>\n",
       "      <td>11758</td>\n",
       "      <td>11348</td>\n",
       "      <td>11195</td>\n",
       "      <td>11059</td>\n",
       "      <td>10807</td>\n",
       "      <td>10724</td>\n",
       "      <td>1034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Covington County</th>\n",
       "      <td>37796</td>\n",
       "      <td>38060</td>\n",
       "      <td>37818</td>\n",
       "      <td>37830</td>\n",
       "      <td>37888</td>\n",
       "      <td>37835</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Crenshaw County</th>\n",
       "      <td>13853</td>\n",
       "      <td>13896</td>\n",
       "      <td>13951</td>\n",
       "      <td>13932</td>\n",
       "      <td>13948</td>\n",
       "      <td>13963</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cullman County</th>\n",
       "      <td>80473</td>\n",
       "      <td>80469</td>\n",
       "      <td>80374</td>\n",
       "      <td>80756</td>\n",
       "      <td>81221</td>\n",
       "      <td>82005</td>\n",
       "      <td>1631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dale County</th>\n",
       "      <td>50358</td>\n",
       "      <td>50109</td>\n",
       "      <td>50324</td>\n",
       "      <td>49833</td>\n",
       "      <td>49501</td>\n",
       "      <td>49565</td>\n",
       "      <td>857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dallas County</th>\n",
       "      <td>43803</td>\n",
       "      <td>43178</td>\n",
       "      <td>42777</td>\n",
       "      <td>42021</td>\n",
       "      <td>41662</td>\n",
       "      <td>41131</td>\n",
       "      <td>2672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DeKalb County</th>\n",
       "      <td>71142</td>\n",
       "      <td>71387</td>\n",
       "      <td>70942</td>\n",
       "      <td>70869</td>\n",
       "      <td>71012</td>\n",
       "      <td>71130</td>\n",
       "      <td>518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Elmore County</th>\n",
       "      <td>79465</td>\n",
       "      <td>80012</td>\n",
       "      <td>80432</td>\n",
       "      <td>80883</td>\n",
       "      <td>81022</td>\n",
       "      <td>81468</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Escambia County</th>\n",
       "      <td>38309</td>\n",
       "      <td>38213</td>\n",
       "      <td>38034</td>\n",
       "      <td>37857</td>\n",
       "      <td>37784</td>\n",
       "      <td>37789</td>\n",
       "      <td>525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Etowah County</th>\n",
       "      <td>104442</td>\n",
       "      <td>104236</td>\n",
       "      <td>104235</td>\n",
       "      <td>103852</td>\n",
       "      <td>103452</td>\n",
       "      <td>103057</td>\n",
       "      <td>1385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fayette County</th>\n",
       "      <td>17231</td>\n",
       "      <td>17062</td>\n",
       "      <td>16960</td>\n",
       "      <td>16857</td>\n",
       "      <td>16842</td>\n",
       "      <td>16759</td>\n",
       "      <td>472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Franklin County</th>\n",
       "      <td>31734</td>\n",
       "      <td>31729</td>\n",
       "      <td>31648</td>\n",
       "      <td>31507</td>\n",
       "      <td>31592</td>\n",
       "      <td>31696</td>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">Wisconsin</th>\n",
       "      <th>Washburn County</th>\n",
       "      <td>15930</td>\n",
       "      <td>15784</td>\n",
       "      <td>15831</td>\n",
       "      <td>15657</td>\n",
       "      <td>15678</td>\n",
       "      <td>15552</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington County</th>\n",
       "      <td>131967</td>\n",
       "      <td>132225</td>\n",
       "      <td>132649</td>\n",
       "      <td>132758</td>\n",
       "      <td>133301</td>\n",
       "      <td>133674</td>\n",
       "      <td>1707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Waukesha County</th>\n",
       "      <td>390076</td>\n",
       "      <td>390808</td>\n",
       "      <td>392710</td>\n",
       "      <td>394025</td>\n",
       "      <td>395335</td>\n",
       "      <td>396488</td>\n",
       "      <td>6412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Waupaca County</th>\n",
       "      <td>52422</td>\n",
       "      <td>52342</td>\n",
       "      <td>52035</td>\n",
       "      <td>52213</td>\n",
       "      <td>52088</td>\n",
       "      <td>51945</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Waushara County</th>\n",
       "      <td>24506</td>\n",
       "      <td>24581</td>\n",
       "      <td>24484</td>\n",
       "      <td>24332</td>\n",
       "      <td>24173</td>\n",
       "      <td>24033</td>\n",
       "      <td>548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Winnebago County</th>\n",
       "      <td>167059</td>\n",
       "      <td>167630</td>\n",
       "      <td>168717</td>\n",
       "      <td>169486</td>\n",
       "      <td>169639</td>\n",
       "      <td>169546</td>\n",
       "      <td>2580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wood County</th>\n",
       "      <td>74807</td>\n",
       "      <td>74647</td>\n",
       "      <td>74384</td>\n",
       "      <td>73996</td>\n",
       "      <td>73597</td>\n",
       "      <td>73435</td>\n",
       "      <td>1372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"23\" valign=\"top\">Wyoming</th>\n",
       "      <th>Albany County</th>\n",
       "      <td>36428</td>\n",
       "      <td>36908</td>\n",
       "      <td>37396</td>\n",
       "      <td>37647</td>\n",
       "      <td>37918</td>\n",
       "      <td>37956</td>\n",
       "      <td>1528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Big Horn County</th>\n",
       "      <td>11672</td>\n",
       "      <td>11745</td>\n",
       "      <td>11785</td>\n",
       "      <td>12002</td>\n",
       "      <td>11919</td>\n",
       "      <td>12022</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Campbell County</th>\n",
       "      <td>46244</td>\n",
       "      <td>46600</td>\n",
       "      <td>47881</td>\n",
       "      <td>48121</td>\n",
       "      <td>48243</td>\n",
       "      <td>49220</td>\n",
       "      <td>2976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Carbon County</th>\n",
       "      <td>15837</td>\n",
       "      <td>15817</td>\n",
       "      <td>15678</td>\n",
       "      <td>15787</td>\n",
       "      <td>15856</td>\n",
       "      <td>15559</td>\n",
       "      <td>297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Converse County</th>\n",
       "      <td>13826</td>\n",
       "      <td>13728</td>\n",
       "      <td>14025</td>\n",
       "      <td>14343</td>\n",
       "      <td>14172</td>\n",
       "      <td>14236</td>\n",
       "      <td>615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Crook County</th>\n",
       "      <td>7114</td>\n",
       "      <td>7129</td>\n",
       "      <td>7148</td>\n",
       "      <td>7160</td>\n",
       "      <td>7264</td>\n",
       "      <td>7444</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fremont County</th>\n",
       "      <td>40222</td>\n",
       "      <td>40591</td>\n",
       "      <td>41129</td>\n",
       "      <td>41024</td>\n",
       "      <td>40717</td>\n",
       "      <td>40315</td>\n",
       "      <td>907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Goshen County</th>\n",
       "      <td>13408</td>\n",
       "      <td>13597</td>\n",
       "      <td>13666</td>\n",
       "      <td>13565</td>\n",
       "      <td>13509</td>\n",
       "      <td>13383</td>\n",
       "      <td>283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hot Springs County</th>\n",
       "      <td>4813</td>\n",
       "      <td>4818</td>\n",
       "      <td>4846</td>\n",
       "      <td>4846</td>\n",
       "      <td>4793</td>\n",
       "      <td>4741</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson County</th>\n",
       "      <td>8581</td>\n",
       "      <td>8636</td>\n",
       "      <td>8610</td>\n",
       "      <td>8619</td>\n",
       "      <td>8552</td>\n",
       "      <td>8585</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Laramie County</th>\n",
       "      <td>92271</td>\n",
       "      <td>92663</td>\n",
       "      <td>94894</td>\n",
       "      <td>96006</td>\n",
       "      <td>96469</td>\n",
       "      <td>97121</td>\n",
       "      <td>4850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lincoln County</th>\n",
       "      <td>18091</td>\n",
       "      <td>18022</td>\n",
       "      <td>17943</td>\n",
       "      <td>18328</td>\n",
       "      <td>18564</td>\n",
       "      <td>18722</td>\n",
       "      <td>779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Natrona County</th>\n",
       "      <td>75472</td>\n",
       "      <td>76420</td>\n",
       "      <td>78699</td>\n",
       "      <td>81156</td>\n",
       "      <td>81603</td>\n",
       "      <td>82178</td>\n",
       "      <td>6706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Niobrara County</th>\n",
       "      <td>2492</td>\n",
       "      <td>2485</td>\n",
       "      <td>2475</td>\n",
       "      <td>2548</td>\n",
       "      <td>2530</td>\n",
       "      <td>2542</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Park County</th>\n",
       "      <td>28259</td>\n",
       "      <td>28473</td>\n",
       "      <td>28863</td>\n",
       "      <td>29237</td>\n",
       "      <td>29126</td>\n",
       "      <td>29228</td>\n",
       "      <td>978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Platte County</th>\n",
       "      <td>8678</td>\n",
       "      <td>8701</td>\n",
       "      <td>8732</td>\n",
       "      <td>8728</td>\n",
       "      <td>8776</td>\n",
       "      <td>8812</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sheridan County</th>\n",
       "      <td>29146</td>\n",
       "      <td>29275</td>\n",
       "      <td>29594</td>\n",
       "      <td>29794</td>\n",
       "      <td>30020</td>\n",
       "      <td>30009</td>\n",
       "      <td>874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sublette County</th>\n",
       "      <td>10244</td>\n",
       "      <td>10142</td>\n",
       "      <td>10418</td>\n",
       "      <td>10086</td>\n",
       "      <td>10039</td>\n",
       "      <td>9899</td>\n",
       "      <td>519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweetwater County</th>\n",
       "      <td>43593</td>\n",
       "      <td>44041</td>\n",
       "      <td>45104</td>\n",
       "      <td>45162</td>\n",
       "      <td>44925</td>\n",
       "      <td>44626</td>\n",
       "      <td>1569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Teton County</th>\n",
       "      <td>21297</td>\n",
       "      <td>21482</td>\n",
       "      <td>21697</td>\n",
       "      <td>22347</td>\n",
       "      <td>22905</td>\n",
       "      <td>23125</td>\n",
       "      <td>1828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uinta County</th>\n",
       "      <td>21102</td>\n",
       "      <td>20912</td>\n",
       "      <td>20989</td>\n",
       "      <td>21022</td>\n",
       "      <td>20903</td>\n",
       "      <td>20822</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washakie County</th>\n",
       "      <td>8545</td>\n",
       "      <td>8469</td>\n",
       "      <td>8443</td>\n",
       "      <td>8443</td>\n",
       "      <td>8316</td>\n",
       "      <td>8328</td>\n",
       "      <td>229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weston County</th>\n",
       "      <td>7181</td>\n",
       "      <td>7114</td>\n",
       "      <td>7065</td>\n",
       "      <td>7160</td>\n",
       "      <td>7185</td>\n",
       "      <td>7234</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3142 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           POPESTIMATE2010  POPESTIMATE2011  POPESTIMATE2012  \\\n",
       "STNAME  CTYNAME                                                                \n",
       "Alabama Autauga County               54660            55253            55175   \n",
       "        Baldwin County              183193           186659           190396   \n",
       "        Barbour County               27341            27226            27159   \n",
       "        Bibb County                  22861            22733            22642   \n",
       "        Blount County                57373            57711            57776   \n",
       "...                                    ...              ...              ...   \n",
       "Wyoming Sweetwater County            43593            44041            45104   \n",
       "        Teton County                 21297            21482            21697   \n",
       "        Uinta County                 21102            20912            20989   \n",
       "        Washakie County               8545             8469             8443   \n",
       "        Weston County                 7181             7114             7065   \n",
       "\n",
       "                           POPESTIMATE2013  POPESTIMATE2014  POPESTIMATE2015  \\\n",
       "STNAME  CTYNAME                                                                \n",
       "Alabama Autauga County               55038            55290            55347   \n",
       "        Baldwin County              195126           199713           203709   \n",
       "        Barbour County               26973            26815            26489   \n",
       "        Bibb County                  22512            22549            22583   \n",
       "        Blount County                57734            57658            57673   \n",
       "...                                    ...              ...              ...   \n",
       "Wyoming Sweetwater County            45162            44925            44626   \n",
       "        Teton County                 22347            22905            23125   \n",
       "        Uinta County                 21022            20903            20822   \n",
       "        Washakie County               8443             8316             8328   \n",
       "        Weston County                 7160             7185             7234   \n",
       "\n",
       "                             abs  \n",
       "STNAME  CTYNAME                   \n",
       "Alabama Autauga County       687  \n",
       "        Baldwin County     20516  \n",
       "        Barbour County       852  \n",
       "        Bibb County          349  \n",
       "        Blount County        403  \n",
       "...                          ...  \n",
       "Wyoming Sweetwater County   1569  \n",
       "        Teton County        1828  \n",
       "        Uinta County         280  \n",
       "        Washakie County      229  \n",
       "        Weston County        169  \n",
       "\n",
       "[3142 rows x 7 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "popcol=[\"POPESTIMATE2010\",\n",
    "        \"POPESTIMATE2011\",\n",
    "        \"POPESTIMATE2012\",\n",
    "        \"POPESTIMATE2013\",\n",
    "        \"POPESTIMATE2014\",\n",
    "        \"POPESTIMATE2015\"]\n",
    "new_df=df[popcol]\n",
    "new_df[\"abs\"]=None\n",
    "\n",
    "for i in range(0,len(new_df)):\n",
    "    new_df.iloc[i,-1]=np.max(new_df.iloc[i][popcol]) - np.min(new_df.iloc[i][popcol]) \n",
    "new_df"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "hide_code_all_hidden": true,
  "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
}