02-demographics-and-severity-of-illness.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# eICU Collaborative Research Database\n",
    "\n",
    "# Notebook 2: Demographics and severity of illness in a single patient\n",
    "\n",
    "The aim of this notebook is to introduce high level admission details relating to a single patient stay, using the following tables:\n",
    "\n",
    "- `patient`\n",
    "- `admissiondx`\n",
    "- `apacheapsvar`\n",
    "- `apachepredvar`\n",
    "- `apachepatientresult`\n",
    "\n",
    "Before starting, you will need to copy the eicu demo database file ('eicu_demo.sqlite3') to the `data` directory.\n",
    "\n",
    "Documentation on the eICU Collaborative Research Database can be found at: http://eicu-crd.mit.edu/."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Getting set up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import sqlite3\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Plot settings\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot')\n",
    "fontsize = 20 # size for x and y ticks\n",
    "plt.rcParams['legend.fontsize'] = fontsize\n",
    "plt.rcParams.update({'font.size': fontsize})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to the database\n",
    "fn = os.path.join('data','eicu_demo.sqlite3')\n",
    "con = sqlite3.connect(fn)\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Display a list of tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \\\n",
    "\"\"\"\n",
    "SELECT type, name\n",
    "FROM sqlite_master \n",
    "WHERE type='table'\n",
    "ORDER BY name;\n",
    "\"\"\"\n",
    "\n",
    "list_of_tables = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>table</td>\n",
       "      <td>admissiondrug</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>table</td>\n",
       "      <td>admissiondx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>table</td>\n",
       "      <td>apacheapsvar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>table</td>\n",
       "      <td>apachepatientresult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>table</td>\n",
       "      <td>apachepredvar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>table</td>\n",
       "      <td>careplancareprovider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>table</td>\n",
       "      <td>careplaneol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>table</td>\n",
       "      <td>careplangeneral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>table</td>\n",
       "      <td>careplangoal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>table</td>\n",
       "      <td>careplaninfectiousdisease</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>table</td>\n",
       "      <td>diagnosis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>table</td>\n",
       "      <td>infusiondrug</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>table</td>\n",
       "      <td>lab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>table</td>\n",
       "      <td>medication</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>table</td>\n",
       "      <td>pasthistory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>table</td>\n",
       "      <td>patient</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>table</td>\n",
       "      <td>treatment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>table</td>\n",
       "      <td>vitalaperiodic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>table</td>\n",
       "      <td>vitalperiodic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     type                       name\n",
       "0   table              admissiondrug\n",
       "1   table                admissiondx\n",
       "2   table               apacheapsvar\n",
       "3   table        apachepatientresult\n",
       "4   table              apachepredvar\n",
       "5   table       careplancareprovider\n",
       "6   table                careplaneol\n",
       "7   table            careplangeneral\n",
       "8   table               careplangoal\n",
       "9   table  careplaninfectiousdisease\n",
       "10  table                  diagnosis\n",
       "11  table               infusiondrug\n",
       "12  table                        lab\n",
       "13  table                 medication\n",
       "14  table                pasthistory\n",
       "15  table                    patient\n",
       "16  table                  treatment\n",
       "17  table             vitalaperiodic\n",
       "18  table              vitalperiodic"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_of_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Selecting a single patient stay "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1. The `patient` table\n",
    "\n",
    "The `patient` table includes general information about the patient admissions (for example, demographics, admission and discharge details). See: http://eicu-crd.mit.edu/eicutables/patient/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "Use your knowledge from the previous notebook and the online documentation (http://eicu-crd.mit.edu/) to answer the following questions:\n",
    "\n",
    "- Which column in the `patient` table is distinct for each stay in the ICU (similar to `icustay_id` in MIMIC-III)?\n",
    "- Which column is unique for each patient (similar to `subject_id` in MIMIC-III)?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# select a single ICU stay\n",
    "patientunitstayid = 141296"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT *\n",
      "FROM patient\n",
      "WHERE patientunitstayid = 141296\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM patient\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>patientunitstayid</th>\n",
       "      <td>141296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>patienthealthsystemstayid</th>\n",
       "      <td>129026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ethnicity</th>\n",
       "      <td>Caucasian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitalid</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wardid</th>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>apacheadmissiondx</th>\n",
       "      <td>Hypovolemia (including dehydration, Do not inc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admissionheight</th>\n",
       "      <td>162.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaladmityear</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaladmittime24</th>\n",
       "      <td>04:18:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaladmittime</th>\n",
       "      <td>morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaladmitoffset</th>\n",
       "      <td>-2254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaladmitsource</th>\n",
       "      <td>Floor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaldischargeyear</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaldischargetime24</th>\n",
       "      <td>03:41:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaldischargetime</th>\n",
       "      <td>morning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaldischargeoffset</th>\n",
       "      <td>7789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaldischargelocation</th>\n",
       "      <td>Death</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hospitaldischargestatus</th>\n",
       "      <td>Expired</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unittype</th>\n",
       "      <td>SICU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitadmityear</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitadmittime24</th>\n",
       "      <td>17:52:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitadmittime</th>\n",
       "      <td>evening</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitadmitsource</th>\n",
       "      <td>Floor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitvisitnumber</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitstaytype</th>\n",
       "      <td>admit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admissionweight</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dischargeweight</th>\n",
       "      <td>76.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitdischargeyear</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitdischargetime24</th>\n",
       "      <td>17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitdischargetime</th>\n",
       "      <td>evening</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitdischargeoffset</th>\n",
       "      <td>2828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitdischargelocation</th>\n",
       "      <td>Floor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unitdischargestatus</th>\n",
       "      <td>Alive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uniquepid</th>\n",
       "      <td>002-30269</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                           0\n",
       "patientunitstayid                                                     141296\n",
       "patienthealthsystemstayid                                             129026\n",
       "gender                                                                  Male\n",
       "age                                                                       63\n",
       "ethnicity                                                          Caucasian\n",
       "hospitalid                                                                73\n",
       "wardid                                                                    85\n",
       "apacheadmissiondx          Hypovolemia (including dehydration, Do not inc...\n",
       "admissionheight                                                        162.6\n",
       "hospitaladmityear                                                       2014\n",
       "hospitaladmittime24                                                 04:18:00\n",
       "hospitaladmittime                                                    morning\n",
       "hospitaladmitoffset                                                    -2254\n",
       "hospitaladmitsource                                                    Floor\n",
       "hospitaldischargeyear                                                   2014\n",
       "hospitaldischargetime24                                             03:41:00\n",
       "hospitaldischargetime                                                morning\n",
       "hospitaldischargeoffset                                                 7789\n",
       "hospitaldischargelocation                                              Death\n",
       "hospitaldischargestatus                                              Expired\n",
       "unittype                                                                SICU\n",
       "unitadmityear                                                           2014\n",
       "unitadmittime24                                                     17:52:00\n",
       "unitadmittime                                                        evening\n",
       "unitadmitsource                                                        Floor\n",
       "unitvisitnumber                                                            1\n",
       "unitstaytype                                                           admit\n",
       "admissionweight                                                         None\n",
       "dischargeweight                                                         76.8\n",
       "unitdischargeyear                                                       2014\n",
       "unitdischargetime24                                                 17:00:00\n",
       "unitdischargetime                                                    evening\n",
       "unitdischargeoffset                                                     2828\n",
       "unitdischargelocation                                                  Floor\n",
       "unitdischargestatus                                                    Alive\n",
       "uniquepid                                                          002-30269"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# run the query and assign the output to a variable\n",
    "patient = pd.read_sql_query(query,con)\n",
    "patient.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'patientunitstayid', u'patienthealthsystemstayid', u'gender', u'age',\n",
       "       u'ethnicity', u'hospitalid', u'wardid', u'apacheadmissiondx',\n",
       "       u'admissionheight', u'hospitaladmityear', u'hospitaladmittime24',\n",
       "       u'hospitaladmittime', u'hospitaladmitoffset', u'hospitaladmitsource',\n",
       "       u'hospitaldischargeyear', u'hospitaldischargetime24',\n",
       "       u'hospitaldischargetime', u'hospitaldischargeoffset',\n",
       "       u'hospitaldischargelocation', u'hospitaldischargestatus', u'unittype',\n",
       "       u'unitadmityear', u'unitadmittime24', u'unitadmittime',\n",
       "       u'unitadmitsource', u'unitvisitnumber', u'unitstaytype',\n",
       "       u'admissionweight', u'dischargeweight', u'unitdischargeyear',\n",
       "       u'unitdischargetime24', u'unitdischargetime', u'unitdischargeoffset',\n",
       "       u'unitdischargelocation', u'unitdischargestatus', u'uniquepid'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# display a complete list of columns\n",
    "patient.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>uniquepid</th>\n",
       "      <th>patientunitstayid</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>unitdischargestatus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>002-30269</td>\n",
       "      <td>141296</td>\n",
       "      <td>Male</td>\n",
       "      <td>63</td>\n",
       "      <td>Alive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uniquepid  patientunitstayid gender age unitdischargestatus\n",
       "0  002-30269             141296   Male  63               Alive"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select a limited number of columns to view\n",
    "columns = ['uniquepid','patientunitstayid','gender','age','unitdischargestatus']\n",
    "patient[columns]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What year was the patient admitted to the ICU? Which year was he or she discharged?\n",
    "- What was the status of the patient upon discharge from the unit?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 3.2. The `admissiondx` table\n",
    "\n",
    "The `admissiondx` table contains the primary diagnosis for admission to the ICU according to the APACHE scoring criteria. For more detail, see: http://eicu-crd.mit.edu/eicutables/admissiondx/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT *\n",
      "FROM admissiondx\n",
      "WHERE patientunitstayid = 141296\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM admissiondx\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>patientunitstayid</th>\n",
       "      <td>141296</td>\n",
       "      <td>141296</td>\n",
       "      <td>141296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admissiondxid</th>\n",
       "      <td>659124</td>\n",
       "      <td>659125</td>\n",
       "      <td>659123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdxenteredyear</th>\n",
       "      <td>2014</td>\n",
       "      <td>2014</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdxenteredtime24</th>\n",
       "      <td>18:20:38</td>\n",
       "      <td>18:20:38</td>\n",
       "      <td>18:20:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdxenteredtime</th>\n",
       "      <td>night</td>\n",
       "      <td>night</td>\n",
       "      <td>night</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdxenteredoffset</th>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdxpath</th>\n",
       "      <td>admission diagnosis|Non-operative Organ System...</td>\n",
       "      <td>admission diagnosis|All Diagnosis|Non-operativ...</td>\n",
       "      <td>admission diagnosis|Was the patient admitted f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdxname</th>\n",
       "      <td>Cardiovascular</td>\n",
       "      <td>Hypovolemia (including dehydration, Do not inc...</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdxtext</th>\n",
       "      <td>Cardiovascular</td>\n",
       "      <td>Hypovolemia (including dehydration, Do not inc...</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                      0  \\\n",
       "patientunitstayid                                                141296   \n",
       "admissiondxid                                                    659124   \n",
       "admitdxenteredyear                                                 2014   \n",
       "admitdxenteredtime24                                           18:20:38   \n",
       "admitdxenteredtime                                                night   \n",
       "admitdxenteredoffset                                                 28   \n",
       "admitdxpath           admission diagnosis|Non-operative Organ System...   \n",
       "admitdxname                                              Cardiovascular   \n",
       "admitdxtext                                              Cardiovascular   \n",
       "\n",
       "                                                                      1  \\\n",
       "patientunitstayid                                                141296   \n",
       "admissiondxid                                                    659125   \n",
       "admitdxenteredyear                                                 2014   \n",
       "admitdxenteredtime24                                           18:20:38   \n",
       "admitdxenteredtime                                                night   \n",
       "admitdxenteredoffset                                                 28   \n",
       "admitdxpath           admission diagnosis|All Diagnosis|Non-operativ...   \n",
       "admitdxname           Hypovolemia (including dehydration, Do not inc...   \n",
       "admitdxtext           Hypovolemia (including dehydration, Do not inc...   \n",
       "\n",
       "                                                                      2  \n",
       "patientunitstayid                                                141296  \n",
       "admissiondxid                                                    659123  \n",
       "admitdxenteredyear                                                 2014  \n",
       "admitdxenteredtime24                                           18:20:38  \n",
       "admitdxenteredtime                                                night  \n",
       "admitdxenteredoffset                                                 28  \n",
       "admitdxpath           admission diagnosis|Was the patient admitted f...  \n",
       "admitdxname                                                          No  \n",
       "admitdxtext                                                          No  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# run the query and assign the output to a variable\n",
    "admissiondx = pd.read_sql_query(query,con)\n",
    "admissiondx.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'patientunitstayid', u'admissiondxid', u'admitdxenteredyear',\n",
       "       u'admitdxenteredtime24', u'admitdxenteredtime', u'admitdxenteredoffset',\n",
       "       u'admitdxpath', u'admitdxname', u'admitdxtext'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "admissiondx.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What was the primary reason for admission?\n",
    "- How soon after admission to the ICU was the diagnoses recorded in eCareManager?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 3.3. The `apacheapsvar` table\n",
    "\n",
    "The `apacheapsvar` table contains the variables used to calculate the Acute Physiology Score (APS) III for patients. APS-III is an established method of summarizing patient severity of illness on admission to the ICU. \n",
    "\n",
    "The score is part of the Acute Physiology Age Chronic Health Evaluation (APACHE) system of equations for predicting outcomes for ICU patients. See: http://eicu-crd.mit.edu/eicutables/apacheApsVar/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT *\n",
      "FROM apacheapsvar\n",
      "WHERE patientunitstayid = 141296\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM apacheapsvar\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>patientunitstayid</th>\n",
       "      <td>141296.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>apacheapsvarid</th>\n",
       "      <td>22095.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>intubated</th>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vent</th>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dialysis</th>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eyes</th>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>motor</th>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>verbal</th>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meds</th>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>urine</th>\n",
       "      <td>-1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wbc</th>\n",
       "      <td>13.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>temperature</th>\n",
       "      <td>36.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>respiratoryrate</th>\n",
       "      <td>59.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sodium</th>\n",
       "      <td>137.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>heartrate</th>\n",
       "      <td>136.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meanbp</th>\n",
       "      <td>125.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ph</th>\n",
       "      <td>7.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hematocrit</th>\n",
       "      <td>29.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>creatinine</th>\n",
       "      <td>4.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>albumin</th>\n",
       "      <td>1.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pao2</th>\n",
       "      <td>54.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pco2</th>\n",
       "      <td>32.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bun</th>\n",
       "      <td>35.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>glucose</th>\n",
       "      <td>101.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bilirubin</th>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fio2</th>\n",
       "      <td>21.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0\n",
       "patientunitstayid  141296.00\n",
       "apacheapsvarid      22095.00\n",
       "intubated               0.00\n",
       "vent                    0.00\n",
       "dialysis                1.00\n",
       "eyes                    4.00\n",
       "motor                   6.00\n",
       "verbal                  5.00\n",
       "meds                    0.00\n",
       "urine                  -1.00\n",
       "wbc                    13.20\n",
       "temperature            36.70\n",
       "respiratoryrate        59.00\n",
       "sodium                137.00\n",
       "heartrate             136.00\n",
       "meanbp                125.00\n",
       "ph                      7.54\n",
       "hematocrit             29.00\n",
       "creatinine              4.33\n",
       "albumin                 1.90\n",
       "pao2                   54.00\n",
       "pco2                   32.00\n",
       "bun                    35.00\n",
       "glucose               101.00\n",
       "bilirubin               0.50\n",
       "fio2                   21.00"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# run the query and assign the output to a variable\n",
    "apacheapsvar = pd.read_sql_query(query,con)\n",
    "apacheapsvar.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "apacheapsvar.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What was the 'worst' heart rate recorded for the patient during the scoring period?\n",
    "- Was the patient oriented and able to converse normally on the day of admission? (hint: the `verbal` element refers to the Glasgow Coma Scale).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4. The `apachepredvar` table\n",
    "\n",
    "The `apachepredvar` table provides variables underlying the APACHE predictions. Acute Physiology Age Chronic Health Evaluation (APACHE) consists of a groups of equations used for predicting outcomes in critically ill patients. See: http://eicu-crd.mit.edu/eicutables/apachePredVar/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT *\n",
      "FROM apachepredvar\n",
      "WHERE patientunitstayid = 141296\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM apachepredvar\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>patientunitstayid</th>\n",
       "      <td>141296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>apachepredvarid</th>\n",
       "      <td>3980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sicuday</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>saps3day1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>saps3today</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>saps3yesterday</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>teachtype</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bedcount</th>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitsource</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>graftcount</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meds</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>verbal</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>motor</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eyes</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admitdiagnosis</th>\n",
       "      <td>HYPOVOLEM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>thrombolytics</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>diedinhospital</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aids</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hepaticfailure</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lymphoma</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>metastaticcancer</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>leukemia</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>immunosuppression</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cirrhosis</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>electivesurgery</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>activetx</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>readmit</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ima</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>midur</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ventday1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>oobventday1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>oobintubday1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>diabetes</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>managementsystem</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>var03hspxlos</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pao2</th>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fio2</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ejectfx</th>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>creatinine</th>\n",
       "      <td>4.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dischargelocation</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>visitnumber</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>amilocation</th>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day1meds</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day1verbal</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day1motor</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day1eyes</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day1pao2</th>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day1fio2</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0\n",
       "patientunitstayid     141296\n",
       "apachepredvarid         3980\n",
       "sicuday                    1\n",
       "saps3day1                  0\n",
       "saps3today                 0\n",
       "saps3yesterday             0\n",
       "gender                     0\n",
       "teachtype                  0\n",
       "region                     3\n",
       "bedcount                  28\n",
       "admitsource                4\n",
       "graftcount                 3\n",
       "meds                       0\n",
       "verbal                     5\n",
       "motor                      6\n",
       "eyes                       4\n",
       "age                       63\n",
       "admitdiagnosis     HYPOVOLEM\n",
       "thrombolytics              0\n",
       "diedinhospital             1\n",
       "aids                       0\n",
       "hepaticfailure             0\n",
       "lymphoma                   0\n",
       "metastaticcancer           0\n",
       "leukemia                   0\n",
       "immunosuppression          1\n",
       "cirrhosis                  0\n",
       "electivesurgery         None\n",
       "activetx                   0\n",
       "readmit                    0\n",
       "ima                        0\n",
       "midur                      0\n",
       "ventday1                   0\n",
       "oobventday1                0\n",
       "oobintubday1               0\n",
       "diabetes                   0\n",
       "managementsystem           1\n",
       "var03hspxlos               0\n",
       "pao2                      54\n",
       "fio2                      21\n",
       "ejectfx                   -1\n",
       "creatinine              4.33\n",
       "dischargelocation          4\n",
       "visitnumber                1\n",
       "amilocation               -1\n",
       "day1meds                   0\n",
       "day1verbal                 5\n",
       "day1motor                  6\n",
       "day1eyes                   4\n",
       "day1pao2                  54\n",
       "day1fio2                  21"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# run the query and assign the output to a variable\n",
    "apachepredvar = pd.read_sql_query(query,con)\n",
    "apachepredvar.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'patientunitstayid', u'apachepredvarid', u'sicuday', u'saps3day1',\n",
       "       u'saps3today', u'saps3yesterday', u'gender', u'teachtype', u'region',\n",
       "       u'bedcount', u'admitsource', u'graftcount', u'meds', u'verbal',\n",
       "       u'motor', u'eyes', u'age', u'admitdiagnosis', u'thrombolytics',\n",
       "       u'diedinhospital', u'aids', u'hepaticfailure', u'lymphoma',\n",
       "       u'metastaticcancer', u'leukemia', u'immunosuppression', u'cirrhosis',\n",
       "       u'electivesurgery', u'activetx', u'readmit', u'ima', u'midur',\n",
       "       u'ventday1', u'oobventday1', u'oobintubday1', u'diabetes',\n",
       "       u'managementsystem', u'var03hspxlos', u'pao2', u'fio2', u'ejectfx',\n",
       "       u'creatinine', u'dischargelocation', u'visitnumber', u'amilocation',\n",
       "       u'day1meds', u'day1verbal', u'day1motor', u'day1eyes', u'day1pao2',\n",
       "       u'day1fio2'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "apachepredvar.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "apachepredvar.ventday1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- Was the patient ventilated during (APACHE) day 1 of their stay?\n",
    "- Did the patient have diabetes?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 3.5. The `apachepatientresult` table\n",
    "\n",
    "The `apachepatientresult` table provides predictions made by the APACHE score (versions IV and IVa), including probability of mortality, length of stay, and ventilation days. See: http://eicu-crd.mit.edu/eicutables/apachePatientResult/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT *\n",
      "FROM apachepatientresult\n",
      "WHERE patientunitstayid = 141296\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM apachepatientresult\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>patientunitstayid</th>\n",
       "      <td>141296</td>\n",
       "      <td>141296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>apachepatientresultsid</th>\n",
       "      <td>42514</td>\n",
       "      <td>42513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>physicianspeciality</th>\n",
       "      <td>critical care medicine (CCM)</td>\n",
       "      <td>critical care medicine (CCM)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>physicianinterventioncategory</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>acutephysiologyscore</th>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>apachescore</th>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>apacheversion</th>\n",
       "      <td>IVa</td>\n",
       "      <td>IV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>predictedicumortality</th>\n",
       "      <td>0.12084500021300711</td>\n",
       "      <td>0.1139538682077893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>actualicumortality</th>\n",
       "      <td>ALIVE</td>\n",
       "      <td>ALIVE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>predictediculos</th>\n",
       "      <td>4.84375</td>\n",
       "      <td>4.72517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>actualiculos</th>\n",
       "      <td>1.9638</td>\n",
       "      <td>1.9638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>predictedhospitalmortality</th>\n",
       "      <td>0.25711724536512859</td>\n",
       "      <td>0.22655498624812659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>actualhospitalmortality</th>\n",
       "      <td>EXPIRED</td>\n",
       "      <td>EXPIRED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>predictedhospitallos</th>\n",
       "      <td>15.115</td>\n",
       "      <td>15.3914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>actualhospitallos</th>\n",
       "      <td>6.9743</td>\n",
       "      <td>6.9743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>preopmi</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>preopcardiaccath</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ptcawithin24h</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unabridgedunitlos</th>\n",
       "      <td>1.9638</td>\n",
       "      <td>1.9638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unabridgedhosplos</th>\n",
       "      <td>6.9743</td>\n",
       "      <td>6.9743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>actualventdays</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>predventdays</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unabridgedactualventdays</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          0  \\\n",
       "patientunitstayid                                    141296   \n",
       "apachepatientresultsid                                42514   \n",
       "physicianspeciality            critical care medicine (CCM)   \n",
       "physicianinterventioncategory                       Unknown   \n",
       "acutephysiologyscore                                     68   \n",
       "apachescore                                              89   \n",
       "apacheversion                                           IVa   \n",
       "predictedicumortality                   0.12084500021300711   \n",
       "actualicumortality                                    ALIVE   \n",
       "predictediculos                                     4.84375   \n",
       "actualiculos                                         1.9638   \n",
       "predictedhospitalmortality              0.25711724536512859   \n",
       "actualhospitalmortality                             EXPIRED   \n",
       "predictedhospitallos                                 15.115   \n",
       "actualhospitallos                                    6.9743   \n",
       "preopmi                                                   0   \n",
       "preopcardiaccath                                          0   \n",
       "ptcawithin24h                                             0   \n",
       "unabridgedunitlos                                    1.9638   \n",
       "unabridgedhosplos                                    6.9743   \n",
       "actualventdays                                         None   \n",
       "predventdays                                           None   \n",
       "unabridgedactualventdays                               None   \n",
       "\n",
       "                                                          1  \n",
       "patientunitstayid                                    141296  \n",
       "apachepatientresultsid                                42513  \n",
       "physicianspeciality            critical care medicine (CCM)  \n",
       "physicianinterventioncategory                       Unknown  \n",
       "acutephysiologyscore                                     68  \n",
       "apachescore                                              89  \n",
       "apacheversion                                            IV  \n",
       "predictedicumortality                    0.1139538682077893  \n",
       "actualicumortality                                    ALIVE  \n",
       "predictediculos                                     4.72517  \n",
       "actualiculos                                         1.9638  \n",
       "predictedhospitalmortality              0.22655498624812659  \n",
       "actualhospitalmortality                             EXPIRED  \n",
       "predictedhospitallos                                15.3914  \n",
       "actualhospitallos                                    6.9743  \n",
       "preopmi                                                   0  \n",
       "preopcardiaccath                                          0  \n",
       "ptcawithin24h                                             0  \n",
       "unabridgedunitlos                                    1.9638  \n",
       "unabridgedhosplos                                    6.9743  \n",
       "actualventdays                                         None  \n",
       "predventdays                                           None  \n",
       "unabridgedactualventdays                               None  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# run the query and assign the output to a variable\n",
    "apachepatientresult = pd.read_sql_query(query,con)\n",
    "apachepatientresult.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "apachepatientresult.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What versions of the APACHE score are computed?\n",
    "- How many days during the stay was the patient ventilated?\n",
    "- How long was the patient predicted to stay in hospital?\n",
    "- Was this prediction close to the truth?"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}