prepare_firsthours.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from collections import Counter\n",
    "from datetime import datetime\n",
    "import os\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "hours = 24"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compute_age(row):\n",
    "    t1 = datetime.strptime(row['ADMITTIME'], '%Y-%m-%d %H:%M:%S')\n",
    "    t2 = datetime.strptime(row['DOB'], '%Y-%m-%d %H:%M:%S')\n",
    "    t = (t1 - t2).days//30\n",
    "    return t\n",
    "\n",
    "def compute_gender(row):\n",
    "    if row['GENDER'] == 'F':\n",
    "        return 0\n",
    "    elif row['GENDER'] == 'M':\n",
    "        return 1\n",
    "    else:\n",
    "        return -1\n",
    "\n",
    "def is_first_hours(row):\n",
    "    t1 = datetime.strptime(row['ADMITTIME'], '%Y-%m-%d %H:%M:%S')\n",
    "    t2 = datetime.strptime(row['CHARTTIME'], '%Y-%m-%d %H:%M:%S')\n",
    "    t = (t2 - t1).total_seconds()\n",
    "    if t < hours*3600:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "def is_early_death(row):\n",
    "    if pd.isna(row['DEATHTIME']):\n",
    "        return 0\n",
    "    else:\n",
    "        t1 = datetime.strptime(row['ADMITTIME'], '%Y-%m-%d %H:%M:%S')\n",
    "        t2 = datetime.strptime(row['DEATHTIME'], '%Y-%m-%d %H:%M:%S')\n",
    "        t = (t2 - t1).total_seconds()\n",
    "        if t < hours*3600:\n",
    "            return 1\n",
    "        else:\n",
    "            return 0\n",
    "        \n",
    "def minmax(arr):\n",
    "    return max(arr) - min(arr)\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 Read raw csv files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = 'raw_data'\n",
    "\n",
    "# read basic info\n",
    "df_admissions = pd.read_csv(os.path.join(data_path, 'ADMISSIONS.csv')) # diagnosis before hospital, gender, age\n",
    "df_icustays = pd.read_csv(os.path.join(data_path, 'ICUSTAYS.csv'))\n",
    "df_patients = pd.read_csv(os.path.join(data_path, 'PATIENTS.csv'))\n",
    "df_diagnosis_icd = pd.read_csv(os.path.join(data_path, 'DIAGNOSES_ICD.csv')) # diagnosis after hospital\n",
    "\n",
    "# read dicts\n",
    "df_d_icd_diagnosis = pd.read_csv(os.path.join(data_path, 'D_ICD_DIAGNOSES.csv'))\n",
    "df_d_items = pd.read_csv(os.path.join(data_path, 'D_ITEMS.csv'))\n",
    "df_d_labitems = pd.read_csv(os.path.join(data_path, 'D_LABITEMS.csv'))\n",
    "\n",
    "# read bed time series\n",
    "df_chartevents = pd.read_csv(os.path.join(data_path, 'CHARTEVENTS.csv')) # yes, 19 distinct items\n",
    "df_labevents = pd.read_csv(os.path.join(data_path, 'LABEVENTS.csv')) # yes, 821 distinct items\n",
    "df_outputevents = pd.read_csv(os.path.join(data_path, 'OUTPUTEVENTS.csv')) # no\n",
    "df_emr_symptoms = pd.read_csv(os.path.join(data_path, 'EMR_SYMPTOMS.csv')) # no\n",
    "df_microbiologyevents = pd.read_csv(os.path.join(data_path, 'MICROBIOLOGYEVENTS.csv')) # no\n",
    "df_or_exam_reports = pd.read_csv(os.path.join(data_path, 'OR_EXAM_REPORTS.csv')) # no\n",
    "df_prescriptions = pd.read_csv(os.path.join(data_path, 'PRESCRIPTIONS.csv')) # no\n",
    "\n",
    "# read operation time series\n",
    "df_surgery_vital_signs = pd.read_csv(os.path.join(data_path, 'SURGERY_VITAL_SIGNS.csv')) # no\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.1 Preprocess demographic table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "input file shape: (13449, 24)\n",
      "Counter of is_early_death: Counter({0: 13258, 1: 191})\n"
     ]
    }
   ],
   "source": [
    "# preprocess demographic table\n",
    "df_demo = df_admissions.merge(df_patients, left_on='SUBJECT_ID', right_on='SUBJECT_ID', how='left')\n",
    "print('input file shape:', df_demo.shape)\n",
    "df_demo['age_month'] = df_demo.apply(lambda row: compute_age(row), axis=1) # age\n",
    "df_demo['gender_is_male'] = df_demo.apply(lambda row: compute_gender(row), axis=1) # gender\n",
    "df_weight = df_chartevents[df_chartevents['ITEMID']==1014] # weight\n",
    "df_weight = df_weight.groupby(['HADM_ID']).first().reset_index()\n",
    "df_demo = df_demo.merge(df_weight, left_on='HADM_ID', right_on='HADM_ID', how='left')\n",
    "df_demo['weight_kg'] = df_demo['VALUENUM']\n",
    "df_demo['is_early_death'] = df_demo.apply(lambda row: is_early_death(row), axis=1)\n",
    "df_demo['SUBJECT_ID'] = df_demo['SUBJECT_ID_x'] # rename\n",
    "cols = ['SUBJECT_ID', 'HADM_ID', 'ADMITTIME', 'age_month', 'gender_is_male', 'weight_kg', 'HOSPITAL_EXPIRE_FLAG', 'is_early_death']\n",
    "df_demo = df_demo[cols]\n",
    "print('Counter of is_early_death:', Counter(df_demo.is_early_death.values))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.2 Preprocess chartevents table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_chartevents_small = df_chartevents.iloc[:1000] # small test\n",
    "df_chartevents_firsthours = df_chartevents.merge(df_demo, left_on='HADM_ID', right_on='HADM_ID', how='left') # merge and compute if it is first hours\n",
    "df_chartevents_firsthours['is_first_hours'] = df_chartevents_firsthours.apply(lambda row: is_first_hours(row), axis=1)\n",
    "df_chartevents_firsthours = df_chartevents_firsthours[df_chartevents_firsthours.is_first_hours==1]\n",
    "df_chartevents_firsthours['SUBJECT_ID'] = df_chartevents_firsthours['SUBJECT_ID_x']\n",
    "cols = ['HADM_ID', 'ITEMID', 'VALUENUM']\n",
    "df_chartevents_firsthours = df_chartevents_firsthours[cols].reset_index(drop=True)\n",
    "\n",
    "df_chartevents_firsthours_max = df_chartevents_firsthours.groupby(['HADM_ID', 'ITEMID']).max().reset_index()\n",
    "df_chartevents_firsthours_max = df_chartevents_firsthours_max.pivot_table(index='HADM_ID', columns='ITEMID', values='VALUENUM')\n",
    "df_chartevents_firsthours_max = df_chartevents_firsthours_max.reset_index()\n",
    "df_chartevents_firsthours_max.columns = ['chart_{}_max'.format(i) for i in list(df_chartevents_firsthours_max.columns)]\n",
    "\n",
    "df_chartevents_firsthours_min = df_chartevents_firsthours.groupby(['HADM_ID', 'ITEMID']).min().reset_index()\n",
    "df_chartevents_firsthours_min = df_chartevents_firsthours_min.pivot_table(index='HADM_ID', columns='ITEMID', values='VALUENUM')\n",
    "df_chartevents_firsthours_min = df_chartevents_firsthours_min.reset_index()\n",
    "df_chartevents_firsthours_min.columns = ['chart_{}_min'.format(i) for i in list(df_chartevents_firsthours_min.columns)]\n",
    "\n",
    "df_chartevents_firsthours_range = df_chartevents_firsthours.groupby(['HADM_ID', 'ITEMID']).agg(minmax).reset_index()\n",
    "df_chartevents_firsthours_range = df_chartevents_firsthours_range.pivot_table(index='HADM_ID', columns='ITEMID', values='VALUENUM')\n",
    "df_chartevents_firsthours_range = df_chartevents_firsthours_range.reset_index()\n",
    "df_chartevents_firsthours_range.columns = ['chart_{}_range'.format(i) for i in list(df_chartevents_firsthours_range.columns)]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.3 Preprocess labevents table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_labevents_small = df_labevents.iloc[:1000] # small test\n",
    "df_labevents_firsthours = df_labevents.merge(df_demo, left_on='HADM_ID', right_on='HADM_ID', how='left')\n",
    "df_labevents_firsthours['is_first_hours'] = df_labevents_firsthours.apply(lambda row: is_first_hours(row), axis=1)\n",
    "df_labevents_firsthours = df_labevents_firsthours[df_labevents_firsthours.is_first_hours==1]\n",
    "df_labevents_firsthours['SUBJECT_ID'] = df_labevents_firsthours['SUBJECT_ID_x']\n",
    "cols = ['HADM_ID', 'ITEMID', 'VALUENUM']\n",
    "df_labevents_firsthours = df_labevents_firsthours[cols].reset_index(drop=True)\n",
    "\n",
    "df_labevents_firsthours_max = df_labevents_firsthours.groupby(['HADM_ID', 'ITEMID']).max().reset_index()\n",
    "df_labevents_firsthours_max = df_labevents_firsthours_max.pivot_table(index='HADM_ID', columns='ITEMID', values='VALUENUM')\n",
    "df_labevents_firsthours_max = df_labevents_firsthours_max.reset_index()\n",
    "df_labevents_firsthours_max.columns = ['lab_{}_max'.format(i) for i in list(df_labevents_firsthours_max.columns)]\n",
    "\n",
    "df_labevents_firsthours_min = df_labevents_firsthours.groupby(['HADM_ID', 'ITEMID']).min().reset_index()\n",
    "df_labevents_firsthours_min = df_labevents_firsthours_min.pivot_table(index='HADM_ID', columns='ITEMID', values='VALUENUM')\n",
    "df_labevents_firsthours_min = df_labevents_firsthours_min.reset_index()\n",
    "df_labevents_firsthours_min.columns = ['lab_{}_min'.format(i) for i in list(df_labevents_firsthours_min.columns)]\n",
    "\n",
    "df_labevents_firsthours_range = df_labevents_firsthours.groupby(['HADM_ID', 'ITEMID']).agg(minmax).reset_index()\n",
    "df_labevents_firsthours_range = df_labevents_firsthours_range.pivot_table(index='HADM_ID', columns='ITEMID', values='VALUENUM')\n",
    "df_labevents_firsthours_range = df_labevents_firsthours_range.reset_index()\n",
    "df_labevents_firsthours_range.columns = ['lab_{}_range'.format(i) for i in list(df_labevents_firsthours_range.columns)]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3 Combine all tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_demo = df_demo[df_demo.is_early_death!=1] # early death patients are excluded\n",
    "df_all = df_demo.merge(df_chartevents_firsthours_max, left_on='HADM_ID', right_on='chart_HADM_ID_max', how='left')\n",
    "df_all = df_all.merge(df_chartevents_firsthours_min, left_on='HADM_ID', right_on='chart_HADM_ID_min', how='left')\n",
    "df_all = df_all.merge(df_chartevents_firsthours_range, left_on='HADM_ID', right_on='chart_HADM_ID_range', how='left')\n",
    "df_all = df_all.merge(df_labevents_firsthours_max, left_on='HADM_ID', right_on='lab_HADM_ID_max', how='left')\n",
    "df_all = df_all.merge(df_labevents_firsthours_min, left_on='HADM_ID', right_on='lab_HADM_ID_min', how='left')\n",
    "df_all = df_all.merge(df_labevents_firsthours_range, left_on='HADM_ID', right_on='lab_HADM_ID_range', how='left')\n",
    "cols = list(df_all.columns)\n",
    "remove_cols = ['chart_HADM_ID_max', 'chart_HADM_ID_min', 'chart_HADM_ID_range', 'lab_HADM_ID_max', 'lab_HADM_ID_min', 'lab_HADM_ID_range']\n",
    "cols = [x for x in cols if x not in remove_cols]\n",
    "df_all = df_all[cols]\n",
    "df_all.to_csv('icu_first{}hours.csv'.format(hours), index=False)\n",
    "print(df_all.shape)\n"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}