sql/initial_cohort_final_updated_los_bins.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "id": "-QkbKoDAaQen",
      "metadata": {
        "id": "-QkbKoDAaQen"
      },
      "source": [
        "# **COMP90089 Final Project**"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c066a893-df1d-4001-bf23-ddeea15b0335",
      "metadata": {
        "id": "c066a893-df1d-4001-bf23-ddeea15b0335"
      },
      "source": [
        "## **Selecting Final Cohort from MIMICIV**"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "Zjr71dP72MTj",
      "metadata": {
        "id": "Zjr71dP72MTj"
      },
      "source": [
        "### **Set up the environemnt**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "id": "aLjtUdgXCbU7",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aLjtUdgXCbU7",
        "outputId": "0300f8ec-367e-46fb-ae65-53815dbdfadd"
      },
      "outputs": [],
      "source": [
        "# from google.colab import drive\n",
        "\n",
        "# drive.mount('/content/drive')\n",
        "# path = '/content/drive/My Drive'"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "id": "_pHRTUFdVG9g",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_pHRTUFdVG9g",
        "outputId": "8085f13e-8e6f-4179-83ce-6392be0aac52"
      },
      "outputs": [],
      "source": [
        "# !pip install -U pandasql"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "id": "609ca55b-9fab-45c6-b997-58a8a847cfe8",
      "metadata": {
        "id": "609ca55b-9fab-45c6-b997-58a8a847cfe8"
      },
      "outputs": [],
      "source": [
        "#Set up the environement\n",
        "\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "import functools as ft\n",
        "from pandasql import sqldf\n",
        "%matplotlib inline\n",
        "\n",
        "\n",
        "#Project_ID\n",
        "project_id = \"composite-drive-359503\"\n",
        "\n",
        "# Read data from BigQuery into pandas dataframes.\n",
        "def run_query(query, project_id=project_id):\n",
        "    return pd.io.gbq.read_gbq(\n",
        "        query,\n",
        "        project_id=project_id,\n",
        "        dialect='standard')\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "wQndVsM4CtJw",
      "metadata": {
        "id": "wQndVsM4CtJw"
      },
      "source": [
        "### **SQL queries**"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "sKfQgEN8m3Hd",
      "metadata": {
        "id": "sKfQgEN8m3Hd"
      },
      "source": [
        "#### **1. Queries for finding the patients with criteria:**\n",
        " * n_stay >= 1\n",
        " * Age between 18 and 90\n",
        " * Date of Death is undefined "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "id": "62976a22-c329-43e9-b073-4f569962996e",
      "metadata": {
        "id": "62976a22-c329-43e9-b073-4f569962996e"
      },
      "outputs": [],
      "source": [
        "#Select patients from ICU_stays based on: n_stay >= 1 per hadm_id:\n",
        "\n",
        "#module: mimiciv-icu\n",
        "#table: icustays\n",
        "\n",
        "c1= f\"\"\"\n",
        "      SELECT\n",
        "        icustays.subject_id As subject_id,\n",
        "        icustays.hadm_id As hadm_id,\n",
        "        count(icustays.stay_id) As n_stays,\n",
        "        sum(icustays.los) As sum_los\n",
        "      FROM\n",
        "      `physionet-data.mimiciv_icu.icustays` AS icustays\n",
        "      GROUP BY\n",
        "        subject_id,\thadm_id\n",
        "      HAVING \n",
        "        n_stays >= 1\n",
        "      ORDER BY\n",
        "        subject_id,\thadm_id\n",
        "      \"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "id": "uci3pNaFJitA",
      "metadata": {
        "id": "uci3pNaFJitA"
      },
      "outputs": [],
      "source": [
        "#Select stay_ids for cohort in c1\n",
        "\n",
        "c2 = f\"\"\"\n",
        "    SELECT DISTINCT\n",
        "           c1.subject_id,\n",
        "           c1.hadm_id As hadm_id,\n",
        "           icustays.stay_id As stay_id, \n",
        "           c1.n_stays,\n",
        "           c1.sum_los As sum_los\n",
        "          FROM ({c1}) As c1\n",
        "          INNER JOIN `physionet-data.mimiciv_icu.icustays` AS icustays\n",
        "            ON c1.hadm_id = icustays.hadm_id\n",
        "\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "id": "c9o--yCZMnAx",
      "metadata": {
        "id": "c9o--yCZMnAx"
      },
      "outputs": [],
      "source": [
        "#Filter the cohort patients from c1 to include only patinets with age range (18 - 90) and survival status (dod is null):\n",
        "#module: mimiciv-icu\n",
        "#table: patients\n",
        "\n",
        "c3 = f\"\"\"\n",
        "     SELECT DISTINCT \n",
        "            c2.*,\n",
        "            patients.anchor_age As anchor_age,\n",
        "            patients.gender As gender\n",
        "            FROM ({c2}) As c2\n",
        "            INNER JOIN `physionet-data.mimiciv_hosp.patients` As patients\n",
        "            ON c2.subject_id = patients.subject_id AND\n",
        "               (patients.anchor_age BETWEEN 18 AND 90) AND\n",
        "               (patients.dod is null)\n",
        "               \n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "id": "QmEBkUf7oNuw",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QmEBkUf7oNuw",
        "outputId": "3505febc-2b53-4720-e10c-348b0884d10b"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/opt/homebrew/Caskroom/miniconda/base/envs/GBQ/lib/python3.10/site-packages/google/auth/_default.py:83: UserWarning: Your application has authenticated using end user credentials from Google Cloud SDK without a quota project. You might receive a \"quota exceeded\" or \"API not enabled\" error. We recommend you rerun `gcloud auth application-default login` and make sure a quota project is added. Or you can use service accounts instead. For more information about service accounts, see https://cloud.google.com/docs/authentication/\n",
            "  warnings.warn(_CLOUD_SDK_CREDENTIALS_WARNING)\n"
          ]
        }
      ],
      "source": [
        "cohort_c = run_query(f\"\"\"\n",
        "            SELECT c3.*\n",
        "            FROM ({c3}) As c3\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "id": "lfMVjdjURIjm",
      "metadata": {
        "id": "lfMVjdjURIjm"
      },
      "outputs": [],
      "source": [
        "# cohort_c.to_csv('/content/drive/MyDrive/cohort_criteria_data.csv', index=False)\n",
        "cohort_c.to_csv('cohort_criteria_data.csv', index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "id": "X70Ogd8kBPCo",
      "metadata": {
        "id": "X70Ogd8kBPCo"
      },
      "outputs": [],
      "source": [
        "#ICU_intime for all cohort stay_ids\n",
        "\n",
        "icu_intime = f\"\"\"\n",
        "      SELECT  c3.subject_id,\n",
        "              c3.stay_id,\n",
        "             icustays.intime As intime\n",
        "      FROM ({c3}) As c3\n",
        "      INNER JOIN `physionet-data.mimiciv_icu.icustays` AS icustays\n",
        "        ON c3.stay_id = icustays.stay_id\n",
        "      \"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "id": "j-oDikZdnzvp",
      "metadata": {
        "id": "j-oDikZdnzvp"
      },
      "outputs": [],
      "source": [
        "#Desired cohort subject_ids, hadm_id, and stay_ids \n",
        "cohort_subject_ids = list(cohort_c['subject_id'].unique())\n",
        "cohort_stay_ids = list(cohort_c['stay_id'].unique())\n",
        "cohort_hadm_ids = list(cohort_c['hadm_id'].unique())"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "lj7Fmrf1oDh6",
      "metadata": {
        "id": "lj7Fmrf1oDh6"
      },
      "source": [
        "#### **2. Queries for selecting demographic features for the cohort:** "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "id": "409agNoVbCPt",
      "metadata": {
        "id": "409agNoVbCPt"
      },
      "outputs": [],
      "source": [
        "##Demographic_1\n",
        "# Presence of infection\n",
        "\n",
        "#1. Find antibiotic amount, starttime, endtime for each patient within our cohort within the first 24hrs: \n",
        "\n",
        "antibiotic_q = f\"\"\"\n",
        "\n",
        "                 SELECT DISTINCT input_antibiotic.subject_id As subject_id,\n",
        "                 input_antibiotic.hadm_id As hadm_id,\n",
        "                 input_antibiotic.stay_id As stay_id,\n",
        "                 input_antibiotic.starttime As anti_starttime,\n",
        "                 input_antibiotic.endtime As anti_endtime,\n",
        "                 input_antibiotic.amount As antibiotic,\n",
        "                 icu_intime.intime As icu_intime\n",
        "                  FROM ({icu_intime}) As icu_intime\n",
        "                   INNER JOIN `physionet-data.mimiciv_icu.inputevents` As input_antibiotic\n",
        "                   ON icu_intime.stay_id = input_antibiotic.stay_id AND\n",
        "                       --input_antibiotic.starttime <= DATETIME_ADD(icu_intime.intime, INTERVAL '1' DAY) AND\n",
        "                        --input_antibiotic.endtime <= DATETIME_ADD(icu_intime.intime, INTERVAL '1' DAY) AND\n",
        "                      input_antibiotic.itemid = 225798   \n",
        "                    ORDER BY anti_endtime ASC                      \n",
        "\"\"\"\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "id": "AdKAW0BVXzT2",
      "metadata": {
        "id": "AdKAW0BVXzT2"
      },
      "outputs": [],
      "source": [
        "#ref code: https://github.com/MIT-LCP/mimic-iv/blob/master/concepts/sepsis/suspicion_of_infection.sql\n",
        "\n",
        "suspicion_infec_q = f\"\"\"\n",
        "\n",
        "WITH ab_tbl AS \n",
        "(\n",
        "  select\n",
        "      abx.subject_id, abx.hadm_id, abx.stay_id\n",
        "    , abx.antibiotic\n",
        "    , FIRST_VALUE(abx.anti_starttime) over (partition by stay_id order by abx.anti_starttime asc) AS antibiotic_time\n",
        "    -- date is used to match microbiology cultures with only date available\n",
        "    , DATETIME_TRUNC(FIRST_VALUE(abx.anti_starttime) over (partition by stay_id order by abx.anti_starttime asc), DAY) AS antibiotic_date\n",
        "    , FIRST_VALUE(abx.anti_endtime) over (partition by stay_id order by abx.anti_endtime DESC) AS anti_endtime\n",
        "    -- create a unique identifier for each patient antibiotic\n",
        "    , ROW_NUMBER() OVER\n",
        "    (\n",
        "      PARTITION BY subject_id\n",
        "      ORDER BY anti_starttime, anti_endtime, antibiotic\n",
        "    ) AS ab_id\n",
        "  from ({antibiotic_q}) As abx\n",
        ")\n",
        ", me as\n",
        "(\n",
        "  select micro_specimen_id\n",
        "    -- the following columns are identical for all rows of the same micro_specimen_id\n",
        "    -- these aggregates simply collapse duplicates down to 1 row\n",
        "    , MAX(subject_id) AS subject_id\n",
        "    , MAX(hadm_id) AS hadm_id\n",
        "    , CAST(MAX(chartdate) AS DATE) AS chartdate\n",
        "    , MAX(charttime) AS charttime\n",
        "    , MAX(spec_type_desc) AS spec_type_desc\n",
        "    , max(case when org_name is not null and org_name != '' then 1 else 0 end) as PositiveCulture\n",
        "  from `physionet-data.mimiciv_hosp.microbiologyevents` \n",
        "  group by micro_specimen_id\n",
        ")\n",
        ", me_then_ab AS\n",
        "(\n",
        "  select\n",
        "    ab_tbl.subject_id\n",
        "    , ab_tbl.hadm_id\n",
        "    , ab_tbl.stay_id\n",
        "    , ab_tbl.ab_id\n",
        "    \n",
        "    , me72.micro_specimen_id\n",
        "    , coalesce(me72.charttime, CAST(me72.chartdate AS DATETIME)) as last72_charttime\n",
        "    , me72.positiveculture as last72_positiveculture\n",
        "    , me72.spec_type_desc as last72_specimen\n",
        "\n",
        "    -- we will use this partition to select the earliest culture before this abx\n",
        "    -- this ensures each antibiotic is only matched to a single culture\n",
        "    -- and consequently we have 1 row per antibiotic\n",
        "    , ROW_NUMBER() OVER\n",
        "    (\n",
        "      PARTITION BY ab_tbl.subject_id, ab_tbl.ab_id\n",
        "      ORDER BY me72.chartdate, me72.charttime NULLS LAST\n",
        "    ) AS micro_seq\n",
        "  from ab_tbl\n",
        "  -- abx taken after culture, but no more than 72 hours after\n",
        "  LEFT JOIN me me72\n",
        "    on ab_tbl.subject_id = me72.subject_id\n",
        "    and\n",
        "    (\n",
        "      (\n",
        "      -- if charttime is available, use it\n",
        "          me72.charttime is not null\n",
        "      and ab_tbl.antibiotic_time > me72.charttime\n",
        "      and ab_tbl.antibiotic_time <= DATETIME_ADD(me72.charttime, INTERVAL 72 HOUR) \n",
        "      )\n",
        "      OR\n",
        "      (\n",
        "      -- if charttime is not available, use chartdate\n",
        "          me72.charttime is null\n",
        "      and antibiotic_date >= me72.chartdate\n",
        "      and antibiotic_date <= DATE_ADD(me72.chartdate, INTERVAL 3 DAY)\n",
        "      )\n",
        "    )\n",
        ")\n",
        ", ab_then_me AS\n",
        "(\n",
        "  select\n",
        "      ab_tbl.subject_id\n",
        "    , ab_tbl.hadm_id\n",
        "    , ab_tbl.stay_id\n",
        "    , ab_tbl.ab_id\n",
        "    \n",
        "    , me24.micro_specimen_id\n",
        "    , COALESCE(me24.charttime, CAST(me24.chartdate AS DATETIME)) as next24_charttime\n",
        "    , me24.positiveculture as next24_positiveculture\n",
        "    , me24.spec_type_desc as next24_specimen\n",
        "\n",
        "    -- we will use this partition to select the earliest culture before this abx\n",
        "    -- this ensures each antibiotic is only matched to a single culture\n",
        "    -- and consequently we have 1 row per antibiotic\n",
        "    , ROW_NUMBER() OVER\n",
        "    (\n",
        "      PARTITION BY ab_tbl.subject_id, ab_tbl.ab_id\n",
        "      ORDER BY me24.chartdate, me24.charttime NULLS LAST\n",
        "    ) AS micro_seq\n",
        "  from ab_tbl\n",
        "  -- culture in subsequent 24 hours\n",
        "  LEFT JOIN me me24\n",
        "    on ab_tbl.subject_id = me24.subject_id\n",
        "    and\n",
        "    (\n",
        "      (\n",
        "          -- if charttime is available, use it\n",
        "          me24.charttime is not null\n",
        "      and ab_tbl.antibiotic_time >= DATETIME_SUB(me24.charttime, INTERVAL 24 HOUR)  \n",
        "      and ab_tbl.antibiotic_time < me24.charttime\n",
        "      )\n",
        "      OR\n",
        "      (\n",
        "          -- if charttime is not available, use chartdate\n",
        "          me24.charttime is null\n",
        "      and ab_tbl.antibiotic_date >= DATE_SUB(me24.chartdate, INTERVAL 1 DAY)\n",
        "      and ab_tbl.antibiotic_date <= me24.chartdate\n",
        "      )\n",
        "    )\n",
        ")\n",
        "SELECT\n",
        "ab_tbl.subject_id\n",
        ", ab_tbl.stay_id\n",
        ", ab_tbl.hadm_id\n",
        ", ab_tbl.ab_id\n",
        ", ab_tbl.antibiotic\n",
        ", ab_tbl.antibiotic_time\n",
        "\n",
        ", CASE\n",
        "  WHEN last72_specimen IS NULL AND next24_specimen IS NULL\n",
        "    THEN 0\n",
        "  ELSE 1 \n",
        "  END AS suspected_infection\n",
        "-- time of suspected infection:\n",
        "--    (1) the culture time (if before antibiotic)\n",
        "--    (2) or the antibiotic time (if before culture)\n",
        ", CASE\n",
        "  WHEN last72_specimen IS NULL AND next24_specimen IS NULL\n",
        "    THEN NULL\n",
        "  ELSE COALESCE(last72_charttime, antibiotic_time)\n",
        "  END AS suspected_infection_time\n",
        "\n",
        ", COALESCE(last72_charttime, next24_charttime) AS culture_time\n",
        "\n",
        "-- the specimen that was cultured\n",
        ", COALESCE(last72_specimen, next24_specimen) AS specimen\n",
        "\n",
        "-- whether the cultured specimen ended up being positive or not\n",
        ", COALESCE(last72_positiveculture, next24_positiveculture) AS positive_culture\n",
        "\n",
        "FROM ab_tbl\n",
        "LEFT JOIN ab_then_me ab2me\n",
        "    ON ab_tbl.subject_id = ab2me.subject_id\n",
        "    AND ab_tbl.ab_id = ab2me.ab_id\n",
        "    AND ab2me.micro_seq = 1\n",
        "LEFT JOIN me_then_ab me2ab\n",
        "    ON ab_tbl.subject_id = me2ab.subject_id\n",
        "    AND ab_tbl.ab_id = me2ab.ab_id\n",
        "    AND me2ab.micro_seq = 1\n",
        "\"\"\"\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "id": "a9f8d71b-dc8c-4f8b-8dce-0f18025424d6",
      "metadata": {
        "id": "a9f8d71b-dc8c-4f8b-8dce-0f18025424d6"
      },
      "outputs": [],
      "source": [
        "#Demographic_1\n",
        "\n",
        "# --- Presence of infection (based on input antibiotic that has itemid = 225798)\n",
        "# module: mimiciv_icu, mimiciv_hosp\n",
        "# Tabels: inputevents, microbiologyevents\n",
        "# Presence of infection (suspected_infection = 1 or 0)\n",
        "\n",
        "\n",
        "demographic_1 = run_query(f\"\"\"\n",
        "    SELECT DISTINCT suspicion_infec_q.subject_id As subject_id,\n",
        "           suspicion_infec_q.hadm_id As hadm_id,\n",
        "           suspicion_infec_q.stay_id As stay_id,\n",
        "           suspicion_infec_q.suspected_infection As suspected_infection        \n",
        "    FROM ({suspicion_infec_q}) As suspicion_infec_q\n",
        "       WHERE suspicion_infec_q.stay_id IN UNNEST ({cohort_stay_ids})\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "id": "4c5V-wbOLxVO",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 0
        },
        "id": "4c5V-wbOLxVO",
        "outputId": "ccba0981-9fc8-4e9a-f9c3-bed9e2a85232"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>hadm_id</th>\n",
              "      <th>stay_id</th>\n",
              "      <th>suspected_infection</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>12868681</td>\n",
              "      <td>23575284</td>\n",
              "      <td>36024074</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>16892349</td>\n",
              "      <td>26582202</td>\n",
              "      <td>30066630</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10815169</td>\n",
              "      <td>20980090</td>\n",
              "      <td>33706843</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>13568806</td>\n",
              "      <td>20385179</td>\n",
              "      <td>34873303</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>17601342</td>\n",
              "      <td>25265986</td>\n",
              "      <td>36558958</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14685</th>\n",
              "      <td>11811472</td>\n",
              "      <td>24446294</td>\n",
              "      <td>36093186</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14686</th>\n",
              "      <td>13102401</td>\n",
              "      <td>22251975</td>\n",
              "      <td>31004936</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14687</th>\n",
              "      <td>13848552</td>\n",
              "      <td>22445188</td>\n",
              "      <td>32839832</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14688</th>\n",
              "      <td>17907596</td>\n",
              "      <td>21801758</td>\n",
              "      <td>34260029</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14689</th>\n",
              "      <td>18250208</td>\n",
              "      <td>21824251</td>\n",
              "      <td>33747782</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>14690 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "       subject_id   hadm_id   stay_id  suspected_infection\n",
              "0        12868681  23575284  36024074                    1\n",
              "1        16892349  26582202  30066630                    1\n",
              "2        10815169  20980090  33706843                    1\n",
              "3        13568806  20385179  34873303                    1\n",
              "4        17601342  25265986  36558958                    1\n",
              "...           ...       ...       ...                  ...\n",
              "14685    11811472  24446294  36093186                    1\n",
              "14686    13102401  22251975  31004936                    1\n",
              "14687    13848552  22445188  32839832                    1\n",
              "14688    17907596  21801758  34260029                    1\n",
              "14689    18250208  21824251  33747782                    1\n",
              "\n",
              "[14690 rows x 4 columns]"
            ]
          },
          "execution_count": 14,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "demographic_1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "id": "GP2HIE5Dc0aM",
      "metadata": {
        "id": "GP2HIE5Dc0aM"
      },
      "outputs": [],
      "source": [
        "# demographic_1.to_csv('/content/drive/MyDrive/cohort_demographic_1.csv',index=False)\n",
        "demographic_1.to_csv('cohort_demographic_1.csv',index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "id": "afae0157-775a-4fc4-8890-0874c65aac12",
      "metadata": {
        "id": "afae0157-775a-4fc4-8890-0874c65aac12"
      },
      "outputs": [],
      "source": [
        "#Demographic_2\n",
        "# Module: mimiciv_hosp\n",
        "# Table: admissions\n",
        "# Type of admission (admission_type)\n",
        "\n",
        "#Note: Some patients have more than one admission type\n",
        "\n",
        "demographic_2 = run_query(f\"\"\"\n",
        "    SELECT  admission.subject_id As subject_id,\n",
        "            admission.hadm_id As hadm_id,\n",
        "            admission.admission_type As admission_type\n",
        "    FROM `physionet-data.mimiciv_hosp.admissions` As admission    \n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "id": "OeNENZCtfDrr",
      "metadata": {
        "id": "OeNENZCtfDrr"
      },
      "outputs": [],
      "source": [
        "# demographic_2.to_csv('/content/drive/MyDrive/cohort_demographic_2.csv',index=False)\n",
        "demographic_2.to_csv('cohort_demographic_2.csv',index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "id": "rRok6TgjxOnt",
      "metadata": {
        "id": "rRok6TgjxOnt"
      },
      "outputs": [],
      "source": [
        "##Demographic_3 item_id = 226512 \n",
        "\n",
        "# Module: mimiciv_icu\n",
        "# Table: chartevents\n",
        "# Admission Weight (Kg)  (--Avergae weight)\n",
        "\n",
        "demographic_3 = run_query(f\"\"\" SELECT\n",
        "   ie.stay_id,\n",
        "   AVG(ce.valuenum) AS weight\n",
        "    FROM ({icu_intime}) ie\n",
        "    -- admission weight\n",
        "    LEFT JOIN`physionet-data.mimiciv_icu.chartevents` As ce\n",
        "      ON ie.stay_id = ce.stay_id AND itemid IN (226512, 224639)\n",
        "      AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY ie.stay_id\n",
        "    \"\"\")\n",
        "   "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "id": "xwe989pdfUct",
      "metadata": {
        "id": "xwe989pdfUct"
      },
      "outputs": [],
      "source": [
        "# demographic_3.to_csv('/content/drive/MyDrive/cohort_demographic_3.csv',index=False)\n",
        "demographic_3.to_csv('cohort_demographic_3.csv',index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "id": "nJoqYgKzvYZH",
      "metadata": {
        "id": "nJoqYgKzvYZH"
      },
      "outputs": [],
      "source": [
        "##Demographic_4 item_id = 226730 \n",
        "\n",
        "# Module: mimiciv_icu\n",
        "# Table: chartevents\n",
        "# Height (cm)\n",
        "demographic_4 = run_query(f\"\"\" SELECT\n",
        "      \n",
        "      ie.stay_id,\n",
        "      AVG(valuenum) as Height_chart\n",
        "    FROM ({icu_intime}) ie\n",
        "    -- admission height\n",
        "    LEFT JOIN`physionet-data.mimiciv_icu.chartevents` As ce\n",
        "      ON ie.stay_id = ce.stay_id AND itemid = 226730 \n",
        "      AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY) AND \n",
        "      ce.valuenum != 0\n",
        "    GROUP BY ie.stay_id \n",
        "    \"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "id": "v538rNXbg-AD",
      "metadata": {
        "id": "v538rNXbg-AD"
      },
      "outputs": [],
      "source": [
        "# demographic_4.to_csv('/content/drive/MyDrive/cohort_demographic_4.csv',index=False)\n",
        "demographic_4.to_csv('cohort_demographic_4.csv',index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "snQVTd41olMr",
      "metadata": {
        "id": "snQVTd41olMr"
      },
      "source": [
        "#### **3. Queries for selecting vitalsigns features for the cohort:** "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "id": "3e3f6203-74b1-4288-bf61-1522b47081f5",
      "metadata": {
        "id": "3e3f6203-74b1-4288-bf61-1522b47081f5"
      },
      "outputs": [],
      "source": [
        "# Vital signs\n",
        "\n",
        "# module: mimiciv_icu\n",
        "# Tabel: chartevents\n",
        "\n",
        "# heart_rate : itemid = 220045\n",
        "# Temperature Fahrenheit: itemid = 223761\n",
        "#Arterial Blood Pressure mean (ABPm): itemid = 220052\n",
        "#Arterial Blood Pressure diastolic (ABPd): itemid = 220051\n",
        "#Arterial Blood Pressure systolic (ABPs): itemid = 220050\n",
        "#Non Invasive Blood Pressure mean (NBPm): itemid = 220181\n",
        "#Non Invasive Blood Pressure diastolic (NBPd): itemid = 220180\n",
        "#Non Invasive Blood Pressure systolic (NBPs): itemid = 220179\n",
        "#Respiratory Rate (RR): itemid = (220210, 224690)\n",
        "#SpO2: itemid =  (220277, 226253)\n",
        "\n",
        "\n",
        "\n",
        "    # AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "    # AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "\n",
        "vitalsigns = f\"\"\"\n",
        "    WITH vitalsigns As (\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS heart_rate_min,\n",
        "           MAX(ce.valuenum) AS heart_rate_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid = 220045\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "       temperature As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS temperature_min,\n",
        "           MAX(ce.valuenum) AS temperature_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid = 223761\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "         ABPm As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS ABPm_min,\n",
        "           MAX(ce.valuenum) AS ABPm_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid = 220052\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     ),    \n",
        "           ABPd As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS ABPd_min,\n",
        "           MAX(ce.valuenum) AS ABPd_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid = 220051\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     ),\n",
        "           ABPs As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS ABPs_min,\n",
        "           MAX(ce.valuenum) AS ABPs_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid =  220050\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     ),       \n",
        "          NBPm As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS NBPm_min,\n",
        "           MAX(ce.valuenum) AS NBPm_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid =  220181\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     ),\n",
        "          NBPd As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS NBPd_min,\n",
        "           MAX(ce.valuenum) AS NBPd_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid =  220180\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     ),\n",
        "        NBPs As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS NBPs_min,\n",
        "           MAX(ce.valuenum) AS NBPs_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid = 220179\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     ),\n",
        "           RR As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS RR_min,\n",
        "           MAX(ce.valuenum) AS RR_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid = 220210\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     ),\n",
        "           SpO2 As (\n",
        "           SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(ce.valuenum) AS SpO2_min,\n",
        "           MAX(ce.valuenum) AS SpO2_max\n",
        "     FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "          ON ie.stay_id = ce.stay_id \n",
        "          AND  ce.itemid IN (220277, 226253)\n",
        "          AND ce.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND ce.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "     )  \n",
        "\n",
        "   SELECT ie.subject_id,\n",
        "          ie.stay_id, \n",
        "          vitalsigns.heart_rate_min,\n",
        "          vitalsigns.heart_rate_max,\n",
        "          temperature.temperature_min,\n",
        "          temperature.temperature_max,\n",
        "          ABPm.ABPm_min,\n",
        "          ABPm.ABPm_max,\n",
        "          ABPd.ABPd_min,\n",
        "          ABPd.ABPd_max,\n",
        "          ABPs.ABPs_min,\n",
        "          ABPs.ABPs_max,\n",
        "          NBPm.NBPm_min,\n",
        "          NBPm.NBPm_max,\n",
        "          NBPd.NBPd_min,\n",
        "          NBPd.NBPd_max,\n",
        "          NBPs.NBPs_min,\n",
        "          NBPs.NBPs_max,\n",
        "          RR.RR_min,\n",
        "          RR.RR_max,\n",
        "          SpO2.SpO2_min,\n",
        "          SpO2.SpO2_max\n",
        "\n",
        "          \n",
        "   FROM ({icu_intime}) As ie\n",
        "   LEFT JOIN vitalsigns \n",
        "   ON ie.stay_id = vitalsigns.stay_id\n",
        "   LEFT JOIN temperature\n",
        "   ON vitalsigns.stay_id = temperature.stay_id\n",
        "   LEFT JOIN ABPm\n",
        "   ON temperature.stay_id = ABPm.stay_id\n",
        "   LEFT JOIN ABPd\n",
        "   ON  ABPm.stay_id = ABPd.stay_id\n",
        "   LEFT JOIN ABPs\n",
        "   ON ABPd.stay_id = ABPs.stay_id \n",
        "   LEFT JOIN NBPm\n",
        "   ON ABPs.stay_id = NBPm.stay_id \n",
        "   LEFT JOIN NBPd\n",
        "   ON NBPm.stay_id = NBPd.stay_id\n",
        "   LEFT JOIN NBPs\n",
        "   ON NBPd.stay_id = NBPs.stay_id\n",
        "   LEFT JOIN RR\n",
        "   ON NBPs.stay_id = RR.stay_id\n",
        "   LEFT JOIN SpO2\n",
        "   ON RR.stay_id = SpO2.stay_id\n",
        "\"\"\"\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "id": "7CtOw1JAwX_x",
      "metadata": {
        "id": "7CtOw1JAwX_x"
      },
      "outputs": [],
      "source": [
        "cohort_vitalsigns = run_query(f\"\"\"\n",
        "  SELECT vitalsigns.*\n",
        "    FROM ({vitalsigns}) As vitalsigns\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "id": "1qdObxVFbNOj",
      "metadata": {
        "id": "1qdObxVFbNOj"
      },
      "outputs": [],
      "source": [
        "# cohort_vitalsigns.to_csv('/content/drive/MyDrive/cohort_vitalsigns.csv',index=False)\n",
        "cohort_vitalsigns.to_csv('cohort_vitalsigns.csv',index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "SjnVs9rFoteZ",
      "metadata": {
        "id": "SjnVs9rFoteZ"
      },
      "source": [
        "#### **4. Queries for selecting lab tests features for the cohort:** "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "id": "d779ce00-44c0-4dbf-af19-4a6d81b6f106",
      "metadata": {
        "id": "d779ce00-44c0-4dbf-af19-4a6d81b6f106"
      },
      "outputs": [],
      "source": [
        "\n",
        "# Laboratory tests 1\n",
        "# module: mimiciv_hosp\n",
        "# Tabel: labevents\n",
        "\n",
        "\n",
        "# Albumin(albumin_min, albumin_max) item_id = 52022, 50862, 53085, 53138\n",
        "# Blood urea nitrogen (bun_min, bun_max) itemid = (51006, 52647)\n",
        "# Calcium_blood (calcium_min, calcium_max) itemid = 50893\n",
        "\n",
        "# creatinine_blood (creatinine_min, creatinine_max) itemid = 50912, 52546\n",
        "# Glucose (glucose_min, glucose_max) itemid = 50931, 52569\n",
        "# Bicarbonate (bicarbonate_min, bicarbonate_max) itemid = 50882\n",
        "\n",
        "# Potassium (potassium_min, potassium_max) itemid = 50971, 52610\n",
        "# Sodium (sodium_min, sodium_max) itemid = (50983, 52623)\n",
        "# Platelet Aggregation (platelets_min, platelets_max) itemid = 51265\n",
        "\n",
        "# Bilirubin (bilirubin_total_min, bilirubin_total_max) item_id = 50885, 53089\n",
        "# White blood cell count (wbc_min, wbc_max) item_id (51301)\n",
        "# Lactate Dehydrogenase itemid = 50813, 52442, 50954\n",
        "\n",
        "# Magnesium (Mg) itemid = 50960\n",
        "# pH (ph_min, ph_max) itemid (50820, 50831)\n",
        "# Partial pressure of carbon dioxide (pCO2_min, pCO2_max) itemid = (50818, 50830)\n",
        "# Partial pressure of Oxygen (pO2_min, pO2_max) itemid = (50821, 50832)\n",
        "\n",
        "#Not Found!\n",
        "# PaO2/FiO2 ratio (pao2fio2ratio_min, pao2fio2ratio_max)  \n",
        "\n",
        "lab_tests = f\"\"\"\n",
        "    WITH labtests As (\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS albumin_min,\n",
        "           MAX(le.valuenum) AS albumin_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id \n",
        "          AND  le.itemid IN (52022, 50862, 53085,53138)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "       platelet  As (\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS platelet_min,\n",
        "           MAX(le.valuenum) AS platelet_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id \n",
        "          AND  le.itemid = 51265\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "       bun As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS bun_min,\n",
        "           MAX(le.valuenum) AS bun_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (51006, 52647)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       calcium As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS calcium_min,\n",
        "           MAX(le.valuenum) AS calcium_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id \n",
        "          AND  le.itemid = 50893\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       creatinine As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS creatinine_min,\n",
        "           MAX(le.valuenum) AS creatinine_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (50912, 52546)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       glucose As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  glucose_min,\n",
        "           MAX(le.valuenum) AS glucose_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (50931, 52569)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       bicarbonate As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  bicarbonate_min,\n",
        "           MAX(le.valuenum) AS bicarbonate_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid = 50882\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "        potassium As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS   potassium_min,\n",
        "           MAX(le.valuenum) AS  potassium_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (50971, 52610)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       sodium As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  sodium_min,\n",
        "           MAX(le.valuenum) AS  sodium_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (50983, 52623)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       bilirubin As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  bilirubin_min,\n",
        "           MAX(le.valuenum) AS  bilirubin_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN  (50885, 50883, 50884, 51568, 51569, 51570, 53089)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "      wbc As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  wbc_min,\n",
        "           MAX(le.valuenum) AS  wbc_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid = 51301\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       lactate As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  lactate_min,\n",
        "           MAX(le.valuenum) AS  lactate_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id \n",
        "          AND  le.itemid IN (50813, 52442, 50954)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       magnesium As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  magnesium_min,\n",
        "           MAX(le.valuenum) AS  magnesium_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid = 50960\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       pH As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  pH_min,\n",
        "           MAX(le.valuenum) AS  pH_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (50820, 50831)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       pCO2 As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  pCO2_min,\n",
        "           MAX(le.valuenum) AS  pCO2_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (50818, 50830)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       ),\n",
        "       pO2 As (\n",
        "         SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           MIN(le.valuenum) AS  pO2_min,\n",
        "           MAX(le.valuenum) AS  pO2_max\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_hosp.labevents` As le\n",
        "          ON ie.subject_id = le.subject_id\n",
        "          AND  le.itemid IN (50821, 50832)\n",
        "          AND le.charttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND le.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "       )\n",
        "\n",
        "       SELECT ie.subject_id,\n",
        "              ie.stay_id,\n",
        "              labtests.albumin_min,\n",
        "              labtests.albumin_max,\n",
        "              platelet.platelet_min,\n",
        "              platelet.platelet_max,\n",
        "              bun.bun_min,\n",
        "              bun.bun_max,\n",
        "              calcium.calcium_min,\n",
        "              calcium.calcium_max,\n",
        "              creatinine.creatinine_min,\n",
        "              creatinine.creatinine_max,\n",
        "              glucose.glucose_min,\n",
        "              glucose.glucose_max,\n",
        "              bicarbonate.bicarbonate_min,\n",
        "              bicarbonate.bicarbonate_max,\n",
        "              potassium.potassium_min,\n",
        "              potassium.potassium_max,\n",
        "              sodium.sodium_min,\n",
        "              sodium.sodium_max,\n",
        "              bilirubin.bilirubin_min,\n",
        "              bilirubin.bilirubin_max,\n",
        "              wbc.wbc_min,\n",
        "              wbc.wbc_max,\n",
        "              lactate.lactate_min,\n",
        "              lactate.lactate_max,\n",
        "              magnesium.magnesium_min,\n",
        "              magnesium.magnesium_max,\n",
        "              pH.pH_min,\n",
        "              pH.pH_max,\n",
        "              pCO2.pCO2_min,\n",
        "              pCO2.pCO2_max,\n",
        "              pO2.pO2_min,\n",
        "              pO2.pO2_max,\n",
        "            FROM ({icu_intime}) As ie\n",
        "            LEFT JOIN labtests\n",
        "            ON ie.stay_id = labtests.stay_id\n",
        "            LEFT JOIN platelet\n",
        "            ON labtests.stay_id = platelet.stay_id\n",
        "            LEFT JOIN bun \n",
        "            ON platelet.stay_id = bun.stay_id\n",
        "            LEFT JOIN calcium\n",
        "            ON bun.stay_id = calcium.stay_id\n",
        "            LEFT JOIN creatinine\n",
        "            ON calcium.stay_id = creatinine.stay_id\n",
        "            LEFT JOIN glucose\n",
        "            ON creatinine.stay_id = glucose.stay_id\n",
        "            LEFT JOIN bicarbonate\n",
        "            ON glucose.stay_id = bicarbonate.stay_id \n",
        "            LEFT JOIN potassium\n",
        "            ON bicarbonate.stay_id = potassium.stay_id \n",
        "            LEFT JOIN sodium\n",
        "            ON potassium.stay_id = sodium.stay_id\n",
        "            LEFT JOIN bilirubin\n",
        "            ON sodium.stay_id  = bilirubin.stay_id\n",
        "            LEFT JOIN wbc\n",
        "            ON bilirubin.stay_id = wbc.stay_id \n",
        "            LEFT JOIN lactate\n",
        "            ON wbc.stay_id = lactate.stay_id\n",
        "            LEFT JOIN magnesium\n",
        "            ON lactate.stay_id = magnesium.stay_id\n",
        "            LEFT JOIN pH\n",
        "            ON magnesium.stay_id = pH.stay_id \n",
        "            LEFT JOIN pCO2\n",
        "            ON pH.stay_id = pCO2.stay_id \n",
        "            LEFT JOIN pO2\n",
        "            ON pCO2.stay_id = pO2.stay_id \n",
        "   \"\"\"       \n",
        "  \n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "id": "jrdohja7viJn",
      "metadata": {
        "id": "jrdohja7viJn"
      },
      "outputs": [],
      "source": [
        "cohort_lab_tests = run_query(f\"\"\"\n",
        " SELECT lab_tests.*\n",
        "   FROM ({lab_tests}) As lab_tests\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "id": "gMm28j6VZd9s",
      "metadata": {
        "id": "gMm28j6VZd9s"
      },
      "outputs": [],
      "source": [
        "# cohort_lab_tests.to_csv('/content/drive/MyDrive/cohort_lab_tests.csv',index=False)\n",
        "cohort_lab_tests.to_csv('cohort_lab_tests.csv',index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "UNM6XhP9o3ZT",
      "metadata": {
        "id": "UNM6XhP9o3ZT"
      },
      "source": [
        "#### **5. Queries for selecting comorbidities scores features for the cohort:** "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "id": "K42p0b0SgU1S",
      "metadata": {
        "id": "K42p0b0SgU1S"
      },
      "outputs": [],
      "source": [
        "#Comorbidities 1\n",
        "\n",
        "# module: mimiciv_icu\n",
        "# Table: chartevents\n",
        "\n",
        "#reference code: https://github.com/MIT-LCP/mimic-iv/blob/master/concepts/firstday/first_day_gcs.sql\n",
        "\n",
        "gcs = f\"\"\"\n",
        "    with base as\n",
        "    (\n",
        "    select \n",
        "      ie.subject_id\n",
        "    , ie.stay_id, ce.charttime\n",
        "    -- pivot each value into its own column\n",
        "    , max(case when ce.ITEMID = 223901 then ce.valuenum else null end) as GCSMotor\n",
        "    , max(case\n",
        "        when ce.ITEMID = 223900 and ce.VALUE = 'No Response-ETT' then 0\n",
        "        when ce.ITEMID = 223900 then ce.valuenum\n",
        "        else null \n",
        "      end) as GCSVerbal\n",
        "    , max(case when ce.ITEMID = 220739 then ce.valuenum else null end) as GCSEyes\n",
        "    -- convert the data into a number, reserving a value of 0 for ET/Trach\n",
        "    , max(case\n",
        "        -- endotrach/vent is assigned a value of 0\n",
        "        -- flag it here to later parse specially\n",
        "        when ce.ITEMID = 223900 and ce.VALUE = 'No Response-ETT' then 1 -- metavision\n",
        "      else 0 end)\n",
        "      as endotrachflag\n",
        "    , ROW_NUMBER ()\n",
        "            OVER (PARTITION BY ce.stay_id ORDER BY ce.charttime ASC) as rn\n",
        "    FROM ({icu_intime}) As ie\n",
        "    LEFT JOIN `physionet-data.mimiciv_icu.chartevents` As ce\n",
        "        ON ce.stay_id = ie.stay_id AND \n",
        "           ce.ITEMID IN (223900, 223901, 220739)\n",
        "    group by ie.subject_id, ie.stay_id, ce.stay_id, ce.charttime\n",
        "    )\n",
        "    , gcs as (\n",
        "    select b.*\n",
        "    , b2.GCSVerbal as GCSVerbalPrev\n",
        "    , b2.GCSMotor as GCSMotorPrev\n",
        "    , b2.GCSEyes as GCSEyesPrev\n",
        "    -- Calculate GCS, factoring in special case when they are intubated and prev vals\n",
        "    -- note that the coalesce are used to implement the following if:\n",
        "    --  if current value exists, use it\n",
        "    --  if previous value exists, use it\n",
        "    --  otherwise, default to normal\n",
        "    , case\n",
        "        -- replace GCS during sedation with 15\n",
        "        when b.GCSVerbal = 0\n",
        "          then 15\n",
        "        when b.GCSVerbal is null and b2.GCSVerbal = 0\n",
        "          then 15\n",
        "        -- if previously they were intub, but they aren't now, do not use previous GCS values\n",
        "        when b2.GCSVerbal = 0\n",
        "          then\n",
        "              coalesce(b.GCSMotor,6)\n",
        "            + coalesce(b.GCSVerbal,5)\n",
        "            + coalesce(b.GCSEyes,4)\n",
        "        -- otherwise, add up score normally, imputing previous value if none available at current time\n",
        "        else\n",
        "              coalesce(b.GCSMotor,coalesce(b2.GCSMotor,6))\n",
        "            + coalesce(b.GCSVerbal,coalesce(b2.GCSVerbal,5))\n",
        "            + coalesce(b.GCSEyes,coalesce(b2.GCSEyes,4))\n",
        "        end as GCS\n",
        "\n",
        "    from base b\n",
        "    -- join to itself within 6 hours to get previous value\n",
        "    left join base b2\n",
        "      on b.stay_id = b2.stay_id\n",
        "      and b.rn = b2.rn+1\n",
        "      and b2.charttime > DATETIME_ADD(b.charttime, INTERVAL 6 HOUR)\n",
        "    )\n",
        "    -- combine components with previous within 6 hours\n",
        "    -- filter down to cohort which is not excluded\n",
        "    -- truncate charttime to the hour\n",
        "    , gcs_stg as\n",
        "    (\n",
        "    select\n",
        "      subject_id\n",
        "    , gs.stay_id, gs.charttime\n",
        "    , GCS\n",
        "    , coalesce(GCSMotor,GCSMotorPrev) as GCSMotor\n",
        "    , coalesce(GCSVerbal,GCSVerbalPrev) as GCSVerbal\n",
        "    , coalesce(GCSEyes,GCSEyesPrev) as GCSEyes\n",
        "    , case when coalesce(GCSMotor,GCSMotorPrev) is null then 0 else 1 end\n",
        "    + case when coalesce(GCSVerbal,GCSVerbalPrev) is null then 0 else 1 end\n",
        "    + case when coalesce(GCSEyes,GCSEyesPrev) is null then 0 else 1 end\n",
        "      as components_measured\n",
        "    , EndoTrachFlag\n",
        "    from gcs gs\n",
        "    )\n",
        "    -- priority is:\n",
        "    --  (i) complete data, (ii) non-sedated GCS, (iii) lowest GCS, (iv) charttime\n",
        "    , gcs_priority as\n",
        "    (\n",
        "    select \n",
        "        subject_id\n",
        "      , stay_id\n",
        "      , charttime\n",
        "      , gcs\n",
        "      , gcsmotor\n",
        "      , gcsverbal\n",
        "      , gcseyes\n",
        "      , EndoTrachFlag\n",
        "      , ROW_NUMBER() over\n",
        "        (\n",
        "          PARTITION BY stay_id, charttime\n",
        "          ORDER BY components_measured ASC, endotrachflag, gcs, charttime ASC\n",
        "        ) as rn\n",
        "    from gcs_stg\n",
        "    )\n",
        "    select DISTINCT\n",
        "    gs.subject_id\n",
        "    , gs.stay_id\n",
        "    , min(GCS) AS gcs_min\n",
        "    from gcs_priority gs\n",
        "    where rn = 1\n",
        "    GROUP BY gs.subject_id, gs.stay_id\n",
        "    \n",
        "      \"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "id": "3YwMplv1w5lA",
      "metadata": {
        "id": "3YwMplv1w5lA"
      },
      "outputs": [],
      "source": [
        "cohort_gcs_first_day = run_query(f\"\"\"\n",
        "   SELECT gcs.*\n",
        "   FROM ({gcs}) As gcs   \n",
        "   \"\"\"\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 30,
      "id": "0D0VCIXvLp2I",
      "metadata": {
        "id": "0D0VCIXvLp2I"
      },
      "outputs": [],
      "source": [
        "# cohort_gcs_first_day.to_csv('/content/drive/MyDrive/cohort_gcs_first_day.csv',index=False)\n",
        "cohort_gcs_first_day.to_csv('cohort_gcs_first_day.csv',index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 31,
      "id": "AnTpPcOZJztd",
      "metadata": {
        "id": "AnTpPcOZJztd"
      },
      "outputs": [],
      "source": [
        "#Comorbidities 2\n",
        "\n",
        "# module: mimiciv_icu_derived\n",
        "# Table: first_day_sofa\n",
        "\n",
        "cohort_sofa_first_day = run_query(f\"\"\"\n",
        "    SELECT icu_intime.stay_id As stay_id,\n",
        "           sofa.SOFA As SOFA\n",
        "    FROM ({icu_intime}) AS icu_intime\n",
        "    INNER JOIN `physionet-data.mimiciv_derived.first_day_sofa` As sofa\n",
        "    ON icu_intime.stay_id = sofa.stay_id     \n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 32,
      "id": "xGiWR7HBmLRG",
      "metadata": {
        "id": "xGiWR7HBmLRG"
      },
      "outputs": [],
      "source": [
        "# cohort_sofa_first_day.to_csv('/content/drive/MyDrive/cohort_sofa_first_day.csv',index=False)\n",
        "cohort_sofa_first_day.to_csv('cohort_sofa_first_day.csv',index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "mlCeXEESpKeV",
      "metadata": {
        "id": "mlCeXEESpKeV"
      },
      "source": [
        "#### **6. Query for selecting ventilation status feature for the cohort:** "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 33,
      "id": "gPcTyIw4LI7l",
      "metadata": {
        "id": "gPcTyIw4LI7l"
      },
      "outputs": [],
      "source": [
        "#module: mimiciv_icu\n",
        "#table: chartevents\n",
        "#reference code: https://github.com/MIT-LCP/mimic-iv/blob/master/concepts/measurement/ventilator_setting.sql\n",
        "\n",
        "ventilator_setting = f\"\"\" \n",
        "with ce as\n",
        "(\n",
        "  SELECT\n",
        "      ce.subject_id\n",
        "    , ce.stay_id\n",
        "    , ce.charttime\n",
        "    , itemid\n",
        "    -- TODO: clean\n",
        "    , value\n",
        "    , case\n",
        "        -- begin fio2 cleaning\n",
        "        when itemid = 223835\n",
        "        then\n",
        "            case\n",
        "                when valuenum >= 0.20 and valuenum <= 1\n",
        "                    then valuenum * 100\n",
        "                -- improperly input data - looks like O2 flow in litres\n",
        "                when valuenum > 1 and valuenum < 20\n",
        "                    then null\n",
        "                when valuenum >= 20 and valuenum <= 100\n",
        "                    then valuenum\n",
        "            ELSE NULL END\n",
        "        -- end of fio2 cleaning\n",
        "        -- begin peep cleaning\n",
        "        WHEN itemid in (220339, 224700)\n",
        "        THEN\n",
        "          CASE\n",
        "            WHEN valuenum > 100 THEN NULL\n",
        "            WHEN valuenum < 0 THEN NULL\n",
        "          ELSE valuenum END\n",
        "        -- end peep cleaning\n",
        "    ELSE valuenum END AS valuenum\n",
        "    , valueuom\n",
        "    , storetime\n",
        "  FROM `physionet-data.mimiciv_icu.chartevents` ce\n",
        "  where ce.value IS NOT NULL\n",
        "  AND ce.stay_id IS NOT NULL\n",
        "  AND ce.itemid IN\n",
        "  (\n",
        "      224688 -- Respiratory Rate (Set)\n",
        "    , 224689 -- Respiratory Rate (spontaneous)\n",
        "    , 224690 -- Respiratory Rate (Total)\n",
        "    , 224687 -- minute volume\n",
        "    , 224685, 224684, 224686 -- tidal volume\n",
        "    , 224696 -- PlateauPressure\n",
        "    , 220339, 224700 -- PEEP\n",
        "    , 223835 -- fio2\n",
        "    , 223849 -- vent mode\n",
        "    , 229314 -- vent mode (Hamilton)\n",
        "    , 223848 -- vent type\n",
        "  )\n",
        ")\n",
        "SELECT\n",
        "      subject_id\n",
        "    , MAX(stay_id) AS stay_id\n",
        "    , charttime\n",
        "    , MAX(CASE WHEN itemid = 224688 THEN valuenum ELSE NULL END) AS respiratory_rate_set\n",
        "    , MAX(CASE WHEN itemid = 224690 THEN valuenum ELSE NULL END) AS respiratory_rate_total\n",
        "    , MAX(CASE WHEN itemid = 224689 THEN valuenum ELSE NULL END) AS respiratory_rate_spontaneous\n",
        "    , MAX(CASE WHEN itemid = 224687 THEN valuenum ELSE NULL END) AS minute_volume\n",
        "    , MAX(CASE WHEN itemid = 224684 THEN valuenum ELSE NULL END) AS tidal_volume_set\n",
        "    , MAX(CASE WHEN itemid = 224685 THEN valuenum ELSE NULL END) AS tidal_volume_observed\n",
        "    , MAX(CASE WHEN itemid = 224686 THEN valuenum ELSE NULL END) AS tidal_volume_spontaneous\n",
        "    , MAX(CASE WHEN itemid = 224696 THEN valuenum ELSE NULL END) AS plateau_pressure\n",
        "    , MAX(CASE WHEN itemid in (220339, 224700) THEN valuenum ELSE NULL END) AS peep\n",
        "    , MAX(CASE WHEN itemid = 223835 THEN valuenum ELSE NULL END) AS fio2\n",
        "    , MAX(CASE WHEN itemid = 223849 THEN value ELSE NULL END) AS ventilator_mode\n",
        "    , MAX(CASE WHEN itemid = 229314 THEN value ELSE NULL END) AS ventilator_mode_hamilton\n",
        "    , MAX(CASE WHEN itemid = 223848 THEN value ELSE NULL END) AS ventilator_type\n",
        "FROM ce\n",
        "GROUP BY subject_id, charttime\n",
        "\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "id": "ifYitrj0MoPo",
      "metadata": {
        "id": "ifYitrj0MoPo"
      },
      "outputs": [],
      "source": [
        "#module: mimiciv_icu\n",
        "#table: chartevents\n",
        "\n",
        "#reference code: https://github.com/MIT-LCP/mimic-iv/blob/master/concepts/measurement/oxygen_delivery.sql\n",
        "oxygen_delivery = f\"\"\"\n",
        "with ce_stg1 as\n",
        "(\n",
        "  SELECT\n",
        "      ce.subject_id\n",
        "    , ce.stay_id\n",
        "    , ce.charttime\n",
        "    , CASE\n",
        "        -- merge o2 flows into a single row\n",
        "        WHEN itemid IN (223834, 227582, 224691) THEN 223834\n",
        "      ELSE itemid END AS itemid\n",
        "    , value\n",
        "    , valuenum\n",
        "    , valueuom\n",
        "    , storetime\n",
        "  FROM `physionet-data.mimiciv_icu.chartevents` ce\n",
        "  WHERE ce.value IS NOT NULL\n",
        "  AND ce.itemid IN\n",
        "  (\n",
        "      223834 -- o2 flow\n",
        "    , 227582 -- bipap o2 flow\n",
        "    , 224691 -- Flow Rate (L)\n",
        "    -- additional o2 flow is its own column\n",
        "    , 227287 -- additional o2 flow\n",
        "  )\n",
        ")\n",
        ", ce_stg2 AS\n",
        "(\n",
        "  select\n",
        "    ce.subject_id\n",
        "    , ce.stay_id\n",
        "    , ce.charttime\n",
        "    , itemid\n",
        "    , value\n",
        "    , valuenum\n",
        "    , valueuom\n",
        "    -- retain only 1 row per charttime\n",
        "    -- prioritizing the last documented value\n",
        "    -- primarily used to subselect o2 flows\n",
        "    , ROW_NUMBER() OVER (PARTITION BY subject_id, charttime, itemid ORDER BY storetime DESC) as rn\n",
        "  FROM ce_stg1 ce\n",
        ")\n",
        ", o2 AS\n",
        "(\n",
        "    -- The below ITEMID can have multiple entires for charttime/storetime\n",
        "    -- These are totally valid entries, and should be retained in derived tables.\n",
        "    --   224181 -- Small Volume Neb Drug #1              | Respiratory             | Text       | chartevents\n",
        "    -- , 227570 -- Small Volume Neb Drug/Dose #1         | Respiratory             | Text       | chartevents\n",
        "    -- , 224833 -- SBT Deferred                          | Respiratory             | Text       | chartevents\n",
        "    -- , 224716 -- SBT Stopped                           | Respiratory             | Text       | chartevents\n",
        "    -- , 224740 -- RSBI Deferred                         | Respiratory             | Text       | chartevents\n",
        "    -- , 224829 -- Trach Tube Type                       | Respiratory             | Text       | chartevents\n",
        "    -- , 226732 -- O2 Delivery Device(s)                 | Respiratory             | Text       | chartevents\n",
        "    -- , 226873 -- Inspiratory Ratio                     | Respiratory             | Numeric    | chartevents\n",
        "    -- , 226871 -- Expiratory Ratio                      | Respiratory             | Numeric    | chartevents\n",
        "    -- maximum of 4 o2 devices on at once\n",
        "    SELECT\n",
        "        subject_id\n",
        "        , stay_id\n",
        "        , charttime\n",
        "        , itemid\n",
        "        , value AS o2_device\n",
        "    , ROW_NUMBER() OVER (PARTITION BY subject_id, charttime, itemid ORDER BY value) as rn\n",
        "    FROM `physionet-data.mimiciv_icu.chartevents`\n",
        "    WHERE itemid = 226732 -- oxygen delivery device(s)\n",
        ")\n",
        ", stg AS\n",
        "(\n",
        "    select\n",
        "      COALESCE(ce.subject_id, o2.subject_id) AS subject_id\n",
        "    , COALESCE(ce.stay_id, o2.stay_id) AS stay_id\n",
        "    , COALESCE(ce.charttime, o2.charttime) AS charttime\n",
        "    , COALESCE(ce.itemid, o2.itemid) AS itemid\n",
        "    , ce.value\n",
        "    , ce.valuenum\n",
        "    , o2.o2_device\n",
        "    , o2.rn\n",
        "    from ce_stg2 ce\n",
        "    FULL OUTER JOIN o2\n",
        "      ON ce.subject_id = o2.subject_id\n",
        "      AND ce.charttime = o2.charttime\n",
        "    -- limit to 1 row per subject_id/charttime/itemid from ce_stg2\n",
        "    WHERE ce.rn = 1\n",
        ")\n",
        "SELECT\n",
        "    subject_id\n",
        "    , MAX(stay_id) AS stay_id\n",
        "    , charttime\n",
        "    , MAX(CASE WHEN itemid = 223834 THEN valuenum ELSE NULL END) AS o2_flow\n",
        "    , MAX(CASE WHEN itemid = 227287 THEN valuenum ELSE NULL END) AS o2_flow_additional\n",
        "    -- ensure we retain all o2 devices for the patient\n",
        "    , MAX(CASE WHEN rn = 1 THEN o2_device ELSE NULL END) AS o2_delivery_device_1\n",
        "    , MAX(CASE WHEN rn = 2 THEN o2_device ELSE NULL END) AS o2_delivery_device_2\n",
        "    , MAX(CASE WHEN rn = 3 THEN o2_device ELSE NULL END) AS o2_delivery_device_3\n",
        "    , MAX(CASE WHEN rn = 4 THEN o2_device ELSE NULL END) AS o2_delivery_device_4\n",
        "FROM stg\n",
        "GROUP BY subject_id, charttime\n",
        "\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 35,
      "id": "5d35a9e8-269f-4aee-8aba-fce19f001d94",
      "metadata": {
        "id": "5d35a9e8-269f-4aee-8aba-fce19f001d94"
      },
      "outputs": [],
      "source": [
        "# module: mimiciv_icu\n",
        "# Table: procedureevents\n",
        "# Ventilation status (ventilation_status) \n",
        "\n",
        "##reference code: https://github.com/MIT-LCP/mimic-iv/blob/master/concepts/treatment/ventilation.sql\n",
        "\n",
        "ventilation_status = f\"\"\"\n",
        "      WITH tm AS\n",
        "      (\n",
        "      SELECT stay_id, charttime\n",
        "      FROM ({ventilator_setting})\n",
        "      UNION DISTINCT\n",
        "      SELECT stay_id, charttime\n",
        "      FROM ({oxygen_delivery})\n",
        "      )\n",
        "      , vs AS\n",
        "      (\n",
        "        SELECT tm.stay_id, tm.charttime\n",
        "        -- source data columns, here for debug\n",
        "        , o2_delivery_device_1\n",
        "        , COALESCE(ventilator_mode, ventilator_mode_hamilton) AS vent_mode\n",
        "        -- case statement determining the type of intervention\n",
        "        -- done in order of priority: trach > mech vent > NIV > high flow > o2\n",
        "        , CASE\n",
        "        -- tracheostomy\n",
        "        WHEN o2_delivery_device_1 IN\n",
        "        (\n",
        "            'Tracheostomy tube'\n",
        "        -- 'Trach mask ' -- 16435 observations\n",
        "        )\n",
        "            THEN 'Trach'\n",
        "        -- mechanical ventilation\n",
        "        WHEN o2_delivery_device_1 IN\n",
        "        (\n",
        "            'Endotracheal tube'\n",
        "        )\n",
        "        OR ventilator_mode IN\n",
        "        (\n",
        "            '(S) CMV',\n",
        "            'APRV',\n",
        "            'APRV/Biphasic+ApnPress',\n",
        "            'APRV/Biphasic+ApnVol',\n",
        "            'APV (cmv)',\n",
        "            'Ambient',\n",
        "            'Apnea Ventilation',\n",
        "            'CMV',\n",
        "            'CMV/ASSIST',\n",
        "            'CMV/ASSIST/AutoFlow',\n",
        "            'CMV/AutoFlow',\n",
        "            'CPAP/PPS',\n",
        "            'CPAP/PSV+Apn TCPL',\n",
        "            'CPAP/PSV+ApnPres',\n",
        "            'CPAP/PSV+ApnVol',\n",
        "            'MMV',\n",
        "            'MMV/AutoFlow',\n",
        "            'MMV/PSV',\n",
        "            'MMV/PSV/AutoFlow',\n",
        "            'P-CMV',\n",
        "            'PCV+',\n",
        "            'PCV+/PSV',\n",
        "            'PCV+Assist',\n",
        "            'PRES/AC',\n",
        "            'PRVC/AC',\n",
        "            'PRVC/SIMV',\n",
        "            'PSV/SBT',\n",
        "            'SIMV',\n",
        "            'SIMV/AutoFlow',\n",
        "            'SIMV/PRES',\n",
        "            'SIMV/PSV',\n",
        "            'SIMV/PSV/AutoFlow',\n",
        "            'SIMV/VOL',\n",
        "            'SYNCHRON MASTER',\n",
        "            'SYNCHRON SLAVE',\n",
        "            'VOL/AC'\n",
        "        )\n",
        "        OR ventilator_mode_hamilton IN\n",
        "        (\n",
        "            'APRV',\n",
        "            'APV (cmv)',\n",
        "            'Ambient',\n",
        "            '(S) CMV',\n",
        "            'P-CMV',\n",
        "            'SIMV',\n",
        "            'APV (simv)',\n",
        "            'P-SIMV',\n",
        "            'VS',\n",
        "            'ASV'\n",
        "        )\n",
        "            THEN 'InvasiveVent'\n",
        "        -- NIV\n",
        "        WHEN o2_delivery_device_1 IN\n",
        "        (\n",
        "            'Bipap mask ', -- 8997 observations\n",
        "            'CPAP mask ' -- 5568 observations\n",
        "        )\n",
        "        OR ventilator_mode_hamilton IN\n",
        "        (\n",
        "            'DuoPaP',\n",
        "            'NIV',\n",
        "            'NIV-ST'\n",
        "        )\n",
        "            THEN 'NonInvasiveVent'\n",
        "        -- high flow\n",
        "        when o2_delivery_device_1 IN\n",
        "        (\n",
        "            'High flow neb', -- 10785 observations\n",
        "            'High flow nasal cannula' -- 925 observations\n",
        "        )\n",
        "            THEN 'HighFlow'\n",
        "        -- normal oxygen delivery\n",
        "        WHEN o2_delivery_device_1 in\n",
        "        (\n",
        "            'Nasal cannula', -- 153714 observations\n",
        "            'Face tent', -- 24601 observations\n",
        "            'Aerosol-cool', -- 24560 observations\n",
        "            'Non-rebreather', -- 5182 observations\n",
        "            'Venti mask ', -- 1947 observations\n",
        "            'Medium conc mask ', -- 1888 observations\n",
        "            'T-piece', -- 1135 observations\n",
        "            'Ultrasonic neb', -- 9 observations\n",
        "            'Vapomist', -- 3 observations\n",
        "            'Oxymizer' -- 1301 observations\n",
        "        )\n",
        "            THEN 'Oxygen'\n",
        "        -- Not categorized:\n",
        "        -- 'Other', 'None'\n",
        "        ELSE NULL END AS ventilation_status\n",
        "      FROM tm\n",
        "      LEFT JOIN ({ventilator_setting}) vs\n",
        "          ON tm.stay_id = vs.stay_id\n",
        "          AND tm.charttime = vs.charttime\n",
        "      LEFT JOIN ({oxygen_delivery}) od\n",
        "          ON tm.stay_id = od.stay_id\n",
        "          AND tm.charttime = od.charttime\n",
        "      )\n",
        "      , vd0 AS\n",
        "      (\n",
        "        SELECT\n",
        "          stay_id, charttime\n",
        "          -- source data columns, here for debug\n",
        "          , o2_delivery_device_1\n",
        "          , vent_mode\n",
        "          -- carry over the previous charttime which had the same state\n",
        "          , LAG(charttime, 1) OVER (PARTITION BY stay_id, ventilation_status ORDER BY charttime) AS charttime_lag\n",
        "          -- bring back the next charttime, regardless of the state\n",
        "          -- this will be used as the end time for state transitions\n",
        "          , LEAD(charttime, 1) OVER w AS charttime_lead\n",
        "          , ventilation_status\n",
        "          , LAG(ventilation_status, 1) OVER w AS ventilation_status_lag\n",
        "        FROM vs\n",
        "        WHERE ventilation_status IS NOT NULL\n",
        "        WINDOW w AS (PARTITION BY stay_id ORDER BY charttime)\n",
        "      )\n",
        "      , vd1 as\n",
        "      (\n",
        "        SELECT\n",
        "            stay_id\n",
        "            -- source data columns, here for debug\n",
        "            , o2_delivery_device_1\n",
        "            , vent_mode\n",
        "            , charttime_lag\n",
        "            , charttime\n",
        "            , charttime_lead\n",
        "            , ventilation_status\n",
        "\n",
        "            -- calculate the time since the last event\n",
        "            , DATETIME_DIFF(charttime, charttime_lag, MINUTE)/60 as ventduration\n",
        "\n",
        "            -- now we determine if the current ventilation status is \"new\", or continuing the previous\n",
        "            , CASE\n",
        "                -- a 14 hour gap always initiates a new event\n",
        "                WHEN DATETIME_DIFF(charttime, charttime_lag, HOUR) >= 14 THEN 1\n",
        "                WHEN ventilation_status_lag IS NULL THEN 1\n",
        "                -- not a new event if identical to the last row\n",
        "                WHEN ventilation_status_lag != ventilation_status THEN 1\n",
        "              ELSE 0\n",
        "              END AS new_status\n",
        "        FROM vd0\n",
        "      )\n",
        "      , vd2 as\n",
        "      (\n",
        "        SELECT vd1.*\n",
        "        -- create a cumulative sum of the instances of new ventilation\n",
        "        -- this results in a monotonic integer assigned to each instance of ventilation\n",
        "        , SUM(new_status) OVER (PARTITION BY stay_id ORDER BY charttime) AS vent_num\n",
        "        FROM vd1\n",
        "      )\n",
        "      -- create the durations for each ventilation instance\n",
        "      SELECT stay_id\n",
        "      , MIN(charttime) AS starttime\n",
        "      -- for the end time of the ventilation event, the time of the *next* setting\n",
        "      -- i.e. if we go NIV -> O2, the end time of NIV is the first row with a documented O2 device\n",
        "      -- ... unless it's been over 14 hours, in which case it's the last row with a documented NIV.\n",
        "      , MAX(\n",
        "            CASE\n",
        "                WHEN charttime_lead IS NULL\n",
        "                OR DATETIME_DIFF(charttime_lead, charttime, HOUR) >= 14\n",
        "                    THEN charttime\n",
        "            ELSE charttime_lead\n",
        "            END\n",
        "        ) AS endtime\n",
        "        -- all rows with the same vent_num will have the same ventilation_status\n",
        "        -- for efficiency, we use an aggregate here, but we could equally well group by this column\n",
        "      , MAX(ventilation_status) AS ventilation_status\n",
        "      FROM vd2\n",
        "      GROUP BY stay_id, vent_num\n",
        "      HAVING min(charttime) != max(charttime)\n",
        "      \n",
        "    \"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "id": "sd6PgqUzE2l-",
      "metadata": {
        "id": "sd6PgqUzE2l-"
      },
      "outputs": [],
      "source": [
        "\n",
        "cohort_ventilation_status = run_query(f\"\"\"\n",
        "     SELECT DISTINCT icu_intime.stay_id,\n",
        "            ventilation.ventilation_status,\n",
        "     FROM ({icu_intime}) As icu_intime\n",
        "     INNER JOIN ({ventilation_status }) As ventilation\n",
        "        ON icu_intime.stay_id = ventilation.stay_id AND\n",
        "        ventilation.starttime <= DATETIME_ADD(icu_intime.intime, INTERVAL '1' DAY) \n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 37,
      "id": "vnygjwyHi5i3",
      "metadata": {
        "id": "vnygjwyHi5i3"
      },
      "outputs": [],
      "source": [
        "# cohort_ventilation_status.to_csv('/content/drive/MyDrive/cohort_ventilation_status.csv',index=False)\n",
        "cohort_ventilation_status.to_csv('cohort_ventilation_status.csv',index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "kthiS0eJpXlX",
      "metadata": {
        "id": "kthiS0eJpXlX"
      },
      "source": [
        "#### **7. Queries for selecting Input/Output feature for the cohort:** "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 38,
      "id": "3581883f-73c2-4d92-a549-65fc78d8620a",
      "metadata": {
        "id": "3581883f-73c2-4d92-a549-65fc78d8620a"
      },
      "outputs": [],
      "source": [
        "# Input/output: \n",
        "\n",
        "#module: mimiciv_icu\n",
        "#table: outputevents\n",
        "#reference code:https://github.com/MIT-LCP/mimic-iv/blob/master/concepts/measurement/urine_output.sql\n",
        "# Urine output (urineoutput)\n",
        "\n",
        "urine_output = f\"\"\"\n",
        "        select\n",
        "      stay_id\n",
        "      , charttime\n",
        "      , sum(urineoutput) as urineoutput\n",
        "      from\n",
        "      (\n",
        "        select\n",
        "        -- patient identifiers\n",
        "        oe.stay_id\n",
        "        , oe.charttime\n",
        "        -- volumes associated with urine output ITEMIDs\n",
        "        -- note we consider input of GU irrigant as a negative volume\n",
        "        -- GU irrigant volume in usually has a corresponding volume out\n",
        "        -- so the net is often 0, despite large irrigant volumes\n",
        "        , case\n",
        "            when oe.itemid = 227488 and oe.value > 0 then -1*oe.value\n",
        "            else oe.value\n",
        "        end as urineoutput\n",
        "        from `physionet-data.mimiciv_icu.outputevents` oe\n",
        "        where itemid in\n",
        "        (\n",
        "        226559, -- Foley\n",
        "        226560, -- Void\n",
        "        226561, -- Condom Cath\n",
        "        226584, -- Ileoconduit\n",
        "        226563, -- Suprapubic\n",
        "        226564, -- R Nephrostomy\n",
        "        226565, -- L Nephrostomy\n",
        "        226567, -- Straight Cath\n",
        "        226557, -- R Ureteral Stent\n",
        "        226558, -- L Ureteral Stent\n",
        "        227488, -- GU Irrigant Volume In\n",
        "        227489  -- GU Irrigant/Urine Volume Out\n",
        "        )\n",
        "      ) uo\n",
        "      group by stay_id, charttime\n",
        "      \n",
        "\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 39,
      "id": "koOtw4CjWZLF",
      "metadata": {
        "id": "koOtw4CjWZLF"
      },
      "outputs": [],
      "source": [
        "cohort_urine_output = run_query(f\"\"\"\n",
        "      SELECT\n",
        "      ie.subject_id\n",
        "      , ie.stay_id\n",
        "      , AVG(urineoutput) AS urineoutput\n",
        "      FROM ({icu_intime}) ie\n",
        "      -- Join to the outputevents table to get urine output\n",
        "      LEFT JOIN ({urine_output}) uo\n",
        "        ON ie.stay_id = uo.stay_id\n",
        "        -- ensure the data occurs during the first day\n",
        "        AND uo.charttime >= ie.intime\n",
        "        AND uo.charttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "      GROUP BY ie.subject_id, ie.stay_id\n",
        "    \"\"\"\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 40,
      "id": "8OKBcSUklLnT",
      "metadata": {
        "id": "8OKBcSUklLnT"
      },
      "outputs": [],
      "source": [
        "# cohort_urine_output.to_csv('/content/drive/MyDrive/cohort_urine_output.csv',index=False)\n",
        "cohort_urine_output.to_csv('cohort_urine_output.csv',index=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 41,
      "id": "65fHXhMDM7KF",
      "metadata": {
        "id": "65fHXhMDM7KF"
      },
      "outputs": [],
      "source": [
        "# Input/output: \n",
        "\n",
        "#vasopressin_drugs\n",
        "#module: mimiciv_icu\n",
        "#table: outputevents\n",
        "\n",
        "#item_ids: dopamine: 221662, \n",
        "# Epinephrine: 221289, 229617, \n",
        "# Norepinephrine: 221906,\n",
        "# Phenylephrine: 221749, 229630, 229631, 229632,\n",
        "# Vasopressin: 222315,\n",
        "# Dobutamine: 221653,\n",
        "# milrinone: 221986\n",
        "\n",
        "\n",
        "vasopressin_med = f\"\"\"\n",
        "    WITH vasopressin_drugs As (\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           AVG(vaso.amount) AS dopamine_amount,\n",
        "           AVG(vaso.rate) AS dopamine_rate\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.inputevents`  As vaso\n",
        "          ON ie.subject_id = vaso.subject_id \n",
        "          AND vaso.itemid = 221662\n",
        "          AND vaso.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND vaso.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "     epinephrine As (\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           AVG(vaso.amount) AS epinephrine_amount,\n",
        "           AVG(vaso.rate) AS epinephrine_rate\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.inputevents`  As vaso\n",
        "          ON ie.subject_id = vaso.subject_id \n",
        "          AND  vaso.itemid IN (21289, 229617)\n",
        "          AND vaso.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND vaso.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "    norepinephrine As (\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           AVG(vaso.amount) AS norepinephrine_amount,\n",
        "           AVG(vaso.rate) AS norepinephrine_rate\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.inputevents`  As vaso\n",
        "          ON ie.subject_id = vaso.subject_id \n",
        "          AND  vaso.itemid =  221906\n",
        "          AND vaso.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND vaso.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "    phenylephrine As (\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           AVG(vaso.amount) AS phenylephrine_amount,\n",
        "           AVG(vaso.rate) AS phenylephrine_rate\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.inputevents`  As vaso\n",
        "          ON ie.subject_id = vaso.subject_id \n",
        "          AND  vaso.itemid IN (221749, 229630, 229631, 229632)\n",
        "          AND vaso.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND vaso.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "    vasopressin As(\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           AVG(vaso.amount) AS vasopressin_amount,\n",
        "           AVG(vaso.rate) AS vasopressin_rate\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.inputevents`  As vaso\n",
        "          ON ie.subject_id = vaso.subject_id \n",
        "          AND  vaso.itemid = 222315\n",
        "          AND vaso.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND vaso.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "    dobutamine As(\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           AVG(vaso.amount) AS dobutamine_amount,\n",
        "           AVG(vaso.rate) AS dobutamine_rate\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.inputevents`  As vaso\n",
        "          ON ie.subject_id = vaso.subject_id \n",
        "          AND  vaso.itemid = 221653\n",
        "          AND vaso.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND vaso.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    ),\n",
        "       milrinone As(\n",
        "       SELECT ie.subject_id,\n",
        "           ie.stay_id,\n",
        "           AVG(vaso.amount) AS milrinone_amount,\n",
        "           AVG(vaso.rate) AS milrinone_rate\n",
        "       FROM ({icu_intime}) As ie\n",
        "       LEFT JOIN `physionet-data.mimiciv_icu.inputevents`  As vaso\n",
        "          ON ie.subject_id = vaso.subject_id \n",
        "          AND  vaso.itemid = 221986\n",
        "          AND vaso.starttime >= DATETIME_SUB(ie.intime, INTERVAL '6' HOUR)\n",
        "          AND vaso.starttime <= DATETIME_ADD(ie.intime, INTERVAL '1' DAY)\n",
        "    GROUP BY  ie.subject_id, ie.stay_id \n",
        "    )\n",
        "   SELECT vasopressin_drugs.subject_id,\n",
        "          vasopressin_drugs.stay_id,\n",
        "          vasopressin_drugs.dopamine_amount,\n",
        "          vasopressin_drugs.dopamine_rate,\n",
        "          epinephrine.epinephrine_amount,\n",
        "          epinephrine.epinephrine_rate,\n",
        "          norepinephrine.norepinephrine_amount,\n",
        "          norepinephrine.norepinephrine_rate,\n",
        "          phenylephrine.phenylephrine_amount,\n",
        "          phenylephrine.phenylephrine_rate,\n",
        "          vasopressin.vasopressin_amount,\n",
        "          vasopressin.vasopressin_rate,\n",
        "          dobutamine.dobutamine_amount,\n",
        "          dobutamine.dobutamine_rate,\n",
        "          milrinone.milrinone_amount,\n",
        "          milrinone.milrinone_rate\n",
        "   FROM vasopressin_drugs\n",
        "   LEFT JOIN epinephrine\n",
        "   ON vasopressin_drugs.stay_id = epinephrine.stay_id\n",
        "   LEFT JOIN norepinephrine\n",
        "   ON epinephrine.stay_id = norepinephrine.stay_id\n",
        "   LEFT JOIN phenylephrine\n",
        "   ON norepinephrine.stay_id = phenylephrine.stay_id\n",
        "   LEFT JOIN vasopressin\n",
        "   ON phenylephrine.stay_id = vasopressin.stay_id\n",
        "   LEFT JOIN dobutamine\n",
        "   ON vasopressin.stay_id = dobutamine.stay_id \n",
        "   LEFT JOIN milrinone\n",
        "   ON dobutamine.stay_id  = milrinone.stay_id\n",
        "\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "id": "NXvNouxRzuWg",
      "metadata": {
        "id": "NXvNouxRzuWg"
      },
      "outputs": [],
      "source": [
        "cohort_vasopressin = run_query(f\"\"\"\n",
        "     SELECT vasopressin_med.*\n",
        "     FROM ({vasopressin_med}) vasopressin_med\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 43,
      "id": "ygmoeW_Oti3z",
      "metadata": {
        "id": "ygmoeW_Oti3z"
      },
      "outputs": [],
      "source": [
        "# cohort_vasopressin.to_csv('/content/drive/MyDrive/cohort_vasopressin.csv',index=False)\n",
        "cohort_vasopressin.to_csv('cohort_vasopressin.csv',index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "KL_OS0XgBMC5",
      "metadata": {
        "id": "KL_OS0XgBMC5"
      },
      "source": [
        "### **Join all result tables**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "id": "rCMA4WiQ4kWT",
      "metadata": {
        "id": "rCMA4WiQ4kWT"
      },
      "outputs": [],
      "source": [
        "#Join tables: \n",
        "\n",
        "cohort_criteria = pd.read_csv('cohort_criteria_data.csv')\n",
        "cohort_demographic_1 = pd.read_csv('cohort_demographic_1.csv')\n",
        "cohort_demographic_2 = pd.read_csv('cohort_demographic_2.csv')\n",
        "cohort_demographic_3 = pd.read_csv('cohort_demographic_3.csv')\n",
        "cohort_demographic_4 = pd.read_csv('cohort_demographic_4.csv')\n",
        "cohort_vitalsigns = pd.read_csv('cohort_vitalsigns.csv')\n",
        "cohort_lab_tests = pd.read_csv('cohort_lab_tests.csv')\n",
        "cohort_comorbidities_gcs = pd.read_csv('cohort_gcs_first_day.csv')\n",
        "cohort_comorbidities_sofa = pd.read_csv('cohort_sofa_first_day.csv')\n",
        "cohort_ventilation_status = pd.read_csv('cohort_ventilation_status.csv')\n",
        "cohort_urine_output = pd.read_csv('cohort_urine_output.csv')\n",
        "cohort_vasopressin =pd.read_csv('cohort_vasopressin.csv')\n",
        "# cohort_criteria = pd.read_csv('/content/drive/MyDrive/cohort_criteria_data.csv')\n",
        "# cohort_demographic_1 = pd.read_csv('/content/drive/MyDrive/cohort_demographic_1.csv')\n",
        "# cohort_demographic_2 = pd.read_csv('/content/drive/MyDrive/cohort_demographic_2.csv')\n",
        "# cohort_demographic_3 = pd.read_csv('/content/drive/MyDrive/cohort_demographic_3.csv')\n",
        "# cohort_demographic_4 = pd.read_csv('/content/drive/MyDrive/cohort_demographic_4.csv')\n",
        "# cohort_vitalsigns = pd.read_csv('/content/drive/MyDrive/cohort_vitalsigns.csv')\n",
        "# cohort_lab_tests = pd.read_csv('/content/drive/MyDrive/cohort_lab_tests.csv')\n",
        "# cohort_comorbidities_gcs = pd.read_csv('/content/drive/MyDrive/cohort_gcs_first_day.csv')\n",
        "# cohort_comorbidities_sofa = pd.read_csv('/content/drive/MyDrive/cohort_sofa_first_day.csv')\n",
        "# cohort_ventilation_status = pd.read_csv('/content/drive/MyDrive/cohort_ventilation_status.csv')\n",
        "# cohort_urine_output = pd.read_csv('/content/drive/MyDrive/cohort_urine_output.csv')\n",
        "# cohort_vasopressin =pd.read_csv('/content/drive/MyDrive/cohort_vasopressin.csv')\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 45,
      "id": "IVBCqdwK5aYP",
      "metadata": {
        "id": "IVBCqdwK5aYP"
      },
      "outputs": [],
      "source": [
        "#1. Join demographic_1:\n",
        "query1 = f\"\"\"\n",
        "SELECT DISTINCT cohort_criteria.*,\n",
        "       cohort_demographic_1.* \n",
        "FROM cohort_criteria  As cohort_criteria\n",
        "LEFT JOIN cohort_demographic_1 AS cohort_demographic_1\n",
        "   ON cohort_demographic_1.stay_id = cohort_criteria.stay_id \n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 46,
      "id": "wb0A2c8e5z94",
      "metadata": {
        "id": "wb0A2c8e5z94"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "demographic_1_joined  = sqldf(query1, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 47,
      "id": "YGrZN_57563a",
      "metadata": {
        "id": "YGrZN_57563a"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "demographic_1_joined = demographic_1_joined.loc[:,~demographic_1_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 48,
      "id": "WD40b-6i6fmk",
      "metadata": {
        "id": "WD40b-6i6fmk"
      },
      "outputs": [],
      "source": [
        "#2. Join demographic_2:\n",
        "query2 = f\"\"\"\n",
        "SELECT DISTINCT demographic_1_joined.*,\n",
        "       cohort_demographic_2.*\n",
        "FROM demographic_1_joined  As demographic_1_joined\n",
        "LEFT JOIN cohort_demographic_2 AS cohort_demographic_2\n",
        "ON demographic_1_joined.hadm_id = cohort_demographic_2.hadm_id\n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 49,
      "id": "VXHpzhUW6ysf",
      "metadata": {
        "id": "VXHpzhUW6ysf"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "demographic_2_joined  = sqldf(query2, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 50,
      "id": "nWkn-gyF65Xi",
      "metadata": {
        "id": "nWkn-gyF65Xi"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "demographic_2_joined = demographic_2_joined.loc[:,~demographic_2_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 51,
      "id": "aPSRIfj4atmL",
      "metadata": {
        "id": "aPSRIfj4atmL"
      },
      "outputs": [],
      "source": [
        "#3. Join lab tests \n",
        "query3 = f\"\"\"\n",
        "SELECT DISTINCT demographic_2_joined.*,\n",
        "      cohort_demographic_3.* \n",
        "FROM demographic_2_joined  As demographic_2_joined\n",
        "LEFT JOIN cohort_demographic_3 AS cohort_demographic_3\n",
        "ON demographic_2_joined.stay_id = cohort_demographic_3.stay_id\n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 52,
      "id": "R-bh5tfLa9yK",
      "metadata": {
        "id": "R-bh5tfLa9yK"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "demographic_3_joined = sqldf(query3, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 53,
      "id": "1i-CM64-bAkJ",
      "metadata": {
        "id": "1i-CM64-bAkJ"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "demographic_3_joined = demographic_3_joined.loc[:,~demographic_3_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 54,
      "id": "aT7DuryT7FM1",
      "metadata": {
        "id": "aT7DuryT7FM1"
      },
      "outputs": [],
      "source": [
        "#4. Join comorbidities scores \n",
        "\n",
        "query4 = f\"\"\"\n",
        "SELECT DISTINCT demographic_3_joined.*,\n",
        "      cohort_demographic_4.*\n",
        "FROM demographic_3_joined  As demographic_3_joined\n",
        "LEFT JOIN cohort_demographic_4 AS cohort_demographic_4\n",
        "ON demographic_3_joined.stay_id = cohort_demographic_4.stay_id\n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 55,
      "id": "bEIh8i1_7p14",
      "metadata": {
        "id": "bEIh8i1_7p14"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "demographic_4_joined  = sqldf(query4, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 56,
      "id": "CxUZmKeB7zh1",
      "metadata": {
        "id": "CxUZmKeB7zh1"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "demographic_4_joined = demographic_4_joined.loc[:,~demographic_4_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 57,
      "id": "sQT7sp1h3RvP",
      "metadata": {
        "id": "sQT7sp1h3RvP"
      },
      "outputs": [],
      "source": [
        "#5. Join vital signs \n",
        "\n",
        "query5 = f\"\"\"\n",
        "SELECT DISTINCT demographic_4_joined.*,\n",
        "     cohort_vitalsigns.*\n",
        "FROM demographic_4_joined  As demographic_4_joined\n",
        "LEFT JOIN cohort_vitalsigns AS cohort_vitalsigns \n",
        "ON demographic_4_joined.stay_id = cohort_vitalsigns .stay_id\n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 58,
      "id": "8QXQsQTS3n0o",
      "metadata": {
        "id": "8QXQsQTS3n0o"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "vitalsigns_joined  = sqldf(query5, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 59,
      "id": "Nx7aqvOC3v78",
      "metadata": {
        "id": "Nx7aqvOC3v78"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "vitalsigns_joined = vitalsigns_joined.loc[:,~vitalsigns_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 60,
      "id": "CGIZu56E4d2R",
      "metadata": {
        "id": "CGIZu56E4d2R"
      },
      "outputs": [],
      "source": [
        "#6. Join lab tests 1 \n",
        "\n",
        "query6 = f\"\"\"\n",
        "SELECT DISTINCT vitalsigns_joined.*,\n",
        "     cohort_lab_tests.*\n",
        "FROM vitalsigns_joined As vitalsigns_joined\n",
        "LEFT JOIN cohort_lab_tests AS cohort_lab_tests\n",
        "ON vitalsigns_joined.stay_id = cohort_lab_tests.stay_id\n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 61,
      "id": "KF27y5UD5e2S",
      "metadata": {
        "id": "KF27y5UD5e2S"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "lab_test_joined  = sqldf(query6, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 62,
      "id": "Yms7mpIq5m3g",
      "metadata": {
        "id": "Yms7mpIq5m3g"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "lab_test_joined = lab_test_joined.loc[:,~lab_test_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 63,
      "id": "rRiGdD475-Xc",
      "metadata": {
        "id": "rRiGdD475-Xc"
      },
      "outputs": [],
      "source": [
        "#7. Join lab tests 2\n",
        "\n",
        "query7 = f\"\"\"\n",
        "SELECT DISTINCT lab_test_joined.*,\n",
        "     cohort_comorbidities_gcs.*\n",
        "FROM lab_test_joined As lab_test_joined\n",
        "LEFT JOIN cohort_comorbidities_gcs AS cohort_comorbidities_gcs\n",
        "ON lab_test_joined.stay_id = cohort_comorbidities_gcs.stay_id\n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 64,
      "id": "D2kP9-ic6hV0",
      "metadata": {
        "id": "D2kP9-ic6hV0"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "cohort_gcs_joined  = sqldf(query7, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 65,
      "id": "Q2T2LCzW6oko",
      "metadata": {
        "id": "Q2T2LCzW6oko"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "cohort_gcs_joined  = cohort_gcs_joined.loc[:,~cohort_gcs_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 66,
      "id": "mG3oKYNk7TGT",
      "metadata": {
        "id": "mG3oKYNk7TGT"
      },
      "outputs": [],
      "source": [
        "#8. Join comorbiditiy score 1 \n",
        "\n",
        "query8 = f\"\"\"\n",
        "SELECT DISTINCT cohort_gcs_joined.*,\n",
        "     cohort_comorbidities_sofa.*\n",
        "FROM cohort_gcs_joined As cohort_gcs_joined\n",
        "LEFT JOIN cohort_comorbidities_sofa AS cohort_comorbidities_sofa\n",
        "ON cohort_gcs_joined.stay_id = cohort_comorbidities_sofa.stay_id\n",
        "\"\"\" \n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 67,
      "id": "Bk4QX_mF7r2M",
      "metadata": {
        "id": "Bk4QX_mF7r2M"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "cohort_sofa_joined = sqldf(query8, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 68,
      "id": "E0LCWoCc7yeZ",
      "metadata": {
        "id": "E0LCWoCc7yeZ"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "cohort_sofa_joined = cohort_sofa_joined.loc[:,~cohort_sofa_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 69,
      "id": "Uk7tv2sS74qD",
      "metadata": {
        "id": "Uk7tv2sS74qD"
      },
      "outputs": [],
      "source": [
        "#9. Join comorbiditiy score 2\n",
        "\n",
        "query9 = f\"\"\"\n",
        "SELECT DISTINCT cohort_sofa_joined.*,\n",
        "    cohort_ventilation_status.*\n",
        "FROM cohort_sofa_joined As cohort_sofa_joined\n",
        "LEFT JOIN cohort_ventilation_status AS cohort_ventilation_status\n",
        "ON cohort_sofa_joined.stay_id = cohort_ventilation_status.stay_id\n",
        "\"\"\" \n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 70,
      "id": "_GUI95_Y8OAi",
      "metadata": {
        "id": "_GUI95_Y8OAi"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "cohort_ventilation_joined  = sqldf(query9, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 71,
      "id": "2I_nZ2kC8Qo6",
      "metadata": {
        "id": "2I_nZ2kC8Qo6"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "cohort_ventilation_joined  = cohort_ventilation_joined.loc[:,~cohort_ventilation_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 72,
      "id": "OpLhiLPa8Ub2",
      "metadata": {
        "id": "OpLhiLPa8Ub2"
      },
      "outputs": [],
      "source": [
        "#10. Join comorbiditiy score 3\n",
        "\n",
        "query10 = f\"\"\"\n",
        "SELECT DISTINCT cohort_ventilation_joined.*,\n",
        "     cohort_urine_output.*\n",
        "FROM cohort_ventilation_joined As cohort_ventilation_joined\n",
        "LEFT JOIN cohort_urine_output AS cohort_urine_output\n",
        "ON cohort_ventilation_joined.stay_id = cohort_urine_output.stay_id\n",
        "\"\"\" "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 73,
      "id": "-_TkwNa68l5_",
      "metadata": {
        "id": "-_TkwNa68l5_"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "cohort_urine_joined  = sqldf(query10, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 74,
      "id": "7D2ZtQ858rSJ",
      "metadata": {
        "id": "7D2ZtQ858rSJ"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "cohort_urine_joined = cohort_urine_joined.loc[:,~cohort_urine_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 75,
      "id": "qnU546g78upx",
      "metadata": {
        "id": "qnU546g78upx"
      },
      "outputs": [],
      "source": [
        "# 11. Join ventilation status\n",
        "query11 = f\"\"\"\n",
        "SELECT DISTINCT cohort_urine_joined.*,\n",
        "     cohort_vasopressin.*\n",
        "FROM cohort_urine_joined As cohort_urine_joined\n",
        "LEFT JOIN cohort_vasopressin AS cohort_vasopressin\n",
        "ON cohort_urine_joined.stay_id = cohort_vasopressin.stay_id\n",
        "\"\"\" \n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 76,
      "id": "mw0apTCS9OKo",
      "metadata": {
        "id": "mw0apTCS9OKo"
      },
      "outputs": [],
      "source": [
        "#Run query\n",
        "cohort_vasopressin_joined = sqldf(query11, globals())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 77,
      "id": "rjxH_wHM9Sz1",
      "metadata": {
        "id": "rjxH_wHM9Sz1"
      },
      "outputs": [],
      "source": [
        "#Remove duplicate Ids columns \n",
        "cohort_vasopressin_joined = cohort_vasopressin_joined.loc[:,~cohort_vasopressin_joined.columns.duplicated()].copy()"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "BYGnjZNBB7DT",
      "metadata": {
        "id": "BYGnjZNBB7DT"
      },
      "source": [
        "### **Final result table**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 85,
      "id": "4Zk6LMqqAZwj",
      "metadata": {
        "id": "4Zk6LMqqAZwj"
      },
      "outputs": [],
      "source": [
        "full_cohort = cohort_vasopressin_joined.copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 86,
      "id": "feJwp6t2Ai-e",
      "metadata": {
        "id": "feJwp6t2Ai-e"
      },
      "outputs": [],
      "source": [
        "full_cohort.to_csv('../data/full_cohort_final_v02.csv')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 87,
      "id": "Ac66qRpboemh",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ac66qRpboemh",
        "outputId": "ba96821f-9639-418b-f4c9-80d162b47b1a"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "subject_id                   0\n",
              "hadm_id                      0\n",
              "stay_id                      0\n",
              "n_stays                      0\n",
              "sum_los                      0\n",
              "anchor_age                   0\n",
              "gender                       0\n",
              "suspected_infection      35196\n",
              "admission_type               0\n",
              "weight                    2009\n",
              "Height_chart             25813\n",
              "heart_rate_min              44\n",
              "heart_rate_max              44\n",
              "temperature_min           4778\n",
              "temperature_max           4778\n",
              "ABPm_min                 29941\n",
              "ABPm_max                 29941\n",
              "ABPd_min                 30042\n",
              "ABPd_max                 30042\n",
              "ABPs_min                 30048\n",
              "ABPs_max                 30048\n",
              "NBPm_min                  6200\n",
              "NBPm_max                  6200\n",
              "NBPd_min                  6187\n",
              "NBPd_max                  6187\n",
              "NBPs_min                  6182\n",
              "NBPs_max                  6182\n",
              "RR_min                     141\n",
              "RR_max                     141\n",
              "SpO2_min                    54\n",
              "SpO2_max                    54\n",
              "albumin_min              36548\n",
              "albumin_max              36548\n",
              "platelet_min               487\n",
              "platelet_max               487\n",
              "bun_min                    425\n",
              "bun_max                    425\n",
              "calcium_min               6701\n",
              "calcium_max               6701\n",
              "creatinine_min             419\n",
              "creatinine_max             419\n",
              "glucose_min                810\n",
              "glucose_max                810\n",
              "bicarbonate_min            531\n",
              "bicarbonate_max            531\n",
              "potassium_min              585\n",
              "potassium_max              585\n",
              "sodium_min                 528\n",
              "sodium_max                 528\n",
              "bilirubin_min            31005\n",
              "bilirubin_max            31005\n",
              "wbc_min                    484\n",
              "wbc_max                    484\n",
              "lactate_min              13241\n",
              "lactate_max              13241\n",
              "magnesium_min             3357\n",
              "magnesium_max             3357\n",
              "pH_min                   18025\n",
              "pH_max                   18025\n",
              "pCO2_min                 19324\n",
              "pCO2_max                 19324\n",
              "pO2_min                  19324\n",
              "pO2_max                  19324\n",
              "gcs_min                      0\n",
              "SOFA                         0\n",
              "ventilation_status       14727\n",
              "urineoutput               1505\n",
              "dopamine_amount          52816\n",
              "dopamine_rate            52816\n",
              "epinephrine_amount       53451\n",
              "epinephrine_rate         53461\n",
              "norepinephrine_amount    47813\n",
              "norepinephrine_rate      47813\n",
              "phenylephrine_amount     40757\n",
              "phenylephrine_rate       40757\n",
              "vasopressin_amount       52161\n",
              "vasopressin_rate         52161\n",
              "dobutamine_amount        53171\n",
              "dobutamine_rate          53171\n",
              "milrinone_amount         52747\n",
              "milrinone_rate           52747\n",
              "dtype: int64"
            ]
          },
          "execution_count": 87,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "pd.set_option('display.max_rows',None)\n",
        "full_cohort.isna().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 88,
      "id": "uSRe0DGbLthb",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uSRe0DGbLthb",
        "outputId": "1cb4c628-73b9-4f90-ebc4-cb44936e04d1"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "53461"
            ]
          },
          "execution_count": 88,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "len(full_cohort)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 89,
      "id": "fa50d5b9",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/plain": [
              "15929"
            ]
          },
          "execution_count": 89,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "initial_cohort = full_cohort[full_cohort[\"sum_los\"] >= 3.3]\n",
        "len(initial_cohort)"
      ]
    }
  ],
  "metadata": {
    "accelerator": "GPU",
    "colab": {
      "collapsed_sections": [
        "Zjr71dP72MTj",
        "sKfQgEN8m3Hd",
        "UNM6XhP9o3ZT",
        "KL_OS0XgBMC5"
      ],
      "include_colab_link": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3.10.6 ('GBQ')",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.6"
    },
    "vscode": {
      "interpreter": {
        "hash": "3b768364c0b3efe15b3112a0334ed1bacae302a4cc75709195a56040dff1454b"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}