vaccination - clean data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "from pprint import pprint\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0_level_0</th>\n",
       "      <th>Influenza vaccination (General Population) - Age</th>\n",
       "      <th>Unnamed: 2_level_0</th>\n",
       "      <th>Unnamed: 3_level_0</th>\n",
       "      <th>Unnamed: 4_level_0</th>\n",
       "      <th>Unnamed: 5_level_0</th>\n",
       "      <th>Unnamed: 6_level_0</th>\n",
       "      <th>Unnamed: 7_level_0</th>\n",
       "      <th>Unnamed: 8_level_0</th>\n",
       "      <th>Unnamed: 9_level_0</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 927_level_0</th>\n",
       "      <th>Unnamed: 928_level_0</th>\n",
       "      <th>Unnamed: 929_level_0</th>\n",
       "      <th>Unnamed: 930_level_0</th>\n",
       "      <th>Unnamed: 931_level_0</th>\n",
       "      <th>Unnamed: 932_level_0</th>\n",
       "      <th>Unnamed: 933_level_0</th>\n",
       "      <th>Unnamed: 934_level_0</th>\n",
       "      <th>Unnamed: 935_level_0</th>\n",
       "      <th>Unnamed: 936_level_0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0_level_1</th>\n",
       "      <th>³6 months</th>\n",
       "      <th>Unnamed: 2_level_1</th>\n",
       "      <th>Unnamed: 3_level_1</th>\n",
       "      <th>Unnamed: 4_level_1</th>\n",
       "      <th>Unnamed: 5_level_1</th>\n",
       "      <th>Unnamed: 6_level_1</th>\n",
       "      <th>Unnamed: 7_level_1</th>\n",
       "      <th>Unnamed: 8_level_1</th>\n",
       "      <th>Unnamed: 9_level_1</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 927_level_1</th>\n",
       "      <th>Unnamed: 928_level_1</th>\n",
       "      <th>Unnamed: 929_level_1</th>\n",
       "      <th>Unnamed: 930_level_1</th>\n",
       "      <th>Unnamed: 931_level_1</th>\n",
       "      <th>Unnamed: 932_level_1</th>\n",
       "      <th>Unnamed: 933_level_1</th>\n",
       "      <th>Unnamed: 934_level_1</th>\n",
       "      <th>Unnamed: 935_level_1</th>\n",
       "      <th>Unnamed: 936_level_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Names</th>\n",
       "      <th>2010-11</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>2011-12</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>...</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>2018-19</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>42.4</td>\n",
       "      <td>40.1</td>\n",
       "      <td>44.7</td>\n",
       "      <td>(±2.3)</td>\n",
       "      <td>8221.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>41.6</td>\n",
       "      <td>39.7</td>\n",
       "      <td>43.5</td>\n",
       "      <td>...</td>\n",
       "      <td>50.7</td>\n",
       "      <td>(±7.8)</td>\n",
       "      <td>445.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>36.5</td>\n",
       "      <td>29.6</td>\n",
       "      <td>43.4</td>\n",
       "      <td>(±6.9)</td>\n",
       "      <td>460</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>37.0</td>\n",
       "      <td>33.3</td>\n",
       "      <td>40.7</td>\n",
       "      <td>(±3.7)</td>\n",
       "      <td>4082.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>34.7</td>\n",
       "      <td>32.2</td>\n",
       "      <td>37.2</td>\n",
       "      <td>...</td>\n",
       "      <td>50.4</td>\n",
       "      <td>(±7.7)</td>\n",
       "      <td>858.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>41.6</td>\n",
       "      <td>35.3</td>\n",
       "      <td>47.9</td>\n",
       "      <td>(±6.3)</td>\n",
       "      <td>930</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.9</td>\n",
       "      <td>38.8</td>\n",
       "      <td>45.0</td>\n",
       "      <td>(±3.1)</td>\n",
       "      <td>7714.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>38.2</td>\n",
       "      <td>35.8</td>\n",
       "      <td>40.6</td>\n",
       "      <td>...</td>\n",
       "      <td>54.9</td>\n",
       "      <td>(±8.9)</td>\n",
       "      <td>1197.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>46.3</td>\n",
       "      <td>39.4</td>\n",
       "      <td>53.2</td>\n",
       "      <td>(±6.9)</td>\n",
       "      <td>1013</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>44.2</td>\n",
       "      <td>40.9</td>\n",
       "      <td>47.5</td>\n",
       "      <td>(±3.3)</td>\n",
       "      <td>5605.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>44.1</td>\n",
       "      <td>49.1</td>\n",
       "      <td>...</td>\n",
       "      <td>48.8</td>\n",
       "      <td>(±9.0)</td>\n",
       "      <td>431.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>36.1</td>\n",
       "      <td>28.3</td>\n",
       "      <td>43.9</td>\n",
       "      <td>(±7.8)</td>\n",
       "      <td>365</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>38.9</td>\n",
       "      <td>37.6</td>\n",
       "      <td>40.2</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>17752.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>40.5</td>\n",
       "      <td>36.2</td>\n",
       "      <td>44.8</td>\n",
       "      <td>...</td>\n",
       "      <td>46.8</td>\n",
       "      <td>(±3.6)</td>\n",
       "      <td>1582.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>40.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>(±4.4)</td>\n",
       "      <td>1332</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>45.9</td>\n",
       "      <td>44.6</td>\n",
       "      <td>47.2</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>44668.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>44.7</td>\n",
       "      <td>46.7</td>\n",
       "      <td>...</td>\n",
       "      <td>44.8</td>\n",
       "      <td>(±4.6)</td>\n",
       "      <td>1769.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.3</td>\n",
       "      <td>45.7</td>\n",
       "      <td>54.9</td>\n",
       "      <td>(±4.6)</td>\n",
       "      <td>1778</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>45.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>50272.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>42.9</td>\n",
       "      <td>41.9</td>\n",
       "      <td>43.9</td>\n",
       "      <td>...</td>\n",
       "      <td>48.5</td>\n",
       "      <td>(±4.1)</td>\n",
       "      <td>3732.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.2</td>\n",
       "      <td>46.3</td>\n",
       "      <td>54.1</td>\n",
       "      <td>(±3.9)</td>\n",
       "      <td>3717</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>39.6</td>\n",
       "      <td>38.5</td>\n",
       "      <td>40.7</td>\n",
       "      <td>(±1.1)</td>\n",
       "      <td>37303.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>37.2</td>\n",
       "      <td>40.2</td>\n",
       "      <td>...</td>\n",
       "      <td>46.2</td>\n",
       "      <td>(±2.9)</td>\n",
       "      <td>7394.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>42.2</td>\n",
       "      <td>49.2</td>\n",
       "      <td>(±3.5)</td>\n",
       "      <td>7331</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>40.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>41.3</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>35002.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>39.4</td>\n",
       "      <td>38.3</td>\n",
       "      <td>40.5</td>\n",
       "      <td>...</td>\n",
       "      <td>48.9</td>\n",
       "      <td>(±4.1)</td>\n",
       "      <td>2664.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>52.6</td>\n",
       "      <td>48.9</td>\n",
       "      <td>56.3</td>\n",
       "      <td>(±3.7)</td>\n",
       "      <td>2826</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</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",
       "<p>63 rows × 937 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0_level_0 Influenza vaccination (General Population) - Age  \\\n",
       "   Unnamed: 0_level_1                                        ³6 months   \n",
       "                Names                                          2010-11   \n",
       "0             Alabama                                             42.4   \n",
       "1              Alaska                                             37.0   \n",
       "2             Arizona                                             41.9   \n",
       "3            Arkansas                                             44.2   \n",
       "4          California                                             38.9   \n",
       "..                ...                                              ...   \n",
       "58          Region  7                                             45.9   \n",
       "59          Region  8                                             45.3   \n",
       "60          Region  9                                             39.6   \n",
       "61          Region 10                                             40.0   \n",
       "62     HP 2020 Target                                             70.0   \n",
       "\n",
       "   Unnamed: 2_level_0 Unnamed: 3_level_0 Unnamed: 4_level_0  \\\n",
       "   Unnamed: 2_level_1 Unnamed: 3_level_1 Unnamed: 4_level_1   \n",
       "                   LL                 UL                 CI   \n",
       "0                40.1               44.7             (±2.3)   \n",
       "1                33.3               40.7             (±3.7)   \n",
       "2                38.8               45.0             (±3.1)   \n",
       "3                40.9               47.5             (±3.3)   \n",
       "4                37.6               40.2             (±1.3)   \n",
       "..                ...                ...                ...   \n",
       "58               44.6               47.2             (±1.3)   \n",
       "59               44.0               46.6             (±1.3)   \n",
       "60               38.5               40.7             (±1.1)   \n",
       "61               38.7               41.3             (±1.3)   \n",
       "62                NaN                NaN                NaN   \n",
       "\n",
       "   Unnamed: 5_level_0 Unnamed: 6_level_0 Unnamed: 7_level_0  \\\n",
       "   Unnamed: 5_level_1 Unnamed: 6_level_1 Unnamed: 7_level_1   \n",
       "               SAMPLE             TARGET            2011-12   \n",
       "0              8221.0               70.0               41.6   \n",
       "1              4082.0               70.0               34.7   \n",
       "2              7714.0               70.0               38.2   \n",
       "3              5605.0               70.0               46.6   \n",
       "4             17752.0               70.0               40.5   \n",
       "..                ...                ...                ...   \n",
       "58            44668.0               70.0               45.7   \n",
       "59            50272.0               70.0               42.9   \n",
       "60            37303.0               70.0               38.7   \n",
       "61            35002.0               70.0               39.4   \n",
       "62                NaN                NaN               70.0   \n",
       "\n",
       "   Unnamed: 8_level_0 Unnamed: 9_level_0  ... Unnamed: 927_level_0  \\\n",
       "   Unnamed: 8_level_1 Unnamed: 9_level_1  ... Unnamed: 927_level_1   \n",
       "                   LL                 UL  ...                   UL   \n",
       "0                39.7               43.5  ...                 50.7   \n",
       "1                32.2               37.2  ...                 50.4   \n",
       "2                35.8               40.6  ...                 54.9   \n",
       "3                44.1               49.1  ...                 48.8   \n",
       "4                36.2               44.8  ...                 46.8   \n",
       "..                ...                ...  ...                  ...   \n",
       "58               44.7               46.7  ...                 44.8   \n",
       "59               41.9               43.9  ...                 48.5   \n",
       "60               37.2               40.2  ...                 46.2   \n",
       "61               38.3               40.5  ...                 48.9   \n",
       "62                NaN                NaN  ...                  NaN   \n",
       "\n",
       "   Unnamed: 928_level_0 Unnamed: 929_level_0 Unnamed: 930_level_0  \\\n",
       "   Unnamed: 928_level_1 Unnamed: 929_level_1 Unnamed: 930_level_1   \n",
       "                     CI               SAMPLE               TARGET   \n",
       "0                (±7.8)                445.0                 70.0   \n",
       "1                (±7.7)                858.0                 70.0   \n",
       "2                (±8.9)               1197.0                 70.0   \n",
       "3                (±9.0)                431.0                 70.0   \n",
       "4                (±3.6)               1582.0                 70.0   \n",
       "..                  ...                  ...                  ...   \n",
       "58               (±4.6)               1769.0                 70.0   \n",
       "59               (±4.1)               3732.0                 70.0   \n",
       "60               (±2.9)               7394.0                 70.0   \n",
       "61               (±4.1)               2664.0                 70.0   \n",
       "62                  NaN                  NaN                  NaN   \n",
       "\n",
       "   Unnamed: 931_level_0 Unnamed: 932_level_0 Unnamed: 933_level_0  \\\n",
       "   Unnamed: 931_level_1 Unnamed: 932_level_1 Unnamed: 933_level_1   \n",
       "                2018-19                   LL                   UL   \n",
       "0                  36.5                 29.6                 43.4   \n",
       "1                  41.6                 35.3                 47.9   \n",
       "2                  46.3                 39.4                 53.2   \n",
       "3                  36.1                 28.3                 43.9   \n",
       "4                  45.0                 40.6                 49.4   \n",
       "..                  ...                  ...                  ...   \n",
       "58                 50.3                 45.7                 54.9   \n",
       "59                 50.2                 46.3                 54.1   \n",
       "60                 45.7                 42.2                 49.2   \n",
       "61                 52.6                 48.9                 56.3   \n",
       "62                 70.0                  NaN                  NaN   \n",
       "\n",
       "   Unnamed: 934_level_0 Unnamed: 935_level_0 Unnamed: 936_level_0  \n",
       "   Unnamed: 934_level_1 Unnamed: 935_level_1 Unnamed: 936_level_1  \n",
       "                     CI               SAMPLE               TARGET  \n",
       "0                (±6.9)                  460                 70.0  \n",
       "1                (±6.3)                  930                 70.0  \n",
       "2                (±6.9)                 1013                 70.0  \n",
       "3                (±7.8)                  365                 70.0  \n",
       "4                (±4.4)                 1332                 70.0  \n",
       "..                  ...                  ...                  ...  \n",
       "58               (±4.6)                 1778                 70.0  \n",
       "59               (±3.9)                 3717                 70.0  \n",
       "60               (±3.5)                 7331                 70.0  \n",
       "61               (±3.7)                 2826                 70.0  \n",
       "62                  NaN                  NaN                  NaN  \n",
       "\n",
       "[63 rows x 937 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# open the raw dataset\n",
    "csv_path = os.path.join(\"vaccination_raw.csv\")\n",
    "df_raw = pd.read_csv(csv_path, encoding='unicode_escape', header=[0,1,2])\n",
    "df_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0_level_0</th>\n",
       "      <th>Influenza vaccination (General Population) - Age</th>\n",
       "      <th>Unnamed: 2_level_0</th>\n",
       "      <th>Unnamed: 3_level_0</th>\n",
       "      <th>Unnamed: 4_level_0</th>\n",
       "      <th>Unnamed: 5_level_0</th>\n",
       "      <th>Unnamed: 6_level_0</th>\n",
       "      <th>Unnamed: 7_level_0</th>\n",
       "      <th>Unnamed: 8_level_0</th>\n",
       "      <th>Unnamed: 9_level_0</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 927_level_0</th>\n",
       "      <th>Unnamed: 928_level_0</th>\n",
       "      <th>Unnamed: 929_level_0</th>\n",
       "      <th>Unnamed: 930_level_0</th>\n",
       "      <th>Unnamed: 931_level_0</th>\n",
       "      <th>Unnamed: 932_level_0</th>\n",
       "      <th>Unnamed: 933_level_0</th>\n",
       "      <th>Unnamed: 934_level_0</th>\n",
       "      <th>Unnamed: 935_level_0</th>\n",
       "      <th>Unnamed: 936_level_0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0_level_1</th>\n",
       "      <th>³6 months</th>\n",
       "      <th>Unnamed: 2_level_1</th>\n",
       "      <th>Unnamed: 3_level_1</th>\n",
       "      <th>Unnamed: 4_level_1</th>\n",
       "      <th>Unnamed: 5_level_1</th>\n",
       "      <th>Unnamed: 6_level_1</th>\n",
       "      <th>Unnamed: 7_level_1</th>\n",
       "      <th>Unnamed: 8_level_1</th>\n",
       "      <th>Unnamed: 9_level_1</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 927_level_1</th>\n",
       "      <th>Unnamed: 928_level_1</th>\n",
       "      <th>Unnamed: 929_level_1</th>\n",
       "      <th>Unnamed: 930_level_1</th>\n",
       "      <th>Unnamed: 931_level_1</th>\n",
       "      <th>Unnamed: 932_level_1</th>\n",
       "      <th>Unnamed: 933_level_1</th>\n",
       "      <th>Unnamed: 934_level_1</th>\n",
       "      <th>Unnamed: 935_level_1</th>\n",
       "      <th>Unnamed: 936_level_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Names</th>\n",
       "      <th>2010-11</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>2011-12</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>...</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>2018-19</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>42.4</td>\n",
       "      <td>40.1</td>\n",
       "      <td>44.7</td>\n",
       "      <td>(±2.3)</td>\n",
       "      <td>8221.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>41.6</td>\n",
       "      <td>39.7</td>\n",
       "      <td>43.5</td>\n",
       "      <td>...</td>\n",
       "      <td>50.7</td>\n",
       "      <td>(±7.8)</td>\n",
       "      <td>445.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>36.5</td>\n",
       "      <td>29.6</td>\n",
       "      <td>43.4</td>\n",
       "      <td>(±6.9)</td>\n",
       "      <td>460</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>37.0</td>\n",
       "      <td>33.3</td>\n",
       "      <td>40.7</td>\n",
       "      <td>(±3.7)</td>\n",
       "      <td>4082.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>34.7</td>\n",
       "      <td>32.2</td>\n",
       "      <td>37.2</td>\n",
       "      <td>...</td>\n",
       "      <td>50.4</td>\n",
       "      <td>(±7.7)</td>\n",
       "      <td>858.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>41.6</td>\n",
       "      <td>35.3</td>\n",
       "      <td>47.9</td>\n",
       "      <td>(±6.3)</td>\n",
       "      <td>930</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.9</td>\n",
       "      <td>38.8</td>\n",
       "      <td>45.0</td>\n",
       "      <td>(±3.1)</td>\n",
       "      <td>7714.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>38.2</td>\n",
       "      <td>35.8</td>\n",
       "      <td>40.6</td>\n",
       "      <td>...</td>\n",
       "      <td>54.9</td>\n",
       "      <td>(±8.9)</td>\n",
       "      <td>1197.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>46.3</td>\n",
       "      <td>39.4</td>\n",
       "      <td>53.2</td>\n",
       "      <td>(±6.9)</td>\n",
       "      <td>1013</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>44.2</td>\n",
       "      <td>40.9</td>\n",
       "      <td>47.5</td>\n",
       "      <td>(±3.3)</td>\n",
       "      <td>5605.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>44.1</td>\n",
       "      <td>49.1</td>\n",
       "      <td>...</td>\n",
       "      <td>48.8</td>\n",
       "      <td>(±9.0)</td>\n",
       "      <td>431.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>36.1</td>\n",
       "      <td>28.3</td>\n",
       "      <td>43.9</td>\n",
       "      <td>(±7.8)</td>\n",
       "      <td>365</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>38.9</td>\n",
       "      <td>37.6</td>\n",
       "      <td>40.2</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>17752.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>40.5</td>\n",
       "      <td>36.2</td>\n",
       "      <td>44.8</td>\n",
       "      <td>...</td>\n",
       "      <td>46.8</td>\n",
       "      <td>(±3.6)</td>\n",
       "      <td>1582.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>40.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>(±4.4)</td>\n",
       "      <td>1332</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>45.9</td>\n",
       "      <td>44.6</td>\n",
       "      <td>47.2</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>44668.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>44.7</td>\n",
       "      <td>46.7</td>\n",
       "      <td>...</td>\n",
       "      <td>44.8</td>\n",
       "      <td>(±4.6)</td>\n",
       "      <td>1769.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.3</td>\n",
       "      <td>45.7</td>\n",
       "      <td>54.9</td>\n",
       "      <td>(±4.6)</td>\n",
       "      <td>1778</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>45.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>50272.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>42.9</td>\n",
       "      <td>41.9</td>\n",
       "      <td>43.9</td>\n",
       "      <td>...</td>\n",
       "      <td>48.5</td>\n",
       "      <td>(±4.1)</td>\n",
       "      <td>3732.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.2</td>\n",
       "      <td>46.3</td>\n",
       "      <td>54.1</td>\n",
       "      <td>(±3.9)</td>\n",
       "      <td>3717</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>39.6</td>\n",
       "      <td>38.5</td>\n",
       "      <td>40.7</td>\n",
       "      <td>(±1.1)</td>\n",
       "      <td>37303.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>37.2</td>\n",
       "      <td>40.2</td>\n",
       "      <td>...</td>\n",
       "      <td>46.2</td>\n",
       "      <td>(±2.9)</td>\n",
       "      <td>7394.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>42.2</td>\n",
       "      <td>49.2</td>\n",
       "      <td>(±3.5)</td>\n",
       "      <td>7331</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>40.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>41.3</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>35002.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>39.4</td>\n",
       "      <td>38.3</td>\n",
       "      <td>40.5</td>\n",
       "      <td>...</td>\n",
       "      <td>48.9</td>\n",
       "      <td>(±4.1)</td>\n",
       "      <td>2664.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>52.6</td>\n",
       "      <td>48.9</td>\n",
       "      <td>56.3</td>\n",
       "      <td>(±3.7)</td>\n",
       "      <td>2826</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</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",
       "<p>63 rows × 937 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0_level_0 Influenza vaccination (General Population) - Age  \\\n",
       "   Unnamed: 0_level_1                                        ³6 months   \n",
       "                Names                                          2010-11   \n",
       "0             Alabama                                             42.4   \n",
       "1              Alaska                                             37.0   \n",
       "2             Arizona                                             41.9   \n",
       "3            Arkansas                                             44.2   \n",
       "4          California                                             38.9   \n",
       "..                ...                                              ...   \n",
       "58          Region  7                                             45.9   \n",
       "59          Region  8                                             45.3   \n",
       "60          Region  9                                             39.6   \n",
       "61          Region 10                                             40.0   \n",
       "62     HP 2020 Target                                             70.0   \n",
       "\n",
       "   Unnamed: 2_level_0 Unnamed: 3_level_0 Unnamed: 4_level_0  \\\n",
       "   Unnamed: 2_level_1 Unnamed: 3_level_1 Unnamed: 4_level_1   \n",
       "                   LL                 UL                 CI   \n",
       "0                40.1               44.7             (±2.3)   \n",
       "1                33.3               40.7             (±3.7)   \n",
       "2                38.8               45.0             (±3.1)   \n",
       "3                40.9               47.5             (±3.3)   \n",
       "4                37.6               40.2             (±1.3)   \n",
       "..                ...                ...                ...   \n",
       "58               44.6               47.2             (±1.3)   \n",
       "59               44.0               46.6             (±1.3)   \n",
       "60               38.5               40.7             (±1.1)   \n",
       "61               38.7               41.3             (±1.3)   \n",
       "62                NaN                NaN                NaN   \n",
       "\n",
       "   Unnamed: 5_level_0 Unnamed: 6_level_0 Unnamed: 7_level_0  \\\n",
       "   Unnamed: 5_level_1 Unnamed: 6_level_1 Unnamed: 7_level_1   \n",
       "               SAMPLE             TARGET            2011-12   \n",
       "0              8221.0               70.0               41.6   \n",
       "1              4082.0               70.0               34.7   \n",
       "2              7714.0               70.0               38.2   \n",
       "3              5605.0               70.0               46.6   \n",
       "4             17752.0               70.0               40.5   \n",
       "..                ...                ...                ...   \n",
       "58            44668.0               70.0               45.7   \n",
       "59            50272.0               70.0               42.9   \n",
       "60            37303.0               70.0               38.7   \n",
       "61            35002.0               70.0               39.4   \n",
       "62                NaN                NaN               70.0   \n",
       "\n",
       "   Unnamed: 8_level_0 Unnamed: 9_level_0  ... Unnamed: 927_level_0  \\\n",
       "   Unnamed: 8_level_1 Unnamed: 9_level_1  ... Unnamed: 927_level_1   \n",
       "                   LL                 UL  ...                   UL   \n",
       "0                39.7               43.5  ...                 50.7   \n",
       "1                32.2               37.2  ...                 50.4   \n",
       "2                35.8               40.6  ...                 54.9   \n",
       "3                44.1               49.1  ...                 48.8   \n",
       "4                36.2               44.8  ...                 46.8   \n",
       "..                ...                ...  ...                  ...   \n",
       "58               44.7               46.7  ...                 44.8   \n",
       "59               41.9               43.9  ...                 48.5   \n",
       "60               37.2               40.2  ...                 46.2   \n",
       "61               38.3               40.5  ...                 48.9   \n",
       "62                NaN                NaN  ...                  NaN   \n",
       "\n",
       "   Unnamed: 928_level_0 Unnamed: 929_level_0 Unnamed: 930_level_0  \\\n",
       "   Unnamed: 928_level_1 Unnamed: 929_level_1 Unnamed: 930_level_1   \n",
       "                     CI               SAMPLE               TARGET   \n",
       "0                (±7.8)                445.0                 70.0   \n",
       "1                (±7.7)                858.0                 70.0   \n",
       "2                (±8.9)               1197.0                 70.0   \n",
       "3                (±9.0)                431.0                 70.0   \n",
       "4                (±3.6)               1582.0                 70.0   \n",
       "..                  ...                  ...                  ...   \n",
       "58               (±4.6)               1769.0                 70.0   \n",
       "59               (±4.1)               3732.0                 70.0   \n",
       "60               (±2.9)               7394.0                 70.0   \n",
       "61               (±4.1)               2664.0                 70.0   \n",
       "62                  NaN                  NaN                  NaN   \n",
       "\n",
       "   Unnamed: 931_level_0 Unnamed: 932_level_0 Unnamed: 933_level_0  \\\n",
       "   Unnamed: 931_level_1 Unnamed: 932_level_1 Unnamed: 933_level_1   \n",
       "                2018-19                   LL                   UL   \n",
       "0                  36.5                 29.6                 43.4   \n",
       "1                  41.6                 35.3                 47.9   \n",
       "2                  46.3                 39.4                 53.2   \n",
       "3                  36.1                 28.3                 43.9   \n",
       "4                  45.0                 40.6                 49.4   \n",
       "..                  ...                  ...                  ...   \n",
       "58                 50.3                 45.7                 54.9   \n",
       "59                 50.2                 46.3                 54.1   \n",
       "60                 45.7                 42.2                 49.2   \n",
       "61                 52.6                 48.9                 56.3   \n",
       "62                 70.0                  NaN                  NaN   \n",
       "\n",
       "   Unnamed: 934_level_0 Unnamed: 935_level_0 Unnamed: 936_level_0  \n",
       "   Unnamed: 934_level_1 Unnamed: 935_level_1 Unnamed: 936_level_1  \n",
       "                     CI               SAMPLE               TARGET  \n",
       "0                (±6.9)                  460                 70.0  \n",
       "1                (±6.3)                  930                 70.0  \n",
       "2                (±6.9)                 1013                 70.0  \n",
       "3                (±7.8)                  365                 70.0  \n",
       "4                (±4.4)                 1332                 70.0  \n",
       "..                  ...                  ...                  ...  \n",
       "58               (±4.6)                 1778                 70.0  \n",
       "59               (±3.9)                 3717                 70.0  \n",
       "60               (±3.5)                 7331                 70.0  \n",
       "61               (±3.7)                 2826                 70.0  \n",
       "62                  NaN                  NaN                  NaN  \n",
       "\n",
       "[63 rows x 937 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make a copy of raw dataset to tailor\n",
    "df_clean = df_raw.copy()\n",
    "df_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "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>Names</th>\n",
       "      <th>2010-11</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>2011-12</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>...</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>2018-19</th>\n",
       "      <th>LL</th>\n",
       "      <th>UL</th>\n",
       "      <th>CI</th>\n",
       "      <th>SAMPLE</th>\n",
       "      <th>TARGET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>42.4</td>\n",
       "      <td>40.1</td>\n",
       "      <td>44.7</td>\n",
       "      <td>(±2.3)</td>\n",
       "      <td>8221.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>41.6</td>\n",
       "      <td>39.7</td>\n",
       "      <td>43.5</td>\n",
       "      <td>...</td>\n",
       "      <td>50.7</td>\n",
       "      <td>(±7.8)</td>\n",
       "      <td>445.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>36.5</td>\n",
       "      <td>29.6</td>\n",
       "      <td>43.4</td>\n",
       "      <td>(±6.9)</td>\n",
       "      <td>460</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>37.0</td>\n",
       "      <td>33.3</td>\n",
       "      <td>40.7</td>\n",
       "      <td>(±3.7)</td>\n",
       "      <td>4082.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>34.7</td>\n",
       "      <td>32.2</td>\n",
       "      <td>37.2</td>\n",
       "      <td>...</td>\n",
       "      <td>50.4</td>\n",
       "      <td>(±7.7)</td>\n",
       "      <td>858.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>41.6</td>\n",
       "      <td>35.3</td>\n",
       "      <td>47.9</td>\n",
       "      <td>(±6.3)</td>\n",
       "      <td>930</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.9</td>\n",
       "      <td>38.8</td>\n",
       "      <td>45.0</td>\n",
       "      <td>(±3.1)</td>\n",
       "      <td>7714.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>38.2</td>\n",
       "      <td>35.8</td>\n",
       "      <td>40.6</td>\n",
       "      <td>...</td>\n",
       "      <td>54.9</td>\n",
       "      <td>(±8.9)</td>\n",
       "      <td>1197.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>46.3</td>\n",
       "      <td>39.4</td>\n",
       "      <td>53.2</td>\n",
       "      <td>(±6.9)</td>\n",
       "      <td>1013</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>44.2</td>\n",
       "      <td>40.9</td>\n",
       "      <td>47.5</td>\n",
       "      <td>(±3.3)</td>\n",
       "      <td>5605.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>44.1</td>\n",
       "      <td>49.1</td>\n",
       "      <td>...</td>\n",
       "      <td>48.8</td>\n",
       "      <td>(±9.0)</td>\n",
       "      <td>431.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>36.1</td>\n",
       "      <td>28.3</td>\n",
       "      <td>43.9</td>\n",
       "      <td>(±7.8)</td>\n",
       "      <td>365</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>38.9</td>\n",
       "      <td>37.6</td>\n",
       "      <td>40.2</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>17752.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>40.5</td>\n",
       "      <td>36.2</td>\n",
       "      <td>44.8</td>\n",
       "      <td>...</td>\n",
       "      <td>46.8</td>\n",
       "      <td>(±3.6)</td>\n",
       "      <td>1582.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>40.6</td>\n",
       "      <td>49.4</td>\n",
       "      <td>(±4.4)</td>\n",
       "      <td>1332</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>45.9</td>\n",
       "      <td>44.6</td>\n",
       "      <td>47.2</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>44668.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>44.7</td>\n",
       "      <td>46.7</td>\n",
       "      <td>...</td>\n",
       "      <td>44.8</td>\n",
       "      <td>(±4.6)</td>\n",
       "      <td>1769.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.3</td>\n",
       "      <td>45.7</td>\n",
       "      <td>54.9</td>\n",
       "      <td>(±4.6)</td>\n",
       "      <td>1778</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>45.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>50272.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>42.9</td>\n",
       "      <td>41.9</td>\n",
       "      <td>43.9</td>\n",
       "      <td>...</td>\n",
       "      <td>48.5</td>\n",
       "      <td>(±4.1)</td>\n",
       "      <td>3732.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>50.2</td>\n",
       "      <td>46.3</td>\n",
       "      <td>54.1</td>\n",
       "      <td>(±3.9)</td>\n",
       "      <td>3717</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>39.6</td>\n",
       "      <td>38.5</td>\n",
       "      <td>40.7</td>\n",
       "      <td>(±1.1)</td>\n",
       "      <td>37303.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>37.2</td>\n",
       "      <td>40.2</td>\n",
       "      <td>...</td>\n",
       "      <td>46.2</td>\n",
       "      <td>(±2.9)</td>\n",
       "      <td>7394.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>42.2</td>\n",
       "      <td>49.2</td>\n",
       "      <td>(±3.5)</td>\n",
       "      <td>7331</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>40.0</td>\n",
       "      <td>38.7</td>\n",
       "      <td>41.3</td>\n",
       "      <td>(±1.3)</td>\n",
       "      <td>35002.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>39.4</td>\n",
       "      <td>38.3</td>\n",
       "      <td>40.5</td>\n",
       "      <td>...</td>\n",
       "      <td>48.9</td>\n",
       "      <td>(±4.1)</td>\n",
       "      <td>2664.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>52.6</td>\n",
       "      <td>48.9</td>\n",
       "      <td>56.3</td>\n",
       "      <td>(±3.7)</td>\n",
       "      <td>2826</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</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",
       "<p>63 rows × 937 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Names  2010-11    LL    UL      CI   SAMPLE TARGET  2011-12  \\\n",
       "0          Alabama     42.4  40.1  44.7  (±2.3)   8221.0   70.0     41.6   \n",
       "1           Alaska     37.0  33.3  40.7  (±3.7)   4082.0   70.0     34.7   \n",
       "2          Arizona     41.9  38.8  45.0  (±3.1)   7714.0   70.0     38.2   \n",
       "3         Arkansas     44.2  40.9  47.5  (±3.3)   5605.0   70.0     46.6   \n",
       "4       California     38.9  37.6  40.2  (±1.3)  17752.0   70.0     40.5   \n",
       "..             ...      ...   ...   ...     ...      ...    ...      ...   \n",
       "58       Region  7     45.9  44.6  47.2  (±1.3)  44668.0   70.0     45.7   \n",
       "59       Region  8     45.3  44.0  46.6  (±1.3)  50272.0   70.0     42.9   \n",
       "60       Region  9     39.6  38.5  40.7  (±1.1)  37303.0   70.0     38.7   \n",
       "61       Region 10     40.0  38.7  41.3  (±1.3)  35002.0   70.0     39.4   \n",
       "62  HP 2020 Target     70.0   NaN   NaN     NaN      NaN    NaN     70.0   \n",
       "\n",
       "      LL    UL  ...    UL      CI  SAMPLE TARGET  2018-19    LL    UL      CI  \\\n",
       "0   39.7  43.5  ...  50.7  (±7.8)   445.0   70.0     36.5  29.6  43.4  (±6.9)   \n",
       "1   32.2  37.2  ...  50.4  (±7.7)   858.0   70.0     41.6  35.3  47.9  (±6.3)   \n",
       "2   35.8  40.6  ...  54.9  (±8.9)  1197.0   70.0     46.3  39.4  53.2  (±6.9)   \n",
       "3   44.1  49.1  ...  48.8  (±9.0)   431.0   70.0     36.1  28.3  43.9  (±7.8)   \n",
       "4   36.2  44.8  ...  46.8  (±3.6)  1582.0   70.0     45.0  40.6  49.4  (±4.4)   \n",
       "..   ...   ...  ...   ...     ...     ...    ...      ...   ...   ...     ...   \n",
       "58  44.7  46.7  ...  44.8  (±4.6)  1769.0   70.0     50.3  45.7  54.9  (±4.6)   \n",
       "59  41.9  43.9  ...  48.5  (±4.1)  3732.0   70.0     50.2  46.3  54.1  (±3.9)   \n",
       "60  37.2  40.2  ...  46.2  (±2.9)  7394.0   70.0     45.7  42.2  49.2  (±3.5)   \n",
       "61  38.3  40.5  ...  48.9  (±4.1)  2664.0   70.0     52.6  48.9  56.3  (±3.7)   \n",
       "62   NaN   NaN  ...   NaN     NaN     NaN    NaN     70.0   NaN   NaN     NaN   \n",
       "\n",
       "    SAMPLE TARGET  \n",
       "0      460   70.0  \n",
       "1      930   70.0  \n",
       "2     1013   70.0  \n",
       "3      365   70.0  \n",
       "4     1332   70.0  \n",
       "..     ...    ...  \n",
       "58    1778   70.0  \n",
       "59    3717   70.0  \n",
       "60    7331   70.0  \n",
       "61    2826   70.0  \n",
       "62     NaN    NaN  \n",
       "\n",
       "[63 rows x 937 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean the header\n",
    "df_clean.columns = df_clean.columns.droplevel([0,1])\n",
    "df_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# set index by \"Names\"\n",
    "df_clean = df_clean.set_index('Names')"
   ]
  },
  {
   "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>2010-11</th>\n",
       "      <th>2011-12</th>\n",
       "      <th>2012-13</th>\n",
       "      <th>2013-14</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2015-16</th>\n",
       "      <th>2016-17</th>\n",
       "      <th>2017-18</th>\n",
       "      <th>2018-19</th>\n",
       "      <th>2010-11</th>\n",
       "      <th>...</th>\n",
       "      <th>2018-19</th>\n",
       "      <th>2010-11</th>\n",
       "      <th>2011-12</th>\n",
       "      <th>2012-13</th>\n",
       "      <th>2013-14</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2015-16</th>\n",
       "      <th>2016-17</th>\n",
       "      <th>2017-18</th>\n",
       "      <th>2018-19</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Names</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",
       "      <td>Alabama</td>\n",
       "      <td>42.4</td>\n",
       "      <td>41.6</td>\n",
       "      <td>45.7</td>\n",
       "      <td>48.3</td>\n",
       "      <td>45.8</td>\n",
       "      <td>43.9</td>\n",
       "      <td>43.9</td>\n",
       "      <td>42.4</td>\n",
       "      <td>48.3</td>\n",
       "      <td>45.9</td>\n",
       "      <td>...</td>\n",
       "      <td>64.2</td>\n",
       "      <td>39.2</td>\n",
       "      <td>45.2</td>\n",
       "      <td>44.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>42.9</td>\n",
       "      <td>45.8</td>\n",
       "      <td>38.9</td>\n",
       "      <td>42.9</td>\n",
       "      <td>36.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Alaska</td>\n",
       "      <td>37.0</td>\n",
       "      <td>34.7</td>\n",
       "      <td>39.7</td>\n",
       "      <td>41.7</td>\n",
       "      <td>44.0</td>\n",
       "      <td>37.9</td>\n",
       "      <td>39.1</td>\n",
       "      <td>40.2</td>\n",
       "      <td>44.1</td>\n",
       "      <td>46.8</td>\n",
       "      <td>...</td>\n",
       "      <td>33.0</td>\n",
       "      <td>40.7</td>\n",
       "      <td>37.7</td>\n",
       "      <td>39.2</td>\n",
       "      <td>47.0</td>\n",
       "      <td>44.7</td>\n",
       "      <td>37.2</td>\n",
       "      <td>44.2</td>\n",
       "      <td>42.7</td>\n",
       "      <td>41.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.9</td>\n",
       "      <td>38.2</td>\n",
       "      <td>38.3</td>\n",
       "      <td>38.5</td>\n",
       "      <td>41.6</td>\n",
       "      <td>41.5</td>\n",
       "      <td>41.8</td>\n",
       "      <td>38.9</td>\n",
       "      <td>45.6</td>\n",
       "      <td>49.0</td>\n",
       "      <td>...</td>\n",
       "      <td>41.9</td>\n",
       "      <td>47.2</td>\n",
       "      <td>48.2</td>\n",
       "      <td>39.4</td>\n",
       "      <td>42.0</td>\n",
       "      <td>43.4</td>\n",
       "      <td>46.9</td>\n",
       "      <td>39.6</td>\n",
       "      <td>46.0</td>\n",
       "      <td>46.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Arkansas</td>\n",
       "      <td>44.2</td>\n",
       "      <td>46.6</td>\n",
       "      <td>47.0</td>\n",
       "      <td>49.7</td>\n",
       "      <td>50.5</td>\n",
       "      <td>45.4</td>\n",
       "      <td>46.2</td>\n",
       "      <td>41.7</td>\n",
       "      <td>48.8</td>\n",
       "      <td>56.1</td>\n",
       "      <td>...</td>\n",
       "      <td>50.7</td>\n",
       "      <td>47.3</td>\n",
       "      <td>53.2</td>\n",
       "      <td>36.6</td>\n",
       "      <td>41.5</td>\n",
       "      <td>54.3</td>\n",
       "      <td>39.6</td>\n",
       "      <td>50.2</td>\n",
       "      <td>39.8</td>\n",
       "      <td>36.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>California</td>\n",
       "      <td>38.9</td>\n",
       "      <td>40.5</td>\n",
       "      <td>44.2</td>\n",
       "      <td>NR</td>\n",
       "      <td>44.2</td>\n",
       "      <td>43.7</td>\n",
       "      <td>48.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>47.4</td>\n",
       "      <td>50.7</td>\n",
       "      <td>...</td>\n",
       "      <td>44.6</td>\n",
       "      <td>37.0</td>\n",
       "      <td>34.6</td>\n",
       "      <td>48.3</td>\n",
       "      <td>NR</td>\n",
       "      <td>48.7</td>\n",
       "      <td>43.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>43.2</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>Region  7</td>\n",
       "      <td>45.9</td>\n",
       "      <td>45.7</td>\n",
       "      <td>46.8</td>\n",
       "      <td>48.5</td>\n",
       "      <td>50.6</td>\n",
       "      <td>48.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>43.9</td>\n",
       "      <td>51.7</td>\n",
       "      <td>48.6</td>\n",
       "      <td>...</td>\n",
       "      <td>51.5</td>\n",
       "      <td>50.8</td>\n",
       "      <td>43.8</td>\n",
       "      <td>50.2</td>\n",
       "      <td>46.4</td>\n",
       "      <td>50.2</td>\n",
       "      <td>46.3</td>\n",
       "      <td>47.2</td>\n",
       "      <td>40.2</td>\n",
       "      <td>50.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region  8</td>\n",
       "      <td>45.3</td>\n",
       "      <td>42.9</td>\n",
       "      <td>47.0</td>\n",
       "      <td>47.7</td>\n",
       "      <td>48.6</td>\n",
       "      <td>47.1</td>\n",
       "      <td>47.1</td>\n",
       "      <td>43.0</td>\n",
       "      <td>49.5</td>\n",
       "      <td>53.5</td>\n",
       "      <td>...</td>\n",
       "      <td>46.2</td>\n",
       "      <td>44.3</td>\n",
       "      <td>43.5</td>\n",
       "      <td>49.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>53.0</td>\n",
       "      <td>48.3</td>\n",
       "      <td>53.9</td>\n",
       "      <td>44.4</td>\n",
       "      <td>50.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region  9</td>\n",
       "      <td>39.6</td>\n",
       "      <td>38.7</td>\n",
       "      <td>43.5</td>\n",
       "      <td>46.5</td>\n",
       "      <td>43.8</td>\n",
       "      <td>43.2</td>\n",
       "      <td>46.4</td>\n",
       "      <td>39.9</td>\n",
       "      <td>46.7</td>\n",
       "      <td>50.8</td>\n",
       "      <td>...</td>\n",
       "      <td>43.9</td>\n",
       "      <td>39.4</td>\n",
       "      <td>38.3</td>\n",
       "      <td>47.7</td>\n",
       "      <td>51.8</td>\n",
       "      <td>48.5</td>\n",
       "      <td>44.3</td>\n",
       "      <td>50.3</td>\n",
       "      <td>43.3</td>\n",
       "      <td>45.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region 10</td>\n",
       "      <td>40.0</td>\n",
       "      <td>39.4</td>\n",
       "      <td>43.8</td>\n",
       "      <td>44.9</td>\n",
       "      <td>46.7</td>\n",
       "      <td>44.5</td>\n",
       "      <td>44.2</td>\n",
       "      <td>44.1</td>\n",
       "      <td>50.2</td>\n",
       "      <td>45.5</td>\n",
       "      <td>...</td>\n",
       "      <td>49.6</td>\n",
       "      <td>36.2</td>\n",
       "      <td>38.5</td>\n",
       "      <td>45.6</td>\n",
       "      <td>45.1</td>\n",
       "      <td>46.3</td>\n",
       "      <td>45.2</td>\n",
       "      <td>45.3</td>\n",
       "      <td>44.8</td>\n",
       "      <td>52.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 156 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                2010-11  2011-12  2012-13  2013-14  2014-15  2015-16  2016-17  \\\n",
       "Names                                                                           \n",
       "Alabama            42.4     41.6     45.7     48.3     45.8     43.9     43.9   \n",
       "Alaska             37.0     34.7     39.7     41.7     44.0     37.9     39.1   \n",
       "Arizona            41.9     38.2     38.3     38.5     41.6     41.5     41.8   \n",
       "Arkansas           44.2     46.6     47.0     49.7     50.5     45.4     46.2   \n",
       "California         38.9     40.5     44.2       NR     44.2     43.7     48.0   \n",
       "...                 ...      ...      ...      ...      ...      ...      ...   \n",
       "Region  7          45.9     45.7     46.8     48.5     50.6     48.0     48.0   \n",
       "Region  8          45.3     42.9     47.0     47.7     48.6     47.1     47.1   \n",
       "Region  9          39.6     38.7     43.5     46.5     43.8     43.2     46.4   \n",
       "Region 10          40.0     39.4     43.8     44.9     46.7     44.5     44.2   \n",
       "HP 2020 Target     70.0     70.0     70.0     70.0     70.0     70.0     70.0   \n",
       "\n",
       "                2017-18  2018-19  2010-11  ...  2018-19  2010-11  2011-12  \\\n",
       "Names                                      ...                              \n",
       "Alabama            42.4     48.3     45.9  ...     64.2     39.2     45.2   \n",
       "Alaska             40.2     44.1     46.8  ...     33.0     40.7     37.7   \n",
       "Arizona            38.9     45.6     49.0  ...     41.9     47.2     48.2   \n",
       "Arkansas           41.7     48.8     56.1  ...     50.7     47.3     53.2   \n",
       "California         40.0     47.4     50.7  ...     44.6     37.0     34.6   \n",
       "...                 ...      ...      ...  ...      ...      ...      ...   \n",
       "Region  7          43.9     51.7     48.6  ...     51.5     50.8     43.8   \n",
       "Region  8          43.0     49.5     53.5  ...     46.2     44.3     43.5   \n",
       "Region  9          39.9     46.7     50.8  ...     43.9     39.4     38.3   \n",
       "Region 10          44.1     50.2     45.5  ...     49.6     36.2     38.5   \n",
       "HP 2020 Target     70.0     70.0     70.0  ...     70.0     70.0     70.0   \n",
       "\n",
       "                2012-13  2013-14  2014-15  2015-16  2016-17  2017-18  2018-19  \n",
       "Names                                                                          \n",
       "Alabama            44.0     46.6     42.9     45.8     38.9     42.9     36.5  \n",
       "Alaska             39.2     47.0     44.7     37.2     44.2     42.7     41.6  \n",
       "Arizona            39.4     42.0     43.4     46.9     39.6     46.0     46.3  \n",
       "Arkansas           36.6     41.5     54.3     39.6     50.2     39.8     36.1  \n",
       "California         48.3       NR     48.7     43.0     51.7     43.2     45.0  \n",
       "...                 ...      ...      ...      ...      ...      ...      ...  \n",
       "Region  7          50.2     46.4     50.2     46.3     47.2     40.2     50.3  \n",
       "Region  8          49.0     51.7     53.0     48.3     53.9     44.4     50.2  \n",
       "Region  9          47.7     51.8     48.5     44.3     50.3     43.3     45.7  \n",
       "Region 10          45.6     45.1     46.3     45.2     45.3     44.8     52.6  \n",
       "HP 2020 Target     70.0     70.0     70.0     70.0     70.0     70.0     70.0  \n",
       "\n",
       "[63 rows x 156 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# delete irrelevant columns\n",
    "df_clean.drop([\"LL\",\"UL\",\"CI\",\"SAMPLE\",\"TARGET\"],axis=1, inplace=True)\n",
    "df_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "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>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "      <th>2014-15</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Names</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",
       "      <td>Alabama</td>\n",
       "      <td>45.8</td>\n",
       "      <td>57.0</td>\n",
       "      <td>63.6</td>\n",
       "      <td>61.2</td>\n",
       "      <td>46.2</td>\n",
       "      <td>42.6</td>\n",
       "      <td>36.8</td>\n",
       "      <td>46.8</td>\n",
       "      <td>33.2</td>\n",
       "      <td>31.3</td>\n",
       "      <td>39.0</td>\n",
       "      <td>29.4</td>\n",
       "      <td>47.6</td>\n",
       "      <td>65.3</td>\n",
       "      <td>47.2</td>\n",
       "      <td>41.5</td>\n",
       "      <td>55.9</td>\n",
       "      <td>42.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Alaska</td>\n",
       "      <td>44.0</td>\n",
       "      <td>53.6</td>\n",
       "      <td>65.2</td>\n",
       "      <td>59.3</td>\n",
       "      <td>33.6</td>\n",
       "      <td>40.9</td>\n",
       "      <td>38.3</td>\n",
       "      <td>42.6</td>\n",
       "      <td>36.9</td>\n",
       "      <td>37.5</td>\n",
       "      <td>33.5</td>\n",
       "      <td>37.6</td>\n",
       "      <td>40.2</td>\n",
       "      <td>57.5</td>\n",
       "      <td>43.5</td>\n",
       "      <td>54.2</td>\n",
       "      <td>40.2</td>\n",
       "      <td>44.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.6</td>\n",
       "      <td>52.0</td>\n",
       "      <td>66.2</td>\n",
       "      <td>52.9</td>\n",
       "      <td>40.6</td>\n",
       "      <td>38.4</td>\n",
       "      <td>31.9</td>\n",
       "      <td>41.4</td>\n",
       "      <td>29.2</td>\n",
       "      <td>27.5</td>\n",
       "      <td>35.2</td>\n",
       "      <td>25.8</td>\n",
       "      <td>41.9</td>\n",
       "      <td>62.4</td>\n",
       "      <td>42.7</td>\n",
       "      <td>30.5</td>\n",
       "      <td>40.4</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Arkansas</td>\n",
       "      <td>50.5</td>\n",
       "      <td>66.0</td>\n",
       "      <td>67.1</td>\n",
       "      <td>71.9</td>\n",
       "      <td>57.3</td>\n",
       "      <td>45.8</td>\n",
       "      <td>39.1</td>\n",
       "      <td>40.9</td>\n",
       "      <td>38.8</td>\n",
       "      <td>37.0</td>\n",
       "      <td>37.3</td>\n",
       "      <td>37.3</td>\n",
       "      <td>43.4</td>\n",
       "      <td>69.6</td>\n",
       "      <td>50.5</td>\n",
       "      <td>43.0</td>\n",
       "      <td>68.4</td>\n",
       "      <td>54.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>California</td>\n",
       "      <td>44.2</td>\n",
       "      <td>59.2</td>\n",
       "      <td>72.0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>44.1</td>\n",
       "      <td>39.7</td>\n",
       "      <td>34.4</td>\n",
       "      <td>51.1</td>\n",
       "      <td>30.1</td>\n",
       "      <td>29.7</td>\n",
       "      <td>42.1</td>\n",
       "      <td>27.8</td>\n",
       "      <td>44.9</td>\n",
       "      <td>64.7</td>\n",
       "      <td>46.6</td>\n",
       "      <td>39.8</td>\n",
       "      <td>40.7</td>\n",
       "      <td>48.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>Region  7</td>\n",
       "      <td>50.6</td>\n",
       "      <td>56.4</td>\n",
       "      <td>69.7</td>\n",
       "      <td>58.5</td>\n",
       "      <td>43.3</td>\n",
       "      <td>48.9</td>\n",
       "      <td>42.7</td>\n",
       "      <td>53.4</td>\n",
       "      <td>39.5</td>\n",
       "      <td>37.8</td>\n",
       "      <td>44.2</td>\n",
       "      <td>36.5</td>\n",
       "      <td>51.8</td>\n",
       "      <td>72.7</td>\n",
       "      <td>51.0</td>\n",
       "      <td>46.7</td>\n",
       "      <td>50.7</td>\n",
       "      <td>50.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region  8</td>\n",
       "      <td>48.6</td>\n",
       "      <td>57.1</td>\n",
       "      <td>70.9</td>\n",
       "      <td>57.2</td>\n",
       "      <td>46.3</td>\n",
       "      <td>45.9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>50.6</td>\n",
       "      <td>39.0</td>\n",
       "      <td>37.9</td>\n",
       "      <td>45.0</td>\n",
       "      <td>37.1</td>\n",
       "      <td>47.7</td>\n",
       "      <td>68.6</td>\n",
       "      <td>48.6</td>\n",
       "      <td>50.8</td>\n",
       "      <td>46.2</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region  9</td>\n",
       "      <td>43.8</td>\n",
       "      <td>58.2</td>\n",
       "      <td>71.0</td>\n",
       "      <td>61.8</td>\n",
       "      <td>43.9</td>\n",
       "      <td>39.5</td>\n",
       "      <td>34.0</td>\n",
       "      <td>49.1</td>\n",
       "      <td>30.1</td>\n",
       "      <td>29.3</td>\n",
       "      <td>40.4</td>\n",
       "      <td>27.5</td>\n",
       "      <td>44.6</td>\n",
       "      <td>64.3</td>\n",
       "      <td>45.6</td>\n",
       "      <td>38.4</td>\n",
       "      <td>40.7</td>\n",
       "      <td>48.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region 10</td>\n",
       "      <td>46.7</td>\n",
       "      <td>56.2</td>\n",
       "      <td>68.5</td>\n",
       "      <td>58.2</td>\n",
       "      <td>42.6</td>\n",
       "      <td>44.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>45.8</td>\n",
       "      <td>37.1</td>\n",
       "      <td>35.9</td>\n",
       "      <td>39.9</td>\n",
       "      <td>35.0</td>\n",
       "      <td>45.5</td>\n",
       "      <td>64.8</td>\n",
       "      <td>46.3</td>\n",
       "      <td>48.0</td>\n",
       "      <td>50.1</td>\n",
       "      <td>46.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                2014-15  2014-15  2014-15  2014-15  2014-15  2014-15  2014-15  \\\n",
       "Names                                                                           \n",
       "Alabama            45.8     57.0     63.6     61.2     46.2     42.6     36.8   \n",
       "Alaska             44.0     53.6     65.2     59.3     33.6     40.9     38.3   \n",
       "Arizona            41.6     52.0     66.2     52.9     40.6     38.4     31.9   \n",
       "Arkansas           50.5     66.0     67.1     71.9     57.3     45.8     39.1   \n",
       "California         44.2     59.2     72.0     63.4     44.1     39.7     34.4   \n",
       "...                 ...      ...      ...      ...      ...      ...      ...   \n",
       "Region  7          50.6     56.4     69.7     58.5     43.3     48.9     42.7   \n",
       "Region  8          48.6     57.1     70.9     57.2     46.3     45.9     41.0   \n",
       "Region  9          43.8     58.2     71.0     61.8     43.9     39.5     34.0   \n",
       "Region 10          46.7     56.2     68.5     58.2     42.6     44.0     39.0   \n",
       "HP 2020 Target     70.0     70.0     70.0     70.0     70.0     70.0     70.0   \n",
       "\n",
       "                2014-15  2014-15  2014-15  2014-15  2014-15  2014-15  2014-15  \\\n",
       "Names                                                                           \n",
       "Alabama            46.8     33.2     31.3     39.0     29.4     47.6     65.3   \n",
       "Alaska             42.6     36.9     37.5     33.5     37.6     40.2     57.5   \n",
       "Arizona            41.4     29.2     27.5     35.2     25.8     41.9     62.4   \n",
       "Arkansas           40.9     38.8     37.0     37.3     37.3     43.4     69.6   \n",
       "California         51.1     30.1     29.7     42.1     27.8     44.9     64.7   \n",
       "...                 ...      ...      ...      ...      ...      ...      ...   \n",
       "Region  7          53.4     39.5     37.8     44.2     36.5     51.8     72.7   \n",
       "Region  8          50.6     39.0     37.9     45.0     37.1     47.7     68.6   \n",
       "Region  9          49.1     30.1     29.3     40.4     27.5     44.6     64.3   \n",
       "Region 10          45.8     37.1     35.9     39.9     35.0     45.5     64.8   \n",
       "HP 2020 Target     70.0     70.0     70.0     70.0     70.0     70.0     70.0   \n",
       "\n",
       "                2014-15  2014-15  2014-15  2014-15  \n",
       "Names                                               \n",
       "Alabama            47.2     41.5     55.9     42.9  \n",
       "Alaska             43.5     54.2     40.2     44.7  \n",
       "Arizona            42.7     30.5     40.4     43.4  \n",
       "Arkansas           50.5     43.0     68.4     54.3  \n",
       "California         46.6     39.8     40.7     48.7  \n",
       "...                 ...      ...      ...      ...  \n",
       "Region  7          51.0     46.7     50.7     50.2  \n",
       "Region  8          48.6     50.8     46.2     53.0  \n",
       "Region  9          45.6     38.4     40.7     48.5  \n",
       "Region 10          46.3     48.0     50.1     46.3  \n",
       "HP 2020 Target     70.0     70.0     70.0     70.0  \n",
       "\n",
       "[63 rows x 18 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pick up data for year 2014-15\n",
    "df_2014 = df_clean[[\"2014-15\"]]\n",
    "df_2014"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "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>&lt; 6 months</th>\n",
       "      <th>6 months - 17 years</th>\n",
       "      <th>6 months - 4 years</th>\n",
       "      <th>5-12 years</th>\n",
       "      <th>13-17 years</th>\n",
       "      <th>³18 years</th>\n",
       "      <th>18-64 years</th>\n",
       "      <th>18-64 years at high risk</th>\n",
       "      <th>18-64 years not at high risk</th>\n",
       "      <th>18-49 years</th>\n",
       "      <th>18-49 years at high risk</th>\n",
       "      <th>18-49 years not at high risk</th>\n",
       "      <th>50-64 years</th>\n",
       "      <th>&gt; 65 years</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other Races</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Names</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",
       "      <td>Alabama</td>\n",
       "      <td>45.8</td>\n",
       "      <td>57.0</td>\n",
       "      <td>63.6</td>\n",
       "      <td>61.2</td>\n",
       "      <td>46.2</td>\n",
       "      <td>42.6</td>\n",
       "      <td>36.8</td>\n",
       "      <td>46.8</td>\n",
       "      <td>33.2</td>\n",
       "      <td>31.3</td>\n",
       "      <td>39.0</td>\n",
       "      <td>29.4</td>\n",
       "      <td>47.6</td>\n",
       "      <td>65.3</td>\n",
       "      <td>47.2</td>\n",
       "      <td>41.5</td>\n",
       "      <td>55.9</td>\n",
       "      <td>42.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Alaska</td>\n",
       "      <td>44.0</td>\n",
       "      <td>53.6</td>\n",
       "      <td>65.2</td>\n",
       "      <td>59.3</td>\n",
       "      <td>33.6</td>\n",
       "      <td>40.9</td>\n",
       "      <td>38.3</td>\n",
       "      <td>42.6</td>\n",
       "      <td>36.9</td>\n",
       "      <td>37.5</td>\n",
       "      <td>33.5</td>\n",
       "      <td>37.6</td>\n",
       "      <td>40.2</td>\n",
       "      <td>57.5</td>\n",
       "      <td>43.5</td>\n",
       "      <td>54.2</td>\n",
       "      <td>40.2</td>\n",
       "      <td>44.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.6</td>\n",
       "      <td>52.0</td>\n",
       "      <td>66.2</td>\n",
       "      <td>52.9</td>\n",
       "      <td>40.6</td>\n",
       "      <td>38.4</td>\n",
       "      <td>31.9</td>\n",
       "      <td>41.4</td>\n",
       "      <td>29.2</td>\n",
       "      <td>27.5</td>\n",
       "      <td>35.2</td>\n",
       "      <td>25.8</td>\n",
       "      <td>41.9</td>\n",
       "      <td>62.4</td>\n",
       "      <td>42.7</td>\n",
       "      <td>30.5</td>\n",
       "      <td>40.4</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Arkansas</td>\n",
       "      <td>50.5</td>\n",
       "      <td>66.0</td>\n",
       "      <td>67.1</td>\n",
       "      <td>71.9</td>\n",
       "      <td>57.3</td>\n",
       "      <td>45.8</td>\n",
       "      <td>39.1</td>\n",
       "      <td>40.9</td>\n",
       "      <td>38.8</td>\n",
       "      <td>37.0</td>\n",
       "      <td>37.3</td>\n",
       "      <td>37.3</td>\n",
       "      <td>43.4</td>\n",
       "      <td>69.6</td>\n",
       "      <td>50.5</td>\n",
       "      <td>43.0</td>\n",
       "      <td>68.4</td>\n",
       "      <td>54.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>California</td>\n",
       "      <td>44.2</td>\n",
       "      <td>59.2</td>\n",
       "      <td>72.0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>44.1</td>\n",
       "      <td>39.7</td>\n",
       "      <td>34.4</td>\n",
       "      <td>51.1</td>\n",
       "      <td>30.1</td>\n",
       "      <td>29.7</td>\n",
       "      <td>42.1</td>\n",
       "      <td>27.8</td>\n",
       "      <td>44.9</td>\n",
       "      <td>64.7</td>\n",
       "      <td>46.6</td>\n",
       "      <td>39.8</td>\n",
       "      <td>40.7</td>\n",
       "      <td>48.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>Region  7</td>\n",
       "      <td>50.6</td>\n",
       "      <td>56.4</td>\n",
       "      <td>69.7</td>\n",
       "      <td>58.5</td>\n",
       "      <td>43.3</td>\n",
       "      <td>48.9</td>\n",
       "      <td>42.7</td>\n",
       "      <td>53.4</td>\n",
       "      <td>39.5</td>\n",
       "      <td>37.8</td>\n",
       "      <td>44.2</td>\n",
       "      <td>36.5</td>\n",
       "      <td>51.8</td>\n",
       "      <td>72.7</td>\n",
       "      <td>51.0</td>\n",
       "      <td>46.7</td>\n",
       "      <td>50.7</td>\n",
       "      <td>50.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region  8</td>\n",
       "      <td>48.6</td>\n",
       "      <td>57.1</td>\n",
       "      <td>70.9</td>\n",
       "      <td>57.2</td>\n",
       "      <td>46.3</td>\n",
       "      <td>45.9</td>\n",
       "      <td>41.0</td>\n",
       "      <td>50.6</td>\n",
       "      <td>39.0</td>\n",
       "      <td>37.9</td>\n",
       "      <td>45.0</td>\n",
       "      <td>37.1</td>\n",
       "      <td>47.7</td>\n",
       "      <td>68.6</td>\n",
       "      <td>48.6</td>\n",
       "      <td>50.8</td>\n",
       "      <td>46.2</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region  9</td>\n",
       "      <td>43.8</td>\n",
       "      <td>58.2</td>\n",
       "      <td>71.0</td>\n",
       "      <td>61.8</td>\n",
       "      <td>43.9</td>\n",
       "      <td>39.5</td>\n",
       "      <td>34.0</td>\n",
       "      <td>49.1</td>\n",
       "      <td>30.1</td>\n",
       "      <td>29.3</td>\n",
       "      <td>40.4</td>\n",
       "      <td>27.5</td>\n",
       "      <td>44.6</td>\n",
       "      <td>64.3</td>\n",
       "      <td>45.6</td>\n",
       "      <td>38.4</td>\n",
       "      <td>40.7</td>\n",
       "      <td>48.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Region 10</td>\n",
       "      <td>46.7</td>\n",
       "      <td>56.2</td>\n",
       "      <td>68.5</td>\n",
       "      <td>58.2</td>\n",
       "      <td>42.6</td>\n",
       "      <td>44.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>45.8</td>\n",
       "      <td>37.1</td>\n",
       "      <td>35.9</td>\n",
       "      <td>39.9</td>\n",
       "      <td>35.0</td>\n",
       "      <td>45.5</td>\n",
       "      <td>64.8</td>\n",
       "      <td>46.3</td>\n",
       "      <td>48.0</td>\n",
       "      <td>50.1</td>\n",
       "      <td>46.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                < 6 months  6 months - 17 years  6 months - 4 years  \\\n",
       "Names                                                                 \n",
       "Alabama               45.8                 57.0                63.6   \n",
       "Alaska                44.0                 53.6                65.2   \n",
       "Arizona               41.6                 52.0                66.2   \n",
       "Arkansas              50.5                 66.0                67.1   \n",
       "California            44.2                 59.2                72.0   \n",
       "...                    ...                  ...                 ...   \n",
       "Region  7             50.6                 56.4                69.7   \n",
       "Region  8             48.6                 57.1                70.9   \n",
       "Region  9             43.8                 58.2                71.0   \n",
       "Region 10             46.7                 56.2                68.5   \n",
       "HP 2020 Target        70.0                 70.0                70.0   \n",
       "\n",
       "                5-12 years  13-17 years  ³18 years  18-64 years  \\\n",
       "Names                                                             \n",
       "Alabama               61.2         46.2       42.6         36.8   \n",
       "Alaska                59.3         33.6       40.9         38.3   \n",
       "Arizona               52.9         40.6       38.4         31.9   \n",
       "Arkansas              71.9         57.3       45.8         39.1   \n",
       "California            63.4         44.1       39.7         34.4   \n",
       "...                    ...          ...        ...          ...   \n",
       "Region  7             58.5         43.3       48.9         42.7   \n",
       "Region  8             57.2         46.3       45.9         41.0   \n",
       "Region  9             61.8         43.9       39.5         34.0   \n",
       "Region 10             58.2         42.6       44.0         39.0   \n",
       "HP 2020 Target        70.0         70.0       70.0         70.0   \n",
       "\n",
       "                18-64 years at high risk  18-64 years not at high risk  \\\n",
       "Names                                                                    \n",
       "Alabama                             46.8                          33.2   \n",
       "Alaska                              42.6                          36.9   \n",
       "Arizona                             41.4                          29.2   \n",
       "Arkansas                            40.9                          38.8   \n",
       "California                          51.1                          30.1   \n",
       "...                                  ...                           ...   \n",
       "Region  7                           53.4                          39.5   \n",
       "Region  8                           50.6                          39.0   \n",
       "Region  9                           49.1                          30.1   \n",
       "Region 10                           45.8                          37.1   \n",
       "HP 2020 Target                      70.0                          70.0   \n",
       "\n",
       "                18-49 years  18-49 years at high risk  \\\n",
       "Names                                                   \n",
       "Alabama                31.3                      39.0   \n",
       "Alaska                 37.5                      33.5   \n",
       "Arizona                27.5                      35.2   \n",
       "Arkansas               37.0                      37.3   \n",
       "California             29.7                      42.1   \n",
       "...                     ...                       ...   \n",
       "Region  7              37.8                      44.2   \n",
       "Region  8              37.9                      45.0   \n",
       "Region  9              29.3                      40.4   \n",
       "Region 10              35.9                      39.9   \n",
       "HP 2020 Target         70.0                      70.0   \n",
       "\n",
       "                18-49 years not at high risk  50-64 years  > 65 years  White  \\\n",
       "Names                                                                          \n",
       "Alabama                                 29.4         47.6        65.3   47.2   \n",
       "Alaska                                  37.6         40.2        57.5   43.5   \n",
       "Arizona                                 25.8         41.9        62.4   42.7   \n",
       "Arkansas                                37.3         43.4        69.6   50.5   \n",
       "California                              27.8         44.9        64.7   46.6   \n",
       "...                                      ...          ...         ...    ...   \n",
       "Region  7                               36.5         51.8        72.7   51.0   \n",
       "Region  8                               37.1         47.7        68.6   48.6   \n",
       "Region  9                               27.5         44.6        64.3   45.6   \n",
       "Region 10                               35.0         45.5        64.8   46.3   \n",
       "HP 2020 Target                          70.0         70.0        70.0   70.0   \n",
       "\n",
       "               Black  Hispanic  Other Races  \n",
       "Names                                        \n",
       "Alabama         41.5      55.9         42.9  \n",
       "Alaska          54.2      40.2         44.7  \n",
       "Arizona         30.5      40.4         43.4  \n",
       "Arkansas        43.0      68.4         54.3  \n",
       "California      39.8      40.7         48.7  \n",
       "...              ...       ...          ...  \n",
       "Region  7       46.7      50.7         50.2  \n",
       "Region  8       50.8      46.2         53.0  \n",
       "Region  9       38.4      40.7         48.5  \n",
       "Region 10       48.0      50.1         46.3  \n",
       "HP 2020 Target  70.0      70.0         70.0  \n",
       "\n",
       "[63 rows x 18 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define each columns \n",
    "df_2014.columns = ['< 6 months', '6 months - 17 years', '6 months - 4 years', '5-12 years', \n",
    "                   '13-17 years', '³18 years','18-64 years','18-64 years at high risk',\n",
    "                   '18-64 years not at high risk','18-49 years','18-49 years at high risk',\n",
    "                   '18-49 years not at high risk','50-64 years','> 65 years','White',\n",
    "                   'Black','Hispanic','Other Races']\n",
    "df_2014"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "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>Names</th>\n",
       "      <th>&lt; 6 months</th>\n",
       "      <th>6 months - 4 years</th>\n",
       "      <th>5-12 years</th>\n",
       "      <th>13-17 years</th>\n",
       "      <th>18-49 years</th>\n",
       "      <th>50-64 years</th>\n",
       "      <th>&gt; 65 years</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other Races</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>45.8</td>\n",
       "      <td>63.6</td>\n",
       "      <td>61.2</td>\n",
       "      <td>46.2</td>\n",
       "      <td>31.3</td>\n",
       "      <td>47.6</td>\n",
       "      <td>65.3</td>\n",
       "      <td>47.2</td>\n",
       "      <td>41.5</td>\n",
       "      <td>55.9</td>\n",
       "      <td>42.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>44.0</td>\n",
       "      <td>65.2</td>\n",
       "      <td>59.3</td>\n",
       "      <td>33.6</td>\n",
       "      <td>37.5</td>\n",
       "      <td>40.2</td>\n",
       "      <td>57.5</td>\n",
       "      <td>43.5</td>\n",
       "      <td>54.2</td>\n",
       "      <td>40.2</td>\n",
       "      <td>44.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.6</td>\n",
       "      <td>66.2</td>\n",
       "      <td>52.9</td>\n",
       "      <td>40.6</td>\n",
       "      <td>27.5</td>\n",
       "      <td>41.9</td>\n",
       "      <td>62.4</td>\n",
       "      <td>42.7</td>\n",
       "      <td>30.5</td>\n",
       "      <td>40.4</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>50.5</td>\n",
       "      <td>67.1</td>\n",
       "      <td>71.9</td>\n",
       "      <td>57.3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>43.4</td>\n",
       "      <td>69.6</td>\n",
       "      <td>50.5</td>\n",
       "      <td>43.0</td>\n",
       "      <td>68.4</td>\n",
       "      <td>54.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>44.2</td>\n",
       "      <td>72.0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>44.1</td>\n",
       "      <td>29.7</td>\n",
       "      <td>44.9</td>\n",
       "      <td>64.7</td>\n",
       "      <td>46.6</td>\n",
       "      <td>39.8</td>\n",
       "      <td>40.7</td>\n",
       "      <td>48.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>50.6</td>\n",
       "      <td>69.7</td>\n",
       "      <td>58.5</td>\n",
       "      <td>43.3</td>\n",
       "      <td>37.8</td>\n",
       "      <td>51.8</td>\n",
       "      <td>72.7</td>\n",
       "      <td>51.0</td>\n",
       "      <td>46.7</td>\n",
       "      <td>50.7</td>\n",
       "      <td>50.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>48.6</td>\n",
       "      <td>70.9</td>\n",
       "      <td>57.2</td>\n",
       "      <td>46.3</td>\n",
       "      <td>37.9</td>\n",
       "      <td>47.7</td>\n",
       "      <td>68.6</td>\n",
       "      <td>48.6</td>\n",
       "      <td>50.8</td>\n",
       "      <td>46.2</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>43.8</td>\n",
       "      <td>71.0</td>\n",
       "      <td>61.8</td>\n",
       "      <td>43.9</td>\n",
       "      <td>29.3</td>\n",
       "      <td>44.6</td>\n",
       "      <td>64.3</td>\n",
       "      <td>45.6</td>\n",
       "      <td>38.4</td>\n",
       "      <td>40.7</td>\n",
       "      <td>48.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>46.7</td>\n",
       "      <td>68.5</td>\n",
       "      <td>58.2</td>\n",
       "      <td>42.6</td>\n",
       "      <td>35.9</td>\n",
       "      <td>45.5</td>\n",
       "      <td>64.8</td>\n",
       "      <td>46.3</td>\n",
       "      <td>48.0</td>\n",
       "      <td>50.1</td>\n",
       "      <td>46.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Names  < 6 months  6 months - 4 years  5-12 years  13-17 years  \\\n",
       "0          Alabama        45.8                63.6        61.2         46.2   \n",
       "1           Alaska        44.0                65.2        59.3         33.6   \n",
       "2          Arizona        41.6                66.2        52.9         40.6   \n",
       "3         Arkansas        50.5                67.1        71.9         57.3   \n",
       "4       California        44.2                72.0        63.4         44.1   \n",
       "..             ...         ...                 ...         ...          ...   \n",
       "58       Region  7        50.6                69.7        58.5         43.3   \n",
       "59       Region  8        48.6                70.9        57.2         46.3   \n",
       "60       Region  9        43.8                71.0        61.8         43.9   \n",
       "61       Region 10        46.7                68.5        58.2         42.6   \n",
       "62  HP 2020 Target        70.0                70.0        70.0         70.0   \n",
       "\n",
       "    18-49 years  50-64 years  > 65 years  White Black  Hispanic  Other Races  \n",
       "0          31.3         47.6        65.3   47.2  41.5      55.9         42.9  \n",
       "1          37.5         40.2        57.5   43.5  54.2      40.2         44.7  \n",
       "2          27.5         41.9        62.4   42.7  30.5      40.4         43.4  \n",
       "3          37.0         43.4        69.6   50.5  43.0      68.4         54.3  \n",
       "4          29.7         44.9        64.7   46.6  39.8      40.7         48.7  \n",
       "..          ...          ...         ...    ...   ...       ...          ...  \n",
       "58         37.8         51.8        72.7   51.0  46.7      50.7         50.2  \n",
       "59         37.9         47.7        68.6   48.6  50.8      46.2         53.0  \n",
       "60         29.3         44.6        64.3   45.6  38.4      40.7         48.5  \n",
       "61         35.9         45.5        64.8   46.3  48.0      50.1         46.3  \n",
       "62         70.0         70.0        70.0   70.0  70.0      70.0         70.0  \n",
       "\n",
       "[63 rows x 12 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# delete the overlapped bins \n",
    "df_2014_clean = df_2014[['< 6 months','6 months - 4 years','5-12 years','13-17 years',\n",
    "                        '18-49 years','50-64 years','> 65 years','White','Black','Hispanic','Other Races']]\n",
    "df_2014_clean = df_2014_clean.reset_index()\n",
    "df_2014_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "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>Names</th>\n",
       "      <th>&lt; 6 months</th>\n",
       "      <th>6 months - 4 years</th>\n",
       "      <th>5-12 years</th>\n",
       "      <th>13-17 years</th>\n",
       "      <th>18-49 years</th>\n",
       "      <th>50-64 years</th>\n",
       "      <th>&gt; 65 years</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other Races</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>43.9</td>\n",
       "      <td>70.2</td>\n",
       "      <td>64.8</td>\n",
       "      <td>50.0</td>\n",
       "      <td>29.1</td>\n",
       "      <td>40.8</td>\n",
       "      <td>61.1</td>\n",
       "      <td>43.4</td>\n",
       "      <td>42.6</td>\n",
       "      <td>61.1</td>\n",
       "      <td>45.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>37.9</td>\n",
       "      <td>56.7</td>\n",
       "      <td>49.4</td>\n",
       "      <td>39.7</td>\n",
       "      <td>28.8</td>\n",
       "      <td>39.0</td>\n",
       "      <td>51.5</td>\n",
       "      <td>38.1</td>\n",
       "      <td>51.6</td>\n",
       "      <td>35.5</td>\n",
       "      <td>37.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.5</td>\n",
       "      <td>58.0</td>\n",
       "      <td>57.2</td>\n",
       "      <td>40.1</td>\n",
       "      <td>29.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>59.4</td>\n",
       "      <td>41.7</td>\n",
       "      <td>46.2</td>\n",
       "      <td>39.0</td>\n",
       "      <td>46.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>45.4</td>\n",
       "      <td>74.3</td>\n",
       "      <td>70.1</td>\n",
       "      <td>54.5</td>\n",
       "      <td>28.5</td>\n",
       "      <td>43.9</td>\n",
       "      <td>59.1</td>\n",
       "      <td>45.1</td>\n",
       "      <td>45.9</td>\n",
       "      <td>55.5</td>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>43.7</td>\n",
       "      <td>70.0</td>\n",
       "      <td>63.5</td>\n",
       "      <td>47.4</td>\n",
       "      <td>32.5</td>\n",
       "      <td>38.3</td>\n",
       "      <td>61.1</td>\n",
       "      <td>47.8</td>\n",
       "      <td>40.7</td>\n",
       "      <td>41.1</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>48.0</td>\n",
       "      <td>70.5</td>\n",
       "      <td>63.6</td>\n",
       "      <td>42.9</td>\n",
       "      <td>34.1</td>\n",
       "      <td>47.2</td>\n",
       "      <td>68.6</td>\n",
       "      <td>48.3</td>\n",
       "      <td>52.0</td>\n",
       "      <td>45.5</td>\n",
       "      <td>46.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>47.1</td>\n",
       "      <td>70.3</td>\n",
       "      <td>59.8</td>\n",
       "      <td>45.6</td>\n",
       "      <td>35.2</td>\n",
       "      <td>47.9</td>\n",
       "      <td>65.6</td>\n",
       "      <td>47.2</td>\n",
       "      <td>53.2</td>\n",
       "      <td>46.7</td>\n",
       "      <td>48.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>43.2</td>\n",
       "      <td>68.2</td>\n",
       "      <td>62.5</td>\n",
       "      <td>46.0</td>\n",
       "      <td>31.7</td>\n",
       "      <td>38.4</td>\n",
       "      <td>60.9</td>\n",
       "      <td>45.5</td>\n",
       "      <td>40.3</td>\n",
       "      <td>40.9</td>\n",
       "      <td>44.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>44.5</td>\n",
       "      <td>69.3</td>\n",
       "      <td>57.1</td>\n",
       "      <td>44.1</td>\n",
       "      <td>32.1</td>\n",
       "      <td>43.9</td>\n",
       "      <td>62.0</td>\n",
       "      <td>44.9</td>\n",
       "      <td>41.6</td>\n",
       "      <td>43.6</td>\n",
       "      <td>45.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Names  < 6 months  6 months - 4 years  5-12 years  13-17 years  \\\n",
       "0          Alabama        43.9                70.2        64.8         50.0   \n",
       "1           Alaska        37.9                56.7        49.4         39.7   \n",
       "2          Arizona        41.5                58.0        57.2         40.1   \n",
       "3         Arkansas        45.4                74.3        70.1         54.5   \n",
       "4       California        43.7                70.0        63.5         47.4   \n",
       "..             ...         ...                 ...         ...          ...   \n",
       "58       Region  7        48.0                70.5        63.6         42.9   \n",
       "59       Region  8        47.1                70.3        59.8         45.6   \n",
       "60       Region  9        43.2                68.2        62.5         46.0   \n",
       "61       Region 10        44.5                69.3        57.1         44.1   \n",
       "62  HP 2020 Target        70.0                70.0        70.0         70.0   \n",
       "\n",
       "    18-49 years  50-64 years  > 65 years  White Black  Hispanic  Other Races  \n",
       "0          29.1         40.8        61.1   43.4  42.6      61.1         45.8  \n",
       "1          28.8         39.0        51.5   38.1  51.6      35.5         37.2  \n",
       "2          29.0         39.0        59.4   41.7  46.2      39.0         46.9  \n",
       "3          28.5         43.9        59.1   45.1  45.9      55.5         39.6  \n",
       "4          32.5         38.3        61.1   47.8  40.7      41.1         43.0  \n",
       "..          ...          ...         ...    ...   ...       ...          ...  \n",
       "58         34.1         47.2        68.6   48.3  52.0      45.5         46.3  \n",
       "59         35.2         47.9        65.6   47.2  53.2      46.7         48.3  \n",
       "60         31.7         38.4        60.9   45.5  40.3      40.9         44.3  \n",
       "61         32.1         43.9        62.0   44.9  41.6      43.6         45.2  \n",
       "62         70.0         70.0        70.0   70.0  70.0      70.0         70.0  \n",
       "\n",
       "[63 rows x 12 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# repeat the steps above to get dataframe for year 2015-16,2016-17,2017-18,2018-19\n",
    "df_2015 = df_clean[[\"2015-16\"]]\n",
    "df_2015.columns = ['< 6 months', '6 months - 17 years', '6 months - 4 years', '5-12 years', \n",
    "                   '13-17 years', '³18 years','18-64 years','18-64 years at high risk',\n",
    "                   '18-64 years not at high risk','18-49 years','18-49 years at high risk',\n",
    "                   '18-49 years not at high risk','50-64 years','> 65 years','White',\n",
    "                   'Black','Hispanic','Other Races']\n",
    "df_2015_clean = df_2015[['< 6 months','6 months - 4 years','5-12 years','13-17 years',\n",
    "                        '18-49 years','50-64 years','> 65 years','White','Black','Hispanic','Other Races']]\n",
    "df_2015_clean = df_2015_clean.reset_index()\n",
    "df_2015_clean\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": false
   },
   "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>Names</th>\n",
       "      <th>&lt; 6 months</th>\n",
       "      <th>6 months - 4 years</th>\n",
       "      <th>5-12 years</th>\n",
       "      <th>13-17 years</th>\n",
       "      <th>18-49 years</th>\n",
       "      <th>50-64 years</th>\n",
       "      <th>&gt; 65 years</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other Races</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>43.9</td>\n",
       "      <td>68.4</td>\n",
       "      <td>55.1</td>\n",
       "      <td>43.2</td>\n",
       "      <td>28.5</td>\n",
       "      <td>47.5</td>\n",
       "      <td>63.0</td>\n",
       "      <td>45.2</td>\n",
       "      <td>39.2</td>\n",
       "      <td>55.4</td>\n",
       "      <td>38.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>39.1</td>\n",
       "      <td>43.9</td>\n",
       "      <td>52.2</td>\n",
       "      <td>38.2</td>\n",
       "      <td>31.6</td>\n",
       "      <td>38.0</td>\n",
       "      <td>55.6</td>\n",
       "      <td>37.8</td>\n",
       "      <td>48.8</td>\n",
       "      <td>36.0</td>\n",
       "      <td>44.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>41.8</td>\n",
       "      <td>60.4</td>\n",
       "      <td>54.6</td>\n",
       "      <td>38.0</td>\n",
       "      <td>28.1</td>\n",
       "      <td>42.5</td>\n",
       "      <td>60.8</td>\n",
       "      <td>43.3</td>\n",
       "      <td>33.6</td>\n",
       "      <td>41.1</td>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>46.2</td>\n",
       "      <td>63.7</td>\n",
       "      <td>66.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>30.1</td>\n",
       "      <td>46.9</td>\n",
       "      <td>62.8</td>\n",
       "      <td>44.9</td>\n",
       "      <td>47.5</td>\n",
       "      <td>53.1</td>\n",
       "      <td>50.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>48.0</td>\n",
       "      <td>73.3</td>\n",
       "      <td>61.5</td>\n",
       "      <td>53.9</td>\n",
       "      <td>36.4</td>\n",
       "      <td>45.7</td>\n",
       "      <td>66.2</td>\n",
       "      <td>50.4</td>\n",
       "      <td>37.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>51.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>48.0</td>\n",
       "      <td>69.3</td>\n",
       "      <td>58.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>46.1</td>\n",
       "      <td>68.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>44.8</td>\n",
       "      <td>51.7</td>\n",
       "      <td>47.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>47.1</td>\n",
       "      <td>69.7</td>\n",
       "      <td>59.5</td>\n",
       "      <td>42.8</td>\n",
       "      <td>35.3</td>\n",
       "      <td>47.1</td>\n",
       "      <td>66.2</td>\n",
       "      <td>46.2</td>\n",
       "      <td>50.8</td>\n",
       "      <td>48.0</td>\n",
       "      <td>53.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>46.4</td>\n",
       "      <td>70.4</td>\n",
       "      <td>59.7</td>\n",
       "      <td>50.3</td>\n",
       "      <td>34.4</td>\n",
       "      <td>45.0</td>\n",
       "      <td>64.4</td>\n",
       "      <td>47.9</td>\n",
       "      <td>36.7</td>\n",
       "      <td>44.9</td>\n",
       "      <td>50.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>44.2</td>\n",
       "      <td>64.1</td>\n",
       "      <td>54.8</td>\n",
       "      <td>42.6</td>\n",
       "      <td>32.3</td>\n",
       "      <td>44.0</td>\n",
       "      <td>62.4</td>\n",
       "      <td>44.0</td>\n",
       "      <td>46.9</td>\n",
       "      <td>45.3</td>\n",
       "      <td>45.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Names  < 6 months  6 months - 4 years  5-12 years  13-17 years  \\\n",
       "0          Alabama        43.9                68.4        55.1         43.2   \n",
       "1           Alaska        39.1                43.9        52.2         38.2   \n",
       "2          Arizona        41.8                60.4        54.6         38.0   \n",
       "3         Arkansas        46.2                63.7        66.0         49.0   \n",
       "4       California        48.0                73.3        61.5         53.9   \n",
       "..             ...         ...                 ...         ...          ...   \n",
       "58       Region  7        48.0                69.3        58.3         44.0   \n",
       "59       Region  8        47.1                69.7        59.5         42.8   \n",
       "60       Region  9        46.4                70.4        59.7         50.3   \n",
       "61       Region 10        44.2                64.1        54.8         42.6   \n",
       "62  HP 2020 Target        70.0                70.0        70.0         70.0   \n",
       "\n",
       "    18-49 years  50-64 years  > 65 years  White Black  Hispanic  Other Races  \n",
       "0          28.5         47.5        63.0   45.2  39.2      55.4         38.9  \n",
       "1          31.6         38.0        55.6   37.8  48.8      36.0         44.2  \n",
       "2          28.1         42.5        60.8   43.3  33.6      41.1         39.6  \n",
       "3          30.1         46.9        62.8   44.9  47.5      53.1         50.2  \n",
       "4          36.4         45.7        66.2   50.4  37.0      46.6         51.7  \n",
       "..          ...          ...         ...    ...   ...       ...          ...  \n",
       "58         36.0         46.1        68.0   48.0  44.8      51.7         47.2  \n",
       "59         35.3         47.1        66.2   46.2  50.8      48.0         53.9  \n",
       "60         34.4         45.0        64.4   47.9  36.7      44.9         50.3  \n",
       "61         32.3         44.0        62.4   44.0  46.9      45.3         45.3  \n",
       "62         70.0         70.0        70.0   70.0  70.0      70.0         70.0  \n",
       "\n",
       "[63 rows x 12 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2016 = df_clean[[\"2016-17\"]]\n",
    "df_2016.columns = ['< 6 months', '6 months - 17 years', '6 months - 4 years', '5-12 years', \n",
    "                   '13-17 years', '³18 years','18-64 years','18-64 years at high risk',\n",
    "                   '18-64 years not at high risk','18-49 years','18-49 years at high risk',\n",
    "                   '18-49 years not at high risk','50-64 years','> 65 years','White',\n",
    "                   'Black','Hispanic','Other Races']\n",
    "df_2016_clean = df_2016[['< 6 months','6 months - 4 years','5-12 years','13-17 years',\n",
    "                        '18-49 years','50-64 years','> 65 years','White','Black','Hispanic','Other Races']]\n",
    "df_2016_clean = df_2016_clean.reset_index()\n",
    "df_2016_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false
   },
   "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>Names</th>\n",
       "      <th>&lt; 6 months</th>\n",
       "      <th>6 months - 4 years</th>\n",
       "      <th>5-12 years</th>\n",
       "      <th>13-17 years</th>\n",
       "      <th>18-49 years</th>\n",
       "      <th>50-64 years</th>\n",
       "      <th>&gt; 65 years</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other Races</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>42.4</td>\n",
       "      <td>61.7</td>\n",
       "      <td>56.0</td>\n",
       "      <td>42.9</td>\n",
       "      <td>27.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>65.4</td>\n",
       "      <td>42.7</td>\n",
       "      <td>39.5</td>\n",
       "      <td>51.4</td>\n",
       "      <td>42.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>40.2</td>\n",
       "      <td>60.4</td>\n",
       "      <td>45.3</td>\n",
       "      <td>43.0</td>\n",
       "      <td>35.3</td>\n",
       "      <td>33.7</td>\n",
       "      <td>51.9</td>\n",
       "      <td>39.9</td>\n",
       "      <td>47.2</td>\n",
       "      <td>33.9</td>\n",
       "      <td>42.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>38.9</td>\n",
       "      <td>67.8</td>\n",
       "      <td>51.8</td>\n",
       "      <td>37.3</td>\n",
       "      <td>26.6</td>\n",
       "      <td>34.9</td>\n",
       "      <td>56.2</td>\n",
       "      <td>39.5</td>\n",
       "      <td>37.7</td>\n",
       "      <td>36.1</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>41.7</td>\n",
       "      <td>62.9</td>\n",
       "      <td>66.2</td>\n",
       "      <td>59.9</td>\n",
       "      <td>24.8</td>\n",
       "      <td>38.5</td>\n",
       "      <td>54.7</td>\n",
       "      <td>41.5</td>\n",
       "      <td>38.8</td>\n",
       "      <td>56.0</td>\n",
       "      <td>39.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>40.0</td>\n",
       "      <td>67.8</td>\n",
       "      <td>58.3</td>\n",
       "      <td>48.7</td>\n",
       "      <td>25.3</td>\n",
       "      <td>39.3</td>\n",
       "      <td>58.1</td>\n",
       "      <td>44.4</td>\n",
       "      <td>35.0</td>\n",
       "      <td>35.2</td>\n",
       "      <td>43.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>43.9</td>\n",
       "      <td>63.7</td>\n",
       "      <td>56.7</td>\n",
       "      <td>44.2</td>\n",
       "      <td>30.8</td>\n",
       "      <td>40.5</td>\n",
       "      <td>64.0</td>\n",
       "      <td>43.9</td>\n",
       "      <td>44.7</td>\n",
       "      <td>46.0</td>\n",
       "      <td>40.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>43.0</td>\n",
       "      <td>68.2</td>\n",
       "      <td>57.7</td>\n",
       "      <td>43.3</td>\n",
       "      <td>30.7</td>\n",
       "      <td>41.2</td>\n",
       "      <td>60.6</td>\n",
       "      <td>42.8</td>\n",
       "      <td>42.3</td>\n",
       "      <td>43.8</td>\n",
       "      <td>44.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>39.9</td>\n",
       "      <td>66.9</td>\n",
       "      <td>56.9</td>\n",
       "      <td>47.2</td>\n",
       "      <td>25.5</td>\n",
       "      <td>38.5</td>\n",
       "      <td>58.4</td>\n",
       "      <td>43.3</td>\n",
       "      <td>35.4</td>\n",
       "      <td>35.2</td>\n",
       "      <td>43.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>44.1</td>\n",
       "      <td>67.2</td>\n",
       "      <td>56.7</td>\n",
       "      <td>48.5</td>\n",
       "      <td>31.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>61.1</td>\n",
       "      <td>44.9</td>\n",
       "      <td>47.4</td>\n",
       "      <td>39.8</td>\n",
       "      <td>44.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Names < 6 months  6 months - 4 years  5-12 years  13-17 years  \\\n",
       "0          Alabama       42.4                61.7        56.0         42.9   \n",
       "1           Alaska       40.2                60.4        45.3         43.0   \n",
       "2          Arizona       38.9                67.8        51.8         37.3   \n",
       "3         Arkansas       41.7                62.9        66.2         59.9   \n",
       "4       California       40.0                67.8        58.3         48.7   \n",
       "..             ...        ...                 ...         ...          ...   \n",
       "58       Region  7       43.9                63.7        56.7         44.2   \n",
       "59       Region  8       43.0                68.2        57.7         43.3   \n",
       "60       Region  9       39.9                66.9        56.9         47.2   \n",
       "61       Region 10       44.1                67.2        56.7         48.5   \n",
       "62  HP 2020 Target       70.0                70.0        70.0         70.0   \n",
       "\n",
       "   18-49 years 50-64 years > 65 years White Black Hispanic Other Races  \n",
       "0         27.0        41.0       65.4  42.7  39.5     51.4        42.9  \n",
       "1         35.3        33.7       51.9  39.9  47.2     33.9        42.7  \n",
       "2         26.6        34.9       56.2  39.5  37.7     36.1        46.0  \n",
       "3         24.8        38.5       54.7  41.5  38.8     56.0        39.8  \n",
       "4         25.3        39.3       58.1  44.4  35.0     35.2        43.2  \n",
       "..         ...         ...        ...   ...   ...      ...         ...  \n",
       "58        30.8        40.5       64.0  43.9  44.7     46.0        40.2  \n",
       "59        30.7        41.2       60.6  42.8  42.3     43.8        44.4  \n",
       "60        25.5        38.5       58.4  43.3  35.4     35.2        43.3  \n",
       "61        31.0        43.6       61.1  44.9  47.4     39.8        44.8  \n",
       "62        70.0        70.0       70.0  70.0  70.0     70.0        70.0  \n",
       "\n",
       "[63 rows x 12 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017 = df_clean[[\"2017-18\"]]\n",
    "df_2017.columns = ['< 6 months', '6 months - 17 years', '6 months - 4 years', '5-12 years', \n",
    "                   '13-17 years', '³18 years','18-64 years','18-64 years at high risk',\n",
    "                   '18-64 years not at high risk','18-49 years','18-49 years at high risk',\n",
    "                   '18-49 years not at high risk','50-64 years','> 65 years','White',\n",
    "                   'Black','Hispanic','Other Races']\n",
    "df_2017_clean = df_2017[['< 6 months','6 months - 4 years','5-12 years','13-17 years',\n",
    "                        '18-49 years','50-64 years','> 65 years','White','Black','Hispanic','Other Races']]\n",
    "df_2017_clean = df_2017_clean.reset_index()\n",
    "df_2017_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "scrolled": true
   },
   "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>Names</th>\n",
       "      <th>&lt; 6 months</th>\n",
       "      <th>6 months - 4 years</th>\n",
       "      <th>5-12 years</th>\n",
       "      <th>13-17 years</th>\n",
       "      <th>18-49 years</th>\n",
       "      <th>50-64 years</th>\n",
       "      <th>&gt; 65 years</th>\n",
       "      <th>White</th>\n",
       "      <th>Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other Races</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>48.3</td>\n",
       "      <td>68.7</td>\n",
       "      <td>64.0</td>\n",
       "      <td>48.2</td>\n",
       "      <td>30.7</td>\n",
       "      <td>51.5</td>\n",
       "      <td>71.5</td>\n",
       "      <td>50.1</td>\n",
       "      <td>43.4</td>\n",
       "      <td>64.2</td>\n",
       "      <td>36.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>44.1</td>\n",
       "      <td>63.8</td>\n",
       "      <td>56.2</td>\n",
       "      <td>44.7</td>\n",
       "      <td>34.8</td>\n",
       "      <td>40.4</td>\n",
       "      <td>61.2</td>\n",
       "      <td>45.4</td>\n",
       "      <td>48.2</td>\n",
       "      <td>33.0</td>\n",
       "      <td>41.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>45.6</td>\n",
       "      <td>63.3</td>\n",
       "      <td>58.6</td>\n",
       "      <td>46.8</td>\n",
       "      <td>31.6</td>\n",
       "      <td>45.5</td>\n",
       "      <td>64.4</td>\n",
       "      <td>47.1</td>\n",
       "      <td>48.6</td>\n",
       "      <td>41.9</td>\n",
       "      <td>46.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>48.8</td>\n",
       "      <td>69.2</td>\n",
       "      <td>64.5</td>\n",
       "      <td>57.0</td>\n",
       "      <td>34.4</td>\n",
       "      <td>47.7</td>\n",
       "      <td>62.8</td>\n",
       "      <td>49.5</td>\n",
       "      <td>46.6</td>\n",
       "      <td>50.7</td>\n",
       "      <td>36.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>California</td>\n",
       "      <td>47.4</td>\n",
       "      <td>77.1</td>\n",
       "      <td>66.6</td>\n",
       "      <td>49.9</td>\n",
       "      <td>33.7</td>\n",
       "      <td>43.8</td>\n",
       "      <td>69.0</td>\n",
       "      <td>52.3</td>\n",
       "      <td>40.8</td>\n",
       "      <td>44.6</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\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",
       "      <td>58</td>\n",
       "      <td>Region  7</td>\n",
       "      <td>51.7</td>\n",
       "      <td>72.6</td>\n",
       "      <td>62.7</td>\n",
       "      <td>51.5</td>\n",
       "      <td>38.6</td>\n",
       "      <td>49.9</td>\n",
       "      <td>71.5</td>\n",
       "      <td>52.5</td>\n",
       "      <td>44.8</td>\n",
       "      <td>51.5</td>\n",
       "      <td>50.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>Region  8</td>\n",
       "      <td>49.5</td>\n",
       "      <td>70.3</td>\n",
       "      <td>62.0</td>\n",
       "      <td>50.1</td>\n",
       "      <td>36.9</td>\n",
       "      <td>49.8</td>\n",
       "      <td>68.0</td>\n",
       "      <td>49.6</td>\n",
       "      <td>58.9</td>\n",
       "      <td>46.2</td>\n",
       "      <td>50.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>Region  9</td>\n",
       "      <td>46.7</td>\n",
       "      <td>74.4</td>\n",
       "      <td>64.4</td>\n",
       "      <td>48.9</td>\n",
       "      <td>32.7</td>\n",
       "      <td>43.6</td>\n",
       "      <td>68.0</td>\n",
       "      <td>50.5</td>\n",
       "      <td>40.0</td>\n",
       "      <td>43.9</td>\n",
       "      <td>45.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>Region 10</td>\n",
       "      <td>50.2</td>\n",
       "      <td>74.3</td>\n",
       "      <td>61.7</td>\n",
       "      <td>48.0</td>\n",
       "      <td>37.1</td>\n",
       "      <td>49.5</td>\n",
       "      <td>69.4</td>\n",
       "      <td>50.3</td>\n",
       "      <td>47.1</td>\n",
       "      <td>49.6</td>\n",
       "      <td>52.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>HP 2020 Target</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Names < 6 months  6 months - 4 years  5-12 years  13-17 years  \\\n",
       "0          Alabama       48.3                68.7        64.0         48.2   \n",
       "1           Alaska       44.1                63.8        56.2         44.7   \n",
       "2          Arizona       45.6                63.3        58.6         46.8   \n",
       "3         Arkansas       48.8                69.2        64.5         57.0   \n",
       "4       California       47.4                77.1        66.6         49.9   \n",
       "..             ...        ...                 ...         ...          ...   \n",
       "58       Region  7       51.7                72.6        62.7         51.5   \n",
       "59       Region  8       49.5                70.3        62.0         50.1   \n",
       "60       Region  9       46.7                74.4        64.4         48.9   \n",
       "61       Region 10       50.2                74.3        61.7         48.0   \n",
       "62  HP 2020 Target       70.0                70.0        70.0         70.0   \n",
       "\n",
       "   18-49 years 50-64 years > 65 years White Black Hispanic Other Races  \n",
       "0         30.7        51.5       71.5  50.1  43.4     64.2        36.5  \n",
       "1         34.8        40.4       61.2  45.4  48.2     33.0        41.6  \n",
       "2         31.6        45.5       64.4  47.1  48.6     41.9        46.3  \n",
       "3         34.4        47.7       62.8  49.5  46.6     50.7        36.1  \n",
       "4         33.7        43.8       69.0  52.3  40.8     44.6        45.0  \n",
       "..         ...         ...        ...   ...   ...      ...         ...  \n",
       "58        38.6        49.9       71.5  52.5  44.8     51.5        50.3  \n",
       "59        36.9        49.8       68.0  49.6  58.9     46.2        50.2  \n",
       "60        32.7        43.6       68.0  50.5  40.0     43.9        45.7  \n",
       "61        37.1        49.5       69.4  50.3  47.1     49.6        52.6  \n",
       "62        70.0        70.0       70.0  70.0  70.0     70.0        70.0  \n",
       "\n",
       "[63 rows x 12 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2018 = df_clean[[\"2018-19\"]]\n",
    "df_2018.columns = ['< 6 months', '6 months - 17 years', '6 months - 4 years', '5-12 years', \n",
    "                   '13-17 years', '³18 years','18-64 years','18-64 years at high risk',\n",
    "                   '18-64 years not at high risk','18-49 years','18-49 years at high risk',\n",
    "                   '18-49 years not at high risk','50-64 years','> 65 years','White',\n",
    "                   'Black','Hispanic','Other Races']\n",
    "df_2018_clean = df_2018[['< 6 months','6 months - 4 years','5-12 years','13-17 years',\n",
    "                        '18-49 years','50-64 years','> 65 years','White','Black','Hispanic','Other Races']]\n",
    "df_2018_clean = df_2018_clean.reset_index()\n",
    "df_2018_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# output each dataset\n",
    "df_2014_clean.to_csv(\"vaccination_2014-15.csv\", index=False, header=True)\n",
    "df_2015_clean.to_csv(\"vaccination_2015-16.csv\", index=False, header=True)\n",
    "df_2016_clean.to_csv(\"vaccination_2016-17.csv\", index=False, header=True)\n",
    "df_2017_clean.to_csv(\"vaccination_2017-18.csv\", index=False, header=True)\n",
    "df_2018_clean.to_csv(\"vaccination_2018-19.csv\", index=False, header=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}