notebooks/05_module_pandas_exercises.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises module 5 - pandas\n",
    "---------------------------------------------\n",
    "\n",
    "## Exercise 5.1\n",
    "\n",
    "1. Create a pandas **DataFrame with 100 rows** and the following 3 colums:\n",
    "\n",
    "    * `row_position`: integers from 0 to 99.\n",
    "    * `row_index`: integers from 1 to 100.\n",
    "    * `random_text`: any string of your choice (can be the same for all rows).\n",
    "   \n",
    "   Here is how the DataFrame should look like (first 5 rows)\n",
    "\n",
    "row_position  | row_index | random_text\n",
    "---           | ----      | ----\n",
    "0             |  1        | hello from pandas\n",
    "1             |  2        | hello from pandas\n",
    "2             |  3        | hello from pandas\n",
    "3             |  4        | hello from pandas\n",
    "\n",
    "2. Print the first and last 5 rows of the DataFrame (in two separate commands). \n",
    "\n",
    "3. Set the values of the `row_index` column as **index** of the dataframe. After this operation, the\n",
    "   index values should be numbers from 1 to 100, and not from 0 to 99 (the default).\n",
    "\n",
    "2. Create a selection of the first 7 rows and the first 2 columns of the DataFrame using **both**\n",
    "   `.loc` and `.iloc` (i.e. please perform the selection twice, once with `.loc` and once with `.iloc`).\n",
    "\n",
    "3. Add a new column named `reverse_index` that contains index values in the reverse order (so, basically, \n",
    "   the numbers from 100 to 1).\n",
    "\n",
    "4. Delete the `index` column from the data frame.\n",
    "\n",
    "5. Create a selection of all rows with an index number multiple of 11 (i.e. rows with index 11, 22, 33, \n",
    "   etc..) and the first 2 columns of the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "### Solution:\n",
    "Uncomment and run the cell below to show the solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load solutions/solution_51.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "## Exercise 5.2\n",
    "\n",
    "In the `data` directory of the course material, you will find the following 2 files  \n",
    "(originally downloaded from [number of hospitalised persons](https://raw.githubusercontent.com/daenuprobst/covid19-cases-switzerland/master/covid19_hospitalized_switzerland_openzh.csv) and [canton demographics](https://raw.githubusercontent.com/daenuprobst/covid19-cases-switzerland/master/demographics.csv)):\n",
    "* `switzerland_covid19_hospitalized.csv`: contains daily records of the number of hospitalized\n",
    "  COVID-19 patients per Canton, from February 2020 to May 2021.\n",
    "  The count for each canton are in a separate column, whose header is the Canton abreviation.\n",
    "* `switzerland_demographics.csv`: contains the number of inhabitants (\"Population\" field) of each\n",
    "  swiss Canton, as well as other data.\n",
    "\n",
    "<br>\n",
    "\n",
    "Combining these two datasets, our objective is to identify, for each Canton, the date at which the hosptitalization rate of COVID-19 patients was maximal.  \n",
    "Here are step-by-step instructions to help you:\n",
    "\n",
    "1. Read the 2 files as pandas `DataFrame` - name them `df_hosp` (hospitalized patient count) and \n",
    "   `df_demo` (demography) respectively.\n",
    "2. Set the index `df_hosp` to the content of its `Date` column. Set the index of `df_demo` to the\n",
    "   content of its `Canton` column.\n",
    "3. In the DataFrame `df_hosp`, keep only columns `AG` to `CH`.\n",
    "4. From `df_demo`, extract the `Population` column data as a panda Series and store it in a variable\n",
    "   named `pop_by_canton`.\n",
    "  \n",
    "5. Using `pop_by_canton`, we can now convert the absolute counts of hospitalized people per canton in\n",
    "   `df_hosp` into relative counts per 10'000 inhabitants.\n",
    "   \n",
    "   **Hint:** we can take advantage of the fact that, when **dividing a DataFrame by a Series**, \n",
    "   pandas will divide each column of the DataFrame by the value in the Series whose index is matching\n",
    "   the DataFrame's column names.\n",
    "   \n",
    "   \n",
    "5. Finally, we can now get for each canton the date at which the hosptitalization rate was maximal.\n",
    "\n",
    "   **Hint:** since the index of `df_hosp` contains the calendar date associated to each row, we can\n",
    "   use the `idxmax()` method of DataFrame that returns, for each column, the index of the row that \n",
    "   contains the highest value in the column.\n",
    "   \n",
    "You can verify your result by comparing it to the values of the following 3 Cantons:\n",
    "```\n",
    "BL    2020-03-29\n",
    "VD    2020-11-16\n",
    "ZH    2020-12-21\n",
    "```\n",
    "    \n",
    "<br>\n",
    "\n",
    "**Additional task (if you have time):**\n",
    "* Using the data from you obtained in the first part of the exercise, create a new DataFrame with the \n",
    "  following 2 columns:\n",
    "    * `MaxHosp`: the maximum number of hospitalized people per 10'000 inhabitants for each canton.\n",
    "    * `Date`: the date corresponding to the value in `MaxHosp`.\n",
    "    \n",
    "* Sort this new DataFrame by the `Date` and `MaxHosp` columns, to see which Cantons had the highest\n",
    "  relative number of hospitalized people, and the chronology of when the different Cantons reached\n",
    "  their highest number of hospitalized patients."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "### Solution:\n",
    "Uncomment and run the cell below to show the solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load solutions/solution_52.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "## Exercise 5.3\n",
    "\n",
    "Load the file `data/mouse_heart_gene_expresssion.tsv` as a DataFrame.  \n",
    "Then:\n",
    "1. Center each column: substract the column's mean from their values.\n",
    "2. Select only the genes whose expression is above the column-wise average in all the WT samples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "### Solution:\n",
    "Uncomment and run the cell below to show the solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load solutions/solution_53.py"
   ]
  }
 ],
 "metadata": {
  "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}