cohort_selection_mimic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cohort selection process for MIMIC data.\n",
    "\n",
    "* We combine MICU (medical) and SICU (surgical) as an approximation to GICU in Bristol.\n",
    "* We use Metavision only because of relatively well structured data (compared with Carevue).\n",
    "\n",
    "The conditions for cohort inclusion are ICUSTAYs that:\n",
    "\n",
    "* Are the first (or only) ICU stay of a hopistal admission.\n",
    "* Have outcome information (survival/mortality or readmission to ICU).\n",
    "* Have 'good' data availability (we test for this later, not in this notebook).\n",
    "\n",
    "A positive outcome is defined as:\n",
    "\n",
    "* Patient survives hospital stay\n",
    "* Patient is not re-admitted to ICU diuring the same hospital admission\n",
    "\n",
    "Therefore, a negative outcome is (at least) one of:\n",
    "\n",
    "* Death during hospital admission (on ICU or ward).\n",
    "* Readmission to ICU during hospital stay (even if patient survives)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import graphlab\n",
    "import graphlab.aggregate as agg\n",
    "import numpy as np\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%sql mysql://root:mysql2016@localhost/MIMIC?unix_socket=/run/mysqld/mysqld.sock\n",
    "%sql USE MIMIC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following query extracts the required information about ICUSTAY, HADM (hospital admission), and outcomes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14595 rows affected.\n"
     ]
    }
   ],
   "source": [
    "data=%sql SELECT I.SUBJECT_ID, I.HADM_ID, I.ICUSTAY_ID, A.HOSPITAL_EXPIRE_FLAG, I.INTIME, I.OUTTIME, A.DEATHTIME \\\n",
    "FROM ICUSTAYS I \\\n",
    "INNER JOIN PATIENTS P \\\n",
    "ON I.SUBJECT_ID=P.SUBJECT_ID \\\n",
    "INNER JOIN ADMISSIONS A \\\n",
    "ON I.HADM_ID=A.HADM_ID \\\n",
    "WHERE (FIRST_CAREUNIT='MICU' or FIRST_CAREUNIT='SICU') AND DBSOURCE='METAVISION'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We store the query output in a dataframe (and save to disk avoid re-running the query later):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14595\n"
     ]
    }
   ],
   "source": [
    "print len(data) \n",
    "gdata = graphlab.SFrame(data.DataFrame())\n",
    "gdata.save('mimic_mortality')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Counting unqiue individuals, stays, and hospital admissions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are: \n",
      "11007 patients,\n",
      "13748 hospitals admissions, and\n",
      "14595 intensive care stays.\n"
     ]
    }
   ],
   "source": [
    "print \"There are: \"\n",
    "print str(len(gdata['SUBJECT_ID'].unique())) + \" patients,\"\n",
    "print str(len(gdata['HADM_ID'].unique())) + \" hospitals admissions, and\"\n",
    "print str(len(gdata['ICUSTAY_ID'].unique())) + \" intensive care stays.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### For each HADM (hospital admission) we want to know how many ICU stays occur:\n",
    "\n",
    "For each HADM we will include the first ICUSTAY in our cohort.\n",
    "\n",
    "(Presence of a second ICUSTAY in the same HADM constitues a negative outcome.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gdata_grouped = gdata.groupby(key_columns='HADM_ID',\n",
    "              operations={\n",
    "                  'SUBJECT_ID' : agg.SELECT_ONE('SUBJECT_ID'),\n",
    "                  'first intime': agg.MIN('INTIME'),\n",
    "                  'first stay'  : agg.ARGMIN('INTIME', 'ICUSTAY_ID'),\n",
    "                  'count' : agg.COUNT('ICUSTAY_ID'),\n",
    "                  'mortality':agg.MAX('HOSPITAL_EXPIRE_FLAG')})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert aggregated data to dictionaries for portable pickle save:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ic_count = dict()\n",
    "first_stays = dict()\n",
    "intimes = dict()\n",
    "\n",
    "for row in gdata_grouped:\n",
    "    \n",
    "    ic_count[row['HADM_ID']] = row['count']\n",
    "    intimes[row['HADM_ID']] = row['first intime']\n",
    "    first_stays[row['HADM_ID']] = row['first stay']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we produce a list of all hospital admissions (HADM_ID) during which the patient dies:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13748\n",
      "1681\n"
     ]
    }
   ],
   "source": [
    "print len(gdata_grouped)\n",
    "print sum(gdata_grouped['mortality']==1)\n",
    "mortality_list = list(gdata_grouped[gdata_grouped['mortality']==1]['HADM_ID'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we produce a list of all ICUSTAYs during which the patient dies on ICU:\n",
    "\n",
    "(This is determined from A.DEATHTIME < I.OUTTIME)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 mortalities have no DEATHTIME recorded.\n",
      " \n",
      "There are 1922 recorded death times.\n",
      "Of these, 1279 occur on ICU.\n"
     ]
    }
   ],
   "source": [
    "print \"%d mortalities have no DEATHTIME recorded.\" %sum(gdata[gdata['DEATHTIME']==None ]['HOSPITAL_EXPIRE_FLAG']==1)  \n",
    "print \" \"\n",
    "\n",
    "_mortalities = gdata[gdata['DEATHTIME']!=None]\n",
    "ic_deaths = _mortalities.apply(lambda row: row['ICUSTAY_ID'] if row['DEATHTIME'] <= row['OUTTIME'] else -1)\n",
    "ic_deaths = [i for i in ic_deaths if i!=-1]\n",
    "\n",
    "print \"There are %d recorded death times.\" %len(_mortalities)\n",
    "print \"Of these, %d occur on ICU.\" %len(ic_deaths)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We save these cohort characteristics for use in extracting data from MIMIC (see 'data_pull_mimic.ipynb'):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## These are dictionaries:\n",
    "f = open('first_stays.pkl', 'wb')\n",
    "pickle.dump(first_stays, f)\n",
    "f.close()\n",
    "\n",
    "f = open('stay_counts.pkl', 'wb')\n",
    "pickle.dump(ic_count, f)\n",
    "f.close()\n",
    "\n",
    "## These are lists:\n",
    "f = open('moratlities.pkl', 'wb')\n",
    "pickle.dump(mortality_list, f)\n",
    "f.close()\n",
    "\n",
    "f = open('icu_deaths.pkl', 'wb')\n",
    "pickle.dump(ic_deaths, f)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "---------------------------------------------------------------------------\n",
    "\n",
    "---------------------------------------------------------------------------\n",
    "#### Other scripting (sanity checks and looking at some occurance rates):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "more_than_one_stay = sum(np.asarray(ic_count.values())>1)\n",
    "more_than_two_stay = sum(np.asarray(ic_count.values())>2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "742\n",
      "80\n"
     ]
    }
   ],
   "source": [
    "print more_than_one_stay\n",
    "print more_than_two_stay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0539714867617108"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Readmission rate:\n",
    "more_than_one_stay/float(len(gdata['HADM_ID'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.122272330521\n"
     ]
    }
   ],
   "source": [
    "## Overall mortality\n",
    "print len(mortality_list)/float(len(gdata['HADM_ID'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.260107816712\n"
     ]
    }
   ],
   "source": [
    "## Readmission mortality:\n",
    "death=0\n",
    "for mortality in mortality_list:\n",
    "    if ic_count[mortality]>1:\n",
    "        death+=1\n",
    "        \n",
    "readmit_mort = death/float(sum(np.asarray(ic_count.values())>1))\n",
    "print readmit_mort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.11440873443\n"
     ]
    }
   ],
   "source": [
    "## Single stay mortality:\n",
    "death=0\n",
    "for mortality in mortality_list:\n",
    "    if ic_count[mortality]==1:\n",
    "        death+=1\n",
    "        \n",
    "single_mort = death/float(sum(np.asarray(ic_count.values())==1))\n",
    "print single_mort"
   ]
  }
 ],
 "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": 1
}