3. data_loading.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# KOL Data Standardization - Step 3 (Data Loading)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "KOL Data Standardization - Step 3 (Extracting the staging area data and loading it to the reporting area)\n",
    "\n",
    "This module represents the final step in KOL Data Standardization process where we load the staging area data and make it adequate for the reporting layer.\n",
    "It includes the following steps:\n",
    "- Loading the staging area dataframe into the memory.\n",
    "- Separating out the mdm_ids and their degree/speciality mappings to store in separate tables.\n",
    "- Store the data into the reporting layer.\n",
    "The above steps will ensure that the data is ready for the reporting layer which is suitable for the end-users.\n",
    "\"\"\"\n",
    "\n",
    "import pandas as pd\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "STAGING_AREA_FILE_PATH = \"output_store/staging_area.xlsx\"\n",
    "REPORTING_AREA_FILE_PATH = \"output_store/reporting_area.xlsx\"\n",
    "REPORTING_AREA_SP_MAP_FILE_PATH = \"output_store/reporting_area_sp_map.xlsx\"\n",
    "REPORTING_AREA_DEG_MAP_FILE_PATH = \"output_store/reporting_area_deg_map.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TASK: Loading 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"
    }
   ],
   "source": [
    "print(\"TASK: Loading the Staging Area Dataframe\")\n",
    "staging_df = pd.read_excel(STAGING_AREA_FILE_PATH)\n",
    "display(staging_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TASK: Preparing the KOL speciality and degree maps\n",
      "TASK: Preparing the KOL reporting layer dataset\n"
     ]
    }
   ],
   "source": [
    "print(\"TASK: Preparing the KOL speciality and degree maps\")\n",
    "kol_speciality_map = staging_df.assign(speciality_id=staging_df['speciality'].str.split(',')).explode('speciality_id')[[\"mdm_id\", \"speciality_id\"]]\n",
    "kol_degree_map = staging_df.assign(degree_id=staging_df['degree'].str.split(',')).explode('degree_id')[[\"mdm_id\", \"degree_id\"]]\n",
    "print(\"TASK: Preparing the KOL reporting layer dataset\")\n",
    "reporting_df = staging_df.drop([\"speciality\", \"degree\", \"batch_id\"], axis=1)"
   ]
  },
  {
   "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>speciality_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mdm_id speciality_id\n",
       "0     109           295\n",
       "1     119           106\n",
       "1     119           187\n",
       "1     119           383\n",
       "1     119             9"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(kol_speciality_map.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>degree_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>119</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>124</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mdm_id degree_id\n",
       "0     109         2\n",
       "0     109         3\n",
       "0     109         6\n",
       "1     119         2\n",
       "2     124         0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(kol_degree_map.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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  \n",
       "0        Not Profiled  \n",
       "1        Not Profiled  \n",
       "2        Not Profiled  \n",
       "3        Not Profiled  \n",
       "4  Partially Profiled  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(reporting_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TASK: Saving the data maps and reporting area dataset\n",
      "TASK: Successfully loaded the data to the reporting area\n"
     ]
    }
   ],
   "source": [
    "print(\"TASK: Saving the data maps and reporting area dataset\")\n",
    "kol_speciality_map.to_excel(REPORTING_AREA_SP_MAP_FILE_PATH, index=False)\n",
    "kol_degree_map.to_excel(REPORTING_AREA_DEG_MAP_FILE_PATH, index=False)\n",
    "reporting_df.to_excel(REPORTING_AREA_FILE_PATH, index=False)\n",
    "print(\"TASK: Successfully loaded the data to the reporting area\")"
   ]
  },
  {
   "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
}