003-kba-mimicIII_ETL_FHIR.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "numerous-terminal",
   "metadata": {},
   "source": [
    "# MIMIC-III tables to FHIR resource mapping in this notebook\n",
    "\n",
    "||Original format | FHIR resource| Progress|Final Check|\n",
    "|------|:-----|:-----|:-----|---:---|\n",
    "|8|prescriptions | medicationRequest| C,L,A|Done|\n",
    "|9|chartevents | observation| C,L,A|Done|\n",
    "|10|datetimeevents | observation| C,L,A|Done|\n",
    "|11|labevents | observation| C,L,A|Done|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "empty-minutes",
   "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": "surrounded-difficulty",
   "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": "confidential-figure",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "extreme-bridges",
   "metadata": {},
   "source": [
    "## fhir.medicationRequest table\n",
    "\n",
    "#### MAPPING: <br>\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "|1|mimic.prescriptions.ROW_ID | fhir.medicationRequest.identifier|\n",
    "|2|mimic.prescriptions.SUBJECT_ID | fhir.medicationRequest.subject|\n",
    "|3|mimic.prescriptions.HADM_ID| fhir.medicationRequest.encounter|\n",
    "|4|mimic.prescriptions.ICUSTAY_ID | fhir.medicationRequest.partOf|\n",
    "|5|mimic.prescriptions.STARTDATE | fhir.medicationRequest.dispenseRequest_start|\n",
    "|6|mimic.prescriptions.ENDDATE | fhir.medicationRequest.dispenseRequest_end|\n",
    "|7|mimic.prescriptions.DRUG_TYPE | fhir.medicationRequest.category|\n",
    "|8|mimic.prescriptions.DRUG | fhir.medicationRequest.medication_name|\n",
    "|9|mimic.prescriptions.DRUG_NAME_GENERIC | fhir.medicationRequest.medication_genericName|\n",
    "|10|mimic.prescriptions.FORMULARY_DRUG_CD | fhir.medicationRequest.medication_code_CD|\n",
    "|11|mimic.prescriptions.GSN | fhir.medicationRequest.medication_code_GSN|\n",
    "|12|mimic.prescriptions.NDC | fhir.medicationRequest.medication_code_NDC|\n",
    "|13|mimic.prescriptions.DOSE_VAL_RX | fhir.medicationRequest.dosageInstruction_value|\n",
    "|14|mimic.prescriptions.DOSE_UNIT_RX | fhir.medicationRequest.dosageInstruction_unit|\n",
    "|15|mimic.prescriptions.FORM_VAL_DISP | fhir.medicationRequest.dispenseRequest_value|\n",
    "|16|mimic.prescriptions.FORM_UNIT_DISP | fhir.medicationRequest.dispenseRequest_unit|\n",
    "|17|mimic.prescriptions.ROUTE | fhir.medicationRequest.courseOfTherapyType|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "figured-remark",
   "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>dispenseRequest_start</th>\n",
       "      <th>dispenseRequest_end</th>\n",
       "      <th>category</th>\n",
       "      <th>medication_name</th>\n",
       "      <th>medication_genericName</th>\n",
       "      <th>medication_code_CD</th>\n",
       "      <th>medication_code_GSN</th>\n",
       "      <th>medication_code_NDC</th>\n",
       "      <th>dosageInstruction_value</th>\n",
       "      <th>dosageInstruction_unit</th>\n",
       "      <th>dispenseRequest_value</th>\n",
       "      <th>dispenseRequest_unit</th>\n",
       "      <th>courseOfTherapyType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2214776</td>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2175-06-11</td>\n",
       "      <td>2175-06-12</td>\n",
       "      <td>MAIN</td>\n",
       "      <td>Tacrolimus</td>\n",
       "      <td>Tacrolimus</td>\n",
       "      <td>TACR1</td>\n",
       "      <td>021796</td>\n",
       "      <td>00469061711</td>\n",
       "      <td>2</td>\n",
       "      <td>mg</td>\n",
       "      <td>2</td>\n",
       "      <td>CAP</td>\n",
       "      <td>PO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2214775</td>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2175-06-11</td>\n",
       "      <td>2175-06-12</td>\n",
       "      <td>MAIN</td>\n",
       "      <td>Warfarin</td>\n",
       "      <td>Warfarin</td>\n",
       "      <td>WARF5</td>\n",
       "      <td>006562</td>\n",
       "      <td>00056017275</td>\n",
       "      <td>5</td>\n",
       "      <td>mg</td>\n",
       "      <td>1</td>\n",
       "      <td>TAB</td>\n",
       "      <td>PO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2215524</td>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2175-06-11</td>\n",
       "      <td>2175-06-12</td>\n",
       "      <td>MAIN</td>\n",
       "      <td>Heparin Sodium</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEPAPREMIX</td>\n",
       "      <td>006522</td>\n",
       "      <td>00338055002</td>\n",
       "      <td>25,000</td>\n",
       "      <td>UNIT</td>\n",
       "      <td>1</td>\n",
       "      <td>BAG</td>\n",
       "      <td>IV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2216265</td>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2175-06-11</td>\n",
       "      <td>2175-06-12</td>\n",
       "      <td>BASE</td>\n",
       "      <td>D5W</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEPBASE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>250</td>\n",
       "      <td>ml</td>\n",
       "      <td>250</td>\n",
       "      <td>ml</td>\n",
       "      <td>IV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2214773</td>\n",
       "      <td>6</td>\n",
       "      <td>107064</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2175-06-11</td>\n",
       "      <td>2175-06-12</td>\n",
       "      <td>MAIN</td>\n",
       "      <td>Furosemide</td>\n",
       "      <td>Furosemide</td>\n",
       "      <td>FURO20</td>\n",
       "      <td>008208</td>\n",
       "      <td>00054829725</td>\n",
       "      <td>20</td>\n",
       "      <td>mg</td>\n",
       "      <td>1</td>\n",
       "      <td>TAB</td>\n",
       "      <td>PO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter  partOf dispenseRequest_start  \\\n",
       "0     2214776        6     107064     NaN            2175-06-11   \n",
       "1     2214775        6     107064     NaN            2175-06-11   \n",
       "2     2215524        6     107064     NaN            2175-06-11   \n",
       "3     2216265        6     107064     NaN            2175-06-11   \n",
       "4     2214773        6     107064     NaN            2175-06-11   \n",
       "\n",
       "  dispenseRequest_end category medication_name medication_genericName  \\\n",
       "0          2175-06-12     MAIN      Tacrolimus             Tacrolimus   \n",
       "1          2175-06-12     MAIN        Warfarin               Warfarin   \n",
       "2          2175-06-12     MAIN  Heparin Sodium                    NaN   \n",
       "3          2175-06-12     BASE             D5W                    NaN   \n",
       "4          2175-06-12     MAIN      Furosemide             Furosemide   \n",
       "\n",
       "  medication_code_CD medication_code_GSN medication_code_NDC  \\\n",
       "0              TACR1              021796         00469061711   \n",
       "1              WARF5              006562         00056017275   \n",
       "2         HEPAPREMIX              006522         00338055002   \n",
       "3            HEPBASE                 NaN                   0   \n",
       "4             FURO20              008208         00054829725   \n",
       "\n",
       "  dosageInstruction_value dosageInstruction_unit dispenseRequest_value  \\\n",
       "0                       2                     mg                     2   \n",
       "1                       5                     mg                     1   \n",
       "2                  25,000                   UNIT                     1   \n",
       "3                     250                     ml                   250   \n",
       "4                      20                     mg                     1   \n",
       "\n",
       "  dispenseRequest_unit courseOfTherapyType  \n",
       "0                  CAP                  PO  \n",
       "1                  TAB                  PO  \n",
       "2                  BAG                  IV  \n",
       "3                   ml                  IV  \n",
       "4                  TAB                  PO  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_prescriptions(data_path, output_path):\n",
    "    prescriptions = pd.read_csv(data_path+'PRESCRIPTIONS'+file_ext, compression=compression,\n",
    "                                usecols=lambda x: x not in ['DRUG_NAME_POE', 'PROD_STRENGTH'],\n",
    "                                dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': int, 'ICUSTAY_ID': float,\n",
    "                                       'DRUG_TYPE': str, 'DRUG': str, 'DRUG_NAME_GENERIC': str,\n",
    "                                       'FORMULARY_DRUG_CD': str, 'GSN': str, 'NDC': str,\n",
    "                                       'DOSE_VAL_RX': str, 'DOSE_UNIT_RX': str, 'FORM_VAL_DISP': str,\n",
    "                                       'FORM_UNIT_DISP': str, 'ROUTE': str},\n",
    "                                parse_dates=['STARTDATE', 'ENDDATE'])\n",
    "\n",
    "#     prescriptions.STARTDATE = pd.to_datetime(prescriptions.STARTDATE, format = '%Y-%m-%d', errors = 'coerce')\n",
    "#     prescriptions.ENDDATE = pd.to_datetime(prescriptions.ENDDATE, format = '%Y-%m-%d', errors = 'coerce')\n",
    "#     \n",
    "#     Drop extra columns\n",
    "#     prescriptions.drop(['DRUG_NAME_POE', 'PROD_STRENGTH'], axis=1, inplace=True)\n",
    "\n",
    "    prescriptions.rename(columns={'ROW_ID':'identifier',\n",
    "                                  'SUBJECT_ID':'subject', \n",
    "                                  'HADM_ID':'encounter', \n",
    "                                  'ICUSTAY_ID':'partOf',\n",
    "                                  'STARTDATE':'dispenseRequest_start', \n",
    "                                  'ENDDATE':'dispenseRequest_end',\n",
    "                                  'DRUG_TYPE':'category', \n",
    "                                  'DRUG':'medication_name', \n",
    "                                  'DRUG_NAME_GENERIC':'medication_genericName',\n",
    "                                  'FORMULARY_DRUG_CD':'medication_code_CD', \n",
    "                                  'GSN':'medication_code_GSN', \n",
    "                                  'NDC':'medication_code_NDC', \n",
    "                                  'DOSE_VAL_RX':'dosageInstruction_value',\n",
    "                                  'DOSE_UNIT_RX':'dosageInstruction_unit', \n",
    "                                  'FORM_VAL_DISP':'dispenseRequest_value', \n",
    "                                  'FORM_UNIT_DISP':'dispenseRequest_unit', \n",
    "                                  'ROUTE':'courseOfTherapyType'}, inplace=True)\n",
    "\n",
    "#     prescriptions.to_csv(output_path+'medicationRequest.csv.gz', compression='gzip', index=False)\n",
    "    return prescriptions\n",
    "\n",
    "medicationRequest = transform_prescriptions(data_path, output_path)\n",
    "medicationRequest.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "caring-arthritis",
   "metadata": {},
   "outputs": [],
   "source": [
    "medicationRequest.to_csv(output_path+'medicationRequest.csv.gz',\n",
    "                         compression={'method': 'gzip', 'compresslevel': 1}, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "existing-daniel",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del medicationRequest\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "presidential-princeton",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "stylish-noise",
   "metadata": {},
   "source": [
    "# fhir.observation table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "potential-inside",
   "metadata": {},
   "source": [
    "## CHARTEVENTS MAPPING:<br>\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "||mimic.chartevents.ROW_ID| fhir.observation.identifier|\n",
    "||mimic.chartevents.SUBJECT_ID | fhir.observation.subject|\n",
    "||mimic.chartevents.HADM_ID | fhir.observation.encounter|\n",
    "||mimic.chartevents.ICUSTAY_ID | fhir.observation.partOf|\n",
    "||mimic.chartevents.ITEMID | fhir.observation.code|\n",
    "||mimic.chartevents.CHARTTIME | fhir.observation.effectiveDateTime|\n",
    "||mimic.chartevents.CGID | fhir.observation.performer|\n",
    "||mimic.chartevents.VALUE | fhir.observation.value|\n",
    "||mimic.chartevents.VALUENUM | fhir.observation.value_quantity|\n",
    "||mimic.chartevents.VALUEUOM | fhir.observation.unit|\n",
    "||mimic.chartevents.WARNING | fhir.observation.interpretation|\n",
    "||mimic.chartevents.RESULTSTATUS | fhir.observation.status|\n",
    "||mimic.d_items.(LABEL+DBSOURCE+PARAM_TYPE) | fhir.observation.note|\n",
    "||mimic.d_items.CATEGORY | fhir.observation.category_sub|\n",
    "||'chartevents' | fhir.observation.category|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "urban-sleeve",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1/33.0 --- 99.12988948822021 seconds ---\n",
      "2/33.0 --- 102.19948816299438 seconds ---\n",
      "3/33.0 --- 102.49104332923889 seconds ---\n",
      "4/33.0 --- 98.38828468322754 seconds ---\n",
      "5/33.0 --- 99.37262392044067 seconds ---\n",
      "6/33.0 --- 98.72399520874023 seconds ---\n",
      "7/33.0 --- 98.82018399238586 seconds ---\n",
      "8/33.0 --- 97.12989234924316 seconds ---\n",
      "9/33.0 --- 98.4189944267273 seconds ---\n",
      "10/33.0 --- 99.18599510192871 seconds ---\n",
      "11/33.0 --- 98.32328128814697 seconds ---\n",
      "12/33.0 --- 97.49378180503845 seconds ---\n",
      "13/33.0 --- 98.69335556030273 seconds ---\n",
      "14/33.0 --- 99.97787261009216 seconds ---\n",
      "15/33.0 --- 101.6583559513092 seconds ---\n",
      "16/33.0 --- 98.44779443740845 seconds ---\n",
      "17/33.0 --- 98.28255653381348 seconds ---\n",
      "18/33.0 --- 103.05923461914062 seconds ---\n",
      "19/33.0 --- 101.67482829093933 seconds ---\n",
      "20/33.0 --- 93.02813959121704 seconds ---\n",
      "21/33.0 --- 91.26527094841003 seconds ---\n",
      "22/33.0 --- 93.36439061164856 seconds ---\n",
      "23/33.0 --- 91.3999195098877 seconds ---\n",
      "24/33.0 --- 91.02369546890259 seconds ---\n",
      "25/33.0 --- 91.8609356880188 seconds ---\n",
      "26/33.0 --- 95.47106218338013 seconds ---\n",
      "27/33.0 --- 105.60843229293823 seconds ---\n",
      "28/33.0 --- 106.98884177207947 seconds ---\n",
      "29/33.0 --- 107.72284126281738 seconds ---\n",
      "30/33.0 --- 107.50220847129822 seconds ---\n",
      "31/33.0 --- 115.52404999732971 seconds ---\n",
      "32/33.0 --- 114.74798035621643 seconds ---\n",
      "33/33.0 --- 114.49016308784485 seconds ---\n",
      "34/33.0 --- 6.881781578063965 seconds ---\n"
     ]
    }
   ],
   "source": [
    "from pathlib import Path\n",
    "import time\n",
    "import gc\n",
    "\n",
    "\n",
    "def transform_chartevents(data_path, output_path, chunksize=10**7):\n",
    "    \"\"\" ~6GB RAM in peak consumption with default chunksize\n",
    "    \"\"\"\n",
    "    # delete outputfile if exists\n",
    "    output_filename = output_path+'observation_ce.csv.gz'\n",
    "    Path(output_filename).unlink(missing_ok=True)\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",
    "    # it is the biggest file ~4GB gzipped, 33GB unpacked, 330M strings\n",
    "    # looks like CareVue and Metavision data should be processed separately\n",
    "    chunk_container =  pd.read_csv(data_path+'CHARTEVENTS'+file_ext, compression=compression,\n",
    "                                   # STORETIME\n",
    "                                   usecols=['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID', 'ITEMID', 'CHARTTIME',\n",
    "                                            'STORETIME', 'CGID', 'VALUE', 'VALUENUM', 'VALUEUOM', 'WARNING', 'ERROR',\n",
    "                                            'RESULTSTATUS', 'STOPPED'],\n",
    "                                   dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': int, 'ICUSTAY_ID': float,\n",
    "                                          'ITEMID': int, 'CGID': float, 'VALUE': str, 'VALUENUM': float, \n",
    "                                          'VALUEUOM': str, 'WARNING': float, 'ERROR': float,\n",
    "                                          'RESULTSTATUS': str, 'STOPPED': str},\n",
    "                                   parse_dates=['CHARTTIME'],\n",
    "                                   chunksize=chunksize)  # 2.67GB for 10**7\n",
    "    for i, chartevents in enumerate(chunk_container):\n",
    "        # Show progress (~330M strings)\n",
    "        print(f'{i + 1}/{330*10**6 / chunksize}', flush=True, end =\" \")\n",
    "        start_time = time.time()\n",
    "\n",
    "        observation_ce = pd.merge(chartevents, d_items, on='ITEMID')\n",
    "\n",
    "        observation_ce['note'] = observation_ce['LABEL'].str.cat(observation_ce['DBSOURCE'], sep=' ', na_rep='NA')\n",
    "        observation_ce['note'] = observation_ce['note'].str.cat(observation_ce['PARAM_TYPE'], sep=' ', na_rep='')\n",
    "\n",
    "        observation_ce.loc[observation_ce['STOPPED'] == \"D/C'd\", 'RESULTSTATUS'] = 'discharged'\n",
    "        observation_ce.loc[observation_ce['ERROR'] == 1, 'RESULTSTATUS'] = 'Error'\n",
    "        # New columns to adapt to Chartevents observations\n",
    "        observation_ce['category'] = 'chartevents'  # ????\n",
    "\n",
    "        observation_ce.drop(['LABEL', 'PARAM_TYPE', 'ERROR', 'DBSOURCE', 'STOPPED'], axis=1, inplace=True)\n",
    "\n",
    "        observation_ce.rename(columns={'ROW_ID':'identifier',\n",
    "                                       'SUBJECT_ID':'subject',\n",
    "                                       'HADM_ID':'encounter',                               \n",
    "                                       'ICUSTAY_ID':'partOf',\n",
    "                                       'ITEMID':'code',\n",
    "                                       'CGID':'performer',\n",
    "                                       'CHARTTIME':'effectiveDateTime',\n",
    "                                       'VALUE':'value',\n",
    "                                       'VALUENUM':'value_quantity',\n",
    "                                       'VALUEUOM':'unit',\n",
    "                                       'WARNING':'interpretation',\n",
    "                                       'RESULTSTATUS':'status',\n",
    "                                       'CATEGORY':'category_sub'}, inplace=True)\n",
    "\n",
    "        observation_ce = observation_ce.reindex(columns=['identifier',\n",
    "                                                         'subject', \n",
    "                                                         'encounter', \n",
    "                                                         'partOf', \n",
    "                                                         'code',\n",
    "                                                         'effectiveDateTime',\n",
    "                                                         'performer',\n",
    "                                                         'value',\n",
    "                                                         'value_quantity',\n",
    "                                                         'unit', \n",
    "                                                         'interpretation',\n",
    "                                                         'status',\n",
    "                                                         'note',\n",
    "                                                         'category_sub',\n",
    "                                                         'category'], copy=False)\n",
    "\n",
    "        observation_ce.to_csv(output_filename, compression={'method': 'gzip', 'compresslevel': 1},\n",
    "                              index=False, mode='a')\n",
    "        # force free mem, for some reasons without it, RAM ends pretty quick\n",
    "        gc.collect()\n",
    "        # show execution time per chunk\n",
    "        print(f\"--- {time.time() - start_time} seconds ---\", flush=True)\n",
    "\n",
    "        \n",
    "transform_chartevents(data_path, output_path)\n",
    "# observation_ce = transform_chartevents(data_path, output_path)\n",
    "# observation_ce.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "characteristic-nursing",
   "metadata": {},
   "source": [
    "## DATETIMEEVENTS MAPPING:<br>\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "||mimic.datetimeevents.ROW_ID| fhir.observation.identifier|\n",
    "||mimic.datetimeevents.SUBJECT_ID | fhir.observation.subject|\n",
    "||mimic.datetimeevents.HADM_ID | fhir.observation.encounter|\n",
    "||mimic.datetimeevents.ICUSTAY_ID | fhir.observation.partOf|\n",
    "||mimic.datetimeevents.ITEMID | fhir.observation.code|\n",
    "||mimic.datetimeevents.CHARTTIME | fhir.observation.effectiveDateTime|\n",
    "||mimic.datetimeevents.CGID | fhir.observation.performer|\n",
    "||mimic.datetimeevents.VALUE | fhir.observation.value|\n",
    "||mimic.datetimeevents.VALUEUOM | fhir.observation.unit|\n",
    "||mimic.datetimeevents.WARNING | fhir.observation.interpretation|\n",
    "||mimic.datetimeevents.RESULTSTATUS | fhir.observation.status|\n",
    "||mimic.d_items.(LABEL+DBSOURCE+PARAM_TYPE) | fhir.observation.note|\n",
    "||mimic.d_items.CATEGORY | fhir.observation.category_sub|\n",
    "||'datetimeevents' | fhir.observation.category|\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "stuffed-working",
   "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>code</th>\n",
       "      <th>effectiveDateTime</th>\n",
       "      <th>performer</th>\n",
       "      <th>value</th>\n",
       "      <th>unit</th>\n",
       "      <th>interpretation</th>\n",
       "      <th>status</th>\n",
       "      <th>note</th>\n",
       "      <th>category_sub</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>711</td>\n",
       "      <td>7657</td>\n",
       "      <td>121183.0</td>\n",
       "      <td>297945.0</td>\n",
       "      <td>3411</td>\n",
       "      <td>2172-03-14 11:00:00</td>\n",
       "      <td>16446.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Equip Change [MM] carevue</td>\n",
       "      <td>NaN</td>\n",
       "      <td>datetimeevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>712</td>\n",
       "      <td>7657</td>\n",
       "      <td>121183.0</td>\n",
       "      <td>297945.0</td>\n",
       "      <td>3411</td>\n",
       "      <td>2172-03-14 13:00:00</td>\n",
       "      <td>16446.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Equip Change [MM] carevue</td>\n",
       "      <td>NaN</td>\n",
       "      <td>datetimeevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>713</td>\n",
       "      <td>7657</td>\n",
       "      <td>121183.0</td>\n",
       "      <td>297945.0</td>\n",
       "      <td>3411</td>\n",
       "      <td>2172-03-14 15:00:00</td>\n",
       "      <td>14957.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Equip Change [MM] carevue</td>\n",
       "      <td>NaN</td>\n",
       "      <td>datetimeevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>714</td>\n",
       "      <td>7657</td>\n",
       "      <td>121183.0</td>\n",
       "      <td>297945.0</td>\n",
       "      <td>3411</td>\n",
       "      <td>2172-03-14 17:00:00</td>\n",
       "      <td>16446.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Equip Change [MM] carevue</td>\n",
       "      <td>NaN</td>\n",
       "      <td>datetimeevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>715</td>\n",
       "      <td>7657</td>\n",
       "      <td>121183.0</td>\n",
       "      <td>297945.0</td>\n",
       "      <td>3411</td>\n",
       "      <td>2172-03-14 19:00:00</td>\n",
       "      <td>14815.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Equip Change [MM] carevue</td>\n",
       "      <td>NaN</td>\n",
       "      <td>datetimeevents</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter    partOf  code   effectiveDateTime  \\\n",
       "0         711     7657   121183.0  297945.0  3411 2172-03-14 11:00:00   \n",
       "1         712     7657   121183.0  297945.0  3411 2172-03-14 13:00:00   \n",
       "2         713     7657   121183.0  297945.0  3411 2172-03-14 15:00:00   \n",
       "3         714     7657   121183.0  297945.0  3411 2172-03-14 17:00:00   \n",
       "4         715     7657   121183.0  297945.0  3411 2172-03-14 19:00:00   \n",
       "\n",
       "   performer value  unit  interpretation status                        note  \\\n",
       "0    16446.0   NaN  Date             NaN    NaN  Equip Change [MM] carevue    \n",
       "1    16446.0   NaN  Date             NaN    NaN  Equip Change [MM] carevue    \n",
       "2    14957.0   NaN  Date             NaN    NaN  Equip Change [MM] carevue    \n",
       "3    16446.0   NaN  Date             NaN    NaN  Equip Change [MM] carevue    \n",
       "4    14815.0   NaN  Date             NaN    NaN  Equip Change [MM] carevue    \n",
       "\n",
       "  category_sub        category  \n",
       "0          NaN  datetimeevents  \n",
       "1          NaN  datetimeevents  \n",
       "2          NaN  datetimeevents  \n",
       "3          NaN  datetimeevents  \n",
       "4          NaN  datetimeevents  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform_datetimeevents(data_path, output_path):\n",
    "    # 'STORETIME'\n",
    "    datetimeevents = pd.read_csv(data_path+'DATETIMEEVENTS'+file_ext, compression=compression,\n",
    "                                 usecols=['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ICUSTAY_ID',\n",
    "                                          'ITEMID', 'CHARTTIME', 'CGID', 'VALUE',\n",
    "                                          'VALUEUOM', 'WARNING', 'ERROR', 'RESULTSTATUS',\n",
    "                                          'STOPPED'],\n",
    "                                 dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': float,\n",
    "                                        'ICUSTAY_ID': float, 'ITEMID': int, 'CGID': float,\n",
    "                                        'VALUEUOM': str, 'WARNING': float, 'ERROR': float,\n",
    "                                        'RESULTSTATUS': str, 'STOPPED': str},\n",
    "                                 parse_dates=['CHARTTIME', 'VALUE']\n",
    "                                )\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",
    "    observation_dte = pd.merge(datetimeevents, d_items, on='ITEMID')\n",
    "    \n",
    "    observation_dte['note'] = observation_dte['LABEL'].str.cat(observation_dte['DBSOURCE'], sep=' ', na_rep='NA')\n",
    "    observation_dte['note'] = observation_dte['note'].str.cat(observation_dte['PARAM_TYPE'], sep=' ', na_rep='')\n",
    "        \n",
    "    observation_dte.loc[observation_dte['STOPPED'] == \"D/C'd\", 'RESULTSTATUS'] = 'discharged'\n",
    "    observation_dte.loc[observation_dte['ERROR'] == 1, 'RESULTSTATUS'] = 'Error'\n",
    "    # New columns to adapt to DateTimeEvents observations\n",
    "    observation_dte['category'] = 'datetimeevents'  # ???\n",
    "\n",
    "    observation_dte.drop(['LABEL', 'PARAM_TYPE', 'ERROR', 'DBSOURCE', 'STOPPED'], axis=1, inplace=True)\n",
    "\n",
    "    observation_dte.rename(columns={'ROW_ID':'identifier',\n",
    "                                    'SUBJECT_ID':'subject',\n",
    "                                    'HADM_ID':'encounter',\n",
    "                                    'ICUSTAY_ID':'partOf',\n",
    "                                    'ITEMID':'code',\n",
    "                                    'CGID':'performer',\n",
    "                                    'CHARTTIME':'effectiveDateTime',\n",
    "                                    'VALUE':'value',\n",
    "                                    'VALUEUOM':'unit',\n",
    "                                    'WARNING':'interpretation',\n",
    "                                    'RESULTSTATUS':'status',\n",
    "                                    'CATEGORY':'category_sub'}, inplace=True)\n",
    "\n",
    "    observation_dte = observation_dte.reindex(columns=['identifier',\n",
    "                                                       'subject', \n",
    "                                                       'encounter', \n",
    "                                                       'partOf',\n",
    "                                                       'code',\n",
    "                                                       'effectiveDateTime', \n",
    "                                                       'performer',\n",
    "                                                       'value',\n",
    "                                                       'unit', \n",
    "                                                       'interpretation',\n",
    "                                                       'status',\n",
    "                                                       'note',\n",
    "                                                       'category_sub',\n",
    "                                                       'category'], copy=False)\n",
    "\n",
    "#     observation_dte.to_csv(output_path+'observation_dte.csv.gz', compression='gzip', index=False)\n",
    "    return observation_dte\n",
    "\n",
    "observation_dte = transform_datetimeevents(data_path, output_path)\n",
    "observation_dte.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fabulous-analysis",
   "metadata": {},
   "outputs": [],
   "source": [
    "observation_dte.to_csv(output_path+'observation_dte.csv.gz',\n",
    "                       compression={'method': 'gzip', 'compresslevel': 1}, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "forced-norwegian",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del observation_dte\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "greek-skill",
   "metadata": {},
   "source": [
    "## LABEVENTS MAPPING:<br>\n",
    "\n",
    "- Consider assigning loinc_code to code not to method. LOINC_CODE would first need to be assigned, which isn't straightforward.\n",
    "\n",
    "||Original format | FHIR resource format|\n",
    "|------|:-----|:-----|\n",
    "||mimic.labevents.ROW_ID|fhir.observation.identifier|\n",
    "||mimic.labevents.SUBJECT_ID|fhir.observation.subject|\n",
    "||mimic.labevents.HADM_ID|fhir.observation.encounter|\n",
    "||mimic.labevents.CHARTTIME|fhir.observation.effectiveDateTime|\n",
    "||mimic.labevents.ITEMID | fhir.observation.code|\n",
    "||mimic.d_labitems.LOINC_CODE | fhir.observation.code_loinc|\n",
    "||mimic.labevents.VALUE | fhir.observation.value|\n",
    "||mimic.labevents.VALUENUM | fhir.observation.value_quantity|\n",
    "||mimic.labevents.VALUEUOM | fhir.observation.unit|\n",
    "||mimic.labevents.FLAG | fhir.observation.interpretation|\n",
    "||mimic.d_labitems.(LABEL+FLUID) | fhir.observation.note|\n",
    "||mimic.d_labitems.CATEGORY | fhir.observation.category_sub|\n",
    "||'labevents' | fhir.observation.category|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "allied-scout",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1/28.0 --- 6.980336904525757 seconds ---\n",
      "2/28.0 --- 7.179809093475342 seconds ---\n",
      "3/28.0 --- 7.201792240142822 seconds ---\n",
      "4/28.0 --- 7.046716928482056 seconds ---\n",
      "5/28.0 --- 7.200324773788452 seconds ---\n",
      "6/28.0 --- 7.3242011070251465 seconds ---\n",
      "7/28.0 --- 7.068134069442749 seconds ---\n",
      "8/28.0 --- 7.024747610092163 seconds ---\n",
      "9/28.0 --- 7.105806827545166 seconds ---\n",
      "10/28.0 --- 7.503292083740234 seconds ---\n",
      "11/28.0 --- 7.739151954650879 seconds ---\n",
      "12/28.0 --- 7.523775815963745 seconds ---\n",
      "13/28.0 --- 7.35314154624939 seconds ---\n",
      "14/28.0 --- 7.229565858840942 seconds ---\n",
      "15/28.0 --- 6.952656030654907 seconds ---\n",
      "16/28.0 --- 7.112035512924194 seconds ---\n",
      "17/28.0 --- 7.4885265827178955 seconds ---\n",
      "18/28.0 --- 6.993064641952515 seconds ---\n",
      "19/28.0 --- 6.979121208190918 seconds ---\n",
      "20/28.0 --- 6.980049133300781 seconds ---\n",
      "21/28.0 --- 7.230544805526733 seconds ---\n",
      "22/28.0 --- 7.312502384185791 seconds ---\n",
      "23/28.0 --- 7.299519300460815 seconds ---\n",
      "24/28.0 --- 7.856963872909546 seconds ---\n",
      "25/28.0 --- 7.651737213134766 seconds ---\n",
      "26/28.0 --- 7.136707782745361 seconds ---\n",
      "27/28.0 --- 7.222909212112427 seconds ---\n",
      "28/28.0 --- 6.299153089523315 seconds ---\n"
     ]
    }
   ],
   "source": [
    "from pathlib import Path\n",
    "import time\n",
    "import gc\n",
    "\n",
    "\n",
    "def transform_labevents(data_path, output_path, chunksize=10**6):\n",
    "    \"\"\" Surprisingly, it is memory greed thing \"\"\"\n",
    "    # delete outputfile if exists\n",
    "    output_filename = output_path + 'observation_le.csv.gz'\n",
    "    Path(output_filename).unlink(missing_ok=True)\n",
    "    \n",
    "    d_labitems = pd.read_csv(data_path+'D_LABITEMS'+file_ext, compression=compression, index_col=0,\n",
    "                             usecols=['ROW_ID', 'ITEMID', 'LABEL', 'FLUID', 'CATEGORY', 'LOINC_CODE'],\n",
    "                             dtype={'ROW_ID': int, 'ITEMID': int, 'LABEL': str,\n",
    "                                    'FLUID': str, 'CATEGORY': str, 'LOINC_CODE': str}\n",
    "                            )\n",
    "\n",
    "    # 5.5GB\n",
    "    chunk_container = pd.read_csv(data_path+'LABEVENTS'+file_ext, compression=compression,\n",
    "                                  usecols=['ROW_ID', 'SUBJECT_ID', 'HADM_ID', 'ITEMID',\n",
    "                                           'CHARTTIME', 'VALUE', 'VALUENUM','VALUEUOM', 'FLAG'],\n",
    "                                  dtype={'ROW_ID': int, 'SUBJECT_ID': int, 'HADM_ID': float, 'ITEMID': int,\n",
    "                                         'VALUE': str, 'VALUENUM': float, 'VALUEUOM': str, 'FLAG': str},\n",
    "                                  parse_dates=['CHARTTIME'],\n",
    "                                  chunksize=chunksize)\n",
    "    for i, labevents in enumerate(chunk_container):\n",
    "        # Show progress (~28M strings)\n",
    "        print(f'{i + 1}/{28*10**6 / chunksize}', flush=True, end =\" \")\n",
    "        start_time = time.time()\n",
    "\n",
    "        observation_le = pd.merge(labevents, d_labitems, on='ITEMID')\n",
    "\n",
    "        observation_le['note'] = observation_le['LABEL'].str.cat(observation_le['FLUID'], sep=' ', na_rep='NA')\n",
    "        observation_le.drop(['LABEL', 'FLUID'], axis=1, inplace=True)\n",
    "\n",
    "        # Add observation type\n",
    "        observation_le['category'] = 'labevents'  # ???\n",
    "\n",
    "        # SUBJECT_ID will be underfilled\n",
    "        observation_le.rename(columns={'ROW_ID':'identifier',\n",
    "                                       'SUBJECT_ID':'subject',\n",
    "                                       'HADM_ID':'encounter',\n",
    "                                       'ITEMID':'code',\n",
    "                                       'LOINC_CODE':'code_loinc',\n",
    "                                       'CHARTTIME':'effectiveDateTime',\n",
    "                                       'VALUE':'value',\n",
    "                                       'VALUENUM':'value_quantity',\n",
    "                                       'VALUEUOM':'unit',\n",
    "                                       'FLAG':'interpretation',\n",
    "                                       'CATEGORY':'category_sub'}, inplace=True)\n",
    "\n",
    "        observation_le = observation_le.reindex(columns=['identifier',\n",
    "                                                         'subject', \n",
    "                                                         'encounter',\n",
    "                                                         'effectiveDateTime',\n",
    "                                                         'code',\n",
    "                                                         'code_loinc',\n",
    "                                                         'value',\n",
    "                                                         'value_quantity',\n",
    "                                                         'unit', \n",
    "                                                         'interpretation',\n",
    "                                                         'note',\n",
    "                                                         'category_sub',\n",
    "                                                         'category'], copy=False)\n",
    "\n",
    "        observation_le.to_csv(output_filename, compression={'method': 'gzip', 'compresslevel': 1},\n",
    "                              index=False, mode='a')\n",
    "        # force free mem, for some reasons without it, RAM ends pretty quick\n",
    "        gc.collect()\n",
    "        # show execution time per chunk\n",
    "        print(f\"--- {time.time() - start_time} seconds ---\", flush=True)\n",
    "\n",
    "        \n",
    "transform_labevents(data_path, output_path)\n",
    "# observation_le.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "absent-installation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>identifier</th>\n",
       "      <th>subject</th>\n",
       "      <th>encounter</th>\n",
       "      <th>effectiveDateTime</th>\n",
       "      <th>code</th>\n",
       "      <th>code_loinc</th>\n",
       "      <th>value</th>\n",
       "      <th>value_quantity</th>\n",
       "      <th>unit</th>\n",
       "      <th>interpretation</th>\n",
       "      <th>note</th>\n",
       "      <th>category_sub</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>281</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2101-10-12 16:07:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.39</td>\n",
       "      <td>7.39</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>289</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2101-10-12 18:17:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.42</td>\n",
       "      <td>7.42</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>396</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2101-10-15 12:42:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.49</td>\n",
       "      <td>7.49</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>976</td>\n",
       "      <td>3</td>\n",
       "      <td>145834.0</td>\n",
       "      <td>2101-10-22 04:31:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.40</td>\n",
       "      <td>7.40</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>989</td>\n",
       "      <td>3</td>\n",
       "      <td>145834.0</td>\n",
       "      <td>2101-10-22 07:13:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.37</td>\n",
       "      <td>7.37</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>998</td>\n",
       "      <td>3</td>\n",
       "      <td>145834.0</td>\n",
       "      <td>2101-10-22 10:16:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.37</td>\n",
       "      <td>7.37</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>3</td>\n",
       "      <td>145834.0</td>\n",
       "      <td>2101-10-22 11:21:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.39</td>\n",
       "      <td>7.39</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1020</td>\n",
       "      <td>3</td>\n",
       "      <td>145834.0</td>\n",
       "      <td>2101-10-22 13:02:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.39</td>\n",
       "      <td>7.39</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1030</td>\n",
       "      <td>3</td>\n",
       "      <td>145834.0</td>\n",
       "      <td>2101-10-22 15:59:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.41</td>\n",
       "      <td>7.41</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1044</td>\n",
       "      <td>3</td>\n",
       "      <td>145834.0</td>\n",
       "      <td>2101-10-22 16:02:00</td>\n",
       "      <td>50820</td>\n",
       "      <td>11558-4</td>\n",
       "      <td>7.36</td>\n",
       "      <td>7.36</td>\n",
       "      <td>units</td>\n",
       "      <td>NaN</td>\n",
       "      <td>pH Blood</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>labevents</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   identifier  subject  encounter    effectiveDateTime   code code_loinc  \\\n",
       "0         281        3        NaN  2101-10-12 16:07:00  50820    11558-4   \n",
       "1         289        3        NaN  2101-10-12 18:17:00  50820    11558-4   \n",
       "2         396        3        NaN  2101-10-15 12:42:00  50820    11558-4   \n",
       "3         976        3   145834.0  2101-10-22 04:31:00  50820    11558-4   \n",
       "4         989        3   145834.0  2101-10-22 07:13:00  50820    11558-4   \n",
       "5         998        3   145834.0  2101-10-22 10:16:00  50820    11558-4   \n",
       "6        1007        3   145834.0  2101-10-22 11:21:00  50820    11558-4   \n",
       "7        1020        3   145834.0  2101-10-22 13:02:00  50820    11558-4   \n",
       "8        1030        3   145834.0  2101-10-22 15:59:00  50820    11558-4   \n",
       "9        1044        3   145834.0  2101-10-22 16:02:00  50820    11558-4   \n",
       "\n",
       "   value  value_quantity   unit  interpretation      note category_sub  \\\n",
       "0   7.39            7.39  units             NaN  pH Blood    Blood Gas   \n",
       "1   7.42            7.42  units             NaN  pH Blood    Blood Gas   \n",
       "2   7.49            7.49  units             NaN  pH Blood    Blood Gas   \n",
       "3   7.40            7.40  units             NaN  pH Blood    Blood Gas   \n",
       "4   7.37            7.37  units             NaN  pH Blood    Blood Gas   \n",
       "5   7.37            7.37  units             NaN  pH Blood    Blood Gas   \n",
       "6   7.39            7.39  units             NaN  pH Blood    Blood Gas   \n",
       "7   7.39            7.39  units             NaN  pH Blood    Blood Gas   \n",
       "8   7.41            7.41  units             NaN  pH Blood    Blood Gas   \n",
       "9   7.36            7.36  units             NaN  pH Blood    Blood Gas   \n",
       "\n",
       "    category  \n",
       "0  labevents  \n",
       "1  labevents  \n",
       "2  labevents  \n",
       "3  labevents  \n",
       "4  labevents  \n",
       "5  labevents  \n",
       "6  labevents  \n",
       "7  labevents  \n",
       "8  labevents  \n",
       "9  labevents  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(output_path + 'observation_le.csv.gz', nrows=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "governmental-tower",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5.434226114302874"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import sys\n",
    "# sys.getsizeof(labevents) / 1024 / 1024 / 1024"
   ]
  }
 ],
 "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
}