Patient_Demos.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "28f0273f-ee0f-4354-9ecb-f5f9285eb70a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from tableone import TableOne, load_dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e166af8c-6d5f-4a5e-8c4b-8ef28ea7d20c",
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.read_csv(\"~/project/n2c2/2022/Data/train.csv\")\n",
    "dev = pd.read_csv(\"~/project/n2c2/2022/Data/dev.csv\")\n",
    "test = pd.read_csv(\"/home/vs428/project/n2c2/2022/Data/n2c2_track3_test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fa802da4-a1be-4c01-8193-10adf2443ffd",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.concat([train,dev, test])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a6b79843-58d5-4853-86fc-c9bdd6eaaa7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import jsonlines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3f5cb18-6bf4-4d2d-a254-2ade664cfbc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "with jsonlines.open(\"/home/vs428/project/n2c2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b01e1705-4395-4a54-ae74-9d2a8e2aa6b1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((4633, 5), (597, 5), (667, 5))"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape, dev.shape, test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "baae0bdf-d8aa-41ff-a5a7-54ef24c5c79c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train ratio: 0.7856537222316432\n",
      "Dev ratio: 0.10123791758521282\n",
      "Test ratio: 0.11310836018314398\n"
     ]
    }
   ],
   "source": [
    "total = train.shape[0] + dev.shape[0] +  test.shape[0]\n",
    "print(f\"Train ratio: {train.shape[0]/total}\")\n",
    "print(f\"Dev ratio: {dev.shape[0]/total}\")\n",
    "print(f\"Test ratio: {test.shape[0]/total}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9c2443eb-507b-4e70-914f-5923441cffe8",
   "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>ROW ID</th>\n",
       "      <th>HADM ID</th>\n",
       "      <th>Assessment</th>\n",
       "      <th>Plan Subsection</th>\n",
       "      <th>Relation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>701359</td>\n",
       "      <td>186454</td>\n",
       "      <td>51 yr old F with a history of 3V CAD, confirme...</td>\n",
       "      <td># CORONARIES: Patient with 3 vessel disease on...</td>\n",
       "      <td>Direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>701359</td>\n",
       "      <td>186454</td>\n",
       "      <td>51 yr old F with a history of 3V CAD, confirme...</td>\n",
       "      <td># Acute on Chronic systolic CHF: Patient with ...</td>\n",
       "      <td>Indirect</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>701359</td>\n",
       "      <td>186454</td>\n",
       "      <td>51 yr old F with a history of 3V CAD, confirme...</td>\n",
       "      <td># Social stressors: Recently lost mother to ca...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>701359</td>\n",
       "      <td>186454</td>\n",
       "      <td>51 yr old F with a history of 3V CAD, confirme...</td>\n",
       "      <td># FEN: Replete lytes PRN, NPO for CABG today\\n...</td>\n",
       "      <td>Not Relevant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>554144</td>\n",
       "      <td>196435</td>\n",
       "      <td>CHRONIC OBSTRUCTIVE PULMONARY DISEASE (COPD, B...</td>\n",
       "      <td># Dyspnea: Differential in this patient would ...</td>\n",
       "      <td>Direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662</th>\n",
       "      <td>560683</td>\n",
       "      <td>196558</td>\n",
       "      <td>DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...</td>\n",
       "      <td>5)       HTN: Cont lisinopril</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>560683</td>\n",
       "      <td>196558</td>\n",
       "      <td>DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...</td>\n",
       "      <td>6)       CRI: Last Cr 1.2: Hold naproxen given...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>664</th>\n",
       "      <td>560683</td>\n",
       "      <td>196558</td>\n",
       "      <td>DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...</td>\n",
       "      <td>7)       Nausea: likely chemo SE, cont compazi...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>665</th>\n",
       "      <td>560683</td>\n",
       "      <td>196558</td>\n",
       "      <td>DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...</td>\n",
       "      <td>8)       Hyperglycemia: trend, consider HISS</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>666</th>\n",
       "      <td>560683</td>\n",
       "      <td>196558</td>\n",
       "      <td>DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...</td>\n",
       "      <td>9)      FEN: repleted K, Mg\\n   [**Month/Year ...</td>\n",
       "      <td>Not Relevant</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5897 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ROW ID  HADM ID                                         Assessment  \\\n",
       "0    701359   186454  51 yr old F with a history of 3V CAD, confirme...   \n",
       "1    701359   186454  51 yr old F with a history of 3V CAD, confirme...   \n",
       "2    701359   186454  51 yr old F with a history of 3V CAD, confirme...   \n",
       "3    701359   186454  51 yr old F with a history of 3V CAD, confirme...   \n",
       "4    554144   196435  CHRONIC OBSTRUCTIVE PULMONARY DISEASE (COPD, B...   \n",
       "..      ...      ...                                                ...   \n",
       "662  560683   196558  DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...   \n",
       "663  560683   196558  DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...   \n",
       "664  560683   196558  DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...   \n",
       "665  560683   196558  DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...   \n",
       "666  560683   196558  DEEP VENOUS THROMBOSIS (DVT), UPPER EXTREMITY\\...   \n",
       "\n",
       "                                       Plan Subsection      Relation  \n",
       "0    # CORONARIES: Patient with 3 vessel disease on...        Direct  \n",
       "1    # Acute on Chronic systolic CHF: Patient with ...      Indirect  \n",
       "2    # Social stressors: Recently lost mother to ca...       Neither  \n",
       "3    # FEN: Replete lytes PRN, NPO for CABG today\\n...  Not Relevant  \n",
       "4    # Dyspnea: Differential in this patient would ...        Direct  \n",
       "..                                                 ...           ...  \n",
       "662                      5)       HTN: Cont lisinopril       Neither  \n",
       "663  6)       CRI: Last Cr 1.2: Hold naproxen given...       Neither  \n",
       "664  7)       Nausea: likely chemo SE, cont compazi...       Neither  \n",
       "665       8)       Hyperglycemia: trend, consider HISS       Neither  \n",
       "666  9)      FEN: repleted K, Mg\\n   [**Month/Year ...  Not Relevant  \n",
       "\n",
       "[5897 rows x 5 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f4fa35ca-69b1-4116-8248-c7861ec2ef15",
   "metadata": {},
   "outputs": [],
   "source": [
    "pts = pd.read_csv(\"/home/vs428/project/MIMIC/files/mimiciii/1.4/PATIENTS.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "31b08447-ff9d-44bd-8a9a-fced94b661a5",
   "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>DOB</th>\n",
       "      <th>DOD</th>\n",
       "      <th>DOD_HOSP</th>\n",
       "      <th>DOD_SSN</th>\n",
       "      <th>EXPIRE_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>234</td>\n",
       "      <td>249</td>\n",
       "      <td>F</td>\n",
       "      <td>2075-03-13 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>235</td>\n",
       "      <td>250</td>\n",
       "      <td>F</td>\n",
       "      <td>2164-12-27 00:00:00</td>\n",
       "      <td>2188-11-22 00:00:00</td>\n",
       "      <td>2188-11-22 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>236</td>\n",
       "      <td>251</td>\n",
       "      <td>M</td>\n",
       "      <td>2090-03-15 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>237</td>\n",
       "      <td>252</td>\n",
       "      <td>M</td>\n",
       "      <td>2078-03-06 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>238</td>\n",
       "      <td>253</td>\n",
       "      <td>F</td>\n",
       "      <td>2089-11-26 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46515</th>\n",
       "      <td>31840</td>\n",
       "      <td>44089</td>\n",
       "      <td>M</td>\n",
       "      <td>2026-05-25 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46516</th>\n",
       "      <td>31841</td>\n",
       "      <td>44115</td>\n",
       "      <td>F</td>\n",
       "      <td>2124-07-27 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46517</th>\n",
       "      <td>31842</td>\n",
       "      <td>44123</td>\n",
       "      <td>F</td>\n",
       "      <td>2049-11-26 00:00:00</td>\n",
       "      <td>2135-01-12 00:00:00</td>\n",
       "      <td>2135-01-12 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46518</th>\n",
       "      <td>31843</td>\n",
       "      <td>44126</td>\n",
       "      <td>F</td>\n",
       "      <td>2076-07-25 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46519</th>\n",
       "      <td>31844</td>\n",
       "      <td>44128</td>\n",
       "      <td>M</td>\n",
       "      <td>2098-07-25 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>46520 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ROW_ID  SUBJECT_ID GENDER                  DOB                  DOD  \\\n",
       "0         234         249      F  2075-03-13 00:00:00                  NaN   \n",
       "1         235         250      F  2164-12-27 00:00:00  2188-11-22 00:00:00   \n",
       "2         236         251      M  2090-03-15 00:00:00                  NaN   \n",
       "3         237         252      M  2078-03-06 00:00:00                  NaN   \n",
       "4         238         253      F  2089-11-26 00:00:00                  NaN   \n",
       "...       ...         ...    ...                  ...                  ...   \n",
       "46515   31840       44089      M  2026-05-25 00:00:00                  NaN   \n",
       "46516   31841       44115      F  2124-07-27 00:00:00                  NaN   \n",
       "46517   31842       44123      F  2049-11-26 00:00:00  2135-01-12 00:00:00   \n",
       "46518   31843       44126      F  2076-07-25 00:00:00                  NaN   \n",
       "46519   31844       44128      M  2098-07-25 00:00:00                  NaN   \n",
       "\n",
       "                  DOD_HOSP DOD_SSN  EXPIRE_FLAG  \n",
       "0                      NaN     NaN            0  \n",
       "1      2188-11-22 00:00:00     NaN            1  \n",
       "2                      NaN     NaN            0  \n",
       "3                      NaN     NaN            0  \n",
       "4                      NaN     NaN            0  \n",
       "...                    ...     ...          ...  \n",
       "46515                  NaN     NaN            0  \n",
       "46516                  NaN     NaN            0  \n",
       "46517  2135-01-12 00:00:00     NaN            1  \n",
       "46518                  NaN     NaN            0  \n",
       "46519                  NaN     NaN            0  \n",
       "\n",
       "[46520 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3fbbd047-f419-4f3f-8124-5c38c0d7964e",
   "metadata": {},
   "outputs": [],
   "source": [
    "hadms = pd.read_csv(\"/home/vs428/project/MIMIC/files/mimiciii/1.4/ADMISSIONS.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "64591870-234c-49a5-ad09-8009d87bd81d",
   "metadata": {},
   "outputs": [],
   "source": [
    "diags = pd.read_csv(\"/home/vs428/project/MIMIC/files/mimiciii/1.4/DIAGNOSES_ICD.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7c1c483a-47b6-443e-bb2d-26191ab8a4d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "icd_map = pd.read_csv(\"/home/vs428/project/MIMIC/files/mimiciii/1.4/D_ICD_DIAGNOSES.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8801f97e-9c8d-4de1-bd56-8a0949ec81fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set(icd_map['ICD9_CODE'].str.slice(0,3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ff39dd06-7e07-4f8c-923e-ae200debfcf0",
   "metadata": {},
   "outputs": [],
   "source": [
    "primary_diags = diags[diags['SEQ_NUM'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ecbd26b0-31d3-47ac-a11b-1e5e61fcc5e5",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>SEQ_NUM</th>\n",
       "      <th>ICD9_CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1297</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1311</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1488</td>\n",
       "      <td>112</td>\n",
       "      <td>174105</td>\n",
       "      <td>1.0</td>\n",
       "      <td>53100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1493</td>\n",
       "      <td>113</td>\n",
       "      <td>109976</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1496</td>\n",
       "      <td>114</td>\n",
       "      <td>178393</td>\n",
       "      <td>1.0</td>\n",
       "      <td>41401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650989</th>\n",
       "      <td>639745</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>1.0</td>\n",
       "      <td>566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651000</th>\n",
       "      <td>639756</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>1.0</td>\n",
       "      <td>43411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651024</th>\n",
       "      <td>639780</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651032</th>\n",
       "      <td>639788</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651041</th>\n",
       "      <td>639797</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58929 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ROW_ID  SUBJECT_ID  HADM_ID  SEQ_NUM ICD9_CODE\n",
       "0         1297         109   172335      1.0     40301\n",
       "14        1311         109   173633      1.0     40301\n",
       "28        1488         112   174105      1.0     53100\n",
       "33        1493         113   109976      1.0      1915\n",
       "36        1496         114   178393      1.0     41401\n",
       "...        ...         ...      ...      ...       ...\n",
       "650989  639745       97488   152542      1.0       566\n",
       "651000  639756       97488   161999      1.0     43411\n",
       "651024  639780       97492   189314      1.0     34680\n",
       "651032  639788       97497   168949      1.0      0529\n",
       "651041  639797       97503   188195      1.0      7842\n",
       "\n",
       "[58929 rows x 5 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "primary_diags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "35191dde-806e-48a2-bbe9-0137411e5b54",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids = hadms.merge(data, how=\"inner\", left_on=\"HADM_ID\", right_on=\"HADM ID\")\n",
    "# pt_ids = data.merge(hadms, how=\"left\", right_on=\"HADM_ID\", left_on=\"HADM ID\", indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "06a9cc12-d2a6-4216-9079-5df0aea20ea8",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ADMITTIME</th>\n",
       "      <th>DISCHTIME</th>\n",
       "      <th>DEATHTIME</th>\n",
       "      <th>ADMISSION_TYPE</th>\n",
       "      <th>ADMISSION_LOCATION</th>\n",
       "      <th>DISCHARGE_LOCATION</th>\n",
       "      <th>INSURANCE</th>\n",
       "      <th>LANGUAGE</th>\n",
       "      <th>RELIGION</th>\n",
       "      <th>MARITAL_STATUS</th>\n",
       "      <th>ETHNICITY</th>\n",
       "      <th>EDREGTIME</th>\n",
       "      <th>EDOUTTIME</th>\n",
       "      <th>DIAGNOSIS</th>\n",
       "      <th>HOSPITAL_EXPIRE_FLAG</th>\n",
       "      <th>HAS_CHARTEVENTS_DATA</th>\n",
       "      <th>ROW ID</th>\n",
       "      <th>HADM ID</th>\n",
       "      <th>Assessment</th>\n",
       "      <th>Plan Subsection</th>\n",
       "      <th>Relation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>390</td>\n",
       "      <td>305</td>\n",
       "      <td>194340</td>\n",
       "      <td>2129-08-20 18:03:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>PORT</td>\n",
       "      <td>CATHOLIC</td>\n",
       "      <td>MARRIED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEART FAILURE</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>560683</td>\n",
       "      <td>194340</td>\n",
       "      <td>ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...</td>\n",
       "      <td>#. Sepsis: Patient has re-developed fevers on ...</td>\n",
       "      <td>Direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>390</td>\n",
       "      <td>305</td>\n",
       "      <td>194340</td>\n",
       "      <td>2129-08-20 18:03:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>PORT</td>\n",
       "      <td>CATHOLIC</td>\n",
       "      <td>MARRIED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEART FAILURE</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>560683</td>\n",
       "      <td>194340</td>\n",
       "      <td>ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...</td>\n",
       "      <td>#. Volume status/Chronic systolic HF- As evide...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>390</td>\n",
       "      <td>305</td>\n",
       "      <td>194340</td>\n",
       "      <td>2129-08-20 18:03:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>PORT</td>\n",
       "      <td>CATHOLIC</td>\n",
       "      <td>MARRIED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEART FAILURE</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>560683</td>\n",
       "      <td>194340</td>\n",
       "      <td>ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...</td>\n",
       "      <td>#. Peripheral arterial disease: will follow up...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>390</td>\n",
       "      <td>305</td>\n",
       "      <td>194340</td>\n",
       "      <td>2129-08-20 18:03:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>PORT</td>\n",
       "      <td>CATHOLIC</td>\n",
       "      <td>MARRIED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEART FAILURE</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>560683</td>\n",
       "      <td>194340</td>\n",
       "      <td>ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...</td>\n",
       "      <td>#. Insulin dependent diabetes: continue ISS wi...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>390</td>\n",
       "      <td>305</td>\n",
       "      <td>194340</td>\n",
       "      <td>2129-08-20 18:03:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>2129-09-07 17:55:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>PORT</td>\n",
       "      <td>CATHOLIC</td>\n",
       "      <td>MARRIED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEART FAILURE</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>560683</td>\n",
       "      <td>194340</td>\n",
       "      <td>ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...</td>\n",
       "      <td>#. Hypertension: hold antihypertensives while ...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5892</th>\n",
       "      <td>58581</td>\n",
       "      <td>98755</td>\n",
       "      <td>184156</td>\n",
       "      <td>2167-05-17 12:36:00</td>\n",
       "      <td>2167-05-18 17:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>LONG TERM CARE HOSPITAL</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>JEWISH</td>\n",
       "      <td>WIDOWED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>2167-05-17 10:48:00</td>\n",
       "      <td>2167-05-17 14:02:00</td>\n",
       "      <td>ASTHMA/COPD EXACERBATION</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>573914</td>\n",
       "      <td>184156</td>\n",
       "      <td>Assessment and Plan: This is a 80 yo female wi...</td>\n",
       "      <td># Hypoxia/Hypercarbia: likely in the setting o...</td>\n",
       "      <td>Direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5893</th>\n",
       "      <td>58581</td>\n",
       "      <td>98755</td>\n",
       "      <td>184156</td>\n",
       "      <td>2167-05-17 12:36:00</td>\n",
       "      <td>2167-05-18 17:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>LONG TERM CARE HOSPITAL</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>JEWISH</td>\n",
       "      <td>WIDOWED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>2167-05-17 10:48:00</td>\n",
       "      <td>2167-05-17 14:02:00</td>\n",
       "      <td>ASTHMA/COPD EXACERBATION</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>573914</td>\n",
       "      <td>184156</td>\n",
       "      <td>Assessment and Plan: This is a 80 yo female wi...</td>\n",
       "      <td># Diastolic HF: doesn't seem to be in overt he...</td>\n",
       "      <td>Indirect</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5894</th>\n",
       "      <td>58581</td>\n",
       "      <td>98755</td>\n",
       "      <td>184156</td>\n",
       "      <td>2167-05-17 12:36:00</td>\n",
       "      <td>2167-05-18 17:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>LONG TERM CARE HOSPITAL</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>JEWISH</td>\n",
       "      <td>WIDOWED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>2167-05-17 10:48:00</td>\n",
       "      <td>2167-05-17 14:02:00</td>\n",
       "      <td>ASTHMA/COPD EXACERBATION</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>573914</td>\n",
       "      <td>184156</td>\n",
       "      <td>Assessment and Plan: This is a 80 yo female wi...</td>\n",
       "      <td># DM2: not on home medications; fingersticks h...</td>\n",
       "      <td>Indirect</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5895</th>\n",
       "      <td>58581</td>\n",
       "      <td>98755</td>\n",
       "      <td>184156</td>\n",
       "      <td>2167-05-17 12:36:00</td>\n",
       "      <td>2167-05-18 17:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>LONG TERM CARE HOSPITAL</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>JEWISH</td>\n",
       "      <td>WIDOWED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>2167-05-17 10:48:00</td>\n",
       "      <td>2167-05-17 14:02:00</td>\n",
       "      <td>ASTHMA/COPD EXACERBATION</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>573914</td>\n",
       "      <td>184156</td>\n",
       "      <td>Assessment and Plan: This is a 80 yo female wi...</td>\n",
       "      <td># Depression/Anxiety: cont home antidepressant...</td>\n",
       "      <td>Neither</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5896</th>\n",
       "      <td>58581</td>\n",
       "      <td>98755</td>\n",
       "      <td>184156</td>\n",
       "      <td>2167-05-17 12:36:00</td>\n",
       "      <td>2167-05-18 17:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>LONG TERM CARE HOSPITAL</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>JEWISH</td>\n",
       "      <td>WIDOWED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>2167-05-17 10:48:00</td>\n",
       "      <td>2167-05-17 14:02:00</td>\n",
       "      <td>ASTHMA/COPD EXACERBATION</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>573914</td>\n",
       "      <td>184156</td>\n",
       "      <td>Assessment and Plan: This is a 80 yo female wi...</td>\n",
       "      <td># FEN:\\n   - hold IVF\\n   - replete electrolyt...</td>\n",
       "      <td>Not Relevant</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5897 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ROW_ID  SUBJECT_ID  HADM_ID            ADMITTIME            DISCHTIME  \\\n",
       "0        390         305   194340  2129-08-20 18:03:00  2129-09-07 17:55:00   \n",
       "1        390         305   194340  2129-08-20 18:03:00  2129-09-07 17:55:00   \n",
       "2        390         305   194340  2129-08-20 18:03:00  2129-09-07 17:55:00   \n",
       "3        390         305   194340  2129-08-20 18:03:00  2129-09-07 17:55:00   \n",
       "4        390         305   194340  2129-08-20 18:03:00  2129-09-07 17:55:00   \n",
       "...      ...         ...      ...                  ...                  ...   \n",
       "5892   58581       98755   184156  2167-05-17 12:36:00  2167-05-18 17:03:00   \n",
       "5893   58581       98755   184156  2167-05-17 12:36:00  2167-05-18 17:03:00   \n",
       "5894   58581       98755   184156  2167-05-17 12:36:00  2167-05-18 17:03:00   \n",
       "5895   58581       98755   184156  2167-05-17 12:36:00  2167-05-18 17:03:00   \n",
       "5896   58581       98755   184156  2167-05-17 12:36:00  2167-05-18 17:03:00   \n",
       "\n",
       "                DEATHTIME ADMISSION_TYPE         ADMISSION_LOCATION  \\\n",
       "0     2129-09-07 17:55:00      EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "1     2129-09-07 17:55:00      EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "2     2129-09-07 17:55:00      EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "3     2129-09-07 17:55:00      EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "4     2129-09-07 17:55:00      EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "...                   ...            ...                        ...   \n",
       "5892                  NaN      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "5893                  NaN      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "5894                  NaN      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "5895                  NaN      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "5896                  NaN      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "\n",
       "           DISCHARGE_LOCATION INSURANCE LANGUAGE  RELIGION MARITAL_STATUS  \\\n",
       "0                DEAD/EXPIRED  Medicare     PORT  CATHOLIC        MARRIED   \n",
       "1                DEAD/EXPIRED  Medicare     PORT  CATHOLIC        MARRIED   \n",
       "2                DEAD/EXPIRED  Medicare     PORT  CATHOLIC        MARRIED   \n",
       "3                DEAD/EXPIRED  Medicare     PORT  CATHOLIC        MARRIED   \n",
       "4                DEAD/EXPIRED  Medicare     PORT  CATHOLIC        MARRIED   \n",
       "...                       ...       ...      ...       ...            ...   \n",
       "5892  LONG TERM CARE HOSPITAL  Medicare     ENGL    JEWISH        WIDOWED   \n",
       "5893  LONG TERM CARE HOSPITAL  Medicare     ENGL    JEWISH        WIDOWED   \n",
       "5894  LONG TERM CARE HOSPITAL  Medicare     ENGL    JEWISH        WIDOWED   \n",
       "5895  LONG TERM CARE HOSPITAL  Medicare     ENGL    JEWISH        WIDOWED   \n",
       "5896  LONG TERM CARE HOSPITAL  Medicare     ENGL    JEWISH        WIDOWED   \n",
       "\n",
       "     ETHNICITY            EDREGTIME            EDOUTTIME  \\\n",
       "0        WHITE                  NaN                  NaN   \n",
       "1        WHITE                  NaN                  NaN   \n",
       "2        WHITE                  NaN                  NaN   \n",
       "3        WHITE                  NaN                  NaN   \n",
       "4        WHITE                  NaN                  NaN   \n",
       "...        ...                  ...                  ...   \n",
       "5892     WHITE  2167-05-17 10:48:00  2167-05-17 14:02:00   \n",
       "5893     WHITE  2167-05-17 10:48:00  2167-05-17 14:02:00   \n",
       "5894     WHITE  2167-05-17 10:48:00  2167-05-17 14:02:00   \n",
       "5895     WHITE  2167-05-17 10:48:00  2167-05-17 14:02:00   \n",
       "5896     WHITE  2167-05-17 10:48:00  2167-05-17 14:02:00   \n",
       "\n",
       "                     DIAGNOSIS  HOSPITAL_EXPIRE_FLAG  HAS_CHARTEVENTS_DATA  \\\n",
       "0                HEART FAILURE                     1                     1   \n",
       "1                HEART FAILURE                     1                     1   \n",
       "2                HEART FAILURE                     1                     1   \n",
       "3                HEART FAILURE                     1                     1   \n",
       "4                HEART FAILURE                     1                     1   \n",
       "...                        ...                   ...                   ...   \n",
       "5892  ASTHMA/COPD EXACERBATION                     0                     1   \n",
       "5893  ASTHMA/COPD EXACERBATION                     0                     1   \n",
       "5894  ASTHMA/COPD EXACERBATION                     0                     1   \n",
       "5895  ASTHMA/COPD EXACERBATION                     0                     1   \n",
       "5896  ASTHMA/COPD EXACERBATION                     0                     1   \n",
       "\n",
       "      ROW ID  HADM ID                                         Assessment  \\\n",
       "0     560683   194340  ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...   \n",
       "1     560683   194340  ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...   \n",
       "2     560683   194340  ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...   \n",
       "3     560683   194340  ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...   \n",
       "4     560683   194340  ssessment and Plan\\n SEPSIS WITHOUT ORGAN DYSF...   \n",
       "...      ...      ...                                                ...   \n",
       "5892  573914   184156  Assessment and Plan: This is a 80 yo female wi...   \n",
       "5893  573914   184156  Assessment and Plan: This is a 80 yo female wi...   \n",
       "5894  573914   184156  Assessment and Plan: This is a 80 yo female wi...   \n",
       "5895  573914   184156  Assessment and Plan: This is a 80 yo female wi...   \n",
       "5896  573914   184156  Assessment and Plan: This is a 80 yo female wi...   \n",
       "\n",
       "                                        Plan Subsection      Relation  \n",
       "0     #. Sepsis: Patient has re-developed fevers on ...        Direct  \n",
       "1     #. Volume status/Chronic systolic HF- As evide...       Neither  \n",
       "2     #. Peripheral arterial disease: will follow up...       Neither  \n",
       "3     #. Insulin dependent diabetes: continue ISS wi...       Neither  \n",
       "4     #. Hypertension: hold antihypertensives while ...       Neither  \n",
       "...                                                 ...           ...  \n",
       "5892  # Hypoxia/Hypercarbia: likely in the setting o...        Direct  \n",
       "5893  # Diastolic HF: doesn't seem to be in overt he...      Indirect  \n",
       "5894  # DM2: not on home medications; fingersticks h...      Indirect  \n",
       "5895  # Depression/Anxiety: cont home antidepressant...       Neither  \n",
       "5896  # FEN:\\n   - hold IVF\\n   - replete electrolyt...  Not Relevant  \n",
       "\n",
       "[5897 rows x 24 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with pd.option_context(\"display.max_columns\", None):\n",
    "    display(pt_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f778cfcc-0b72-4857-8d2e-62b952f84605",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ADMITTIME', 'DISCHTIME',\n",
       "       'DEATHTIME', 'ADMISSION_TYPE', 'ADMISSION_LOCATION',\n",
       "       'DISCHARGE_LOCATION', 'INSURANCE', 'LANGUAGE', 'RELIGION',\n",
       "       'MARITAL_STATUS', 'ETHNICITY', 'EDREGTIME', 'EDOUTTIME', 'DIAGNOSIS',\n",
       "       'HOSPITAL_EXPIRE_FLAG', 'HAS_CHARTEVENTS_DATA', 'ROW ID', 'HADM ID',\n",
       "       'Assessment', 'Plan Subsection', 'Relation'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt_ids.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "91ade7af-e388-4093-b6af-ceecb0084362",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids = pt_ids.astype({\n",
    "    'ROW_ID':int, \n",
    "    'SUBJECT_ID':int,\n",
    "    'HADM_ID':int,\n",
    "    'ADMITTIME':\"datetime64[ns]\",\n",
    "    'DISCHTIME':\"datetime64[ns]\",\n",
    "    'DEATHTIME':\"datetime64[ns]\",\n",
    "    \"EDREGTIME\":\"datetime64[ns]\",\n",
    "    \"EDOUTTIME\":\"datetime64[ns]\",\n",
    "    'HOSPITAL_EXPIRE_FLAG':int,\n",
    "    'HAS_CHARTEVENTS_DATA':int,\n",
    "    'ROW ID':int,\n",
    "    'HADM ID':int,\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "0d210b8c-5014-4e86-9cb3-9f07a206ec12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Index(['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ADMITTIME', 'DISCHTIME',\n",
       "        'DEATHTIME', 'ADMISSION_TYPE', 'ADMISSION_LOCATION',\n",
       "        'DISCHARGE_LOCATION', 'INSURANCE', 'LANGUAGE', 'RELIGION',\n",
       "        'MARITAL_STATUS', 'ETHNICITY', 'EDREGTIME', 'EDOUTTIME', 'DIAGNOSIS',\n",
       "        'HOSPITAL_EXPIRE_FLAG', 'HAS_CHARTEVENTS_DATA', 'ROW ID', 'HADM ID',\n",
       "        'Assessment', 'Plan Subsection', 'Relation'],\n",
       "       dtype='object'),\n",
       " Index(['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'SEQ_NUM', 'ICD9_CODE'], dtype='object'),\n",
       " Index(['ROW_ID', 'SUBJECT_ID', 'GENDER', 'DOB', 'DOD', 'DOD_HOSP', 'DOD_SSN',\n",
       "        'EXPIRE_FLAG'],\n",
       "       dtype='object'))"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt_ids.columns, primary_diags.columns, pts.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "3eab9d54-78da-4e8d-b468-f16f2b03fd14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5897, 24)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt_ids.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "dc580b3f-20a7-4f22-a982-2bdc188f0c1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags = pt_ids.merge(primary_diags[['HADM_ID', \"ICD9_CODE\"]], on=\"HADM_ID\", how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "a8607b05-7535-40d0-8d6c-185a3900ab1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags = pt_ids_with_diags.merge(pts[['SUBJECT_ID', 'GENDER', 'DOB']], on=\"SUBJECT_ID\", how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a366075-cc26-4e18-bccb-a9c1d299e1b3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "6f92162a-8af9-46ef-9b4b-5455e60caba3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Index(['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ADMITTIME', 'DISCHTIME',\n",
       "        'DEATHTIME', 'ADMISSION_TYPE', 'ADMISSION_LOCATION',\n",
       "        'DISCHARGE_LOCATION', 'INSURANCE', 'LANGUAGE', 'RELIGION',\n",
       "        'MARITAL_STATUS', 'ETHNICITY', 'EDREGTIME', 'EDOUTTIME', 'DIAGNOSIS',\n",
       "        'HOSPITAL_EXPIRE_FLAG', 'HAS_CHARTEVENTS_DATA', 'ROW ID', 'HADM ID',\n",
       "        'Assessment', 'Plan Subsection', 'Relation', 'ICD9_CODE', 'GENDER',\n",
       "        'DOB'],\n",
       "       dtype='object'),\n",
       " Index(['ROW_ID', 'ICD9_CODE', 'SHORT_TITLE', 'LONG_TITLE'], dtype='object'))"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt_ids_with_diags.columns, icd_map.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "49b664b0-a4e3-4c4c-8189-b5a27c706d3b",
   "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>ROW_ID</th>\n",
       "      <th>ICD9_CODE</th>\n",
       "      <th>SHORT_TITLE</th>\n",
       "      <th>LONG_TITLE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>609</th>\n",
       "      <td>660</td>\n",
       "      <td>0389</td>\n",
       "      <td>Septicemia NOS</td>\n",
       "      <td>Unspecified septicemia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ROW_ID ICD9_CODE     SHORT_TITLE              LONG_TITLE\n",
       "609     660      0389  Septicemia NOS  Unspecified septicemia"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "icd_map[icd_map['ICD9_CODE'] == \"0389\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "fcecf98d-a5e5-43a6-93b7-7e6454ad7307",
   "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ADMITTIME</th>\n",
       "      <th>DISCHTIME</th>\n",
       "      <th>DEATHTIME</th>\n",
       "      <th>ADMISSION_TYPE</th>\n",
       "      <th>ADMISSION_LOCATION</th>\n",
       "      <th>DISCHARGE_LOCATION</th>\n",
       "      <th>INSURANCE</th>\n",
       "      <th>...</th>\n",
       "      <th>HOSPITAL_EXPIRE_FLAG</th>\n",
       "      <th>HAS_CHARTEVENTS_DATA</th>\n",
       "      <th>ROW ID</th>\n",
       "      <th>HADM ID</th>\n",
       "      <th>Assessment</th>\n",
       "      <th>Plan Subsection</th>\n",
       "      <th>Relation</th>\n",
       "      <th>ICD9_CODE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>DOB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5459</th>\n",
       "      <td>58454</td>\n",
       "      <td>98347</td>\n",
       "      <td>110484</td>\n",
       "      <td>2121-10-27 12:30:00</td>\n",
       "      <td>2121-11-06 17:43:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>700410</td>\n",
       "      <td>110484</td>\n",
       "      <td>49 yo man with presumed CAD, DM who presents w...</td>\n",
       "      <td>Aspiration PNA - Febrile with WBC count elevat...</td>\n",
       "      <td>Direct</td>\n",
       "      <td>99672</td>\n",
       "      <td>M</td>\n",
       "      <td>2073-09-17 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5799</th>\n",
       "      <td>57554</td>\n",
       "      <td>95611</td>\n",
       "      <td>105027</td>\n",
       "      <td>2125-09-18 06:22:00</td>\n",
       "      <td>2125-10-08 17:35:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>493359</td>\n",
       "      <td>105027</td>\n",
       "      <td>37 year old female with acute sepsis secondary...</td>\n",
       "      <td>Sinus Tachycardia: Peaked to the 140s yesterda...</td>\n",
       "      <td>Direct</td>\n",
       "      <td>59381</td>\n",
       "      <td>F</td>\n",
       "      <td>2088-06-30 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2572</th>\n",
       "      <td>44871</td>\n",
       "      <td>55881</td>\n",
       "      <td>192318</td>\n",
       "      <td>2130-03-21 19:58:00</td>\n",
       "      <td>2130-03-23 14:37:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Private</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>560683</td>\n",
       "      <td>192318</td>\n",
       "      <td>Assessment and Plan Mr. [**Known lastname 512*...</td>\n",
       "      <td># Hypertension. Patient hypertensive on floor ...</td>\n",
       "      <td>Indirect</td>\n",
       "      <td>9351</td>\n",
       "      <td>M</td>\n",
       "      <td>2070-07-15 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1953</th>\n",
       "      <td>41310</td>\n",
       "      <td>44890</td>\n",
       "      <td>150042</td>\n",
       "      <td>2129-05-21 16:31:00</td>\n",
       "      <td>2129-05-28 12:29:00</td>\n",
       "      <td>2129-05-28 12:29:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>467544</td>\n",
       "      <td>150042</td>\n",
       "      <td>The patient is a 73 yo man with h/o PVD, DM, C...</td>\n",
       "      <td>Non-AG metabolic acidosis: Most likely RTA as ...</td>\n",
       "      <td>Neither</td>\n",
       "      <td>27652</td>\n",
       "      <td>M</td>\n",
       "      <td>2055-10-28 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5170</th>\n",
       "      <td>53814</td>\n",
       "      <td>83653</td>\n",
       "      <td>147696</td>\n",
       "      <td>2181-04-30 16:52:00</td>\n",
       "      <td>2181-05-08 17:39:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>SNF</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>734034</td>\n",
       "      <td>147696</td>\n",
       "      <td>Mr. [**Known lastname 12424**] is a [**Age ove...</td>\n",
       "      <td>Tophaceous gout: On chronic steroids, allopuri...</td>\n",
       "      <td>Neither</td>\n",
       "      <td>4260</td>\n",
       "      <td>M</td>\n",
       "      <td>1881-04-30 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2100</th>\n",
       "      <td>41599</td>\n",
       "      <td>45801</td>\n",
       "      <td>101382</td>\n",
       "      <td>2180-08-24 19:17:00</td>\n",
       "      <td>2180-08-29 05:04:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>694549</td>\n",
       "      <td>101382</td>\n",
       "      <td>67 y/o blind, deaf male w/ESRD, s/p cath on [*...</td>\n",
       "      <td>Hypotension: Currently on dopamine given perip...</td>\n",
       "      <td>Direct</td>\n",
       "      <td>41071</td>\n",
       "      <td>M</td>\n",
       "      <td>2112-12-30 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3117</th>\n",
       "      <td>47791</td>\n",
       "      <td>64911</td>\n",
       "      <td>103718</td>\n",
       "      <td>2106-10-03 17:59:00</td>\n",
       "      <td>2106-10-12 13:49:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Private</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>560683</td>\n",
       "      <td>103718</td>\n",
       "      <td>57 y/oM with h/o HCV and Alcohol abuse, admitt...</td>\n",
       "      <td>ICU Care\\n   Nutrition:\\n   Glycemic Control:\\...</td>\n",
       "      <td>Not Relevant</td>\n",
       "      <td>5770</td>\n",
       "      <td>M</td>\n",
       "      <td>2049-06-29 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2740</th>\n",
       "      <td>41408</td>\n",
       "      <td>45213</td>\n",
       "      <td>197986</td>\n",
       "      <td>2106-01-31 22:50:00</td>\n",
       "      <td>2106-02-07 12:50:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Medicaid</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>715731</td>\n",
       "      <td>197986</td>\n",
       "      <td>41 year old female with strange and complicate...</td>\n",
       "      <td># HTN: Well controlled, continue home regimen</td>\n",
       "      <td>Neither</td>\n",
       "      <td>4232</td>\n",
       "      <td>F</td>\n",
       "      <td>2064-07-04 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>750</th>\n",
       "      <td>18196</td>\n",
       "      <td>14873</td>\n",
       "      <td>197497</td>\n",
       "      <td>2175-05-10 09:38:00</td>\n",
       "      <td>2175-05-26 17:26:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>378689</td>\n",
       "      <td>197497</td>\n",
       "      <td>66 yo woman with ESRD on HD with recent line s...</td>\n",
       "      <td># Sepsis/fever: Hypotension resolved; no leuko...</td>\n",
       "      <td>Direct</td>\n",
       "      <td>99931</td>\n",
       "      <td>F</td>\n",
       "      <td>2108-05-17 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1562</th>\n",
       "      <td>41055</td>\n",
       "      <td>44083</td>\n",
       "      <td>198330</td>\n",
       "      <td>2112-05-28 15:45:00</td>\n",
       "      <td>2112-06-07 16:50:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Private</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>580332</td>\n",
       "      <td>198330</td>\n",
       "      <td>54-yo man with hypertension, dyslipidemia, loc...</td>\n",
       "      <td># Pericardial effusion: Patient is s/p pericar...</td>\n",
       "      <td>Direct</td>\n",
       "      <td>4239</td>\n",
       "      <td>M</td>\n",
       "      <td>2057-11-15 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ROW_ID  SUBJECT_ID  HADM_ID           ADMITTIME           DISCHTIME  \\\n",
       "5459   58454       98347   110484 2121-10-27 12:30:00 2121-11-06 17:43:00   \n",
       "5799   57554       95611   105027 2125-09-18 06:22:00 2125-10-08 17:35:00   \n",
       "2572   44871       55881   192318 2130-03-21 19:58:00 2130-03-23 14:37:00   \n",
       "1953   41310       44890   150042 2129-05-21 16:31:00 2129-05-28 12:29:00   \n",
       "5170   53814       83653   147696 2181-04-30 16:52:00 2181-05-08 17:39:00   \n",
       "2100   41599       45801   101382 2180-08-24 19:17:00 2180-08-29 05:04:00   \n",
       "3117   47791       64911   103718 2106-10-03 17:59:00 2106-10-12 13:49:00   \n",
       "2740   41408       45213   197986 2106-01-31 22:50:00 2106-02-07 12:50:00   \n",
       "750    18196       14873   197497 2175-05-10 09:38:00 2175-05-26 17:26:00   \n",
       "1562   41055       44083   198330 2112-05-28 15:45:00 2112-06-07 16:50:00   \n",
       "\n",
       "               DEATHTIME ADMISSION_TYPE         ADMISSION_LOCATION  \\\n",
       "5459                 NaT      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "5799                 NaT      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "2572                 NaT      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "1953 2129-05-28 12:29:00      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "5170                 NaT      EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "2100                 NaT      EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "3117                 NaT      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "2740                 NaT      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "750                  NaT      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "1562                 NaT      EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "\n",
       "     DISCHARGE_LOCATION INSURANCE  ... HOSPITAL_EXPIRE_FLAG  \\\n",
       "5459   HOME HEALTH CARE  Medicaid  ...                    0   \n",
       "5799               HOME  Medicaid  ...                    0   \n",
       "2572               HOME   Private  ...                    0   \n",
       "1953       DEAD/EXPIRED  Medicare  ...                    1   \n",
       "5170                SNF  Medicare  ...                    0   \n",
       "2100   HOME HEALTH CARE  Medicare  ...                    0   \n",
       "3117               HOME   Private  ...                    0   \n",
       "2740               HOME  Medicaid  ...                    0   \n",
       "750    HOME HEALTH CARE  Medicare  ...                    0   \n",
       "1562               HOME   Private  ...                    0   \n",
       "\n",
       "     HAS_CHARTEVENTS_DATA  ROW ID HADM ID  \\\n",
       "5459                    1  700410  110484   \n",
       "5799                    1  493359  105027   \n",
       "2572                    1  560683  192318   \n",
       "1953                    1  467544  150042   \n",
       "5170                    1  734034  147696   \n",
       "2100                    1  694549  101382   \n",
       "3117                    1  560683  103718   \n",
       "2740                    1  715731  197986   \n",
       "750                     1  378689  197497   \n",
       "1562                    1  580332  198330   \n",
       "\n",
       "                                             Assessment  \\\n",
       "5459  49 yo man with presumed CAD, DM who presents w...   \n",
       "5799  37 year old female with acute sepsis secondary...   \n",
       "2572  Assessment and Plan Mr. [**Known lastname 512*...   \n",
       "1953  The patient is a 73 yo man with h/o PVD, DM, C...   \n",
       "5170  Mr. [**Known lastname 12424**] is a [**Age ove...   \n",
       "2100  67 y/o blind, deaf male w/ESRD, s/p cath on [*...   \n",
       "3117  57 y/oM with h/o HCV and Alcohol abuse, admitt...   \n",
       "2740  41 year old female with strange and complicate...   \n",
       "750   66 yo woman with ESRD on HD with recent line s...   \n",
       "1562  54-yo man with hypertension, dyslipidemia, loc...   \n",
       "\n",
       "                                        Plan Subsection      Relation  \\\n",
       "5459  Aspiration PNA - Febrile with WBC count elevat...        Direct   \n",
       "5799  Sinus Tachycardia: Peaked to the 140s yesterda...        Direct   \n",
       "2572  # Hypertension. Patient hypertensive on floor ...      Indirect   \n",
       "1953  Non-AG metabolic acidosis: Most likely RTA as ...       Neither   \n",
       "5170  Tophaceous gout: On chronic steroids, allopuri...       Neither   \n",
       "2100  Hypotension: Currently on dopamine given perip...        Direct   \n",
       "3117  ICU Care\\n   Nutrition:\\n   Glycemic Control:\\...  Not Relevant   \n",
       "2740      # HTN: Well controlled, continue home regimen       Neither   \n",
       "750   # Sepsis/fever: Hypotension resolved; no leuko...        Direct   \n",
       "1562  # Pericardial effusion: Patient is s/p pericar...        Direct   \n",
       "\n",
       "      ICD9_CODE  GENDER                  DOB  \n",
       "5459      99672       M  2073-09-17 00:00:00  \n",
       "5799      59381       F  2088-06-30 00:00:00  \n",
       "2572       9351       M  2070-07-15 00:00:00  \n",
       "1953      27652       M  2055-10-28 00:00:00  \n",
       "5170       4260       M  1881-04-30 00:00:00  \n",
       "2100      41071       M  2112-12-30 00:00:00  \n",
       "3117       5770       M  2049-06-29 00:00:00  \n",
       "2740       4232       F  2064-07-04 00:00:00  \n",
       "750       99931       F  2108-05-17 00:00:00  \n",
       "1562       4239       M  2057-11-15 00:00:00  \n",
       "\n",
       "[10 rows x 27 columns]"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt_ids_with_diags.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "b307f4ac-efbc-49ce-9de0-c6326497b138",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags = pt_ids_with_diags.merge(icd_map[['ICD9_CODE', \"LONG_TITLE\"]], on=\"ICD9_CODE\", how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "36914e77-d634-4124-a70a-817592ad6af3",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags = pt_ids_with_diags.astype({\n",
    "    'GENDER':str,\n",
    "    'DOB':\"datetime64[ns]\",\n",
    "    'ICD9_CODE':str,\n",
    "    \"LONG_TITLE\":str\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "844ee1eb-e433-403e-80d7-995eac35851f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags = pt_ids_with_diags.rename({\"LONG_TITLE\":\"PRIMARY_DIAGNOSIS\"}, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "1c326af0-149a-4b46-9326-0d30a2b522c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags['ICD_CATEGORY'] = pt_ids_with_diags['ICD9_CODE'].str.slice(0,2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "69191225-b324-40ef-a504-af54c4d4f0e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags = pt_ids_with_diags.merge(icd_map[['ICD9_CODE', \"LONG_TITLE\"]], left_on='ICD_CATEGORY', right_on=\"ICD9_CODE\", how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "fd1ff331-66d0-48ab-b176-f10469834efe",
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "3a64abef-0114-4fce-92e5-2051c788b0dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids_with_diags['FINAL_PRIMARY_DIAGNOSIS'] = pt_ids_with_diags['LONG_TITLE'].fillna(pt_ids_with_diags['PRIMARY_DIAGNOSIS']).tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "3d56d2a9-eda7-4827-80f7-8eb10dd2d256",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\n",
    "    # 'ADMITTIME', 'DISCHTIME',\n",
    "    #    'DEATHTIME',\n",
    "    'ADMISSION_TYPE',\n",
    "    # 'ADMISSION_LOCATION',\n",
    "       'DISCHARGE_LOCATION', 'INSURANCE', 'LANGUAGE', \n",
    "       # 'MARITAL_STATUS', \n",
    "    'ETHNICITY',\n",
    "    \n",
    "    # \"RELIGION\",\n",
    "    # 'EDREGTIME', 'EDOUTTIME', \n",
    "    # 'DIAGNOSIS',\n",
    "       # 'HOSPITAL_EXPIRE_FLAG', \n",
    "           \"Relation\",\n",
    "    # \"ICD_CATEGORY\",\n",
    "'GENDER',\n",
    "# 'FINAL_PRIMARY_DIAGNOSIS'    \n",
    "          ]\n",
    "\n",
    "groupby = 'Relation'\n",
    "\n",
    "categorical = ['ADMISSION_TYPE',\"DISCHARGE_LOCATION\", \"INSURANCE\", \"LANGUAGE\", \n",
    " \"ETHNICITY\", \n",
    "               # \"FINAL_PRIMARY_DIAGNOSIS\", \n",
    "               \"Relation\", 'GENDER',    \n",
    "               # \"ICD_CATEGORY\",\n",
    "              ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "cf184552-0a44-4689-8142-d2e5ad1bb24b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids = pt_ids.merge(pts[['SUBJECT_ID', 'GENDER', 'DOB']], on=\"SUBJECT_ID\", how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "33e251cb-f832-48c0-b73f-f0c57c4556f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ADMITTIME', 'DISCHTIME',\n",
       "       'DEATHTIME', 'ADMISSION_TYPE', 'ADMISSION_LOCATION',\n",
       "       'DISCHARGE_LOCATION', 'INSURANCE', 'LANGUAGE', 'RELIGION',\n",
       "       'MARITAL_STATUS', 'ETHNICITY', 'EDREGTIME', 'EDOUTTIME', 'DIAGNOSIS',\n",
       "       'HOSPITAL_EXPIRE_FLAG', 'HAS_CHARTEVENTS_DATA', 'ROW ID', 'HADM ID',\n",
       "       'Assessment', 'Plan Subsection', 'Relation', 'GENDER', 'DOB'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt_ids.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "acb24412-89e2-4d79-9e7f-b2aaa78b6cc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pt_ids_with_diags.loc[pt_ids_with_diags['LANGUAGE'].isin([\"*CHI\", \"CANT\", \"MAND\", \"VIET\"]), \"LANGUAGE\"] =  \"East Asian\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['LANGUAGE'].isin([\"ITAL\", \"SPAN\", \"PORT\"]), \"LANGUAGE\"] =  \"Romance\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['LANGUAGE'].isin([\"ETHI\", \"HAIT\"]), \"LANGUAGE\"] =  \"African\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['LANGUAGE'].isin([\"PERS\", \"*FAR\"]), \"LANGUAGE\"] =  \"Middle Eastern\"\n",
    "# # pt_ids_with_diags.loc[~pt_ids_with_diags['LANGUAGE'].isin([\"ENGL\", \"SPAN\"]), \"LANGUAGE\"] =  \"OTHER\"\n",
    "\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['ETHNICITY'].isin([\"ASIAN\", \"ASIAN - ASIAN INDIAN\", \"ASIAN - CHINESE\",\n",
    "#                                                             \"ASIAN - FILIPINO\", \"ASIAN - JAPANESE\",\"ASIAN - OTHER\",\n",
    "#                                                            ]), \"ETHNICITY\"] =  \"ASIAN\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['ETHNICITY'].isin([\"BLACK/AFRICAN AMERICAN\", \"BLACK/CAPE VERDEAN\", \"BLACK/HAITIAN\", \n",
    "#                                                            ]), \"ETHNICITY\"] =  \"BLACK\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['ETHNICITY'].isin([\"HISPANIC OR LATINO\", \"HISPANIC/LATINO - MEXICAN\", \n",
    "#                                                            \"HISPANIC/LATINO - PUERTO RICAN\", \"HISPANIC/LATINO - SALVADORAN\",  \n",
    "#                                                            ]), \"ETHNICITY\"] =  \"HISPANIC/LATINO\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['ETHNICITY'].isin([\"WHITE\", \"WHITE - OTHER EUROPEAN\", \"WHITE - RUSSIAN\",   \n",
    "#                                                            ]), \"ETHNICITY\"] =  \"WHITE\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['ETHNICITY'].isin([\"PORTUGUESE\",   \n",
    "#                                                            ]), \"ETHNICITY\"] =  \"OTHER\"\n",
    "# pt_ids_with_diags.loc[pt_ids_with_diags['ETHNICITY'].isin([\"AMERICAN INDIAN/ALASKA NATIVE\",   \"AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE\"\n",
    "#                                                            ]), \"ETHNICITY\"] =  \"AMERICAN INDIAN\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "372d9ca2-f3b1-43db-a848-6ffa19b71a3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt_ids.loc[pt_ids['LANGUAGE'].isin([\"*CHI\", \"CANT\", \"MAND\", \"VIET\"]), \"LANGUAGE\"] =  \"East Asian\"\n",
    "pt_ids.loc[pt_ids['LANGUAGE'].isin([\"ITAL\", \"SPAN\", \"PORT\"]), \"LANGUAGE\"] =  \"Romance\"\n",
    "pt_ids.loc[pt_ids['LANGUAGE'].isin([\"ETHI\", \"HAIT\"]), \"LANGUAGE\"] =  \"African\"\n",
    "pt_ids.loc[pt_ids['LANGUAGE'].isin([\"PERS\", \"*FAR\"]), \"LANGUAGE\"] =  \"Middle Eastern\"\n",
    "# pt_ids.loc[~pt_ids['LANGUAGE'].isin([\"ENGL\", \"SPAN\"]), \"LANGUAGE\"] =  \"OTHER\"\n",
    "\n",
    "pt_ids.loc[pt_ids['ETHNICITY'].isin([\"ASIAN\", \"ASIAN - ASIAN INDIAN\", \"ASIAN - CHINESE\",\n",
    "                                                            \"ASIAN - FILIPINO\", \"ASIAN - JAPANESE\",\"ASIAN - OTHER\",\n",
    "                                                           ]), \"ETHNICITY\"] =  \"ASIAN\"\n",
    "pt_ids.loc[pt_ids['ETHNICITY'].isin([\"BLACK/AFRICAN AMERICAN\", \"BLACK/CAPE VERDEAN\", \"BLACK/HAITIAN\", \n",
    "                                                           ]), \"ETHNICITY\"] =  \"BLACK\"\n",
    "pt_ids.loc[pt_ids['ETHNICITY'].isin([\"HISPANIC OR LATINO\", \"HISPANIC/LATINO - MEXICAN\", \n",
    "                                                           \"HISPANIC/LATINO - PUERTO RICAN\", \"HISPANIC/LATINO - SALVADORAN\",  \n",
    "                                                           ]), \"ETHNICITY\"] =  \"HISPANIC/LATINO\"\n",
    "pt_ids.loc[pt_ids['ETHNICITY'].isin([\"WHITE\", \"WHITE - OTHER EUROPEAN\", \"WHITE - RUSSIAN\",   \n",
    "                                                           ]), \"ETHNICITY\"] =  \"WHITE\"\n",
    "pt_ids.loc[pt_ids['ETHNICITY'].isin([\"PORTUGUESE\",   \n",
    "                                                           ]), \"ETHNICITY\"] =  \"OTHER\"\n",
    "pt_ids.loc[pt_ids['ETHNICITY'].isin([\"AMERICAN INDIAN/ALASKA NATIVE\",   \"AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE\"\n",
    "                                                           ]), \"ETHNICITY\"] =  \"AMERICAN INDIAN\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "57a5f9ef-f29c-45ac-8d96-548f884d1388",
   "metadata": {},
   "outputs": [],
   "source": [
    "mytable = TableOne(pt_ids, columns=columns, categorical=categorical, pval=False, groupby=groupby)\n",
    "# print(mytable.tabulate(tablefmt = \"fancy_grid\"))\n",
    "mytable.to_excel('n2c2_tableone_all.xlsx')\n",
    "\n",
    "# with pd.option_context(\"display.max_rows\", None):\n",
    "#     display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f584c4fd-0e93-4c8d-9210-d0abdfa8ecf1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "9cf9d66a-18df-45e5-80d7-7cee32adbc63",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4280     2286\n",
       "5849     2055\n",
       "4019     2034\n",
       "42731    1985\n",
       "51881    1558\n",
       "         ... \n",
       "29532       2\n",
       "33520       2\n",
       "4279        2\n",
       "99642       2\n",
       "6010        2\n",
       "Name: ICD9_CODE, Length: 1562, dtype: int64"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diags.merge(data, left_on=\"HADM_ID\", right_on=\"HADM ID\")['ICD9_CODE'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "ee345556-0943-4d8c-98b2-9c966ca1d588",
   "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>SEQ_NUM</th>\n",
       "      <th>ICD9_CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1297</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1298</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>2.0</td>\n",
       "      <td>486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1299</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>3.0</td>\n",
       "      <td>58281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1300</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1301</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651042</th>\n",
       "      <td>639798</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651043</th>\n",
       "      <td>639799</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>3.0</td>\n",
       "      <td>V5869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651044</th>\n",
       "      <td>639800</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V1279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651045</th>\n",
       "      <td>639801</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651046</th>\n",
       "      <td>639802</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5569</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>651047 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ROW_ID  SUBJECT_ID  HADM_ID  SEQ_NUM ICD9_CODE\n",
       "0         1297         109   172335      1.0     40301\n",
       "1         1298         109   172335      2.0       486\n",
       "2         1299         109   172335      3.0     58281\n",
       "3         1300         109   172335      4.0      5855\n",
       "4         1301         109   172335      5.0      4254\n",
       "...        ...         ...      ...      ...       ...\n",
       "651042  639798       97503   188195      2.0     20280\n",
       "651043  639799       97503   188195      3.0     V5869\n",
       "651044  639800       97503   188195      4.0     V1279\n",
       "651045  639801       97503   188195      5.0      5275\n",
       "651046  639802       97503   188195      6.0      5569\n",
       "\n",
       "[651047 rows x 5 columns]"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3dd5107-1fb4-4c46-aafa-e07fe5bb5e46",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4b98de7-6485-4ba3-b134-93ce046a55b6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}