pre_process.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 💾 The data\n",
    "\n",
    "The company stores the information you need in the following four tables. Some of the fields are anonymized to comply with privacy regulations.\n",
    "\n",
    "#### Doctors contains information on doctors. Each row represents one doctor.\n",
    "- \"DoctorID\" - is a unique identifier for each doctor.\n",
    "- \"Region\" - the current geographical region of the doctor.\n",
    "- \"Category\" - the type of doctor, either 'Specialist' or 'General Practitioner.'\n",
    "- \"Rank\" - is an internal ranking system. It is an ordered variable: The highest level is Ambassadors, followed by Titanium Plus, Titanium, Platinum Plus, Platinum, Gold Plus, Gold, Silver Plus, and the lowest level is Silver.\n",
    "- \"Incidence rate\"  and \"R rate\" - relate to the amount of re-work each doctor generates.\n",
    "- \"Satisfaction\" - measures doctors' satisfaction with the company.\n",
    "- \"Experience\" - relates to the doctor's experience with the company.\n",
    "- \"Purchases\" - purchases over the last year.\n",
    "\n",
    "#### Orders contains details on orders. Each row represents one order; a doctor can place multiple orders.\n",
    "- \"DoctorID\" - doctor id (matches the other tables).\n",
    "- \"OrderID\" - order identifier.\n",
    "- \"OrderNum\" - order number.\n",
    "- \"Conditions A through J\" - map the different settings of the devices in each order. Each order goes to an individual patient.\n",
    "\n",
    "#### Complaints collects information on doctor complaints.\n",
    "- \"DoctorID\" - doctor id (matches the other tables).\n",
    "- \"Complaint Type\" - the company's classification of the complaints.\n",
    "- \"Qty\" - number of complaints per complaint type per doctor.\n",
    "\n",
    "#### Instructions has information on whether the doctor includes special instructions on their orders.\n",
    "- \"DoctorID\" - doctor id (matches the other tables).\n",
    "- \"Instructions\" - 'Yes' when the doctor includes special instructions, 'No' when they do not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "data_directory = \".\\\\data\\\\\"\n",
    "\n",
    "# Load each CSV file into a pandas DataFrame\n",
    "complaints_df = pd.read_csv(os.path.join(data_directory, 'complaints.csv'))\n",
    "doctors_df = pd.read_csv(os.path.join(data_directory, 'doctors.csv'))\n",
    "instructions_df = pd.read_csv(os.path.join(data_directory, 'instructions.csv'))\n",
    "orders_df = pd.read_csv(os.path.join(data_directory, 'orders.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-Process Complaints Data\n",
    "- Complaints can have multiple entry for the same `DoctorID` since there can be various `Complaint Type`.\n",
    "- We transform the data to wide-data and make each `Complaint Type` as a feature.\n",
    "- We add a new feature `TotalComplaints` to sum up all the complaints type made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot the Complaints dataframe to transform 'Complaint Type' into columns\n",
    "complaints_mod = complaints_df.pivot_table(index='DoctorID', columns='Complaint Type', values='Qty', fill_value=0).reset_index()\n",
    "\n",
    "# Rename columns to reflect the transformation\n",
    "complaints_mod.columns = ['DoctorID'] + [f'Complaint Type - {col}' for col in complaints_mod.columns[1:]]\n",
    "\n",
    "# Create total complains column.\n",
    "complaint_columns = [col for col in complaints_mod.columns if 'Complaint Type' in col]\n",
    "complaints_mod['ComplaintTotal'] = complaints_mod[complaint_columns].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID with complaints vs Total ID: 0.6498855835240275\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DoctorID</th>\n",
       "      <th>Complaint Type - Correct</th>\n",
       "      <th>Complaint Type - Incorrect</th>\n",
       "      <th>Complaint Type - R&amp;R</th>\n",
       "      <th>Complaint Type - Specific</th>\n",
       "      <th>Complaint Type - Unknown</th>\n",
       "      <th>ComplaintTotal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAAEAH</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAAHCE</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AABDHC</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AABGAB</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AACCGA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  DoctorID  Complaint Type - Correct  Complaint Type - Incorrect  \\\n",
       "0   AAAEAH                       1.0                         0.0   \n",
       "1   AAAHCE                       1.0                         2.0   \n",
       "2   AABDHC                       0.0                         1.0   \n",
       "3   AABGAB                       3.0                         0.0   \n",
       "4   AACCGA                       0.0                         1.0   \n",
       "\n",
       "   Complaint Type - R&R  Complaint Type - Specific  Complaint Type - Unknown  \\\n",
       "0                   0.0                        0.0                       0.0   \n",
       "1                   0.0                        0.0                       1.0   \n",
       "2                   0.0                        0.0                       0.0   \n",
       "3                   0.0                        0.0                       1.0   \n",
       "4                   0.0                        0.0                       0.0   \n",
       "\n",
       "   ComplaintTotal  \n",
       "0             1.0  \n",
       "1             4.0  \n",
       "2             1.0  \n",
       "3             4.0  \n",
       "4             1.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(f\"ID with complaints vs Total ID: {complaints_mod.shape[0]/doctors_df.shape[0]}\")\n",
    "\n",
    "display(complaints_mod.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-Process Orders data\n",
    "- Orders can have multiple entry for the same `DoctorID` since an ID can make multiple Orders.\n",
    "- So we first create a table to count the number of orders made by each `DoctorID`.\n",
    "- We then create a unique spec order. For example if Spec1 = True, Spec2 = False, Spec3 = True, then the unique feature is Spec1-Spec2.\n",
    "- We then transform this list of orders with unique feature into a wide-data frame with the specs as new features (so that now we will have a unique `DoctorID`).\n",
    "- This are then combined witht the total orders table to create the final 'orders_mod' table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Create a column 'OrderCount' to count the number of orders for each unique 'DoctorID'\n",
    "orders_mod_1 = orders_df.groupby('DoctorID').size().reset_index(name='OrderCount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ibrah\\AppData\\Local\\Temp\\ipykernel_8584\\1331349628.py:5: FutureWarning: Downcasting object dtype arrays on .fillna, .ffill, .bfill is deprecated and will change in a future version. Call result.infer_objects(copy=False) instead. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`\n",
      "  orders_df[column] = orders_df[column].fillna(most_frequent_value)\n"
     ]
    }
   ],
   "source": [
    "# Fill missing values with the most frequent value in each column from 'Condition A' onwards\n",
    "conditions_columns = orders_df.columns[3:]\n",
    "for column in conditions_columns:\n",
    "    most_frequent_value = orders_df[column].mode()[0]\n",
    "    orders_df[column] = orders_df[column].fillna(most_frequent_value)\n",
    "\n",
    "# Create 'OrderSpec' based on conditions\n",
    "def create_order_spec(row):\n",
    "    conditions = [f\"{col[-1]}\" for col in conditions_columns[:-1] if row[col] == True]\n",
    "    conditions.append(row['Condition J'])\n",
    "    return '-'.join(conditions)\n",
    "\n",
    "orders_mod_2 = orders_df.copy()\n",
    "orders_mod_2['OrderSpec'] = orders_mod_2.apply(create_order_spec, axis=1)\n",
    "orders_mod_2 = orders_mod_2[['DoctorID', 'OrderID', 'OrderSpec']]\n",
    "\n",
    "# Pivot to transform 'OrderSpec' into columns\n",
    "orders_mod_2_pivot = orders_mod_2.pivot_table(index='DoctorID', columns='OrderSpec', aggfunc='size', fill_value=0).reset_index()\n",
    "\n",
    "# Merge Orders_mod_1 and Orders_mod_2 on 'DoctorID'\n",
    "orders_mod = orders_mod_1.merge(orders_mod_2_pivot, on='DoctorID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID with orders vs Total ID: 0.17391304347826086\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DoctorID</th>\n",
       "      <th>OrderCount</th>\n",
       "      <th>A-B-Before</th>\n",
       "      <th>A-B-C-D-Before</th>\n",
       "      <th>A-B-D-Before</th>\n",
       "      <th>A-B-H-Before</th>\n",
       "      <th>A-Before</th>\n",
       "      <th>A-C-H-Before</th>\n",
       "      <th>A-D-Before</th>\n",
       "      <th>A-D-G-Before</th>\n",
       "      <th>...</th>\n",
       "      <th>D-F-H-Before</th>\n",
       "      <th>D-G-Before</th>\n",
       "      <th>D-G-H-Before</th>\n",
       "      <th>D-H-Before</th>\n",
       "      <th>D-I-Before</th>\n",
       "      <th>G-Before</th>\n",
       "      <th>G-H-Before</th>\n",
       "      <th>G-I-Before</th>\n",
       "      <th>H-Before</th>\n",
       "      <th>I-Before</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAAEAH</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AABDHC</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AABGAB</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AADDIG</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAEIEG</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  DoctorID  OrderCount  A-B-Before  A-B-C-D-Before  A-B-D-Before  \\\n",
       "0   AAAEAH          19           0               0             0   \n",
       "1   AABDHC           1           0               0             0   \n",
       "2   AABGAB           4           0               0             0   \n",
       "3   AADDIG           2           0               0             0   \n",
       "4   AAEIEG           2           0               0             0   \n",
       "\n",
       "   A-B-H-Before  A-Before  A-C-H-Before  A-D-Before  A-D-G-Before  ...  \\\n",
       "0             0         3             0           0             0  ...   \n",
       "1             0         0             0           0             0  ...   \n",
       "2             0         0             0           0             0  ...   \n",
       "3             0         0             0           0             0  ...   \n",
       "4             0         0             0           0             0  ...   \n",
       "\n",
       "   D-F-H-Before  D-G-Before  D-G-H-Before  D-H-Before  D-I-Before  G-Before  \\\n",
       "0             0           0             0           0           0         0   \n",
       "1             0           0             0           0           0         0   \n",
       "2             0           0             0           0           0         0   \n",
       "3             0           0             0           0           0         0   \n",
       "4             0           0             0           0           0         0   \n",
       "\n",
       "   G-H-Before  G-I-Before  H-Before  I-Before  \n",
       "0           0           1         2         1  \n",
       "1           0           0         1         0  \n",
       "2           0           0         2         0  \n",
       "3           0           0         0         0  \n",
       "4           1           0         0         0  \n",
       "\n",
       "[5 rows x 44 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(f\"ID with orders vs Total ID: {orders_mod.shape[0]/doctors_df.shape[0]}\")\n",
    "display(orders_mod.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine all data into a single Master Dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for duplicate 'DoctorID' in each dataframe\n",
    "assert not doctors_df['DoctorID'].duplicated().any(), \"Duplicate DoctorID found in doctors_df\"\n",
    "assert not complaints_mod['DoctorID'].duplicated().any(), \"Duplicate DoctorID found in complaints_mod_df\"\n",
    "assert not instructions_df['DoctorID'].duplicated().any(), \"Duplicate DoctorID found in instructions_df\"\n",
    "assert not orders_mod['DoctorID'].duplicated().any(), \"Duplicate DoctorID found in orders_mod_df\"\n",
    "\n",
    "# Merge dataframes\n",
    "master_df = doctors_df.merge(complaints_mod, on='DoctorID', how='left')\n",
    "master_df = master_df.merge(instructions_df, on='DoctorID', how='left')\n",
    "master_df = master_df.merge(orders_mod, on='DoctorID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean-up the Master Dataframe for missing values:\n",
    "- Replace missing values in `Complaint Type` columns with value 0- since not all `DoctorID` will have this. (only 65% of ID make complaints)\n",
    "- Replace missing `Instruction` column with value 'No'. (only 17.6% of ID have instructions)\n",
    "- Replace missing `Orders` column with 0. (only 17.4% of ID have instructions)\n",
    "- Replace missing `Rank` values. (there are two missing values)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID with orders vs Total ID: 0.17620137299771166\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DoctorID</th>\n",
       "      <th>Region</th>\n",
       "      <th>Category</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Incidence rate</th>\n",
       "      <th>R rate</th>\n",
       "      <th>Satisfaction</th>\n",
       "      <th>Experience</th>\n",
       "      <th>Purchases</th>\n",
       "      <th>Complaint Type - Correct</th>\n",
       "      <th>...</th>\n",
       "      <th>D-F-H-Before</th>\n",
       "      <th>D-G-Before</th>\n",
       "      <th>D-G-H-Before</th>\n",
       "      <th>D-H-Before</th>\n",
       "      <th>D-I-Before</th>\n",
       "      <th>G-Before</th>\n",
       "      <th>G-H-Before</th>\n",
       "      <th>G-I-Before</th>\n",
       "      <th>H-Before</th>\n",
       "      <th>I-Before</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AHDCBA</td>\n",
       "      <td>4 15</td>\n",
       "      <td>Specialist</td>\n",
       "      <td>Ambassador</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.90</td>\n",
       "      <td>53.85</td>\n",
       "      <td>1.20</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABHAHF</td>\n",
       "      <td>1 8 T4</td>\n",
       "      <td>General Practitioner</td>\n",
       "      <td>Ambassador</td>\n",
       "      <td>37.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FDHFJ</td>\n",
       "      <td>1 9 T4</td>\n",
       "      <td>Specialist</td>\n",
       "      <td>Ambassador</td>\n",
       "      <td>33.0</td>\n",
       "      <td>1.53</td>\n",
       "      <td>--</td>\n",
       "      <td>0.00</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BJJHCA</td>\n",
       "      <td>1 10 T3</td>\n",
       "      <td>Specialist</td>\n",
       "      <td>Ambassador</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2.03</td>\n",
       "      <td>--</td>\n",
       "      <td>0.48</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FJBEA</td>\n",
       "      <td>1 14 T4</td>\n",
       "      <td>Specialist</td>\n",
       "      <td>Ambassador</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.96</td>\n",
       "      <td>76.79</td>\n",
       "      <td>0.75</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  DoctorID   Region              Category        Rank  Incidence rate  R rate  \\\n",
       "0   AHDCBA     4 15            Specialist  Ambassador            49.0    0.90   \n",
       "1   ABHAHF   1 8 T4  General Practitioner  Ambassador            37.0    0.00   \n",
       "2    FDHFJ   1 9 T4            Specialist  Ambassador            33.0    1.53   \n",
       "3   BJJHCA  1 10 T3            Specialist  Ambassador            28.0    2.03   \n",
       "4    FJBEA  1 14 T4            Specialist  Ambassador            23.0    0.96   \n",
       "\n",
       "  Satisfaction  Experience  Purchases  Complaint Type - Correct  ...  \\\n",
       "0        53.85        1.20       49.0                       0.0  ...   \n",
       "1       100.00        0.00       38.0                       0.0  ...   \n",
       "2           --        0.00       34.0                       0.0  ...   \n",
       "3           --        0.48       29.0                       0.0  ...   \n",
       "4        76.79        0.75       24.0                       0.0  ...   \n",
       "\n",
       "   D-F-H-Before  D-G-Before  D-G-H-Before  D-H-Before  D-I-Before G-Before  \\\n",
       "0           0.0         0.0           0.0         0.0         0.0      0.0   \n",
       "1           0.0         0.0           0.0         0.0         0.0      0.0   \n",
       "2           0.0         0.0           0.0         0.0         0.0      0.0   \n",
       "3           0.0         0.0           0.0         0.0         0.0      0.0   \n",
       "4           0.0         0.0           0.0         0.0         0.0      0.0   \n",
       "\n",
       "   G-H-Before  G-I-Before  H-Before  I-Before  \n",
       "0         0.0         0.0       0.0       0.0  \n",
       "1         0.0         0.0       0.0       0.0  \n",
       "2         0.0         0.0       0.0       0.0  \n",
       "3         0.0         0.0       0.0       0.0  \n",
       "4         0.0         0.0       0.0       0.0  \n",
       "\n",
       "[5 rows x 59 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get columns for missing values processing\n",
    "complaint_columns = [col for col in master_df.columns if 'Complaint Type' in col]\n",
    "orders_columns = master_df.columns[master_df.columns.get_loc('OrderCount'):]\n",
    "\n",
    "# Replace missing values\n",
    "master_df[complaint_columns] = master_df[complaint_columns].fillna(0)\n",
    "master_df['ComplaintTotal'] = master_df['ComplaintTotal'].fillna(0)\n",
    "master_df['Instructions'] = master_df['Instructions'].fillna('No')\n",
    "master_df[orders_columns] = master_df[orders_columns].fillna(0)\n",
    "\n",
    "# Find the most frequent value in the 'Rank' column\n",
    "most_frequent_rank = master_df['Rank'].mode()[0]\n",
    "\n",
    "# Replace missing values in the 'Rank' column with the most frequent value\n",
    "master_df['Rank'] = master_df['Rank'].fillna(most_frequent_rank)\n",
    "\n",
    "print(f\"ID with orders vs Total ID: {instructions_df.shape[0]/doctors_df.shape[0]}\")\n",
    "display(master_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Encode some features to be numerical\n",
    "- Replace the '--' value in `Satisfaction` column to be 0 (i.e neutral)\n",
    "- Label the `Region` column into numerical\n",
    "- Convert 'Yes' to 1 and 'No' to 0 in the `Instructions` column.\n",
    "- Encode the Rank label based on their hierarchy :  Ambassadors >  Titanium Plus > Titanium > Platinum Plus > Platinum > Gold Plus > Gold > Silver Plus > and the lowest level Silver."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace '--' with 0 in the 'Satisfaction' column and convert to numeric\n",
    "master_df['Satisfaction'] = master_df['Satisfaction'].replace('--', 0).astype(float)\n",
    "\n",
    "# Label encode the 'Region' column\n",
    "label_encoder = LabelEncoder()\n",
    "master_df['Region'] = label_encoder.fit_transform(master_df['Region'])\n",
    "\n",
    "# Convert 'Yes' to 1 and 'No' to 0 in the 'Instructions' column\n",
    "master_df['Instructions'] = master_df['Instructions'].map({'Yes': 1, 'No': 0})\n",
    "\n",
    "# Define the ranking order\n",
    "rank_order = {\n",
    "    'Ambassador': 9,\n",
    "    'Titanium Plus': 8,\n",
    "    'Titanium': 7,\n",
    "    'Platinum Plus': 6,\n",
    "    'Platinum': 5,\n",
    "    'Gold Plus': 4,\n",
    "    'Gold': 3,\n",
    "    'Silver Plus': 2,\n",
    "    'Silver': 1\n",
    "}\n",
    "\n",
    "# Encode the 'Rank' column\n",
    "master_df['Rank'] = master_df['Rank'].map(rank_order)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Optional Transformation for Analysis\n",
    "- To make an effective clustering, we will group the purchases into three category of \"Low\", \"Medium\" and \"High\"\n",
    "- Additionally to simplyfiy the satisfaction cateogy into Negative, Neutral/Unrate, Positive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Categorize Purchases into Low, Medium, High using quantiles\n",
    "master_df['Purchases_Category'] = pd.qcut(master_df['Purchases'], q=3, labels=['Low', 'Medium', 'High'])\n",
    "\n",
    "# Categorize Satisfaction into Negative, Neutral, Positive\n",
    "def categorize_satisfaction(value):\n",
    "    if value < 0:\n",
    "        return 'Negative'\n",
    "    elif value == 0:\n",
    "        return 'Neutral'\n",
    "    else:\n",
    "        return 'Positive'\n",
    "\n",
    "master_df['Satisfaction_Category'] = master_df['Satisfaction'].apply(categorize_satisfaction)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}