Modifying XLS Files Web Scrapping and Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ca5882ec",
   "metadata": {},
   "source": [
    "# Modifying Excel Files, Web Scrapping and Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb7dc2b5",
   "metadata": {},
   "source": [
    "## Web Scrapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "082b8b72",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "import requests\n",
    "from xls2xlsx import XLS2XLSX\n",
    "from os import listdir\n",
    "from bs4 import BeautifulSoup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6298d486",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here, we are going to convert the datafiles from .xls (old versions) to .xlsx to be read by pandas.\n",
    "files = sorted(listdir('/Users/federicokurten/Desktop/Projects/Diabetes_Prevalence/Diabetes_per_Country'))\n",
    "files = files[1:]\n",
    "for i in files: \n",
    "    xls = XLS2XLSX(f'/Users/federicokurten/Desktop/Projects/Diabetes_Prevalence/Diabetes_per_Country/{i}')\n",
    "    xls.to_xlsx(f'/Users/federicokurten/Desktop/Projects/Diabetes_Prevalence/Diabetes_per_Country_copy/{i}x')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b09509f4",
   "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>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>856500.0</td>\n",
       "      <td>19031</td>\n",
       "      <td>28.6</td>\n",
       "      <td>33</td>\n",
       "      <td>1090800.0</td>\n",
       "      <td>18630.3</td>\n",
       "      <td>182.7</td>\n",
       "      <td>167.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country Country Code People with diabetes 2010  \\\n",
       "0  Afghanistan          AFG                  856500.0   \n",
       "\n",
       "  Deaths attributable to diabetes 2010  \\\n",
       "0                                19031   \n",
       "\n",
       "  Total diabetes-related health expenditure, USD million 2010  \\\n",
       "0                                               28.6            \n",
       "\n",
       "  Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "0                                                 33              \n",
       "\n",
       "   People with diabetes 2019  Deaths attributable to diabetes 2019  \\\n",
       "0                  1090800.0                               18630.3   \n",
       "\n",
       "   Total diabetes-related health expenditure, USD million 2019  \\\n",
       "0                                              182.7             \n",
       "\n",
       "   Mean diabetes-related health expenditure per person, USD 2019  \n",
       "0                                              167.5              "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Now we have to modify the xlsx files to a cleaner form using pandas. We will do this by importing one data \n",
    "# file and use it as a global df for later to be concatenated with the data of the other countries.\n",
    "\n",
    "global_df = pd.read_excel('Diabetes_per_Country_copy/Afghanistan-diabetes-report.xlsx')\n",
    "global_df = global_df.transpose()\n",
    "global_df = global_df.loc[:,[1,9,22,24]]\n",
    "global_df.columns = global_df.iloc[0]\n",
    "global_df.columns = ['People with diabetes 2010', 'Deaths attributable to diabetes 2010', \n",
    "                     'Total diabetes-related health expenditure, USD million 2010', \n",
    "                     'Mean diabetes-related health expenditure per person, USD 2010']\n",
    "global_df = global_df.drop([\"At a glance\", 2030, 2045]).reset_index()\\\n",
    "            .rename(columns={'index':'Year'})\n",
    "global_df['People with diabetes 2010'] *= 1000 \n",
    "global_df['People with diabetes 2019'] = global_df.iloc[1,1]\n",
    "global_df['Deaths attributable to diabetes 2019'] = global_df.iloc[1,2]\n",
    "global_df['Total diabetes-related health expenditure, USD million 2019'] = global_df.iloc[1,3]\n",
    "global_df['Mean diabetes-related health expenditure per person, USD 2019'] = global_df.iloc[1,4]\n",
    "global_df['Country'] = 'Afghanistan'\n",
    "global_df = global_df.drop(1).drop(columns=['Year'])\n",
    "\n",
    "\n",
    "# Having the global df we are going to retrieve the 3 letter ISO codes from each country to be included in the \n",
    "# dataframe using BeautifulSoup and the requests modules from the World Population Review webpage and generate \n",
    "# a .csv file.\n",
    "\n",
    "url = requests.get('https://worldpopulationreview.com/country-rankings/country-codes')\n",
    "soup = BeautifulSoup(url.text, 'html.parser')\n",
    "code_table = soup.find('table', class_ = 'jsx-1487038798 table table-striped tp-table-body')\n",
    "\n",
    "countries = []\n",
    "country_code = []\n",
    "for c in code_table.find_all('tbody'): \n",
    "    rows = c.find_all('tr')\n",
    "    for row in rows:\n",
    "        country_info = row.find_all('td')\n",
    "        country,a,code,b = country_info\n",
    "        countries.append(country.text)\n",
    "        country_code.append(code.text)\n",
    "        \n",
    "df_codes = pd.DataFrame(data={'Country':countries, 'Code':country_code})\n",
    "df_codes.to_csv('/Users/federicokurten/Desktop/Projects/Diabetes_Prevalence/Country_Codes.csv')\n",
    "global_df['Country Code'] = country_code[0]\n",
    "global_df = global_df.iloc[:, [8,9,0,1,2,3,4,5,6,7]]\n",
    "display(global_df)\n",
    "\n",
    "# Lastly, we are going to retrieve all the countries from the Diabetes Prevalence scrapped data to compare them \n",
    "# to the countries we retrieved from the World Population Review webpage as the country's name are not the same \n",
    "# as well as some other countries that are not included. \n",
    "\n",
    "files = sorted(listdir('/Users/federicokurten/Desktop/Projects/Diabetes_Prevalence/Diabetes_per_Country_copy'))\n",
    "files = files[1:]\n",
    "countries = [re.search(r'([^.-]{1,})', i).group() for i in files]\n",
    "df_country = pd.DataFrame(data={'Country':countries})\n",
    "df_country.to_csv('/Users/federicokurten/Desktop/Projects/Diabetes_Prevalence/Country_Order.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99425bd4",
   "metadata": {},
   "source": [
    "After we cleaned the data, web scrapped the web to find the 3 letter ISO codes for each country, we now want to include the codes for each country and aggregate it into `global_df` that we just generated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9c8ee48b",
   "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>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>856500.0</td>\n",
       "      <td>19031</td>\n",
       "      <td>28.6</td>\n",
       "      <td>33</td>\n",
       "      <td>1090800.0</td>\n",
       "      <td>18630.3</td>\n",
       "      <td>182.7</td>\n",
       "      <td>167.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>102800.0</td>\n",
       "      <td>1358</td>\n",
       "      <td>26.8</td>\n",
       "      <td>261</td>\n",
       "      <td>237600.0</td>\n",
       "      <td>2448.1</td>\n",
       "      <td>155.1</td>\n",
       "      <td>652.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>DZA</td>\n",
       "      <td>1632100.0</td>\n",
       "      <td>14078</td>\n",
       "      <td>264.2</td>\n",
       "      <td>162</td>\n",
       "      <td>1904700.0</td>\n",
       "      <td>12657.2</td>\n",
       "      <td>1515.0</td>\n",
       "      <td>795.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>4600.0</td>\n",
       "      <td>31</td>\n",
       "      <td>13.5</td>\n",
       "      <td>2970</td>\n",
       "      <td>6900.0</td>\n",
       "      <td>32.4</td>\n",
       "      <td>27.6</td>\n",
       "      <td>4005.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>223700.0</td>\n",
       "      <td>4791</td>\n",
       "      <td>14.7</td>\n",
       "      <td>66</td>\n",
       "      <td>532400.0</td>\n",
       "      <td>6987.7</td>\n",
       "      <td>282.3</td>\n",
       "      <td>530.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>Viet Nam</td>\n",
       "      <td>VNM</td>\n",
       "      <td>1646600.0</td>\n",
       "      <td>32505</td>\n",
       "      <td>101.8</td>\n",
       "      <td>62</td>\n",
       "      <td>3779600.0</td>\n",
       "      <td>30096.2</td>\n",
       "      <td>1220.1</td>\n",
       "      <td>322.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>ESH</td>\n",
       "      <td>13500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>YEM</td>\n",
       "      <td>270000</td>\n",
       "      <td>3410</td>\n",
       "      <td>20</td>\n",
       "      <td>74</td>\n",
       "      <td>572700.0</td>\n",
       "      <td>5772.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>ZMB</td>\n",
       "      <td>169000</td>\n",
       "      <td>6529</td>\n",
       "      <td>11.1</td>\n",
       "      <td>66</td>\n",
       "      <td>273800.0</td>\n",
       "      <td>8000.2</td>\n",
       "      <td>81.3</td>\n",
       "      <td>296.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>235300.0</td>\n",
       "      <td>13567</td>\n",
       "      <td>8.9</td>\n",
       "      <td>38</td>\n",
       "      <td>103200.0</td>\n",
       "      <td>2621.9</td>\n",
       "      <td>55.8</td>\n",
       "      <td>540.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>217 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Country Country Code People with diabetes 2010  \\\n",
       "0       Afghanistan          AFG                  856500.0   \n",
       "1           Albania          ALB                  102800.0   \n",
       "2           Algeria          DZA                 1632100.0   \n",
       "3           Andorra          AND                    4600.0   \n",
       "4            Angola          AGO                  223700.0   \n",
       "..              ...          ...                       ...   \n",
       "212        Viet Nam          VNM                 1646600.0   \n",
       "213  Western Sahara          ESH                   13500.0   \n",
       "214           Yemen          YEM                    270000   \n",
       "215          Zambia          ZMB                    169000   \n",
       "216        Zimbabwe          ZWE                  235300.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2010  \\\n",
       "0                                  19031   \n",
       "1                                   1358   \n",
       "2                                  14078   \n",
       "3                                     31   \n",
       "4                                   4791   \n",
       "..                                   ...   \n",
       "212                                32505   \n",
       "213                                  NaN   \n",
       "214                                 3410   \n",
       "215                                 6529   \n",
       "216                                13567   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "0                                                 28.6            \n",
       "1                                                 26.8            \n",
       "2                                                264.2            \n",
       "3                                                 13.5            \n",
       "4                                                 14.7            \n",
       "..                                                 ...            \n",
       "212                                              101.8            \n",
       "213                                                NaN            \n",
       "214                                                 20            \n",
       "215                                               11.1            \n",
       "216                                                8.9            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "0                                                   33              \n",
       "1                                                  261              \n",
       "2                                                  162              \n",
       "3                                                 2970              \n",
       "4                                                   66              \n",
       "..                                                 ...              \n",
       "212                                                 62              \n",
       "213                                                NaN              \n",
       "214                                                 74              \n",
       "215                                                 66              \n",
       "216                                                 38              \n",
       "\n",
       "                             People with diabetes 2019  \\\n",
       "0                                            1090800.0   \n",
       "1                                             237600.0   \n",
       "2                                            1904700.0   \n",
       "3                                               6900.0   \n",
       "4                                             532400.0   \n",
       "..                                                 ...   \n",
       "212                                          3779600.0   \n",
       "213  ----------------------------------------------...   \n",
       "214                                           572700.0   \n",
       "215                                           273800.0   \n",
       "216                                           103200.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2019  \\\n",
       "0                                18630.3   \n",
       "1                                 2448.1   \n",
       "2                                12657.2   \n",
       "3                                   32.4   \n",
       "4                                 6987.7   \n",
       "..                                   ...   \n",
       "212                              30096.2   \n",
       "213                                    -   \n",
       "214                               5772.3   \n",
       "215                               8000.2   \n",
       "216                               2621.9   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "0                                                182.7            \n",
       "1                                                155.1            \n",
       "2                                               1515.0            \n",
       "3                                                 27.6            \n",
       "4                                                282.3            \n",
       "..                                                 ...            \n",
       "212                                             1220.1            \n",
       "213                                                  -            \n",
       "214                                                NaN            \n",
       "215                                               81.3            \n",
       "216                                               55.8            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \n",
       "0                                                167.5             \n",
       "1                                                652.8             \n",
       "2                                                795.4             \n",
       "3                                               4005.5             \n",
       "4                                                530.2             \n",
       "..                                                 ...             \n",
       "212                                              322.8             \n",
       "213                                                  -             \n",
       "214                                                NaN             \n",
       "215                                              296.8             \n",
       "216                                              540.9             \n",
       "\n",
       "[217 rows x 10 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_codes = pd.read_csv('Country_Codes_Clean.csv')\n",
    "country_code = df_codes['Code'].tolist()\n",
    "\n",
    "for i,code in zip(files[1:],country_code[1:]):\n",
    "    country = re.search(r'([^.-]{1,})', i).group()\n",
    "    df = pd.read_excel(f'Diabetes_per_Country_copy/{i}')\n",
    "    df = df.transpose()\n",
    "    df = df.loc[:,[1,9,22,24]]\n",
    "    df.columns = df.iloc[0]\n",
    "    df.columns = ['People with diabetes 2010', 'Deaths attributable to diabetes 2010', \n",
    "                         'Total diabetes-related health expenditure, USD million 2010', \n",
    "                         'Mean diabetes-related health expenditure per person, USD 2010']\n",
    "    df = df.drop([\"At a glance\", 2030, 2045]).reset_index()\\\n",
    "                .rename(columns={'index':'Year'})\n",
    "    df['People with diabetes 2010'] *= 1000 \n",
    "\n",
    "    df['People with diabetes 2019'] = df.iloc[1,1]\n",
    "    df['Deaths attributable to diabetes 2019'] = df.iloc[1,2]\n",
    "    df['Total diabetes-related health expenditure, USD million 2019'] = df.iloc[1,3]\n",
    "    df['Mean diabetes-related health expenditure per person, USD 2019'] = df.iloc[1,4]\n",
    "    df['Country'] = country\n",
    "    df['Country Code'] = code\n",
    "    df = df.drop(1).drop(columns=['Year'])\n",
    "    df = df.iloc[:, [8,9,0,1,2,3,4,5,6,7]]\n",
    "\n",
    "    global_df = pd.concat([global_df, df], ignore_index=True)\n",
    "\n",
    "global_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "245ab1d4",
   "metadata": {},
   "source": [
    "Having the country's diabetes information and the 3 letter codes, we can now merge the `global_df` to the excel file of population that was found on the internet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "65f222e9",
   "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>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>2010</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>856500.0</td>\n",
       "      <td>19031</td>\n",
       "      <td>28.6</td>\n",
       "      <td>33</td>\n",
       "      <td>1090800.0</td>\n",
       "      <td>18630.3</td>\n",
       "      <td>182.7</td>\n",
       "      <td>167.5</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>29185511.0</td>\n",
       "      <td>38041757.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>102800.0</td>\n",
       "      <td>1358</td>\n",
       "      <td>26.8</td>\n",
       "      <td>261</td>\n",
       "      <td>237600.0</td>\n",
       "      <td>2448.1</td>\n",
       "      <td>155.1</td>\n",
       "      <td>652.8</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2913021.0</td>\n",
       "      <td>2854191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>DZA</td>\n",
       "      <td>1632100.0</td>\n",
       "      <td>14078</td>\n",
       "      <td>264.2</td>\n",
       "      <td>162</td>\n",
       "      <td>1904700.0</td>\n",
       "      <td>12657.2</td>\n",
       "      <td>1515.0</td>\n",
       "      <td>795.4</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>35977451.0</td>\n",
       "      <td>43053054.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>4600.0</td>\n",
       "      <td>31</td>\n",
       "      <td>13.5</td>\n",
       "      <td>2970</td>\n",
       "      <td>6900.0</td>\n",
       "      <td>32.4</td>\n",
       "      <td>27.6</td>\n",
       "      <td>4005.5</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>84454.0</td>\n",
       "      <td>77146.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>223700.0</td>\n",
       "      <td>4791</td>\n",
       "      <td>14.7</td>\n",
       "      <td>66</td>\n",
       "      <td>532400.0</td>\n",
       "      <td>6987.7</td>\n",
       "      <td>282.3</td>\n",
       "      <td>530.2</td>\n",
       "      <td>Angola</td>\n",
       "      <td>23356247.0</td>\n",
       "      <td>31825299.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>Viet Nam</td>\n",
       "      <td>VNM</td>\n",
       "      <td>1646600.0</td>\n",
       "      <td>32505</td>\n",
       "      <td>101.8</td>\n",
       "      <td>62</td>\n",
       "      <td>3779600.0</td>\n",
       "      <td>30096.2</td>\n",
       "      <td>1220.1</td>\n",
       "      <td>322.8</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>87967655.0</td>\n",
       "      <td>96462108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>ESH</td>\n",
       "      <td>13500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>YEM</td>\n",
       "      <td>270000</td>\n",
       "      <td>3410</td>\n",
       "      <td>20</td>\n",
       "      <td>74</td>\n",
       "      <td>572700.0</td>\n",
       "      <td>5772.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen, Rep.</td>\n",
       "      <td>23154854.0</td>\n",
       "      <td>29161922.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>ZMB</td>\n",
       "      <td>169000</td>\n",
       "      <td>6529</td>\n",
       "      <td>11.1</td>\n",
       "      <td>66</td>\n",
       "      <td>273800.0</td>\n",
       "      <td>8000.2</td>\n",
       "      <td>81.3</td>\n",
       "      <td>296.8</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>13605986.0</td>\n",
       "      <td>17861034.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>235300.0</td>\n",
       "      <td>13567</td>\n",
       "      <td>8.9</td>\n",
       "      <td>38</td>\n",
       "      <td>103200.0</td>\n",
       "      <td>2621.9</td>\n",
       "      <td>55.8</td>\n",
       "      <td>540.9</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>12697728.0</td>\n",
       "      <td>14645473.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>217 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Country Country Code People with diabetes 2010  \\\n",
       "0       Afghanistan          AFG                  856500.0   \n",
       "1           Albania          ALB                  102800.0   \n",
       "2           Algeria          DZA                 1632100.0   \n",
       "3           Andorra          AND                    4600.0   \n",
       "4            Angola          AGO                  223700.0   \n",
       "..              ...          ...                       ...   \n",
       "212        Viet Nam          VNM                 1646600.0   \n",
       "213  Western Sahara          ESH                   13500.0   \n",
       "214           Yemen          YEM                    270000   \n",
       "215          Zambia          ZMB                    169000   \n",
       "216        Zimbabwe          ZWE                  235300.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2010  \\\n",
       "0                                  19031   \n",
       "1                                   1358   \n",
       "2                                  14078   \n",
       "3                                     31   \n",
       "4                                   4791   \n",
       "..                                   ...   \n",
       "212                                32505   \n",
       "213                                  NaN   \n",
       "214                                 3410   \n",
       "215                                 6529   \n",
       "216                                13567   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "0                                                 28.6            \n",
       "1                                                 26.8            \n",
       "2                                                264.2            \n",
       "3                                                 13.5            \n",
       "4                                                 14.7            \n",
       "..                                                 ...            \n",
       "212                                              101.8            \n",
       "213                                                NaN            \n",
       "214                                                 20            \n",
       "215                                               11.1            \n",
       "216                                                8.9            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "0                                                   33              \n",
       "1                                                  261              \n",
       "2                                                  162              \n",
       "3                                                 2970              \n",
       "4                                                   66              \n",
       "..                                                 ...              \n",
       "212                                                 62              \n",
       "213                                                NaN              \n",
       "214                                                 74              \n",
       "215                                                 66              \n",
       "216                                                 38              \n",
       "\n",
       "                             People with diabetes 2019  \\\n",
       "0                                            1090800.0   \n",
       "1                                             237600.0   \n",
       "2                                            1904700.0   \n",
       "3                                               6900.0   \n",
       "4                                             532400.0   \n",
       "..                                                 ...   \n",
       "212                                          3779600.0   \n",
       "213  ----------------------------------------------...   \n",
       "214                                           572700.0   \n",
       "215                                           273800.0   \n",
       "216                                           103200.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2019  \\\n",
       "0                                18630.3   \n",
       "1                                 2448.1   \n",
       "2                                12657.2   \n",
       "3                                   32.4   \n",
       "4                                 6987.7   \n",
       "..                                   ...   \n",
       "212                              30096.2   \n",
       "213                                    -   \n",
       "214                               5772.3   \n",
       "215                               8000.2   \n",
       "216                               2621.9   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "0                                                182.7            \n",
       "1                                                155.1            \n",
       "2                                               1515.0            \n",
       "3                                                 27.6            \n",
       "4                                                282.3            \n",
       "..                                                 ...            \n",
       "212                                             1220.1            \n",
       "213                                                  -            \n",
       "214                                                NaN            \n",
       "215                                               81.3            \n",
       "216                                               55.8            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \\\n",
       "0                                                167.5              \n",
       "1                                                652.8              \n",
       "2                                                795.4              \n",
       "3                                               4005.5              \n",
       "4                                                530.2              \n",
       "..                                                 ...              \n",
       "212                                              322.8              \n",
       "213                                                  -              \n",
       "214                                                NaN              \n",
       "215                                              296.8              \n",
       "216                                              540.9              \n",
       "\n",
       "    Country Name        2010        2019  \n",
       "0    Afghanistan  29185511.0  38041757.0  \n",
       "1        Albania   2913021.0   2854191.0  \n",
       "2        Algeria  35977451.0  43053054.0  \n",
       "3        Andorra     84454.0     77146.0  \n",
       "4         Angola  23356247.0  31825299.0  \n",
       "..           ...         ...         ...  \n",
       "212      Vietnam  87967655.0  96462108.0  \n",
       "213          NaN         NaN         NaN  \n",
       "214  Yemen, Rep.  23154854.0  29161922.0  \n",
       "215       Zambia  13605986.0  17861034.0  \n",
       "216     Zimbabwe  12697728.0  14645473.0  \n",
       "\n",
       "[217 rows x 13 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_population = pd.read_csv('Population.csv')\n",
    "\n",
    "global_df = global_df.merge(df_population, how='left', on='Country Code')\n",
    "global_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "384b664d",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68dacb75",
   "metadata": {},
   "source": [
    "We can see that all the population values got in perfectly into `global_df`. We now have to data clean the df before doing any analysis. To do this, we have to see which values have got `NaN` values from the population information that got merged, as it was a Left Join, where only the values being in the Left df (`global_df`) will be kept while the countries that were not in the population information will become `NaN`.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "64e6c963",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11, 13)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>2010</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Anguilla</td>\n",
       "      <td>AIA</td>\n",
       "      <td>500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Cook Islands</td>\n",
       "      <td>COK</td>\n",
       "      <td>800.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.5</td>\n",
       "      <td>678</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>French Guiana</td>\n",
       "      <td>GUF</td>\n",
       "      <td>14500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Guadeloupe</td>\n",
       "      <td>GLP</td>\n",
       "      <td>32500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Martinique</td>\n",
       "      <td>MTQ</td>\n",
       "      <td>30500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Netherlands Antilles</td>\n",
       "      <td>ANT</td>\n",
       "      <td>19900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Niue</td>\n",
       "      <td>NIU</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1594</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Reunion</td>\n",
       "      <td>REU</td>\n",
       "      <td>87200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>TWN</td>\n",
       "      <td>815900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1228800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>Tokelau</td>\n",
       "      <td>TKL</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>ESH</td>\n",
       "      <td>13500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Country Country Code People with diabetes 2010  \\\n",
       "5                Anguilla          AIA                     500.0   \n",
       "44           Cook Islands          COK                     800.0   \n",
       "70          French Guiana          GUF                   14500.0   \n",
       "80             Guadeloupe          GLP                   32500.0   \n",
       "122            Martinique          MTQ                   30500.0   \n",
       "136  Netherlands Antilles          ANT                   19900.0   \n",
       "143                  Niue          NIU                         0   \n",
       "160               Reunion          REU                   87200.0   \n",
       "189                Taiwan          TWN                  815900.0   \n",
       "194               Tokelau          TKL                     100.0   \n",
       "213        Western Sahara          ESH                   13500.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2010  \\\n",
       "5                                    NaN   \n",
       "44                                     4   \n",
       "70                                   NaN   \n",
       "80                                   NaN   \n",
       "122                                  NaN   \n",
       "136                                  NaN   \n",
       "143                                    0   \n",
       "160                                  NaN   \n",
       "189                                  NaN   \n",
       "194                                  NaN   \n",
       "213                                  NaN   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "5                                                  NaN            \n",
       "44                                                 0.5            \n",
       "70                                                 NaN            \n",
       "80                                                 NaN            \n",
       "122                                                NaN            \n",
       "136                                                NaN            \n",
       "143                                                0.1            \n",
       "160                                                NaN            \n",
       "189                                                NaN            \n",
       "194                                                NaN            \n",
       "213                                                NaN            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "5                                                  NaN              \n",
       "44                                                 678              \n",
       "70                                                 NaN              \n",
       "80                                                 NaN              \n",
       "122                                                NaN              \n",
       "136                                                NaN              \n",
       "143                                               1594              \n",
       "160                                                NaN              \n",
       "189                                                NaN              \n",
       "194                                                NaN              \n",
       "213                                                NaN              \n",
       "\n",
       "                             People with diabetes 2019  \\\n",
       "5    ----------------------------------------------...   \n",
       "44   ----------------------------------------------...   \n",
       "70   ----------------------------------------------...   \n",
       "80   ----------------------------------------------...   \n",
       "122  ----------------------------------------------...   \n",
       "136  ----------------------------------------------...   \n",
       "143  ----------------------------------------------...   \n",
       "160  ----------------------------------------------...   \n",
       "189                                          1228800.0   \n",
       "194  ----------------------------------------------...   \n",
       "213  ----------------------------------------------...   \n",
       "\n",
       "    Deaths attributable to diabetes 2019  \\\n",
       "5                                      -   \n",
       "44                                     -   \n",
       "70                                     -   \n",
       "80                                     -   \n",
       "122                                    -   \n",
       "136                                    -   \n",
       "143                                    -   \n",
       "160                                    -   \n",
       "189                                  NaN   \n",
       "194                                    -   \n",
       "213                                    -   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "5                                                    -            \n",
       "44                                                   -            \n",
       "70                                                   -            \n",
       "80                                                   -            \n",
       "122                                                  -            \n",
       "136                                                  -            \n",
       "143                                                  -            \n",
       "160                                                  -            \n",
       "189                                                NaN            \n",
       "194                                                  -            \n",
       "213                                                  -            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \\\n",
       "5                                                    -              \n",
       "44                                                   -              \n",
       "70                                                   -              \n",
       "80                                                   -              \n",
       "122                                                  -              \n",
       "136                                                  -              \n",
       "143                                                  -              \n",
       "160                                                  -              \n",
       "189                                                NaN              \n",
       "194                                                  -              \n",
       "213                                                  -              \n",
       "\n",
       "    Country Name  2010  2019  \n",
       "5            NaN   NaN   NaN  \n",
       "44           NaN   NaN   NaN  \n",
       "70           NaN   NaN   NaN  \n",
       "80           NaN   NaN   NaN  \n",
       "122          NaN   NaN   NaN  \n",
       "136          NaN   NaN   NaN  \n",
       "143          NaN   NaN   NaN  \n",
       "160          NaN   NaN   NaN  \n",
       "189          NaN   NaN   NaN  \n",
       "194          NaN   NaN   NaN  \n",
       "213          NaN   NaN   NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NaN_vals = global_df[(global_df['Country Name'].isnull()) & (global_df['2010'].isnull()) \\\n",
    "                     & (global_df['2019'].isnull())]\n",
    "print(NaN_vals.shape)\n",
    "NaN_vals"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f74b54d",
   "metadata": {},
   "source": [
    "There 11 countries that were in the `global_df` and not in the `df_population`. There is another problem that can be seen in the `People with diabetes 2019` column. Some values are a conglomeration of `-` and therefore those countries without information about diabetes need to be removed from the analysis. We will see in the other column `People with diabetes 2010` as well if there are some other strange values appearing in that column as well.\n",
    "\n",
    "We will first remove those weird values from the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e7f64614",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(15, 13)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>2010</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Anguilla</td>\n",
       "      <td>AIA</td>\n",
       "      <td>500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Cook Islands</td>\n",
       "      <td>COK</td>\n",
       "      <td>800.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.5</td>\n",
       "      <td>678</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Curaçao</td>\n",
       "      <td>CUW</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>19700.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Curacao</td>\n",
       "      <td>148703.0</td>\n",
       "      <td>157441.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Faroe Islands</td>\n",
       "      <td>FRO</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Faroe Islands</td>\n",
       "      <td>47803.0</td>\n",
       "      <td>48677.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>French Guiana</td>\n",
       "      <td>GUF</td>\n",
       "      <td>14500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Greenland</td>\n",
       "      <td>GRL</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greenland</td>\n",
       "      <td>56905.0</td>\n",
       "      <td>56225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Guadeloupe</td>\n",
       "      <td>GLP</td>\n",
       "      <td>32500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Martinique</td>\n",
       "      <td>MTQ</td>\n",
       "      <td>30500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Netherlands Antilles</td>\n",
       "      <td>ANT</td>\n",
       "      <td>19900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Niue</td>\n",
       "      <td>NIU</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1594</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Reunion</td>\n",
       "      <td>REU</td>\n",
       "      <td>87200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>Sint Maarten</td>\n",
       "      <td>SXM</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>3800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sint Maarten (Dutch part)</td>\n",
       "      <td>34056.0</td>\n",
       "      <td>40733.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>South Sudan</td>\n",
       "      <td>SSD</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>493700.0</td>\n",
       "      <td>7017.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South Sudan</td>\n",
       "      <td>9508372.0</td>\n",
       "      <td>11062114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>Tokelau</td>\n",
       "      <td>TKL</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>ESH</td>\n",
       "      <td>13500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>----------------------------------------------...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Country Country Code  \\\n",
       "5                Anguilla          AIA   \n",
       "44           Cook Islands          COK   \n",
       "48                Curaçao          CUW   \n",
       "66          Faroe Islands          FRO   \n",
       "70          French Guiana          GUF   \n",
       "78              Greenland          GRL   \n",
       "80             Guadeloupe          GLP   \n",
       "122            Martinique          MTQ   \n",
       "136  Netherlands Antilles          ANT   \n",
       "143                  Niue          NIU   \n",
       "160               Reunion          REU   \n",
       "176          Sint Maarten          SXM   \n",
       "182           South Sudan          SSD   \n",
       "194               Tokelau          TKL   \n",
       "213        Western Sahara          ESH   \n",
       "\n",
       "                             People with diabetes 2010  \\\n",
       "5                                                500.0   \n",
       "44                                               800.0   \n",
       "48   ----------------------------------------------...   \n",
       "66   ----------------------------------------------...   \n",
       "70                                             14500.0   \n",
       "78   ----------------------------------------------...   \n",
       "80                                             32500.0   \n",
       "122                                            30500.0   \n",
       "136                                            19900.0   \n",
       "143                                                  0   \n",
       "160                                            87200.0   \n",
       "176  ----------------------------------------------...   \n",
       "182  ----------------------------------------------...   \n",
       "194                                              100.0   \n",
       "213                                            13500.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2010  \\\n",
       "5                                    NaN   \n",
       "44                                     4   \n",
       "48                                     -   \n",
       "66                                     -   \n",
       "70                                   NaN   \n",
       "78                                     -   \n",
       "80                                   NaN   \n",
       "122                                  NaN   \n",
       "136                                  NaN   \n",
       "143                                    0   \n",
       "160                                  NaN   \n",
       "176                                    -   \n",
       "182                                    -   \n",
       "194                                  NaN   \n",
       "213                                  NaN   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "5                                                  NaN            \n",
       "44                                                 0.5            \n",
       "48                                                   -            \n",
       "66                                                   -            \n",
       "70                                                 NaN            \n",
       "78                                                   -            \n",
       "80                                                 NaN            \n",
       "122                                                NaN            \n",
       "136                                                NaN            \n",
       "143                                                0.1            \n",
       "160                                                NaN            \n",
       "176                                                  -            \n",
       "182                                                  -            \n",
       "194                                                NaN            \n",
       "213                                                NaN            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "5                                                  NaN              \n",
       "44                                                 678              \n",
       "48                                                   -              \n",
       "66                                                   -              \n",
       "70                                                 NaN              \n",
       "78                                                   -              \n",
       "80                                                 NaN              \n",
       "122                                                NaN              \n",
       "136                                                NaN              \n",
       "143                                               1594              \n",
       "160                                                NaN              \n",
       "176                                                  -              \n",
       "182                                                  -              \n",
       "194                                                NaN              \n",
       "213                                                NaN              \n",
       "\n",
       "                             People with diabetes 2019  \\\n",
       "5    ----------------------------------------------...   \n",
       "44   ----------------------------------------------...   \n",
       "48                                             19700.0   \n",
       "66                                              2400.0   \n",
       "70   ----------------------------------------------...   \n",
       "78                                              1300.0   \n",
       "80   ----------------------------------------------...   \n",
       "122  ----------------------------------------------...   \n",
       "136  ----------------------------------------------...   \n",
       "143  ----------------------------------------------...   \n",
       "160  ----------------------------------------------...   \n",
       "176                                             3800.0   \n",
       "182                                           493700.0   \n",
       "194  ----------------------------------------------...   \n",
       "213  ----------------------------------------------...   \n",
       "\n",
       "    Deaths attributable to diabetes 2019  \\\n",
       "5                                      -   \n",
       "44                                     -   \n",
       "48                                   NaN   \n",
       "66                                   NaN   \n",
       "70                                     -   \n",
       "78                                   NaN   \n",
       "80                                     -   \n",
       "122                                    -   \n",
       "136                                    -   \n",
       "143                                    -   \n",
       "160                                    -   \n",
       "176                                  NaN   \n",
       "182                               7017.1   \n",
       "194                                    -   \n",
       "213                                    -   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "5                                                    -            \n",
       "44                                                   -            \n",
       "48                                                 NaN            \n",
       "66                                                 NaN            \n",
       "70                                                   -            \n",
       "78                                                 NaN            \n",
       "80                                                   -            \n",
       "122                                                  -            \n",
       "136                                                  -            \n",
       "143                                                  -            \n",
       "160                                                  -            \n",
       "176                                                NaN            \n",
       "182                                                NaN            \n",
       "194                                                  -            \n",
       "213                                                  -            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \\\n",
       "5                                                    -              \n",
       "44                                                   -              \n",
       "48                                                 NaN              \n",
       "66                                                 NaN              \n",
       "70                                                   -              \n",
       "78                                                 NaN              \n",
       "80                                                   -              \n",
       "122                                                  -              \n",
       "136                                                  -              \n",
       "143                                                  -              \n",
       "160                                                  -              \n",
       "176                                                NaN              \n",
       "182                                                NaN              \n",
       "194                                                  -              \n",
       "213                                                  -              \n",
       "\n",
       "                  Country Name       2010        2019  \n",
       "5                          NaN        NaN         NaN  \n",
       "44                         NaN        NaN         NaN  \n",
       "48                     Curacao   148703.0    157441.0  \n",
       "66               Faroe Islands    47803.0     48677.0  \n",
       "70                         NaN        NaN         NaN  \n",
       "78                   Greenland    56905.0     56225.0  \n",
       "80                         NaN        NaN         NaN  \n",
       "122                        NaN        NaN         NaN  \n",
       "136                        NaN        NaN         NaN  \n",
       "143                        NaN        NaN         NaN  \n",
       "160                        NaN        NaN         NaN  \n",
       "176  Sint Maarten (Dutch part)    34056.0     40733.0  \n",
       "182                South Sudan  9508372.0  11062114.0  \n",
       "194                        NaN        NaN         NaN  \n",
       "213                        NaN        NaN         NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weird_vals = global_df.loc[5,'People with diabetes 2019']\n",
    "countries_with_vals = global_df[(global_df['People with diabetes 2010'] == weird_vals) | \\\n",
    "                                (global_df['People with diabetes 2019'] == weird_vals)]\n",
    "print(countries_with_vals.shape)\n",
    "countries_with_vals"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98636406",
   "metadata": {},
   "source": [
    "We can see that there are 15 countries with no Diabetes information, and we will proceed in removing them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "39bfe937",
   "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>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>2010</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>856500.0</td>\n",
       "      <td>19031</td>\n",
       "      <td>28.6</td>\n",
       "      <td>33</td>\n",
       "      <td>1090800.0</td>\n",
       "      <td>18630.3</td>\n",
       "      <td>182.7</td>\n",
       "      <td>167.5</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>29185511.0</td>\n",
       "      <td>38041757.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>102800.0</td>\n",
       "      <td>1358</td>\n",
       "      <td>26.8</td>\n",
       "      <td>261</td>\n",
       "      <td>237600.0</td>\n",
       "      <td>2448.1</td>\n",
       "      <td>155.1</td>\n",
       "      <td>652.8</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2913021.0</td>\n",
       "      <td>2854191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>DZA</td>\n",
       "      <td>1632100.0</td>\n",
       "      <td>14078</td>\n",
       "      <td>264.2</td>\n",
       "      <td>162</td>\n",
       "      <td>1904700.0</td>\n",
       "      <td>12657.2</td>\n",
       "      <td>1515.0</td>\n",
       "      <td>795.4</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>35977451.0</td>\n",
       "      <td>43053054.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>4600.0</td>\n",
       "      <td>31</td>\n",
       "      <td>13.5</td>\n",
       "      <td>2970</td>\n",
       "      <td>6900.0</td>\n",
       "      <td>32.4</td>\n",
       "      <td>27.6</td>\n",
       "      <td>4005.5</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>84454.0</td>\n",
       "      <td>77146.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>223700.0</td>\n",
       "      <td>4791</td>\n",
       "      <td>14.7</td>\n",
       "      <td>66</td>\n",
       "      <td>532400.0</td>\n",
       "      <td>6987.7</td>\n",
       "      <td>282.3</td>\n",
       "      <td>530.2</td>\n",
       "      <td>Angola</td>\n",
       "      <td>23356247.0</td>\n",
       "      <td>31825299.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>Venezuela (Bolivarian Republic of)</td>\n",
       "      <td>VEN</td>\n",
       "      <td>1033700.0</td>\n",
       "      <td>8810</td>\n",
       "      <td>412.8</td>\n",
       "      <td>399</td>\n",
       "      <td>1403600.0</td>\n",
       "      <td>10649.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Venezuela, RB</td>\n",
       "      <td>28439942.0</td>\n",
       "      <td>28515829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>Viet Nam</td>\n",
       "      <td>VNM</td>\n",
       "      <td>1646600.0</td>\n",
       "      <td>32505</td>\n",
       "      <td>101.8</td>\n",
       "      <td>62</td>\n",
       "      <td>3779600.0</td>\n",
       "      <td>30096.2</td>\n",
       "      <td>1220.1</td>\n",
       "      <td>322.8</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>87967655.0</td>\n",
       "      <td>96462108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>YEM</td>\n",
       "      <td>270000</td>\n",
       "      <td>3410</td>\n",
       "      <td>20</td>\n",
       "      <td>74</td>\n",
       "      <td>572700.0</td>\n",
       "      <td>5772.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen, Rep.</td>\n",
       "      <td>23154854.0</td>\n",
       "      <td>29161922.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>ZMB</td>\n",
       "      <td>169000</td>\n",
       "      <td>6529</td>\n",
       "      <td>11.1</td>\n",
       "      <td>66</td>\n",
       "      <td>273800.0</td>\n",
       "      <td>8000.2</td>\n",
       "      <td>81.3</td>\n",
       "      <td>296.8</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>13605986.0</td>\n",
       "      <td>17861034.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>235300.0</td>\n",
       "      <td>13567</td>\n",
       "      <td>8.9</td>\n",
       "      <td>38</td>\n",
       "      <td>103200.0</td>\n",
       "      <td>2621.9</td>\n",
       "      <td>55.8</td>\n",
       "      <td>540.9</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>12697728.0</td>\n",
       "      <td>14645473.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>202 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Country Country Code  \\\n",
       "0                           Afghanistan          AFG   \n",
       "1                               Albania          ALB   \n",
       "2                               Algeria          DZA   \n",
       "3                               Andorra          AND   \n",
       "4                                Angola          AGO   \n",
       "..                                  ...          ...   \n",
       "197  Venezuela (Bolivarian Republic of)          VEN   \n",
       "198                            Viet Nam          VNM   \n",
       "199                               Yemen          YEM   \n",
       "200                              Zambia          ZMB   \n",
       "201                            Zimbabwe          ZWE   \n",
       "\n",
       "    People with diabetes 2010 Deaths attributable to diabetes 2010  \\\n",
       "0                    856500.0                                19031   \n",
       "1                    102800.0                                 1358   \n",
       "2                   1632100.0                                14078   \n",
       "3                      4600.0                                   31   \n",
       "4                    223700.0                                 4791   \n",
       "..                        ...                                  ...   \n",
       "197                 1033700.0                                 8810   \n",
       "198                 1646600.0                                32505   \n",
       "199                    270000                                 3410   \n",
       "200                    169000                                 6529   \n",
       "201                  235300.0                                13567   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "0                                                 28.6            \n",
       "1                                                 26.8            \n",
       "2                                                264.2            \n",
       "3                                                 13.5            \n",
       "4                                                 14.7            \n",
       "..                                                 ...            \n",
       "197                                              412.8            \n",
       "198                                              101.8            \n",
       "199                                                 20            \n",
       "200                                               11.1            \n",
       "201                                                8.9            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "0                                                   33              \n",
       "1                                                  261              \n",
       "2                                                  162              \n",
       "3                                                 2970              \n",
       "4                                                   66              \n",
       "..                                                 ...              \n",
       "197                                                399              \n",
       "198                                                 62              \n",
       "199                                                 74              \n",
       "200                                                 66              \n",
       "201                                                 38              \n",
       "\n",
       "    People with diabetes 2019 Deaths attributable to diabetes 2019  \\\n",
       "0                   1090800.0                              18630.3   \n",
       "1                    237600.0                               2448.1   \n",
       "2                   1904700.0                              12657.2   \n",
       "3                      6900.0                                 32.4   \n",
       "4                    532400.0                               6987.7   \n",
       "..                        ...                                  ...   \n",
       "197                 1403600.0                              10649.0   \n",
       "198                 3779600.0                              30096.2   \n",
       "199                  572700.0                               5772.3   \n",
       "200                  273800.0                               8000.2   \n",
       "201                  103200.0                               2621.9   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "0                                                182.7            \n",
       "1                                                155.1            \n",
       "2                                               1515.0            \n",
       "3                                                 27.6            \n",
       "4                                                282.3            \n",
       "..                                                 ...            \n",
       "197                                                NaN            \n",
       "198                                             1220.1            \n",
       "199                                                NaN            \n",
       "200                                               81.3            \n",
       "201                                               55.8            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \\\n",
       "0                                                167.5              \n",
       "1                                                652.8              \n",
       "2                                                795.4              \n",
       "3                                               4005.5              \n",
       "4                                                530.2              \n",
       "..                                                 ...              \n",
       "197                                                NaN              \n",
       "198                                              322.8              \n",
       "199                                                NaN              \n",
       "200                                              296.8              \n",
       "201                                              540.9              \n",
       "\n",
       "      Country Name        2010        2019  \n",
       "0      Afghanistan  29185511.0  38041757.0  \n",
       "1          Albania   2913021.0   2854191.0  \n",
       "2          Algeria  35977451.0  43053054.0  \n",
       "3          Andorra     84454.0     77146.0  \n",
       "4           Angola  23356247.0  31825299.0  \n",
       "..             ...         ...         ...  \n",
       "197  Venezuela, RB  28439942.0  28515829.0  \n",
       "198        Vietnam  87967655.0  96462108.0  \n",
       "199    Yemen, Rep.  23154854.0  29161922.0  \n",
       "200         Zambia  13605986.0  17861034.0  \n",
       "201       Zimbabwe  12697728.0  14645473.0  \n",
       "\n",
       "[202 rows x 13 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries = countries_with_vals['Country'].to_list()\n",
    "global_df = global_df.query(f'Country != {countries}').reset_index(drop=True)\n",
    "global_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6eda1757",
   "metadata": {},
   "source": [
    "Having cleaned the weird values, we will now proceed in cleaning the countries where there is no information regarding population."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "73cfaf88",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 13)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>2010</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>TWN</td>\n",
       "      <td>815900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1228800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country Country Code People with diabetes 2010  \\\n",
       "176  Taiwan          TWN                  815900.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2010  \\\n",
       "176                                  NaN   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "176                                                NaN            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "176                                                NaN              \n",
       "\n",
       "    People with diabetes 2019 Deaths attributable to diabetes 2019  \\\n",
       "176                 1228800.0                                  NaN   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "176                                                NaN            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \\\n",
       "176                                                NaN              \n",
       "\n",
       "    Country Name  2010  2019  \n",
       "176          NaN   NaN   NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NaN_vals = global_df[(global_df['Country Name'].isnull()) & (global_df['2010'].isnull()) \\\n",
    "                     & (global_df['2019'].isnull())]\n",
    "print(NaN_vals.shape)\n",
    "NaN_vals"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee288316",
   "metadata": {},
   "source": [
    "Now, the only country where there is no population data is Taiwan. We can therefore search through the web their respective population for 2010 and 2019.\n",
    "\n",
    "Thanks to https://www.worldometers.info/world-population/population-by-country/ we can see that Taiwan in 2010 had a popuation of 23,187,551 and in 2019 they had a population of 23,816,775. We can now update that information in `global_df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "020a7b02",
   "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>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "      <th>Country Name</th>\n",
       "      <th>2010</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>TWN</td>\n",
       "      <td>815900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1228800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23187551.0</td>\n",
       "      <td>23816775.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country Country Code People with diabetes 2010  \\\n",
       "176  Taiwan          TWN                  815900.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2010  \\\n",
       "176                                  NaN   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "176                                                NaN            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "176                                                NaN              \n",
       "\n",
       "    People with diabetes 2019 Deaths attributable to diabetes 2019  \\\n",
       "176                 1228800.0                                  NaN   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "176                                                NaN            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \\\n",
       "176                                                NaN              \n",
       "\n",
       "    Country Name        2010        2019  \n",
       "176          NaN  23187551.0  23816775.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "global_df.loc[176,'2010'] = 23187551\n",
    "global_df.loc[176,'2019'] = 23816775\n",
    "global_df.query('Country == \"Taiwan\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea495b43",
   "metadata": {},
   "source": [
    "Done that, the last thing we need is to remove the column we are not going to be using in the analysis (Country Name), rearrange the columns and rename the `2010` and `2019` columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "638b6d84",
   "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>Country</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Population in 2010</th>\n",
       "      <th>Population in 2019</th>\n",
       "      <th>People with diabetes 2010</th>\n",
       "      <th>People with diabetes 2019</th>\n",
       "      <th>Deaths attributable to diabetes 2010</th>\n",
       "      <th>Deaths attributable to diabetes 2019</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2010</th>\n",
       "      <th>Total diabetes-related health expenditure, USD million 2019</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2010</th>\n",
       "      <th>Mean diabetes-related health expenditure per person, USD 2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>29185511.0</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>856500.0</td>\n",
       "      <td>1090800.0</td>\n",
       "      <td>19031</td>\n",
       "      <td>18630.3</td>\n",
       "      <td>28.6</td>\n",
       "      <td>182.7</td>\n",
       "      <td>33</td>\n",
       "      <td>167.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>2913021.0</td>\n",
       "      <td>2854191.0</td>\n",
       "      <td>102800.0</td>\n",
       "      <td>237600.0</td>\n",
       "      <td>1358</td>\n",
       "      <td>2448.1</td>\n",
       "      <td>26.8</td>\n",
       "      <td>155.1</td>\n",
       "      <td>261</td>\n",
       "      <td>652.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>DZA</td>\n",
       "      <td>35977451.0</td>\n",
       "      <td>43053054.0</td>\n",
       "      <td>1632100.0</td>\n",
       "      <td>1904700.0</td>\n",
       "      <td>14078</td>\n",
       "      <td>12657.2</td>\n",
       "      <td>264.2</td>\n",
       "      <td>1515.0</td>\n",
       "      <td>162</td>\n",
       "      <td>795.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>84454.0</td>\n",
       "      <td>77146.0</td>\n",
       "      <td>4600.0</td>\n",
       "      <td>6900.0</td>\n",
       "      <td>31</td>\n",
       "      <td>32.4</td>\n",
       "      <td>13.5</td>\n",
       "      <td>27.6</td>\n",
       "      <td>2970</td>\n",
       "      <td>4005.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>23356247.0</td>\n",
       "      <td>31825299.0</td>\n",
       "      <td>223700.0</td>\n",
       "      <td>532400.0</td>\n",
       "      <td>4791</td>\n",
       "      <td>6987.7</td>\n",
       "      <td>14.7</td>\n",
       "      <td>282.3</td>\n",
       "      <td>66</td>\n",
       "      <td>530.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>Venezuela (Bolivarian Republic of)</td>\n",
       "      <td>VEN</td>\n",
       "      <td>28439942.0</td>\n",
       "      <td>28515829.0</td>\n",
       "      <td>1033700.0</td>\n",
       "      <td>1403600.0</td>\n",
       "      <td>8810</td>\n",
       "      <td>10649.0</td>\n",
       "      <td>412.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>399</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>Viet Nam</td>\n",
       "      <td>VNM</td>\n",
       "      <td>87967655.0</td>\n",
       "      <td>96462108.0</td>\n",
       "      <td>1646600.0</td>\n",
       "      <td>3779600.0</td>\n",
       "      <td>32505</td>\n",
       "      <td>30096.2</td>\n",
       "      <td>101.8</td>\n",
       "      <td>1220.1</td>\n",
       "      <td>62</td>\n",
       "      <td>322.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>YEM</td>\n",
       "      <td>23154854.0</td>\n",
       "      <td>29161922.0</td>\n",
       "      <td>270000</td>\n",
       "      <td>572700.0</td>\n",
       "      <td>3410</td>\n",
       "      <td>5772.3</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>ZMB</td>\n",
       "      <td>13605986.0</td>\n",
       "      <td>17861034.0</td>\n",
       "      <td>169000</td>\n",
       "      <td>273800.0</td>\n",
       "      <td>6529</td>\n",
       "      <td>8000.2</td>\n",
       "      <td>11.1</td>\n",
       "      <td>81.3</td>\n",
       "      <td>66</td>\n",
       "      <td>296.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>12697728.0</td>\n",
       "      <td>14645473.0</td>\n",
       "      <td>235300.0</td>\n",
       "      <td>103200.0</td>\n",
       "      <td>13567</td>\n",
       "      <td>2621.9</td>\n",
       "      <td>8.9</td>\n",
       "      <td>55.8</td>\n",
       "      <td>38</td>\n",
       "      <td>540.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>202 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Country Country Code  Population in 2010  \\\n",
       "0                           Afghanistan          AFG          29185511.0   \n",
       "1                               Albania          ALB           2913021.0   \n",
       "2                               Algeria          DZA          35977451.0   \n",
       "3                               Andorra          AND             84454.0   \n",
       "4                                Angola          AGO          23356247.0   \n",
       "..                                  ...          ...                 ...   \n",
       "197  Venezuela (Bolivarian Republic of)          VEN          28439942.0   \n",
       "198                            Viet Nam          VNM          87967655.0   \n",
       "199                               Yemen          YEM          23154854.0   \n",
       "200                              Zambia          ZMB          13605986.0   \n",
       "201                            Zimbabwe          ZWE          12697728.0   \n",
       "\n",
       "     Population in 2019 People with diabetes 2010 People with diabetes 2019  \\\n",
       "0            38041757.0                  856500.0                 1090800.0   \n",
       "1             2854191.0                  102800.0                  237600.0   \n",
       "2            43053054.0                 1632100.0                 1904700.0   \n",
       "3               77146.0                    4600.0                    6900.0   \n",
       "4            31825299.0                  223700.0                  532400.0   \n",
       "..                  ...                       ...                       ...   \n",
       "197          28515829.0                 1033700.0                 1403600.0   \n",
       "198          96462108.0                 1646600.0                 3779600.0   \n",
       "199          29161922.0                    270000                  572700.0   \n",
       "200          17861034.0                    169000                  273800.0   \n",
       "201          14645473.0                  235300.0                  103200.0   \n",
       "\n",
       "    Deaths attributable to diabetes 2010 Deaths attributable to diabetes 2019  \\\n",
       "0                                  19031                              18630.3   \n",
       "1                                   1358                               2448.1   \n",
       "2                                  14078                              12657.2   \n",
       "3                                     31                                 32.4   \n",
       "4                                   4791                               6987.7   \n",
       "..                                   ...                                  ...   \n",
       "197                                 8810                              10649.0   \n",
       "198                                32505                              30096.2   \n",
       "199                                 3410                               5772.3   \n",
       "200                                 6529                               8000.2   \n",
       "201                                13567                               2621.9   \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2010  \\\n",
       "0                                                 28.6            \n",
       "1                                                 26.8            \n",
       "2                                                264.2            \n",
       "3                                                 13.5            \n",
       "4                                                 14.7            \n",
       "..                                                 ...            \n",
       "197                                              412.8            \n",
       "198                                              101.8            \n",
       "199                                                 20            \n",
       "200                                               11.1            \n",
       "201                                                8.9            \n",
       "\n",
       "    Total diabetes-related health expenditure, USD million 2019  \\\n",
       "0                                                182.7            \n",
       "1                                                155.1            \n",
       "2                                               1515.0            \n",
       "3                                                 27.6            \n",
       "4                                                282.3            \n",
       "..                                                 ...            \n",
       "197                                                NaN            \n",
       "198                                             1220.1            \n",
       "199                                                NaN            \n",
       "200                                               81.3            \n",
       "201                                               55.8            \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2010  \\\n",
       "0                                                   33              \n",
       "1                                                  261              \n",
       "2                                                  162              \n",
       "3                                                 2970              \n",
       "4                                                   66              \n",
       "..                                                 ...              \n",
       "197                                                399              \n",
       "198                                                 62              \n",
       "199                                                 74              \n",
       "200                                                 66              \n",
       "201                                                 38              \n",
       "\n",
       "    Mean diabetes-related health expenditure per person, USD 2019  \n",
       "0                                                167.5             \n",
       "1                                                652.8             \n",
       "2                                                795.4             \n",
       "3                                               4005.5             \n",
       "4                                                530.2             \n",
       "..                                                 ...             \n",
       "197                                                NaN             \n",
       "198                                              322.8             \n",
       "199                                                NaN             \n",
       "200                                              296.8             \n",
       "201                                              540.9             \n",
       "\n",
       "[202 rows x 12 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "global_df = global_df.iloc[:, [0,1,11,12,2,6,3,7,4,8,5,9]]\n",
    "global_df = global_df.rename(columns={'2010':'Population in 2010', '2019':'Population in 2019'})\n",
    "global_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df278205",
   "metadata": {},
   "source": [
    "Now that all the data frame is cleaned up, we are going to be exporting this data to be used in Tableau."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "84ab0b22",
   "metadata": {},
   "outputs": [],
   "source": [
    "global_df.to_csv('/Users/federicokurten/Desktop/Projects/Diabetes_Prevalence/Global_data_cleaned.csv')"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}