004-kba-mimicIII_ETL_FHIR.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "behavioral-baghdad",
   "metadata": {},
   "source": [
    "# MIMIC-III tables to FHIR resource mapping in this notebook\n",
    "\n",
    "||Original format | FHIR resource| Progress|Final Check|\n",
    "|------|:-----|:-----|:-----|---:---|\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": "code",
   "execution_count": 1,
   "id": "million-atlanta",
   "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,
   "id": "opened-accuracy",
   "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": "markdown",
   "id": "present-sheet",
   "metadata": {},
   "source": [
    "## fhir.practitioner table\n",
    "\n",
    "#### MAPPING:\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.caregivers.CGID|fhir.practitioner.identifier|\n",
    "|2|mimic.caregivers.LABEL|fhir.practitioner.qualification_category|\n",
    "|3|mimic.caregivers.DESCRIPTION|fhir.practitioner.qualification_label|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "stuffed-quebec",
   "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>qualification_label</th>\n",
       "      <th>qualification_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16174</td>\n",
       "      <td>RO</td>\n",
       "      <td>Read Only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16175</td>\n",
       "      <td>RO</td>\n",
       "      <td>Read Only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16176</td>\n",
       "      <td>Res</td>\n",
       "      <td>Resident/Fellow/PA/NP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16177</td>\n",
       "      <td>RO</td>\n",
       "      <td>Read Only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16178</td>\n",
       "      <td>RT</td>\n",
       "      <td>Respiratory</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier qualification_label qualification_category\n",
       "0       16174                  RO              Read Only\n",
       "1       16175                  RO              Read Only\n",
       "2       16176                 Res  Resident/Fellow/PA/NP\n",
       "3       16177                  RO              Read Only\n",
       "4       16178                  RT            Respiratory"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_caregivers(data_path, output_path):\n",
    "    caregivers = pd.read_csv(data_path+'CAREGIVERS'+file_ext, compression=compression,\n",
    "                             usecols = ['CGID', 'LABEL', 'DESCRIPTION'],\n",
    "                             dtype = {'CGID': int, 'LABEL': str, 'DESCRIPTION': str})\n",
    "\n",
    "    caregivers.rename(columns={'CGID':'identifier',\n",
    "                               'LABEL':'qualification_label',\n",
    "                               'DESCRIPTION':'qualification_category'}, inplace=True)\n",
    "\n",
    "    caregivers.to_csv(output_path+'practitioner.csv.gz', compression='gzip', index=False)\n",
    "    return caregivers\n",
    "\n",
    "practitioner = transform_caregivers(data_path, output_path)\n",
    "practitioner.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "attached-dance",
   "metadata": {},
   "source": [
    "## fhir.procedure table\n",
    "\n",
    "#### PROCEDURES_ICD MAPPING:<br>\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.procedures_icd.ROW_ID | fhir.procedure.identifier|\n",
    "|2|mimic.procedures_icd.SUBJECT_ID | fhir.procedure.subject|\n",
    "|3|mimic.procedures_icd.HADM_ID | fhir.procedure.encounter|\n",
    "|4|mimic.procedures_icd.ICD9_CODE | fhir.procedure.code_icd9|\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "prerequisite-hazard",
   "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>code</th>\n",
       "      <th>followUp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>944</td>\n",
       "      <td>62641</td>\n",
       "      <td>154460</td>\n",
       "      <td>3404</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>945</td>\n",
       "      <td>2592</td>\n",
       "      <td>130856</td>\n",
       "      <td>9671</td>\n",
       "      <td>946.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>946</td>\n",
       "      <td>2592</td>\n",
       "      <td>130856</td>\n",
       "      <td>3893</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>947</td>\n",
       "      <td>55357</td>\n",
       "      <td>119355</td>\n",
       "      <td>9672</td>\n",
       "      <td>948.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>948</td>\n",
       "      <td>55357</td>\n",
       "      <td>119355</td>\n",
       "      <td>0331</td>\n",
       "      <td>949.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter  code  followUp\n",
       "0         944    62641     154460  3404       NaN\n",
       "1         945     2592     130856  9671     946.0\n",
       "2         946     2592     130856  3893       NaN\n",
       "3         947    55357     119355  9672     948.0\n",
       "4         948    55357     119355  0331     949.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_procedures_icd(data_path, output_path):\n",
    "    \"\"\" original function had erroneous result \"\"\"\n",
    "    procedures_icd = pd.read_csv(data_path+'PROCEDURES_ICD'+file_ext, compression=compression,\n",
    "                                 dtype = {'ROW_ID': int, 'SUBJECT_ID': int,\n",
    "                                          'HADM_ID': int, 'ICD9_CODE': str,\n",
    "                                          'SEQ_NUM': int})\n",
    "    \n",
    "    # will have next procedure ROW_ID if any\n",
    "    procedures_icd['followUp'] = procedures_icd.sort_values(['HADM_ID', 'SEQ_NUM']).groupby('HADM_ID')['ROW_ID'].shift(-1)\n",
    "    procedures_icd.drop(columns=['SEQ_NUM'], inplace=True)\n",
    "    procedures_icd.rename(columns={'ROW_ID':'identifier',\n",
    "                                   'SUBJECT_ID':'subject',\n",
    "                                   'HADM_ID':'encounter',\n",
    "                                   'ICD9_CODE':'code'}, inplace=True)\n",
    "\n",
    "    procedures_icd.to_csv(output_path+'procedure_icd9.csv.gz', compression='gzip', index=False)\n",
    "    return procedures_icd\n",
    "\n",
    "procedure_icd9 = transform_procedures_icd(data_path, output_path)\n",
    "procedure_icd9.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hired-universe",
   "metadata": {},
   "source": [
    "#### PROCEDUREEVENTS_MV MAPPING:<br>\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.procedureevents_mv.ROW_ID | fhir.procedure.identifier|\n",
    "|2|mimic.procedureevents_mv.SUBJECT_ID | fhir.procedure.subject|\n",
    "|3|mimic.procedureevents_mv.HADM_ID | fhir.procedure.encounter|\n",
    "|4|mimic.procedureevents_mv.ICUSTAY_ID| fhir.procedure.partOf|\n",
    "|5|mimic.procedureevents_mv.STARTTIME| fhir.procedure.performedRange_start|\n",
    "|6|mimic.procedureevents_mv.ENDTIME| fhir.procedure.performedRange_end|\n",
    "|7|mimic.procedureevents_mv.ITEMID| fhir.procedure.code|\n",
    "|8|mimic.procedureevents_mv.VALUE| fhir.procedure.outcome_value|\n",
    "|9|mimic.procedureevents_mv.VALUEOM | fhir.procedure.outcome_unit|\n",
    "|10|mimic.procedureevents_mv.LOCATION| fhir.procedure.location_name|\n",
    "|11|mimic.procedureevents_mv.LOCATIONCATEGORY| fhir.procedure.location_category|\n",
    "|12|mimic.procedureevents_mv.CGID|fhir.procedure.performer|\n",
    "|13|mimic.procedureevents_mv.ORDERID| fhir.procedure.basedOn|\n",
    "|14|mimic.procedureevents_mv.LINKORDERID|fhir.procedure.basedOn_linked|\n",
    "|15|mimic.procedureevents_mv.ORDERCATEGORYNAME| fhir.procedure.category_order_name|\n",
    "|16|mimic.procedureevents_mv.SECONDARYORDERCATEGORYNAME|fhir.procedure.category_secOrder_name|\n",
    "|17|mimic.procedureevents_mv.ORDERCATEGORYDESCRIPTION|fhir.procedure.category_order_description|\n",
    "|18|mimic.procedureevents_mv.ISOPENBAG| fhir.procedure.usedReference_openBag|\n",
    "|19|mimic.procedureevents_mv.CONTINUEINEXTDEPT| fhir.procedure.report_contExtDep|\n",
    "|20|mimic.procedureevents_mv.CANCELREASON| fhir.procedure.report_cancelReason|\n",
    "|21|mimic.procedureevents_mv.STATUSDESCRIPTION| fhir.procedure.status|\n",
    "|22|mimic.procedureevents_mv.COMMENTS_EDITEDBY| fhir.procedure.report_editedBy|\n",
    "|23|mimic.procedureevents_mv.COMMENTS_CANCELEDBY| fhir.procedure.report_canceledBy|\n",
    "|24|mimic.procedureevents_mv.COMMENTS_DATE| fhir.procedure.report_canceledDate|\n",
    "|25|mimic.d_items.(LABEL+DBSOURCE+PARAM_TYPE)|mimic.procedure.note|\n",
    "|26|mimic.d_items.CATEGORY|mimic.procedure.category|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "personalized-brunei",
   "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>partOf</th>\n",
       "      <th>performedRange_start</th>\n",
       "      <th>performedRange_end</th>\n",
       "      <th>code</th>\n",
       "      <th>outcome_value</th>\n",
       "      <th>outcome_unit</th>\n",
       "      <th>location_name</th>\n",
       "      <th>...</th>\n",
       "      <th>category_order_description</th>\n",
       "      <th>usedReference_openBag</th>\n",
       "      <th>report_contNextDep</th>\n",
       "      <th>report_cancelReason</th>\n",
       "      <th>status</th>\n",
       "      <th>report_editedBy</th>\n",
       "      <th>report_canceledBy</th>\n",
       "      <th>report_canceledDate</th>\n",
       "      <th>category</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>379</td>\n",
       "      <td>29070</td>\n",
       "      <td>115071</td>\n",
       "      <td>232563.0</td>\n",
       "      <td>2145-03-12 23:04:00</td>\n",
       "      <td>2145-03-12 23:05:00</td>\n",
       "      <td>225401</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Electrolytes</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>FinishedRunning</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>6-Cultures</td>\n",
       "      <td>Blood Cultured metavision Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>390</td>\n",
       "      <td>29070</td>\n",
       "      <td>115071</td>\n",
       "      <td>232563.0</td>\n",
       "      <td>2145-03-15 12:17:00</td>\n",
       "      <td>2145-03-15 12:18:00</td>\n",
       "      <td>225401</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Electrolytes</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>FinishedRunning</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>6-Cultures</td>\n",
       "      <td>Blood Cultured metavision Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>393</td>\n",
       "      <td>29070</td>\n",
       "      <td>115071</td>\n",
       "      <td>232563.0</td>\n",
       "      <td>2145-03-16 12:02:00</td>\n",
       "      <td>2145-03-16 12:03:00</td>\n",
       "      <td>225401</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Electrolytes</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>FinishedRunning</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>6-Cultures</td>\n",
       "      <td>Blood Cultured metavision Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>396</td>\n",
       "      <td>29070</td>\n",
       "      <td>115071</td>\n",
       "      <td>232563.0</td>\n",
       "      <td>2145-03-17 00:51:00</td>\n",
       "      <td>2145-03-17 00:52:00</td>\n",
       "      <td>225401</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Electrolytes</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Rewritten</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RN</td>\n",
       "      <td>2145-03-17 00:52:00</td>\n",
       "      <td>6-Cultures</td>\n",
       "      <td>Blood Cultured metavision Process</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>397</td>\n",
       "      <td>29070</td>\n",
       "      <td>115071</td>\n",
       "      <td>232563.0</td>\n",
       "      <td>2145-03-17 00:52:00</td>\n",
       "      <td>2145-03-17 00:53:00</td>\n",
       "      <td>225401</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Electrolytes</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>FinishedRunning</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>6-Cultures</td>\n",
       "      <td>Blood Cultured metavision Process</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter    partOf performedRange_start  \\\n",
       "0         379    29070     115071  232563.0  2145-03-12 23:04:00   \n",
       "1         390    29070     115071  232563.0  2145-03-15 12:17:00   \n",
       "2         393    29070     115071  232563.0  2145-03-16 12:02:00   \n",
       "3         396    29070     115071  232563.0  2145-03-17 00:51:00   \n",
       "4         397    29070     115071  232563.0  2145-03-17 00:52:00   \n",
       "\n",
       "   performedRange_end    code  outcome_value outcome_unit location_name  ...  \\\n",
       "0 2145-03-12 23:05:00  225401            1.0         None           NaN  ...   \n",
       "1 2145-03-15 12:18:00  225401            1.0         None           NaN  ...   \n",
       "2 2145-03-16 12:03:00  225401            1.0         None           NaN  ...   \n",
       "3 2145-03-17 00:52:00  225401            1.0         None           NaN  ...   \n",
       "4 2145-03-17 00:53:00  225401            1.0         None           NaN  ...   \n",
       "\n",
       "  category_order_description  usedReference_openBag  report_contNextDep  \\\n",
       "0               Electrolytes                      0                   0   \n",
       "1               Electrolytes                      0                   0   \n",
       "2               Electrolytes                      0                   0   \n",
       "3               Electrolytes                      0                   0   \n",
       "4               Electrolytes                      0                   0   \n",
       "\n",
       "   report_cancelReason           status report_editedBy report_canceledBy  \\\n",
       "0                    0  FinishedRunning             NaN               NaN   \n",
       "1                    0  FinishedRunning             NaN               NaN   \n",
       "2                    0  FinishedRunning             NaN               NaN   \n",
       "3                    1        Rewritten             NaN                RN   \n",
       "4                    0  FinishedRunning             NaN               NaN   \n",
       "\n",
       "   report_canceledDate    category                               note  \n",
       "0                  NaT  6-Cultures  Blood Cultured metavision Process  \n",
       "1                  NaT  6-Cultures  Blood Cultured metavision Process  \n",
       "2                  NaT  6-Cultures  Blood Cultured metavision Process  \n",
       "3  2145-03-17 00:52:00  6-Cultures  Blood Cultured metavision Process  \n",
       "4                  NaT  6-Cultures  Blood Cultured metavision Process  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_procedurevents_mv(data_path, output_path):\n",
    "    procedurevents_mv = pd.read_csv(data_path+'PROCEDUREEVENTS_MV'+file_ext, compression=compression,\n",
    "                                    # 'STORETIME'\n",
    "                                    usecols = ['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID',\n",
    "                                               'STARTTIME', 'ENDTIME', 'ITEMID', 'VALUE', 'VALUEUOM',\n",
    "                                               'LOCATION', 'LOCATIONCATEGORY',\n",
    "                                               'CGID', 'ORDERID', 'LINKORDERID', 'ORDERCATEGORYNAME',\n",
    "                                               'SECONDARYORDERCATEGORYNAME', 'ORDERCATEGORYDESCRIPTION',\n",
    "                                               'ISOPENBAG', 'CONTINUEINNEXTDEPT', 'CANCELREASON',\n",
    "                                               'STATUSDESCRIPTION', 'COMMENTS_EDITEDBY',\n",
    "                                               'COMMENTS_CANCELEDBY', 'COMMENTS_DATE'],\n",
    "                                    dtype ={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': int,\n",
    "                                            'ICUSTAY_ID': float, 'ITEMID': int, 'VALUE': np.float64,\n",
    "                                            'VALUEUOM': str, 'LOCATION': str, 'LOCATIONCATEGORY': str,\n",
    "                                            'CGID': int, 'ORDERID': int, 'LINKORDERID': int,\n",
    "                                            'ORDERCATEGORYNAME': str, 'SECONDARYORDERCATEGORYNAME': str,\n",
    "                                            'ORDERCATEGORYDESCRIPTION': str, 'ISOPENBAG': int,\n",
    "                                            'CONTINUEINNEXTDEPT': int, 'CANCELREASON': int,\n",
    "                                            'STATUSDESCRIPTION': str, 'COMMENTS_EDITEDBY': str,\n",
    "                                            'COMMENTS_CANCELEDBY': str},\n",
    "                                    parse_dates=['ENDTIME', 'STARTTIME', 'COMMENTS_DATE'],\n",
    "                                   )\n",
    "    \n",
    "    d_items = pd.read_csv(data_path+'D_ITEMS'+file_ext, compression=compression, index_col=0,\n",
    "                      # dropped 'ABBREVIATION', 'LINKSTO', 'CONCEPTID', 'UNITNAME'\n",
    "                      usecols=['ROW_ID', 'ITEMID', 'LABEL', 'DBSOURCE', 'CATEGORY', 'PARAM_TYPE'],\n",
    "                      dtype={'ROW_ID': int, 'ITEMID': int, 'LABEL': str, 'DBSOURCE': 'category',\n",
    "                             'CATEGORY': 'category', 'PARAM_TYPE': str})\n",
    "    \n",
    "    procedurevents_mv = pd.merge(procedurevents_mv, d_items, on='ITEMID')\n",
    "    \n",
    "    procedurevents_mv['note'] = procedurevents_mv['LABEL'].str.cat(procedurevents_mv['DBSOURCE'], sep=' ', na_rep='NA')\n",
    "    procedurevents_mv['note'] = procedurevents_mv['note'].str.cat(procedurevents_mv['PARAM_TYPE'], sep=' ', na_rep='')\n",
    "    \n",
    "    procedurevents_mv.drop(columns=['LABEL', 'PARAM_TYPE', 'DBSOURCE'], inplace=True)\n",
    "\n",
    "    procedurevents_mv.rename(columns={'ROW_ID':'identifier',\n",
    "                                      'SUBJECT_ID':'subject',\n",
    "                                      'HADM_ID':'encounter',\n",
    "                                      'ICUSTAY_ID':'partOf',\n",
    "                                      'STARTTIME':'performedRange_start',\n",
    "                                      'ENDTIME':'performedRange_end',\n",
    "                                      'ITEMID':'code',\n",
    "                                      'VALUE':'outcome_value',\n",
    "                                      'VALUEUOM':'outcome_unit',\n",
    "                                      'LOCATION':'location_name',\n",
    "                                      'LOCATIONCATEGORY':'location_category',\n",
    "                                      'CGID':'performer',\n",
    "                                      'ORDERID':'basedOn',\n",
    "                                      'LINKORDERID':'basedOn_linked',\n",
    "                                      'ORDERCATEGORYNAME':'category_order_name',\n",
    "                                      'SECONDARYORDERCATEGORYNAME':'category_secOrder_name',\n",
    "                                      'ORDERCATEGORYDESCRIPTION':'category_order_description',\n",
    "                                      'ISOPENBAG':'usedReference_openBag',\n",
    "                                      'CONTINUEINNEXTDEPT':'report_contNextDep',\n",
    "                                      'CANCELREASON':'report_cancelReason',\n",
    "                                      'STATUSDESCRIPTION':'status',\n",
    "                                      'COMMENTS_EDITEDBY':'report_editedBy',\n",
    "                                      'COMMENTS_CANCELEDBY':'report_canceledBy',\n",
    "                                      'COMMENTS_DATE':'report_canceledDate',\n",
    "                                      'CATEGORY':'category'\n",
    "                                     }, inplace=True)\n",
    "\n",
    "    procedurevents_mv.to_csv(output_path+'procedure_mv.csv.gz', compression='gzip', index=False)\n",
    "    return procedurevents_mv\n",
    "\n",
    "procedure_mv = transform_procedurevents_mv(data_path, output_path)\n",
    "procedure_mv.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "associate-updating",
   "metadata": {},
   "source": [
    "## fhir.specimen table\n",
    "\n",
    "#### OUTPUTEVENTS MAPPING:<br>\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.outputevents:ROW_ID | fhir.specimen.identifier|\n",
    "|2|mimic.outputevents.SUBJECT_ID | fhir.specimen.subject|\n",
    "|3|mimic.outputevents.HADM_ID | fhir.specimen.request_encounter_admission|\n",
    "|4|mimic.outputevents.ICUSTAY_ID | fhir.specimen.request_encounter_icustay|\n",
    "|5|mimic.outputevents.ITEMID | fhir.specimen.type_code|\n",
    "|6|mimic.d_items.CATEGORY | fhir.specimen.type_category|\n",
    "|7|mimic.outputevents.CGID | fhir.specimen.collection_collector|\n",
    "|8|mimic.outputevents.CHARTTIME | fhir.specimen.collection_dateTime|\n",
    "|9|mimic.outputevents.VALUE | fhir.specimen.collection_quantity|\n",
    "|10|mimic.outputevents.VALUEUOM | fhir.specimen.collection_unit|\n",
    "|11|mimic.outputevents.NEWBOTTLE | fhir.specimen.collection_newBottle|\n",
    "|12|mimic.outputevents.(STOPPED+ISERROR) | fhir.specimen.status|\n",
    "|13|mimic.d_items.(LABEL+DBSOURCE+PARAM_TYPE) | fhir.specimen.note|\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "straight-queue",
   "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>request_encounter_admission</th>\n",
       "      <th>request_encounter_icustay</th>\n",
       "      <th>type_code</th>\n",
       "      <th>type_category</th>\n",
       "      <th>collection_collector</th>\n",
       "      <th>collection_dateTime</th>\n",
       "      <th>collection_quantity</th>\n",
       "      <th>collection_unit</th>\n",
       "      <th>collection_newBottle</th>\n",
       "      <th>status</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>344</td>\n",
       "      <td>21219</td>\n",
       "      <td>177991.0</td>\n",
       "      <td>225765.0</td>\n",
       "      <td>40055</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Urine Out Foley carevue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>345</td>\n",
       "      <td>21219</td>\n",
       "      <td>177991.0</td>\n",
       "      <td>225765.0</td>\n",
       "      <td>40055</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Urine Out Foley carevue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>346</td>\n",
       "      <td>21219</td>\n",
       "      <td>177991.0</td>\n",
       "      <td>225765.0</td>\n",
       "      <td>40055</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>120.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Urine Out Foley carevue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>347</td>\n",
       "      <td>21219</td>\n",
       "      <td>177991.0</td>\n",
       "      <td>225765.0</td>\n",
       "      <td>40055</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Urine Out Foley carevue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>348</td>\n",
       "      <td>21219</td>\n",
       "      <td>177991.0</td>\n",
       "      <td>225765.0</td>\n",
       "      <td>40055</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Urine Out Foley carevue</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  request_encounter_admission  \\\n",
       "0         344    21219                     177991.0   \n",
       "1         345    21219                     177991.0   \n",
       "2         346    21219                     177991.0   \n",
       "3         347    21219                     177991.0   \n",
       "4         348    21219                     177991.0   \n",
       "\n",
       "   request_encounter_icustay  type_code type_category  collection_collector  \\\n",
       "0                   225765.0      40055           NaN                   NaN   \n",
       "1                   225765.0      40055           NaN                   NaN   \n",
       "2                   225765.0      40055           NaN                   NaN   \n",
       "3                   225765.0      40055           NaN                   NaN   \n",
       "4                   225765.0      40055           NaN                   NaN   \n",
       "\n",
       "   collection_dateTime  collection_quantity collection_unit  \\\n",
       "0                  NaN                200.0              ml   \n",
       "1                  NaN                200.0              ml   \n",
       "2                  NaN                120.0              ml   \n",
       "3                  NaN                100.0              ml   \n",
       "4                  NaN                200.0              ml   \n",
       "\n",
       "   collection_newBottle status                      note  \n",
       "0                   NaN    NaN  Urine Out Foley carevue   \n",
       "1                   NaN    NaN  Urine Out Foley carevue   \n",
       "2                   NaN    NaN  Urine Out Foley carevue   \n",
       "3                   NaN    NaN  Urine Out Foley carevue   \n",
       "4                   NaN    NaN  Urine Out Foley carevue   "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_outputevents(data_path, output_path):\n",
    "    # 'STORETIME'\n",
    "    outputevents = pd.read_csv(data_path+'OUTPUTEVENTS'+file_ext, compression=compression,\n",
    "                               usecols = ['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID',\n",
    "                                          'CHARTTIME', 'ITEMID', 'VALUE', 'VALUEUOM',\n",
    "                                          'CGID', 'STOPPED', 'NEWBOTTLE', 'ISERROR'],\n",
    "                               dtype = {'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': float,\n",
    "                                        'ICUSTAY_ID': float, 'ITEMID': int, 'VALUE': np.float64,\n",
    "                                        'VALUEUOM': str, 'CGID': int, 'STOPPED': str,\n",
    "                                        'NEWBOTTLE': float, 'ISERROR': float},\n",
    "                               parse_dates=['CHARTTIME'],\n",
    "                               )\n",
    "    \n",
    "    d_items = pd.read_csv(data_path+'D_ITEMS'+file_ext, compression=compression, index_col=0,\n",
    "                      # dropped 'ABBREVIATION', 'LINKSTO', 'CONCEPTID', 'UNITNAME'\n",
    "                      usecols=['ROW_ID', 'ITEMID', 'LABEL', 'DBSOURCE', 'CATEGORY', 'PARAM_TYPE'],\n",
    "                      dtype={'ROW_ID': int, 'ITEMID': int, 'LABEL': str, 'DBSOURCE': 'category',\n",
    "                             'CATEGORY': 'category', 'PARAM_TYPE': str})\n",
    "    \n",
    "    specimen_oe = pd.merge(outputevents, d_items, on='ITEMID')\n",
    "\n",
    "    # Replace NaN in columns with empty strings so that concatenation in notes works\n",
    "    specimen_oe['note'] = specimen_oe['LABEL'].str.cat(specimen_oe['DBSOURCE'], sep=' ', na_rep='NA')\n",
    "    specimen_oe['note'] = specimen_oe['note'].str.cat(specimen_oe['PARAM_TYPE'], sep=' ', na_rep='')\n",
    "    \n",
    "    # Combine STOPPED and ISERROR column, Errorneous notes entries will be eliminated later on\n",
    "    specimen_oe.loc[specimen_oe.ISERROR==1,'STOPPED'] = 'Error'\n",
    "\n",
    "    # Drop Columns not needed anymore\n",
    "    specimen_oe.drop(columns=['LABEL', 'PARAM_TYPE', 'DBSOURCE', 'ISERROR'], inplace=True)\n",
    "\n",
    "    specimen_oe.rename(columns={'ROW_ID':'identifier',\n",
    "                                'SUBJECT_ID':'subject',\n",
    "                                'HADM_ID':'request_encounter_admission',\n",
    "                                'ICUSTAY_ID':'request_encounter_icustay',\n",
    "                                'ITEMID':'type_code',\n",
    "                                'CATEGORY':'type_category',\n",
    "                                'CGID':'collector',\n",
    "                                'CHARTTIME':'collected_dateTime',\n",
    "                                'VALUE':'collection_quantity',\n",
    "                                'VALUEUOM':'collection_unit',\n",
    "                                'NEWBOTTLE':'collection_newBottle',\n",
    "                                'STOPPED':'status'}, inplace=True)\n",
    "    \n",
    "    specimen_oe = specimen_oe.reindex(columns=['identifier',\n",
    "                                                'subject',\n",
    "                                                'request_encounter_admission',\n",
    "                                                'request_encounter_icustay',\n",
    "                                                'type_code',\n",
    "                                                'type_category',\n",
    "                                                'collection_collector',\n",
    "                                                'collection_dateTime',\n",
    "                                                'collection_quantity',\n",
    "                                                'collection_unit',\n",
    "                                                'collection_newBottle',\n",
    "                                                'status',\n",
    "                                                'note'], copy=False)\n",
    "\n",
    "    specimen_oe.to_csv(output_path+'specimen_oe.csv.gz', compression='gzip', index=False)\n",
    "    return specimen_oe\n",
    "\n",
    "specimen_oe = transform_outputevents(data_path, output_path)\n",
    "specimen_oe.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aging-hindu",
   "metadata": {},
   "source": [
    "#### MICROBIOLOGYEVENTS MAPPING:<br>\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.microbiologyevents.ROW_ID| fhir.specimen.identifier|\n",
    "|2|mimic.microbiologyevents.SUBJECT_ID | fhir.specimen.subject|\n",
    "|3|mimic.microbiologyevents.HADM_ID | fhir.specimen.request_encounter_admission|\n",
    "|4|mimic.microbiologyevents.CHARTTIME | fhir.specimen.collection_dateTime|\n",
    "|5|mimic.microbiologyevents.SPEC_ITEMID | fhir.specimen.type_code|\n",
    "|6|mimic.microbiologyevents.SPEC_TYPE_DESC | fhir.specimen.type_name|\n",
    "|7|mimic.d_items(on SPEC).CATEGORY | fhir.specimen.type_category|\n",
    "|8|mimic.microbiologyevents.ORG_ITEMID | fhir.specimen.method_bact_code|\n",
    "|9|mimic.microbiologyevents.ORG_NAME | fhir.specimen.method_bact_name|\n",
    "|10|mimic.microbiologyevents.ISOLATE_NUM | fhir.specimen.method_colNum|\n",
    "|11|mimic.microbiologyevents.AB_ITEMID | fhir.specimen.method_antibiotic_code|\n",
    "|12|mimic.microbiologyevents.AB_NAME | fhir.specimen.method_antibiotic_name|\n",
    "|13|mimic.microbiologyevents.DILUTION_TEXT | fhir.specimen.method_dilution_description|\n",
    "|14|mimic.microbiologyevents.DILUTION_COMPARISON | fhir.specimen.method_dilution_comp|\n",
    "|15|mimic.microbiologyevents.DILUTION_VALUE | fhir.specimen.method_dilution_value|\n",
    "|16|mimic.microbiologyevents.INTERPRETATION | fhir.specimen.note_interpretation|\n",
    "|17|mimic.d_items_(SPEC/ORG/AB).(LABEL+PARAM_TYPE+DBSOURCE) | fhir.specimen.note|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "eastern-testament",
   "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>request_encounter_admission</th>\n",
       "      <th>collection_dateTime</th>\n",
       "      <th>type_code</th>\n",
       "      <th>type_name</th>\n",
       "      <th>method_bact_code</th>\n",
       "      <th>method_bact_name</th>\n",
       "      <th>method_colNum</th>\n",
       "      <th>method_antibiotic_code</th>\n",
       "      <th>method_antibiotic_name</th>\n",
       "      <th>method_dilution_description</th>\n",
       "      <th>method_dilution_comp</th>\n",
       "      <th>method_dilution_value</th>\n",
       "      <th>note_interpretation</th>\n",
       "      <th>type_category</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>706</td>\n",
       "      <td>96</td>\n",
       "      <td>170324</td>\n",
       "      <td>2156-04-08 15:48:00</td>\n",
       "      <td>70021.0</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE</td>\n",
       "      <td>80026.0</td>\n",
       "      <td>PSEUDOMONAS AERUGINOSA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>90021.0</td>\n",
       "      <td>PIPERACILLIN</td>\n",
       "      <td>&lt;=4</td>\n",
       "      <td>&lt;=</td>\n",
       "      <td>4.0</td>\n",
       "      <td>S</td>\n",
       "      <td>SPECIMEN</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5400</td>\n",
       "      <td>419</td>\n",
       "      <td>148312</td>\n",
       "      <td>2113-06-20 17:35:00</td>\n",
       "      <td>70021.0</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE</td>\n",
       "      <td>80026.0</td>\n",
       "      <td>PSEUDOMONAS AERUGINOSA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>90021.0</td>\n",
       "      <td>PIPERACILLIN</td>\n",
       "      <td>=&gt;128</td>\n",
       "      <td>=&gt;</td>\n",
       "      <td>128.0</td>\n",
       "      <td>R</td>\n",
       "      <td>SPECIMEN</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29349</td>\n",
       "      <td>2322</td>\n",
       "      <td>169179</td>\n",
       "      <td>2176-03-15 15:00:00</td>\n",
       "      <td>70021.0</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE</td>\n",
       "      <td>80026.0</td>\n",
       "      <td>PSEUDOMONAS AERUGINOSA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>90021.0</td>\n",
       "      <td>PIPERACILLIN</td>\n",
       "      <td>&lt;=4</td>\n",
       "      <td>&lt;=</td>\n",
       "      <td>4.0</td>\n",
       "      <td>S</td>\n",
       "      <td>SPECIMEN</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39371</td>\n",
       "      <td>3064</td>\n",
       "      <td>172794</td>\n",
       "      <td>2186-06-01 14:15:00</td>\n",
       "      <td>70021.0</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE</td>\n",
       "      <td>80026.0</td>\n",
       "      <td>PSEUDOMONAS AERUGINOSA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>90021.0</td>\n",
       "      <td>PIPERACILLIN</td>\n",
       "      <td>16</td>\n",
       "      <td>=</td>\n",
       "      <td>16.0</td>\n",
       "      <td>S</td>\n",
       "      <td>SPECIMEN</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32461</td>\n",
       "      <td>2514</td>\n",
       "      <td>147513</td>\n",
       "      <td>2131-06-20 17:52:00</td>\n",
       "      <td>70021.0</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE</td>\n",
       "      <td>80026.0</td>\n",
       "      <td>PSEUDOMONAS AERUGINOSA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>90021.0</td>\n",
       "      <td>PIPERACILLIN</td>\n",
       "      <td>&lt;=4</td>\n",
       "      <td>&lt;=</td>\n",
       "      <td>4.0</td>\n",
       "      <td>S</td>\n",
       "      <td>SPECIMEN</td>\n",
       "      <td>BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  request_encounter_admission collection_dateTime  \\\n",
       "0         706       96                       170324 2156-04-08 15:48:00   \n",
       "1        5400      419                       148312 2113-06-20 17:35:00   \n",
       "2       29349     2322                       169179 2176-03-15 15:00:00   \n",
       "3       39371     3064                       172794 2186-06-01 14:15:00   \n",
       "4       32461     2514                       147513 2131-06-20 17:52:00   \n",
       "\n",
       "   type_code               type_name  method_bact_code  \\\n",
       "0    70021.0  BRONCHOALVEOLAR LAVAGE           80026.0   \n",
       "1    70021.0  BRONCHOALVEOLAR LAVAGE           80026.0   \n",
       "2    70021.0  BRONCHOALVEOLAR LAVAGE           80026.0   \n",
       "3    70021.0  BRONCHOALVEOLAR LAVAGE           80026.0   \n",
       "4    70021.0  BRONCHOALVEOLAR LAVAGE           80026.0   \n",
       "\n",
       "         method_bact_name  method_colNum  method_antibiotic_code  \\\n",
       "0  PSEUDOMONAS AERUGINOSA            1.0                 90021.0   \n",
       "1  PSEUDOMONAS AERUGINOSA            1.0                 90021.0   \n",
       "2  PSEUDOMONAS AERUGINOSA            1.0                 90021.0   \n",
       "3  PSEUDOMONAS AERUGINOSA            1.0                 90021.0   \n",
       "4  PSEUDOMONAS AERUGINOSA            1.0                 90021.0   \n",
       "\n",
       "  method_antibiotic_name method_dilution_description method_dilution_comp  \\\n",
       "0           PIPERACILLIN                         <=4                   <=   \n",
       "1           PIPERACILLIN                       =>128                   =>   \n",
       "2           PIPERACILLIN                         <=4                   <=   \n",
       "3           PIPERACILLIN                          16                    =   \n",
       "4           PIPERACILLIN                         <=4                   <=   \n",
       "\n",
       "   method_dilution_value note_interpretation type_category  \\\n",
       "0                    4.0                   S      SPECIMEN   \n",
       "1                  128.0                   R      SPECIMEN   \n",
       "2                    4.0                   S      SPECIMEN   \n",
       "3                   16.0                   S      SPECIMEN   \n",
       "4                    4.0                   S      SPECIMEN   \n",
       "\n",
       "                                                note  \n",
       "0  BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...  \n",
       "1  BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...  \n",
       "2  BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...  \n",
       "3  BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...  \n",
       "4  BRONCHOALVEOLAR LAVAGE hospital  PSEUDOMONAS A...  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_microbiologyevents(data_path, output_path):\n",
    "    # 'CHARTDATE', \n",
    "    microbiologyevents = pd.read_csv(data_path+'MICROBIOLOGYEVENTS'+file_ext, compression=compression,\n",
    "                                     usecols = ['ROW_ID', 'SUBJECT_ID', 'HADM_ID',\n",
    "                                                'CHARTTIME', 'SPEC_ITEMID', 'SPEC_TYPE_DESC',\n",
    "                                                'ORG_ITEMID', 'ORG_NAME', 'ISOLATE_NUM', 'AB_ITEMID',\n",
    "                                                'AB_NAME', 'DILUTION_TEXT', 'DILUTION_COMPARISON',\n",
    "                                                'DILUTION_VALUE', 'INTERPRETATION'],\n",
    "                                     dtype = {'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': int,\n",
    "                                              'SPEC_ITEMID': float, 'SPEC_TYPE_DESC': str,\n",
    "                                              'ORG_ITEMID': float, 'ORG_NAME': str, 'ISOLATE_NUM': float,\n",
    "                                              'AB_ITEMID': float, 'AB_NAME': str, 'DILUTION_TEXT': str,\n",
    "                                              'DILUTION_COMPARISON': str, 'DILUTION_VALUE': np.float64,\n",
    "                                              'INTERPRETATION': str},\n",
    "                                     parse_dates=['CHARTTIME'],\n",
    "                                    )\n",
    "    \n",
    "    d_items = pd.read_csv(data_path+'D_ITEMS'+file_ext, compression=compression, index_col=0,\n",
    "                          # dropped 'ABBREVIATION', 'LINKSTO', 'CONCEPTID', 'UNITNAME'\n",
    "                          usecols=['ROW_ID', 'ITEMID', 'LABEL', 'DBSOURCE', 'CATEGORY', 'PARAM_TYPE'],\n",
    "                          dtype={'ROW_ID': int, 'ITEMID': int, 'LABEL': str, 'DBSOURCE': 'category',\n",
    "                                 'CATEGORY': 'category', 'PARAM_TYPE': str})\n",
    "    \n",
    "    # ???\n",
    "    specimen_mbe = pd.merge(microbiologyevents, d_items[['ITEMID','LABEL','DBSOURCE','PARAM_TYPE','CATEGORY']], left_on='SPEC_ITEMID', right_on='ITEMID')\n",
    "    specimen_mbe = pd.merge(specimen_mbe, d_items[['ITEMID','LABEL','DBSOURCE','PARAM_TYPE']], left_on='ORG_ITEMID', right_on='ITEMID', suffixes=('','_org'))\n",
    "    specimen_mbe = pd.merge(specimen_mbe, d_items[['ITEMID','LABEL','DBSOURCE','PARAM_TYPE']], left_on='AB_ITEMID', right_on='ITEMID', suffixes=('','_ab'))\n",
    "\n",
    "    # Replace NaN in columns with empty strings so that concatenation in notes works\n",
    "    specimen_mbe['note'] = specimen_mbe['LABEL'].str.cat(specimen_mbe['DBSOURCE'], sep=' ', na_rep='NA')\n",
    "    specimen_mbe['note'] = specimen_mbe['note'].str.cat(specimen_mbe['PARAM_TYPE'], sep=' ', na_rep='')\n",
    "    specimen_mbe['note'] = specimen_mbe['note'].str.cat(specimen_mbe['LABEL_org'], sep=' ', na_rep='')\n",
    "    specimen_mbe['note'] = specimen_mbe['note'].str.cat(specimen_mbe['DBSOURCE_org'], sep=' ', na_rep='')\n",
    "    specimen_mbe['note'] = specimen_mbe['note'].str.cat(specimen_mbe['PARAM_TYPE_org'], sep=' ', na_rep='')\n",
    "    specimen_mbe['note'] = specimen_mbe['note'].str.cat(specimen_mbe['LABEL_ab'], sep=' ', na_rep='')\n",
    "    specimen_mbe['note'] = specimen_mbe['note'].str.cat(specimen_mbe['DBSOURCE_ab'], sep=' ', na_rep='')\n",
    "    specimen_mbe['note'] = specimen_mbe['note'].str.cat(specimen_mbe['PARAM_TYPE_ab'], sep=' ', na_rep='')\n",
    "\n",
    "    # Drop columns combined to note field\n",
    "    specimen_mbe.drop(columns=['ITEMID', 'LABEL', 'PARAM_TYPE','DBSOURCE',\n",
    "                               'ITEMID_org', 'LABEL_org', 'PARAM_TYPE_org','DBSOURCE_org',\n",
    "                               'ITEMID_ab', 'LABEL_ab', 'PARAM_TYPE_ab','DBSOURCE_ab'\n",
    "                              ], inplace=True)\n",
    "\n",
    "    specimen_mbe.rename(columns={'ROW_ID':'identifier',\n",
    "                                 'SUBJECT_ID':'subject',\n",
    "                                 'HADM_ID':'request_encounter_admission',\n",
    "                                 'CHARTTIME':'collection_dateTime',\n",
    "                                 'SPEC_ITEMID':'type_code',\n",
    "                                 'SPEC_TYPE_DESC':'type_name',\n",
    "                                 'CATEGORY':'type_category',\n",
    "                                 'ORG_ITEMID':'method_bact_code',\n",
    "                                 'ORG_NAME':'method_bact_name',\n",
    "                                 'ISOLATE_NUM':'method_colNum',\n",
    "                                 'AB_ITEMID':'method_antibiotic_code',\n",
    "                                 'AB_NAME':'method_antibiotic_name',\n",
    "                                 'DILUTION_TEXT':'method_dilution_description',\n",
    "                                 'DILUTION_COMPARISON':'method_dilution_comp',\n",
    "                                 'DILUTION_VALUE':'method_dilution_value',\n",
    "                                 'INTERPRETATION':'note_interpretation'}, inplace=True)\n",
    "\n",
    "    specimen_mbe.to_csv(output_path+'specimen_mbe.csv.gz', compression='gzip', index=False)\n",
    "    return specimen_mbe\n",
    "\n",
    "specimen_mbe = transform_microbiologyevents(data_path, output_path)\n",
    "specimen_mbe.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "rocky-angle",
   "metadata": {},
   "source": [
    "## fhir.serviceRequest table\n",
    "\n",
    "#### MAPPING:<br>\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.services.ROW_ID | fhir.serviceRequest.identifier|\n",
    "|2|mimic.services.SUBJECT_ID | fhir.serviceRequest.subject|\n",
    "|3|mimic.services.HADM_ID | fhir.serviceRequest.encounter|\n",
    "|4|mimic.services.TRANSFERTIME | fhir.serviceRequest.occuranceDateTime|\n",
    "|5|mimic.services.PREV_SERVICE | fhir.serviceRequest.replaces|\n",
    "|6|mimic.services.CURR_SERVICE | fhir.serviceRequest.code_name|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "seeing-support",
   "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>occuranceDateTime</th>\n",
       "      <th>replaces</th>\n",
       "      <th>code_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>758</td>\n",
       "      <td>471</td>\n",
       "      <td>135879</td>\n",
       "      <td>2122-07-22 14:07:27</td>\n",
       "      <td>TSURG</td>\n",
       "      <td>MED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>759</td>\n",
       "      <td>471</td>\n",
       "      <td>135879</td>\n",
       "      <td>2122-07-26 18:31:49</td>\n",
       "      <td>MED</td>\n",
       "      <td>TSURG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>760</td>\n",
       "      <td>472</td>\n",
       "      <td>173064</td>\n",
       "      <td>2172-09-28 19:22:15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>761</td>\n",
       "      <td>473</td>\n",
       "      <td>129194</td>\n",
       "      <td>2201-01-09 20:16:45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>762</td>\n",
       "      <td>474</td>\n",
       "      <td>194246</td>\n",
       "      <td>2181-03-23 08:24:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter   occuranceDateTime replaces code_name\n",
       "0         758      471     135879 2122-07-22 14:07:27    TSURG       MED\n",
       "1         759      471     135879 2122-07-26 18:31:49      MED     TSURG\n",
       "2         760      472     173064 2172-09-28 19:22:15      NaN      CMED\n",
       "3         761      473     129194 2201-01-09 20:16:45      NaN        NB\n",
       "4         762      474     194246 2181-03-23 08:24:41      NaN        NB"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_services(data_path, output_path):\n",
    "    services = pd.read_csv(data_path+'SERVICES'+file_ext, compression=compression,\n",
    "                           dtype = {'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': int,\n",
    "                                    'PREV_SERVICE': str, 'CURR_SERVICE': str},\n",
    "                           parse_dates=['TRANSFERTIME'],\n",
    "                           )\n",
    "    \n",
    "    services.rename(columns={'ROW_ID':'identifier',\n",
    "                             'SUBJECT_ID':'subject',\n",
    "                             'HADM_ID':'encounter',\n",
    "                             'TRANSFERTIME':'occuranceDateTime',\n",
    "                             'PREV_SERVICE':'replaces',\n",
    "                             'CURR_SERVICE':'code_name'}, inplace=True)\n",
    "    \n",
    "    services.to_csv(output_path+'services.csv.gz', compression='gzip', index=False)\n",
    "    return services\n",
    "\n",
    "serviceRequest = transform_services(data_path, output_path)\n",
    "serviceRequest.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "coastal-cover",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": 5
}