002-kba-mimicIII_ETL_FHIR.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "killing-reporter",
   "metadata": {},
   "source": [
    "# MIMIC-III tables to FHIR resource mapping in this notebook\n",
    "\n",
    "||Original format | FHIR resource| Progress|Final Check|\n",
    "|------|:-----|:-----|:-----|---:---|\n",
    "|6|inputevents_cv | medicationDispense| C,L,A|Done|\n",
    "|7|inputevents_mv | medicationDispense| C,L,A|Done|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "naughty-thickness",
   "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": "fifty-flavor",
   "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": null,
   "id": "functional-jonathan",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "smaller-reserve",
   "metadata": {},
   "source": [
    "## fhir.medicationDispense table\n",
    "\n",
    "#### INPUTEVENTS_CV MAPPING:\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.inputevents_cv.ROW_ID|fhir.medicationDispense.identifier|\n",
    "|2|mimic.inputevents_cv.SUBJECT_ID | fhir.medicationDispense.subject|\n",
    "|3|mimic.inputevents_cv.HADM_ID | fhir.medicationDispense.encounter|\n",
    "|4|mimic.inputevents_cv.ICUSTAY_ID | fhir.medicationDispense.partOf|\n",
    "|5|mimic.inputevents_cv.ITEMID | fhir.medicationDispense.medicationCodeableConcept|\n",
    "|6|mimic.inputevents_cv.CHARTTIME | fhir.medicationDispense.whenHandedOver|\n",
    "|7|mimic.inputevents_cv.AMOUNT | fhir.medicationDispense.valueQuantity|\n",
    "|8|mimic.inputevents_cv.AMOUNTUOM | fhir.medicationDispense.unit|\n",
    "|9|mimic.inputevents_cv.RATE | fhir.medicationDispense.dosageRate|\n",
    "|10|mimic.inputevents_cv.RATEUOM | fhir.medicationDispense.dosageRate_unit|\n",
    "|11|mimic.inputevents_cv.CGID | fhir.medicationDispense.performer|\n",
    "|12|mimic.inputevents_cv.ORDERID | fhir.medicationDispense.type|\n",
    "|13|mimic.inputevents_cv.LINKORDERID | fhir.medicationDispense.type_sub|\n",
    "|14|mimic.inputevents_cv.STOPPED | fhir.medicationDispense.status|\n",
    "|15|mimic.inputevents_cv.ORIGINALAMOUNT | fhir.medicationDispense.dosageOriginal_amount|\n",
    "|16|mimic.inputevents_cv.ORIGINALAMOUNTUOM|fhir.medicationDispense.dosageOriginal_amountUnit|\n",
    "|17|mimic.inputevents_cv.ORIGNALROUTE | fhir.medicationDispense.dosageOriginal_route|\n",
    "|18|mimic.inputevents_cv.ORIGINALRATE | fhir.medicationDispense.dosageOriginal_rate|\n",
    "|19|mimic.inputevents_cv.ORIGINALRATEUOM | fhir.medicationDispense.dosageOriginal_rateUnit|\n",
    "|20|mimic.inputevents_cv.ORIGINALSITE | fhir.medicationDispense.dosageOriginal_site|\n",
    "|21|mimic.inputevents_cv.NEWBOTTLE | fhir.medicationDispense.note|\n",
    "|22|mimic.d_items.(LABEL+DBSOURCE+PARAM_TYPE) | fhir.medicationDispense.note|\n",
    "|23|mimic.d_items.CATEGORY | fhir.medicationDispense.category|\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "otherwise-diesel",
   "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>whenHandedOver</th>\n",
       "      <th>medicationCodeableConcept</th>\n",
       "      <th>valueQuantity</th>\n",
       "      <th>unit</th>\n",
       "      <th>dosageRate</th>\n",
       "      <th>dosageRate_unit</th>\n",
       "      <th>...</th>\n",
       "      <th>type_sub</th>\n",
       "      <th>status</th>\n",
       "      <th>dosageOriginal_amount</th>\n",
       "      <th>dosageOriginal_amountUnit</th>\n",
       "      <th>dosageOriginal_route</th>\n",
       "      <th>dosageOriginal_rate</th>\n",
       "      <th>dosageOriginal_rateUnit</th>\n",
       "      <th>dosageOriginal_site</th>\n",
       "      <th>category</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>592</td>\n",
       "      <td>24457</td>\n",
       "      <td>184834.0</td>\n",
       "      <td>205776.0</td>\n",
       "      <td>2193-09-11 09:00:00</td>\n",
       "      <td>30056</td>\n",
       "      <td>100.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>9359133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ml</td>\n",
       "      <td>Oral</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Po Intake carevue  0.0 new bottle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>593</td>\n",
       "      <td>24457</td>\n",
       "      <td>184834.0</td>\n",
       "      <td>205776.0</td>\n",
       "      <td>2193-09-11 12:00:00</td>\n",
       "      <td>30056</td>\n",
       "      <td>200.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>9359133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ml</td>\n",
       "      <td>Oral</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Po Intake carevue  0.0 new bottle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>594</td>\n",
       "      <td>24457</td>\n",
       "      <td>184834.0</td>\n",
       "      <td>205776.0</td>\n",
       "      <td>2193-09-11 16:00:00</td>\n",
       "      <td>30056</td>\n",
       "      <td>160.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>9359133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ml</td>\n",
       "      <td>Oral</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Po Intake carevue  0.0 new bottle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>595</td>\n",
       "      <td>24457</td>\n",
       "      <td>184834.0</td>\n",
       "      <td>205776.0</td>\n",
       "      <td>2193-09-11 19:00:00</td>\n",
       "      <td>30056</td>\n",
       "      <td>240.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>9359133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ml</td>\n",
       "      <td>Oral</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Po Intake carevue  0.0 new bottle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>596</td>\n",
       "      <td>24457</td>\n",
       "      <td>184834.0</td>\n",
       "      <td>205776.0</td>\n",
       "      <td>2193-09-11 21:00:00</td>\n",
       "      <td>30056</td>\n",
       "      <td>50.0</td>\n",
       "      <td>ml</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>9359133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ml</td>\n",
       "      <td>Oral</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Po Intake carevue  0.0 new bottle</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter    partOf      whenHandedOver  \\\n",
       "0         592    24457   184834.0  205776.0 2193-09-11 09:00:00   \n",
       "1         593    24457   184834.0  205776.0 2193-09-11 12:00:00   \n",
       "2         594    24457   184834.0  205776.0 2193-09-11 16:00:00   \n",
       "3         595    24457   184834.0  205776.0 2193-09-11 19:00:00   \n",
       "4         596    24457   184834.0  205776.0 2193-09-11 21:00:00   \n",
       "\n",
       "   medicationCodeableConcept  valueQuantity unit  dosageRate dosageRate_unit  \\\n",
       "0                      30056          100.0   ml         NaN             NaN   \n",
       "1                      30056          200.0   ml         NaN             NaN   \n",
       "2                      30056          160.0   ml         NaN             NaN   \n",
       "3                      30056          240.0   ml         NaN             NaN   \n",
       "4                      30056           50.0   ml         NaN             NaN   \n",
       "\n",
       "   ...  type_sub  status  dosageOriginal_amount dosageOriginal_amountUnit  \\\n",
       "0  ...   9359133     NaN                    NaN                        ml   \n",
       "1  ...   9359133     NaN                    NaN                        ml   \n",
       "2  ...   9359133     NaN                    NaN                        ml   \n",
       "3  ...   9359133     NaN                    NaN                        ml   \n",
       "4  ...   9359133     NaN                    NaN                        ml   \n",
       "\n",
       "   dosageOriginal_route dosageOriginal_rate dosageOriginal_rateUnit  \\\n",
       "0                  Oral                 NaN                     NaN   \n",
       "1                  Oral                 NaN                     NaN   \n",
       "2                  Oral                 NaN                     NaN   \n",
       "3                  Oral                 NaN                     NaN   \n",
       "4                  Oral                 NaN                     NaN   \n",
       "\n",
       "   dosageOriginal_site category                               note  \n",
       "0                  NaN      NaN  Po Intake carevue  0.0 new bottle  \n",
       "1                  NaN      NaN  Po Intake carevue  0.0 new bottle  \n",
       "2                  NaN      NaN  Po Intake carevue  0.0 new bottle  \n",
       "3                  NaN      NaN  Po Intake carevue  0.0 new bottle  \n",
       "4                  NaN      NaN  Po Intake carevue  0.0 new bottle  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_inputevents_cv(data_path, output_path):\n",
    "    inputevents_cv = pd.read_csv(data_path+'INPUTEVENTS_CV'+file_ext, compression=compression,\n",
    "                                 # dropped 'STORETIME'\n",
    "                                 usecols=['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID', 'CHARTTIME',\n",
    "                                          'ITEMID', 'AMOUNT', 'AMOUNTUOM', 'RATE', 'RATEUOM',\n",
    "                                          'CGID', 'ORDERID', 'LINKORDERID', 'STOPPED', 'NEWBOTTLE', 'ORIGINALAMOUNT',\n",
    "                                          'ORIGINALAMOUNTUOM', 'ORIGINALROUTE', 'ORIGINALRATE', 'ORIGINALRATEUOM',\n",
    "                                          'ORIGINALSITE'],\n",
    "                                 dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': np.float32, 'ICUSTAY_ID': np.float32,\n",
    "                                        'ITEMID': int, 'AMOUNT': np.float16, 'AMOUNTUOM': 'category', 'RATE': np.float64, \n",
    "                                        'RATEUOM': 'category', 'CGID': float, 'ORDERID': int, 'LINKORDERID': int,\n",
    "                                        'STOPPED': 'category', 'NEWBOTTLE': np.float16, 'ORIGINALAMOUNT': np.float64,\n",
    "                                        'ORIGINALAMOUNTUOM': 'category', 'ORIGINALROUTE': 'category', 'ORIGINALRATE': float,\n",
    "                                        'ORIGINALRATEUOM': 'category', 'ORIGINALSITE': 'category'},\n",
    "                                 parse_dates=['CHARTTIME'])\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",
    "    inputevents_cv['NEWBOTTLE'].replace(np.NaN, 0, inplace=True)\n",
    "    medicationDispense = pd.merge(inputevents_cv, d_items, on='ITEMID')\n",
    "#     medicationDispense.CHARTTIME = pd.to_datetime(medicationDispense.CHARTTIME, format = '%Y-%m-%d %H:%M:%S', errors = 'coerce')\n",
    "# \n",
    "#     medicationDispense['NEWBOTTLE'].replace(np.NaN, 0, inplace=True)\n",
    "#     medicationDispense['PARAM_TYPE'].replace(np.NaN, '', regex=True, inplace=True)\n",
    "#     medicationDispense['note'] = medicationDispense['LABEL'] + ' ' + medicationDispense['DBSOURCE'] + ' ' + medicationDispense['PARAM_TYPE'] + ' ' + medicationDispense['NEWBOTTLE'].astype(str) +' new bottle'\n",
    "\n",
    "    # x2.5 fater and more readable\n",
    "    medicationDispense['note'] = medicationDispense['LABEL'].str.cat(medicationDispense['DBSOURCE'], sep=' ', na_rep='NA')\n",
    "    medicationDispense['note'] = medicationDispense['note'].str.cat(medicationDispense['PARAM_TYPE'], sep=' ', na_rep='')\n",
    "    medicationDispense['note'] = medicationDispense['note'].str.cat(medicationDispense['NEWBOTTLE'].astype(str), sep=' ')\n",
    "    medicationDispense['note'] = medicationDispense['note'] + ' new bottle'\n",
    "    \n",
    "    \n",
    "    medicationDispense.rename(columns={'ROW_ID':'identifier',\n",
    "                                       'SUBJECT_ID':'subject',\n",
    "                                       'HADM_ID':'encounter',\n",
    "                                       'ICUSTAY_ID':'partOf',\n",
    "                                       'CHARTTIME':'whenHandedOver',\n",
    "                                       'ITEMID':'medicationCodeableConcept',\n",
    "                                       'AMOUNT':'valueQuantity',\n",
    "                                       'AMOUNTUOM':'unit',\n",
    "                                       'RATE':'dosageRate',\n",
    "                                       'RATEUOM':'dosageRate_unit',\n",
    "                                       'CGID':'performer',\n",
    "                                       'ORDERID':'type',\n",
    "                                       'LINKORDERID':'type_sub',\n",
    "                                       'STOPPED':'status',\n",
    "                                       'ORIGINALAMOUNT':'dosageOriginal_amount',\n",
    "                                       'ORIGINALAMOUNTUOM':'dosageOriginal_amountUnit',\n",
    "                                       'ORIGINALROUTE':'dosageOriginal_route',\n",
    "                                       'ORIGINALRATE':'dosageOriginal_rate',\n",
    "                                       'ORIGINALRATEUOM':'dosageOriginal_rateUnit',\n",
    "                                       'ORIGINALSITE':'dosageOriginal_site',\n",
    "                                       'CATEGORY':'category'}, inplace=True)\n",
    "\n",
    "    # 'STORETIME', 'ABBREVIATION', 'LINKSTO', 'CONCEPTID', 'UNITNAME'\n",
    "    medicationDispense.drop(['LABEL', 'PARAM_TYPE', 'DBSOURCE','NEWBOTTLE'], axis=1, inplace=True)\n",
    "#     medicationDispense.to_csv(output_path + 'medicationDispense.csv.gz', compression='gzip', index=False)\n",
    "\n",
    "    return medicationDispense\n",
    "\n",
    "medicationDispense_cv = transform_inputevents_cv(data_path, output_path)\n",
    "medicationDispense_cv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "located-white",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import sys\n",
    "\n",
    "# sys.getsizeof(inputevents_cv) / 1024/ 1024/ 1024"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "conditional-union",
   "metadata": {},
   "outputs": [],
   "source": [
    "medicationDispense_cv.to_csv(output_path+'medicationDispense.csv.gz',\n",
    "                             compression={'method': 'gzip', 'compresslevel': 1}, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "built-memphis",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del medicationDispense_cv\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ruled-emperor",
   "metadata": {},
   "source": [
    "#### INPUTEVENTS_MV MAPPING:\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.inputevents_mv.ROW_ID|fhir.medicationDispense.identifier|\n",
    "|2|mimic.inputevents_mv.SUBJECT_ID|fhir.medicationDispense.subject|\n",
    "|3|mimic.inputevents_mv.HADM_ID|fhir.medicationDispense.encounter|\n",
    "|4|mimic.inputevents_mv.ICUSTAY_ID|fhir.medicationDispense.partOf|\n",
    "|5|mimic.inputevents_mv.ITEMID|fhir.medicationDispense.medicationCodeableConcept|\n",
    "|6|mimic.inputevents_mv.STARTTIME|fhir.medicationDispense.whenHandedOver_start|\n",
    "|7|mimic.inputevents_mv.ENDTIME|fhir.medicationDispense.whenHandedOver_end|\n",
    "|8|mimic.inputevents_mv.AMOUNT|fhir.medicationDispense.valueQuantity|\n",
    "|9|mimic.inputevents_mv.AMOUNTUOM|fhir.medicationDispense.unit|\n",
    "|10|mimic.inputevents_mv.RATE|fhir.medicationDispense.dosageRate|\n",
    "|11|mimic.inputevents_mv.RATEUOM|fhir.medicationDispense.dosageRate_unit|\n",
    "|12|mimic.inputevents_mv.CGID|fhir.medicationDispense.performer|\n",
    "|13|mimic.inputevents_mv.ORDERID|fhir.medicationDispense.type|\n",
    "|14|mimic.inputevents_mv.LINKORDERID|fhir.medicationDispense.type_sub|\n",
    "|15|mimic.inputevents_mv.ORDERCATEGORYNAME|fhir.medicationDispense.supportingInformation_order_catName|\n",
    "|16|mimic.inputevents_mv.SECONDARYORDERCATEGORYNAME|fhir.medicationDispense.supportingInformation_order_secCatName|\n",
    "|17|mimic.inputevents_mv.ORDERCOMPONENTTYPEDESCRIPTION|fhir.medicationDispense.supportingInformation_order_desc_componentTyped|\n",
    "|18|mimic.inputevents_mv.ORDERCATEGORYDESCRIPTION|fhir.medicationDispense.supportingInformation_order_desc_cat|\n",
    "|19|mimic.inputevents_mv.PATIENTWEIGHT|fhir.medicationDispense.supportingInformation_patientWeight|\n",
    "|20|mimic.inputevents_mv.ORIGINALAMOUNT|fhir.medicationDispense.dosageInstruction_original_amount|\n",
    "|21|mimic.inputevents_mv.ORIGINALRATE|fhir.medicationDispense.dosageInstruction_original_rate|\n",
    "|22|mimic.inputevents_mv.TOTALAMOUNT|fhir.medicationDispense.dosageInstruction_total_amount|\n",
    "|23|mimic.inputevents_mv.TOTALAMOUNTUOM|fhir.medicationDispense.dosageInstruction_total_unit|\n",
    "|24|mimic.inputevents_mv.ISOPENBAG|fhir.medicationDispense.dosageInstruction_openBag|\n",
    "|25|mimic.inputevents_mv.CONTINUEINNEXTDEPT|fhir.medicationDispense.eventHistory_contExtDep|\n",
    "|26|mimic.inputevents_mv.CANCELREASON|fhir.medicationDispense.detectedIssue_code|\n",
    "|27|mimic.inputevents_mv.STATUSDESCRIPTION|fhir.medicationDispense.status|\n",
    "|28|mimic.inputevents_mv.COMMENTS_EDITEDBY|fhir.medicationDispense.performer_comment_edit|\n",
    "|29|mimic.inputevents_mv.COMMENTS_CANCELEDBY|fhir.medicationDispense.performer_comment_cancel|\n",
    "|30|mimic.inputevents_mv.COMMENTS_DATE|fhir.medicationDispense.detectedIssue_date|\n",
    "|31|mimic.d_items.(LABEL+DBSOURCE+PARAM_TYPE)|fhir.medicationDispense.note|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "defined-month",
   "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>whenHandedOver_start</th>\n",
       "      <th>whenHandedOver_end</th>\n",
       "      <th>medicationCodeableConcept</th>\n",
       "      <th>valueQuantity</th>\n",
       "      <th>unit</th>\n",
       "      <th>dosageRate</th>\n",
       "      <th>...</th>\n",
       "      <th>dosageInstruction_total_unit</th>\n",
       "      <th>dosageInstruction_openBag</th>\n",
       "      <th>eventHistory_contExtDep</th>\n",
       "      <th>detectedIssue_code</th>\n",
       "      <th>status</th>\n",
       "      <th>detectedIssue_date</th>\n",
       "      <th>dosageInstruction_original_amount</th>\n",
       "      <th>dosageInstruction_original_rate</th>\n",
       "      <th>category</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>241</td>\n",
       "      <td>27063</td>\n",
       "      <td>139787</td>\n",
       "      <td>223259.0</td>\n",
       "      <td>2133-02-05 06:29:00</td>\n",
       "      <td>2133-02-05 08:45:00</td>\n",
       "      <td>225166</td>\n",
       "      <td>6.774532</td>\n",
       "      <td>mEq</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>ml</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Rewritten</td>\n",
       "      <td>2133-02-05 12:52:00</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>Medications</td>\n",
       "      <td>Potassium Chloride metavision Solution</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>243</td>\n",
       "      <td>27063</td>\n",
       "      <td>139787</td>\n",
       "      <td>223259.0</td>\n",
       "      <td>2133-02-05 05:34:00</td>\n",
       "      <td>2133-02-05 06:30:00</td>\n",
       "      <td>225166</td>\n",
       "      <td>2.813300</td>\n",
       "      <td>mEq</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>ml</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>FinishedRunning</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2.813300</td>\n",
       "      <td>0.050426</td>\n",
       "      <td>Medications</td>\n",
       "      <td>Potassium Chloride metavision Solution</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>247</td>\n",
       "      <td>27063</td>\n",
       "      <td>139787</td>\n",
       "      <td>223259.0</td>\n",
       "      <td>2133-02-05 05:34:00</td>\n",
       "      <td>2133-02-05 07:03:00</td>\n",
       "      <td>225166</td>\n",
       "      <td>4.433333</td>\n",
       "      <td>mEq</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>ml</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Rewritten</td>\n",
       "      <td>NaT</td>\n",
       "      <td>4.433333</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>Medications</td>\n",
       "      <td>Potassium Chloride metavision Solution</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>249</td>\n",
       "      <td>27063</td>\n",
       "      <td>139787</td>\n",
       "      <td>223259.0</td>\n",
       "      <td>2133-02-05 09:43:00</td>\n",
       "      <td>2133-02-05 12:30:00</td>\n",
       "      <td>225166</td>\n",
       "      <td>8.360021</td>\n",
       "      <td>mEq</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>ml</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Changed</td>\n",
       "      <td>NaT</td>\n",
       "      <td>10.000001</td>\n",
       "      <td>0.050248</td>\n",
       "      <td>Medications</td>\n",
       "      <td>Potassium Chloride metavision Solution</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>251</td>\n",
       "      <td>27063</td>\n",
       "      <td>139787</td>\n",
       "      <td>223259.0</td>\n",
       "      <td>2133-02-05 05:34:00</td>\n",
       "      <td>2133-02-05 05:35:00</td>\n",
       "      <td>225166</td>\n",
       "      <td>0.050060</td>\n",
       "      <td>mEq</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>ml</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Rewritten</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2.753300</td>\n",
       "      <td>0.050248</td>\n",
       "      <td>Medications</td>\n",
       "      <td>Potassium Chloride metavision Solution</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter    partOf whenHandedOver_start  \\\n",
       "0         241    27063     139787  223259.0  2133-02-05 06:29:00   \n",
       "1         243    27063     139787  223259.0  2133-02-05 05:34:00   \n",
       "2         247    27063     139787  223259.0  2133-02-05 05:34:00   \n",
       "3         249    27063     139787  223259.0  2133-02-05 09:43:00   \n",
       "4         251    27063     139787  223259.0  2133-02-05 05:34:00   \n",
       "\n",
       "   whenHandedOver_end  medicationCodeableConcept  valueQuantity unit  \\\n",
       "0 2133-02-05 08:45:00                     225166       6.774532  mEq   \n",
       "1 2133-02-05 06:30:00                     225166       2.813300  mEq   \n",
       "2 2133-02-05 07:03:00                     225166       4.433333  mEq   \n",
       "3 2133-02-05 12:30:00                     225166       8.360021  mEq   \n",
       "4 2133-02-05 05:35:00                     225166       0.050060  mEq   \n",
       "\n",
       "   dosageRate  ... dosageInstruction_total_unit  dosageInstruction_openBag  \\\n",
       "0         NaN  ...                           ml                          0   \n",
       "1         NaN  ...                           ml                          0   \n",
       "2         NaN  ...                           ml                          0   \n",
       "3         NaN  ...                           ml                          0   \n",
       "4         NaN  ...                           ml                          0   \n",
       "\n",
       "   eventHistory_contExtDep  detectedIssue_code           status  \\\n",
       "0                        0                   1        Rewritten   \n",
       "1                        0                   0  FinishedRunning   \n",
       "2                        0                   0        Rewritten   \n",
       "3                        0                   0          Changed   \n",
       "4                        0                   0        Rewritten   \n",
       "\n",
       "   detectedIssue_date dosageInstruction_original_amount  \\\n",
       "0 2133-02-05 12:52:00                         10.000000   \n",
       "1                 NaT                          2.813300   \n",
       "2                 NaT                          4.433333   \n",
       "3                 NaT                         10.000001   \n",
       "4                 NaT                          2.753300   \n",
       "\n",
       "  dosageInstruction_original_rate     category  \\\n",
       "0                        0.050000  Medications   \n",
       "1                        0.050426  Medications   \n",
       "2                        0.050000  Medications   \n",
       "3                        0.050248  Medications   \n",
       "4                        0.050248  Medications   \n",
       "\n",
       "                                     note  \n",
       "0  Potassium Chloride metavision Solution  \n",
       "1  Potassium Chloride metavision Solution  \n",
       "2  Potassium Chloride metavision Solution  \n",
       "3  Potassium Chloride metavision Solution  \n",
       "4  Potassium Chloride metavision Solution  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_inputevents_mv(data_path, output_path):\n",
    "    inputevents_mv = pd.read_csv(data_path+'INPUTEVENTS_MV'+file_ext, compression=compression,\n",
    "                                 # STORETIME, 'COMMENTS_STATUS', 'COMMENTS_TITLE'\n",
    "                                 usecols=['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID', 'STARTTIME', 'ENDTIME',\n",
    "                                          'ITEMID', 'AMOUNT', 'AMOUNTUOM', 'RATE', 'RATEUOM', 'CGID',\n",
    "                                          'ORDERID', 'LINKORDERID', 'ORDERCATEGORYNAME', 'SECONDARYORDERCATEGORYNAME',\n",
    "                                          'ORDERCOMPONENTTYPEDESCRIPTION', 'ORDERCATEGORYDESCRIPTION', 'PATIENTWEIGHT',\n",
    "                                          'TOTALAMOUNT', 'TOTALAMOUNTUOM', 'ISOPENBAG', 'CONTINUEINNEXTDEPT',\n",
    "                                          'CANCELREASON', 'STATUSDESCRIPTION',\n",
    "                                          'COMMENTS_DATE', 'ORIGINALAMOUNT', 'ORIGINALRATE'],\n",
    "                                 dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': int, 'ICUSTAY_ID': float,\n",
    "                                        'ITEMID': int, 'AMOUNT': np.float64, 'AMOUNTUOM': str, 'RATE': np.float64,\n",
    "                                        'RATEUOM': str, 'CGID': np.float64, 'ORDERID': np.float64,\n",
    "                                        'LINKORDERID': np.float64, 'ORDERCATEGORYNAME': str,\n",
    "                                        'SECONDARYORDERCATEGORYNAME': str, 'ORDERCOMPONENTTYPEDESCRIPTION': str,\n",
    "                                        'ORDERCATEGORYDESCRIPTION': str, 'PATIENTWEIGHT': np.float64,\n",
    "                                        'TOTALAMOUNT': np.float64, 'TOTALAMOUNTUOM': str, 'ISOPENBAG': int,\n",
    "                                        'CONTINUEINNEXTDEPT': int, 'CANCELREASON': int, 'STATUSDESCRIPTION': str,\n",
    "                                        'ORIGINALAMOUNT': np.float64, 'ORIGINALRATE': np.float64},\n",
    "                                 parse_dates=['STARTTIME', 'ENDTIME', '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",
    "    medicationDispense = pd.merge(inputevents_mv, d_items, on='ITEMID')\n",
    "    \n",
    "#     medicationDispense.STARTTIME = pd.to_datetime(medicationDispense.STARTTIME, format = '%Y-%m-%d %H:%M:%S', errors = 'coerce')\n",
    "#     medicationDispense.ENDTIME = pd.to_datetime(medicationDispense.ENDTIME, format = '%Y-%m-%d %H:%M:%S', errors = 'coerce')\n",
    "#     medicationDispense.COMMENTS_DATE = pd.to_datetime(medicationDispense.COMMENTS_DATE, format = '%Y-%m-%d', errors = 'coerce')\n",
    "\n",
    "#     medicationDispense['PARAM_TYPE'].replace(np.NaN, '', regex=True, inplace=True)\n",
    "#     medicationDispense['note'] = medicationDispense['LABEL'] + ' ' + medicationDispense['DBSOURCE'] + ' ' + medicationDispense['PARAM_TYPE']\n",
    "    \n",
    "    medicationDispense['note'] = medicationDispense['LABEL'].str.cat(medicationDispense['DBSOURCE'], sep=' ', na_rep='NA')\n",
    "    medicationDispense['note'] = medicationDispense['note'].str.cat(medicationDispense['PARAM_TYPE'], sep=' ', na_rep='')\n",
    "    \n",
    "    medicationDispense.drop(['LABEL', 'PARAM_TYPE', 'DBSOURCE'], axis=1, inplace=True)\n",
    "\n",
    "    medicationDispense.rename(columns={'ROW_ID':'identifier',\n",
    "                                       'SUBJECT_ID':'subject',\n",
    "                                       'HADM_ID':'encounter',\n",
    "                                       'ICUSTAY_ID':'partOf',\n",
    "                                       'STARTTIME':'whenHandedOver_start',\n",
    "                                       'ENDTIME':'whenHandedOver_end',\n",
    "                                       'ITEMID':'medicationCodeableConcept',\n",
    "                                       'AMOUNT':'valueQuantity',\n",
    "                                       'AMOUNTUOM':'unit',\n",
    "                                       'RATE':'dosageRate',\n",
    "                                       'RATEUOM':'dosageRate_unit',\n",
    "                                       'CGID':'performer',\n",
    "                                       'ORDERID':'type',\n",
    "                                       'LINKORDERID':'type_sub',\n",
    "                                       'ORDERCATEGORYNAME':'supportingInformation_order_catName',\n",
    "                                       'SECONDARYORDERCATEGORYNAME':'supportingInformation_order_secCatName', \n",
    "                                       'ORDERCOMPONENTTYPEDESCRIPTION':'supportingInformation_order_desc_componentTyped',\n",
    "                                       'ORDERCATEGORYDESCRIPTION':'supportingInformation_order_desc_cat', \n",
    "                                       'PATIENTWEIGHT':'supportingInformation_patientWeight', \n",
    "                                       'TOTALAMOUNT':'dosageInstruction_total_amount',\n",
    "                                       'TOTALAMOUNTUOM':'dosageInstruction_total_unit', \n",
    "                                       'ISOPENBAG':'dosageInstruction_openBag', \n",
    "                                       'CONTINUEINNEXTDEPT':'eventHistory_contExtDep', \n",
    "                                       'CANCELREASON':'detectedIssue_code',\n",
    "                                       'STATUSDESCRIPTION':'status', \n",
    "# We do not have such keys and FHIR does not have such resources\n",
    "#                                        'COMMENTS_EDITEDBY':'performer_comment_edit', \n",
    "#                                        'COMMENTS_CANCELEDBY':'performer_comment_cancel',\n",
    "                                       'COMMENTS_DATE':'detectedIssue_date',\n",
    "                                       'ORIGINALAMOUNT':'dosageInstruction_original_amount',\n",
    "                                       'ORIGINALRATE':'dosageInstruction_original_rate',\n",
    "                                       'CATEGORY':'category'}, inplace=True)\n",
    "    \n",
    "#     medicationDispense.to_csv(output_path + 'medicationDispense_mv.csv.gz', compression='gzip', index=False)\n",
    "    return medicationDispense\n",
    "\n",
    "medicationDispense_mv = transform_inputevents_mv(data_path, output_path)\n",
    "medicationDispense_mv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "corporate-insurance",
   "metadata": {},
   "outputs": [],
   "source": [
    "medicationDispense_mv.to_csv(output_path+'medicationDispense_mv.csv.gz',\n",
    "                             compression={'method': 'gzip', 'compresslevel': 1}, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "confirmed-belly",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del medicationDispense_mv\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caring-walter",
   "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
}