inference_01_extractionR.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "inference_01_extraction.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "ir",
      "display_name": "R"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PXqIHxPDk9KC",
        "colab_type": "text"
      },
      "source": [
        "# BIDMC Datathon Question #1\n",
        "# English vs. Non-English Speaker MIMIC-III Cohort\n",
        "\n",
        "# Notebook 1: Explore database and identify English vs. Non-English speakers\n",
        "\n",
        "The aim of this notebook is to access the [Medical Information Mart for Intensive Care III (MIMIC-III)](https://mimic.physionet.org/) database and to start generating the cohort to be investigated as part of BIDMC Datathon Question #1 assessing whether non-English speakers receive different levels of intensive care at the end of life than English speakers.\n",
        "\n",
        "This notebook is the R version."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XGoBGf3XGjIE",
        "colab_type": "text"
      },
      "source": [
        "# Setup"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jq2Jm_3slPV4",
        "colab_type": "text"
      },
      "source": [
        "## Prerequisites\n",
        "\n",
        "- If you do not have a Gmail account, please create one at http://www.gmail.com. \n",
        "- If you have not yet signed the data use agreement (DUA) sent by the organizers, please do so now to get access to the dataset."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d5J2gJ1qk8rW",
        "colab_type": "text"
      },
      "source": [
        "## Load libraries\n",
        "\n",
        "Run the following cells to install and import some libraries."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yVlGPvPO25Nj",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "install.packages(\"bigrquery\")\n",
        "install.packages(\"googledrive\")\n",
        "install.packages(\"R.utils\")\n",
        "install.packages(\"cowplot\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z60PpIfIeF4Z",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "suppressPackageStartupMessages({\n",
        "  library(tidyverse)   ## for easy DS in R\n",
        "  library(cowplot)     ## for plotting plots together\n",
        "  library(bigrquery)   ## for querying BigQuery\n",
        "  library(googledrive) ## for read/write Google Drive\n",
        "  library(httr)        ## for hacking bigrquery to run in Colab\n",
        "  library(R.utils)     ## \"\"\n",
        "  library(repr)        ## for modifying R plot dims in Colab\n",
        "})"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rE6-FS4YQ1lx",
        "colab_type": "text"
      },
      "source": [
        "We also set a few global parameters for nicer plots."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QHu7Iol3Q2HH",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "## specify default plot dimensions\n",
        "options(repr.plot.width=4, repr.plot.height=3, repr.plot.res = 300)\n",
        "\n",
        "## specify default ggplot2 theme\n",
        "theme_set(theme_bw())"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tVFDucjdGnWa",
        "colab_type": "text"
      },
      "source": [
        "## Connect to BigQuery"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6pAAL_QXlkN_",
        "colab_type": "text"
      },
      "source": [
        "Before running any queries, you need to first authenticate yourself by running the following cell. If you are running it for the first time, it will ask you to follow a link to log in using your Gmail account, and accept the data access requests to your profile. Once this is done, it will generate a string of verification code, which you should paste back to the cell below and press enter."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I2_ToB7Gzmm7",
        "colab_type": "text"
      },
      "source": [
        "The following is a hack to allow R access to Google BigQuery with the `bigrquery` package from a Google Colab notebook."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WfND5moFlr1t",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "## source: https://github.com/r-lib/httr/pull/634\n",
        "reassignInPackage(\"is_interactive\", pkgName = \"httr\", function() {return(TRUE)})"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pVwtUaDf0NaV",
        "colab_type": "text"
      },
      "source": [
        "The following command will setup access to BigQuery. Sign in with the Google account that you provided the organizers of the event (to guarantee access to the MIMIC dataset). Follow the link and copy and paste the authorization code in the prompt below."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ExvNMn-syrXs",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "bq_auth(use_oob = TRUE)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KASjNTJ1mQ3k",
        "colab_type": "text"
      },
      "source": [
        "We'll also set the project name that will be used to run queries and access MIMIC data for this datathon on Google BigQuery."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sGAJCEKHmO4h",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "projectid = \"bidmc-datathon\""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gWN0_J7BuZRr",
        "colab_type": "text"
      },
      "source": [
        "## Query to BigQuery"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QQxXc7AducO_",
        "colab_type": "text"
      },
      "source": [
        "Now we can start exploring the data. \n",
        "\n",
        "Our dataset is stored on BigQuery, Google's database engine. Since this notebook is running the R kernel, *unfortunately*, we won't be able to use some of the nice Colab notebook magic functions to run BigQuery SQL queries directly in a cell. **Fortunately**, querying data from BigQuery using R's `bigrquery` package is still relatively straightforward! Queries are written in SQL, a common language for extracting data from databases. The structure of an SQL query is:\n",
        "\n",
        "```sql\n",
        "SELECT <columns>\n",
        "FROM <table>\n",
        "WHERE <criteria, optional>\n",
        "```\n",
        "\n",
        "`*` is a wildcard that indicates all columns. For more details on syntax for writing SQL queries, check out the [BigQuery query syntax guide](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax).\n",
        "\n",
        "To run an SQL query in R, simply turn the query into a string and call the `bq_project_query` function to *execute* the query and `bq_table_download` to *download* the results.\n",
        "\n",
        "Here's a simple example showing how to download all rows (`*`) from an example dataset called `patients` in the `mimiciii_demo` project. \n",
        "\n",
        "*Remember to include your `projectid` defined above!*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4mceGkwOuZlh",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "demo_sql <- \"\n",
        "SELECT *\n",
        "FROM `physionet-data.mimiciii_demo.patients`\n",
        "\"\n",
        "\n",
        "## execute SQL \n",
        "demo_patients <- bq_project_query(projectid, query = demo_sql)\n",
        "\n",
        "## download results\n",
        "demo_patients <- bq_table_download(demo_patients)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3ejzYN38w0nm",
        "colab_type": "text"
      },
      "source": [
        "We can now inspect our downloaded dataset."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xvyreu00w0dE",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "head(demo_patients)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uCrEmbemw0Gn",
        "colab_type": "text"
      },
      "source": [
        "Great! That wasn't too bad at all!\n",
        "\n",
        "We'll be mixing a fair bit of SQL in our cohort and feature extraction code below to gather data from various tables in the MIMIC-III database. Details about the different tables that we'll be working with are available on the [MIMIC site](https://mimic.physionet.org/gettingstarted/overview/) and for derived tables (some referenced below), the [`MIMIC-code` GitHub repo](https://github.com/MIT-LCP/mimic-code/tree/master/concepts)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eTSNP3n500uq",
        "colab_type": "text"
      },
      "source": [
        "We'll write a quick wrapper to download our data since there's no real need to call the \"query\" and \"download\" functions separately"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pxedw3dZ01ER",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "bq_runsql <- function(query) {\n",
        "  bq_table_download(bq_project_query(projectid, query))\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lpN0EWeO1DWi",
        "colab_type": "text"
      },
      "source": [
        "We can now just call `bq_runsql()` in future code chunks to execute our queries!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZDcbPm2MGfCl",
        "colab_type": "text"
      },
      "source": [
        "# Cohort extraction\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Cfr9x06XMNgh",
        "colab_type": "text"
      },
      "source": [
        "We'll start by extracting our cohort and features from a few different tables in the MIMIC-III database.\n",
        "\n",
        "For each hospital admission, features of the patient and stay are collected from the following clinical MIMIC databases:\n",
        "- `physionet-data.mimiciii_clinical.admissions`\n",
        "- `physionet-data.mimiciii_clinical.icustays`\n",
        "- `physionet-data.mimiciii_clinical.patients`\n",
        "\n",
        "Again, check the [MIMIC site](https://mimic.physionet.org/gettingstarted/overview/) and [`MIMIC-code` GitHub repo](https://github.com/MIT-LCP/mimic-code/tree/master/concepts) for more information about each of the clinical and derived tables.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BQfgb8ciNEf2",
        "colab_type": "text"
      },
      "source": [
        "## Quick introduction"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "u1d-Nu5eNGrr",
        "colab_type": "text"
      },
      "source": [
        "Let's start by taking a quick look at the `admissions` table. We'll start by reading in the entire table by making a quick call to BigQuery.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mTdYkQqvMNql",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql_admissions <- \"\n",
        "SELECT *\n",
        "FROM `physionet-data.mimiciii_clinical.admissions`\n",
        "\"\n",
        "\n",
        "admissions <- bq_runsql(sql_admissions)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "udmjdqE4NIPR",
        "colab_type": "text"
      },
      "source": [
        "Let's take a look at the first few rows of the table and also the size of the table."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tcMH-04jNIbP",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "head(admissions)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YBj4FUkzNNDc",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dim(admissions)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VURW8mq5NNMj",
        "colab_type": "text"
      },
      "source": [
        "There's a *lot* of interesting columns in this table! Let's take a quick stab at this by looking at the distribution of values in the `LANGUAGE` column. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DpoBnungNNZS",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "admissions %>%\n",
        "    dplyr::count(LANGUAGE, sort = TRUE) %>%\n",
        "    print()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mMULuc7NOmYy",
        "colab_type": "text"
      },
      "source": [
        "We see that most admissions were labeled as `ENGL` or `NA` but we also see a lot of other languages! \n",
        "\n",
        "**Questions**\n",
        "- What are the other common languages?\n",
        "- How many languages do we have? \n",
        "\n",
        "We can also plot this to see the entire distribution."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2lJJxSHXOmM5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "options(repr.plot.width = 8, repr.plot.height = 4)\n",
        "\n",
        "admissions %>%\n",
        "    dplyr::count(LANGUAGE) %>%\n",
        "    dplyr::mutate(LANGUAGE = reorder(LANGUAGE, -n)) %>%\n",
        "    ggplot(aes(x = LANGUAGE, y = n)) +\n",
        "    geom_bar(color = 'black', stat = \"identity\") +\n",
        "    ggtitle(\"Distribution of languages across admissions\") +\n",
        "    theme(axis.text.x = element_text(angle = 90, vjust = 1/2, hjust = 1))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "03j6CofuPx91",
        "colab_type": "text"
      },
      "source": [
        "We can do other simple analysis, but we'll leave it at this for now."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "g3wA3ihN0X8X",
        "colab_type": "text"
      },
      "source": [
        "## Define cohort"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-mG_iL2o5eor",
        "colab_type": "text"
      },
      "source": [
        "We'll be restricting our analysis to patient records for hospital admissions that meet the following criteria:\n",
        "- patient expired during the admission\n",
        "- patient record is not an \"organ donor\" entry\n",
        "- patient record is from [MetaVision](https://mimic.physionet.org/mimicdata/metavision/) system (vs. [CareVue](https://mimic.physionet.org/mimicdata/carevue/))\n",
        "- patient language is recorded\n",
        "- patient chart events are available in database"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IxxODZ-b4zty",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql_cohort <- \n",
        "\"\n",
        "    SELECT DISTINCT \n",
        "        ic.subject_id,\n",
        "        ic.hadm_id,\n",
        "        ic.icustay_id,\n",
        "        CASE WHEN ad.language='ENGL' THEN 1 ELSE 0 END AS english,\n",
        "        ROUND(CAST(LEAST(\n",
        "            DATETIME_DIFF(ic.intime, pt.dob, SECOND) / (60*60*24*365.242),\n",
        "            91.4) AS NUMERIC), 1\n",
        "        ) AS age,\n",
        "        CASE\n",
        "            WHEN pt.gender='M' THEN 1\n",
        "            WHEN pt.gender='F' THEN 0\n",
        "            ELSE NULL\n",
        "        END AS male,\n",
        "        ad.ethnicity,\n",
        "        ad.marital_status,\n",
        "        ad.religion,\n",
        "        ad.insurance,\n",
        "        ad.diagnosis,\n",
        "        ad.admission_type,\n",
        "        ad.admission_location,\n",
        "        ad.admittime AS hosp_admittime,\n",
        "        ad.dischtime AS hosp_dischtime,\n",
        "        ROUND(CAST(DATETIME_DIFF(ad.dischtime, ad.admittime, SECOND) / (60*60*24)\n",
        "            AS NUMERIC), 4\n",
        "        ) AS los_hospital,\n",
        "        ic.first_careunit,\n",
        "        ic.intime AS icu_intime,\n",
        "        ic.outtime AS icu_outtime,\n",
        "        ic.los AS los_icu\n",
        "    FROM `physionet-data.mimiciii_clinical.icustays` ic\n",
        "        INNER JOIN `physionet-data.mimiciii_clinical.admissions` ad \n",
        "            ON ad.hadm_id = ic.hadm_id\n",
        "        INNER JOIN `physionet-data.mimiciii_clinical.patients` pt \n",
        "            ON ic.subject_id = pt.subject_id\n",
        "    WHERE ad.hospital_expire_flag = 1\n",
        "        AND LOWER(ad.diagnosis) NOT LIKE 'organ donor account%'\n",
        "        AND ic.dbsource = 'metavision'\n",
        "        AND ad.language IS NOT NULL\n",
        "        AND ad.has_chartevents_data = 1\n",
        "    ORDER BY 1\n",
        "\"\n",
        "\n",
        "bq_cohort <- bq_runsql(sql_cohort)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W7ZiWCbT5Loe",
        "colab_type": "text"
      },
      "source": [
        "The above table includes multiple ICU stays (`icustay_id`) for each hospital admission (`hadm_id`). For the purposes of our analysis, we will only be looking at the *first* ICU stay of each hospital admission. \n",
        "\n",
        "While we could do directly in R, we'll determine the set of `icustay_id`s which correspond to the first ICU stay of each hospital admission using a query to BigQuery."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HHM0f0TXnNZB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql_firststays <- \n",
        "\"\n",
        "SELECT FIRST_VALUE(icustay_id) OVER w AS icustay_id\n",
        "FROM `physionet-data.mimiciii_clinical.icustays`\n",
        "WINDOW w AS (PARTITION BY hadm_id ORDER BY intime ASC)\n",
        "\"\n",
        "\n",
        "bq_firststays <- bq_runsql(sql_firststays)\n",
        "\n",
        "## make distinct since table includes duplicated rows\n",
        "bq_firststays <- dplyr::distinct(bq_firststays)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FdxEUkMjz3vw",
        "colab_type": "text"
      },
      "source": [
        "Now, we use an `inner_join` to subset our cohort to just the first stay of each admission."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4rH7WgQ1DenE",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- bq_cohort %>%\n",
        "    dplyr::inner_join(bq_firststays, by = \"icustay_id\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cPgLL1F5Dtjb",
        "colab_type": "text"
      },
      "source": [
        "Check the number of ICU stays we have in our cohort."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_x-slA9gEpTM",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dim(cohort)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VvwPYzFgduRp",
        "colab_type": "text"
      },
      "source": [
        "That's still a sizeable number of observations!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qWE0Tr7kViYy",
        "colab_type": "text"
      },
      "source": [
        "# Extraction (prelude)\n",
        "\n",
        "Now that we've extracted and defined our primary patient cohort along with several patient-level features, we will add additional features/covariates pulled from separate tables (vital & lab values, sepsis-3, ventilation status) to control for additioadmissions-levelcovariates and analyze our outcomes (e.g. invasive procedures) during modelling. \n",
        "\n",
        "But why controlling for covariates? \n",
        "\n",
        "Our problem definition is to analyze whether English vs. non-English speaking patients received different (or more invasive) treatment. What if a patient needed more invasive treatments (such as ventilation) because of other reasons, e.g. organ failure at admission? To account for this, we need to add covariates (variables capturing the state of a patient) to our model to adjust for whether a treatment was ordered due to a more severe patient state when studying the relationship with language.\n",
        "\n",
        "The MIMIC repository fortunately already provides some SQL code and derived tables for extracting these variables/outcomes (e.g. vitals, labs, ventilation events). Before running those extraction SQL queries and building our design matrix, we'll quickly cover how features like blood pressure, heart rate etc. are actually extracted from MIMIC."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XnKzYmRwZk1K",
        "colab_type": "text"
      },
      "source": [
        "### `chartevents` and _ItemIDs_\n",
        "MIMIC has two central tables which contain most of the structured vital and lab data called:\n",
        "\n",
        "- `physionet-data.mimiciii_clinical.chartevents` (`chartevents`) and\n",
        "- `physionet-data.mimiciii_clinical.labevents` (`labevents`).\n",
        "\n",
        "Those tables have roughly the following schema:\n",
        "\n",
        "| Patient ID | Admission ID | ICU Stay ID | Timestamp    | ItemID | Value | Unit               |\n",
        "|------------|--------------|-------------|--------------|--------|-------|--------------------|\n",
        "| 1          | 1            | 1           | Jan 1st, 1pm | 1      | 80    | bpm                |\n",
        "| 2          | 2            | 2           | Jan 2nd, 2pm | 2      | 13    | breaths per minute |\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X9gtdX74cRCP",
        "colab_type": "text"
      },
      "source": [
        "The first three columns specify:\n",
        "- the *patient*, \n",
        "- the *hospital admission* (a patient could have multiple hospital admissions) and \n",
        "- the *ICU admission* (there can be multiple ICU admissions during each hospital stay). \n",
        "\n",
        "The fourth column specifies the administration timestamp when the the event occurred or when the lab value was entered into the EHR. \n",
        "\n",
        "Columns 5 to 7 contain the most important part of each row: the actual value which is charted in this row. For example, _ItemID_: `1` in the first row in the table above denotes the heart rate for _patientID_: `1`, charted at `Jan 1st, 1pm`, which is `80` `bpm`. The next row denotes denotes a patient who had a respiration rate (_itemID_: `2`) of `13` `breaths per minute` at `Jan 2nd, 2pm`. \n",
        "\n",
        "You might be wondering how we knew _itemID_: `1` corresponded to \"heart rate\". Good question! This information is stored in a separate `d_items` table in MIMIC-III which maps from _itemID_ values to _labels_. We'll explore this more below.\n",
        "\n",
        "Let's take a quick look at some of the real entries in the `chartevents` table in MIMIC-III. (We're using the `LIMIT` statement in our SQL query to only show the first 10 entries in this table.)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "G441JQBWkQ4x",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql <- \"\n",
        "SELECT subject_id, hadm_id, icustay_id, charttime, itemid, value, valueuom\n",
        "FROM `physionet-data.mimiciii_clinical.chartevents`\n",
        "ORDER BY charttime\n",
        "LIMIT 10\n",
        "\"\n",
        "\n",
        "bq_runsql(sql)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hFLKYqvpkv2Q",
        "colab_type": "text"
      },
      "source": [
        "In the first 10 rows that we downloaded, you can see that those entries/item ids probably encode the religion, the marital state and the language of a patient."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tcfa_bEF2FTA",
        "colab_type": "text"
      },
      "source": [
        "# Feature extraction"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZQb4zMidBHat",
        "colab_type": "text"
      },
      "source": [
        "## First day vitals/labs"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FW1zC8oTkCbL",
        "colab_type": "text"
      },
      "source": [
        "Before we start to extract vital/labs for each patient, we need to answer two different questions: \n",
        "1. which item ids correspond to which vital/lab value in _chartevents_ and _labevents_, and \n",
        "2. in which time window do we want to extract those items.\n",
        "\n",
        "While the second question depends on the actual context of the problem definition, the first one can be answered by looking into two other important tables in MIMIC: `d_items` (mentioned above) and `d_labitems`. Those tables contain the name of each vital and lab value charted/administered in the Beth Isreal ICU and map those names to their corresponding item ids. Let's have a look how one of those tables is structured:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LX0Qd_nJiSfO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql <- \"\n",
        "SELECT itemid, label\n",
        "FROM `physionet-data.mimiciii_clinical.d_items`\n",
        "ORDER BY itemid\n",
        "LIMIT 10\"\n",
        "\n",
        "bq_runsql(sql)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2ZVYKoDbjgPw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql <- \"\n",
        "SELECT itemid, label\n",
        "FROM `physionet-data.mimiciii_clinical.d_labitems`\n",
        "ORDER BY itemid\n",
        "LIMIT 10\"\n",
        "\n",
        "bq_runsql(sql)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4RoiKWLjjTbY",
        "colab_type": "text"
      },
      "source": [
        "As you can see, this table contains even the smallest events which can occur in the `chartevents` / `labevents` tables. The columns in those table, obviously, now map each vital/drug name to one _itemid_ which can now be extracted from the `chartevents` (for the `d_items` table) and `labevents` (for the `d_labitems` table) tables. \n",
        "\n",
        "Let's say we now want to extract the Inspiration Time (%) (first entry in the `d_items` table above) from `chartevents`. \n",
        "\n",
        "We could use the following SQL code to extract those events:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jECThX8NjOeV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql <- \"\n",
        "SELECT subject_id, hadm_id, icustay_id, charttime, itemid, value, valueuom\n",
        "FROM `physionet-data.mimiciii_clinical.chartevents`\n",
        "WHERE itemid = 1\n",
        "ORDER BY charttime\n",
        "LIMIT 10\"\n",
        "\n",
        "bq_runsql(sql)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wKB1ka8Nl2aG",
        "colab_type": "text"
      },
      "source": [
        "As you can see, we now filtered the `chartevents` table for the first 10 inspiration rates (%) for multiple patients. Note that the charttimes have been shifted to the future (year 2100) to de-identify the patients in MIMIC.\n",
        "\n",
        "Extracting reliable item IDs from MIMIC for, e.g. extracting heart rates, respiration rates etc. is a really tedious task since sometimes there are multiple item ids describing the same vital feature, so bear in mind that collecting item IDs for particular features can take a lot of time! \n",
        "\n",
        "Since we don't have enough time today to collect all of those co-variates manually, we fortunately can use ready-to-use MIMIC SQL code for that purpose. In the official MIMIC code repository [HERE](https://github.com/MIT-LCP/mimic-code) (under the _concepts_ folder) you can find tons of SQL code which already extracts vitals, labs, dialysis events, mechanical ventilation data etc. - and that's why we now want to make use of that code! \n",
        "\n",
        "Fortunately, all those SQL queries have already been executed by the MIT team and have been added to the `mimiciii_derived` table in the BigQuery MIMIC dataset, so we don't need to re-run those queries. For instance, the SQL query [`vitals-first-day.sql`](https://github.com/MIT-LCP/mimic-code/blob/master/concepts/firstday/vitals-first-day.sql) already extracts all relevant vital values for the first day of each ICU admission in MIMIC, so let's use the resulting table for our own cohort. This is the output of the table when limiting the query to the first 10 rows:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "X_IlojV8plqF",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql <- \"\n",
        "SELECT *\n",
        "FROM `physionet-data.mimiciii_derived.vitalsfirstday`\n",
        "LIMIT 10\"\n",
        "\n",
        "bq_runsql(sql)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_kBWMnZs8a6r",
        "colab_type": "text"
      },
      "source": [
        "And the output for `labsfirstday`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DMPxruKrpvHP",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql <- \"SELECT *\n",
        "FROM `physionet-data.mimiciii_derived.labsfirstday`\n",
        "LIMIT 10\"\n",
        "\n",
        "bq_runsql(sql)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wwLAjDDJpmYW",
        "colab_type": "text"
      },
      "source": [
        "*Note that it's sufficent for our problem definition to only use the first admission day vitals and labs since using all values for each ICU stays would basically translate into a time-series problem which is a completely different game than just using one data row per patient (the mean, max and min values of each vital/lab already contain much valuable information!)*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1nKQ9gv-8lTE",
        "colab_type": "text"
      },
      "source": [
        "*Also, you might have notices that many entries are NaN, so missing values. You need to come up with a strategy during inference, how you want to treat/impute those values. Missingness is one of the biggest challenges in working with EHR data.*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ExYAhzK8zXir",
        "colab_type": "text"
      },
      "source": [
        "After now finding out how to extract vitals and labs using the ready-to-use SQL code from the MIMIC repository, we now need to create a larger table which concats all of those lab/vital features for each ICU stay of each patient (we later limit our cohort to only the first ICU stay of each patient).\n",
        "The following SQL code queries merges (_JOIN_ s) the different lab/vital tables and outputs one big table which contains first day min, max and mean lab and vitals for _each_ patient in MIMIC (we still need to limit it to our cohort). We store the resulting table in the variable `bq_labvital`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VQRd9mtV84Gy",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql_labvital <- \"\n",
        "WITH t1 AS (\n",
        "SELECT labs.*, \n",
        "    HeartRate_Min, HeartRate_Max, HeartRate_Mean, \n",
        "    SysBP_Min, SysBP_Max,\tSysBP_Mean,\t\n",
        "    DiasBP_Min,\tDiasBP_Max,\tDiasBP_Mean,\n",
        "    MeanBP_Min,\tMeanBP_Max,\tMeanBP_Mean,\n",
        "    RespRate_Min,\tRespRate_Max,\tRespRate_Mean,\t\n",
        "    TempC_Min, TempC_Max,\tTempC_Mean,\t\n",
        "    SpO2_Min,\tSpO2_Max,\tSpO2_Mean\n",
        "FROM `physionet-data.mimiciii_derived.labsfirstday` labs\n",
        "LEFT JOIN `physionet-data.mimiciii_derived.vitalsfirstday` vitals\n",
        "  ON vitals.icustay_id = labs.icustay_id\n",
        ")\n",
        ", firstadmissions_1 AS (\n",
        "  SELECT *, ROW_NUMBER() OVER (PARTITION BY subject_id ORDER BY intime) AS rowcnt\n",
        "  FROM `physionet-data.mimiciii_clinical.icustays`\n",
        "), firstadmissions_2 AS (\n",
        "  SELECT *\n",
        "  FROM firstadmissions_1\n",
        "  WHERE rowcnt = 1\n",
        ")\n",
        "SELECT *\n",
        "FROM t1\n",
        "WHERE t1.icustay_id \n",
        "IN (\n",
        "  SELECT DISTINCT icustay_id \n",
        "  FROM firstadmissions_2 \n",
        "  WHERE NOT icustay_id is NULL\n",
        "  )\"\n",
        "\n",
        "bq_labvital <- bq_runsql(sql_labvital)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rYgs2z2HAraJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "head(bq_labvital)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7QuoYq1BAu6m",
        "colab_type": "text"
      },
      "source": [
        "Now we've extracted a design matrix which contains all first day lab and vital statistics for the _complete_ MIMIC population. We'll later limit those patients to our cohort, but before that we keep adding more features to our design matrix (this can be both covariates and outcomes)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-oSl0tdL7g5m",
        "colab_type": "text"
      },
      "source": [
        "Note that this table of first day labs and vitals spans many more ICU stays and hospital admissions than our cohort extracted and defined above.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KQT8B-JT8kxv",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dim(bq_labvital)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_DRKqzLr8ljV",
        "colab_type": "text"
      },
      "source": [
        "We are only interested in the labs and vitals for our cohort. We add this to our existing `cohort` table by `left_join`ing on `subject_id`, `hadm_id`, and `icustay_id`.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rfNWxBpu7hN8",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::left_join(bq_labvital, by = c(\"subject_id\", \"hadm_id\", \"icustay_id\"))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YxSiHwbjBYs5",
        "colab_type": "text"
      },
      "source": [
        "## Severity scores (SAPS-II)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3loEp3OcBcg2",
        "colab_type": "text"
      },
      "source": [
        "Severity score are great features since they translate clinical knowledge into an understandable score which correlates with a patient's current physical state (at admission). These scores could for instance capture organ dysfunction, the inflammatory state of a patient or the consciousness of a patient. \n",
        "\n",
        "We will add the Simplified Acute Physiology Score II (SAPS II)to our table. You can later decide if you want to use this score to control for a patient's severity of illness.\n",
        "SAPS-II has already been computed using the SQL code [HERE](https://github.com/MIT-LCP/mimic-code/blob/master/concepts/severityscores/sapsii.sql). We will simply download the derived table already available on BigQuery and add it to our feature table (variable: `sapsii`). \n",
        "\n",
        "Please note that we'll merge the resulting `bq_sapsii` table later to our vital/labs design matrix we derived earlier."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JfoByHZdBVIg",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "bq_tab_sapsii <- \"physionet-data.mimiciii_derived.sapsii\"\n",
        "\n",
        "## no need to execute an SQL query - just download!\n",
        "bq_sapsii <- bq_table_download(bq_tab_sapsii)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bz4dXwFD8uTG",
        "colab_type": "text"
      },
      "source": [
        "Again, this table covers many more admissions and stays."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2WCvrvrL8fGx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dim(bq_sapsii)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ou3XZJI49GlV",
        "colab_type": "text"
      },
      "source": [
        "We again join by subject, admissions and ICU stay IDs."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DJEcj3lC9GuQ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::left_join(bq_sapsii, by = c(\"subject_id\", \"hadm_id\", \"icustay_id\"))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WcDOpAeK-yQH",
        "colab_type": "text"
      },
      "source": [
        "\n",
        "## Comorbidity scores (Elixhauser)\n",
        "\n",
        "When doing the inference analysis, it is also important to control for comorbidities of the patients. Since there are many comorbidity scores (e.g. Elixhauser), MIMIC already contains code that not only extracts comorbidities (binary flags, e.g. renal failure) by using the ICD-9 diagnosis codes, but also computes different comorbidity scores (e.g. Elixhauser QUAN). \n",
        "\n",
        "We will extract various binary comorbidity flags and a few Elixhauser scores from the follow tables to add to our cohort table.\n",
        "- `physionet-data.mimiciii_derived.elixhauser_quan`\n",
        "- `physionet-data.mimiciii_derived.elixhauser_quan_score`\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7C6djy7b_-Cp",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql_comorb <- \"\n",
        "    SELECT DISTINCT \n",
        "        el.hadm_id,\n",
        "        el.congestive_heart_failure,\n",
        "        el.cardiac_arrhythmias,\n",
        "        el.valvular_disease,\n",
        "        el.pulmonary_circulation,\n",
        "        el.peripheral_vascular,\n",
        "        el.hypertension,\n",
        "        el.paralysis,\n",
        "        el.other_neurological,\n",
        "        el.chronic_pulmonary,\n",
        "        el.diabetes_uncomplicated,\n",
        "        el.diabetes_complicated,\n",
        "        el.hypothyroidism,\n",
        "        el.renal_failure,\n",
        "        el.liver_disease,\n",
        "        el.peptic_ulcer,\n",
        "        el.aids,\n",
        "        el.lymphoma,\n",
        "        el.metastatic_cancer,\n",
        "        el.solid_tumor,\n",
        "        el.rheumatoid_arthritis,\n",
        "        el.coagulopathy,\n",
        "        el.obesity,\n",
        "        el.weight_loss,\n",
        "        el.fluid_electrolyte,\n",
        "        el.blood_loss_anemia,\n",
        "        el.deficiency_anemias,\n",
        "        el.alcohol_abuse,\n",
        "        el.drug_abuse,\n",
        "        el.psychoses,\n",
        "        el.depression,\n",
        "        elscore.elixhauser_vanwalraven,\n",
        "        elscore.elixhauser_sid29,\n",
        "        elscore.elixhauser_sid30\n",
        "    FROM `physionet-data.mimiciii_derived.elixhauser_quan` el \n",
        "        INNER JOIN `physionet-data.mimiciii_derived.elixhauser_quan_score` elscore \n",
        "            ON el.hadm_id = elscore.hadm_id\n",
        "\"\n",
        "\n",
        "bq_comorb <- bq_runsql(sql_comorb)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1_FN3rQ1NKl7",
        "colab_type": "text"
      },
      "source": [
        "We can take a quick look at the various comorbidities that we have available in this table."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "v7PLrsg5NK3X",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "head(bq_comorb)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "r78-AhGEMYag",
        "colab_type": "text"
      },
      "source": [
        "We now add these comorbidity flags and scores to our cohort table."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L-EMDlc_MYjk",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::left_join(bq_comorb, by = \"hadm_id\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gY42UNPDFPeq",
        "colab_type": "text"
      },
      "source": [
        "## Sepsis status (sepsis-III)\n",
        "Next, we want to add sepsis-III to our data table. Sepsis is a one of the major death causes in ICUs around the world, so it's really important to control for sepsis-markers during modelling. Sepsis-III is the most recent definition to define if a patient is having sepsis and adopted in many data-driven projects. A occurence of sepsis is defined here as a SOFA score (organ dysfunction severity score) increase by 2 points and the occurence of a suspected infection (microbiologyevent and antibiotics subscription). Since it would take too long to extract those patients manually from MIMIC, we stick to some code from another [MIT-MIMIC repository](https://github.com/alistairewj/sepsis3-mimic)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uOHRT60ZLCME",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sql_sepsis3 <- \"\n",
        "WITH t1 as\n",
        "(\n",
        "select ie.icustay_id, ie.intime\n",
        "\n",
        "    -- suspicion of infection using POE\n",
        "    , case when spoe.suspected_infection_time is not null then 1 else 0 end\n",
        "        as suspected_of_infection_poe\n",
        "    , spoe.suspected_infection_time as suspected_infection_time_poe\n",
        "    , (UNIX_SECONDS(CAST(ie.intime as TIMESTAMP)) - UNIX_SECONDS(CAST(spoe.suspected_infection_time as TIMESTAMP)))\n",
        "          / 60.0 / 60.0 / 24.0 as suspected_infection_time_poe_days\n",
        "    , so.sofa as sofa\n",
        "from `physionet-data.mimiciii_clinical.icustays` ie\n",
        "inner join `physionet-data.mimiciii_clinical.admissions` adm\n",
        "    on ie.hadm_id = adm.hadm_id\n",
        "inner join `physionet-data.mimiciii_clinical.patients` pat\n",
        "    on ie.subject_id = pat.subject_id\n",
        "left join `physionet-data.mimiciii_derived.sofa` so\n",
        "  on ie.icustay_id = so.icustay_id\n",
        "left join `physionet-data.mimiciii_derived.suspinfect_poe` spoe\n",
        "  on ie.icustay_id = spoe.icustay_id\n",
        ")\n",
        "\n",
        "SELECT DISTINCT icustay_id, 1 as sepsis_3, sofa\n",
        "FROM t1 \n",
        "WHERE  ((t1.suspected_infection_time_poe is not null\n",
        "    and UNIX_SECONDS(TIMESTAMP(t1.suspected_infection_time_poe)) >= (UNIX_SECONDS(TIMESTAMP(t1.intime)) - 24*60*60)\n",
        "  )\n",
        "or (\n",
        "        t1.suspected_infection_time_poe is not null\n",
        "    and UNIX_SECONDS(TIMESTAMP(t1.suspected_infection_time_poe)) <= (UNIX_SECONDS(TIMESTAMP(t1.intime)) + 24*60*60)\n",
        "  ))\n",
        "  AND NOT t1.suspected_infection_time_poe_days IS NULL\n",
        "  AND sofa >= 2\n",
        "\"\n",
        "\n",
        "bq_sepsis3 <- bq_runsql(sql_sepsis3)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P37nVthwBstw",
        "colab_type": "text"
      },
      "source": [
        "This table includes the subset of ICU stays that have been identified as being positive for sepsis according to the Sepsis-3 criteria described above, as well as the increase in SOFA score for this subset of ICU stays."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pscB0j_SBsis",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "head(bq_sepsis3)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "79kMYC7HC-Q7",
        "colab_type": "text"
      },
      "source": [
        "We will now add these scores to our cohort table and set the `sepsis_3` value to \"0\" for ICU stays not in the Sepsis-3 table created above (these ICU stays were not identified as sepsis positive)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zoyD0h2aCk5y",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::left_join(bq_sepsis3, by = \"icustay_id\") %>%\n",
        "    tidyr::replace_na(list(sepsis_3 = 0L))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oSzpvA5mDsBK",
        "colab_type": "text"
      },
      "source": [
        "## Ventilation status"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aIJWKyqlIH4C",
        "colab_type": "text"
      },
      "source": [
        "Ventilator status is extracted from the `ventdurations` table. This derived table was created using the code available on the `MIMIC-code` GitHub repo [here](https://github.com/MIT-LCP/mimic-code/blob/master/concepts/durations/ventilation-durations.sql). \n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZQm3nXt5F6kz",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "bq_tab_ventdur <- \"physionet-data.mimiciii_derived.ventdurations\"\n",
        "\n",
        "## no need to execute an SQL query - just download!\n",
        "bq_ventdur <- bq_table_download(bq_tab_ventdur)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GaXaHbqkKr-q",
        "colab_type": "text"
      },
      "source": [
        "The `ventdurations` table includes durations for all predicted patient ventilation events included in the MIMIC database (see the above link for how this is determined). This means the table includes multiple ventilation durations (events) for each patient and each stay. \n",
        "\n",
        "We will collapse the table to individual ICU stays (`icustay_id`). "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ak7rBn4DKo9F",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "ventdur_bystay <- bq_ventdur %>%\n",
        "    dplyr::filter(!is.na(icustay_id)) %>%\n",
        "    dplyr::group_by(icustay_id) %>%\n",
        "    dplyr::summarize(mechvent_days = sum(duration_hours) / 24,\n",
        "                     mechvent_start = min(starttime)) %>%\n",
        "    dplyr::mutate(mechvent_event = 1L)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZKR76rFwLqtk",
        "colab_type": "text"
      },
      "source": [
        "For each ICU stay, we now have the number of days on mechanical ventilation and the start of ventilation. (We also now know which ICU stays had ventilation events - the ones in the table.)\n",
        "\n",
        "We now add these ventilation features to our cohort."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fWkNcDtULhRB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::left_join(ventdur_bystay, by = \"icustay_id\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0puJ-ZgFM3I6",
        "colab_type": "text"
      },
      "source": [
        "Since our ventilation table only include ICU stays *with* ventilation events, the other ICU stays in the table will have `NAs` in the ventilation columns. We'll replace these with \"0s\"."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "P4UyhBKIM0CT",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::mutate(mechvent_event = ifelse(is.na(mechvent_event), 0, mechvent_event),\n",
        "                  mechvent_days = ifelse(is.na(mechvent_days), 0, mechvent_days))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "74Xa_SP-J6PV",
        "colab_type": "text"
      },
      "source": [
        "While we have ventilation status and duration, what we're actually interested in is the ventilation *free days*. We compute the ventilation free days as the number of days without ventilation *after* ventilation is started. This is calculated using the ICU \"out time\", ventilation start time, and ventilation day columns."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1UXkdUIVJ-Nw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::mutate(\n",
        "        mechvent_candidate_days = as.numeric(icu_outtime - mechvent_start, unit = \"days\"),\n",
        "        mechvent_free_days      = mechvent_candidate_days - mechvent_days)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "voouDfMtODmg",
        "colab_type": "text"
      },
      "source": [
        "We will drop the columns that we're no longer interested in using."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KctAVImeOD2f",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "cohort <- cohort %>%\n",
        "    dplyr::select(-mechvent_days, -mechvent_candidate_days, -mechvent_start)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wKrASqzuN9FY",
        "colab_type": "text"
      },
      "source": [
        "# Save cohort"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hfjuD8X1N_BL",
        "colab_type": "text"
      },
      "source": [
        "Finally, we'll write our complete cohort table to Google Drive. To do this, we will use the [`googledrive` package](https://googledrive.tidyverse.org/). There are several functions in this package (most prefixed with `drive_*`) that can be used to move, create, copy and remove files on Google Drive.\n",
        "\n",
        "Let's get started!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t_rx37IQOD4N",
        "colab_type": "text"
      },
      "source": [
        "## Connect to Drive"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rG4rHtp7wHzd",
        "colab_type": "text"
      },
      "source": [
        "Connecting the Google Colab with Google Drive is a similar process to the one we used to connect to BigQuery above. First, we need to authenticate our account with the following command.\n",
        "\n",
        "(Notice that here we call `drive_auth` and above we called `bq_auth`.)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hHbYv66VN_gJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "drive_auth(use_oob = TRUE)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vSKF9KLyv7Ly",
        "colab_type": "text"
      },
      "source": [
        "Now that we have acces to Google Drive, we can check our connection.\n",
        "\n",
        "To see all of the files at the home path of our Google Drive account (usually called \"My Drive\"), we can call the `drive_ls()` function. Remember to specify `path = \"~\"` when calling this function, otherwise the function will try to list out *all* of your files on Google Drive. (If you're like me, that can be a lot!!)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "i0odvQWRqv4N",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "gd_files <- drive_ls(path = \"~\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nZnLUOddxRS_",
        "colab_type": "text"
      },
      "source": [
        "Let's take a look at what was returned."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "beP0N71VxQsD",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "show(gd_files)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tYstfZoOxTsc",
        "colab_type": "text"
      },
      "source": [
        "This is a table with the `name` of the files, as well as the Google Drive `id`, and a complex `drive_resource` column. We can ignore these additional files.\n",
        "\n",
        "If you visit your [Google Drive page](https://drive.google.com/), hopefully you'll see the same set as files as above.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t9TLLOMKOHMs",
        "colab_type": "text"
      },
      "source": [
        "## Save to Drive"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QBbxs_XitH54",
        "colab_type": "text"
      },
      "source": [
        "The last step is to write our CSV file to Google Drive.\n",
        "\n",
        "We'll do this in 2 steps:\n",
        "- save a temporary CSV file here (in the Colab world),\n",
        "- copy that file to our Google Drive account."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i40UJlYPx7Hh",
        "colab_type": "text"
      },
      "source": [
        "First, we write the table to a CSV file."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6zvMqsKHtHwO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "write_csv(cohort, \"dataset_datathon_28022020.csv\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e3eGILWzx_ph",
        "colab_type": "text"
      },
      "source": [
        "We can check to see that a file with the above name now exists."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "irJaAiC7tHhB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "list.files()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V0YTGU4HyD3f",
        "colab_type": "text"
      },
      "source": [
        "Next, we upload the file to Google Drive. The following command will upload our CSV file to our home folder on Google Drive (\"My Drive\")."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OsP3T4ncOHc6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "drive_upload(\"dataset_datathon_28022020.csv\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-uz8pHuVyHca",
        "colab_type": "text"
      },
      "source": [
        "Let's verify that the file is now uploaded to Google Drive by calling the same `drive_ls()` function as above."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vdfwFOJByN8r",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "show(drive_ls(\"~\"))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4cIveJvVyiR7",
        "colab_type": "text"
      },
      "source": [
        "Now that we have our cohort, we're ready to move on to our analysis."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SIJhYxQgN0PO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}