mimic-iii/notebooks/emergency-department-exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Emergency department\n",
    "\n",
    "Many patients admitted to the intensive care unit (ICU) originally present in the emergency department (ED). Information on care provided is not directly a part of MIMIC-III, though there are some pieces of information about patient ED stays which are available. Information available includes: (1) admission information (2) (infrequently) fluids/treatment received."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import psycopg2\n",
    "from IPython.display import display, HTML # used to print out pretty pandas dataframes\n",
    "import matplotlib.dates as dates\n",
    "import matplotlib.lines as mlines\n",
    "\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot') \n",
    "\n",
    "# specify user/password/where the database is\n",
    "sqluser = 'postgres'\n",
    "sqlpass = 'postgres'\n",
    "dbname = 'mimic'\n",
    "schema_name = 'mimiciii'\n",
    "host = 'localhost'\n",
    "\n",
    "query_schema = 'SET search_path to ' + schema_name + ';'\n",
    "\n",
    "# connect to the database\n",
    "con = psycopg2.connect(dbname=dbname, user=sqluser, password=sqlpass, host=host)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "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>ed-registered</th>\n",
       "      <th>ed-out</th>\n",
       "      <th>admission_location</th>\n",
       "      <th>hosp-admit</th>\n",
       "      <th>hosp-disch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>04-09 10:06</td>\n",
       "      <td>04-09 13:24</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>04-09 12:26</td>\n",
       "      <td>04-10 15:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>09-03 07:15</td>\n",
       "      <td>09-08 19:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>10-18 19:34</td>\n",
       "      <td>10-25 14:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>06-06 16:14</td>\n",
       "      <td>06-09 12:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-02 01:01</td>\n",
       "      <td>11-02 04:27</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>11-02 02:06</td>\n",
       "      <td>11-05 14:55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ed-registered       ed-out         admission_location   hosp-admit  \\\n",
       "0   04-09 10:06  04-09 13:24       EMERGENCY ROOM ADMIT  04-09 12:26   \n",
       "1                             PHYS REFERRAL/NORMAL DELI  09-03 07:15   \n",
       "2                             TRANSFER FROM HOSP/EXTRAM  10-18 19:34   \n",
       "3                             TRANSFER FROM HOSP/EXTRAM  06-06 16:14   \n",
       "4   11-02 01:01  11-02 04:27       EMERGENCY ROOM ADMIT  11-02 02:06   \n",
       "\n",
       "    hosp-disch  \n",
       "0  04-10 15:54  \n",
       "1  09-08 19:10  \n",
       "2  10-25 14:00  \n",
       "3  06-09 12:48  \n",
       "4  11-05 14:55  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load the admissions table from the local database\n",
    "query = query_schema + 'select * from admissions'\n",
    "df = pd.read_sql_query(query,con)\n",
    "\n",
    "# define a function to only output human readable dates\n",
    "def pretty_dates(x):\n",
    "    if 'NaTType' in str(type(x)):\n",
    "        return ''\n",
    "    else:\n",
    "        return x.strftime('%m-%d %H:%M')\n",
    "\n",
    "df['ed-registered'] = df['edregtime'].apply(pretty_dates)\n",
    "df['ed-out'] = df['edouttime'].apply(pretty_dates)\n",
    "\n",
    "df['hosp-admit'] = df['admittime'].apply(pretty_dates)\n",
    "df['hosp-disch'] = df['dischtime'].apply(pretty_dates)\n",
    "\n",
    "# look at the top 5 rows for the table\n",
    "df[['ed-registered','ed-out','admission_location','hosp-admit','hosp-disch']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above we can see how the emergency department (ED) times in the admissions table work. Let's look at the top row (index 0): the patient is registered in the ED at 10:06, and is recorded as leaving the ED at 13:24. During this time they have been admitted to the hospital (`admittime` of 12:26). While it seems odd to be in the ED and the hospital at the same time, keep in mind that the ED and the hospital are operating on different systems, and `admittime` is an administrative database, not a real-time GPS tracker!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "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>edregtime</th>\n",
       "      <th>edouttime</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>admission_location</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>EMERGENCY ROOM ADMIT</th>\n",
       "      <td>22437</td>\n",
       "      <td>22437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLINIC REFERRAL/PREMATURE</th>\n",
       "      <td>7932</td>\n",
       "      <td>7932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRANSFER FROM HOSP/EXTRAM</th>\n",
       "      <td>196</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRANSFER FROM SKILLED NUR</th>\n",
       "      <td>192</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PHYS REFERRAL/NORMAL DELI</th>\n",
       "      <td>103</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRANSFER FROM OTHER HEALT</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>** INFO NOT AVAILABLE **</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HMO REFERRAL/SICK</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRSF WITHIN THIS FACILITY</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           edregtime  edouttime\n",
       "admission_location                             \n",
       "EMERGENCY ROOM ADMIT           22437      22437\n",
       "CLINIC REFERRAL/PREMATURE       7932       7932\n",
       "TRANSFER FROM HOSP/EXTRAM        196        196\n",
       "TRANSFER FROM SKILLED NUR        192        192\n",
       "PHYS REFERRAL/NORMAL DELI        103        103\n",
       "TRANSFER FROM OTHER HEALT         14         14\n",
       "** INFO NOT AVAILABLE **           2          2\n",
       "HMO REFERRAL/SICK                  1          1\n",
       "TRSF WITHIN THIS FACILITY          0          0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('admission_location')[['edregtime','edouttime']].count().sort_values('edregtime', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the above group, we can see that most patients who go through the ED are direct admissions, but sometimes clinical referrals and transfers also go via the ED."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prescriptions\n",
    "\n",
    "@msjoding asks:\n",
    "\n",
    "> I see that the PRESCRIPTIONS table includes meds that were ordered outside of ICU stays. Does this table also include any medications that were ordered in the emergency department just prior to the hospital admission?\n",
    "\n",
    "We can investigate this by cross-referencing hospital admission times with prescriptions. However, since prescriptions are only available with a date, we need to isolate to admissions where the patient was in the ED for at least a calendar day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load entire prescriptions table into memory\n",
    "query = query_schema + 'select * from prescriptions'\n",
    "pr = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "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>ed-registered</th>\n",
       "      <th>ed-out</th>\n",
       "      <th>admission_location</th>\n",
       "      <th>hosp-admit</th>\n",
       "      <th>hosp-disch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>12-15 21:10</td>\n",
       "      <td>12-16 22:05</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>12-16 19:48</td>\n",
       "      <td>12-22 16:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>05-17 15:27</td>\n",
       "      <td>05-18 11:50</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>05-18 11:06</td>\n",
       "      <td>05-26 14:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285</th>\n",
       "      <td>03-31 23:28</td>\n",
       "      <td>04-01 08:28</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>04-01 07:11</td>\n",
       "      <td>04-18 15:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>04-21 21:40</td>\n",
       "      <td>04-22 09:40</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>04-22 08:44</td>\n",
       "      <td>05-05 02:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <td>04-06 20:11</td>\n",
       "      <td>04-07 23:50</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>04-07 11:59</td>\n",
       "      <td>06-06 14:21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ed-registered       ed-out    admission_location   hosp-admit   hosp-disch\n",
       "40    12-15 21:10  12-16 22:05  EMERGENCY ROOM ADMIT  12-16 19:48  12-22 16:15\n",
       "143   05-17 15:27  05-18 11:50  EMERGENCY ROOM ADMIT  05-18 11:06  05-26 14:30\n",
       "285   03-31 23:28  04-01 08:28  EMERGENCY ROOM ADMIT  04-01 07:11  04-18 15:25\n",
       "309   04-21 21:40  04-22 09:40  EMERGENCY ROOM ADMIT  04-22 08:44  05-05 02:04\n",
       "380   04-06 20:11  04-07 23:50  EMERGENCY ROOM ADMIT  04-07 11:59  06-06 14:21"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first, isolate to patients in the ed\n",
    "ed = df.loc[df['edregtime'].notnull(), :]\n",
    "\n",
    "# now find patients in the ED but not in the hospital\n",
    "ed_day = ed['edregtime'].dt.floor('d')\n",
    "hosp_day = ed['admittime'].dt.floor('d')\n",
    "hosp_hour = ed['admittime'].apply(lambda x: x.hour)\n",
    "\n",
    "# add a gap\n",
    "idx = (hosp_day > ed_day) & (hosp_hour > 6)\n",
    "ed = ed.loc[idx, :]\n",
    "\n",
    "# look at the top 5 rows for the table\n",
    "ed[['ed-registered','ed-out','admission_location','hosp-admit','hosp-disch']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above, row index 143 looks promising: they were registered in the ED at 15:27 and admitted to the hospital the next day at 11:06."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 hospital admissions for subject.\n",
      "Row 143 is the first admission.\n"
     ]
    },
    {
     "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>row_id</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>icustay_id</th>\n",
       "      <th>startdate</th>\n",
       "      <th>enddate</th>\n",
       "      <th>drug_type</th>\n",
       "      <th>drug</th>\n",
       "      <th>drug_name_poe</th>\n",
       "      <th>drug_name_generic</th>\n",
       "      <th>formulary_drug_cd</th>\n",
       "      <th>gsn</th>\n",
       "      <th>ndc</th>\n",
       "      <th>prod_strength</th>\n",
       "      <th>dose_val_rx</th>\n",
       "      <th>dose_unit_rx</th>\n",
       "      <th>form_val_disp</th>\n",
       "      <th>form_unit_disp</th>\n",
       "      <th>route</th>\n",
       "      <th>mimic_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [row_id, subject_id, hadm_id, icustay_id, startdate, enddate, drug_type, drug, drug_name_poe, drug_name_generic, formulary_drug_cd, gsn, ndc, prod_strength, dose_val_rx, dose_unit_rx, form_val_disp, form_unit_disp, route, mimic_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subject_id = ed.loc[143, 'subject_id']\n",
    "\n",
    "# how many admissions does this patient have?\n",
    "idx = df['subject_id']==subject_id\n",
    "print('{} hospital admissions for subject.'.format(df.loc[idx, 'hadm_id'].count()))\n",
    "print('Row {} is the first admission.'.format(\n",
    "    df.loc[idx, :].sort_values('admittime', ascending=True).index[0]))\n",
    "\n",
    "query = query_schema + 'select * from prescriptions where subject_id = {}'.format(subject_id)\n",
    "pr = pd.read_sql_query(query,con)\n",
    "\n",
    "# truncate the admittime to day\n",
    "admitdate = pd.Timestamp(ed.loc[143, 'admittime'].date())\n",
    "\n",
    "# look for rows before this day\n",
    "idx = (pr['subject_id'] == subject_id) & (pr['startdate'] < admitdate)\n",
    "pr.loc[idx, :].sort_values('startdate')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Given the above, it seems unlikely the prescriptions table captures ED medications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "subject_id   135 - 0 rows.\n",
      "subject_id   453 - 0 rows.\n",
      "subject_id   384 - 0 rows.\n",
      "subject_id   407 - 0 rows.\n",
      "subject_id   209 - 0 rows.\n",
      "subject_id   878 - 0 rows.\n",
      "subject_id   653 - 0 rows.\n",
      "subject_id  1032 - 0 rows.\n",
      "subject_id  1092 - 0 rows.\n",
      "subject_id   702 - 0 rows.\n",
      "subject_id   810 - 0 rows.\n",
      "subject_id   822 - 0 rows.\n",
      "subject_id  1245 - 0 rows.\n",
      "subject_id  1119 - 0 rows.\n",
      "subject_id  1136 - 0 rows.\n",
      "subject_id  1541 - 0 rows.\n",
      "subject_id  1630 - 0 rows.\n",
      "subject_id  1683 - 0 rows.\n",
      "subject_id  1262 - 0 rows.\n",
      "subject_id  1297 - 0 rows.\n",
      "subject_id  2001 - 0 rows.\n"
     ]
    }
   ],
   "source": [
    "n = 0\n",
    "for i, row in ed.iterrows():\n",
    "    # get the subject ID\n",
    "    subject_id = row['subject_id']\n",
    "    \n",
    "    # skip this row if it is a secondary admission to the hospital\n",
    "    idx = df['subject_id']==subject_id\n",
    "    if i != df.loc[idx, :].sort_values('admittime', ascending=True).index[0]:\n",
    "        continue\n",
    "        \n",
    "    # truncate the admittime to day\n",
    "    admitdate = pd.Timestamp(row['admittime'].date())\n",
    "\n",
    "    # look for rows before this day but no more than 7 days before\n",
    "    idx = (pr['subject_id'] == subject_id) & (pr['startdate'] < admitdate)\n",
    "    print('subject_id {:5d} - {} rows.'.format(subject_id, idx.sum()))\n",
    "    n+=1\n",
    "    \n",
    "    if n>20:\n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It would appear that no emergency department prescriptions exist in the `prescriptions` table!"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}