data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import csv\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Explore the csv, featuring aid received, literacy rates, and GDP per capita. Clean the data. Many columns have \"..\" signalling that the data doesn't exist. This is particularly true of the literacy rates indicator, which, for many countries, does not record a statistic per year. For now, we'll replace those with zeroes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "country_aid_gdp_lit_df = pd.read_csv(\"data/2nd_try_aid_gdp_lit.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Reshape table so each year for each country is its own row. \n",
    "df1 = country_aid_gdp_lit_df.set_index(['Country Name', 'Series Name'])\n",
    "df1.columns.name = 'year'\n",
    "df1 = df1.stack().unstack('Series Name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Series Name</th>\n",
       "      <th>GDP per capita (current US$)</th>\n",
       "      <th>Literacy rate, adult total (% of people ages 15 and above)</th>\n",
       "      <th>Net official development assistance and official aid received (current US$)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country Name</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Afghanistan</th>\n",
       "      <th>1990</th>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>121720000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991</th>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>507530000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992</th>\n",
       "      <td>..</td>\n",
       "      <td>..</td>\n",
       "      <td>204900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Series Name       GDP per capita (current US$)  \\\n",
       "Country Name year                                \n",
       "Afghanistan  1990                           ..   \n",
       "             1991                           ..   \n",
       "             1992                           ..   \n",
       "\n",
       "Series Name       Literacy rate, adult total (% of people ages 15 and above)  \\\n",
       "Country Name year                                                              \n",
       "Afghanistan  1990                                                 ..           \n",
       "             1991                                                 ..           \n",
       "             1992                                                 ..           \n",
       "\n",
       "Series Name       Net official development assistance and official aid received (current US$)  \n",
       "Country Name year                                                                              \n",
       "Afghanistan  1990                                          121720000                           \n",
       "             1991                                          507530000                           \n",
       "             1992                                          204900000                           "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#replace \"..\" with zeros\n",
    "df1 = df1.replace(\"..\", \"0\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Series Name</th>\n",
       "      <th>GDP per capita (current US$)</th>\n",
       "      <th>Literacy rate, adult total (% of people ages 15 and above)</th>\n",
       "      <th>Net official development assistance and official aid received (current US$)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country Name</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Afghanistan</th>\n",
       "      <th>1990</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>121720000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>507530000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>204900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Series Name       GDP per capita (current US$)  \\\n",
       "Country Name year                                \n",
       "Afghanistan  1990                            0   \n",
       "             1991                            0   \n",
       "             1992                            0   \n",
       "\n",
       "Series Name       Literacy rate, adult total (% of people ages 15 and above)  \\\n",
       "Country Name year                                                              \n",
       "Afghanistan  1990                                                  0           \n",
       "             1991                                                  0           \n",
       "             1992                                                  0           \n",
       "\n",
       "Series Name       Net official development assistance and official aid received (current US$)  \n",
       "Country Name year                                                                              \n",
       "Afghanistan  1990                                          121720000                           \n",
       "             1991                                          507530000                           \n",
       "             1992                                          204900000                           "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series Name\n",
       "GDP per capita (current US$)                                                   object\n",
       "Literacy rate, adult total (% of people ages 15 and above)                     object\n",
       "Net official development assistance and official aid received (current US$)    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#lets take a look at the dtypes\n",
    "df1.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series Name\n",
       "Country Name                                                                   object\n",
       "year                                                                           object\n",
       "GDP per capita (current US$)                                                   object\n",
       "Literacy rate, adult total (% of people ages 15 and above)                     object\n",
       "Net official development assistance and official aid received (current US$)    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#all objects? Need to change that, also need to change column name\n",
    "df1 = df1.reset_index()\n",
    "df1.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1.index.names = ['id']\n",
    "df1.columns = ['country', 'year', 'gdp_per_capita', 'literacy_rate', 'net_aid_received']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "#time to change the column types\n",
    "df1['country'] = df1['country'].astype('str')\n",
    "df1['year'] = df1['year'].astype(\"int32\")\n",
    "df1['gdp_per_capita'] = df1['gdp_per_capita'].astype('float64')\n",
    "df1['literacy_rate'] = df1['literacy_rate'].astype('float64')\n",
    "df1['net_aid_received'] = df1['net_aid_received'].astype('float64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>gdp_per_capita</th>\n",
       "      <th>literacy_rate</th>\n",
       "      <th>net_aid_received</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1990</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121720000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1991</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>507530000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>204900000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        country  year  gdp_per_capita  literacy_rate  net_aid_received\n",
       "id                                                                    \n",
       "0   Afghanistan  1990             0.0            0.0       121720000.0\n",
       "1   Afghanistan  1991             0.0            0.0       507530000.0\n",
       "2   Afghanistan  1992             0.0            0.0       204900000.0"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country              object\n",
       "year                  int32\n",
       "gdp_per_capita      float64\n",
       "literacy_rate       float64\n",
       "net_aid_received    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.to_csv(\"clean_data/gdp_lit_aid.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now it's time to investigate aid spending per sector by year csv. Should not be as big as the previous csv. Move from wide to long. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2 = pd.read_csv(\"data/aid_spending_by_sector_per_yer.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Sector(s)</th>\n",
       "      <th>Time Period</th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>...</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I.1. Education, Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5664.71</td>\n",
       "      <td>4973.52</td>\n",
       "      <td>4793.35</td>\n",
       "      <td>4672.43</td>\n",
       "      <td>5376.85</td>\n",
       "      <td>6054.95</td>\n",
       "      <td>5261.22</td>\n",
       "      <td>4683.23</td>\n",
       "      <td>...</td>\n",
       "      <td>8440.51</td>\n",
       "      <td>9417.38</td>\n",
       "      <td>9329.86</td>\n",
       "      <td>9318.94</td>\n",
       "      <td>9780.09</td>\n",
       "      <td>8793.18</td>\n",
       "      <td>8227.74</td>\n",
       "      <td>9157.74</td>\n",
       "      <td>8050.54</td>\n",
       "      <td>8635.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>I.2. Health, Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1613.27</td>\n",
       "      <td>1424.84</td>\n",
       "      <td>1617.43</td>\n",
       "      <td>1654.98</td>\n",
       "      <td>1831.49</td>\n",
       "      <td>2171.42</td>\n",
       "      <td>2283.51</td>\n",
       "      <td>1667.18</td>\n",
       "      <td>...</td>\n",
       "      <td>4447.27</td>\n",
       "      <td>5077.02</td>\n",
       "      <td>4990.22</td>\n",
       "      <td>5108.05</td>\n",
       "      <td>6012.12</td>\n",
       "      <td>6160.02</td>\n",
       "      <td>5938.76</td>\n",
       "      <td>5324.30</td>\n",
       "      <td>6094.47</td>\n",
       "      <td>6393.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>I.5. Government &amp; Civil Society, Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1735.06</td>\n",
       "      <td>1387.19</td>\n",
       "      <td>1717.47</td>\n",
       "      <td>1300.48</td>\n",
       "      <td>1425.16</td>\n",
       "      <td>1792.34</td>\n",
       "      <td>1496.31</td>\n",
       "      <td>1130.09</td>\n",
       "      <td>...</td>\n",
       "      <td>11678.39</td>\n",
       "      <td>13221.55</td>\n",
       "      <td>13325.07</td>\n",
       "      <td>13983.96</td>\n",
       "      <td>14531.76</td>\n",
       "      <td>13014.89</td>\n",
       "      <td>12526.83</td>\n",
       "      <td>12952.03</td>\n",
       "      <td>11936.95</td>\n",
       "      <td>11924.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>II. Economic Infrastructure &amp; Services, Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8382.07</td>\n",
       "      <td>12182.65</td>\n",
       "      <td>8038.75</td>\n",
       "      <td>9591.39</td>\n",
       "      <td>10805.58</td>\n",
       "      <td>12880.17</td>\n",
       "      <td>11512.02</td>\n",
       "      <td>10127.12</td>\n",
       "      <td>...</td>\n",
       "      <td>12242.26</td>\n",
       "      <td>19522.05</td>\n",
       "      <td>15348.72</td>\n",
       "      <td>19487.56</td>\n",
       "      <td>16998.42</td>\n",
       "      <td>17974.14</td>\n",
       "      <td>20836.01</td>\n",
       "      <td>22160.40</td>\n",
       "      <td>22234.71</td>\n",
       "      <td>22004.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>II.5. Business &amp; Other Services, Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>394.33</td>\n",
       "      <td>308.94</td>\n",
       "      <td>430.42</td>\n",
       "      <td>346.42</td>\n",
       "      <td>1206.94</td>\n",
       "      <td>659.63</td>\n",
       "      <td>642.43</td>\n",
       "      <td>1008.88</td>\n",
       "      <td>...</td>\n",
       "      <td>1443.38</td>\n",
       "      <td>1631.89</td>\n",
       "      <td>1106.67</td>\n",
       "      <td>1309.79</td>\n",
       "      <td>1746.30</td>\n",
       "      <td>941.00</td>\n",
       "      <td>1245.62</td>\n",
       "      <td>2152.68</td>\n",
       "      <td>1238.58</td>\n",
       "      <td>1385.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Sector(s)  Time Period     1990  \\\n",
       "0                          I.1. Education, Total          NaN  5664.71   \n",
       "1                             I.2. Health, Total          NaN  1613.27   \n",
       "2         I.5. Government & Civil Society, Total          NaN  1735.06   \n",
       "3  II. Economic Infrastructure & Services, Total          NaN  8382.07   \n",
       "4         II.5. Business & Other Services, Total          NaN   394.33   \n",
       "\n",
       "       1991     1992     1993      1994      1995      1996      1997  \\\n",
       "0   4973.52  4793.35  4672.43   5376.85   6054.95   5261.22   4683.23   \n",
       "1   1424.84  1617.43  1654.98   1831.49   2171.42   2283.51   1667.18   \n",
       "2   1387.19  1717.47  1300.48   1425.16   1792.34   1496.31   1130.09   \n",
       "3  12182.65  8038.75  9591.39  10805.58  12880.17  11512.02  10127.12   \n",
       "4    308.94   430.42   346.42   1206.94    659.63    642.43   1008.88   \n",
       "\n",
       "     ...         2007      2008      2009      2010      2011      2012  \\\n",
       "0    ...      8440.51   9417.38   9329.86   9318.94   9780.09   8793.18   \n",
       "1    ...      4447.27   5077.02   4990.22   5108.05   6012.12   6160.02   \n",
       "2    ...     11678.39  13221.55  13325.07  13983.96  14531.76  13014.89   \n",
       "3    ...     12242.26  19522.05  15348.72  19487.56  16998.42  17974.14   \n",
       "4    ...      1443.38   1631.89   1106.67   1309.79   1746.30    941.00   \n",
       "\n",
       "       2013      2014      2015      2016  \n",
       "0   8227.74   9157.74   8050.54   8635.15  \n",
       "1   5938.76   5324.30   6094.47   6393.36  \n",
       "2  12526.83  12952.03  11936.95  11924.34  \n",
       "3  20836.01  22160.40  22234.71  22004.57  \n",
       "4   1245.62   2152.68   1238.58   1385.94  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 339,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#lets drop the time period column and reshape the whole thing with years as rows\n",
    "df2 = df2.drop(['Time Period'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.set_index(['Sector(s)'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>...</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sector(s)</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>I.1. Education, Total</th>\n",
       "      <td>5664.71</td>\n",
       "      <td>4973.52</td>\n",
       "      <td>4793.35</td>\n",
       "      <td>4672.43</td>\n",
       "      <td>5376.85</td>\n",
       "      <td>6054.95</td>\n",
       "      <td>5261.22</td>\n",
       "      <td>4683.23</td>\n",
       "      <td>4492.27</td>\n",
       "      <td>5038.70</td>\n",
       "      <td>...</td>\n",
       "      <td>8440.51</td>\n",
       "      <td>9417.38</td>\n",
       "      <td>9329.86</td>\n",
       "      <td>9318.94</td>\n",
       "      <td>9780.09</td>\n",
       "      <td>8793.18</td>\n",
       "      <td>8227.74</td>\n",
       "      <td>9157.74</td>\n",
       "      <td>8050.54</td>\n",
       "      <td>8635.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I.2. Health, Total</th>\n",
       "      <td>1613.27</td>\n",
       "      <td>1424.84</td>\n",
       "      <td>1617.43</td>\n",
       "      <td>1654.98</td>\n",
       "      <td>1831.49</td>\n",
       "      <td>2171.42</td>\n",
       "      <td>2283.51</td>\n",
       "      <td>1667.18</td>\n",
       "      <td>1593.50</td>\n",
       "      <td>1973.92</td>\n",
       "      <td>...</td>\n",
       "      <td>4447.27</td>\n",
       "      <td>5077.02</td>\n",
       "      <td>4990.22</td>\n",
       "      <td>5108.05</td>\n",
       "      <td>6012.12</td>\n",
       "      <td>6160.02</td>\n",
       "      <td>5938.76</td>\n",
       "      <td>5324.30</td>\n",
       "      <td>6094.47</td>\n",
       "      <td>6393.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I.5. Government &amp; Civil Society, Total</th>\n",
       "      <td>1735.06</td>\n",
       "      <td>1387.19</td>\n",
       "      <td>1717.47</td>\n",
       "      <td>1300.48</td>\n",
       "      <td>1425.16</td>\n",
       "      <td>1792.34</td>\n",
       "      <td>1496.31</td>\n",
       "      <td>1130.09</td>\n",
       "      <td>1946.19</td>\n",
       "      <td>1997.61</td>\n",
       "      <td>...</td>\n",
       "      <td>11678.39</td>\n",
       "      <td>13221.55</td>\n",
       "      <td>13325.07</td>\n",
       "      <td>13983.96</td>\n",
       "      <td>14531.76</td>\n",
       "      <td>13014.89</td>\n",
       "      <td>12526.83</td>\n",
       "      <td>12952.03</td>\n",
       "      <td>11936.95</td>\n",
       "      <td>11924.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>II. Economic Infrastructure &amp; Services, Total</th>\n",
       "      <td>8382.07</td>\n",
       "      <td>12182.65</td>\n",
       "      <td>8038.75</td>\n",
       "      <td>9591.39</td>\n",
       "      <td>10805.58</td>\n",
       "      <td>12880.17</td>\n",
       "      <td>11512.02</td>\n",
       "      <td>10127.12</td>\n",
       "      <td>7803.86</td>\n",
       "      <td>8062.73</td>\n",
       "      <td>...</td>\n",
       "      <td>12242.26</td>\n",
       "      <td>19522.05</td>\n",
       "      <td>15348.72</td>\n",
       "      <td>19487.56</td>\n",
       "      <td>16998.42</td>\n",
       "      <td>17974.14</td>\n",
       "      <td>20836.01</td>\n",
       "      <td>22160.40</td>\n",
       "      <td>22234.71</td>\n",
       "      <td>22004.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>II.5. Business &amp; Other Services, Total</th>\n",
       "      <td>394.33</td>\n",
       "      <td>308.94</td>\n",
       "      <td>430.42</td>\n",
       "      <td>346.42</td>\n",
       "      <td>1206.94</td>\n",
       "      <td>659.63</td>\n",
       "      <td>642.43</td>\n",
       "      <td>1008.88</td>\n",
       "      <td>534.92</td>\n",
       "      <td>1444.93</td>\n",
       "      <td>...</td>\n",
       "      <td>1443.38</td>\n",
       "      <td>1631.89</td>\n",
       "      <td>1106.67</td>\n",
       "      <td>1309.79</td>\n",
       "      <td>1746.30</td>\n",
       "      <td>941.00</td>\n",
       "      <td>1245.62</td>\n",
       "      <td>2152.68</td>\n",
       "      <td>1238.58</td>\n",
       "      <td>1385.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  1990      1991     1992  \\\n",
       "Sector(s)                                                                   \n",
       "I.1. Education, Total                          5664.71   4973.52  4793.35   \n",
       "I.2. Health, Total                             1613.27   1424.84  1617.43   \n",
       "I.5. Government & Civil Society, Total         1735.06   1387.19  1717.47   \n",
       "II. Economic Infrastructure & Services, Total  8382.07  12182.65  8038.75   \n",
       "II.5. Business & Other Services, Total          394.33    308.94   430.42   \n",
       "\n",
       "                                                  1993      1994      1995  \\\n",
       "Sector(s)                                                                    \n",
       "I.1. Education, Total                          4672.43   5376.85   6054.95   \n",
       "I.2. Health, Total                             1654.98   1831.49   2171.42   \n",
       "I.5. Government & Civil Society, Total         1300.48   1425.16   1792.34   \n",
       "II. Economic Infrastructure & Services, Total  9591.39  10805.58  12880.17   \n",
       "II.5. Business & Other Services, Total          346.42   1206.94    659.63   \n",
       "\n",
       "                                                   1996      1997     1998  \\\n",
       "Sector(s)                                                                    \n",
       "I.1. Education, Total                           5261.22   4683.23  4492.27   \n",
       "I.2. Health, Total                              2283.51   1667.18  1593.50   \n",
       "I.5. Government & Civil Society, Total          1496.31   1130.09  1946.19   \n",
       "II. Economic Infrastructure & Services, Total  11512.02  10127.12  7803.86   \n",
       "II.5. Business & Other Services, Total           642.43   1008.88   534.92   \n",
       "\n",
       "                                                  1999    ...         2007  \\\n",
       "Sector(s)                                                 ...                \n",
       "I.1. Education, Total                          5038.70    ...      8440.51   \n",
       "I.2. Health, Total                             1973.92    ...      4447.27   \n",
       "I.5. Government & Civil Society, Total         1997.61    ...     11678.39   \n",
       "II. Economic Infrastructure & Services, Total  8062.73    ...     12242.26   \n",
       "II.5. Business & Other Services, Total         1444.93    ...      1443.38   \n",
       "\n",
       "                                                   2008      2009      2010  \\\n",
       "Sector(s)                                                                     \n",
       "I.1. Education, Total                           9417.38   9329.86   9318.94   \n",
       "I.2. Health, Total                              5077.02   4990.22   5108.05   \n",
       "I.5. Government & Civil Society, Total         13221.55  13325.07  13983.96   \n",
       "II. Economic Infrastructure & Services, Total  19522.05  15348.72  19487.56   \n",
       "II.5. Business & Other Services, Total          1631.89   1106.67   1309.79   \n",
       "\n",
       "                                                   2011      2012      2013  \\\n",
       "Sector(s)                                                                     \n",
       "I.1. Education, Total                           9780.09   8793.18   8227.74   \n",
       "I.2. Health, Total                              6012.12   6160.02   5938.76   \n",
       "I.5. Government & Civil Society, Total         14531.76  13014.89  12526.83   \n",
       "II. Economic Infrastructure & Services, Total  16998.42  17974.14  20836.01   \n",
       "II.5. Business & Other Services, Total          1746.30    941.00   1245.62   \n",
       "\n",
       "                                                   2014      2015      2016  \n",
       "Sector(s)                                                                    \n",
       "I.1. Education, Total                           9157.74   8050.54   8635.15  \n",
       "I.2. Health, Total                              5324.30   6094.47   6393.36  \n",
       "I.5. Government & Civil Society, Total         12952.03  11936.95  11924.34  \n",
       "II. Economic Infrastructure & Services, Total  22160.40  22234.71  22004.57  \n",
       "II.5. Business & Other Services, Total          2152.68   1238.58   1385.94  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 342,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 343,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.index.name = 'year'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 345,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 346,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Sector(s)</th>\n",
       "      <th>year</th>\n",
       "      <th>I.1. Education, Total</th>\n",
       "      <th>I.2. Health, Total</th>\n",
       "      <th>I.5. Government &amp; Civil Society, Total</th>\n",
       "      <th>II. Economic Infrastructure &amp; Services, Total</th>\n",
       "      <th>II.5. Business &amp; Other Services, Total</th>\n",
       "      <th>III. Production Sectors, Total</th>\n",
       "      <th>IV. Multi-Sector / Cross-Cutting, Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990</td>\n",
       "      <td>5664.71</td>\n",
       "      <td>1613.27</td>\n",
       "      <td>1735.06</td>\n",
       "      <td>8382.07</td>\n",
       "      <td>394.33</td>\n",
       "      <td>6878.85</td>\n",
       "      <td>1871.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1991</td>\n",
       "      <td>4973.52</td>\n",
       "      <td>1424.84</td>\n",
       "      <td>1387.19</td>\n",
       "      <td>12182.65</td>\n",
       "      <td>308.94</td>\n",
       "      <td>6199.57</td>\n",
       "      <td>1524.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1992</td>\n",
       "      <td>4793.35</td>\n",
       "      <td>1617.43</td>\n",
       "      <td>1717.47</td>\n",
       "      <td>8038.75</td>\n",
       "      <td>430.42</td>\n",
       "      <td>8069.91</td>\n",
       "      <td>2041.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1993</td>\n",
       "      <td>4672.43</td>\n",
       "      <td>1654.98</td>\n",
       "      <td>1300.48</td>\n",
       "      <td>9591.39</td>\n",
       "      <td>346.42</td>\n",
       "      <td>5780.78</td>\n",
       "      <td>2045.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1994</td>\n",
       "      <td>5376.85</td>\n",
       "      <td>1831.49</td>\n",
       "      <td>1425.16</td>\n",
       "      <td>10805.58</td>\n",
       "      <td>1206.94</td>\n",
       "      <td>5265.10</td>\n",
       "      <td>1965.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Sector(s)  year  I.1. Education, Total  I.2. Health, Total  \\\n",
       "0          1990                5664.71             1613.27   \n",
       "1          1991                4973.52             1424.84   \n",
       "2          1992                4793.35             1617.43   \n",
       "3          1993                4672.43             1654.98   \n",
       "4          1994                5376.85             1831.49   \n",
       "\n",
       "Sector(s)  I.5. Government & Civil Society, Total  \\\n",
       "0                                         1735.06   \n",
       "1                                         1387.19   \n",
       "2                                         1717.47   \n",
       "3                                         1300.48   \n",
       "4                                         1425.16   \n",
       "\n",
       "Sector(s)  II. Economic Infrastructure & Services, Total  \\\n",
       "0                                                8382.07   \n",
       "1                                               12182.65   \n",
       "2                                                8038.75   \n",
       "3                                                9591.39   \n",
       "4                                               10805.58   \n",
       "\n",
       "Sector(s)  II.5. Business & Other Services, Total  \\\n",
       "0                                          394.33   \n",
       "1                                          308.94   \n",
       "2                                          430.42   \n",
       "3                                          346.42   \n",
       "4                                         1206.94   \n",
       "\n",
       "Sector(s)  III. Production Sectors, Total  \\\n",
       "0                                 6878.85   \n",
       "1                                 6199.57   \n",
       "2                                 8069.91   \n",
       "3                                 5780.78   \n",
       "4                                 5265.10   \n",
       "\n",
       "Sector(s)  IV. Multi-Sector / Cross-Cutting, Total  \n",
       "0                                          1871.51  \n",
       "1                                          1524.03  \n",
       "2                                          2041.24  \n",
       "3                                          2045.15  \n",
       "4                                          1965.18  "
      ]
     },
     "execution_count": 346,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 347,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Sector(s)\n",
       "year                                              object\n",
       "I.1. Education, Total                            float64\n",
       "I.2. Health, Total                               float64\n",
       "I.5. Government & Civil Society, Total           float64\n",
       "II. Economic Infrastructure & Services, Total    float64\n",
       "II.5. Business & Other Services, Total           float64\n",
       "III. Production Sectors, Total                   float64\n",
       "IV. Multi-Sector / Cross-Cutting, Total          float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 347,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 348,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Sector(s)</th>\n",
       "      <th>year</th>\n",
       "      <th>I.1. Education, Total</th>\n",
       "      <th>I.2. Health, Total</th>\n",
       "      <th>I.5. Government &amp; Civil Society, Total</th>\n",
       "      <th>II. Economic Infrastructure &amp; Services, Total</th>\n",
       "      <th>II.5. Business &amp; Other Services, Total</th>\n",
       "      <th>III. Production Sectors, Total</th>\n",
       "      <th>IV. Multi-Sector / Cross-Cutting, Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990</td>\n",
       "      <td>5664.71</td>\n",
       "      <td>1613.27</td>\n",
       "      <td>1735.06</td>\n",
       "      <td>8382.07</td>\n",
       "      <td>394.33</td>\n",
       "      <td>6878.85</td>\n",
       "      <td>1871.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1991</td>\n",
       "      <td>4973.52</td>\n",
       "      <td>1424.84</td>\n",
       "      <td>1387.19</td>\n",
       "      <td>12182.65</td>\n",
       "      <td>308.94</td>\n",
       "      <td>6199.57</td>\n",
       "      <td>1524.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1992</td>\n",
       "      <td>4793.35</td>\n",
       "      <td>1617.43</td>\n",
       "      <td>1717.47</td>\n",
       "      <td>8038.75</td>\n",
       "      <td>430.42</td>\n",
       "      <td>8069.91</td>\n",
       "      <td>2041.24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Sector(s)  year  I.1. Education, Total  I.2. Health, Total  \\\n",
       "id                                                           \n",
       "0          1990                5664.71             1613.27   \n",
       "1          1991                4973.52             1424.84   \n",
       "2          1992                4793.35             1617.43   \n",
       "\n",
       "Sector(s)  I.5. Government & Civil Society, Total  \\\n",
       "id                                                  \n",
       "0                                         1735.06   \n",
       "1                                         1387.19   \n",
       "2                                         1717.47   \n",
       "\n",
       "Sector(s)  II. Economic Infrastructure & Services, Total  \\\n",
       "id                                                         \n",
       "0                                                8382.07   \n",
       "1                                               12182.65   \n",
       "2                                                8038.75   \n",
       "\n",
       "Sector(s)  II.5. Business & Other Services, Total  \\\n",
       "id                                                  \n",
       "0                                          394.33   \n",
       "1                                          308.94   \n",
       "2                                          430.42   \n",
       "\n",
       "Sector(s)  III. Production Sectors, Total  \\\n",
       "id                                          \n",
       "0                                 6878.85   \n",
       "1                                 6199.57   \n",
       "2                                 8069.91   \n",
       "\n",
       "Sector(s)  IV. Multi-Sector / Cross-Cutting, Total  \n",
       "id                                                  \n",
       "0                                          1871.51  \n",
       "1                                          1524.03  \n",
       "2                                          2041.24  "
      ]
     },
     "execution_count": 348,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.index.names = [\"id\"]\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 349,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Sector(s)\n",
       "year                                              object\n",
       "I.1. Education, Total                            float64\n",
       "I.2. Health, Total                               float64\n",
       "I.5. Government & Civil Society, Total           float64\n",
       "II. Economic Infrastructure & Services, Total    float64\n",
       "II.5. Business & Other Services, Total           float64\n",
       "III. Production Sectors, Total                   float64\n",
       "IV. Multi-Sector / Cross-Cutting, Total          float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 349,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 350,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(27, 8)"
      ]
     },
     "execution_count": 350,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 351,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2.columns = ['year', 'education', 'health', 'government', 'economic', 'business', 'production', 'multi-sector']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 352,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['year'] = df2['year'].astype('int32')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year              int32\n",
       "education       float64\n",
       "health          float64\n",
       "government      float64\n",
       "economic        float64\n",
       "business        float64\n",
       "production      float64\n",
       "multi-sector    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 353,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>education</th>\n",
       "      <th>health</th>\n",
       "      <th>government</th>\n",
       "      <th>economic</th>\n",
       "      <th>business</th>\n",
       "      <th>production</th>\n",
       "      <th>multi-sector</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990</td>\n",
       "      <td>5664.71</td>\n",
       "      <td>1613.27</td>\n",
       "      <td>1735.06</td>\n",
       "      <td>8382.07</td>\n",
       "      <td>394.33</td>\n",
       "      <td>6878.85</td>\n",
       "      <td>1871.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1991</td>\n",
       "      <td>4973.52</td>\n",
       "      <td>1424.84</td>\n",
       "      <td>1387.19</td>\n",
       "      <td>12182.65</td>\n",
       "      <td>308.94</td>\n",
       "      <td>6199.57</td>\n",
       "      <td>1524.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1992</td>\n",
       "      <td>4793.35</td>\n",
       "      <td>1617.43</td>\n",
       "      <td>1717.47</td>\n",
       "      <td>8038.75</td>\n",
       "      <td>430.42</td>\n",
       "      <td>8069.91</td>\n",
       "      <td>2041.24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    year  education   health  government  economic  business  production  \\\n",
       "id                                                                         \n",
       "0   1990    5664.71  1613.27     1735.06   8382.07    394.33     6878.85   \n",
       "1   1991    4973.52  1424.84     1387.19  12182.65    308.94     6199.57   \n",
       "2   1992    4793.35  1617.43     1717.47   8038.75    430.42     8069.91   \n",
       "\n",
       "    multi-sector  \n",
       "id                \n",
       "0        1871.51  \n",
       "1        1524.03  \n",
       "2        2041.24  "
      ]
     },
     "execution_count": 354,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 355,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>education</th>\n",
       "      <th>health</th>\n",
       "      <th>government</th>\n",
       "      <th>economic</th>\n",
       "      <th>business</th>\n",
       "      <th>production</th>\n",
       "      <th>multi-sector</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990</th>\n",
       "      <td>5664.71</td>\n",
       "      <td>1613.27</td>\n",
       "      <td>1735.06</td>\n",
       "      <td>8382.07</td>\n",
       "      <td>394.33</td>\n",
       "      <td>6878.85</td>\n",
       "      <td>1871.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991</th>\n",
       "      <td>4973.52</td>\n",
       "      <td>1424.84</td>\n",
       "      <td>1387.19</td>\n",
       "      <td>12182.65</td>\n",
       "      <td>308.94</td>\n",
       "      <td>6199.57</td>\n",
       "      <td>1524.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992</th>\n",
       "      <td>4793.35</td>\n",
       "      <td>1617.43</td>\n",
       "      <td>1717.47</td>\n",
       "      <td>8038.75</td>\n",
       "      <td>430.42</td>\n",
       "      <td>8069.91</td>\n",
       "      <td>2041.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993</th>\n",
       "      <td>4672.43</td>\n",
       "      <td>1654.98</td>\n",
       "      <td>1300.48</td>\n",
       "      <td>9591.39</td>\n",
       "      <td>346.42</td>\n",
       "      <td>5780.78</td>\n",
       "      <td>2045.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1994</th>\n",
       "      <td>5376.85</td>\n",
       "      <td>1831.49</td>\n",
       "      <td>1425.16</td>\n",
       "      <td>10805.58</td>\n",
       "      <td>1206.94</td>\n",
       "      <td>5265.10</td>\n",
       "      <td>1965.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      education   health  government  economic  business  production  \\\n",
       "year                                                                   \n",
       "1990    5664.71  1613.27     1735.06   8382.07    394.33     6878.85   \n",
       "1991    4973.52  1424.84     1387.19  12182.65    308.94     6199.57   \n",
       "1992    4793.35  1617.43     1717.47   8038.75    430.42     8069.91   \n",
       "1993    4672.43  1654.98     1300.48   9591.39    346.42     5780.78   \n",
       "1994    5376.85  1831.49     1425.16  10805.58   1206.94     5265.10   \n",
       "\n",
       "      multi-sector  \n",
       "year                \n",
       "1990       1871.51  \n",
       "1991       1524.03  \n",
       "1992       2041.24  \n",
       "1993       2045.15  \n",
       "1994       1965.18  "
      ]
     },
     "execution_count": 355,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df2.set_index(['year'])\n",
    "df2.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 356,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2.to_csv(\"clean_data/sector.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, clean and inspect the spending vs life expectancy time csv. The csv uses aggregate indicators. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 357,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df3 = pd.read_csv(\"data/life_expect.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 358,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name</th>\n",
       "      <th>1990 [YR1990]</th>\n",
       "      <th>1991 [YR1991]</th>\n",
       "      <th>1992 [YR1992]</th>\n",
       "      <th>1993 [YR1993]</th>\n",
       "      <th>1994 [YR1994]</th>\n",
       "      <th>1995 [YR1995]</th>\n",
       "      <th>1996 [YR1996]</th>\n",
       "      <th>1997 [YR1997]</th>\n",
       "      <th>1998 [YR1998]</th>\n",
       "      <th>...</th>\n",
       "      <th>2006 [YR2006]</th>\n",
       "      <th>2007 [YR2007]</th>\n",
       "      <th>2008 [YR2008]</th>\n",
       "      <th>2009 [YR2009]</th>\n",
       "      <th>2010 [YR2010]</th>\n",
       "      <th>2011 [YR2011]</th>\n",
       "      <th>2012 [YR2012]</th>\n",
       "      <th>2013 [YR2013]</th>\n",
       "      <th>2014 [YR2014]</th>\n",
       "      <th>2015 [YR2015]</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IDA only</td>\n",
       "      <td>53.039285</td>\n",
       "      <td>53.301928</td>\n",
       "      <td>53.582540</td>\n",
       "      <td>53.885894</td>\n",
       "      <td>54.209501</td>\n",
       "      <td>54.558191</td>\n",
       "      <td>54.930012</td>\n",
       "      <td>55.314159</td>\n",
       "      <td>55.710239</td>\n",
       "      <td>...</td>\n",
       "      <td>59.858147</td>\n",
       "      <td>60.459556</td>\n",
       "      <td>61.044044</td>\n",
       "      <td>61.599662</td>\n",
       "      <td>62.116124</td>\n",
       "      <td>62.594243</td>\n",
       "      <td>63.040820</td>\n",
       "      <td>63.459617</td>\n",
       "      <td>63.853122</td>\n",
       "      <td>64.222994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Lower middle income</td>\n",
       "      <td>59.513275</td>\n",
       "      <td>59.829442</td>\n",
       "      <td>60.183902</td>\n",
       "      <td>60.514125</td>\n",
       "      <td>60.838366</td>\n",
       "      <td>61.157632</td>\n",
       "      <td>61.483625</td>\n",
       "      <td>61.817486</td>\n",
       "      <td>62.150271</td>\n",
       "      <td>...</td>\n",
       "      <td>64.799075</td>\n",
       "      <td>65.165819</td>\n",
       "      <td>65.529030</td>\n",
       "      <td>65.901517</td>\n",
       "      <td>66.262475</td>\n",
       "      <td>66.596613</td>\n",
       "      <td>66.904789</td>\n",
       "      <td>67.193320</td>\n",
       "      <td>67.458766</td>\n",
       "      <td>67.705178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Low &amp; middle income</td>\n",
       "      <td>63.106052</td>\n",
       "      <td>63.326673</td>\n",
       "      <td>63.493558</td>\n",
       "      <td>63.653089</td>\n",
       "      <td>63.868162</td>\n",
       "      <td>64.114060</td>\n",
       "      <td>64.404773</td>\n",
       "      <td>64.718892</td>\n",
       "      <td>65.011587</td>\n",
       "      <td>...</td>\n",
       "      <td>67.538034</td>\n",
       "      <td>67.885040</td>\n",
       "      <td>68.212894</td>\n",
       "      <td>68.555213</td>\n",
       "      <td>68.865134</td>\n",
       "      <td>69.178624</td>\n",
       "      <td>69.459854</td>\n",
       "      <td>69.729661</td>\n",
       "      <td>69.970356</td>\n",
       "      <td>70.193261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Country Name  1990 [YR1990]  1991 [YR1991]  1992 [YR1992]  \\\n",
       "0             IDA only      53.039285      53.301928      53.582540   \n",
       "1  Lower middle income      59.513275      59.829442      60.183902   \n",
       "2  Low & middle income      63.106052      63.326673      63.493558   \n",
       "\n",
       "   1993 [YR1993]  1994 [YR1994]  1995 [YR1995]  1996 [YR1996]  1997 [YR1997]  \\\n",
       "0      53.885894      54.209501      54.558191      54.930012      55.314159   \n",
       "1      60.514125      60.838366      61.157632      61.483625      61.817486   \n",
       "2      63.653089      63.868162      64.114060      64.404773      64.718892   \n",
       "\n",
       "   1998 [YR1998]      ...        2006 [YR2006]  2007 [YR2007]  2008 [YR2008]  \\\n",
       "0      55.710239      ...            59.858147      60.459556      61.044044   \n",
       "1      62.150271      ...            64.799075      65.165819      65.529030   \n",
       "2      65.011587      ...            67.538034      67.885040      68.212894   \n",
       "\n",
       "   2009 [YR2009]  2010 [YR2010]  2011 [YR2011]  2012 [YR2012]  2013 [YR2013]  \\\n",
       "0      61.599662      62.116124      62.594243      63.040820      63.459617   \n",
       "1      65.901517      66.262475      66.596613      66.904789      67.193320   \n",
       "2      68.555213      68.865134      69.178624      69.459854      69.729661   \n",
       "\n",
       "   2014 [YR2014]  2015 [YR2015]  \n",
       "0      63.853122      64.222994  \n",
       "1      67.458766      67.705178  \n",
       "2      69.970356      70.193261  \n",
       "\n",
       "[3 rows x 27 columns]"
      ]
     },
     "execution_count": 358,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 359,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.columns = ['aggregator','1990', '1991','1992', '1993', '1994', '1995', '1996', '1997', '1998','1999', '2000', '2001', '2002', '2003', '2004', '2005', '2006', '2007','2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 360,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>aggregator</th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>...</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IDA only</td>\n",
       "      <td>53.039285</td>\n",
       "      <td>53.301928</td>\n",
       "      <td>53.582540</td>\n",
       "      <td>53.885894</td>\n",
       "      <td>54.209501</td>\n",
       "      <td>54.558191</td>\n",
       "      <td>54.930012</td>\n",
       "      <td>55.314159</td>\n",
       "      <td>55.710239</td>\n",
       "      <td>...</td>\n",
       "      <td>59.858147</td>\n",
       "      <td>60.459556</td>\n",
       "      <td>61.044044</td>\n",
       "      <td>61.599662</td>\n",
       "      <td>62.116124</td>\n",
       "      <td>62.594243</td>\n",
       "      <td>63.040820</td>\n",
       "      <td>63.459617</td>\n",
       "      <td>63.853122</td>\n",
       "      <td>64.222994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Lower middle income</td>\n",
       "      <td>59.513275</td>\n",
       "      <td>59.829442</td>\n",
       "      <td>60.183902</td>\n",
       "      <td>60.514125</td>\n",
       "      <td>60.838366</td>\n",
       "      <td>61.157632</td>\n",
       "      <td>61.483625</td>\n",
       "      <td>61.817486</td>\n",
       "      <td>62.150271</td>\n",
       "      <td>...</td>\n",
       "      <td>64.799075</td>\n",
       "      <td>65.165819</td>\n",
       "      <td>65.529030</td>\n",
       "      <td>65.901517</td>\n",
       "      <td>66.262475</td>\n",
       "      <td>66.596613</td>\n",
       "      <td>66.904789</td>\n",
       "      <td>67.193320</td>\n",
       "      <td>67.458766</td>\n",
       "      <td>67.705178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Low &amp; middle income</td>\n",
       "      <td>63.106052</td>\n",
       "      <td>63.326673</td>\n",
       "      <td>63.493558</td>\n",
       "      <td>63.653089</td>\n",
       "      <td>63.868162</td>\n",
       "      <td>64.114060</td>\n",
       "      <td>64.404773</td>\n",
       "      <td>64.718892</td>\n",
       "      <td>65.011587</td>\n",
       "      <td>...</td>\n",
       "      <td>67.538034</td>\n",
       "      <td>67.885040</td>\n",
       "      <td>68.212894</td>\n",
       "      <td>68.555213</td>\n",
       "      <td>68.865134</td>\n",
       "      <td>69.178624</td>\n",
       "      <td>69.459854</td>\n",
       "      <td>69.729661</td>\n",
       "      <td>69.970356</td>\n",
       "      <td>70.193261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            aggregator       1990       1991       1992       1993       1994  \\\n",
       "0             IDA only  53.039285  53.301928  53.582540  53.885894  54.209501   \n",
       "1  Lower middle income  59.513275  59.829442  60.183902  60.514125  60.838366   \n",
       "2  Low & middle income  63.106052  63.326673  63.493558  63.653089  63.868162   \n",
       "\n",
       "        1995       1996       1997       1998    ...           2006  \\\n",
       "0  54.558191  54.930012  55.314159  55.710239    ...      59.858147   \n",
       "1  61.157632  61.483625  61.817486  62.150271    ...      64.799075   \n",
       "2  64.114060  64.404773  64.718892  65.011587    ...      67.538034   \n",
       "\n",
       "        2007       2008       2009       2010       2011       2012  \\\n",
       "0  60.459556  61.044044  61.599662  62.116124  62.594243  63.040820   \n",
       "1  65.165819  65.529030  65.901517  66.262475  66.596613  66.904789   \n",
       "2  67.885040  68.212894  68.555213  68.865134  69.178624  69.459854   \n",
       "\n",
       "        2013       2014       2015  \n",
       "0  63.459617  63.853122  64.222994  \n",
       "1  67.193320  67.458766  67.705178  \n",
       "2  69.729661  69.970356  70.193261  \n",
       "\n",
       "[3 rows x 27 columns]"
      ]
     },
     "execution_count": 360,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head(3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 361,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = df3.set_index('aggregator')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 362,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>...</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aggregator</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>IDA only</th>\n",
       "      <td>53.039285</td>\n",
       "      <td>53.301928</td>\n",
       "      <td>53.582540</td>\n",
       "      <td>53.885894</td>\n",
       "      <td>54.209501</td>\n",
       "      <td>54.558191</td>\n",
       "      <td>54.930012</td>\n",
       "      <td>55.314159</td>\n",
       "      <td>55.710239</td>\n",
       "      <td>56.128856</td>\n",
       "      <td>...</td>\n",
       "      <td>59.858147</td>\n",
       "      <td>60.459556</td>\n",
       "      <td>61.044044</td>\n",
       "      <td>61.599662</td>\n",
       "      <td>62.116124</td>\n",
       "      <td>62.594243</td>\n",
       "      <td>63.040820</td>\n",
       "      <td>63.459617</td>\n",
       "      <td>63.853122</td>\n",
       "      <td>64.222994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lower middle income</th>\n",
       "      <td>59.513275</td>\n",
       "      <td>59.829442</td>\n",
       "      <td>60.183902</td>\n",
       "      <td>60.514125</td>\n",
       "      <td>60.838366</td>\n",
       "      <td>61.157632</td>\n",
       "      <td>61.483625</td>\n",
       "      <td>61.817486</td>\n",
       "      <td>62.150271</td>\n",
       "      <td>62.469101</td>\n",
       "      <td>...</td>\n",
       "      <td>64.799075</td>\n",
       "      <td>65.165819</td>\n",
       "      <td>65.529030</td>\n",
       "      <td>65.901517</td>\n",
       "      <td>66.262475</td>\n",
       "      <td>66.596613</td>\n",
       "      <td>66.904789</td>\n",
       "      <td>67.193320</td>\n",
       "      <td>67.458766</td>\n",
       "      <td>67.705178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Low &amp; middle income</th>\n",
       "      <td>63.106052</td>\n",
       "      <td>63.326673</td>\n",
       "      <td>63.493558</td>\n",
       "      <td>63.653089</td>\n",
       "      <td>63.868162</td>\n",
       "      <td>64.114060</td>\n",
       "      <td>64.404773</td>\n",
       "      <td>64.718892</td>\n",
       "      <td>65.011587</td>\n",
       "      <td>65.281524</td>\n",
       "      <td>...</td>\n",
       "      <td>67.538034</td>\n",
       "      <td>67.885040</td>\n",
       "      <td>68.212894</td>\n",
       "      <td>68.555213</td>\n",
       "      <td>68.865134</td>\n",
       "      <td>69.178624</td>\n",
       "      <td>69.459854</td>\n",
       "      <td>69.729661</td>\n",
       "      <td>69.970356</td>\n",
       "      <td>70.193261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          1990       1991       1992       1993       1994  \\\n",
       "aggregator                                                                   \n",
       "IDA only             53.039285  53.301928  53.582540  53.885894  54.209501   \n",
       "Lower middle income  59.513275  59.829442  60.183902  60.514125  60.838366   \n",
       "Low & middle income  63.106052  63.326673  63.493558  63.653089  63.868162   \n",
       "\n",
       "                          1995       1996       1997       1998       1999  \\\n",
       "aggregator                                                                   \n",
       "IDA only             54.558191  54.930012  55.314159  55.710239  56.128856   \n",
       "Lower middle income  61.157632  61.483625  61.817486  62.150271  62.469101   \n",
       "Low & middle income  64.114060  64.404773  64.718892  65.011587  65.281524   \n",
       "\n",
       "                       ...           2006       2007       2008       2009  \\\n",
       "aggregator             ...                                                   \n",
       "IDA only               ...      59.858147  60.459556  61.044044  61.599662   \n",
       "Lower middle income    ...      64.799075  65.165819  65.529030  65.901517   \n",
       "Low & middle income    ...      67.538034  67.885040  68.212894  68.555213   \n",
       "\n",
       "                          2010       2011       2012       2013       2014  \\\n",
       "aggregator                                                                   \n",
       "IDA only             62.116124  62.594243  63.040820  63.459617  63.853122   \n",
       "Lower middle income  66.262475  66.596613  66.904789  67.193320  67.458766   \n",
       "Low & middle income  68.865134  69.178624  69.459854  69.729661  69.970356   \n",
       "\n",
       "                          2015  \n",
       "aggregator                      \n",
       "IDA only             64.222994  \n",
       "Lower middle income  67.705178  \n",
       "Low & middle income  70.193261  \n",
       "\n",
       "[3 rows x 26 columns]"
      ]
     },
     "execution_count": 362,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 381,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2_temp = pd.read_csv('data/aid_spending_by_sector_per_yer.csv')\n",
    "df2_temp = df2_temp.set_index(['Sector(s)'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 386,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2_temp = df2_temp.drop(['Time Period'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 387,
   "metadata": {},
   "outputs": [],
   "source": [
    "dd = df2_temp.loc[\"I.2. Health, Total\", :'2015'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 389,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = df3.append(dd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 391,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = df3.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 395,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df3.index.name = 'year'\n",
    "df3.columns = ['ida_only', 'lower_middle_income', 'low_middle_income', 'health']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 396,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ida_only</th>\n",
       "      <th>lower_middle_income</th>\n",
       "      <th>low_middle_income</th>\n",
       "      <th>health</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990</th>\n",
       "      <td>53.039285</td>\n",
       "      <td>59.513275</td>\n",
       "      <td>63.106052</td>\n",
       "      <td>1613.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991</th>\n",
       "      <td>53.301928</td>\n",
       "      <td>59.829442</td>\n",
       "      <td>63.326673</td>\n",
       "      <td>1424.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992</th>\n",
       "      <td>53.582540</td>\n",
       "      <td>60.183902</td>\n",
       "      <td>63.493558</td>\n",
       "      <td>1617.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1993</th>\n",
       "      <td>53.885894</td>\n",
       "      <td>60.514125</td>\n",
       "      <td>63.653089</td>\n",
       "      <td>1654.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1994</th>\n",
       "      <td>54.209501</td>\n",
       "      <td>60.838366</td>\n",
       "      <td>63.868162</td>\n",
       "      <td>1831.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ida_only  lower_middle_income  low_middle_income   health\n",
       "year                                                            \n",
       "1990  53.039285            59.513275          63.106052  1613.27\n",
       "1991  53.301928            59.829442          63.326673  1424.84\n",
       "1992  53.582540            60.183902          63.493558  1617.43\n",
       "1993  53.885894            60.514125          63.653089  1654.98\n",
       "1994  54.209501            60.838366          63.868162  1831.49"
      ]
     },
     "execution_count": 396,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 398,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df3.to_csv(\"clean_data/life_clean.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 397,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ida_only               float64\n",
       "lower_middle_income    float64\n",
       "low_middle_income      float64\n",
       "health                 float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 397,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:PythonData]",
   "language": "python",
   "name": "conda-env-PythonData-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}