week1_session2_numpy_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "# AI in Medicine: Data Science - Basics I\n",
    " \n",
    "## Python Programming: `numpy` and `pandas`\n",
    "\n",
    "- **Instructor**: Corey Taylor, AG Volkamer, Charité (corey.taylor@charite.de)\n",
    "- **Target audience**: Medical students from the Charité\n",
    "- **Course date**: January 2022"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Aims of this session\n",
    "\n",
    "In this talktorial, you will get in touch with **data science**. Using the **Python packages `numpy` and `pandas`**, you will load and work with the RKI COVID-19 dataset for Berlin."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Learning goals\n",
    "\n",
    "### Theory\n",
    "\n",
    "* Data science\n",
    "* The `numpy` library\n",
    "* The `pandas` library\n",
    "\n",
    "### Practical\n",
    "\n",
    "1. Dataset\n",
    "2. Read data with `pandas` as `DataFrame`\n",
    "3. Look at data\n",
    "4. Select columns\n",
    "5. Get unique entries in a column\n",
    "6. Select rows\n",
    "7. Group data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. References\n",
    "\n",
    "- Data science, machine learning, artificial intelligence\n",
    "  - http://varianceexplained.org/r/ds-ml-ai/\n",
    "- Vectors, matrices, tensors\n",
    "  - https://www.quantstart.com/articles/scalars-vectors-matrices-and-tensors-linear-algebra-for-deep-learning-part-1/\n",
    "  - https://dev.to/mmithrakumar/scalars-vectors-matrices-and-tensors-with-tensorflow-2-0-1f66\n",
    "- `numpy`\n",
    "  - https://numpy.org/doc/stable/user/absolute_beginners.html\n",
    "  - https://scipy-lectures.org/intro/numpy/array_object.html\n",
    "- `pandas`\n",
    "  - https://medium.com/dunder-data/how-to-learn-pandas-108905ab4955\n",
    "  - https://www.shanelynn.ie/select-pandas-dataframe-rows-and-columns-using-iloc-loc-and-ix/#iloc-selection\n",
    "  - https://medium.com/dunder-data/selecting-subsets-of-data-in-pandas-6fcd0170be9c\n",
    "- Datasets\n",
    "  - COVID-19 cases:  [raw RKI data](https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv)\n",
    "  - Vaccination progress in Germany: [raw RKI data](https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Daten/Impfquoten-Tab.html) and [processed data](https://github.com/ard-data/2020-rki-impf-archive)\n",
    "- Data visualization\n",
    "  - [RKI COVID-19 dashboard](https://corona.rki.de/)\n",
    "  - [COVID-19 cases for Berlin's districts > Bezirke > Übersicht](https://www.berlin.de/corona/lagebericht/)\n",
    "  - [Vaccination dashboard](https://impfdashboard.de/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Theory"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Science"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the difference between data science, machine learning, and artificial intelligence? \n",
    "\n",
    "Adapted from [David Robinson's blog post](http://varianceexplained.org/r/ds-ml-ai/).\n",
    "\n",
    "The fields data science, machine learning, and artificial intelligence do have a great deal of **overlap**, but they are **not interchangeable**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Data science** produces **insights**\n",
    "  - “The average patient has a 70% chance of survival” (descriptive: describe a dataset)\n",
    "  - “Different patients have different chances of survival” (exploratory: find relationships you did not know about)  \n",
    "  - “A randomized experiment shows that patients assigned to Alice are more likely to survive than those assigned to Bob” (correlation: find out what happens to one variable when you make another variable change)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Machine learning** (ML) produces **predictions**\n",
    "  - \"Predict whether this patient will go into sepsis”\n",
    "  - “Predict whether this image has a bird in it\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Artificial intelligence** (AI) produces **actions**\n",
    "  - Game-playing algorithms (Deep Blue, AlphaGo)\n",
    "  - Robotics and control theory (motion planning, walking a bipedal robot)\n",
    "  - Optimization (Google Maps choosing a route)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `numpy` library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Overview\n",
    "\n",
    "* Role: Scientific computing (with arrays)\n",
    "* Website: https://numpy.org/\n",
    "* Description (taken from [here](https://numpy.org/doc/stable/user/absolute_beginners.html)):\n",
    "> NumPy (Numerical Python) is an open source Python library that’s used in almost every field of science and engineering. It’s the universal standard for working with numerical data in Python, and it’s at the core of the scientific Python and PyData ecosystems. NumPy users include everyone from beginning coders to experienced researchers doing state-of-the-art scientific and industrial research and development. The NumPy API is used extensively in Pandas, SciPy, Matplotlib, scikit-learn, scikit-image and most other data science and scientific Python packages.\n",
    "* Documentation: https://numpy.org/devdocs/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Applications\n",
    "\n",
    "- Create vectors (1D), matrices (2D), tensors (>= 3D) in the form of arrays\n",
    "- Use a large collection of high-level mathematical functions to operate on these arrays\n",
    "- Used extensively in `pandas`, `scipy`, `matplotlib`, `scikit-learn` and most other data science and scientific Python packages\n",
    "\n",
    "![](https://res.cloudinary.com/practicaldev/image/fetch/s--oTgfo1EL--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://raw.githubusercontent.com/adhiraiyan/DeepLearningWithTF2.0/master/notebooks/figures/fig0201a.png)\n",
    "\n",
    "Figure source: https://dev.to/mmithrakumar/scalars-vectors-matrices-and-tensors-with-tensorflow-2-0-1f66"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `pandas` library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Overview\n",
    "\n",
    "* Role: Data manipulation and analysis\n",
    "* Website: https://pandas.pydata.org/\n",
    "* Description (taken from [here](https://pandas.pydata.org/)):\n",
    "> pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool,\n",
    "built on top of the Python programming language.\n",
    "* Documentation: https://pandas.pydata.org/pandas-docs/stable/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Applications\n",
    "\n",
    "Taken from: https://medium.com/dunder-data/how-to-learn-pandas-108905ab4955\n",
    "\n",
    "> `pandas` is capable of many tasks including:\n",
    ">\n",
    "> * Reading/writing many different data formats\n",
    "> * Selecting subsets of data\n",
    "> * Calculating across rows and down columns\n",
    "> * Finding and filling missing data\n",
    "> * Applying operations to independent groups within the data\n",
    "> * Reshaping data into different forms\n",
    "> * Visualization through matplotlib and seaborn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `DataFrame` and `Series`\n",
    "\n",
    "The `pandas` library has two main containers of data, the `DataFrame` (2D) and the `Series` (1D). \n",
    "\n",
    "- `DataFrame` [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html):\n",
    "  > Two-dimensional size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns). Arithmetic operations align on both row and column labels. Can be thought of as a dict-like container for Series objects. The primary pandas data structure\n",
    "- `Series` [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.html):\n",
    "  > One-dimensional ndarray with axis labels (including time series).\n",
    "\n",
    "\n",
    "The `DataFrame` is used more than the `Series`, so let’s take a look at its components.\n",
    "\n",
    "![DataFrame anatomy](https://raw.githubusercontent.com/volkamerlab/ai_in_medicine/master/images/dataframe_anatomy.png)\n",
    "\n",
    "Figure source: https://medium.com/dunder-data/selecting-subsets-of-data-in-pandas-6fcd0170be9c"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Practical"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>Our aim:</b> We will walk through all functionalities in pandas that we will need to visualize the latest COVID-19 case numbers for Berlin by age group and district. After you have seen how this can be done, you will get the latest data for the German vaccination progress and will plot the time course of first/second vaccinations yourself.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.1. Dataset\n",
    "\n",
    "We will work with the data on COVID-19 cases which is daily published by the Robert-Koch-Institut (RKI) and is visualized very nicely on the RKI COVID-19 Dashboard (https://corona.rki.de). \n",
    "In this notebook we will focus on data for Berlin.\n",
    "\n",
    "The dataset is freely available [here](https://npgeo-corona-npgeo-de.hub.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0): We can load the dataset directly into `pandas` from this URL: https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.2. Read data with `pandas` as `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we import the libraries `numpy` and `pandas` (abbreviated as `np` and `pd` so that we can write shorter code from here on). Libraries are a collection of functionalities that enable you to perform many common tasks without writing the whole code yourself from scratch.\n",
    "\n",
    "For instance, the `pandas` library provides the function `read_csv()` to read a comma-separated values (csv) file into a so-called `DataFrame`.\n",
    "\n",
    "**Tip**: You can check out available functionalities of a library in this Jupyter notebook, by writing the library name followed by a dot and then hitting the tab key. All available functionalities will pop up for you to explore. Since there are a lot of options, you can narrow it down by writing e.g. `read` while the popup windows is up. \n",
    "\n",
    "**Note**: If you are working in in Google Colab you will first have to disable `Automatically trigger code completions` on `Tools` > `Settings` > `Editor` in order to be able to use this feature.\n",
    "\n",
    "See for yourself all the possible file formats that you can read with `pandas`:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "pd.read  # Hit the tab key while your cursor sits after \"pd.read\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we execute (with `Enter`) this cell, we get an `AttributeError` because the module `pandas` does not know `read()`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use `?` to get a function's docstring, i.e. a description of what the function does and what kind of parameters we can pass!"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "pd.read_csv?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will now use the `read_csv()` function ([see docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html)) to load the csv file content as `DataFrame` into the variable `data_raw`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "# `read_csv` takes paths in your computer, but also Internet URLs!\n",
    "# Reading the remote csv file takes a couple of seconds\n",
    "data_raw = pd.read_csv(\"https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv\", sep=',')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at that `DataFrame` in `data_raw`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.3. Look at data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `DataFrame` head/tail\n",
    "\n",
    "Let's have a look at the first few rows of the table using the `head()` function ([see docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.head.html)). \n",
    "\n",
    "**Note**: We will use this command a lot to avoid printing large tables in this Jupyter notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_raw.head()  # Shows by default the first 5 entries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's have a look at the last few rows of the table using the `tail()` function ([see docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.tail.html)). Note that you can pass a number to the `head()` and `tail()` functions to specify how many first/last rows you want to see."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_raw.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `DataFrame` dimensionality\n",
    "\n",
    "Let's show the number of columns and rows (= dimensionality/shape) of the table in the form of `(number of rows, number of columns)` using `shape`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_raw.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `DataFrame` column names\n",
    "\n",
    "We can get all columns names using `columns`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_raw.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's list here the meaning of a few criteria (see full list on [RKI COVID-19 data download website](https://www.arcgis.com/home/item.html?id=dd4580c810204019a7b8eb3e0b329dd6)):\n",
    "\n",
    "- `Bundesland`: State name\n",
    "- `Landkreis`: District name\n",
    "- `Altersgruppe`: Age group (6 groups: `0-4`, `5-14`, `15-34`, `35-59`, `60-79`, `80+` and `unbekannt`=unknown)\n",
    "- `Geschlecht`: Gender (`M`=male, `W`=female and `unbekannt`=unknown)\n",
    "- `AnzahlFall`: Number of cases in group\n",
    "- `AnzahlTodesfall`: Number of deaths in group\n",
    "- `AnzahlGenesen`: Number of recoveries cases in group\n",
    "- `Meldedatum`: Date when case was reported to the Gesundheitsamt (you will use this in the next lesson on data visualization with `matplotlib`)\n",
    "- `Datenstand`: Date when data was updated"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can think of a `DataFrame` as a list of lists (whereby each list can contain different data types) which is shown as a table with metadata such as column and index names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_lists = [['Helen', 20, 'female'], ['Paul', 25, 'male'], ['Kim', 35, 'female']]\n",
    "list_of_lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(list_of_lists, columns=['name', 'age', 'gender'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _Your turn_: Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 1__: Get (a) the first 4 rows and (b) the last 5 rows in `data_raw`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 2__: Get (a) the number of columns and (b) the third column name in `data_raw`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 3__: Set up a `DataFrame` containing data on 4 countries:\n",
    "- Country name\n",
    "- Your favorite thing about this country\n",
    "- Have you been there already?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.4 Select columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By column name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's select some interesting columns! The `DataFrame` is quite large and we are only interested in a subset of the offered criteria. With `pandas`, it is very easy to slice the columns that you want by the following syntax:\n",
    "\n",
    "```python\n",
    "data_raw[list_of_interesting_columns]\n",
    "```\n",
    "\n",
    "The list of column names of interest could look like this:\n",
    "```python\n",
    "list_of_interesting_columns = ['Bundesland', 'Landkreis']\n",
    "```\n",
    "\n",
    "Taking both steps together it looks like this (note the two sets of `[]`, the inner `[]` is part of the list, the outer `[]` is the syntax for `DataFrame` slicing):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_raw[['Bundesland', 'Landkreis']].head()  # Note the use of .head() to show only the first 5 rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see in the following that it is possible to write a command over multiple lines to make is easier to read.\n",
    "\n",
    "Let's write this operation's output into the variable `data`; we will use this variable from now on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data_raw[\n",
    "    [\n",
    "        'Bundesland', \n",
    "        'Landkreis', \n",
    "        'Altersgruppe', \n",
    "        'Geschlecht', \n",
    "        'AnzahlFall', \n",
    "        'AnzahlTodesfall', \n",
    "        'AnzahlGenesen', \n",
    "        'Datenstand'\n",
    "    ]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You will have noticed that there is no cell output as before. This happens when the output is saved in a variable (here `data`). Let's inspect the content of `data`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By column AND index names/indices using `loc/iloc`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. __Recap__\n",
    "\n",
    "So far we sliced columns using column names like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['Bundesland', 'Landkreis']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. __`loc`__\n",
    "\n",
    "The above code is a shorter form for using `loc`:\n",
    "```python\n",
    "dataframe.loc[list_of_row_names, list_of_column_names]\n",
    "```\n",
    "`index_names` or `column_names` can be set to `:` if we want to select the full row or column, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[:, ['Bundesland', 'Landkreis']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. __`iloc`__\n",
    "\n",
    "Or, instead of row and column names, we can use their indices (like you learnt on day 1 where you selected elements from a list). \n",
    "\n",
    "```python\n",
    "dataframe.iloc[list_of_row_indices, list_of_column_indices].head()\n",
    "```\n",
    "\n",
    "Remember, in Python indices are 0-indexed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check out index of columns of interest\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.iloc[:, [2, 3]].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Note__: You will use `loc/iloc` in the notebooks to come in the next lessons, but for this lesson here, we will use column selection by column names as discussed first:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[['Bundesland', 'Landkreis']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _Your turn_: Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 4__: Select the columns listing the number of cases, deaths and recoveries using their __column names__."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 5__: Do the same as in Exercise 4 but this time use __`loc`__."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 6__: Do the same as in Exercise 4 and 5 but this time use __`iloc`__."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.5 Get unique entries in a column\n",
    "\n",
    "Now, we'd like to check what kind of entries we can find in a column. \n",
    "\n",
    "First, we select a column, similar to how we learned it in *Chapter 5.4*. Since we select this time only **one** column, we do not pass the column name as a list but as a simple string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Bundesland'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This returns a `Series` (instead of a `DataFrame`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(data['Bundesland'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's apply the `unique()` function ([see docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.unique.html)) and check the states in our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Bundesland'].unique()  # Note: Here we pass the single column as string not as list (as shown in Chapter 5.4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There should be 16 states, let's check with Python's built-in function `len` ([see docs](https://docs.python.org/3/library/functions.html#len)) that returns the length of e.g. list-like objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(data['Bundesland'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _Your turn_: Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 7__: Select the column on age groups (`'Altersgruppe'`) - which age groups are monitored?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 8__: Select the column on districts (`'Landkreis'`) - how many districts are monitored?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.6. Select rows (by conditions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Very often, you not only have more criteria (columns) in your dataset than you are actually interested in but also more data points (rows) than you need. Let's say for instance, that we are mainly interested in data points regarding Berlin. Since we have a dataset for Germany, we will need to do some (row) filtering."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's select only the state column (`Bundesland`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Bundesland']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With `Series` it is very easy to check for each row if it fullfils a given condition. As an example, let's ask for \"Thüringen\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Bundesland'] == 'Thüringen'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see, that this operation returns a `Series` of the same length and index as our initial `Series` containing boolean values (`True` or `False`).\n",
    "\n",
    "How can we use this boolean Series know to subset `data` for data points concerning Berlin (i.e. filter `data` for rows concerning Berlin)? We use the following syntax:\n",
    "\n",
    "```python\n",
    "data[condition]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Condition\n",
    "state_is_berlin = data['Bundesland'] == 'Berlin'\n",
    "\n",
    "# Subset dataset by condition\n",
    "data[state_is_berlin]  # equals\n",
    "data[data['Bundesland'] == 'Berlin']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _Your turn_: Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 9__: Select only data points for Berlin Mitte (one condition)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 10__: Select only data points for Berlin and patients between 35 and 59 years old (two conditions).\n",
    "\n",
    "```python\n",
    "# Use one condition\n",
    "data[condition]\n",
    "\n",
    "# Use multiple conditions\n",
    "data[condition1 & condition2]  # Fullfill condition 1 AND 2\n",
    "data[condition1 & not condition2]  # Fullfill condition 1 AND not 2\n",
    "data[condition1 | condition2]  # Fullfill condition 1 OR 2\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.7. Group data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From here on, we will continue to work only with data for Berlin, so we will save the subset to the new variable `data_berlin`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_berlin = data[data['Bundesland'] == 'Berlin']\n",
    "data_berlin.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html:\n",
    "\n",
    "> By `groupby()` we are referring to a process involving one or more of the following steps:\n",
    "> * **Splitting** the data into groups based on some criteria.\n",
    "> * **Applying** a function to each group independently.\n",
    "> * **Combining** the results into a data structure.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example: Get group sum with `sum()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Splitting**: Split data into groups based on a criteria."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_berlin.groupby('Altersgruppe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(data_berlin.groupby('Altersgruppe'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at one of the groups (= subset of the full DataFrame)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_berlin.groupby('Altersgruppe').get_group(\"A00-A04\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Applying and combining**: Apply function to each group, e.g. get the sum of numerical values in each group using `sum()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_berlin.groupby('Altersgruppe').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With `pandas` it is very easy to quickly plot data using the `plot()` function ([see docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.html)) - with the parameter `kind` you can specify what plot type you want to plot (in our case we want a barplot). Note that the index labels will serve as x-axis labels.\n",
    "\n",
    "Select `AnzahlFall` for the plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_berlin.groupby('Altersgruppe').sum()['AnzahlFall'].plot(\n",
    "    kind='bar', \n",
    "    title=f'Number of all COVID19 cases in Berlin since the beginning of the pandemic ({data[\"Datenstand\"].unique()[0]})'\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compare this plot with the [RKI Dashboard](https://corona.rki.de/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _Your turn_: Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 11__: Since the `groupby` functionality is very powerful but also at first difficult to wraps our head around, go through the first two examples above again in your group and discuss questions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 12__: Get number of total COVID-19 cases by Berlin's districts and compare your findings to the [official COVID-19 table for Berlin > Bezirke > Übersicht](https://www.berlin.de/corona/lagebericht/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Exercise 13__: Plot the number of total COVID-19 cases in Berlin grouped by Berlin's districts (barplot)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Discussion\n",
    "\n",
    "In this notebook, we saw how quickly possible it is to read in a csv file as `DataFrame` (*Chapter 5.2*) and to start working with it. \n",
    "- We got a first impression on our COVID-19 Berlin dataset. We looked at the number of data points (`DataFrame` rows) and criteria (`DataFrame` columns) as well as some example data points, see *Chapter 5.3*.\n",
    "- We selected interesting columns and checked what kind of column entries we can except, see *Chapter 5.4 and 5.5*. \n",
    "- We grouped data by certain criteria (columns), and applied operations on these groups, e.g. we calculated the sum within each group). We also did some first steps towards plotting with `pandas`, see *Chapter 5.6*. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Final exercise\n",
    "\n",
    "As promised at the beginning, you will get your own dataset now :)\n",
    "\n",
    "Last year during the course, we could only work with COVID-19 cases data but luckily, this year, we have something positive to look at as well - the vaccination progress in Germany! You can find that data online again at the [RKI website](https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Daten/Impfquoten-Tab.html) (under the \"Daten\" section). \n",
    "\n",
    "The provided Excel file is a bit difficult to handle, thus many GitHub repos have been set up to process the dataset into formats that are easier to work with, e.g. https://github.com/ard-data/2020-rki-impf-archive."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Let's load the cumulative vaccination progress for Germany. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vaccination_cumulative = pd.read_csv(\n",
    "    \"https://raw.githubusercontent.com/ard-data/2020-rki-impf-archive/master/data/9_csv_v3/region_DE.csv\"\n",
    ")\n",
    "vaccination_cumulative.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Let the `date` column know that it represents dates (change data structure from `object` to `datetime`). This will help us later during plotting because `pandas` will not try to label each day in the plot but maybe rather every month (depending on the range of dates)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vaccination_cumulative[\"date\"] = pd.to_datetime(vaccination_cumulative[\"date\"])\n",
    "vaccination_cumulative.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "vaccination_cumulative.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Set the date as the `DataFrame` index. Use `your_dataframe.set_index(column_name)` for that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Select only the columns containing the cumulative number of people who are fully vaccinated or vaccinated once/twice (`personen_voll_kumulativ`, `personen_erst_kumulativ`, and `personen_zweit_kumulativ`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5. Plot the cumulative time series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6. Compare your results to the data on the BMG website: https://impfdashboard.de/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Solutions__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Words of encouragement :)__ \n",
    "\n",
    "Before you take a look at the solutions, try to solve the exercises yourself. \n",
    "\n",
    "All the information needed lives in _5. Practical_ - if you are stuck, first take a look at the material there. Talk to your fellow students. If you have a solution, then go ahead and take a look here.\n",
    "\n",
    "Also note that the solutions given here show only one possibility - most of the times there are multiple options to achieve the same end result."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 1</summary>\n",
    "    \n",
    "```python\n",
    "data.head(4)\n",
    "data.tail()\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 2</summary>\n",
    "    \n",
    "```python\n",
    "len(data.columns)\n",
    "data.columns[2]\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 3</summary>\n",
    "    \n",
    "```python\n",
    "pd.DataFrame(\n",
    "    [\n",
    "        [\"France\", \"Gewürztraminer\", True], \n",
    "        [\"Australia\", \"beautiful nature\", True], \n",
    "        [\"Israel\", \"hummus\", True], \n",
    "        [\"Iceland\", \"language\", False]\n",
    "    ], \n",
    "    columns=[\"country\", \"awesome because of\", \"been there\"]\n",
    ")\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 4</summary>\n",
    "    \n",
    "```python\n",
    "data[[\"AnzahlFall\", \"AnzahlTodesfall\", \"AnzahlGenesen\"]]\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 5</summary>\n",
    "    \n",
    "```python\n",
    "data.loc[:, [\"AnzahlFall\", \"AnzahlTodesfall\", \"AnzahlGenesen\"]]\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 6</summary>\n",
    "    \n",
    "```python\n",
    "data.iloc[:, [4, 5, 6]]\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 7</summary>\n",
    "    \n",
    "```python\n",
    "data[\"Altersgruppe\"].unique()\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 8</summary>\n",
    "    \n",
    "```python\n",
    "len(data[\"Landkreis\"].unique())\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 9</summary>\n",
    "    \n",
    "```python\n",
    "data[data[\"Landkreis\"] == \"SK Berlin Mitte\"]\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 10</summary>\n",
    "    \n",
    "```python\n",
    "data[\n",
    "    (data[\"Bundesland\"] == \"Berlin\") & \n",
    "    (data[\"Altersgruppe\"] == \"A35-A59\")\n",
    "]\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 11</summary>\n",
    "    \n",
    "Go through _Chapter 5.6._ one more time.\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 12</summary>\n",
    "    \n",
    "```python\n",
    "data_berlin.groupby('Landkreis')['AnzahlFall'].sum()\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution 13</summary>\n",
    "    \n",
    "```python\n",
    "data_berlin.groupby('Landkreis')['AnzahlFall'].sum().plot(\n",
    "    kind='bar', title=f'Number of COVID-19 cases in Berlin'\n",
    ")\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "<summary> > Solution to final exercise</summary>\n",
    "    \n",
    "```python\n",
    "vaccination_cumulative = vaccination_cumulative.set_index(\"date\")\n",
    "vaccination_cumulative = vaccination_cumulative[[\"personen_erst_kumulativ\", \"personen_voll_kumulativ\", \"personen_zweit_kumulativ\"]]\n",
    "vaccination_cumulative.plot()\n",
    "```\n",
    "    \n",
    "</details>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}