mimic_data_preprocessing/create_clean_chartevents.ipynb

{
 "metadata": {
  "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.9.0"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.0 64-bit"
  },
  "metadata": {
   "interpreter": {
    "hash": "e2bfb1b1dd0bcdebdb315279aa118b1f834444d4ba3ba6d660e9f6ce7703f6a2"
   }
  },
  "interpreter": {
   "hash": "e2bfb1b1dd0bcdebdb315279aa118b1f834444d4ba3ba6d660e9f6ce7703f6a2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Create clean CHARTEVENTS data set\r\n",
    "\r\n",
    "Purpose of this script is to prepare the MIMIC-III data set `CHARTEVENTS.csv` for later use in the master project context by cleaning the vital parameter values and alarm threshold values it contains.\r\n",
    "\r\n",
    "This cleaning includes filtering the data set to a subset that includes only the relevant CHARTEVENT rows with sufficient measurements. In addition, both vital parameter and alarm threshold values that are outside the respective clinically valid value ranges are removed (e.g. a heart rate of 1000 bpm and a heart rate alarm threshold of type *high* of 4000 bpm). Furthermore, two more complex cleanup steps are performed for alarm threshold values: *Revert local threshold swap* and *Remove threshold values due to local overlap*. These steps aim to correct implausible alarm threshold value series. To be precise, those sections of the alarm threshold value series are corrected where the threshold value of type *high* is lower than the threshold value of type *low* and vice versa. According to the medical experts consulted, such an overlap of alarm threshold values makes no sense.\r\n",
    "\r\n",
    "**Revert local threshold swap**: In some cases, the thresholds appear to be exactly swapped, i.e. the threshold of type *high* appears to be actually the threshold of type *low* and vice versa. Such an apparent swap is usually not present over the entire period of the alarm threshold value series, but only at certain sections, i.e. locations. Accordingly, we call it a *local threshold swap*. Local threshold swaps are corrected by reversing the swap, i.e. by swapping the alarm threshold value of type *high* with that of type *low* at the affected locations.\r\n",
    "\r\n",
    "**Remove threshold values due to local overlap**: There are also cases where the alarm threshold value series of type *high* and *low* overlap, but are not exactly swapped (e.g. the alarm threshold value of the type *high* is set unreasonably low and falls below the alarm threshold value of type *low*, while the latter seems to have a correct value). Such an overlap is usually not present over the entire period of the alarm threshold value series, but only at certain sections, i.e. locations. Accordingly, we call it a *local overlap*. These cases cannot be corrected by swapping. Also, it is not possible (without a lot of effort) to determine whether only one of the two thresholds is unreasonably high/low or both. Therefore, the threshold values of both types (high and low) are removed at those locations where they overlap. Hence, we call this step *Remove threshold values due to local overlap*.\r\n",
    "\r\n",
    "As the example of *Revert local threshold swap* and *Remove threshold values due to local overlap* illustrates, the **order of the cleaning steps matters**. For example, *Revert local threshold swaps* must be done first, then *Remove threshold values due to local overlap*. Otherwise, exact swaps would no longer be identifiable because they would already have been removed as a local overlap.\r\n",
    "\r\n",
    "The following overview shows the cleaning steps in sequence:\r\n",
    "\r\n",
    "1. Create chartevents_subset\r\n",
    "    - Created file: `chartevents_subset.parquet` (subset of CHARTEVENTS data frame)\r\n",
    "2. Remove rows with insufficient values from chartevents_subset\r\n",
    "    - Created file: `chartevents_cleaning_02_sufficient_values_only.parquet` (modified CHARTEVENTS data frame)\r\n",
    "3. Remove measurement values outside clinically valid ranges\r\n",
    "    - 3.1 Flag measurements outside clinically valid ranges\r\n",
    "    - 3.2 Introduce new VALUENUM_CLEAN column and set measurement values outside valid ranges to NaN\r\n",
    "    - Created file: `chartevents_cleaning_03_measurements_in_valid_range.parquet` (modified CHARTEVENTS data frame)\r\n",
    "4. Revert local threshold swap\r\n",
    "    - 4.1 Identify candidates for local threshold swap\r\n",
    "    - 4.2 Prepare data set for local threshold swap\r\n",
    "    - 4.3 Perform local threshold swap\r\n",
    "    - Created file: `chartevents_cleaning_04_exact_threshold_swaps_reverted.parquet` (modified CHARTEVENTS data frame)\r\n",
    "5. Remove alarm threshold values outside clinically valid ranges\r\n",
    "    - Created file: `chartevents_cleaning_05_thresholds_in_valid_range.parquet` (modified CHARTEVENTS data frame)\r\n",
    "6. Remove threshold values due to local overlap\r\n",
    "    - 6.1 Identify candidates for threshold removal due to local overlap\r\n",
    "    - 6.2 Prepare data set for threshold removal due to local overlap\r\n",
    "    - 6.3 Perform threshold removal due to local overlap\r\n",
    "    - Created file: `chartevents_cleaning_06_overlapping_thresholds_removed.parquet` (modified CHARTEVENTS data frame), which is the final data frame `chartevents_clean.parquet` as of the master project completion date. \r\n",
    "\r\n",
    "As the overview of the steps shows, auxiliary data frames as well as intermediate results of the CHARTEVENTS cleanup are saved as parquet files. This was done to avoid recomputation of data frames when working on individual cleaning sections with limited computing resources."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 1. Create chartevents_subset \r\n",
    "\r\n",
    "- Create subset of the MIMIC-III data set called `CHARTEVENTS.csv` (see also respective [MIMIC schema website](https://mit-lcp.github.io/mimic-schema-spy/tables/chartevents.html))\r\n",
    "- No change in columns, keep all of them\r\n",
    "- Reduce number of rows by:\r\n",
    "  - Filtering for relevant ITEMIDs,\r\n",
    "  - Removing rows without ICUSTAY_ID, and\r\n",
    "  - Removing rows flagged as erroneous"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd\r\n",
    "import dask.dataframe as dd\r\n",
    "import pyarrow as pa\r\n",
    "from dask.diagnostics import ProgressBar\r\n",
    "\r\n",
    "path_to_dir_chartevents_csv = '../data/mimic/'\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "\r\n",
    "# Read CHARTEVENTS.csv as Dask DataFrame\r\n",
    "# Data types based on MIMIC schema specification https://mit-lcp.github.io/mimic-schema-spy/tables/chartevents.html\r\n",
    "# Problem: Complicated use of intger data types with NaNs in Pandas, see https://pandas.pydata.org/pandas-docs/stable/user_guide/gotchas.html#nan-integer-na-values-and-na-type-promotions\r\n",
    "# Decision: Floats and integers are read in as 'float64', strings as 'object', and timestamps via Dask's parse_dates provided for this purpose.\r\n",
    "chartevents = dd.read_csv(path_to_dir_chartevents_csv+'CHARTEVENTS.csv', parse_dates=['CHARTTIME','STORETIME'], dtype={\r\n",
    "    'ROW_ID': 'float64', # int4 according to specification\r\n",
    "    'SUBJECT_ID': 'float64', # int4 according to specification\r\n",
    "    'HADM_ID': 'float64', # int4 according to specification\r\n",
    "    'ICUSTAY_ID': 'float64', # int4 according to specification\r\n",
    "    'ITEMID': 'float64', # int4 according to specification\r\n",
    "    'CGID': 'float64', # int4 according to specification\r\n",
    "    'VALUE': 'object',\r\n",
    "    'VALUENUM': 'float64', # float8 according to specification\r\n",
    "    'VALUEUOM': 'object',\r\n",
    "    'WARNING': 'float64', # int4 according to specification\r\n",
    "    'ERROR': 'float64', # int4 according to specification\r\n",
    "    'RESULTSTATUS': 'object',\r\n",
    "    'STOPPED': 'object'})\r\n",
    "\r\n",
    "# Create list of relevant ITEMIDs to filter by\r\n",
    "itemid_filter = [220045, 220046, 220047, 220179, 223751, 223752, 220277, 223769, 223770]\r\n",
    "# 220045 Heart Rate\r\n",
    "# 220046 Heart rate Alarm - High\r\n",
    "# 220047 Heart Rate Alarm - Low\r\n",
    "# 220179 Non Invasive Blood Pressure systolic\r\n",
    "# 223751 Non-Invasive Blood Pressure Alarm - High\r\n",
    "# 223752 Non-Invasive Blood Pressure Alarm - Low\r\n",
    "# 220277 O2 saturation pulseoxymetry\r\n",
    "# 223769 O2 Saturation Pulseoxymetry Alarm - High\r\n",
    "# 223770 O2 Saturation Pulseoxymetry Alarm - Low\r\n",
    "\r\n",
    "with ProgressBar():\r\n",
    "    # Filter by ITEMIDs\r\n",
    "    chartevents_subset = chartevents[chartevents.ITEMID.isin(itemid_filter)]\r\n",
    "    # Drop rows without ICUSTAY_ID\r\n",
    "    chartevents_subset = chartevents_subset.dropna(how='any', subset=['ICUSTAY_ID'])\r\n",
    "    # Keep only rows without error, coded by value 0 in ERROR column\r\n",
    "    chartevents_subset = chartevents_subset[chartevents_subset.ERROR.isin([0])]\r\n",
    "    # Apply previously defined commands to Dask DataFrame, resulting in desired Pandas DataFrame\r\n",
    "    chartevents_subset = chartevents_subset.compute()\r\n",
    "\r\n",
    "# Sort rows and reset index (not essential, but provides better overview)\r\n",
    "chartevents_subset = chartevents_subset.sort_values(by=['ICUSTAY_ID', 'CHARTTIME','ITEMID']).reset_index(drop=True)\r\n",
    "\r\n",
    "# Save as parquet file\r\n",
    "chartevents_subset.to_parquet(path_to_dir_chartevents_clean+'chartevents_subset.parquet', engine='pyarrow')"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "[########################################] | 100% Completed | 17min 15.4s\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 2. Remove rows with insufficient values from chartevents_subset\r\n",
    "\r\n",
    "The CHARTEVENTS subset contains ...\r\n",
    "- ICU stays that have *threshold* values but no *measurement* values for certain parameters.\r\n",
    "- ICU stays that have only *measurement* values but no *threshold* values for certain parameters.\r\n",
    "- ICU stays that have only a single measurement value for certain parameters.\r\n",
    "\r\n",
    "Therefore, remove those ICU stay parameter combinations ...\r\n",
    "- Without measurement values,\r\n",
    "- Without threshold values, and\r\n",
    "- With only one measurement value."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Read chartevents_subset from parquet file\r\n",
    "chartevents_subset = pd.read_parquet(path_to_dir_chartevents_clean+'chartevents_subset.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "# Create data frame containing a list of the available ITEMIDs for each ICU stay\r\n",
    "itemids_per_icustay = chartevents_subset.groupby(['ICUSTAY_ID'])['ITEMID'].unique().apply(list).reset_index()\r\n",
    "itemids_per_icustay['ITEMID'] = itemids_per_icustay.ITEMID.apply(lambda x: sorted(x))\r\n",
    "display(itemids_per_icustay)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "       ICUSTAY_ID                                             ITEMID\n",
       "0        200001.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "1        200010.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "2        200011.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "3        200016.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "4        200021.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "...           ...                                                ...\n",
       "23441    299956.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "23442    299957.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "23443    299962.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "23444    299979.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "23445    299998.0  [220045.0, 220046.0, 220047.0, 220179.0, 22027...\n",
       "\n",
       "[23446 rows x 2 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200010.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200011.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200016.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200021.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23441</th>\n",
       "      <td>299956.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23442</th>\n",
       "      <td>299957.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23443</th>\n",
       "      <td>299962.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23444</th>\n",
       "      <td>299979.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23445</th>\n",
       "      <td>299998.0</td>\n",
       "      <td>[220045.0, 220046.0, 220047.0, 220179.0, 22027...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23446 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# Identify ICU stays that have thresholds but no measurements\r\n",
    "icustayids_without_measurement_hr = pd.Series(dtype='float64')\r\n",
    "icustayids_without_measurement_bp = pd.Series(dtype='float64')\r\n",
    "icustayids_without_measurement_o2 = pd.Series(dtype='float64')\r\n",
    "\r\n",
    "for i, icustay in itemids_per_icustay.iterrows():\r\n",
    "    if (([220046] in icustay.ITEMID) and ([220047] in icustay.ITEMID) and ([220045] not in icustay.ITEMID)):\r\n",
    "        icustayids_without_measurement_hr = icustayids_without_measurement_hr.append(pd.Series(icustay.ICUSTAY_ID)).reset_index(drop=True)\r\n",
    "    \r\n",
    "    if (([223751] in icustay.ITEMID) and ([223752] in icustay.ITEMID) and ([220179] not in icustay.ITEMID)):\r\n",
    "        icustayids_without_measurement_bp = icustayids_without_measurement_bp.append(pd.Series(icustay.ICUSTAY_ID)).reset_index(drop=True)\r\n",
    "    \r\n",
    "    if (([223769] in icustay.ITEMID) and ([223770] in icustay.ITEMID) and ([220277] not in icustay.ITEMID)):\r\n",
    "        icustayids_without_measurement_o2 = icustayids_without_measurement_o2.append(pd.Series(icustay.ICUSTAY_ID)).reset_index(drop=True)\r\n",
    "\r\n",
    "# Print some statistics\r\n",
    "print(\"Number of ICU stays that have thresholds but no measurements:\")\r\n",
    "print(\"  HR:   \",len(icustayids_without_measurement_hr))\r\n",
    "print(\"  NBPs: \",len(icustayids_without_measurement_bp))\r\n",
    "print(\"  SpO2: \",len(icustayids_without_measurement_o2))\r\n",
    "print(\"  ______________\")\r\n",
    "print(\"  Sum:  \",len(icustayids_without_measurement_hr) + len(icustayids_without_measurement_bp) + len(icustayids_without_measurement_o2))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Number of ICU stays that have thresholds but no measurements:\n",
      "  HR:    6\n",
      "  NBPs:  135\n",
      "  SpO2:  20\n",
      "  ______________\n",
      "  Sum:   161\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "# Identify ICU stays that have measurements but at least one missing threshold series\r\n",
    "icustayids_with_missing_threshold_hr = pd.Series(dtype='float64')\r\n",
    "icustayids_with_missing_threshold_bp = pd.Series(dtype='float64')\r\n",
    "icustayids_with_missing_threshold_o2 = pd.Series(dtype='float64')\r\n",
    "\r\n",
    "for i, icustay in itemids_per_icustay.iterrows():\r\n",
    "    if ([220045] in icustay.ITEMID) and (([220046] not in icustay.ITEMID) or ([220047] not in icustay.ITEMID)):\r\n",
    "        icustayids_with_missing_threshold_hr = icustayids_with_missing_threshold_hr.append(pd.Series(icustay.ICUSTAY_ID)).reset_index(drop=True)\r\n",
    "    \r\n",
    "    if ([220179] in icustay.ITEMID) and (([223751] not in icustay.ITEMID) or ([223752] not in icustay.ITEMID)):\r\n",
    "        icustayids_with_missing_threshold_bp = icustayids_with_missing_threshold_bp.append(pd.Series(icustay.ICUSTAY_ID)).reset_index(drop=True)\r\n",
    "    \r\n",
    "    if ([220277] in icustay.ITEMID) and (([223769] not in icustay.ITEMID) or ([223770] not in icustay.ITEMID)):\r\n",
    "        icustayids_with_missing_threshold_o2 = icustayids_with_missing_threshold_o2.append(pd.Series(icustay.ICUSTAY_ID)).reset_index(drop=True)\r\n",
    "\r\n",
    "# Print some statistics\r\n",
    "print(\"Number of ICU stays with at least one missing threshold series:\")\r\n",
    "print(\"  HR:   \",len(icustayids_with_missing_threshold_hr))\r\n",
    "print(\"  NBPs: \",len(icustayids_with_missing_threshold_bp))\r\n",
    "print(\"  SpO2: \",len(icustayids_with_missing_threshold_o2))\r\n",
    "print(\"  ______________\")\r\n",
    "print(\"  Sum:  \",len(icustayids_with_missing_threshold_hr) + len(icustayids_with_missing_threshold_bp) + len(icustayids_with_missing_threshold_o2))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Number of ICU stays with at least one missing threshold series:\n",
      "  HR:    61\n",
      "  NBPs:  879\n",
      "  SpO2:  63\n",
      "  ______________\n",
      "  Sum:   1003\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "# Create a data frame, which will be used as filter.\r\n",
    "# This data frame contains all ICU stay parameter combinations to be removed due to missing measurement or threshold values.\r\n",
    "# This data frame must consist of the two columns ICUSTAY_ID and ITEMID.\r\n",
    "\r\n",
    "# For all ICU stays that have been identified as having mising measurement or threshold values ...\r\n",
    "icustayids_hr = pd.concat([icustayids_without_measurement_hr, icustayids_with_missing_threshold_hr]).unique()\r\n",
    "icustayids_bp = pd.concat([icustayids_without_measurement_bp, icustayids_with_missing_threshold_bp]).unique()\r\n",
    "icustayids_o2 = pd.concat([icustayids_without_measurement_o2, icustayids_with_missing_threshold_o2]).unique()\r\n",
    "# ... generate combinations with the three ITEMIDs of the respective parameter (measurement, threshold high, and threshold low)\r\n",
    "missing_values_filter = pd.concat([\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_hr, 'ITEMID': 220045}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_hr, 'ITEMID': 220046}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_hr, 'ITEMID': 220047}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_bp, 'ITEMID': 220179}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_bp, 'ITEMID': 223751}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_bp, 'ITEMID': 223752}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_o2, 'ITEMID': 220277}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_o2, 'ITEMID': 223769}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_o2, 'ITEMID': 223770})\r\n",
    "    ])\r\n",
    "\r\n",
    "# Print some statistics\r\n",
    "print(\"Number of ICU stay parameter combinations to be removed due to missing measurement or threshold values:\")\r\n",
    "print(\"  HR:   \",len(icustayids_hr))\r\n",
    "print(\"  NBPs: \",len(icustayids_bp))\r\n",
    "print(\"  SpO2: \",len(icustayids_o2))\r\n",
    "print(\"  ______________\")\r\n",
    "print(\"  Sum:  \",len(icustayids_hr) + len(icustayids_bp) + len(icustayids_o2))\r\n",
    "# Number of ICUSTAY_ID ITEMID comninations in filter is three times the ICU stay parameter combinations, since 3 ITEMIDs apply per parameter (measurement, threshold high, and threshold low).\r\n",
    "display(missing_values_filter)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Number of ICU stay parameter combinations to be removed due to missing measurement or threshold values:\n",
      "  HR:    67\n",
      "  NBPs:  1014\n",
      "  SpO2:  83\n",
      "  ______________\n",
      "  Sum:   1164\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "    ICUSTAY_ID  ITEMID\n",
       "0     214738.0  220045\n",
       "1     229737.0  220045\n",
       "2     239953.0  220045\n",
       "3     244456.0  220045\n",
       "4     261030.0  220045\n",
       "..         ...     ...\n",
       "78    294198.0  223770\n",
       "79    296917.0  223770\n",
       "80    298497.0  223770\n",
       "81    298841.0  223770\n",
       "82    299171.0  223770\n",
       "\n",
       "[3492 rows x 2 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>214738.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>229737.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>239953.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>244456.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>261030.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>294198.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>296917.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>298497.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>298841.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>299171.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3492 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "# Identify ICU stays that have only a single measurement value\r\n",
    "# Create data frame with number of measurement values per ICU stay and measurement ITEMID\r\n",
    "n_measurements_per_icustay_and_itemid = chartevents_subset[chartevents_subset.ITEMID.isin([220045, 220179, 220277])][['ICUSTAY_ID','ITEMID','VALUENUM']]\r\n",
    "n_measurements_per_icustay_and_itemid = n_measurements_per_icustay_and_itemid.groupby(['ICUSTAY_ID','ITEMID']).count()\r\n",
    "n_measurements_per_icustay_and_itemid = n_measurements_per_icustay_and_itemid.rename(columns = {'VALUENUM':'VALUENUM_COUNT'}).reset_index()\r\n",
    "\r\n",
    "# Select ICU stays that have only a single measurement value ...\r\n",
    "icustayids_hr = n_measurements_per_icustay_and_itemid[(n_measurements_per_icustay_and_itemid.ITEMID == 220045) & (n_measurements_per_icustay_and_itemid.VALUENUM_COUNT == 1)]['ICUSTAY_ID']\r\n",
    "icustayids_bp = n_measurements_per_icustay_and_itemid[(n_measurements_per_icustay_and_itemid.ITEMID == 220179) & (n_measurements_per_icustay_and_itemid.VALUENUM_COUNT == 1)]['ICUSTAY_ID']\r\n",
    "icustayids_o2 = n_measurements_per_icustay_and_itemid[(n_measurements_per_icustay_and_itemid.ITEMID == 220277) & (n_measurements_per_icustay_and_itemid.VALUENUM_COUNT == 1)]['ICUSTAY_ID']\r\n",
    "# ... and generate combinations with the three ITEMIDs of the respective parameter (measurement, threshold high, and threshold low)\r\n",
    "single_measurement_filter = pd.concat([\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_hr, 'ITEMID': 220045}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_hr, 'ITEMID': 220046}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_hr, 'ITEMID': 220047}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_bp, 'ITEMID': 220179}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_bp, 'ITEMID': 223751}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_bp, 'ITEMID': 223752}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_o2, 'ITEMID': 220277}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_o2, 'ITEMID': 223769}),\r\n",
    "    pd.DataFrame({'ICUSTAY_ID': icustayids_o2, 'ITEMID': 223770})\r\n",
    "    ])\r\n",
    "\r\n",
    "# Print some statistics\r\n",
    "print(\"Number of ICU stay parameter combinations to be removed due to single measurement:\")\r\n",
    "print(\"  HR:   \",len(icustayids_hr))\r\n",
    "print(\"  NBPs: \",len(icustayids_bp))\r\n",
    "print(\"  SpO2: \",len(icustayids_o2))\r\n",
    "print(\"  ______________\")\r\n",
    "print(\"  Sum:  \",len(icustayids_hr) + len(icustayids_bp) + len(icustayids_o2))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Number of ICU stay parameter combinations to be removed due to single measurement:\n",
      "  HR:    15\n",
      "  NBPs:  179\n",
      "  SpO2:  24\n",
      "  ______________\n",
      "  Sum:   218\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "# Merge filter data frames and drop duplicates\r\n",
    "# Duplicates occur when both filters apply, e.g. when an ICU stay has a single heart rate measurement and a missing threshold series.\r\n",
    "insufficient_values_filter = missing_values_filter.append(single_measurement_filter).drop_duplicates()\r\n",
    "\r\n",
    "# Print some statistics\r\n",
    "print(\"Number of ICU stay parameter combinations to be removed due to insufficient values:\")\r\n",
    "print(\"  HR:   \",len(insufficient_values_filter[insufficient_values_filter.ITEMID == 220045]))\r\n",
    "print(\"  NBPs: \",len(insufficient_values_filter[insufficient_values_filter.ITEMID == 220179]))\r\n",
    "print(\"  SpO2: \",len(insufficient_values_filter[insufficient_values_filter.ITEMID == 220277]))\r\n",
    "print(\"  ______________\")\r\n",
    "print(\"  Sum:  \",len(insufficient_values_filter)//3)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Number of ICU stay parameter combinations to be removed due to insufficient values:\n",
      "  HR:    75\n",
      "  NBPs:  1122\n",
      "  SpO2:  96\n",
      "  ______________\n",
      "  Sum:   1293\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "# Filter the chartevents_subset based on insufficient_values_filter\r\n",
    "df = pd.merge(chartevents_subset, insufficient_values_filter, how='outer', left_on=['ICUSTAY_ID', 'ITEMID'], right_on=['ICUSTAY_ID', 'ITEMID'], indicator=True)\r\n",
    "# Keep only those chartevents_subset rows where the ICUSTAY_ID ITEMID combination is not present in insufficient_values_filter\r\n",
    "df = df[df._merge == 'left_only'].drop('_merge', 1)\r\n",
    "chartevents_cleaning_02_sufficient_values_only = df\r\n",
    "display(chartevents_cleaning_02_sufficient_values_only)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "             ROW_ID  SUBJECT_ID   HADM_ID  ICUSTAY_ID    ITEMID  \\\n",
       "0        14005075.0     55973.0  152234.0    200001.0  220045.0   \n",
       "1        14005090.0     55973.0  152234.0    200001.0  220045.0   \n",
       "2        14005105.0     55973.0  152234.0    200001.0  220045.0   \n",
       "3        14005111.0     55973.0  152234.0    200001.0  220045.0   \n",
       "4        14005117.0     55973.0  152234.0    200001.0  220045.0   \n",
       "...             ...         ...       ...         ...       ...   \n",
       "7886595  20563805.0     69587.0  158288.0    299998.0  223751.0   \n",
       "7886596  20563920.0     69587.0  158288.0    299998.0  223751.0   \n",
       "7886597  20563683.0     69587.0  158288.0    299998.0  223752.0   \n",
       "7886598  20563806.0     69587.0  158288.0    299998.0  223752.0   \n",
       "7886599  20563921.0     69587.0  158288.0    299998.0  223752.0   \n",
       "\n",
       "                  CHARTTIME           STORETIME     CGID VALUE  VALUENUM  \\\n",
       "0       2181-11-25 19:06:00 2181-11-25 19:17:00  20622.0   115     115.0   \n",
       "1       2181-11-25 19:16:00 2181-11-25 19:16:00  20622.0   114     114.0   \n",
       "2       2181-11-25 20:00:00 2181-11-25 22:02:00  21108.0   113     113.0   \n",
       "3       2181-11-25 21:00:00 2181-11-25 22:02:00  21108.0   108     108.0   \n",
       "4       2181-11-25 22:00:00 2181-11-25 22:02:00  21108.0   110     110.0   \n",
       "...                     ...                 ...      ...   ...       ...   \n",
       "7886595 2181-07-06 20:19:00 2181-07-06 20:19:00  20053.0   160     160.0   \n",
       "7886596 2181-07-07 08:00:00 2181-07-07 10:04:00  18462.0   160     160.0   \n",
       "7886597 2181-07-06 08:02:00 2181-07-06 08:04:00  18576.0    90      90.0   \n",
       "7886598 2181-07-06 20:19:00 2181-07-06 20:19:00  20053.0    90      90.0   \n",
       "7886599 2181-07-07 08:00:00 2181-07-07 10:04:00  18462.0    90      90.0   \n",
       "\n",
       "        VALUEUOM  WARNING  ERROR RESULTSTATUS STOPPED  \n",
       "0            bpm      0.0    0.0         None    None  \n",
       "1            bpm      0.0    0.0         None    None  \n",
       "2            bpm      0.0    0.0         None    None  \n",
       "3            bpm      0.0    0.0         None    None  \n",
       "4            bpm      0.0    0.0         None    None  \n",
       "...          ...      ...    ...          ...     ...  \n",
       "7886595     mmHg      0.0    0.0         None    None  \n",
       "7886596     mmHg      0.0    0.0         None    None  \n",
       "7886597     mmHg      0.0    0.0         None    None  \n",
       "7886598     mmHg      0.0    0.0         None    None  \n",
       "7886599     mmHg      0.0    0.0         None    None  \n",
       "\n",
       "[7877920 rows x 15 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>STORETIME</th>\n",
       "      <th>CGID</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>VALUENUM</th>\n",
       "      <th>VALUEUOM</th>\n",
       "      <th>WARNING</th>\n",
       "      <th>ERROR</th>\n",
       "      <th>RESULTSTATUS</th>\n",
       "      <th>STOPPED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14005075.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 19:06:00</td>\n",
       "      <td>2181-11-25 19:17:00</td>\n",
       "      <td>20622.0</td>\n",
       "      <td>115</td>\n",
       "      <td>115.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14005090.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 19:16:00</td>\n",
       "      <td>2181-11-25 19:16:00</td>\n",
       "      <td>20622.0</td>\n",
       "      <td>114</td>\n",
       "      <td>114.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14005105.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 20:00:00</td>\n",
       "      <td>2181-11-25 22:02:00</td>\n",
       "      <td>21108.0</td>\n",
       "      <td>113</td>\n",
       "      <td>113.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14005111.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 21:00:00</td>\n",
       "      <td>2181-11-25 22:02:00</td>\n",
       "      <td>21108.0</td>\n",
       "      <td>108</td>\n",
       "      <td>108.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14005117.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 22:00:00</td>\n",
       "      <td>2181-11-25 22:02:00</td>\n",
       "      <td>21108.0</td>\n",
       "      <td>110</td>\n",
       "      <td>110.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7886595</th>\n",
       "      <td>20563805.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223751.0</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>20053.0</td>\n",
       "      <td>160</td>\n",
       "      <td>160.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7886596</th>\n",
       "      <td>20563920.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223751.0</td>\n",
       "      <td>2181-07-07 08:00:00</td>\n",
       "      <td>2181-07-07 10:04:00</td>\n",
       "      <td>18462.0</td>\n",
       "      <td>160</td>\n",
       "      <td>160.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7886597</th>\n",
       "      <td>20563683.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223752.0</td>\n",
       "      <td>2181-07-06 08:02:00</td>\n",
       "      <td>2181-07-06 08:04:00</td>\n",
       "      <td>18576.0</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7886598</th>\n",
       "      <td>20563806.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223752.0</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>20053.0</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7886599</th>\n",
       "      <td>20563921.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223752.0</td>\n",
       "      <td>2181-07-07 08:00:00</td>\n",
       "      <td>2181-07-07 10:04:00</td>\n",
       "      <td>18462.0</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7877920 rows × 15 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "# Print some statistics about the effects of the cleaning step\r\n",
    "df_before = chartevents_subset\r\n",
    "df_after = chartevents_cleaning_02_sufficient_values_only\r\n",
    "\r\n",
    "row_count_before = len(df_before)\r\n",
    "row_count_after = len(df_after)\r\n",
    "row_count_dif = row_count_before - row_count_after\r\n",
    "\r\n",
    "icustay_count_before = len(df_before.ICUSTAY_ID.unique())\r\n",
    "icustay_count_after = len(df_after.ICUSTAY_ID.unique())\r\n",
    "icustay_count_dif = icustay_count_before - icustay_count_after\r\n",
    "\r\n",
    "icustay_itemid_count_before = len(df_before.groupby(['ICUSTAY_ID','ITEMID']).size())\r\n",
    "icustay_itemid_count_after = len(df_after.groupby(['ICUSTAY_ID','ITEMID']).size())\r\n",
    "icustay_itemid_count_dif = icustay_itemid_count_before - icustay_itemid_count_after\r\n",
    "\r\n",
    "icustay_hr_count_before = len(df_before[df_before.ITEMID.isin([220045, 220046, 220047])]['ICUSTAY_ID'].unique())\r\n",
    "icustay_bp_count_before = len(df_before[df_before.ITEMID.isin([220179, 223751, 223752])]['ICUSTAY_ID'].unique())\r\n",
    "icustay_o2_count_before = len(df_before[df_before.ITEMID.isin([220277, 223769, 223770])]['ICUSTAY_ID'].unique())\r\n",
    "icustay_hr_count_after = len(df_after[df_after.ITEMID.isin([220045, 220046, 220047])]['ICUSTAY_ID'].unique())\r\n",
    "icustay_bp_count_after = len(df_after[df_after.ITEMID.isin([220179, 223751, 223752])]['ICUSTAY_ID'].unique())\r\n",
    "icustay_o2_count_after = len(df_after[df_after.ITEMID.isin([220277, 223769, 223770])]['ICUSTAY_ID'].unique())\r\n",
    "icustay_hr_count_dif = icustay_hr_count_before - icustay_hr_count_after\r\n",
    "icustay_bp_count_dif = icustay_bp_count_before - icustay_bp_count_after\r\n",
    "icustay_o2_count_dif = icustay_o2_count_before - icustay_o2_count_after\r\n",
    "\r\n",
    "icustay_parameter_count_before = icustay_hr_count_before + icustay_bp_count_before + icustay_o2_count_before\r\n",
    "icustay_parameter_count_after = icustay_hr_count_after + icustay_bp_count_after + icustay_o2_count_after\r\n",
    "icustay_parameter_count_dif = icustay_parameter_count_before - icustay_parameter_count_after\r\n",
    "\r\n",
    "print(\"The length of the data frame is reduced by\",f'{row_count_dif:,}',\"rows from\",f'{row_count_before:,}',\"rows to\",f'{row_count_after:,}',\"rows.\")\r\n",
    "print(\"The number of ICUSTAY_IDs is reduced by\",f'{icustay_count_dif:,}',\"from\",f'{icustay_count_before:,}',\"to\",f'{icustay_count_after:,}',\"ICUSTAY_IDs.\")\r\n",
    "print(\"The number of ICUSTAY_ID ITEMID combinations is reduced by\",f'{icustay_itemid_count_dif:,}',\"from\",f'{icustay_itemid_count_before:,}',\"to\",f'{icustay_itemid_count_after:,}',\"ICUSTAY_ID ITEMID combinations.\")\r\n",
    "print(\"The number of ICU stay parameter combinations is reduced by\",f'{icustay_parameter_count_dif:,}',\"from\",f'{icustay_parameter_count_before:,}',\"to\",f'{icustay_parameter_count_after:,}',\"ICUSTAY_IDs.\")\r\n",
    "print(\"  HR:  \",\"reduced from\",f'{icustay_hr_count_before:,}',\"to\",f'{icustay_hr_count_after:,}',\"by\",f'{icustay_hr_count_dif:,}')\r\n",
    "print(\"  NBPs:\",\"reduced from\",f'{icustay_bp_count_before:,}',\"to\",f'{icustay_bp_count_after:,}',\"by\",f'{icustay_bp_count_dif:,}')\r\n",
    "print(\"  SpO2:\",\"reduced from\",f'{icustay_o2_count_before:,}',\"to\",f'{icustay_o2_count_after:,}',\"by\",f'{icustay_o2_count_dif:,}')"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "The length of the data frame is reduced by 8,680 rows from 7,886,600 rows to 7,877,920 rows.\n",
      "The number of ICUSTAY_IDs is reduced by 68 from 23,446 to 23,378 ICUSTAY_IDs.\n",
      "The number of ICUSTAY_ID ITEMID combinations is reduced by 1,724 from 208,374 to 206,650 ICUSTAY_ID ITEMID combinations.\n",
      "The number of ICU stay parameter combinations is reduced by 1,293 from 70,177 to 68,884 ICUSTAY_IDs.\n",
      "  HR:   reduced from 23,445 to 23,370 by 75\n",
      "  NBPs: reduced from 23,296 to 22,174 by 1,122\n",
      "  SpO2: reduced from 23,436 to 23,340 by 96\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Save chartevents_cleaning_02_sufficient_values_only as parquet file\r\n",
    "chartevents_cleaning_02_sufficient_values_only.to_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_02_sufficient_values_only.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 3. Remove measurement values outside clinically valid ranges\r\n",
    "\r\n",
    "Firstly, measurement values outside the clinically valid ranges are flagged in the new column `CLEANING_FLAG`.\r\n",
    "\r\n",
    "Secondly, the affected measurement values are set to NaN, i.e. the data points are removed. In doing so, the values are not deleted from the original `VALUENUM` column. Instead, a new `VALUENUM_CLEAN` column is added to the modified CHARTEVENTS data frame, which contains the cleaned values. This column will be further used in the following cleaning steps.\r\n",
    "\r\n",
    "### 3.1 Flag measurements outside clinically valid ranges"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Read chartevents_cleaning_02_sufficient_values_only from parquet file\r\n",
    "chartevents_cleaning_02_sufficient_values_only = pd.read_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_02_sufficient_values_only.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# Clinically valid value ranges\r\n",
    "# Heart Rate (220045): 0-350\r\n",
    "# Non Invasive Blood Pressure systolic (220179): 0-375\r\n",
    "# Non Invasive Blood Pressure diastolic (220180): 0-375\r\n",
    "# O2 saturation pulseoxymetry (220277): 0-100\r\n",
    "\r\n",
    "# Add new column CLEANING_FLAG, which is used to mark values outside the respective clinically valid range as \"Below valid measurement range\" or \"Above valid measurement range\".\r\n",
    "import numpy as np\r\n",
    "flagged_measurements = chartevents_cleaning_02_sufficient_values_only[['ROW_ID','ITEMID','VALUENUM']].copy()\r\n",
    "flagged_measurements.insert(loc=len(flagged_measurements.columns), column='CLEANING_FLAG', value=np.nan)\r\n",
    "\r\n",
    "flagged_measurements.loc[\r\n",
    "    ((flagged_measurements['ITEMID'] == 220045) & (flagged_measurements['VALUENUM'] < 0)) | \r\n",
    "    ((flagged_measurements['ITEMID'] == 220179) & (flagged_measurements['VALUENUM'] < 0)) | \r\n",
    "    ((flagged_measurements['ITEMID'] == 220180) & (flagged_measurements['VALUENUM'] < 0)) |\r\n",
    "    ((flagged_measurements['ITEMID'] == 220277) & (flagged_measurements['VALUENUM'] < 0)),\r\n",
    "    'CLEANING_FLAG'] = \"Below valid measurement range\"\r\n",
    "\r\n",
    "flagged_measurements.loc[\r\n",
    "    ((flagged_measurements['ITEMID'] == 220045) & (flagged_measurements['VALUENUM'] > 350)) | \r\n",
    "    ((flagged_measurements['ITEMID'] == 220179) & (flagged_measurements['VALUENUM'] > 375)) | \r\n",
    "    ((flagged_measurements['ITEMID'] == 220180) & (flagged_measurements['VALUENUM'] > 375)) |\r\n",
    "    ((flagged_measurements['ITEMID'] == 220277) & (flagged_measurements['VALUENUM'] > 100)),\r\n",
    "    'CLEANING_FLAG'] = \"Above valid measurement range\"\r\n",
    "\r\n",
    "# Reduce to relevant columns and rows (only those needed for merging with chartevents_cleaning_02_sufficient_values_only data frame) \r\n",
    "flagged_measurements = flagged_measurements.drop(['ITEMID','VALUENUM'], axis = 1).dropna().reset_index(drop=True)\r\n",
    "\r\n",
    "display(flagged_measurements)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "        ROW_ID                  CLEANING_FLAG\n",
       "0   23041147.0  Above valid measurement range\n",
       "1    4153364.0  Above valid measurement range\n",
       "2   10012784.0  Above valid measurement range\n",
       "3   19249944.0  Above valid measurement range\n",
       "4   19526026.0  Above valid measurement range\n",
       "..         ...                            ...\n",
       "68   2621410.0  Above valid measurement range\n",
       "69  14074468.0  Above valid measurement range\n",
       "70   1256062.0  Above valid measurement range\n",
       "71  24960577.0  Above valid measurement range\n",
       "72   6725109.0  Above valid measurement range\n",
       "\n",
       "[73 rows x 2 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23041147.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4153364.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10012784.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19249944.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19526026.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2621410.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>14074468.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>1256062.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>24960577.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>6725109.0</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# Print some statistical information about the effects of the cleaning step.\r\n",
    "measurements_affected_count = len(flagged_measurements)\r\n",
    "measurements_affected_above_count = len(flagged_measurements[flagged_measurements.CLEANING_FLAG == \"Above valid measurement range\"])\r\n",
    "measurements_affected_below_count = len(flagged_measurements[flagged_measurements.CLEANING_FLAG == \"Below valid measurement range\"])\r\n",
    "\r\n",
    "print(f'{measurements_affected_count:,}',\"measurements are outside the clinically valid range,\",f'{measurements_affected_above_count:,}',\"above and\",f'{measurements_affected_below_count:,}',\"below.\")"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "73 measurements are outside the clinically valid range, 70 above and 3 below.\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 3.2 Introduce new VALUENUM_CLEAN column and set measurement values outside valid ranges to NaN"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "# Next, data frames chartevents_cleaning_02_sufficient_values_only and flagged_measurements are merged to form a new data frame chartevents_cleaning_03_measurements_in_valid_range.\r\n",
    "# The latter will be extended/modified again in the next cleaning step and so on.\r\n",
    "\r\n",
    "chartevents_cleaning_03_measurements_in_valid_range = chartevents_cleaning_02_sufficient_values_only.copy()\r\n",
    "# Create new column 'VALUENUM_CLEAN' that equals 'VALUENUM' values\r\n",
    "chartevents_cleaning_03_measurements_in_valid_range['VALUENUM_CLEAN'] = chartevents_cleaning_03_measurements_in_valid_range['VALUENUM']\r\n",
    "\r\n",
    "# Set all 'VALUENUM_CLEAN' cells to NaN where the original values have been flagged as being below or above the valid range.\r\n",
    "chartevents_cleaning_03_measurements_in_valid_range = chartevents_cleaning_03_measurements_in_valid_range.merge(flagged_measurements, how='left', on=['ROW_ID'])\r\n",
    "chartevents_cleaning_03_measurements_in_valid_range.loc[\r\n",
    "    (chartevents_cleaning_03_measurements_in_valid_range.CLEANING_FLAG == \"Below valid measurement range\") | \r\n",
    "    (chartevents_cleaning_03_measurements_in_valid_range.CLEANING_FLAG == \"Above valid measurement range\"),\r\n",
    "    'VALUENUM_CLEAN'] = None\r\n",
    "\r\n",
    "display(chartevents_cleaning_03_measurements_in_valid_range)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "             ROW_ID  SUBJECT_ID   HADM_ID  ICUSTAY_ID    ITEMID  \\\n",
       "0        14005075.0     55973.0  152234.0    200001.0  220045.0   \n",
       "1        14005090.0     55973.0  152234.0    200001.0  220045.0   \n",
       "2        14005105.0     55973.0  152234.0    200001.0  220045.0   \n",
       "3        14005111.0     55973.0  152234.0    200001.0  220045.0   \n",
       "4        14005117.0     55973.0  152234.0    200001.0  220045.0   \n",
       "...             ...         ...       ...         ...       ...   \n",
       "7877915  20563805.0     69587.0  158288.0    299998.0  223751.0   \n",
       "7877916  20563920.0     69587.0  158288.0    299998.0  223751.0   \n",
       "7877917  20563683.0     69587.0  158288.0    299998.0  223752.0   \n",
       "7877918  20563806.0     69587.0  158288.0    299998.0  223752.0   \n",
       "7877919  20563921.0     69587.0  158288.0    299998.0  223752.0   \n",
       "\n",
       "                  CHARTTIME           STORETIME     CGID VALUE  VALUENUM  \\\n",
       "0       2181-11-25 19:06:00 2181-11-25 19:17:00  20622.0   115     115.0   \n",
       "1       2181-11-25 19:16:00 2181-11-25 19:16:00  20622.0   114     114.0   \n",
       "2       2181-11-25 20:00:00 2181-11-25 22:02:00  21108.0   113     113.0   \n",
       "3       2181-11-25 21:00:00 2181-11-25 22:02:00  21108.0   108     108.0   \n",
       "4       2181-11-25 22:00:00 2181-11-25 22:02:00  21108.0   110     110.0   \n",
       "...                     ...                 ...      ...   ...       ...   \n",
       "7877915 2181-07-06 20:19:00 2181-07-06 20:19:00  20053.0   160     160.0   \n",
       "7877916 2181-07-07 08:00:00 2181-07-07 10:04:00  18462.0   160     160.0   \n",
       "7877917 2181-07-06 08:02:00 2181-07-06 08:04:00  18576.0    90      90.0   \n",
       "7877918 2181-07-06 20:19:00 2181-07-06 20:19:00  20053.0    90      90.0   \n",
       "7877919 2181-07-07 08:00:00 2181-07-07 10:04:00  18462.0    90      90.0   \n",
       "\n",
       "        VALUEUOM  WARNING  ERROR RESULTSTATUS STOPPED  VALUENUM_CLEAN  \\\n",
       "0            bpm      0.0    0.0         None    None           115.0   \n",
       "1            bpm      0.0    0.0         None    None           114.0   \n",
       "2            bpm      0.0    0.0         None    None           113.0   \n",
       "3            bpm      0.0    0.0         None    None           108.0   \n",
       "4            bpm      0.0    0.0         None    None           110.0   \n",
       "...          ...      ...    ...          ...     ...             ...   \n",
       "7877915     mmHg      0.0    0.0         None    None           160.0   \n",
       "7877916     mmHg      0.0    0.0         None    None           160.0   \n",
       "7877917     mmHg      0.0    0.0         None    None            90.0   \n",
       "7877918     mmHg      0.0    0.0         None    None            90.0   \n",
       "7877919     mmHg      0.0    0.0         None    None            90.0   \n",
       "\n",
       "        CLEANING_FLAG  \n",
       "0                 NaN  \n",
       "1                 NaN  \n",
       "2                 NaN  \n",
       "3                 NaN  \n",
       "4                 NaN  \n",
       "...               ...  \n",
       "7877915           NaN  \n",
       "7877916           NaN  \n",
       "7877917           NaN  \n",
       "7877918           NaN  \n",
       "7877919           NaN  \n",
       "\n",
       "[7877920 rows x 17 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>STORETIME</th>\n",
       "      <th>CGID</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>VALUENUM</th>\n",
       "      <th>VALUEUOM</th>\n",
       "      <th>WARNING</th>\n",
       "      <th>ERROR</th>\n",
       "      <th>RESULTSTATUS</th>\n",
       "      <th>STOPPED</th>\n",
       "      <th>VALUENUM_CLEAN</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14005075.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 19:06:00</td>\n",
       "      <td>2181-11-25 19:17:00</td>\n",
       "      <td>20622.0</td>\n",
       "      <td>115</td>\n",
       "      <td>115.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>115.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14005090.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 19:16:00</td>\n",
       "      <td>2181-11-25 19:16:00</td>\n",
       "      <td>20622.0</td>\n",
       "      <td>114</td>\n",
       "      <td>114.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>114.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14005105.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 20:00:00</td>\n",
       "      <td>2181-11-25 22:02:00</td>\n",
       "      <td>21108.0</td>\n",
       "      <td>113</td>\n",
       "      <td>113.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>113.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14005111.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 21:00:00</td>\n",
       "      <td>2181-11-25 22:02:00</td>\n",
       "      <td>21108.0</td>\n",
       "      <td>108</td>\n",
       "      <td>108.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>108.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14005117.0</td>\n",
       "      <td>55973.0</td>\n",
       "      <td>152234.0</td>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2181-11-25 22:00:00</td>\n",
       "      <td>2181-11-25 22:02:00</td>\n",
       "      <td>21108.0</td>\n",
       "      <td>110</td>\n",
       "      <td>110.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>110.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7877915</th>\n",
       "      <td>20563805.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223751.0</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>20053.0</td>\n",
       "      <td>160</td>\n",
       "      <td>160.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>160.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7877916</th>\n",
       "      <td>20563920.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223751.0</td>\n",
       "      <td>2181-07-07 08:00:00</td>\n",
       "      <td>2181-07-07 10:04:00</td>\n",
       "      <td>18462.0</td>\n",
       "      <td>160</td>\n",
       "      <td>160.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>160.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7877917</th>\n",
       "      <td>20563683.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223752.0</td>\n",
       "      <td>2181-07-06 08:02:00</td>\n",
       "      <td>2181-07-06 08:04:00</td>\n",
       "      <td>18576.0</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>90.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7877918</th>\n",
       "      <td>20563806.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223752.0</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>2181-07-06 20:19:00</td>\n",
       "      <td>20053.0</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>90.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7877919</th>\n",
       "      <td>20563921.0</td>\n",
       "      <td>69587.0</td>\n",
       "      <td>158288.0</td>\n",
       "      <td>299998.0</td>\n",
       "      <td>223752.0</td>\n",
       "      <td>2181-07-07 08:00:00</td>\n",
       "      <td>2181-07-07 10:04:00</td>\n",
       "      <td>18462.0</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>90.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7877920 rows × 17 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "# For demo purposes, show rows with cleaning flag (selected columns)\r\n",
    "chartevents_cleaning_03_measurements_in_valid_range[\r\n",
    "    chartevents_cleaning_03_measurements_in_valid_range.CLEANING_FLAG.notnull()\r\n",
    "    ][['ROW_ID','ICUSTAY_ID','ITEMID','CHARTTIME','VALUENUM','VALUENUM_CLEAN','CLEANING_FLAG']]"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "             ROW_ID  ICUSTAY_ID    ITEMID           CHARTTIME   VALUENUM  \\\n",
       "16851    23041147.0    200238.0  220277.0 2117-04-23 00:00:00    1010.00   \n",
       "225482    4153364.0    202944.0  220179.0 2134-11-05 22:56:00    1148.00   \n",
       "520864   10012784.0    206851.0  220277.0 2172-02-25 08:30:00    9694.00   \n",
       "570048   19249944.0    207606.0  220277.0 2165-03-31 07:00:00    1005.00   \n",
       "806224   19526026.0    210772.0  220045.0 2129-09-21 11:38:00     459.00   \n",
       "...             ...         ...       ...                 ...        ...   \n",
       "6920798   2621410.0    289641.0  220179.0 2172-03-11 11:00:00  141146.04   \n",
       "7445940  14074468.0    295569.0  220277.0 2199-12-31 00:00:00     964.00   \n",
       "7690153   1256062.0    297300.0  220179.0 2155-09-24 17:00:00    1114.00   \n",
       "7738012  24960577.0    298066.0  220179.0 2186-04-14 18:00:00    8582.00   \n",
       "7789260   6725109.0    298685.0  220179.0 2166-02-14 15:45:00   11647.00   \n",
       "\n",
       "         VALUENUM_CLEAN                  CLEANING_FLAG  \n",
       "16851               NaN  Above valid measurement range  \n",
       "225482              NaN  Above valid measurement range  \n",
       "520864              NaN  Above valid measurement range  \n",
       "570048              NaN  Above valid measurement range  \n",
       "806224              NaN  Above valid measurement range  \n",
       "...                 ...                            ...  \n",
       "6920798             NaN  Above valid measurement range  \n",
       "7445940             NaN  Above valid measurement range  \n",
       "7690153             NaN  Above valid measurement range  \n",
       "7738012             NaN  Above valid measurement range  \n",
       "7789260             NaN  Above valid measurement range  \n",
       "\n",
       "[73 rows x 7 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>VALUENUM</th>\n",
       "      <th>VALUENUM_CLEAN</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16851</th>\n",
       "      <td>23041147.0</td>\n",
       "      <td>200238.0</td>\n",
       "      <td>220277.0</td>\n",
       "      <td>2117-04-23 00:00:00</td>\n",
       "      <td>1010.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225482</th>\n",
       "      <td>4153364.0</td>\n",
       "      <td>202944.0</td>\n",
       "      <td>220179.0</td>\n",
       "      <td>2134-11-05 22:56:00</td>\n",
       "      <td>1148.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520864</th>\n",
       "      <td>10012784.0</td>\n",
       "      <td>206851.0</td>\n",
       "      <td>220277.0</td>\n",
       "      <td>2172-02-25 08:30:00</td>\n",
       "      <td>9694.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570048</th>\n",
       "      <td>19249944.0</td>\n",
       "      <td>207606.0</td>\n",
       "      <td>220277.0</td>\n",
       "      <td>2165-03-31 07:00:00</td>\n",
       "      <td>1005.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>806224</th>\n",
       "      <td>19526026.0</td>\n",
       "      <td>210772.0</td>\n",
       "      <td>220045.0</td>\n",
       "      <td>2129-09-21 11:38:00</td>\n",
       "      <td>459.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6920798</th>\n",
       "      <td>2621410.0</td>\n",
       "      <td>289641.0</td>\n",
       "      <td>220179.0</td>\n",
       "      <td>2172-03-11 11:00:00</td>\n",
       "      <td>141146.04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7445940</th>\n",
       "      <td>14074468.0</td>\n",
       "      <td>295569.0</td>\n",
       "      <td>220277.0</td>\n",
       "      <td>2199-12-31 00:00:00</td>\n",
       "      <td>964.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7690153</th>\n",
       "      <td>1256062.0</td>\n",
       "      <td>297300.0</td>\n",
       "      <td>220179.0</td>\n",
       "      <td>2155-09-24 17:00:00</td>\n",
       "      <td>1114.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7738012</th>\n",
       "      <td>24960577.0</td>\n",
       "      <td>298066.0</td>\n",
       "      <td>220179.0</td>\n",
       "      <td>2186-04-14 18:00:00</td>\n",
       "      <td>8582.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7789260</th>\n",
       "      <td>6725109.0</td>\n",
       "      <td>298685.0</td>\n",
       "      <td>220179.0</td>\n",
       "      <td>2166-02-14 15:45:00</td>\n",
       "      <td>11647.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid measurement range</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 7 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Save chartevents_cleaning_03_measurements_in_valid_range as parquet file\r\n",
    "chartevents_cleaning_03_measurements_in_valid_range.to_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_03_measurements_in_valid_range.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 4. Revert local threshold swap\n",
    "\n",
    "Short explanation of **Revert local threshold swap**:\n",
    "\n",
    "In some cases, the thresholds appear to be exactly swapped, i.e. the threshold of type *high* appears to be actually the threshold of type *low* and vice versa. Such an apparent swap is usually not present over the entire period of the alarm threshold value series, but only at certain sections, i.e. locations. Accordingly, we call it a *local threshold swap*. Local threshold swaps are corrected by reversing the swap, i.e. by swapping the alarm threshold value of type *high* with that of type *low* at the affected locations.\n",
    "\n",
    "More detailed explanation:\n",
    "\n",
    "In the data of some ICU stays, the *high* and *low* alarm threshold value seris overlap at certain points. For example, during a certain period of time, the threshold for a heart rate that is too high may be below the threshold for a heart rate that is too low, and vice versa.\n",
    "\n",
    "According to the medical experts consulted, there is no plausible reason for this. In fact, medical devices should not allow the setting of such overlapping alarm thresholds in the first place.\n",
    "\n",
    "The overlap can show up in different ways. Roughly speaking, we have observed three variants looking at time series plots:\n",
    "\n",
    "1. The low threshold temporarily exceeds the high threshold, while the latter continues 'normally'.\n",
    "2. The high threshold value temporarily falls below the low threshold value, while the latter continues 'normally'.\n",
    "3. Both thresholds temporarily overlap so that they appear swapped, which is 'abnormal' for both.\n",
    "\n",
    "There are two sub-variants for variant (3):\n",
    "\n",
    "- 3a The threshold values are swapped, but do not decrease/increase to the same extent, so it is not an exact swap.\n",
    "- 3b The thresholds are swapped, decreasing/increasing  to the same extent, so it looks like an exact swap.\n",
    "\n",
    "According to the agreement with medical experts, the two threshold values for case 3b (exact swap) are swapped back for the time period affected."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.1 Identify candidates for local threshold swap\n",
    "\n",
    "Coarse detection of the ICU stay/ vital parameter combinations for which the local swapping of thresholds is a possible option. Purpose of this preliminary step is to reduce the computational effort. The aim is to reduce the relatively complex threshold swap step to potentially affected cases.\n",
    "\n",
    "The coarse detection is done by comparing the minimum *high* threshold to the maximum *low* threshold for each ICU stay/ vital parameter combination. If the minimum *high* threshold is below the maximum *low* threshold, the ICU stay/ vital parameter combination is considered for threshold swapping.\n",
    "\n",
    "The output of this section is a data frame that includes the ICU stay/ vital parameter combinations that are candidates for swapping. It is to be expected that the number of candidates is higher than the number of swaps eventually performed. The reason for this is that not all alarm threshold overlaps represent an exact swap that can be reverted."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Read chartevents_cleaning_03_measurements_in_valid_range from parquet file\r\n",
    "chartevents_cleaning_03_measurements_in_valid_range = pd.read_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_03_measurements_in_valid_range.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "import pandas as pd\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "# Create empty data frames to which will be appended during the loop\r\n",
    "min_threshold_high_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'THRESHOLD_HIGH_MIN', 'ITEMID_VALUE'])\r\n",
    "max_threshold_high_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'THRESHOLD_LOW_MAX', 'ITEMID_VALUE'])\r\n",
    "\r\n",
    "for i, parameter in parameters.iterrows():\r\n",
    "\r\n",
    "    # For current parameter, compute minimum value of high threshold for all ICU stays\r\n",
    "    min_threshold_high = chartevents_cleaning_03_measurements_in_valid_range[\r\n",
    "        chartevents_cleaning_03_measurements_in_valid_range['ITEMID'] == parameter['THRESHOLD_HIGH']\r\n",
    "        ].groupby(['ICUSTAY_ID','ITEMID'])['VALUENUM'].min()\r\n",
    "    min_threshold_high = min_threshold_high.reset_index()\r\n",
    "    min_threshold_high = min_threshold_high[['ICUSTAY_ID','VALUENUM']].rename(columns = {'VALUENUM':'THRESHOLD_HIGH_MIN'}).assign(ITEMID_VALUE=parameter.VALUE)\r\n",
    "\r\n",
    "    # For current parameter, compute maximium value of low threshold for all ICU stays\r\n",
    "    max_threshold_low = chartevents_cleaning_03_measurements_in_valid_range[\r\n",
    "        chartevents_cleaning_03_measurements_in_valid_range['ITEMID'] == parameter['THRESHOLD_LOW']\r\n",
    "        ].groupby(['ICUSTAY_ID','ITEMID'])['VALUENUM'].max()\r\n",
    "    max_threshold_low = max_threshold_low.reset_index()\r\n",
    "    max_threshold_low = max_threshold_low[['ICUSTAY_ID','VALUENUM']].rename(columns = {'VALUENUM':'THRESHOLD_LOW_MAX'}).assign(ITEMID_VALUE=parameter.VALUE)\r\n",
    "\r\n",
    "    # Append the results of the current parameter to the data frames for the overall results\r\n",
    "    min_threshold_high_per_icustay = min_threshold_high_per_icustay.append(min_threshold_high, ignore_index=True)\r\n",
    "    max_threshold_high_per_icustay = max_threshold_high_per_icustay.append(max_threshold_low, ignore_index=True)\r\n",
    "\r\n",
    "# Merge data frames\r\n",
    "threshold_min_max_per_icustay = min_threshold_high_per_icustay.merge(max_threshold_high_per_icustay, on=['ICUSTAY_ID','ITEMID_VALUE'])\r\n",
    "threshold_min_max_per_icustay = threshold_min_max_per_icustay[['ICUSTAY_ID', 'ITEMID_VALUE', 'THRESHOLD_HIGH_MIN', 'THRESHOLD_LOW_MAX']]\r\n",
    "\r\n",
    "display(threshold_min_max_per_icustay)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "       ICUSTAY_ID ITEMID_VALUE  THRESHOLD_HIGH_MIN  THRESHOLD_LOW_MAX\n",
       "0        200001.0       220045               120.0               60.0\n",
       "1        200010.0       220045               120.0               50.0\n",
       "2        200011.0       220045               120.0               55.0\n",
       "3        200016.0       220045               120.0               60.0\n",
       "4        200021.0       220045               120.0               50.0\n",
       "...           ...          ...                 ...                ...\n",
       "68878    299956.0       220277               100.0               90.0\n",
       "68879    299957.0       220277               100.0               92.0\n",
       "68880    299962.0       220277               100.0               92.0\n",
       "68881    299979.0       220277               100.0               93.0\n",
       "68882    299998.0       220277               100.0               90.0\n",
       "\n",
       "[68883 rows x 4 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "      <th>THRESHOLD_HIGH_MIN</th>\n",
       "      <th>THRESHOLD_LOW_MAX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200010.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200011.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200016.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200021.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68878</th>\n",
       "      <td>299956.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>299957.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>299962.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>299979.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>299998.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68883 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# Identify threshold swap candidates by comparing the minimum high threshold to the maximum low threshold for each ICU stay arameter combination.\r\n",
    "# If the minimum low threshold is below the maximum high threshold, the ICU stay parameter combination is considered for threshold swapping. \r\n",
    "threshold_min_max_per_icustay['CROSS'] = threshold_min_max_per_icustay['THRESHOLD_HIGH_MIN'] < threshold_min_max_per_icustay['THRESHOLD_LOW_MAX']\r\n",
    "threshold_swap_candidates = threshold_min_max_per_icustay[threshold_min_max_per_icustay['CROSS'] == True][['ICUSTAY_ID','ITEMID_VALUE']].reset_index(drop=True)\r\n",
    "display(threshold_swap_candidates)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "      ICUSTAY_ID ITEMID_VALUE\n",
       "0       200143.0       220045\n",
       "1       200153.0       220045\n",
       "2       200339.0       220045\n",
       "3       200550.0       220045\n",
       "4       200552.0       220045\n",
       "...          ...          ...\n",
       "1538    299387.0       220277\n",
       "1539    299725.0       220277\n",
       "1540    299742.0       220277\n",
       "1541    299847.0       220277\n",
       "1542    299889.0       220277\n",
       "\n",
       "[1543 rows x 2 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200143.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200153.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200339.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200550.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200552.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1538</th>\n",
       "      <td>299387.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1539</th>\n",
       "      <td>299725.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1540</th>\n",
       "      <td>299742.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1541</th>\n",
       "      <td>299847.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1542</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1543 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.2 Prepare data set for local threshold swap"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "# The threshold_swap_candidates data frame contains only the ITEMIDs of the vital sign values, not the ITEMIDs of the associated thresholds.\r\n",
    "# To facilitate the subsequent subsetting of the CHARTEVENTS data frame, auxiliary data frames are created with the threshold ITEMIDs.\r\n",
    "# The threshold ITEMIDs are combined into one data frame, which is then used to filter the CHARTEVENT data frame.\r\n",
    "# There is probably a smarter way to do this, but this was the quick way.\r\n",
    "\r\n",
    "import pandas as pd\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "# Create empty data frames to which will be appended during the loop\r\n",
    "itemid_threshold_high_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'ITEMID'])\r\n",
    "itemid_threshold_low_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'ITEMID'])\r\n",
    "\r\n",
    "for i, parameter in parameters.iterrows():\r\n",
    "\r\n",
    "    # For current parameter, create data frames with threshold ITEMIDs\r\n",
    "    itemid_threshold_high = threshold_swap_candidates[threshold_swap_candidates['ITEMID_VALUE'] == parameter['VALUE']][['ICUSTAY_ID']].assign(ITEMID=parameter.THRESHOLD_HIGH)\r\n",
    "    itemid_threshold_low = threshold_swap_candidates[threshold_swap_candidates['ITEMID_VALUE'] == parameter['VALUE']][['ICUSTAY_ID']].assign(ITEMID=parameter.THRESHOLD_LOW)\r\n",
    "\r\n",
    "    # Append the results of the current parameter to the data frames for the overall results\r\n",
    "    itemid_threshold_high_per_icustay = itemid_threshold_high_per_icustay.append(itemid_threshold_high, ignore_index=True)\r\n",
    "    itemid_threshold_low_per_icustay = itemid_threshold_low_per_icustay.append(itemid_threshold_low, ignore_index=True)\r\n",
    "\r\n",
    "# Merge data frames vertically\r\n",
    "threshold_swap_filter = pd.concat([itemid_threshold_high_per_icustay, itemid_threshold_low_per_icustay], axis= 0)\r\n",
    "\r\n",
    "# Sort to make it pretty (not important)\r\n",
    "threshold_swap_filter = threshold_swap_filter.sort_values(by=['ICUSTAY_ID','ITEMID']).reset_index(drop=True)\r\n",
    "\r\n",
    "display(threshold_swap_filter)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "      ICUSTAY_ID  ITEMID\n",
       "0       200061.0  223769\n",
       "1       200061.0  223770\n",
       "2       200075.0  223769\n",
       "3       200075.0  223770\n",
       "4       200143.0  220046\n",
       "...          ...     ...\n",
       "3081    299847.0  223770\n",
       "3082    299879.0  220046\n",
       "3083    299879.0  220047\n",
       "3084    299889.0  223769\n",
       "3085    299889.0  223770\n",
       "\n",
       "[3086 rows x 2 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200061.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200075.0</td>\n",
       "      <td>223769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200075.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200143.0</td>\n",
       "      <td>220046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3081</th>\n",
       "      <td>299847.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3082</th>\n",
       "      <td>299879.0</td>\n",
       "      <td>220046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3083</th>\n",
       "      <td>299879.0</td>\n",
       "      <td>220047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3084</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>223769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3085</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3086 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "# Filter the chartevents_cleaning_03_measurements_in_valid_range based on the threshold_swap_filter\r\n",
    "threshold_swap_data = pd.merge(chartevents_cleaning_03_measurements_in_valid_range,threshold_swap_filter)\r\n",
    "display(threshold_swap_data)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "           ROW_ID  SUBJECT_ID   HADM_ID  ICUSTAY_ID    ITEMID  \\\n",
       "0      22446631.0     74282.0  121149.0    200061.0  223769.0   \n",
       "1      22446678.0     74282.0  121149.0    200061.0  223769.0   \n",
       "2      22446831.0     74282.0  121149.0    200061.0  223769.0   \n",
       "3      22446923.0     74282.0  121149.0    200061.0  223769.0   \n",
       "4      22447074.0     74282.0  121149.0    200061.0  223769.0   \n",
       "...           ...         ...       ...         ...       ...   \n",
       "69997  12445279.0     54358.0  134569.0    299889.0  223770.0   \n",
       "69998  12445425.0     54358.0  134569.0    299889.0  223770.0   \n",
       "69999  12445599.0     54358.0  134569.0    299889.0  223770.0   \n",
       "70000  12445763.0     54358.0  134569.0    299889.0  223770.0   \n",
       "70001  12445901.0     54358.0  134569.0    299889.0  223770.0   \n",
       "\n",
       "                CHARTTIME           STORETIME     CGID VALUE  VALUENUM  \\\n",
       "0     2134-01-23 18:13:00 2134-01-23 18:13:00  15014.0   100     100.0   \n",
       "1     2134-01-23 20:05:00 2134-01-23 20:06:00  17609.0   100     100.0   \n",
       "2     2134-01-24 08:13:00 2134-01-24 09:13:00  15014.0   100     100.0   \n",
       "3     2134-01-24 20:34:00 2134-01-24 20:35:00  20117.0    35      35.0   \n",
       "4     2134-01-25 08:44:00 2134-01-25 08:44:00  14518.0   100     100.0   \n",
       "...                   ...                 ...      ...   ...       ...   \n",
       "69997 2172-06-06 08:00:00 2172-06-06 07:21:00  18959.0    92      92.0   \n",
       "69998 2172-06-06 20:00:00 2172-06-06 20:27:00  14576.0    92      92.0   \n",
       "69999 2172-06-07 08:00:00 2172-06-07 07:45:00  16139.0    92      92.0   \n",
       "70000 2172-06-07 20:00:00 2172-06-07 19:43:00  14576.0    92      92.0   \n",
       "70001 2172-06-08 07:40:00 2172-06-08 07:41:00  15659.0    92      92.0   \n",
       "\n",
       "      VALUEUOM  WARNING  ERROR RESULTSTATUS STOPPED  VALUENUM_CLEAN  \\\n",
       "0            %      0.0    0.0         None    None           100.0   \n",
       "1            %      0.0    0.0         None    None           100.0   \n",
       "2            %      0.0    0.0         None    None           100.0   \n",
       "3            %      0.0    0.0         None    None            35.0   \n",
       "4            %      0.0    0.0         None    None           100.0   \n",
       "...        ...      ...    ...          ...     ...             ...   \n",
       "69997        %      0.0    0.0         None    None            92.0   \n",
       "69998        %      0.0    0.0         None    None            92.0   \n",
       "69999        %      0.0    0.0         None    None            92.0   \n",
       "70000        %      0.0    0.0         None    None            92.0   \n",
       "70001        %      0.0    0.0         None    None            92.0   \n",
       "\n",
       "      CLEANING_FLAG  \n",
       "0              None  \n",
       "1              None  \n",
       "2              None  \n",
       "3              None  \n",
       "4              None  \n",
       "...             ...  \n",
       "69997          None  \n",
       "69998          None  \n",
       "69999          None  \n",
       "70000          None  \n",
       "70001          None  \n",
       "\n",
       "[70002 rows x 17 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>STORETIME</th>\n",
       "      <th>CGID</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>VALUENUM</th>\n",
       "      <th>VALUEUOM</th>\n",
       "      <th>WARNING</th>\n",
       "      <th>ERROR</th>\n",
       "      <th>RESULTSTATUS</th>\n",
       "      <th>STOPPED</th>\n",
       "      <th>VALUENUM_CLEAN</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22446631.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-23 18:13:00</td>\n",
       "      <td>2134-01-23 18:13:00</td>\n",
       "      <td>15014.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22446678.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-23 20:05:00</td>\n",
       "      <td>2134-01-23 20:06:00</td>\n",
       "      <td>17609.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22446831.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-24 08:13:00</td>\n",
       "      <td>2134-01-24 09:13:00</td>\n",
       "      <td>15014.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22446923.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-24 20:34:00</td>\n",
       "      <td>2134-01-24 20:35:00</td>\n",
       "      <td>20117.0</td>\n",
       "      <td>35</td>\n",
       "      <td>35.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>35.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22447074.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-25 08:44:00</td>\n",
       "      <td>2134-01-25 08:44:00</td>\n",
       "      <td>14518.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69997</th>\n",
       "      <td>12445279.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-06 08:00:00</td>\n",
       "      <td>2172-06-06 07:21:00</td>\n",
       "      <td>18959.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69998</th>\n",
       "      <td>12445425.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-06 20:00:00</td>\n",
       "      <td>2172-06-06 20:27:00</td>\n",
       "      <td>14576.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69999</th>\n",
       "      <td>12445599.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-07 08:00:00</td>\n",
       "      <td>2172-06-07 07:45:00</td>\n",
       "      <td>16139.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70000</th>\n",
       "      <td>12445763.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-07 20:00:00</td>\n",
       "      <td>2172-06-07 19:43:00</td>\n",
       "      <td>14576.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70001</th>\n",
       "      <td>12445901.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-08 07:40:00</td>\n",
       "      <td>2172-06-08 07:41:00</td>\n",
       "      <td>15659.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70002 rows × 17 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.3 Perform local threshold swap"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "import numpy as np\r\n",
    "import pandas as pd\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "icustays = threshold_swap_data.ICUSTAY_ID.unique()\r\n",
    "\r\n",
    "thresholds_fixed = pd.DataFrame(columns=['ICUSTAY_ID', 'ITEMID_VALUE', 'CHARTTIME', 'THRESHOLD_LOW_FIXED', 'THRESHOLD_HIGH_FIXED'])\r\n",
    "\r\n",
    "for icustay in icustays:\r\n",
    "    \r\n",
    "    for i, parameter in parameters.iterrows():\r\n",
    "        \r\n",
    "        threshold_high = threshold_swap_data[\r\n",
    "            (threshold_swap_data[\"ICUSTAY_ID\"] == icustay) & \r\n",
    "            (threshold_swap_data[\"ITEMID\"] == parameter['THRESHOLD_HIGH'])][\r\n",
    "            ['CHARTTIME','VALUENUM']\r\n",
    "            ].sort_values(by=['CHARTTIME']).rename(columns = {'VALUENUM':'THRESHOLD_HIGH'})\r\n",
    "\r\n",
    "        threshold_low = threshold_swap_data[\r\n",
    "            (threshold_swap_data[\"ICUSTAY_ID\"] == icustay) & \r\n",
    "            (threshold_swap_data[\"ITEMID\"] == parameter['THRESHOLD_LOW'])][\r\n",
    "            ['CHARTTIME','VALUENUM']\r\n",
    "            ].sort_values(by=['CHARTTIME']).rename(columns = {'VALUENUM':'THRESHOLD_LOW'})\r\n",
    "\r\n",
    "        thresholds_by_icustay_parameter_charttime = threshold_high.merge(threshold_low, on=['CHARTTIME']).assign(ICUSTAY_ID=icustay, ITEMID_VALUE=parameter.VALUE)\r\n",
    "        \r\n",
    "        # Create a new column that contains the chronologically following threshold value of the same type\r\n",
    "        thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH_NEXT'] = thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH'].shift(-1)\r\n",
    "        thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW_NEXT'] = thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW'].shift(-1)\r\n",
    "\r\n",
    "        thresholds_by_icustay_parameter_charttime['DIF_THRESHOLD_HIGH_NEXT'] = thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH_NEXT'] - thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH']\r\n",
    "        thresholds_by_icustay_parameter_charttime['DIF_THRESHOLD_LOW_NEXT'] = thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW_NEXT'] - thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW']\r\n",
    "        \r\n",
    "\r\n",
    "        thresholds_by_icustay_parameter_charttime.insert(loc=len(thresholds_by_icustay_parameter_charttime.columns), column='THRESHOLD_HIGH_FIXED', value=np.nan)\r\n",
    "        thresholds_by_icustay_parameter_charttime.insert(loc=len(thresholds_by_icustay_parameter_charttime.columns), column='THRESHOLD_LOW_FIXED', value=np.nan)\r\n",
    "\r\n",
    "        thresholds_by_icustay_parameter_charttime.loc[\r\n",
    "            (thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH'] < thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW']) &\r\n",
    "            (abs(thresholds_by_icustay_parameter_charttime['DIF_THRESHOLD_HIGH_NEXT']) == abs(thresholds_by_icustay_parameter_charttime['DIF_THRESHOLD_LOW_NEXT'])),\r\n",
    "            'THRESHOLD_HIGH_FIXED'] = thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW']\r\n",
    "\r\n",
    "        thresholds_by_icustay_parameter_charttime.loc[\r\n",
    "            (thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH'] < thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW']) &\r\n",
    "            (abs(thresholds_by_icustay_parameter_charttime['DIF_THRESHOLD_HIGH_NEXT']) == abs(thresholds_by_icustay_parameter_charttime['DIF_THRESHOLD_LOW_NEXT'])),\r\n",
    "            'THRESHOLD_LOW_FIXED'] = thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH']\r\n",
    "        \r\n",
    "        thresholds_by_icustay_parameter_charttime.dropna(inplace=True)\r\n",
    "        thresholds_fixed_for_icustayid_itemid = thresholds_by_icustay_parameter_charttime[['ICUSTAY_ID', 'ITEMID_VALUE', 'CHARTTIME', 'THRESHOLD_LOW_FIXED', 'THRESHOLD_HIGH_FIXED']]\r\n",
    "\r\n",
    "        thresholds_fixed = thresholds_fixed.append(thresholds_fixed_for_icustayid_itemid, ignore_index=True)\r\n",
    "\r\n",
    "display(thresholds_fixed)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "     ICUSTAY_ID ITEMID_VALUE           CHARTTIME  THRESHOLD_LOW_FIXED  \\\n",
       "0      200550.0       220277 2182-08-03 20:00:00                100.0   \n",
       "1      200552.0       220045 2149-10-06 08:00:00                 60.0   \n",
       "2      200552.0       220045 2149-10-06 16:00:00                 60.0   \n",
       "3      201220.0       220277 2106-04-29 08:00:00                 90.0   \n",
       "4      201360.0       220179 2149-01-17 19:52:00                 90.0   \n",
       "..          ...          ...                 ...                  ...   \n",
       "430    297549.0       220045 2165-12-27 12:00:00                 60.0   \n",
       "431    297714.0       220045 2198-10-04 08:00:00                 50.0   \n",
       "432    297767.0       220045 2165-04-26 08:00:00                 50.0   \n",
       "433    297767.0       220045 2165-04-26 16:00:00                 50.0   \n",
       "434    298534.0       220045 2145-12-06 09:21:00                 70.0   \n",
       "\n",
       "     THRESHOLD_HIGH_FIXED  \n",
       "0                   921.0  \n",
       "1                   150.0  \n",
       "2                   150.0  \n",
       "3                   100.0  \n",
       "4                   160.0  \n",
       "..                    ...  \n",
       "430                 120.0  \n",
       "431                 130.0  \n",
       "432                 120.0  \n",
       "433                 120.0  \n",
       "434                 185.0  \n",
       "\n",
       "[435 rows x 5 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>THRESHOLD_LOW_FIXED</th>\n",
       "      <th>THRESHOLD_HIGH_FIXED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200550.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2182-08-03 20:00:00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>921.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200552.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2149-10-06 08:00:00</td>\n",
       "      <td>60.0</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200552.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2149-10-06 16:00:00</td>\n",
       "      <td>60.0</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201220.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2106-04-29 08:00:00</td>\n",
       "      <td>90.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201360.0</td>\n",
       "      <td>220179</td>\n",
       "      <td>2149-01-17 19:52:00</td>\n",
       "      <td>90.0</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430</th>\n",
       "      <td>297549.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2165-12-27 12:00:00</td>\n",
       "      <td>60.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>297714.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2198-10-04 08:00:00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>432</th>\n",
       "      <td>297767.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2165-04-26 08:00:00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>297767.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2165-04-26 16:00:00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434</th>\n",
       "      <td>298534.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2145-12-06 09:21:00</td>\n",
       "      <td>70.0</td>\n",
       "      <td>185.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>435 rows × 5 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "# In the next step, data frames chartevents_cleaning_03_measurements_in_valid_range and thresholds_fixed are merged ...\r\n",
    "# ... to form a new data frame chartevents_cleaning_04_exact_threshold_swaps_reverted.\r\n",
    "# The latter will be extended/modified again in the next cleaning step and so on.\r\n",
    "chartevents_cleaning_04_exact_threshold_swaps_reverted = chartevents_cleaning_03_measurements_in_valid_range.copy()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "# Unnecessarily complicated step; could be simplified by better preparation of the data frame to be merged.\r\n",
    "# Needed because the thresholds_fixed data frame does contain the ITEMID_VALUE (i.e. the ITEMIDs of the vital parameters) but not the alarm threshold ITEMIDs.\r\n",
    "import pandas as pd\r\n",
    "import numpy as np\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "thresholds_fixed.insert(loc=len(thresholds_fixed.columns), column='ITEMID_THRESHOLD_HIGH', value=np.nan)\r\n",
    "thresholds_fixed.insert(loc=len(thresholds_fixed.columns), column='ITEMID_THRESHOLD_LOW', value=np.nan)\r\n",
    "\r\n",
    "for i, parameter in parameters.iterrows():\r\n",
    "\r\n",
    "    thresholds_fixed.loc[thresholds_fixed.ITEMID_VALUE == parameter['VALUE'], 'ITEMID_THRESHOLD_HIGH'] = parameter['THRESHOLD_HIGH']\r\n",
    "    thresholds_fixed.loc[thresholds_fixed.ITEMID_VALUE == parameter['VALUE'], 'ITEMID_THRESHOLD_LOW'] = parameter['THRESHOLD_LOW']\r\n",
    "\r\n",
    "display(thresholds_fixed)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "     ICUSTAY_ID ITEMID_VALUE           CHARTTIME  THRESHOLD_LOW_FIXED  \\\n",
       "0      200550.0       220277 2182-08-03 20:00:00                100.0   \n",
       "1      200552.0       220045 2149-10-06 08:00:00                 60.0   \n",
       "2      200552.0       220045 2149-10-06 16:00:00                 60.0   \n",
       "3      201220.0       220277 2106-04-29 08:00:00                 90.0   \n",
       "4      201360.0       220179 2149-01-17 19:52:00                 90.0   \n",
       "..          ...          ...                 ...                  ...   \n",
       "430    297549.0       220045 2165-12-27 12:00:00                 60.0   \n",
       "431    297714.0       220045 2198-10-04 08:00:00                 50.0   \n",
       "432    297767.0       220045 2165-04-26 08:00:00                 50.0   \n",
       "433    297767.0       220045 2165-04-26 16:00:00                 50.0   \n",
       "434    298534.0       220045 2145-12-06 09:21:00                 70.0   \n",
       "\n",
       "     THRESHOLD_HIGH_FIXED  ITEMID_THRESHOLD_HIGH  ITEMID_THRESHOLD_LOW  \n",
       "0                   921.0               223769.0              223770.0  \n",
       "1                   150.0               220046.0              220047.0  \n",
       "2                   150.0               220046.0              220047.0  \n",
       "3                   100.0               223769.0              223770.0  \n",
       "4                   160.0               223751.0              223752.0  \n",
       "..                    ...                    ...                   ...  \n",
       "430                 120.0               220046.0              220047.0  \n",
       "431                 130.0               220046.0              220047.0  \n",
       "432                 120.0               220046.0              220047.0  \n",
       "433                 120.0               220046.0              220047.0  \n",
       "434                 185.0               220046.0              220047.0  \n",
       "\n",
       "[435 rows x 7 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>THRESHOLD_LOW_FIXED</th>\n",
       "      <th>THRESHOLD_HIGH_FIXED</th>\n",
       "      <th>ITEMID_THRESHOLD_HIGH</th>\n",
       "      <th>ITEMID_THRESHOLD_LOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200550.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2182-08-03 20:00:00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>921.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>223770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200552.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2149-10-06 08:00:00</td>\n",
       "      <td>60.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200552.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2149-10-06 16:00:00</td>\n",
       "      <td>60.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201220.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2106-04-29 08:00:00</td>\n",
       "      <td>90.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>223770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201360.0</td>\n",
       "      <td>220179</td>\n",
       "      <td>2149-01-17 19:52:00</td>\n",
       "      <td>90.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>223751.0</td>\n",
       "      <td>223752.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430</th>\n",
       "      <td>297549.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2165-12-27 12:00:00</td>\n",
       "      <td>60.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>297714.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2198-10-04 08:00:00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>432</th>\n",
       "      <td>297767.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2165-04-26 08:00:00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>297767.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2165-04-26 16:00:00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434</th>\n",
       "      <td>298534.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2145-12-06 09:21:00</td>\n",
       "      <td>70.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>435 rows × 7 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "thresholds_fixed_high = thresholds_fixed[\r\n",
    "    ['ICUSTAY_ID','CHARTTIME','THRESHOLD_HIGH_FIXED','ITEMID_THRESHOLD_HIGH']\r\n",
    "    ].rename(columns = {'THRESHOLD_HIGH_FIXED':'VALUENUM_CLEAN', 'ITEMID_THRESHOLD_HIGH':'ITEMID'})\r\n",
    "thresholds_fixed_high = thresholds_fixed_high[['ICUSTAY_ID','ITEMID','CHARTTIME','VALUENUM_CLEAN']]\r\n",
    "\r\n",
    "thresholds_fixed_low = thresholds_fixed[\r\n",
    "    ['ICUSTAY_ID','CHARTTIME','THRESHOLD_LOW_FIXED','ITEMID_THRESHOLD_LOW']\r\n",
    "    ].rename(columns = {'THRESHOLD_LOW_FIXED':'VALUENUM_CLEAN', 'ITEMID_THRESHOLD_LOW':'ITEMID'})\r\n",
    "thresholds_fixed_low = thresholds_fixed_low[['ICUSTAY_ID','ITEMID','CHARTTIME','VALUENUM_CLEAN']]"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# Takes quite long; there is probably a better why to insert/replace the swapped threshold values in the VALUENUM_CLEAN column\r\n",
    "\r\n",
    "for i, row in thresholds_fixed_high.iterrows():\r\n",
    "\r\n",
    "    chartevents_cleaning_04_exact_threshold_swaps_reverted.loc[\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'VALUENUM_CLEAN'] = row['VALUENUM_CLEAN']\r\n",
    "\r\n",
    "    chartevents_cleaning_04_exact_threshold_swaps_reverted.loc[\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'CLEANING_FLAG'] = \"High threshold fixed by swap\"\r\n",
    "\r\n",
    "for i, row in thresholds_fixed_low.iterrows():\r\n",
    "\r\n",
    "    chartevents_cleaning_04_exact_threshold_swaps_reverted.loc[\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'VALUENUM_CLEAN'] = row['VALUENUM_CLEAN']\r\n",
    "\r\n",
    "    chartevents_cleaning_04_exact_threshold_swaps_reverted.loc[\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_04_exact_threshold_swaps_reverted.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'CLEANING_FLAG'] = \"Low threshold fixed by swap\"\r\n",
    "\r\n",
    "display(chartevents_cleaning_04_exact_threshold_swaps_reverted[\r\n",
    "    chartevents_cleaning_04_exact_threshold_swaps_reverted.CLEANING_FLAG.isin(['High threshold fixed by swap','Low threshold fixed by swap'])])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Save chartevents_cleaning_04_exact_threshold_swaps_reverted as parquet file\r\n",
    "chartevents_cleaning_04_exact_threshold_swaps_reverted.to_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_04_exact_threshold_swaps_reverted.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 5. Remove alarm threshold values outside clinically valid ranges\r\n",
    "\r\n",
    "* If the alarm threshold value is out of the respective clinically valid range set it to NaN, i.e. remove the data point, which reduces the sampling rate of the affected alarm threshold value series.\r\n",
    "* Currently, the valid alarm threshold value ranges are identical to the corresponding vital parameter value ranges, and they are the same for both threshold types (low and high)."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Read chartevents_cleaning_04_exact_threshold_swaps_reverted from parquet file to pandas data frame\r\n",
    "chartevents_cleaning_04_exact_threshold_swaps_reverted = pd.read_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_04_exact_threshold_swaps_reverted.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "# Clinically valid threshold ranges\r\n",
    "# Currently, identical to the valid vital parameter value ranges and the same for both threshold types (low and high).\r\n",
    "# It is conceivable to define the ranges for each threshold separately, although we currently lack the medical knowledge base for this.\r\n",
    "# Heart rate Alarm - High (220046): 0-350\r\n",
    "# Heart Rate Alarm - Low (220047): 0-350\r\n",
    "# Non-Invasive Blood Pressure Alarm - High (223751): 0-375\r\n",
    "# Non-Invasive Blood Pressure Alarm - Low (223752): 0-375\r\n",
    "# O2 Saturation Pulseoxymetry Alarm - High (223769): 0-100\r\n",
    "# O2 Saturation Pulseoxymetry Alarm - Low (223770): 0-100\r\n",
    "\r\n",
    "# The approach of alarm threshold value flagging is similar to the vital parameter value flagging performed above.\r\n",
    "# However, in this case not the original VALUENUM is used but the VALUENUM_CLEAN.\r\n",
    "# The reason for this is that alarm threshold values outside the valid ranges can also be among the swapped back alarm threshold values.\r\n",
    "# This is done to include the reverted exact threshold swaps (see above), which may contain thresholds outside the valid ranges.\r\n",
    "\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range = chartevents_cleaning_04_exact_threshold_swaps_reverted.copy()\r\n",
    "\r\n",
    "# Multiple cleaning rules may apply to a row. There are thresholds that are first swapped and then removed because they are outside the valid range.\r\n",
    "# Both steps are tracked in the CLEANING_FLAG column. Existing CLEANING_FLAG values such as 'High threshold fixed by swap' are not overwritten but extended, e.g.'High threshold fixed by swap; Above valid threshold range'.\r\n",
    "\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range.loc[\r\n",
    "    (\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([220046, 220047])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] < 0)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223751, 223752])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] < 0)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223769, 223770])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] < 0))\r\n",
    "    ) & (chartevents_cleaning_05_thresholds_in_valid_range['CLEANING_FLAG'].isin(['High threshold fixed by swap', 'Low threshold fixed by swap'])),\r\n",
    "    'CLEANING_FLAG'] = chartevents_cleaning_05_thresholds_in_valid_range['CLEANING_FLAG'].astype(str)+\"; Below valid threshold range\"\r\n",
    "\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range.loc[\r\n",
    "    (\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([220046, 220047])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] < 0)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223751, 223752])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] < 0)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223769, 223770])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] < 0))\r\n",
    "    ) & (chartevents_cleaning_05_thresholds_in_valid_range['CLEANING_FLAG'].isnull()),\r\n",
    "    'CLEANING_FLAG'] = \"Below valid threshold range\"\r\n",
    "\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range.loc[\r\n",
    "    (\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([220046, 220047])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] > 350)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223751, 223752])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] > 375)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223769, 223770])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] > 100))\r\n",
    "    ) & (chartevents_cleaning_05_thresholds_in_valid_range['CLEANING_FLAG'].isin(['High threshold fixed by swap', 'Low threshold fixed by swap'])),\r\n",
    "    'CLEANING_FLAG'] = chartevents_cleaning_05_thresholds_in_valid_range['CLEANING_FLAG'].astype(str)+\"; Above valid threshold range\"\r\n",
    "\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range.loc[\r\n",
    "    (\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([220046, 220047])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] > 350)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223751, 223752])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] > 375)) |\r\n",
    "        ((chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'].isin([223769, 223770])) & (chartevents_cleaning_05_thresholds_in_valid_range['VALUENUM_CLEAN'] > 100))\r\n",
    "    ) & (chartevents_cleaning_05_thresholds_in_valid_range['CLEANING_FLAG'].isnull()),\r\n",
    "    'CLEANING_FLAG'] = \"Above valid threshold range\"\r\n",
    "\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range.loc[\r\n",
    "    (chartevents_cleaning_05_thresholds_in_valid_range.CLEANING_FLAG.str.contains('Below valid threshold range')) |\r\n",
    "    (chartevents_cleaning_05_thresholds_in_valid_range.CLEANING_FLAG.str.contains('Above valid threshold range')),\r\n",
    "    'VALUENUM_CLEAN'] = None\r\n",
    "\r\n",
    "display(chartevents_cleaning_05_thresholds_in_valid_range[\r\n",
    "    (chartevents_cleaning_05_thresholds_in_valid_range.CLEANING_FLAG.str.contains('Below valid threshold range')) |\r\n",
    "    (chartevents_cleaning_05_thresholds_in_valid_range.CLEANING_FLAG.str.contains('Above valid threshold range'))\r\n",
    "    ])"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "             ROW_ID  SUBJECT_ID   HADM_ID  ICUSTAY_ID    ITEMID  \\\n",
       "24044    11355583.0     51839.0  150324.0    200339.0  220047.0   \n",
       "36338    19557273.0     67348.0  179548.0    200550.0  223769.0   \n",
       "36371    19557370.0     67348.0  179548.0    200550.0  223770.0   \n",
       "51670    20400719.0     69237.0  138300.0    200571.0  223769.0   \n",
       "64276    30900042.0     91563.0  155738.0    200737.0  223769.0   \n",
       "...             ...         ...       ...         ...       ...   \n",
       "7818959  22210210.0     73579.0  159680.0    299144.0  223769.0   \n",
       "7827184  27456991.0     84534.0  151051.0    299305.0  223751.0   \n",
       "7836742   9687038.0     48451.0  177942.0    299457.0  223769.0   \n",
       "7838628   4791244.0     31381.0  117080.0    299482.0  223769.0   \n",
       "7871074  15792075.0     58654.0  176082.0    299879.0  220047.0   \n",
       "\n",
       "                  CHARTTIME           STORETIME     CGID   VALUE  VALUENUM  \\\n",
       "24044   2192-02-23 08:13:00 2192-02-23 08:14:00  14597.0     500     500.0   \n",
       "36338   2182-08-03 20:00:00 2182-08-03 19:48:00  18305.0     100     100.0   \n",
       "36371   2182-08-04 05:14:00 2182-08-04 05:14:00  18305.0     921     921.0   \n",
       "51670   2150-12-23 08:00:00 2150-12-23 08:44:00  17457.0     199     199.0   \n",
       "64276   2179-08-18 08:00:00 2179-08-18 08:02:00  15083.0     101     101.0   \n",
       "...                     ...                 ...      ...     ...       ...   \n",
       "7818959 2153-10-22 08:15:00 2153-10-22 08:16:00  14772.0     120     120.0   \n",
       "7827184 2128-01-05 20:00:00 2128-01-05 20:37:00  18901.0    1600    1600.0   \n",
       "7836742 2186-10-07 08:25:00 2186-10-07 08:25:00  17582.0     160     160.0   \n",
       "7838628 2101-09-16 08:00:00 2101-09-16 07:56:00  14357.0  100185  100185.0   \n",
       "7871074 2174-08-17 00:07:00 2174-08-17 00:08:00  15803.0   60120   60120.0   \n",
       "\n",
       "        VALUEUOM  WARNING  ERROR RESULTSTATUS STOPPED  VALUENUM_CLEAN  \\\n",
       "24044        bpm      0.0    0.0         None    None             NaN   \n",
       "36338          %      0.0    0.0         None    None             NaN   \n",
       "36371          %      0.0    0.0         None    None             NaN   \n",
       "51670          %      0.0    0.0         None    None             NaN   \n",
       "64276          %      0.0    0.0         None    None             NaN   \n",
       "...          ...      ...    ...          ...     ...             ...   \n",
       "7818959        %      0.0    0.0         None    None             NaN   \n",
       "7827184     mmHg      0.0    0.0         None    None             NaN   \n",
       "7836742        %      0.0    0.0         None    None             NaN   \n",
       "7838628        %      0.0    0.0         None    None             NaN   \n",
       "7871074      bpm      0.0    0.0         None    None             NaN   \n",
       "\n",
       "                                             CLEANING_FLAG  \n",
       "24044                          Above valid threshold range  \n",
       "36338    High threshold fixed by swap; Above valid thre...  \n",
       "36371                          Above valid threshold range  \n",
       "51670                          Above valid threshold range  \n",
       "64276                          Above valid threshold range  \n",
       "...                                                    ...  \n",
       "7818959                        Above valid threshold range  \n",
       "7827184                        Above valid threshold range  \n",
       "7836742                        Above valid threshold range  \n",
       "7838628                        Above valid threshold range  \n",
       "7871074                        Above valid threshold range  \n",
       "\n",
       "[838 rows x 17 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>STORETIME</th>\n",
       "      <th>CGID</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>VALUENUM</th>\n",
       "      <th>VALUEUOM</th>\n",
       "      <th>WARNING</th>\n",
       "      <th>ERROR</th>\n",
       "      <th>RESULTSTATUS</th>\n",
       "      <th>STOPPED</th>\n",
       "      <th>VALUENUM_CLEAN</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24044</th>\n",
       "      <td>11355583.0</td>\n",
       "      <td>51839.0</td>\n",
       "      <td>150324.0</td>\n",
       "      <td>200339.0</td>\n",
       "      <td>220047.0</td>\n",
       "      <td>2192-02-23 08:13:00</td>\n",
       "      <td>2192-02-23 08:14:00</td>\n",
       "      <td>14597.0</td>\n",
       "      <td>500</td>\n",
       "      <td>500.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36338</th>\n",
       "      <td>19557273.0</td>\n",
       "      <td>67348.0</td>\n",
       "      <td>179548.0</td>\n",
       "      <td>200550.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2182-08-03 20:00:00</td>\n",
       "      <td>2182-08-03 19:48:00</td>\n",
       "      <td>18305.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>High threshold fixed by swap; Above valid thre...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36371</th>\n",
       "      <td>19557370.0</td>\n",
       "      <td>67348.0</td>\n",
       "      <td>179548.0</td>\n",
       "      <td>200550.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2182-08-04 05:14:00</td>\n",
       "      <td>2182-08-04 05:14:00</td>\n",
       "      <td>18305.0</td>\n",
       "      <td>921</td>\n",
       "      <td>921.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51670</th>\n",
       "      <td>20400719.0</td>\n",
       "      <td>69237.0</td>\n",
       "      <td>138300.0</td>\n",
       "      <td>200571.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2150-12-23 08:00:00</td>\n",
       "      <td>2150-12-23 08:44:00</td>\n",
       "      <td>17457.0</td>\n",
       "      <td>199</td>\n",
       "      <td>199.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64276</th>\n",
       "      <td>30900042.0</td>\n",
       "      <td>91563.0</td>\n",
       "      <td>155738.0</td>\n",
       "      <td>200737.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2179-08-18 08:00:00</td>\n",
       "      <td>2179-08-18 08:02:00</td>\n",
       "      <td>15083.0</td>\n",
       "      <td>101</td>\n",
       "      <td>101.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7818959</th>\n",
       "      <td>22210210.0</td>\n",
       "      <td>73579.0</td>\n",
       "      <td>159680.0</td>\n",
       "      <td>299144.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2153-10-22 08:15:00</td>\n",
       "      <td>2153-10-22 08:16:00</td>\n",
       "      <td>14772.0</td>\n",
       "      <td>120</td>\n",
       "      <td>120.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7827184</th>\n",
       "      <td>27456991.0</td>\n",
       "      <td>84534.0</td>\n",
       "      <td>151051.0</td>\n",
       "      <td>299305.0</td>\n",
       "      <td>223751.0</td>\n",
       "      <td>2128-01-05 20:00:00</td>\n",
       "      <td>2128-01-05 20:37:00</td>\n",
       "      <td>18901.0</td>\n",
       "      <td>1600</td>\n",
       "      <td>1600.0</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7836742</th>\n",
       "      <td>9687038.0</td>\n",
       "      <td>48451.0</td>\n",
       "      <td>177942.0</td>\n",
       "      <td>299457.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2186-10-07 08:25:00</td>\n",
       "      <td>2186-10-07 08:25:00</td>\n",
       "      <td>17582.0</td>\n",
       "      <td>160</td>\n",
       "      <td>160.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7838628</th>\n",
       "      <td>4791244.0</td>\n",
       "      <td>31381.0</td>\n",
       "      <td>117080.0</td>\n",
       "      <td>299482.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2101-09-16 08:00:00</td>\n",
       "      <td>2101-09-16 07:56:00</td>\n",
       "      <td>14357.0</td>\n",
       "      <td>100185</td>\n",
       "      <td>100185.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7871074</th>\n",
       "      <td>15792075.0</td>\n",
       "      <td>58654.0</td>\n",
       "      <td>176082.0</td>\n",
       "      <td>299879.0</td>\n",
       "      <td>220047.0</td>\n",
       "      <td>2174-08-17 00:07:00</td>\n",
       "      <td>2174-08-17 00:08:00</td>\n",
       "      <td>15803.0</td>\n",
       "      <td>60120</td>\n",
       "      <td>60120.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Above valid threshold range</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>838 rows × 17 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Save chartevents_cleaning_05_thresholds_in_valid_range as parquet file\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range.to_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_05_thresholds_in_valid_range.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 6. Remove threshold values due to local overlap\n",
    "\n",
    "Besides the exact alarm threshold swaps addressed above, there are also cases where the alarm threshold value series of type *high* and *low* overlap, but are not exactly swapped (e.g. the alarm threshold value of the type *high* is set unreasonably low and falls below the alarm threshold value of type *low*, while the latter seems to have a correct value). Such an overlap is usually not present over the entire period of the alarm threshold value series, but only at certain sections, i.e. locations. Accordingly, we call it a *local overlap*. These cases cannot be corrected by swapping. Also, it is not possible (without a lot of effort) to determine whether only one of the two thresholds is unreasonably high/low or both. Therefore, the threshold values of both types (high and low) are removed at those locations where they overlap. Hence, we call this step *Remove threshold values due to local overlap*.\n",
    "\n",
    "### 6.1 Identify candidates for threshold removal due to local overlap\n",
    "\n",
    "The approach to identifying candidates for threshold removal due to local overlap is similar to the candidate identification performed above in the context of threshold swapping. However, in this case not the original VALUENUM is used but the VALUENUM_CLEAN."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Read chartevents_cleaning_05_thresholds_in_valid_range from parquet file\r\n",
    "chartevents_cleaning_05_thresholds_in_valid_range = pd.read_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_05_thresholds_in_valid_range.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "import pandas as pd\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "# Create empty data frames which will be appended during the loop\r\n",
    "min_threshold_high_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'THRESHOLD_HIGH_MIN', 'ITEMID_VALUE'])\r\n",
    "max_threshold_high_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'THRESHOLD_LOW_MAX', 'ITEMID_VALUE'])\r\n",
    "\r\n",
    "for i, parameter in parameters.iterrows():\r\n",
    "\r\n",
    "    # For current parameter, compute minimum value of high threshold for all ICU stays\r\n",
    "    min_threshold_high = chartevents_cleaning_05_thresholds_in_valid_range[\r\n",
    "        chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'] == parameter['THRESHOLD_HIGH']\r\n",
    "        ].groupby(['ICUSTAY_ID','ITEMID'])['VALUENUM_CLEAN'].min()\r\n",
    "    min_threshold_high = min_threshold_high.reset_index()\r\n",
    "    min_threshold_high = min_threshold_high[['ICUSTAY_ID','VALUENUM_CLEAN']].rename(columns = {'VALUENUM_CLEAN':'THRESHOLD_HIGH_MIN'}).assign(ITEMID_VALUE=parameter.VALUE)\r\n",
    "\r\n",
    "    # For current parameter, compute maximium value of low threshold for all ICU stays\r\n",
    "    max_threshold_low = chartevents_cleaning_05_thresholds_in_valid_range[\r\n",
    "        chartevents_cleaning_05_thresholds_in_valid_range['ITEMID'] == parameter['THRESHOLD_LOW']\r\n",
    "        ].groupby(['ICUSTAY_ID','ITEMID'])['VALUENUM_CLEAN'].max()\r\n",
    "    max_threshold_low = max_threshold_low.reset_index()\r\n",
    "    max_threshold_low = max_threshold_low[['ICUSTAY_ID','VALUENUM_CLEAN']].rename(columns = {'VALUENUM_CLEAN':'THRESHOLD_LOW_MAX'}).assign(ITEMID_VALUE=parameter.VALUE)\r\n",
    "\r\n",
    "    # Append the results of the current parameter to the data frames for the overall results\r\n",
    "    min_threshold_high_per_icustay = min_threshold_high_per_icustay.append(min_threshold_high, ignore_index=True)\r\n",
    "    max_threshold_high_per_icustay = max_threshold_high_per_icustay.append(max_threshold_low, ignore_index=True)\r\n",
    "\r\n",
    "# Merge data frames\r\n",
    "threshold_min_max_per_icustay = min_threshold_high_per_icustay.merge(max_threshold_high_per_icustay, on=['ICUSTAY_ID','ITEMID_VALUE'])\r\n",
    "threshold_min_max_per_icustay = threshold_min_max_per_icustay[['ICUSTAY_ID', 'ITEMID_VALUE', 'THRESHOLD_HIGH_MIN', 'THRESHOLD_LOW_MAX']]\r\n",
    "\r\n",
    "display(threshold_min_max_per_icustay)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "       ICUSTAY_ID ITEMID_VALUE  THRESHOLD_HIGH_MIN  THRESHOLD_LOW_MAX\n",
       "0        200001.0       220045               120.0               60.0\n",
       "1        200010.0       220045               120.0               50.0\n",
       "2        200011.0       220045               120.0               55.0\n",
       "3        200016.0       220045               120.0               60.0\n",
       "4        200021.0       220045               120.0               50.0\n",
       "...           ...          ...                 ...                ...\n",
       "68878    299956.0       220277               100.0               90.0\n",
       "68879    299957.0       220277               100.0               92.0\n",
       "68880    299962.0       220277               100.0               92.0\n",
       "68881    299979.0       220277               100.0               93.0\n",
       "68882    299998.0       220277               100.0               90.0\n",
       "\n",
       "[68883 rows x 4 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "      <th>THRESHOLD_HIGH_MIN</th>\n",
       "      <th>THRESHOLD_LOW_MAX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200001.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200010.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200011.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200016.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200021.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>120.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68878</th>\n",
       "      <td>299956.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68879</th>\n",
       "      <td>299957.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68880</th>\n",
       "      <td>299962.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68881</th>\n",
       "      <td>299979.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68882</th>\n",
       "      <td>299998.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>100.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68883 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# Identify threshold removal candidates by comparing the minimum high threshold to the maximum low threshold for each ICU stay vital parameter combination.\r\n",
    "# If the minimum low threshold is below the maximum high threshold, the ICU stay vital parameter combination is considered for threshold removal. \r\n",
    "threshold_min_max_per_icustay['CROSS'] = threshold_min_max_per_icustay['THRESHOLD_HIGH_MIN'] < threshold_min_max_per_icustay['THRESHOLD_LOW_MAX']\r\n",
    "threshold_removal_candidates = threshold_min_max_per_icustay[threshold_min_max_per_icustay['CROSS'] == True][['ICUSTAY_ID','ITEMID_VALUE']].reset_index(drop=True)\r\n",
    "display(threshold_removal_candidates)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "      ICUSTAY_ID ITEMID_VALUE\n",
       "0       200143.0       220045\n",
       "1       200153.0       220045\n",
       "2       200550.0       220045\n",
       "3       200571.0       220045\n",
       "4       200696.0       220045\n",
       "...          ...          ...\n",
       "1101    299387.0       220277\n",
       "1102    299725.0       220277\n",
       "1103    299742.0       220277\n",
       "1104    299847.0       220277\n",
       "1105    299889.0       220277\n",
       "\n",
       "[1106 rows x 2 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200143.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200153.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200550.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200571.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200696.0</td>\n",
       "      <td>220045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>299387.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1102</th>\n",
       "      <td>299725.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1103</th>\n",
       "      <td>299742.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1104</th>\n",
       "      <td>299847.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>220277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1106 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 6.2 Prepare data set for threshold removal due to local overlap"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "# The threshold_removal_candidates data frame contains only the ITEMIDs of the vital parameter values, not the ITEMIDs of the associated thresholds.\r\n",
    "# To facilitate the subsequent subsetting, auxiliary data frames are created with the threshold ITEMIDs.\r\n",
    "# The threshold ITEMIDs are combined into one data frame, which is then used to filter the data frame.\r\n",
    "# There is probably a smarter way to do this, but this was fast enough.\r\n",
    "\r\n",
    "import pandas as pd\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "# Create empty data frames to which will be appended during the loop\r\n",
    "itemid_threshold_high_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'ITEMID'])\r\n",
    "itemid_threshold_low_per_icustay = pd.DataFrame(columns=['ICUSTAY_ID', 'ITEMID'])\r\n",
    "\r\n",
    "for i, parameter in parameters.iterrows():\r\n",
    "\r\n",
    "    # For current parameter, create data frames with threshold ITEMIDs\r\n",
    "    itemid_threshold_high = threshold_removal_candidates[threshold_removal_candidates['ITEMID_VALUE'] == parameter['VALUE']][['ICUSTAY_ID']].assign(ITEMID=parameter.THRESHOLD_HIGH)\r\n",
    "    itemid_threshold_low = threshold_removal_candidates[threshold_removal_candidates['ITEMID_VALUE'] == parameter['VALUE']][['ICUSTAY_ID']].assign(ITEMID=parameter.THRESHOLD_LOW)\r\n",
    "\r\n",
    "    # Append the results of the current parameter to the data frames for the overall results\r\n",
    "    itemid_threshold_high_per_icustay = itemid_threshold_high_per_icustay.append(itemid_threshold_high, ignore_index=True)\r\n",
    "    itemid_threshold_low_per_icustay = itemid_threshold_low_per_icustay.append(itemid_threshold_low, ignore_index=True)\r\n",
    "\r\n",
    "# Merge data frames vertically\r\n",
    "threshold_removal_filter = pd.concat([itemid_threshold_high_per_icustay, itemid_threshold_low_per_icustay], axis= 0)\r\n",
    "\r\n",
    "# Sort to make it pretty (not important)\r\n",
    "threshold_removal_filter = threshold_removal_filter.sort_values(by=['ICUSTAY_ID','ITEMID']).reset_index(drop=True)\r\n",
    "\r\n",
    "display(threshold_removal_filter)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "      ICUSTAY_ID  ITEMID\n",
       "0       200061.0  223769\n",
       "1       200061.0  223770\n",
       "2       200075.0  223769\n",
       "3       200075.0  223770\n",
       "4       200143.0  220046\n",
       "...          ...     ...\n",
       "2207    299742.0  223770\n",
       "2208    299847.0  223769\n",
       "2209    299847.0  223770\n",
       "2210    299889.0  223769\n",
       "2211    299889.0  223770\n",
       "\n",
       "[2212 rows x 2 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200061.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200075.0</td>\n",
       "      <td>223769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200075.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200143.0</td>\n",
       "      <td>220046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2207</th>\n",
       "      <td>299742.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2208</th>\n",
       "      <td>299847.0</td>\n",
       "      <td>223769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2209</th>\n",
       "      <td>299847.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2210</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>223769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2211</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2212 rows × 2 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "# Filter the chartevents_cleaning_05_thresholds_in_valid_range based on the threshold_removal_filter\r\n",
    "threshold_removal_data = pd.merge(chartevents_cleaning_05_thresholds_in_valid_range,threshold_removal_filter)\r\n",
    "display(threshold_removal_data)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "           ROW_ID  SUBJECT_ID   HADM_ID  ICUSTAY_ID    ITEMID  \\\n",
       "0      22446631.0     74282.0  121149.0    200061.0  223769.0   \n",
       "1      22446678.0     74282.0  121149.0    200061.0  223769.0   \n",
       "2      22446831.0     74282.0  121149.0    200061.0  223769.0   \n",
       "3      22446923.0     74282.0  121149.0    200061.0  223769.0   \n",
       "4      22447074.0     74282.0  121149.0    200061.0  223769.0   \n",
       "...           ...         ...       ...         ...       ...   \n",
       "48593  12445279.0     54358.0  134569.0    299889.0  223770.0   \n",
       "48594  12445425.0     54358.0  134569.0    299889.0  223770.0   \n",
       "48595  12445599.0     54358.0  134569.0    299889.0  223770.0   \n",
       "48596  12445763.0     54358.0  134569.0    299889.0  223770.0   \n",
       "48597  12445901.0     54358.0  134569.0    299889.0  223770.0   \n",
       "\n",
       "                CHARTTIME           STORETIME     CGID VALUE  VALUENUM  \\\n",
       "0     2134-01-23 18:13:00 2134-01-23 18:13:00  15014.0   100     100.0   \n",
       "1     2134-01-23 20:05:00 2134-01-23 20:06:00  17609.0   100     100.0   \n",
       "2     2134-01-24 08:13:00 2134-01-24 09:13:00  15014.0   100     100.0   \n",
       "3     2134-01-24 20:34:00 2134-01-24 20:35:00  20117.0    35      35.0   \n",
       "4     2134-01-25 08:44:00 2134-01-25 08:44:00  14518.0   100     100.0   \n",
       "...                   ...                 ...      ...   ...       ...   \n",
       "48593 2172-06-06 08:00:00 2172-06-06 07:21:00  18959.0    92      92.0   \n",
       "48594 2172-06-06 20:00:00 2172-06-06 20:27:00  14576.0    92      92.0   \n",
       "48595 2172-06-07 08:00:00 2172-06-07 07:45:00  16139.0    92      92.0   \n",
       "48596 2172-06-07 20:00:00 2172-06-07 19:43:00  14576.0    92      92.0   \n",
       "48597 2172-06-08 07:40:00 2172-06-08 07:41:00  15659.0    92      92.0   \n",
       "\n",
       "      VALUEUOM  WARNING  ERROR RESULTSTATUS STOPPED  VALUENUM_CLEAN  \\\n",
       "0            %      0.0    0.0         None    None           100.0   \n",
       "1            %      0.0    0.0         None    None           100.0   \n",
       "2            %      0.0    0.0         None    None           100.0   \n",
       "3            %      0.0    0.0         None    None            35.0   \n",
       "4            %      0.0    0.0         None    None           100.0   \n",
       "...        ...      ...    ...          ...     ...             ...   \n",
       "48593        %      0.0    0.0         None    None            92.0   \n",
       "48594        %      0.0    0.0         None    None            92.0   \n",
       "48595        %      0.0    0.0         None    None            92.0   \n",
       "48596        %      0.0    0.0         None    None            92.0   \n",
       "48597        %      0.0    0.0         None    None            92.0   \n",
       "\n",
       "      CLEANING_FLAG  \n",
       "0              None  \n",
       "1              None  \n",
       "2              None  \n",
       "3              None  \n",
       "4              None  \n",
       "...             ...  \n",
       "48593          None  \n",
       "48594          None  \n",
       "48595          None  \n",
       "48596          None  \n",
       "48597          None  \n",
       "\n",
       "[48598 rows x 17 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>STORETIME</th>\n",
       "      <th>CGID</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>VALUENUM</th>\n",
       "      <th>VALUEUOM</th>\n",
       "      <th>WARNING</th>\n",
       "      <th>ERROR</th>\n",
       "      <th>RESULTSTATUS</th>\n",
       "      <th>STOPPED</th>\n",
       "      <th>VALUENUM_CLEAN</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22446631.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-23 18:13:00</td>\n",
       "      <td>2134-01-23 18:13:00</td>\n",
       "      <td>15014.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22446678.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-23 20:05:00</td>\n",
       "      <td>2134-01-23 20:06:00</td>\n",
       "      <td>17609.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22446831.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-24 08:13:00</td>\n",
       "      <td>2134-01-24 09:13:00</td>\n",
       "      <td>15014.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22446923.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-24 20:34:00</td>\n",
       "      <td>2134-01-24 20:35:00</td>\n",
       "      <td>20117.0</td>\n",
       "      <td>35</td>\n",
       "      <td>35.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>35.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22447074.0</td>\n",
       "      <td>74282.0</td>\n",
       "      <td>121149.0</td>\n",
       "      <td>200061.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2134-01-25 08:44:00</td>\n",
       "      <td>2134-01-25 08:44:00</td>\n",
       "      <td>14518.0</td>\n",
       "      <td>100</td>\n",
       "      <td>100.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48593</th>\n",
       "      <td>12445279.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-06 08:00:00</td>\n",
       "      <td>2172-06-06 07:21:00</td>\n",
       "      <td>18959.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48594</th>\n",
       "      <td>12445425.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-06 20:00:00</td>\n",
       "      <td>2172-06-06 20:27:00</td>\n",
       "      <td>14576.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48595</th>\n",
       "      <td>12445599.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-07 08:00:00</td>\n",
       "      <td>2172-06-07 07:45:00</td>\n",
       "      <td>16139.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48596</th>\n",
       "      <td>12445763.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-07 20:00:00</td>\n",
       "      <td>2172-06-07 19:43:00</td>\n",
       "      <td>14576.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48597</th>\n",
       "      <td>12445901.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-08 07:40:00</td>\n",
       "      <td>2172-06-08 07:41:00</td>\n",
       "      <td>15659.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>92.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>48598 rows × 17 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 6.3 Perform threshold removal due to local overlap\n",
    "\n",
    "* Where alarm threshold values overlap, set both threshold values (high and low) to NaN for the affected time period, which reduces sampling rate of both alarm threshold value series (high and low).\n",
    "* This cleaning step may lead to incorrect assumptions due to later interpolation of alarm threshold values series."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "import numpy as np\r\n",
    "import pandas as pd\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "icustays = threshold_removal_data.ICUSTAY_ID.unique()\r\n",
    "\r\n",
    "thresholds_to_be_removed = pd.DataFrame(columns=['ICUSTAY_ID', 'ITEMID_VALUE', 'CHARTTIME'])\r\n",
    "\r\n",
    "for icustay in icustays:\r\n",
    "    \r\n",
    "    for i, parameter in parameters.iterrows():\r\n",
    "        \r\n",
    "        threshold_high = threshold_removal_data[\r\n",
    "            (threshold_removal_data[\"ICUSTAY_ID\"] == icustay) & \r\n",
    "            (threshold_removal_data[\"ITEMID\"] == parameter['THRESHOLD_HIGH'])][\r\n",
    "            ['CHARTTIME','VALUENUM_CLEAN']\r\n",
    "            ].sort_values(by=['CHARTTIME']).rename(columns = {'VALUENUM_CLEAN':'THRESHOLD_HIGH'})\r\n",
    "\r\n",
    "        threshold_low = threshold_removal_data[\r\n",
    "            (threshold_removal_data[\"ICUSTAY_ID\"] == icustay) & \r\n",
    "            (threshold_removal_data[\"ITEMID\"] == parameter['THRESHOLD_LOW'])][\r\n",
    "            ['CHARTTIME','VALUENUM_CLEAN']\r\n",
    "            ].sort_values(by=['CHARTTIME']).rename(columns = {'VALUENUM_CLEAN':'THRESHOLD_LOW'})\r\n",
    "\r\n",
    "        thresholds_by_icustay_parameter_charttime = threshold_high.merge(threshold_low, on=['CHARTTIME']).assign(ICUSTAY_ID=icustay, ITEMID_VALUE=parameter.VALUE)\r\n",
    "\r\n",
    "        thresholds_by_icustay_parameter_charttime.insert(loc=len(thresholds_by_icustay_parameter_charttime.columns), column='CLEANING_FLAG', value=np.nan)\r\n",
    "        thresholds_by_icustay_parameter_charttime.loc[\r\n",
    "            (thresholds_by_icustay_parameter_charttime['THRESHOLD_HIGH'] < thresholds_by_icustay_parameter_charttime['THRESHOLD_LOW']),\r\n",
    "            'CLEANING_FLAG'] = \"Threshold removal due to overlap\"\r\n",
    "        \r\n",
    "        thresholds_by_icustay_parameter_charttime.dropna(inplace=True)\r\n",
    "        thresholds_to_be_removed_for_icustayid_itemid = thresholds_by_icustay_parameter_charttime[['ICUSTAY_ID', 'ITEMID_VALUE', 'CHARTTIME', 'CLEANING_FLAG']]\r\n",
    "\r\n",
    "        thresholds_to_be_removed = thresholds_to_be_removed.append(thresholds_to_be_removed_for_icustayid_itemid, ignore_index=True)\r\n",
    "\r\n",
    "display(thresholds_to_be_removed)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "     ICUSTAY_ID ITEMID_VALUE           CHARTTIME  \\\n",
       "0      200075.0       220277 2159-09-23 01:35:00   \n",
       "1      200143.0       220045 2191-04-28 08:00:00   \n",
       "2      200153.0       220045 2194-08-16 19:55:00   \n",
       "3      200550.0       220045 2182-08-10 20:00:00   \n",
       "4      200571.0       220045 2150-12-19 17:24:00   \n",
       "..          ...          ...                 ...   \n",
       "769    299554.0       220179 2180-08-29 08:00:00   \n",
       "770    299725.0       220277 2172-09-18 20:45:00   \n",
       "771    299742.0       220277 2134-11-28 20:29:00   \n",
       "772    299847.0       220277 2200-11-11 20:13:00   \n",
       "773    299889.0       220277 2172-06-04 07:55:00   \n",
       "\n",
       "                        CLEANING_FLAG  \n",
       "0    Threshold removal due to overlap  \n",
       "1    Threshold removal due to overlap  \n",
       "2    Threshold removal due to overlap  \n",
       "3    Threshold removal due to overlap  \n",
       "4    Threshold removal due to overlap  \n",
       "..                                ...  \n",
       "769  Threshold removal due to overlap  \n",
       "770  Threshold removal due to overlap  \n",
       "771  Threshold removal due to overlap  \n",
       "772  Threshold removal due to overlap  \n",
       "773  Threshold removal due to overlap  \n",
       "\n",
       "[774 rows x 4 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200075.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2159-09-23 01:35:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200143.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2191-04-28 08:00:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200153.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2194-08-16 19:55:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200550.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2182-08-10 20:00:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200571.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2150-12-19 17:24:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>769</th>\n",
       "      <td>299554.0</td>\n",
       "      <td>220179</td>\n",
       "      <td>2180-08-29 08:00:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>770</th>\n",
       "      <td>299725.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2172-09-18 20:45:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>771</th>\n",
       "      <td>299742.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2134-11-28 20:29:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>772</th>\n",
       "      <td>299847.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2200-11-11 20:13:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>773</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2172-06-04 07:55:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>774 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "chartevents_cleaning_06_overlapping_thresholds_removed = chartevents_cleaning_05_thresholds_in_valid_range.copy()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "# Unnecessarily complicated step; could be simplified by better preparation of the data frame to be merged.\r\n",
    "# Needed because the thresholds_to_be_removed data frame does contain the ITEMID_VALUE (i.e. the ITEMIDs of the vital parameters) but not the alarm threshold ITEMIDs.\r\n",
    "import pandas as pd\r\n",
    "import numpy as np\r\n",
    "\r\n",
    "parameters = pd.DataFrame({\r\n",
    "    'LABEL':            ['HR',      'NBPs',     'SpO2'],\r\n",
    "    'VALUE':            [220045,    220179,     220277],\r\n",
    "    'THRESHOLD_HIGH':   [220046,    223751,     223769],\r\n",
    "    'THRESHOLD_LOW':    [220047,    223752,     223770]})\r\n",
    "\r\n",
    "thresholds_to_be_removed.insert(loc=len(thresholds_to_be_removed.columns), column='ITEMID_THRESHOLD_HIGH', value=np.nan)\r\n",
    "thresholds_to_be_removed.insert(loc=len(thresholds_to_be_removed.columns), column='ITEMID_THRESHOLD_LOW', value=np.nan)\r\n",
    "\r\n",
    "for i, parameter in parameters.iterrows():\r\n",
    "\r\n",
    "    thresholds_to_be_removed.loc[thresholds_to_be_removed.ITEMID_VALUE == parameter['VALUE'], 'ITEMID_THRESHOLD_HIGH'] = parameter['THRESHOLD_HIGH']\r\n",
    "    thresholds_to_be_removed.loc[thresholds_to_be_removed.ITEMID_VALUE == parameter['VALUE'], 'ITEMID_THRESHOLD_LOW'] = parameter['THRESHOLD_LOW']\r\n",
    "\r\n",
    "display(thresholds_to_be_removed)"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "     ICUSTAY_ID ITEMID_VALUE           CHARTTIME  \\\n",
       "0      200075.0       220277 2159-09-23 01:35:00   \n",
       "1      200143.0       220045 2191-04-28 08:00:00   \n",
       "2      200153.0       220045 2194-08-16 19:55:00   \n",
       "3      200550.0       220045 2182-08-10 20:00:00   \n",
       "4      200571.0       220045 2150-12-19 17:24:00   \n",
       "..          ...          ...                 ...   \n",
       "769    299554.0       220179 2180-08-29 08:00:00   \n",
       "770    299725.0       220277 2172-09-18 20:45:00   \n",
       "771    299742.0       220277 2134-11-28 20:29:00   \n",
       "772    299847.0       220277 2200-11-11 20:13:00   \n",
       "773    299889.0       220277 2172-06-04 07:55:00   \n",
       "\n",
       "                        CLEANING_FLAG  ITEMID_THRESHOLD_HIGH  \\\n",
       "0    Threshold removal due to overlap               223769.0   \n",
       "1    Threshold removal due to overlap               220046.0   \n",
       "2    Threshold removal due to overlap               220046.0   \n",
       "3    Threshold removal due to overlap               220046.0   \n",
       "4    Threshold removal due to overlap               220046.0   \n",
       "..                                ...                    ...   \n",
       "769  Threshold removal due to overlap               223751.0   \n",
       "770  Threshold removal due to overlap               223769.0   \n",
       "771  Threshold removal due to overlap               223769.0   \n",
       "772  Threshold removal due to overlap               223769.0   \n",
       "773  Threshold removal due to overlap               223769.0   \n",
       "\n",
       "     ITEMID_THRESHOLD_LOW  \n",
       "0                223770.0  \n",
       "1                220047.0  \n",
       "2                220047.0  \n",
       "3                220047.0  \n",
       "4                220047.0  \n",
       "..                    ...  \n",
       "769              223752.0  \n",
       "770              223770.0  \n",
       "771              223770.0  \n",
       "772              223770.0  \n",
       "773              223770.0  \n",
       "\n",
       "[774 rows x 6 columns]"
      ],
      "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>ICUSTAY_ID</th>\n",
       "      <th>ITEMID_VALUE</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "      <th>ITEMID_THRESHOLD_HIGH</th>\n",
       "      <th>ITEMID_THRESHOLD_LOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200075.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2159-09-23 01:35:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>223770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200143.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2191-04-28 08:00:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200153.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2194-08-16 19:55:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200550.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2182-08-10 20:00:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200571.0</td>\n",
       "      <td>220045</td>\n",
       "      <td>2150-12-19 17:24:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>220047.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>769</th>\n",
       "      <td>299554.0</td>\n",
       "      <td>220179</td>\n",
       "      <td>2180-08-29 08:00:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>223751.0</td>\n",
       "      <td>223752.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>770</th>\n",
       "      <td>299725.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2172-09-18 20:45:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>223770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>771</th>\n",
       "      <td>299742.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2134-11-28 20:29:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>223770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>772</th>\n",
       "      <td>299847.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2200-11-11 20:13:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>223770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>773</th>\n",
       "      <td>299889.0</td>\n",
       "      <td>220277</td>\n",
       "      <td>2172-06-04 07:55:00</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>223770.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>774 rows × 6 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "thresholds_to_be_removed_high = thresholds_to_be_removed[\r\n",
    "    ['ICUSTAY_ID','CHARTTIME','CLEANING_FLAG','ITEMID_THRESHOLD_HIGH']\r\n",
    "    ].rename(columns = {'ITEMID_THRESHOLD_HIGH':'ITEMID'})\r\n",
    "thresholds_to_be_removed_high = thresholds_to_be_removed_high[['ICUSTAY_ID','ITEMID','CHARTTIME','CLEANING_FLAG']]\r\n",
    "\r\n",
    "thresholds_to_be_removed_low = thresholds_to_be_removed[\r\n",
    "    ['ICUSTAY_ID','CHARTTIME','CLEANING_FLAG','ITEMID_THRESHOLD_LOW']\r\n",
    "    ].rename(columns = {'ITEMID_THRESHOLD_LOW':'ITEMID'})\r\n",
    "thresholds_to_be_removed_low = thresholds_to_be_removed_low[['ICUSTAY_ID','ITEMID','CHARTTIME','CLEANING_FLAG']]"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "# Takes quite long; there is probably a smarter way to insert/replace the threshold values in the VALUENUM_CLEAN column\r\n",
    "\r\n",
    "for i, row in thresholds_to_be_removed_high.iterrows():\r\n",
    "\r\n",
    "    chartevents_cleaning_06_overlapping_thresholds_removed.loc[\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'VALUENUM_CLEAN'] = None\r\n",
    "\r\n",
    "    chartevents_cleaning_06_overlapping_thresholds_removed.loc[\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'CLEANING_FLAG'] = \"Threshold removal due to overlap\"\r\n",
    "\r\n",
    "for i, row in thresholds_to_be_removed_low.iterrows():\r\n",
    "\r\n",
    "    chartevents_cleaning_06_overlapping_thresholds_removed.loc[\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'VALUENUM_CLEAN'] = None\r\n",
    "\r\n",
    "    chartevents_cleaning_06_overlapping_thresholds_removed.loc[\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ICUSTAY_ID == row['ICUSTAY_ID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.ITEMID == row['ITEMID']) &\r\n",
    "        (chartevents_cleaning_06_overlapping_thresholds_removed.CHARTTIME == row['CHARTTIME']), \r\n",
    "        'CLEANING_FLAG'] = \"Threshold removal due to overlap\"\r\n",
    "\r\n",
    "display(chartevents_cleaning_06_overlapping_thresholds_removed[\r\n",
    "    chartevents_cleaning_06_overlapping_thresholds_removed.CLEANING_FLAG == \"Threshold removal due to overlap\"])"
   ],
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": [
       "             ROW_ID  SUBJECT_ID   HADM_ID  ICUSTAY_ID    ITEMID  \\\n",
       "6265     19732601.0     67800.0  132255.0    200075.0  223769.0   \n",
       "6270     19732602.0     67800.0  132255.0    200075.0  223770.0   \n",
       "11593    31970192.0     94414.0  170244.0    200143.0  220046.0   \n",
       "11659    31970193.0     94414.0  170244.0    200143.0  220047.0   \n",
       "12008    20740822.0     69995.0  164810.0    200153.0  220046.0   \n",
       "...             ...         ...       ...         ...       ...   \n",
       "7862151   4154449.0     29660.0  186040.0    299742.0  223770.0   \n",
       "7868322   8783149.0     46439.0  132850.0    299847.0  223769.0   \n",
       "7868329   8783150.0     46439.0  132850.0    299847.0  223770.0   \n",
       "7872449  12444451.0     54358.0  134569.0    299889.0  223769.0   \n",
       "7872471  12444452.0     54358.0  134569.0    299889.0  223770.0   \n",
       "\n",
       "                  CHARTTIME           STORETIME     CGID VALUE  VALUENUM  \\\n",
       "6265    2159-09-23 01:35:00 2159-09-23 01:35:00  20030.0    10      10.0   \n",
       "6270    2159-09-23 01:35:00 2159-09-23 01:35:00  20030.0    90      90.0   \n",
       "11593   2191-04-28 08:00:00 2191-04-28 10:56:00  17140.0    50      50.0   \n",
       "11659   2191-04-28 08:00:00 2191-04-28 10:56:00  17140.0   120     120.0   \n",
       "12008   2194-08-16 19:55:00 2194-08-16 19:57:00  14772.0    60      60.0   \n",
       "...                     ...                 ...      ...   ...       ...   \n",
       "7862151 2134-11-28 20:29:00 2134-11-28 20:30:00  20053.0    85      85.0   \n",
       "7868322 2200-11-11 20:13:00 2200-11-11 20:14:00  17446.0     8       8.0   \n",
       "7868329 2200-11-11 20:13:00 2200-11-11 20:14:00  17446.0    85      85.0   \n",
       "7872449 2172-06-04 07:55:00 2172-06-04 07:55:00  15659.0    88      88.0   \n",
       "7872471 2172-06-04 07:55:00 2172-06-04 07:55:00  15659.0    92      92.0   \n",
       "\n",
       "        VALUEUOM  WARNING  ERROR RESULTSTATUS STOPPED  VALUENUM_CLEAN  \\\n",
       "6265           %      0.0    0.0         None    None             NaN   \n",
       "6270           %      0.0    0.0         None    None             NaN   \n",
       "11593        bpm      0.0    0.0         None    None             NaN   \n",
       "11659        bpm      0.0    0.0         None    None             NaN   \n",
       "12008        bpm      0.0    0.0         None    None             NaN   \n",
       "...          ...      ...    ...          ...     ...             ...   \n",
       "7862151        %      0.0    0.0         None    None             NaN   \n",
       "7868322        %      0.0    0.0         None    None             NaN   \n",
       "7868329        %      0.0    0.0         None    None             NaN   \n",
       "7872449        %      0.0    0.0         None    None             NaN   \n",
       "7872471        %      0.0    0.0         None    None             NaN   \n",
       "\n",
       "                            CLEANING_FLAG  \n",
       "6265     Threshold removal due to overlap  \n",
       "6270     Threshold removal due to overlap  \n",
       "11593    Threshold removal due to overlap  \n",
       "11659    Threshold removal due to overlap  \n",
       "12008    Threshold removal due to overlap  \n",
       "...                                   ...  \n",
       "7862151  Threshold removal due to overlap  \n",
       "7868322  Threshold removal due to overlap  \n",
       "7868329  Threshold removal due to overlap  \n",
       "7872449  Threshold removal due to overlap  \n",
       "7872471  Threshold removal due to overlap  \n",
       "\n",
       "[1548 rows x 17 columns]"
      ],
      "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>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ICUSTAY_ID</th>\n",
       "      <th>ITEMID</th>\n",
       "      <th>CHARTTIME</th>\n",
       "      <th>STORETIME</th>\n",
       "      <th>CGID</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>VALUENUM</th>\n",
       "      <th>VALUEUOM</th>\n",
       "      <th>WARNING</th>\n",
       "      <th>ERROR</th>\n",
       "      <th>RESULTSTATUS</th>\n",
       "      <th>STOPPED</th>\n",
       "      <th>VALUENUM_CLEAN</th>\n",
       "      <th>CLEANING_FLAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6265</th>\n",
       "      <td>19732601.0</td>\n",
       "      <td>67800.0</td>\n",
       "      <td>132255.0</td>\n",
       "      <td>200075.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2159-09-23 01:35:00</td>\n",
       "      <td>2159-09-23 01:35:00</td>\n",
       "      <td>20030.0</td>\n",
       "      <td>10</td>\n",
       "      <td>10.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6270</th>\n",
       "      <td>19732602.0</td>\n",
       "      <td>67800.0</td>\n",
       "      <td>132255.0</td>\n",
       "      <td>200075.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2159-09-23 01:35:00</td>\n",
       "      <td>2159-09-23 01:35:00</td>\n",
       "      <td>20030.0</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11593</th>\n",
       "      <td>31970192.0</td>\n",
       "      <td>94414.0</td>\n",
       "      <td>170244.0</td>\n",
       "      <td>200143.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>2191-04-28 08:00:00</td>\n",
       "      <td>2191-04-28 10:56:00</td>\n",
       "      <td>17140.0</td>\n",
       "      <td>50</td>\n",
       "      <td>50.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11659</th>\n",
       "      <td>31970193.0</td>\n",
       "      <td>94414.0</td>\n",
       "      <td>170244.0</td>\n",
       "      <td>200143.0</td>\n",
       "      <td>220047.0</td>\n",
       "      <td>2191-04-28 08:00:00</td>\n",
       "      <td>2191-04-28 10:56:00</td>\n",
       "      <td>17140.0</td>\n",
       "      <td>120</td>\n",
       "      <td>120.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12008</th>\n",
       "      <td>20740822.0</td>\n",
       "      <td>69995.0</td>\n",
       "      <td>164810.0</td>\n",
       "      <td>200153.0</td>\n",
       "      <td>220046.0</td>\n",
       "      <td>2194-08-16 19:55:00</td>\n",
       "      <td>2194-08-16 19:57:00</td>\n",
       "      <td>14772.0</td>\n",
       "      <td>60</td>\n",
       "      <td>60.0</td>\n",
       "      <td>bpm</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7862151</th>\n",
       "      <td>4154449.0</td>\n",
       "      <td>29660.0</td>\n",
       "      <td>186040.0</td>\n",
       "      <td>299742.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2134-11-28 20:29:00</td>\n",
       "      <td>2134-11-28 20:30:00</td>\n",
       "      <td>20053.0</td>\n",
       "      <td>85</td>\n",
       "      <td>85.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7868322</th>\n",
       "      <td>8783149.0</td>\n",
       "      <td>46439.0</td>\n",
       "      <td>132850.0</td>\n",
       "      <td>299847.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2200-11-11 20:13:00</td>\n",
       "      <td>2200-11-11 20:14:00</td>\n",
       "      <td>17446.0</td>\n",
       "      <td>8</td>\n",
       "      <td>8.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7868329</th>\n",
       "      <td>8783150.0</td>\n",
       "      <td>46439.0</td>\n",
       "      <td>132850.0</td>\n",
       "      <td>299847.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2200-11-11 20:13:00</td>\n",
       "      <td>2200-11-11 20:14:00</td>\n",
       "      <td>17446.0</td>\n",
       "      <td>85</td>\n",
       "      <td>85.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7872449</th>\n",
       "      <td>12444451.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223769.0</td>\n",
       "      <td>2172-06-04 07:55:00</td>\n",
       "      <td>2172-06-04 07:55:00</td>\n",
       "      <td>15659.0</td>\n",
       "      <td>88</td>\n",
       "      <td>88.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7872471</th>\n",
       "      <td>12444452.0</td>\n",
       "      <td>54358.0</td>\n",
       "      <td>134569.0</td>\n",
       "      <td>299889.0</td>\n",
       "      <td>223770.0</td>\n",
       "      <td>2172-06-04 07:55:00</td>\n",
       "      <td>2172-06-04 07:55:00</td>\n",
       "      <td>15659.0</td>\n",
       "      <td>92</td>\n",
       "      <td>92.0</td>\n",
       "      <td>%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Threshold removal due to overlap</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1548 rows × 17 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {}
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "# Save chartevents_cleaning_06_overlapping_thresholds_removed as parquet file\r\n",
    "chartevents_cleaning_06_overlapping_thresholds_removed.to_parquet(path_to_dir_chartevents_clean+'chartevents_cleaning_06_overlapping_thresholds_removed.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Save final chartevents_clean data frame"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "import pandas as pd\r\n",
    "import pyarrow as pa\r\n",
    "# For now, the data frame is stored twice with different names, because it may be that further cleanup steps follow later, so that chartevents_cleaning_06_overlapping_thresholds_removed is not the final chartevents_clean anymore.\r\n",
    "chartevents_clean = chartevents_cleaning_06_overlapping_thresholds_removed\r\n",
    "# Save chartevents_clean as parquet file\r\n",
    "path_to_dir_chartevents_clean = './reportdata/'\r\n",
    "chartevents_clean.to_parquet(path_to_dir_chartevents_clean+'chartevents_clean.parquet', engine='pyarrow')"
   ],
   "outputs": [],
   "metadata": {}
  }
 ]
}