Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Almost Matching Exactly Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Algorithm Selection\n",
    "Select which algorithm you want to clean your dataset for. Note that DAME and FLAME have the same data requirements, as do MALTS and AHB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4aa9359a2ce54642a996900567e83a14",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "ToggleButtons(description='algorithm:', options=(('DAME / FLAME', 0), ('MALTS / AHB', 1)), tooltips=('This opt…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import ipywidgets as widgets\n",
    "algorithm = widgets.ToggleButtons(\n",
    "    options=[('DAME / FLAME', 0), ('MALTS / AHB', 1)],\n",
    "    description='algorithm:',\n",
    "    disabled=False,\n",
    "    tooltips=[\n",
    "        'This option will result in a cleaned dataset compatible with DAME or FLAME', \n",
    "        'This option will result in a cleaned dataset compatible with MALTS or AHB'\n",
    "    ]\n",
    ")\n",
    "display(algorithm)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import File\n",
    "This field only accepts file paths ending in .csv or .xpt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d432a40c474f485eb8bee729b726a9b9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Text(value='D:\\\\Downloads\\\\DEMO.XPT', description='file path:', placeholder='D:\\\\Downloads\\\\DEMO.XPT')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "filepath = widgets.Text(\n",
    "    value='D:\\Downloads\\DEMO.XPT',\n",
    "    placeholder='D:\\Downloads\\DEMO.XPT',\n",
    "    description='file path:',\n",
    "    disabled=False\n",
    ")\n",
    "display(filepath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select Columns of Interest\n",
    "In this field, you can select as many columns as you want. Be sure to include the treatment and outcome columns in your selection, as well as all covariates of interest, because all other columns will be removed from the cleaned dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "67070c37ae5947bea0739c0b1f27fdd7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(Label(value='Select all columns of interest:'), SelectMultiple(index=(0, 1, 2, 3, 4, 5, 6, 7, 8…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# Read XPT or CSV into dataframe\n",
    "if filepath.value.lower().endswith('xpt'):\n",
    "    df = pd.read_sas(filepath.value)\n",
    "elif filepath.value.lower().endswith('csv'):\n",
    "    df = pd.read_csv(filepth.value)\n",
    "else:\n",
    "    raise Exception('Unsupported file type')\n",
    "# Select columns of interest\n",
    "columns = list(df.columns)\n",
    "covariate_select = widgets.HBox([\n",
    "    widgets.Label(value='Select all columns of interest:'), \n",
    "    widgets.SelectMultiple(\n",
    "        options=columns,\n",
    "        value=columns,\n",
    "        disabled=False)\n",
    "])\n",
    "display(covariate_select)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Input General Information\n",
    "In this step, you will select which column contains information about whether a unit is treated or control and which column contains the outcome variable. You will also have the option to provide a set of comma-delimited missing indicators, which are values in the provided dataset which indicate missing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3a7ad8ebe837443bb959c1cf11a66232",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(Label(value='treatment indicator:', layout=Layout(height='auto', width='14rem')), Select(layout…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "aa02d52b16ab41e5b9d03298ac604fda",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(Label(value='outcome variable:', layout=Layout(height='auto', width='14rem')), Select(layout=La…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "deef8676706443bc9bdaf37f86dc7c78",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(Label(value='missing indicators:', layout=Layout(height='auto', width='14rem')), Text(value='',…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "54480baced9349da87b11048828eabbc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(Label(value='continuous covariates:', layout=Layout(height='auto', width='14rem')), SelectMulti…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from ipywidgets import Layout\n",
    "# Define layout\n",
    "label = Layout(width='14rem', height='auto')\n",
    "box = Layout(width='25rem', height='auto', margin='0px 2rem 0px 0px')\n",
    "desc = Layout(width='50rem', height='auto')\n",
    "# Retain columns of interest\n",
    "df = df[list(covariate_select.children[1].value)]\n",
    "columns = list(df.columns)\n",
    "# Select treatment indicator\n",
    "treatment_select = widgets.HBox([\n",
    "    widgets.Label(value='treatment indicator:', layout=label), \n",
    "    widgets.Select(\n",
    "        options=columns,\n",
    "        value=columns[0],\n",
    "        disabled=False,\n",
    "        layout=box),\n",
    "    widgets.VBox([\n",
    "        widgets.HBox([\n",
    "            widgets.Label(value='treatment condition:', layout=label),\n",
    "            widgets.Text(\n",
    "                value='x==1',\n",
    "                placeholder='x==1',\n",
    "                disabled=False,\n",
    "                layout=box)\n",
    "        ]),\n",
    "        widgets.HBox([\n",
    "            widgets.HTML(value=\"\"\"NOTE: The treatment condition must be a boolean \n",
    "            function of x where x is a unit's value in the column selected as the \n",
    "            treatment indicator. The function should return True if the unit is treated\n",
    "            and False otherwise. (e.g. x==1 or x>3)\"\"\", layout=desc),\n",
    "        ])\n",
    "    ])\n",
    "    \n",
    "])\n",
    "display(treatment_select)\n",
    "# Select outcome column\n",
    "outcome_select = widgets.HBox([\n",
    "    widgets.Label(value='outcome variable:', layout=label), \n",
    "    widgets.Select(\n",
    "        options=columns,\n",
    "        value=columns[0],\n",
    "        disabled=False,\n",
    "        layout=box)\n",
    "])\n",
    "display(outcome_select)\n",
    "# Enter missing indicator\n",
    "missing_indicator_input = widgets.HBox([\n",
    "    widgets.Label(value='missing indicators:', layout=label),\n",
    "    widgets.Text(\n",
    "        value='',\n",
    "        placeholder='Leave blank if not applicable',\n",
    "        disabled=False,\n",
    "        layout=box)\n",
    "])\n",
    "display(missing_indicator_input)\n",
    "# Indicate any known continuous covariates\n",
    "continuous_select = widgets.HBox([\n",
    "    widgets.Label(value='continuous covariates:', layout=label), \n",
    "    widgets.SelectMultiple(\n",
    "        options=columns,\n",
    "        value=[],\n",
    "        disabled=False,\n",
    "        layout=box),\n",
    "    widgets.HTML(value=\"\"\"OPTIONAL: Select any covariates which you know are continuous. There is no need\n",
    "    to select the outcome column, since that is already assumed to be continuous. Also note that the next\n",
    "    code segment will identify other potentially continuous covariates.\"\"\", layout=desc)\n",
    "])\n",
    "if algorithm.value == 0:\n",
    "    display(continuous_select)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning\n",
    "This code will clean the dataset according to your algorithm selection and general information inputs. If DAME/FLAME is selected, you may be prompted to handle potentially continuous covariates. The inputs will accept the following options:\n",
    "\n",
    "How do you want to proceed?\n",
    "- **ignore**: Leave the data for this column as it is in the original dataset\n",
    "- **ignore all**: Leave the data for this column and all continuous columns following this one as they are in the original dataset\n",
    "- **delete**: Delete this column from the cleaned dataset\n",
    "- **delete all**: Delete this column and all continous columns following this one\n",
    "- **bin**: Bin the data for this column\n",
    "- **bin all**: Bin the data for this column and all continuous columns following this one *using the same binning options*\n",
    "\n",
    "If you select **bin** or **bin all**, you will see the following prompt, which accepts these options:\n",
    "\n",
    "Number of bins, set of boundaries, or binning method:\n",
    "- If an integer *n* is provided, the data will be split into *n* equal-width bins\n",
    "- If a set of comma-delimited values are provided, those values will be used as boundaries for binning, inclduing the rightmost edge\n",
    "- If a string from the list below is provided, the bins will be computed with the selected method:\n",
    "    - **auto**: Maximum of the ‘sturges’ and ‘fd’ estimators. Provides good all around performance.\n",
    "\n",
    "    - **fd**: Freedman Diaconis Estimator - Robust (resilient to outliers) estimator that takes into account data variability and data size.\n",
    "\n",
    "    - **doane**: An improved version of Sturges’ estimator that works better with non-normal datasets.\n",
    "\n",
    "    - **scott**: Less robust estimator that that takes into account data variability and data size.\n",
    "\n",
    "    - **stone**: Estimator based on leave-one-out cross-validation estimate of the integrated squared error. Can be regarded as a generalization of Scott’s rule.\n",
    "\n",
    "    - **rice**: Estimator does not take variability into account, only data size. Commonly overestimates number of bins required.\n",
    "\n",
    "    - **sturges**: R’s default method, only accounts for data size. Only optimal for gaussian data and underestimates number of bins for large non-gaussian datasets.\n",
    "\n",
    "    - **sqrt**: Square root (of data size) estimator, used by Excel and other programs for its speed and simplicity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARNING: RIDAGEMN may be continuous\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "# Extract treatment column name and treatment condition\n",
    "treatment_column_name = treatment_select.children[1].value\n",
    "treatment_condition = treatment_select.children[2].children[0].children[1].value\n",
    "# Extract outcome column name\n",
    "outcome_column_name = outcome_select.children[1].value\n",
    "# Extract missing indicator\n",
    "missing_indicator = missing_indicator_input.children[1].value\n",
    "# Remove units with missingness in treatment or outcome columns\n",
    "if missing_indicator:\n",
    "    df.drop(df[df[treatment_column_name] == missing_indicator].index, inplace=True)\n",
    "    df.drop(df[df[outcome_column_name] == missing_indicator].index, inplace=True)\n",
    "# Apply treatment condition to treatment column\n",
    "df[treatment_column_name] = df[treatment_column_name].apply(lambda x: 1 if eval(treatment_condition) else 0)\n",
    "# Deal with any continuous data if algorithm selection requires categorical covariates\n",
    "if algorithm.value == 0:\n",
    "    option = ''\n",
    "    bins = ''\n",
    "    for col in [x for x in columns if x != treatment_column_name and x != outcome_column_name]:\n",
    "        # Determine if a covariate is likely to be continuous\n",
    "        if len(pd.unique(df[col])) > len(df[col]) / 10:\n",
    "            if option[-3:] != 'all':\n",
    "                print('WARNING: ' + str(col) + ' may be continuous')\n",
    "                option = input(\"How do you want to proceed? \")\n",
    "                if option not in ['ignore', 'ignore all', 'delete', 'delete all', 'bin', 'bin all']:\n",
    "                    print('ERROR: Invalid input')\n",
    "                    option = input(\"How do you want to proceed? \")\n",
    "            if option == 'delete' or option == 'delete all':\n",
    "                df = df.drop(columns=col)\n",
    "                print(str(col) + ' was deleted')\n",
    "            if option == 'bin' or (option == 'bin all' and bins == ''):\n",
    "                bins = input('Number of bins, set of boundaries, or binning method: ')\n",
    "                bins = list(bins.split(', '))\n",
    "                if bins[0].isnumeric():\n",
    "                    bins = list(map(int, bins))\n",
    "                if len(bins) == 1:\n",
    "                    bins = bins[0]\n",
    "            if option == 'bin' or option == 'bin all':\n",
    "                edges = np.histogram_bin_edges(df[col], bins, range=(df[col].min(), df[col].max()))\n",
    "                df[col] = pd.cut(list(df[col]), edges, labels=False)\n",
    "                print(str(col) + ' was binned')\n",
    "            if option == 'ignore' or option == 'ignore all':\n",
    "                print(str(col) + ' was ignored')\n",
    "    print('SUCCESS: Data cleaned for DAME/FLAME')\n",
    "else :\n",
    "    print('SUCCESS: Data cleaned for MALTS/AHB')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download Cleaned Dataset\n",
    "Provide a destination file path for the cleaned dataset that ends in .csv and download! You are now ready to use your selected algorithm."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "destination = widgets.HBox([\n",
    "    widgets.Label(value='destination file path:'),\n",
    "    widgets.Text(\n",
    "        value=filepath.value[:-4] + '_CLEANED.csv',\n",
    "        placeholder='D:\\Downloads\\DEMO_CLEANED.csv',\n",
    "        disabled=False)\n",
    "])\n",
    "display(destination)\n",
    "button = widgets.Button(\n",
    "    description='Download',\n",
    "    disabled=False,\n",
    "    tooltip='Download',\n",
    "    icon='download'\n",
    ")\n",
    "display(button)\n",
    "\n",
    "def download(button):\n",
    "    # Extract destination file path\n",
    "    destination_path = destination.children[1].value\n",
    "    # Download cleaned data\n",
    "    df.to_csv(destination_path, index=False)\n",
    "    print('Cleaned data downloaded to ' + destination_path)\n",
    "button.on_click(download)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}