MIMIC_DPP_100724.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 2",
      "language": "python",
      "name": "python2"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/matthieukomorowski/mimic-code/blob/master/MIMIC_DPP_100724.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hHMyakcA8mA-"
      },
      "source": [
        "# SESSION 1 - DATA EXTRACTION"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7ICcmHnz8mBD"
      },
      "source": [
        "In this session, you will:\n",
        "- Initiate the **connection to the MIMIC-III database**\n",
        "- Explore the MIMIC-III database with some **simple SQL queries**\n",
        "- **Extract the raw dataset** for the mortality prediction project. This will require many steps:\n",
        "    - Define the cohort of interest\n",
        "    - Compute/extract the patients' age, gender, hospital and 90-day mortality\n",
        "    - Compute the highest or lowest value during the first 24h after admission for a range of vital signs and lab values\n",
        "    - Combine all those values in a single DataFrame\n",
        "\n",
        "Make sure you log in **Google Big Query** and explore the MIMIC-III database first."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4hgT7bjr8mBH"
      },
      "source": [
        "# Import required libraries and connect to the MIMIC-III database"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vVxSrZ808mBL"
      },
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import pprint\n",
        "import os\n",
        "\n",
        "pd.set_option('display.max_colwidth',100000) #https://stackoverflow.com/questions/54692405/output-truncation-in-google-colab\n",
        "\n",
        "# Below imports are used to print out pretty pandas dataframes\n",
        "from IPython.display import display, HTML\n",
        "\n",
        "# Imports for accessing Datathon data using Google BigQuery.\n",
        "from google.colab import auth\n",
        "from google.cloud import bigquery\n",
        "from google.colab import files"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aXKpH5bcMRi9"
      },
      "source": [
        "#user authentication\n",
        "auth.authenticate_user()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zm3D48VGMRo5"
      },
      "source": [
        "# Set up the project ID\n",
        "# Note that the physionet-data project is for data hosting only.\n",
        "project_id = 'mindstream-ai'\n",
        "os.environ['mindstream-ai'] = project_id"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Set up the project ID\n",
        "# Note that the physionet-data project is for data hosting only.\n",
        "project_id = 'physionet-data'\n",
        "os.environ['physionet-data'] = project_id"
      ],
      "metadata": {
        "id": "pvzO5jOLYJ7s"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zxvB-vXtMcgt"
      },
      "source": [
        "# Read data from BigQuery into pandas dataframes.\n",
        "def run_query(query):\n",
        "  return pd.io.gbq.read_gbq(\n",
        "      query,\n",
        "      project_id=project_id,\n",
        "      configuration={'query': {\n",
        "          'useLegacySql': False\n",
        "      }})"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "djjVYmLD8mBp"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "euGpIc4n8mBs"
      },
      "source": [
        "# Explore MIMIC-III with some examples"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wkyQHwPz8mBu"
      },
      "source": [
        "## Count of patients\n",
        "\n",
        "Please make note of the syntax for calling an SQL query from within Python.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7uDQYhpf8mBx",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 547
        },
        "outputId": "cb7e1f7a-d3cd-455c-cf23-7e745c2227d7"
      },
      "source": [
        "\n",
        "#query = \"SELECT COUNT(*) FROM `mindstream-ai.mimiciii_clinical.patients`  \"\n",
        "query = \"SELECT COUNT(*) FROM `physionet-data.mimiciv_icu.icustays`  \"\n",
        "#print(query)\n",
        "\n",
        "d = run_query(query)\n",
        "print(d) # Preview the data\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "GenericGBQException",
          "evalue": "Reason: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/physionet-data/jobs?prettyPrint=false: Access Denied: Project physionet-data: User does not have bigquery.jobs.create permission in project physionet-data.\n\nLocation: None\nJob ID: 3b7df50b-f1e3-4290-8141-229224b007e5\n",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mForbidden\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas_gbq/gbq.py\u001b[0m in \u001b[0;36mrun_query\u001b[0;34m(self, query, max_results, progress_bar_type, **kwargs)\u001b[0m\n\u001b[1;32m    478\u001b[0m             \u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Requesting query... \"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 479\u001b[0;31m             query_reply = self.client.query(\n\u001b[0m\u001b[1;32m    480\u001b[0m                 \u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/cloud/bigquery/client.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, query, job_config, job_id, job_id_prefix, location, project, retry, timeout, job_retry, api_method)\u001b[0m\n\u001b[1;32m   3410\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0mapi_method\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0menums\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mQueryApiMethod\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mINSERT\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3411\u001b[0;31m             return _job_helpers.query_jobs_insert(\n\u001b[0m\u001b[1;32m   3412\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/cloud/bigquery/_job_helpers.py\u001b[0m in \u001b[0;36mquery_jobs_insert\u001b[0;34m(client, query, job_config, job_id, job_id_prefix, location, project, retry, timeout, job_retry)\u001b[0m\n\u001b[1;32m    113\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 114\u001b[0;31m     \u001b[0mfuture\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdo_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    115\u001b[0m     \u001b[0;31m# The future might be in a failed state now, but if it's\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/cloud/bigquery/_job_helpers.py\u001b[0m in \u001b[0;36mdo_query\u001b[0;34m()\u001b[0m\n\u001b[1;32m     90\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 91\u001b[0;31m             \u001b[0mquery_job\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_begin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mretry\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mretry\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     92\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mcore_exceptions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mConflict\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mcreate_exc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/cloud/bigquery/job/query.py\u001b[0m in \u001b[0;36m_begin\u001b[0;34m(self, client, retry, timeout)\u001b[0m\n\u001b[1;32m   1370\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1371\u001b[0;31m             \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mQueryJob\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_begin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mclient\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mclient\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mretry\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mretry\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1372\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mexceptions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mGoogleAPICallError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/cloud/bigquery/job/base.py\u001b[0m in \u001b[0;36m_begin\u001b[0;34m(self, client, retry, timeout)\u001b[0m\n\u001b[1;32m    692\u001b[0m         \u001b[0mspan_attributes\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m\"path\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mpath\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 693\u001b[0;31m         api_response = client._call_api(\n\u001b[0m\u001b[1;32m    694\u001b[0m             \u001b[0mretry\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/cloud/bigquery/client.py\u001b[0m in \u001b[0;36m_call_api\u001b[0;34m(self, retry, span_name, span_attributes, job_ref, headers, **kwargs)\u001b[0m\n\u001b[1;32m    815\u001b[0m             ):\n\u001b[0;32m--> 816\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mcall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    817\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/api_core/retry.py\u001b[0m in \u001b[0;36mretry_wrapped_func\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    348\u001b[0m             )\n\u001b[0;32m--> 349\u001b[0;31m             return retry_target(\n\u001b[0m\u001b[1;32m    350\u001b[0m                 \u001b[0mtarget\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/api_core/retry.py\u001b[0m in \u001b[0;36mretry_target\u001b[0;34m(target, predicate, sleep_generator, timeout, on_error, **kwargs)\u001b[0m\n\u001b[1;32m    190\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 191\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mtarget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    192\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/google/cloud/_http/__init__.py\u001b[0m in \u001b[0;36mapi_request\u001b[0;34m(self, method, path, query_params, data, content_type, headers, api_base_url, api_version, expect_json, _target_object, timeout, extra_api_info)\u001b[0m\n\u001b[1;32m    493\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;36m200\u001b[0m \u001b[0;34m<=\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatus_code\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m300\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 494\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexceptions\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfrom_http_response\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    495\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mForbidden\u001b[0m: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/physionet-data/jobs?prettyPrint=false: Access Denied: Project physionet-data: User does not have bigquery.jobs.create permission in project physionet-data.\n\nLocation: None\nJob ID: 3b7df50b-f1e3-4290-8141-229224b007e5\n",
            "\nThe above exception was the direct cause of the following exception:\n",
            "\u001b[0;31mGenericGBQException\u001b[0m                       Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-5-b9d5e4509d5d>\u001b[0m in \u001b[0;36m<cell line: 5>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;31m#print(query)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0md\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrun_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0md\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;31m# Preview the data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m<ipython-input-4-11e3446d7381>\u001b[0m in \u001b[0;36mrun_query\u001b[0;34m(query)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Read data from BigQuery into pandas dataframes.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mrun_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m   return pd.io.gbq.read_gbq(\n\u001b[0m\u001b[1;32m      4\u001b[0m       \u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m       \u001b[0mproject_id\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mproject_id\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas/io/gbq.py\u001b[0m in \u001b[0;36mread_gbq\u001b[0;34m(query, project_id, index_col, col_order, reauth, auth_local_webserver, dialect, location, configuration, credentials, use_bqstorage_api, max_results, progress_bar_type)\u001b[0m\n\u001b[1;32m    187\u001b[0m     \u001b[0;31m# END: new kwargs\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    188\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 189\u001b[0;31m     return pandas_gbq.read_gbq(\n\u001b[0m\u001b[1;32m    190\u001b[0m         \u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    191\u001b[0m         \u001b[0mproject_id\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mproject_id\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas_gbq/gbq.py\u001b[0m in \u001b[0;36mread_gbq\u001b[0;34m(query_or_table, project_id, index_col, col_order, reauth, auth_local_webserver, dialect, location, configuration, credentials, use_bqstorage_api, max_results, verbose, private_key, progress_bar_type, dtypes, auth_redirect_uri, client_id, client_secret)\u001b[0m\n\u001b[1;32m    941\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    942\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0m_is_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery_or_table\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 943\u001b[0;31m         final_df = connector.run_query(\n\u001b[0m\u001b[1;32m    944\u001b[0m             \u001b[0mquery_or_table\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    945\u001b[0m             \u001b[0mconfiguration\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconfiguration\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas_gbq/gbq.py\u001b[0m in \u001b[0;36mrun_query\u001b[0;34m(self, query, max_results, progress_bar_type, **kwargs)\u001b[0m\n\u001b[1;32m    493\u001b[0m                 )\n\u001b[1;32m    494\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhttp_error\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mex\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 495\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprocess_http_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    496\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    497\u001b[0m         \u001b[0mjob_id\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery_reply\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjob_id\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/pandas_gbq/gbq.py\u001b[0m in \u001b[0;36mprocess_http_error\u001b[0;34m(ex)\u001b[0m\n\u001b[1;32m    394\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mTableCreationError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"Reason: {error_message}\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    395\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 396\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mGenericGBQException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Reason: {0}\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mex\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    397\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    398\u001b[0m     def download_table(\n",
            "\u001b[0;31mGenericGBQException\u001b[0m: Reason: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/physionet-data/jobs?prettyPrint=false: Access Denied: Project physionet-data: User does not have bigquery.jobs.create permission in project physionet-data.\n\nLocation: None\nJob ID: 3b7df50b-f1e3-4290-8141-229224b007e5\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Mi2xDJkTuzSF"
      },
      "source": [
        "## List the patients' subject_ID, hospital admission_ID and ICU stay_ID\n",
        "\n",
        "You'll find this data in the table *icustays*."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OS-56iMGuxJU",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "45398044-55a7-4b91-abcc-fac5d25c5b92"
      },
      "source": [
        "#your code here\n",
        "\n",
        "query = \"\"\"\n",
        "\n",
        "SELECT subject_id, hadm_id, icustay_id\n",
        "FROM `mindstream-ai.mimiciii_clinical.icustays`\n",
        "\n",
        "\n",
        "\"\"\"\n",
        "d = run_query(query)\n",
        "\n",
        "print(d) # Preview the data"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "       subject_id  hadm_id  icustay_id\n",
            "0             275   129886      219649\n",
            "1             291   113649      256641\n",
            "2             291   125726      275109\n",
            "3             294   152578      222074\n",
            "4             301   160332      288401\n",
            "...           ...      ...         ...\n",
            "61527       87133   176047      240340\n",
            "61528       95864   172947      280012\n",
            "61529       90317   149802      282296\n",
            "61530       96333   183951      262968\n",
            "61531       90699   128548      292671\n",
            "\n",
            "[61532 rows x 3 columns]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_RBt6OOD8mCE"
      },
      "source": [
        "## Find patients' gender\n",
        "For each hospital admission id instead of subject id, since that's what we'll be using later.\n",
        "You'll have to combine data from 2 tables: *icustays* and *patients*."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DhPZ2oBZnm_S",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "outputId": "37771dd3-ac95-4b02-83b5-d685596416c6"
      },
      "source": [
        "query=\"\"\"\n",
        "\n",
        "SELECT ic.subject_id , ic.hadm_id, pt.subject_id ,pt.gender\n",
        "FROM `mindstream-ai.mimiciii_clinical.patients` AS pt, `mindstream-ai.mimiciii_clinical.icustays` AS ic\n",
        "WHERE pt.subject_id = ic.subject_id\n",
        "ORDER BY ic.hadm_id\n",
        "\n",
        "\"\"\"\n",
        "gender = run_query(query)\n",
        "# Preview the data\n",
        "# Use 'head' to limit the number of rows returned\n",
        "gender.head(10)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-fdbea1f2-3266-4296-9c63-67a9246943d8\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>subject_id</th>\n",
              "      <th>hadm_id</th>\n",
              "      <th>subject_id_1</th>\n",
              "      <th>gender</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>58526</td>\n",
              "      <td>100001</td>\n",
              "      <td>58526</td>\n",
              "      <td>F</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>54610</td>\n",
              "      <td>100003</td>\n",
              "      <td>54610</td>\n",
              "      <td>M</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>9895</td>\n",
              "      <td>100006</td>\n",
              "      <td>9895</td>\n",
              "      <td>F</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>23018</td>\n",
              "      <td>100007</td>\n",
              "      <td>23018</td>\n",
              "      <td>F</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>533</td>\n",
              "      <td>100009</td>\n",
              "      <td>533</td>\n",
              "      <td>M</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>55853</td>\n",
              "      <td>100010</td>\n",
              "      <td>55853</td>\n",
              "      <td>F</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>87977</td>\n",
              "      <td>100011</td>\n",
              "      <td>87977</td>\n",
              "      <td>M</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>60039</td>\n",
              "      <td>100012</td>\n",
              "      <td>60039</td>\n",
              "      <td>M</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>68591</td>\n",
              "      <td>100016</td>\n",
              "      <td>68591</td>\n",
              "      <td>M</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>16229</td>\n",
              "      <td>100017</td>\n",
              "      <td>16229</td>\n",
              "      <td>M</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-fdbea1f2-3266-4296-9c63-67a9246943d8')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-fdbea1f2-3266-4296-9c63-67a9246943d8 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-fdbea1f2-3266-4296-9c63-67a9246943d8');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   subject_id  hadm_id  subject_id_1 gender\n",
              "0       58526   100001         58526      F\n",
              "1       54610   100003         54610      M\n",
              "2        9895   100006          9895      F\n",
              "3       23018   100007         23018      F\n",
              "4         533   100009           533      M\n",
              "5       55853   100010         55853      F\n",
              "6       87977   100011         87977      M\n",
              "7       60039   100012         60039      M\n",
              "8       68591   100016         68591      M\n",
              "9       16229   100017         16229      M"
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Gender repartition: \")\n",
        "print(gender.gender.value_counts())"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2ITJ4TXLm46U",
        "outputId": "4772ad6d-3fe0-491d-dd09-182479930446"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Gender repartition: \n",
            "M    34469\n",
            "F    27063\n",
            "Name: gender, dtype: int64\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rJQFbQcJnslF"
      },
      "source": [
        "Computer programs rather work with numbers than strings.\n",
        "\n",
        "Repeat the query but replace the gender format from string to binary. This is  a pre-processing technique called 'label encoding'."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RQJYeDGV8mCI",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "32ea9497-2d8a-4ee3-9d24-ea3aa308618d"
      },
      "source": [
        "query=\"\"\"\n",
        "select ic.hadm_id,\n",
        "(case when pt.gender='M' then 1 when pt.gender='F' then 0 end) as gender\n",
        "from `mindstream-ai.mimiciii_clinical.patients` pt, `mindstream-ai.mimiciii_clinical.icustays` ic\n",
        "where pt.subject_id=ic.subject_id\n",
        "order by ic.hadm_id\n",
        "\"\"\"\n",
        "gender = run_query(query)\n",
        "gender.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-f4c96742-b2b4-46cc-82cf-176720060bb7\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>gender</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f4c96742-b2b4-46cc-82cf-176720060bb7')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-f4c96742-b2b4-46cc-82cf-176720060bb7 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-f4c96742-b2b4-46cc-82cf-176720060bb7');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  gender\n",
              "0   100001       0\n",
              "1   100003       1\n",
              "2   100006       0\n",
              "3   100007       0\n",
              "4   100009       1"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vuqzzRP18mCU"
      },
      "source": [
        "## Find patient's age\n",
        "\n",
        "Here, we demonstrate how doing a JOIN can be equivalent to what we did in the previous query, to match data across 2 tables."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B7NHZlTQ8mCY",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "outputId": "38841b0d-3971-4c93-c0fb-7379fe021ac5"
      },
      "source": [
        "query = \"\"\"\n",
        "    SELECT ad.hadm_id , datetime_diff( ad.admittime, p.dob, year)  AS age\n",
        "    FROM `mindstream-ai.mimiciii_clinical.admissions` ad\n",
        "    JOIN `mindstream-ai.mimiciii_clinical.patients` p\n",
        "    ON ad.subject_id = p.subject_id\n",
        "    order by ad.hadm_id\n",
        "\"\"\"\n",
        "\n",
        "age = run_query(query)\n",
        "age.head(10)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-3910f77a-3215-4a9f-a8c7-9a7dabcb7125\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>age</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>60</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>61</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>100010</td>\n",
              "      <td>54</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>100011</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>100012</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>100014</td>\n",
              "      <td>50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>100016</td>\n",
              "      <td>56</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3910f77a-3215-4a9f-a8c7-9a7dabcb7125')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-3910f77a-3215-4a9f-a8c7-9a7dabcb7125 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-3910f77a-3215-4a9f-a8c7-9a7dabcb7125');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  age\n",
              "0   100001   35\n",
              "1   100003   60\n",
              "2   100006   49\n",
              "3   100007   74\n",
              "4   100009   61\n",
              "5   100010   54\n",
              "6   100011   21\n",
              "7   100012   68\n",
              "8   100014   50\n",
              "9   100016   56"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Age repartition: \")\n",
        "print((age.age / 365).value_counts(bins=[0, 20, 40, 60, 80, 100]))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HY98095xoDfD",
        "outputId": "f0ec2689-5424-47d5-e2c3-c212471a969d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Age repartition: \n",
            "(60.0, 80.0]      21066\n",
            "(40.0, 60.0]      14349\n",
            "(-0.001, 20.0]     8497\n",
            "(80.0, 100.0]      7683\n",
            "(20.0, 40.0]       4765\n",
            "Name: age, dtype: int64\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yV5f9Zlr8mCj"
      },
      "source": [
        "# Define the cohort of interest\n",
        "\n",
        "Include only adult patients, and their first hospital admission\n",
        "To help you, have a look at the following query from the MIMIC cookbook: https://github.com/MIT-LCP/mimic-code/blob/master/concepts/demographics/icustay-detail.sql\n",
        "\n",
        "First, let's extract all the data we need to define this cohort: **hospital admission id, age and hospital admission sequence number** (plus a few others):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TWHVGUAz8mCn",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 676
        },
        "outputId": "83eae2fb-eca2-4231-8675-cf74c1b30263"
      },
      "source": [
        "query =\"\"\"\n",
        "SELECT ie.subject_id, ie.hadm_id, ie.icustay_id, adm.admittime\n",
        ", datetime_diff( adm.admittime, pat.dob,year)  AS age\n",
        ", DENSE_RANK() OVER (PARTITION BY adm.subject_id ORDER BY adm.admittime) AS hospstay_seq\n",
        "\n",
        "FROM `mindstream-ai.mimiciii_clinical.icustays` ie\n",
        "INNER JOIN `mindstream-ai.mimiciii_clinical.admissions` adm\n",
        "    ON ie.hadm_id = adm.hadm_id\n",
        "INNER JOIN `mindstream-ai.mimiciii_clinical.patients` pat\n",
        "    ON ie.subject_id = pat.subject_id\n",
        "WHERE adm.has_chartevents_data = 1\n",
        "ORDER BY ie.subject_id, adm.admittime, ie.intime\n",
        "\"\"\"\n",
        "\n",
        "d = run_query(query)\n",
        "d.head(20)\n",
        "\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-608c818a-715e-4aa8-838a-6b59745b92a2\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>subject_id</th>\n",
              "      <th>hadm_id</th>\n",
              "      <th>icustay_id</th>\n",
              "      <th>admittime</th>\n",
              "      <th>age</th>\n",
              "      <th>hospstay_seq</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2</td>\n",
              "      <td>163353</td>\n",
              "      <td>243653</td>\n",
              "      <td>2138-07-17 19:04:00</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>3</td>\n",
              "      <td>145834</td>\n",
              "      <td>211552</td>\n",
              "      <td>2101-10-20 19:08:00</td>\n",
              "      <td>76</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>4</td>\n",
              "      <td>185777</td>\n",
              "      <td>294638</td>\n",
              "      <td>2191-03-16 00:28:00</td>\n",
              "      <td>48</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>5</td>\n",
              "      <td>178980</td>\n",
              "      <td>214757</td>\n",
              "      <td>2103-02-02 04:31:00</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>6</td>\n",
              "      <td>107064</td>\n",
              "      <td>228232</td>\n",
              "      <td>2175-05-30 07:15:00</td>\n",
              "      <td>66</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>7</td>\n",
              "      <td>118037</td>\n",
              "      <td>278444</td>\n",
              "      <td>2121-05-23 15:05:00</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>7</td>\n",
              "      <td>118037</td>\n",
              "      <td>236754</td>\n",
              "      <td>2121-05-23 15:05:00</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>8</td>\n",
              "      <td>159514</td>\n",
              "      <td>262299</td>\n",
              "      <td>2117-11-20 10:22:00</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>9</td>\n",
              "      <td>150750</td>\n",
              "      <td>220597</td>\n",
              "      <td>2149-11-09 13:06:00</td>\n",
              "      <td>41</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>10</td>\n",
              "      <td>184167</td>\n",
              "      <td>288409</td>\n",
              "      <td>2103-06-28 11:36:00</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>11</td>\n",
              "      <td>194540</td>\n",
              "      <td>229441</td>\n",
              "      <td>2178-04-16 06:18:00</td>\n",
              "      <td>50</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>12</td>\n",
              "      <td>112213</td>\n",
              "      <td>232669</td>\n",
              "      <td>2104-08-07 10:15:00</td>\n",
              "      <td>72</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>13</td>\n",
              "      <td>143045</td>\n",
              "      <td>263738</td>\n",
              "      <td>2167-01-08 18:43:00</td>\n",
              "      <td>40</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>16</td>\n",
              "      <td>103251</td>\n",
              "      <td>240232</td>\n",
              "      <td>2178-02-03 06:35:00</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>17</td>\n",
              "      <td>194023</td>\n",
              "      <td>277042</td>\n",
              "      <td>2134-12-27 07:15:00</td>\n",
              "      <td>47</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>17</td>\n",
              "      <td>161087</td>\n",
              "      <td>257980</td>\n",
              "      <td>2135-05-09 14:11:00</td>\n",
              "      <td>48</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>18</td>\n",
              "      <td>188822</td>\n",
              "      <td>298129</td>\n",
              "      <td>2167-10-02 11:18:00</td>\n",
              "      <td>51</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>19</td>\n",
              "      <td>109235</td>\n",
              "      <td>273430</td>\n",
              "      <td>2108-08-05 16:25:00</td>\n",
              "      <td>300</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>20</td>\n",
              "      <td>157681</td>\n",
              "      <td>264490</td>\n",
              "      <td>2183-04-28 09:45:00</td>\n",
              "      <td>76</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>21</td>\n",
              "      <td>109451</td>\n",
              "      <td>217847</td>\n",
              "      <td>2134-09-11 12:17:00</td>\n",
              "      <td>87</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-608c818a-715e-4aa8-838a-6b59745b92a2')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-608c818a-715e-4aa8-838a-6b59745b92a2 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-608c818a-715e-4aa8-838a-6b59745b92a2');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "    subject_id  hadm_id  icustay_id           admittime  age  hospstay_seq\n",
              "0            2   163353      243653 2138-07-17 19:04:00    0             1\n",
              "1            3   145834      211552 2101-10-20 19:08:00   76             1\n",
              "2            4   185777      294638 2191-03-16 00:28:00   48             1\n",
              "3            5   178980      214757 2103-02-02 04:31:00    0             1\n",
              "4            6   107064      228232 2175-05-30 07:15:00   66             1\n",
              "5            7   118037      278444 2121-05-23 15:05:00    0             1\n",
              "6            7   118037      236754 2121-05-23 15:05:00    0             1\n",
              "7            8   159514      262299 2117-11-20 10:22:00    0             1\n",
              "8            9   150750      220597 2149-11-09 13:06:00   41             1\n",
              "9           10   184167      288409 2103-06-28 11:36:00    0             1\n",
              "10          11   194540      229441 2178-04-16 06:18:00   50             1\n",
              "11          12   112213      232669 2104-08-07 10:15:00   72             1\n",
              "12          13   143045      263738 2167-01-08 18:43:00   40             1\n",
              "13          16   103251      240232 2178-02-03 06:35:00    0             1\n",
              "14          17   194023      277042 2134-12-27 07:15:00   47             1\n",
              "15          17   161087      257980 2135-05-09 14:11:00   48             2\n",
              "16          18   188822      298129 2167-10-02 11:18:00   51             1\n",
              "17          19   109235      273430 2108-08-05 16:25:00  300             1\n",
              "18          20   157681      264490 2183-04-28 09:45:00   76             1\n",
              "19          21   109451      217847 2134-09-11 12:17:00   87             1"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d64Vkm1u8mCy"
      },
      "source": [
        "Then, we'll use what we wrote above in a **subquery**, and add conditions to select only the subset of patients we want.\n",
        "\n",
        "Note that **there is no need to sort the data in the subquery**, this will save time."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YbFXihOd8mC1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "a0111f35-606e-4e05-c847-11c086643e20"
      },
      "source": [
        "query =\"\"\"\n",
        "\n",
        "WITH t1 as(\n",
        "\n",
        "SELECT ie.subject_id, ie.hadm_id, ie.icustay_id, adm.admittime\n",
        ", datetime_diff( adm.admittime, pat.dob,year)  AS age\n",
        ", DENSE_RANK() OVER (PARTITION BY adm.subject_id ORDER BY adm.admittime) AS hospstay_seq\n",
        "FROM `mindstream-ai.mimiciii_clinical.icustays` ie\n",
        "INNER JOIN `mindstream-ai.mimiciii_clinical.admissions` adm\n",
        "    ON ie.hadm_id = adm.hadm_id\n",
        "INNER JOIN `mindstream-ai.mimiciii_clinical.patients` pat\n",
        "    ON ie.subject_id = pat.subject_id\n",
        "WHERE adm.has_chartevents_data = 1\n",
        "\n",
        ")\n",
        "\n",
        "SELECT hadm_id, age\n",
        "FROM t1\n",
        "WHERE age >=18 and hospstay_seq=1\n",
        "ORDER BY hadm_id\n",
        "\"\"\"\n",
        "\n",
        "cohort = run_query(query)\n",
        "cohort.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-233e9b8d-399d-4941-9984-d7975afefae3\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>age</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>60</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>74</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>61</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-233e9b8d-399d-4941-9984-d7975afefae3')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-233e9b8d-399d-4941-9984-d7975afefae3 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-233e9b8d-399d-4941-9984-d7975afefae3');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  age\n",
              "0   100001   35\n",
              "1   100003   60\n",
              "2   100006   49\n",
              "3   100007   74\n",
              "4   100009   61"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZJQdjJVL8mDA"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "szLtTx2S8mDE"
      },
      "source": [
        "## Establish the mapping of item identifiers\n",
        "\n",
        "It is common for relational databases to store the data using item identifiers instead of textual labels. This is the case in the table _chartevents_. The mapping between item identifiers and their respective labels is in another table, _d_items_. Here is a generic method to learn the mapping, which is often messy (e.g. several item ids for a single concept). This will prove very useful when starting to work with any new database.\n",
        "\n",
        "\n",
        "### 1- Establish the list of parameters you are interested in\n",
        "\n",
        "This can be heart rate, admission weight, or blood sodium.\n",
        "\n",
        "### 2- Identify potential item identifiers with regular expressions\n",
        "\n",
        "Example: find all the rows in _d_items_ where the label contains the word 'heart'\n",
        "and keep the list of all the potential correct item identifiers for 'heart rate'."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rRQ4Zpts8mDI",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "905190ce-5f99-45aa-c299-a7773daf273f"
      },
      "source": [
        "query = \"\"\"\n",
        "select itemid, label, unitname, param_type\n",
        "from `mindstream-ai.mimiciii_clinical.d_items`\n",
        "where lower (label) like '%heart%'\n",
        "order by label asc\n",
        "\"\"\"\n",
        "d = run_query(query)\n",
        "print(d)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "    itemid                                           label unitname param_type\n",
            "0   228186             Access Site Observed (Tandem Heart)     None   Checkbox\n",
            "1   228189                            CO-Tandem Heart Flow    L/min    Numeric\n",
            "2      211                                      Heart Rate     None       None\n",
            "3   220045                                      Heart Rate      bpm    Numeric\n",
            "4   220047                          Heart Rate Alarm - Low      bpm    Numeric\n",
            "5      212                                    Heart Rhythm     None       None\n",
            "6   220048                                    Heart Rhythm     None       Text\n",
            "7      213                                    Heart Sounds     None       None\n",
            "8   224389                                    Heart Sounds     None       Text\n",
            "9   220046                         Heart rate Alarm - High      bpm    Numeric\n",
            "10    3494                               Lowest Heart Rate     None       None\n",
            "11  228194                Peripheral Pulses (Tandem Heart)     None       Text\n",
            "12  228196             Return Site Observed (Tandem Heart)     None   Checkbox\n",
            "13  228201                        Tandem Heart Access Line     None    Process\n",
            "14  228203           Tandem Heart Access Line Cannula Site     None       Text\n",
            "15  228204        Tandem Heart Access Line Catheter Length       cm    Numeric\n",
            "16  228206           Tandem Heart Access Line Discontinued     None       Text\n",
            "17  228207        Tandem Heart Access Line Dressing Change     None  Date time\n",
            "18  228208     Tandem Heart Access Line Dressing Occlusive     None   Checkbox\n",
            "19  228209         Tandem Heart Access Line Insertion Date     None  Date time\n",
            "20  228210               Tandem Heart Access Line Position     None       Text\n",
            "21  228211      Tandem Heart Access Line Postion Confirmed     None       Text\n",
            "22  228212   Tandem Heart Access Line Pump Housing Checked     None   Checkbox\n",
            "23  228213           Tandem Heart Access Line Repositioned     None   Checkbox\n",
            "24  228214            Tandem Heart Access Line Site Appear     None       Text\n",
            "25  228215          Tandem Heart Access Line Tubing Change     None  Date time\n",
            "26  228216                   Tandem Heart Access Line Type     None       Text\n",
            "27  228198                               Tandem Heart Flow    L/min    Numeric\n",
            "28  228217        Tandem Heart Return Cannula Discontinued     None       Text\n",
            "29  228218     Tandem Heart Return Cannula Dressing Change     None  Date time\n",
            "30  228219  Tandem Heart Return Cannula Dressing Occlusive     None   Checkbox\n",
            "31  228221      Tandem Heart Return Cannula Insertion Date     None  Date time\n",
            "32  228222           Tandem Heart Return Cannula Migration     None   Checkbox\n",
            "33  228223            Tandem Heart Return Cannula Position     None       Text\n",
            "34  228224        Tandem Heart Return Cannula Repositioned     None   Checkbox\n",
            "35  228225                Tandem Heart Return Cannula Site     None       Text\n",
            "36  228226         Tandem Heart Return Cannula Site Appear     None       Text\n",
            "37  228227                Tandem Heart Return Cannula Size     None       Text\n",
            "38  228202                        Tandem Heart Return Line     None    Process\n",
            "39  228199                             Type (Tandem Heart)     None       Text\n",
            "40    2781                               fetal heart sound     None       None\n",
            "41    5972                                 fetal heartrate     None       None\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RWO3b7268mDX"
      },
      "source": [
        "### 3- Display count of records and average value for each item_id identified\n",
        "\n",
        "The most frequently used item_ids will appear at the top."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZCSy5LaA8mDc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "0f825e01-d55d-4ece-d22c-27023a0a402b"
      },
      "source": [
        "query= \"\"\"\n",
        "select ce.itemid, ci.label, count (*) as count, AVG(ce.valuenum) as Average\n",
        "from `mindstream-ai.mimiciii_clinical.chartevents` ce, `mindstream-ai.mimiciii_clinical.d_items` ci\n",
        "where ce.subject_id<100 and ce.itemid=ci.itemid and ci.itemid in (211, 212, 220045, 220046, 220047)\n",
        "group by ce.itemid, ci.label\n",
        "order by count desc\n",
        "\"\"\"\n",
        "\n",
        "d = run_query(query)\n",
        "d.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-ed020e9c-512e-40f9-b169-a030955740f7\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>itemid</th>\n",
              "      <th>label</th>\n",
              "      <th>count</th>\n",
              "      <th>Average</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>211</td>\n",
              "      <td>Heart Rate</td>\n",
              "      <td>13127</td>\n",
              "      <td>124.439747</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>212</td>\n",
              "      <td>Heart Rhythm</td>\n",
              "      <td>6210</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>220045</td>\n",
              "      <td>Heart Rate</td>\n",
              "      <td>224</td>\n",
              "      <td>83.589286</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>220047</td>\n",
              "      <td>Heart Rate Alarm - Low</td>\n",
              "      <td>18</td>\n",
              "      <td>51.666667</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>220046</td>\n",
              "      <td>Heart rate Alarm - High</td>\n",
              "      <td>18</td>\n",
              "      <td>119.722222</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ed020e9c-512e-40f9-b169-a030955740f7')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-ed020e9c-512e-40f9-b169-a030955740f7 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-ed020e9c-512e-40f9-b169-a030955740f7');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   itemid                    label  count     Average\n",
              "0     211               Heart Rate  13127  124.439747\n",
              "1     212             Heart Rhythm   6210         NaN\n",
              "2  220045               Heart Rate    224   83.589286\n",
              "3  220047   Heart Rate Alarm - Low     18   51.666667\n",
              "4  220046  Heart rate Alarm - High     18  119.722222"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mlSTTnpV8mDk"
      },
      "source": [
        "### 4- Confirm or infirm validity of item_ids\n",
        "\n",
        "Discard item_ids which have a very low count and/or erroneous values. Domain expertise is necessary.\n",
        "\n",
        "\n",
        "### 5- When in doubt, check individual values\n",
        "\n",
        "Example: explore values of item identifier '220045'"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "F4EHJuJp8mDm",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 677
        },
        "outputId": "485204dd-9248-4dca-8aa9-ccd4004bcf4d"
      },
      "source": [
        "query = \"\"\"\n",
        "select *\n",
        "from `mindstream-ai.mimiciii_clinical.chartevents`\n",
        "where itemid in (220045)  and subject_id <100\n",
        "limit 50\n",
        "\"\"\"\n",
        "\n",
        "d = run_query(query)\n",
        "d.head(20)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-9521beee-370b-41e9-af8e-72bf51df868d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>ROW_ID</th>\n",
              "      <th>SUBJECT_ID</th>\n",
              "      <th>HADM_ID</th>\n",
              "      <th>ICUSTAY_ID</th>\n",
              "      <th>ITEMID</th>\n",
              "      <th>CHARTTIME</th>\n",
              "      <th>STORETIME</th>\n",
              "      <th>CGID</th>\n",
              "      <th>VALUE</th>\n",
              "      <th>VALUENUM</th>\n",
              "      <th>VALUEUOM</th>\n",
              "      <th>WARNING</th>\n",
              "      <th>ERROR</th>\n",
              "      <th>RESULTSTATUS</th>\n",
              "      <th>STOPPED</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>53</td>\n",
              "      <td>23</td>\n",
              "      <td>124321</td>\n",
              "      <td>234044</td>\n",
              "      <td>220045</td>\n",
              "      <td>2157-10-21 16:17:00</td>\n",
              "      <td>2157-10-21 16:24:00</td>\n",
              "      <td>16978</td>\n",
              "      <td>72</td>\n",
              "      <td>72.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1214</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-13 15:00:00</td>\n",
              "      <td>2134-05-13 15:27:00</td>\n",
              "      <td>15225</td>\n",
              "      <td>88</td>\n",
              "      <td>88.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1948</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-16 08:00:00</td>\n",
              "      <td>2134-05-16 08:31:00</td>\n",
              "      <td>15065</td>\n",
              "      <td>76</td>\n",
              "      <td>76.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1142</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-13 09:00:00</td>\n",
              "      <td>2134-05-13 09:09:00</td>\n",
              "      <td>15225</td>\n",
              "      <td>72</td>\n",
              "      <td>72.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2</td>\n",
              "      <td>23</td>\n",
              "      <td>124321</td>\n",
              "      <td>234044</td>\n",
              "      <td>220045</td>\n",
              "      <td>2157-10-21 12:06:00</td>\n",
              "      <td>2157-10-21 12:08:00</td>\n",
              "      <td>16978</td>\n",
              "      <td>77</td>\n",
              "      <td>77.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>1774</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-15 10:00:00</td>\n",
              "      <td>2134-05-15 10:28:00</td>\n",
              "      <td>15065</td>\n",
              "      <td>87</td>\n",
              "      <td>87.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>1800</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-15 14:00:00</td>\n",
              "      <td>2134-05-15 14:16:00</td>\n",
              "      <td>15065</td>\n",
              "      <td>93</td>\n",
              "      <td>93.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>1997</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-16 13:00:00</td>\n",
              "      <td>2134-05-16 14:01:00</td>\n",
              "      <td>15065</td>\n",
              "      <td>83</td>\n",
              "      <td>83.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>1276</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-13 20:00:00</td>\n",
              "      <td>2134-05-13 21:55:00</td>\n",
              "      <td>16797</td>\n",
              "      <td>93</td>\n",
              "      <td>93.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2320</td>\n",
              "      <td>85</td>\n",
              "      <td>112077</td>\n",
              "      <td>291697</td>\n",
              "      <td>220045</td>\n",
              "      <td>2167-07-27 08:00:00</td>\n",
              "      <td>2167-07-27 10:27:00</td>\n",
              "      <td>16603</td>\n",
              "      <td>112</td>\n",
              "      <td>112.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>989</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-13 01:00:00</td>\n",
              "      <td>2134-05-13 01:07:00</td>\n",
              "      <td>15526</td>\n",
              "      <td>75</td>\n",
              "      <td>75.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>2155</td>\n",
              "      <td>85</td>\n",
              "      <td>112077</td>\n",
              "      <td>291697</td>\n",
              "      <td>220045</td>\n",
              "      <td>2167-07-26 12:00:00</td>\n",
              "      <td>2167-07-26 12:27:00</td>\n",
              "      <td>20214</td>\n",
              "      <td>89</td>\n",
              "      <td>89.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>596</td>\n",
              "      <td>34</td>\n",
              "      <td>144319</td>\n",
              "      <td>290505</td>\n",
              "      <td>220045</td>\n",
              "      <td>2191-02-24 06:00:00</td>\n",
              "      <td>2191-02-24 06:06:00</td>\n",
              "      <td>15526</td>\n",
              "      <td>61</td>\n",
              "      <td>61.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>287</td>\n",
              "      <td>23</td>\n",
              "      <td>124321</td>\n",
              "      <td>234044</td>\n",
              "      <td>220045</td>\n",
              "      <td>2157-10-22 13:00:00</td>\n",
              "      <td>2157-10-22 13:35:00</td>\n",
              "      <td>19907</td>\n",
              "      <td>79</td>\n",
              "      <td>79.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>71</td>\n",
              "      <td>23</td>\n",
              "      <td>124321</td>\n",
              "      <td>234044</td>\n",
              "      <td>220045</td>\n",
              "      <td>2157-10-21 19:00:00</td>\n",
              "      <td>2157-10-21 19:12:00</td>\n",
              "      <td>16978</td>\n",
              "      <td>66</td>\n",
              "      <td>66.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>2269</td>\n",
              "      <td>85</td>\n",
              "      <td>112077</td>\n",
              "      <td>291697</td>\n",
              "      <td>220045</td>\n",
              "      <td>2167-07-27 03:00:00</td>\n",
              "      <td>2167-07-27 03:26:00</td>\n",
              "      <td>21050</td>\n",
              "      <td>117</td>\n",
              "      <td>117.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>1905</td>\n",
              "      <td>36</td>\n",
              "      <td>165660</td>\n",
              "      <td>241249</td>\n",
              "      <td>220045</td>\n",
              "      <td>2134-05-16 04:00:00</td>\n",
              "      <td>2134-05-16 05:24:00</td>\n",
              "      <td>21198</td>\n",
              "      <td>83</td>\n",
              "      <td>83.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>637</td>\n",
              "      <td>34</td>\n",
              "      <td>144319</td>\n",
              "      <td>290505</td>\n",
              "      <td>220045</td>\n",
              "      <td>2191-02-24 11:00:00</td>\n",
              "      <td>2191-02-24 12:22:00</td>\n",
              "      <td>17741</td>\n",
              "      <td>59</td>\n",
              "      <td>59.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>2030</td>\n",
              "      <td>85</td>\n",
              "      <td>112077</td>\n",
              "      <td>291697</td>\n",
              "      <td>220045</td>\n",
              "      <td>2167-07-25 22:00:00</td>\n",
              "      <td>2167-07-25 22:07:00</td>\n",
              "      <td>21050</td>\n",
              "      <td>131</td>\n",
              "      <td>131.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>2073</td>\n",
              "      <td>85</td>\n",
              "      <td>112077</td>\n",
              "      <td>291697</td>\n",
              "      <td>220045</td>\n",
              "      <td>2167-07-26 03:00:00</td>\n",
              "      <td>2167-07-26 03:03:00</td>\n",
              "      <td>21050</td>\n",
              "      <td>85</td>\n",
              "      <td>85.0</td>\n",
              "      <td>bpm</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9521beee-370b-41e9-af8e-72bf51df868d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-9521beee-370b-41e9-af8e-72bf51df868d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-9521beee-370b-41e9-af8e-72bf51df868d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "    ROW_ID  SUBJECT_ID  HADM_ID  ...  ERROR  RESULTSTATUS STOPPED\n",
              "0       53          23   124321  ...      0          None    None\n",
              "1     1214          36   165660  ...      0          None    None\n",
              "2     1948          36   165660  ...      0          None    None\n",
              "3     1142          36   165660  ...      0          None    None\n",
              "4        2          23   124321  ...      0          None    None\n",
              "5     1774          36   165660  ...      0          None    None\n",
              "6     1800          36   165660  ...      0          None    None\n",
              "7     1997          36   165660  ...      0          None    None\n",
              "8     1276          36   165660  ...      0          None    None\n",
              "9     2320          85   112077  ...      0          None    None\n",
              "10     989          36   165660  ...      0          None    None\n",
              "11    2155          85   112077  ...      0          None    None\n",
              "12     596          34   144319  ...      0          None    None\n",
              "13     287          23   124321  ...      0          None    None\n",
              "14      71          23   124321  ...      0          None    None\n",
              "15    2269          85   112077  ...      0          None    None\n",
              "16    1905          36   165660  ...      0          None    None\n",
              "17     637          34   144319  ...      0          None    None\n",
              "18    2030          85   112077  ...      0          None    None\n",
              "19    2073          85   112077  ...      0          None    None\n",
              "\n",
              "[20 rows x 15 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eWztBEsp8mDw"
      },
      "source": [
        "## Load the chartevents data\n",
        "\n",
        "- The chartevents table contains data charted at the patient bedside. It includes variables such as heart rate, respiratory rate, temperature, and so on.\n",
        "- We'll begin by loading the chartevents data for _a single patient_."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n0JEvuZ_1_HM"
      },
      "source": [
        "### Exercise: load, for icustay_id 252522 from the table chartevents, the following columns: icustay_id, charttime, value and valuenum"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e8wXz4qb19NR"
      },
      "source": [
        "#your code here"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pHYuhx9M2XQO"
      },
      "source": [
        "### Exercise: now, add a column containing the time elapsed since ICU admission for all these records, in hours."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zlv3BRvA2p04"
      },
      "source": [
        "# your code here"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "62ZjxeFV2puq"
      },
      "source": [
        "Below is the final query, including the label of all the records."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "09bQTCLH8mDy",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "e3f621d2-5259-4bc0-8d4e-a889337584a8"
      },
      "source": [
        "query = \"\"\"\n",
        "SELECT de.icustay_id\n",
        "  , datetime_diff(de.charttime, ie.intime, hour) as hours\n",
        "  , di.itemid\n",
        "  , di.label\n",
        "  , de.value\n",
        "  , de.valuenum\n",
        "FROM `mindstream-ai.mimiciii_clinical.chartevents` de\n",
        "INNER join `mindstream-ai.mimiciii_clinical.d_items` di\n",
        "ON de.itemid = di.itemid\n",
        "INNER join `mindstream-ai.mimiciii_clinical.icustays` ie\n",
        "ON de.icustay_id = ie.icustay_id\n",
        "WHERE de.icustay_id = 252522\n",
        "ORDER BY charttime;\n",
        "\"\"\"\n",
        "\n",
        "ce = run_query(query)\n",
        "ce.head(100)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-4baf8fb2-ad6a-4da2-a30f-1229bece24fa\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>icustay_id</th>\n",
              "      <th>hours</th>\n",
              "      <th>itemid</th>\n",
              "      <th>label</th>\n",
              "      <th>value</th>\n",
              "      <th>valuenum</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>252522</td>\n",
              "      <td>0</td>\n",
              "      <td>227378</td>\n",
              "      <td>Patient Location</td>\n",
              "      <td>CC6D</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>252522</td>\n",
              "      <td>0</td>\n",
              "      <td>226381</td>\n",
              "      <td>Marital Status</td>\n",
              "      <td>Widowed</td>\n",
              "      <td>4.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>252522</td>\n",
              "      <td>0</td>\n",
              "      <td>226543</td>\n",
              "      <td>Religion</td>\n",
              "      <td>Unknown</td>\n",
              "      <td>15.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>252522</td>\n",
              "      <td>1</td>\n",
              "      <td>220210</td>\n",
              "      <td>Respiratory Rate</td>\n",
              "      <td>28</td>\n",
              "      <td>28.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>252522</td>\n",
              "      <td>1</td>\n",
              "      <td>220180</td>\n",
              "      <td>Non Invasive Blood Pressure diastolic</td>\n",
              "      <td>63</td>\n",
              "      <td>63.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>95</th>\n",
              "      <td>252522</td>\n",
              "      <td>1</td>\n",
              "      <td>227969</td>\n",
              "      <td>Safety Measures_U_1</td>\n",
              "      <td>Adequate lighting</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>96</th>\n",
              "      <td>252522</td>\n",
              "      <td>1</td>\n",
              "      <td>227342</td>\n",
              "      <td>Secondary diagnosis</td>\n",
              "      <td>Yes</td>\n",
              "      <td>15.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>97</th>\n",
              "      <td>252522</td>\n",
              "      <td>1</td>\n",
              "      <td>227944</td>\n",
              "      <td>Side Rails</td>\n",
              "      <td>3 rails up</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>98</th>\n",
              "      <td>252522</td>\n",
              "      <td>1</td>\n",
              "      <td>227969</td>\n",
              "      <td>Safety Measures_U_1</td>\n",
              "      <td>Bed locked in low position</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>99</th>\n",
              "      <td>252522</td>\n",
              "      <td>1</td>\n",
              "      <td>227345</td>\n",
              "      <td>Gait/Transferring</td>\n",
              "      <td>Bed rest</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>100 rows × 6 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4baf8fb2-ad6a-4da2-a30f-1229bece24fa')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-4baf8fb2-ad6a-4da2-a30f-1229bece24fa button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-4baf8fb2-ad6a-4da2-a30f-1229bece24fa');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "    icustay_id  hours  ...                       value valuenum\n",
              "0       252522      0  ...                        CC6D      NaN\n",
              "1       252522      0  ...                     Widowed      4.0\n",
              "2       252522      0  ...                     Unknown     15.0\n",
              "3       252522      1  ...                          28     28.0\n",
              "4       252522      1  ...                          63     63.0\n",
              "..         ...    ...  ...                         ...      ...\n",
              "95      252522      1  ...           Adequate lighting      NaN\n",
              "96      252522      1  ...                         Yes     15.0\n",
              "97      252522      1  ...                  3 rails up      NaN\n",
              "98      252522      1  ...  Bed locked in low position      NaN\n",
              "99      252522      1  ...                    Bed rest      0.0\n",
              "\n",
              "[100 rows x 6 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A-0VnKRv0e5Z"
      },
      "source": [
        "# Vital signs"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zDZqtRtR5ipB"
      },
      "source": [
        "### Exercise: extract the itemids 220045 (heart rate), 220210 (respiratory rate) and 226512 (weight) for hadm_id 100003 for the first 24h after ICU admission\n",
        "\n",
        "Tip: use the table _icustays_ to get the hospital admission time (\"intime\"). The SQL function \"interval\" can also be useful."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SqOQC4Ey5jWW",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "3d9661f0-73d9-4787-eb94-1591975e865b"
      },
      "source": [
        "# your code here\n",
        "\n",
        "query=\"\"\"\n",
        "SELECT ce.hadm_id, ce.charttime, ce.itemid, ce.value\n",
        "\n",
        "FROM `mindstream-ai.mimiciii_clinical.chartevents` ce\n",
        "INNER JOIN `mindstream-ai.mimiciii_clinical.icustays` ic\n",
        "ON ce.hadm_id = ic.hadm_id\n",
        "\n",
        "WHERE ce.charttime BETWEEN ic.intime and DATETIME_ADD(ic.intime, INTERVAL 1 DAY)\n",
        "      and ce.hadm_id=100003 and itemid in (220045,220210,226512)\n",
        "\n",
        "ORDER BY ce.charttime\n",
        "\"\"\"\n",
        "d=run_query(query)\n",
        "print(d)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "    hadm_id           charttime  itemid value\n",
            "0    100003 2150-04-17 17:50:00  226512  85.3\n",
            "1    100003 2150-04-17 17:51:00  220045    71\n",
            "2    100003 2150-04-17 17:52:00  220210    11\n",
            "3    100003 2150-04-17 18:00:00  220210    16\n",
            "4    100003 2150-04-17 18:00:00  220045    73\n",
            "..      ...                 ...     ...   ...\n",
            "64   100003 2150-04-18 13:00:00  220210    18\n",
            "65   100003 2150-04-18 14:00:00  220210    15\n",
            "66   100003 2150-04-18 14:00:00  220045    80\n",
            "67   100003 2150-04-18 15:00:00  220045    80\n",
            "68   100003 2150-04-18 15:00:00  220210    19\n",
            "\n",
            "[69 rows x 4 columns]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JBaTU5Zw7yJ6"
      },
      "source": [
        "### Exercise: using the query above as a subquery, create 3 new columns containig the maximum value of the 3 parameters during the first 24h after ICU admission."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kEITGA8B7zHl",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "a7b22e22-e472-499c-9728-5ee000252a1d"
      },
      "source": [
        "# your code here\n",
        "\n",
        "query=\"\"\"\n",
        "\n",
        "with t1 as (\n",
        "SELECT ce.hadm_id, ce.charttime, ce.itemid, ce.valuenum\n",
        "\n",
        "FROM `mindstream-ai.mimiciii_clinical.chartevents` ce\n",
        "INNER JOIN `mindstream-ai.mimiciii_clinical.icustays` ic\n",
        "ON ce.hadm_id = ic.hadm_id\n",
        "\n",
        "WHERE ce.charttime BETWEEN ic.intime and DATETIME_ADD(ic.intime, INTERVAL 1 DAY)\n",
        "and ce.hadm_id in (100001,100003) and itemid in (220045,220210,226512)\n",
        "\n",
        "ORDER BY ce.charttime\n",
        ")\n",
        "\n",
        "\n",
        "SELECT hadm_id,\n",
        "max(case when itemid in (220045) then round(valuenum) else Null end ) as hr,\n",
        "max(case when itemid in (220210) then round(valuenum) else Null end ) as rr,\n",
        "max(case when itemid in (226512) then round(valuenum) else Null end ) as weight\n",
        "FROM t1\n",
        "GROUP BY hadm_id\n",
        "\n",
        "\"\"\"\n",
        "d=run_query(query)\n",
        "print(d)\n",
        "\n",
        "#comment\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "   hadm_id     hr    rr  weight\n",
            "0   100003  104.0  21.0    85.0\n",
            "1   100001  124.0  22.0     NaN\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P_FaoYAy8mEu"
      },
      "source": [
        "### Exercise: Extract the maximum or minimum value during the first 24h after ICU admission for all the vital signs\n",
        "\n",
        "We provide you with the correct mapping of vital signs (which item IDs map which feature).\n",
        "\n",
        "Depending on the parameter, a high or a low value can indicate severity (domain knowledge!). This exercise will require using a subquery, then convert the raw data into tabular format with 1 column for each parameter."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q8HSk7NE8mEv",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "818a75d6-a0b7-405f-98d4-e288161abae4"
      },
      "source": [
        "query = \"\"\"\n",
        "with t1 as(\n",
        "  select ce.hadm_id, charttime, itemid, valuenum\n",
        "  FROM `mindstream-ai.mimiciii_clinical.chartevents` ce\n",
        "  INNER JOIN`mindstream-ai.mimiciii_clinical.icustays` ic\n",
        "  ON ce.hadm_id = ic.hadm_id\n",
        "  and ce.charttime between ic.intime and DATETIME_ADD(ic.intime, INTERVAL 1 DAY)\n",
        "  WHERE  itemid IN (581,580,224639,226512,198,211,220045,220181,220052,225312,224322,443,52,456,220210,3337,224422,618,3603,615,220277,646,834,3655,223762,223761,678,220074,113)\n",
        ")\n",
        "\n",
        "select hadm_id,\n",
        "max(case when itemid in (581,580,224639,226512) then round(valuenum) else Null end ) as weight,\n",
        "min(case when itemid in (198) then valuenum else Null end ) as gcs,\n",
        "max(case when itemid in (211,220045) then valuenum else Null end ) as hr,\n",
        "min(case when itemid in (220181,220052,225312,224322,443,52,456) then round(valuenum) else Null end ) as mbp,\n",
        "max(case when itemid in (220210,3337,224422,618,3603,615) then valuenum else Null end ) as rr,\n",
        "min(case when itemid in (220277,646,834) then valuenum else Null end ) as spo2,\n",
        "max(case when itemid in (3655,223762) then round(valuenum,2) when itemid in (223761,678) then round((cast(valuenum as float64) - 32)*0.5555,2) else Null end) as tempc,\n",
        "min(case when itemid in (220074,113) then valuenum else Null end ) as cvp\n",
        "\n",
        "from t1\n",
        "group by hadm_id\n",
        "order by hadm_id asc\n",
        "\"\"\"\n",
        "\n",
        "vitals = run_query(query)\n",
        "vitals.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-d4e8a9fb-9122-4a5c-8976-233be5162a8d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>weight</th>\n",
              "      <th>gcs</th>\n",
              "      <th>hr</th>\n",
              "      <th>mbp</th>\n",
              "      <th>rr</th>\n",
              "      <th>spo2</th>\n",
              "      <th>tempc</th>\n",
              "      <th>cvp</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>124.0</td>\n",
              "      <td>75.0</td>\n",
              "      <td>22.0</td>\n",
              "      <td>96.0</td>\n",
              "      <td>37.77</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>85.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>104.0</td>\n",
              "      <td>47.0</td>\n",
              "      <td>21.0</td>\n",
              "      <td>87.0</td>\n",
              "      <td>36.77</td>\n",
              "      <td>7.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>57.0</td>\n",
              "      <td>15.0</td>\n",
              "      <td>127.0</td>\n",
              "      <td>77.0</td>\n",
              "      <td>29.0</td>\n",
              "      <td>94.0</td>\n",
              "      <td>36.55</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>55.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>93.0</td>\n",
              "      <td>63.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>98.0</td>\n",
              "      <td>37.72</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>121.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>92.0</td>\n",
              "      <td>61.0</td>\n",
              "      <td>37.0</td>\n",
              "      <td>91.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>-1.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d4e8a9fb-9122-4a5c-8976-233be5162a8d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d4e8a9fb-9122-4a5c-8976-233be5162a8d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d4e8a9fb-9122-4a5c-8976-233be5162a8d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  weight   gcs     hr   mbp    rr  spo2  tempc  cvp\n",
              "0   100001     NaN   NaN  124.0  75.0  22.0  96.0  37.77  NaN\n",
              "1   100003    85.0   NaN  104.0  47.0  21.0  87.0  36.77  7.0\n",
              "2   100006    57.0  15.0  127.0  77.0  29.0  94.0  36.55  NaN\n",
              "3   100007    55.0  11.0   93.0  63.0  25.0  98.0  37.72  3.0\n",
              "4   100009   121.0   NaN   92.0  61.0  37.0  91.0    NaN -1.0"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0R_-3_uw8mE1"
      },
      "source": [
        "# Laboratory measurements\n",
        "\n",
        "We'll now load the labevents data for a single patient. This data corresponds to measurements made in a laboratory - usually on a sample of patient blood."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c_-EaX9E8mFO"
      },
      "source": [
        "### Exercise: Extract the maximum or minimum value during the first 24h after ICU admission for all the blood tests\n",
        "\n",
        "We'lll basically repeat the process for lab values.\n",
        "\n",
        "Same comments as above: depending on the parameter, a high or a low value can indicate severity (domain knowledge!). This exercise will require using a subquery, then convert the raw data into tabular format with 1 column for each parameter."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iAi6sUQL8mFP",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 677
        },
        "outputId": "791dc934-6f08-476f-ba4c-f34df033533b"
      },
      "source": [
        " query = \"\"\"\n",
        "\n",
        " with t1 as(\n",
        "  select lab.hadm_id, charttime, itemid, valuenum\n",
        "  FROM `mindstream-ai.mimiciii_clinical.labevents` lab\n",
        "  INNER JOIN `mindstream-ai.mimiciii_clinical.icustays` ic\n",
        "  ON lab.hadm_id = ic.hadm_id\n",
        "  and lab.charttime between ic.intime and datetime_add(ic.intime, interval 1 day)\n",
        "  WHERE itemid IN (51006,50912,51081,50885,50862,50811,51222,51300,51301,50820,50821,50818,50983, 50824,50809,50931, 50889)\n",
        ")\n",
        "\n",
        "select hadm_id,\n",
        "max(case when itemid in (50983,50824) then valuenum else Null end ) as sodium,\n",
        "max(case when itemid in (50809,50931) then valuenum else Null end ) as glucose,\n",
        "max(case when itemid in (51006) then valuenum else Null end ) as urea,\n",
        "max(case when itemid in (50912,51081) then valuenum else Null end ) as creatinine,\n",
        "max(case when itemid in (50885) then valuenum else Null end ) as bilirubin,\n",
        "max(case when itemid in (50862) then valuenum else Null end ) as albumin,\n",
        "min(case when itemid in (50811,51222) then valuenum else Null end ) as hb,\n",
        "max(case when itemid in (51300,51301) then valuenum else Null end ) as wbc,\n",
        "max(case when itemid in (50889) then valuenum else Null end ) as crp,\n",
        "min(case when itemid in (50820) then valuenum else Null end ) as ph,\n",
        "min(case when itemid in (50821) then valuenum else Null end ) as po2,\n",
        "max(case when itemid in (50818) then valuenum else Null end ) as pco2\n",
        "from t1\n",
        "group by hadm_id\n",
        "order by hadm_id asc\n",
        "\n",
        "  \"\"\"\n",
        "\n",
        "labs = run_query(query)\n",
        "labs.head(20)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-7c84ad36-072b-4071-8c35-d6eecb540fb9\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>sodium</th>\n",
              "      <th>glucose</th>\n",
              "      <th>urea</th>\n",
              "      <th>creatinine</th>\n",
              "      <th>bilirubin</th>\n",
              "      <th>albumin</th>\n",
              "      <th>hb</th>\n",
              "      <th>wbc</th>\n",
              "      <th>crp</th>\n",
              "      <th>ph</th>\n",
              "      <th>po2</th>\n",
              "      <th>pco2</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>144.0</td>\n",
              "      <td>185.0</td>\n",
              "      <td>42.0</td>\n",
              "      <td>2.4</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>133.0</td>\n",
              "      <td>113.0</td>\n",
              "      <td>49.0</td>\n",
              "      <td>1.2</td>\n",
              "      <td>5.5</td>\n",
              "      <td>2.3</td>\n",
              "      <td>7.1</td>\n",
              "      <td>14.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.37</td>\n",
              "      <td>87.0</td>\n",
              "      <td>29.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>131.0</td>\n",
              "      <td>129.0</td>\n",
              "      <td>16.0</td>\n",
              "      <td>0.6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>10.3</td>\n",
              "      <td>13.6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.34</td>\n",
              "      <td>69.0</td>\n",
              "      <td>48.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>140.0</td>\n",
              "      <td>137.0</td>\n",
              "      <td>14.0</td>\n",
              "      <td>0.7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11.3</td>\n",
              "      <td>12.3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>143.0</td>\n",
              "      <td>177.0</td>\n",
              "      <td>13.0</td>\n",
              "      <td>0.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11.5</td>\n",
              "      <td>17.3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.28</td>\n",
              "      <td>88.0</td>\n",
              "      <td>59.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>100010</td>\n",
              "      <td>138.0</td>\n",
              "      <td>133.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>1.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9.7</td>\n",
              "      <td>15.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>100011</td>\n",
              "      <td>141.0</td>\n",
              "      <td>160.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>1.3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9.0</td>\n",
              "      <td>17.3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.25</td>\n",
              "      <td>185.0</td>\n",
              "      <td>52.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>100012</td>\n",
              "      <td>133.0</td>\n",
              "      <td>129.0</td>\n",
              "      <td>14.0</td>\n",
              "      <td>0.9</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>8.1</td>\n",
              "      <td>14.1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.34</td>\n",
              "      <td>156.0</td>\n",
              "      <td>46.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>100016</td>\n",
              "      <td>137.0</td>\n",
              "      <td>76.0</td>\n",
              "      <td>51.0</td>\n",
              "      <td>1.1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1.6</td>\n",
              "      <td>10.4</td>\n",
              "      <td>9.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.43</td>\n",
              "      <td>86.0</td>\n",
              "      <td>42.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>100017</td>\n",
              "      <td>143.0</td>\n",
              "      <td>77.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>0.8</td>\n",
              "      <td>0.8</td>\n",
              "      <td>4.1</td>\n",
              "      <td>14.3</td>\n",
              "      <td>9.3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.52</td>\n",
              "      <td>171.0</td>\n",
              "      <td>32.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>100018</td>\n",
              "      <td>133.0</td>\n",
              "      <td>214.0</td>\n",
              "      <td>43.0</td>\n",
              "      <td>1.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>8.8</td>\n",
              "      <td>14.5</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.37</td>\n",
              "      <td>104.0</td>\n",
              "      <td>41.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>100020</td>\n",
              "      <td>131.0</td>\n",
              "      <td>101.0</td>\n",
              "      <td>16.0</td>\n",
              "      <td>0.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11.3</td>\n",
              "      <td>5.6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>100021</td>\n",
              "      <td>139.0</td>\n",
              "      <td>142.0</td>\n",
              "      <td>15.0</td>\n",
              "      <td>0.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>10.2</td>\n",
              "      <td>7.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>100023</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>16.9</td>\n",
              "      <td>17.7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>100024</td>\n",
              "      <td>140.0</td>\n",
              "      <td>161.0</td>\n",
              "      <td>18.0</td>\n",
              "      <td>0.9</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9.4</td>\n",
              "      <td>12.3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.34</td>\n",
              "      <td>114.0</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>100028</td>\n",
              "      <td>139.0</td>\n",
              "      <td>118.0</td>\n",
              "      <td>16.0</td>\n",
              "      <td>0.7</td>\n",
              "      <td>6.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11.7</td>\n",
              "      <td>24.6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>100030</td>\n",
              "      <td>136.0</td>\n",
              "      <td>134.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>0.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>14.0</td>\n",
              "      <td>9.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>100031</td>\n",
              "      <td>136.0</td>\n",
              "      <td>185.0</td>\n",
              "      <td>18.0</td>\n",
              "      <td>0.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>6.7</td>\n",
              "      <td>6.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.26</td>\n",
              "      <td>41.0</td>\n",
              "      <td>56.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>100033</td>\n",
              "      <td>138.0</td>\n",
              "      <td>85.0</td>\n",
              "      <td>13.0</td>\n",
              "      <td>0.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>15.1</td>\n",
              "      <td>11.9</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>100034</td>\n",
              "      <td>143.0</td>\n",
              "      <td>190.0</td>\n",
              "      <td>17.0</td>\n",
              "      <td>0.9</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9.2</td>\n",
              "      <td>19.3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.33</td>\n",
              "      <td>66.0</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7c84ad36-072b-4071-8c35-d6eecb540fb9')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-7c84ad36-072b-4071-8c35-d6eecb540fb9 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-7c84ad36-072b-4071-8c35-d6eecb540fb9');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "    hadm_id  sodium  glucose  urea  creatinine  ...   wbc  crp    ph    po2  pco2\n",
              "0    100001   144.0    185.0  42.0         2.4  ...  11.2  NaN   NaN    NaN   NaN\n",
              "1    100003   133.0    113.0  49.0         1.2  ...  14.2  NaN  7.37   87.0  29.0\n",
              "2    100006   131.0    129.0  16.0         0.6  ...  13.6  NaN  7.34   69.0  48.0\n",
              "3    100007   140.0    137.0  14.0         0.7  ...  12.3  NaN   NaN    NaN   NaN\n",
              "4    100009   143.0    177.0  13.0         0.8  ...  17.3  NaN  7.28   88.0  59.0\n",
              "5    100010   138.0    133.0  12.0         1.2  ...  15.0  NaN   NaN    NaN   NaN\n",
              "6    100011   141.0    160.0  12.0         1.3  ...  17.3  NaN  7.25  185.0  52.0\n",
              "7    100012   133.0    129.0  14.0         0.9  ...  14.1  NaN  7.34  156.0  46.0\n",
              "8    100016   137.0     76.0  51.0         1.1  ...   9.8  NaN  7.43   86.0  42.0\n",
              "9    100017   143.0     77.0  10.0         0.8  ...   9.3  NaN  7.52  171.0  32.0\n",
              "10   100018   133.0    214.0  43.0         1.2  ...  14.5  NaN  7.37  104.0  41.0\n",
              "11   100020   131.0    101.0  16.0         0.8  ...   5.6  NaN   NaN    NaN   NaN\n",
              "12   100021   139.0    142.0  15.0         0.8  ...   7.2  NaN   NaN    NaN   NaN\n",
              "13   100023     NaN      NaN   NaN         NaN  ...  17.7  NaN   NaN    NaN   NaN\n",
              "14   100024   140.0    161.0  18.0         0.9  ...  12.3  NaN  7.34  114.0  50.0\n",
              "15   100028   139.0    118.0  16.0         0.7  ...  24.6  NaN   NaN    NaN   NaN\n",
              "16   100030   136.0    134.0  12.0         0.8  ...   9.8  NaN   NaN    NaN   NaN\n",
              "17   100031   136.0    185.0  18.0         0.8  ...   6.0  NaN  7.26   41.0  56.0\n",
              "18   100033   138.0     85.0  13.0         0.8  ...  11.9  NaN   NaN    NaN   NaN\n",
              "19   100034   143.0    190.0  17.0         0.9  ...  19.3  NaN  7.33   66.0  50.0\n",
              "\n",
              "[20 rows x 13 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WELvR4gS8mFV"
      },
      "source": [
        "# Urine output\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "m3uuGdvO_rVU"
      },
      "source": [
        "### Exercise: try to list all the item_ids related to urine output in d_items"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iJhaMXJh_rzc"
      },
      "source": [
        "# your code here\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jEIF5Sfa_sns"
      },
      "source": [
        "The query below gives the total urine output for the first 24h after ICU admission."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gyGQwH7f8mFZ",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "643250d4-f838-4664-eeb3-d6d85b3036c1"
      },
      "source": [
        " query= \"\"\"\n",
        " select oe.hadm_id, sum(oe.VALUE) as urineoutput\n",
        "  FROM `mindstream-ai.mimiciii_clinical.outputevents` oe\n",
        "  INNER JOIN `mindstream-ai.mimiciii_clinical.icustays` ic\n",
        "  ON oe.hadm_id = ic.hadm_id\n",
        "  and oe.charttime between ic.intime and datetime_add(ic.intime, interval 1 day) -- first ICU day\n",
        "  WHERE itemid IN\n",
        "  (\n",
        "  -- these are the most frequently occurring urine output observations in CareVue\n",
        "  40055, -- \"Urine Out Foley\"\n",
        "  43175, -- \"Urine .\"\n",
        "  40069, -- \"Urine Out Void\"\n",
        "  40094, -- \"Urine Out Condom Cath\"\n",
        "  40715, -- \"Urine Out Suprapubic\"\n",
        "  40473, -- \"Urine Out IleoConduit\"\n",
        "  40085, -- \"Urine Out Incontinent\"\n",
        "  40057, -- \"Urine Out Rt Nephrostomy\"\n",
        "  40056, -- \"Urine Out Lt Nephrostomy\"\n",
        "  40405, -- \"Urine Out Other\"\n",
        "  40428, -- \"Urine Out Straight Cath\"\n",
        "  40086,--\tUrine Out Incontinent\n",
        "  40096, -- \"Urine Out Ureteral Stent #1\"\n",
        "  40651, -- \"Urine Out Ureteral Stent #2\"\n",
        "\n",
        "  -- these are the most frequently occurring urine output observations in Metavision\n",
        "  226559, -- \"Foley\"\n",
        "  226560, -- \"Void\"\n",
        "  227510, -- \"TF Residual\"\n",
        "  226561, -- \"Condom Cath\"\n",
        "  226584, -- \"Ileoconduit\"\n",
        "  226563, -- \"Suprapubic\"\n",
        "  226564, -- \"R Nephrostomy\"\n",
        "  226565, -- \"L Nephrostomy\"\n",
        "  226567, --\tStraight Cath\n",
        "  226557, -- \"R Ureteral Stent\"\n",
        "  226558  -- \"L Ureteral Stent\"\n",
        "  )\n",
        "  group by oe.hadm_id\n",
        "\"\"\"\n",
        "uo  = run_query(query)\n",
        "uo.head()\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-0455d4d3-1519-47c7-b91d-d6c844822401\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>urineoutput</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>188531</td>\n",
              "      <td>1405.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>181188</td>\n",
              "      <td>2890.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>142131</td>\n",
              "      <td>4335.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>174927</td>\n",
              "      <td>1660.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>171509</td>\n",
              "      <td>1450.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0455d4d3-1519-47c7-b91d-d6c844822401')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-0455d4d3-1519-47c7-b91d-d6c844822401 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-0455d4d3-1519-47c7-b91d-d6c844822401');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  urineoutput\n",
              "0   188531       1405.0\n",
              "1   181188       2890.0\n",
              "2   142131       4335.0\n",
              "3   174927       1660.0\n",
              "4   171509       1450.0"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cwRc5p2L8mFp"
      },
      "source": [
        "# ICD codes\n",
        "\n",
        "The international classification of diseases (ICD) is a coding system used to record patients' past medical history, diagnoses and procedures.\n",
        "MIMIC-III contains ICD (version 9) data. This can be used to augment a mortality prediction score. For example, APACHE-IV includes several items related to patient past medical history: https://intensivecarenetwork.com/Calculators/Files/Apache4.html\n",
        "\n",
        "Example: let's look for ICD codes for 'congestive heart failure'. You'll find these codes online, e.g.: https://en.wikipedia.org/wiki/List_of_ICD-9_codes_390%E2%80%93459:_diseases_of_the_circulatory_system#Other_forms_of_heart_disease_(420%E2%80%93429)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yhWIHbJJ8mFs",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 677
        },
        "outputId": "25309011-4ce1-4594-a39a-c86807f28fec"
      },
      "source": [
        "query=\"\"\"\n",
        "select hadm_id, seq_num, icd9_code\n",
        ", CASE\n",
        "  when icd9_code in ('39891','40201','40211','40291','40401','40403','40411','40413','40491','40493') then 1\n",
        "  when SUBSTR(icd9_code, 1, 4) in ('4254','4255','4257','4258','4259') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3) in ('428') then 1\n",
        "  else 0 end as chf\n",
        "from `mindstream-ai.mimiciii_clinical.diagnoses_icd`\n",
        "where subject_id<100 -- to save time\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "d  = run_query(query)\n",
        "d.head(20)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-52c7597b-6866-47e3-8bcb-b826f08ad96c\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>seq_num</th>\n",
              "      <th>icd9_code</th>\n",
              "      <th>chf</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>163353</td>\n",
              "      <td>1</td>\n",
              "      <td>V3001</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>163353</td>\n",
              "      <td>2</td>\n",
              "      <td>V053</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>163353</td>\n",
              "      <td>3</td>\n",
              "      <td>V290</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>145834</td>\n",
              "      <td>1</td>\n",
              "      <td>0389</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>145834</td>\n",
              "      <td>2</td>\n",
              "      <td>78559</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>145834</td>\n",
              "      <td>3</td>\n",
              "      <td>5849</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>145834</td>\n",
              "      <td>4</td>\n",
              "      <td>4275</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>145834</td>\n",
              "      <td>5</td>\n",
              "      <td>41071</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>145834</td>\n",
              "      <td>6</td>\n",
              "      <td>4280</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>145834</td>\n",
              "      <td>7</td>\n",
              "      <td>6826</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>145834</td>\n",
              "      <td>8</td>\n",
              "      <td>4254</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>145834</td>\n",
              "      <td>9</td>\n",
              "      <td>2639</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>185777</td>\n",
              "      <td>1</td>\n",
              "      <td>042</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>185777</td>\n",
              "      <td>2</td>\n",
              "      <td>1363</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>185777</td>\n",
              "      <td>3</td>\n",
              "      <td>7994</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>185777</td>\n",
              "      <td>4</td>\n",
              "      <td>2763</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>185777</td>\n",
              "      <td>5</td>\n",
              "      <td>7907</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>185777</td>\n",
              "      <td>6</td>\n",
              "      <td>5715</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>185777</td>\n",
              "      <td>7</td>\n",
              "      <td>04111</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>185777</td>\n",
              "      <td>8</td>\n",
              "      <td>V090</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-52c7597b-6866-47e3-8bcb-b826f08ad96c')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-52c7597b-6866-47e3-8bcb-b826f08ad96c button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-52c7597b-6866-47e3-8bcb-b826f08ad96c');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "    hadm_id  seq_num icd9_code  chf\n",
              "0    163353        1     V3001    0\n",
              "1    163353        2      V053    0\n",
              "2    163353        3      V290    0\n",
              "3    145834        1      0389    0\n",
              "4    145834        2     78559    0\n",
              "5    145834        3      5849    0\n",
              "6    145834        4      4275    0\n",
              "7    145834        5     41071    0\n",
              "8    145834        6      4280    1\n",
              "9    145834        7      6826    0\n",
              "10   145834        8      4254    1\n",
              "11   145834        9      2639    0\n",
              "12   185777        1       042    0\n",
              "13   185777        2      1363    0\n",
              "14   185777        3      7994    0\n",
              "15   185777        4      2763    0\n",
              "16   185777        5      7907    0\n",
              "17   185777        6      5715    0\n",
              "18   185777        7     04111    0\n",
              "19   185777        8      V090    0"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dJ662zdE8mF0"
      },
      "source": [
        "We've already found some patients with a history of CHF!\n",
        "\n",
        "Now let's look for some more ICD codes that we think could be related to mortality, and encode their presence as a binary feature. Our new 'COMORBIDITY' score can take values from 0 to 9."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "m41CYqcR8mF4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "outputId": "11814a5b-9cff-4877-c140-335a14019704"
      },
      "source": [
        "query=\"\"\"\n",
        "with icd as\n",
        "(\n",
        "  select hadm_id, seq_num, icd9_code\n",
        "  from `mindstream-ai.mimiciii_clinical.diagnoses_icd`\n",
        "  where seq_num != 1 -- we do not include the primary icd-9 code\n",
        ")\n",
        "\n",
        ", flag as\n",
        "(\n",
        "select hadm_id, icd9_code\n",
        ", CASE\n",
        "  when icd9_code in ('39891','40201','40211','40291','40401','40403','40411','40413','40491','40493') then 1\n",
        "  when SUBSTR(icd9_code, 1, 4)  in ('4254','4255','4257','4258','4259') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('428') then 1\n",
        "  else 0 end as chf       /* Congestive heart failure */\n",
        "\n",
        ", CASE\n",
        "  when SUBSTR(icd9_code, 1, 4)  in ('4168','4169','5064','5081','5088') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('490','491','492','493','494','495','496','500','501','502','503','504','505') then 1\n",
        "  else 0 end as lung  /* Chronic pulmonary disease */\n",
        "\n",
        ", CASE\n",
        "  when SUBSTR(icd9_code, 1, 4) in ('2500','2501','2502','2503','2504','2505','2506','2507','2508','2509') then 1\n",
        "  else 0 end as dm      /* Diabetes*/\n",
        "\n",
        ", CASE\n",
        "  when icd9_code in ('40301','40311','40391','40402','40403','40412','40413','40492','40493') then 1\n",
        "  when SUBSTR(icd9_code, 1, 4)  in ('5880','V420','V451') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('585','586','V56') then 1\n",
        "  else 0 end as ren  /* Renal failure */\n",
        "\n",
        ", CASE\n",
        "  when icd9_code in ('07022','07023','07032','07033','07044','07054') then 1\n",
        "  when SUBSTR(icd9_code, 1, 4)  in ('0706','0709','4560','4561','4562','5722','5723','5724','5728','5733','5734','5738','5739','V427') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('570','571') then 1\n",
        "  else 0 end as liver     /* Liver disease */\n",
        "\n",
        ", CASE\n",
        "  when SUBSTR(icd9_code, 1, 4)  in ('2030','2386') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('200','201','202') then 1\n",
        "  else 0 end as lymph     /* Lymphoma */\n",
        "\n",
        ", CASE\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('196','197','198','199') then 1\n",
        "  else 0 end as mets      /* Metastatic cancer */\n",
        "\n",
        ", CASE\n",
        "  when SUBSTR(icd9_code, 1, 4)  in ('2652','2911','2912','2913','2915','2918','2919','3030','3039','3050','3575','4255','5353','5710','5711','5712','5713','V113') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('980') then 1\n",
        "  else 0 end as alcohol /* Alcohol abuse */\n",
        "\n",
        ", CASE\n",
        "  when icd9_code in ('V6542') then 1\n",
        "  when SUBSTR(icd9_code, 1, 4)  in ('3052','3053','3054','3055','3056','3057','3058','3059') then 1\n",
        "  when SUBSTR(icd9_code, 1, 3)  in ('292','304') then 1\n",
        "  else 0 end as drug /* Drug abuse */\n",
        "\n",
        "from icd\n",
        ")\n",
        "\n",
        "-- collapse the icd9_code specific flags into hadm_id specific flags\n",
        "-- this groups comorbidities together for a single patient admission\n",
        "\n",
        ", grp as\n",
        "(\n",
        "  select hadm_id\n",
        "  , max(chf) as chf\n",
        "  , max(lung) as lung\n",
        "  , max(dm) as dm\n",
        "  , max(ren) as ren\n",
        "  , max(liver) as liver\n",
        "  , max(lymph) as lymph\n",
        "  , max(mets) as mets\n",
        "  , max(alcohol) as alcohol\n",
        "  , max(drug) as drug\n",
        "from flag\n",
        "group by hadm_id\n",
        ")\n",
        "\n",
        "\n",
        "\n",
        "select hadm_id\n",
        ", chf as CONGESTIVE_HEART_FAILURE\n",
        ", lung as CHRONIC_PULMONARY\n",
        ", dm as DIABETES\n",
        ", ren as RENAL_FAILURE\n",
        ", liver as LIVER_DISEASE\n",
        ", lymph as LYMPHOMA\n",
        ", mets as METASTATIC_CANCER\n",
        ", alcohol as ALCOHOL_ABUSE\n",
        ", drug as DRUG_ABUSE\n",
        ", chf+lung+dm+ren+liver+lymph+mets+alcohol+drug as comorbidity\n",
        "from grp\n",
        "order by hadm_id;\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "comorbidity  = run_query(query)\n",
        "comorbidity.head(10)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-9b9db25c-1a82-4fe4-82d3-2534a67e07a2\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>CONGESTIVE_HEART_FAILURE</th>\n",
              "      <th>CHRONIC_PULMONARY</th>\n",
              "      <th>DIABETES</th>\n",
              "      <th>RENAL_FAILURE</th>\n",
              "      <th>LIVER_DISEASE</th>\n",
              "      <th>LYMPHOMA</th>\n",
              "      <th>METASTATIC_CANCER</th>\n",
              "      <th>ALCOHOL_ABUSE</th>\n",
              "      <th>DRUG_ABUSE</th>\n",
              "      <th>comorbidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>100010</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>100011</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>100012</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>100014</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>100016</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9b9db25c-1a82-4fe4-82d3-2534a67e07a2')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-9b9db25c-1a82-4fe4-82d3-2534a67e07a2 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-9b9db25c-1a82-4fe4-82d3-2534a67e07a2');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  CONGESTIVE_HEART_FAILURE  ...  DRUG_ABUSE  comorbidity\n",
              "0   100001                         0  ...           0            2\n",
              "1   100003                         0  ...           0            1\n",
              "2   100006                         0  ...           0            1\n",
              "3   100007                         0  ...           0            0\n",
              "4   100009                         0  ...           0            1\n",
              "5   100010                         0  ...           0            1\n",
              "6   100011                         0  ...           1            1\n",
              "7   100012                         0  ...           0            0\n",
              "8   100014                         0  ...           0            0\n",
              "9   100016                         0  ...           0            0\n",
              "\n",
              "[10 rows x 11 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "96JnXZnK8mGB"
      },
      "source": [
        "Let's keep only the total score value..."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZtITHBR48mGB",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "outputId": "afb6b1d4-23b2-4f2d-8036-60caa3eba481"
      },
      "source": [
        "comorbidity=comorbidity[['hadm_id','comorbidity']]\n",
        "comorbidity.head(10)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-3fb4dc0a-3f4b-41b0-b09a-9e1a102ce6df\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>comorbidity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>100010</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>100011</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>100012</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>100014</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>100016</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3fb4dc0a-3f4b-41b0-b09a-9e1a102ce6df')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-3fb4dc0a-3f4b-41b0-b09a-9e1a102ce6df button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-3fb4dc0a-3f4b-41b0-b09a-9e1a102ce6df');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  comorbidity\n",
              "0   100001            2\n",
              "1   100003            1\n",
              "2   100006            1\n",
              "3   100007            0\n",
              "4   100009            1\n",
              "5   100010            1\n",
              "6   100011            1\n",
              "7   100012            0\n",
              "8   100014            0\n",
              "9   100016            0"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VnOQwScd8mGF"
      },
      "source": [
        "# Patient outcome: hospital mortality and mortality at 90 days\n",
        "\n",
        "We'll use patient parameters on day 1 of ICU admission to predict whether they'll be dead or alive at the end of their hospital stay and 90 days after ICU admission. Note: if the patients dies after leaving the hospital, but before 90 days, his hospital mortality will be 0 but his 90-day mortality will be 1."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "r5mQNXWi8mGG",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 444
        },
        "outputId": "e0b8d0b6-2428-4084-8bc5-8d19c07cf8ed"
      },
      "source": [
        "query=\"\"\"\n",
        "select ad.hadm_id\n",
        "    , i.icustay_id\n",
        "    , admittime\n",
        "    , dischtime\n",
        "    , ROW_NUMBER() over (partition by ad.subject_id order by i.intime asc) as adm_order\n",
        "    , dob\n",
        "    , dod\n",
        "    , p.expire_flag as died\n",
        "    , ad.hospital_expire_flag as morta_hosp\n",
        "    , case when (datetime_diff(dod,admittime,day)<=90) = True then 1 else 0 end as morta_90d\n",
        "from `mindstream-ai.mimiciii_clinical.admissions` ad, `mindstream-ai.mimiciii_clinical.icustays` i, `mindstream-ai.mimiciii_clinical.patients` p\n",
        "where ad.hadm_id=i.hadm_id and p.subject_id=i.subject_id\n",
        "order by ad.hadm_id asc, adm_order\n",
        "\"\"\"\n",
        "d  = run_query(query)\n",
        "d.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-9ba8d94d-f2ae-4eef-8770-d40c43d31e56\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>icustay_id</th>\n",
              "      <th>admittime</th>\n",
              "      <th>dischtime</th>\n",
              "      <th>adm_order</th>\n",
              "      <th>dob</th>\n",
              "      <th>dod</th>\n",
              "      <th>died</th>\n",
              "      <th>morta_hosp</th>\n",
              "      <th>morta_90d</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>275225</td>\n",
              "      <td>2117-09-11 11:46:00</td>\n",
              "      <td>2117-09-17 16:45:00</td>\n",
              "      <td>1</td>\n",
              "      <td>2082-03-21</td>\n",
              "      <td>NaT</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>209281</td>\n",
              "      <td>2150-04-17 15:34:00</td>\n",
              "      <td>2150-04-21 17:30:00</td>\n",
              "      <td>1</td>\n",
              "      <td>2090-05-19</td>\n",
              "      <td>2150-12-28</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>291788</td>\n",
              "      <td>2108-04-06 15:49:00</td>\n",
              "      <td>2108-04-18 17:18:00</td>\n",
              "      <td>1</td>\n",
              "      <td>2059-05-07</td>\n",
              "      <td>2109-10-24</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>217937</td>\n",
              "      <td>2145-03-31 05:33:00</td>\n",
              "      <td>2145-04-07 12:40:00</td>\n",
              "      <td>1</td>\n",
              "      <td>2071-06-04</td>\n",
              "      <td>NaT</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>253656</td>\n",
              "      <td>2162-05-16 15:56:00</td>\n",
              "      <td>2162-05-21 13:37:00</td>\n",
              "      <td>1</td>\n",
              "      <td>2101-07-30</td>\n",
              "      <td>NaT</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9ba8d94d-f2ae-4eef-8770-d40c43d31e56')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-9ba8d94d-f2ae-4eef-8770-d40c43d31e56 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-9ba8d94d-f2ae-4eef-8770-d40c43d31e56');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  icustay_id           admittime  ... died  morta_hosp morta_90d\n",
              "0   100001      275225 2117-09-11 11:46:00  ...    0           0         0\n",
              "1   100003      209281 2150-04-17 15:34:00  ...    1           0         0\n",
              "2   100006      291788 2108-04-06 15:49:00  ...    1           0         0\n",
              "3   100007      217937 2145-03-31 05:33:00  ...    0           0         0\n",
              "4   100009      253656 2162-05-16 15:56:00  ...    0           0         0\n",
              "\n",
              "[5 rows x 10 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LMLb4zjZ8mGK"
      },
      "source": [
        "### Exercise: extract only hadm_id, hospital and 90-day mortality from the table above, and only for the first ICU stay in each hospital stay"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CZLaamZU8mGL",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "d8d1e8bc-b45d-4e26-8d9c-9aca676bf4eb"
      },
      "source": [
        "query=\"\"\"\n",
        "with t1 as (\n",
        "select ad.hadm_id\n",
        "    , i.icustay_id\n",
        "    , admittime\n",
        "    , dischtime\n",
        "    , ROW_NUMBER() over (partition by ad.subject_id order by i.intime asc) as adm_order\n",
        "    , dob\n",
        "    , dod\n",
        "    , p.expire_flag as died\n",
        "    , ad.hospital_expire_flag as morta_hosp\n",
        "    , case when (datetime_diff(dod,admittime,day)<=90) = True then 1 else 0 end as morta_90d\n",
        "from `mindstream-ai.mimiciii_clinical.admissions` ad, `mindstream-ai.mimiciii_clinical.icustays` i, `mindstream-ai.mimiciii_clinical.patients` p\n",
        "where ad.hadm_id=i.hadm_id and p.subject_id=i.subject_id\n",
        "order by ad.hadm_id asc, adm_order\n",
        ")\n",
        "\n",
        "select hadm_id,morta_hosp, morta_90d\n",
        "from t1\n",
        "where adm_order=1\n",
        "\"\"\"\n",
        "mortality = run_query(query)\n",
        "mortality.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-308166d1-4c68-4498-83ef-cce379c82301\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>morta_hosp</th>\n",
              "      <th>morta_90d</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100006</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100007</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100009</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-308166d1-4c68-4498-83ef-cce379c82301')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-308166d1-4c68-4498-83ef-cce379c82301 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-308166d1-4c68-4498-83ef-cce379c82301');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  morta_hosp  morta_90d\n",
              "0   100001           0          0\n",
              "1   100003           0          0\n",
              "2   100006           0          0\n",
              "3   100007           0          0\n",
              "4   100009           0          0"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oQ9xKKqRK91F",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d7b1919b-1ef4-4b3b-d14b-b28f1858ba55"
      },
      "source": [
        "# average mortality\n",
        "mortality.morta_90d.mean()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.15119631637834582"
            ]
          },
          "metadata": {},
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "efLE_My88mGO"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EHa-63Ax8mGP"
      },
      "source": [
        "# Data extraction\n",
        "\n",
        "Here, we'll **join** all the subtables we have created along the way into a single big dataframe."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C-NOz-Y_8mGT",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 112
        },
        "outputId": "f16fba39-fdff-4f21-b59f-667f67c00e43"
      },
      "source": [
        "d = pd.merge(cohort, gender, how='left',on='hadm_id')\n",
        "d = pd.merge(d,comorbidity,how='left',on='hadm_id')\n",
        "d = pd.merge(d,vitals,how='left',on='hadm_id')\n",
        "d = pd.merge(d,uo,how='left',on='hadm_id')\n",
        "d = pd.merge(d,labs,how='left',on='hadm_id')\n",
        "data = pd.merge(d,mortality,how='left',on='hadm_id')\n",
        "\n",
        "data.head(2)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-2b5abf8b-1a35-44e6-ba8b-622b5c304939\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>hadm_id</th>\n",
              "      <th>age</th>\n",
              "      <th>gender</th>\n",
              "      <th>comorbidity</th>\n",
              "      <th>weight</th>\n",
              "      <th>gcs</th>\n",
              "      <th>hr</th>\n",
              "      <th>mbp</th>\n",
              "      <th>rr</th>\n",
              "      <th>spo2</th>\n",
              "      <th>tempc</th>\n",
              "      <th>cvp</th>\n",
              "      <th>urineoutput</th>\n",
              "      <th>sodium</th>\n",
              "      <th>glucose</th>\n",
              "      <th>urea</th>\n",
              "      <th>creatinine</th>\n",
              "      <th>bilirubin</th>\n",
              "      <th>albumin</th>\n",
              "      <th>hb</th>\n",
              "      <th>wbc</th>\n",
              "      <th>crp</th>\n",
              "      <th>ph</th>\n",
              "      <th>po2</th>\n",
              "      <th>pco2</th>\n",
              "      <th>morta_hosp</th>\n",
              "      <th>morta_90d</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100001</td>\n",
              "      <td>35</td>\n",
              "      <td>0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>124.0</td>\n",
              "      <td>75.0</td>\n",
              "      <td>22.0</td>\n",
              "      <td>96.0</td>\n",
              "      <td>37.77</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3900.0</td>\n",
              "      <td>144.0</td>\n",
              "      <td>185.0</td>\n",
              "      <td>42.0</td>\n",
              "      <td>2.4</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11.0</td>\n",
              "      <td>11.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100003</td>\n",
              "      <td>60</td>\n",
              "      <td>1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>85.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>104.0</td>\n",
              "      <td>47.0</td>\n",
              "      <td>21.0</td>\n",
              "      <td>87.0</td>\n",
              "      <td>36.77</td>\n",
              "      <td>7.0</td>\n",
              "      <td>2580.0</td>\n",
              "      <td>133.0</td>\n",
              "      <td>113.0</td>\n",
              "      <td>49.0</td>\n",
              "      <td>1.2</td>\n",
              "      <td>5.5</td>\n",
              "      <td>2.3</td>\n",
              "      <td>7.1</td>\n",
              "      <td>14.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.37</td>\n",
              "      <td>87.0</td>\n",
              "      <td>29.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2b5abf8b-1a35-44e6-ba8b-622b5c304939')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-2b5abf8b-1a35-44e6-ba8b-622b5c304939 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-2b5abf8b-1a35-44e6-ba8b-622b5c304939');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "   hadm_id  age  gender  comorbidity  ...   po2  pco2  morta_hosp  morta_90d\n",
              "0   100001   35       0          2.0  ...   NaN   NaN         0.0        0.0\n",
              "1   100003   60       1          1.0  ...  87.0  29.0         0.0        0.0\n",
              "\n",
              "[2 rows x 27 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mQRcPbEOFUjQ"
      },
      "source": [
        "### Missing data\n",
        "\n",
        "Let's look at the count of missing values per column."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "r1v3lvnkB2ZI",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "bcfeaf88-43ee-4736-8b8e-ccfe80add14a"
      },
      "source": [
        "np.sum(np.isnan(data))"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "hadm_id            0\n",
              "age                0\n",
              "gender             0\n",
              "comorbidity      183\n",
              "weight         11538\n",
              "gcs            20054\n",
              "hr               294\n",
              "mbp              296\n",
              "rr               351\n",
              "spo2             322\n",
              "tempc           3648\n",
              "cvp            29621\n",
              "urineoutput      991\n",
              "sodium          1111\n",
              "glucose         1106\n",
              "urea            1169\n",
              "creatinine      1149\n",
              "bilirubin      29069\n",
              "albumin        32143\n",
              "hb              1465\n",
              "wbc             1626\n",
              "crp            46891\n",
              "ph             16672\n",
              "po2            17704\n",
              "pco2           17704\n",
              "morta_hosp       386\n",
              "morta_90d        386\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I27tQpK8T1MC"
      },
      "source": [
        "# Save dataset locally for future sessions"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hUXXnnXhB2FU",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "outputId": "d4b0e24c-964d-4e01-daaa-45837dfcfde7"
      },
      "source": [
        "# download the dataset locally\n",
        "data.to_csv('data.csv')\n",
        "files.download('data.csv')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "application/javascript": [
              "download(\"download_8fd1c7a8-3e17-41ef-ad53-fa7a6a70318b\", \"data.csv\", 5545599)"
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BbCkFE0z8mGZ"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d44eZTI78mGy"
      },
      "source": [
        "# Data pre-processing\n",
        "\n",
        "This will be the purpose of a later session.\n",
        "\n",
        "Probably the most time consuming step in any data science project.\n",
        "Data pre-processing includes all the steps required to change newly extracted raw data into a representation that is suitable for building the downstream models.\n",
        "\n",
        "Recommended Python packages and ressources:\n",
        "- https://scikit-learn.org/stable/modules/preprocessing.html\n",
        "- https://www.analyticsvidhya.com/blog/2016/07/practical-guide-data-preprocessing-python-scikit-learn/"
      ]
    }
  ]
}