NSQIP_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NSQIP Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notebook setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import pre-installed packages "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "from fastai.tabular.all import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Package versions: \n",
    "* *Python v. 3.7.7*<br>\n",
    "* *Pandas v. 1.1.4*<br>\n",
    "* *PyTorch v. 1.6.0*<br>\n",
    "* *Fastai v. 2.0.11*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set display options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = 20\n",
    "pd.options.display.max_columns = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set seed for reproducable results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "seed = 42\n",
    "\n",
    "# python RNG\n",
    "import random\n",
    "random.seed(seed)\n",
    "\n",
    "# pytorch RNGs\n",
    "import torch\n",
    "torch.manual_seed(seed)\n",
    "torch.backends.cudnn.deterministic = True\n",
    "if torch.cuda.is_available(): torch.cuda.manual_seed_all(seed)\n",
    "\n",
    "# numpy RNG\n",
    "import numpy as np\n",
    "np.random.seed(seed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a path to the NSQIP data folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_folder = Path(r'E:\\Data\\NSQIP')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data import and merging"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get and merge NSQIP data from 2013-2018"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a list of variables to import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "var_col = ['CaseID', 'SEX', 'RACE_NEW', 'ETHNICITY_HISPANIC', 'PRNCPTX', 'CPT', 'WORKRVU', 'Age', 'ANESTHES', 'SURGSPEC', 'ELECTSURG', 'EMERGNCY', 'WNDCLAS', 'ASACLAS', 'DIABETES', 'SMOKE', 'DYSPNEA', 'FNSTATUS2', 'VENTILAT', 'HXCOPD', 'ASCITES', 'HXCHF', 'HYPERMED', 'RENAFAIL', 'DIALYSIS', 'DISCANCR', 'WNDINF', 'STEROID', 'WTLOSS', 'BLEEDDIS', 'TRANSFUS', 'PRSEPIS', 'HEIGHT', 'WEIGHT', 'PRSODM', 'PRBUN', 'PRCREAT', 'PRALBUM', 'PRBILI', 'PRSGOT', 'PRALKPH', 'PRWBC', 'PRHCT', 'PRPLATE', 'PRPTT', 'PRINR', 'PRPT', 'DOpertoD', 'OUPNEUMO', 'CDARREST', 'CDMI', 'SUPINFEC', 'WNDINFD', 'ORGSPCSSI', 'URNINFEC', 'OTHDVT', 'PULEMBOL', 'RENAINSF', 'OPRENAFL', 'DEHIS', 'REINTUB', 'FAILWEAN', 'CNSCVA', 'OTHSYSEP', 'OTHBLEED', 'OTHSESHOCK', 'SEPSHOCKPATOS', 'OTHERCPT1', 'OTHERCPT2', 'OTHERCPT3', 'OTHERCPT4', 'OTHERCPT5', 'OTHERCPT6', 'OTHERCPT7', 'OTHERCPT8', 'OTHERCPT9', 'OTHERCPT10', 'CONCPT1', 'CONCPT2', 'CONCPT3', 'CONCPT4', 'CONCPT5', 'CONCPT6', 'CONCPT7', 'CONCPT8', 'CONCPT9', 'CONCPT10', 'MORTPROB', 'MORBPROB', 'INOUT', 'TRANST', 'DPRNA', 'DPRBUN', 'DPRCREAT', 'DPRALBUM', 'DPRBILI', 'DPRSGOT', 'DPRALKPH', 'DPRWBC', 'DPRHCT', 'DPRPLATE', 'DPRPTT', 'DPRPT', 'DPRINR', 'OperYR', 'OPTIME', 'HtoODay', 'SSSIPATOS', 'DSSIPATOS', 'OSSIPATOS', 'PNAPATOS', 'VENTPATOS', 'UTIPATOS', 'SEPSISPATOS', 'SEPSHOCKPATOS', 'OPTIME']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specify integer variables, float variables and set the rest as string variables. \n",
    "Create a dictionary with variables + types. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "int_variables= ['CaseID', 'OperYR', 'HEIGHT', 'WEIGHT', 'DPRNA', 'DPRBUN', 'DPRCREAT', 'DPRALBUM', 'DPRBILI', 'DPRSGOT', 'DPRALKPH', 'DPRWBC', 'DPRHCT', 'DPRPLATE','DPRPTT', 'DPRPT', 'DPRINR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "float_variables=['PRSODM', 'PRBUN','PRCREAT', 'PRALBUM', 'PRBILI', 'PRSGOT', 'PRALKPH', 'PRWBC', 'PRHCT', 'PRPLATE', 'PRPTT', 'PRINR', 'PRPT','WORKRVU', 'MORTPROB', 'MORBPROB', 'HtoODay', 'DOpertoD', 'OPTIME']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "types_dict = {} "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in int_variables:\n",
    "        types_dict[i] = int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in float_variables:\n",
    "        types_dict[i] = float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "types_dict.update({col: str for col in var_col if col not in types_dict})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import data from 2013 to 2018. Some column names in the 2012 dataset are formatted differently, so 2012 data is handled seperately. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "file_name = 'acs_nsqip_puf{}.txt'\n",
    "df_list = []\n",
    "for i in range(13,19):\n",
    "    df_list.append(pd.read_csv(data_folder/file_name.format(i), delimiter = '\\t', usecols=var_col, dtype=types_dict))\n",
    "    \n",
    "nsqip_2013_2018 = pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Get NSQIP data from 2012"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All column names are capitalized in data from 2012: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "var_col_2012=[x.upper() for x in var_col]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "int_variables_2012=[x.upper() for x in int_variables]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "float_variables_2012=[x.upper() for x in float_variables]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "types_dict_2012 = {} "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in int_variables_2012:\n",
    "        types_dict_2012[i] = int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in float_variables_2012:\n",
    "        types_dict_2012[i] = float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "types_dict_2012.update({col: str for col in var_col_2012 if col not in types_dict_2012})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip_2012 = pd.read_csv(data_folder/'acs_nsqip_puf12.txt', delimiter = '\\t', usecols=var_col_2012, dtype=types_dict_2012)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert column names to match 2013-2018 data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip_2012=nsqip_2012.rename(columns={\"AGE\": \"Age\", \"DOPERTOD\": \"DOpertoD\", \"CASEID\": \"CaseID\", 'OPERYR': 'OperYR',\n",
    "                                      'HTOODAY': 'HtoODay'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge NSQIP data from 2012 with 2013-2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip = pd.concat([nsqip_2012, nsqip_2013_2018])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save NSQIP as feather format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip.to_feather(data_folder/'nsqip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip = pd.read_feather(data_folder/'nsqip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replace missing values with NaN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace missing values with np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip[\"Age_cont\"] = nsqip.Age.replace('90+', '90').astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip=nsqip.replace(-99, np.nan)\n",
    "nsqip=nsqip.replace('Unknown', np.nan)\n",
    "nsqip=nsqip.replace('None assigned', np.nan)\n",
    "nsqip=nsqip.replace('Unknown/Not Reported', np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handle categorical variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Ordinal columns;* Columns that contain strings with a natural ordering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def ordinal(column, sizes):\n",
    "    nsqip[column] = nsqip[column].astype('category')\n",
    "    return nsqip[column].cat.set_categories(sizes, ordered=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal('DIABETES', ('NO','NON-INSULIN', 'INSULIN'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal('FNSTATUS2', ('Independent','Partially Dependent','Totally Dependent'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal('ASACLAS', ('1-No Disturb','2-Mild Disturb','3-Severe Disturb','4-Life Threat','5-Moribund'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal('PRSEPIS', ('None', 'SIRS', 'Sepsis', 'Septic Shock'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal('DYSPNEA', ('No', 'MODERATE EXERTION', 'AT REST'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "ordinal('WNDCLAS', ('1-Clean','2-Clean/Contaminated','3-Contaminated','4-Dirty/Infected'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handle dependent variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create one-hot encoded columns for each of the 19 dependent variables. Some of the patients who had DVT where coded as *\"DVT Requiring Therapy\"* while others where coded as *\"DVT Requiring Therap\"*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "def multi_label(df):\n",
    "    SSSI,DSSI,OSSI,WOUND,PNEUMONIA,UNPINT,PE,VENT48,PRI,ARF,UTI,STROKE,CAR,MI,DVT1,DVT2,SEPSIS,SEPSHOCK,BLEED, DECEASED  = [],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]\n",
    "    for row in df.itertuples():\n",
    "        SSSI.append((row.SUPINFEC == 'Superficial Incisional SSI') & (row.SSSIPATOS != 'Yes')) \n",
    "        DSSI.append((row.WNDINFD == 'Deep Incisional SSI') & (row.DSSIPATOS != 'Yes')) \n",
    "        OSSI.append((row.ORGSPCSSI == 'Organ/Space SSI') & (row.OSSIPATOS != 'Yes')) \n",
    "        WOUND.append(row.DEHIS == 'Wound Disruption')\n",
    "        PNEUMONIA.append((row.OUPNEUMO == 'Pneumonia') & (row.PNAPATOS != 'Yes'))\n",
    "        UNPINT.append(row.REINTUB == 'Unplanned Intubation') \n",
    "        PE.append(row.PULEMBOL == 'Pulmonary Embolism')\n",
    "        VENT48.append((row.FAILWEAN == 'On Ventilator greater than 48 Hours') & (row.VENTPATOS != 'Yes'))\n",
    "        PRI.append(row.RENAINSF == 'Progressive Renal Insufficiency')\n",
    "        ARF.append(row.OPRENAFL == 'Acute Renal Failure')\n",
    "        UTI.append((row.URNINFEC == 'Urinary Tract Infection') & (row.UTIPATOS != 'Yes'))\n",
    "        STROKE.append(row.CNSCVA == 'Stroke/CVA')\n",
    "        CAR.append(row.CDARREST == 'Cardiac Arrest Requiring CPR')\n",
    "        MI.append(row.CDMI == 'Myocardial Infarction')\n",
    "        DVT1.append(row.OTHDVT == 'DVT Requiring Therapy')\n",
    "        DVT2.append(row.OTHDVT == 'DVT Requiring Therap')\n",
    "        SEPSIS.append((row.OTHSYSEP == 'Sepsis') & (row.SEPSISPATOS != 'Yes'))\n",
    "        SEPSHOCK.append((row.OTHSESHOCK == 'Septic Shock') & (row.SEPSHOCKPATOS != 'Yes'))\n",
    "        BLEED.append(row.OTHBLEED == 'Transfusions/Intraop/Postop')\n",
    "        DECEASED.append(row.DOpertoD > -99)\n",
    "    df['SSSI'] = np.array(SSSI)\n",
    "    df['DSSI'] = np.array(DSSI)\n",
    "    df['OSSI'] = np.array(OSSI)\n",
    "    df['WOUND'] = np.array(WOUND)\n",
    "    df['PNEUMONIA'] = np.array(PNEUMONIA)\n",
    "    df['UNPINT'] = np.array(UNPINT)\n",
    "    df['PE'] = np.array(PE)\n",
    "    df['VENT48'] = np.array(VENT48)\n",
    "    df['PRI'] = np.array(PRI)\n",
    "    df['ARF'] = np.array(ARF)\n",
    "    df['UTI'] = np.array(UTI)\n",
    "    df['STROKE'] = np.array(STROKE)\n",
    "    df['CAR'] = np.array(CAR)\n",
    "    df['MI'] = np.array(MI)\n",
    "    df['DVT1'] = np.array(DVT1)\n",
    "    df['DVT2'] = np.array(DVT2)\n",
    "    df['SEPSIS'] = np.array(SEPSIS)\n",
    "    df['SEPSHOCK'] = np.array(SEPSHOCK)\n",
    "    df['BLEED'] = np.array(BLEED)\n",
    "    df['DECEASED'] = np.array(DECEASED)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip = multi_label(nsqip)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge DVT columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip['DVT'] = nsqip.DVT1 | nsqip.DVT2 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save cleaned dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "nsqip.to_feather(data_folder/'nsqip')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}