001-kba-mimicIII_ETL_FHIR.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MIMIC-III tables to FHIR resource mapping in this notebook\n",
    "\n",
    "||Original format | FHIR resource| Progress|Final Check|\n",
    "|------|:-----|:-----|:-----|---:---|\n",
    "|1|patients | patient| C,L,A|Done|\n",
    "|2a|admissions | encounter| C,L,A|Done|\n",
    "|2b|diagnoses_icd | encounter| C,L,A|Done|\n",
    "|3|icustay | enounter|C,L,A|Done|\n",
    "|4|cptevents | claim| C,L,A|Done|\n",
    "|5|noteevents | diagnosticReport| C,L,A|Done|\n",
    "<!-- |6|inputevents_cv | medicationDispense| C,L,A|Done|\n",
    "|7|inputevents_mv | medicationDispense| C,L,A|Done|\n",
    "|8|prescriptions | medicationRequest| C,L,A|Done|\n",
    "|9|chartevents | observation| C,L,A|Done|\n",
    "|10|datetimeevents | observation| C,L,A|Done|\n",
    "|11|labevents | observation| C,L,A|Done|\n",
    "|12|caregivers | practitioner| C,L,A|Done|\n",
    "|13|procedures_icd | procedure| C,L,A|Done|\n",
    "|14|procedureevents_mv | procedure| C,L,A|Done|\n",
    "|15|microbiology | specimen| C,L,A|Done|\n",
    "|16|outputevents | specimen| C,L,A|Done|\n",
    "|17|service | serviceRequest|C,L,A|Done|\n",
    "|18|callout | -|-|-|\n",
    "|19|transfers | -|-|-|\n",
    "|20|drgcodes | -|-|-| -->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import packages and data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = './data/mimic-iii-clinical-database-1.4/'\n",
    "output_path = './data/fhir_out/'\n",
    "file_ext = '.csv.gz'\n",
    "compression = 'gzip'\n",
    "\n",
    "data_files = os.listdir(data_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://github.com/pandas-dev/pandas/issues/33196\n",
    "# https://github.com/pandas-dev/pandas/pull/33398\n",
    "# pd.options.io.compressionlevel = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create each FHIR resource type table individually"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fhir.patients table\n",
    "\n",
    "#### MAPPING:<br>\n",
    "\n",
    "- As soon as this table is joineed with another table the identifier column needs to be renamed to 'subject'\n",
    "- We could add the deathtime from the admissions table, which could be more accurate.\n",
    "- Check https://www.hl7.org/fhir/valueset-languages.html for language codes(probably easiest to just create a bigger version defining the language the language name is in and the language name itself)\n",
    "- Using MARITIAL_STATUS from last encounter\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.patients.SUBJECT_ID | fhir.patient.identifier|\n",
    "|2|mimic.patients.GENDER | fhir.patient.gender|\n",
    "|3|mimic.patients.DOB | fhir.patient.birthDate|\n",
    "|4|mimic.patients.DOD | fhir.patient.deceasedDateTime|\n",
    "|5|mimic.admissions.LANGUAGE | fhir.patient.communication_language|\n",
    "|6|mimic.admissions.MARITIAL_STATUS | fhir.patient.maritialStatus|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>identifier</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthDate</th>\n",
       "      <th>deceasedDateTime</th>\n",
       "      <th>communication_language</th>\n",
       "      <th>maritalStatus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>249</td>\n",
       "      <td>F</td>\n",
       "      <td>2075-03-13</td>\n",
       "      <td>NaT</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>DIVORCED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>250</td>\n",
       "      <td>F</td>\n",
       "      <td>2164-12-27</td>\n",
       "      <td>2188-11-22</td>\n",
       "      <td>HAIT</td>\n",
       "      <td>SINGLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>251</td>\n",
       "      <td>M</td>\n",
       "      <td>2090-03-15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>252</td>\n",
       "      <td>M</td>\n",
       "      <td>2078-03-06</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SINGLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>253</td>\n",
       "      <td>F</td>\n",
       "      <td>2089-11-26</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WIDOWED</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier gender  birthDate deceasedDateTime communication_language  \\\n",
       "0         249      F 2075-03-13              NaT                   ENGL   \n",
       "1         250      F 2164-12-27       2188-11-22                   HAIT   \n",
       "2         251      M 2090-03-15              NaT                    NaN   \n",
       "3         252      M 2078-03-06              NaT                    NaN   \n",
       "4         253      F 2089-11-26              NaT                    NaN   \n",
       "\n",
       "  maritalStatus  \n",
       "0      DIVORCED  \n",
       "1        SINGLE  \n",
       "2           NaN  \n",
       "3        SINGLE  \n",
       "4       WIDOWED  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_patients(data_path, output_path):\n",
    "    patients = pd.read_csv(data_path+'PATIENTS'+file_ext, compression=compression,\n",
    "                           usecols=['SUBJECT_ID', 'GENDER', 'DOB', 'DOD'],\n",
    "                           dtype={'SUBJECT_ID': int, 'GENDER': 'category'}, \n",
    "                           parse_dates=['DOB', 'DOD'])\n",
    "    \n",
    "    admissions = pd.read_csv(data_path+'ADMISSIONS'+file_ext, compression=compression,\n",
    "                             usecols=['SUBJECT_ID', 'LANGUAGE', 'MARITAL_STATUS'],\n",
    "                             dtype={'SUBJECT_ID': int, 'LANGUAGE': 'category', 'MARITAL_STATUS': 'category'})\n",
    "    \n",
    "    # Add the marital status and language information from the admissions dataframe by using the information from the last admission.\n",
    "    ad_temp = admissions.groupby('SUBJECT_ID').tail(1)\n",
    "    patients = pd.merge(patients, ad_temp, on='SUBJECT_ID')\n",
    "\n",
    "    # Rename columns to FHIR names\n",
    "    patients.rename(columns={'SUBJECT_ID':'identifier',\n",
    "                             'GENDER':'gender', \n",
    "                             'DOB':'birthDate', \n",
    "                             'DOD':'deceasedDateTime',\n",
    "                             'LANGUAGE':'communication_language',\n",
    "                             'MARITAL_STATUS':'maritalStatus'}, inplace=True)\n",
    "\n",
    "    # Output to csv\n",
    "    patients.to_csv(output_path+'patient.csv.gz', compression='gzip', index=False)\n",
    "    return patients\n",
    "\n",
    "patient_fhir = transform_patients(data_path, output_path)\n",
    "patient_fhir.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del patient_fhir\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fhir.encounter table\n",
    "\n",
    "#### ADMISSIONS MAPPING:<br>\n",
    "\n",
    "- location column (add all interim locations and periods)\n",
    "- - Could add location field by using Transfer and SERVICES mimictables\n",
    "- - Combine both mimic.admissions.EDREGTIME and mimic.admissions.EDOUTTIME to add time period for fhir.encounter.locations list\n",
    "- mimic.admissions.INSURANCE - Might be better placed in claims fhir table\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.admissions.SUBJECT_ID|fhir.encounter.subject|\n",
    "|2|mimic.admissions.HADM_ID|fhir.encounter.identifier|\n",
    "|3|'admission'|fhir.encounter.serviceType|\n",
    "|4|mimic.admissions.ADMITTIME|fhir.encounter.period_start|\n",
    "|5|mimic.admissions.DISCHTIME|fhir.encounter.period_end|\n",
    "|6|mimic.admissions.(DISCHTIME - ADMITTIME)|fhir.encounter.length|\n",
    "|7|mimic.admissions.ADMISSION_TYPE|fhir.encounter.type|\n",
    "|8|mimic.admissions.ADMISSION_LOCATION|fhir.encounter.hospitalization_origin|\n",
    "|9|mimic.admissions.DISCHARGE_LOCATION|fhir.encounter.hospitalization_destination|\n",
    "|10|mimic.admissions.INSURANCE|fhir.encounter.serviceProvider|\n",
    "|11|mimic.DIAGNOSES_ICD.ICD9_CODE|fhir.encounter.diagnosis_condition|\n",
    "|12|mimic.DIAGNOSES_ICD.SEQ_NUM|fhir.encounter.diagnosis_condition|\n",
    "|13|mimic.admissions.DIAGNOSIS|fhir.encounter.diagnosis_description|\n",
    "\n",
    "#### ICUSTAYS MAPPING:<br>\n",
    "\n",
    "- Could improve location field by using Transfer table\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.icustays.SUBJECT_ID|fhir.encounter.subject|\n",
    "|2|mimic.icustays.HADM_ID|fhir.encounter.partOf|\n",
    "|3|mimic.icustays.ICUSTAY_ID|fhir.encounter.identifier|\n",
    "|4|'icustay'|fhir.encounter.serviceType|\n",
    "|5|mimic.icustays.INTIME|fhir.encounter.period_start|\n",
    "|6|mimic.icustays.OUTTIME|fhir.encounter.period_end|\n",
    "|7|mimic.icustays.(OUTTIME - INTIME)|fhir.encounter.length|\n",
    "|8|mimic.icustays.DBSOURCE|fhir.encounter.type|\n",
    "|9|mimic.icustays.FIRST_CAREUNIT|fhir.encounter.location|\n",
    "|10|mimic.icustays.LAST_CAREUNIT|fhir.encounter.location|\n",
    "|11|mimic.icustays.FIRST_WARDID|fhir.encounter.location_id|\n",
    "|12|mimic.icustays.LAST_WARDID|fhir.encounter.location_id|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject</th>\n",
       "      <th>identifier</th>\n",
       "      <th>period_start</th>\n",
       "      <th>period_end</th>\n",
       "      <th>length</th>\n",
       "      <th>type</th>\n",
       "      <th>hospitalization_origin</th>\n",
       "      <th>hospitalization_destination</th>\n",
       "      <th>serviceProvider</th>\n",
       "      <th>diagnosis_condition</th>\n",
       "      <th>diagnosis_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22</td>\n",
       "      <td>165315</td>\n",
       "      <td>2196-04-09 12:26:00</td>\n",
       "      <td>2196-04-10 15:54:00</td>\n",
       "      <td>1 days 03:28:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>DISC-TRAN CANCER/CHLDRN H</td>\n",
       "      <td>Private</td>\n",
       "      <td>[9678, 9693, E9502, E9503, 3488, 29620, 4019]</td>\n",
       "      <td>BENZODIAZEPINE OVERDOSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23</td>\n",
       "      <td>152223</td>\n",
       "      <td>2153-09-03 07:15:00</td>\n",
       "      <td>2153-09-08 19:10:00</td>\n",
       "      <td>5 days 11:55:00</td>\n",
       "      <td>ELECTIVE</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>[41401, 4111, 4241, V4582, 2724, 4019, 60000, ...</td>\n",
       "      <td>CORONARY ARTERY DISEASE\\CORONARY ARTERY BYPASS...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23</td>\n",
       "      <td>124321</td>\n",
       "      <td>2157-10-18 19:34:00</td>\n",
       "      <td>2157-10-25 14:00:00</td>\n",
       "      <td>6 days 18:26:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>[2252, 3485, 78039, 4241, 4019, 2720, 2724, V4...</td>\n",
       "      <td>BRAIN MASS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24</td>\n",
       "      <td>161859</td>\n",
       "      <td>2139-06-06 16:14:00</td>\n",
       "      <td>2139-06-09 12:48:00</td>\n",
       "      <td>2 days 20:34:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Private</td>\n",
       "      <td>[41041, 41401, 53081, 25000]</td>\n",
       "      <td>INTERIOR MYOCARDIAL INFARCTION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25</td>\n",
       "      <td>129635</td>\n",
       "      <td>2160-11-02 02:06:00</td>\n",
       "      <td>2160-11-05 14:55:00</td>\n",
       "      <td>3 days 12:49:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Private</td>\n",
       "      <td>[41071, 25011, 41401, 4019]</td>\n",
       "      <td>ACUTE CORONARY SYNDROME</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject  identifier        period_start          period_end  \\\n",
       "0       22      165315 2196-04-09 12:26:00 2196-04-10 15:54:00   \n",
       "1       23      152223 2153-09-03 07:15:00 2153-09-08 19:10:00   \n",
       "2       23      124321 2157-10-18 19:34:00 2157-10-25 14:00:00   \n",
       "3       24      161859 2139-06-06 16:14:00 2139-06-09 12:48:00   \n",
       "4       25      129635 2160-11-02 02:06:00 2160-11-05 14:55:00   \n",
       "\n",
       "           length       type     hospitalization_origin  \\\n",
       "0 1 days 03:28:00  EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "1 5 days 11:55:00   ELECTIVE  PHYS REFERRAL/NORMAL DELI   \n",
       "2 6 days 18:26:00  EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "3 2 days 20:34:00  EMERGENCY  TRANSFER FROM HOSP/EXTRAM   \n",
       "4 3 days 12:49:00  EMERGENCY       EMERGENCY ROOM ADMIT   \n",
       "\n",
       "  hospitalization_destination serviceProvider  \\\n",
       "0   DISC-TRAN CANCER/CHLDRN H         Private   \n",
       "1            HOME HEALTH CARE        Medicare   \n",
       "2            HOME HEALTH CARE        Medicare   \n",
       "3                        HOME         Private   \n",
       "4                        HOME         Private   \n",
       "\n",
       "                                 diagnosis_condition  \\\n",
       "0      [9678, 9693, E9502, E9503, 3488, 29620, 4019]   \n",
       "1  [41401, 4111, 4241, V4582, 2724, 4019, 60000, ...   \n",
       "2  [2252, 3485, 78039, 4241, 4019, 2720, 2724, V4...   \n",
       "3                       [41041, 41401, 53081, 25000]   \n",
       "4                        [41071, 25011, 41401, 4019]   \n",
       "\n",
       "                               diagnosis_description  \n",
       "0                            BENZODIAZEPINE OVERDOSE  \n",
       "1  CORONARY ARTERY DISEASE\\CORONARY ARTERY BYPASS...  \n",
       "2                                         BRAIN MASS  \n",
       "3                     INTERIOR MYOCARDIAL INFARCTION  \n",
       "4                            ACUTE CORONARY SYNDROME  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_admissions(data_path, output_path):\n",
    "    admissions = pd.read_csv(data_path+'ADMISSIONS'+file_ext, compression=compression,\n",
    "                             usecols=['SUBJECT_ID', 'HADM_ID', 'ADMITTIME', 'DISCHTIME',\n",
    "                                      'ADMISSION_TYPE', 'ADMISSION_LOCATION', 'DISCHARGE_LOCATION',\n",
    "                                      'INSURANCE', 'DIAGNOSIS'],\n",
    "                             dtype={'SUBJECT_ID': int, 'HADM_ID': int, 'ADMISSION_TYPE': 'category',\n",
    "                                    'ADMISSION_LOCATION': 'category', 'DISCHARGE_LOCATION': 'category', \n",
    "                                    'INSURANCE': 'category', 'DIAGNOSIS': str},\n",
    "                             parse_dates=['ADMITTIME', 'DISCHTIME'])\n",
    "    \n",
    "    diagnoses_icd = pd.read_csv(data_path+'DIAGNOSES_ICD'+file_ext, compression=compression,\n",
    "                                usecols=['SUBJECT_ID', 'HADM_ID', 'SEQ_NUM', 'ICD9_CODE'],\n",
    "                                dtype={'SUBJECT_ID': int, 'HADM_ID': int, 'SEQ_NUM': float, 'ICD9_CODE': str})\n",
    "    \n",
    "    # Convert times to datetime formats and add length column\n",
    "    #admissions.ADMITTIME = pd.to_datetime(admissions.ADMITTIME, format = '%Y-%m-%d %H:%M:%S', errors = 'coerce')\n",
    "    #admissions.DISCHTIME = pd.to_datetime(admissions.DISCHTIME, format = '%Y-%m-%d %H:%M:%S', errors = 'coerce')\n",
    "    admissions['length'] = admissions['DISCHTIME'] - admissions['ADMITTIME']\n",
    "\n",
    "    # Add diagnoses icd list. Icd codes automatically in the order of priority (descending priority), which is important for billing.\n",
    "    diagnoses_icd_list = diagnoses_icd.groupby('HADM_ID')['ICD9_CODE'].apply(list).reset_index(name='diagnosis_condition')\n",
    "    admissions = pd.merge(admissions, diagnoses_icd_list, on='HADM_ID')\n",
    "    \n",
    "    # Define encounter service type\n",
    "    admissions['serviceType'] = 'admission'\n",
    "    \n",
    "    # Rename columns to FHIR names\n",
    "    admissions.rename(columns={'SUBJECT_ID':'subject',\n",
    "                               'HADM_ID':'identifier', \n",
    "                               'ADMITTIME':'period_start', \n",
    "                               'DISCHTIME':'period_end', \n",
    "                               'ADMISSION_TYPE':'type',\n",
    "                               'ADMISSION_LOCATION':'hospitalization_origin',\n",
    "                               'DISCHARGE_LOCATION':'hospitalization_destination',\n",
    "                               'INSURANCE':'serviceProvider',\n",
    "                               'DIAGNOSIS':'diagnosis_description'}, inplace=True)\n",
    "    \n",
    "    admissions = admissions.reindex(columns=['subject', \n",
    "                                             'identifier', \n",
    "                                             'period_start', \n",
    "                                             'period_end',\n",
    "                                             'length',\n",
    "                                             'type',\n",
    "                                             'hospitalization_origin',\n",
    "                                             'hospitalization_destination',\n",
    "                                             'serviceProvider',\n",
    "                                             'diagnosis_condition',\n",
    "                                             'diagnosis_description'], copy=False)\n",
    "    \n",
    "    # Output to csv\n",
    "    admissions.to_csv(output_path+'encounter.csv.gz', compression='gzip', index=False)\n",
    "    return admissions\n",
    "\n",
    "encounter_admissions = transform_admissions(data_path, output_path)\n",
    "encounter_admissions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del encounter_admissions\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subject</th>\n",
       "      <th>partOf</th>\n",
       "      <th>identifier</th>\n",
       "      <th>period_start</th>\n",
       "      <th>period_end</th>\n",
       "      <th>length</th>\n",
       "      <th>type</th>\n",
       "      <th>serviceType</th>\n",
       "      <th>location</th>\n",
       "      <th>location_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>268</td>\n",
       "      <td>110404</td>\n",
       "      <td>280836</td>\n",
       "      <td>2198-02-14 23:27:38</td>\n",
       "      <td>2198-02-18 05:26:11</td>\n",
       "      <td>3 days 05:58:33.600000</td>\n",
       "      <td>carevue</td>\n",
       "      <td>icustay</td>\n",
       "      <td>[MICU, MICU]</td>\n",
       "      <td>[52, 52]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>269</td>\n",
       "      <td>106296</td>\n",
       "      <td>206613</td>\n",
       "      <td>2170-11-05 11:05:29</td>\n",
       "      <td>2170-11-08 17:46:57</td>\n",
       "      <td>3 days 06:41:28.320000</td>\n",
       "      <td>carevue</td>\n",
       "      <td>icustay</td>\n",
       "      <td>[MICU, MICU]</td>\n",
       "      <td>[52, 52]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>270</td>\n",
       "      <td>188028</td>\n",
       "      <td>220345</td>\n",
       "      <td>2128-06-24 15:05:20</td>\n",
       "      <td>2128-06-27 12:32:29</td>\n",
       "      <td>2 days 21:27:12.960000</td>\n",
       "      <td>carevue</td>\n",
       "      <td>icustay</td>\n",
       "      <td>[CCU, CCU]</td>\n",
       "      <td>[57, 57]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>271</td>\n",
       "      <td>173727</td>\n",
       "      <td>249196</td>\n",
       "      <td>2120-08-07 23:12:42</td>\n",
       "      <td>2120-08-10 00:39:04</td>\n",
       "      <td>2 days 01:26:24</td>\n",
       "      <td>carevue</td>\n",
       "      <td>icustay</td>\n",
       "      <td>[MICU, SICU]</td>\n",
       "      <td>[52, 23]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>272</td>\n",
       "      <td>164716</td>\n",
       "      <td>210407</td>\n",
       "      <td>2186-12-25 21:08:04</td>\n",
       "      <td>2186-12-27 12:01:13</td>\n",
       "      <td>1 days 14:53:05.280000</td>\n",
       "      <td>carevue</td>\n",
       "      <td>icustay</td>\n",
       "      <td>[CCU, CCU]</td>\n",
       "      <td>[57, 57]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subject  partOf  identifier        period_start          period_end  \\\n",
       "0      268  110404      280836 2198-02-14 23:27:38 2198-02-18 05:26:11   \n",
       "1      269  106296      206613 2170-11-05 11:05:29 2170-11-08 17:46:57   \n",
       "2      270  188028      220345 2128-06-24 15:05:20 2128-06-27 12:32:29   \n",
       "3      271  173727      249196 2120-08-07 23:12:42 2120-08-10 00:39:04   \n",
       "4      272  164716      210407 2186-12-25 21:08:04 2186-12-27 12:01:13   \n",
       "\n",
       "                  length     type serviceType      location location_id  \n",
       "0 3 days 05:58:33.600000  carevue     icustay  [MICU, MICU]    [52, 52]  \n",
       "1 3 days 06:41:28.320000  carevue     icustay  [MICU, MICU]    [52, 52]  \n",
       "2 2 days 21:27:12.960000  carevue     icustay    [CCU, CCU]    [57, 57]  \n",
       "3        2 days 01:26:24  carevue     icustay  [MICU, SICU]    [52, 23]  \n",
       "4 1 days 14:53:05.280000  carevue     icustay    [CCU, CCU]    [57, 57]  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_icustays(data_path, output_path):\n",
    "    icustays = pd.read_csv(data_path+'ICUSTAYS'+file_ext, compression=compression,\n",
    "                           usecols=['SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID', 'DBSOURCE',\n",
    "                                    'FIRST_CAREUNIT', 'LAST_CAREUNIT', 'FIRST_WARDID',\n",
    "                                    'LAST_WARDID', 'INTIME', 'OUTTIME', 'LOS'],\n",
    "                           parse_dates=['INTIME', 'OUTTIME'],\n",
    "                           dtype={'SUBJECT_ID': int, 'HADM_ID': int, 'ICUSTAY_ID': int,\n",
    "                                  'DBSOURCE': 'category', 'FIRST_CAREUNIT': 'category',\n",
    "                                  'LAST_CAREUNIT': 'category', 'FIRST_WARDID': int,\n",
    "                                  'LAST_WARDID': int, 'LOS': float})    \n",
    "\n",
    "    ##### !!! WHY, whe have 'LOS' columns for that !!!\n",
    "    # Convert times to datetime formats and add length column\n",
    "    #icustays.INTIME = pd.to_datetime(icustays.INTIME, format = '%Y-%m-%d %H:%M:%S', errors = 'coerce')\n",
    "    #icustays.OUTTIME = pd.to_datetime(icustays.OUTTIME, format = '%Y-%m-%d %H:%M:%S', errors = 'coerce')\n",
    "    #icustays['length'] = icustays['OUTTIME'] - icustays['INTIME']\n",
    "    icustays['length'] = pd.to_timedelta(icustays['LOS'], unit='day')\n",
    "\n",
    "    # Combine ADMISSION_LOCATION and DISCHARGE_LOCATION to location list\n",
    "    icustays['location'] = icustays[['FIRST_CAREUNIT', 'LAST_CAREUNIT']].values.tolist()\n",
    "    icustays.drop(['FIRST_CAREUNIT', 'LAST_CAREUNIT'], axis=1, inplace=True)\n",
    "    icustays['location_id'] = icustays[['FIRST_WARDID', 'LAST_WARDID']].values.tolist()\n",
    "    icustays.drop(['FIRST_WARDID', 'LAST_WARDID'], axis=1, inplace=True)\n",
    "    \n",
    "    # Define encounter service type\n",
    "    icustays['serviceType'] = 'icustay'\n",
    "\n",
    "    # Rename columns to FHIR names\n",
    "    icustays.rename(columns={'SUBJECT_ID':'subject',\n",
    "                             'HADM_ID':'partOf', \n",
    "                             'ICUSTAY_ID':'identifier',\n",
    "                             'DBSOURCE':'type',\n",
    "                             'INTIME':'period_start', \n",
    "                             'OUTTIME':'period_end'}, inplace=True)\n",
    "    \n",
    "    icustays = icustays.reindex(columns=['subject', \n",
    "                                         'partOf',\n",
    "                                         'identifier', \n",
    "                                         'period_start', \n",
    "                                         'period_end',\n",
    "                                         'length',\n",
    "                                         'type',\n",
    "                                         'serviceType',\n",
    "                                         'location',\n",
    "                                         'location_id'], copy=False)\n",
    "    \n",
    "    # Output to csv\n",
    "    icustays.to_csv(output_path+'encounter_icustays.csv.gz', compression='gzip', index=False)\n",
    "    return icustays\n",
    "\n",
    "encounter_icustays = transform_icustays(data_path, output_path)\n",
    "encounter_icustays.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del encounter_icustays\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fhir.claim table\n",
    "\n",
    "#### MAPPING: <br>\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.cptevents.ROW_ID|fhir.claim.identifier|\n",
    "|2|mimic.cptevents.SUBJECT_ID|fhir.claim.subject|\n",
    "|3|mimic.cptevents.HADM_ID|fhir.claim.encounter|\n",
    "|4|mimic.cptevents.COSTCENTER|fhir.claim.provider|\n",
    "|5|mimic.cptevents.CHARTDATE|fhir.claim.created|\n",
    "|6|mimic.cptevents.CPT_NUMBER|fhir.claim.item_category_cpt_num|\n",
    "|7|mimic.cptevents.CPT_SUFFIX|fhir.claim.item_category_cpt_str|\n",
    "|8|mimic.cptevents.TICKET_ID_SEQ|fhir.claim.item_sequence|\n",
    "|9|mimic.cptevents.DESCRIPTION|fhir.claim.item_detail|\n",
    "|10|mimic.cptevents.SECTIONHEADER|fhir.claim.type|\n",
    "|11|mimic.cptevents.SUBSECTIONHEADER|fhir.claim.subType|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>identifier</th>\n",
       "      <th>subject</th>\n",
       "      <th>encounter</th>\n",
       "      <th>provider</th>\n",
       "      <th>created</th>\n",
       "      <th>item_category_cpt_num</th>\n",
       "      <th>item_category_cpt_str</th>\n",
       "      <th>item_sequence</th>\n",
       "      <th>type</th>\n",
       "      <th>subType</th>\n",
       "      <th>item_detail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>317</td>\n",
       "      <td>11743</td>\n",
       "      <td>129545</td>\n",
       "      <td>ICU</td>\n",
       "      <td>NaT</td>\n",
       "      <td>99232.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Evaluation and management</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>318</td>\n",
       "      <td>11743</td>\n",
       "      <td>129545</td>\n",
       "      <td>ICU</td>\n",
       "      <td>NaT</td>\n",
       "      <td>99232.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Evaluation and management</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>319</td>\n",
       "      <td>11743</td>\n",
       "      <td>129545</td>\n",
       "      <td>ICU</td>\n",
       "      <td>NaT</td>\n",
       "      <td>99232.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Evaluation and management</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>320</td>\n",
       "      <td>11743</td>\n",
       "      <td>129545</td>\n",
       "      <td>ICU</td>\n",
       "      <td>NaT</td>\n",
       "      <td>99232.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Evaluation and management</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>321</td>\n",
       "      <td>6185</td>\n",
       "      <td>183725</td>\n",
       "      <td>ICU</td>\n",
       "      <td>NaT</td>\n",
       "      <td>99223.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Evaluation and management</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter provider created  item_category_cpt_num  \\\n",
       "0         317    11743     129545      ICU     NaT                99232.0   \n",
       "1         318    11743     129545      ICU     NaT                99232.0   \n",
       "2         319    11743     129545      ICU     NaT                99232.0   \n",
       "3         320    11743     129545      ICU     NaT                99232.0   \n",
       "4         321     6185     183725      ICU     NaT                99223.0   \n",
       "\n",
       "  item_category_cpt_str  item_sequence                       type  \\\n",
       "0                   NaN            6.0  Evaluation and management   \n",
       "1                   NaN            7.0  Evaluation and management   \n",
       "2                   NaN            8.0  Evaluation and management   \n",
       "3                   NaN            9.0  Evaluation and management   \n",
       "4                   NaN            1.0  Evaluation and management   \n",
       "\n",
       "                       subType item_detail  \n",
       "0  Hospital inpatient services         NaN  \n",
       "1  Hospital inpatient services         NaN  \n",
       "2  Hospital inpatient services         NaN  \n",
       "3  Hospital inpatient services         NaN  \n",
       "4  Hospital inpatient services         NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_cptevents(data_path, output_path):\n",
    "    cptevents = pd.read_csv(data_path+'CPTEVENTS'+file_ext, compression=compression,\n",
    "                            usecols=lambda x: x not in ['CPT_CD'],\n",
    "                            dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': int,  \n",
    "                                   'SECTIONHEADER': 'category', 'SUBSECTIONHEADER': 'category',\n",
    "                                   'COSTCENTER': 'category', 'CPT_NUMBER': float,\n",
    "                                   'CPT_SUFFIX': str, 'TICKET_ID_SEQ': float,\n",
    "                                   'DESCRIPTION': str},\n",
    "                           parse_dates=['CHARTDATE'])\n",
    "    \n",
    "#     cptevents.CHARTDATE = pd.to_datetime(cptevents.CHARTDATE, format = '%Y-%m-%d', errors = 'coerce')\n",
    "# \n",
    "#     Drop extra columns\n",
    "#     cptevents.drop(['CPT_CD'], axis=1, inplace=True)\n",
    "\n",
    "    # ROW_ID as identifier looks like a bad idea\n",
    "    cptevents.rename(columns={'ROW_ID':'identifier',\n",
    "                              'SUBJECT_ID':'subject', \n",
    "                              'HADM_ID':'encounter', \n",
    "                              'CHARTDATE':'created', \n",
    "                              'SECTIONHEADER':'type',\n",
    "                              'SUBSECTIONHEADER':'subType',\n",
    "                              'COSTCENTER':'provider',\n",
    "                              'CPT_NUMBER':'item_category_cpt_num',\n",
    "                              'CPT_SUFFIX':'item_category_cpt_str',\n",
    "                              'TICKET_ID_SEQ':'item_sequence',\n",
    "                              'DESCRIPTION':'item_detail'}, inplace=True)\n",
    "\n",
    "    cptevents.to_csv(output_path+'claim.csv.gz', compression='gzip', index=False)\n",
    "    return cptevents\n",
    "\n",
    "claims = transform_cptevents(data_path, output_path)\n",
    "claims.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del claims\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fhir.diagnosticReport table\n",
    "\n",
    "#### MAPPING: <br>\n",
    "\n",
    "- DESCRIPTION isn't a code but comes closest to the idea of a concept.\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.notevents.ROW_ID|fhir.diagnosticReport.identifier|\n",
    "|2|mimic.notevents.SUBJECT_ID|fhir.diagnosticReport.subject|\n",
    "|3|mimic.notevents.HADM_ID|fhir.diagnosticReport.encounter|\n",
    "|4|mimic.notevents.CHARTDATE|fhir.diagnosticReport.effectiveDateTime|\n",
    "|5|mimic.notevents.CGID|fhir.diagnosticReport.performer|\n",
    "|6|mimic.notevents.CATEGORY|fhir.diagnosticReport.category|\n",
    "|7|mimic.notevents.DESCRIPTION|fhir.diagnosticReport.codeDisplay|\n",
    "|8|mimic.notevents.TEXT|fhir.diagnosticReport.presentedForm|\n",
    "|9|mimic.notevents.ISERROR| fhir.diagnosticReport.status_error|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>identifier</th>\n",
       "      <th>subject</th>\n",
       "      <th>encounter</th>\n",
       "      <th>effectiveDateTime</th>\n",
       "      <th>category</th>\n",
       "      <th>codeDisplay</th>\n",
       "      <th>performer</th>\n",
       "      <th>status_error</th>\n",
       "      <th>presentedForm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>174</td>\n",
       "      <td>22532</td>\n",
       "      <td>167853.0</td>\n",
       "      <td>2151-08-04</td>\n",
       "      <td>Discharge summary</td>\n",
       "      <td>Report</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Admission Date:  [**2151-7-16**]       Dischar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>175</td>\n",
       "      <td>13702</td>\n",
       "      <td>107527.0</td>\n",
       "      <td>2118-06-14</td>\n",
       "      <td>Discharge summary</td>\n",
       "      <td>Report</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Admission Date:  [**2118-6-2**]       Discharg...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>176</td>\n",
       "      <td>13702</td>\n",
       "      <td>167118.0</td>\n",
       "      <td>2119-05-25</td>\n",
       "      <td>Discharge summary</td>\n",
       "      <td>Report</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Admission Date:  [**2119-5-4**]              D...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>177</td>\n",
       "      <td>13702</td>\n",
       "      <td>196489.0</td>\n",
       "      <td>2124-08-18</td>\n",
       "      <td>Discharge summary</td>\n",
       "      <td>Report</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Admission Date:  [**2124-7-21**]              ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>178</td>\n",
       "      <td>26880</td>\n",
       "      <td>135453.0</td>\n",
       "      <td>2162-03-25</td>\n",
       "      <td>Discharge summary</td>\n",
       "      <td>Report</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Admission Date:  [**2162-3-3**]              D...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter effectiveDateTime           category  \\\n",
       "0         174    22532   167853.0        2151-08-04  Discharge summary   \n",
       "1         175    13702   107527.0        2118-06-14  Discharge summary   \n",
       "2         176    13702   167118.0        2119-05-25  Discharge summary   \n",
       "3         177    13702   196489.0        2124-08-18  Discharge summary   \n",
       "4         178    26880   135453.0        2162-03-25  Discharge summary   \n",
       "\n",
       "  codeDisplay  performer status_error  \\\n",
       "0      Report        NaN          NaN   \n",
       "1      Report        NaN          NaN   \n",
       "2      Report        NaN          NaN   \n",
       "3      Report        NaN          NaN   \n",
       "4      Report        NaN          NaN   \n",
       "\n",
       "                                       presentedForm  \n",
       "0  Admission Date:  [**2151-7-16**]       Dischar...  \n",
       "1  Admission Date:  [**2118-6-2**]       Discharg...  \n",
       "2  Admission Date:  [**2119-5-4**]              D...  \n",
       "3  Admission Date:  [**2124-7-21**]              ...  \n",
       "4  Admission Date:  [**2162-3-3**]              D...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_noteevents(data_path, output_path):\n",
    "    noteevents = pd.read_csv(data_path+'NOTEEVENTS'+file_ext, compression=compression,\n",
    "                             usecols=lambda x: x not in ['CHARTTIME', 'STORETIME'],\n",
    "                             parse_dates=['CHARTDATE'],\n",
    "                             dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': float,\n",
    "                                    'CGID': float, 'CATEGORY': 'category',\n",
    "                                    'DESCRIPTION': str, 'TEXT': str, 'ISERROR': str}) \n",
    "#     noteevents.CHARTDATE = pd.to_datetime(noteevents.CHARTDATE, format = '%Y-%m-%d', errors = 'coerce')\n",
    "# \n",
    "#     Drop extra columns\n",
    "#     noteevents.drop(['CHARTTIME', 'STORETIME'], axis=1, inplace=True)\n",
    "    \n",
    "    # ROW_ID as identifier looks like a bad idea\n",
    "    noteevents.rename(columns={'ROW_ID':'identifier',\n",
    "                                'SUBJECT_ID':'subject',\n",
    "                                'HADM_ID':'encounter',\n",
    "                                'CHARTDATE':'effectiveDateTime',\n",
    "                                'CGID':'performer',\n",
    "                                'CATEGORY':'category',\n",
    "                                'DESCRIPTION':'codeDisplay',\n",
    "                                'TEXT':'presentedForm',\n",
    "                                'ISERROR':'status_error'}, inplace=True)\n",
    "\n",
    "#     noteevents.to_csv(output_path+'diagnosticReport.csv.gz', compression='gzip', index=False) \n",
    "    return noteevents\n",
    "\n",
    "diagnosticReport = transform_noteevents(data_path, output_path)\n",
    "diagnosticReport.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Speed tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5min 22s ± 0 ns per loop (mean ± std. dev. of 1 run, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit -n1 -r1\n",
    "# that takes a lot of time\n",
    "diagnosticReport.to_csv(output_path+'diagnosticReport.csv.gz', compression='gzip', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2min 11s ± 0 ns per loop (mean ± std. dev. of 1 run, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# %%timeit -n1 -r1\n",
    "# https://github.com/pandas-dev/pandas/issues/33196\n",
    "# https://github.com/pandas-dev/pandas/pull/33398\n",
    "diagnosticReport.to_csv(output_path+'diagnosticReport.csv.gz', compression={'method': 'gzip', 'compresslevel': 1},\n",
    "                        index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2min 14s ± 0 ns per loop (mean ± std. dev. of 1 run, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit -n1 -r1\n",
    "# https://github.com/pandas-dev/pandas/issues/33196\n",
    "# https://github.com/pandas-dev/pandas/pull/33398\n",
    "diagnosticReport.to_csv(output_path+'diagnosticReport_3.csv.gz', compression={'method': 'gzip', 'compresslevel': 1},\n",
    "                        index=False, chunksize=10000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2min 15s ± 0 ns per loop (mean ± std. dev. of 1 run, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit -n1 -r1\n",
    "# https://github.com/pandas-dev/pandas/issues/33196\n",
    "# https://github.com/pandas-dev/pandas/pull/33398\n",
    "diagnosticReport.to_csv(output_path+'diagnosticReport_4.csv.gz', compression={'method': 'gzip', 'compresslevel': 1},\n",
    "                        index=False, chunksize=100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2min 11s ± 0 ns per loop (mean ± std. dev. of 1 run, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit -n1 -r1\n",
    "# https://github.com/pandas-dev/pandas/issues/33196\n",
    "# https://github.com/pandas-dev/pandas/pull/33398\n",
    "diagnosticReport.to_csv(output_path+'diagnosticReport_5.csv.gz', compression={'method': 'gzip', 'compresslevel': 1},\n",
    "                        index=False, chunksize=1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del diagnosticReport\n",
    "gc.collect()"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}