02_MIMICII_Intro_and_Cohorts.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NOTE : This notebook was originally created by Dr. Brian Chapman and others.  It has been modified slightly for our 2017 course."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identifying Patient Cohorts in [MIMIC-II](http://www.ncbi.nlm.nih.gov/pmc/articles/PMC3124312/)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[MIMIC-II](https://physionet.org/mimic2/mimic2_clinical_overview.shtml) is a freely available database of ICU patients. To access the full database (now migrated to [MIMIC-III](https://www.nature.com/articles/sdata201635.pdf))  you must sign a data use agreement. However, there is a [demo data set](https://physionet.org/mimic2/demo/) based on 4000 deceased patients that can be used without signing any DUA.\n",
    "\n",
    "## How to Use the MIMIC-II Database\n",
    "* [MIMIC-II Cookbook](https://physionet.org/mimic2/demo/MIMICIICookBook_v1.pdf)\n",
    "* [MIMIC Data Dictionaries](http://physionet.incor.usp.br/physiobank/database/dictionaries/)\n",
    "\n",
    "\n",
    "## The Varieties of...Data\n",
    "The data set is very rich and so is a good resource for exploring the varieties of clinical data\n",
    "\n",
    "![MIMIC Paper](./images/mimic_paper_header.jpg)\n",
    "(Sources : https://mimic.physionet.org/)\n",
    "\n",
    "Data incluces free text notes (nursing, radiology, discharg summaries, etc.), input/output events, test results, procedure codes, diagnosis codes, etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Very Short FAQ : \n",
    "* Q : What is the difference between MIMIC-II and MIMIC-III?\n",
    "* A : MIMIC-II spans the time period of 2001 to 2008.  MIMIC-III spans 2001 to 2012 so it contains more data.  In addition, some data structures have been improved to make MIMIC-III easier to work with.  Some data quality issues have been resolved as well\n",
    "\n",
    "\n",
    "* Q : How can I get access to MIMIC-III for my own research?\n",
    "* A : You'll need to do CITI training and then some other steps.  Start here: https://mimic.physionet.org/gettingstarted/access/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "import getpass\n",
    "import pandas as pd\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter MySQL passwd for jovyan········\n"
     ]
    }
   ],
   "source": [
    "conn = pymysql.connect(host=\"mysql\",\n",
    "                       port=3306,user=\"jovyan\",\n",
    "                       passwd=getpass.getpass(\"Enter MySQL passwd for jovyan\"),db='mimic2')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example Query: Identifying ICD9 Codes for Patients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "icd9_codes = pd.read_sql('SELECT subject_id, code, description from icd9',conn)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>ICD9 Counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>UNSPECIFIED ESSENTIAL HYPERTENSION</th>\n",
       "      <td>0.027176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CONGESTIVE HEART FAILURE UNSPECIFIED</th>\n",
       "      <td>0.025083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ATRIAL FIBRILLATION</th>\n",
       "      <td>0.022574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACUTE RESPIRATORY FAILURE</th>\n",
       "      <td>0.016785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACUTE RENAL FAILURE UNSPECIFIED</th>\n",
       "      <td>0.016068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CORONARY ATHEROSCLEROSIS OF NATIVE CORONARY ARTERY</th>\n",
       "      <td>0.015389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DIABETES MELLITUS WITHOUT COMPLICATION TYPE II OR</th>\n",
       "      <td>0.012843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>URINARY TRACT INFECTION SITE NOT SPECIFIED</th>\n",
       "      <td>0.011429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CONGESTIVE HEART FAILURE                \\r</th>\n",
       "      <td>0.009712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PNEUMONIA ORGANISM UNSPECIFIED</th>\n",
       "      <td>0.009184</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    ICD9 Counts\n",
       "UNSPECIFIED ESSENTIAL HYPERTENSION                     0.027176\n",
       "CONGESTIVE HEART FAILURE UNSPECIFIED                   0.025083\n",
       "ATRIAL FIBRILLATION                                    0.022574\n",
       "ACUTE RESPIRATORY FAILURE                              0.016785\n",
       "ACUTE RENAL FAILURE UNSPECIFIED                        0.016068\n",
       "CORONARY ATHEROSCLEROSIS OF NATIVE CORONARY ARTERY     0.015389\n",
       "DIABETES MELLITUS WITHOUT COMPLICATION TYPE II OR      0.012843\n",
       "URINARY TRACT INFECTION SITE NOT SPECIFIED             0.011429\n",
       "CONGESTIVE HEART FAILURE                \\r             0.009712\n",
       "PNEUMONIA ORGANISM UNSPECIFIED                         0.009184"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "icd9_counts = icd9_codes[\"description\"].value_counts([\"description\"]).to_frame(name=\"ICD9 Counts\")\n",
    "icd9_counts.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selecting Cohorts\n",
    "\n",
    "Let's find some pneumonia patients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>HADM_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>202</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   HADM_ID\n",
       "0        3\n",
       "1       15\n",
       "2      130\n",
       "3      167\n",
       "4      202"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(pd.read_sql_query(\"SELECT d.HADM_ID FROM  icd9 d    WHERE  (code like '486%%')  GROUP BY d.HADM_ID\",conn)[:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select all the patient with or without pneumonia ICD9 code\n",
    "### [Codes obtained from CDC](http://www.icd9data.com/2012/Volume1/460-519/480-488/486/486.htm)\n",
    "\n",
    "Get a list of admission ids with the label of pneumonia diagnoses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "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>admit_dt</th>\n",
       "      <th>Encounter_Pneumonia_Diagnosis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>56</td>\n",
       "      <td>28766</td>\n",
       "      <td>2644-01-17 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>2682-09-07 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>3138-10-29 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>20882</td>\n",
       "      <td>3139-03-19 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>12532</td>\n",
       "      <td>2875-09-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>3079-03-03 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>37</td>\n",
       "      <td>18052</td>\n",
       "      <td>3264-08-14 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>31</td>\n",
       "      <td>15325</td>\n",
       "      <td>2678-08-21 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>61</td>\n",
       "      <td>7149</td>\n",
       "      <td>3352-06-23 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>61</td>\n",
       "      <td>5712</td>\n",
       "      <td>3353-01-10 00:00:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id             admit_dt  Encounter_Pneumonia_Diagnosis\n",
       "0          56    28766  2644-01-17 00:00:00                              0\n",
       "1           3     2075  2682-09-07 00:00:00                              0\n",
       "2          21    20666  3138-10-29 00:00:00                              0\n",
       "3          21    20882  3139-03-19 00:00:00                              0\n",
       "4          12    12532  2875-09-26 00:00:00                              0\n",
       "5          26    15067  3079-03-03 00:00:00                              0\n",
       "6          37    18052  3264-08-14 00:00:00                              1\n",
       "7          31    15325  2678-08-21 00:00:00                              1\n",
       "8          61     7149  3352-06-23 00:00:00                              0\n",
       "9          61     5712  3353-01-10 00:00:00                              0"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pneumonia_query = \"\"\"\n",
    "    SELECT \n",
    "a.subject_id\n",
    ",a.hadm_id\n",
    ",a.admit_dt\n",
    ",(CASE WHEN pneu.HADM_ID IS NOT NULL THEN 1 ELSE 0 END) as Encounter_Pneumonia_Diagnosis\n",
    "FROM admissions a\n",
    "LEFT JOIN \n",
    "(\n",
    "    SELECT\n",
    "    d.HADM_ID\n",
    "    FROM  icd9 d\n",
    "    WHERE \n",
    "        (code like '486%%')\n",
    "    GROUP BY d.HADM_ID\n",
    ") pneu\n",
    "ON a.HADM_ID = pneu.HADM_ID LIMIT 10\n",
    "\"\"\"\n",
    "\n",
    "adm_data = \\\n",
    "pd.read_sql(pneumonia_query,conn)\n",
    "copd_data.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pull radiology notes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-10**] 5:22 AM\\n    ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-11**] 4:06 PM\\n    ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-11**] 8:05 AM\\n    ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-12**] 6:32 AM\\n    ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-13**] 6:01 AM\\n    ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject_id  hadm_id                                               text\n",
       "0           3     2075  \\n\\n\\n     DATE: [**2682-9-10**] 5:22 AM\\n    ...\n",
       "1           3     2075  \\n\\n\\n     DATE: [**2682-9-11**] 4:06 PM\\n    ...\n",
       "2           3     2075  \\n\\n\\n     DATE: [**2682-9-11**] 8:05 AM\\n    ...\n",
       "3           3     2075  \\n\\n\\n     DATE: [**2682-9-12**] 6:32 AM\\n    ...\n",
       "4           3     2075  \\n\\n\\n     DATE: [**2682-9-13**] 6:01 AM\\n    ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "chest_xray_note_query = \"\"\"\n",
    "SELECT\n",
    "subject_id\n",
    ",hadm_id\n",
    ",LTRIM(RTRIM(text)) as text\n",
    "FROM noteevents\n",
    "WHERE category = 'RADIOLOGY_REPORT'\n",
    "    AND (text like '%%CHEST (PORTABLE AP)%%' OR text like '%%CHEST (PA & LAT)%%')\n",
    "    AND subject_id is not NULL\n",
    "    AND hadm_id is not NULL\n",
    "GROUP BY subject_id, hadm_id, text LIMIT 5\n",
    "\"\"\"\n",
    "chest_xray_note_df = pd.read_sql_query(chest_xray_note_query, conn)\n",
    "display(chest_xray_note_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select notes from patients with or without pneumonia ICD9 code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "notes_query='''\n",
    "YOU NEED WRITE THIS SQL'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>subject_id</th>\n",
       "      <th>hadm_id</th>\n",
       "      <th>text</th>\n",
       "      <th>Encounter_Pneumonia_Diagnosis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-10**] 5:22 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-11**] 4:06 PM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-11**] 8:05 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-12**] 6:32 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-13**] 6:01 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-14**] 11:41 PM\\n   ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-7**] 10:23 PM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-7**] 6:16 PM\\n     ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-8**] 1:00 AM\\n     ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-8**] 4:43 PM\\n     ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-8**] 6:10 AM\\n     ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>2075</td>\n",
       "      <td>\\n\\n\\n     DATE: [**2682-9-9**] 4:27 PM\\n     ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-10-29**] 8:30 PM\\n   ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-10-29**] 9:31 AM\\n   ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-10-30**] 10:48 AM\\n  ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-10-31**] 5:43 AM\\n   ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-11-1**] 9:23 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-11-2**] 7:15 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-11-3**] 7:16 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-11-4**] 7:05 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-11-5**] 7:17 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-11-6**] 11:08 AM\\n   ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>21</td>\n",
       "      <td>20666</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3138-11-7**] 7:03 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>21</td>\n",
       "      <td>20882</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3139-3-20**] 1:22 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>21</td>\n",
       "      <td>20882</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3139-3-25**] 3:11 PM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3079-3-6**] 8:03 AM\\n     ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>15067</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3079-3-7**] 11:35 AM\\n    ...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>37</td>\n",
       "      <td>18052</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3264-8-14**] 10:57 AM\\n   ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>37</td>\n",
       "      <td>18052</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3264-8-14**] 6:06 AM\\n    ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>37</td>\n",
       "      <td>18052</td>\n",
       "      <td>\\n\\n\\n     DATE: [**3264-8-16**] 1:53 PM\\n    ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    subject_id  hadm_id                                               text  \\\n",
       "0            3     2075  \\n\\n\\n     DATE: [**2682-9-10**] 5:22 AM\\n    ...   \n",
       "1            3     2075  \\n\\n\\n     DATE: [**2682-9-11**] 4:06 PM\\n    ...   \n",
       "2            3     2075  \\n\\n\\n     DATE: [**2682-9-11**] 8:05 AM\\n    ...   \n",
       "3            3     2075  \\n\\n\\n     DATE: [**2682-9-12**] 6:32 AM\\n    ...   \n",
       "4            3     2075  \\n\\n\\n     DATE: [**2682-9-13**] 6:01 AM\\n    ...   \n",
       "5            3     2075  \\n\\n\\n     DATE: [**2682-9-14**] 11:41 PM\\n   ...   \n",
       "6            3     2075  \\n\\n\\n     DATE: [**2682-9-7**] 10:23 PM\\n    ...   \n",
       "7            3     2075  \\n\\n\\n     DATE: [**2682-9-7**] 6:16 PM\\n     ...   \n",
       "8            3     2075  \\n\\n\\n     DATE: [**2682-9-8**] 1:00 AM\\n     ...   \n",
       "9            3     2075  \\n\\n\\n     DATE: [**2682-9-8**] 4:43 PM\\n     ...   \n",
       "10           3     2075  \\n\\n\\n     DATE: [**2682-9-8**] 6:10 AM\\n     ...   \n",
       "11           3     2075  \\n\\n\\n     DATE: [**2682-9-9**] 4:27 PM\\n     ...   \n",
       "12          21    20666  \\n\\n\\n     DATE: [**3138-10-29**] 8:30 PM\\n   ...   \n",
       "13          21    20666  \\n\\n\\n     DATE: [**3138-10-29**] 9:31 AM\\n   ...   \n",
       "14          21    20666  \\n\\n\\n     DATE: [**3138-10-30**] 10:48 AM\\n  ...   \n",
       "15          21    20666  \\n\\n\\n     DATE: [**3138-10-31**] 5:43 AM\\n   ...   \n",
       "16          21    20666  \\n\\n\\n     DATE: [**3138-11-1**] 9:23 AM\\n    ...   \n",
       "17          21    20666  \\n\\n\\n     DATE: [**3138-11-2**] 7:15 AM\\n    ...   \n",
       "18          21    20666  \\n\\n\\n     DATE: [**3138-11-3**] 7:16 AM\\n    ...   \n",
       "19          21    20666  \\n\\n\\n     DATE: [**3138-11-4**] 7:05 AM\\n    ...   \n",
       "20          21    20666  \\n\\n\\n     DATE: [**3138-11-5**] 7:17 AM\\n    ...   \n",
       "21          21    20666  \\n\\n\\n     DATE: [**3138-11-6**] 11:08 AM\\n   ...   \n",
       "22          21    20666  \\n\\n\\n     DATE: [**3138-11-7**] 7:03 AM\\n    ...   \n",
       "23          21    20882  \\n\\n\\n     DATE: [**3139-3-20**] 1:22 AM\\n    ...   \n",
       "24          21    20882  \\n\\n\\n     DATE: [**3139-3-25**] 3:11 PM\\n    ...   \n",
       "25          26    15067  \\n\\n\\n     DATE: [**3079-3-6**] 8:03 AM\\n     ...   \n",
       "26          26    15067  \\n\\n\\n     DATE: [**3079-3-7**] 11:35 AM\\n    ...   \n",
       "27          37    18052  \\n\\n\\n     DATE: [**3264-8-14**] 10:57 AM\\n   ...   \n",
       "28          37    18052  \\n\\n\\n     DATE: [**3264-8-14**] 6:06 AM\\n    ...   \n",
       "29          37    18052  \\n\\n\\n     DATE: [**3264-8-16**] 1:53 PM\\n    ...   \n",
       "\n",
       "    Encounter_Pneumonia_Diagnosis  \n",
       "0                               0  \n",
       "1                               0  \n",
       "2                               0  \n",
       "3                               0  \n",
       "4                               0  \n",
       "5                               0  \n",
       "6                               0  \n",
       "7                               0  \n",
       "8                               0  \n",
       "9                               0  \n",
       "10                              0  \n",
       "11                              0  \n",
       "12                              0  \n",
       "13                              0  \n",
       "14                              0  \n",
       "15                              0  \n",
       "16                              0  \n",
       "17                              0  \n",
       "18                              0  \n",
       "19                              0  \n",
       "20                              0  \n",
       "21                              0  \n",
       "22                              0  \n",
       "23                              0  \n",
       "24                              0  \n",
       "25                              0  \n",
       "26                              0  \n",
       "27                              1  \n",
       "28                              1  \n",
       "29                              1  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pneumonia_note_df = \\\n",
    "pd.read_sql(notes_query,conn)\n",
    "pneumonia_note_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br/><br/>This material presented as part of the DeCART Data Science for the Health Science Summer Program at the University of Utah in 2019.<br/>\n",
    "Presenters : Dr. Wendy Chapman, Kelly Peterson, Alec Chapman, Jianlin Shi <br> Acknowledgement: Many thanks to Olga Patterson because part of the materials are adopted from his previous work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": true
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}