lectures/tutorial-assignment-2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cca9b0e7-98e6-4861-97da-0032f358b8d4",
   "metadata": {},
   "source": [
    "# Tutorial Assignment 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "51ae9508-dfa6-4031-9a69-5bd640c08286",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sklearn.linear_model\n",
    "import sklearn.metrics  # use existing libraries!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6a54dcb2-9f1d-4362-b299-d06a0df85cdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# score function: binary cross entropy loss\n",
    "def score_yp(y, p): # y, p are numpy arrays\n",
    "    return sklearn.metrics.log_loss(y, p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "264d9bec-30cf-4996-8686-e06d373349d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# score wrapper, for data frames. we need this when using the `truth` data frame\n",
    "def score(x, y): # x, y are data frames\n",
    "    xy = pd.merge(x, y, on=[\"customer\", \"product\", \"week\"])\n",
    "    assert xy.shape[0] == x.shape[0]\n",
    "    return score_yp(xy[\"y\"].values, xy[\"probability\"].values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01161aa8-962e-4c8c-9ed2-4421c23ed05c",
   "metadata": {},
   "source": [
    "## Input"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8da0367b-3b62-46e1-ad64-dff69f73b65d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# the path that contains the data\n",
    "path = os.path.expandvars(\"~/Dropbox/teaching/big-data/data/dr-s/upload\")\n",
    "\n",
    "# note that you don't have access to the truth data set\n",
    "path_truth = os.path.expandvars(\"~/Dropbox/teaching/big-data/data/dr-s/private\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4ea8f257-e4f4-4258-acdc-6b125794a916",
   "metadata": {},
   "outputs": [],
   "source": [
    "# INPUT\n",
    "training_week = 88  # for model training\n",
    "validation_week = 89  # for model and baseline validation\n",
    "test_week = 90  # for the final prediction (one week in the future, beyond our data)\n",
    "target_customers = list(range(2000))\n",
    "target_products = list(range(250))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6b7cc60-62e5-40b7-9491-a2640f44e08c",
   "metadata": {},
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cf7453b7-28fd-490b-9efe-5af764d06a79",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71</td>\n",
       "      <td>629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>91</td>\n",
       "      <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116</td>\n",
       "      <td>715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>123</td>\n",
       "      <td>483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>157</td>\n",
       "      <td>592</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   week  customer  product  price\n",
       "0     0         0       71    629\n",
       "1     0         0       91    605\n",
       "2     0         0      116    715\n",
       "3     0         0      123    483\n",
       "4     0         0      157    592"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baskets = pd.read_parquet(f\"{path}/baskets.parquet\")\n",
    "# we only keep the 2000 customers required for the test set\n",
    "# a complex model might require more data, e.g., see Boosted Tree model in Gabel & Timoshenko (2021)\n",
    "baskets = baskets[baskets[\"customer\"]<2000]\n",
    "baskets.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "87236da5-ea4b-43f8-8aa4-5019ba484255",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   week  customer  product\n",
       "0    90         0        0\n",
       "1    90         0        1\n",
       "2    90         0        2\n",
       "3    90         0        3\n",
       "4    90         0        4"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prediction_index = pd.read_parquet(f\"{path}/prediction_index.parquet\")\n",
    "prediction_index.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48a56122-fd8e-46a4-8f03-2d315a4fba0a",
   "metadata": {},
   "source": [
    "<br> \n",
    "\n",
    "## Reminder\n",
    "\n",
    "<img src=\"https://raw.githubusercontent.com/sbstn-gbl/learning-from-big-data/master/source/_static/img/time-split.png\" width=\"900\"/>\n",
    "\n",
    "## Some first steps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0679623e-5782-4f12-9e77-7343d572d5c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "89"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baskets.week.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "63428aa6-0d0d-466f-ad34-704ffa2265fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "90\n"
     ]
    }
   ],
   "source": [
    "n_weeks = baskets.week.nunique()\n",
    "print(n_weeks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1b8818aa-71d2-439c-9b7a-60f70844771d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.08888888888888889"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    baskets[(baskets[\"customer\"]==0) & (baskets[\"product\"]==4)]\n",
    "    .week.count()\n",
    "    / n_weeks\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "708b97ef-7188-4a10-abf2-58662ccec1aa",
   "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>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.088889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.011111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>0.044444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>0.011111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142059</th>\n",
       "      <td>1999</td>\n",
       "      <td>237</td>\n",
       "      <td>0.044444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142060</th>\n",
       "      <td>1999</td>\n",
       "      <td>242</td>\n",
       "      <td>0.388889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142061</th>\n",
       "      <td>1999</td>\n",
       "      <td>243</td>\n",
       "      <td>0.011111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142062</th>\n",
       "      <td>1999</td>\n",
       "      <td>245</td>\n",
       "      <td>0.133333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142063</th>\n",
       "      <td>1999</td>\n",
       "      <td>249</td>\n",
       "      <td>0.088889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>142064 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customer  product  probability\n",
       "0              0        4     0.088889\n",
       "1              0       11     0.100000\n",
       "2              0       15     0.011111\n",
       "3              0       16     0.044444\n",
       "4              0       21     0.011111\n",
       "...          ...      ...          ...\n",
       "142059      1999      237     0.044444\n",
       "142060      1999      242     0.388889\n",
       "142061      1999      243     0.011111\n",
       "142062      1999      245     0.133333\n",
       "142063      1999      249     0.088889\n",
       "\n",
       "[142064 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this is our first \"model\" – wow, that's easy\n",
    "purchase_frequency_ij = (\n",
    "    (\n",
    "        baskets.groupby([\"customer\", \"product\"])\n",
    "        [[\"week\"]].count()\n",
    "        / n_weeks\n",
    "    )\n",
    "    .rename(columns={\"week\": \"probability\"})\n",
    "    .reset_index()\n",
    ")\n",
    "purchase_frequency_ij"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "39dfbedc-6a16-4578-af82-cade6c930741",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d454b813-d592-4bc6-a788-2cbacd7369d2",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        week  customer  product\n",
       "0         90         0        0\n",
       "1         90         0        1\n",
       "2         90         0        2\n",
       "3         90         0        3\n",
       "4         90         0        4\n",
       "...      ...       ...      ...\n",
       "499995    90      1999      245\n",
       "499996    90      1999      246\n",
       "499997    90      1999      247\n",
       "499998    90      1999      248\n",
       "499999    90      1999      249\n",
       "\n",
       "[500000 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add all missing values\n",
    "# Q1: what values are missing?\n",
    "# Q2: what is a good value for probabilities here?\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"week\": test_week,\n",
    "        \"customer\": np.repeat(target_customers, len(target_products), axis=0),\n",
    "        \"product\": target_products * len(target_customers),\n",
    "    }\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "243c1783-df48-48eb-abf9-5d4427c753c3",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.088889</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>499995</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>245</td>\n",
       "      <td>0.133333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>246</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>247</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>248</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>90</td>\n",
       "      <td>1999</td>\n",
       "      <td>249</td>\n",
       "      <td>0.088889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        week  customer  product  probability\n",
       "0         90         0        0     0.000000\n",
       "1         90         0        1     0.000000\n",
       "2         90         0        2     0.000000\n",
       "3         90         0        3     0.000000\n",
       "4         90         0        4     0.088889\n",
       "...      ...       ...      ...          ...\n",
       "499995    90      1999      245     0.133333\n",
       "499996    90      1999      246     0.000000\n",
       "499997    90      1999      247     0.000000\n",
       "499998    90      1999      248     0.000000\n",
       "499999    90      1999      249     0.088889\n",
       "\n",
       "[500000 rows x 4 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_baseline = pd.merge(\n",
    "    df,\n",
    "    purchase_frequency_ij,\n",
    "    on=[\"customer\", \"product\"],\n",
    "    how=\"left\",\n",
    ").fillna(0)\n",
    "result_baseline"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c26e13e7-eb6b-4836-8160-351e83b00326",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "## SOLUTION 1: Descriptive feature (past purchase rates) = Baseline\n",
    "\n",
    "Now we have predictions, but we have no idea how good this baseline is.  So let's do the following:\n",
    "1. construct a ground truth data set\n",
    "1. do a proper train/validation split\n",
    "1. predict purchase probabilities using our baseline\n",
    "1. evaluate the predictions vs. the base\n",
    "\n",
    "### Validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "067771f5-b725-438e-bc11-ccb51c055cc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to define target variable for all customer-product combinations (in a given week)\n",
    "def build_target(baskets, week):\n",
    "\n",
    "    baskets_week = baskets[baskets[\"week\"] == week][\n",
    "        [\"week\", \"customer\", \"product\"]\n",
    "    ].reset_index(drop=True)\n",
    "    baskets_week[\"y\"] = 1\n",
    "\n",
    "    df = pd.DataFrame(\n",
    "        {\n",
    "            \"week\": week,\n",
    "            \"customer\": np.repeat(target_customers, len(target_products), axis=0),\n",
    "            \"product\": target_products * len(target_customers),\n",
    "        }\n",
    "    )\n",
    "\n",
    "    df = df.merge(baskets_week, on=[\"week\", \"customer\", \"product\"], how=\"left\")\n",
    "    df[\"y\"] = df[\"y\"].fillna(0).astype(int)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "90a3dd46-eec4-40c1-95c9-7868e7c1b04c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "89"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "validation_week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c1072589-8cc3-42f3-9489-0cba40e13e81",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   week  customer  product  y\n",
       "0    89         0        0  0\n",
       "1    89         0        1  0\n",
       "2    89         0        2  0\n",
       "3    89         0        3  0\n",
       "4    89         0        4  0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseline_target = build_target(baskets, validation_week)\n",
    "baseline_target.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ecd950d9-b6f2-418a-80e2-71f7b6b563c4",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.000000</td>\n",
       "      <td>500000.000000</td>\n",
       "      <td>500000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>89.0</td>\n",
       "      <td>999.500000</td>\n",
       "      <td>124.500000</td>\n",
       "      <td>0.030386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>577.350774</td>\n",
       "      <td>72.168278</td>\n",
       "      <td>0.171647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>89.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>89.0</td>\n",
       "      <td>499.750000</td>\n",
       "      <td>62.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>89.0</td>\n",
       "      <td>999.500000</td>\n",
       "      <td>124.500000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>89.0</td>\n",
       "      <td>1499.250000</td>\n",
       "      <td>187.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>89.0</td>\n",
       "      <td>1999.000000</td>\n",
       "      <td>249.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           week       customer        product              y\n",
       "count  500000.0  500000.000000  500000.000000  500000.000000\n",
       "mean       89.0     999.500000     124.500000       0.030386\n",
       "std         0.0     577.350774      72.168278       0.171647\n",
       "min        89.0       0.000000       0.000000       0.000000\n",
       "25%        89.0     499.750000      62.000000       0.000000\n",
       "50%        89.0     999.500000     124.500000       0.000000\n",
       "75%        89.0    1499.250000     187.000000       0.000000\n",
       "max        89.0    1999.000000     249.000000       1.000000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseline_target.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "40fd4861-7705-47bd-961d-0df29d114984",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.99200722162641e-16"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the perfect prediction has a loss of 0\n",
    "score_yp(baseline_target.y.values, baseline_target.y.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a12153b6-4e7d-4cc7-8b83-11397c20c9c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# wrap code into function:\n",
    "# baseline = purchase rates for customer-product combinations before the target week\n",
    "def baseline_prediction(baskets, week):\n",
    "\n",
    "    # subset baskets\n",
    "    baskets_t = baskets[baskets[\"week\"] < week].reset_index(drop=True)\n",
    "    n_weeks = baskets_t.week.nunique()\n",
    "    print(n_weeks)\n",
    "\n",
    "    # model (non-0 probabilities)\n",
    "    purchase_frequency_ij = (\n",
    "        (baskets_t.groupby([\"customer\", \"product\"])[[\"week\"]].count() / n_weeks)\n",
    "        .rename(columns={\"week\": \"probability\"})\n",
    "        .reset_index()\n",
    "    )\n",
    "\n",
    "    # filling in 0s\n",
    "    df = pd.DataFrame(\n",
    "        {\n",
    "            \"week\": week,\n",
    "            \"customer\": np.repeat(target_customers, len(target_products), axis=0),\n",
    "            \"product\": target_products * len(target_customers),\n",
    "        }\n",
    "    )\n",
    "\n",
    "    result_baseline = pd.merge(\n",
    "        df,\n",
    "        purchase_frequency_ij,\n",
    "        on=[\"customer\", \"product\"],\n",
    "        how=\"left\",\n",
    "    ).fillna(0)\n",
    "\n",
    "    return result_baseline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "74a7db27-509a-468a-803d-87656c790ade",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "89\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.089888</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   week  customer  product  probability\n",
       "0    89         0        0     0.000000\n",
       "1    89         0        1     0.000000\n",
       "2    89         0        2     0.000000\n",
       "3    89         0        3     0.000000\n",
       "4    89         0        4     0.089888"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# prediction for validation data\n",
    "baseline_validation = baseline_prediction(baskets, validation_week)\n",
    "baseline_validation.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0a0142f1-825b-4e2a-87d9-a0ec334ddbd9",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</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>499995</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>245</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>246</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>247</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>248</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>249</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        week  customer  product  y\n",
       "0         89         0        0  0\n",
       "1         89         0        1  0\n",
       "2         89         0        2  0\n",
       "3         89         0        3  0\n",
       "4         89         0        4  0\n",
       "...      ...       ...      ... ..\n",
       "499995    89      1999      245  1\n",
       "499996    89      1999      246  0\n",
       "499997    89      1999      247  0\n",
       "499998    89      1999      248  0\n",
       "499999    89      1999      249  0\n",
       "\n",
       "[500000 rows x 4 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseline_target"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a3306a3d-1320-45a0-8b6c-e853f690a2e7",
   "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>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.089888</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>499995</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>245</td>\n",
       "      <td>0.123596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>246</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>247</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>248</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>89</td>\n",
       "      <td>1999</td>\n",
       "      <td>249</td>\n",
       "      <td>0.089888</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        week  customer  product  probability\n",
       "0         89         0        0     0.000000\n",
       "1         89         0        1     0.000000\n",
       "2         89         0        2     0.000000\n",
       "3         89         0        3     0.000000\n",
       "4         89         0        4     0.089888\n",
       "...      ...       ...      ...          ...\n",
       "499995    89      1999      245     0.123596\n",
       "499996    89      1999      246     0.000000\n",
       "499997    89      1999      247     0.000000\n",
       "499998    89      1999      248     0.000000\n",
       "499999    89      1999      249     0.089888\n",
       "\n",
       "[500000 rows x 4 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseline_validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f02626fc-3ef6-41aa-8b08-4c22040a15a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.10034939532139737"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# score for validation data\n",
    "score(baseline_target, baseline_validation)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe67e876-f4a2-4c92-8185-e23469736030",
   "metadata": {},
   "source": [
    "### Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "5dcc090f-37e3-4484-8a32-931658388c82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "validation_week + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "61632ad6-4d7d-4526-be15-e0681dfae9a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a0b8b5b0-bc38-45de-bd24-f6efe662cea1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "90\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.088889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   week  customer  product  probability\n",
       "0    90         0        0     0.000000\n",
       "1    90         0        1     0.000000\n",
       "2    90         0        2     0.000000\n",
       "3    90         0        3     0.000000\n",
       "4    90         0        4     0.088889"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# prediction for test data\n",
    "# we can't evaluate this\n",
    "baseline_test = baseline_prediction(baskets, test_week)\n",
    "baseline_test.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2896477c-d68d-42bd-9fb7-6a2b22f8559a",
   "metadata": {},
   "source": [
    "Conclusion:\n",
    "1. Train model to week 88, evaluate predictions for week 89 with truth data \n",
    "1. Predcit week 90, and submit"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab68bae7-b6e8-4c26-a538-d45773a1e8e4",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "## SOLUTION 2: simple machine learning model   \n",
    "\n",
    "### Example for constructing the features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4065efc6-5713-4a57-a130-a67a363479a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_frequency_feature(baskets, week_start, week_end, feature_name):\n",
    "    # subset baskets\n",
    "    baskets_subset = baskets[\n",
    "        (baskets[\"week\"] >= week_start) & (baskets[\"week\"] <= week_end)\n",
    "    ]\n",
    "    print(baskets_subset.week.nunique())\n",
    "\n",
    "    purchase_frequency_ij = (\n",
    "        (\n",
    "            baskets_subset.groupby([\"customer\", \"product\"])[[\"week\"]].count()\n",
    "            / baskets_subset.week.nunique()\n",
    "        )\n",
    "        .rename(columns={\"week\": feature_name})\n",
    "        .reset_index()\n",
    "    )\n",
    "\n",
    "    return purchase_frequency_ij"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "3bcc5035-0054-4732-abbe-449f5fe0507d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "88\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>frequency_full</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.090909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0.102273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.011364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>0.034091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>0.011364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141147</th>\n",
       "      <td>1999</td>\n",
       "      <td>237</td>\n",
       "      <td>0.045455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141148</th>\n",
       "      <td>1999</td>\n",
       "      <td>242</td>\n",
       "      <td>0.397727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141149</th>\n",
       "      <td>1999</td>\n",
       "      <td>243</td>\n",
       "      <td>0.011364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141150</th>\n",
       "      <td>1999</td>\n",
       "      <td>245</td>\n",
       "      <td>0.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141151</th>\n",
       "      <td>1999</td>\n",
       "      <td>249</td>\n",
       "      <td>0.090909</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141152 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customer  product  frequency_full\n",
       "0              0        4        0.090909\n",
       "1              0       11        0.102273\n",
       "2              0       15        0.011364\n",
       "3              0       16        0.034091\n",
       "4              0       21        0.011364\n",
       "...          ...      ...             ...\n",
       "141147      1999      237        0.045455\n",
       "141148      1999      242        0.397727\n",
       "141149      1999      243        0.011364\n",
       "141150      1999      245        0.125000\n",
       "141151      1999      249        0.090909\n",
       "\n",
       "[141152 rows x 3 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "build_frequency_feature(baskets, -1, training_week - 1, \"frequency_full\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d231b96-d896-4db1-96c7-ae2f9d4e6468",
   "metadata": {},
   "source": [
    "### Example for building the base table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a59c0d16-5468-4bee-a25a-5620ed685fa4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_base_table(baskets, week):\n",
    "    # target variable (product purchase)\n",
    "    # consider using multiple weeks for training! more data might lead to better results.\n",
    "    # also, different weeks might have different information.\n",
    "    y = build_target(baskets, week)\n",
    "    # features\n",
    "    # note how features are computed on data BEFORE the target week\n",
    "    x_1 = build_frequency_feature(baskets, -1, week - 1, \"frequency_full\")\n",
    "    x_2 = build_frequency_feature(baskets, week - 30, week - 1, \"frequency_l30\")\n",
    "    x_3 = build_frequency_feature(baskets, week - 5, week - 1, \"frequency_l5\")\n",
    "    base_table_yx = (\n",
    "        y.merge(x_1, on=[\"customer\", \"product\"], how=\"left\")\n",
    "        .merge(x_2, on=[\"customer\", \"product\"], how=\"left\")\n",
    "        .merge(x_3, on=[\"customer\", \"product\"], how=\"left\")\n",
    "        .fillna(0)\n",
    "    )\n",
    "    return base_table_yx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "70480664-3133-432a-a1e1-88db432355ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "88\n",
      "30\n",
      "5\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week</th>\n",
       "      <th>customer</th>\n",
       "      <th>product</th>\n",
       "      <th>y</th>\n",
       "      <th>frequency_full</th>\n",
       "      <th>frequency_l30</th>\n",
       "      <th>frequency_l5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>88</td>\n",
       "      <td>1999</td>\n",
       "      <td>245</td>\n",
       "      <td>0</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.133333</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>88</td>\n",
       "      <td>1999</td>\n",
       "      <td>246</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>88</td>\n",
       "      <td>1999</td>\n",
       "      <td>247</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>88</td>\n",
       "      <td>1999</td>\n",
       "      <td>248</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>88</td>\n",
       "      <td>1999</td>\n",
       "      <td>249</td>\n",
       "      <td>0</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        week  customer  product  y  frequency_full  frequency_l30  \\\n",
       "0         88         0        0  0        0.000000       0.000000   \n",
       "1         88         0        1  0        0.000000       0.000000   \n",
       "2         88         0        2  0        0.000000       0.000000   \n",
       "3         88         0        3  0        0.000000       0.000000   \n",
       "4         88         0        4  0        0.090909       0.100000   \n",
       "...      ...       ...      ... ..             ...            ...   \n",
       "499995    88      1999      245  0        0.125000       0.133333   \n",
       "499996    88      1999      246  0        0.000000       0.000000   \n",
       "499997    88      1999      247  0        0.000000       0.000000   \n",
       "499998    88      1999      248  0        0.000000       0.000000   \n",
       "499999    88      1999      249  0        0.090909       0.000000   \n",
       "\n",
       "        frequency_l5  \n",
       "0                0.0  \n",
       "1                0.0  \n",
       "2                0.0  \n",
       "3                0.0  \n",
       "4                0.0  \n",
       "...              ...  \n",
       "499995           0.0  \n",
       "499996           0.0  \n",
       "499997           0.0  \n",
       "499998           0.0  \n",
       "499999           0.0  \n",
       "\n",
       "[500000 rows x 7 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "build_base_table(baskets, training_week)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb767f60-d7f4-4a70-b723-2328705831f0",
   "metadata": {},
   "source": [
    "### Training: Train model (week < 89)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "57bccda4-6fb8-4ea8-984c-7de207ca3b2f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "88\n",
      "30\n",
      "5\n"
     ]
    }
   ],
   "source": [
    "base_table_train = build_base_table(baskets, training_week)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "56cd38b6-0689-4df2-b3f4-daf2b0593d51",
   "metadata": {},
   "outputs": [],
   "source": [
    "y = base_table_train[\"y\"].values  # 1s and 0s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "8ccf351c-7628-4cd0-9d8e-87629d2d5ce4",
   "metadata": {},
   "outputs": [],
   "source": [
    "X = base_table_train[[\"frequency_full\", \"frequency_l30\", \"frequency_l5\"]].values  # purchase frequencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ae120e14-f2f1-4c6b-80ee-11b69fa61486",
   "metadata": {},
   "outputs": [],
   "source": [
    "log_reg = sklearn.linear_model.LogisticRegression().fit(X, y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "93867b1f-651b-4c6a-a397-600340f184f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([-4.48545291]), array([[10.74680489,  0.30805447, -0.98091252]]))"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log_reg.intercept_, log_reg.coef_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "8fb23066-f93e-4006-b690-a520325462d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "base_table_train[\"probability\"] = log_reg.predict_proba(X)[:, 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "624e3305-0ded-40a1-b549-841be54a0f93",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.09008063416504797"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_yp(\n",
    "    base_table_train[\"y\"].values,\n",
    "    base_table_train[\"probability\"].values,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "941b5649-7825-4459-8b55-958099aec8bb",
   "metadata": {},
   "source": [
    "### Validation: Test model performance (week 89)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "743ceb26-a12f-4255-84cd-23736e789cdb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "89\n",
      "30\n",
      "5\n"
     ]
    }
   ],
   "source": [
    "base_table_validation = build_base_table(baskets, validation_week)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "ba893570-26f2-462b-87d4-371aae3f77c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_validation = base_table_validation[\n",
    "    [\"frequency_full\", \"frequency_l30\", \"frequency_l5\"]\n",
    "].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "de82b189-49f0-4451-a224-e337e22c43c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "base_table_validation[\"probability\"] = log_reg.predict_proba(X_validation)[:, 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "d1e4689f-04cd-4747-8d2e-1aebe32950fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.08962234697918603"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_yp(\n",
    "    base_table_validation[\"y\"].values,\n",
    "    base_table_validation[\"probability\"].values,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71de74d3-b96d-40b0-89c8-4365bdfd178e",
   "metadata": {},
   "source": [
    "### Test: Produce final result (week 90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "97d2670f-8cbe-450e-9b9c-6192d9b46414",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "90\n",
      "30\n",
      "5\n"
     ]
    }
   ],
   "source": [
    "base_table_test = build_base_table(baskets, test_week)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "91222ef4-7878-43db-95d7-c431742ce699",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_test = base_table_test[[\"frequency_full\", \"frequency_l30\", \"frequency_l5\"]].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "3ce7d8db-0370-4303-8a88-e6c4bef74106",
   "metadata": {},
   "outputs": [],
   "source": [
    "base_table_test[\"probability\"] = log_reg.predict_proba(X_test)[:, 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3ecd46c-6fbc-4520-8ad6-358a874a4e3b",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "## Score on test set\n",
    "\n",
    "In real life, never load your test/validation data durnig model training!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe03d728-289a-4ccc-b5b4-0bf0713af373",
   "metadata": {},
   "source": [
    "<img src=\"https://raw.githubusercontent.com/sbstn-gbl/learning-from-big-data/master/source/_static/img/danger-zone.gif\" width=\"700\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "70dd858b-6c77-45b7-bf6a-31efae5ae32a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# true purchases in week 90\n",
    "truth = pd.read_parquet(f\"{path_truth}/truth.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "62365c33-9c85-4340-ba7b-f16c3fb5b2a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.10008904979918823"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score(truth, baseline_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "3fe478c1-5a62-4c50-b6a0-b000a128b049",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.089987445612237"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score(\n",
    "    truth,\n",
    "    base_table_test[[\"customer\", \"product\", \"week\", \"probability\"]],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8064849-d130-45cb-a026-72f684304b69",
   "metadata": {},
   "source": [
    "<br>\n",
    "<br>\n",
    "&mdash; <br>\n",
    "Sebastian Gabel <br>\n",
    "`Learning from Big Data`, Module 2, Extra Tutorial <br>\n",
    "2021/22 <br>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}