Assignment_combine_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assignment Covid\n",
    "\n",
    "You will learn about combining data with pandas and numpy. Learning outcomes:\n",
    "\n",
    "- Load a tabular dataset\n",
    "- Inspect the dataset for quality and metadata information\n",
    "- Combine data from several tables into one dataframe\n",
    "- Subselect specific data from dataframes\n",
    "- Reshape the dataset into a format suitable for visual and statistical analysis\n",
    "\n",
    "\n",
    "The assignment consists of 6 parts:\n",
    "\n",
    "- [part 1: load the data](#0)\n",
    "     - [Exercise 1.1](#ex-11)\n",
    "- [part 2: data wrangling](#1)\n",
    "     - [Exercise 2.1](#ex-21)\n",
    "- [part 3: display data](#2)\n",
    "     - [Exercise 3.1](#ex-31)\n",
    "\n",
    "\n",
    "## About the data\n",
    "\n",
    "The data is generated by Synthea's COVID-19 module. The data was constructed using three peer-reviewed publications published in the early stages of the global pandemic, when less was known, along with emerging resources, data, publications, and clinical knowledge. The simulation outputs synthetic Electronic Health Records (EHR), including the daily consumption of Personal Protective Equipment (PPE) and other medical devices and supplies. For this assignment the `conditions`, `patients`, `observations`, `careplans` and `encounters` table will be used. The Data is stored in separate tables to avoid redundancy, with as a concequence that tables need to be combined and reorganized in dataframes for analysing purpose.\n",
    "\n",
    "Source: Walonoski J, Klaus S, Granger E, Hall D, Gregorowicz A, Neyarapally G, Watson A, Eastman J. Synthea™ Novel coronavirus (COVID-19) model and synthetic data set. Intelligence-Based Medicine. 2020 Nov;1:100007. https://doi.org/10.1016/j.ibmed.2020.100007\n",
    "\n",
    "Please <a href = \"https://storage.googleapis.com/synthea-public/10k_synthea_covid19_csv.zip\">download</a> the data\n",
    "\n",
    "#### Covid Patients\n",
    "Patients are considered Covid patients if they are identified with `CODE` `840539006`\n",
    "\n",
    "\n",
    "#### Survivors\n",
    "Patients that had covid and where tested negative after isolation have tested code `94531-1`,  SARS-CoV-2 RNA Pnl Resp NAA+probe (covid-sars test) + a value of `Not detected (qualifier value)`. These patients are considered to be survived covid patients. \n",
    "\n",
    "#### Non-Survivors\n",
    "Patients that did not survived Covid have a `DEATHDATE` which is not null. \n",
    "\n",
    "\n",
    "#### Lab values  COVID-19 patients\n",
    "\n",
    "Patients are monitored for blood and heart conditions once they are admitted in Hospital or under treatment. The lab values of interest are as follow: \n",
    "\n",
    "- `48065-7`  Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma\n",
    "- `26881-3`   Interleukin 6 [Mass/volume] in Serum or Plasma\n",
    "- `2276-4` Ferritin [Mass/volume] in Serum or Plasma\n",
    "- `89579-7` Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method\n",
    "- `731-0` Lymphocytes [#/volume] in Blood by Automated count\n",
    "- `14804-9` Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reaction\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name='0'></a>\n",
    "## Part 1: Load the data (20 pt)\n",
    "\n",
    "Instructions: Load the data of the following files\n",
    "\n",
    "- conditions.csv\n",
    "- patients.csv\n",
    "- observations.csv\n",
    "- careplans.csv\n",
    "- encounters.csv\n",
    "\n",
    "Once we read the data we can get familiar with the data. We create some meaningful overviews like \n",
    "\n",
    "1. How many patients are there\n",
    "2. How many covid-patients are there\n",
    "3. How many patients do have a 'Hospital admission for isolation' encounter\n",
    "\n",
    "\n",
    "<a name='ex-11'></a>\n",
    "### 1.1 Code your solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#YOUR CODE HERE\n",
    "conditions = \"\"\n",
    "patients = \"\"\n",
    "observations = \"\"\n",
    "care_plans = \"\"\n",
    "encounters = \"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 Test your solution\n",
    "The following function needs to be called. You can use this as a test. There are however more meaningful overviews \n",
    "you can create. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 12352 patients in total\n",
      "There are 8820 covid patients\n",
      "There are 1867 admitted patients\n",
      "2352 patients died\n"
     ]
    }
   ],
   "source": [
    "# - How many patients are there\n",
    "num_pat = len(set(patients['Id']))\n",
    "# - How many covid-patients are there\n",
    "df_cov = conditions[conditions['CODE'] == 840539006].copy() #make a different df for patients with covid\n",
    "num_cov = len(set(df_cov['PATIENT']))\n",
    "# - How many patients do have a 'Hospital admission for isolation' encounter\n",
    "patients_died = patients[~patients['DEATHDATE'].isnull()].copy()\n",
    "num_died = len(set(patients_died['Id']))\n",
    "admitted_enco = encounters[encounters['DESCRIPTION'] == 'Hospital admission for isolation (procedure)'].copy()\n",
    "num_admitted = len(set(admitted_enco['Id']))\n",
    "\n",
    "def part1(num_pat, num_cov, num_admitted, num_died):\n",
    "    print(f'There are {num_pat} patients in total')\n",
    "    print(f'There are {num_cov} covid patients')\n",
    "    print(f'There are {num_admitted} admitted patients')\n",
    "    print(f'{num_died} patients died')\n",
    "    \n",
    "part1(num_pat, num_cov, num_admitted, num_died)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name='1'></a>\n",
    "## Part 2: Data Wrangling: set up the dataframe (30 pt)\n",
    "\n",
    "In this part we are going to combine data to create a dataframe with values of interest for the lab values analysis. \n",
    "\n",
    "We would like a dataframe containing the following information per record (only Covid patients!!!)\n",
    "\n",
    "- `PATIENT` - the ID of the covid patient\n",
    "- `days` - the number of days the patient is under observation\n",
    "- `CODE-Y` - the code of the observation  \n",
    "- `VALUE` - the lab value of the observation\n",
    "\n",
    "where only the following observation codes needs to be selected:\n",
    "\n",
    "- `48065-7`  Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma\n",
    "- `26881-3`   Interleukin 6 [Mass/volume] in Serum or Plasma\n",
    "- `2276-4` Ferritin [Mass/volume] in Serum or Plasma\n",
    "- `89579-7` Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method\n",
    "- `731-0` Lymphocytes [#/volume] in Blood by Automated count\n",
    "- `14804-9` Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reaction\n",
    "\n",
    "The days information is not primarely available and needs to be calculated by substracting observation DATE - START. \n",
    "\n",
    "An example of such a dataframe is given below:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "                                      days   CODE-Y  VALUE    UNITS\n",
    "PATIENT                                                            \n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   0.0  14804-9  241.8      U/L\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   0.0   2276-4  489.5     ug/L\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   0.0  48065-7    0.4    ug/mL\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   0.0    731-0    1.1  10*3/uL\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   0.0  89579-7    3.4    pg/mL\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   1.0    731-0    1.1  10*3/uL\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   2.0  14804-9  233.7      U/L\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   2.0   2276-4  497.3     ug/L\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   2.0  48065-7    0.3    ug/mL\n",
    "00079a57-24a8-430f-b4f8-a1cf34f90060   2.0    731-0    1.0  10*3/uL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Possible approach:\n",
    "\n",
    "#Combine conditions table (only covid patients, the df_cov) with the patient table into a covid_patient table\n",
    "#select the only the relevant lab observations from the observations table into a lab_obs table\n",
    "#merge the covid_patient table with the lab_obs table into a covid_patients_obs table\n",
    "#clean the covid_patients_obs table (rename columns, select only relevant columns, sort, typecast, add days column)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>    \n",
    "<summary>\n",
    "    <font size=\"3\" color=\"darkgreen\"><b>Hints</b></font>\n",
    "</summary>\n",
    "    <ul><li>you can use pandas.DataFrame.merge() to merge dataframes</li>\n",
    "    <li>df = df[(df.CODE == condition1 | df.CODE == condition1 )] selects rows with CODE of 2 conditional values</li>\n",
    "    <li>df.DATE - df.START return days if DATE and START are datetime format</li>\n",
    "    <li>use observations[observations['CODE'].isin(['48065-7','26881-3', '2276-4', '89579-7', '731-0', '14804-9'])] to select the codes</li>\n",
    "    <li>pd.to_datetime() can be used to typecast to datetime</li>\n",
    "</ul>\n",
    "</details>\n",
    "\n",
    "<a name='ex-21'></a>\n",
    "### 2.1 Code your solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Combine conditions table (only covid patients) with the patient table into a covid_patient table\n",
    "covid_patient = \"your code here\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#select the only the relevant lab observations from the observations table into a lab_obs table (see hints above)\n",
    "lab_obs = \"your code here\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>START</th>\n",
       "      <th>STOP</th>\n",
       "      <th>PATIENT</th>\n",
       "      <th>ENCOUNTER_x</th>\n",
       "      <th>CODE_x</th>\n",
       "      <th>DESCRIPTION_x</th>\n",
       "      <th>Id</th>\n",
       "      <th>BIRTHDATE</th>\n",
       "      <th>DEATHDATE</th>\n",
       "      <th>SSN</th>\n",
       "      <th>...</th>\n",
       "      <th>LON</th>\n",
       "      <th>HEALTHCARE_EXPENSES</th>\n",
       "      <th>HEALTHCARE_COVERAGE</th>\n",
       "      <th>DATE</th>\n",
       "      <th>ENCOUNTER_y</th>\n",
       "      <th>CODE_y</th>\n",
       "      <th>DESCRIPTION_y</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>UNITS</th>\n",
       "      <th>TYPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>2020-02-28</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>e3143bce-4a59-40aa-a198-7a9e54077fd8</td>\n",
       "      <td>840539006</td>\n",
       "      <td>COVID-19</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>2019-06-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>999-81-4349</td>\n",
       "      <td>...</td>\n",
       "      <td>-72.722648</td>\n",
       "      <td>22940.0</td>\n",
       "      <td>893.28</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>e97e8d37-7497-4c13-98fd-a4a45655c0bb</td>\n",
       "      <td>731-0</td>\n",
       "      <td>Lymphocytes [#/volume] in Blood by Automated c...</td>\n",
       "      <td>1.1</td>\n",
       "      <td>10*3/uL</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>2020-02-28</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>e3143bce-4a59-40aa-a198-7a9e54077fd8</td>\n",
       "      <td>840539006</td>\n",
       "      <td>COVID-19</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>2019-06-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>999-81-4349</td>\n",
       "      <td>...</td>\n",
       "      <td>-72.722648</td>\n",
       "      <td>22940.0</td>\n",
       "      <td>893.28</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>e97e8d37-7497-4c13-98fd-a4a45655c0bb</td>\n",
       "      <td>48065-7</td>\n",
       "      <td>Fibrin D-dimer FEU [Mass/volume] in Platelet p...</td>\n",
       "      <td>0.4</td>\n",
       "      <td>ug/mL</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>2020-02-28</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>e3143bce-4a59-40aa-a198-7a9e54077fd8</td>\n",
       "      <td>840539006</td>\n",
       "      <td>COVID-19</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>2019-06-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>999-81-4349</td>\n",
       "      <td>...</td>\n",
       "      <td>-72.722648</td>\n",
       "      <td>22940.0</td>\n",
       "      <td>893.28</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>e97e8d37-7497-4c13-98fd-a4a45655c0bb</td>\n",
       "      <td>2276-4</td>\n",
       "      <td>Ferritin [Mass/volume] in Serum or Plasma</td>\n",
       "      <td>332.4</td>\n",
       "      <td>ug/L</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>2020-02-28</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>e3143bce-4a59-40aa-a198-7a9e54077fd8</td>\n",
       "      <td>840539006</td>\n",
       "      <td>COVID-19</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>2019-06-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>999-81-4349</td>\n",
       "      <td>...</td>\n",
       "      <td>-72.722648</td>\n",
       "      <td>22940.0</td>\n",
       "      <td>893.28</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>e97e8d37-7497-4c13-98fd-a4a45655c0bb</td>\n",
       "      <td>89579-7</td>\n",
       "      <td>Troponin I.cardiac [Mass/volume] in Serum or P...</td>\n",
       "      <td>2.3</td>\n",
       "      <td>pg/mL</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>2020-02-28</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>e3143bce-4a59-40aa-a198-7a9e54077fd8</td>\n",
       "      <td>840539006</td>\n",
       "      <td>COVID-19</td>\n",
       "      <td>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</td>\n",
       "      <td>2019-06-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>999-81-4349</td>\n",
       "      <td>...</td>\n",
       "      <td>-72.722648</td>\n",
       "      <td>22940.0</td>\n",
       "      <td>893.28</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>e97e8d37-7497-4c13-98fd-a4a45655c0bb</td>\n",
       "      <td>14804-9</td>\n",
       "      <td>Lactate dehydrogenase [Enzymatic activity/volu...</td>\n",
       "      <td>223.9</td>\n",
       "      <td>U/L</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        START        STOP                               PATIENT  \\\n",
       "0  2020-02-19  2020-02-28  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   \n",
       "1  2020-02-19  2020-02-28  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   \n",
       "2  2020-02-19  2020-02-28  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   \n",
       "3  2020-02-19  2020-02-28  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   \n",
       "4  2020-02-19  2020-02-28  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   \n",
       "\n",
       "                            ENCOUNTER_x     CODE_x DESCRIPTION_x  \\\n",
       "0  e3143bce-4a59-40aa-a198-7a9e54077fd8  840539006      COVID-19   \n",
       "1  e3143bce-4a59-40aa-a198-7a9e54077fd8  840539006      COVID-19   \n",
       "2  e3143bce-4a59-40aa-a198-7a9e54077fd8  840539006      COVID-19   \n",
       "3  e3143bce-4a59-40aa-a198-7a9e54077fd8  840539006      COVID-19   \n",
       "4  e3143bce-4a59-40aa-a198-7a9e54077fd8  840539006      COVID-19   \n",
       "\n",
       "                                     Id   BIRTHDATE DEATHDATE          SSN  \\\n",
       "0  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c  2019-06-12       NaN  999-81-4349   \n",
       "1  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c  2019-06-12       NaN  999-81-4349   \n",
       "2  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c  2019-06-12       NaN  999-81-4349   \n",
       "3  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c  2019-06-12       NaN  999-81-4349   \n",
       "4  f58bf921-cba1-475a-b4f8-dc6fa3b8f89c  2019-06-12       NaN  999-81-4349   \n",
       "\n",
       "   ...        LON HEALTHCARE_EXPENSES HEALTHCARE_COVERAGE        DATE  \\\n",
       "0  ... -72.722648             22940.0              893.28  2020-02-19   \n",
       "1  ... -72.722648             22940.0              893.28  2020-02-19   \n",
       "2  ... -72.722648             22940.0              893.28  2020-02-19   \n",
       "3  ... -72.722648             22940.0              893.28  2020-02-19   \n",
       "4  ... -72.722648             22940.0              893.28  2020-02-19   \n",
       "\n",
       "                            ENCOUNTER_y   CODE_y  \\\n",
       "0  e97e8d37-7497-4c13-98fd-a4a45655c0bb    731-0   \n",
       "1  e97e8d37-7497-4c13-98fd-a4a45655c0bb  48065-7   \n",
       "2  e97e8d37-7497-4c13-98fd-a4a45655c0bb   2276-4   \n",
       "3  e97e8d37-7497-4c13-98fd-a4a45655c0bb  89579-7   \n",
       "4  e97e8d37-7497-4c13-98fd-a4a45655c0bb  14804-9   \n",
       "\n",
       "                                       DESCRIPTION_y  VALUE    UNITS     TYPE  \n",
       "0  Lymphocytes [#/volume] in Blood by Automated c...    1.1  10*3/uL  numeric  \n",
       "1  Fibrin D-dimer FEU [Mass/volume] in Platelet p...    0.4    ug/mL  numeric  \n",
       "2          Ferritin [Mass/volume] in Serum or Plasma  332.4     ug/L  numeric  \n",
       "3  Troponin I.cardiac [Mass/volume] in Serum or P...    2.3    pg/mL  numeric  \n",
       "4  Lactate dehydrogenase [Enzymatic activity/volu...  223.9      U/L  numeric  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merge the covid_patient table with the lab_obs table into a covid_patients_obs table\n",
    "covid_patients_obs = pd.merge(covid_patient, lab_obs, on = 'PATIENT')\n",
    "covid_patients_obs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>days</th>\n",
       "      <th>CODE-Y</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>UNITS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PATIENT</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</th>\n",
       "      <td>0.0</td>\n",
       "      <td>731-0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>10*3/uL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</th>\n",
       "      <td>0.0</td>\n",
       "      <td>48065-7</td>\n",
       "      <td>0.4</td>\n",
       "      <td>ug/mL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2276-4</td>\n",
       "      <td>332.4</td>\n",
       "      <td>ug/L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</th>\n",
       "      <td>0.0</td>\n",
       "      <td>89579-7</td>\n",
       "      <td>2.3</td>\n",
       "      <td>pg/mL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f58bf921-cba1-475a-b4f8-dc6fa3b8f89c</th>\n",
       "      <td>0.0</td>\n",
       "      <td>14804-9</td>\n",
       "      <td>223.9</td>\n",
       "      <td>U/L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c9699449-7a8b-400a-8e86-fab6aa7134cb</th>\n",
       "      <td>8.0</td>\n",
       "      <td>731-0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>10*3/uL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c9699449-7a8b-400a-8e86-fab6aa7134cb</th>\n",
       "      <td>8.0</td>\n",
       "      <td>48065-7</td>\n",
       "      <td>0.5</td>\n",
       "      <td>ug/mL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c9699449-7a8b-400a-8e86-fab6aa7134cb</th>\n",
       "      <td>8.0</td>\n",
       "      <td>2276-4</td>\n",
       "      <td>525.2</td>\n",
       "      <td>ug/L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c9699449-7a8b-400a-8e86-fab6aa7134cb</th>\n",
       "      <td>8.0</td>\n",
       "      <td>89579-7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>pg/mL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c9699449-7a8b-400a-8e86-fab6aa7134cb</th>\n",
       "      <td>8.0</td>\n",
       "      <td>14804-9</td>\n",
       "      <td>247.9</td>\n",
       "      <td>U/L</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73918 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      days   CODE-Y  VALUE    UNITS\n",
       "PATIENT                                                            \n",
       "f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   0.0    731-0    1.1  10*3/uL\n",
       "f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   0.0  48065-7    0.4    ug/mL\n",
       "f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   0.0   2276-4  332.4     ug/L\n",
       "f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   0.0  89579-7    2.3    pg/mL\n",
       "f58bf921-cba1-475a-b4f8-dc6fa3b8f89c   0.0  14804-9  223.9      U/L\n",
       "...                                    ...      ...    ...      ...\n",
       "c9699449-7a8b-400a-8e86-fab6aa7134cb   8.0    731-0    0.9  10*3/uL\n",
       "c9699449-7a8b-400a-8e86-fab6aa7134cb   8.0  48065-7    0.5    ug/mL\n",
       "c9699449-7a8b-400a-8e86-fab6aa7134cb   8.0   2276-4  525.2     ug/L\n",
       "c9699449-7a8b-400a-8e86-fab6aa7134cb   8.0  89579-7    3.0    pg/mL\n",
       "c9699449-7a8b-400a-8e86-fab6aa7134cb   8.0  14804-9  247.9      U/L\n",
       "\n",
       "[73918 rows x 4 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#clean the covid_patients_obs table (rename columns, select only relevant columns, sort, typecast, add days column)\n",
    "#to_datetime\n",
    "covid_patients_obs[['DATE','START']] = covid_patients_obs[['DATE','START']].apply(pd.to_datetime)\n",
    "#Calculate days between DATE and START\n",
    "covid_patients_obs['lab_days'] = covid_patients_obs['DATE'] - covid_patients_obs['START']\n",
    "covid_patients_obs['days'] = covid_patients_obs.lab_days / np.timedelta64(1, 'D')\n",
    "# covid_patients_obs['days'] = covid_patients_obs['lab_days'].dt.days\n",
    "#Select columns\n",
    "covid_patients_obs_clean = covid_patients_obs[['PATIENT', 'days', 'CODE_y', 'VALUE', 'UNITS']]\n",
    "#rename column\n",
    "covid_patients_obs_clean = covid_patients_obs_clean.rename(columns={'CODE_y': 'CODE-Y'})\n",
    "#Set index\n",
    "covid_patients_obs_clean = covid_patients_obs_clean.set_index('PATIENT')\n",
    "covid_patients_obs_clean['VALUE'] = covid_patients_obs_clean['VALUE'].astype(float)\n",
    "covid_patients_obs_clean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name='2'></a>\n",
    "## Display data (challenge)\n",
    "<a name='ex-31'></a>\n",
    "### 3.1 plotting with panel\n",
    "Create a panel where you can select the code-y column for displaying a plot of days versus value"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}