mimic-iii/tutorials/cohort-selection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cohort selection\n",
    "\n",
    "The aim of this tutorial is to describe how patients are tracked in the MIMIC-III database. By the end of this notebook you should:\n",
    "\n",
    "* Understand what `subject_id`, `hadm_id`, and `icustay_id` represent\n",
    "* Know how to set up a cohort table for subselecting a patient population\n",
    "* Understand the difference between service and physical location\n",
    "\n",
    "Requirements:\n",
    "\n",
    "* MIMIC-III in a PostgreSQL database\n",
    "* Python packages installable with: \n",
    "    * `pip install numpy pandas matplotlib psycopg2 jupyter`\n",
    "    \n",
    "First, as always, we open a connection to a local copy of the database. If you don't have a local copy of the database in PostgreSQL, follow the tutorial online (http://mimic.physionet.org) to install one.\n",
    "\n",
    "Let's begin!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import psycopg2\n",
    "\n",
    "# below imports are used to print out pretty pandas dataframes\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot')\n",
    "\n",
    "# information used to create a database connection\n",
    "sqluser = 'postgres'\n",
    "dbname = 'mimic'\n",
    "schema_name = 'mimiciii'\n",
    "\n",
    "# Connect to postgres with a copy of the MIMIC-III database\n",
    "con = psycopg2.connect(dbname=dbname, user=sqluser)\n",
    "\n",
    "# the below statement is prepended to queries to ensure they select from the right schema\n",
    "query_schema = 'set search_path to ' + schema_name + ';'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cohort selection will begin with three tables: *patients*, *admissions*, and *icustays*:\n",
    "\n",
    "* *patients*: information about a patient that does not change - e.g. date of birth, genotypical sex\n",
    "* *admissions*: information recorded on hospital admission - admission type (elective, emergency), time of admission\n",
    "* *icustays*: information recorded on intensive care unit admission - primarily admission and discharge time\n",
    "\n",
    "As MIMIC-III is primarily an intensive care unit (ICU) database, the focus will be on patients admitted to and discharged from the ICU. That is, rather than selecting our cohort based off the individual patient (identified by `subject_id` in the database), we will usually want to select our cohort based off the ICU stay (identified by `icustay_id`). Thus, it is sensible to begin with the *icustays* table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>268</td>\n",
       "      <td>110404</td>\n",
       "      <td>280836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>269</td>\n",
       "      <td>106296</td>\n",
       "      <td>206613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>270</td>\n",
       "      <td>188028</td>\n",
       "      <td>220345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>271</td>\n",
       "      <td>173727</td>\n",
       "      <td>249196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>272</td>\n",
       "      <td>164716</td>\n",
       "      <td>210407</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id\n",
       "0         268   110404      280836\n",
       "1         269   106296      206613\n",
       "2         270   188028      220345\n",
       "3         271   173727      249196\n",
       "4         272   164716      210407"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT subject_id, hadm_id, icustay_id\n",
    "FROM icustays\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: in the above we use `LIMIT 10`: this limits our results to only 10 rows. It's nice to include this statement when prototyping as it speeds up queries immensely. Later on when we are doing full data extraction, we would remove this statement.\n",
    "\n",
    "If we are interested in the length of stay for the ICU patients, we can query the `intime` and `outtime` columns, adding in some SQL specific syntax for calculating the difference between two dates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay_interval</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>268</td>\n",
       "      <td>110404</td>\n",
       "      <td>280836</td>\n",
       "      <td>3 days 05:58:33</td>\n",
       "      <td>280713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>269</td>\n",
       "      <td>106296</td>\n",
       "      <td>206613</td>\n",
       "      <td>3 days 06:41:28</td>\n",
       "      <td>283288.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>270</td>\n",
       "      <td>188028</td>\n",
       "      <td>220345</td>\n",
       "      <td>2 days 21:27:09</td>\n",
       "      <td>250029.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>271</td>\n",
       "      <td>173727</td>\n",
       "      <td>249196</td>\n",
       "      <td>2 days 01:26:22</td>\n",
       "      <td>177982.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>272</td>\n",
       "      <td>164716</td>\n",
       "      <td>210407</td>\n",
       "      <td>1 days 14:53:09</td>\n",
       "      <td>139989.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay_interval  \\\n",
       "0         268   110404      280836              3 days 05:58:33   \n",
       "1         269   106296      206613              3 days 06:41:28   \n",
       "2         270   188028      220345              2 days 21:27:09   \n",
       "3         271   173727      249196              2 days 01:26:22   \n",
       "4         272   164716      210407              1 days 14:53:09   \n",
       "\n",
       "   icu_length_of_stay  \n",
       "0            280713.0  \n",
       "1            283288.0  \n",
       "2            250029.0  \n",
       "3            177982.0  \n",
       "4            139989.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT subject_id, hadm_id, icustay_id\n",
    ", outtime - intime as icu_length_of_stay_interval\n",
    ", EXTRACT(EPOCH FROM outtime - intime) as icu_length_of_stay\n",
    "FROM icustays\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the `EXTRACT(EPOCH FROM ... )` code extracts the number of fractional seconds represented by an interval data type. So the logic is roughly `intime - outtime` -> `icu_length_of_stay_interval`, followed by `EXTRACT(EPOCH FROM icu_length_of_stay_interval)` -> fractional seconds (numeric). ICU Length of stay is most readily interpretted when represented in fractional days, so let's do that conversion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>268</td>\n",
       "      <td>110404</td>\n",
       "      <td>280836</td>\n",
       "      <td>3.248993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>269</td>\n",
       "      <td>106296</td>\n",
       "      <td>206613</td>\n",
       "      <td>3.278796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>270</td>\n",
       "      <td>188028</td>\n",
       "      <td>220345</td>\n",
       "      <td>2.893854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>271</td>\n",
       "      <td>173727</td>\n",
       "      <td>249196</td>\n",
       "      <td>2.059977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>272</td>\n",
       "      <td>164716</td>\n",
       "      <td>210407</td>\n",
       "      <td>1.620243</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay\n",
       "0         268   110404      280836            3.248993\n",
       "1         269   106296      206613            3.278796\n",
       "2         270   188028      220345            2.893854\n",
       "3         271   173727      249196            2.059977\n",
       "4         272   164716      210407            1.620243"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT subject_id, hadm_id, icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    "FROM icustays\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the above, seconds are converted to days easily by dividing by: 60 (seconds in a minute), 60 (minutes in an hour), and 24 (hours in a day). We also omit the `icu_length_of_stay_interval` as it's now redundant for our purposes.\n",
    "\n",
    "If we are only interested in ICU stays lasting a certain length (say 24 hours), we need to do the following two steps:\n",
    "\n",
    "* use an in-line view to \"hold\" the data\n",
    "* use the `WHERE` clause to filter this data using our generated column\n",
    "\n",
    "Here's an example of us filtering to only stays lasting at least 2 days:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>268</td>\n",
       "      <td>110404</td>\n",
       "      <td>280836</td>\n",
       "      <td>3.248993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>269</td>\n",
       "      <td>106296</td>\n",
       "      <td>206613</td>\n",
       "      <td>3.278796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>270</td>\n",
       "      <td>188028</td>\n",
       "      <td>220345</td>\n",
       "      <td>2.893854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>271</td>\n",
       "      <td>173727</td>\n",
       "      <td>249196</td>\n",
       "      <td>2.059977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>274</td>\n",
       "      <td>130546</td>\n",
       "      <td>254851</td>\n",
       "      <td>8.814259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>275</td>\n",
       "      <td>129886</td>\n",
       "      <td>219649</td>\n",
       "      <td>7.131412</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay\n",
       "0         268   110404      280836            3.248993\n",
       "1         269   106296      206613            3.278796\n",
       "2         270   188028      220345            2.893854\n",
       "3         271   173727      249196            2.059977\n",
       "4         274   130546      254851            8.814259\n",
       "5         275   129886      219649            7.131412"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT subject_id, hadm_id, icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    "FROM icustays\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "FROM co\n",
    "WHERE icu_length_of_stay >= 2\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks good - none of the above stays are shorter than 2 days.\n",
    "\n",
    "Many studies using the MIMIC-III database are focused on specific subgroups of patients. For example, MIMIC-III contains both adults and neonates, but it is rare that a study would like to evaluate some phenomenom in both groups simulatenously. As a result, the first step of many studies is selecting a subpopulation from the *icustays* table. Concretely, we will want to select a set of `icustay_id` which represent our patient population. You've just seen an example of doing this: in the above code, we limited our population to only those who were in the ICU for at least 2 days.\n",
    "\n",
    "When subselecting the patient population, it is generally good practice to build a \"cohort\" table - that is a table with all `icustay_id` available in the database, each associated with binary flags indicating whether or not they are excluded from your population. Let's take a look at how this would work with the above query which limited the dataset to patients who stayed longer than 2 days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>exclusion_los</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>268</td>\n",
       "      <td>110404</td>\n",
       "      <td>280836</td>\n",
       "      <td>3.248993</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>269</td>\n",
       "      <td>106296</td>\n",
       "      <td>206613</td>\n",
       "      <td>3.278796</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>270</td>\n",
       "      <td>188028</td>\n",
       "      <td>220345</td>\n",
       "      <td>2.893854</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>271</td>\n",
       "      <td>173727</td>\n",
       "      <td>249196</td>\n",
       "      <td>2.059977</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>272</td>\n",
       "      <td>164716</td>\n",
       "      <td>210407</td>\n",
       "      <td>1.620243</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>273</td>\n",
       "      <td>158689</td>\n",
       "      <td>241507</td>\n",
       "      <td>1.486181</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>274</td>\n",
       "      <td>130546</td>\n",
       "      <td>254851</td>\n",
       "      <td>8.814259</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>275</td>\n",
       "      <td>129886</td>\n",
       "      <td>219649</td>\n",
       "      <td>7.131412</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>276</td>\n",
       "      <td>135156</td>\n",
       "      <td>206327</td>\n",
       "      <td>1.337836</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>277</td>\n",
       "      <td>171601</td>\n",
       "      <td>272866</td>\n",
       "      <td>0.731273</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay  exclusion_los\n",
       "0         268   110404      280836            3.248993              0\n",
       "1         269   106296      206613            3.278796              0\n",
       "2         270   188028      220345            2.893854              0\n",
       "3         271   173727      249196            2.059977              0\n",
       "4         272   164716      210407            1.620243              1\n",
       "5         273   158689      241507            1.486181              1\n",
       "6         274   130546      254851            8.814259              0\n",
       "7         275   129886      219649            7.131412              0\n",
       "8         276   135156      206327            1.337836              1\n",
       "9         277   171601      272866            0.731273              1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT subject_id, hadm_id, icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    "FROM icustays\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "        as exclusion_los\n",
    "FROM co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the earlier query we had a total of 6 rows returned because we filtered 4 of them out. In the above query, we keep all 10 rows, but we have indicated that 4 of them should be excluded in the last column. This is a good practice to have as it will make it very easy to summarize your exclusions at the end of your study and modify them if your later work deems it necessary.\n",
    "\n",
    "Let's go back to the common exclusion criteria mentioned earlier: flagging non-adults for removal. First, we'll need to calculate the patient's age on ICU admission, which will require the patient's date of birth and the ICU admission time. We already have the ICU admission time (`intime` in the *icustays* table), so all we need to do is get the date of birth from the *patients* table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>age</th>\n",
       "      <th>exclusion_los</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>163353</td>\n",
       "      <td>243653</td>\n",
       "      <td>0.091829</td>\n",
       "      <td>0 days 21:20:07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>145834</td>\n",
       "      <td>211552</td>\n",
       "      <td>6.064560</td>\n",
       "      <td>27950 days 19:10:11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>1.678472</td>\n",
       "      <td>17475 days 00:29:31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>178980</td>\n",
       "      <td>214757</td>\n",
       "      <td>0.084444</td>\n",
       "      <td>0 days 06:04:24</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>228232</td>\n",
       "      <td>3.672917</td>\n",
       "      <td>24084 days 21:30:54</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0 days 15:35:29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>236754</td>\n",
       "      <td>0.739097</td>\n",
       "      <td>2 days 03:26:01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>159514</td>\n",
       "      <td>262299</td>\n",
       "      <td>1.075521</td>\n",
       "      <td>0 days 12:36:10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>150750</td>\n",
       "      <td>220597</td>\n",
       "      <td>5.323056</td>\n",
       "      <td>15263 days 13:07:02</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>184167</td>\n",
       "      <td>288409</td>\n",
       "      <td>8.092106</td>\n",
       "      <td>0 days 11:39:05</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay                 age  \\\n",
       "0           2   163353      243653            0.091829     0 days 21:20:07   \n",
       "1           3   145834      211552            6.064560 27950 days 19:10:11   \n",
       "2           4   185777      294638            1.678472 17475 days 00:29:31   \n",
       "3           5   178980      214757            0.084444     0 days 06:04:24   \n",
       "4           6   107064      228232            3.672917 24084 days 21:30:54   \n",
       "5           7   118037      278444            0.267720     0 days 15:35:29   \n",
       "6           7   118037      236754            0.739097     2 days 03:26:01   \n",
       "7           8   159514      262299            1.075521     0 days 12:36:10   \n",
       "8           9   150750      220597            5.323056 15263 days 13:07:02   \n",
       "9          10   184167      288409            8.092106     0 days 11:39:05   \n",
       "\n",
       "   exclusion_los  \n",
       "0              1  \n",
       "1              0  \n",
       "2              1  \n",
       "3              1  \n",
       "4              0  \n",
       "5              1  \n",
       "6              1  \n",
       "7              1  \n",
       "8              0  \n",
       "9              0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT icu.subject_id, icu.hadm_id, icu.icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    ", icu.intime - pat.dob AS age\n",
    "FROM icustays icu\n",
    "INNER JOIN patients pat\n",
    "  ON icu.subject_id = pat.subject_id\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , co.age\n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "        as exclusion_los\n",
    "FROM co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notes from the above query: we have to specify the table in `icu.subject_id` because there is a `subject_id` column in both the *icustays* table and the *patients* table, and the program doesn't know which we want unless we specify it.\n",
    "\n",
    "Now, looking at the results, it appears `age` is returned as the number of days between the `dob` and the `intime` - perhaps not what we desire! As mentioned before, this is an interval data type - it's useful when doing date operations but for our purposes it is not practical. We have three options:\n",
    "\n",
    "* we can use the function `EXTRACT()` to extract the seconds and convert that into an age by dividing by the number of seconds in a year (as we did before)\n",
    "* we can use the PostgreSQL function `AGE()` to return a symbolic representation of the age in years followed by the function `DATE_PART()` to extract the years\n",
    "* the same as the above, but calling `DATE_PART()` to get the months and days as well for more precision\n",
    "\n",
    "Let's look at all three."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>age</th>\n",
       "      <th>age_extract_year</th>\n",
       "      <th>age_extract_precise</th>\n",
       "      <th>age_extract_epoch</th>\n",
       "      <th>exclusion_los</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>163353</td>\n",
       "      <td>243653</td>\n",
       "      <td>0.091829</td>\n",
       "      <td>0 days 21:20:07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.002402</td>\n",
       "      <td>0.002434</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>145834</td>\n",
       "      <td>211552</td>\n",
       "      <td>6.064560</td>\n",
       "      <td>27950 days 19:10:11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>76.526779</td>\n",
       "      <td>76.526792</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>1.678472</td>\n",
       "      <td>17475 days 00:29:31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47.844990</td>\n",
       "      <td>47.845047</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>178980</td>\n",
       "      <td>214757</td>\n",
       "      <td>0.084444</td>\n",
       "      <td>0 days 06:04:24</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000686</td>\n",
       "      <td>0.000693</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>228232</td>\n",
       "      <td>3.672917</td>\n",
       "      <td>24084 days 21:30:54</td>\n",
       "      <td>0.0</td>\n",
       "      <td>65.942245</td>\n",
       "      <td>65.942297</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0 days 15:35:29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.001716</td>\n",
       "      <td>0.001779</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>236754</td>\n",
       "      <td>0.739097</td>\n",
       "      <td>2 days 03:26:01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.005819</td>\n",
       "      <td>0.005868</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>159514</td>\n",
       "      <td>262299</td>\n",
       "      <td>1.075521</td>\n",
       "      <td>0 days 12:36:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.001373</td>\n",
       "      <td>0.001438</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>150750</td>\n",
       "      <td>220597</td>\n",
       "      <td>5.323056</td>\n",
       "      <td>15263 days 13:07:02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41.790219</td>\n",
       "      <td>41.790228</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>184167</td>\n",
       "      <td>288409</td>\n",
       "      <td>8.092106</td>\n",
       "      <td>0 days 11:39:05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.001258</td>\n",
       "      <td>0.001329</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay                 age  \\\n",
       "0           2   163353      243653            0.091829     0 days 21:20:07   \n",
       "1           3   145834      211552            6.064560 27950 days 19:10:11   \n",
       "2           4   185777      294638            1.678472 17475 days 00:29:31   \n",
       "3           5   178980      214757            0.084444     0 days 06:04:24   \n",
       "4           6   107064      228232            3.672917 24084 days 21:30:54   \n",
       "5           7   118037      278444            0.267720     0 days 15:35:29   \n",
       "6           7   118037      236754            0.739097     2 days 03:26:01   \n",
       "7           8   159514      262299            1.075521     0 days 12:36:10   \n",
       "8           9   150750      220597            5.323056 15263 days 13:07:02   \n",
       "9          10   184167      288409            8.092106     0 days 11:39:05   \n",
       "\n",
       "   age_extract_year  age_extract_precise  age_extract_epoch  exclusion_los  \n",
       "0               0.0             0.002402           0.002434              1  \n",
       "1               0.0            76.526779          76.526792              0  \n",
       "2               0.0            47.844990          47.845047              1  \n",
       "3               0.0             0.000686           0.000693              1  \n",
       "4               0.0            65.942245          65.942297              0  \n",
       "5               0.0             0.001716           0.001779              1  \n",
       "6               0.0             0.005819           0.005868              1  \n",
       "7               0.0             0.001373           0.001438              1  \n",
       "8               0.0            41.790219          41.790228              0  \n",
       "9               0.0             0.001258           0.001329              0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT icu.subject_id, icu.hadm_id, icu.icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    ", icu.intime - pat.dob AS age\n",
    "FROM icustays icu\n",
    "INNER JOIN patients pat\n",
    "  ON icu.subject_id = pat.subject_id\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , co.age\n",
    "  , EXTRACT('year' FROM co.age) as age_extract_year \n",
    "  , EXTRACT('year' FROM co.age) \n",
    "    + EXTRACT('months' FROM co.age) / 12.0\n",
    "    + EXTRACT('days' FROM co.age) / 365.242\n",
    "    + EXTRACT('hours' FROM co.age) / 24.0 / 364.242\n",
    "    as age_extract_precise\n",
    "  , EXTRACT('epoch' from co.age) / 60.0 / 60.0 / 24.0 / 365.242 as age_extract_epoch\n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "        as exclusion_los\n",
    "FROM co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, there is very little difference between the second and last approach - so it is up to preference (and desire for true precision). We will use the `EXTRACT('epoch' ... )` approach as it's the simplest.\n",
    "\n",
    "Now, we will filter out neonates by requiring age to be greater than 16 (note while this also removes children, there are no children in the MIMIC-III database)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>age</th>\n",
       "      <th>exclusion_los</th>\n",
       "      <th>exclusion_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>163353</td>\n",
       "      <td>243653</td>\n",
       "      <td>0.091829</td>\n",
       "      <td>0.002434</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>145834</td>\n",
       "      <td>211552</td>\n",
       "      <td>6.064560</td>\n",
       "      <td>76.526792</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>1.678472</td>\n",
       "      <td>47.845047</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>178980</td>\n",
       "      <td>214757</td>\n",
       "      <td>0.084444</td>\n",
       "      <td>0.000693</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>228232</td>\n",
       "      <td>3.672917</td>\n",
       "      <td>65.942297</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0.001779</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>236754</td>\n",
       "      <td>0.739097</td>\n",
       "      <td>0.005868</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>159514</td>\n",
       "      <td>262299</td>\n",
       "      <td>1.075521</td>\n",
       "      <td>0.001438</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>150750</td>\n",
       "      <td>220597</td>\n",
       "      <td>5.323056</td>\n",
       "      <td>41.790228</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>184167</td>\n",
       "      <td>288409</td>\n",
       "      <td>8.092106</td>\n",
       "      <td>0.001329</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay        age  \\\n",
       "0           2   163353      243653            0.091829   0.002434   \n",
       "1           3   145834      211552            6.064560  76.526792   \n",
       "2           4   185777      294638            1.678472  47.845047   \n",
       "3           5   178980      214757            0.084444   0.000693   \n",
       "4           6   107064      228232            3.672917  65.942297   \n",
       "5           7   118037      278444            0.267720   0.001779   \n",
       "6           7   118037      236754            0.739097   0.005868   \n",
       "7           8   159514      262299            1.075521   0.001438   \n",
       "8           9   150750      220597            5.323056  41.790228   \n",
       "9          10   184167      288409            8.092106   0.001329   \n",
       "\n",
       "   exclusion_los  exclusion_age  \n",
       "0              1              1  \n",
       "1              0              0  \n",
       "2              1              0  \n",
       "3              1              1  \n",
       "4              0              0  \n",
       "5              1              1  \n",
       "6              1              1  \n",
       "7              1              1  \n",
       "8              0              0  \n",
       "9              0              1  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT icu.subject_id, icu.hadm_id, icu.icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    ", EXTRACT('epoch' from icu.intime - pat.dob) / 60.0 / 60.0 / 24.0 / 365.242 as age\n",
    "FROM icustays icu\n",
    "INNER JOIN patients pat\n",
    "  ON icu.subject_id = pat.subject_id\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , co.age\n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "        as exclusion_los\n",
    "  , CASE\n",
    "        WHEN co.age < 16 then 1\n",
    "    ELSE 0 END\n",
    "        as exclusion_age\n",
    "FROM co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above we can see we have \"flagged\" 6 `icustay_id` for exclusion due to their age, and that many of these exclusions overlap with the requirement that their ICU length of stay be longer than 2 days.\n",
    "\n",
    "Let's try another common exclusion criteria: secondary admissions to the ICU - either in-hospital or out of hospital. The primary reason for this is it simplifies many statistical analyses which assume independent observations. If we kept multiple ICU stays for the same patient, then we would have to account for the fact that these ICU stays are highly correlated (e.g. the same patient may repeatedly be admitted for the same condition), and this can add an undesirable layer of complexity. To identify readmissions, we first rank ICU stays from earliest to latest using the `RANK()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>age</th>\n",
       "      <th>icustay_id_order</th>\n",
       "      <th>exclusion_los</th>\n",
       "      <th>exclusion_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>163353</td>\n",
       "      <td>243653</td>\n",
       "      <td>0.091829</td>\n",
       "      <td>0.002434</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>145834</td>\n",
       "      <td>211552</td>\n",
       "      <td>6.064560</td>\n",
       "      <td>76.526792</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>1.678472</td>\n",
       "      <td>47.845047</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>178980</td>\n",
       "      <td>214757</td>\n",
       "      <td>0.084444</td>\n",
       "      <td>0.000693</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>228232</td>\n",
       "      <td>3.672917</td>\n",
       "      <td>65.942297</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0.001779</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>236754</td>\n",
       "      <td>0.739097</td>\n",
       "      <td>0.005868</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>159514</td>\n",
       "      <td>262299</td>\n",
       "      <td>1.075521</td>\n",
       "      <td>0.001438</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>150750</td>\n",
       "      <td>220597</td>\n",
       "      <td>5.323056</td>\n",
       "      <td>41.790228</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>184167</td>\n",
       "      <td>288409</td>\n",
       "      <td>8.092106</td>\n",
       "      <td>0.001329</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay        age  \\\n",
       "0           2   163353      243653            0.091829   0.002434   \n",
       "1           3   145834      211552            6.064560  76.526792   \n",
       "2           4   185777      294638            1.678472  47.845047   \n",
       "3           5   178980      214757            0.084444   0.000693   \n",
       "4           6   107064      228232            3.672917  65.942297   \n",
       "5           7   118037      278444            0.267720   0.001779   \n",
       "6           7   118037      236754            0.739097   0.005868   \n",
       "7           8   159514      262299            1.075521   0.001438   \n",
       "8           9   150750      220597            5.323056  41.790228   \n",
       "9          10   184167      288409            8.092106   0.001329   \n",
       "\n",
       "   icustay_id_order  exclusion_los  exclusion_age  \n",
       "0                 1              1              1  \n",
       "1                 1              0              0  \n",
       "2                 1              1              0  \n",
       "3                 1              1              1  \n",
       "4                 1              0              0  \n",
       "5                 1              1              1  \n",
       "6                 2              1              1  \n",
       "7                 1              1              1  \n",
       "8                 1              0              0  \n",
       "9                 1              0              1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT icu.subject_id, icu.hadm_id, icu.icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    ", EXTRACT('epoch' from icu.intime - pat.dob) / 60.0 / 60.0 / 24.0 / 365.242 as age\n",
    "\n",
    ", RANK() OVER (PARTITION BY icu.subject_id ORDER BY icu.intime) AS icustay_id_order\n",
    "\n",
    "FROM icustays icu\n",
    "INNER JOIN patients pat\n",
    "  ON icu.subject_id = pat.subject_id\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , co.age\n",
    "  , co.icustay_id_order\n",
    "  \n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "        as exclusion_los\n",
    "  , CASE\n",
    "        WHEN co.age < 16 then 1\n",
    "    ELSE 0 END\n",
    "        as exclusion_age\n",
    "FROM co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that `subject_id` = 7 has been admitted twice - we would like to exclude this second admission, so we code in a `CASE` statement to do exactly this (note: while the subject would be excluded anyway, due to the other exclusion criteria, it's still a good example case!)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>age</th>\n",
       "      <th>icustay_id_order</th>\n",
       "      <th>exclusion_los</th>\n",
       "      <th>exclusion_age</th>\n",
       "      <th>exclusion_first_stay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>163353</td>\n",
       "      <td>243653</td>\n",
       "      <td>0.091829</td>\n",
       "      <td>0.002434</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>145834</td>\n",
       "      <td>211552</td>\n",
       "      <td>6.064560</td>\n",
       "      <td>76.526792</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>1.678472</td>\n",
       "      <td>47.845047</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>178980</td>\n",
       "      <td>214757</td>\n",
       "      <td>0.084444</td>\n",
       "      <td>0.000693</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>228232</td>\n",
       "      <td>3.672917</td>\n",
       "      <td>65.942297</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0.001779</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>236754</td>\n",
       "      <td>0.739097</td>\n",
       "      <td>0.005868</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>159514</td>\n",
       "      <td>262299</td>\n",
       "      <td>1.075521</td>\n",
       "      <td>0.001438</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>150750</td>\n",
       "      <td>220597</td>\n",
       "      <td>5.323056</td>\n",
       "      <td>41.790228</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>184167</td>\n",
       "      <td>288409</td>\n",
       "      <td>8.092106</td>\n",
       "      <td>0.001329</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay        age  \\\n",
       "0           2   163353      243653            0.091829   0.002434   \n",
       "1           3   145834      211552            6.064560  76.526792   \n",
       "2           4   185777      294638            1.678472  47.845047   \n",
       "3           5   178980      214757            0.084444   0.000693   \n",
       "4           6   107064      228232            3.672917  65.942297   \n",
       "5           7   118037      278444            0.267720   0.001779   \n",
       "6           7   118037      236754            0.739097   0.005868   \n",
       "7           8   159514      262299            1.075521   0.001438   \n",
       "8           9   150750      220597            5.323056  41.790228   \n",
       "9          10   184167      288409            8.092106   0.001329   \n",
       "\n",
       "   icustay_id_order  exclusion_los  exclusion_age  exclusion_first_stay  \n",
       "0                 1              1              1                     0  \n",
       "1                 1              0              0                     0  \n",
       "2                 1              1              0                     0  \n",
       "3                 1              1              1                     0  \n",
       "4                 1              0              0                     0  \n",
       "5                 1              1              1                     0  \n",
       "6                 2              1              1                     1  \n",
       "7                 1              1              1                     0  \n",
       "8                 1              0              0                     0  \n",
       "9                 1              0              1                     0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT icu.subject_id, icu.hadm_id, icu.icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    ", EXTRACT('epoch' from icu.intime - pat.dob) / 60.0 / 60.0 / 24.0 / 365.242 as age\n",
    "\n",
    ", RANK() OVER (PARTITION BY icu.subject_id ORDER BY icu.intime) AS icustay_id_order\n",
    "\n",
    "FROM icustays icu\n",
    "INNER JOIN patients pat\n",
    "  ON icu.subject_id = pat.subject_id\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , co.age\n",
    "  , co.icustay_id_order\n",
    "  \n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "    AS exclusion_los\n",
    "  , CASE\n",
    "        WHEN co.age < 16 then 1\n",
    "    ELSE 0 END\n",
    "    AS exclusion_age\n",
    "  , CASE \n",
    "        WHEN co.icustay_id_order != 1 THEN 1\n",
    "    ELSE 0 END \n",
    "    AS exclusion_first_stay\n",
    "FROM co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, we are now excluding this later admission.\n",
    "\n",
    "Finally, we may want to exclude patients who were admitted for certain services. This is commonly done as the patient demographics can vary widely based upon service type, and we may want a more homoegenous group of patients. The *services* table provides the hospital service that a patient was admitted under, and is the best place to identify the type of care the patient is receiving. Let's take a look at the *services* table now:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>transfertime</th>\n",
       "      <th>prev_service</th>\n",
       "      <th>curr_service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>471</td>\n",
       "      <td>135879</td>\n",
       "      <td>2122-07-22 14:07:27</td>\n",
       "      <td>TSURG</td>\n",
       "      <td>MED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>471</td>\n",
       "      <td>135879</td>\n",
       "      <td>2122-07-26 18:31:49</td>\n",
       "      <td>MED</td>\n",
       "      <td>TSURG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>472</td>\n",
       "      <td>173064</td>\n",
       "      <td>2172-09-28 19:22:15</td>\n",
       "      <td>None</td>\n",
       "      <td>CMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>473</td>\n",
       "      <td>129194</td>\n",
       "      <td>2201-01-09 20:16:45</td>\n",
       "      <td>None</td>\n",
       "      <td>NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>474</td>\n",
       "      <td>194246</td>\n",
       "      <td>2181-03-23 08:24:41</td>\n",
       "      <td>None</td>\n",
       "      <td>NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>474</td>\n",
       "      <td>146746</td>\n",
       "      <td>2181-04-04 17:38:46</td>\n",
       "      <td>None</td>\n",
       "      <td>NBB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>475</td>\n",
       "      <td>139351</td>\n",
       "      <td>2131-09-16 18:44:04</td>\n",
       "      <td>None</td>\n",
       "      <td>NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>476</td>\n",
       "      <td>161042</td>\n",
       "      <td>2100-07-05 10:26:45</td>\n",
       "      <td>None</td>\n",
       "      <td>NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>477</td>\n",
       "      <td>191025</td>\n",
       "      <td>2156-07-20 11:53:03</td>\n",
       "      <td>None</td>\n",
       "      <td>MED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>478</td>\n",
       "      <td>137370</td>\n",
       "      <td>2194-07-15 13:55:21</td>\n",
       "      <td>None</td>\n",
       "      <td>NB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id        transfertime prev_service curr_service\n",
       "0         471   135879 2122-07-22 14:07:27        TSURG          MED\n",
       "1         471   135879 2122-07-26 18:31:49          MED        TSURG\n",
       "2         472   173064 2172-09-28 19:22:15         None         CMED\n",
       "3         473   129194 2201-01-09 20:16:45         None           NB\n",
       "4         474   194246 2181-03-23 08:24:41         None           NB\n",
       "5         474   146746 2181-04-04 17:38:46         None          NBB\n",
       "6         475   139351 2131-09-16 18:44:04         None           NB\n",
       "7         476   161042 2100-07-05 10:26:45         None           NB\n",
       "8         477   191025 2156-07-20 11:53:03         None          MED\n",
       "9         478   137370 2194-07-15 13:55:21         None           NB"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT subject_id, hadm_id, transfertime, prev_service, curr_service\n",
    "FROM services\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above we can see that the `curr_service` column gives an abbreviation for the current service. The `prev_service` column is null, *unless* the patient had a transfer of service, in which case it identifies the previous service. For example, we can see `subject_id = 471` has had at least two service changes: once from TSURG to MED and once from MED back to TSURG (note: there may be more as we have limited this query using `LIMIT 10`, and you could examine this patient in detail using `SELECT * FROM services WHERE subject_id = 471` if you like).\n",
    "\n",
    "A list of the unique services and their descriptions can be found at:\n",
    "http://mimic.physionet.org/mimictables/services/\n",
    "\n",
    "In particular, if we want to exclude surgery, we should exclude patients who were admitted under:\n",
    "\n",
    "* CSURG\n",
    "* NSURG\n",
    "* ORTHO\n",
    "* PSURG\n",
    "* SURG\n",
    "* TSURG\n",
    "* VSURG\n",
    "\n",
    "We can simplify this to patients who were under service `'%SURG'` or `'ORTHO'` - where `'%'` is a wildcard matching any letter(s)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>curr_service</th>\n",
       "      <th>surgical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>135879</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>135879</td>\n",
       "      <td>TSURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>173064</td>\n",
       "      <td>CMED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>129194</td>\n",
       "      <td>NB</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>194246</td>\n",
       "      <td>NB</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>146746</td>\n",
       "      <td>NBB</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>139351</td>\n",
       "      <td>NB</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>161042</td>\n",
       "      <td>NB</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>191025</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>137370</td>\n",
       "      <td>NB</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hadm_id curr_service  surgical\n",
       "0   135879          MED         0\n",
       "1   135879        TSURG         1\n",
       "2   173064         CMED         0\n",
       "3   129194           NB         0\n",
       "4   194246           NB         0\n",
       "5   146746          NBB         0\n",
       "6   139351           NB         0\n",
       "7   161042           NB         0\n",
       "8   191025          MED         0\n",
       "9   137370           NB         0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT hadm_id, curr_service\n",
    ", CASE\n",
    "    WHEN curr_service like '%SURG' then 1\n",
    "    WHEN curr_service = 'ORTHO' then 1\n",
    "    ELSE 0 END\n",
    "  AS surgical\n",
    "FROM services se\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This seems to be working nicely - except we only have `hadm_id`, and we are basing our cohort off of `icustay_id`. No problem, we can join from the *icustays* table to get the `icustay_id` for each `hadm_id`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>curr_service</th>\n",
       "      <th>surgical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>275225</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100003</td>\n",
       "      <td>209281</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100006</td>\n",
       "      <td>291788</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100006</td>\n",
       "      <td>291788</td>\n",
       "      <td>OMED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100007</td>\n",
       "      <td>217937</td>\n",
       "      <td>SURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100009</td>\n",
       "      <td>253656</td>\n",
       "      <td>CSURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100010</td>\n",
       "      <td>271147</td>\n",
       "      <td>GU</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100011</td>\n",
       "      <td>214619</td>\n",
       "      <td>TRAUM</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100012</td>\n",
       "      <td>239289</td>\n",
       "      <td>SURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100016</td>\n",
       "      <td>217590</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hadm_id  icustay_id curr_service  surgical\n",
       "0   100001      275225          MED         0\n",
       "1   100003      209281          MED         0\n",
       "2   100006      291788          MED         0\n",
       "3   100006      291788         OMED         0\n",
       "4   100007      217937         SURG         1\n",
       "5   100009      253656        CSURG         1\n",
       "6   100010      271147           GU         0\n",
       "7   100011      214619        TRAUM         0\n",
       "8   100012      239289         SURG         1\n",
       "9   100016      217590          MED         0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT icu.hadm_id, icu.icustay_id, curr_service\n",
    ", CASE\n",
    "    WHEN curr_service like '%SURG' then 1\n",
    "    WHEN curr_service = 'ORTHO' then 1\n",
    "    ELSE 0 END\n",
    "  AS surgical\n",
    "FROM icustays icu\n",
    "LEFT JOIN services se\n",
    "  ON icu.hadm_id = se.hadm_id\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note however that now we have a new issue: which service do we pick for each `icustay_id`? This is a cohort selection question, not a syntax question. We choose to exclude patients whose **last** service **before** ICU admission was surgical. We can update our join clause to reflect this choice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>curr_service</th>\n",
       "      <th>surgical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>275225</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100003</td>\n",
       "      <td>209281</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100006</td>\n",
       "      <td>291788</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100007</td>\n",
       "      <td>217937</td>\n",
       "      <td>SURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100009</td>\n",
       "      <td>253656</td>\n",
       "      <td>CSURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100010</td>\n",
       "      <td>271147</td>\n",
       "      <td>GU</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100011</td>\n",
       "      <td>214619</td>\n",
       "      <td>TRAUM</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100012</td>\n",
       "      <td>239289</td>\n",
       "      <td>SURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100016</td>\n",
       "      <td>217590</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100017</td>\n",
       "      <td>258320</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hadm_id  icustay_id curr_service  surgical\n",
       "0   100001      275225          MED         0\n",
       "1   100003      209281          MED         0\n",
       "2   100006      291788          MED         0\n",
       "3   100007      217937         SURG         1\n",
       "4   100009      253656        CSURG         1\n",
       "5   100010      271147           GU         0\n",
       "6   100011      214619        TRAUM         0\n",
       "7   100012      239289         SURG         1\n",
       "8   100016      217590          MED         0\n",
       "9   100017      258320          MED         0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "SELECT icu.hadm_id, icu.icustay_id, se.curr_service\n",
    ", CASE\n",
    "    WHEN curr_service like '%SURG' then 1\n",
    "    WHEN curr_service = 'ORTHO' then 1\n",
    "    ELSE 0 END\n",
    "  AS surgical\n",
    "FROM icustays icu\n",
    "LEFT JOIN services se\n",
    " ON icu.hadm_id = se.hadm_id\n",
    "AND se.transfertime < icu.intime + interval '12' hour\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how `icustay_id` = 291788 no longer has an entry for OMED above: this is because this service was given after their ICU admission, so we do not want to consider it. Also note that our join clause has `+ interval '12' hour` - this adds a bit of \"fuzziness\" to our criteria. As these times are entered asynchronously by different people in varying locations in the hospital, there can be some minor inconsistencies in the order. For example, a patient may be transferred to the surgical service for ICU admission, but the `transfertime` in *services* occurs after the `intime` in *icustays* by an hour or so. This is administrative \"noise\" - and a fuzzy interval can be useful in these cases. Again, this is a cohort selection decision - you may not want to use an interval as large as 12 hours - perhaps only 2 or 4 - though in this case there is likely to be very minor differences as 80% of patients never have a change in hospital service.\n",
    "\n",
    "Finally, we want to collapse this down so we only have one service for a given ICU admission. As done earlier, we will use RANK() to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>curr_service</th>\n",
       "      <th>surgical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>275225</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100003</td>\n",
       "      <td>209281</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100006</td>\n",
       "      <td>291788</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100007</td>\n",
       "      <td>217937</td>\n",
       "      <td>SURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100009</td>\n",
       "      <td>253656</td>\n",
       "      <td>CSURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100010</td>\n",
       "      <td>271147</td>\n",
       "      <td>GU</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100011</td>\n",
       "      <td>214619</td>\n",
       "      <td>TRAUM</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100012</td>\n",
       "      <td>239289</td>\n",
       "      <td>SURG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100016</td>\n",
       "      <td>217590</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>100017</td>\n",
       "      <td>258320</td>\n",
       "      <td>MED</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hadm_id  icustay_id curr_service  surgical\n",
       "0   100001      275225          MED         0\n",
       "1   100003      209281          MED         0\n",
       "2   100006      291788          MED         0\n",
       "3   100007      217937         SURG         1\n",
       "4   100009      253656        CSURG         1\n",
       "5   100010      271147           GU         0\n",
       "6   100011      214619        TRAUM         0\n",
       "7   100012      239289         SURG         1\n",
       "8   100016      217590          MED         0\n",
       "9   100017      258320          MED         0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH serv AS\n",
    "(\n",
    "SELECT icu.hadm_id, icu.icustay_id, se.curr_service\n",
    ", CASE\n",
    "    WHEN curr_service like '%SURG' then 1\n",
    "    WHEN curr_service = 'ORTHO' then 1\n",
    "    ELSE 0 END\n",
    "  AS surgical\n",
    ", RANK() OVER (PARTITION BY icu.hadm_id ORDER BY se.transfertime DESC) as rank\n",
    "FROM icustays icu\n",
    "LEFT JOIN services se\n",
    " ON icu.hadm_id = se.hadm_id\n",
    "AND se.transfertime < icu.intime + interval '12' hour\n",
    "LIMIT 10\n",
    ")\n",
    "SELECT hadm_id, icustay_id, curr_service, surgical\n",
    "FROM serv\n",
    "WHERE rank = 1\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can join this table to our original cohort from above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>age</th>\n",
       "      <th>icustay_id_order</th>\n",
       "      <th>exclusion_los</th>\n",
       "      <th>exclusion_age</th>\n",
       "      <th>exclusion_first_stay</th>\n",
       "      <th>exclusion_surgical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>228232</td>\n",
       "      <td>3.672917</td>\n",
       "      <td>65.942297</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0.001779</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>236754</td>\n",
       "      <td>0.739097</td>\n",
       "      <td>0.005868</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>145834</td>\n",
       "      <td>211552</td>\n",
       "      <td>6.064560</td>\n",
       "      <td>76.526792</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>150750</td>\n",
       "      <td>220597</td>\n",
       "      <td>5.323056</td>\n",
       "      <td>41.790228</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>159514</td>\n",
       "      <td>262299</td>\n",
       "      <td>1.075521</td>\n",
       "      <td>0.001438</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>163353</td>\n",
       "      <td>243653</td>\n",
       "      <td>0.091829</td>\n",
       "      <td>0.002434</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>178980</td>\n",
       "      <td>214757</td>\n",
       "      <td>0.084444</td>\n",
       "      <td>0.000693</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>184167</td>\n",
       "      <td>288409</td>\n",
       "      <td>8.092106</td>\n",
       "      <td>0.001329</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>1.678472</td>\n",
       "      <td>47.845047</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay        age  \\\n",
       "0           6   107064      228232            3.672917  65.942297   \n",
       "1           7   118037      278444            0.267720   0.001779   \n",
       "2           7   118037      236754            0.739097   0.005868   \n",
       "3           3   145834      211552            6.064560  76.526792   \n",
       "4           9   150750      220597            5.323056  41.790228   \n",
       "5           8   159514      262299            1.075521   0.001438   \n",
       "6           2   163353      243653            0.091829   0.002434   \n",
       "7           5   178980      214757            0.084444   0.000693   \n",
       "8          10   184167      288409            8.092106   0.001329   \n",
       "9           4   185777      294638            1.678472  47.845047   \n",
       "\n",
       "   icustay_id_order  exclusion_los  exclusion_age  exclusion_first_stay  \\\n",
       "0                 1              0              0                     0   \n",
       "1                 1              1              1                     0   \n",
       "2                 2              1              1                     1   \n",
       "3                 1              0              0                     0   \n",
       "4                 1              0              0                     0   \n",
       "5                 1              1              1                     0   \n",
       "6                 1              1              1                     0   \n",
       "7                 1              1              1                     0   \n",
       "8                 1              0              1                     0   \n",
       "9                 1              1              0                     0   \n",
       "\n",
       "   exclusion_surgical  \n",
       "0                   1  \n",
       "1                   0  \n",
       "2                   0  \n",
       "3                   1  \n",
       "4                   0  \n",
       "5                   0  \n",
       "6                   0  \n",
       "7                   0  \n",
       "8                   0  \n",
       "9                   0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT icu.subject_id, icu.hadm_id, icu.icustay_id\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    ", EXTRACT('epoch' from icu.intime - pat.dob) / 60.0 / 60.0 / 24.0 / 365.242 as age\n",
    ", RANK() OVER (PARTITION BY icu.subject_id ORDER BY icu.intime) AS icustay_id_order\n",
    "FROM icustays icu\n",
    "INNER JOIN patients pat\n",
    "  ON icu.subject_id = pat.subject_id\n",
    "LIMIT 10\n",
    ")\n",
    ", serv AS\n",
    "(\n",
    "SELECT icu.hadm_id, icu.icustay_id, se.curr_service\n",
    ", CASE\n",
    "    WHEN curr_service like '%SURG' then 1\n",
    "    WHEN curr_service = 'ORTHO' then 1\n",
    "    ELSE 0 END\n",
    "  as surgical\n",
    ", RANK() OVER (PARTITION BY icu.hadm_id ORDER BY se.transfertime DESC) as rank\n",
    "FROM icustays icu\n",
    "LEFT JOIN services se\n",
    " ON icu.hadm_id = se.hadm_id\n",
    "AND se.transfertime < icu.intime + interval '12' hour\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , co.age\n",
    "  , co.icustay_id_order\n",
    "  \n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "    AS exclusion_los\n",
    "  , CASE\n",
    "        WHEN co.age < 16 then 1\n",
    "    ELSE 0 END\n",
    "    AS exclusion_age\n",
    "  , CASE \n",
    "        WHEN co.icustay_id_order != 1 THEN 1\n",
    "    ELSE 0 END \n",
    "    AS exclusion_first_stay\n",
    "  , CASE\n",
    "        WHEN serv.surgical = 1 THEN 1\n",
    "    ELSE 0 END\n",
    "    as exclusion_surgical\n",
    "FROM co\n",
    "LEFT JOIN serv\n",
    "  ON  co.icustay_id = serv.icustay_id\n",
    "  AND serv.rank = 1\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! You now have a cohort for which you can start extracting data.\n",
    "\n",
    "A common question asked is: why did we use the *services* table for identifying surgical patients, rather than the `first_careunit` column from the *icustays*? This is a very important concept in the MIMIC-III database: while patients may be cared for by the surgical service, they are **not necessarily in the surgical ICU**. These patients are called \"boarders\", and the reason why they are not in the usual ICU for their service is multifactorial. Let's take a look at some care units:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_length_of_stay</th>\n",
       "      <th>age</th>\n",
       "      <th>icustay_id_order</th>\n",
       "      <th>curr_service</th>\n",
       "      <th>first_careunit</th>\n",
       "      <th>exclusion_los</th>\n",
       "      <th>exclusion_age</th>\n",
       "      <th>exclusion_first_stay</th>\n",
       "      <th>exclusion_surgical</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>228232</td>\n",
       "      <td>3.672917</td>\n",
       "      <td>65.942297</td>\n",
       "      <td>1</td>\n",
       "      <td>SURG</td>\n",
       "      <td>SICU</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0.001779</td>\n",
       "      <td>1</td>\n",
       "      <td>NB</td>\n",
       "      <td>NICU</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>236754</td>\n",
       "      <td>0.739097</td>\n",
       "      <td>0.005868</td>\n",
       "      <td>2</td>\n",
       "      <td>NB</td>\n",
       "      <td>NICU</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>145834</td>\n",
       "      <td>211552</td>\n",
       "      <td>6.064560</td>\n",
       "      <td>76.526792</td>\n",
       "      <td>1</td>\n",
       "      <td>VSURG</td>\n",
       "      <td>MICU</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>150750</td>\n",
       "      <td>220597</td>\n",
       "      <td>5.323056</td>\n",
       "      <td>41.790228</td>\n",
       "      <td>1</td>\n",
       "      <td>NMED</td>\n",
       "      <td>MICU</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>159514</td>\n",
       "      <td>262299</td>\n",
       "      <td>1.075521</td>\n",
       "      <td>0.001438</td>\n",
       "      <td>1</td>\n",
       "      <td>NB</td>\n",
       "      <td>NICU</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>163353</td>\n",
       "      <td>243653</td>\n",
       "      <td>0.091829</td>\n",
       "      <td>0.002434</td>\n",
       "      <td>1</td>\n",
       "      <td>NB</td>\n",
       "      <td>NICU</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>178980</td>\n",
       "      <td>214757</td>\n",
       "      <td>0.084444</td>\n",
       "      <td>0.000693</td>\n",
       "      <td>1</td>\n",
       "      <td>NB</td>\n",
       "      <td>NICU</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>184167</td>\n",
       "      <td>288409</td>\n",
       "      <td>8.092106</td>\n",
       "      <td>0.001329</td>\n",
       "      <td>1</td>\n",
       "      <td>NB</td>\n",
       "      <td>NICU</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>1.678472</td>\n",
       "      <td>47.845047</td>\n",
       "      <td>1</td>\n",
       "      <td>MED</td>\n",
       "      <td>MICU</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id  icu_length_of_stay        age  \\\n",
       "0           6   107064      228232            3.672917  65.942297   \n",
       "1           7   118037      278444            0.267720   0.001779   \n",
       "2           7   118037      236754            0.739097   0.005868   \n",
       "3           3   145834      211552            6.064560  76.526792   \n",
       "4           9   150750      220597            5.323056  41.790228   \n",
       "5           8   159514      262299            1.075521   0.001438   \n",
       "6           2   163353      243653            0.091829   0.002434   \n",
       "7           5   178980      214757            0.084444   0.000693   \n",
       "8          10   184167      288409            8.092106   0.001329   \n",
       "9           4   185777      294638            1.678472  47.845047   \n",
       "\n",
       "   icustay_id_order curr_service first_careunit  exclusion_los  exclusion_age  \\\n",
       "0                 1         SURG           SICU              0              0   \n",
       "1                 1           NB           NICU              1              1   \n",
       "2                 2           NB           NICU              1              1   \n",
       "3                 1        VSURG           MICU              0              0   \n",
       "4                 1         NMED           MICU              0              0   \n",
       "5                 1           NB           NICU              1              1   \n",
       "6                 1           NB           NICU              1              1   \n",
       "7                 1           NB           NICU              1              1   \n",
       "8                 1           NB           NICU              0              1   \n",
       "9                 1          MED           MICU              1              0   \n",
       "\n",
       "   exclusion_first_stay  exclusion_surgical  \n",
       "0                     0                   1  \n",
       "1                     0                   0  \n",
       "2                     1                   0  \n",
       "3                     0                   1  \n",
       "4                     0                   0  \n",
       "5                     0                   0  \n",
       "6                     0                   0  \n",
       "7                     0                   0  \n",
       "8                     0                   0  \n",
       "9                     0                   0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = query_schema + \"\"\"\n",
    "WITH co AS\n",
    "(\n",
    "SELECT icu.subject_id, icu.hadm_id, icu.icustay_id, first_careunit\n",
    ", EXTRACT(EPOCH FROM outtime - intime)/60.0/60.0/24.0 as icu_length_of_stay\n",
    ", EXTRACT('epoch' from icu.intime - pat.dob) / 60.0 / 60.0 / 24.0 / 365.242 as age\n",
    ", RANK() OVER (PARTITION BY icu.subject_id ORDER BY icu.intime) AS icustay_id_order\n",
    "FROM icustays icu\n",
    "INNER JOIN patients pat\n",
    "  ON icu.subject_id = pat.subject_id\n",
    "LIMIT 10\n",
    ")\n",
    ", serv AS\n",
    "(\n",
    "SELECT icu.hadm_id, icu.icustay_id, se.curr_service\n",
    ", CASE\n",
    "    WHEN curr_service like '%SURG' then 1\n",
    "    WHEN curr_service = 'ORTHO' then 1\n",
    "    ELSE 0 END\n",
    "  as surgical\n",
    ", RANK() OVER (PARTITION BY icu.hadm_id ORDER BY se.transfertime DESC) as rank\n",
    "FROM icustays icu\n",
    "LEFT JOIN services se\n",
    " ON icu.hadm_id = se.hadm_id\n",
    "AND se.transfertime < icu.intime + interval '12' hour\n",
    ")\n",
    "SELECT\n",
    "  co.subject_id, co.hadm_id, co.icustay_id, co.icu_length_of_stay\n",
    "  , co.age\n",
    "  , co.icustay_id_order\n",
    "  , serv.curr_service\n",
    "  , co.first_careunit\n",
    "  , CASE\n",
    "        WHEN co.icu_length_of_stay < 2 then 1\n",
    "    ELSE 0 END\n",
    "    AS exclusion_los\n",
    "  , CASE\n",
    "        WHEN co.age < 16 then 1\n",
    "    ELSE 0 END\n",
    "    AS exclusion_age\n",
    "  , CASE \n",
    "        WHEN co.icustay_id_order != 1 THEN 1\n",
    "    ELSE 0 END \n",
    "    AS exclusion_first_stay\n",
    "  , CASE\n",
    "        WHEN serv.surgical = 1 THEN 1\n",
    "    ELSE 0 END\n",
    "    as exclusion_surgical\n",
    "FROM co\n",
    "LEFT JOIN serv\n",
    "  ON  co.icustay_id = serv.icustay_id\n",
    "  AND serv.rank = 1\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Without specifically looking for it, we have found an example in `icustay_id` 211552: they were admitted under the VSURG service, but admitted to a medical ICU (MICU). If we used the `first_careunit`, then we would undesirably include this \"boarder\" in our study.\n",
    "\n",
    "Let's summarize our exclusions by looking at some simple summary measures of the dataframe df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Observations            10\n",
      "exclusion_los            6 (60.00%)\n",
      "exclusion_age            6 (60.00%)\n",
      "exclusion_first_stay     1 (10.00%)\n",
      "exclusion_surgical       2 (20.00%)\n",
      "\n",
      "Total excluded           9 (90.00%)\n"
     ]
    }
   ],
   "source": [
    "print('{:20s} {:5d}'.format('Observations', df.shape[0]))\n",
    "idxExcl = np.zeros(df.shape[0],dtype=bool)\n",
    "for col in df.columns:\n",
    "    if \"exclusion_\" in col:\n",
    "        print('{:20s} {:5d} ({:2.2f}%)'.format(col, df[col].sum(), df[col].sum()*100.0/df.shape[0]))\n",
    "        idxExcl = (idxExcl) | (df[col]==1)\n",
    "\n",
    "# print a summary of how many were excluded in total\n",
    "print('')\n",
    "print('{:20s} {:5d} ({:2.2f}%)'.format('Total excluded', np.sum(idxExcl), np.sum(idxExcl)*100.0/df.shape[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, summarizing the exclusions is very simple because we have created this cohort table. With that, we conclude this tutorial on cohort selection. To recap, you have learned that:\n",
    "\n",
    "* best practice is to create a \"cohort\" table with a single row for every unique `icustay_id`, which is usually the identifier of interest\n",
    "* exclusions flags can be created based off rules, allowing easy prototyping, modification, and summarization later\n",
    "* when identifying the type of care provided, use the *services* table\n",
    "* read the docs, and don't make assumptions!\n",
    "\n",
    "Also, remember that when prototyping the `LIMIT` clause is very useful for speed gains, but don't forget to remove it once you want to test your code on all 60,000+ admissions :)\n",
    "\n",
    "Good luck in your analysis!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# close out the database connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!-- TODO: steal from hst-953 course -->"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}