next-purchase-date-with-ml-routine-transactions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ff01c5f0",
   "metadata": {
    "papermill": {
     "duration": 0.0075,
     "end_time": "2024-02-19T09:09:41.460697",
     "exception": false,
     "start_time": "2024-02-19T09:09:41.453197",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Next Purchase Date with ML - Routine Transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8a31993",
   "metadata": {
    "papermill": {
     "duration": 0.007136,
     "end_time": "2024-02-19T09:09:41.475162",
     "exception": false,
     "start_time": "2024-02-19T09:09:41.468026",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "In this use case, we use machine learning approach to know the model's performance if trained with the routine transactions between the user and the item. We assume that routine transactions are when a user buys a certain item at least once a month. To avoid overfitting for the too-frequent transactions (e.g., buying an item every day), we choose two interactions of user and item with the biggest average interval of transactions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15b899d9",
   "metadata": {
    "papermill": {
     "duration": 0.006692,
     "end_time": "2024-02-19T09:09:41.489724",
     "exception": false,
     "start_time": "2024-02-19T09:09:41.483032",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Load and Preprocess Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69c83079",
   "metadata": {
    "papermill": {
     "duration": 0.006297,
     "end_time": "2024-02-19T09:09:41.502775",
     "exception": false,
     "start_time": "2024-02-19T09:09:41.496478",
     "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": "5f0670f4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:09:41.518954Z",
     "iopub.status.busy": "2024-02-19T09:09:41.517585Z",
     "iopub.status.idle": "2024-02-19T09:09:44.620500Z",
     "shell.execute_reply": "2024-02-19T09:09:44.619260Z"
    },
    "papermill": {
     "duration": 3.114177,
     "end_time": "2024-02-19T09:09:44.623576",
     "exception": false,
     "start_time": "2024-02-19T09:09:41.509399",
     "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": "8503095b",
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "execution": {
     "iopub.execute_input": "2024-02-19T09:09:44.639103Z",
     "iopub.status.busy": "2024-02-19T09:09:44.638522Z",
     "iopub.status.idle": "2024-02-19T09:09:46.713350Z",
     "shell.execute_reply": "2024-02-19T09:09:46.712023Z"
    },
    "papermill": {
     "duration": 2.085695,
     "end_time": "2024-02-19T09:09:46.716133",
     "exception": false,
     "start_time": "2024-02-19T09:09:44.630438",
     "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": "2b8506c0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:09:46.733039Z",
     "iopub.status.busy": "2024-02-19T09:09:46.732606Z",
     "iopub.status.idle": "2024-02-19T09:09:46.941391Z",
     "shell.execute_reply": "2024-02-19T09:09:46.940153Z"
    },
    "papermill": {
     "duration": 0.221062,
     "end_time": "2024-02-19T09:09:46.944700",
     "exception": false,
     "start_time": "2024-02-19T09:09:46.723638",
     "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": "73beffe5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:09:46.961707Z",
     "iopub.status.busy": "2024-02-19T09:09:46.961261Z",
     "iopub.status.idle": "2024-02-19T09:09:47.533335Z",
     "shell.execute_reply": "2024-02-19T09:09:47.532149Z"
    },
    "papermill": {
     "duration": 0.583444,
     "end_time": "2024-02-19T09:09:47.535989",
     "exception": false,
     "start_time": "2024-02-19T09:09:46.952545",
     "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": "87642ed1",
   "metadata": {
    "papermill": {
     "duration": 0.007391,
     "end_time": "2024-02-19T09:09:47.551108",
     "exception": false,
     "start_time": "2024-02-19T09:09:47.543717",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Preprocessing for Usecase"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40bb70bf",
   "metadata": {
    "papermill": {
     "duration": 0.00763,
     "end_time": "2024-02-19T09:09:47.566313",
     "exception": false,
     "start_time": "2024-02-19T09:09:47.558683",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "First, we filter the data to be in the range of 2022 and 2023 only. We assume that 2021 or before is still in the COVID period, so the data may not be accurate and useful for predicting the present and future conditions. Not all the features are used in this use case; we only focused on the sales quantity and timestamp column.\n",
    "\n",
    "In the process of searching the selected routine transactions, we do these steps:\n",
    "1. Choose the user that has transactions at least once every month.\n",
    "2. From the selected users, choose the item from each user that has transactions at least once every month.\n",
    "3. From the selected users and items, calculate the average timestamp interval between all consecutive transactions in each user-item interaction. (in a daily unit)\n",
    "4. Choose the user-item interactions with the biggest average interval. Why? Because we don't want to consider the data to be quite robust, as it will be too easy for the model to learn and predict it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d44ff767",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:09:47.586327Z",
     "iopub.status.busy": "2024-02-19T09:09:47.585934Z",
     "iopub.status.idle": "2024-02-19T09:09:47.673171Z",
     "shell.execute_reply": "2024-02-19T09:09:47.671891Z"
    },
    "papermill": {
     "duration": 0.10048,
     "end_time": "2024-02-19T09:09:47.676654",
     "exception": false,
     "start_time": "2024-02-19T09:09:47.576174",
     "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>2022-01-12</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>2022-01-19</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TPRGR</td>\n",
       "      <td>PROMAG TABLET</td>\n",
       "      <td>PT SAKAFARMA LABORATORIES (CHD)</td>\n",
       "      <td>71092</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-26</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TVIPA</td>\n",
       "      <td>VITAZYM PLUS 100 TABLET</td>\n",
       "      <td>KALBE NIMITZ (PHARMAMED)</td>\n",
       "      <td>75000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-25</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TALOD</td>\n",
       "      <td>ALLOPURINOL 300 MG 100 TAB</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>126000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-06</td>\n",
       "      <td>JK1-AP. DEVITA_GROUP_NA</td>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>JK1</td>\n",
       "      <td>TSPLB</td>\n",
       "      <td>SPASMINAL 100 TABLET</td>\n",
       "      <td>HEXPHARM (PHARMAMED)</td>\n",
       "      <td>80000</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    trx_date            customer_name ship_to_id branch_code item_code  \\\n",
       "0 2022-01-12  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     KCFMB   \n",
       "1 2022-01-19  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TPRGR   \n",
       "2 2022-01-26  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TVIPA   \n",
       "3 2022-01-25  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TALOD   \n",
       "4 2022-01-06  JK1-AP. DEVITA_GROUP_NA  EPM_34950         JK1     TSPLB   \n",
       "\n",
       "                    item_desc                   principal_desc  \\\n",
       "0    CEFIXIME 100MG 50 KAPSUL             HEXPHARM (PHARMAMED)   \n",
       "1               PROMAG TABLET  PT SAKAFARMA LABORATORIES (CHD)   \n",
       "2     VITAZYM PLUS 100 TABLET         KALBE NIMITZ (PHARMAMED)   \n",
       "3  ALLOPURINOL 300 MG 100 TAB             HEXPHARM (PHARMAMED)   \n",
       "4        SPASMINAL 100 TABLET             HEXPHARM (PHARMAMED)   \n",
       "\n",
       "   gross_sales_amount  sales_qty  \n",
       "0              195000          3  \n",
       "1               71092          1  \n",
       "2               75000          3  \n",
       "3              126000          2  \n",
       "4               80000          6  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get transactions after 2021\n",
    "dfDate = df[df['trx_date'] > '2021-12-31'].reset_index(drop=True)\n",
    "dfDate.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b24e3054",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:09:47.694467Z",
     "iopub.status.busy": "2024-02-19T09:09:47.694090Z",
     "iopub.status.idle": "2024-02-19T09:09:49.382457Z",
     "shell.execute_reply": "2024-02-19T09:09:49.381196Z"
    },
    "papermill": {
     "duration": 1.700421,
     "end_time": "2024-02-19T09:09:49.385111",
     "exception": false,
     "start_time": "2024-02-19T09:09:47.684690",
     "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>2022-01-12</td>\n",
       "      <td>3</td>\n",
       "      <td>1.641946e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TPRGR</td>\n",
       "      <td>2022-01-19</td>\n",
       "      <td>1</td>\n",
       "      <td>1.642550e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TVIPA</td>\n",
       "      <td>2022-01-26</td>\n",
       "      <td>3</td>\n",
       "      <td>1.643155e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TALOD</td>\n",
       "      <td>2022-01-25</td>\n",
       "      <td>2</td>\n",
       "      <td>1.643069e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EPM_34950</td>\n",
       "      <td>TSPLB</td>\n",
       "      <td>2022-01-06</td>\n",
       "      <td>6</td>\n",
       "      <td>1.641427e+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>312541</th>\n",
       "      <td>EPM_4554507</td>\n",
       "      <td>BLMBN</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.696032e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312542</th>\n",
       "      <td>EPM_4554507</td>\n",
       "      <td>BLCGR</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.696032e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312543</th>\n",
       "      <td>EPM_4554507</td>\n",
       "      <td>BLCVS</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>2</td>\n",
       "      <td>1.696032e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312544</th>\n",
       "      <td>EPM_4554507</td>\n",
       "      <td>BLCVC</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>4</td>\n",
       "      <td>1.696032e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312545</th>\n",
       "      <td>EPM_4554507</td>\n",
       "      <td>BLCBI</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>20</td>\n",
       "      <td>1.696032e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>312546 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         ship_to_id item_code    trx_date  sales_qty     timestamp\n",
       "0         EPM_34950     KCFMB  2022-01-12          3  1.641946e+09\n",
       "1         EPM_34950     TPRGR  2022-01-19          1  1.642550e+09\n",
       "2         EPM_34950     TVIPA  2022-01-26          3  1.643155e+09\n",
       "3         EPM_34950     TALOD  2022-01-25          2  1.643069e+09\n",
       "4         EPM_34950     TSPLB  2022-01-06          6  1.641427e+09\n",
       "...             ...       ...         ...        ...           ...\n",
       "312541  EPM_4554507     BLMBN  2023-09-30          1  1.696032e+09\n",
       "312542  EPM_4554507     BLCGR  2023-09-30          1  1.696032e+09\n",
       "312543  EPM_4554507     BLCVS  2023-09-30          2  1.696032e+09\n",
       "312544  EPM_4554507     BLCVC  2023-09-30          4  1.696032e+09\n",
       "312545  EPM_4554507     BLCBI  2023-09-30         20  1.696032e+09\n",
       "\n",
       "[312546 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create timestamp column\n",
    "dfDate['timestamp'] = dfDate['trx_date'].apply(lambda x: x.timestamp())\n",
    "dfDate['trx_date'] = dfDate['trx_date'].dt.strftime('%Y-%m-%d')\n",
    "dfDate = dfDate[['ship_to_id', 'item_code', 'trx_date', 'sales_qty', 'timestamp']]\n",
    "dfDate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "82b388d3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:09:49.405294Z",
     "iopub.status.busy": "2024-02-19T09:09:49.404893Z",
     "iopub.status.idle": "2024-02-19T09:10:27.915292Z",
     "shell.execute_reply": "2024-02-19T09:10:27.914215Z"
    },
    "papermill": {
     "duration": 38.531294,
     "end_time": "2024-02-19T09:10:27.925942",
     "exception": false,
     "start_time": "2024-02-19T09:09:49.394648",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Total Users: 214'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if user has transactions in every month\n",
    "users = dfDate['ship_to_id'].unique()\n",
    "userAv = []\n",
    "for u in users:\n",
    "    dfUser = dfDate[dfDate['ship_to_id'] == u]\n",
    "    ada = True\n",
    "    for i in range(1, 22):\n",
    "        if i <= 12:\n",
    "            ln = len(dfUser[(dfUser['trx_date'] >= f'2022-{str(i).zfill(2)}-01') & \n",
    "                            (dfUser['trx_date'] <= f'2022-{str(i).zfill(2)}-31')])\n",
    "        else:\n",
    "            ln = len(dfUser[(dfUser['trx_date'] >= f'2023-{str(i - 12).zfill(2)}-01') & \n",
    "                            (dfUser['trx_date'] <= f'2023-{str(i - 12).zfill(2)}-31')])\n",
    "        if ln == 0:\n",
    "            ada = False\n",
    "    if ada:\n",
    "        userAv.append(u)\n",
    "    \n",
    "f\"Total Users: {len(userAv)}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "18637d54",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:10:27.945059Z",
     "iopub.status.busy": "2024-02-19T09:10:27.944657Z",
     "iopub.status.idle": "2024-02-19T09:18:58.036661Z",
     "shell.execute_reply": "2024-02-19T09:18:58.035516Z"
    },
    "papermill": {
     "duration": 510.113108,
     "end_time": "2024-02-19T09:18:58.047511",
     "exception": false,
     "start_time": "2024-02-19T09:10:27.934403",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Total Pairs of User and Item: 255'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if user and item has transactions in every month\n",
    "freqs = []\n",
    "for u in userAv:\n",
    "    dfUser = dfDate[dfDate['ship_to_id'] == u]\n",
    "    items = dfUser['item_code'].unique()\n",
    "    for it in items:\n",
    "        dfUserItem = dfUser[dfUser['item_code'] == it]\n",
    "        ada = True\n",
    "        for i in range(1, 22):\n",
    "            if i <= 12:\n",
    "                ln = len(dfUserItem[(dfUserItem['trx_date'] >= f'2022-{str(i).zfill(2)}-01') & \n",
    "                                    (dfUserItem['trx_date'] <= f'2022-{str(i).zfill(2)}-31')])\n",
    "            else:\n",
    "                ln = len(dfUserItem[(dfUserItem['trx_date'] >= f'2023-{str(i - 12).zfill(2)}-01') & \n",
    "                                    (dfUserItem['trx_date'] <= f'2023-{str(i - 12).zfill(2)}-31')])\n",
    "            if ln == 0:\n",
    "                ada = False\n",
    "        if ada:\n",
    "            freqs.append((u, it))\n",
    "\n",
    "f\"Total Pairs of User and Item: {len(freqs)}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "df21d71a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:18:58.066427Z",
     "iopub.status.busy": "2024-02-19T09:18:58.066055Z",
     "iopub.status.idle": "2024-02-19T09:18:58.074103Z",
     "shell.execute_reply": "2024-02-19T09:18:58.072878Z"
    },
    "papermill": {
     "duration": 0.020506,
     "end_time": "2024-02-19T09:18:58.076547",
     "exception": false,
     "start_time": "2024-02-19T09:18:58.056041",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('EPM_34971', 'TMPSD'),\n",
       " ('EPM_34971', 'TALNE'),\n",
       " ('EPM_34971', 'KLSPB'),\n",
       " ('EPM_34972', 'TALNF'),\n",
       " ('EPM_34972', 'TMFNB')]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pair example\n",
    "freqs[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2a554c59",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:18:58.096262Z",
     "iopub.status.busy": "2024-02-19T09:18:58.095824Z",
     "iopub.status.idle": "2024-02-19T09:19:27.837411Z",
     "shell.execute_reply": "2024-02-19T09:19:27.836255Z"
    },
    "papermill": {
     "duration": 29.754507,
     "end_time": "2024-02-19T09:19:27.839954",
     "exception": false,
     "start_time": "2024-02-19T09:18:58.085447",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('EPM_136080', 'TRGCA')\n",
      "('EPM_3041843', 'CKPXB')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[20.233333333333334,\n",
       " 21.0,\n",
       " 21.0,\n",
       " 21.392857142857142,\n",
       " 21.928571428571427,\n",
       " 22.0,\n",
       " 22.653846153846153,\n",
       " 23.615384615384617,\n",
       " 24.0,\n",
       " 24.833333333333332]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check average interval transactions, and choose the biggest one\n",
    "means = []\n",
    "dfDate['trx_date'] = pd.to_datetime(dfDate['trx_date'])\n",
    "for f in freqs:\n",
    "    dfFreq = dfDate[(dfDate['ship_to_id'] == f[0]) & (dfDate['item_code'] == f[1])].sort_values(by='trx_date') \\\n",
    "                                                                                   .reset_index(drop=True)\n",
    "\n",
    "    dfFreq['period'] = dfFreq['trx_date'].diff() \\\n",
    "                                         .apply(lambda x: x.days)[1:] \\\n",
    "                                         .reset_index(drop=True) \n",
    "    means.append(dfFreq['period'].describe()['mean'])\n",
    "\n",
    "    if means[-1] >= 24:\n",
    "        print(f)\n",
    "\n",
    "sorted(means)[-10:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7b1077ae",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:19:27.861512Z",
     "iopub.status.busy": "2024-02-19T09:19:27.860238Z",
     "iopub.status.idle": "2024-02-19T09:19:28.111164Z",
     "shell.execute_reply": "2024-02-19T09:19:28.109984Z"
    },
    "papermill": {
     "duration": 0.26474,
     "end_time": "2024-02-19T09:19:28.114020",
     "exception": false,
     "start_time": "2024-02-19T09:19:27.849280",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('EPM_136080', 'TRGCA')\n",
      "    ship_to_id item_code   trx_date  sales_qty     timestamp  period\n",
      "0   EPM_136080     TRGCA 2022-01-05          4  1.641341e+09     5.0\n",
      "1   EPM_136080     TRGCA 2022-01-10          3  1.641773e+09    31.0\n",
      "2   EPM_136080     TRGCA 2022-02-10          1  1.644451e+09    40.0\n",
      "3   EPM_136080     TRGCA 2022-03-22          5  1.647907e+09    28.0\n",
      "4   EPM_136080     TRGCA 2022-04-19          2  1.650326e+09    10.0\n",
      "5   EPM_136080     TRGCA 2022-04-29         10  1.651190e+09     8.0\n",
      "6   EPM_136080     TRGCA 2022-05-07          2  1.651882e+09    17.0\n",
      "7   EPM_136080     TRGCA 2022-05-24          1  1.653350e+09    28.0\n",
      "8   EPM_136080     TRGCA 2022-06-21          2  1.655770e+09    30.0\n",
      "9   EPM_136080     TRGCA 2022-07-21          4  1.658362e+09    18.0\n",
      "10  EPM_136080     TRGCA 2022-08-08          6  1.659917e+09    28.0\n",
      "11  EPM_136080     TRGCA 2022-09-05          3  1.662336e+09     3.0\n",
      "12  EPM_136080     TRGCA 2022-09-08         10  1.662595e+09    41.0\n",
      "13  EPM_136080     TRGCA 2022-10-19          1  1.666138e+09    17.0\n",
      "14  EPM_136080     TRGCA 2022-11-05          2  1.667606e+09    38.0\n",
      "15  EPM_136080     TRGCA 2022-12-13          1  1.670890e+09    36.0\n",
      "16  EPM_136080     TRGCA 2023-01-18          2  1.674000e+09    26.0\n",
      "17  EPM_136080     TRGCA 2023-02-13          2  1.676246e+09     4.0\n",
      "18  EPM_136080     TRGCA 2023-02-17         10  1.676592e+09    28.0\n",
      "19  EPM_136080     TRGCA 2023-03-17          3  1.679011e+09    31.0\n",
      "20  EPM_136080     TRGCA 2023-04-17          3  1.681690e+09    23.0\n",
      "21  EPM_136080     TRGCA 2023-05-10         10  1.683677e+09    33.0\n",
      "22  EPM_136080     TRGCA 2023-06-12          3  1.686528e+09    32.0\n",
      "23  EPM_136080     TRGCA 2023-07-14         10  1.689293e+09    35.0\n",
      "24  EPM_136080     TRGCA 2023-08-18        100  1.692317e+09    26.0\n",
      "25  EPM_136080     TRGCA 2023-09-13          2  1.694563e+09     8.0\n",
      "-----------------------------\n",
      "('EPM_3041843', 'CKPXB')\n",
      "     ship_to_id item_code   trx_date  sales_qty     timestamp  period\n",
      "0   EPM_3041843     CKPXB 2022-01-24          1  1.642982e+09    18.0\n",
      "1   EPM_3041843     CKPXB 2022-02-11          6  1.644538e+09     5.0\n",
      "2   EPM_3041843     CKPXB 2022-02-16          5  1.644970e+09    33.0\n",
      "3   EPM_3041843     CKPXB 2022-03-21         60  1.647821e+09    22.0\n",
      "4   EPM_3041843     CKPXB 2022-04-12          7  1.649722e+09    46.0\n",
      "5   EPM_3041843     CKPXB 2022-05-28          1  1.653696e+09    21.0\n",
      "6   EPM_3041843     CKPXB 2022-06-18          1  1.655510e+09    37.0\n",
      "7   EPM_3041843     CKPXB 2022-07-25          2  1.658707e+09    25.0\n",
      "8   EPM_3041843     CKPXB 2022-08-19          1  1.660867e+09    33.0\n",
      "9   EPM_3041843     CKPXB 2022-09-21          1  1.663718e+09    21.0\n",
      "10  EPM_3041843     CKPXB 2022-10-12          1  1.665533e+09    31.0\n",
      "11  EPM_3041843     CKPXB 2022-11-12          2  1.668211e+09    24.0\n",
      "12  EPM_3041843     CKPXB 2022-12-06          6  1.670285e+09    37.0\n",
      "13  EPM_3041843     CKPXB 2023-01-12          2  1.673482e+09    26.0\n",
      "14  EPM_3041843     CKPXB 2023-02-07          1  1.675728e+09    15.0\n",
      "15  EPM_3041843     CKPXB 2023-02-22          3  1.677024e+09    17.0\n",
      "16  EPM_3041843     CKPXB 2023-03-11          2  1.678493e+09    17.0\n",
      "17  EPM_3041843     CKPXB 2023-03-28          4  1.679962e+09    20.0\n",
      "18  EPM_3041843     CKPXB 2023-04-17         10  1.681690e+09    15.0\n",
      "19  EPM_3041843     CKPXB 2023-05-02          4  1.682986e+09    27.0\n",
      "20  EPM_3041843     CKPXB 2023-05-29          2  1.685318e+09    17.0\n",
      "21  EPM_3041843     CKPXB 2023-06-15          2  1.686787e+09    36.0\n",
      "22  EPM_3041843     CKPXB 2023-07-21          1  1.689898e+09    22.0\n",
      "23  EPM_3041843     CKPXB 2023-08-12          2  1.691798e+09    31.0\n",
      "-----------------------------\n"
     ]
    }
   ],
   "source": [
    "# Show sample data\n",
    "lst = [('EPM_136080', 'TRGCA'), ('EPM_3041843', 'CKPXB')]\n",
    "for f in lst:\n",
    "    dfShow = dfDate[(dfDate['item_code'] == f[1]) & (dfDate['ship_to_id'] == f[0])].sort_values('trx_date').reset_index(drop=True)\n",
    "    dfShow['period'] = dfShow['trx_date'].diff().apply(lambda x: x.days)[1:].reset_index(drop=True)\n",
    "    dfTrain, dfTest = dfShow[:-1], dfShow[-1:]\n",
    "    print(f)\n",
    "    print(dfTrain)\n",
    "    print('-----------------------------')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b797b688",
   "metadata": {
    "papermill": {
     "duration": 0.009542,
     "end_time": "2024-02-19T09:19:28.133035",
     "exception": false,
     "start_time": "2024-02-19T09:19:28.123493",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Training Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f752f75b",
   "metadata": {
    "papermill": {
     "duration": 0.008976,
     "end_time": "2024-02-19T09:19:28.151385",
     "exception": false,
     "start_time": "2024-02-19T09:19:28.142409",
     "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": 12,
   "id": "a9c0ca51",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-02-19T09:19:28.172380Z",
     "iopub.status.busy": "2024-02-19T09:19:28.171596Z",
     "iopub.status.idle": "2024-02-19T09:19:28.961308Z",
     "shell.execute_reply": "2024-02-19T09:19:28.960216Z"
    },
    "papermill": {
     "duration": 0.803635,
     "end_time": "2024-02-19T09:19:28.964346",
     "exception": false,
     "start_time": "2024-02-19T09:19:28.160711",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EPM_136080   TRGCA    \n",
      "Linear Regression MAE: 19.31 MSE: 717.22 \n",
      "Random Forest     MAE: 14.73 MSE: 301.46 \n",
      "XGBoost           MAE: 15.01 MSE: 284.1 \n",
      "\n",
      "EPM_3041843  CKPXB    \n",
      "Linear Regression MAE: 7.23  MSE: 60.38  \n",
      "Random Forest     MAE: 7.75  MSE: 102.05 \n",
      "XGBoost           MAE: 11.51 MSE: 242.78\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for f in lst:\n",
    "    \n",
    "    dfShow = dfDate[(dfDate['item_code'] == f[1]) & (dfDate['ship_to_id'] == f[0])].sort_values('trx_date').reset_index(drop=True)\n",
    "    dfShow['period'] = dfShow['trx_date'].diff().apply(lambda x: x.days)[1:].reset_index(drop=True)\n",
    "    dfTrain, dfTest = dfShow[:-1], dfShow[-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} {:<8} \\nLinear Regression MAE: {:<5} MSE: {:<6} \\nRandom Forest     MAE: {:<5} MSE: {:<6} \\nXGBoost           MAE: {:<5} MSE: {:<6}\\n\".format(f[0], f[1], 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": 30635,
   "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.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 592.871136,
   "end_time": "2024-02-19T09:19:29.701553",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2024-02-19T09:09:36.830417",
   "version": "2.4.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}