inspectData.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d1814ae4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import os\n",
    "from pandasql import sqldf\n",
    "\n",
    "from scipy.stats import pearsonr\n",
    "\n",
    "makeQuery = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "21046093",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['regions_hcps.csv',\n",
       " 'region_features',\n",
       " 'total_sales_per_region.csv',\n",
       " 'rtes.csv',\n",
       " 'time_features',\n",
       " 'sales_test_splitted.csv',\n",
       " 'sales_train.csv',\n",
       " 'submissions',\n",
       " 'rtes_brand_2.csv',\n",
       " 'train_validation_split_10.json',\n",
       " 'sales_train_splitted.csv',\n",
       " 'rtes_brand_1.csv',\n",
       " 'activity.csv',\n",
       " 'rtes_cleaned.csv',\n",
       " 'hcps.csv',\n",
       " 'regions.csv',\n",
       " 'train_validation_split_5.json']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir('./data/data_files')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "42063592",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201, 5), (4939, 4), (53938, 7), (18100, 4), (41668, 11), (201, 9))"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "regions = pd.read_csv('./data/data_files/regions.csv')\n",
    "hcps = pd.read_csv('./data/data_files/hcps.csv')\n",
    "activity = pd.read_csv('./data/data_files/activity.csv')\n",
    "salesTrain = pd.read_csv('./data/data_files/sales_train.csv')\n",
    "rtes = pd.read_csv('./data/data_files/rtes.csv')\n",
    "regionHcps = pd.read_csv('./data/data_files/regions_hcps.csv')\n",
    "regions.shape, hcps.shape, activity.shape, salesTrain.shape, rtes.shape, regionHcps.shape"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d14827ad",
   "metadata": {},
   "source": [
    "regions.tail()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "e22ee01d",
   "metadata": {},
   "source": [
    "hcps.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "0518db6b",
   "metadata": {},
   "source": [
    "hcps[hcps['hcp'] == 307]"
   ]
  },
  {
   "cell_type": "raw",
   "id": "fd5b1226",
   "metadata": {},
   "source": [
    "len(set(hcps.hcp.values.tolist())), hcps.shape"
   ]
  },
  {
   "cell_type": "raw",
   "id": "0dd5292e",
   "metadata": {},
   "source": [
    "activity.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "2cbe49b3",
   "metadata": {},
   "source": [
    "set(activity.channel.values.tolist())"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d5180a4c",
   "metadata": {},
   "source": [
    "activity.groupby('hcp').count().sort_values('specialty', ascending=False)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d2e0aad0",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "activity[activity['hcp'] == 32137]"
   ]
  },
  {
   "cell_type": "raw",
   "id": "55689756",
   "metadata": {},
   "source": [
    "activity[activity['region'] == 'region_164']"
   ]
  },
  {
   "cell_type": "raw",
   "id": "509ed6b1",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "salesTrain.head(n=10)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "64de27ec",
   "metadata": {},
   "source": [
    "# clean sales data for march 2020 --> we cannot expect covid situation to repeat --> take average of february and april\n",
    "brand3Sales = salesTrain[salesTrain['brand'] == 'brand_3_market']\n",
    "\n",
    "fig, ax = plt.subplots(nrows=4, ncols=4, sharex=True, sharey=True, figsize=(8,8))\n",
    "for i in range(4): \n",
    "    for j in range(4): \n",
    "        data = brand3Sales[brand3Sales['region'] == 'region_{}'.format(i*4 + j)]\n",
    "        ax[i, j].plot(data['month'].values, data['sales'].values)\n",
    "    \n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "1a67331f",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "raw",
   "id": "47c56e9b",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for region in set(salesTrain.region.values.tolist()): \n",
    "    for brand in set(salesTrain.brand.values.tolist()):\n",
    "        if int(region.split('_')[1]) > 150 and int(brand.split('_')[1]) in [1, 2]: \n",
    "            continue\n",
    "        febIndex = salesTrain[salesTrain['brand'] == brand][salesTrain['region'] == region][salesTrain['month'] == '2020-02'].index\n",
    "        aprIndex = salesTrain[salesTrain['brand'] == brand][salesTrain['region'] == region][salesTrain['month'] == '2020-04'].index\n",
    "        marIndex = salesTrain[salesTrain['brand'] == brand][salesTrain['region'] == region][salesTrain['month'] == '2020-03'].index\n",
    "        salesTrain.at[marIndex.values[0], 'sales'] = (salesTrain.at[febIndex.values[0], 'sales'] + salesTrain.at[aprIndex.values[0], 'sales']) / 2"
   ]
  },
  {
   "cell_type": "raw",
   "id": "51e52626",
   "metadata": {},
   "source": [
    "brand3Sales = salesTrain[salesTrain['brand'] == 'brand_3_market']\n",
    "\n",
    "fig, ax = plt.subplots(nrows=4, ncols=4, sharex=True, sharey=True, figsize=(8,8))\n",
    "for i in range(4): \n",
    "    for j in range(4): \n",
    "        data = brand3Sales[brand3Sales['region'] == 'region_{}'.format(i*4 + j)]\n",
    "        ax[i, j].plot(data['month'].values, data['sales'].values)\n",
    "    \n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "eb6180b0",
   "metadata": {},
   "source": [
    "salesTest = salesTrain[salesTrain['region'].apply(lambda x: int(x.split('_')[1]) > 150)]\n",
    "salesTest.shape, salesTrain.shape"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7f4d601b",
   "metadata": {},
   "source": [
    "salesTest.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f0f4f6c3",
   "metadata": {},
   "source": [
    "salesTrain.drop(salesTest.index, inplace=True, axis=0)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "09428aa9",
   "metadata": {},
   "source": [
    "salesTrain.to_csv('./data/data_files/sales_train_splitted.csv')\n",
    "salesTest.to_csv('./data/data_files/sales_test_splitted.csv')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "413ff117",
   "metadata": {},
   "source": [
    "rtes.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5685da02",
   "metadata": {},
   "source": [
    "set(rtes.email_type.values.tolist())"
   ]
  },
  {
   "cell_type": "raw",
   "id": "b8ea08b5",
   "metadata": {},
   "source": [
    "regionHcps.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "058bf019",
   "metadata": {},
   "source": [
    "regions.all()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "bc2595ee",
   "metadata": {},
   "source": [
    "hcps.all()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "59900150",
   "metadata": {},
   "source": [
    "activity.all()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "da76ed11",
   "metadata": {},
   "source": [
    "salesTrain.all()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "662a0429",
   "metadata": {},
   "source": [
    "rtes.all()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "6f008176",
   "metadata": {},
   "source": [
    "regionHcps.all()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "15dc3bbe",
   "metadata": {},
   "source": [
    "regionHcpsTransformed = pd.concat([regions.set_index('region', drop=True).drop(['area', 'pci16', 'pci18'], axis=1), \n",
    "                                   regionHcps.set_index('region', drop=True)], axis=1)\n",
    "regionHcpsTransformed.shape"
   ]
  },
  {
   "cell_type": "raw",
   "id": "1e74eb07",
   "metadata": {},
   "source": [
    "regionHcpsTransformed.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "30bc2bd5",
   "metadata": {},
   "source": [
    "# regionHcpAll = pd.concat([regionHcpsTransformed, hcps.set_index('region', drop=True)], axis=1)\n",
    "regionHcpAll = regionHcpsTransformed.merge(hcps, how='inner',  right_on='region', left_index=True)\n",
    "regionHcpAll.set_index('hcp', drop=False, inplace=True)\n",
    "regionHcpAll.sort_index(inplace=True)\n",
    "regionHcpAll.shape"
   ]
  },
  {
   "cell_type": "raw",
   "id": "b5c37be9",
   "metadata": {},
   "source": [
    "regionHcpAll.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "a98551f4",
   "metadata": {},
   "source": [
    "regionHcpAll.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "4c1c2a84",
   "metadata": {},
   "source": [
    "regionHcpAll.rename(columns={c: c.replace(' ', '_') for c in regionHcpAll.columns}, inplace=True)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "4ebf2923",
   "metadata": {},
   "source": [
    "regionHcpAll.to_csv('./data/data_files/region_hcp_joined.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38a6c3a1",
   "metadata": {},
   "source": [
    "### Analyse hcp"
   ]
  },
  {
   "cell_type": "raw",
   "id": "a60c218d",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "    region, \n",
    "    tier,\n",
    "    COUNT(hcp) AS hcp_count\n",
    "    \n",
    "FROM regionHcpAll\n",
    "GROUP BY region, tier\n",
    "\"\"\"\n",
    "makeQuery(query)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "3cc5dd38",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT specialty, COUNT(hcp) AS hcp_count\n",
    "FROM hcps\n",
    "GROUP BY specialty\n",
    "\"\"\"\n",
    "hcp_per_specialty = makeQuery(query)\n",
    "hcp_per_specialty"
   ]
  },
  {
   "cell_type": "raw",
   "id": "4fabb57f",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "    regions.region, \n",
    "    population, \n",
    "    area, \n",
    "    pci16, \n",
    "    pci18, \n",
    "    hcp_count, \n",
    "    hcp_count/area AS hcp_density, \n",
    "    population/area AS population_density, \n",
    "    hcp_count / (population/area) AS hcp_per_population_density\n",
    "FROM (\n",
    "    SELECT region, COUNT(hcp) AS hcp_count\n",
    "    FROM hcps\n",
    "    GROUP BY region \n",
    "    ) AS hcp_per_region\n",
    "INNER JOIN regions\n",
    "ON regions.region = hcp_per_region.region\n",
    "\"\"\"\n",
    "hcp_count = makeQuery(query)\n",
    "hcp_count"
   ]
  },
  {
   "cell_type": "raw",
   "id": "b2befaf4",
   "metadata": {},
   "source": [
    "hcp_count.plot.scatter(x='area', y='hcp_count')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "0310c8c7",
   "metadata": {},
   "source": [
    "hcp_count.plot.scatter(x='population_density', y='hcp_count')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "13854396",
   "metadata": {},
   "source": [
    "hcp_count.plot.scatter(x='population', y='hcp_count')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55b77039",
   "metadata": {},
   "source": [
    "### Analyse total sales"
   ]
  },
  {
   "cell_type": "raw",
   "id": "26f1011b",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT region, brand, SUM(sales) AS total_sales\n",
    "FROM salesTrain\n",
    "GROUP BY region, brand\n",
    "\"\"\"\n",
    "total_sales_per_region = makeQuery(query)\n",
    "total_sales_per_region"
   ]
  },
  {
   "cell_type": "raw",
   "id": "3b0dc809",
   "metadata": {},
   "source": [
    "total_sales_per_region.to_csv('./data/data_files/total_sales_per_region.csv')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "eddc1fef",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "    hcp_count.region, \n",
    "    brand, \n",
    "    total_sales, \n",
    "    population, \n",
    "    area, \n",
    "    pci16, \n",
    "    pci18, \n",
    "    hcp_count, \n",
    "    hcp_density, \n",
    "    population_density, \n",
    "    hcp_per_population_density\n",
    "FROM total_sales_per_region\n",
    "INNER JOIN hcp_count\n",
    "ON hcp_count.region = total_sales_per_region.region\n",
    "\"\"\"\n",
    "hcp_sales_joined = makeQuery(query)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "eeee7baf",
   "metadata": {},
   "source": [
    "hcp_sales_joined.shape"
   ]
  },
  {
   "cell_type": "raw",
   "id": "a10ec7c5",
   "metadata": {},
   "source": [
    "hcp_sales_joined.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "0941de3e",
   "metadata": {},
   "source": [
    "brands = set(hcp_sales_joined.brand.values.tolist())\n",
    "brands"
   ]
  },
  {
   "cell_type": "raw",
   "id": "6bff962c",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for value in set(hcp_sales_joined.brand.values.tolist()): \n",
    "    hcp_sales_joined[hcp_sales_joined['brand'] == value].plot.scatter(x='hcp_per_population_density', y='total_sales')\n",
    "    plt.title(value)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "be0d8d7d",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for value in set(hcp_sales_joined.brand.values.tolist()): \n",
    "    hcp_sales_joined[hcp_sales_joined['brand'] == value].plot.scatter(x='population_density', y='total_sales')\n",
    "    plt.title(value)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "664c1868",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for value in set(hcp_sales_joined.brand.values.tolist()): \n",
    "    hcp_sales_joined[hcp_sales_joined['brand'] == value].plot.scatter(x='hcp_density', y='total_sales')\n",
    "    plt.title(value)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "23a75dd6",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for value in set(hcp_sales_joined.brand.values.tolist()): \n",
    "    hcp_sales_joined[hcp_sales_joined['brand'] == value].plot.scatter(x='hcp_count', y='total_sales')\n",
    "    plt.title(value)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5c8884dc",
   "metadata": {},
   "source": [
    "transformedData = {}\n",
    "for brand in brands: \n",
    "    query = \"\"\"\n",
    "    SELECT \n",
    "        region,\n",
    "        total_sales / area AS sales_per_area, \n",
    "        total_sales / population AS sales_per_population, \n",
    "        total_sales / population_density AS sales_per_population_density, \n",
    "        total_sales / hcp_count AS sales_per_hcp, \n",
    "        total_sales / hcp_density AS sales_per_hcp_density, \n",
    "        total_sales / hcp_per_population_density AS sales_per_hcp_pop_density, \n",
    "        hcp_count, \n",
    "        hcp_density, \n",
    "        hcp_per_population_density, \n",
    "        area, \n",
    "        population\n",
    "    FROM hcp_sales_joined\n",
    "    WHERE brand = '{brand}'\n",
    "    \"\"\".format(brand=brand)\n",
    "    salesTransformed = makeQuery(query)\n",
    "    transformedData[brand] = salesTransformed"
   ]
  },
  {
   "cell_type": "raw",
   "id": "1bbde3a5",
   "metadata": {},
   "source": [
    "transformedData['brand_3'].head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "b218bec8",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for brand in brands: \n",
    "    temp = pd.DataFrame(1/transformedData[brand]['hcp_count'], columns=['hcp_count'])\n",
    "    temp['sales_per_hcp'] = transformedData[brand]['sales_per_hcp']\n",
    "    temp.plot.scatter(x='hcp_count', y='sales_per_hcp')\n",
    "    plt.title(brand)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "b0cf85eb",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for brand in brands: \n",
    "    temp = pd.DataFrame(1/transformedData[brand]['hcp_density'], columns=['hcp_density'])\n",
    "    temp['sales_per_hcp'] = transformedData[brand]['sales_per_hcp']\n",
    "    temp.plot.scatter(x='hcp_density', y='sales_per_hcp')\n",
    "    # transformedData[brand].plot.scatter(x='hcp_density', y='sales_per_hcp')\n",
    "    plt.title(brand)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "64c166ed",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for brand in brands: \n",
    "    temp = pd.DataFrame(1/transformedData[brand]['hcp_per_population_density'], columns=['hcp_per_population_density'])\n",
    "    temp['sales_per_hcp'] = transformedData[brand]['sales_per_hcp']\n",
    "    temp.plot.scatter(x='hcp_per_population_density', y='sales_per_hcp')\n",
    "    #transformedData[brand].plot.scatter(x='hcp_per_population_density', y='sales_per_hcp')\n",
    "    plt.title(brand)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "272981a3",
   "metadata": {},
   "source": [
    "### Analyse rte"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f6b0fa40",
   "metadata": {
    "scrolled": true
   },
   "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>hcp</th>\n",
       "      <th>specialty</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>email_type</th>\n",
       "      <th>content_id</th>\n",
       "      <th>no. openings</th>\n",
       "      <th>no. clicks</th>\n",
       "      <th>time_sent</th>\n",
       "      <th>time_last_opened</th>\n",
       "      <th>time_last_clicked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>58555</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jMvEAI</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-03-02 08:42:00</td>\n",
       "      <td>2021-03-02 08:45:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>819</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019gA2EAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-07-16 11:58:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>819</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jSnEAI</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-17 14:47:00</td>\n",
       "      <td>2020-08-17 23:16:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>819</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f000000MtZ3EAK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-03 16:08:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>819</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f000000N0nhEAC</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-10-16 13:35:00</td>\n",
       "      <td>2020-10-16 22:39:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>819</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jMvEAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-12-14 12:06:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4005</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019gA2EAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-07-16 15:15:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4005</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jSnEAI</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-17 14:59:00</td>\n",
       "      <td>2020-08-17 15:58:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4005</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-03 06:22:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4005</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jMvEAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-11-09 22:24:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4887</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-07 11:21:00</td>\n",
       "      <td>2020-09-18 20:47:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4887</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jMvEAI</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-11-19 09:46:00</td>\n",
       "      <td>2020-11-29 16:26:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>9113</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-06 16:10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10003</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019gA2EAI</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-07-16 16:05:00</td>\n",
       "      <td>2020-07-17 07:08:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10003</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jSnEAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-17 15:47:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10003</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 03:27:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11885</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019gA2EAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-07-16 16:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11885</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jSnEAI</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-17 15:45:00</td>\n",
       "      <td>2020-08-17 18:27:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>11885</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 03:27:00</td>\n",
       "      <td>2020-09-04 07:34:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>13228</td>\n",
       "      <td>Internal medicine</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019gA2EAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-07-16 15:46:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>13228</td>\n",
       "      <td>Internal medicine</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jSnEAI</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-17 15:36:00</td>\n",
       "      <td>2020-08-17 17:02:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>13228</td>\n",
       "      <td>Internal medicine</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 03:27:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>13228</td>\n",
       "      <td>Internal medicine</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f000000MtZ3EAK</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 15:59:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>14388</td>\n",
       "      <td>Internal medicine and general practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019gA2EAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-07-16 11:46:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>14388</td>\n",
       "      <td>Internal medicine and general practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>general</td>\n",
       "      <td>a3D6f0000019jSnEAI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-17 14:37:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      hcp                                   specialty    region    brand  \\\n",
       "0   58555                        General practicioner  region_0  brand_1   \n",
       "1     819                        General practicioner  region_1  brand_2   \n",
       "2     819                        General practicioner  region_1  brand_2   \n",
       "3     819                        General practicioner  region_1  brand_1   \n",
       "4     819                        General practicioner  region_1  brand_1   \n",
       "5     819                        General practicioner  region_1  brand_1   \n",
       "6    4005                        General practicioner  region_1  brand_2   \n",
       "7    4005                        General practicioner  region_1  brand_2   \n",
       "8    4005                        General practicioner  region_1  brand_1   \n",
       "9    4005                        General practicioner  region_1  brand_1   \n",
       "10   4887                        General practicioner  region_1  brand_1   \n",
       "11   4887                        General practicioner  region_1  brand_1   \n",
       "12   9113                        General practicioner  region_1  brand_1   \n",
       "13  10003                        General practicioner  region_1  brand_2   \n",
       "14  10003                        General practicioner  region_1  brand_2   \n",
       "15  10003                        General practicioner  region_1  brand_1   \n",
       "16  11885                        General practicioner  region_1  brand_2   \n",
       "17  11885                        General practicioner  region_1  brand_2   \n",
       "18  11885                        General practicioner  region_1  brand_1   \n",
       "19  13228                           Internal medicine  region_1  brand_2   \n",
       "20  13228                           Internal medicine  region_1  brand_2   \n",
       "21  13228                           Internal medicine  region_1  brand_1   \n",
       "22  13228                           Internal medicine  region_1  brand_1   \n",
       "23  14388  Internal medicine and general practicioner  region_1  brand_2   \n",
       "24  14388  Internal medicine and general practicioner  region_1  brand_2   \n",
       "\n",
       "         email_type                             content_id  no. openings  \\\n",
       "0           general                     a3D6f0000019jMvEAI             1   \n",
       "1   product_related                     a3D6f0000019gA2EAI             0   \n",
       "2           general                     a3D6f0000019jSnEAI             1   \n",
       "3   product_related                     a3D6f000000MtZ3EAK             0   \n",
       "4           general                     a3D6f000000N0nhEAC             1   \n",
       "5           general                     a3D6f0000019jMvEAI             0   \n",
       "6   product_related                     a3D6f0000019gA2EAI             0   \n",
       "7           general                     a3D6f0000019jSnEAI             1   \n",
       "8   product_related  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0   \n",
       "9           general                     a3D6f0000019jMvEAI             0   \n",
       "10  product_related                                    NaN             2   \n",
       "11          general                     a3D6f0000019jMvEAI             1   \n",
       "12  product_related  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0   \n",
       "13  product_related                     a3D6f0000019gA2EAI             1   \n",
       "14          general                     a3D6f0000019jSnEAI             0   \n",
       "15  product_related  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0   \n",
       "16  product_related                     a3D6f0000019gA2EAI             0   \n",
       "17          general                     a3D6f0000019jSnEAI             1   \n",
       "18  product_related  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             1   \n",
       "19  product_related                     a3D6f0000019gA2EAI             0   \n",
       "20          general                     a3D6f0000019jSnEAI             1   \n",
       "21  product_related  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0   \n",
       "22  product_related                     a3D6f000000MtZ3EAK             0   \n",
       "23  product_related                     a3D6f0000019gA2EAI             0   \n",
       "24          general                     a3D6f0000019jSnEAI             0   \n",
       "\n",
       "    no. clicks            time_sent     time_last_opened time_last_clicked  \n",
       "0            0  2021-03-02 08:42:00  2021-03-02 08:45:00               NaN  \n",
       "1            0  2020-07-16 11:58:00                  NaN               NaN  \n",
       "2            0  2020-08-17 14:47:00  2020-08-17 23:16:00               NaN  \n",
       "3            0  2020-09-03 16:08:00                  NaN               NaN  \n",
       "4            0  2020-10-16 13:35:00  2020-10-16 22:39:00               NaN  \n",
       "5            0  2020-12-14 12:06:00                  NaN               NaN  \n",
       "6            0  2020-07-16 15:15:00                  NaN               NaN  \n",
       "7            0  2020-08-17 14:59:00  2020-08-17 15:58:00               NaN  \n",
       "8            0  2020-09-03 06:22:00                  NaN               NaN  \n",
       "9            0  2020-11-09 22:24:00                  NaN               NaN  \n",
       "10           0  2020-09-07 11:21:00  2020-09-18 20:47:00               NaN  \n",
       "11           0  2020-11-19 09:46:00  2020-11-29 16:26:00               NaN  \n",
       "12           0  2020-09-06 16:10:00                  NaN               NaN  \n",
       "13           0  2020-07-16 16:05:00  2020-07-17 07:08:00               NaN  \n",
       "14           0  2020-08-17 15:47:00                  NaN               NaN  \n",
       "15           0  2020-09-04 03:27:00                  NaN               NaN  \n",
       "16           0  2020-07-16 16:03:00                  NaN               NaN  \n",
       "17           0  2020-08-17 15:45:00  2020-08-17 18:27:00               NaN  \n",
       "18           0  2020-09-04 03:27:00  2020-09-04 07:34:00               NaN  \n",
       "19           0  2020-07-16 15:46:00                  NaN               NaN  \n",
       "20           0  2020-08-17 15:36:00  2020-08-17 17:02:00               NaN  \n",
       "21           0  2020-09-04 03:27:00                  NaN               NaN  \n",
       "22           0  2020-09-04 15:59:00                  NaN               NaN  \n",
       "23           0  2020-07-16 11:46:00                  NaN               NaN  \n",
       "24           0  2020-08-17 14:37:00                  NaN               NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rtes.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2367a483",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(201, 2)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(rtes.region.values.tolist())), len(set(rtes.brand.values.tolist()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "76a25a22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6728, 11)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# split content id into separate columns\n",
    "multiContentColumns = rtes[rtes['content_id'].apply(lambda x: not pd.isna(x) and ',' in x)]\n",
    "multiContentColumns.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "997bd84d",
   "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>hcp</th>\n",
       "      <th>specialty</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>email_type</th>\n",
       "      <th>content_id</th>\n",
       "      <th>no. openings</th>\n",
       "      <th>no. clicks</th>\n",
       "      <th>time_sent</th>\n",
       "      <th>time_last_opened</th>\n",
       "      <th>time_last_clicked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4005</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-03 06:22:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>9113</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-06 16:10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10003</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 03:27:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>11885</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 03:27:00</td>\n",
       "      <td>2020-09-04 07:34:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>13228</td>\n",
       "      <td>Internal medicine</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY,a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 03:27:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      hcp             specialty    region    brand       email_type  \\\n",
       "8    4005  General practicioner  region_1  brand_1  product_related   \n",
       "12   9113  General practicioner  region_1  brand_1  product_related   \n",
       "15  10003  General practicioner  region_1  brand_1  product_related   \n",
       "18  11885  General practicioner  region_1  brand_1  product_related   \n",
       "21  13228     Internal medicine  region_1  brand_1  product_related   \n",
       "\n",
       "                               content_id  no. openings  no. clicks  \\\n",
       "8   a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0           0   \n",
       "12  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0           0   \n",
       "15  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0           0   \n",
       "18  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             1           0   \n",
       "21  a3D6f0000019jDTEAY,a3D6f000000MtbmEAC             0           0   \n",
       "\n",
       "              time_sent     time_last_opened time_last_clicked  \n",
       "8   2020-09-03 06:22:00                  NaN               NaN  \n",
       "12  2020-09-06 16:10:00                  NaN               NaN  \n",
       "15  2020-09-04 03:27:00                  NaN               NaN  \n",
       "18  2020-09-04 03:27:00  2020-09-04 07:34:00               NaN  \n",
       "21  2020-09-04 03:27:00                  NaN               NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "multiContentColumns.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "89428969",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1348, 11)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missingContentColumns = rtes[rtes['content_id'].apply(pd.isna)]\n",
    "missingContentColumns.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b614d5e3",
   "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>hcp</th>\n",
       "      <th>specialty</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>email_type</th>\n",
       "      <th>content_id</th>\n",
       "      <th>no. openings</th>\n",
       "      <th>no. clicks</th>\n",
       "      <th>time_sent</th>\n",
       "      <th>time_last_opened</th>\n",
       "      <th>time_last_clicked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4887</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-07 11:21:00</td>\n",
       "      <td>2020-09-18 20:47:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>19510</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 16:12:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>25273</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-06-10 10:40:00</td>\n",
       "      <td>2021-06-12 14:02:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>28364</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 15:37:00</td>\n",
       "      <td>2020-09-08 11:26:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>34693</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-10-14 10:23:00</td>\n",
       "      <td>2020-10-14 10:28:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       hcp             specialty    region    brand       email_type  \\\n",
       "10    4887  General practicioner  region_1  brand_1  product_related   \n",
       "53   19510  General practicioner  region_1  brand_1  product_related   \n",
       "87   25273  General practicioner  region_1  brand_1  product_related   \n",
       "105  28364  General practicioner  region_1  brand_1  product_related   \n",
       "125  34693  General practicioner  region_1  brand_1  product_related   \n",
       "\n",
       "    content_id  no. openings  no. clicks            time_sent  \\\n",
       "10         NaN             2           0  2020-09-07 11:21:00   \n",
       "53         NaN             0           0  2020-09-04 16:12:00   \n",
       "87         NaN             5           0  2021-06-10 10:40:00   \n",
       "105        NaN             7           0  2020-09-04 15:37:00   \n",
       "125        NaN             1           0  2020-10-14 10:23:00   \n",
       "\n",
       "        time_last_opened time_last_clicked  \n",
       "10   2020-09-18 20:47:00               NaN  \n",
       "53                   NaN               NaN  \n",
       "87   2021-06-12 14:02:00               NaN  \n",
       "105  2020-09-08 11:26:00               NaN  \n",
       "125  2020-10-14 10:28:00               NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missingContentColumns.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "21a554f0",
   "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>hcp</th>\n",
       "      <th>specialty</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>email_type</th>\n",
       "      <th>content_id</th>\n",
       "      <th>no. openings</th>\n",
       "      <th>no. clicks</th>\n",
       "      <th>time_sent</th>\n",
       "      <th>time_last_opened</th>\n",
       "      <th>time_last_clicked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4005</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-03 06:22:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4005</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-03 06:22:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9113</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-06 16:10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9113</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f000000MtbmEAC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-06 16:10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10003</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>product_related</td>\n",
       "      <td>a3D6f0000019jDTEAY</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-09-04 03:27:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     hcp             specialty    region    brand       email_type  \\\n",
       "0   4005  General practicioner  region_1  brand_1  product_related   \n",
       "1   4005  General practicioner  region_1  brand_1  product_related   \n",
       "2   9113  General practicioner  region_1  brand_1  product_related   \n",
       "3   9113  General practicioner  region_1  brand_1  product_related   \n",
       "4  10003  General practicioner  region_1  brand_1  product_related   \n",
       "\n",
       "           content_id  no. openings  no. clicks            time_sent  \\\n",
       "0  a3D6f0000019jDTEAY             0           0  2020-09-03 06:22:00   \n",
       "1  a3D6f000000MtbmEAC             0           0  2020-09-03 06:22:00   \n",
       "2  a3D6f0000019jDTEAY             0           0  2020-09-06 16:10:00   \n",
       "3  a3D6f000000MtbmEAC             0           0  2020-09-06 16:10:00   \n",
       "4  a3D6f0000019jDTEAY             0           0  2020-09-04 03:27:00   \n",
       "\n",
       "  time_last_opened time_last_clicked  \n",
       "0              NaN               NaN  \n",
       "1              NaN               NaN  \n",
       "2              NaN               NaN  \n",
       "3              NaN               NaN  \n",
       "4              NaN               NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "splittedContentColumns = []\n",
    "for i, row in multiContentColumns.iterrows(): \n",
    "    contents = row['content_id'].split(',')\n",
    "    for content in contents: \n",
    "        row['content_id'] = content\n",
    "        splittedContentColumns.append([v for v in row.values])\n",
    "        \n",
    "splittedContentColumns = pd.DataFrame(splittedContentColumns, columns=multiContentColumns.columns)\n",
    "splittedContentColumns.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7dd9685c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(41668, 11)\n",
      "(34940, 11)\n",
      "(50435, 11)\n"
     ]
    }
   ],
   "source": [
    "print(rtes.shape)\n",
    "rtes.drop(multiContentColumns.index, axis=0, inplace=True)\n",
    "print(rtes.shape)\n",
    "rtes = pd.concat([rtes, splittedContentColumns], axis=0)\n",
    "rtes.reset_index(drop=True, inplace=True)\n",
    "print(rtes.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1c66b73b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(49087, 11)\n"
     ]
    }
   ],
   "source": [
    "rtes.dropna(axis=0, inplace=True, subset=['content_id'])\n",
    "print(rtes.shape)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "8c624bcf",
   "metadata": {},
   "source": [
    "rtes.to_csv('./data/data_files/rtes_cleaned.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "aaf915ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'brand_1', 'brand_2'}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands = set(rtes['brand'].values.tolist())\n",
    "brands"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c8ca306e",
   "metadata": {},
   "source": [
    "for brand in brands: \n",
    "    rtes[rtes['brand'] == brand].to_csv('./data/data_files/rtes_{}.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "42059c8a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "brand_1 (39134, 11)\n",
      "brand_2 (9953, 11)\n"
     ]
    }
   ],
   "source": [
    "rte_per_brand = {brand: rtes[rtes['brand'] == brand] for brand in brands}\n",
    "for brand, rte in rte_per_brand.items(): \n",
    "    print(brand, rte.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfcbef67",
   "metadata": {},
   "source": [
    "### Analyse timing of rte on response rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7824fb97",
   "metadata": {},
   "outputs": [],
   "source": [
    "for timeColumn in ['time_sent', 'time_last_opened', 'time_last_clicked']: \n",
    "    rtes[timeColumn] = pd.to_datetime(rtes[timeColumn])"
   ]
  },
  {
   "cell_type": "raw",
   "id": "bb1cb75a",
   "metadata": {},
   "source": [
    "rtes['hour_sent'] = rtes['time_sent'].apply(lambda x: x.hour)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "1a748b81",
   "metadata": {},
   "source": [
    "rtes['day_sent'] = rtes['time_sent'].apply(lambda x: x.day)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "b9b77b9b",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "groupedByHour = rtes.groupby('hour_sent').agg({\n",
    "    'hcp': 'count', \n",
    "    'no. openings': 'sum',\n",
    "    'no. clicks': 'sum', \n",
    "})\n",
    "groupedByHour"
   ]
  },
  {
   "cell_type": "raw",
   "id": "71e33d22",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "groupedByHour.reset_index(drop=False, inplace=True)\n",
    "groupedByHour.rename(columns={'hcp': 'mails_sent'}, inplace=True)\n",
    "groupedByHour"
   ]
  },
  {
   "cell_type": "raw",
   "id": "e6e46eb5",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for c in groupedByHour.columns: \n",
    "    if c == 'hour_sent': \n",
    "        continue\n",
    "    groupedByHour.plot.bar(x='hour_sent', y=c)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "edcf5c71",
   "metadata": {},
   "source": [
    "groupedByHour[['mails_sent', 'no. openings', 'no. clicks']].corr()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "3fcde700",
   "metadata": {},
   "source": [
    "groupedByHour['openingsPerMail'] = groupedByHour['no. openings'] / groupedByHour['mails_sent']\n",
    "groupedByHour['clicksPerMail'] = groupedByHour['no. clicks'] / groupedByHour['mails_sent']\n",
    "for c in ['openingsPerMail', 'clicksPerMail']: \n",
    "    groupedByHour.plot.bar(x='hour_sent', y=c)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "11d1aaf9",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "groupedByDay = rtes.groupby('day_sent').agg({\n",
    "    'hcp': 'count', \n",
    "    'no. openings': 'sum',\n",
    "    'no. clicks': 'sum', \n",
    "})\n",
    "groupedByDay"
   ]
  },
  {
   "cell_type": "raw",
   "id": "34e78a46",
   "metadata": {},
   "source": [
    "groupedByDay.reset_index(drop=False, inplace=True)\n",
    "groupedByDay.rename(columns={'hcp': 'mails_sent'}, inplace=True)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f56bb4db",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for c in groupedByDay.columns: \n",
    "    if c == 'day_sent': \n",
    "        continue\n",
    "    groupedByDay.plot.bar(x='day_sent', y=c)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f6fc8e7e",
   "metadata": {},
   "source": [
    "groupedByDay[['mails_sent', 'no. openings', 'no. clicks']].corr()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "47d3554e",
   "metadata": {},
   "source": [
    "groupedByDay['openingsPerMail'] = groupedByDay['no. openings'] / groupedByDay['mails_sent']\n",
    "groupedByDay['clicksPerMail'] = groupedByDay['no. clicks'] / groupedByDay['mails_sent']\n",
    "for c in ['openingsPerMail', 'clicksPerMail']: \n",
    "    groupedByDay.plot.bar(x='day_sent', y=c)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "538e4bf5",
   "metadata": {},
   "source": [
    "rtes['weekday_sent'] = rtes['time_sent'].apply(lambda x: x.weekday())"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c0cc1895",
   "metadata": {},
   "source": [
    "groupedByWeekDay = rtes.groupby('weekday_sent').agg({\n",
    "    'hcp': 'count', \n",
    "    'no. openings': 'sum',\n",
    "    'no. clicks': 'sum', \n",
    "})\n",
    "groupedByWeekDay"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7d748e2b",
   "metadata": {},
   "source": [
    "groupedByWeekDay.reset_index(drop=False, inplace=True)\n",
    "groupedByWeekDay.rename(columns={'hcp': 'mails_sent'}, inplace=True)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "833c9a84",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for c in groupedByWeekDay.columns: \n",
    "    if c == 'weekday_sent': \n",
    "        continue\n",
    "    groupedByWeekDay.plot.bar(x='weekday_sent', y=c)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "95967672",
   "metadata": {
    "scrolled": false
   },
   "source": [
    "groupedByWeekDay['openingsPerMail'] = groupedByWeekDay['no. openings'] / groupedByWeekDay['mails_sent']\n",
    "groupedByWeekDay['clicksPerMail'] = groupedByWeekDay['no. clicks'] / groupedByWeekDay['mails_sent']\n",
    "for c in ['openingsPerMail', 'clicksPerMail']: \n",
    "    groupedByWeekDay.plot.bar(x='weekday_sent', y=c)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "1a52e971",
   "metadata": {},
   "source": [
    "meanOpeningsPerMail = groupedByWeekDay['openingsPerMail'].mean()\n",
    "meanClicksPerMail = groupedByWeekDay['clicksPerMail'].mean()\n",
    "stdOpeningsPerMail = groupedByWeekDay['openingsPerMail'].std()\n",
    "stdClicksPerMail = groupedByWeekDay['clicksPerMail'].std()\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "316c86a4",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "fig, ax = plt.subplots()\n",
    "ax.bar(groupedByWeekDay['weekday_sent'].values, groupedByWeekDay['openingsPerMail'].values)\n",
    "\n",
    "ax.plot([0, 6], [meanOpeningsPerMail, meanOpeningsPerMail], \"r--\")\n",
    "ax.plot([0, 6], [meanOpeningsPerMail+stdOpeningsPerMail, meanOpeningsPerMail+stdOpeningsPerMail], 'g--')\n",
    "ax.plot([0, 6], [meanOpeningsPerMail-stdOpeningsPerMail, meanOpeningsPerMail-stdOpeningsPerMail], 'g--')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "cc93edba",
   "metadata": {},
   "source": [
    "fig, ax = plt.subplots()\n",
    "ax.bar(groupedByWeekDay['weekday_sent'].values, groupedByWeekDay['clicksPerMail'].values)\n",
    "\n",
    "ax.plot([0, 6], [meanClicksPerMail, meanClicksPerMail], \"r--\")\n",
    "ax.plot([0, 6], [meanClicksPerMail+stdClicksPerMail, meanClicksPerMail+stdClicksPerMail], 'g--')\n",
    "ax.plot([0, 6], [meanClicksPerMail-stdClicksPerMail, meanClicksPerMail-stdClicksPerMail], 'g--')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "159b8bea",
   "metadata": {},
   "source": [
    "meanOpeningsPerMail = groupedByHour['openingsPerMail'].mean()\n",
    "meanClicksPerMail = groupedByHour['clicksPerMail'].mean()\n",
    "stdOpeningsPerMail = groupedByHour['openingsPerMail'].std()\n",
    "stdClicksPerMail = groupedByHour['clicksPerMail'].std()\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "8097cda1",
   "metadata": {},
   "source": [
    "fig, ax = plt.subplots()\n",
    "ax.bar(groupedByHour['hour_sent'].values, groupedByHour['openingsPerMail'].values)\n",
    "\n",
    "ax.plot([0, 23], [meanOpeningsPerMail, meanOpeningsPerMail], \"r--\")\n",
    "ax.plot([0, 23], [meanOpeningsPerMail+stdOpeningsPerMail, meanOpeningsPerMail+stdOpeningsPerMail], 'g--')\n",
    "ax.plot([0, 23], [meanOpeningsPerMail-stdOpeningsPerMail, meanOpeningsPerMail-stdOpeningsPerMail], 'g--')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "11c6ba62",
   "metadata": {},
   "source": [
    "fig, ax = plt.subplots()\n",
    "ax.bar(groupedByHour['hour_sent'].values, groupedByHour['clicksPerMail'].values)\n",
    "\n",
    "ax.plot([0, 23], [meanClicksPerMail, meanClicksPerMail], \"r--\")\n",
    "ax.plot([0, 23], [meanClicksPerMail+stdClicksPerMail, meanClicksPerMail+stdClicksPerMail], 'g--')\n",
    "ax.plot([0, 23], [meanClicksPerMail-stdClicksPerMail, meanClicksPerMail-stdClicksPerMail], 'g--')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "35f9c11b",
   "metadata": {},
   "source": [
    "### Response rate per specialty"
   ]
  },
  {
   "cell_type": "raw",
   "id": "1b5f13e8",
   "metadata": {},
   "source": [
    "groupedBySpecialty = rtes.groupby('specialty').agg({\n",
    "    'hcp': 'count', \n",
    "    'no. openings': 'sum',\n",
    "    'no. clicks': 'sum', \n",
    "})\n",
    "groupedBySpecialty.reset_index(drop=False, inplace=True)\n",
    "groupedBySpecialty.rename(columns={'hcp': 'mails_sent'}, inplace=True)\n",
    "groupedBySpecialty['openingsPerMail'] = groupedBySpecialty['no. openings'] / groupedBySpecialty['mails_sent']\n",
    "groupedBySpecialty['clicksPerMail'] = groupedBySpecialty['no. clicks'] / groupedBySpecialty['mails_sent']\n",
    "groupedBySpecialty"
   ]
  },
  {
   "cell_type": "raw",
   "id": "723f5ce7",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "set(rtes[rtes['specialty'] == 'Pediatrician'].region.values.tolist())"
   ]
  },
  {
   "cell_type": "raw",
   "id": "0bb0fe79",
   "metadata": {},
   "source": [
    "specialtyCount = hcps.groupby('specialty').agg({'hcp': 'count'})\n",
    "specialtyCount.rename(columns={'hcp': 'hcp_count'}, inplace=True)\n",
    "specialtyCount"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7697d56a",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "merged = pd.concat([specialtyCount, groupedBySpecialty.set_index('specialty', drop=False)], axis=1)\n",
    "merged.reset_index(drop=True, inplace=True)\n",
    "merged['mailsPerHcp'] = merged['mails_sent']/merged['hcp_count']\n",
    "merged"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7db47119",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for c in ['openingsPerMail', 'clicksPerMail']: \n",
    "    merged.plot.bar(x='specialty', y=c)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "94354fcd",
   "metadata": {},
   "source": [
    "merged.plot.scatter(x='mailsPerHcp', y='openingsPerMail')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "6d7dbbef",
   "metadata": {},
   "source": [
    "merged.plot.scatter(x='mailsPerHcp', y='clicksPerMail')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c5425d37",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "    specialty, \n",
    "    COUNT(*) AS content_count, \n",
    "    COUNT(DISTINCT content_id) AS uniqueContentCount\n",
    "FROM rtes\n",
    "GROUP BY specialty\n",
    "\"\"\"\n",
    "contentDiversity = makeQuery(query)\n",
    "contentDiversity"
   ]
  },
  {
   "cell_type": "raw",
   "id": "fcd8ad58",
   "metadata": {},
   "source": [
    "merged = pd.concat([merged.set_index('specialty', drop=False), \n",
    "                    contentDiversity.set_index('specialty', drop=True)], \n",
    "                  axis=1)\n",
    "merged.reset_index(drop=True, inplace=True)\n",
    "merged"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f35e442",
   "metadata": {},
   "source": [
    "### Does the response rate per region / per specialty correlate with sales?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "26ecc5cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "rtes.rename(columns={'no. openings': 'nr_openings', 'no. clicks': 'nr_clicks'}, inplace=True)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d3bf02f9",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "    grouped_by_region.region, \n",
    "    mails_sent, \n",
    "    nr_openings, \n",
    "    nr_clicks, \n",
    "    content_count, \n",
    "    unique_content_count, \n",
    "    hcp_count,\n",
    "    nr_openings / mails_sent AS openings_per_mail, \n",
    "    nr_clicks / mails_sent AS clicks_per_mail, \n",
    "    mails_sent / hcp_count AS mails_per_hcp\n",
    "FROM (\n",
    "    SELECT \n",
    "        region,\n",
    "        specialty,\n",
    "        CAST(COUNT(*)AS real) AS mails_sent , \n",
    "        CAST(SUM(nr_openings) AS real) AS nr_openings, \n",
    "        CAST(SUM(nr_clicks) AS real) AS nr_clicks, \n",
    "        COUNT(content_id) AS content_count, \n",
    "        COUNT(DISTINCT content_id) AS unique_content_count\n",
    "    FROM rtes\n",
    "    GROUP BY region\n",
    "    ) AS grouped_by_region\n",
    "INNER JOIN (\n",
    "    SELECT \n",
    "        region,\n",
    "        COUNT(*) AS hcp_count\n",
    "    FROM hcps\n",
    "    GROUP BY region\n",
    "    ) AS hcp_per_region_count\n",
    "ON hcp_per_region_count.region = grouped_by_region.region\n",
    "\"\"\"\n",
    "grouped_by_region = makeQuery(query)\n",
    "grouped_by_region.tail()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "abef27f3",
   "metadata": {},
   "source": [
    "grouped_by_region[grouped_by_region['region'] == 'region_169']"
   ]
  },
  {
   "cell_type": "raw",
   "id": "de2b21dd",
   "metadata": {},
   "source": [
    "grouped_by_region.shape"
   ]
  },
  {
   "cell_type": "raw",
   "id": "95a8ad8d",
   "metadata": {},
   "source": [
    "pearsonr(grouped_by_region.mails_sent.values, grouped_by_region.openings_per_mail.values)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "285348dd",
   "metadata": {},
   "source": [
    "pearsonr(grouped_by_region.mails_sent.values, grouped_by_region.clicks_per_mail.values)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "63e301ca",
   "metadata": {},
   "source": [
    "pearsonr(grouped_by_region.unique_content_count.values, grouped_by_region.openings_per_mail.values)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c3da840f",
   "metadata": {},
   "source": [
    "pearsonr(grouped_by_region.unique_content_count.values, grouped_by_region.clicks_per_mail.values)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "db6003c7",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "grouped_by_region.plot.scatter(x='mails_sent', y='openings_per_mail')\n",
    "grouped_by_region.plot.scatter(x='mails_sent', y='clicks_per_mail')\n",
    "grouped_by_region.plot.scatter(x='unique_content_count', y='openings_per_mail')\n",
    "grouped_by_region.plot.scatter(x='unique_content_count', y='clicks_per_mail')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "fcd4acd7",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "grouped_by_region.plot.scatter(x='content_count', y='openings_per_mail')\n",
    "grouped_by_region.plot.scatter(x='content_count', y='clicks_per_mail')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "6e30e246",
   "metadata": {},
   "source": [
    "pearsonr(grouped_by_region.content_count.values, grouped_by_region.clicks_per_mail.values)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "e8a95117",
   "metadata": {},
   "source": [
    "pearsonr(grouped_by_region.content_count.values, grouped_by_region.openings_per_mail.values)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "3d79de1e",
   "metadata": {},
   "source": [
    "query = \"\"\"\n",
    "SELECT *\n",
    "FROM (\n",
    "    SELECT \n",
    "        region, \n",
    "        SUM(sales) AS total_sales\n",
    "    FROM salesTrain\n",
    "    WHERE brand = 'brand_1' OR brand = 'brand_2'\n",
    "    GROUP BY region ) AS sales_per_region\n",
    "INNER JOIN grouped_by_region\n",
    "ON sales_per_region.region = grouped_by_region.region\n",
    "\"\"\"\n",
    "totalSales = makeQuery(query)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c36b5538",
   "metadata": {},
   "source": [
    "totalSales.head()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "75cb1884",
   "metadata": {},
   "source": [
    "totalSales.plot.scatter(x='mails_sent', y='total_sales')\n",
    "totalSales.plot.scatter(x='nr_openings', y='total_sales')\n",
    "totalSales.plot.scatter(x='nr_clicks', y='total_sales')\n",
    "totalSales.plot.scatter(x='content_count', y='total_sales')\n",
    "totalSales.plot.scatter(x='unique_content_count', y='total_sales')\n",
    "totalSales.plot.scatter(x='hcp_count', y='total_sales')\n",
    "totalSales.plot.scatter(x='openings_per_mail', y='total_sales')\n",
    "totalSales.plot.scatter(x='clicks_per_mail', y='total_sales')\n",
    "totalSales.plot.scatter(x='mails_per_hcp', y='total_sales')"
   ]
  },
  {
   "cell_type": "raw",
   "id": "847e5d56",
   "metadata": {},
   "source": [
    "for c in ['mails_sent', 'nr_openings', 'nr_clicks', 'content_count', 'unique_content_count', 'hcp_count', \n",
    "         'openings_per_mail', 'clicks_per_mail', 'mails_per_hcp']: \n",
    "    print(c, round(pearsonr(totalSales[c].values, totalSales['total_sales'].values)[0], 3))"
   ]
  },
  {
   "cell_type": "raw",
   "id": "3f548362",
   "metadata": {},
   "source": [
    "dataPerSpecialty = {}\n",
    "brand = 'brand_1'\n",
    "for specialty in set(hcps.specialty.values.tolist()): \n",
    "    query = \"\"\"\n",
    "    SELECT \n",
    "        sales_per_region.region, \n",
    "        total_sales, \n",
    "        mails_sent, \n",
    "        nr_openings, \n",
    "        nr_clicks, \n",
    "        content_count, \n",
    "        unique_content_count, \n",
    "        hcp_count,\n",
    "        openings_per_mail, \n",
    "        clicks_per_mail, \n",
    "        mails_per_hcp\n",
    "    FROM (\n",
    "        SELECT \n",
    "            region, \n",
    "            SUM(sales) AS total_sales\n",
    "        FROM salesTrain\n",
    "        WHERE brand = '{brand}'\n",
    "        GROUP BY region ) AS sales_per_region\n",
    "    LEFT OUTER JOIN (\n",
    "        SELECT\n",
    "            grouped_by_region.region, \n",
    "            mails_sent, \n",
    "            nr_openings, \n",
    "            nr_clicks, \n",
    "            content_count, \n",
    "            unique_content_count, \n",
    "            hcp_count,\n",
    "            nr_openings / mails_sent AS openings_per_mail, \n",
    "            nr_clicks / mails_sent AS clicks_per_mail, \n",
    "            mails_sent / hcp_count AS mails_per_hcp\n",
    "        FROM (\n",
    "            SELECT \n",
    "                region,\n",
    "                specialty,\n",
    "                CAST(COUNT(*)AS real) AS mails_sent , \n",
    "                CAST(SUM(nr_openings) AS real) AS nr_openings, \n",
    "                CAST(SUM(nr_clicks) AS real) AS nr_clicks, \n",
    "                COUNT(content_id) AS content_count, \n",
    "                COUNT(DISTINCT content_id) AS unique_content_count\n",
    "            FROM rtes\n",
    "            WHERE specialty = '{specialty}' AND brand = '{brand}'\n",
    "            GROUP BY region\n",
    "            ) AS grouped_by_region\n",
    "        LEFT OUTER JOIN (\n",
    "            SELECT \n",
    "                region,\n",
    "                COUNT(*) AS hcp_count\n",
    "            FROM hcps\n",
    "            WHERE specialty = '{specialty}'\n",
    "            GROUP BY region\n",
    "            ) AS hcp_per_region_count\n",
    "        ON hcp_per_region_count.region = grouped_by_region.region\n",
    "        ) AS  grouped_by_region\n",
    "    ON sales_per_region.region = grouped_by_region.region\n",
    "    \"\"\".format(specialty=specialty, brand=brand)\n",
    "    df = makeQuery(query)\n",
    "    dataPerSpecialty[specialty] = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "654cd7f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataPerSpecialty = {}\n",
    "brand = 'brand_1'\n",
    "for specialty in set(hcps.specialty.values.tolist()): \n",
    "    query = \"\"\"\n",
    "    SELECT \n",
    "        sales_per_region.region, \n",
    "        mails_sent, \n",
    "        nr_openings, \n",
    "        nr_clicks, \n",
    "        content_count, \n",
    "        unique_content_count, \n",
    "        hcp_count,\n",
    "        openings_per_mail, \n",
    "        clicks_per_mail, \n",
    "        mails_per_hcp, \n",
    "        -- market_potential\n",
    "    FROM (\n",
    "        SELECT \n",
    "            region, \n",
    "            SUM(sales) AS market_potential\n",
    "        FROM salesTrain\n",
    "        WHERE brand = 'brand_12_market'\n",
    "        GROUP BY region ) AS sales_per_region\n",
    "    LEFT OUTER JOIN (\n",
    "        SELECT\n",
    "            grouped_by_region.region, \n",
    "            mails_sent, \n",
    "            nr_openings, \n",
    "            nr_clicks, \n",
    "            content_count, \n",
    "            unique_content_count, \n",
    "            hcp_count,\n",
    "            nr_openings / mails_sent AS openings_per_mail, \n",
    "            nr_clicks / mails_sent AS clicks_per_mail, \n",
    "            mails_sent / hcp_count AS mails_per_hcp\n",
    "        FROM (\n",
    "            SELECT \n",
    "                region,\n",
    "                specialty,\n",
    "                CAST(COUNT(*)AS real) AS mails_sent , \n",
    "                CAST(SUM(nr_openings) AS real) AS nr_openings, \n",
    "                CAST(SUM(nr_clicks) AS real) AS nr_clicks, \n",
    "                COUNT(content_id) AS content_count, \n",
    "                COUNT(DISTINCT content_id) AS unique_content_count\n",
    "            FROM rtes\n",
    "            WHERE specialty = '{specialty}' AND brand = '{brand}'\n",
    "            GROUP BY region\n",
    "            ) AS grouped_by_region\n",
    "        LEFT OUTER JOIN (\n",
    "            SELECT \n",
    "                region,\n",
    "                COUNT(*) AS hcp_count\n",
    "            FROM hcps\n",
    "            WHERE specialty = '{specialty}'\n",
    "            GROUP BY region\n",
    "            ) AS hcp_per_region_count\n",
    "        ON hcp_per_region_count.region = grouped_by_region.region\n",
    "        ) AS  grouped_by_region\n",
    "    ON sales_per_region.region = grouped_by_region.region\n",
    "    \"\"\".format(specialty=specialty, brand=brand)\n",
    "    df = makeQuery(query)\n",
    "    dataPerSpecialty[specialty] = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "be2431f2",
   "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>region</th>\n",
       "      <th>mails_sent</th>\n",
       "      <th>nr_openings</th>\n",
       "      <th>nr_clicks</th>\n",
       "      <th>content_count</th>\n",
       "      <th>unique_content_count</th>\n",
       "      <th>hcp_count</th>\n",
       "      <th>openings_per_mail</th>\n",
       "      <th>clicks_per_mail</th>\n",
       "      <th>mails_per_hcp</th>\n",
       "      <th>market_potential</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10666956.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_1</td>\n",
       "      <td>65.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.753846</td>\n",
       "      <td>0.138462</td>\n",
       "      <td>5.909091</td>\n",
       "      <td>6803632.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_10</td>\n",
       "      <td>93.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.387097</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>18.600000</td>\n",
       "      <td>5437642.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_100</td>\n",
       "      <td>8.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.125000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>5342599.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_101</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>3954334.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>region_95</td>\n",
       "      <td>175.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.742857</td>\n",
       "      <td>0.051429</td>\n",
       "      <td>10.937500</td>\n",
       "      <td>5409466.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>region_96</td>\n",
       "      <td>46.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.782609</td>\n",
       "      <td>0.086957</td>\n",
       "      <td>7.666667</td>\n",
       "      <td>5974509.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>region_97</td>\n",
       "      <td>54.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.703704</td>\n",
       "      <td>0.018519</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>4901265.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>region_98</td>\n",
       "      <td>56.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.607143</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>5386680.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>region_99</td>\n",
       "      <td>40.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.650000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>5618976.22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>201 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         region  mails_sent  nr_openings  nr_clicks  content_count  \\\n",
       "0      region_0         NaN          NaN        NaN            NaN   \n",
       "1      region_1        65.0         49.0        9.0           65.0   \n",
       "2     region_10        93.0         36.0        0.0           93.0   \n",
       "3    region_100         8.0         17.0        0.0            8.0   \n",
       "4    region_101         1.0          1.0        0.0            1.0   \n",
       "..          ...         ...          ...        ...            ...   \n",
       "196   region_95       175.0        130.0        9.0          175.0   \n",
       "197   region_96        46.0         36.0        4.0           46.0   \n",
       "198   region_97        54.0         38.0        1.0           54.0   \n",
       "199   region_98        56.0         34.0        0.0           56.0   \n",
       "200   region_99        40.0         26.0        0.0           40.0   \n",
       "\n",
       "     unique_content_count  hcp_count  openings_per_mail  clicks_per_mail  \\\n",
       "0                     NaN        NaN                NaN              NaN   \n",
       "1                    11.0       11.0           0.753846         0.138462   \n",
       "2                    19.0        5.0           0.387097         0.000000   \n",
       "3                     3.0        1.0           2.125000         0.000000   \n",
       "4                     1.0        2.0           1.000000         0.000000   \n",
       "..                    ...        ...                ...              ...   \n",
       "196                  20.0       16.0           0.742857         0.051429   \n",
       "197                   8.0        6.0           0.782609         0.086957   \n",
       "198                  10.0        6.0           0.703704         0.018519   \n",
       "199                   2.0        2.0           0.607143         0.000000   \n",
       "200                   2.0        2.0           0.650000         0.000000   \n",
       "\n",
       "     mails_per_hcp  market_potential  \n",
       "0              NaN       10666956.92  \n",
       "1         5.909091        6803632.80  \n",
       "2        18.600000        5437642.05  \n",
       "3         8.000000        5342599.84  \n",
       "4         0.500000        3954334.49  \n",
       "..             ...               ...  \n",
       "196      10.937500        5409466.07  \n",
       "197       7.666667        5974509.31  \n",
       "198       9.000000        4901265.54  \n",
       "199      28.000000        5386680.46  \n",
       "200      20.000000        5618976.22  \n",
       "\n",
       "[201 rows x 11 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataPerSpecialty['Internal medicine']"
   ]
  },
  {
   "cell_type": "raw",
   "id": "a71f26e9",
   "metadata": {},
   "source": [
    "dataPerSpecialty['Internal medicine']"
   ]
  },
  {
   "cell_type": "raw",
   "id": "52e91a9e",
   "metadata": {},
   "source": [
    "for key, value in dataPerSpecialty.items(): \n",
    "    print(key,value.shape)\n",
    "    value.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "90be9e9f",
   "metadata": {},
   "source": [
    "dataPerSpecialty['Pediatrician']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "209dde41",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(201, 50)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allSpecialties = []\n",
    "for specialty, data in dataPerSpecialty.items(): \n",
    "    #if specialty != 'Pediatrician': \n",
    "    #    data.drop('total_sales', axis=1, inplace=True)\n",
    "    data.fillna(0, inplace=True)\n",
    "    data.set_index('region', drop=True, inplace=True)\n",
    "    data.rename(columns={c: '{}_{}'.format(c, specialty) for c in data.columns if c not in ['region', 'total_sales']}, inplace=True)\n",
    "    allSpecialties.append(data)\n",
    "    \n",
    "allSpecialties = pd.concat(allSpecialties, axis=1)\n",
    "allSpecialties.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "63e2c311",
   "metadata": {},
   "outputs": [],
   "source": [
    "viableFeatures = [\n",
    "    'content_count_Internal medicine / pneumology',\n",
    "    'mails_sent_Internal medicine / pneumology',\n",
    "    'unique_content_count_Internal medicine / pneumology',\n",
    "    'hcp_count_Internal medicine / pneumology',\n",
    "    'mails_per_hcp_Internal medicine / pneumology',\n",
    "    'unique_content_count_Internal medicine',\n",
    "    'content_count_Internal medicine',\n",
    "    'mails_sent_Internal medicine',\n",
    "    'content_count_General practicioner',\n",
    "    'mails_sent_General practicioner',\n",
    "]\n",
    "allSpecialties[viableFeatures].to_csv('./data/data_files/region_features/rte_features_{}.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "raw",
   "id": "75ca8858",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "correlations = []\n",
    "for c in allSpecialties.columns: \n",
    "    if c in ['region', 'total_sales']: \n",
    "        continue\n",
    "    corr = round(pearsonr(allSpecialties[c].values, allSpecialties['total_sales'].values)[0], 3)\n",
    "    correlations.append((c, corr))\n",
    "    \n",
    "correlations = pd.DataFrame(correlations, columns=['column', 'correlation'])\n",
    "correlations.sort_values('correlation', ascending=False, inplace=True)\n",
    "correlationFiltered = correlations[correlations['correlation'] > 0.25]\n",
    "selectedFeatures = correlationFiltered[correlationFiltered['column'].apply(lambda x: 'Pediatrician' not in x and 'Internal medicine / General practitioner' not in x)]\n",
    "selectedFeatures"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f9f7df0d",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "for c in selectedFeatures['column'].values: \n",
    "    allSpecialties.plot.scatter(x=c, y='total_sales')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49eefd07",
   "metadata": {},
   "source": [
    "### Analyse activity with regards to total sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0f9fc917",
   "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>hcp</th>\n",
       "      <th>specialty</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>month</th>\n",
       "      <th>channel</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39972</td>\n",
       "      <td>Internal medicine / pneumology</td>\n",
       "      <td>region_61</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>video</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>64026</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_168</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>phone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27892</td>\n",
       "      <td>Internal medicine</td>\n",
       "      <td>region_26</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>phone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33500</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_26</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>phone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>61239</td>\n",
       "      <td>General practicioner</td>\n",
       "      <td>region_26</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>phone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     hcp                       specialty      region    brand    month  \\\n",
       "0  39972  Internal medicine / pneumology   region_61  brand_1  2020-05   \n",
       "1  64026            General practicioner  region_168  brand_2  2020-05   \n",
       "2  27892               Internal medicine   region_26  brand_2  2020-06   \n",
       "3  33500            General practicioner   region_26  brand_2  2020-06   \n",
       "4  61239            General practicioner   region_26  brand_2  2020-06   \n",
       "\n",
       "  channel  count  \n",
       "0   video      1  \n",
       "1   phone      1  \n",
       "2   phone      1  \n",
       "3   phone      1  \n",
       "4   phone      1  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activity.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "01edede7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "hcp 15819\n",
      "specialty 5\n",
      "region 201\n",
      "brand 2\n",
      "month 17\n",
      "channel 4\n",
      "count 7\n"
     ]
    }
   ],
   "source": [
    "for c in activity.columns: \n",
    "    print(c, len(set(activity[c].values.tolist())))"
   ]
  },
  {
   "cell_type": "raw",
   "id": "60ab354c",
   "metadata": {},
   "source": [
    "brand = 'brand_1'\n",
    "specialties = set(activity['specialty'].values.tolist())\n",
    "channels = set(activity['channel'].values.tolist())\n",
    "dataPerSpecialty = {}\n",
    "for specialty in specialties: \n",
    "    if specialty in ['Internal medicine and general practicioner', 'Pediatrician']: \n",
    "        continue\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "        sales_per_region.region, \n",
    "        channel,\n",
    "        total_sales, \n",
    "        activity_count, \n",
    "        unique_hcp_count, \n",
    "        nr_interactions\n",
    "    FROM (\n",
    "        SELECT \n",
    "            region, \n",
    "            SUM(sales) AS total_sales\n",
    "        FROM salesTrain\n",
    "        WHERE brand = '{brand}'\n",
    "        GROUP BY region \n",
    "        ) AS sales_per_region\n",
    "    LEFT OUTER JOIN (\n",
    "        SELECT \n",
    "            region,\n",
    "            channel,\n",
    "            COUNT(hcp) AS activity_count, \n",
    "            COUNT(DISTINCT hcp) AS unique_hcp_count, \n",
    "            SUM(count) AS nr_interactions\n",
    "        FROM activity\n",
    "        WHERE brand = '{brand}' AND specialty = '{specialty}'\n",
    "        GROUP BY region, channel\n",
    "        ) AS grouped_activity\n",
    "    ON grouped_activity.region = sales_per_region.region    \n",
    "    \"\"\".format(brand=brand, specialty=specialty)\n",
    "    df = makeQuery(query)\n",
    "    dataPerSpecialty[specialty] = df\n",
    "    \n",
    "for key, value in dataPerSpecialty.items(): \n",
    "    print(key, value.shape)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "176021fc",
   "metadata": {},
   "source": [
    "brand = 'brand_2'\n",
    "specialties = set(activity['specialty'].values.tolist())\n",
    "channels = set(activity['channel'].values.tolist())\n",
    "dataPerSpecialty = {}\n",
    "for specialty in specialties: \n",
    "    if specialty in ['Internal medicine and general practicioner', 'Pediatrician']: \n",
    "        continue\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "        sales_per_region.region, \n",
    "        total_sales, \n",
    "        activity_count, \n",
    "        unique_hcp_count, \n",
    "        nr_interactions\n",
    "    FROM (\n",
    "        SELECT \n",
    "            region, \n",
    "            SUM(sales) AS total_sales\n",
    "        FROM salesTrain\n",
    "        WHERE brand = '{brand}'\n",
    "        GROUP BY region \n",
    "        ) AS sales_per_region\n",
    "    LEFT OUTER JOIN (\n",
    "        SELECT \n",
    "            region,\n",
    "            COUNT(hcp) AS activity_count, \n",
    "            COUNT(DISTINCT hcp) AS unique_hcp_count, \n",
    "            SUM(count) AS nr_interactions\n",
    "        FROM activity\n",
    "        WHERE brand = '{brand}' AND specialty = '{specialty}'\n",
    "        GROUP BY region\n",
    "        ) AS grouped_activity\n",
    "    ON grouped_activity.region = sales_per_region.region    \n",
    "    \"\"\".format(brand=brand, specialty=specialty)\n",
    "    df = makeQuery(query)\n",
    "    dataPerSpecialty[specialty] = df\n",
    "    \n",
    "for key, value in dataPerSpecialty.items(): \n",
    "    print(key, value.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "4f4019d7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "General practicioner (201, 5)\n",
      "Internal medicine (201, 5)\n",
      "Internal medicine / pneumology (201, 5)\n"
     ]
    }
   ],
   "source": [
    "brand = 'brand_2'\n",
    "specialties = set(activity['specialty'].values.tolist())\n",
    "channels = set(activity['channel'].values.tolist())\n",
    "dataPerSpecialty = {}\n",
    "for specialty in specialties: \n",
    "    if specialty in ['Internal medicine and general practicioner', 'Pediatrician']: \n",
    "        continue\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "        sales_per_region.region, \n",
    "        activity_count, \n",
    "        unique_hcp_count, \n",
    "        nr_interactions, \n",
    "        market_potential\n",
    "    FROM (\n",
    "        SELECT \n",
    "            region, \n",
    "            SUM(sales) AS market_potential\n",
    "        FROM salesTrain\n",
    "        WHERE brand = 'brand_12_market'\n",
    "        GROUP BY region \n",
    "        ) AS sales_per_region\n",
    "    LEFT OUTER JOIN (\n",
    "        SELECT \n",
    "            region,\n",
    "            COUNT(hcp) AS activity_count, \n",
    "            COUNT(DISTINCT hcp) AS unique_hcp_count, \n",
    "            SUM(count) AS nr_interactions\n",
    "        FROM activity\n",
    "        WHERE brand = '{brand}' AND specialty = '{specialty}'\n",
    "        GROUP BY region\n",
    "        ) AS grouped_activity\n",
    "    ON grouped_activity.region = sales_per_region.region    \n",
    "    \"\"\".format(brand=brand, specialty=specialty)\n",
    "    df = makeQuery(query)\n",
    "    dataPerSpecialty[specialty] = df\n",
    "    \n",
    "for key, value in dataPerSpecialty.items(): \n",
    "    print(key, value.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "fef2ed1c",
   "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>region</th>\n",
       "      <th>activity_count</th>\n",
       "      <th>unique_hcp_count</th>\n",
       "      <th>nr_interactions</th>\n",
       "      <th>market_potential</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10666956.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_1</td>\n",
       "      <td>56.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>6803632.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_10</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5437642.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_100</td>\n",
       "      <td>45.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>5342599.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_101</td>\n",
       "      <td>19.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3954334.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>region_95</td>\n",
       "      <td>67.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>5409466.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>region_96</td>\n",
       "      <td>84.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>5974509.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>region_97</td>\n",
       "      <td>42.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>4901265.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>region_98</td>\n",
       "      <td>20.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>5386680.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>region_99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5618976.22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>201 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         region  activity_count  unique_hcp_count  nr_interactions  \\\n",
       "0      region_0            10.0               9.0             10.0   \n",
       "1      region_1            56.0              19.0             66.0   \n",
       "2     region_10            10.0               8.0             10.0   \n",
       "3    region_100            45.0              22.0             49.0   \n",
       "4    region_101            19.0              13.0             20.0   \n",
       "..          ...             ...               ...              ...   \n",
       "196   region_95            67.0              38.0             71.0   \n",
       "197   region_96            84.0              30.0             87.0   \n",
       "198   region_97            42.0              25.0             44.0   \n",
       "199   region_98            20.0              14.0             25.0   \n",
       "200   region_99             NaN               NaN              NaN   \n",
       "\n",
       "     market_potential  \n",
       "0         10666956.92  \n",
       "1          6803632.80  \n",
       "2          5437642.05  \n",
       "3          5342599.84  \n",
       "4          3954334.49  \n",
       "..                ...  \n",
       "196        5409466.07  \n",
       "197        5974509.31  \n",
       "198        4901265.54  \n",
       "199        5386680.46  \n",
       "200        5618976.22  \n",
       "\n",
       "[201 rows x 5 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataPerSpecialty['Internal medicine']"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f27d5e9f",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "allData = {}\n",
    "columnsOfInterest = ['activity_count', 'unique_hcp_count', 'nr_interactions']\n",
    "for specialty, data in dataPerSpecialty.items(): \n",
    "    # complete data\n",
    "    for channel in channels: \n",
    "        rowsToAdd = []\n",
    "        for i in range(151): \n",
    "            region = 'region_{}'.format(i)\n",
    "            currentRegion = data[data['region'] == region]\n",
    "            missingChannels = channels - set(currentRegion.channel.values.tolist())\n",
    "            for channel in missingChannels: \n",
    "                rowsToAdd.append([region, channel, currentRegion.at[currentRegion[currentRegion['region'] == region].index.values[0], 'total_sales'], 0, 0, 0])\n",
    "    data = pd.concat([data, pd.DataFrame(rowsToAdd, columns=data.columns)], axis=0)\n",
    "    \n",
    "    # reshape data\n",
    "    regionData = data.groupby('region').agg({'total_sales': 'first'})\n",
    "    regionData.reset_index(drop=False, inplace=True)\n",
    "    newColumns = []\n",
    "    newColumnNames = []\n",
    "    for channel in channels: \n",
    "        if 'total_sales' in data.columns: \n",
    "            data.drop('total_sales', axis=1, inplace=True)\n",
    "        values = {c: [] for c in columnsOfInterest}\n",
    "        for i in range(151): \n",
    "            region = 'region_{}'.format(i)\n",
    "            currentRegions = data[data['region'] == region]\n",
    "            for c in columnsOfInterest: \n",
    "                values[c].append(currentRegions[currentRegions['channel'] == channel][c].values[0])\n",
    "        for c, v in values.items(): \n",
    "            newColumns.append(v)\n",
    "            newColumnNames.append('{}_{}'.format(channel, c))\n",
    "        \n",
    "    temp = pd.DataFrame(newColumns).T\n",
    "    temp.rename(columns={i: newColumnNames[i] for i in range(temp.shape[1])}, inplace=True)\n",
    "    allData[specialty] = pd.concat([regionData, temp], axis=1)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "63aac829",
   "metadata": {},
   "source": [
    "allData[specialty]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "cc1314c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(201, 10)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allSpecialties = []\n",
    "# for specialty, data in allData.items(): \n",
    "for specialty, data in dataPerSpecialty.items(): \n",
    "    if specialty != 'Internal medicine': \n",
    "        data.drop('market_potential', axis=1, inplace=True)\n",
    "    data.fillna(0, inplace=True)\n",
    "    data.set_index('region', drop=True, inplace=True)\n",
    "    data.rename(columns={c: '{}_{}'.format(c, specialty) for c in data.columns if c not in ['region', 'total_sales']}, inplace=True)\n",
    "    allSpecialties.append(data)\n",
    "    \n",
    "allSpecialties = pd.concat(allSpecialties, axis=1)\n",
    "allSpecialties.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "7a92852c",
   "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>activity_count_General practicioner</th>\n",
       "      <th>unique_hcp_count_General practicioner</th>\n",
       "      <th>nr_interactions_General practicioner</th>\n",
       "      <th>activity_count_Internal medicine</th>\n",
       "      <th>unique_hcp_count_Internal medicine</th>\n",
       "      <th>nr_interactions_Internal medicine</th>\n",
       "      <th>market_potential_Internal medicine</th>\n",
       "      <th>activity_count_Internal medicine / pneumology</th>\n",
       "      <th>unique_hcp_count_Internal medicine / pneumology</th>\n",
       "      <th>nr_interactions_Internal medicine / pneumology</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>region_0</th>\n",
       "      <td>23.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10666956.92</td>\n",
       "      <td>45.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_1</th>\n",
       "      <td>157.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>6803632.80</td>\n",
       "      <td>61.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_10</th>\n",
       "      <td>23.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5437642.05</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_100</th>\n",
       "      <td>145.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>151.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>5342599.84</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_101</th>\n",
       "      <td>38.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>3954334.49</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            activity_count_General practicioner  \\\n",
       "region                                            \n",
       "region_0                                   23.0   \n",
       "region_1                                  157.0   \n",
       "region_10                                  23.0   \n",
       "region_100                                145.0   \n",
       "region_101                                 38.0   \n",
       "\n",
       "            unique_hcp_count_General practicioner  \\\n",
       "region                                              \n",
       "region_0                                     20.0   \n",
       "region_1                                     66.0   \n",
       "region_10                                    19.0   \n",
       "region_100                                   77.0   \n",
       "region_101                                   28.0   \n",
       "\n",
       "            nr_interactions_General practicioner  \\\n",
       "region                                             \n",
       "region_0                                    23.0   \n",
       "region_1                                   163.0   \n",
       "region_10                                   23.0   \n",
       "region_100                                 151.0   \n",
       "region_101                                  38.0   \n",
       "\n",
       "            activity_count_Internal medicine  \\\n",
       "region                                         \n",
       "region_0                                10.0   \n",
       "region_1                                56.0   \n",
       "region_10                               10.0   \n",
       "region_100                              45.0   \n",
       "region_101                              19.0   \n",
       "\n",
       "            unique_hcp_count_Internal medicine  \\\n",
       "region                                           \n",
       "region_0                                   9.0   \n",
       "region_1                                  19.0   \n",
       "region_10                                  8.0   \n",
       "region_100                                22.0   \n",
       "region_101                                13.0   \n",
       "\n",
       "            nr_interactions_Internal medicine  \\\n",
       "region                                          \n",
       "region_0                                 10.0   \n",
       "region_1                                 66.0   \n",
       "region_10                                10.0   \n",
       "region_100                               49.0   \n",
       "region_101                               20.0   \n",
       "\n",
       "            market_potential_Internal medicine  \\\n",
       "region                                           \n",
       "region_0                           10666956.92   \n",
       "region_1                            6803632.80   \n",
       "region_10                           5437642.05   \n",
       "region_100                          5342599.84   \n",
       "region_101                          3954334.49   \n",
       "\n",
       "            activity_count_Internal medicine / pneumology  \\\n",
       "region                                                      \n",
       "region_0                                             45.0   \n",
       "region_1                                             61.0   \n",
       "region_10                                             3.0   \n",
       "region_100                                           30.0   \n",
       "region_101                                            2.0   \n",
       "\n",
       "            unique_hcp_count_Internal medicine / pneumology  \\\n",
       "region                                                        \n",
       "region_0                                               13.0   \n",
       "region_1                                                7.0   \n",
       "region_10                                               1.0   \n",
       "region_100                                              5.0   \n",
       "region_101                                              1.0   \n",
       "\n",
       "            nr_interactions_Internal medicine / pneumology  \n",
       "region                                                      \n",
       "region_0                                              65.0  \n",
       "region_1                                              73.0  \n",
       "region_10                                              5.0  \n",
       "region_100                                            43.0  \n",
       "region_101                                             2.0  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allSpecialties.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "26d84a47",
   "metadata": {},
   "outputs": [],
   "source": [
    "viableFeatures = [ \n",
    "    # brand 1\n",
    "    #'unique_hcp_count_Internal medicine / pneumology',\n",
    "    #'activity_count_Internal medicine / pneumology',\n",
    "    #'nr_interactions_Internal medicine / pneumology',\n",
    "    #'unique_hcp_count_Internal medicine',\n",
    "    #'activity_count_Internal medicine',\n",
    "    #'nr_interactions_Internal medicine',\n",
    "    #'market_potential_Internal medicine',\n",
    "    \n",
    "    # brand 2\n",
    "    'activity_count_Internal medicine / pneumology',\n",
    "    'unique_hcp_count_Internal medicine / pneumology',\n",
    "    'activity_count_Internal medicine',\n",
    "    'nr_interactions_Internal medicine',\n",
    "    'nr_interactions_Internal medicine / pneumology',\n",
    "    'nr_interactions_General practicioner',\n",
    "    'activity_count_General practicioner',\n",
    "    'unique_hcp_count_General practicioner',\n",
    "    'unique_hcp_count_Internal medicine',\n",
    "    'market_potential_Internal medicine',\n",
    "]\n",
    "allSpecialties[viableFeatures].to_csv('./data/data_files/region_features/activity_features_{}.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "raw",
   "id": "3e46eecf",
   "metadata": {},
   "source": [
    "correlations = []\n",
    "for c in allSpecialties.columns: \n",
    "    if c in ['region', 'total_sales']: \n",
    "        continue\n",
    "    corr = round(pearsonr(allSpecialties[c].values, allSpecialties['total_sales'].values)[0], 3)\n",
    "    correlations.append((c, corr))\n",
    "    \n",
    "correlations = pd.DataFrame(correlations, columns=['column', 'correlation'])\n",
    "correlations.sort_values('correlation', ascending=False, inplace=True)\n",
    "correlationFiltered = correlations[correlations['correlation'].abs() > 0.10]\n",
    "correlationFiltered\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "16b9c9cc",
   "metadata": {},
   "source": [
    "for c in correlationFiltered['column'].values: \n",
    "    allSpecialties.plot.scatter(x=c, y='total_sales')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a671d107",
   "metadata": {},
   "source": [
    "### Make region dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "4de1e961",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "brand_1 (201, 29)\n",
      "brand_2 (201, 22)\n"
     ]
    }
   ],
   "source": [
    "regions = pd.read_csv('./data/data_files/regions.csv')\n",
    "regionHcps = pd.read_csv('./data/data_files/regions_hcps.csv')\n",
    "regions.set_index('region', inplace=True, drop=True)\n",
    "regionHcps.set_index('region', inplace=True, drop=True)\n",
    "\n",
    "brands = ['brand_1', 'brand_2']\n",
    "brandData = {}\n",
    "for brand in brands: \n",
    "    datasets = [regions, regionHcps]\n",
    "    if os.path.isfile('./data/data_files/region_features/activity_features_{}.csv'.format(brand)): \n",
    "        df = pd.read_csv('./data/data_files/region_features/activity_features_{}.csv'.format(brand), \n",
    "                                   index_col=0)\n",
    "        # df.set_index('region', inplace=True, drop=True)\n",
    "        datasets.append(df)\n",
    "        \n",
    "    if os.path.isfile('./data/data_files/region_features/rte_features_{}.csv'.format(brand)): \n",
    "        df = pd.read_csv('./data/data_files/region_features/rte_features_{}.csv'.format(brand), \n",
    "                                   index_col=0)\n",
    "        # df.set_index('region', inplace=True, drop=True)\n",
    "        datasets.append(df)\n",
    "        \n",
    "    df = pd.concat(datasets, axis=1)\n",
    "    print(brand, df.shape)\n",
    "    brandData[brand] = df.loc[:, ~df.columns.duplicated()]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "2c5e30b1",
   "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>population</th>\n",
       "      <th>area</th>\n",
       "      <th>pci16</th>\n",
       "      <th>pci18</th>\n",
       "      <th>Internal medicine</th>\n",
       "      <th>Internal medicine / pneumology</th>\n",
       "      <th>General practicioner</th>\n",
       "      <th>Internal medicine and general practicioner</th>\n",
       "      <th>Pediatrician</th>\n",
       "      <th>activity_count_Internal medicine / pneumology</th>\n",
       "      <th>unique_hcp_count_Internal medicine / pneumology</th>\n",
       "      <th>activity_count_Internal medicine</th>\n",
       "      <th>nr_interactions_Internal medicine</th>\n",
       "      <th>nr_interactions_Internal medicine / pneumology</th>\n",
       "      <th>nr_interactions_General practicioner</th>\n",
       "      <th>activity_count_General practicioner</th>\n",
       "      <th>unique_hcp_count_General practicioner</th>\n",
       "      <th>unique_hcp_count_Internal medicine</th>\n",
       "      <th>market_potential_Internal medicine</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>region_196</th>\n",
       "      <td>488935</td>\n",
       "      <td>4383.308467</td>\n",
       "      <td>47628.428064</td>\n",
       "      <td>50590.455429</td>\n",
       "      <td>204</td>\n",
       "      <td>5</td>\n",
       "      <td>326</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "      <td>24.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>153.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>5666663.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_197</th>\n",
       "      <td>367538</td>\n",
       "      <td>6688.080642</td>\n",
       "      <td>41370.782569</td>\n",
       "      <td>44005.413325</td>\n",
       "      <td>212</td>\n",
       "      <td>8</td>\n",
       "      <td>279</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4152148.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_198</th>\n",
       "      <td>374756</td>\n",
       "      <td>6500.068131</td>\n",
       "      <td>43452.297389</td>\n",
       "      <td>45337.580895</td>\n",
       "      <td>182</td>\n",
       "      <td>8</td>\n",
       "      <td>228</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>25.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5601796.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_199</th>\n",
       "      <td>319660</td>\n",
       "      <td>2309.103167</td>\n",
       "      <td>40976.821501</td>\n",
       "      <td>42691.767247</td>\n",
       "      <td>124</td>\n",
       "      <td>5</td>\n",
       "      <td>170</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>36.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>6112388.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region_200</th>\n",
       "      <td>395114</td>\n",
       "      <td>2598.725436</td>\n",
       "      <td>44282.292557</td>\n",
       "      <td>46215.140591</td>\n",
       "      <td>269</td>\n",
       "      <td>4</td>\n",
       "      <td>239</td>\n",
       "      <td>25</td>\n",
       "      <td>57</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>201.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>6644712.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            population         area         pci16         pci18  \\\n",
       "region                                                            \n",
       "region_196      488935  4383.308467  47628.428064  50590.455429   \n",
       "region_197      367538  6688.080642  41370.782569  44005.413325   \n",
       "region_198      374756  6500.068131  43452.297389  45337.580895   \n",
       "region_199      319660  2309.103167  40976.821501  42691.767247   \n",
       "region_200      395114  2598.725436  44282.292557  46215.140591   \n",
       "\n",
       "            Internal medicine  Internal medicine / pneumology  \\\n",
       "region                                                          \n",
       "region_196                204                               5   \n",
       "region_197                212                               8   \n",
       "region_198                182                               8   \n",
       "region_199                124                               5   \n",
       "region_200                269                               4   \n",
       "\n",
       "            General practicioner  Internal medicine and general practicioner  \\\n",
       "region                                                                         \n",
       "region_196                   326                                           3   \n",
       "region_197                   279                                           0   \n",
       "region_198                   228                                          12   \n",
       "region_199                   170                                           2   \n",
       "region_200                   239                                          25   \n",
       "\n",
       "            Pediatrician  activity_count_Internal medicine / pneumology  \\\n",
       "region                                                                    \n",
       "region_196            52                                           24.0   \n",
       "region_197            34                                            9.0   \n",
       "region_198            25                                           25.0   \n",
       "region_199            30                                           36.0   \n",
       "region_200            57                                           16.0   \n",
       "\n",
       "            unique_hcp_count_Internal medicine / pneumology  \\\n",
       "region                                                        \n",
       "region_196                                              5.0   \n",
       "region_197                                              4.0   \n",
       "region_198                                              6.0   \n",
       "region_199                                              5.0   \n",
       "region_200                                              3.0   \n",
       "\n",
       "            activity_count_Internal medicine  \\\n",
       "region                                         \n",
       "region_196                              59.0   \n",
       "region_197                               3.0   \n",
       "region_198                              29.0   \n",
       "region_199                              16.0   \n",
       "region_200                              50.0   \n",
       "\n",
       "            nr_interactions_Internal medicine  \\\n",
       "region                                          \n",
       "region_196                               61.0   \n",
       "region_197                                3.0   \n",
       "region_198                               29.0   \n",
       "region_199                               16.0   \n",
       "region_200                               55.0   \n",
       "\n",
       "            nr_interactions_Internal medicine / pneumology  \\\n",
       "region                                                       \n",
       "region_196                                            30.0   \n",
       "region_197                                            12.0   \n",
       "region_198                                            29.0   \n",
       "region_199                                            41.0   \n",
       "region_200                                            18.0   \n",
       "\n",
       "            nr_interactions_General practicioner  \\\n",
       "region                                             \n",
       "region_196                                 153.0   \n",
       "region_197                                  12.0   \n",
       "region_198                                 112.0   \n",
       "region_199                                  28.0   \n",
       "region_200                                 238.0   \n",
       "\n",
       "            activity_count_General practicioner  \\\n",
       "region                                            \n",
       "region_196                                129.0   \n",
       "region_197                                 11.0   \n",
       "region_198                                111.0   \n",
       "region_199                                 27.0   \n",
       "region_200                                201.0   \n",
       "\n",
       "            unique_hcp_count_General practicioner  \\\n",
       "region                                              \n",
       "region_196                                   56.0   \n",
       "region_197                                    8.0   \n",
       "region_198                                   49.0   \n",
       "region_199                                   18.0   \n",
       "region_200                                  105.0   \n",
       "\n",
       "            unique_hcp_count_Internal medicine  \\\n",
       "region                                           \n",
       "region_196                                34.0   \n",
       "region_197                                 2.0   \n",
       "region_198                                13.0   \n",
       "region_199                                10.0   \n",
       "region_200                                30.0   \n",
       "\n",
       "            market_potential_Internal medicine  \n",
       "region                                          \n",
       "region_196                          5666663.62  \n",
       "region_197                          4152148.78  \n",
       "region_198                          5601796.70  \n",
       "region_199                          6112388.64  \n",
       "region_200                          6644712.06  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brandData['brand_2'].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "7aeca2df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(151, 26) (50, 26)\n",
      "(151, 19) (50, 19)\n"
     ]
    }
   ],
   "source": [
    "for brand in brands: \n",
    "    df = brandData[brand]\n",
    "    train = df.loc[['region_{}'.format(i) for i in range(151)], :]\n",
    "    test = df.loc[['region_{}'.format(i) for i in range(151, 201)], :]\n",
    "    print(train.shape, test.shape)\n",
    "    train.to_csv('./data/data_files/region_features/training_{}.csv'.format(brand))\n",
    "    test.to_csv('./data/data_files/region_features/test_{}.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad305966",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}