Time-based-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e3f6e20e",
   "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",
    "from datetime import datetime\n",
    "\n",
    "makeQuery = lambda q: sqldf(q, globals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a5d35d82",
   "metadata": {},
   "outputs": [],
   "source": [
    "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_cleaned.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa94bee4",
   "metadata": {},
   "source": [
    "### Time based rte analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ed6618c3",
   "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>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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     hcp             specialty    region    brand       email_type  \\\n",
       "0  58555  General practicioner  region_0  brand_1          general   \n",
       "1    819  General practicioner  region_1  brand_2  product_related   \n",
       "2    819  General practicioner  region_1  brand_2          general   \n",
       "3    819  General practicioner  region_1  brand_1  product_related   \n",
       "4    819  General practicioner  region_1  brand_1          general   \n",
       "\n",
       "           content_id  no. openings  no. clicks            time_sent  \\\n",
       "0  a3D6f0000019jMvEAI             1           0  2021-03-02 08:42:00   \n",
       "1  a3D6f0000019gA2EAI             0           0  2020-07-16 11:58:00   \n",
       "2  a3D6f0000019jSnEAI             1           0  2020-08-17 14:47:00   \n",
       "3  a3D6f000000MtZ3EAK             0           0  2020-09-03 16:08:00   \n",
       "4  a3D6f000000N0nhEAC             1           0  2020-10-16 13:35:00   \n",
       "\n",
       "      time_last_opened time_last_clicked  \n",
       "0  2021-03-02 08:45:00               NaN  \n",
       "1                  NaN               NaN  \n",
       "2  2020-08-17 23:16:00               NaN  \n",
       "3                  NaN               NaN  \n",
       "4  2020-10-16 22:39:00               NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rtes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "981403eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "for timeColumn in ['time_sent', 'time_last_opened', 'time_last_clicked']: \n",
    "    rtes[timeColumn] = pd.to_datetime(rtes[timeColumn])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "50ecf041",
   "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",
       "      <th>month_sent</th>\n",
       "      <th>month_opened</th>\n",
       "      <th>month_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>NaT</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>nan-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>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>nan-nan</td>\n",
       "      <td>nan-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>NaT</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>nan-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>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>nan-nan</td>\n",
       "      <td>nan-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>NaT</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>nan-nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     hcp             specialty    region    brand       email_type  \\\n",
       "0  58555  General practicioner  region_0  brand_1          general   \n",
       "1    819  General practicioner  region_1  brand_2  product_related   \n",
       "2    819  General practicioner  region_1  brand_2          general   \n",
       "3    819  General practicioner  region_1  brand_1  product_related   \n",
       "4    819  General practicioner  region_1  brand_1          general   \n",
       "\n",
       "           content_id  no. openings  no. clicks           time_sent  \\\n",
       "0  a3D6f0000019jMvEAI             1           0 2021-03-02 08:42:00   \n",
       "1  a3D6f0000019gA2EAI             0           0 2020-07-16 11:58:00   \n",
       "2  a3D6f0000019jSnEAI             1           0 2020-08-17 14:47:00   \n",
       "3  a3D6f000000MtZ3EAK             0           0 2020-09-03 16:08:00   \n",
       "4  a3D6f000000N0nhEAC             1           0 2020-10-16 13:35:00   \n",
       "\n",
       "     time_last_opened time_last_clicked month_sent month_opened month_clicked  \n",
       "0 2021-03-02 08:45:00               NaT    2021-03      2021-03       nan-nan  \n",
       "1                 NaT               NaT    2020-07      nan-nan       nan-nan  \n",
       "2 2020-08-17 23:16:00               NaT    2020-08      2020-08       nan-nan  \n",
       "3                 NaT               NaT    2020-09      nan-nan       nan-nan  \n",
       "4 2020-10-16 22:39:00               NaT    2020-10      2020-10       nan-nan  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rtes['month_sent'] = rtes['time_sent'].apply(lambda x: '{}-{}'.format(x.year, '0'+str(x.month) if x.month < 10 else x.month))\n",
    "rtes['month_opened'] = rtes['time_last_opened'].apply(lambda x: '{}-{}'.format(x.year, '0'+str(x.month) if x.month < 10 else x.month))\n",
    "rtes['month_clicked'] = rtes['time_last_clicked'].apply(lambda x: '{}-{}'.format(x.year, '0'+str(x.month) if x.month < 10 else x.month))\n",
    "rtes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "1a5ce73c",
   "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>month_sent</th>\n",
       "      <th>tier</th>\n",
       "      <th>nr_contacts</th>\n",
       "      <th>hcp_contacted</th>\n",
       "      <th>mails_opened</th>\n",
       "      <th>mails_clicked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>22</td>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     region month_sent  tier  nr_contacts  hcp_contacted  mails_opened  \\\n",
       "0  region_0    2021-03     2            1              1             1   \n",
       "1  region_1    2020-09     1           10              5            10   \n",
       "2  region_1    2020-09     2           42             22            42   \n",
       "3  region_1    2020-10     2            1              1             1   \n",
       "4  region_1    2020-11     2            3              3             3   \n",
       "\n",
       "   mails_clicked  \n",
       "0              1  \n",
       "1             10  \n",
       "2             42  \n",
       "3              1  \n",
       "4              3  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand = 'brand_1'\n",
    "query = \"\"\"\n",
    "    SELECT \n",
    "        rtes.region,\n",
    "        month_sent,\n",
    "        tier,\n",
    "        COUNT(rtes.hcp) AS nr_contacts, \n",
    "        COUNT(DISTINCT rtes.hcp) AS hcp_contacted, \n",
    "        COUNT(month_opened) AS mails_opened, \n",
    "        COUNT(month_clicked) AS mails_clicked\n",
    "        \n",
    "    FROM rtes\n",
    "    INNER JOIN hcps\n",
    "    ON hcps.hcp = rtes.hcp\n",
    "    WHERE brand = '{}'\n",
    "    GROUP BY rtes.region, month_sent, tier\n",
    "    \n",
    "\"\"\".format(brand)\n",
    "rtesMonthly = makeQuery(query)\n",
    "rtesMonthly.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "49bccb55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2100, 7)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rtesMonthly.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c112c1cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4020, 10)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rteTier = []\n",
    "columns = ['nr_contacts', 'hcp_contacted', 'mails_opened', 'mails_clicked']\n",
    "regions = ['region_{}'.format(i) for i in range(201)]\n",
    "months = set([datetime.strptime(m, '%Y-%m') for m in salesTrain.month.values])\n",
    "months = list(months)\n",
    "months.sort()\n",
    "months = ['{}-{}'.format(d.year, '0'+str(d.month) if d.month < 10 else d.month) for d in months]\n",
    "for region in regions: \n",
    "    regionDf = rtesMonthly[rtesMonthly.region == region]\n",
    "    for month in months: \n",
    "        monthDf = regionDf[regionDf.month_sent == month]\n",
    "        row = [region, month]\n",
    "        \n",
    "        if monthDf.shape[0] == 0: \n",
    "            row.extend([0, 0, 0, 0, 0, 0, 0, 0])\n",
    "            rteTier.append(row)\n",
    "            continue \n",
    "            \n",
    "        for tier in [1, 2]: \n",
    "            tierDf = monthDf[monthDf.tier == tier]\n",
    "            if tierDf.shape[0] == 0: \n",
    "                row.extend([0, 0, 0, 0])\n",
    "            else: \n",
    "                row.extend(tierDf[columns].values[0].tolist())\n",
    "        rteTier.append(row)\n",
    "            \n",
    "rteTierColumns = ['region', 'month_sent']\n",
    "for tier in [1, 2]: \n",
    "    rteTierColumns.extend(['{}_{}'.format(c, tier) for c in columns])\n",
    "    \n",
    "rteTier = pd.DataFrame(rteTier, columns=rteTierColumns)\n",
    "rteTier.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "43bce113",
   "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>month_sent</th>\n",
       "      <th>nr_contacts_1</th>\n",
       "      <th>hcp_contacted_1</th>\n",
       "      <th>mails_opened_1</th>\n",
       "      <th>mails_clicked_1</th>\n",
       "      <th>nr_contacts_2</th>\n",
       "      <th>hcp_contacted_2</th>\n",
       "      <th>mails_opened_2</th>\n",
       "      <th>mails_clicked_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     region month_sent  nr_contacts_1  hcp_contacted_1  mails_opened_1  \\\n",
       "0  region_0    2020-01              0                0               0   \n",
       "1  region_0    2020-02              0                0               0   \n",
       "2  region_0    2020-03              0                0               0   \n",
       "3  region_0    2020-04              0                0               0   \n",
       "4  region_0    2020-05              0                0               0   \n",
       "5  region_0    2020-06              0                0               0   \n",
       "6  region_0    2020-07              0                0               0   \n",
       "7  region_0    2020-08              0                0               0   \n",
       "8  region_0    2020-09              0                0               0   \n",
       "9  region_0    2020-10              0                0               0   \n",
       "\n",
       "   mails_clicked_1  nr_contacts_2  hcp_contacted_2  mails_opened_2  \\\n",
       "0                0              0                0               0   \n",
       "1                0              0                0               0   \n",
       "2                0              0                0               0   \n",
       "3                0              0                0               0   \n",
       "4                0              0                0               0   \n",
       "5                0              0                0               0   \n",
       "6                0              0                0               0   \n",
       "7                0              0                0               0   \n",
       "8                0              0                0               0   \n",
       "9                0              0                0               0   \n",
       "\n",
       "   mails_clicked_2  \n",
       "0                0  \n",
       "1                0  \n",
       "2                0  \n",
       "3                0  \n",
       "4                0  \n",
       "5                0  \n",
       "6                0  \n",
       "7                0  \n",
       "8                0  \n",
       "9                0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rteTier.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a9551c6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "rteTier.fillna(0, inplace=True)\n",
    "rteTier.region = pd.Categorical(rteTier.region, regions)\n",
    "rteTier.rename(columns={'month_sent': 'month'}, inplace=True)\n",
    "rteTier.month = pd.Categorical(rteTier.month, months)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d20c2a85",
   "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>region</th>\n",
       "      <th>month</th>\n",
       "      <th>nr_contacts_1</th>\n",
       "      <th>hcp_contacted_1</th>\n",
       "      <th>mails_opened_1</th>\n",
       "      <th>mails_clicked_1</th>\n",
       "      <th>nr_contacts_2</th>\n",
       "      <th>hcp_contacted_2</th>\n",
       "      <th>mails_opened_2</th>\n",
       "      <th>mails_clicked_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      region    month  nr_contacts_1  hcp_contacted_1  mails_opened_1  \\\n",
       "0   region_0  2020-01              0                0               0   \n",
       "1   region_0  2020-02              0                0               0   \n",
       "2   region_0  2020-03              0                0               0   \n",
       "3   region_0  2020-04              0                0               0   \n",
       "4   region_0  2020-05              0                0               0   \n",
       "5   region_0  2020-06              0                0               0   \n",
       "6   region_0  2020-07              0                0               0   \n",
       "7   region_0  2020-08              0                0               0   \n",
       "8   region_0  2020-09              0                0               0   \n",
       "9   region_0  2020-10              0                0               0   \n",
       "10  region_0  2020-11              0                0               0   \n",
       "11  region_0  2020-12              0                0               0   \n",
       "12  region_0  2021-01              0                0               0   \n",
       "13  region_0  2021-02              0                0               0   \n",
       "14  region_0  2021-03              0                0               0   \n",
       "15  region_0  2021-04              0                0               0   \n",
       "16  region_0  2021-05              0                0               0   \n",
       "17  region_0  2021-06              0                0               0   \n",
       "18  region_0  2021-07              0                0               0   \n",
       "19  region_0  2021-08              0                0               0   \n",
       "20  region_1  2020-01              0                0               0   \n",
       "21  region_1  2020-02              0                0               0   \n",
       "22  region_1  2020-03              0                0               0   \n",
       "23  region_1  2020-04              0                0               0   \n",
       "24  region_1  2020-05              0                0               0   \n",
       "\n",
       "    mails_clicked_1  nr_contacts_2  hcp_contacted_2  mails_opened_2  \\\n",
       "0                 0              0                0               0   \n",
       "1                 0              0                0               0   \n",
       "2                 0              0                0               0   \n",
       "3                 0              0                0               0   \n",
       "4                 0              0                0               0   \n",
       "5                 0              0                0               0   \n",
       "6                 0              0                0               0   \n",
       "7                 0              0                0               0   \n",
       "8                 0              0                0               0   \n",
       "9                 0              0                0               0   \n",
       "10                0              0                0               0   \n",
       "11                0              0                0               0   \n",
       "12                0              0                0               0   \n",
       "13                0              0                0               0   \n",
       "14                0              1                1               1   \n",
       "15                0              0                0               0   \n",
       "16                0              0                0               0   \n",
       "17                0              0                0               0   \n",
       "18                0              0                0               0   \n",
       "19                0              0                0               0   \n",
       "20                0              0                0               0   \n",
       "21                0              0                0               0   \n",
       "22                0              0                0               0   \n",
       "23                0              0                0               0   \n",
       "24                0              0                0               0   \n",
       "\n",
       "    mails_clicked_2  \n",
       "0                 0  \n",
       "1                 0  \n",
       "2                 0  \n",
       "3                 0  \n",
       "4                 0  \n",
       "5                 0  \n",
       "6                 0  \n",
       "7                 0  \n",
       "8                 0  \n",
       "9                 0  \n",
       "10                0  \n",
       "11                0  \n",
       "12                0  \n",
       "13                0  \n",
       "14                1  \n",
       "15                0  \n",
       "16                0  \n",
       "17                0  \n",
       "18                0  \n",
       "19                0  \n",
       "20                0  \n",
       "21                0  \n",
       "22                0  \n",
       "23                0  \n",
       "24                0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rteTier.sort_values(['region', 'month'], ascending=True, inplace=True)\n",
    "rteTier.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c50f0ae7",
   "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>region</th>\n",
       "      <th>month</th>\n",
       "      <th>nr_contacts_1</th>\n",
       "      <th>hcp_contacted_1</th>\n",
       "      <th>mails_opened_1</th>\n",
       "      <th>mails_clicked_1</th>\n",
       "      <th>nr_contacts_2</th>\n",
       "      <th>hcp_contacted_2</th>\n",
       "      <th>mails_opened_2</th>\n",
       "      <th>mails_clicked_2</th>\n",
       "      <th>delta_nr_contacts_1</th>\n",
       "      <th>delta_hcp_contacted_1</th>\n",
       "      <th>delta_mails_opened_1</th>\n",
       "      <th>delta_mails_clicked_1</th>\n",
       "      <th>delta_nr_contacts_2</th>\n",
       "      <th>delta_hcp_contacted_2</th>\n",
       "      <th>delta_mails_opened_2</th>\n",
       "      <th>delta_mails_clicked_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      region    month  nr_contacts_1  hcp_contacted_1  mails_opened_1  \\\n",
       "0   region_0  2020-01              0                0               0   \n",
       "1   region_0  2020-02              0                0               0   \n",
       "2   region_0  2020-03              0                0               0   \n",
       "3   region_0  2020-04              0                0               0   \n",
       "4   region_0  2020-05              0                0               0   \n",
       "5   region_0  2020-06              0                0               0   \n",
       "6   region_0  2020-07              0                0               0   \n",
       "7   region_0  2020-08              0                0               0   \n",
       "8   region_0  2020-09              0                0               0   \n",
       "9   region_0  2020-10              0                0               0   \n",
       "10  region_0  2020-11              0                0               0   \n",
       "11  region_0  2020-12              0                0               0   \n",
       "12  region_0  2021-01              0                0               0   \n",
       "13  region_0  2021-02              0                0               0   \n",
       "14  region_0  2021-03              0                0               0   \n",
       "15  region_0  2021-04              0                0               0   \n",
       "16  region_0  2021-05              0                0               0   \n",
       "17  region_0  2021-06              0                0               0   \n",
       "18  region_0  2021-07              0                0               0   \n",
       "19  region_0  2021-08              0                0               0   \n",
       "20  region_1  2020-01              0                0               0   \n",
       "21  region_1  2020-02              0                0               0   \n",
       "22  region_1  2020-03              0                0               0   \n",
       "23  region_1  2020-04              0                0               0   \n",
       "24  region_1  2020-05              0                0               0   \n",
       "\n",
       "    mails_clicked_1  nr_contacts_2  hcp_contacted_2  mails_opened_2  \\\n",
       "0                 0              0                0               0   \n",
       "1                 0              0                0               0   \n",
       "2                 0              0                0               0   \n",
       "3                 0              0                0               0   \n",
       "4                 0              0                0               0   \n",
       "5                 0              0                0               0   \n",
       "6                 0              0                0               0   \n",
       "7                 0              0                0               0   \n",
       "8                 0              0                0               0   \n",
       "9                 0              0                0               0   \n",
       "10                0              0                0               0   \n",
       "11                0              0                0               0   \n",
       "12                0              0                0               0   \n",
       "13                0              0                0               0   \n",
       "14                0              1                1               1   \n",
       "15                0              0                0               0   \n",
       "16                0              0                0               0   \n",
       "17                0              0                0               0   \n",
       "18                0              0                0               0   \n",
       "19                0              0                0               0   \n",
       "20                0              0                0               0   \n",
       "21                0              0                0               0   \n",
       "22                0              0                0               0   \n",
       "23                0              0                0               0   \n",
       "24                0              0                0               0   \n",
       "\n",
       "    mails_clicked_2  delta_nr_contacts_1  delta_hcp_contacted_1  \\\n",
       "0                 0                  0.0                    0.0   \n",
       "1                 0                  0.0                    0.0   \n",
       "2                 0                  0.0                    0.0   \n",
       "3                 0                  0.0                    0.0   \n",
       "4                 0                  0.0                    0.0   \n",
       "5                 0                  0.0                    0.0   \n",
       "6                 0                  0.0                    0.0   \n",
       "7                 0                  0.0                    0.0   \n",
       "8                 0                  0.0                    0.0   \n",
       "9                 0                  0.0                    0.0   \n",
       "10                0                  0.0                    0.0   \n",
       "11                0                  0.0                    0.0   \n",
       "12                0                  0.0                    0.0   \n",
       "13                0                  0.0                    0.0   \n",
       "14                1                  0.0                    0.0   \n",
       "15                0                  0.0                    0.0   \n",
       "16                0                  0.0                    0.0   \n",
       "17                0                  0.0                    0.0   \n",
       "18                0                  0.0                    0.0   \n",
       "19                0                  0.0                    0.0   \n",
       "20                0                  0.0                    0.0   \n",
       "21                0                  0.0                    0.0   \n",
       "22                0                  0.0                    0.0   \n",
       "23                0                  0.0                    0.0   \n",
       "24                0                  0.0                    0.0   \n",
       "\n",
       "    delta_mails_opened_1  delta_mails_clicked_1  delta_nr_contacts_2  \\\n",
       "0                    0.0                    0.0                  0.0   \n",
       "1                    0.0                    0.0                  0.0   \n",
       "2                    0.0                    0.0                  0.0   \n",
       "3                    0.0                    0.0                  0.0   \n",
       "4                    0.0                    0.0                  0.0   \n",
       "5                    0.0                    0.0                  0.0   \n",
       "6                    0.0                    0.0                  0.0   \n",
       "7                    0.0                    0.0                  0.0   \n",
       "8                    0.0                    0.0                  0.0   \n",
       "9                    0.0                    0.0                  0.0   \n",
       "10                   0.0                    0.0                  0.0   \n",
       "11                   0.0                    0.0                  0.0   \n",
       "12                   0.0                    0.0                  0.0   \n",
       "13                   0.0                    0.0                  0.0   \n",
       "14                   0.0                    0.0                  1.0   \n",
       "15                   0.0                    0.0                 -1.0   \n",
       "16                   0.0                    0.0                  0.0   \n",
       "17                   0.0                    0.0                  0.0   \n",
       "18                   0.0                    0.0                  0.0   \n",
       "19                   0.0                    0.0                  0.0   \n",
       "20                   0.0                    0.0                  0.0   \n",
       "21                   0.0                    0.0                  0.0   \n",
       "22                   0.0                    0.0                  0.0   \n",
       "23                   0.0                    0.0                  0.0   \n",
       "24                   0.0                    0.0                  0.0   \n",
       "\n",
       "    delta_hcp_contacted_2  delta_mails_opened_2  delta_mails_clicked_2  \n",
       "0                     0.0                   0.0                    0.0  \n",
       "1                     0.0                   0.0                    0.0  \n",
       "2                     0.0                   0.0                    0.0  \n",
       "3                     0.0                   0.0                    0.0  \n",
       "4                     0.0                   0.0                    0.0  \n",
       "5                     0.0                   0.0                    0.0  \n",
       "6                     0.0                   0.0                    0.0  \n",
       "7                     0.0                   0.0                    0.0  \n",
       "8                     0.0                   0.0                    0.0  \n",
       "9                     0.0                   0.0                    0.0  \n",
       "10                    0.0                   0.0                    0.0  \n",
       "11                    0.0                   0.0                    0.0  \n",
       "12                    0.0                   0.0                    0.0  \n",
       "13                    0.0                   0.0                    0.0  \n",
       "14                    1.0                   1.0                    1.0  \n",
       "15                   -1.0                  -1.0                   -1.0  \n",
       "16                    0.0                   0.0                    0.0  \n",
       "17                    0.0                   0.0                    0.0  \n",
       "18                    0.0                   0.0                    0.0  \n",
       "19                    0.0                   0.0                    0.0  \n",
       "20                    0.0                   0.0                    0.0  \n",
       "21                    0.0                   0.0                    0.0  \n",
       "22                    0.0                   0.0                    0.0  \n",
       "23                    0.0                   0.0                    0.0  \n",
       "24                    0.0                   0.0                    0.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deltas = []\n",
    "for region in regions: \n",
    "    regionDf = rteTier[rteTier.region == region].copy()\n",
    "    firstIndex = regionDf.index.values[0]\n",
    "    for c in regionDf.columns:  \n",
    "        if c in ['region', 'month']: \n",
    "            continue\n",
    "    \n",
    "        regionDf['delta_{}'.format(c)] = regionDf[c].diff()\n",
    "        regionDf.at[firstIndex, 'delta_{}'.format(c)] = 0\n",
    "    deltas.append(regionDf)\n",
    "deltas = pd.concat(deltas, axis=0)\n",
    "deltas.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "24042722",
   "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>month</th>\n",
       "      <th>delta_nr_contacts_1</th>\n",
       "      <th>delta_hcp_contacted_1</th>\n",
       "      <th>delta_mails_opened_1</th>\n",
       "      <th>delta_mails_clicked_1</th>\n",
       "      <th>delta_nr_contacts_2</th>\n",
       "      <th>delta_hcp_contacted_2</th>\n",
       "      <th>delta_mails_opened_2</th>\n",
       "      <th>delta_mails_clicked_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     region    month  delta_nr_contacts_1  delta_hcp_contacted_1  \\\n",
       "0  region_0  2020-01                  0.0                    0.0   \n",
       "1  region_0  2020-02                  0.0                    0.0   \n",
       "2  region_0  2020-03                  0.0                    0.0   \n",
       "3  region_0  2020-04                  0.0                    0.0   \n",
       "4  region_0  2020-05                  0.0                    0.0   \n",
       "\n",
       "   delta_mails_opened_1  delta_mails_clicked_1  delta_nr_contacts_2  \\\n",
       "0                   0.0                    0.0                  0.0   \n",
       "1                   0.0                    0.0                  0.0   \n",
       "2                   0.0                    0.0                  0.0   \n",
       "3                   0.0                    0.0                  0.0   \n",
       "4                   0.0                    0.0                  0.0   \n",
       "\n",
       "   delta_hcp_contacted_2  delta_mails_opened_2  delta_mails_clicked_2  \n",
       "0                    0.0                   0.0                    0.0  \n",
       "1                    0.0                   0.0                    0.0  \n",
       "2                    0.0                   0.0                    0.0  \n",
       "3                    0.0                   0.0                    0.0  \n",
       "4                    0.0                   0.0                    0.0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deltas.drop([c for c in rteTier.columns if c not in ['region', 'month']], axis=1, inplace=True)\n",
    "deltas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a0fc875f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.any(deltas.isna().values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "37571628",
   "metadata": {},
   "outputs": [],
   "source": [
    "deltas.to_csv('./data/data_files/time_features/rte_delta_{}.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2814546",
   "metadata": {},
   "source": [
    "### Time based activity analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "cbca42d4",
   "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": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activity.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "8aab5d80",
   "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>region</th>\n",
       "      <th>tier</th>\n",
       "      <th>month</th>\n",
       "      <th>channel</th>\n",
       "      <th>interactions</th>\n",
       "      <th>distinct_interactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>f2f</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>phone</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>f2f</td>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>phone</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>f2f</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>f2f</td>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>phone</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>f2f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>phone</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>f2f</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>f2f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>phone</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-07</td>\n",
       "      <td>f2f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>region_0</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>f2f</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>region_1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>f2f</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>region_1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>phone</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>f2f</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>phone</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>region_1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>phone</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>f2f</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>phone</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>region_1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>f2f</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>region_1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>phone</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>f2f</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>region_1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>f2f</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      region  tier    month channel  interactions  distinct_interactions\n",
       "0   region_0     1  2020-07     f2f            10                      9\n",
       "1   region_0     1  2020-07   phone             1                      1\n",
       "2   region_0     1  2020-08     f2f            23                     12\n",
       "3   region_0     1  2020-08   phone             3                      3\n",
       "4   region_0     2  2020-08     f2f             4                      4\n",
       "5   region_0     1  2020-09     f2f            18                     10\n",
       "6   region_0     1  2020-09   phone             3                      3\n",
       "7   region_0     2  2020-09     f2f             1                      1\n",
       "8   region_0     2  2020-09   phone             2                      2\n",
       "9   region_0     1  2020-10     f2f             2                      2\n",
       "10  region_0     2  2020-10     f2f             1                      1\n",
       "11  region_0     1  2020-11   phone             1                      1\n",
       "12  region_0     1  2021-07     f2f             1                      1\n",
       "13  region_0     1  2021-08     f2f             3                      3\n",
       "14  region_1     1  2020-07     f2f            10                      8\n",
       "15  region_1     1  2020-07   phone             3                      3\n",
       "16  region_1     2  2020-07     f2f            11                     11\n",
       "17  region_1     2  2020-07   phone             6                      6\n",
       "18  region_1     1  2020-08   phone             5                      2\n",
       "19  region_1     2  2020-08     f2f            10                     10\n",
       "20  region_1     2  2020-08   phone             5                      5\n",
       "21  region_1     1  2020-09     f2f             5                      5\n",
       "22  region_1     1  2020-09   phone             2                      1\n",
       "23  region_1     2  2020-09     f2f             3                      3\n",
       "24  region_1     1  2020-10     f2f             7                      5"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand = 'brand_2'\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    activity.region,\n",
    "    hcps.tier,  \n",
    "    month, \n",
    "    channel,\n",
    "    -- hcps.hcp, \n",
    "    SUM(count) AS interactions, \n",
    "    COUNT(DISTINCT hcps.hcp) AS distinct_interactions\n",
    "FROM activity\n",
    "INNER JOIN hcps \n",
    "ON hcps.hcp = activity.hcp\n",
    "WHERE brand = '{}'\n",
    "GROUP BY activity.region, month, tier, channel\n",
    "\"\"\".format(brand)\n",
    "df = makeQuery(query)\n",
    "df.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "b2b20d88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4020, 18)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activityTier = []\n",
    "columns = ['interactions', 'distinct_interactions']\n",
    "regions = ['region_{}'.format(i) for i in range(201)]\n",
    "channels = list(set(df.channel.values.tolist()))\n",
    "tiers = [1, 2]\n",
    "months = set([datetime.strptime(m, '%Y-%m') for m in salesTrain.month.values])\n",
    "months = list(months)\n",
    "months.sort()\n",
    "months = ['{}-{}'.format(d.year, '0'+str(d.month) if d.month < 10 else d.month) for d in months]\n",
    "for region in regions: \n",
    "    regionDf = df[df.region == region]\n",
    "    for month in months: \n",
    "        monthDf = regionDf[regionDf.month == month]\n",
    "        row = [region, month]\n",
    "        \n",
    "        if monthDf.shape[0] == 0: \n",
    "            row.extend([0]*len(tiers)*len(channels)*len(columns))\n",
    "            activityTier.append(row)\n",
    "            continue \n",
    "            \n",
    "        for tier in tiers: \n",
    "            tierDf = monthDf[monthDf.tier == tier]\n",
    "            if tierDf.shape[0] == 0: \n",
    "                row.extend([0]*len(channels) * len(columns))\n",
    "                continue\n",
    "            \n",
    "            for channel in channels: \n",
    "                channelDf = tierDf[tierDf.channel == channel]\n",
    "\n",
    "                if channelDf.shape[0] == 0: \n",
    "                    row.extend([0] * len(columns))\n",
    "                else: \n",
    "                    row.extend(channelDf[columns].values[0].tolist())\n",
    "                    \n",
    "        activityTier.append(row)\n",
    "            \n",
    "activityTierColumns = ['region', 'month']\n",
    "for tier in [1, 2]: \n",
    "    for channel in channels: \n",
    "        activityTierColumns.extend(['{}_{}_{}'.format(c, tier, channel) for c in columns])\n",
    "    \n",
    "activityTier = pd.DataFrame(activityTier, columns=activityTierColumns)\n",
    "activityTier.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "7e419657",
   "metadata": {
    "collapsed": 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>region</th>\n",
       "      <th>month</th>\n",
       "      <th>interactions_1_f2f</th>\n",
       "      <th>distinct_interactions_1_f2f</th>\n",
       "      <th>interactions_1_other</th>\n",
       "      <th>distinct_interactions_1_other</th>\n",
       "      <th>interactions_1_phone</th>\n",
       "      <th>distinct_interactions_1_phone</th>\n",
       "      <th>interactions_1_video</th>\n",
       "      <th>distinct_interactions_1_video</th>\n",
       "      <th>interactions_2_f2f</th>\n",
       "      <th>distinct_interactions_2_f2f</th>\n",
       "      <th>interactions_2_other</th>\n",
       "      <th>distinct_interactions_2_other</th>\n",
       "      <th>interactions_2_phone</th>\n",
       "      <th>distinct_interactions_2_phone</th>\n",
       "      <th>interactions_2_video</th>\n",
       "      <th>distinct_interactions_2_video</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-06</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-07</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      region    month  interactions_1_f2f  distinct_interactions_1_f2f  \\\n",
       "0   region_0  2020-01                   0                            0   \n",
       "1   region_0  2020-02                   0                            0   \n",
       "2   region_0  2020-03                   0                            0   \n",
       "3   region_0  2020-04                   0                            0   \n",
       "4   region_0  2020-05                   0                            0   \n",
       "5   region_0  2020-06                   0                            0   \n",
       "6   region_0  2020-07                  10                            9   \n",
       "7   region_0  2020-08                  23                           12   \n",
       "8   region_0  2020-09                  18                           10   \n",
       "9   region_0  2020-10                   2                            2   \n",
       "10  region_0  2020-11                   0                            0   \n",
       "11  region_0  2020-12                   0                            0   \n",
       "12  region_0  2021-01                   0                            0   \n",
       "13  region_0  2021-02                   0                            0   \n",
       "14  region_0  2021-03                   0                            0   \n",
       "15  region_0  2021-04                   0                            0   \n",
       "16  region_0  2021-05                   0                            0   \n",
       "17  region_0  2021-06                   0                            0   \n",
       "18  region_0  2021-07                   1                            1   \n",
       "19  region_0  2021-08                   3                            3   \n",
       "20  region_1  2020-01                   0                            0   \n",
       "21  region_1  2020-02                   0                            0   \n",
       "22  region_1  2020-03                   0                            0   \n",
       "23  region_1  2020-04                   0                            0   \n",
       "24  region_1  2020-05                   0                            0   \n",
       "\n",
       "    interactions_1_other  distinct_interactions_1_other  interactions_1_phone  \\\n",
       "0                      0                              0                     0   \n",
       "1                      0                              0                     0   \n",
       "2                      0                              0                     0   \n",
       "3                      0                              0                     0   \n",
       "4                      0                              0                     0   \n",
       "5                      0                              0                     0   \n",
       "6                      0                              0                     1   \n",
       "7                      0                              0                     3   \n",
       "8                      0                              0                     3   \n",
       "9                      0                              0                     0   \n",
       "10                     0                              0                     1   \n",
       "11                     0                              0                     0   \n",
       "12                     0                              0                     0   \n",
       "13                     0                              0                     0   \n",
       "14                     0                              0                     0   \n",
       "15                     0                              0                     0   \n",
       "16                     0                              0                     0   \n",
       "17                     0                              0                     0   \n",
       "18                     0                              0                     0   \n",
       "19                     0                              0                     0   \n",
       "20                     0                              0                     0   \n",
       "21                     0                              0                     0   \n",
       "22                     0                              0                     0   \n",
       "23                     0                              0                     0   \n",
       "24                     0                              0                     0   \n",
       "\n",
       "    distinct_interactions_1_phone  interactions_1_video  \\\n",
       "0                               0                     0   \n",
       "1                               0                     0   \n",
       "2                               0                     0   \n",
       "3                               0                     0   \n",
       "4                               0                     0   \n",
       "5                               0                     0   \n",
       "6                               1                     0   \n",
       "7                               3                     0   \n",
       "8                               3                     0   \n",
       "9                               0                     0   \n",
       "10                              1                     0   \n",
       "11                              0                     0   \n",
       "12                              0                     0   \n",
       "13                              0                     0   \n",
       "14                              0                     0   \n",
       "15                              0                     0   \n",
       "16                              0                     0   \n",
       "17                              0                     0   \n",
       "18                              0                     0   \n",
       "19                              0                     0   \n",
       "20                              0                     0   \n",
       "21                              0                     0   \n",
       "22                              0                     0   \n",
       "23                              0                     0   \n",
       "24                              0                     0   \n",
       "\n",
       "    distinct_interactions_1_video  interactions_2_f2f  \\\n",
       "0                               0                   0   \n",
       "1                               0                   0   \n",
       "2                               0                   0   \n",
       "3                               0                   0   \n",
       "4                               0                   0   \n",
       "5                               0                   0   \n",
       "6                               0                   0   \n",
       "7                               0                   4   \n",
       "8                               0                   1   \n",
       "9                               0                   1   \n",
       "10                              0                   0   \n",
       "11                              0                   0   \n",
       "12                              0                   0   \n",
       "13                              0                   0   \n",
       "14                              0                   0   \n",
       "15                              0                   0   \n",
       "16                              0                   0   \n",
       "17                              0                   0   \n",
       "18                              0                   0   \n",
       "19                              0                   0   \n",
       "20                              0                   0   \n",
       "21                              0                   0   \n",
       "22                              0                   0   \n",
       "23                              0                   0   \n",
       "24                              0                   0   \n",
       "\n",
       "    distinct_interactions_2_f2f  interactions_2_other  \\\n",
       "0                             0                     0   \n",
       "1                             0                     0   \n",
       "2                             0                     0   \n",
       "3                             0                     0   \n",
       "4                             0                     0   \n",
       "5                             0                     0   \n",
       "6                             0                     0   \n",
       "7                             4                     0   \n",
       "8                             1                     0   \n",
       "9                             1                     0   \n",
       "10                            0                     0   \n",
       "11                            0                     0   \n",
       "12                            0                     0   \n",
       "13                            0                     0   \n",
       "14                            0                     0   \n",
       "15                            0                     0   \n",
       "16                            0                     0   \n",
       "17                            0                     0   \n",
       "18                            0                     0   \n",
       "19                            0                     0   \n",
       "20                            0                     0   \n",
       "21                            0                     0   \n",
       "22                            0                     0   \n",
       "23                            0                     0   \n",
       "24                            0                     0   \n",
       "\n",
       "    distinct_interactions_2_other  interactions_2_phone  \\\n",
       "0                               0                     0   \n",
       "1                               0                     0   \n",
       "2                               0                     0   \n",
       "3                               0                     0   \n",
       "4                               0                     0   \n",
       "5                               0                     0   \n",
       "6                               0                     0   \n",
       "7                               0                     0   \n",
       "8                               0                     2   \n",
       "9                               0                     0   \n",
       "10                              0                     0   \n",
       "11                              0                     0   \n",
       "12                              0                     0   \n",
       "13                              0                     0   \n",
       "14                              0                     0   \n",
       "15                              0                     0   \n",
       "16                              0                     0   \n",
       "17                              0                     0   \n",
       "18                              0                     0   \n",
       "19                              0                     0   \n",
       "20                              0                     0   \n",
       "21                              0                     0   \n",
       "22                              0                     0   \n",
       "23                              0                     0   \n",
       "24                              0                     0   \n",
       "\n",
       "    distinct_interactions_2_phone  interactions_2_video  \\\n",
       "0                               0                     0   \n",
       "1                               0                     0   \n",
       "2                               0                     0   \n",
       "3                               0                     0   \n",
       "4                               0                     0   \n",
       "5                               0                     0   \n",
       "6                               0                     0   \n",
       "7                               0                     0   \n",
       "8                               2                     0   \n",
       "9                               0                     0   \n",
       "10                              0                     0   \n",
       "11                              0                     0   \n",
       "12                              0                     0   \n",
       "13                              0                     0   \n",
       "14                              0                     0   \n",
       "15                              0                     0   \n",
       "16                              0                     0   \n",
       "17                              0                     0   \n",
       "18                              0                     0   \n",
       "19                              0                     0   \n",
       "20                              0                     0   \n",
       "21                              0                     0   \n",
       "22                              0                     0   \n",
       "23                              0                     0   \n",
       "24                              0                     0   \n",
       "\n",
       "    distinct_interactions_2_video  \n",
       "0                               0  \n",
       "1                               0  \n",
       "2                               0  \n",
       "3                               0  \n",
       "4                               0  \n",
       "5                               0  \n",
       "6                               0  \n",
       "7                               0  \n",
       "8                               0  \n",
       "9                               0  \n",
       "10                              0  \n",
       "11                              0  \n",
       "12                              0  \n",
       "13                              0  \n",
       "14                              0  \n",
       "15                              0  \n",
       "16                              0  \n",
       "17                              0  \n",
       "18                              0  \n",
       "19                              0  \n",
       "20                              0  \n",
       "21                              0  \n",
       "22                              0  \n",
       "23                              0  \n",
       "24                              0  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activityTier.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "5325391d",
   "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>month</th>\n",
       "      <th>interactions_1_f2f</th>\n",
       "      <th>distinct_interactions_1_f2f</th>\n",
       "      <th>interactions_1_other</th>\n",
       "      <th>distinct_interactions_1_other</th>\n",
       "      <th>interactions_1_phone</th>\n",
       "      <th>distinct_interactions_1_phone</th>\n",
       "      <th>interactions_1_video</th>\n",
       "      <th>distinct_interactions_1_video</th>\n",
       "      <th>...</th>\n",
       "      <th>delta_interactions_1_video</th>\n",
       "      <th>delta_distinct_interactions_1_video</th>\n",
       "      <th>delta_interactions_2_f2f</th>\n",
       "      <th>delta_distinct_interactions_2_f2f</th>\n",
       "      <th>delta_interactions_2_other</th>\n",
       "      <th>delta_distinct_interactions_2_other</th>\n",
       "      <th>delta_interactions_2_phone</th>\n",
       "      <th>delta_distinct_interactions_2_phone</th>\n",
       "      <th>delta_interactions_2_video</th>\n",
       "      <th>delta_distinct_interactions_2_video</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     region    month  interactions_1_f2f  distinct_interactions_1_f2f  \\\n",
       "0  region_0  2020-01                   0                            0   \n",
       "1  region_0  2020-02                   0                            0   \n",
       "2  region_0  2020-03                   0                            0   \n",
       "3  region_0  2020-04                   0                            0   \n",
       "4  region_0  2020-05                   0                            0   \n",
       "\n",
       "   interactions_1_other  distinct_interactions_1_other  interactions_1_phone  \\\n",
       "0                     0                              0                     0   \n",
       "1                     0                              0                     0   \n",
       "2                     0                              0                     0   \n",
       "3                     0                              0                     0   \n",
       "4                     0                              0                     0   \n",
       "\n",
       "   distinct_interactions_1_phone  interactions_1_video  \\\n",
       "0                              0                     0   \n",
       "1                              0                     0   \n",
       "2                              0                     0   \n",
       "3                              0                     0   \n",
       "4                              0                     0   \n",
       "\n",
       "   distinct_interactions_1_video  ...  delta_interactions_1_video  \\\n",
       "0                              0  ...                         0.0   \n",
       "1                              0  ...                         0.0   \n",
       "2                              0  ...                         0.0   \n",
       "3                              0  ...                         0.0   \n",
       "4                              0  ...                         0.0   \n",
       "\n",
       "   delta_distinct_interactions_1_video  delta_interactions_2_f2f  \\\n",
       "0                                  0.0                       0.0   \n",
       "1                                  0.0                       0.0   \n",
       "2                                  0.0                       0.0   \n",
       "3                                  0.0                       0.0   \n",
       "4                                  0.0                       0.0   \n",
       "\n",
       "   delta_distinct_interactions_2_f2f  delta_interactions_2_other  \\\n",
       "0                                0.0                         0.0   \n",
       "1                                0.0                         0.0   \n",
       "2                                0.0                         0.0   \n",
       "3                                0.0                         0.0   \n",
       "4                                0.0                         0.0   \n",
       "\n",
       "   delta_distinct_interactions_2_other  delta_interactions_2_phone  \\\n",
       "0                                  0.0                         0.0   \n",
       "1                                  0.0                         0.0   \n",
       "2                                  0.0                         0.0   \n",
       "3                                  0.0                         0.0   \n",
       "4                                  0.0                         0.0   \n",
       "\n",
       "   delta_distinct_interactions_2_phone  delta_interactions_2_video  \\\n",
       "0                                  0.0                         0.0   \n",
       "1                                  0.0                         0.0   \n",
       "2                                  0.0                         0.0   \n",
       "3                                  0.0                         0.0   \n",
       "4                                  0.0                         0.0   \n",
       "\n",
       "   delta_distinct_interactions_2_video  \n",
       "0                                  0.0  \n",
       "1                                  0.0  \n",
       "2                                  0.0  \n",
       "3                                  0.0  \n",
       "4                                  0.0  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deltas = []\n",
    "for region in regions: \n",
    "    regionDf = activityTier[activityTier.region == region].copy()\n",
    "    firstIndex = regionDf.index.values[0]\n",
    "    for c in regionDf.columns:  \n",
    "        if c in ['region', 'month']: \n",
    "            continue\n",
    "    \n",
    "        regionDf['delta_{}'.format(c)] = regionDf[c].diff()\n",
    "        regionDf.at[firstIndex, 'delta_{}'.format(c)] = 0\n",
    "    deltas.append(regionDf)\n",
    "deltas = pd.concat(deltas, axis=0)\n",
    "deltas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "0e3ddae7",
   "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>region</th>\n",
       "      <th>month</th>\n",
       "      <th>delta_interactions_1_f2f</th>\n",
       "      <th>delta_distinct_interactions_1_f2f</th>\n",
       "      <th>delta_interactions_1_other</th>\n",
       "      <th>delta_distinct_interactions_1_other</th>\n",
       "      <th>delta_interactions_1_phone</th>\n",
       "      <th>delta_distinct_interactions_1_phone</th>\n",
       "      <th>delta_interactions_1_video</th>\n",
       "      <th>delta_distinct_interactions_1_video</th>\n",
       "      <th>delta_interactions_2_f2f</th>\n",
       "      <th>delta_distinct_interactions_2_f2f</th>\n",
       "      <th>delta_interactions_2_other</th>\n",
       "      <th>delta_distinct_interactions_2_other</th>\n",
       "      <th>delta_interactions_2_phone</th>\n",
       "      <th>delta_distinct_interactions_2_phone</th>\n",
       "      <th>delta_interactions_2_video</th>\n",
       "      <th>delta_distinct_interactions_2_video</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>region_1</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      region    month  delta_interactions_1_f2f  \\\n",
       "0   region_0  2020-01                       0.0   \n",
       "1   region_0  2020-02                       0.0   \n",
       "2   region_0  2020-03                       0.0   \n",
       "3   region_0  2020-04                       0.0   \n",
       "4   region_0  2020-05                       0.0   \n",
       "5   region_0  2020-06                       0.0   \n",
       "6   region_0  2020-07                      10.0   \n",
       "7   region_0  2020-08                      13.0   \n",
       "8   region_0  2020-09                      -5.0   \n",
       "9   region_0  2020-10                     -16.0   \n",
       "10  region_0  2020-11                      -2.0   \n",
       "11  region_0  2020-12                       0.0   \n",
       "12  region_0  2021-01                       0.0   \n",
       "13  region_0  2021-02                       0.0   \n",
       "14  region_0  2021-03                       0.0   \n",
       "15  region_0  2021-04                       0.0   \n",
       "16  region_0  2021-05                       0.0   \n",
       "17  region_0  2021-06                       0.0   \n",
       "18  region_0  2021-07                       1.0   \n",
       "19  region_0  2021-08                       2.0   \n",
       "20  region_1  2020-01                       0.0   \n",
       "21  region_1  2020-02                       0.0   \n",
       "22  region_1  2020-03                       0.0   \n",
       "23  region_1  2020-04                       0.0   \n",
       "24  region_1  2020-05                       0.0   \n",
       "\n",
       "    delta_distinct_interactions_1_f2f  delta_interactions_1_other  \\\n",
       "0                                 0.0                         0.0   \n",
       "1                                 0.0                         0.0   \n",
       "2                                 0.0                         0.0   \n",
       "3                                 0.0                         0.0   \n",
       "4                                 0.0                         0.0   \n",
       "5                                 0.0                         0.0   \n",
       "6                                 9.0                         0.0   \n",
       "7                                 3.0                         0.0   \n",
       "8                                -2.0                         0.0   \n",
       "9                                -8.0                         0.0   \n",
       "10                               -2.0                         0.0   \n",
       "11                                0.0                         0.0   \n",
       "12                                0.0                         0.0   \n",
       "13                                0.0                         0.0   \n",
       "14                                0.0                         0.0   \n",
       "15                                0.0                         0.0   \n",
       "16                                0.0                         0.0   \n",
       "17                                0.0                         0.0   \n",
       "18                                1.0                         0.0   \n",
       "19                                2.0                         0.0   \n",
       "20                                0.0                         0.0   \n",
       "21                                0.0                         0.0   \n",
       "22                                0.0                         0.0   \n",
       "23                                0.0                         0.0   \n",
       "24                                0.0                         0.0   \n",
       "\n",
       "    delta_distinct_interactions_1_other  delta_interactions_1_phone  \\\n",
       "0                                   0.0                         0.0   \n",
       "1                                   0.0                         0.0   \n",
       "2                                   0.0                         0.0   \n",
       "3                                   0.0                         0.0   \n",
       "4                                   0.0                         0.0   \n",
       "5                                   0.0                         0.0   \n",
       "6                                   0.0                         1.0   \n",
       "7                                   0.0                         2.0   \n",
       "8                                   0.0                         0.0   \n",
       "9                                   0.0                        -3.0   \n",
       "10                                  0.0                         1.0   \n",
       "11                                  0.0                        -1.0   \n",
       "12                                  0.0                         0.0   \n",
       "13                                  0.0                         0.0   \n",
       "14                                  0.0                         0.0   \n",
       "15                                  0.0                         0.0   \n",
       "16                                  0.0                         0.0   \n",
       "17                                  0.0                         0.0   \n",
       "18                                  0.0                         0.0   \n",
       "19                                  0.0                         0.0   \n",
       "20                                  0.0                         0.0   \n",
       "21                                  0.0                         0.0   \n",
       "22                                  0.0                         0.0   \n",
       "23                                  0.0                         0.0   \n",
       "24                                  0.0                         0.0   \n",
       "\n",
       "    delta_distinct_interactions_1_phone  delta_interactions_1_video  \\\n",
       "0                                   0.0                         0.0   \n",
       "1                                   0.0                         0.0   \n",
       "2                                   0.0                         0.0   \n",
       "3                                   0.0                         0.0   \n",
       "4                                   0.0                         0.0   \n",
       "5                                   0.0                         0.0   \n",
       "6                                   1.0                         0.0   \n",
       "7                                   2.0                         0.0   \n",
       "8                                   0.0                         0.0   \n",
       "9                                  -3.0                         0.0   \n",
       "10                                  1.0                         0.0   \n",
       "11                                 -1.0                         0.0   \n",
       "12                                  0.0                         0.0   \n",
       "13                                  0.0                         0.0   \n",
       "14                                  0.0                         0.0   \n",
       "15                                  0.0                         0.0   \n",
       "16                                  0.0                         0.0   \n",
       "17                                  0.0                         0.0   \n",
       "18                                  0.0                         0.0   \n",
       "19                                  0.0                         0.0   \n",
       "20                                  0.0                         0.0   \n",
       "21                                  0.0                         0.0   \n",
       "22                                  0.0                         0.0   \n",
       "23                                  0.0                         0.0   \n",
       "24                                  0.0                         0.0   \n",
       "\n",
       "    delta_distinct_interactions_1_video  delta_interactions_2_f2f  \\\n",
       "0                                   0.0                       0.0   \n",
       "1                                   0.0                       0.0   \n",
       "2                                   0.0                       0.0   \n",
       "3                                   0.0                       0.0   \n",
       "4                                   0.0                       0.0   \n",
       "5                                   0.0                       0.0   \n",
       "6                                   0.0                       0.0   \n",
       "7                                   0.0                       4.0   \n",
       "8                                   0.0                      -3.0   \n",
       "9                                   0.0                       0.0   \n",
       "10                                  0.0                      -1.0   \n",
       "11                                  0.0                       0.0   \n",
       "12                                  0.0                       0.0   \n",
       "13                                  0.0                       0.0   \n",
       "14                                  0.0                       0.0   \n",
       "15                                  0.0                       0.0   \n",
       "16                                  0.0                       0.0   \n",
       "17                                  0.0                       0.0   \n",
       "18                                  0.0                       0.0   \n",
       "19                                  0.0                       0.0   \n",
       "20                                  0.0                       0.0   \n",
       "21                                  0.0                       0.0   \n",
       "22                                  0.0                       0.0   \n",
       "23                                  0.0                       0.0   \n",
       "24                                  0.0                       0.0   \n",
       "\n",
       "    delta_distinct_interactions_2_f2f  delta_interactions_2_other  \\\n",
       "0                                 0.0                         0.0   \n",
       "1                                 0.0                         0.0   \n",
       "2                                 0.0                         0.0   \n",
       "3                                 0.0                         0.0   \n",
       "4                                 0.0                         0.0   \n",
       "5                                 0.0                         0.0   \n",
       "6                                 0.0                         0.0   \n",
       "7                                 4.0                         0.0   \n",
       "8                                -3.0                         0.0   \n",
       "9                                 0.0                         0.0   \n",
       "10                               -1.0                         0.0   \n",
       "11                                0.0                         0.0   \n",
       "12                                0.0                         0.0   \n",
       "13                                0.0                         0.0   \n",
       "14                                0.0                         0.0   \n",
       "15                                0.0                         0.0   \n",
       "16                                0.0                         0.0   \n",
       "17                                0.0                         0.0   \n",
       "18                                0.0                         0.0   \n",
       "19                                0.0                         0.0   \n",
       "20                                0.0                         0.0   \n",
       "21                                0.0                         0.0   \n",
       "22                                0.0                         0.0   \n",
       "23                                0.0                         0.0   \n",
       "24                                0.0                         0.0   \n",
       "\n",
       "    delta_distinct_interactions_2_other  delta_interactions_2_phone  \\\n",
       "0                                   0.0                         0.0   \n",
       "1                                   0.0                         0.0   \n",
       "2                                   0.0                         0.0   \n",
       "3                                   0.0                         0.0   \n",
       "4                                   0.0                         0.0   \n",
       "5                                   0.0                         0.0   \n",
       "6                                   0.0                         0.0   \n",
       "7                                   0.0                         0.0   \n",
       "8                                   0.0                         2.0   \n",
       "9                                   0.0                        -2.0   \n",
       "10                                  0.0                         0.0   \n",
       "11                                  0.0                         0.0   \n",
       "12                                  0.0                         0.0   \n",
       "13                                  0.0                         0.0   \n",
       "14                                  0.0                         0.0   \n",
       "15                                  0.0                         0.0   \n",
       "16                                  0.0                         0.0   \n",
       "17                                  0.0                         0.0   \n",
       "18                                  0.0                         0.0   \n",
       "19                                  0.0                         0.0   \n",
       "20                                  0.0                         0.0   \n",
       "21                                  0.0                         0.0   \n",
       "22                                  0.0                         0.0   \n",
       "23                                  0.0                         0.0   \n",
       "24                                  0.0                         0.0   \n",
       "\n",
       "    delta_distinct_interactions_2_phone  delta_interactions_2_video  \\\n",
       "0                                   0.0                         0.0   \n",
       "1                                   0.0                         0.0   \n",
       "2                                   0.0                         0.0   \n",
       "3                                   0.0                         0.0   \n",
       "4                                   0.0                         0.0   \n",
       "5                                   0.0                         0.0   \n",
       "6                                   0.0                         0.0   \n",
       "7                                   0.0                         0.0   \n",
       "8                                   2.0                         0.0   \n",
       "9                                  -2.0                         0.0   \n",
       "10                                  0.0                         0.0   \n",
       "11                                  0.0                         0.0   \n",
       "12                                  0.0                         0.0   \n",
       "13                                  0.0                         0.0   \n",
       "14                                  0.0                         0.0   \n",
       "15                                  0.0                         0.0   \n",
       "16                                  0.0                         0.0   \n",
       "17                                  0.0                         0.0   \n",
       "18                                  0.0                         0.0   \n",
       "19                                  0.0                         0.0   \n",
       "20                                  0.0                         0.0   \n",
       "21                                  0.0                         0.0   \n",
       "22                                  0.0                         0.0   \n",
       "23                                  0.0                         0.0   \n",
       "24                                  0.0                         0.0   \n",
       "\n",
       "    delta_distinct_interactions_2_video  \n",
       "0                                   0.0  \n",
       "1                                   0.0  \n",
       "2                                   0.0  \n",
       "3                                   0.0  \n",
       "4                                   0.0  \n",
       "5                                   0.0  \n",
       "6                                   0.0  \n",
       "7                                   0.0  \n",
       "8                                   0.0  \n",
       "9                                   0.0  \n",
       "10                                  0.0  \n",
       "11                                  0.0  \n",
       "12                                  0.0  \n",
       "13                                  0.0  \n",
       "14                                  0.0  \n",
       "15                                  0.0  \n",
       "16                                  0.0  \n",
       "17                                  0.0  \n",
       "18                                  0.0  \n",
       "19                                  0.0  \n",
       "20                                  0.0  \n",
       "21                                  0.0  \n",
       "22                                  0.0  \n",
       "23                                  0.0  \n",
       "24                                  0.0  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deltas.drop([c for c in activityTier.columns if c not in ['region', 'month']], axis=1, inplace=True)\n",
    "deltas.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "de7c0cb4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.any(deltas.isna().values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "8e290020",
   "metadata": {},
   "outputs": [],
   "source": [
    "deltas.to_csv('./data/data_files/time_features/activity_delta_{}.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1642c5ac",
   "metadata": {},
   "source": [
    "### Time-based sales analysis\n",
    "get last sales, and relative change. also add market potential"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "25ad0b97",
   "metadata": {
    "collapsed": 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>month</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>65007.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>509023.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>940469.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>45929.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>344182.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_1</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>625300.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_2</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_2</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_2</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>49600.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_2</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>232335.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_2</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>499457.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_3</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_3</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_3</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>28624.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_3</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>233121.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_3</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>413104.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_4</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_4</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_4</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>43625.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_4</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>275719.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_4</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>577482.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      month    region            brand      sales\n",
       "0   2020-01  region_0          brand_1       0.00\n",
       "1   2020-01  region_0          brand_2       0.00\n",
       "2   2020-01  region_0          brand_3   65007.49\n",
       "3   2020-01  region_0  brand_12_market  509023.69\n",
       "4   2020-01  region_0   brand_3_market  940469.05\n",
       "5   2020-01  region_1          brand_1       0.00\n",
       "6   2020-01  region_1          brand_2       0.00\n",
       "7   2020-01  region_1          brand_3   45929.88\n",
       "8   2020-01  region_1  brand_12_market  344182.42\n",
       "9   2020-01  region_1   brand_3_market  625300.10\n",
       "10  2020-01  region_2          brand_1       0.00\n",
       "11  2020-01  region_2          brand_2       0.00\n",
       "12  2020-01  region_2          brand_3   49600.98\n",
       "13  2020-01  region_2  brand_12_market  232335.01\n",
       "14  2020-01  region_2   brand_3_market  499457.13\n",
       "15  2020-01  region_3          brand_1       0.00\n",
       "16  2020-01  region_3          brand_2       0.00\n",
       "17  2020-01  region_3          brand_3   28624.41\n",
       "18  2020-01  region_3  brand_12_market  233121.00\n",
       "19  2020-01  region_3   brand_3_market  413104.96\n",
       "20  2020-01  region_4          brand_1       0.00\n",
       "21  2020-01  region_4          brand_2       0.00\n",
       "22  2020-01  region_4          brand_3   43625.70\n",
       "23  2020-01  region_4  brand_12_market  275719.49\n",
       "24  2020-01  region_4   brand_3_market  577482.54"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salesTrain = pd.read_csv('./data/data_files/sales_train_splitted.csv', index_col=0)\n",
    "salesTrain.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "df3614ae",
   "metadata": {
    "collapsed": 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>month</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>755</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_151</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>35347.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>756</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_151</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>213189.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_151</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>423435.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>758</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_152</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>34091.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>759</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_152</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>188121.51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       month      region            brand      sales\n",
       "755  2020-01  region_151          brand_3   35347.30\n",
       "756  2020-01  region_151  brand_12_market  213189.25\n",
       "757  2020-01  region_151   brand_3_market  423435.51\n",
       "758  2020-01  region_152          brand_3   34091.04\n",
       "759  2020-01  region_152  brand_12_market  188121.51"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salesTest = pd.read_csv('./data/data_files/sales_test_splitted.csv', index_col=0)\n",
    "salesTest.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "22c66945",
   "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>month</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>65007.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>509023.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>940469.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>51971.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>458929.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>826222.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>54654.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>477955.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>853831.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>57338.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>496981.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>881440.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>54664.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>473531.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>870793.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      month    region            brand      sales\n",
       "0   2020-01  region_0          brand_1       0.00\n",
       "1   2020-01  region_0          brand_2       0.00\n",
       "2   2020-01  region_0          brand_3   65007.49\n",
       "3   2020-01  region_0  brand_12_market  509023.69\n",
       "4   2020-01  region_0   brand_3_market  940469.05\n",
       "5   2020-02  region_0          brand_1       0.00\n",
       "6   2020-02  region_0          brand_2       0.00\n",
       "7   2020-02  region_0          brand_3   51971.16\n",
       "8   2020-02  region_0  brand_12_market  458929.00\n",
       "9   2020-02  region_0   brand_3_market  826222.47\n",
       "10  2020-03  region_0          brand_1       0.00\n",
       "11  2020-03  region_0          brand_2       0.00\n",
       "12  2020-03  region_0          brand_3   54654.78\n",
       "13  2020-03  region_0  brand_12_market  477955.06\n",
       "14  2020-03  region_0   brand_3_market  853831.67\n",
       "15  2020-04  region_0          brand_1       0.00\n",
       "16  2020-04  region_0          brand_2       0.00\n",
       "17  2020-04  region_0          brand_3   57338.40\n",
       "18  2020-04  region_0  brand_12_market  496981.12\n",
       "19  2020-04  region_0   brand_3_market  881440.87\n",
       "20  2020-05  region_0          brand_1       0.00\n",
       "21  2020-05  region_0          brand_2       0.00\n",
       "22  2020-05  region_0          brand_3   54664.87\n",
       "23  2020-05  region_0  brand_12_market  473531.38\n",
       "24  2020-05  region_0   brand_3_market  870793.13"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales = pd.concat([salesTrain, salesTest], axis=0)\n",
    "sales.region = pd.Categorical(sales.region, regions)\n",
    "sales.month = pd.Categorical(sales.month, months)\n",
    "sales.sort_values(['region', 'month'], ascending=True, inplace=True)\n",
    "sales.reset_index(drop=True, inplace=True)\n",
    "sales.head(n=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "c9084e9b",
   "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>month</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18095</th>\n",
       "      <td>2021-07</td>\n",
       "      <td>region_200</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>410435.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18096</th>\n",
       "      <td>2021-07</td>\n",
       "      <td>region_200</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>715514.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18097</th>\n",
       "      <td>2021-08</td>\n",
       "      <td>region_200</td>\n",
       "      <td>brand_3</td>\n",
       "      <td>50417.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18098</th>\n",
       "      <td>2021-08</td>\n",
       "      <td>region_200</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>289230.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18099</th>\n",
       "      <td>2021-08</td>\n",
       "      <td>region_200</td>\n",
       "      <td>brand_3_market</td>\n",
       "      <td>509048.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         month      region            brand      sales\n",
       "18095  2021-07  region_200  brand_12_market  410435.21\n",
       "18096  2021-07  region_200   brand_3_market  715514.89\n",
       "18097  2021-08  region_200          brand_3   50417.07\n",
       "18098  2021-08  region_200  brand_12_market  289230.73\n",
       "18099  2021-08  region_200   brand_3_market  509048.09"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "7896ff62",
   "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>month</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-06</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-07</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-08</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>253.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-09</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>56.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-10</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_2</td>\n",
       "      <td>480.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     month    region    brand   sales\n",
       "0  2020-01  region_0  brand_2    0.00\n",
       "1  2020-02  region_0  brand_2    0.00\n",
       "2  2020-03  region_0  brand_2    0.00\n",
       "3  2020-04  region_0  brand_2    0.00\n",
       "4  2020-05  region_0  brand_2    0.00\n",
       "5  2020-06  region_0  brand_2    0.00\n",
       "6  2020-07  region_0  brand_2    0.00\n",
       "7  2020-08  region_0  brand_2  253.22\n",
       "8  2020-09  region_0  brand_2   56.27\n",
       "9  2020-10  region_0  brand_2  480.06"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand = 'brand_2'\n",
    "salesSub = sales[sales['brand'] == brand]\n",
    "salesSub.reset_index(drop=True, inplace=True)\n",
    "salesSub.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "8bcdc38a",
   "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>month</th>\n",
       "      <th>region</th>\n",
       "      <th>brand</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>509023.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>458929.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>477955.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>496981.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>region_0</td>\n",
       "      <td>brand_12_market</td>\n",
       "      <td>473531.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     month    region            brand      sales\n",
       "0  2020-01  region_0  brand_12_market  509023.69\n",
       "1  2020-02  region_0  brand_12_market  458929.00\n",
       "2  2020-03  region_0  brand_12_market  477955.06\n",
       "3  2020-04  region_0  brand_12_market  496981.12\n",
       "4  2020-05  region_0  brand_12_market  473531.38"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "marketPotential = sales[sales['brand'] == 'brand_12_market']\n",
    "marketPotential.reset_index(drop=True, inplace=True)\n",
    "marketPotential.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "1a666999",
   "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>region</th>\n",
       "      <th>month</th>\n",
       "      <th>sales</th>\n",
       "      <th>delta_sales</th>\n",
       "      <th>rel_delta_sales</th>\n",
       "      <th>prevSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-07</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-08</td>\n",
       "      <td>253.22</td>\n",
       "      <td>253.22</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-09</td>\n",
       "      <td>56.27</td>\n",
       "      <td>-196.95</td>\n",
       "      <td>-0.777782</td>\n",
       "      <td>253.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>region_0</td>\n",
       "      <td>2020-10</td>\n",
       "      <td>480.06</td>\n",
       "      <td>423.79</td>\n",
       "      <td>7.531367</td>\n",
       "      <td>56.27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     region    month   sales  delta_sales  rel_delta_sales  prevSales\n",
       "0  region_0  2020-01    0.00         0.00         0.000000       0.00\n",
       "1  region_0  2020-02    0.00         0.00         0.000000       0.00\n",
       "2  region_0  2020-03    0.00         0.00         0.000000       0.00\n",
       "3  region_0  2020-04    0.00         0.00         0.000000       0.00\n",
       "4  region_0  2020-05    0.00         0.00         0.000000       0.00\n",
       "5  region_0  2020-06    0.00         0.00         0.000000       0.00\n",
       "6  region_0  2020-07    0.00         0.00         0.000000       0.00\n",
       "7  region_0  2020-08  253.22       253.22       100.000000       0.00\n",
       "8  region_0  2020-09   56.27      -196.95        -0.777782     253.22\n",
       "9  region_0  2020-10  480.06       423.79         7.531367      56.27"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salesDiff = []\n",
    "for region in regions[:151]: \n",
    "    regionDf = salesSub[salesSub.region == region][['region', 'month', 'sales']].copy()\n",
    "    firstIndex = regionDf.index.values[0]\n",
    "    regionDf['delta_sales'] = regionDf['sales'].diff()\n",
    "    regionDf['rel_delta_sales'] = regionDf['sales'].pct_change()\n",
    "    infIndex = regionDf[regionDf['rel_delta_sales'] == np.inf].index.values\n",
    "    if infIndex.shape[0] > 0: \n",
    "        regionDf.loc[infIndex, 'rel_delta_sales'] = 100\n",
    "    regionDf.at[firstIndex, 'delta_sales'] = 0\n",
    "    naIndex = regionDf[regionDf['rel_delta_sales'].isna()].index.values\n",
    "    if naIndex.shape[0] > 0: \n",
    "        regionDf.loc[naIndex, 'rel_delta_sales'] = 0\n",
    "    regionDf['prevSales'] = regionDf['sales'].shift()\n",
    "    regionDf.at[firstIndex, 'prevSales'] = 0\n",
    "    # regionDf.fillna(0, inplace=True)\n",
    "    salesDiff.append(regionDf)\n",
    "salesDiff = pd.concat(salesDiff, axis=0)\n",
    "salesDiff.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "8bbafecc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((3020, 6), (4020, 4))"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salesDiff.shape, marketPotential.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "5d922f6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "salesDiff.to_csv('./data/data_files/time_features/sales_delta_{}_train.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "33603e68",
   "metadata": {},
   "outputs": [],
   "source": [
    "marketPotential.to_csv('./data/data_files/time_features/market_potential_12.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba3741f4",
   "metadata": {},
   "source": [
    "### Merge datasets into training and test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "b9ff7183",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3020, 27) (1000, 27)\n",
      "(3020, 27) (1000, 27)\n"
     ]
    }
   ],
   "source": [
    "brands = ['brand_1', 'brand_2']\n",
    "marketPotential = pd.read_csv('./data/data_files/time_features/market_potential_12.csv', index_col=0)\n",
    "trainRegions = regions[:151]\n",
    "testRegions = regions[151:]\n",
    "for brand in brands: \n",
    "    activityDelta = pd.read_csv('./data/data_files/time_features/activity_delta_{}.csv'.format(brand), index_col=0)\n",
    "    rteDelta = pd.read_csv('./data/data_files/time_features/rte_delta_{}.csv'.format(brand), index_col=0)\n",
    "    \n",
    "    salesDelta = pd.read_csv('./data/data_files/time_features/sales_delta_{}_train.csv'.format(brand), index_col=0)\n",
    "    \n",
    "    data = pd.concat([activityDelta, rteDelta.drop(['region', 'month'], axis=1)], axis=1)\n",
    "    dataTrain = data[data['region'].apply(lambda x: x in trainRegions)]\n",
    "    dataTest = data[data['region'].apply(lambda x: x in testRegions)]\n",
    "    potentialTrain = marketPotential[marketPotential['region'].apply(lambda x: x in trainRegions)]\n",
    "    potentialTest = marketPotential[marketPotential['region'].apply(lambda x: x in testRegions)]\n",
    "    #dataTrain = pd.concat([dataTrain, salesDelta['prevSales'], potentialTrain], axis=0)\n",
    "    dataTrain = pd.concat([dataTrain, potentialTrain.rename(columns={'sales': 'market_potential'})['market_potential']], axis=1)\n",
    "    dataTest = pd.concat([dataTest, potentialTest.rename(columns={'sales': 'market_potential'})['market_potential']], axis=1)\n",
    "    print(dataTrain.shape, dataTest.shape)\n",
    "    \n",
    "    dataTrain.to_csv('./data/data_files/time_features/time_features_{}_train.csv'.format(brand))\n",
    "    dataTest.to_csv('./data/data_files/time_features/time_features_{}_test.csv'.format(brand))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a478f021",
   "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
}