cohort-selection.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:44.729511Z",
     "start_time": "2019-04-05T18:06:44.059501Z"
    }
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot')\n",
    "\n",
    "import pandas as pd\n",
    "import psycopg2\n",
    "import numpy as np\n",
    "\n",
    "from IPython.display import display, HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:44.737260Z",
     "start_time": "2019-04-05T18:06:44.731385Z"
    }
   },
   "outputs": [],
   "source": [
    "con = psycopg2.connect(dbname='mimic', user='sudarshan', host='/var/run/postgresql')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:47.702673Z",
     "start_time": "2019-04-05T18:06:47.693563Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select subject_id, hadm_id, icustay_id from icustays limit 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:47.947327Z",
     "start_time": "2019-04-05T18:06:47.874460Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_los_interval</th>\n",
       "      <th>icu_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 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_los_interval   icu_los\n",
       "0         268   110404      280836  3 days 05:58:33  280713.0\n",
       "1         269   106296      206613  3 days 06:41:28  283288.0\n",
       "2         270   188028      220345  2 days 21:27:09  250029.0\n",
       "3         271   173727      249196  2 days 01:26:22  177982.0\n",
       "4         272   164716      210407  1 days 14:53:09  139989.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select subject_id, hadm_id, icustay_id, outtime-intime as icu_los_interval, extract(epoch from outtime-intime) as icu_los from icustays limit 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below LOS is represented as *fractional days*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:48.210451Z",
     "start_time": "2019-04-05T18:06:48.193385Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_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",
       "    </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_los\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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select subject_id, hadm_id, icustay_id, extract(epoch from outtime-intime)/60.0/60.0/24.0 as icu_los from icustays limit 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:48.396183Z",
     "start_time": "2019-04-05T18:06:48.343720Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_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",
       "    </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_los\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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "with co as \n",
    "(\n",
    "    select subject_id, hadm_id, icustay_id, extract(epoch from outtime-intime)/60.0/60.0/24.0 as icu_los from icustays limit 10\n",
    ")\n",
    "select co.subject_id, co.hadm_id, co.icustay_id, co.icu_los from co where icu_los >= 2\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:48.511874Z",
     "start_time": "2019-04-05T18:06:48.497564Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_los</th>\n",
       "      <th>exlusion_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_los  exlusion_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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "with co as \n",
    "(\n",
    "    select subject_id, hadm_id, icustay_id, extract(epoch from outtime-intime)/60.0/60.0/24.0 as icu_los from icustays limit 10\n",
    ")\n",
    "select co.subject_id, co.hadm_id, co.icustay_id, co.icu_los,\n",
    "case when co.icu_los < 2 then 1\n",
    "else 0 end as exlusion_los\n",
    "from co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:48.745253Z",
     "start_time": "2019-04-05T18:06:48.650128Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_los</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_los                 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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "with co as \n",
    "(\n",
    "    select icu.subject_id, icu.hadm_id, icu.icustay_id, extract(epoch from outtime-intime)/60.0/60.0/24.0 as icu_los, icu.intime - pat.dob as age from icustays icu\n",
    "    inner join patients pat\n",
    "    on icu.subject_id = pat.subject_id limit 10\n",
    ")\n",
    "select co.subject_id, co.hadm_id, co.icustay_id, co.icu_los, co.age,\n",
    "case when co.icu_los < 2 then 1\n",
    "else 0 end as exclusion_los\n",
    "from co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:48.829815Z",
     "start_time": "2019-04-05T18:06:48.807464Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_los</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.002396</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.526773</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.000684</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.942238</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.001711</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.005818</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.001369</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.790215</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.001255</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_los                 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.002396           0.002434              1  \n",
       "1               0.0            76.526773          76.526792              0  \n",
       "2               0.0            47.844990          47.845047              1  \n",
       "3               0.0             0.000684           0.000693              1  \n",
       "4               0.0            65.942238          65.942297              0  \n",
       "5               0.0             0.001711           0.001779              1  \n",
       "6               0.0             0.005818           0.005868              1  \n",
       "7               0.0             0.001369           0.001438              1  \n",
       "8               0.0            41.790215          41.790228              0  \n",
       "9               0.0             0.001255           0.001329              0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "with co as \n",
    "(\n",
    "    select icu.subject_id, icu.hadm_id, icu.icustay_id, extract(epoch from outtime-intime)/60.0/60.0/24.0 as icu_los, icu.intime - pat.dob as age from icustays icu\n",
    "    inner join patients pat\n",
    "    on icu.subject_id = pat.subject_id limit 10\n",
    ")\n",
    "select co.subject_id, co.hadm_id, co.icustay_id, co.icu_los, co.age,\n",
    "extract('year' from co.age) as age_extract_year,\n",
    "extract('year' from co.age) + extract('months' from co.age)/12.0 + extract('days' from co.age)/365.242 + extract('hours' from co.age)/24.0/365.242 as age_extract_precise,\n",
    "extract('epoch' from co.age)/60.0/60.0/24.0/365.242 as age_extract_epoch,\n",
    "case when co.icu_los < 2 then 1\n",
    "else 0 end as exclusion_los\n",
    "from co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:48.976381Z",
     "start_time": "2019-04-05T18:06:48.961939Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_los</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_los        age  exclusion_los  \\\n",
       "0           2   163353      243653  0.091829   0.002434              1   \n",
       "1           3   145834      211552  6.064560  76.526792              0   \n",
       "2           4   185777      294638  1.678472  47.845047              1   \n",
       "3           5   178980      214757  0.084444   0.000693              1   \n",
       "4           6   107064      228232  3.672917  65.942297              0   \n",
       "5           7   118037      278444  0.267720   0.001779              1   \n",
       "6           7   118037      236754  0.739097   0.005868              1   \n",
       "7           8   159514      262299  1.075521   0.001438              1   \n",
       "8           9   150750      220597  5.323056  41.790228              0   \n",
       "9          10   184167      288409  8.092106   0.001329              0   \n",
       "\n",
       "   exclusion_age  \n",
       "0              1  \n",
       "1              0  \n",
       "2              0  \n",
       "3              1  \n",
       "4              0  \n",
       "5              1  \n",
       "6              1  \n",
       "7              1  \n",
       "8              0  \n",
       "9              1  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "with co as \n",
    "(\n",
    "    select icu.subject_id, icu.hadm_id, icu.icustay_id, extract(epoch from outtime-intime)/60.0/60.0/24.0 as icu_los, extract(epoch from icu.intime - pat.dob)/60.0/60.0/24.0/365.242 as age from icustays icu\n",
    "    inner join patients pat\n",
    "    on icu.subject_id = pat.subject_id limit 10\n",
    ")\n",
    "select co.subject_id, co.hadm_id, co.icustay_id, co.icu_los, co.age,\n",
    "case when co.icu_los < 2 then 1\n",
    "else 0 end as exclusion_los,\n",
    "case when co.age < 16 then 1\n",
    "else 0 end as exclusion_age\n",
    "from co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:49.236250Z",
     "start_time": "2019-04-05T18:06:49.115159Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_los</th>\n",
       "      <th>age</th>\n",
       "      <th>icustay_id_order</th>\n",
       "      <th>exclusion_los</th>\n",
       "      <th>exclusion_age</th>\n",
       "      <th>exlusion_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_los        age  icustay_id_order  \\\n",
       "0           2   163353      243653  0.091829   0.002434                 1   \n",
       "1           3   145834      211552  6.064560  76.526792                 1   \n",
       "2           4   185777      294638  1.678472  47.845047                 1   \n",
       "3           5   178980      214757  0.084444   0.000693                 1   \n",
       "4           6   107064      228232  3.672917  65.942297                 1   \n",
       "5           7   118037      278444  0.267720   0.001779                 1   \n",
       "6           7   118037      236754  0.739097   0.005868                 2   \n",
       "7           8   159514      262299  1.075521   0.001438                 1   \n",
       "8           9   150750      220597  5.323056  41.790228                 1   \n",
       "9          10   184167      288409  8.092106   0.001329                 1   \n",
       "\n",
       "   exclusion_los  exclusion_age  exlusion_first_stay  \n",
       "0              1              1                    0  \n",
       "1              0              0                    0  \n",
       "2              1              0                    0  \n",
       "3              1              1                    0  \n",
       "4              0              0                    0  \n",
       "5              1              1                    0  \n",
       "6              1              1                    1  \n",
       "7              1              1                    0  \n",
       "8              0              0                    0  \n",
       "9              0              1                    0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\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_los,\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 inner join patients pat\n",
    "    on icu.subject_id = pat.subject_id limit 10\n",
    ")\n",
    "select co.subject_id, co.hadm_id, co.icustay_id, co.icu_los, co.age, co.icustay_id_order,\n",
    "case when co.icu_los < 2 then 1\n",
    "else 0 end as exclusion_los,\n",
    "case when co.age < 16 then 1\n",
    "else 0 end as exclusion_age,\n",
    "case when co.icustay_id_order != 1 then 1\n",
    "else 0 end as exlusion_first_stay\n",
    "from co\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:49.315586Z",
     "start_time": "2019-04-05T18:06:49.253235Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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 = \"\"\"\n",
    "select hadm_id, curr_service,\n",
    "case when curr_service like '%SURG' then 1\n",
    "when curr_service = 'ORTHO' then 1\n",
    "else 0 end as surgical\n",
    "from services limit 10\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:49.450907Z",
     "start_time": "2019-04-05T18:06:49.424282Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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 = \"\"\"\n",
    "select icu.hadm_id, icu.icustay_id, se.curr_service,\n",
    "case when curr_service like '%SURG' then 1\n",
    "when curr_service = 'ORTHO' then 1\n",
    "else 0 end 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": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:49.629461Z",
     "start_time": "2019-04-05T18:06:49.560816Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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 = \"\"\"\n",
    "select icu.hadm_id, icu.icustay_id, se.curr_service,\n",
    "case when curr_service like '%SURG' then 1\n",
    "when curr_service = 'ORTHO' then 1\n",
    "else 0 end 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": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:49.821106Z",
     "start_time": "2019-04-05T18:06:49.729094Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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 = \"\"\"\n",
    "with serv as (\n",
    "select icu.hadm_id, icu.icustay_id, se.curr_service,\n",
    "case when curr_service like '%SURG' then 1\n",
    "when curr_service = 'ORTHO' then 1\n",
    "else 0 end as surgical,\n",
    "rank() over (partition by icu.hadm_id order by se.transfertime desc) as rank\n",
    "from icustays icu 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 from serv where rank = 1\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:50.533532Z",
     "start_time": "2019-04-05T18:06:49.863308Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>icu_los</th>\n",
       "      <th>age</th>\n",
       "      <th>icustay_id_order</th>\n",
       "      <th>exclusion_los</th>\n",
       "      <th>exclusion_age</th>\n",
       "      <th>exlusion_first_stay</th>\n",
       "      <th>exclusion_surgical</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",
       "      <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",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>185777</td>\n",
       "      <td>294638</td>\n",
       "      <td>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",
       "    <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",
       "      <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",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>118037</td>\n",
       "      <td>278444</td>\n",
       "      <td>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>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",
       "      <td>0</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",
       "      <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",
       "      <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",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id  icustay_id   icu_los        age  icustay_id_order  \\\n",
       "0           2   163353      243653  0.091829   0.002434                 1   \n",
       "1           3   145834      211552  6.064560  76.526792                 1   \n",
       "2           4   185777      294638  1.678472  47.845047                 1   \n",
       "3           5   178980      214757  0.084444   0.000693                 1   \n",
       "4           6   107064      228232  3.672917  65.942297                 1   \n",
       "5           7   118037      278444  0.267720   0.001779                 1   \n",
       "6           7   118037      236754  0.739097   0.005868                 2   \n",
       "7           8   159514      262299  1.075521   0.001438                 1   \n",
       "8           9   150750      220597  5.323056  41.790228                 1   \n",
       "9          10   184167      288409  8.092106   0.001329                 1   \n",
       "\n",
       "   exclusion_los  exclusion_age  exlusion_first_stay  exclusion_surgical  \n",
       "0              1              1                    0                   0  \n",
       "1              0              0                    0                   1  \n",
       "2              1              0                    0                   0  \n",
       "3              1              1                    0                   0  \n",
       "4              0              0                    0                   1  \n",
       "5              1              1                    0                   0  \n",
       "6              1              1                    1                   0  \n",
       "7              1              1                    0                   0  \n",
       "8              0              0                    0                   0  \n",
       "9              0              1                    0                   0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\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_los,\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 inner join patients pat\n",
    "    on icu.subject_id = pat.subject_id\n",
    "),\n",
    "serv as (\n",
    "select icu.hadm_id, icu.icustay_id, se.curr_service,\n",
    "case when curr_service like '%SURG' then 1\n",
    "when curr_service = 'ORTHO' then 1\n",
    "else 0 end as surgical,\n",
    "rank() over (partition by icu.hadm_id order by se.transfertime desc) as rank\n",
    "from icustays icu left join services se\n",
    "on icu.hadm_id = se.hadm_id\n",
    "and se.transfertime < icu.intime + interval '12' hour\n",
    ")\n",
    "select co.subject_id, co.hadm_id, co.icustay_id, co.icu_los, co.age, co.icustay_id_order,\n",
    "case when co.icu_los < 2 then 1\n",
    "else 0 end as exclusion_los,\n",
    "case when co.age < 16 then 1\n",
    "else 0 end as exclusion_age,\n",
    "case when co.icustay_id_order != 1 then 1\n",
    "else 0 end as exlusion_first_stay,\n",
    "case when serv.surgical = 1 then 1\n",
    "else 0 end as exclusion_surgical\n",
    "from co\n",
    "left join serv on co.icustay_id = serv.icustay_id and serv.rank = 1\n",
    "\"\"\"\n",
    "df = pd.read_sql_query(query, con)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:50.548606Z",
     "start_time": "2019-04-05T18:06:50.535478Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Observations         61534\n",
      "exclusion_los        29213 (47.47%)\n",
      "exclusion_age         8109 (13.18%)\n",
      "exclusion_surgical   18225 (29.62%)\n",
      "\n",
      "Total excluded       42945 (69.79%)\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": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-05T18:06:50.569914Z",
     "start_time": "2019-04-05T18:06:50.550558Z"
    }
   },
   "outputs": [],
   "source": [
    "con.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}