FDS-S1-02-data-wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Foundations of Data Science\n",
    "## S1 Week 02: Pandas - Data Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Learning outcomes:** \n",
    "In this lab you will learn to prepare your data for future use. By the end of the lab you should be able to:\n",
    "- get an overview of your data,\n",
    "- clean data, and\n",
    "- combine data from multiple datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After analysing the survival rate of different passenger groups on the titanic last week, now imagine you are a political advisor for the UN this week. You will analyse data from the World Health Organization (WHO), in order to answer the following question:\n",
    "\n",
    "**Research question:** Which medical, social and economic factors have the biggest impact on life expectancy, and which improvement would have the biggest impact on the three countries with the shortest life expectancy?\n",
    "\n",
    "**Data description:** The data from the first [dataset](https://www.kaggle.com/kumarajarshi/life-expectancy-who) for this lab originates from the Global Health Observatory (GHO) data repository under the WHO. It contains health factors, as well as social and economic data of 193 countries between 2000 and 2015. The dataset was slightly preprocessed for this lab. The [second dataset](https://data.world/fivethirtyeight/alcohol-consumption) is based on data from an article in 'Fivethirtyeight', a website that focuses on political and economic articles and polls. It contains information about the alcohol consumption in 193 countries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remarks**\n",
    "- Starting from this lab, we will not provide all the code, but expect you to be able to draw from previous labs to fill in the code.\n",
    "- Try not to use copy+paste when coding these labs, as typing will help you memorize the code better.\n",
    "- Try to understand each detail in the code we provide, and read the comments!\n",
    "- In the text `df` refers to a generic pandas DataFrame, which allows us to indicate which methods are applied to DataFrames. E.g. in `df.head()`, the  `head` is a member function of a pandas DataFrame.\n",
    "- Finally, try to keep a **clean structure** when programming: As discussed in the previous lab, we will start by loading all libraries we need at the top of the lab."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "# Package to display the hints and soultions\n",
    "from common.show_solutions import show"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now start by loading the data we will use in this lab.\n",
    "\n",
    "**Exercise 01:** \n",
    "\n",
    "a) Load the `life_expectancy.csv` file which is in `datasets`, and store the variable as `life_expectancy`. \n",
    "\n",
    "b) Print the first few entries of the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the last lab you learned to load data, and get an overview of the data using `df.head()`. Now, we will look at higher level information of the data. Whenever you work with data, it is important to get a good idea of how your data looks like:\n",
    "- **Nature of data:** Is the data from a population (e.g. medical data from patients) or rather high-level statistics (e.g. economic data comparison of different countries)?\n",
    "- **Data format:** Is the dataset a relational database, if so what columns does it have?\n",
    "- **Missing data:** Is the data complete (e.g. does every row contain an entry in every column, or are some entries missing)?\n",
    "- **Consistent data:** Is the notation used consistent throughout the database (e.g. Nan vs None vs Null vs __ vs 0 vs -1 ...)?\n",
    "\n",
    "All those points and many more should be answered before you start running any algorithms on your data. **Clean data is key!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.A Data exploration: High level information of DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Exercise 01 you printed out the first few entries and also the column names. These entries might give you an idea of what type of entries you should expect in a specific columns. For example, in the column 'Total expenditure', which reflects the total general government expenditure on health, you should only expect numbers. However, this might not always be the case and the data might be stored in a different format from what you would expect. Columns with numbers could be floats, integers or even strings, e.g. 0 vs 0.0 vs 'Zero' vs 'NIL' vs '0'.\n",
    "\n",
    "**Exercise 02:** Use filtering to get all the entries for 'Zimbabwe'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " As you can see it contains a string 'No data' in the 'Total expenditure' column. If we print out the entry below, which looks like a float (6.44), we realize something unexpected:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.iloc[2923]['Total expenditure']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's printed out as '6.44' instead of 6.44. This is because pandas expects all entries in a specific column to be of the same type. Thus, when pandas loads the table from a csv file and finds an entry that clearly isn't a numeric value ('No data'), it casts all other entries in the same column to a string.\n",
    "\n",
    "To be certain of **what types are used** we can use `df.info()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remark** The 'Total expenditure' column has `dtype:object`, but the entries we printed out above looked like strings. To understand why pandas stores strings as objects, read this answer on [stackoverflow](https://stackoverflow.com/questions/21018654/strings-in-a-dataframe-but-dtype-is-object/21020411). **Stackoverflow** is an extremely helpful website for most questions you will encounter when coding!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get an **overview of the numerical data** you have in your dataset `.describe()` is a good option. It gives you the most important statistical analysis of your columns with numerical data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Have a look at the output of `df.describe()`.\n",
    "\n",
    "**Discussion 01:** Discuss with your lab partner what the `count` row represents. Check  the correct answer on stackoverflow or the official pandas documentation. How is this information helpful when analyzing your dataset? Use stackoverflow or the official documentation to work out how you could compute just one of the statistics for one specific column, e.g. how can we compute the mean of the 'BMI' column? Write down your answers:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your answer:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "'Total expenditure' is not listed in the statistical analysis, which is because, as mentioned above, all values were cast to a string when loaded. This is very annoying, as most values are actually numeric, and the statistical measures of the numeric values, such as the median, are still meaningful, even though not all values are numeric. To compute these values, we will have to clean up the data first. As mentioned above: **Clean data is key!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.B Cleaning data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.1 Detecting non-numerical values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A common problem in datasets is that data are not consistently of the same type. For example, a date is written in a non-standard way or a number is given as a string instead of a numeric value. Many data analysis algorithms are applied to numeric data. So let's tackle the problem we encountered above in the 'Total expenditure' column.\n",
    "\n",
    "To help us ensure that every entry in a column is numeric, pandas offers the function `pd.to_numeric(series, errors={‘ignore’, ‘raise’, ‘coerce’}, downcast = {‘integer’, ‘signed’, ‘unsigned’, ‘float’})`.\n",
    "\n",
    "This function does three things:\n",
    "1. it checks whether every entry is a numeric value\n",
    "2. if a non-numeric value is encountered (error), either it is ignored, raised (see below) or replaced by a NaN value (`error = 'coerce'`)\n",
    "3. if `downcast` is chosen (default is `None`), it casts all numeric values down to the specified parameter. \n",
    "\n",
    "**Remark:** If the chosen `dtype` occupies more bits  than the `dtype` of the value (e.g. chosen `dtype` is `float`, but the found value is `int`) `pd.to_numeric` doesn't recast the value!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy['Total expenditure'] = pd.to_numeric(life_expectancy['Total expenditure'], \n",
    "                                                    errors = 'coerce')\n",
    "life_expectancy['Total expenditure']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remark** `pd.numeric` creates a copy and does not apply the changes directly to the series, hence the need to save the changes as shown above. If you just want to check whether or not all values are numeric, you can call `pd.to_numeric(life_expectancy['Total expenditure'], errors='raise')`, which will raise errors but not save any changes.\n",
    "\n",
    "Why is it interesting to have NaN values instead of strings, such as 'No data'? Let's check the type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(np.NaN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's a float, which means that you can apply all numeric operations to entire columns; NaN values are usually just ignored. Now we can compute the missing values from our statistical analysis above.\n",
    "\n",
    "**Exercise 03:**\n",
    "\n",
    "a) Start by saving the values from the 'Total expenditure' column in a new variable `total_expenditure`. What type does `total_expenditure` have? (Try to think of the answer first, then check with `type()`.)\n",
    "\n",
    "b) Compute the eight values (count, mean, ...) using the methods you should have learned about in **Discussion 01**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remark:** By default, when loading a csv file in pandas, empty values are usually filled with *NaN values automatically*.\n",
    "\n",
    "However, even NaN values can be annoying. Where do you plot a value with x-coordinate 1 and y-coordinate NaN? There are different options for how to deal with NaN values.\n",
    "\n",
    "First, let's start by checking which values are NaN, using `.isna()`, which is a mask (we have discussed masks in lab01). It returns the same data structure as the one it is applied to, with `True` and `False` values instead of the values from the original data structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Series:\\n')\n",
    "print(life_expectancy['Life expectancy'].isna())\n",
    "print('\\n\\nDataFrames:\\n')\n",
    "print(life_expectancy.isna())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like there are no NaN in the 'Life expectancy' column. Let's double check, by summing all the values of each column. (Remember that False == 0, and True == 1)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy['Life expectancy'].isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It turns out that there are a few missing values, but none of them are at the beginning or the end, so we didn't see them. This is an **important lesson**: using `.head()` gives you a good initial idea of how the data looks like, but it is by no means comprehensive!\n",
    "\n",
    "### 2.B.2 Working with NaN values\n",
    "\n",
    "#### Dropping NaN values\n",
    "\n",
    "Now we know how to find NaN values, let's fix those values. We have a couple of options. First, if you just want to get rid of data entries with NaN values, you can drop them using `.dropna(axis={0 or'index', 1 or'columns}, how={‘any’, ‘all’}, thresh=N, subset=[col1, col2, ...])`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.dropna(axis = 0, how = 'any') # drops rows if any value is a NaN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because we specified `how='any'`, pandas drops a row if *any* column has a NaN value, so  the DataFrame is reduced to 1649 rows from 2938 rows initially. However, we wanted to discuss more ways of handling NaN values, and now we have dropped them. The good news is that  `dropna()` creates a copy, as usual with pandas DataFrame methods, and the original DataFrame is preserved. If you do want to apply those changes, you can use either `df = df.dropna(axis = 0, how = 'any')` or `df.dropna(axis = 0, how = 'any', inplace = True)`. `inplace=True` can be used in many DataFrame methods to apply the operations to the DataFrame.\n",
    "\n",
    "Now that we know that we didn't apply the changes to the DataFrame, let's discuss some more options for dealing with NaNs.\n",
    "\n",
    "Instead of `how = 'any'`, you can choose `'all'`, which drops rows only if *all* values are NaN. You can specify `axis=1`, to look along columns for NaN values and drop columns instead of rows. You can further set `subset=[col1, col2]` to search specific columns or rows for NaN values, and finally you can set a `thresh=N` to specify that at least `N` values need to be **non-NaN** to be kept. Give it a try!\n",
    "\n",
    "**Exercise 04:** Drop columns with more than 20% NaN values. Save the result to a new DataFrame and print that DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You should have got the original DataFrame without the 'Population' column.\n",
    "\n",
    "#### Overwriting NaN values\n",
    "If you don't want to drop your data entries you can replace the values. You have many options, including: \n",
    "- The mean, max or min value of the column\n",
    "- strings\n",
    "- 0\n",
    "\n",
    "The function needed is `df.fillna(value={scalar, dict}, method={‘bfill’, ‘ffill’}, axis={0, 1}, limit=N)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.fillna(0).loc[2922] # Get row 2922 of the DataFrame where NaN values are replaced with 0."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above we have applied two operations at once to the DataFrame. It shows the row we initially inspected where 'Total expenditure' = 'No data'. Now it is 0.\n",
    "\n",
    "**Remark:** In some cases, it makes more sense to replace different NaN values according to the column they are in. You can do this by passing a dictionary instead of a scalar. `df.fillna(value={'col1': value1, 'col2': value2})`. For more information on `fillna()` you are invited to check the [pandas documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html).\n",
    "\n",
    "We will only be interested in entries with no NaN values for the rest of this lab, so we will drop all entries with NaN values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.C Unique values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far, we have loaded the data, we have extracted some high-level information about the data set, and dealt with missing data entries. These steps are important initial steps that need to be taken whenever you deal with data, especially data you haven't generated yourself.\n",
    "\n",
    "Another important step is to check whether your data is free of duplicate observations. Let's check how many unique values we have in each column of the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that, for example, in 'Status', we only have two unique values. Let's check which ones they are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.unique(life_expectancy['Status'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remark:** `df.nunique()` is applied to the data structure (Series or DataFrames), whereas `pd.unique()` takes the data structure as a parameter.\n",
    "\n",
    "The above query showed us that there are two categories in the 'Status' column: Developing and Developed. Let's find out the impact of whether a country is considered to be developing or developed.\n",
    "\n",
    "**Exercise 05:** \n",
    "\n",
    "a) Split the dataset into two subsets with the names 'developed' and 'developing' according to the status of the countries.\n",
    "\n",
    "b) Compute the mean life expectancy of each subset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Discussion:** Is the difference of life expectancy between the two groups about what you expected? If not what statistical metric could help you understand why the life expectancy difference is not what you expected? If you can think of a specific metric, compute that metric and see whether this confirms your expectations. Write down your answers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=5.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your answer:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have found the unique values, but `pd.unique` searches unique values in each column. To search for entire rows of data that duplicate other rows, we need to use `df.duplicated(subset=[col1, col2, ...], keep={'first', 'last', False})`. This function is a mask; by now you should know how to use masks. It returns a Series with `True` and `False` entries, where `True` marks the rows that are duplicates of another row. The subset parameter allows you to specify which columns to consider when checking for duplicates. This can be very helpful when you have a column of unique identifiers, and you want to know if there are rows that are identical apart from the unique identifier. For example, different patients (with different IDs = unique identifier) might have the same symptoms. The `keep` parameter lets you specify which occurrence of a duplicate to mark as `False` (i.e. not as a duplicate): \n",
    "- `'first'`, which is the default value, marks the first occurrence as not a duplicate, but all other occurrences as duplicates of the first occurrence, \n",
    "- `'last'` marks the last occurrence as not a duplicate, but all other occurrences as duplicates of the last occurrence, and\n",
    "- `False` marks all occurrences of duplicates as duplicates.\n",
    "\n",
    "Let's give it a try:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.duplicated()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As promised above, `.duplicated()` gives you a mask. It looks like no entry is a duplicate. Let's check."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Remember, `False==0`, `True==1`; thus summing the series tells you how many `True` entries there were in the Series). The above tells you that there are no duplicates. Thankfully the WHO, as we would hope, created a clean dataset.\n",
    "\n",
    "Let's check whether there are countries in which the life expectancy remained the same over the years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "life_expectancy.duplicated(subset=['Country', 'Life expectancy']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 136 instances in which the life expectancy did not change. Let's print them.\n",
    "\n",
    "**Exercise 06:** \n",
    "- Print *all* occurrences of duplicates.\n",
    "- Print all the countries that appear in the list. Each country should only appear once.\n",
    "- Create a new DataFrame from the `life_expectancy` DataFrame, with the name `cleaned_life_expectancy`, in which  each country only appears once. Since the entries are sorted newest to oldest, only keep the newest observation of each country. Print out the first few lines to check your DataFrame looks as expected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have finished cleaning our dataset. Let's plot some of the results. You can ignore the code below; we will learn about plotting in the next two labs and this is just to give you an idea of your data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_life_expectancy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.pairplot(cleaned_life_expectancy, x_vars = ['Life expectancy'], y_vars = ['percentage expenditure', 'Hepatitis B', 'Measles',\n",
    "       'BMI', 'under-five deaths', 'Polio', 'Total expenditure',\n",
    "       'Diphtheria', 'HIV/AIDS', 'GDP', 'Population',\n",
    "       'thinness 1-19 years', 'thinness 5-9 years',\n",
    "       'Income composition of resources', 'Schooling'], hue='Status', palette='Dark2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Discussion:** Discuss with your lab partner which factors seem to have a clear correlation with life expectancy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with the solution\n",
    "show(week=2, question=6.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your answer:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Up to now, we have used single datasets and analyzed them. The WHO dataset contains medical and economic factors, and the plot above showed whether or not there was a correlation between those factors and life expectancy. \n",
    "\n",
    "Now suppose that a politician approaches you who is interested in whether limiting alcohol consumption might have a positive effect on life expectancy. To analyze this you need to use a second dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drinks_by_country_loc = os.path.join(os.getcwd(), 'datasets', 'drinks_by_country.csv')\n",
    "drinks_by_country = pd.read_csv(drinks_by_country_loc)\n",
    "drinks_by_country.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.D Combining data from two datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You will rarely find all the information you need in one dataset. Being able to combine data from different datasets into one is very important. Practise it until you feel very comfortable; having just tried it once is not enough. \n",
    "\n",
    "There a various ways of combining data from datasets: *concatenation*, *merging* and *joining*."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenation\n",
    "\n",
    "Let's start with the arguably simplest pandas function: `concat`\n",
    "\n",
    "When you concatenate two *data structures*, pandas DataFrames or pandas series, you simply append one to the other along one axis (along rows or along columns), i.e. you expect that the new data is \"independent\" of the existing data. \n",
    "\n",
    "Let's illustrate that on a simple toy example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                    'B': ['B0', 'B1', 'B2', 'B3'],\n",
    "                    'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                    'D': ['D0', 'D1', 'D2', 'D3']},\n",
    "                    index=[0, 1, 2, 3])\n",
    "\n",
    "df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],\n",
    "                    'B': ['B4', 'B5', 'B6', 'B7'],\n",
    "                    'C': ['C4', 'C5', 'C6', 'C7'],\n",
    "                    'D': ['D4', 'D5', 'D6', 'D7']},\n",
    "                    index=[0, 1, 2, 3])\n",
    "\n",
    "print(df1)\n",
    "print('\\n\\n')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.concat([df1, df2])\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remarks:** \n",
    "- Both DataFrames had entries with indices 0-3. However, using `concat` means that there is no relational connection between the rows, and thus `df2` is just appended to `df1`.\n",
    "- `pd.concat()` takes as input a list of data structures and can concatenate several DataFrames at once.\n",
    "\n",
    "As mentioned above, we can specify along which axis the data structures should be concatenated. The default is `axis=0`, as above. We hinted above that `concat` and `join` have two different meanings. In fact, `join={'inner', 'outer'}` is a parameter allowing you to specify the set logic of how data entries that only exist in one set but not the other should be treated. Using *inner* join means that you only want the intersection, while *outer* join means you want to keep the union. Sounds a bit abstract? Let's try it out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                    'B': ['B0', 'B1', 'B2', 'B3'],\n",
    "                    'D': ['D0', 'D1', 'D2', 'D3']},\n",
    "                    index=[0, 1, 2, 3])\n",
    "\n",
    "df4 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],\n",
    "                    'B': ['B4', 'B5', 'B6', 'B7'],\n",
    "                    'C': ['C4', 'C5', 'C6', 'C7']},\n",
    "                    index=[0, 1, 2, 3])\n",
    "\n",
    "print(df3)\n",
    "print('\\n\\n')\n",
    "print(df4)\n",
    "\n",
    "result = pd.concat([df3, df4], axis=0, join='outer')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the example above along `axis=0`, one dataset has the columns 'A', 'B', and 'D', while the other has the columns 'A', 'B', and 'C'. Because we specified `join='outer'` it took the *union* of the two column sets, i.e. 'A', 'B', 'C', and 'D' and just filled out the missing information with `NaN` values. What would happen in the case of `join='inner'`? Give it a try. Is it what you would expect?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above we have seen, that indices 0-3 were repeated, because we just appended one dataset to the other and didn't assume any relation. However, in that case the indices most probably don't even have a meaning, and could be ignored. This can be achieved with, who could have guessed, `ignore_index=True`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.concat([df1, df2], ignore_index=True)\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging\n",
    "\n",
    "What can we do, when we have a relation between two datasets? For example, in both the life expectancy dataset and the alcohol consumption dataset, we have the column 'Country'. Combining both datasets by comparing those columns and combining the corresponding data entries would be very helpful.\n",
    "\n",
    "Let's consider a toy example again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'key': ['K0', 'K1', 'K2'],\n",
    "                     'A': ['A0', 'A1', 'A2'],\n",
    "                     'B': ['B0', 'B1', 'B2']})\n",
    "\n",
    "right = pd.DataFrame({'key': ['K1', 'K0', 'K3'],\n",
    "                      'C': ['C0', 'C1', 'C3'],\n",
    "                      'D': ['D0', 'D1', 'D3']})\n",
    "print(left)\n",
    "print('\\n\\n')\n",
    "print(right)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.merge(left, right, on='key')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With `on='column'` we specify the column the two datasets will be compared on.\n",
    "\n",
    "**Remarks**\n",
    "- In contrast to `pd.concat()`, `pd.merge()` only takes two DataFrames as input (left and right)\n",
    "- `pd.merge()` compares the rows across all data entries. For example, in the 'right' dataset the data entry 'K0' is in the second row though in the left data set it is in the first row.\n",
    "- As you can see above, by default, `pd.merge()` uses an inner join.\n",
    "- The column specified by the keyword 'on' needs to be *written the same way* in both datasets.\n",
    "\n",
    "With `pd.concat()` the 'join' parameter specifies an inner join and an outer join. With `pd.merge()` we have the same option, only the keyword is `how={'inner', 'outer', 'left', 'right'}`, with the two extra options `left` and `right`. Technically `left` stands for 'left outer join', and means that:\n",
    "- we keep all rows that appear in the left dataset\n",
    "- merge the matching rows from the right dataset into the left dataset\n",
    "- drop all rows that only appear in the right dataset.\n",
    "\n",
    "Let's try it out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.merge(left, right, on='key', how='left')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "'K0', 'K1', and 'K2' are kept, as they all appear in the `left` dataset. However, 'K3', which only appears in the right dataset is dropped.\n",
    "\n",
    "**Note on performance**: By default, `merge` will sort the output based on the values in the key column in lexicographic order, which can affect the performance substantially, if you do not care about the order, you can set `sort=False`.\n",
    "\n",
    "There are a couple of more parameters you can set, in both `concat` and `merge`. However, the above are the most important. If you are interested, check out the [pandas website](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html).\n",
    "\n",
    "**Exercise 07:** Use either `pd.merge` or `pd.concat` to combine the two datasets(`cleaned_life_expectancy`, `drinks_by_country`) so we can relate drinks consumption to life expectancy. We don't want to have unnecessary NaN values, so only keep the entries that are found in both datasets. Save the result to `final_life_expectancy`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, ignore the code below. Just run it and compare the impact of different drinks on life expectancy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.pairplot(final_life_expectancy, x_vars = ['Life expectancy'], y_vars = ['Beer Servings', 'Spirit Servings', 'Wine Servings', 'Total Litres of Pure Alcohol'], hue='Status', palette='Dark2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Discussion:** \n",
    "- Does the data above suggest that alcohol consumption as an average for a population has an impact on life expectancy?\n",
    "- Find the three countries with the lowest life expectancy from `final_life_expectancy`.\n",
    "- Have a look at the plots above, and discuss with your lab partner: Can we pinpoint the short life expectancy to specific factors? What changes would help those countries probably most to improve their life expectancy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Run this cell to be offered with hints and solution\n",
    "show(week=2, question=7.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your answer: \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.17"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}