03_severity_of_illness.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "03-severity-of-illness",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/MIT-LCP/2019_hack_aotearoa_eicu/blob/master/03_severity_of_illness.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "metadata": {
        "id": "y4AOVdliM8gm",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# eICU Collaborative Research Database\n",
        "\n",
        "# Notebook 3: Severity of illness\n",
        "\n",
        "This notebook introduces high level admission details relating to a single patient stay, using the following tables:\n",
        "\n",
        "- patient\n",
        "- admissiondx\n",
        "- apacheapsvar\n",
        "- apachepredvar\n",
        "- apachepatientresult\n",
        "\n"
      ]
    },
    {
      "metadata": {
        "id": "e0lUnIkYOyv4",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Load libraries and connect to the database"
      ]
    },
    {
      "metadata": {
        "id": "SJ6l1i3fOL4j",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Import libraries\n",
        "import numpy as np\n",
        "import os\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import matplotlib.patches as patches\n",
        "import matplotlib.path as path\n",
        "\n",
        "# Make pandas dataframes prettier\n",
        "from IPython.display import display, HTML\n",
        "\n",
        "# Access data using Google BigQuery.\n",
        "from google.colab import auth\n",
        "from google.cloud import bigquery"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "TE4JYS8aO-69",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# authenticate\n",
        "auth.authenticate_user()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "oVavf-ujPOAv",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Set up environment variables\n",
        "project_id='new-zealand-2018-datathon'\n",
        "os.environ[\"GOOGLE_CLOUD_PROJECT\"]=project_id"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "aBc7PA0KSIFM",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Helper function to read data from BigQuery into a DataFrame.\n",
        "def run_query(query):\n",
        "    return pd.io.gbq.read_gbq(query, project_id=project_id, verbose=False, \n",
        "                            configuration={'query':{'useLegacySql': False}})"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "a1CAI3GjQYE0",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Selecting a single patient stay¶\n",
        "\n",
        "As we have seen, the patient table includes general information about the patient admissions (for example, demographics, admission and discharge details). See: http://eicu-crd.mit.edu/eicutables/patient/\n",
        "\n",
        "## Questions\n",
        "\n",
        "Use your knowledge from the previous notebook and the online documentation (http://eicu-crd.mit.edu/) to answer the following questions:\n",
        "\n",
        "- Which column in the patient table is distinct for each stay in the ICU (similar to `icustay_id` in MIMIC-III)?\n",
        "- Which column is unique for each patient (similar to `subject_id` in MIMIC-III)?"
      ]
    },
    {
      "metadata": {
        "id": "R6huFICkSQAd",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# view distinct ids\n",
        "query = \"\"\"\n",
        "SELECT DISTINCT(patientunitstayid)\n",
        "FROM `physionet-data.eicu_crd_demo.patient`\n",
        "\"\"\"\n",
        "\n",
        "run_query(query)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "lfeQwFlvRly7",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# select a single ICU stay\n",
        "patientunitstayid = <your_id_here>"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "yEBIFRBqRo4y",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# set the where clause to select the stay of interest\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM `physionet-data.eicu_crd_demo.patient`\n",
        "WHERE patientunitstayid = {}\n",
        "\"\"\".format(patientunitstayid)\n",
        "\n",
        "patient = run_query(query)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "LjIL2XR6TAyp",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "patient"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "QSbKYqF0TQ1n",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Questions\n",
        "\n",
        "- Which type of unit was the patient admitted to? Hint: Try `patient['unittype']` or `patient.unittype`\n",
        "- What year was the patient discharged from the ICU? Hint: You can view the table columns with `patient.columns`\n",
        "- What was the status of the patient upon discharge from the unit?"
      ]
    },
    {
      "metadata": {
        "id": "izaH0XwwUxDD",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## The admissiondx table\n",
        "\n",
        "The `admissiondx` table contains the primary diagnosis for admission to the ICU according to the APACHE scoring criteria. For more detail, see: http://eicu-crd.mit.edu/eicutables/admissiondx/"
      ]
    },
    {
      "metadata": {
        "id": "dlj3UCDTTEjj",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# set the where clause to select the stay of interest\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM `physionet-data.eicu_crd_demo.admissiondx`\n",
        "WHERE patientunitstayid = {}\n",
        "\"\"\".format(patientunitstayid)\n",
        "\n",
        "admissiondx = run_query(query)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "3wdEHFLJVMKm",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# View the columns in this data\n",
        "admissiondx.columns"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "tbOA44lAVNLr",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# View the data\n",
        "admissiondx.head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "Hc0y4ueOVWOk",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Set the display options to avoid truncating the text\n",
        "pd.set_option('display.max_colwidth', -1)\n",
        "admissiondx.admitdxpath"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "mSb_BrgvWDdD",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Questions\n",
        "\n",
        "- What was the primary reason for admission?\n",
        "- How soon after admission to the ICU was the diagnoses recorded in eCareManager? Hint: The `offset` columns indicate the time in minutes after admission to the ICU. "
      ]
    },
    {
      "metadata": {
        "id": "rd3Tw6_kWwlS",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## The apacheapsvar table\n",
        "\n",
        "The apacheapsvar table contains the variables used to calculate the Acute Physiology Score (APS) III for patients. APS-III is an established method of summarizing patient severity of illness on admission to the ICU, taking the \"worst\" observations for a patient in a 24 hour period.\n",
        "\n",
        "The score is part of the Acute Physiology Age Chronic Health Evaluation (APACHE) system of equations for predicting outcomes for ICU patients. See: http://eicu-crd.mit.edu/eicutables/apacheApsVar/"
      ]
    },
    {
      "metadata": {
        "id": "fXOzR5XWVdNa",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# set the where clause to select the stay of interest\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM `physionet-data.eicu_crd_demo.apacheapsvar`\n",
        "WHERE patientunitstayid = {}\n",
        "\"\"\".format(patientunitstayid)\n",
        "\n",
        "apacheapsvar = run_query(query)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "mL_lVORdXDIg",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "apacheapsvar.head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "8x_Z8q4jXH7D",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Questions\n",
        "\n",
        "- What was the 'worst' heart rate recorded for the patient during the scoring period?\n",
        "- Was the patient oriented and able to converse normally on the day of admission? (hint: the verbal element refers to the Glasgow Coma Scale)."
      ]
    },
    {
      "metadata": {
        "id": "XplJvhIYX432",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# apachepredvar table\n",
        "\n",
        "The apachepredvar table provides variables underlying the APACHE predictions. Acute Physiology Age Chronic Health Evaluation (APACHE) consists of a groups of equations used for predicting outcomes in critically ill patients. See: http://eicu-crd.mit.edu/eicutables/apachePredVar/"
      ]
    },
    {
      "metadata": {
        "id": "iAIFESy9XFhC",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# set the where clause to select the stay of interest\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM `physionet-data.eicu_crd_demo.apachepredvar`\n",
        "WHERE patientunitstayid = {}\n",
        "\"\"\".format(patientunitstayid)\n",
        "\n",
        "apachepredvar = run_query(query)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "LAu7G72cYEY1",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "apachepredvar.columns"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "IEaS6L9OY0vJ",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Questions\n",
        "\n",
        "- Was the patient ventilated during (APACHE) day 1 of their stay?\n",
        "- Is the patient recorded as having diabetes?"
      ]
    },
    {
      "metadata": {
        "id": "nrTEkjxqZD2l",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# `apachepatientresult` table\n",
        "\n",
        "The `apachepatientresult` table provides predictions made by the APACHE score (versions IV and IVa), including probability of mortality, length of stay, and ventilation days. See: http://eicu-crd.mit.edu/eicutables/apachePatientResult/"
      ]
    },
    {
      "metadata": {
        "id": "M2RCJNBgZOJ2",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# set the where clause to select the stay of interest\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM `physionet-data.eicu_crd_demo.apachepatientresult`\n",
        "WHERE patientunitstayid = {}\n",
        "\"\"\".format(patientunitstayid)\n",
        "\n",
        "apachepatientresult = run_query(query)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "4whVaOP1Za8f",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "apachepatientresult"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "5YO_GQcNZUWR",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Questions\n",
        "\n",
        "- What versions of the APACHE score are computed?\n",
        "- How many days during the stay was the patient ventilated?\n",
        "- How long was the patient predicted to stay in hospital?\n",
        "- Was this prediction close to the truth?"
      ]
    }
  ]
}