1. data_extraction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# KOL Data Standardization - Step 1 (Data Extraction)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "KOL Data Standardization - Step 1 (Extract the Data from various sources (Batches) and store it in the Landing table)\n",
    "\n",
    "This module represents the first step in KOL Data Standardization process where we load the data which is available to us\n",
    "in different set of batches. The data once loaded is checked for null values with following constraints:\n",
    "- The mdm_id should not be null.\n",
    "- The first name of the KOL should not be null.\n",
    "Any data point failing this constraint will be stored separately (warehouse area) for future reference. Rest will be sent to landing area.\n",
    "\"\"\"\n",
    "\n",
    "import os\n",
    "import datetime\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "RAW_DATA_STORE_PATH = \"data_store\"\n",
    "WAREHOUSE_AREA_FILE_PATH = \"output_store/landing_area.xlsx\"\n",
    "LANDING_AREA_FILE_PATH = \"output_store/landing_area.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Extracting the data from various batches and storing it in Landing area.\n",
      "\n",
      "Processing batch: data_store\\batch_1.xlsx\n",
      "Processing batch: data_store\\batch_2.xlsx\n",
      "Processing batch: data_store\\batch_3.xlsx\n",
      "\n",
      "Successfully completed data extraction process\n",
      "\n",
      "Successfully updated the Landing area\n"
     ]
    }
   ],
   "source": [
    "# Data Extraction Process - Loading all the data from different batches and storing them in either warehouse or in landing stage.\n",
    "\n",
    "warehouse_area = []\n",
    "landing_area = []\n",
    "print(\"Extracting the data from various batches and storing it in Landing area.\\n\")\n",
    "for batch in os.listdir(RAW_DATA_STORE_PATH):\n",
    "    file_path = os.path.join(RAW_DATA_STORE_PATH, batch)\n",
    "    print(f\"Processing batch: {file_path}\")\n",
    "    dataframe = pd.read_excel(file_path)\n",
    "\n",
    "    condition = dataframe[[\"mdm_id\", \"first_name\"]].isna().any(axis=1)\n",
    "\n",
    "    # Datapoints having NULL values in essential columns\n",
    "    warehouse_area.append(dataframe[condition])\n",
    "\n",
    "    # Adequate data input for landing stage\n",
    "    correct_data_df = dataframe[~condition].reset_index(drop=True)\n",
    "    correct_data_df[\"batch_id\"] = datetime.datetime.now()\n",
    "    landing_area.append(correct_data_df)\n",
    "\n",
    "warehouse_df = pd.concat(warehouse_area).reset_index(drop=True)\n",
    "landing_df = pd.concat(landing_area).reset_index(drop=True)\n",
    "print(\"\\nSuccessfully completed data extraction process\")\n",
    "\n",
    "# Storing both the dataframes\n",
    "warehouse_df.to_excel(WAREHOUSE_AREA_FILE_PATH, index=False)\n",
    "landing_df.to_excel(LANDING_AREA_FILE_PATH, index=False)\n",
    "print(\"\\nSuccessfully updated the Landing area\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>155</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.0</td>\n",
       "      <td>Richardton</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Pharmacoepidemilogy,Sports Medicine(Physical M...</td>\n",
       "      <td>MBBS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>166</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Williams</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Neurodegenerative diseases,Occupational Medici...</td>\n",
       "      <td>BHMS,BHMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>195</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Clark</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BUMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>158</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.0</td>\n",
       "      <td>West Andrewburgh</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Inflmmation at the Skin Barrier</td>\n",
       "      <td>BDS,MD,MD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>189</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Crosby</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Inflmmation at the Skin Barrier,Allergic Rhini...</td>\n",
       "      <td>PHD,BHMS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mdm_id first_name last_name   age              city         state  \\\n",
       "0     155        NaN       NaN  33.0        Richardton      Colorado   \n",
       "1     166        NaN  Williams   NaN               NaN           NaN   \n",
       "2     195        NaN     Clark   NaN               NaN           NaN   \n",
       "3     158        NaN       NaN  32.0  West Andrewburgh       Indiana   \n",
       "4     189        NaN    Crosby  18.0               NaN  Pennsylvania   \n",
       "\n",
       "   profile_status                                         speciality  \\\n",
       "0             1.0  Pharmacoepidemilogy,Sports Medicine(Physical M...   \n",
       "1             1.0  Neurodegenerative diseases,Occupational Medici...   \n",
       "2             1.0                                                NaN   \n",
       "3             1.0                    Inflmmation at the Skin Barrier   \n",
       "4             1.0  Inflmmation at the Skin Barrier,Allergic Rhini...   \n",
       "\n",
       "      degree  \n",
       "0       MBBS  \n",
       "1  BHMS,BHMS  \n",
       "2       BUMS  \n",
       "3  BDS,MD,MD  \n",
       "4   PHD,BHMS  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Warehouse DataFrame\n",
    "warehouse_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "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>182</td>\n",
       "      <td>James</td>\n",
       "      <td>Allen</td>\n",
       "      <td>58.0</td>\n",
       "      <td>Loriview</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Vascular Medicine,Brain imaging</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2024-09-07 14:43:31.928517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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.928517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>199</td>\n",
       "      <td>Ashley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>East Johnview</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Retinal Ophthalmology,Molluscum,Otology/Neurot...</td>\n",
       "      <td>BUMS,MBBS</td>\n",
       "      <td>2024-09-07 14:43:31.928517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180</td>\n",
       "      <td>Jose</td>\n",
       "      <td>Chambers</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2024-09-07 14:43:31.928517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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.928517</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mdm_id first_name  last_name   age           city       state  \\\n",
       "0     182      James      Allen  58.0       Loriview         NaN   \n",
       "1     109     Justin  Davenport   NaN     Laurenport  Washington   \n",
       "2     199     Ashley        NaN   NaN  East Johnview         NaN   \n",
       "3     180       Jose   Chambers  59.0            NaN         NaN   \n",
       "4     124     Justin     Sexton   NaN       Bethstad    Colorado   \n",
       "\n",
       "   profile_status                                         speciality  \\\n",
       "0             2.0                    Vascular Medicine,Brain imaging   \n",
       "1             NaN                                Dermatopharmacology   \n",
       "2             2.0  Retinal Ophthalmology,Molluscum,Otology/Neurot...   \n",
       "3             NaN                                                NaN   \n",
       "4             NaN  Immunological Disorders,Interventional Pain Me...   \n",
       "\n",
       "        degree                   batch_id  \n",
       "0          NaN 2024-09-07 14:43:31.928517  \n",
       "1  PHD,BHMS,MD 2024-09-07 14:43:31.928517  \n",
       "2    BUMS,MBBS 2024-09-07 14:43:31.928517  \n",
       "3          NaN 2024-09-07 14:43:31.928517  \n",
       "4          NaN 2024-09-07 14:43:31.928517  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Landing Area DataFrame\n",
    "landing_df.head()"
   ]
  },
  {
   "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
}