MIMIC_preproc.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3aae981e",
   "metadata": {},
   "source": [
    "# MIMIC Pre-Processing Notebook\n",
    "\n",
    "This notebook runs through the pre-processing we perform to get a single table of patient data.\n",
    "\n",
    "MIMIC-III exists in a multi table format however our work is focused on generating synthetic versions of single table data. As a result we pre-process the data by collecting files from randomised patients and bring them into a single file format.\n",
    "\n",
    "This file contains numerous continuous, categorical and datetime columns. This range can then be used to fully test data synthesis methods and ensuring they are robust to a realistic healthcare EHR type dataset.\n",
    "\n",
    "This notebook can create various size tables. These different sizes are shown in cell headers. We can create a small, medium, large set each with 11k, 81k and 217k rows respectively. Run the cells to get the dataset size that you desire. The difference between them is mainly that we allow multiple entries for specific patients depending on how many times they are admitted to a respective hospital.\n",
    "\n",
    "NOTE: Currently our model does NOT support missing data and as a result date of death (from original MIMIC) is ignored - future work should look at handling this common trend in data.\n",
    "\n",
    "In order to run this file you need:\n",
    "\n",
    "- admissions.csv.gz\n",
    "- CHARTEVENTS.csv.gz\n",
    "- D_ITEMS.csv.gz\n",
    "- ICUSTAYS.csv.gz\n",
    "- OUTPUTEVENTS.csv.gz\n",
    "- PATIENTS.csv.gz\n",
    "\n",
    "These can all be downloaded if you have MIMIC-III access - be careful to ensure they are accessible to load depending on file structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd97d146",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from random import randrange, seed\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3052c339",
   "metadata": {},
   "source": [
    "## Data Imports\n",
    "\n",
    "Load in the compressed csv files that MIMIC-III provides. We choose a selection from these files however this is arbitrary. You can create whatever table you wish using our formula just note that it must follow the data guidance to work with the current version of SynthVAE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6419f34c",
   "metadata": {},
   "outputs": [],
   "source": [
    "mimic_path = \"../Private MIMIC Data/Original MIMIC Data/\" # Path containing MIMIC data\n",
    "output_path = \"../outputs/\" # Path to save the created datasets\n",
    "\n",
    "admissions = pd.read_csv(mimic_path + \"admissions.csv.gz\")\n",
    "chartevents = pd.read_csv(mimic_path + \"CHARTEVENTS.csv.gz\", nrows=10000000)\n",
    "icustays = pd.read_csv(mimic_path + \"ICUSTAYS.csv.gz\")\n",
    "items = pd.read_csv(mimic_path + \"D_ITEMS.csv.gz\")\n",
    "outputevents = pd.read_csv(mimic_path + \"OUTPUTEVENTS.csv.gz\")\n",
    "patients = pd.read_csv(mimic_path + \"PATIENTS.csv.gz\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec2bd273",
   "metadata": {},
   "source": [
    "## Augment Patient DOBs\n",
    "\n",
    "Here we augment patients DOBs in the MIMIC-III set. We are basically just bringing the date of births forward to closer mimic a period in time closer to when SynthVAE was created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ef0408f",
   "metadata": {},
   "outputs": [],
   "source": [
    "seed(2021)\n",
    "## Augment patient DOBs\n",
    "new_dobs = []\n",
    "dob_offset = []\n",
    "for index, row in tqdm(patients.iterrows(), total=patients.shape[0]):\n",
    "    years_diff_behind = len(\n",
    "        pd.date_range(\n",
    "            start=pd.to_datetime(row[\"DOB\"], format=\"%Y-%m-%d %H:%M:%S\"),\n",
    "            end=pd.to_datetime(\"2021-12-01 00:00:00\", format=\"%Y-%m-%d %H:%M:%S\"),\n",
    "            freq=\"Y\",\n",
    "        )\n",
    "    )\n",
    "    years_diff_ahead = len(\n",
    "        pd.date_range(\n",
    "            start=pd.to_datetime(\"2021-12-01 00:00:00\", format=\"%Y-%m-%d %H:%M:%S\"),\n",
    "            end=pd.to_datetime(row[\"DOB\"], format=\"%Y-%m-%d %H:%M:%S\"),\n",
    "            freq=\"Y\",\n",
    "        )\n",
    "    )\n",
    "\n",
    "    if (years_diff_behind != 0) and (\n",
    "        pd.to_datetime(row[\"DOB\"], format=\"%Y-%m-%d %H:%M:%S\")\n",
    "        < pd.to_datetime(\"1930-01-01 00:00:00\", format=\"%Y-%m-%d %H:%M:%S\")\n",
    "    ):\n",
    "        num_years = randrange(years_diff_behind - 80, years_diff_behind - 40)\n",
    "        new_dobs.append(\n",
    "            pd.to_datetime(row[\"DOB\"], format=\"%Y-%m-%d %H:%M:%S\")\n",
    "            + pd.DateOffset(years=num_years)\n",
    "        )\n",
    "        dob_offset.append(num_years)\n",
    "    elif (years_diff_ahead != 0) and (\n",
    "        pd.to_datetime(row[\"DOB\"], format=\"%Y-%m-%d %H:%M:%S\")\n",
    "        > pd.to_datetime(\"2021-12-01 00:00:00\", format=\"%Y-%m-%d %H:%M:%S\")\n",
    "    ):\n",
    "        num_years = randrange(years_diff_ahead + 30, years_diff_ahead + 50)\n",
    "        new_dobs.append(\n",
    "            pd.to_datetime(row[\"DOB\"], format=\"%Y-%m-%d %H:%M:%S\")\n",
    "            - pd.DateOffset(years=num_years)\n",
    "        )\n",
    "        dob_offset.append(-num_years)\n",
    "    else:\n",
    "        new_dobs.append(pd.to_datetime(row[\"DOB\"], format=\"%Y-%m-%d %H:%M:%S\"))\n",
    "        dob_offset.append(0)\n",
    "patients[\"DOB\"] = new_dobs\n",
    "patients[\"DOB_offset\"] = dob_offset\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "580d7e52",
   "metadata": {},
   "source": [
    "## Produce small input (11k rows)\n",
    "\n",
    "First we set the seed to get reproducible tables independent of user.\n",
    "\n",
    "Secondly we merge all the respective tables we have chosen to form our single table as well as the columns that we desire.\n",
    "\n",
    "Thirdly, we loop through the rows we are generating and add on the offset we added to the date of birth, in the cell above, to all the other relevant datetime columns.\n",
    "\n",
    "Fourthly, we then ensure that the admit time is before the chart time and ensure that the datetime columns make sense in their given context.\n",
    "\n",
    "Finally, we drop columns that are not required, reformat ages etc to make them appropriate all before saving the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a38da68",
   "metadata": {},
   "outputs": [],
   "source": [
    "seed(2021)\n",
    "# Generate small input data file\n",
    "mimic_table = admissions[['SUBJECT_ID','ETHNICITY','ADMITTIME','DISCHTIME','DISCHARGE_LOCATION']]\n",
    "mimic_table = mimic_table.merge(patients[['SUBJECT_ID','GENDER','DOB']])\n",
    "mimic_table = mimic_table.merge(icustays[['SUBJECT_ID','ICUSTAY_ID','FIRST_CAREUNIT']])\n",
    "one_per_pat = chartevents.drop_duplicates(subset=['SUBJECT_ID','ICUSTAY_ID'])\n",
    "mimic_table = mimic_table.merge(one_per_pat[['SUBJECT_ID','ICUSTAY_ID','CHARTTIME','ITEMID','VALUE','VALUEUOM']],on=['SUBJECT_ID','ICUSTAY_ID'])\n",
    "\n",
    "new_admits = []\n",
    "new_dischs = []\n",
    "new_chart = []\n",
    "\n",
    "for index, row in tqdm(mimic_table.iterrows(), total=mimic_table.shape[0]):\n",
    "    admit_min = len(pd.date_range(start=pd.to_datetime(row['DOB'],format='%Y-%m-%d %H:%M:%S'),end=pd.to_datetime('2021-12-01 00:00:00',format='%Y-%m-%d %H:%M:%S'),freq='D'))\n",
    "    stay_len = len(pd.date_range(start=pd.to_datetime(row['ADMITTIME'],format='%Y-%m-%d %H:%M:%S'),end=pd.to_datetime(row['DISCHTIME'],format='%Y-%m-%d %H:%M:%S'),freq='S'))\n",
    "\n",
    "    num_days_admit = randrange(np.round(admit_min*0.25,0),np.round(admit_min*0.9,0)+5)\n",
    "    num_days_disch = randrange(0,50)\n",
    "    num_secs_chart = randrange(np.round(stay_len*0.01,0)+1,np.round(stay_len*0.99,0)+10)\n",
    "    new_admit_date = pd.to_datetime(row['DOB'],format='%Y-%m-%d %H:%M:%S') + pd.DateOffset(days=num_days_admit)\n",
    "    new_admits.append(new_admit_date)\n",
    "    new_dischs.append(new_admit_date + pd.DateOffset(days=num_days_disch))\n",
    "    new_chart.append(new_admit_date + pd.DateOffset(seconds=num_secs_chart))\n",
    "\n",
    "mimic_table['ADMITTIME'] = new_admits\n",
    "mimic_table['DISCHTIME'] = new_dischs\n",
    "mimic_table['CHARTTIME'] = new_chart\n",
    "\n",
    "mimic_table = mimic_table[(pd.to_datetime(mimic_table.ADMITTIME) < pd.to_datetime(mimic_table.CHARTTIME)) & (pd.to_datetime(mimic_table.DISCHTIME) > pd.to_datetime(mimic_table.CHARTTIME))]\n",
    "mimic_table = mimic_table.merge(items[['ITEMID','LABEL']],on=['ITEMID'])\n",
    "mimic_table.drop(['ICUSTAY_ID','ITEMID'],axis=1,inplace=True)\n",
    "age_calc = pd.Timestamp('2021-12-01 00:00:00')\n",
    "mimic_table['DOB'] = pd.to_datetime(mimic_table['DOB'], format='%Y-%m-%d %H:%M:%S')\n",
    "mimic_table['age'] = (age_calc - mimic_table['DOB']).astype('<m8[Y]')\n",
    "mimic_table = mimic_table.groupby('SUBJECT_ID').head(4)\n",
    "mimic_table.to_csv(output_path + \"table_one_{}.csv\".format(mimic_table.shape[0]), index=False)\n",
    "print(f\"Small input table saved, number of columns:  {mimic_table.shape[1]}, number of rows: {mimic_table.shape[0]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b3e7400",
   "metadata": {},
   "outputs": [],
   "source": [
    "mimic_table.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40b6b25f",
   "metadata": {},
   "source": [
    "## Produce medium input (81k rows)\n",
    "\n",
    "The process to produce the table is exactly the same as the small table the only difference now is we add in an additional step inbetween the second and third steps.\n",
    "\n",
    "This additional step chooses a data split where we are including multiple rows for that patient:\n",
    "\n",
    "- Patients included in <b>split_one</b> are forced to only have one row in the dataset.\n",
    "\n",
    "- Patients between <b>split_one</b> and <b>split_two</b> are forced to have only two rows in the dataset.\n",
    "\n",
    "- Patients after <b>split_two</b> i.e. the remainder, are forced to have up to 100 rows in the dataset.\n",
    "\n",
    "Note: Just because we specify <b>head(2)</b> or <b>head(100)</b> does NOT mean that every patient looped over will be given 2 or 100 rows in the single table. They will be given the amount of rows that they can feasibly have. I.e. if a patient in the middle set only has one row then <b>head(2)</b> is just going to put one row in the single table. Likewise <b>head(100)</b> for a patient with 5 rows, will only enter 5 rows into the single table. These values the rows a patient COULD have in the set provided they have that many rows available to us in MIMIC-III."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65ab26b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "seed(2021)\n",
    "# Generate mid-sized input file\n",
    "mimic_table = admissions[['SUBJECT_ID','ETHNICITY','ADMITTIME','DISCHTIME','DISCHARGE_LOCATION']]\n",
    "mimic_table = mimic_table.merge(patients[['SUBJECT_ID','GENDER','DOB']])\n",
    "mimic_table = mimic_table.merge(icustays[['SUBJECT_ID','ICUSTAY_ID','FIRST_CAREUNIT']])\n",
    "\n",
    "total_subjects = list(chartevents.SUBJECT_ID.unique())\n",
    "\n",
    "df_list = []\n",
    "\n",
    "split_one = int(np.round(len(total_subjects)*0.3,0))\n",
    "split_two = int(np.round(len(total_subjects)*0.9,0))\n",
    "\n",
    "for sub_index in range(0,split_one):\n",
    "    search_id = total_subjects[sub_index]\n",
    "    df_list.append(chartevents[chartevents.SUBJECT_ID == int(search_id)].head(1))\n",
    "\n",
    "for sub_index in range(split_one,split_two):\n",
    "    search_id = total_subjects[sub_index]\n",
    "    df_list.append(chartevents[chartevents.SUBJECT_ID == int(search_id)].head(2))\n",
    "\n",
    "for sub_index in range(split_two,len(total_subjects)):\n",
    "    search_id = total_subjects[sub_index]\n",
    "    df_list.append(chartevents[chartevents.SUBJECT_ID == int(search_id)].head(100))\n",
    "\n",
    "one_per_pat = pd.concat(df_list)\n",
    "one_per_pat.shape\n",
    "\n",
    "mimic_table = mimic_table.merge(one_per_pat[['SUBJECT_ID','ICUSTAY_ID','CHARTTIME','ITEMID','VALUE','VALUEUOM']],on=['SUBJECT_ID','ICUSTAY_ID'])\n",
    "\n",
    "new_admits = []\n",
    "new_dischs = []\n",
    "new_chart = []\n",
    "\n",
    "patient_stays = mimic_table[['SUBJECT_ID','ICUSTAY_ID','DOB','ADMITTIME']]\n",
    "patient_stays.drop_duplicates(inplace=True)\n",
    "\n",
    "for index, row in tqdm(mimic_table.iterrows(), total=mimic_table.shape[0]):\n",
    "    admit_min = len(pd.date_range(start=pd.to_datetime(row['DOB'],format='%Y-%m-%d %H:%M:%S'),end=pd.to_datetime('2021-12-01 00:00:00',format='%Y-%m-%d %H:%M:%S'),freq='D'))\n",
    "    stay_len = len(pd.date_range(start=pd.to_datetime(row['ADMITTIME'],format='%Y-%m-%d %H:%M:%S'),end=pd.to_datetime(row['DISCHTIME'],format='%Y-%m-%d %H:%M:%S'),freq='S'))\n",
    "\n",
    "    num_days_admit = randrange(np.round(admit_min*0.25,0),np.round(admit_min*0.9,0)+5)\n",
    "    num_days_disch = randrange(0,50)\n",
    "    num_secs_chart = randrange(np.round(stay_len*0.01,0)+1,np.round(stay_len*0.99,0)+10)\n",
    "    new_admit_date = pd.to_datetime(row['DOB'],format='%Y-%m-%d %H:%M:%S') + pd.DateOffset(days=num_days_admit)\n",
    "    new_admits.append(new_admit_date)\n",
    "    new_dischs.append(new_admit_date + pd.DateOffset(days=num_days_disch))\n",
    "    new_chart.append(new_admit_date + pd.DateOffset(seconds=num_secs_chart))\n",
    "\n",
    "mimic_table['ADMITTIME'] = new_admits\n",
    "mimic_table['DISCHTIME'] = new_dischs\n",
    "mimic_table['CHARTTIME'] = new_chart\n",
    "\n",
    "mimic_table = mimic_table[(pd.to_datetime(mimic_table.ADMITTIME) < pd.to_datetime(mimic_table.CHARTTIME)) & (pd.to_datetime(mimic_table.DISCHTIME) > pd.to_datetime(mimic_table.CHARTTIME))]\n",
    "mimic_table = mimic_table.merge(items[['ITEMID','LABEL']],on=['ITEMID'])\n",
    "mimic_table.drop(['ICUSTAY_ID','ITEMID'],axis=1,inplace=True)\n",
    "age_calc = pd.Timestamp('2021-12-01 00:00:00')\n",
    "mimic_table['DOB'] = pd.to_datetime(mimic_table['DOB'], format='%Y-%m-%d %H:%M:%S')\n",
    "mimic_table['age'] = (age_calc - mimic_table['DOB']).astype('<m8[Y]')\n",
    "mimic_table.to_csv(output_path + \"table_one_{}.csv\".format(mimic_table.shape[0]), index=False)\n",
    "print(f\"Mid sized input table saved, number of columns:  {mimic_table.shape[1]}, number of rows: {mimic_table.shape[0]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14c9596e",
   "metadata": {},
   "source": [
    "## Produce large input (217k rows)\n",
    "\n",
    "Same rules apply in here as the medium set, only difference here is that <b>split_one</b> and <b>split_two</b> are different sizes allowing for more patients with up to 2 and up to 100 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3feff799",
   "metadata": {},
   "outputs": [],
   "source": [
    "seed(2021)\n",
    "# Generate large-sized input file\n",
    "mimic_table = admissions[['SUBJECT_ID','ETHNICITY','ADMITTIME','DISCHTIME','DISCHARGE_LOCATION']]\n",
    "mimic_table = mimic_table.merge(patients[['SUBJECT_ID','GENDER','DOB']])\n",
    "mimic_table = mimic_table.merge(icustays[['SUBJECT_ID','ICUSTAY_ID','FIRST_CAREUNIT']])\n",
    "\n",
    "total_subjects = list(chartevents.SUBJECT_ID.unique())\n",
    "\n",
    "df_list = []\n",
    "\n",
    "split_one = int(np.round(len(total_subjects)*0.3,0))\n",
    "split_two = int(np.round(len(total_subjects)*0.7,0))\n",
    "\n",
    "for sub_index in range(0,split_one):\n",
    "    search_id = total_subjects[sub_index]\n",
    "    df_list.append(chartevents[chartevents.SUBJECT_ID == int(search_id)].head(1))\n",
    "\n",
    "for sub_index in range(split_one,split_two):\n",
    "    search_id = total_subjects[sub_index]\n",
    "    df_list.append(chartevents[chartevents.SUBJECT_ID == int(search_id)].head(2))\n",
    "\n",
    "for sub_index in range(split_two,len(total_subjects)):\n",
    "    search_id = total_subjects[sub_index]\n",
    "    df_list.append(chartevents[chartevents.SUBJECT_ID == int(search_id)].head(100))\n",
    "\n",
    "one_per_pat = pd.concat(df_list)\n",
    "one_per_pat.shape\n",
    "\n",
    "mimic_table = mimic_table.merge(one_per_pat[['SUBJECT_ID','ICUSTAY_ID','CHARTTIME','ITEMID','VALUE','VALUEUOM']],on=['SUBJECT_ID','ICUSTAY_ID'])\n",
    "\n",
    "new_admits = []\n",
    "new_dischs = []\n",
    "new_chart = []\n",
    "\n",
    "patient_stays = mimic_table[['SUBJECT_ID','ICUSTAY_ID','DOB','ADMITTIME']]\n",
    "patient_stays.drop_duplicates(inplace=True)\n",
    "\n",
    "for index, row in tqdm(mimic_table.iterrows(), total=mimic_table.shape[0]):\n",
    "    admit_min = len(pd.date_range(start=pd.to_datetime(row['DOB'],format='%Y-%m-%d %H:%M:%S'),end=pd.to_datetime('2021-12-01 00:00:00',format='%Y-%m-%d %H:%M:%S'),freq='D'))\n",
    "    stay_len = len(pd.date_range(start=pd.to_datetime(row['ADMITTIME'],format='%Y-%m-%d %H:%M:%S'),end=pd.to_datetime(row['DISCHTIME'],format='%Y-%m-%d %H:%M:%S'),freq='S'))\n",
    "\n",
    "    num_days_admit = randrange(np.round(admit_min*0.25,0),np.round(admit_min*0.9,0)+5)\n",
    "    num_days_disch = randrange(0,50)\n",
    "    num_secs_chart = randrange(np.round(stay_len*0.01,0)+1,np.round(stay_len*0.99,0)+10)\n",
    "    new_admit_date = pd.to_datetime(row['DOB'],format='%Y-%m-%d %H:%M:%S') + pd.DateOffset(days=num_days_admit)\n",
    "    new_admits.append(new_admit_date)\n",
    "    new_dischs.append(new_admit_date + pd.DateOffset(days=num_days_disch))\n",
    "    new_chart.append(new_admit_date + pd.DateOffset(seconds=num_secs_chart))\n",
    "\n",
    "mimic_table['ADMITTIME'] = new_admits\n",
    "mimic_table['DISCHTIME'] = new_dischs\n",
    "mimic_table['CHARTTIME'] = new_chart\n",
    "\n",
    "mimic_table = mimic_table[(pd.to_datetime(mimic_table.ADMITTIME) < pd.to_datetime(mimic_table.CHARTTIME)) & (pd.to_datetime(mimic_table.DISCHTIME) > pd.to_datetime(mimic_table.CHARTTIME))]\n",
    "mimic_table = mimic_table.merge(items[['ITEMID','LABEL']],on=['ITEMID'])\n",
    "mimic_table.drop(['ICUSTAY_ID','ITEMID'],axis=1,inplace=True)\n",
    "age_calc = pd.Timestamp('2021-12-01 00:00:00')\n",
    "mimic_table['DOB'] = pd.to_datetime(mimic_table['DOB'], format='%Y-%m-%d %H:%M:%S')\n",
    "mimic_table['age'] = (age_calc - mimic_table['DOB']).astype('<m8[Y]')\n",
    "mimic_table.to_csv(output_path + \"table_one_{}.csv\".format(mimic_table.shape[0]), index=False)\n",
    "print(f\"Large input table saved, number of columns:  {mimic_table.shape[1]}, number of rows: {mimic_table.shape[0]}\")"
   ]
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}