buying_behaviour_tracking.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import plotly.express as px\n",
    "import plotly.graph_objs as go\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from prophet import Prophet\n",
    "\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.metrics import accuracy_score\n",
    "from sklearn.metrics import confusion_matrix\n",
    "from sklearn.ensemble import VotingClassifier\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import AdaBoostClassifier\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.ensemble import GradientBoostingClassifier\n",
    "\n",
    "import os\n",
    "import re\n",
    "import random\n",
    "import logging\n",
    "import warnings\n",
    "from pathlib import Path\n",
    "\n",
    "sns.set_style(\"whitegrid\")\n",
    "\n",
    "logging.getLogger(\"prophet\").setLevel(logging.WARNING)\n",
    "logging.getLogger(\"cmdstanpy\").disabled=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Alım Davranışı Takip', 'Reklam Test Araştırması']"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.ExcelFile('data/Datathon@MetuStatClub Final.xlsx').sheet_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "buying_behaviour_tracking = pd.read_excel(\"data/Datathon@MetuStatClub Final.xlsx\", sheet_name=\"Alım Davranışı Takip\")\n",
    "ad_test_research = pd.read_excel(\"data/Datathon@MetuStatClub Final.xlsx\", sheet_name=\"Reklam Test Araştırması\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>Company</th>\n",
       "      <th>Brand</th>\n",
       "      <th>Kategori</th>\n",
       "      <th>Metric Type</th>\n",
       "      <th>Metric</th>\n",
       "      <th>2020 Ocak</th>\n",
       "      <th>2020 Subat</th>\n",
       "      <th>2020 Mart</th>\n",
       "      <th>2020 Nisan</th>\n",
       "      <th>2020 Mayıs</th>\n",
       "      <th>...</th>\n",
       "      <th>2022 Mart</th>\n",
       "      <th>2022 Nisan</th>\n",
       "      <th>2022 Mayıs</th>\n",
       "      <th>2022 Haziran</th>\n",
       "      <th>2022 Temmuz</th>\n",
       "      <th>2022 Agustos</th>\n",
       "      <th>2022 Eylül</th>\n",
       "      <th>2022 Ekim</th>\n",
       "      <th>2022 Kasım</th>\n",
       "      <th>2022 Aralık</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1550</td>\n",
       "      <td>1550</td>\n",
       "      <td>1550</td>\n",
       "      <td>1550</td>\n",
       "      <td>1550</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>...</td>\n",
       "      <td>1420</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1409</td>\n",
       "      <td>1398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>11</td>\n",
       "      <td>69</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>23</td>\n",
       "      <td>744</td>\n",
       "      <td>744</td>\n",
       "      <td>742</td>\n",
       "      <td>746</td>\n",
       "      <td>740</td>\n",
       "      <td>...</td>\n",
       "      <td>756</td>\n",
       "      <td>748</td>\n",
       "      <td>742</td>\n",
       "      <td>744</td>\n",
       "      <td>725</td>\n",
       "      <td>738</td>\n",
       "      <td>730</td>\n",
       "      <td>739</td>\n",
       "      <td>745</td>\n",
       "      <td>733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>ANZ</td>\n",
       "      <td>Mavi</td>\n",
       "      <td>3</td>\n",
       "      <td>Brand</td>\n",
       "      <td>Penetration</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>703</td>\n",
       "      <td>44</td>\n",
       "      <td>506</td>\n",
       "      <td>790</td>\n",
       "      <td>70</td>\n",
       "      <td>68</td>\n",
       "      <td>63</td>\n",
       "      <td>66</td>\n",
       "      <td>99</td>\n",
       "      <td>98</td>\n",
       "      <td>...</td>\n",
       "      <td>34</td>\n",
       "      <td>72</td>\n",
       "      <td>67</td>\n",
       "      <td>72</td>\n",
       "      <td>71</td>\n",
       "      <td>70</td>\n",
       "      <td>72</td>\n",
       "      <td>37</td>\n",
       "      <td>35</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Company Brand  Kategori Metric Type       Metric 2020 Ocak 2020 Subat   \n",
       "count     1550  1550      1550        1550         1550      1409       1409  \\\n",
       "unique      11    69         5           2           23       744        744   \n",
       "top        ANZ  Mavi         3       Brand  Penetration         -          -   \n",
       "freq       703    44       506         790           70        68         63   \n",
       "\n",
       "       2020 Mart 2020 Nisan 2020 Mayıs  ... 2022 Mart 2022 Nisan 2022 Mayıs   \n",
       "count       1409       1409       1409  ...      1420       1409       1409  \\\n",
       "unique       742        746        740  ...       756        748        742   \n",
       "top            -          -          -  ...         -          -          -   \n",
       "freq          66         99         98  ...        34         72         67   \n",
       "\n",
       "       2022 Haziran 2022 Temmuz 2022 Agustos 2022 Eylül 2022 Ekim 2022 Kasım   \n",
       "count          1409        1409         1409       1409      1409       1409  \\\n",
       "unique          744         725          738        730       739        745   \n",
       "top               -           -            -          -         -          -   \n",
       "freq             72          71           70         72        37         35   \n",
       "\n",
       "       2022 Aralık  \n",
       "count         1398  \n",
       "unique         733  \n",
       "top              -  \n",
       "freq            37  \n",
       "\n",
       "[4 rows x 41 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buying_behaviour_tracking.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first row is total row, we don't need it\n",
    "buying_behaviour_tracking = buying_behaviour_tracking.iloc[1:]\n",
    "\n",
    "# change xxx to NaN\n",
    "ad_test_research = ad_test_research.replace(r'^[xX]+$', np.nan, regex=True)\n",
    "\n",
    "buying_behaviour_tracking[\"Company-Brand\"] = buying_behaviour_tracking[\"Company\"] + \"-\" + buying_behaviour_tracking[\"Brand\"]\n",
    "\n",
    "buying_behaviour_tracking.loc[buying_behaviour_tracking['Company'] == buying_behaviour_tracking['Brand'], 'Company-Brand'] = buying_behaviour_tracking['Company']\n",
    "# drop company and brand columns\n",
    "buying_behaviour_tracking = buying_behaviour_tracking.drop([\"Company\", \"Brand\"], axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df = buying_behaviour_tracking\n",
    "\n",
    "df = pd.melt(\n",
    "    df,\n",
    "    id_vars=['Company-Brand', 'Metric', 'Kategori','Metric Type'],\n",
    "    var_name='Dates',\n",
    "    value_name='Value'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Company-Brand       0\n",
       "Metric              0\n",
       "Kategori            0\n",
       "Metric Type         0\n",
       "Dates               0\n",
       "Value            5054\n",
       "dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.replace(\"-\", np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Company-Brand       0\n",
       "Metric              0\n",
       "Kategori            0\n",
       "Metric Type         0\n",
       "Dates               0\n",
       "Value            7448\n",
       "dtype: int64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "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>Company-Brand</th>\n",
       "      <th>Metric</th>\n",
       "      <th>Kategori</th>\n",
       "      <th>Metric Type</th>\n",
       "      <th>Dates</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Prizy-Y</td>\n",
       "      <td>TOM</td>\n",
       "      <td>1</td>\n",
       "      <td>Brand</td>\n",
       "      <td>2020 Ocak</td>\n",
       "      <td>12.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Prizy-Y</td>\n",
       "      <td>Spontaneous Awareness</td>\n",
       "      <td>1</td>\n",
       "      <td>Brand</td>\n",
       "      <td>2020 Ocak</td>\n",
       "      <td>33.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Prizy-Y</td>\n",
       "      <td>Awareness</td>\n",
       "      <td>1</td>\n",
       "      <td>Brand</td>\n",
       "      <td>2020 Ocak</td>\n",
       "      <td>94.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Prizy-Y</td>\n",
       "      <td>Consideration</td>\n",
       "      <td>1</td>\n",
       "      <td>Brand</td>\n",
       "      <td>2020 Ocak</td>\n",
       "      <td>93.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Prizy-Y</td>\n",
       "      <td>Ever Used</td>\n",
       "      <td>1</td>\n",
       "      <td>Brand</td>\n",
       "      <td>2020 Ocak</td>\n",
       "      <td>87.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55759</th>\n",
       "      <td>DLZ</td>\n",
       "      <td>PF (times)</td>\n",
       "      <td>4</td>\n",
       "      <td>Behavior</td>\n",
       "      <td>2022 Aralık</td>\n",
       "      <td>1.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55760</th>\n",
       "      <td>DLZ</td>\n",
       "      <td>TS (gr)</td>\n",
       "      <td>4</td>\n",
       "      <td>Behavior</td>\n",
       "      <td>2022 Aralık</td>\n",
       "      <td>498.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55761</th>\n",
       "      <td>DLZ</td>\n",
       "      <td>TV (tl)</td>\n",
       "      <td>4</td>\n",
       "      <td>Behavior</td>\n",
       "      <td>2022 Aralık</td>\n",
       "      <td>17.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55762</th>\n",
       "      <td>DLZ</td>\n",
       "      <td>Price (gr/tl)</td>\n",
       "      <td>4</td>\n",
       "      <td>Behavior</td>\n",
       "      <td>2022 Aralık</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55763</th>\n",
       "      <td>DLZ</td>\n",
       "      <td>No of Sku (barcode)</td>\n",
       "      <td>4</td>\n",
       "      <td>Behavior</td>\n",
       "      <td>2022 Aralık</td>\n",
       "      <td>21.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>55764 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Company-Brand                 Metric  Kategori Metric Type        Dates   \n",
       "0           Prizy-Y                    TOM         1       Brand    2020 Ocak  \\\n",
       "1           Prizy-Y  Spontaneous Awareness         1       Brand    2020 Ocak   \n",
       "2           Prizy-Y              Awareness         1       Brand    2020 Ocak   \n",
       "3           Prizy-Y          Consideration         1       Brand    2020 Ocak   \n",
       "4           Prizy-Y              Ever Used         1       Brand    2020 Ocak   \n",
       "...             ...                    ...       ...         ...          ...   \n",
       "55759           DLZ             PF (times)         4    Behavior  2022 Aralık   \n",
       "55760           DLZ                TS (gr)         4    Behavior  2022 Aralık   \n",
       "55761           DLZ                TV (tl)         4    Behavior  2022 Aralık   \n",
       "55762           DLZ          Price (gr/tl)         4    Behavior  2022 Aralık   \n",
       "55763           DLZ    No of Sku (barcode)         4    Behavior  2022 Aralık   \n",
       "\n",
       "        Value  \n",
       "0       12.00  \n",
       "1       33.00  \n",
       "2       94.00  \n",
       "3       93.00  \n",
       "4       87.26  \n",
       "...       ...  \n",
       "55759    1.27  \n",
       "55760  498.45  \n",
       "55761   17.65  \n",
       "55762    0.04  \n",
       "55763   21.00  \n",
       "\n",
       "[55764 rows x 6 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Value'] = df.groupby('Metric')['Value'].transform(lambda x: x.fillna(x.mean()))\n",
    "df['Value'] = df['Value'].round(2)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "translate_months = {\n",
    "    'Ocak': 'January',\n",
    "    'Şubat': 'February',\n",
    "    'Subat': 'February',\n",
    "    'Mart': 'March',\n",
    "    'Nisan': 'April',\n",
    "    'Mayıs': 'May',\n",
    "    'Mayis': 'May',\n",
    "    'Haziran': 'June',\n",
    "    'Temmuz': 'July',\n",
    "    'Ağustos': 'August',\n",
    "    'Agustos': 'August',\n",
    "    'Eylül': 'September',\n",
    "    'Eylul': 'September',\n",
    "    'Ekim': 'October',\n",
    "    'Kasım': 'November',\n",
    "    'Kasim': 'November',\n",
    "    'Aralık': 'December',\n",
    "    'Aralik': 'December'\n",
    "}\n",
    "\n",
    "df['Dates'] = df['Dates'].replace(translate_months, regex=True)\n",
    "df['Dates'] = pd.to_datetime(df['Dates'], format='%Y %B')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Value'] = df['Value'].round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>Company-Brand</th>\n",
       "      <th>Dates</th>\n",
       "      <th>Metric</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>Awareness</td>\n",
       "      <td>98.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Awareness</td>\n",
       "      <td>97.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Awareness</td>\n",
       "      <td>96.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>Awareness</td>\n",
       "      <td>98.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>Awareness</td>\n",
       "      <td>97.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59611</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-08-01</td>\n",
       "      <td>Market Share</td>\n",
       "      <td>0.000031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59612</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-09-01</td>\n",
       "      <td>Market Share</td>\n",
       "      <td>0.000050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59613</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>Market Share</td>\n",
       "      <td>0.000042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59614</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>Market Share</td>\n",
       "      <td>0.000034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59615</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>Market Share</td>\n",
       "      <td>0.000015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59616 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Company-Brand      Dates        Metric      Value\n",
       "0             ANZ-A 2020-01-01     Awareness  98.000000\n",
       "1             ANZ-A 2020-02-01     Awareness  97.000000\n",
       "2             ANZ-A 2020-03-01     Awareness  96.000000\n",
       "3             ANZ-A 2020-04-01     Awareness  98.000000\n",
       "4             ANZ-A 2020-05-01     Awareness  97.000000\n",
       "...             ...        ...           ...        ...\n",
       "59611      Prizy-ZV 2022-08-01  Market Share   0.000031\n",
       "59612      Prizy-ZV 2022-09-01  Market Share   0.000050\n",
       "59613      Prizy-ZV 2022-10-01  Market Share   0.000042\n",
       "59614      Prizy-ZV 2022-11-01  Market Share   0.000034\n",
       "59615      Prizy-ZV 2022-12-01  Market Share   0.000015\n",
       "\n",
       "[59616 rows x 4 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted = df.pivot(index=[\"Company-Brand\",'Dates'], columns='Metric', values='Value')\n",
    "\n",
    "\n",
    "pivoted[\"Market Share\"] = pivoted[\"Volume (gr)\"] / pivoted[\"Volume (gr)\"].sum()\n",
    "pivoted = pivoted.reset_index()\n",
    "df2 = pd.melt(\n",
    "    pivoted,\n",
    "    id_vars=[\"Company-Brand\",'Dates'],\n",
    "    var_name=\"Metric\",\n",
    "    value_name=\"Value\"\n",
    ")\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "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>Metric</th>\n",
       "      <th>Company-Brand</th>\n",
       "      <th>Dates</th>\n",
       "      <th>Awareness</th>\n",
       "      <th>Consideration</th>\n",
       "      <th>Ever Used</th>\n",
       "      <th>Most Often</th>\n",
       "      <th>No of Sku (barcode)</th>\n",
       "      <th>PF (times)</th>\n",
       "      <th>Penetration</th>\n",
       "      <th>Performance</th>\n",
       "      <th>...</th>\n",
       "      <th>Spontaneous Awareness</th>\n",
       "      <th>TOM</th>\n",
       "      <th>TS (gr)</th>\n",
       "      <th>TV (tl)</th>\n",
       "      <th>Total Ad Awareness (T5B)</th>\n",
       "      <th>Trial</th>\n",
       "      <th>Value (tl)</th>\n",
       "      <th>Volume (gr)</th>\n",
       "      <th>Yakınlık</th>\n",
       "      <th>Market Share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>98.00</td>\n",
       "      <td>95.00</td>\n",
       "      <td>87.26</td>\n",
       "      <td>44.00</td>\n",
       "      <td>80.0</td>\n",
       "      <td>1.46</td>\n",
       "      <td>4575322.59</td>\n",
       "      <td>63.00</td>\n",
       "      <td>...</td>\n",
       "      <td>86.0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>88.16</td>\n",
       "      <td>5.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85.00</td>\n",
       "      <td>33813115.02</td>\n",
       "      <td>5.902114e+08</td>\n",
       "      <td>73.00</td>\n",
       "      <td>0.001017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>97.00</td>\n",
       "      <td>93.00</td>\n",
       "      <td>87.26</td>\n",
       "      <td>37.00</td>\n",
       "      <td>84.0</td>\n",
       "      <td>1.45</td>\n",
       "      <td>4943679.08</td>\n",
       "      <td>56.00</td>\n",
       "      <td>...</td>\n",
       "      <td>80.0</td>\n",
       "      <td>37.00</td>\n",
       "      <td>91.31</td>\n",
       "      <td>5.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>81.00</td>\n",
       "      <td>37605796.32</td>\n",
       "      <td>6.541825e+08</td>\n",
       "      <td>62.00</td>\n",
       "      <td>0.001127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>96.00</td>\n",
       "      <td>89.00</td>\n",
       "      <td>87.26</td>\n",
       "      <td>30.00</td>\n",
       "      <td>85.0</td>\n",
       "      <td>1.43</td>\n",
       "      <td>4378076.07</td>\n",
       "      <td>50.00</td>\n",
       "      <td>...</td>\n",
       "      <td>82.0</td>\n",
       "      <td>36.00</td>\n",
       "      <td>92.27</td>\n",
       "      <td>5.35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75.00</td>\n",
       "      <td>33493507.49</td>\n",
       "      <td>5.773998e+08</td>\n",
       "      <td>55.00</td>\n",
       "      <td>0.000995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>98.00</td>\n",
       "      <td>92.00</td>\n",
       "      <td>87.26</td>\n",
       "      <td>30.00</td>\n",
       "      <td>77.0</td>\n",
       "      <td>1.48</td>\n",
       "      <td>4461556.02</td>\n",
       "      <td>58.00</td>\n",
       "      <td>...</td>\n",
       "      <td>86.0</td>\n",
       "      <td>38.00</td>\n",
       "      <td>104.23</td>\n",
       "      <td>6.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79.00</td>\n",
       "      <td>39896879.66</td>\n",
       "      <td>6.878893e+08</td>\n",
       "      <td>58.00</td>\n",
       "      <td>0.001185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANZ-A</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>97.00</td>\n",
       "      <td>92.00</td>\n",
       "      <td>87.26</td>\n",
       "      <td>32.00</td>\n",
       "      <td>77.0</td>\n",
       "      <td>1.44</td>\n",
       "      <td>3951388.79</td>\n",
       "      <td>60.00</td>\n",
       "      <td>...</td>\n",
       "      <td>88.0</td>\n",
       "      <td>36.00</td>\n",
       "      <td>107.47</td>\n",
       "      <td>6.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79.00</td>\n",
       "      <td>35554699.71</td>\n",
       "      <td>6.108979e+08</td>\n",
       "      <td>57.00</td>\n",
       "      <td>0.001053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2479</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-08-01</td>\n",
       "      <td>90.52</td>\n",
       "      <td>69.79</td>\n",
       "      <td>87.26</td>\n",
       "      <td>7.17</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.11</td>\n",
       "      <td>186480.05</td>\n",
       "      <td>44.12</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>86.89</td>\n",
       "      <td>9.49</td>\n",
       "      <td>36.46</td>\n",
       "      <td>62.53</td>\n",
       "      <td>1965684.04</td>\n",
       "      <td>1.799400e+07</td>\n",
       "      <td>42.11</td>\n",
       "      <td>0.000031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2480</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-09-01</td>\n",
       "      <td>90.52</td>\n",
       "      <td>69.79</td>\n",
       "      <td>87.26</td>\n",
       "      <td>7.17</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.06</td>\n",
       "      <td>244750.69</td>\n",
       "      <td>44.12</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>111.61</td>\n",
       "      <td>12.93</td>\n",
       "      <td>36.46</td>\n",
       "      <td>62.53</td>\n",
       "      <td>3352397.25</td>\n",
       "      <td>2.893069e+07</td>\n",
       "      <td>42.11</td>\n",
       "      <td>0.000050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2481</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>90.52</td>\n",
       "      <td>69.79</td>\n",
       "      <td>87.26</td>\n",
       "      <td>7.17</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.09</td>\n",
       "      <td>270900.52</td>\n",
       "      <td>44.12</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.17</td>\n",
       "      <td>81.65</td>\n",
       "      <td>10.59</td>\n",
       "      <td>36.46</td>\n",
       "      <td>62.53</td>\n",
       "      <td>3130631.76</td>\n",
       "      <td>2.412814e+07</td>\n",
       "      <td>42.11</td>\n",
       "      <td>0.000042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2482</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>90.52</td>\n",
       "      <td>69.79</td>\n",
       "      <td>87.26</td>\n",
       "      <td>7.17</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.15</td>\n",
       "      <td>190178.45</td>\n",
       "      <td>44.12</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>90.51</td>\n",
       "      <td>11.98</td>\n",
       "      <td>36.46</td>\n",
       "      <td>62.53</td>\n",
       "      <td>2609846.67</td>\n",
       "      <td>1.972099e+07</td>\n",
       "      <td>42.11</td>\n",
       "      <td>0.000034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2483</th>\n",
       "      <td>Prizy-ZV</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>90.52</td>\n",
       "      <td>69.79</td>\n",
       "      <td>87.26</td>\n",
       "      <td>7.17</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.09</td>\n",
       "      <td>125220.77</td>\n",
       "      <td>44.12</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.17</td>\n",
       "      <td>63.10</td>\n",
       "      <td>8.92</td>\n",
       "      <td>36.46</td>\n",
       "      <td>62.53</td>\n",
       "      <td>1212421.25</td>\n",
       "      <td>8.576195e+06</td>\n",
       "      <td>42.11</td>\n",
       "      <td>0.000015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2484 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Metric Company-Brand      Dates  Awareness  Consideration  Ever Used   \n",
       "0              ANZ-A 2020-01-01      98.00          95.00      87.26  \\\n",
       "1              ANZ-A 2020-02-01      97.00          93.00      87.26   \n",
       "2              ANZ-A 2020-03-01      96.00          89.00      87.26   \n",
       "3              ANZ-A 2020-04-01      98.00          92.00      87.26   \n",
       "4              ANZ-A 2020-05-01      97.00          92.00      87.26   \n",
       "...              ...        ...        ...            ...        ...   \n",
       "2479        Prizy-ZV 2022-08-01      90.52          69.79      87.26   \n",
       "2480        Prizy-ZV 2022-09-01      90.52          69.79      87.26   \n",
       "2481        Prizy-ZV 2022-10-01      90.52          69.79      87.26   \n",
       "2482        Prizy-ZV 2022-11-01      90.52          69.79      87.26   \n",
       "2483        Prizy-ZV 2022-12-01      90.52          69.79      87.26   \n",
       "\n",
       "Metric  Most Often  No of Sku (barcode)  PF (times)  Penetration  Performance   \n",
       "0            44.00                 80.0        1.46   4575322.59        63.00  \\\n",
       "1            37.00                 84.0        1.45   4943679.08        56.00   \n",
       "2            30.00                 85.0        1.43   4378076.07        50.00   \n",
       "3            30.00                 77.0        1.48   4461556.02        58.00   \n",
       "4            32.00                 77.0        1.44   3951388.79        60.00   \n",
       "...            ...                  ...         ...          ...          ...   \n",
       "2479          7.17                  3.0        1.11    186480.05        44.12   \n",
       "2480          7.17                  3.0        1.06    244750.69        44.12   \n",
       "2481          7.17                  3.0        1.09    270900.52        44.12   \n",
       "2482          7.17                  3.0        1.15    190178.45        44.12   \n",
       "2483          7.17                  3.0        1.09    125220.77        44.12   \n",
       "\n",
       "Metric  ...  Spontaneous Awareness    TOM  TS (gr)  TV (tl)   \n",
       "0       ...                   86.0  45.00    88.16     5.05  \\\n",
       "1       ...                   80.0  37.00    91.31     5.25   \n",
       "2       ...                   82.0  36.00    92.27     5.35   \n",
       "3       ...                   86.0  38.00   104.23     6.05   \n",
       "4       ...                   88.0  36.00   107.47     6.25   \n",
       "...     ...                    ...    ...      ...      ...   \n",
       "2479    ...                    4.0   0.00    86.89     9.49   \n",
       "2480    ...                    3.0   0.00   111.61    12.93   \n",
       "2481    ...                    5.0   6.17    81.65    10.59   \n",
       "2482    ...                    2.0   0.00    90.51    11.98   \n",
       "2483    ...                    3.0   6.17    63.10     8.92   \n",
       "\n",
       "Metric  Total Ad Awareness (T5B)  Trial   Value (tl)   Volume (gr)  Yakınlık   \n",
       "0                            NaN  85.00  33813115.02  5.902114e+08     73.00  \\\n",
       "1                            NaN  81.00  37605796.32  6.541825e+08     62.00   \n",
       "2                            NaN  75.00  33493507.49  5.773998e+08     55.00   \n",
       "3                            NaN  79.00  39896879.66  6.878893e+08     58.00   \n",
       "4                            NaN  79.00  35554699.71  6.108979e+08     57.00   \n",
       "...                          ...    ...          ...           ...       ...   \n",
       "2479                       36.46  62.53   1965684.04  1.799400e+07     42.11   \n",
       "2480                       36.46  62.53   3352397.25  2.893069e+07     42.11   \n",
       "2481                       36.46  62.53   3130631.76  2.412814e+07     42.11   \n",
       "2482                       36.46  62.53   2609846.67  1.972099e+07     42.11   \n",
       "2483                       36.46  62.53   1212421.25  8.576195e+06     42.11   \n",
       "\n",
       "Metric  Market Share  \n",
       "0           0.001017  \n",
       "1           0.001127  \n",
       "2           0.000995  \n",
       "3           0.001185  \n",
       "4           0.001053  \n",
       "...              ...  \n",
       "2479        0.000031  \n",
       "2480        0.000050  \n",
       "2481        0.000042  \n",
       "2482        0.000034  \n",
       "2483        0.000015  \n",
       "\n",
       "[2484 rows x 26 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "alignmentgroup": "True",
         "hovertemplate": "Correlation=%{x}<br>Metrics=%{y}<extra></extra>",
         "legendgroup": "",
         "marker": {
          "color": "#636efa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "",
         "offsetgroup": "",
         "orientation": "h",
         "showlegend": false,
         "textposition": "auto",
         "type": "bar",
         "x": [
          1.0000000000000018,
          1,
          0.7752022793187834,
          0.7525032962414999,
          0.7100342287050461,
          0.6067803819837223,
          0.5206472803821912,
          0.46127764965518564,
          0.3901638403085763,
          0.37123637011678445,
          0.3681175991599162,
          0.3431594393266282,
          0.32248475513210173,
          0.2984937932228811,
          0.2844241226310794,
          0.2624805954827367,
          0.24174737958495965,
          0.2352253170146539,
          0.2209972574612535,
          0.18455506465128896,
          0.08621087455169953,
          0.02167478032032959,
          -0.03324684776273581,
          -0.30467245120950714
         ],
         "xaxis": "x",
         "y": [
          "Volume (gr)",
          "Market Share",
          "QPB (gr)",
          "Penetration",
          "TS (gr)",
          "Value (tl)",
          "REPEATER Rate",
          "PF (times)",
          "Repurchase",
          "Trial",
          "Spontaneous Awareness",
          "Purchase",
          "TOM",
          "No of Sku (barcode)",
          "Consideration",
          "Most Often",
          "Performance",
          "Yakınlık",
          "Awareness",
          "Ever Used",
          "Total Ad Awareness (T5B)",
          "SPB (gr)",
          "TV (tl)",
          "Price (gr/tl)"
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "barmode": "relative",
        "height": 800,
        "legend": {
         "tracegroupgap": 0
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "Correlation with Market Share"
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "Correlation"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "Metrics"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Now that we created our Market Share metric lets visualize its correlation with other metrics\n",
    "market_share_corr = (\n",
    "    pivoted.drop([\"Company-Brand\", \"Dates\"], axis=1)\n",
    "    .corr()[\"Market Share\"]\n",
    "    .sort_values(ascending=False)\n",
    ")\n",
    "\n",
    "fig = px.bar(\n",
    "    y=market_share_corr.index,\n",
    "    x=market_share_corr.values,\n",
    "    orientation=\"h\",\n",
    "    title=\"Correlation with Market Share\",\n",
    "    labels={\"x\": \"Correlation\", \"y\": \"Metrics\"},\n",
    "    height=800,\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "def plot_all_metrics(df2, plot_only_prizy=False, save_plots=False, save_path=\"plots/all_company_metrics\", show_plots=True, separate_prizy_x_y=True):\n",
    "    # create directory if it doesn't exist\n",
    "    if not os.path.exists(save_path):\n",
    "        os.makedirs(save_path)\n",
    "\n",
    "    df2[\"Company\"] = df2[\"Company-Brand\"].apply(lambda x: x.split(\"-\")[0])\n",
    "    df2[\"Brand\"] = df2[\"Company-Brand\"].apply(lambda x: x.split(\"-\")[-1])\n",
    "\n",
    "    if separate_prizy_x_y:\n",
    "        y_brands = [\n",
    "            \"Y\",\n",
    "            \"Y-i\",\n",
    "            \"Y-ii\",\n",
    "            \"Y-iii\",\n",
    "        ]\n",
    "\n",
    "        x_brands = [\n",
    "            \"X\",\n",
    "            \"X-i\",\n",
    "            \"X-ii\",\n",
    "        ]\n",
    "        df2.loc[df2['Brand'].isin(y_brands), 'Company'] = \"Prizy_y\"\n",
    "        df2.loc[df2['Brand'].isin(x_brands), 'Company'] = \"Prizy_x\"\n",
    "\n",
    "    company_means = df2.groupby([\"Company\", \"Metric\", \"Dates\"])[\"Value\"].mean()\n",
    "    company_means = company_means.reset_index()\n",
    "\n",
    "    for metric in company_means[\"Metric\"].unique():\n",
    "        metric_mean = company_means[company_means[\"Metric\"] == metric][\"Value\"].mean()\n",
    "\n",
    "        if plot_only_prizy:\n",
    "            company_means = company_means[company_means[\"Company\"].isin([\"Prizy_x\", \"Prizy_y\", \"Prizy\"])]\n",
    "\n",
    "        fig = px.line(company_means[company_means[\"Metric\"] == metric], x=\"Dates\", y=\"Value\", color=\"Company\")\n",
    "        fig.add_trace(go.Scatter(x=company_means[company_means[\"Metric\"] == metric][\"Dates\"], y=[metric_mean] * len(company_means[company_means[\"Metric\"] == metric]), mode=\"lines\", name=\"Mean\", line=dict(color=\"red\", width=3, dash=\"dash\")))\n",
    "        \n",
    "\n",
    "        fig.update_layout(title=metric, legend=dict(x=1.05, y=1, bordercolor=\"gray\", borderwidth=1))\n",
    "\n",
    "        if save_plots:\n",
    "            os.makedirs(save_path, exist_ok=True)\n",
    "            metric = re.sub(r'\\([^)]*\\)', '', metric).strip()\n",
    "            fig.write_image(f\"{save_path}/{metric}.png\")\n",
    "\n",
    "        if show_plots:\n",
    "            fig.update_xaxes(tickangle=45)\n",
    "            fig.show()\n",
    "\n",
    "\n",
    "\n",
    "plot_all_metrics(df2, plot_only_prizy=True, save_plots=True, save_path=\"plots/all_company_metrics\", show_plots=False, separate_prizy_x_y=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "prizy_y = df2[df2[\"Company\"] == \"Prizy_y\"]\n",
    "prizy_x = df2[df2[\"Company\"] == \"Prizy_x\"]\n",
    "\n",
    "prizy_y_pivot = prizy_y.pivot_table(index='Dates', columns='Metric', values='Value')\n",
    "prizy_x_pivot = prizy_x.pivot_table(index='Dates', columns='Metric', values='Value')\n",
    "\n",
    "\n",
    "# Define a function to forecast a given metric for a given company\n",
    "def forecast_metric(df, metric):\n",
    "    # Create a new dataframe with the required columns\n",
    "    df = df.copy()\n",
    "    df = df.reset_index()\n",
    "    data = df[['Dates', metric]].rename(columns={metric: 'y'})\n",
    "    data = data.dropna()  # Drop rows with missing values\n",
    "    data[\"ds\"] = data[\"Dates\"]\n",
    "    # Create and fit a new Prophet model\n",
    "    model = Prophet()\n",
    "    model.fit(data)\n",
    "\n",
    "    future = model.make_future_dataframe(periods=6*12, freq='M')  # Forecast for 6 years\n",
    "    forecast = model.predict(future)\n",
    "    forecast = forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]\n",
    "\n",
    "    # Rename the columns to match the original dataframe\n",
    "    forecast = forecast.rename(\n",
    "        columns={\n",
    "            'ds': 'date',\n",
    "            'yhat': metric,\n",
    "            'yhat_lower': f'{metric}_lower',\n",
    "            'yhat_upper': f'{metric}_upper',\n",
    "        }\n",
    "    )\n",
    "\n",
    "    # Set the index to be the date and return the forecast dataframe\n",
    "    return forecast.set_index('date')\n",
    "\n",
    "\n",
    "# Create an empty dataframe to store the forecasts\n",
    "prizy_x_predictions = pd.DataFrame()\n",
    "prizy_y_predictions = pd.DataFrame()\n",
    "\n",
    "# Loop through each metric and forecast it for Prizy-X\n",
    "for metric in prizy_x_pivot.columns:\n",
    "    metric_predictions = forecast_metric(prizy_x_pivot, metric)\n",
    "    prizy_x_predictions = pd.concat([prizy_x_predictions, metric_predictions], axis=1)\n",
    "\n",
    "    metric_predictions = forecast_metric(prizy_y_pivot, metric)\n",
    "    prizy_y_predictions = pd.concat([prizy_y_predictions, metric_predictions], axis=1)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def plot_forecast(pivots: tuple, predictions: tuple, save_plots=False, save_path=\"plots/forecast\", show_plots=True):    \n",
    "    # in predictions cut all values before 2022-12-31\n",
    "    prizy_x_pivot, prizy_y_pivot = pivots\n",
    "    prizy_x_predictions, prizy_y_predictions = predictions\n",
    "\n",
    "    prizy_x_predictions = prizy_x_predictions[prizy_x_predictions.index > \"2022-12-31\"]\n",
    "    prizy_y_predictions = prizy_y_predictions[prizy_y_predictions.index > \"2022-12-31\"]\n",
    "\n",
    "    for metric in prizy_x_pivot.columns:\n",
    "        fig, ax = plt.subplots(figsize=(15, 5))\n",
    "        \n",
    "        # metric, metric_lower, metric_upper \n",
    "        # and their corresponding prizy_x and prizy_y\n",
    "\n",
    "        # Plot the actual values\n",
    "        sns.lineplot(data=prizy_x_pivot, x=prizy_x_pivot.index, y=metric, ax=ax, label=\"Prizy-X\", color=\"red\")\n",
    "        sns.lineplot(data=prizy_y_pivot, x=prizy_y_pivot.index, y=metric, ax=ax, label=\"Prizy-Y\", color=\"blue\")\n",
    "\n",
    "        # Plot the forecasts\n",
    "        sns.lineplot(data=prizy_x_predictions, x=prizy_x_predictions.index, y=metric, ax=ax, label=\"Prizy-X Forecast\", color=\"red\")\n",
    "        sns.lineplot(data=prizy_y_predictions, x=prizy_y_predictions.index, y=metric, ax=ax, label=\"Prizy-Y Forecast\", color=\"blue\")\n",
    "\n",
    "        # Plot the confidence intervals\n",
    "        ax.fill_between(\n",
    "            prizy_x_predictions.index,\n",
    "            prizy_x_predictions[f'{metric}_lower'],\n",
    "            prizy_x_predictions[f'{metric}_upper'],\n",
    "            alpha=0.3,\n",
    "            label=\"Confidence Interval\",\n",
    "            color=\"red\"\n",
    "        )\n",
    "\n",
    "        ax.fill_between(\n",
    "            prizy_y_predictions.index,\n",
    "            prizy_y_predictions[f'{metric}_lower'],\n",
    "            prizy_y_predictions[f'{metric}_upper'],\n",
    "            alpha=0.3,\n",
    "            label=\"Confidence Interval\",\n",
    "            color=\"blue\"\n",
    "        )\n",
    "\n",
    "\n",
    "        plt.title(metric)\n",
    "\n",
    "        if save_plots:\n",
    "            os.makedirs(save_path, exist_ok=True)\n",
    "            metric = re.sub(r'\\([^)]*\\)', '', metric).strip()\n",
    "            fig.savefig(f\"{save_path}/{metric}.png\")\n",
    "\n",
    "        if show_plots:\n",
    "            plt.show()\n",
    "        plt.close()\n",
    "\n",
    "plot_forecast((prizy_x_pivot, prizy_y_pivot), (prizy_x_predictions, prizy_y_predictions), save_plots=True, save_path=\"plots/forecast\", show_plots=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.1"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}