next-purchase-date-with-ml-top-items.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "96729080",
   "metadata": {
    "papermill": {
     "duration": 0.005175,
     "end_time": "2024-02-20T07:00:26.218581",
     "exception": false,
     "start_time": "2024-02-20T07:00:26.213406",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Next Purchase Date with ML - Top Items"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1c1f6d5",
   "metadata": {
    "papermill": {
     "duration": 0.004057,
     "end_time": "2024-02-20T07:00:26.227487",
     "exception": false,
     "start_time": "2024-02-20T07:00:26.223430",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "In this use case, we use machine learning approach to know the model's performance if trained with some items with the highest number of transactions. Hopefully, we can achieve the best score because using the largest number of transactions compared to the others will make the model learn and perform better."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dadf43d1",
   "metadata": {
    "papermill": {
     "duration": 0.003857,
     "end_time": "2024-02-20T07:00:26.235516",
     "exception": false,
     "start_time": "2024-02-20T07:00:26.231659",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Load and Preprocess Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae8436a0",
   "metadata": {
    "papermill": {
     "duration": 0.003874,
     "end_time": "2024-02-20T07:00:26.243535",
     "exception": false,
     "start_time": "2024-02-20T07:00:26.239661",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "We use more than 500k transaction data between users and items from the EPM database. The raw data still has some returning transactions with a negative amount, but we are only looking for buying transactions. Each transaction has a timestamp record daily. Because a user can buy the same item multiple times on the same day, we consider it a single data aggregating the sales quantity column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0bfd3c3c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:26.253800Z",
     "iopub.status.busy": "2024-02-20T07:00:26.253099Z",
     "iopub.status.idle": "2024-02-20T07:00:28.820046Z",
     "shell.execute_reply": "2024-02-20T07:00:28.818682Z"
    },
    "papermill": {
     "duration": 2.575223,
     "end_time": "2024-02-20T07:00:28.822831",
     "exception": false,
     "start_time": "2024-02-20T07:00:26.247608",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_absolute_error, mean_squared_error\n",
    "from sklearn.model_selection import train_test_split\n",
    "from xgboost import XGBRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "af393c06",
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:28.833523Z",
     "iopub.status.busy": "2024-02-20T07:00:28.832989Z",
     "iopub.status.idle": "2024-02-20T07:00:30.590524Z",
     "shell.execute_reply": "2024-02-20T07:00:30.589332Z"
    },
    "papermill": {
     "duration": 1.765528,
     "end_time": "2024-02-20T07:00:30.592938",
     "exception": false,
     "start_time": "2024-02-20T07:00:28.827410",
     "status": "completed"
    },
    "tags": []
   },
   "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>trx_date</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>ship_to_id</th>\n",
       "      <th>branch_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_desc</th>\n",
       "      <th>principal_desc</th>\n",
       "      <th>gross_sales_amount</th>\n",
       "      <th>sales_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>KCFMB</td>\n",
       "      <td>CEFIXIME 100MG 50 KAPSUL</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>195000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>CKCOA</td>\n",
       "      <td>KALCINOL N CREAM  5 GR</td>\n",
       "      <td>KALBE NIMITZ (PHARMAMED)</td>\n",
       "      <td>28500</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TMFNB</td>\n",
       "      <td>METFORMIN HCL 200 TABLET</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>175000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TBSVC</td>\n",
       "      <td>BRONSOLVAN 100 TABLET</td>\n",
       "      <td>HEXPHARM TSJ (PHARMAMED)</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TALNF</td>\n",
       "      <td>AMLODIPINE BESILATE 10MG</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>255000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trx_date            customer_name ship_to_id branch_code item_code  \\\n",
       "0  2021-08-20  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     KCFMB   \n",
       "1  2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     CKCOA   \n",
       "2  2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TMFNB   \n",
       "3  2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TBSVC   \n",
       "4  2021-08-20  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TALNF   \n",
       "\n",
       "                  item_desc            principal_desc  gross_sales_amount  \\\n",
       "0  CEFIXIME 100MG 50 KAPSUL      HEXPHARM (PHARMAMED)              195000   \n",
       "1    KALCINOL N CREAM  5 GR  KALBE NIMITZ (PHARMAMED)               28500   \n",
       "2  METFORMIN HCL 200 TABLET      HEXPHARM (PHARMAMED)              175000   \n",
       "3     BRONSOLVAN 100 TABLET  HEXPHARM TSJ (PHARMAMED)               35000   \n",
       "4  AMLODIPINE BESILATE 10MG      HEXPHARM (PHARMAMED)              255000   \n",
       "\n",
       "   sales_qty  \n",
       "0          3  \n",
       "1          3  \n",
       "2          5  \n",
       "3          1  \n",
       "4          3  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load Dataset\n",
    "df = pd.read_csv('/kaggle/input/epm-prep/EPM.csv')\n",
    "df = df.drop(['Unnamed: 0', 'principal_code'], axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "976611c1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:30.603954Z",
     "iopub.status.busy": "2024-02-20T07:00:30.603562Z",
     "iopub.status.idle": "2024-02-20T07:00:30.777693Z",
     "shell.execute_reply": "2024-02-20T07:00:30.776521Z"
    },
    "papermill": {
     "duration": 0.182549,
     "end_time": "2024-02-20T07:00:30.780107",
     "exception": false,
     "start_time": "2024-02-20T07:00:30.597558",
     "status": "completed"
    },
    "tags": []
   },
   "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>trx_date</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>ship_to_id</th>\n",
       "      <th>branch_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_desc</th>\n",
       "      <th>principal_desc</th>\n",
       "      <th>gross_sales_amount</th>\n",
       "      <th>sales_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>KCFMB</td>\n",
       "      <td>CEFIXIME 100MG 50 KAPSUL</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>195000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>CKCOA</td>\n",
       "      <td>KALCINOL N CREAM  5 GR</td>\n",
       "      <td>KALBE NIMITZ (PHARMAMED)</td>\n",
       "      <td>28500</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TMFNB</td>\n",
       "      <td>METFORMIN HCL 200 TABLET</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>175000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TBSVC</td>\n",
       "      <td>BRONSOLVAN 100 TABLET</td>\n",
       "      <td>HEXPHARM TSJ (PHARMAMED)</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TALNF</td>\n",
       "      <td>AMLODIPINE BESILATE 10MG</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>255000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    trx_date            customer_name ship_to_id branch_code item_code  \\\n",
       "0 2021-08-20  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     KCFMB   \n",
       "1 2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     CKCOA   \n",
       "2 2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TMFNB   \n",
       "3 2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TBSVC   \n",
       "4 2021-08-20  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TALNF   \n",
       "\n",
       "                  item_desc            principal_desc  gross_sales_amount  \\\n",
       "0  CEFIXIME 100MG 50 KAPSUL      HEXPHARM (PHARMAMED)              195000   \n",
       "1    KALCINOL N CREAM  5 GR  KALBE NIMITZ (PHARMAMED)               28500   \n",
       "2  METFORMIN HCL 200 TABLET      HEXPHARM (PHARMAMED)              175000   \n",
       "3     BRONSOLVAN 100 TABLET  HEXPHARM TSJ (PHARMAMED)               35000   \n",
       "4  AMLODIPINE BESILATE 10MG      HEXPHARM (PHARMAMED)              255000   \n",
       "\n",
       "   sales_qty  \n",
       "0          3  \n",
       "1          3  \n",
       "2          5  \n",
       "3          1  \n",
       "4          3  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter negative transactions\n",
    "df = df[(df['sales_qty'] > 0) & (df['gross_sales_amount'] > 0)]\n",
    "df['trx_date'] = pd.to_datetime(df['trx_date'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "41e9ed5a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:30.792028Z",
     "iopub.status.busy": "2024-02-20T07:00:30.791622Z",
     "iopub.status.idle": "2024-02-20T07:00:31.289674Z",
     "shell.execute_reply": "2024-02-20T07:00:31.288308Z"
    },
    "papermill": {
     "duration": 0.506899,
     "end_time": "2024-02-20T07:00:31.292240",
     "exception": false,
     "start_time": "2024-02-20T07:00:30.785341",
     "status": "completed"
    },
    "tags": []
   },
   "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>trx_date</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>ship_to_id</th>\n",
       "      <th>branch_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_desc</th>\n",
       "      <th>principal_desc</th>\n",
       "      <th>gross_sales_amount</th>\n",
       "      <th>sales_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>KCFMB</td>\n",
       "      <td>CEFIXIME 100MG 50 KAPSUL</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>195000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>CKCOA</td>\n",
       "      <td>KALCINOL N CREAM  5 GR</td>\n",
       "      <td>KALBE NIMITZ (PHARMAMED)</td>\n",
       "      <td>28500</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TMFNB</td>\n",
       "      <td>METFORMIN HCL 200 TABLET</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>175000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TBSVC</td>\n",
       "      <td>BRONSOLVAN 100 TABLET</td>\n",
       "      <td>HEXPHARM TSJ (PHARMAMED)</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TALNF</td>\n",
       "      <td>AMLODIPINE BESILATE 10MG</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>255000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    trx_date            customer_name ship_to_id branch_code item_code  \\\n",
       "0 2021-08-20  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     KCFMB   \n",
       "1 2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     CKCOA   \n",
       "2 2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TMFNB   \n",
       "3 2021-11-02  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TBSVC   \n",
       "4 2021-08-20  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TALNF   \n",
       "\n",
       "                  item_desc            principal_desc  gross_sales_amount  \\\n",
       "0  CEFIXIME 100MG 50 KAPSUL      HEXPHARM (PHARMAMED)              195000   \n",
       "1    KALCINOL N CREAM  5 GR  KALBE NIMITZ (PHARMAMED)               28500   \n",
       "2  METFORMIN HCL 200 TABLET      HEXPHARM (PHARMAMED)              175000   \n",
       "3     BRONSOLVAN 100 TABLET  HEXPHARM TSJ (PHARMAMED)               35000   \n",
       "4  AMLODIPINE BESILATE 10MG      HEXPHARM (PHARMAMED)              255000   \n",
       "\n",
       "   sales_qty  \n",
       "0          1  \n",
       "1          1  \n",
       "2          1  \n",
       "3          1  \n",
       "4          2  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop duplicate transactions\n",
    "temp = df[['ship_to_id', 'item_code', 'trx_date', 'sales_qty']].groupby(['ship_to_id', 'item_code', 'trx_date']).sum().reset_index(drop=True)\n",
    "df = df.drop_duplicates(['ship_to_id', 'item_code', 'trx_date']).reset_index(drop=True)\n",
    "df['sales_qty'] = temp\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a0151cb",
   "metadata": {
    "papermill": {
     "duration": 0.005079,
     "end_time": "2024-02-20T07:00:31.302665",
     "exception": false,
     "start_time": "2024-02-20T07:00:31.297586",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Preproecssing for Usecase"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0112376",
   "metadata": {
    "papermill": {
     "duration": 0.004804,
     "end_time": "2024-02-20T07:00:31.312631",
     "exception": false,
     "start_time": "2024-02-20T07:00:31.307827",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "We don't use all the features but only focus on the sales quantity and timestamp column.\n",
    "\n",
    "In the process of searching the selected data, we do these steps:\n",
    "1. Sort the unique items from the data based on the number of transactions.\n",
    "2. Choose the top x items with the highest number of transactions (in this case, x=1).\n",
    "3. From the selected item, sort the unique users from the data related to the chosen item only.\n",
    "4. Choose the top y users with the highest number of transactions (in this case, y=10)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4fe0d7b2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:31.324312Z",
     "iopub.status.busy": "2024-02-20T07:00:31.323891Z",
     "iopub.status.idle": "2024-02-20T07:00:32.804897Z",
     "shell.execute_reply": "2024-02-20T07:00:32.803872Z"
    },
    "papermill": {
     "duration": 1.489578,
     "end_time": "2024-02-20T07:00:32.807177",
     "exception": false,
     "start_time": "2024-02-20T07:00:31.317599",
     "status": "completed"
    },
    "tags": []
   },
   "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>ship_to_id</th>\n",
       "      <th>item_code</th>\n",
       "      <th>trx_date</th>\n",
       "      <th>sales_qty</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>KCFMB</td>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>1</td>\n",
       "      <td>1.629418e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>CKCOA</td>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>1</td>\n",
       "      <td>1.635811e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TMFNB</td>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>1</td>\n",
       "      <td>1.635811e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TBSVC</td>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>1</td>\n",
       "      <td>1.635811e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TALNF</td>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>2</td>\n",
       "      <td>1.629418e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ship_to_id item_code   trx_date  sales_qty     timestamp\n",
       "0  EPM_34950     KCFMB 2021-08-20          1  1.629418e+09\n",
       "1  EPM_34950     CKCOA 2021-11-02          1  1.635811e+09\n",
       "2  EPM_34950     TMFNB 2021-11-02          1  1.635811e+09\n",
       "3  EPM_34950     TBSVC 2021-11-02          1  1.635811e+09\n",
       "4  EPM_34950     TALNF 2021-08-20          2  1.629418e+09"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create timestamp column\n",
    "df['timestamp'] = df['trx_date'].apply(lambda x: x.timestamp())\n",
    "df['trx_date'] = pd.to_datetime(df['trx_date'])\n",
    "df = df[['ship_to_id', 'item_code', 'trx_date', 'sales_qty', 'timestamp']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c77ba2e2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:32.819345Z",
     "iopub.status.busy": "2024-02-20T07:00:32.818967Z",
     "iopub.status.idle": "2024-02-20T07:00:32.904978Z",
     "shell.execute_reply": "2024-02-20T07:00:32.903872Z"
    },
    "papermill": {
     "duration": 0.094936,
     "end_time": "2024-02-20T07:00:32.907553",
     "exception": false,
     "start_time": "2024-02-20T07:00:32.812617",
     "status": "completed"
    },
    "tags": []
   },
   "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>item_code</th>\n",
       "      <th>ship_to_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TALNE</td>\n",
       "      <td>7118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TMFNB</td>\n",
       "      <td>5631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TALNF</td>\n",
       "      <td>5593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TPRGR</td>\n",
       "      <td>4734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TPODA</td>\n",
       "      <td>4733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1284</th>\n",
       "      <td>SRIEM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1285</th>\n",
       "      <td>KHLBQ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1286</th>\n",
       "      <td>KHLBS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1287</th>\n",
       "      <td>TR1D1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1288</th>\n",
       "      <td>LPEGS</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1289 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     item_code  ship_to_id\n",
       "0        TALNE        7118\n",
       "1        TMFNB        5631\n",
       "2        TALNF        5593\n",
       "3        TPRGR        4734\n",
       "4        TPODA        4733\n",
       "...        ...         ...\n",
       "1284     SRIEM           1\n",
       "1285     KHLBQ           1\n",
       "1286     KHLBS           1\n",
       "1287     TR1D1           1\n",
       "1288     LPEGS           1\n",
       "\n",
       "[1289 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Look for top items with the most transactions \n",
    "dfSortItem = df.groupby(['item_code']).count().sort_values('trx_date', ascending=False).reset_index()\n",
    "dfSortItem[['item_code', 'ship_to_id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4ab21dac",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:32.921055Z",
     "iopub.status.busy": "2024-02-20T07:00:32.920526Z",
     "iopub.status.idle": "2024-02-20T07:00:32.981008Z",
     "shell.execute_reply": "2024-02-20T07:00:32.979856Z"
    },
    "papermill": {
     "duration": 0.070411,
     "end_time": "2024-02-20T07:00:32.983510",
     "exception": false,
     "start_time": "2024-02-20T07:00:32.913099",
     "status": "completed"
    },
    "tags": []
   },
   "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>ship_to_id</th>\n",
       "      <th>item_code</th>\n",
       "      <th>trx_date</th>\n",
       "      <th>sales_qty</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2021-08-20</td>\n",
       "      <td>4</td>\n",
       "      <td>1.629418e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2021-06-14</td>\n",
       "      <td>2</td>\n",
       "      <td>1.623629e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>3</td>\n",
       "      <td>1.613520e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2021-01-27</td>\n",
       "      <td>4</td>\n",
       "      <td>1.611706e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2021-12-06</td>\n",
       "      <td>2</td>\n",
       "      <td>1.638749e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7113</th>\n",
       "      <td>EPM_4504186</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2023-06-13</td>\n",
       "      <td>6</td>\n",
       "      <td>1.686614e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7114</th>\n",
       "      <td>EPM_4523526</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2023-07-25</td>\n",
       "      <td>1</td>\n",
       "      <td>1.690243e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7115</th>\n",
       "      <td>EPM_4523526</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2023-08-30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.693354e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7116</th>\n",
       "      <td>EPM_4524809</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2023-09-12</td>\n",
       "      <td>1</td>\n",
       "      <td>1.694477e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7117</th>\n",
       "      <td>EPM_4539938</td>\n",
       "      <td>TALNE</td>\n",
       "      <td>2023-08-22</td>\n",
       "      <td>2</td>\n",
       "      <td>1.692662e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7118 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ship_to_id item_code   trx_date  sales_qty     timestamp\n",
       "0       EPM_34950     TALNE 2021-08-20          4  1.629418e+09\n",
       "1       EPM_34950     TALNE 2021-06-14          2  1.623629e+09\n",
       "2       EPM_34950     TALNE 2021-02-17          3  1.613520e+09\n",
       "3       EPM_34950     TALNE 2021-01-27          4  1.611706e+09\n",
       "4       EPM_34950     TALNE 2021-12-06          2  1.638749e+09\n",
       "...           ...       ...        ...        ...           ...\n",
       "7113  EPM_4504186     TALNE 2023-06-13          6  1.686614e+09\n",
       "7114  EPM_4523526     TALNE 2023-07-25          1  1.690243e+09\n",
       "7115  EPM_4523526     TALNE 2023-08-30          1  1.693354e+09\n",
       "7116  EPM_4524809     TALNE 2023-09-12          1  1.694477e+09\n",
       "7117  EPM_4539938     TALNE 2023-08-22          2  1.692662e+09\n",
       "\n",
       "[7118 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show data with the first top item\n",
    "itemMax = dfSortItem.loc[0, 'item_code']\n",
    "dfItemMax = df[df['item_code'] == itemMax].reset_index(drop=True)\n",
    "dfItemMax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e2cbb269",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:32.997438Z",
     "iopub.status.busy": "2024-02-20T07:00:32.997043Z",
     "iopub.status.idle": "2024-02-20T07:00:33.012132Z",
     "shell.execute_reply": "2024-02-20T07:00:33.011142Z"
    },
    "papermill": {
     "duration": 0.024875,
     "end_time": "2024-02-20T07:00:33.014442",
     "exception": false,
     "start_time": "2024-02-20T07:00:32.989567",
     "status": "completed"
    },
    "tags": []
   },
   "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>ship_to_id</th>\n",
       "      <th>item_code</th>\n",
       "      <th>trx_date</th>\n",
       "      <th>sales_qty</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EPM_35159</td>\n",
       "      <td>178</td>\n",
       "      <td>178</td>\n",
       "      <td>178</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EPM_4334085</td>\n",
       "      <td>168</td>\n",
       "      <td>168</td>\n",
       "      <td>168</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EPM_1807311</td>\n",
       "      <td>165</td>\n",
       "      <td>165</td>\n",
       "      <td>165</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EPM_3564728</td>\n",
       "      <td>164</td>\n",
       "      <td>164</td>\n",
       "      <td>164</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EPM_35002</td>\n",
       "      <td>153</td>\n",
       "      <td>153</td>\n",
       "      <td>153</td>\n",
       "      <td>153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>EPM_34985</td>\n",
       "      <td>137</td>\n",
       "      <td>137</td>\n",
       "      <td>137</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>EPM_136080</td>\n",
       "      <td>133</td>\n",
       "      <td>133</td>\n",
       "      <td>133</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>EPM_1624002</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>EPM_3676050</td>\n",
       "      <td>125</td>\n",
       "      <td>125</td>\n",
       "      <td>125</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>EPM_34923</td>\n",
       "      <td>124</td>\n",
       "      <td>124</td>\n",
       "      <td>124</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ship_to_id  item_code  trx_date  sales_qty  timestamp\n",
       "0    EPM_35159        178       178        178        178\n",
       "1  EPM_4334085        168       168        168        168\n",
       "2  EPM_1807311        165       165        165        165\n",
       "3  EPM_3564728        164       164        164        164\n",
       "4    EPM_35002        153       153        153        153\n",
       "5    EPM_34985        137       137        137        137\n",
       "6   EPM_136080        133       133        133        133\n",
       "7  EPM_1624002        126       126        126        126\n",
       "8  EPM_3676050        125       125        125        125\n",
       "9    EPM_34923        124       124        124        124"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Look for top users with most transactions with the related item\n",
    "dfItemMaxSortUser = dfItemMax.groupby(['ship_to_id']).count().sort_values('trx_date', ascending=False).reset_index()\n",
    "dfItemMaxSortUser.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9e6c74c5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:33.027940Z",
     "iopub.status.busy": "2024-02-20T07:00:33.027552Z",
     "iopub.status.idle": "2024-02-20T07:00:33.035390Z",
     "shell.execute_reply": "2024-02-20T07:00:33.034612Z"
    },
    "papermill": {
     "duration": 0.016978,
     "end_time": "2024-02-20T07:00:33.037317",
     "exception": false,
     "start_time": "2024-02-20T07:00:33.020339",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['EPM_35159',\n",
       " 'EPM_4334085',\n",
       " 'EPM_1807311',\n",
       " 'EPM_3564728',\n",
       " 'EPM_35002',\n",
       " 'EPM_34985',\n",
       " 'EPM_136080',\n",
       " 'EPM_1624002',\n",
       " 'EPM_3676050',\n",
       " 'EPM_34923']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top = [i for i in range(10)]\n",
    "usersMax = dfItemMaxSortUser.loc[top, 'ship_to_id']\n",
    "list(usersMax)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3237d265",
   "metadata": {
    "papermill": {
     "duration": 0.005774,
     "end_time": "2024-02-20T07:00:33.049204",
     "exception": false,
     "start_time": "2024-02-20T07:00:33.043430",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Training Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85c83cb3",
   "metadata": {
    "papermill": {
     "duration": 0.005617,
     "end_time": "2024-02-20T07:00:33.061974",
     "exception": false,
     "start_time": "2024-02-20T07:00:33.056357",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "After choosing a specific user and item, we put those data into the model. We try three different models and compare the performance. The models are trained with 80% of the data and evaluated with the rest by the Mean Average Error and Mean Squared Error metrics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "bc216f77",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-20T07:00:33.075989Z",
     "iopub.status.busy": "2024-02-20T07:00:33.074957Z",
     "iopub.status.idle": "2024-02-20T07:00:36.124786Z",
     "shell.execute_reply": "2024-02-20T07:00:36.123795Z"
    },
    "papermill": {
     "duration": 3.059033,
     "end_time": "2024-02-20T07:00:36.126877",
     "exception": false,
     "start_time": "2024-02-20T07:00:33.067844",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EPM_35159    \n",
      "Linear Regression MAE: 2.84  MSE: 23.12  Random Forest MAE: 3.07  MSE: 23.62  XGBoost MAE: 3.23  MSE: 23.54 \n",
      "\n",
      "EPM_4334085  \n",
      "Linear Regression MAE: 1.91  MSE: 7.2    Random Forest MAE: 2.34  MSE: 13.09  XGBoost MAE: 2.31  MSE: 9.71  \n",
      "\n",
      "EPM_1807311  \n",
      "Linear Regression MAE: 2.65  MSE: 9.21   Random Forest MAE: 3.7   MSE: 28.12  XGBoost MAE: 3.83  MSE: 28.95 \n",
      "\n",
      "EPM_3564728  \n",
      "Linear Regression MAE: 4.36  MSE: 67.18  Random Forest MAE: 4.74  MSE: 51.96  XGBoost MAE: 4.35  MSE: 32.82 \n",
      "\n",
      "EPM_35002    \n",
      "Linear Regression MAE: 2.44  MSE: 9.15   Random Forest MAE: 2.94  MSE: 12.29  XGBoost MAE: 3.18  MSE: 17.92 \n",
      "\n",
      "EPM_34985    \n",
      "Linear Regression MAE: 5.33  MSE: 45.32  Random Forest MAE: 5.0   MSE: 46.68  XGBoost MAE: 6.1   MSE: 77.85 \n",
      "\n",
      "EPM_136080   \n",
      "Linear Regression MAE: 2.36  MSE: 9.62   Random Forest MAE: 3.79  MSE: 39.41  XGBoost MAE: 5.38  MSE: 78.8  \n",
      "\n",
      "EPM_1624002  \n",
      "Linear Regression MAE: 4.12  MSE: 23.38  Random Forest MAE: 3.61  MSE: 22.75  XGBoost MAE: 4.33  MSE: 34.78 \n",
      "\n",
      "EPM_3676050  \n",
      "Linear Regression MAE: 2.27  MSE: 10.93  Random Forest MAE: 2.73  MSE: 15.83  XGBoost MAE: 3.83  MSE: 48.13 \n",
      "\n",
      "EPM_34923    \n",
      "Linear Regression MAE: 4.96  MSE: 60.18  Random Forest MAE: 5.39  MSE: 75.4   XGBoost MAE: 5.49  MSE: 74.39 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfTrain = pd.DataFrame()\n",
    "dfTest = pd.DataFrame()\n",
    "\n",
    "for i in usersMax:\n",
    "\n",
    "    dfUser = df[(df['item_code'] == itemMax) & (df['ship_to_id'] == i)].sort_values('trx_date').reset_index(drop=True)\n",
    "    dfUser['period'] = dfUser['trx_date'].diff().apply(lambda x: x.days)[1:].reset_index(drop=True)\n",
    "    dfTrain, dfTest = dfUser[:-1], dfUser[-1:] # The last row doesnt have interval value for next purchase\n",
    "\n",
    "    dfTest = dfTest.drop(['period', 'ship_to_id', 'item_code', 'trx_date'], axis=1)\n",
    "    X = dfTrain.drop(['period', 'ship_to_id', 'item_code', 'trx_date'], axis=1)\n",
    "    y = dfTrain['period']\n",
    "    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)\n",
    "\n",
    "    # Linear Regression\n",
    "    modelLR = LinearRegression()\n",
    "    modelLR.fit(X_train, y_train)\n",
    "    y_pred = modelLR.predict(X_test)\n",
    "    maeLR = mean_absolute_error(y_test, y_pred)\n",
    "    mseLR = mean_squared_error(y_test, y_pred)\n",
    "\n",
    "    # Random Forest\n",
    "    modelRF = RandomForestRegressor(random_state=42)\n",
    "    modelRF.fit(X_train, y_train)\n",
    "    y_pred = modelRF.predict(X_test)\n",
    "    maeRF = mean_absolute_error(y_test, y_pred)\n",
    "    mseRF = mean_squared_error(y_test, y_pred)\n",
    "    \n",
    "    # XGBoost\n",
    "    modelXGB = XGBRegressor()\n",
    "    modelXGB.fit(X_train, y_train)\n",
    "    y_pred = modelXGB.predict(X_test)\n",
    "    maeXGB = mean_absolute_error(y_test, y_pred)\n",
    "    mseXGB = mean_squared_error(y_test, y_pred)\n",
    "\n",
    "    print(\"{:<12} \\nLinear Regression MAE: {:<5} MSE: {:<6} Random Forest MAE: {:<5} MSE: {:<6} XGBoost MAE: {:<5} MSE: {:<6}\\n\".format(i, round(maeLR, 2), round(mseLR, 2), round(maeRF, 2), round(mseRF, 2), round(maeXGB, 2), round(mseXGB, 2)))"
   ]
  }
 ],
 "metadata": {
  "kaggle": {
   "accelerator": "none",
   "dataSources": [
    {
     "datasetId": 3947402,
     "sourceId": 7173504,
     "sourceType": "datasetVersion"
    }
   ],
   "dockerImageVersionId": 30646,
   "isGpuEnabled": false,
   "isInternetEnabled": true,
   "language": "python",
   "sourceType": "notebook"
  },
  "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.10.13"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 13.274863,
   "end_time": "2024-02-20T07:00:36.753714",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2024-02-20T07:00:23.478851",
   "version": "2.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}