problem_sets/problem_set_5.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c846f484",
   "metadata": {},
   "source": [
    "# Problem Set 5\n",
    "\n",
    "See [Introduction](https://datascience.quantecon.org/../pandas/intro.html) and [Basic Functionality](https://datascience.quantecon.org/../pandas/basics.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1b816f82",
   "metadata": {
    "hide-output": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec64d73c",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "These questions use data on daily Covid cases in health regions in Canada from the [COVID-19 Canada Open Data Working Group](https://github.com/ccodwg/Covid19Canada)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "62963847",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "url = \"https://github.com/ccodwg/Covid19Canada/raw/master/timeseries_hr/cases_timeseries_hr.csv\"\n",
    "try : # only download if cases_raw has not already been defined \n",
    "    cases_raw\n",
    "except:\n",
    "    cases_raw = pd.read_csv(url, parse_dates=[\"date_report\"])\n",
    "\n",
    "try :\n",
    "    hr_map \n",
    "except: \n",
    "    hr_map = pd.read_csv(\"https://github.com/ccodwg/Covid19Canada/raw/master/other/hr_map.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "676a013f",
   "metadata": {},
   "source": [
    "Now, we create cases per 100,000 and then do the same manipulation as in the pandas basics lecture. We will focus on BC health regions in this problem set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c354eb3a-fa30-4cab-ac3a-9a967c9cc922",
   "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>province</th>\n",
       "      <th>health_region</th>\n",
       "      <th>date_report</th>\n",
       "      <th>cases</th>\n",
       "      <th>cumulative_cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alberta</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>2020-01-25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alberta</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alberta</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alberta</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alberta</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>2020-01-29</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78025</th>\n",
       "      <td>Yukon</td>\n",
       "      <td>Yukon</td>\n",
       "      <td>2022-02-23</td>\n",
       "      <td>8</td>\n",
       "      <td>3412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78026</th>\n",
       "      <td>Yukon</td>\n",
       "      <td>Yukon</td>\n",
       "      <td>2022-02-24</td>\n",
       "      <td>14</td>\n",
       "      <td>3426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78027</th>\n",
       "      <td>Yukon</td>\n",
       "      <td>Yukon</td>\n",
       "      <td>2022-02-25</td>\n",
       "      <td>0</td>\n",
       "      <td>3426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78028</th>\n",
       "      <td>Yukon</td>\n",
       "      <td>Yukon</td>\n",
       "      <td>2022-02-26</td>\n",
       "      <td>0</td>\n",
       "      <td>3426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78029</th>\n",
       "      <td>Yukon</td>\n",
       "      <td>Yukon</td>\n",
       "      <td>2022-02-27</td>\n",
       "      <td>0</td>\n",
       "      <td>3426</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78030 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      province health_region date_report  cases  cumulative_cases\n",
       "0      Alberta       Calgary  2020-01-25      0                 0\n",
       "1      Alberta       Calgary  2020-01-26      0                 0\n",
       "2      Alberta       Calgary  2020-01-27      0                 0\n",
       "3      Alberta       Calgary  2020-01-28      0                 0\n",
       "4      Alberta       Calgary  2020-01-29      0                 0\n",
       "...        ...           ...         ...    ...               ...\n",
       "78025    Yukon         Yukon  2022-02-23      8              3412\n",
       "78026    Yukon         Yukon  2022-02-24     14              3426\n",
       "78027    Yukon         Yukon  2022-02-25      0              3426\n",
       "78028    Yukon         Yukon  2022-02-26      0              3426\n",
       "78029    Yukon         Yukon  2022-02-27      0              3426\n",
       "\n",
       "[78030 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cases_raw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8d9c8075",
   "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>health_region</th>\n",
       "      <th>Fraser</th>\n",
       "      <th>Interior</th>\n",
       "      <th>Island</th>\n",
       "      <th>Northern</th>\n",
       "      <th>Vancouver Coastal</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_report</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-01-01</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-01-02</th>\n",
       "      <td>25.701861</td>\n",
       "      <td>24.589675</td>\n",
       "      <td>9.101332</td>\n",
       "      <td>33.213897</td>\n",
       "      <td>24.191511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-01-03</th>\n",
       "      <td>45.464436</td>\n",
       "      <td>9.935222</td>\n",
       "      <td>10.519722</td>\n",
       "      <td>39.252788</td>\n",
       "      <td>26.815675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-01-04</th>\n",
       "      <td>21.129137</td>\n",
       "      <td>7.203036</td>\n",
       "      <td>6.500952</td>\n",
       "      <td>17.110190</td>\n",
       "      <td>21.813363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-01-05</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-27</th>\n",
       "      <td>42.153154</td>\n",
       "      <td>36.387752</td>\n",
       "      <td>24.585417</td>\n",
       "      <td>17.781177</td>\n",
       "      <td>28.865804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-28</th>\n",
       "      <td>41.995474</td>\n",
       "      <td>33.282995</td>\n",
       "      <td>33.686749</td>\n",
       "      <td>13.755250</td>\n",
       "      <td>32.966060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-29</th>\n",
       "      <td>83.623027</td>\n",
       "      <td>46.447164</td>\n",
       "      <td>36.523528</td>\n",
       "      <td>33.213897</td>\n",
       "      <td>46.824926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-30</th>\n",
       "      <td>121.886737</td>\n",
       "      <td>62.219330</td>\n",
       "      <td>54.371594</td>\n",
       "      <td>40.930258</td>\n",
       "      <td>80.119006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-31</th>\n",
       "      <td>102.176722</td>\n",
       "      <td>39.740889</td>\n",
       "      <td>51.298417</td>\n",
       "      <td>43.278715</td>\n",
       "      <td>79.134944</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>365 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "health_region      Fraser   Interior     Island   Northern  Vancouver Coastal\n",
       "date_report                                                                  \n",
       "2021-01-01       0.000000   0.000000   0.000000   0.000000           0.000000\n",
       "2021-01-02      25.701861  24.589675   9.101332  33.213897          24.191511\n",
       "2021-01-03      45.464436   9.935222  10.519722  39.252788          26.815675\n",
       "2021-01-04      21.129137   7.203036   6.500952  17.110190          21.813363\n",
       "2021-01-05       0.000000   0.000000   0.000000   0.000000           0.000000\n",
       "...                   ...        ...        ...        ...                ...\n",
       "2021-12-27      42.153154  36.387752  24.585417  17.781177          28.865804\n",
       "2021-12-28      41.995474  33.282995  33.686749  13.755250          32.966060\n",
       "2021-12-29      83.623027  46.447164  36.523528  33.213897          46.824926\n",
       "2021-12-30     121.886737  62.219330  54.371594  40.930258          80.119006\n",
       "2021-12-31     102.176722  39.740889  51.298417  43.278715          79.134944\n",
       "\n",
       "[365 rows x 5 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cases_bc = cases_raw.loc[(cases_raw['province'] == 'BC') &  \n",
    "                         (cases_raw['date_report'] < pd.to_datetime('2022-01-01')) &\n",
    "                         (cases_raw['date_report'] >= pd.to_datetime('2021-01-01')),:] # Take the data for BC in year 2021 only\n",
    "# create cases per 100,000\n",
    "cases_bc = cases_bc.merge(hr_map[['province','health_region','pop']],\n",
    "                          on=['province','health_region'],\n",
    "                          how='left')\n",
    "cases_bc['cases100k'] = cases_bc['cases'] / cases_bc['pop'] * 100_000\n",
    "cases_bc = ( \n",
    "    cases_bc.reset_index()\n",
    "    .pivot_table(index='date_report',columns='health_region', values='cases100k')\n",
    ")    \n",
    "cases_bc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da46b1cd",
   "metadata": {},
   "source": [
    "The resulting `cases_bc` DataFrame contains Covid cases per 100,000 population for each BC health region and day, in 2021."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50254b0a",
   "metadata": {},
   "source": [
    "## Question 1\n",
    "\n",
    "At each date, what is the minimum number of cases per 100,000 across health regions?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44208577",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcdede75",
   "metadata": {},
   "source": [
    "What was the (daily) median number of cases per 100,000 in each health region?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b32abe9",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b91179ff",
   "metadata": {},
   "source": [
    "What was the maximum number of cases per 100,000 across health regions? In what health region did it happen? On what date was this achieved?\n",
    "\n",
    "- Hint 1: What Python type (not `dtype`) is returned by a reduction?  \n",
    "- Hint 2: Read documentation for the method `idxmax`.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c761cd3",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93a96e55",
   "metadata": {},
   "source": [
    "Classify each health region as high or low volatility based on whether the variance of their cases per 100,000 is above or below 100."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4befbb8a-df79-494b-94d8-fa7972ec18af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65c21d92",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Question 2\n",
    "\n",
    "Imagine that we want to determine whether cases per 100,000 was High (> 10),\n",
    "Low (0 < x <= 10), or None (x = 0) for each health region and each day.\n",
    "\n",
    "Write a Python function that takes a single number as an input and\n",
    "outputs a single string which notes whether that number is High, Low, or None."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8d067f2",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e2acdef",
   "metadata": {},
   "source": [
    "Pass your function to either `apply` or `applymap` and save the result in a new DataFrame called `case_bins`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6241de94",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7228672f",
   "metadata": {},
   "source": [
    "## Question 3\n",
    "\n",
    "This exercise has multiple parts:\n",
    "\n",
    "Use another transformation on `case_bins` to count how many times each health region had each of the three classifications.\n",
    "\n",
    "- Hint 1: Will you need to use `apply` or `applymap` for transformation?  \n",
    "- Hint 2: `value_counts`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d287f65a",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5961213",
   "metadata": {},
   "source": [
    "Construct a horizontal bar chart (you can refer to an example of horizontal bar chart [here](https://www150.statcan.gc.ca/edu/power-pouvoir/c-g/c-g05-2-3-eng.png)) to detail the occurrences of each level.\n",
    "Use one bar per health region and classification for 15 total bars."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "511481a7",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "495f36a2",
   "metadata": {},
   "source": [
    "## Question 4\n",
    "\n",
    "For a single health region of your choice, determine the mean\n",
    "cases per 100,000 during “High” and “Low” case times.\n",
    "(recall your `case_bins` DataFrame from the exercise above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c823431",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1633950",
   "metadata": {},
   "source": [
    "Which health regions in our sample performs the best during “bad times\" ? To\n",
    "determine this, compute each health region’s mean daily cases per 100,000 where the daily cases per 100,000 is greater than 10 (i.e., in the \"high\"\n",
    "category as defined above)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7eb3d125",
   "metadata": {
    "hide-output": false
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  }
 ],
 "metadata": {
  "date": 1633586292.4168322,
  "filename": "problem_set_5.md",
  "interpreter": {
   "hash": "767d51c1340bd893661ea55ea3124f6de3c7a262a8b4abca0554b478b1e2ff90"
  },
  "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.11"
  },
  "title": "Problem Set 5"
 },
 "nbformat": 4,
 "nbformat_minor": 5
}