1) Create Patient Admits Dataframe.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview\n",
    "This notebook creates an initial dataframe of unique patient admissions and ICU stays alongside key demographics.\n",
    "Makes use of 3 MIMIC-III tables: Patients, Admissions, and ICUStays.\n",
    "\n",
    "## Notebook sections\n",
    "#### a) Library Import and BigQuery Auth Setup for utilizing SQL\n",
    "#### b) Data Cleaning Key Activities:\n",
    "    1) Ethnicities mapped to smaller groups in a new columns\n",
    "    2) Ages > 300 represent patients aver age 89 and are recoded as age 90\n",
    "#### c) Labeling of patients for potential exclusion:\n",
    "    1) lack of ICU stay\n",
    "    2) young age and therefore minimal likelihood of acquiring C Diff infection\n",
    "    3) Pre-identified C. Diff or diarrhea upon admission to hospital\n",
    "\n",
    "## Output\n",
    "#### Two files:\n",
    "    1) Patient Admits dataframe\n",
    "    2) ICU Stays dataframe     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A) Import libraries and set up BigQuery authorization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import google bigquery and authorization library, and set up client and project name for billing\n",
    "from google.cloud import bigquery\n",
    "from google_auth_oauthlib import flow\n",
    "\n",
    "client = bigquery.Client()\n",
    "\n",
    "project = 'mimiciii-249117'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries for analysis within notebook\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Next three cells launch browser and run through end-user login for access to mimic-iii project hosted on BigQuery \n",
    "launch_browser = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=579414793473-ak30u31klvdecqfv6kg8542ofeeegm18.apps.googleusercontent.com&redirect_uri=http%3A%2F%2Flocalhost%3A8080%2F&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fbigquery&state=8RExhDaHPAUUCyHNTgIiZ7AamioQiB&access_type=offline&code_challenge=FQrNPym0fPLjxzbVm8O8EJ4WCzlcUErLW2TUp28s-2c&code_challenge_method=S256\n"
     ]
    }
   ],
   "source": [
    "appflow = flow.InstalledAppFlow.from_client_secrets_file(\n",
    "    'C:/Users/fuzzyman27/BigQuery/client_secrets.json',\n",
    "    scopes=['https://www.googleapis.com/auth/bigquery'])\n",
    "\n",
    "if launch_browser:\n",
    "    appflow.run_local_server()\n",
    "else:\n",
    "    appflow.run_console()\n",
    "\n",
    "credentials = appflow.credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = bigquery.Client(project=project, credentials=credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define small function to run SQL queries and return a dataframe\n",
    "def run_query(query):\n",
    "    return client.query(query).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## B) Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Patient and Admissions Data\n",
    "Leverages both patients and admissions tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combines patients and admissions table and extract all fields\n",
    "# Three calculated fields include length of stay, patient age, and sequence of hospital stay\n",
    "# for each hospital admission, and sequence of hospital admission for each patientCode from here in part inspired by calculated fields here\n",
    "\n",
    "# https://github.com/MIT-LCP/mimic-code/blob/master/concepts/demographics/icustay-detail.sql\n",
    "query = \"\"\"\n",
    "    SELECT p.subject_id, p.gender, p.dob, p.dod, p.dod_hosp, p.dod_ssn,\n",
    "    p.expire_flag, a.hadm_id, a.admittime, a.dischtime, a.deathtime,\n",
    "    a.admission_type, a.admission_location, a.discharge_location,\n",
    "    a.insurance, a.language, a.religion, a.marital_status, a.ethnicity,\n",
    "    a.edregtime, a.edouttime, a.diagnosis, a.has_chartevents_data,\n",
    "    datetime_diff(a.dischtime, a.admittime, second) / (60*60*24) as hosp_los,\n",
    "    datetime_diff(a.admittime, p.dob, second) / (60*60*24*365.242) as admit_age,\n",
    "    DENSE_RANK() OVER (PARTITION BY a.subject_id ORDER BY a.admittime) as hospstay_seq\n",
    "    FROM `physionet-data.mimiciii_clinical.admissions` as a\n",
    "    JOIN `physionet-data.mimiciii_clinical.patients` as p\n",
    "    on (a.subject_id = p.subject_id)\n",
    "    ;\n",
    "\"\"\"\n",
    "patient_admits = run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Initial data cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "subject_id                       int64\n",
       "gender                          object\n",
       "dob                     datetime64[ns]\n",
       "dod                     datetime64[ns]\n",
       "dod_hosp                datetime64[ns]\n",
       "dod_ssn                 datetime64[ns]\n",
       "expire_flag                      int64\n",
       "hadm_id                          int64\n",
       "admittime               datetime64[ns]\n",
       "dischtime               datetime64[ns]\n",
       "deathtime               datetime64[ns]\n",
       "admission_type                  object\n",
       "admission_location              object\n",
       "discharge_location              object\n",
       "insurance                       object\n",
       "language                        object\n",
       "religion                        object\n",
       "marital_status                  object\n",
       "ethnicity                       object\n",
       "edregtime               datetime64[ns]\n",
       "edouttime               datetime64[ns]\n",
       "diagnosis                       object\n",
       "has_chartevents_data             int64\n",
       "hosp_los                       float64\n",
       "admit_age                      float64\n",
       "hospstay_seq                     int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check data types\n",
    "patient_admits.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "M    32950\n",
       "F    26026\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check to confirm gender has two possible values and\n",
    "# change gender to binary feature with 1 == F and 0 == M\n",
    "patient_admits[\"gender\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"gender\"] = patient_admits[\"gender\"].map(lambda x: 1 if x == \"F\" else 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check for missing values and adjust accordingly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "subject_id                  0\n",
       "gender                      0\n",
       "dob                         0\n",
       "dod                     36390\n",
       "dod_hosp                43905\n",
       "dod_ssn                 39907\n",
       "expire_flag                 0\n",
       "hadm_id                     0\n",
       "admittime                   0\n",
       "dischtime                   0\n",
       "deathtime               53122\n",
       "admission_type              0\n",
       "admission_location          0\n",
       "discharge_location          0\n",
       "insurance                   0\n",
       "language                25332\n",
       "religion                  458\n",
       "marital_status          10128\n",
       "ethnicity                   0\n",
       "edregtime               28099\n",
       "edouttime               28099\n",
       "diagnosis                  25\n",
       "has_chartevents_data        0\n",
       "hosp_los                    0\n",
       "admit_age                   0\n",
       "hospstay_seq                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_admits.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Time of death missing values\n",
    "There are 4 main columns relating to time of death of patients.\n",
    "\n",
    "Given feature engineering will include calculating difference between time of death and other features, null values for patients still living are kept blank so as not to cause potential confusion in calculated results.\n",
    "\n",
    "Outline of column definitions:\n",
    "\n",
    "    1) DOD - overall time of death for patients\n",
    "    2) DOD_Hosp as recorded in hospital database\n",
    "    3) DOD_SSN = date of death from social \n",
    "    4) deathtime - time of death as recorded for specific hospital admission"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Emergency room in and out time missing values\n",
    "\n",
    "Similar to time of death, these values are stored as date times and are null when the characteristic is note relevant (eg patient did not utilize emergency room). To avoid potential calculation conflicts when comparing dates, these values are left as nulls"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Language, Religion, and Marital Status Missing Values\n",
    "\n",
    "Fill missing values with \"None\".\n",
    "\n",
    "If found to be an importance factor in likelihood of CDI, data imputation techniques will be considered."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"language\"].fillna(\"None\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"religion\"].fillna(value=\"None\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"marital_status\"].fillna(value=\"None\", inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Diagnosis Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill nulls for diagnosis column to enable calculations and convert to lowercase strings\n",
    "patient_admits[\"diagnosis\"].fillna(value = \"\", inplace=True)\n",
    "patient_admits[\"diagnosis\"] = patient_admits[\"diagnosis\"].str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Recategorize ethnicity\n",
    "\n",
    "Many patients are listed with two part ethnicity. Splitting these values below enables consolidating into fewer groups for analysis with larger sample sizes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "WHITE                                                       40996\n",
       "BLACK/AFRICAN AMERICAN                                       5440\n",
       "UNKNOWN/NOT SPECIFIED                                        4523\n",
       "HISPANIC OR LATINO                                           1696\n",
       "OTHER                                                        1512\n",
       "ASIAN                                                        1509\n",
       "UNABLE TO OBTAIN                                              814\n",
       "PATIENT DECLINED TO ANSWER                                    559\n",
       "ASIAN - CHINESE                                               277\n",
       "HISPANIC/LATINO - PUERTO RICAN                                232\n",
       "BLACK/CAPE VERDEAN                                            200\n",
       "WHITE - RUSSIAN                                               164\n",
       "MULTI RACE ETHNICITY                                          130\n",
       "BLACK/HAITIAN                                                 101\n",
       "ASIAN - ASIAN INDIAN                                           85\n",
       "WHITE - OTHER EUROPEAN                                         81\n",
       "HISPANIC/LATINO - DOMINICAN                                    78\n",
       "PORTUGUESE                                                     61\n",
       "WHITE - BRAZILIAN                                              59\n",
       "ASIAN - VIETNAMESE                                             53\n",
       "AMERICAN INDIAN/ALASKA NATIVE                                  51\n",
       "BLACK/AFRICAN                                                  44\n",
       "MIDDLE EASTERN                                                 43\n",
       "HISPANIC/LATINO - GUATEMALAN                                   40\n",
       "WHITE - EASTERN EUROPEAN                                       25\n",
       "ASIAN - FILIPINO                                               25\n",
       "HISPANIC/LATINO - CUBAN                                        24\n",
       "HISPANIC/LATINO - SALVADORAN                                   19\n",
       "NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER                      18\n",
       "ASIAN - OTHER                                                  17\n",
       "ASIAN - CAMBODIAN                                              17\n",
       "ASIAN - KOREAN                                                 13\n",
       "HISPANIC/LATINO - CENTRAL AMERICAN (OTHER)                     13\n",
       "HISPANIC/LATINO - MEXICAN                                      13\n",
       "HISPANIC/LATINO - COLOMBIAN                                     9\n",
       "CARIBBEAN ISLAND                                                9\n",
       "SOUTH AMERICAN                                                  8\n",
       "ASIAN - JAPANESE                                                7\n",
       "ASIAN - THAI                                                    4\n",
       "HISPANIC/LATINO - HONDURAN                                      4\n",
       "AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE        3\n",
       "Name: ethnicity, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_admits[\"ethnicity\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "split_ethnicity = patient_admits[\"ethnicity\"].str.split(\"-\")\n",
    "first_ethnicity = split_ethnicity.map(lambda x: x[0].strip())\n",
    "num_ethnicities = split_ethnicity.map(lambda x: len(x))\n",
    "second_ethnicity = split_ethnicity.map(lambda x: x[1].strip() if len(x) == 2 else \"N/A\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "WHITE                                                       41325\n",
       "BLACK/AFRICAN AMERICAN                                       5440\n",
       "UNKNOWN/NOT SPECIFIED                                        4523\n",
       "ASIAN                                                        2007\n",
       "HISPANIC OR LATINO                                           1696\n",
       "OTHER                                                        1512\n",
       "UNABLE TO OBTAIN                                              814\n",
       "PATIENT DECLINED TO ANSWER                                    559\n",
       "HISPANIC/LATINO                                               432\n",
       "BLACK/CAPE VERDEAN                                            200\n",
       "MULTI RACE ETHNICITY                                          130\n",
       "BLACK/HAITIAN                                                 101\n",
       "PORTUGUESE                                                     61\n",
       "AMERICAN INDIAN/ALASKA NATIVE                                  51\n",
       "BLACK/AFRICAN                                                  44\n",
       "MIDDLE EASTERN                                                 43\n",
       "NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER                      18\n",
       "CARIBBEAN ISLAND                                                9\n",
       "SOUTH AMERICAN                                                  8\n",
       "AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE        3\n",
       "Name: ethnicity, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_ethnicity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"first_ethnicity\"] = first_ethnicity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "N/A                         57717\n",
       "CHINESE                       277\n",
       "PUERTO RICAN                  232\n",
       "RUSSIAN                       164\n",
       "ASIAN INDIAN                   85\n",
       "OTHER EUROPEAN                 81\n",
       "DOMINICAN                      78\n",
       "BRAZILIAN                      59\n",
       "VIETNAMESE                     53\n",
       "GUATEMALAN                     40\n",
       "FILIPINO                       25\n",
       "EASTERN EUROPEAN               25\n",
       "CUBAN                          24\n",
       "SALVADORAN                     19\n",
       "CAMBODIAN                      17\n",
       "OTHER                          17\n",
       "CENTRAL AMERICAN (OTHER)       13\n",
       "KOREAN                         13\n",
       "MEXICAN                        13\n",
       "COLOMBIAN                       9\n",
       "JAPANESE                        7\n",
       "THAI                            4\n",
       "HONDURAN                        4\n",
       "Name: ethnicity, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "second_ethnicity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"second_ethnicity\"] = second_ethnicity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Adjust Age\n",
    "\n",
    "Ages above 89 are automatically shifted to age 300+ to protect patient privacy. Below cells recode age as 90. Given the goal of maintaining patient privacy, age imputation for these patients is not considered here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"admit_age\"] = patient_admits[\"admit_age\"].map(lambda x: x if x < 300 else 90.0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ICU Stays Data\n",
    "Leverages icustays table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select key ICU metrics including calculated sequence of ICU stay\n",
    "query = \"\"\"\n",
    "    SELECT subject_id, hadm_id, icustay_id, intime, outtime, los,\n",
    "    DENSE_RANK() OVER (PARTITION BY hadm_id, subject_id ORDER BY intime) as icu_stay_seq\n",
    "    FROM `physionet-data.mimiciii_clinical.icustays`\n",
    "    ;\n",
    "\"\"\"\n",
    "icu_stays = run_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate LOS metics across all ICU stays for a given hospital admission\n",
    "query = \"\"\"\n",
    "    SELECT icu.hadm_id as hadm_id, sum(icu.LOS) as admit_icu_los,\n",
    "    count(icu.icustay_id) as admit_icu_stays\n",
    "    FROM `physionet-data.mimiciii_clinical.icustays` as icu\n",
    "    GROUP BY icu.hadm_id\n",
    "    ;\n",
    "\"\"\"\n",
    "icu_stats_by_admit = run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "icu_stays.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "icu_stats_by_admit.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "there are 10 icu stays for which there is no recorded out time and therefore no LOS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Determine which subjects and hospital admissions are associated with a\n",
    "# missing ICU LOS\n",
    "icu_stays[icu_stays[\"los\"].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create list of hadm id's associated with missing icu LOS\n",
    "missing_icu_los_hadm = icu_stays[icu_stays[\"los\"].isnull()][\"hadm_id\"].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Filter ICU stays by hadm id's\n",
    "icu_stays[icu_stays[\"hadm_id\"].isin(missing_icu_los_hadm)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter ICU stats hadm id's\n",
    "icu_stats_by_admit[icu_stats_by_admit[\"hadm_id\"].isin(missing_icu_los_hadm)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter patient_admits by hadm id's\n",
    "patient_admits[(patient_admits[\"hadm_id\"].isin(missing_icu_los_hadm))][[\"subject_id\",\n",
    "                                                                      \"dod\",\n",
    "                                                                      \"dod_hosp\",\n",
    "                                                                      \"dod_ssn\",\n",
    "                                                                      \"expire_flag\",\n",
    "                                                                      \"hadm_id\",\n",
    "                                                                      \"admission_location\",\n",
    "                                                                      \"discharge_location\",\n",
    "                                                                      \"hosp_los\",\n",
    "                                                                      \"admittime\",\n",
    "                                                                      \"dischtime\",\n",
    "                                                                      \"edregtime\",\n",
    "                                                                      \"edouttime\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "all 10 patients with missing ICU outtimes do have hospital discharge times and were only in 1 unique ICU stay over the course of the hospital visit. As such, will label ICU outtime and LOS with overall hospital discharge time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for icu_index in icu_stays[icu_stays[\"hadm_id\"].isin(missing_icu_los_hadm)].index:\n",
    "    hadm_id = icu_stays.loc[icu_index, \"hadm_id\"]\n",
    "    icu_stats_index = icu_stats_by_admit[icu_stats_by_admit[\"hadm_id\"] == hadm_id].index[0]\n",
    "    outtime = patient_admits[patient_admits[\"hadm_id\"] == hadm_id][\"dischtime\"].to_list()[0]\n",
    "    icu_stays.loc[icu_index, \"outtime\"] = outtime\n",
    "    intime = icu_stays.loc[icu_index, \"intime\"]\n",
    "    los = (outtime - np.timedelta64(0,'s')) - ((intime - np.timedelta64(0,'s')))\n",
    "    icu_stats_by_admit.loc[icu_stats_index,\"admit_icu_los\"]=los\n",
    "    icu_stays.loc[icu_index, \"los\"] = los"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merge ICU Stats and Patient Admits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits = pd.merge(patient_admits, icu_stats_by_admit, how = \"outer\", left_on = \"hadm_id\", right_on = \"hadm_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## C) Label Patient Admits For Potential Exclusion\n",
    "\n",
    "1) Patients labeled as having CDI upon admission will not be considered for further analysis\n",
    "\n",
    "2) Patients without an ICU stay\n",
    "\n",
    "3) Patients with Diarrhea Upon Admission - depending on whether or not CDI testing was performed close to admission, these patients may be excluded\n",
    "\n",
    "4) Admissions for Newborns and \n",
    "5) Babies under 1 year old - Newborns and babies under 1 do not have fully formed microbiomes yet and are often colonized with clostridium difficile without getting an infection. Further, newborns tend to have diarrhea that is mainly caused by other factors (viral infection most commonly)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1) CDI Upon Admission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Patients with noted C Diff at admission are already at least suspected of infection\n",
    "# and for whome risk prediction is not useful\n",
    "patient_admits[\"cdi_at_admit\"] = ((patient_admits[\"diagnosis\"].str.contains(\"difficile\")) |\n",
    "                                  (patient_admits[\"diagnosis\"].str.contains(\"c-diff\")) |\n",
    "                                  (patient_admits[\"diagnosis\"].str.contains(\"cdiff\")) |\n",
    "                                  (patient_admits[\"diagnosis\"].str.contains(\"c diff\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 2) No ICU Stay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create binary variable for no ICU stays\n",
    "patient_admits[\"no_icu_stay\"] = patient_admits[\"admit_icu_stays\"].isnull().map(lambda x: 1 if x == True else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace null icu stays with 0\n",
    "patient_admits[\"admit_icu_stays\"].fillna(value=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Replace null icu LOS with 0\n",
    "patient_admits[\"admit_icu_los\"].fillna(value=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Correct for icu_los stored as timedelta and not float\n",
    "for index in patient_admits.index:\n",
    "    row_type = type(patient_admits.loc[index,\"admit_icu_los\"])\n",
    "    if(row_type != float):\n",
    "        if((row_type == int)):\n",
    "            patient_admits.loc[index,\"admit_icu_los\"] = float(patient_admits.loc[index,\"admit_icu_los\"])\n",
    "        else:\n",
    "            patient_admits.loc[index,\"admit_icu_los\"] = ((patient_admits.loc[index,\"admit_icu_los\"].components.days) +\n",
    "                                                         (patient_admits.loc[index,\"admit_icu_los\"].components.hours/24) +\n",
    "                                                         (patient_admits.loc[index,\"admit_icu_los\"].components.minutes/(60*24)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Change series type to float\n",
    "patient_admits[\"admit_icu_los\"] = patient_admits[\"admit_icu_los\"].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1,190 hospital admissions were without an ICU stay\n",
    "\n",
    "While these admissions will not be observations for whom C Diff risk is predicted, these admissions may belong to patients who have other ICU stays and for whom this medical history may be important."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 3) Diarrhea at Admit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "patient_admits[\"diarr_at_admit\"] = ((patient_admits[\"diagnosis\"].str.contains(\"loose stool\")) |\n",
    "                                    (patient_admits[\"diagnosis\"].str.contains(\"diarrhea\"))).map(lambda x: 1 if x == True else 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 4) Newborn Admission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"admission_type\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"newborn_admit\"] = patient_admits[\"admission_type\"].map(lambda x: 1 if x == \"NEWBORN\" else 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 5) Age Below 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"age_below_1\"] = ((patient_admits[\"newborn_admit\"] == 0) &\n",
    "                                 (patient_admits[\"admit_age\"] < 1)).map(lambda x: 1 if x == True else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits[\"age_below_1\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_admits.to_csv(\"../Data/1 - Patient Admits.csv\")\n",
    "icu_stays.to_csv(\"../Data/1 - ICU Stays.csv\")"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}