2. data_transformation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# KOL Data Standardization - Step 2 (Data Transformation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "KOL Data Standardization - Step 2 (Load the Data from landing area and apply some transformations and business logic to it.)\n",
    "\n",
    "This module represents the second step in KOL Data Standardization process where we load the landing area data and transform it using the following business rules:\n",
    "- Merge the data of same KOLs (data points with same mdm_id) and ensure that the latest batch data is updated for all columns except degree and speciality.\n",
    "- Since, a KOL can have multiple degrees and multiple specialities, concat these datapoints across the landing stage batches.\n",
    "- Based on the master tables, apply name-id mappings to degree, speciality, and profile status.\n",
    "- Handle null values on other columns\n",
    "The above transformations will ensure data consistency and will allow us to prepare the staging area before loading it into the final reporting layer database.\n",
    "\"\"\"\n",
    "\n",
    "import pandas as pd\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Master Tables Path - Used for business logic implementation\n",
    "\n",
    "SPECIALITY_MASTER_PATH = \"master_tables/speciality_master.csv\"\n",
    "PROFILE_STATUS_MATER_PATH = \"master_tables/profile_status_master.csv\"\n",
    "DEGREE_MASTER_PATH = \"master_tables/degree_master.csv\"\n",
    "LANDING_AREA_FILE_PATH = \"output_store/landing_area.xlsx\"\n",
    "STAGING_AREA_FILE_PATH = \"output_store/staging_area.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_master_tables():\n",
    "    \"\"\"Utility Function to fetch all the master tables for the KOL data\"\"\"\n",
    "    degree_master = pd.read_csv(DEGREE_MASTER_PATH)\n",
    "    degree_master = degree_master.set_index(\"degree\").to_dict()[\"id\"]\n",
    "\n",
    "    speciality_master = pd.read_csv(SPECIALITY_MASTER_PATH)\n",
    "    speciality_master = speciality_master.set_index(\"speciality\").to_dict()[\"id\"]\n",
    "\n",
    "    profile_master = pd.read_csv(PROFILE_STATUS_MATER_PATH)\n",
    "    profile_master = profile_master.set_index(\"id\").to_dict()[\"profile_status\"]\n",
    "\n",
    "    return degree_master, speciality_master, profile_master\n",
    "\n",
    "\n",
    "def speciality_mapper(speciality_data, speciality_master):\n",
    "    \"\"\"Utility Function to map specialities of a KOL with their respective ids\"\"\"\n",
    "    speciality_data = str(speciality_data).strip()\n",
    "    if len(speciality_data) != 0:\n",
    "        speciality_ids = [\n",
    "            str(speciality_master.get(x.strip())) for x in speciality_data.split(\",\")\n",
    "        ]\n",
    "        return \",\".join(sorted(speciality_ids))\n",
    "    return \"0\"\n",
    "\n",
    "\n",
    "def degree_mapper(degree_data, degree_master):\n",
    "    \"\"\"Utility Function to map degrees of a KOL with their respective ids\"\"\"\n",
    "    degree_data = str(degree_data).strip()\n",
    "    if len(degree_data) != 0:\n",
    "        degree_ids = [str(degree_master.get(x.strip())) for x in degree_data.split(\",\")]\n",
    "        return \",\".join(sorted(degree_ids))\n",
    "    return \"0\"\n",
    "\n",
    "\n",
    "def prepare_staging_area():\n",
    "    \"\"\"Main Data Transformation function to transform data and make it ready for the staging area.\"\"\"\n",
    "    print(\"TASK: Loading the Landing Area Dataframe\")\n",
    "    landing_df = pd.read_excel(LANDING_AREA_FILE_PATH)\n",
    "    landing_df = landing_df.sort_values([\"mdm_id\", \"batch_id\"]).reset_index(drop=True)\n",
    "    print(\"TASK: Successfully loaded the Landing Area Dataframe\")\n",
    "    display(landing_df.head())\n",
    "\n",
    "    # Merging Data of the same KOL (same mdm_id) based on batches and below business logic\n",
    "    print(\n",
    "        \"\\nTASK (Transformation Process 1): Merging the data from different batches in landing area for the same KOLs based on some business logic.\"\n",
    "    )\n",
    "    staging_df = (\n",
    "        landing_df.groupby(\"mdm_id\")\n",
    "        .agg(\n",
    "            {\n",
    "                \"first_name\": \"last\",  # take the last non-null value among the batches\n",
    "                \"last_name\": \"last\",  # take the last non-null value among the batches\n",
    "                \"age\": \"last\",  # take the last non-null value among the batches\n",
    "                \"city\": \"last\",  # take the last non-null value among the batches\n",
    "                \"state\": \"last\",  # take the last non-null value among the batches\n",
    "                \"profile_status\": \"last\",  # take the last non-null value among the batches\n",
    "                \"speciality\": lambda x: \", \".join(\n",
    "                    x.dropna().unique()\n",
    "                ),  # combine unique specialities among the batches\n",
    "                \"degree\": lambda x: \", \".join(\n",
    "                    x.dropna().unique()\n",
    "                ),  # combine unique degrees among the batches\n",
    "                \"batch_id\": \"last\",  # take the latest batch_id\n",
    "            }\n",
    "        )\n",
    "        .reset_index()\n",
    "    )\n",
    "    staging_df[[\"last_name\", \"city\", \"state\"]] = staging_df[\n",
    "        [\"last_name\", \"city\", \"state\"]\n",
    "    ].fillna(\"-\")\n",
    "    staging_df[\"age\"] = staging_df[\"age\"].fillna(-1).astype(\"int\")\n",
    "    print(\"TASK: Succcessfully implemented the merging logic\")\n",
    "    display(staging_df.head())\n",
    "\n",
    "    print(\n",
    "        \"\\nTASK (Transformation Process 2): Mapping Specialities, Degrees, and Profiling status of various KOLs based on master tables\"\n",
    "    )\n",
    "    degree_master, speciality_master, profile_master = get_master_tables()\n",
    "    staging_df[\"speciality\"] = staging_df[\"speciality\"].apply(\n",
    "        lambda x: speciality_mapper(x, speciality_master)\n",
    "    )\n",
    "    staging_df[\"degree\"] = staging_df[\"degree\"].apply(\n",
    "        lambda x: degree_mapper(x, degree_master)\n",
    "    )\n",
    "    staging_df[\"profile_status\"] = (\n",
    "        staging_df[\"profile_status\"].fillna(0).astype(\"int\").apply(profile_master.get)\n",
    "    )\n",
    "    print(\"TASK: Succcessfully prepared the Staging area dataframe\")\n",
    "    display(staging_df.head())\n",
    "\n",
    "    return staging_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting the Data Transformation Process\n",
      "\n",
      "\n",
      "TASK: Loading the Landing Area Dataframe\n",
      "TASK: Successfully loaded the Landing Area Dataframe\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>mdm_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>profile_status</th>\n",
       "      <th>speciality</th>\n",
       "      <th>degree</th>\n",
       "      <th>batch_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>Justin</td>\n",
       "      <td>Davenport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Laurenport</td>\n",
       "      <td>Washington</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dermatopharmacology</td>\n",
       "      <td>PHD,BHMS,MD</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>Jared</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Forensic Pathology,Pruritus,Emergency Medicine...</td>\n",
       "      <td>MD</td>\n",
       "      <td>2024-09-07 14:43:31.966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>124</td>\n",
       "      <td>Justin</td>\n",
       "      <td>Sexton</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bethstad</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Immunological Disorders,Interventional Pain Me...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>130</td>\n",
       "      <td>Courtney</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Jamesview</td>\n",
       "      <td>Montana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2024-09-07 14:43:31.966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>Alex</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.0</td>\n",
       "      <td>Thomasside</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Eosinophilic Esophagitis,Public Health And Gen...</td>\n",
       "      <td>MS</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mdm_id first_name  last_name   age        city         state  \\\n",
       "0     109     Justin  Davenport   NaN  Laurenport    Washington   \n",
       "1     119      Jared        NaN  43.0         NaN  North Dakota   \n",
       "2     124     Justin     Sexton   NaN    Bethstad      Colorado   \n",
       "3     130   Courtney        NaN   NaN   Jamesview       Montana   \n",
       "4     133       Alex        NaN  19.0  Thomasside           NaN   \n",
       "\n",
       "   profile_status                                         speciality  \\\n",
       "0             NaN                                Dermatopharmacology   \n",
       "1             NaN  Forensic Pathology,Pruritus,Emergency Medicine...   \n",
       "2             NaN  Immunological Disorders,Interventional Pain Me...   \n",
       "3             NaN                                                NaN   \n",
       "4             1.0  Eosinophilic Esophagitis,Public Health And Gen...   \n",
       "\n",
       "        degree                batch_id  \n",
       "0  PHD,BHMS,MD 2024-09-07 14:43:31.929  \n",
       "1           MD 2024-09-07 14:43:31.966  \n",
       "2          NaN 2024-09-07 14:43:31.929  \n",
       "3          NaN 2024-09-07 14:43:31.966  \n",
       "4           MS 2024-09-07 14:43:31.929  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "TASK (Transformation Process 1): Merging the data from different batches in landing area for the same KOLs based on some business logic.\n",
      "TASK: Succcessfully implemented the merging logic\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>mdm_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>profile_status</th>\n",
       "      <th>speciality</th>\n",
       "      <th>degree</th>\n",
       "      <th>batch_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>Justin</td>\n",
       "      <td>Davenport</td>\n",
       "      <td>-1</td>\n",
       "      <td>Laurenport</td>\n",
       "      <td>Washington</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dermatopharmacology</td>\n",
       "      <td>PHD,BHMS,MD</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>Jared</td>\n",
       "      <td>-</td>\n",
       "      <td>43</td>\n",
       "      <td>-</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Forensic Pathology,Pruritus,Emergency Medicine...</td>\n",
       "      <td>MD</td>\n",
       "      <td>2024-09-07 14:43:31.966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>124</td>\n",
       "      <td>Justin</td>\n",
       "      <td>Sexton</td>\n",
       "      <td>-1</td>\n",
       "      <td>Bethstad</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Immunological Disorders,Interventional Pain Me...</td>\n",
       "      <td></td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>130</td>\n",
       "      <td>Courtney</td>\n",
       "      <td>-</td>\n",
       "      <td>-1</td>\n",
       "      <td>Jamesview</td>\n",
       "      <td>Montana</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2024-09-07 14:43:31.966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>Alex</td>\n",
       "      <td>-</td>\n",
       "      <td>19</td>\n",
       "      <td>Thomasside</td>\n",
       "      <td>-</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Eosinophilic Esophagitis,Public Health And Gen...</td>\n",
       "      <td>MS</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mdm_id first_name  last_name  age        city         state  \\\n",
       "0     109     Justin  Davenport   -1  Laurenport    Washington   \n",
       "1     119      Jared          -   43           -  North Dakota   \n",
       "2     124     Justin     Sexton   -1    Bethstad      Colorado   \n",
       "3     130   Courtney          -   -1   Jamesview       Montana   \n",
       "4     133       Alex          -   19  Thomasside             -   \n",
       "\n",
       "   profile_status                                         speciality  \\\n",
       "0             NaN                                Dermatopharmacology   \n",
       "1             NaN  Forensic Pathology,Pruritus,Emergency Medicine...   \n",
       "2             NaN  Immunological Disorders,Interventional Pain Me...   \n",
       "3             NaN                                                      \n",
       "4             1.0  Eosinophilic Esophagitis,Public Health And Gen...   \n",
       "\n",
       "        degree                batch_id  \n",
       "0  PHD,BHMS,MD 2024-09-07 14:43:31.929  \n",
       "1           MD 2024-09-07 14:43:31.966  \n",
       "2              2024-09-07 14:43:31.929  \n",
       "3              2024-09-07 14:43:31.966  \n",
       "4           MS 2024-09-07 14:43:31.929  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "TASK (Transformation Process 2): Mapping Specialities, Degrees, and Profiling status of various KOLs based on master tables\n",
      "TASK: Succcessfully prepared the Staging area dataframe\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>mdm_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>profile_status</th>\n",
       "      <th>speciality</th>\n",
       "      <th>degree</th>\n",
       "      <th>batch_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>Justin</td>\n",
       "      <td>Davenport</td>\n",
       "      <td>-1</td>\n",
       "      <td>Laurenport</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Not Profiled</td>\n",
       "      <td>295</td>\n",
       "      <td>2,3,6</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>Jared</td>\n",
       "      <td>-</td>\n",
       "      <td>43</td>\n",
       "      <td>-</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>Not Profiled</td>\n",
       "      <td>106,187,383,9</td>\n",
       "      <td>2</td>\n",
       "      <td>2024-09-07 14:43:31.966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>124</td>\n",
       "      <td>Justin</td>\n",
       "      <td>Sexton</td>\n",
       "      <td>-1</td>\n",
       "      <td>Bethstad</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Not Profiled</td>\n",
       "      <td>330,466</td>\n",
       "      <td>0</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>130</td>\n",
       "      <td>Courtney</td>\n",
       "      <td>-</td>\n",
       "      <td>-1</td>\n",
       "      <td>Jamesview</td>\n",
       "      <td>Montana</td>\n",
       "      <td>Not Profiled</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2024-09-07 14:43:31.966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>Alex</td>\n",
       "      <td>-</td>\n",
       "      <td>19</td>\n",
       "      <td>Thomasside</td>\n",
       "      <td>-</td>\n",
       "      <td>Partially Profiled</td>\n",
       "      <td>307,70</td>\n",
       "      <td>8</td>\n",
       "      <td>2024-09-07 14:43:31.929</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mdm_id first_name  last_name  age        city         state  \\\n",
       "0     109     Justin  Davenport   -1  Laurenport    Washington   \n",
       "1     119      Jared          -   43           -  North Dakota   \n",
       "2     124     Justin     Sexton   -1    Bethstad      Colorado   \n",
       "3     130   Courtney          -   -1   Jamesview       Montana   \n",
       "4     133       Alex          -   19  Thomasside             -   \n",
       "\n",
       "       profile_status     speciality degree                batch_id  \n",
       "0        Not Profiled            295  2,3,6 2024-09-07 14:43:31.929  \n",
       "1        Not Profiled  106,187,383,9      2 2024-09-07 14:43:31.966  \n",
       "2        Not Profiled        330,466      0 2024-09-07 14:43:31.929  \n",
       "3        Not Profiled              0      0 2024-09-07 14:43:31.966  \n",
       "4  Partially Profiled         307,70      8 2024-09-07 14:43:31.929  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Successfully updated the staging area: output_store/staging_area.xlsx\n"
     ]
    }
   ],
   "source": [
    "if __name__ == \"__main__\":\n",
    "    print(\"Starting the Data Transformation Process\\n\\n\")\n",
    "    staging_df = prepare_staging_area()\n",
    "    staging_df.to_excel(STAGING_AREA_FILE_PATH, index=False)\n",
    "    print(f\"\\n\\nSuccessfully updated the staging area: {STAGING_AREA_FILE_PATH}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}