Assignment_combine_data_solution.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assignment Combine Data Solution\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": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bk-root\">\n",
       "        <a href=\"https://bokeh.org\" target=\"_blank\" class=\"bk-logo bk-logo-small bk-logo-notebook\"></a>\n",
       "        <span id=\"1001\">Loading BokehJS ...</span>\n",
       "    </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "\n",
       "(function(root) {\n",
       "  function now() {\n",
       "    return new Date();\n",
       "  }\n",
       "\n",
       "  var force = true;\n",
       "\n",
       "  if (typeof root._bokeh_onload_callbacks === \"undefined\" || force === true) {\n",
       "    root._bokeh_onload_callbacks = [];\n",
       "    root._bokeh_is_loading = undefined;\n",
       "  }\n",
       "\n",
       "  var JS_MIME_TYPE = 'application/javascript';\n",
       "  var HTML_MIME_TYPE = 'text/html';\n",
       "  var EXEC_MIME_TYPE = 'application/vnd.bokehjs_exec.v0+json';\n",
       "  var CLASS_NAME = 'output_bokeh rendered_html';\n",
       "\n",
       "  /**\n",
       "   * Render data to the DOM node\n",
       "   */\n",
       "  function render(props, node) {\n",
       "    var script = document.createElement(\"script\");\n",
       "    node.appendChild(script);\n",
       "  }\n",
       "\n",
       "  /**\n",
       "   * Handle when an output is cleared or removed\n",
       "   */\n",
       "  function handleClearOutput(event, handle) {\n",
       "    var cell = handle.cell;\n",
       "\n",
       "    var id = cell.output_area._bokeh_element_id;\n",
       "    var server_id = cell.output_area._bokeh_server_id;\n",
       "    // Clean up Bokeh references\n",
       "    if (id != null && id in Bokeh.index) {\n",
       "      Bokeh.index[id].model.document.clear();\n",
       "      delete Bokeh.index[id];\n",
       "    }\n",
       "\n",
       "    if (server_id !== undefined) {\n",
       "      // Clean up Bokeh references\n",
       "      var cmd = \"from bokeh.io.state import curstate; print(curstate().uuid_to_server['\" + server_id + \"'].get_sessions()[0].document.roots[0]._id)\";\n",
       "      cell.notebook.kernel.execute(cmd, {\n",
       "        iopub: {\n",
       "          output: function(msg) {\n",
       "            var id = msg.content.text.trim();\n",
       "            if (id in Bokeh.index) {\n",
       "              Bokeh.index[id].model.document.clear();\n",
       "              delete Bokeh.index[id];\n",
       "            }\n",
       "          }\n",
       "        }\n",
       "      });\n",
       "      // Destroy server and session\n",
       "      var cmd = \"import bokeh.io.notebook as ion; ion.destroy_server('\" + server_id + \"')\";\n",
       "      cell.notebook.kernel.execute(cmd);\n",
       "    }\n",
       "  }\n",
       "\n",
       "  /**\n",
       "   * Handle when a new output is added\n",
       "   */\n",
       "  function handleAddOutput(event, handle) {\n",
       "    var output_area = handle.output_area;\n",
       "    var output = handle.output;\n",
       "\n",
       "    // limit handleAddOutput to display_data with EXEC_MIME_TYPE content only\n",
       "    if ((output.output_type != \"display_data\") || (!output.data.hasOwnProperty(EXEC_MIME_TYPE))) {\n",
       "      return\n",
       "    }\n",
       "\n",
       "    var toinsert = output_area.element.find(\".\" + CLASS_NAME.split(' ')[0]);\n",
       "\n",
       "    if (output.metadata[EXEC_MIME_TYPE][\"id\"] !== undefined) {\n",
       "      toinsert[toinsert.length - 1].firstChild.textContent = output.data[JS_MIME_TYPE];\n",
       "      // store reference to embed id on output_area\n",
       "      output_area._bokeh_element_id = output.metadata[EXEC_MIME_TYPE][\"id\"];\n",
       "    }\n",
       "    if (output.metadata[EXEC_MIME_TYPE][\"server_id\"] !== undefined) {\n",
       "      var bk_div = document.createElement(\"div\");\n",
       "      bk_div.innerHTML = output.data[HTML_MIME_TYPE];\n",
       "      var script_attrs = bk_div.children[0].attributes;\n",
       "      for (var i = 0; i < script_attrs.length; i++) {\n",
       "        toinsert[toinsert.length - 1].firstChild.setAttribute(script_attrs[i].name, script_attrs[i].value);\n",
       "        toinsert[toinsert.length - 1].firstChild.textContent = bk_div.children[0].textContent\n",
       "      }\n",
       "      // store reference to server id on output_area\n",
       "      output_area._bokeh_server_id = output.metadata[EXEC_MIME_TYPE][\"server_id\"];\n",
       "    }\n",
       "  }\n",
       "\n",
       "  function register_renderer(events, OutputArea) {\n",
       "\n",
       "    function append_mime(data, metadata, element) {\n",
       "      // create a DOM node to render to\n",
       "      var toinsert = this.create_output_subarea(\n",
       "        metadata,\n",
       "        CLASS_NAME,\n",
       "        EXEC_MIME_TYPE\n",
       "      );\n",
       "      this.keyboard_manager.register_events(toinsert);\n",
       "      // Render to node\n",
       "      var props = {data: data, metadata: metadata[EXEC_MIME_TYPE]};\n",
       "      render(props, toinsert[toinsert.length - 1]);\n",
       "      element.append(toinsert);\n",
       "      return toinsert\n",
       "    }\n",
       "\n",
       "    /* Handle when an output is cleared or removed */\n",
       "    events.on('clear_output.CodeCell', handleClearOutput);\n",
       "    events.on('delete.Cell', handleClearOutput);\n",
       "\n",
       "    /* Handle when a new output is added */\n",
       "    events.on('output_added.OutputArea', handleAddOutput);\n",
       "\n",
       "    /**\n",
       "     * Register the mime type and append_mime function with output_area\n",
       "     */\n",
       "    OutputArea.prototype.register_mime_type(EXEC_MIME_TYPE, append_mime, {\n",
       "      /* Is output safe? */\n",
       "      safe: true,\n",
       "      /* Index of renderer in `output_area.display_order` */\n",
       "      index: 0\n",
       "    });\n",
       "  }\n",
       "\n",
       "  // register the mime type if in Jupyter Notebook environment and previously unregistered\n",
       "  if (root.Jupyter !== undefined) {\n",
       "    var events = require('base/js/events');\n",
       "    var OutputArea = require('notebook/js/outputarea').OutputArea;\n",
       "\n",
       "    if (OutputArea.prototype.mime_types().indexOf(EXEC_MIME_TYPE) == -1) {\n",
       "      register_renderer(events, OutputArea);\n",
       "    }\n",
       "  }\n",
       "\n",
       "  \n",
       "  if (typeof (root._bokeh_timeout) === \"undefined\" || force === true) {\n",
       "    root._bokeh_timeout = Date.now() + 5000;\n",
       "    root._bokeh_failed_load = false;\n",
       "  }\n",
       "\n",
       "  var NB_LOAD_WARNING = {'data': {'text/html':\n",
       "     \"<div style='background-color: #fdd'>\\n\"+\n",
       "     \"<p>\\n\"+\n",
       "     \"BokehJS does not appear to have successfully loaded. If loading BokehJS from CDN, this \\n\"+\n",
       "     \"may be due to a slow or bad network connection. Possible fixes:\\n\"+\n",
       "     \"</p>\\n\"+\n",
       "     \"<ul>\\n\"+\n",
       "     \"<li>re-rerun `output_notebook()` to attempt to load from CDN again, or</li>\\n\"+\n",
       "     \"<li>use INLINE resources instead, as so:</li>\\n\"+\n",
       "     \"</ul>\\n\"+\n",
       "     \"<code>\\n\"+\n",
       "     \"from bokeh.resources import INLINE\\n\"+\n",
       "     \"output_notebook(resources=INLINE)\\n\"+\n",
       "     \"</code>\\n\"+\n",
       "     \"</div>\"}};\n",
       "\n",
       "  function display_loaded() {\n",
       "    var el = document.getElementById(\"1001\");\n",
       "    if (el != null) {\n",
       "      el.textContent = \"BokehJS is loading...\";\n",
       "    }\n",
       "    if (root.Bokeh !== undefined) {\n",
       "      if (el != null) {\n",
       "        el.textContent = \"BokehJS \" + root.Bokeh.version + \" successfully loaded.\";\n",
       "      }\n",
       "    } else if (Date.now() < root._bokeh_timeout) {\n",
       "      setTimeout(display_loaded, 100)\n",
       "    }\n",
       "  }\n",
       "\n",
       "\n",
       "  function run_callbacks() {\n",
       "    try {\n",
       "      root._bokeh_onload_callbacks.forEach(function(callback) {\n",
       "        if (callback != null)\n",
       "          callback();\n",
       "      });\n",
       "    } finally {\n",
       "      delete root._bokeh_onload_callbacks\n",
       "    }\n",
       "    console.debug(\"Bokeh: all callbacks have finished\");\n",
       "  }\n",
       "\n",
       "  function load_libs(css_urls, js_urls, callback) {\n",
       "    if (css_urls == null) css_urls = [];\n",
       "    if (js_urls == null) js_urls = [];\n",
       "\n",
       "    root._bokeh_onload_callbacks.push(callback);\n",
       "    if (root._bokeh_is_loading > 0) {\n",
       "      console.debug(\"Bokeh: BokehJS is being loaded, scheduling callback at\", now());\n",
       "      return null;\n",
       "    }\n",
       "    if (js_urls == null || js_urls.length === 0) {\n",
       "      run_callbacks();\n",
       "      return null;\n",
       "    }\n",
       "    console.debug(\"Bokeh: BokehJS not loaded, scheduling load and callback at\", now());\n",
       "    root._bokeh_is_loading = css_urls.length + js_urls.length;\n",
       "\n",
       "    function on_load() {\n",
       "      root._bokeh_is_loading--;\n",
       "      if (root._bokeh_is_loading === 0) {\n",
       "        console.debug(\"Bokeh: all BokehJS libraries/stylesheets loaded\");\n",
       "        run_callbacks()\n",
       "      }\n",
       "    }\n",
       "\n",
       "    function on_error() {\n",
       "      console.error(\"failed to load \" + url);\n",
       "    }\n",
       "\n",
       "    for (var i = 0; i < css_urls.length; i++) {\n",
       "      var url = css_urls[i];\n",
       "      const element = document.createElement(\"link\");\n",
       "      element.onload = on_load;\n",
       "      element.onerror = on_error;\n",
       "      element.rel = \"stylesheet\";\n",
       "      element.type = \"text/css\";\n",
       "      element.href = url;\n",
       "      console.debug(\"Bokeh: injecting link tag for BokehJS stylesheet: \", url);\n",
       "      document.body.appendChild(element);\n",
       "    }\n",
       "\n",
       "    const hashes = {\"https://cdn.bokeh.org/bokeh/release/bokeh-2.2.3.min.js\": \"T2yuo9Oe71Cz/I4X9Ac5+gpEa5a8PpJCDlqKYO0CfAuEszu1JrXLl8YugMqYe3sM\", \"https://cdn.bokeh.org/bokeh/release/bokeh-widgets-2.2.3.min.js\": \"98GDGJ0kOMCUMUePhksaQ/GYgB3+NH9h996V88sh3aOiUNX3N+fLXAtry6xctSZ6\", \"https://cdn.bokeh.org/bokeh/release/bokeh-tables-2.2.3.min.js\": \"89bArO+nlbP3sgakeHjCo1JYxYR5wufVgA3IbUvDY+K7w4zyxJqssu7wVnfeKCq8\"};\n",
       "\n",
       "    for (var i = 0; i < js_urls.length; i++) {\n",
       "      var url = js_urls[i];\n",
       "      var element = document.createElement('script');\n",
       "      element.onload = on_load;\n",
       "      element.onerror = on_error;\n",
       "      element.async = false;\n",
       "      element.src = url;\n",
       "      if (url in hashes) {\n",
       "        element.crossOrigin = \"anonymous\";\n",
       "        element.integrity = \"sha384-\" + hashes[url];\n",
       "      }\n",
       "      console.debug(\"Bokeh: injecting script tag for BokehJS library: \", url);\n",
       "      document.head.appendChild(element);\n",
       "    }\n",
       "  };\n",
       "\n",
       "  function inject_raw_css(css) {\n",
       "    const element = document.createElement(\"style\");\n",
       "    element.appendChild(document.createTextNode(css));\n",
       "    document.body.appendChild(element);\n",
       "  }\n",
       "\n",
       "  \n",
       "  var js_urls = [\"https://cdn.bokeh.org/bokeh/release/bokeh-2.2.3.min.js\", \"https://cdn.bokeh.org/bokeh/release/bokeh-widgets-2.2.3.min.js\", \"https://cdn.bokeh.org/bokeh/release/bokeh-tables-2.2.3.min.js\"];\n",
       "  var css_urls = [];\n",
       "  \n",
       "\n",
       "  var inline_js = [\n",
       "    function(Bokeh) {\n",
       "      Bokeh.set_log_level(\"info\");\n",
       "    },\n",
       "    function(Bokeh) {\n",
       "    \n",
       "    \n",
       "    }\n",
       "  ];\n",
       "\n",
       "  function run_inline_js() {\n",
       "    \n",
       "    if (root.Bokeh !== undefined || force === true) {\n",
       "      \n",
       "    for (var i = 0; i < inline_js.length; i++) {\n",
       "      inline_js[i].call(root, root.Bokeh);\n",
       "    }\n",
       "    if (force === true) {\n",
       "        display_loaded();\n",
       "      }} else if (Date.now() < root._bokeh_timeout) {\n",
       "      setTimeout(run_inline_js, 100);\n",
       "    } else if (!root._bokeh_failed_load) {\n",
       "      console.log(\"Bokeh: BokehJS failed to load within specified timeout.\");\n",
       "      root._bokeh_failed_load = true;\n",
       "    } else if (force !== true) {\n",
       "      var cell = $(document.getElementById(\"1001\")).parents('.cell').data().cell;\n",
       "      cell.output_area.append_execute_result(NB_LOAD_WARNING)\n",
       "    }\n",
       "\n",
       "  }\n",
       "\n",
       "  if (root._bokeh_is_loading === 0) {\n",
       "    console.debug(\"Bokeh: BokehJS loaded, going straight to plotting\");\n",
       "    run_inline_js();\n",
       "  } else {\n",
       "    load_libs(css_urls, js_urls, function() {\n",
       "      console.debug(\"Bokeh: BokehJS plotting callback run at\", now());\n",
       "      run_inline_js();\n",
       "    });\n",
       "  }\n",
       "}(window));"
      ],
      "application/vnd.bokehjs_load.v0+json": "\n(function(root) {\n  function now() {\n    return new Date();\n  }\n\n  var force = true;\n\n  if (typeof root._bokeh_onload_callbacks === \"undefined\" || force === true) {\n    root._bokeh_onload_callbacks = [];\n    root._bokeh_is_loading = undefined;\n  }\n\n  \n\n  \n  if (typeof (root._bokeh_timeout) === \"undefined\" || force === true) {\n    root._bokeh_timeout = Date.now() + 5000;\n    root._bokeh_failed_load = false;\n  }\n\n  var NB_LOAD_WARNING = {'data': {'text/html':\n     \"<div style='background-color: #fdd'>\\n\"+\n     \"<p>\\n\"+\n     \"BokehJS does not appear to have successfully loaded. If loading BokehJS from CDN, this \\n\"+\n     \"may be due to a slow or bad network connection. Possible fixes:\\n\"+\n     \"</p>\\n\"+\n     \"<ul>\\n\"+\n     \"<li>re-rerun `output_notebook()` to attempt to load from CDN again, or</li>\\n\"+\n     \"<li>use INLINE resources instead, as so:</li>\\n\"+\n     \"</ul>\\n\"+\n     \"<code>\\n\"+\n     \"from bokeh.resources import INLINE\\n\"+\n     \"output_notebook(resources=INLINE)\\n\"+\n     \"</code>\\n\"+\n     \"</div>\"}};\n\n  function display_loaded() {\n    var el = document.getElementById(\"1001\");\n    if (el != null) {\n      el.textContent = \"BokehJS is loading...\";\n    }\n    if (root.Bokeh !== undefined) {\n      if (el != null) {\n        el.textContent = \"BokehJS \" + root.Bokeh.version + \" successfully loaded.\";\n      }\n    } else if (Date.now() < root._bokeh_timeout) {\n      setTimeout(display_loaded, 100)\n    }\n  }\n\n\n  function run_callbacks() {\n    try {\n      root._bokeh_onload_callbacks.forEach(function(callback) {\n        if (callback != null)\n          callback();\n      });\n    } finally {\n      delete root._bokeh_onload_callbacks\n    }\n    console.debug(\"Bokeh: all callbacks have finished\");\n  }\n\n  function load_libs(css_urls, js_urls, callback) {\n    if (css_urls == null) css_urls = [];\n    if (js_urls == null) js_urls = [];\n\n    root._bokeh_onload_callbacks.push(callback);\n    if (root._bokeh_is_loading > 0) {\n      console.debug(\"Bokeh: BokehJS is being loaded, scheduling callback at\", now());\n      return null;\n    }\n    if (js_urls == null || js_urls.length === 0) {\n      run_callbacks();\n      return null;\n    }\n    console.debug(\"Bokeh: BokehJS not loaded, scheduling load and callback at\", now());\n    root._bokeh_is_loading = css_urls.length + js_urls.length;\n\n    function on_load() {\n      root._bokeh_is_loading--;\n      if (root._bokeh_is_loading === 0) {\n        console.debug(\"Bokeh: all BokehJS libraries/stylesheets loaded\");\n        run_callbacks()\n      }\n    }\n\n    function on_error() {\n      console.error(\"failed to load \" + url);\n    }\n\n    for (var i = 0; i < css_urls.length; i++) {\n      var url = css_urls[i];\n      const element = document.createElement(\"link\");\n      element.onload = on_load;\n      element.onerror = on_error;\n      element.rel = \"stylesheet\";\n      element.type = \"text/css\";\n      element.href = url;\n      console.debug(\"Bokeh: injecting link tag for BokehJS stylesheet: \", url);\n      document.body.appendChild(element);\n    }\n\n    const hashes = {\"https://cdn.bokeh.org/bokeh/release/bokeh-2.2.3.min.js\": \"T2yuo9Oe71Cz/I4X9Ac5+gpEa5a8PpJCDlqKYO0CfAuEszu1JrXLl8YugMqYe3sM\", \"https://cdn.bokeh.org/bokeh/release/bokeh-widgets-2.2.3.min.js\": \"98GDGJ0kOMCUMUePhksaQ/GYgB3+NH9h996V88sh3aOiUNX3N+fLXAtry6xctSZ6\", \"https://cdn.bokeh.org/bokeh/release/bokeh-tables-2.2.3.min.js\": \"89bArO+nlbP3sgakeHjCo1JYxYR5wufVgA3IbUvDY+K7w4zyxJqssu7wVnfeKCq8\"};\n\n    for (var i = 0; i < js_urls.length; i++) {\n      var url = js_urls[i];\n      var element = document.createElement('script');\n      element.onload = on_load;\n      element.onerror = on_error;\n      element.async = false;\n      element.src = url;\n      if (url in hashes) {\n        element.crossOrigin = \"anonymous\";\n        element.integrity = \"sha384-\" + hashes[url];\n      }\n      console.debug(\"Bokeh: injecting script tag for BokehJS library: \", url);\n      document.head.appendChild(element);\n    }\n  };\n\n  function inject_raw_css(css) {\n    const element = document.createElement(\"style\");\n    element.appendChild(document.createTextNode(css));\n    document.body.appendChild(element);\n  }\n\n  \n  var js_urls = [\"https://cdn.bokeh.org/bokeh/release/bokeh-2.2.3.min.js\", \"https://cdn.bokeh.org/bokeh/release/bokeh-widgets-2.2.3.min.js\", \"https://cdn.bokeh.org/bokeh/release/bokeh-tables-2.2.3.min.js\"];\n  var css_urls = [];\n  \n\n  var inline_js = [\n    function(Bokeh) {\n      Bokeh.set_log_level(\"info\");\n    },\n    function(Bokeh) {\n    \n    \n    }\n  ];\n\n  function run_inline_js() {\n    \n    if (root.Bokeh !== undefined || force === true) {\n      \n    for (var i = 0; i < inline_js.length; i++) {\n      inline_js[i].call(root, root.Bokeh);\n    }\n    if (force === true) {\n        display_loaded();\n      }} else if (Date.now() < root._bokeh_timeout) {\n      setTimeout(run_inline_js, 100);\n    } else if (!root._bokeh_failed_load) {\n      console.log(\"Bokeh: BokehJS failed to load within specified timeout.\");\n      root._bokeh_failed_load = true;\n    } else if (force !== true) {\n      var cell = $(document.getElementById(\"1001\")).parents('.cell').data().cell;\n      cell.output_area.append_execute_result(NB_LOAD_WARNING)\n    }\n\n  }\n\n  if (root._bokeh_is_loading === 0) {\n    console.debug(\"Bokeh: BokehJS loaded, going straight to plotting\");\n    run_inline_js();\n  } else {\n    load_libs(css_urls, js_urls, function() {\n      console.debug(\"Bokeh: BokehJS plotting callback run at\", now());\n      run_inline_js();\n    });\n  }\n}(window));"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#All imports\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from bokeh.models.widgets import Panel, Tabs\n",
    "from bokeh.models import ColumnDataSource, HoverTool, Circle\n",
    "from bokeh.plotting import figure, show #, output_file\n",
    "from bokeh.layouts import gridplot\n",
    "from bokeh.plotting import ColumnDataSource\n",
    "from collections import OrderedDict\n",
    "from bokeh.io import output_notebook\n",
    "from bokeh.tile_providers import get_provider, ESRI_IMAGERY, CARTODBPOSITRON\n",
    "output_notebook()"
   ]
  },
  {
   "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",
    "Get yourself familiar with the data. Create some meaningful overviews. Answer the following questions\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",
    "<details>    \n",
    "<summary>\n",
    "    <font size=\"3\" color=\"darkgreen\"><b>Hints</b></font>\n",
    "</summary>\n",
    "    <ul><li>use a unique dataframe for each file, use a meaningful name</li>\n",
    "    <li>pandas.read_csv() method can be used to read a csv file</li>\n",
    "    <li>pandas.DataFrame.head() method is often used to inspect the dataframe</li>\n",
    "    <li>.unique() returns a list of unique values of a column</li>\n",
    "</ul>\n",
    "</details>\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 = pd.read_csv('data/conditions.csv')\n",
    "patients = pd.read_csv('data/patients.csv')\n",
    "observations = pd.read_csv('data/observations.csv')\n",
    "care_plans = pd.read_csv('data/careplans.csv')\n",
    "encounters = pd.read_csv('data/encounters.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "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"
   ]
  },
  {
   "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": 4,
   "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": [
    "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": [
    "### Expected outcome"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "There are 12352 patients in total\n",
    "There are 8820 covid patients\n",
    "There are 1867 admitted patients\n",
    "2352 patients died"
   ]
  },
  {
   "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": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Possible approach:\n",
    "\n",
    "#Select all the patients with covid from the conditions table\n",
    "#Combine conditions table (only covid patients) 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>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": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Combine conditions table (only covid patients) with the patient table into a covid_patient table\n",
    "covid_patient = pd.merge(df_cov, patients, left_on=  'PATIENT', right_on= 'Id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#select the only the relevant lab observations from the observations table into a lab_obs table\n",
    "lab_obs = observations[observations['CODE'].isin(['48065-7','26881-3', '2276-4', '89579-7', '731-0', '14804-9'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": 8,
     "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": 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>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": 9,
     "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",
    "# make float of objects\n",
    "covid_patients_obs_clean['VALUE'] = covid_patients_obs_clean['VALUE'].astype(float)\n",
    "covid_patients_obs_clean\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name='2'></a>\n",
    "## Part 3: Data Wrangling, split into survived and not survived (10 pt)\n",
    "\n",
    "Now we have the required data we would like to split the data into survived and not survived. First we fetch all the ids of the survived and deceased patients. We can use these ids to select the records of the survived patients and the patients that did not survived"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your job is to split the data into survived and not survived records. There are multiple ways to do this. One way is the  `.isin()` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the following code is given, RUN THIS CELL\n",
    "#get survived and deceased ids\n",
    "completed_isolation_patients = care_plans[(care_plans.CODE == 736376001) & (care_plans.STOP.notna()) \\\n",
    "                                          & (care_plans.REASONCODE == 840539006)].PATIENT\n",
    "negative_covid_patient_ids = observations[(observations.CODE == '94531-1') \\\n",
    "                                          & (observations.VALUE == 'Not detected (qualifier value)')].PATIENT.unique()\n",
    "survivor_ids = np.union1d(completed_isolation_patients, negative_covid_patient_ids)\n",
    "deceased_ids = patients[patients.DEATHDATE.notna()].Id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name='ex-31'></a>\n",
    "### 3.1 Code your solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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",
       "  </tbody>\n",
       "</table>\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"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Splot the data into survived and not survived dataframes\n",
    "survivor_df = covid_patients_obs_clean[covid_patients_obs_clean.index.isin(survivor_ids)]\n",
    "deceased_df = covid_patients_obs_clean[covid_patients_obs_clean.index.isin(deceased_ids)]\n",
    "survivor_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 Test your solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "patients records survived: 57303, patients records deceased 16793\n"
     ]
    }
   ],
   "source": [
    "def test3(survived, died):\n",
    "    print(f'patients records survived: {survived}, patients records deceased {died}')\n",
    "#call the test3\n",
    "test3(len(survivor_df), len(deceased_df))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Expected outcome"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "patients records survived: 57303, patients records deceased 16793"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}