CLV_prediction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "72b97296",
   "metadata": {},
   "source": [
    "# Customer lifetime value prediction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a45ca06",
   "metadata": {},
   "source": [
    "Customer lifetime value (CLV):\n",
    "- indicates the total revenue from the customer during the entire relationship\n",
    "- helps companies to focus on those potential customers who can bring in the more revenue in the future\n",
    "- demonstrates the implications of acquiring long-term customers compare to short-term customers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49025d14",
   "metadata": {},
   "source": [
    "## Data preparation\n",
    "\n",
    "Using the data from EDA and Segmentation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "62959eb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import plotly.express as px\n",
    "import plotly.io as pio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "45686878",
   "metadata": {},
   "outputs": [],
   "source": [
    "import statsmodels.api as st\n",
    "from sklearn import preprocessing\n",
    "from sklearn.metrics import classification_report,confusion_matrix, mean_squared_error\n",
    "from sklearn.model_selection import KFold, cross_val_score, train_test_split\n",
    "\n",
    "from sklearn.svm import SVC, SVR\n",
    "from sklearn.multioutput import MultiOutputClassifier\n",
    "from sklearn.ensemble import GradientBoostingClassifier\n",
    "from sklearn.tree import DecisionTreeRegressor, DecisionTreeClassifier\n",
    "from sklearn.neighbors import KNeighborsRegressor, KNeighborsClassifier\n",
    "from sklearn.naive_bayes import GaussianNB\n",
    "from sklearn.ensemble import RandomForestRegressor, RandomForestClassifier\n",
    "from sklearn.linear_model import LinearRegression, LogisticRegression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bea4f1bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>weekday</th>\n",
       "      <th>...</th>\n",
       "      <th>payment_value</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>customer_zip_code_prefix</th>\n",
       "      <th>customer_city</th>\n",
       "      <th>customer_state</th>\n",
       "      <th>first_order_date</th>\n",
       "      <th>usertype</th>\n",
       "      <th>no_of_items</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>9ef432eb6251297304e76186b10a928d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>2017-10-02 11:07:15</td>\n",
       "      <td>2017-10-04 19:55:00</td>\n",
       "      <td>2017-10-10 21:25:13</td>\n",
       "      <td>2017-10-18 00:00:00</td>\n",
       "      <td>Mon</td>\n",
       "      <td>...</td>\n",
       "      <td>38.71</td>\n",
       "      <td>7c396fd4830fd04220f754e42b4e5bff</td>\n",
       "      <td>3149</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>2017-09-04 11:26:38</td>\n",
       "      <td>Return</td>\n",
       "      <td>1.0</td>\n",
       "      <td>29.99</td>\n",
       "      <td>8.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>b0830fb4747a6c6d20dea0b8c802d7ef</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>2018-07-26 03:24:27</td>\n",
       "      <td>2018-07-26 14:31:00</td>\n",
       "      <td>2018-08-07 15:27:45</td>\n",
       "      <td>2018-08-13 00:00:00</td>\n",
       "      <td>Tue</td>\n",
       "      <td>...</td>\n",
       "      <td>141.46</td>\n",
       "      <td>af07308b275d755c9edb36a90c618231</td>\n",
       "      <td>47813</td>\n",
       "      <td>barreiras</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>118.70</td>\n",
       "      <td>22.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>41ce2a54c0b03bf3443c3d931a367089</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-08 08:38:49</td>\n",
       "      <td>2018-08-08 08:55:23</td>\n",
       "      <td>2018-08-08 13:50:00</td>\n",
       "      <td>2018-08-17 18:06:29</td>\n",
       "      <td>2018-09-04 00:00:00</td>\n",
       "      <td>Wed</td>\n",
       "      <td>...</td>\n",
       "      <td>179.12</td>\n",
       "      <td>3a653a41f6f9fc3d2a113cf8398680e8</td>\n",
       "      <td>75265</td>\n",
       "      <td>vianopolis</td>\n",
       "      <td>Goiás</td>\n",
       "      <td>2018-08-08 08:38:49</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>159.90</td>\n",
       "      <td>19.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>f88197465ea7920adcdbec7375364d82</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-18 19:28:06</td>\n",
       "      <td>2017-11-18 19:45:59</td>\n",
       "      <td>2017-11-22 13:39:59</td>\n",
       "      <td>2017-12-02 00:28:42</td>\n",
       "      <td>2017-12-15 00:00:00</td>\n",
       "      <td>Sat</td>\n",
       "      <td>...</td>\n",
       "      <td>72.20</td>\n",
       "      <td>7c142cf63193a1473d2e66489a9ae977</td>\n",
       "      <td>59296</td>\n",
       "      <td>sao goncalo do amarante</td>\n",
       "      <td>Rio Grande do Norte</td>\n",
       "      <td>2017-11-18 19:28:06</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>27.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ad21c59c0840e6cb83a9ceb5573f8159</td>\n",
       "      <td>8ab97904e6daea8866dbdbc4fb7aad2c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-13 21:18:39</td>\n",
       "      <td>2018-02-13 22:20:29</td>\n",
       "      <td>2018-02-14 19:46:34</td>\n",
       "      <td>2018-02-16 18:17:02</td>\n",
       "      <td>2018-02-26 00:00:00</td>\n",
       "      <td>Tue</td>\n",
       "      <td>...</td>\n",
       "      <td>28.62</td>\n",
       "      <td>72632f0f9dd73dfee390c9b22eb56dd6</td>\n",
       "      <td>9195</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>2018-02-13 21:18:39</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.90</td>\n",
       "      <td>8.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99086</th>\n",
       "      <td>99086</td>\n",
       "      <td>9c5dedf39a927c1b2549525ed64a053c</td>\n",
       "      <td>39bd1228ee8140590ac3aca26f2dfe00</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>2017-03-10 11:18:03</td>\n",
       "      <td>2017-03-17 15:08:01</td>\n",
       "      <td>2017-03-28 00:00:00</td>\n",
       "      <td>Thu</td>\n",
       "      <td>...</td>\n",
       "      <td>85.08</td>\n",
       "      <td>6359f309b166b0196dbf7ad2ac62bb5a</td>\n",
       "      <td>12209</td>\n",
       "      <td>sao jose dos campos</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>2017-03-09 09:54:05</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>72.00</td>\n",
       "      <td>13.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99087</th>\n",
       "      <td>99087</td>\n",
       "      <td>63943bddc261676b46f01ca7ac2f7bd8</td>\n",
       "      <td>1fca14ff2861355f6e5f14306ff977a7</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-06 12:58:58</td>\n",
       "      <td>2018-02-06 13:10:37</td>\n",
       "      <td>2018-02-07 23:22:42</td>\n",
       "      <td>2018-02-28 17:37:56</td>\n",
       "      <td>2018-03-02 00:00:00</td>\n",
       "      <td>Tue</td>\n",
       "      <td>...</td>\n",
       "      <td>195.00</td>\n",
       "      <td>da62f9e57a76d978d02ab5362c509660</td>\n",
       "      <td>11722</td>\n",
       "      <td>praia grande</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>2018-02-06 12:58:58</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>174.90</td>\n",
       "      <td>20.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99088</th>\n",
       "      <td>99088</td>\n",
       "      <td>83c1379a015df1e13d02aae0204711ab</td>\n",
       "      <td>1aa71eb042121263aafbe80c1b562c9c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-08-27 14:46:43</td>\n",
       "      <td>2017-08-27 15:04:16</td>\n",
       "      <td>2017-08-28 20:52:26</td>\n",
       "      <td>2017-09-21 11:24:17</td>\n",
       "      <td>2017-09-27 00:00:00</td>\n",
       "      <td>Sun</td>\n",
       "      <td>...</td>\n",
       "      <td>271.01</td>\n",
       "      <td>737520a9aad80b3fbbdad19b66b37b30</td>\n",
       "      <td>45920</td>\n",
       "      <td>nova vicosa</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>2017-08-27 14:46:43</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>205.99</td>\n",
       "      <td>65.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99089</th>\n",
       "      <td>99089</td>\n",
       "      <td>11c177c8e97725db2631073c19f07b62</td>\n",
       "      <td>b331b74b18dc79bcdf6532d51e1637c1</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-01-08 21:28:27</td>\n",
       "      <td>2018-01-08 21:36:21</td>\n",
       "      <td>2018-01-12 15:35:03</td>\n",
       "      <td>2018-01-25 23:32:54</td>\n",
       "      <td>2018-02-15 00:00:00</td>\n",
       "      <td>Mon</td>\n",
       "      <td>...</td>\n",
       "      <td>441.16</td>\n",
       "      <td>5097a5312c8b157bb7be58ae360ef43c</td>\n",
       "      <td>28685</td>\n",
       "      <td>japuiba</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>2018-01-08 21:28:27</td>\n",
       "      <td>New</td>\n",
       "      <td>2.0</td>\n",
       "      <td>359.98</td>\n",
       "      <td>81.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99090</th>\n",
       "      <td>99090</td>\n",
       "      <td>66dea50a8b16d9b4dee7af250b4be1a5</td>\n",
       "      <td>edb027a75a1449115f6b43211ae02a24</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-03-08 20:57:30</td>\n",
       "      <td>2018-03-09 11:20:28</td>\n",
       "      <td>2018-03-09 22:11:59</td>\n",
       "      <td>2018-03-16 13:08:30</td>\n",
       "      <td>2018-04-03 00:00:00</td>\n",
       "      <td>Thu</td>\n",
       "      <td>...</td>\n",
       "      <td>86.86</td>\n",
       "      <td>60350aa974b26ff12caad89e55993bd6</td>\n",
       "      <td>83750</td>\n",
       "      <td>lapa</td>\n",
       "      <td>Paraná</td>\n",
       "      <td>2018-03-08 20:57:30</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>68.50</td>\n",
       "      <td>18.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99091 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0                          order_id  \\\n",
       "0               0  e481f51cbdc54678b7cc49136f2d6af7   \n",
       "1               1  53cdb2fc8bc7dce0b6741e2150273451   \n",
       "2               2  47770eb9100c2d0c44946d9cf07ec65d   \n",
       "3               3  949d5b44dbf5de918fe9c16f97b45f8a   \n",
       "4               4  ad21c59c0840e6cb83a9ceb5573f8159   \n",
       "...           ...                               ...   \n",
       "99086       99086  9c5dedf39a927c1b2549525ed64a053c   \n",
       "99087       99087  63943bddc261676b46f01ca7ac2f7bd8   \n",
       "99088       99088  83c1379a015df1e13d02aae0204711ab   \n",
       "99089       99089  11c177c8e97725db2631073c19f07b62   \n",
       "99090       99090  66dea50a8b16d9b4dee7af250b4be1a5   \n",
       "\n",
       "                            customer_id order_status order_purchase_timestamp  \\\n",
       "0      9ef432eb6251297304e76186b10a928d    delivered      2017-10-02 10:56:33   \n",
       "1      b0830fb4747a6c6d20dea0b8c802d7ef    delivered      2018-07-24 20:41:37   \n",
       "2      41ce2a54c0b03bf3443c3d931a367089    delivered      2018-08-08 08:38:49   \n",
       "3      f88197465ea7920adcdbec7375364d82    delivered      2017-11-18 19:28:06   \n",
       "4      8ab97904e6daea8866dbdbc4fb7aad2c    delivered      2018-02-13 21:18:39   \n",
       "...                                 ...          ...                      ...   \n",
       "99086  39bd1228ee8140590ac3aca26f2dfe00    delivered      2017-03-09 09:54:05   \n",
       "99087  1fca14ff2861355f6e5f14306ff977a7    delivered      2018-02-06 12:58:58   \n",
       "99088  1aa71eb042121263aafbe80c1b562c9c    delivered      2017-08-27 14:46:43   \n",
       "99089  b331b74b18dc79bcdf6532d51e1637c1    delivered      2018-01-08 21:28:27   \n",
       "99090  edb027a75a1449115f6b43211ae02a24    delivered      2018-03-08 20:57:30   \n",
       "\n",
       "         order_approved_at order_delivered_carrier_date  \\\n",
       "0      2017-10-02 11:07:15          2017-10-04 19:55:00   \n",
       "1      2018-07-26 03:24:27          2018-07-26 14:31:00   \n",
       "2      2018-08-08 08:55:23          2018-08-08 13:50:00   \n",
       "3      2017-11-18 19:45:59          2017-11-22 13:39:59   \n",
       "4      2018-02-13 22:20:29          2018-02-14 19:46:34   \n",
       "...                    ...                          ...   \n",
       "99086  2017-03-09 09:54:05          2017-03-10 11:18:03   \n",
       "99087  2018-02-06 13:10:37          2018-02-07 23:22:42   \n",
       "99088  2017-08-27 15:04:16          2017-08-28 20:52:26   \n",
       "99089  2018-01-08 21:36:21          2018-01-12 15:35:03   \n",
       "99090  2018-03-09 11:20:28          2018-03-09 22:11:59   \n",
       "\n",
       "      order_delivered_customer_date order_estimated_delivery_date weekday  \\\n",
       "0               2017-10-10 21:25:13           2017-10-18 00:00:00     Mon   \n",
       "1               2018-08-07 15:27:45           2018-08-13 00:00:00     Tue   \n",
       "2               2018-08-17 18:06:29           2018-09-04 00:00:00     Wed   \n",
       "3               2017-12-02 00:28:42           2017-12-15 00:00:00     Sat   \n",
       "4               2018-02-16 18:17:02           2018-02-26 00:00:00     Tue   \n",
       "...                             ...                           ...     ...   \n",
       "99086           2017-03-17 15:08:01           2017-03-28 00:00:00     Thu   \n",
       "99087           2018-02-28 17:37:56           2018-03-02 00:00:00     Tue   \n",
       "99088           2017-09-21 11:24:17           2017-09-27 00:00:00     Sun   \n",
       "99089           2018-01-25 23:32:54           2018-02-15 00:00:00     Mon   \n",
       "99090           2018-03-16 13:08:30           2018-04-03 00:00:00     Thu   \n",
       "\n",
       "       ...  payment_value                customer_unique_id  \\\n",
       "0      ...          38.71  7c396fd4830fd04220f754e42b4e5bff   \n",
       "1      ...         141.46  af07308b275d755c9edb36a90c618231   \n",
       "2      ...         179.12  3a653a41f6f9fc3d2a113cf8398680e8   \n",
       "3      ...          72.20  7c142cf63193a1473d2e66489a9ae977   \n",
       "4      ...          28.62  72632f0f9dd73dfee390c9b22eb56dd6   \n",
       "...    ...            ...                               ...   \n",
       "99086  ...          85.08  6359f309b166b0196dbf7ad2ac62bb5a   \n",
       "99087  ...         195.00  da62f9e57a76d978d02ab5362c509660   \n",
       "99088  ...         271.01  737520a9aad80b3fbbdad19b66b37b30   \n",
       "99089  ...         441.16  5097a5312c8b157bb7be58ae360ef43c   \n",
       "99090  ...          86.86  60350aa974b26ff12caad89e55993bd6   \n",
       "\n",
       "      customer_zip_code_prefix            customer_city       customer_state  \\\n",
       "0                         3149                sao paulo            São Paulo   \n",
       "1                        47813                barreiras                Bahia   \n",
       "2                        75265               vianopolis                Goiás   \n",
       "3                        59296  sao goncalo do amarante  Rio Grande do Norte   \n",
       "4                         9195              santo andre            São Paulo   \n",
       "...                        ...                      ...                  ...   \n",
       "99086                    12209      sao jose dos campos            São Paulo   \n",
       "99087                    11722             praia grande            São Paulo   \n",
       "99088                    45920              nova vicosa                Bahia   \n",
       "99089                    28685                  japuiba       Rio de Janeiro   \n",
       "99090                    83750                     lapa               Paraná   \n",
       "\n",
       "          first_order_date usertype no_of_items   price  freight_value  \n",
       "0      2017-09-04 11:26:38   Return         1.0   29.99           8.72  \n",
       "1      2018-07-24 20:41:37      New         1.0  118.70          22.76  \n",
       "2      2018-08-08 08:38:49      New         1.0  159.90          19.22  \n",
       "3      2017-11-18 19:28:06      New         1.0   45.00          27.20  \n",
       "4      2018-02-13 21:18:39      New         1.0   19.90           8.72  \n",
       "...                    ...      ...         ...     ...            ...  \n",
       "99086  2017-03-09 09:54:05      New         1.0   72.00          13.08  \n",
       "99087  2018-02-06 12:58:58      New         1.0  174.90          20.10  \n",
       "99088  2017-08-27 14:46:43      New         1.0  205.99          65.02  \n",
       "99089  2018-01-08 21:28:27      New         2.0  359.98          81.18  \n",
       "99090  2018-03-08 20:57:30      New         1.0   68.50          18.36  \n",
       "\n",
       "[99091 rows x 21 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = pd.read_csv(\"combined_data.csv\")\n",
    "df_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2398d431",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all[\"order_purchase_timestamp\"] = pd.to_datetime(df_all[\"order_purchase_timestamp\"])\n",
    "df_all[\"order_approved_at\"] = pd.to_datetime(df_all[\"order_approved_at\"])\n",
    "df_all[\"order_delivered_carrier_date\"] = pd.to_datetime(df_all[\"order_delivered_carrier_date\"])\n",
    "df_all[\"order_delivered_customer_date\"] = pd.to_datetime(df_all[\"order_delivered_customer_date\"])\n",
    "df_all[\"order_estimated_delivery_date\"] = pd.to_datetime(df_all[\"order_estimated_delivery_date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4af80c9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 99091 entries, 0 to 99090\n",
      "Data columns (total 21 columns):\n",
      " #   Column                         Non-Null Count  Dtype         \n",
      "---  ------                         --------------  -----         \n",
      " 0   Unnamed: 0                     99091 non-null  int64         \n",
      " 1   order_id                       99091 non-null  object        \n",
      " 2   customer_id                    99091 non-null  object        \n",
      " 3   order_status                   99091 non-null  object        \n",
      " 4   order_purchase_timestamp       99091 non-null  datetime64[ns]\n",
      " 5   order_approved_at              98957 non-null  datetime64[ns]\n",
      " 6   order_delivered_carrier_date   97376 non-null  datetime64[ns]\n",
      " 7   order_delivered_customer_date  96204 non-null  datetime64[ns]\n",
      " 8   order_estimated_delivery_date  99091 non-null  datetime64[ns]\n",
      " 9   weekday                        99091 non-null  object        \n",
      " 10  hour                           99091 non-null  int64         \n",
      " 11  payment_value                  99091 non-null  float64       \n",
      " 12  customer_unique_id             99091 non-null  object        \n",
      " 13  customer_zip_code_prefix       99091 non-null  int64         \n",
      " 14  customer_city                  99091 non-null  object        \n",
      " 15  customer_state                 99091 non-null  object        \n",
      " 16  first_order_date               99091 non-null  object        \n",
      " 17  usertype                       99091 non-null  object        \n",
      " 18  no_of_items                    98353 non-null  float64       \n",
      " 19  price                          98353 non-null  float64       \n",
      " 20  freight_value                  98353 non-null  float64       \n",
      "dtypes: datetime64[ns](5), float64(4), int64(3), object(9)\n",
      "memory usage: 15.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b105067",
   "metadata": {},
   "source": [
    "Calculate revenue from each customer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f4e3be16",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_status</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>order_approved_at</th>\n",
       "      <th>order_delivered_carrier_date</th>\n",
       "      <th>order_delivered_customer_date</th>\n",
       "      <th>order_estimated_delivery_date</th>\n",
       "      <th>weekday</th>\n",
       "      <th>...</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>customer_zip_code_prefix</th>\n",
       "      <th>customer_city</th>\n",
       "      <th>customer_state</th>\n",
       "      <th>first_order_date</th>\n",
       "      <th>usertype</th>\n",
       "      <th>no_of_items</th>\n",
       "      <th>price</th>\n",
       "      <th>freight_value</th>\n",
       "      <th>yr_mon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>e481f51cbdc54678b7cc49136f2d6af7</td>\n",
       "      <td>9ef432eb6251297304e76186b10a928d</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-10-02 10:56:33</td>\n",
       "      <td>2017-10-02 11:07:15</td>\n",
       "      <td>2017-10-04 19:55:00</td>\n",
       "      <td>2017-10-10 21:25:13</td>\n",
       "      <td>2017-10-18</td>\n",
       "      <td>Mon</td>\n",
       "      <td>...</td>\n",
       "      <td>7c396fd4830fd04220f754e42b4e5bff</td>\n",
       "      <td>3149</td>\n",
       "      <td>sao paulo</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>2017-09-04 11:26:38</td>\n",
       "      <td>Return</td>\n",
       "      <td>1.0</td>\n",
       "      <td>29.99</td>\n",
       "      <td>8.72</td>\n",
       "      <td>2017-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>53cdb2fc8bc7dce0b6741e2150273451</td>\n",
       "      <td>b0830fb4747a6c6d20dea0b8c802d7ef</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>2018-07-26 03:24:27</td>\n",
       "      <td>2018-07-26 14:31:00</td>\n",
       "      <td>2018-08-07 15:27:45</td>\n",
       "      <td>2018-08-13</td>\n",
       "      <td>Tue</td>\n",
       "      <td>...</td>\n",
       "      <td>af07308b275d755c9edb36a90c618231</td>\n",
       "      <td>47813</td>\n",
       "      <td>barreiras</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>2018-07-24 20:41:37</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>118.70</td>\n",
       "      <td>22.76</td>\n",
       "      <td>2018-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>47770eb9100c2d0c44946d9cf07ec65d</td>\n",
       "      <td>41ce2a54c0b03bf3443c3d931a367089</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-08-08 08:38:49</td>\n",
       "      <td>2018-08-08 08:55:23</td>\n",
       "      <td>2018-08-08 13:50:00</td>\n",
       "      <td>2018-08-17 18:06:29</td>\n",
       "      <td>2018-09-04</td>\n",
       "      <td>Wed</td>\n",
       "      <td>...</td>\n",
       "      <td>3a653a41f6f9fc3d2a113cf8398680e8</td>\n",
       "      <td>75265</td>\n",
       "      <td>vianopolis</td>\n",
       "      <td>Goiás</td>\n",
       "      <td>2018-08-08 08:38:49</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>159.90</td>\n",
       "      <td>19.22</td>\n",
       "      <td>2018-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>949d5b44dbf5de918fe9c16f97b45f8a</td>\n",
       "      <td>f88197465ea7920adcdbec7375364d82</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2017-11-18 19:28:06</td>\n",
       "      <td>2017-11-18 19:45:59</td>\n",
       "      <td>2017-11-22 13:39:59</td>\n",
       "      <td>2017-12-02 00:28:42</td>\n",
       "      <td>2017-12-15</td>\n",
       "      <td>Sat</td>\n",
       "      <td>...</td>\n",
       "      <td>7c142cf63193a1473d2e66489a9ae977</td>\n",
       "      <td>59296</td>\n",
       "      <td>sao goncalo do amarante</td>\n",
       "      <td>Rio Grande do Norte</td>\n",
       "      <td>2017-11-18 19:28:06</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>27.20</td>\n",
       "      <td>2017-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ad21c59c0840e6cb83a9ceb5573f8159</td>\n",
       "      <td>8ab97904e6daea8866dbdbc4fb7aad2c</td>\n",
       "      <td>delivered</td>\n",
       "      <td>2018-02-13 21:18:39</td>\n",
       "      <td>2018-02-13 22:20:29</td>\n",
       "      <td>2018-02-14 19:46:34</td>\n",
       "      <td>2018-02-16 18:17:02</td>\n",
       "      <td>2018-02-26</td>\n",
       "      <td>Tue</td>\n",
       "      <td>...</td>\n",
       "      <td>72632f0f9dd73dfee390c9b22eb56dd6</td>\n",
       "      <td>9195</td>\n",
       "      <td>santo andre</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>2018-02-13 21:18:39</td>\n",
       "      <td>New</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.90</td>\n",
       "      <td>8.72</td>\n",
       "      <td>2018-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                          order_id  \\\n",
       "0           0  e481f51cbdc54678b7cc49136f2d6af7   \n",
       "1           1  53cdb2fc8bc7dce0b6741e2150273451   \n",
       "2           2  47770eb9100c2d0c44946d9cf07ec65d   \n",
       "3           3  949d5b44dbf5de918fe9c16f97b45f8a   \n",
       "4           4  ad21c59c0840e6cb83a9ceb5573f8159   \n",
       "\n",
       "                        customer_id order_status order_purchase_timestamp  \\\n",
       "0  9ef432eb6251297304e76186b10a928d    delivered      2017-10-02 10:56:33   \n",
       "1  b0830fb4747a6c6d20dea0b8c802d7ef    delivered      2018-07-24 20:41:37   \n",
       "2  41ce2a54c0b03bf3443c3d931a367089    delivered      2018-08-08 08:38:49   \n",
       "3  f88197465ea7920adcdbec7375364d82    delivered      2017-11-18 19:28:06   \n",
       "4  8ab97904e6daea8866dbdbc4fb7aad2c    delivered      2018-02-13 21:18:39   \n",
       "\n",
       "    order_approved_at order_delivered_carrier_date  \\\n",
       "0 2017-10-02 11:07:15          2017-10-04 19:55:00   \n",
       "1 2018-07-26 03:24:27          2018-07-26 14:31:00   \n",
       "2 2018-08-08 08:55:23          2018-08-08 13:50:00   \n",
       "3 2017-11-18 19:45:59          2017-11-22 13:39:59   \n",
       "4 2018-02-13 22:20:29          2018-02-14 19:46:34   \n",
       "\n",
       "  order_delivered_customer_date order_estimated_delivery_date weekday  ...  \\\n",
       "0           2017-10-10 21:25:13                    2017-10-18     Mon  ...   \n",
       "1           2018-08-07 15:27:45                    2018-08-13     Tue  ...   \n",
       "2           2018-08-17 18:06:29                    2018-09-04     Wed  ...   \n",
       "3           2017-12-02 00:28:42                    2017-12-15     Sat  ...   \n",
       "4           2018-02-16 18:17:02                    2018-02-26     Tue  ...   \n",
       "\n",
       "                 customer_unique_id  customer_zip_code_prefix  \\\n",
       "0  7c396fd4830fd04220f754e42b4e5bff                      3149   \n",
       "1  af07308b275d755c9edb36a90c618231                     47813   \n",
       "2  3a653a41f6f9fc3d2a113cf8398680e8                     75265   \n",
       "3  7c142cf63193a1473d2e66489a9ae977                     59296   \n",
       "4  72632f0f9dd73dfee390c9b22eb56dd6                      9195   \n",
       "\n",
       "             customer_city       customer_state     first_order_date usertype  \\\n",
       "0                sao paulo            São Paulo  2017-09-04 11:26:38   Return   \n",
       "1                barreiras                Bahia  2018-07-24 20:41:37      New   \n",
       "2               vianopolis                Goiás  2018-08-08 08:38:49      New   \n",
       "3  sao goncalo do amarante  Rio Grande do Norte  2017-11-18 19:28:06      New   \n",
       "4              santo andre            São Paulo  2018-02-13 21:18:39      New   \n",
       "\n",
       "  no_of_items   price  freight_value   yr_mon  \n",
       "0         1.0   29.99           8.72  2017-10  \n",
       "1         1.0  118.70          22.76  2018-07  \n",
       "2         1.0  159.90          19.22  2018-08  \n",
       "3         1.0   45.00          27.20  2017-11  \n",
       "4         1.0   19.90           8.72  2018-02  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all['yr_mon'] = df_all['order_purchase_timestamp'].dt.to_period('M').astype(\"string\")\n",
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b9d6f56f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>yr_mon</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>2017-01</th>\n",
       "      <th>2017-02</th>\n",
       "      <th>2017-03</th>\n",
       "      <th>2017-04</th>\n",
       "      <th>2017-05</th>\n",
       "      <th>2017-06</th>\n",
       "      <th>2017-07</th>\n",
       "      <th>2017-08</th>\n",
       "      <th>2017-09</th>\n",
       "      <th>...</th>\n",
       "      <th>2017-11</th>\n",
       "      <th>2017-12</th>\n",
       "      <th>2018-01</th>\n",
       "      <th>2018-02</th>\n",
       "      <th>2018-03</th>\n",
       "      <th>2018-04</th>\n",
       "      <th>2018-05</th>\n",
       "      <th>2018-06</th>\n",
       "      <th>2018-07</th>\n",
       "      <th>2018-08</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86.22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>196.89</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "yr_mon                customer_unique_id  2017-01  2017-02  2017-03  2017-04  \\\n",
       "0       0000366f3b9a7992bf8c76cfdf3221e2      0.0      0.0     0.00      0.0   \n",
       "1       0000b849f77a49e4a4ce2b2a4ca5be3f      0.0      0.0     0.00      0.0   \n",
       "2       0000f46a3911fa3c0805444483337064      0.0      0.0    86.22      0.0   \n",
       "3       0000f6ccb0745a6a4b88665a16c9f078      0.0      0.0     0.00      0.0   \n",
       "4       0004aac84e0df4da2b147fca70cf8255      0.0      0.0     0.00      0.0   \n",
       "\n",
       "yr_mon  2017-05  2017-06  2017-07  2017-08  2017-09  ...  2017-11  2017-12  \\\n",
       "0           0.0      0.0      0.0      0.0      0.0  ...     0.00      0.0   \n",
       "1           0.0      0.0      0.0      0.0      0.0  ...     0.00      0.0   \n",
       "2           0.0      0.0      0.0      0.0      0.0  ...     0.00      0.0   \n",
       "3           0.0      0.0      0.0      0.0      0.0  ...     0.00      0.0   \n",
       "4           0.0      0.0      0.0      0.0      0.0  ...   196.89      0.0   \n",
       "\n",
       "yr_mon  2018-01  2018-02  2018-03  2018-04  2018-05  2018-06  2018-07  2018-08  \n",
       "0           0.0      0.0      0.0      0.0   141.90      0.0      0.0      0.0  \n",
       "1           0.0      0.0      0.0      0.0    27.19      0.0      0.0      0.0  \n",
       "2           0.0      0.0      0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "3           0.0      0.0      0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "4           0.0      0.0      0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SALES = df_all.pivot_table(index=[\"customer_unique_id\"], columns=[\"yr_mon\"],\n",
    "                           values=\"payment_value\", aggfunc=\"sum\", fill_value=0).reset_index()\n",
    "SALES.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a37d6867",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>yr_mon</th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>2017-01</th>\n",
       "      <th>2017-02</th>\n",
       "      <th>2017-03</th>\n",
       "      <th>2017-04</th>\n",
       "      <th>2017-05</th>\n",
       "      <th>2017-06</th>\n",
       "      <th>2017-07</th>\n",
       "      <th>2017-08</th>\n",
       "      <th>2017-09</th>\n",
       "      <th>...</th>\n",
       "      <th>2017-12</th>\n",
       "      <th>2018-01</th>\n",
       "      <th>2018-02</th>\n",
       "      <th>2018-03</th>\n",
       "      <th>2018-04</th>\n",
       "      <th>2018-05</th>\n",
       "      <th>2018-06</th>\n",
       "      <th>2018-07</th>\n",
       "      <th>2018-08</th>\n",
       "      <th>CLV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86.22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>196.89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "yr_mon                customer_unique_id  2017-01  2017-02  2017-03  2017-04  \\\n",
       "0       0000366f3b9a7992bf8c76cfdf3221e2      0.0      0.0     0.00      0.0   \n",
       "1       0000b849f77a49e4a4ce2b2a4ca5be3f      0.0      0.0     0.00      0.0   \n",
       "2       0000f46a3911fa3c0805444483337064      0.0      0.0    86.22      0.0   \n",
       "3       0000f6ccb0745a6a4b88665a16c9f078      0.0      0.0     0.00      0.0   \n",
       "4       0004aac84e0df4da2b147fca70cf8255      0.0      0.0     0.00      0.0   \n",
       "\n",
       "yr_mon  2017-05  2017-06  2017-07  2017-08  2017-09  ...  2017-12  2018-01  \\\n",
       "0           0.0      0.0      0.0      0.0      0.0  ...      0.0      0.0   \n",
       "1           0.0      0.0      0.0      0.0      0.0  ...      0.0      0.0   \n",
       "2           0.0      0.0      0.0      0.0      0.0  ...      0.0      0.0   \n",
       "3           0.0      0.0      0.0      0.0      0.0  ...      0.0      0.0   \n",
       "4           0.0      0.0      0.0      0.0      0.0  ...      0.0      0.0   \n",
       "\n",
       "yr_mon  2018-02  2018-03  2018-04  2018-05  2018-06  2018-07  2018-08     CLV  \n",
       "0           0.0      0.0      0.0   141.90      0.0      0.0      0.0  141.90  \n",
       "1           0.0      0.0      0.0    27.19      0.0      0.0      0.0   27.19  \n",
       "2           0.0      0.0      0.0     0.00      0.0      0.0      0.0   86.22  \n",
       "3           0.0      0.0      0.0     0.00      0.0      0.0      0.0   43.62  \n",
       "4           0.0      0.0      0.0     0.00      0.0      0.0      0.0  196.89  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SALES[\"CLV\"] = SALES.iloc[:,2:].sum(axis=1)\n",
    "SALES.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "021325b4",
   "metadata": {},
   "source": [
    "## Part 1: predict CLV\n",
    "\n",
    "Using historical montly revenue data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d9db0a61",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>yr_mon</th>\n",
       "      <th>2017-08</th>\n",
       "      <th>2017-09</th>\n",
       "      <th>2017-10</th>\n",
       "      <th>2017-11</th>\n",
       "      <th>2017-12</th>\n",
       "      <th>2018-01</th>\n",
       "      <th>2018-02</th>\n",
       "      <th>2018-03</th>\n",
       "      <th>2018-04</th>\n",
       "      <th>2018-05</th>\n",
       "      <th>2018-06</th>\n",
       "      <th>2018-07</th>\n",
       "      <th>2018-08</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>43.62</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>196.89</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95768</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95769</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>84.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95770</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95771</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>133.69</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95772</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95773 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "yr_mon  2017-08  2017-09  2017-10  2017-11  2017-12  2018-01  2018-02  \\\n",
       "0           0.0      0.0     0.00     0.00     0.00      0.0      0.0   \n",
       "1           0.0      0.0     0.00     0.00     0.00      0.0      0.0   \n",
       "2           0.0      0.0     0.00     0.00     0.00      0.0      0.0   \n",
       "3           0.0      0.0    43.62     0.00     0.00      0.0      0.0   \n",
       "4           0.0      0.0     0.00   196.89     0.00      0.0      0.0   \n",
       "...         ...      ...      ...      ...      ...      ...      ...   \n",
       "95768       0.0      0.0     0.00     0.00     0.00      0.0      0.0   \n",
       "95769       0.0      0.0     0.00     0.00    84.58      0.0      0.0   \n",
       "95770       0.0      0.0     0.00     0.00     0.00      0.0      0.0   \n",
       "95771       0.0      0.0     0.00     0.00     0.00      0.0      0.0   \n",
       "95772       0.0      0.0     0.00     0.00     0.00      0.0      0.0   \n",
       "\n",
       "yr_mon  2018-03  2018-04  2018-05  2018-06  2018-07  2018-08  \n",
       "0           0.0      0.0   141.90      0.0      0.0      0.0  \n",
       "1           0.0      0.0    27.19      0.0      0.0      0.0  \n",
       "2           0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "3           0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "4           0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "...         ...      ...      ...      ...      ...      ...  \n",
       "95768       0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "95769       0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "95770       0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "95771       0.0      0.0   133.69      0.0      0.0      0.0  \n",
       "95772       0.0      0.0     0.00      0.0      0.0      0.0  \n",
       "\n",
       "[95773 rows x 13 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X = SALES[['2017-08', '2017-09', '2017-10', '2017-11', '2017-12', '2018-01', '2018-02', \n",
    "           '2018-03', '2018-04', '2018-05', '2018-06', '2018-07', '2018-08']]\n",
    "y = SALES[['CLV']]\n",
    "X"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9aa1bdf5",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.33, random_state=42)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c8da899",
   "metadata": {},
   "source": [
    "### Linear Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ccb41b85",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:                    CLV   R-squared:                       0.750\n",
      "Model:                            OLS   Adj. R-squared:                  0.750\n",
      "Method:                 Least Squares   F-statistic:                 1.477e+04\n",
      "Date:                Thu, 24 Mar 2022   Prob (F-statistic):               0.00\n",
      "Time:                        14:51:38   Log-Likelihood:            -3.9650e+05\n",
      "No. Observations:               64167   AIC:                         7.930e+05\n",
      "Df Residuals:                   64153   BIC:                         7.932e+05\n",
      "Df Model:                          13                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "==============================================================================\n",
      "                 coef    std err          t      P>|t|      [0.025      0.975]\n",
      "------------------------------------------------------------------------------\n",
      "const         40.4292      0.547     73.933      0.000      39.357      41.501\n",
      "2017-08        0.9251      0.009    105.173      0.000       0.908       0.942\n",
      "2017-09        0.9540      0.006    161.957      0.000       0.942       0.966\n",
      "2017-10        0.9250      0.007    127.235      0.000       0.911       0.939\n",
      "2017-11        0.9027      0.007    133.794      0.000       0.890       0.916\n",
      "2017-12        0.9033      0.008    118.743      0.000       0.888       0.918\n",
      "2018-01        0.8971      0.007    128.317      0.000       0.883       0.911\n",
      "2018-02        0.8986      0.007    121.880      0.000       0.884       0.913\n",
      "2018-03        0.9024      0.007    133.770      0.000       0.889       0.916\n",
      "2018-04        0.9216      0.006    152.972      0.000       0.910       0.933\n",
      "2018-05        0.9233      0.006    153.723      0.000       0.912       0.935\n",
      "2018-06        0.9207      0.006    142.545      0.000       0.908       0.933\n",
      "2018-07        0.9402      0.006    167.967      0.000       0.929       0.951\n",
      "2018-08        0.9250      0.006    145.601      0.000       0.913       0.937\n",
      "==============================================================================\n",
      "Omnibus:                   131874.107   Durbin-Watson:                   1.979\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):       1083066758.817\n",
      "Skew:                          17.024   Prob(JB):                         0.00\n",
      "Kurtosis:                     638.558   Cond. No.                         99.5\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n"
     ]
    }
   ],
   "source": [
    "regressor = LinearRegression()\n",
    "regressor.fit(X_train, y_train)\n",
    "\n",
    "X_train1 = st.add_constant(X_train)\n",
    "reg_model=st.OLS(y_train,X_train1)\n",
    "result=reg_model.fit()\n",
    "print(result.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1af3c24b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Precision of linear regression on training set: 0.750\n",
      "Precision of linear regression on test set: 0.672\n"
     ]
    }
   ],
   "source": [
    "print(\"Precision of linear regression on training set: {:.3f}\".format(regressor.score(X_train, y_train)))\n",
    "print(\"Precision of linear regression on test set: {:.3f}\".format(regressor.score(X_test, y_test)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d280bf18",
   "metadata": {},
   "source": [
    "### KNN Regressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "75680fff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYgAAAEICAYAAABF82P+AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAA470lEQVR4nO3deXyU5bn/8c+VjSwkIQkBEkIIKDskEMImKiiKoIJaqxW344pLtWqPHrW/c7Q9PW1ttVZtXYoKKlrFrXVDRSoERUEW2fedEJYsJKzZr98fzyQMYRISmMlMkuv9es1rZp5trkScb+77fp77EVXFGGOMqS3I3wUYY4wJTBYQxhhjPLKAMMYY45EFhDHGGI8sIIwxxnhkAWGMMcajEF8eXETGAc8CwcArqvpErfWxwJtAqquWp1R1WkP29aR9+/aalpbm1Z/BGGNasiVLluSraqKndeKr6yBEJBjYAFwI5ACLgEmqusZtm18Bsar6sIgkAuuBTkDlyfb1JCsrSxcvXuyLH8cYY1okEVmiqlme1vmyi2kosElVt6hqGfAOcFmtbRSIFhEB2gKFQEUD9zXGGONDvgyIzsBOt/c5rmXu/gb0AXKBlcB9qlrVwH2NMcb4kC8DQjwsq92fdRGwDEgGBgJ/E5GYBu7rfIjIZBFZLCKL8/LyTr1aY4wxx/HlIHUO0MXtfQpOS8HdzcAT6gyEbBKRrUDvBu4LgKpOAaaAMwbhndKNaT3Ky8vJycmhpKTE36UYHwoPDyclJYXQ0NAG7+PLgFgE9BCRbsAu4Brg2lrb7ADGAN+ISEegF7AFKGrAvsYYL8jJySE6Opq0tDSc4UDT0qgqBQUF5OTk0K1btwbv57OAUNUKEbkH+BLnVNWpqrpaRO50rX8J+C3wmoisxOlWelhV8wE87eurWo1pzUpKSiwcWjgRISEhgcZ2w/v0OghVnQnMrLXsJbfXucDYhu5rjPENC4eW71T+G9uV1AHsu835bNh70N9lGGNaKQuIAPX37M1c+/JCLnpmHr98dxk7C4/4uyRjfKKoqIgXXnjhlPa9+OKLKSoqqnebxx57jNmzZ5/S8Vs7n11J7Q8t4UpqVeWJL9bx9+wtXDIgiZS4CF77bhtVqlw3rCs/P+9MEqPb+LtM04KsXbuWPn36+O3zt23bxqWXXsqqVatOWFdZWUlwcLAfqvKviooKQkK8PwLg6b+1v66kNo1UUVnFwx+s4O/ZW7huWCrPTRrEoxf3Ifuh8/jp4C5MX7CdUU/O4elZ6zlQUu7vco3xikceeYTNmzczcOBAHnroIebOnct5553Htddey4ABAwC4/PLLGTx4MP369WPKlCk1+6alpZGfn8+2bdvo06cPt99+O/369WPs2LEcPXoUgJtuuon333+/ZvvHH3+czMxMBgwYwLp16wDIy8vjwgsvJDMzkzvuuIOuXbuSn59/Qq133XUXWVlZ9OvXj8cff7xm+aJFizjrrLPIyMhg6NChHDx4kMrKSh588EEGDBhAeno6f/3rX4+rGWDx4sWMHj0agF//+tdMnjyZsWPHcuONN7Jt2zbOOeccMjMzyczM5Lvvvqv5vD/96U8MGDCAjIyMmt9fZmZmzfqNGzcyePDg0/5v49NBatNwJeWV/OLtH5m1Zi+/GNODBy7oUTOo1Ck2nD/8ZAC3n9ONP3+1gee+3sT0Bdu5e/SZ3DCiK+Ghre8vLOMbv/lkNWtyD3j1mH2TY3h8Qr861z/xxBOsWrWKZcuWATB37lx++OEHVq1aVXNK5tSpU4mPj+fo0aMMGTKEK6+8koSEhOOOs3HjRt5++21efvllrr76aj744AOuv/76Ez6vffv2LF26lBdeeIGnnnqKV155hd/85jecf/75PProo3zxxRfHhZC73/3ud8THx1NZWcmYMWNYsWIFvXv35mc/+xkzZsxgyJAhHDhwgIiICKZMmcLWrVv58ccfCQkJobCw8KS/qyVLlvDtt98SERHBkSNH+OqrrwgPD2fjxo1MmjSJxYsX8/nnn/Ovf/2LhQsXEhkZSWFhIfHx8cTGxrJs2TIGDhzItGnTuOmmm076eSdjLYgAcLCknJum/cCsNXt5fEJffnlhT49nHHRPbMvz12byyT1n079zLL+buZbznprLjEU7qKis8kPlxvjG0KFDjztf/7nnniMjI4Phw4ezc+dONm7ceMI+3bp1Y+DAgQAMHjyYbdu2eTz2T37ykxO2+fbbb7nmmmsAGDduHHFxcR73fffdd8nMzGTQoEGsXr2aNWvWsH79epKSkhgyZAgAMTExhISEMHv2bO68886arqL4+PiT/twTJ04kIiICcC5gvP322xkwYABXXXUVa9Y4c5XOnj2bm2++mcjIyOOOe9tttzFt2jQqKyuZMWMG1157+peOWQvCz/IPlXLTtB9Yt/sgz/xsIJcPOvmUUwNSYpl+6zC+25zPn75Yz8MfrOTv87bw0NhejOvfyU5ZNKesvr/0m1JUVFTN67lz5zJ79my+//57IiMjGT16tMervtu0OTY2FxwcXNPFVNd2wcHBVFRUAM7Y38ls3bqVp556ikWLFhEXF8dNN91ESUkJqurx/7m6loeEhFBV5fxBV/vncP+5//KXv9CxY0eWL19OVVUV4eHh9R73yiuvrGkJDR48+IQW1qmwFoQf7Sw8wlUvfc+mfYd4+casBoWDu7POaM8/7z6Lv98wmCAR7nprKZc9P5/5m07sOzUmUEVHR3PwYN2ncxcXFxMXF0dkZCTr1q1jwYIFXq/h7LPP5t133wVg1qxZ7N+//4RtDhw4QFRUFLGxsezdu5fPP/8cgN69e5Obm8uiRYsAOHjwIBUVFYwdO5aXXnqpJoSqu5jS0tJYsmQJAB988EGdNRUXF5OUlERQUBDTp0+nsrISgLFjxzJ16lSOHDly3HHDw8O56KKLuOuuu7j55ptP+3cCFhB+s37PQX760ncUHCrlzVuHcV7vDqd0HBHhon6d+PL+c3nyp+kUHCrjulcWcv0rC1m+s8i7RRvjAwkJCYwcOZL+/fvz0EMPnbB+3LhxVFRUkJ6ezv/8z/8wfPhwr9fw+OOPM2vWLDIzM/n8889JSkoiOjr6uG0yMjIYNGgQ/fr145ZbbmHkyJEAhIWFMWPGDO69914yMjK48MILKSkp4bbbbiM1NZX09HQyMjL4xz/+UfNZ9913H+ecc069Z2jdfffdvP766wwfPpwNGzbUtC7GjRvHxIkTycrKYuDAgTz11FM1+1x33XWICGPHerz+uNHsNFc/WLJ9P7e8tog2IUG8cetQeneK8dqxSysqeWvBDv42ZxOFh8sY378T/zm2F2d2aOu1zzAti79Pcw0EpaWlBAcHExISwvfff89dd91VM2jenDz11FMUFxfz29/+1uP6xp7mamMQTWzu+n3c9eZSOsa0Yfqtw+gSH+nV47cJCeaWs7tx9ZAuvPLNFl6et4UvV+/hqsFduO+CHiS3i/Dq5xnTEuzYsYOrr76aqqoqwsLCePnll/1dUqNdccUVbN68ma+//tprx7QWRBP6aNku/vPd5fTsGM3rtwxtkgveCg6V8vyczby5YDsI3Di8K3efdybxUWE+/2zTPFgLovWwC+UC1OvfbeP+GcsY3DWOd+4Y3mRXQye0bcNjE/ry9YOjmJiRzNT5Wzn3T3N47t8bOVxa0SQ1GGOaJwsIH1NV/vLVBh7/eDUX9OnI67cMJSa84Tfs8JaUuEieuiqDL+8/l5FnJvD0VxsY9eQcXpu/ldKKyiavxxgT+CwgfKiqSnnso9U8+++NXDU4hRevy/T7Vc89Okbz9xuy+PDuszizQ1t+/ckaxvw5mw+X5lBZ1XK6G40xp88CwkfKKqq4b8Yypi/Yzh3ndudPP00nJDhwft2ZqXG8fftw3rhlKO0iQ/nlu8u5+Nlv+GrN3gZdNGSMafkC5xurBTlSVsFtbyzmk+W5PDK+N49e3Ccgr24WEc7tmcjHPz+bv107iLLKKm5/YzFXvvgdC7cU+Ls800qcznTfAM8880zNRWPGuywgvGz/4TKufXkh327M409XpnPnqDP8XdJJBQUJl6YnM+uBc/nDTwawq+goP5uygJum/cDq3GJ/l2dauJYQENVXS7c0FhBetKe4hKv//j1rdh/gxesHc/WQLv4uqVFCg4OYNDSV7IfO49HxvflxRxGXPPct9779I9vyD/u7PNNC1Z7uG+DJJ59kyJAhpKen10yrffjwYS655BIyMjLo378/M2bM4LnnniM3N5fzzjuP884774Rj/+///i9Dhgyhf//+TJ48uab7dNOmTVxwwQVkZGSQmZnJ5s2bgROn0QYYPXo01afP5+fnk5aWBsBrr73GVVddxYQJExg7diyHDh1izJgxNVOJf/TRRzV1vPHGGzVXVN9www0cPHiQbt26UV7uTNt/4MAB0tLSat4HCrtQzku25B3ihld/oPhoOa/fPJQRZ5z+RFn+Eh4azB2jzuCaoam8PG8Lr367lc9X7uZnQ7rwizE96BgT7u8Sja98/gjsWendY3YaAOOfqHN17em+Z82axcaNG/nhhx9QVSZOnMi8efPIy8sjOTmZzz77DHDmKoqNjeXpp59mzpw5tG/f/oRj33PPPTz22GMA3HDDDXz66adMmDCB6667jkceeYQrrriCkpISqqqqPE6jfTLff/89K1asID4+noqKCv75z38SExNDfn4+w4cPZ+LEiaxZs4bf/e53zJ8/n/bt21NYWEh0dDSjR4/ms88+4/LLL+edd97hyiuvJDS06c9wrI+1ILxgZU4xV730PSXllbwzeXizDgd3sRGhPHhRL7L/azTXDktlxqKdjHpyDn/8Yh2Fh8v8XZ5poWbNmsWsWbMYNGgQmZmZrFu3jo0bNzJgwABmz57Nww8/zDfffENsbOxJjzVnzhyGDRvGgAED+Prrr1m9ejUHDx5k165dXHHFFYAzyV1kZGSd02jX58ILL6zZTlX51a9+RXp6OhdccAG7du1i7969fP311/z0pz+tCbDa03MDTJs2zWsT7HmTtSBO03eb85n8xhJiI0KZfutQuie2vDmPOkSH87+X9ee2s7vz9FfreSl7My/P28I5PdozcWAyF/btRNs29k+pRajnL/2moqo8+uij3HHHHSesW7JkCTNnzuTRRx9l7NixNa0DT0pKSrj77rtZvHgxXbp04de//nXN9Nx1fe7pTM/91ltvkZeXx5IlSwgNDSUtLa3e6cBHjhzJtm3byM7OprKykv79+9f5s/iLtSBOwxerdnPT1EUktwvng7vOapHh4C41IZJnrhnErPvP5dZzurFh7yEemLGcrP/7ip+/tZQvVu2hpNwuujONU3u674suuoipU6dy6NAhAHbt2sW+ffvIzc0lMjKS66+/ngcffJClS5d63L9a9Zd5+/btOXToUM1tR2NiYkhJSeFf//oX4EzUd+TIkTqn0Xafnrv6GJ4UFxfToUMHQkNDmTNnDtu3bwdgzJgxvPvuuxQUFBx3XIAbb7yRSZMmBWTrAawFccpmLNrBox+uJKNLO6bdNIR2ka1nbqMeHaN5dHwfHr6oN0t37OejZbnMXLmbz1buJjo8hHH9OjFxYDIjuicE1LUfJjC5T/c9fvx4nnzySdauXcuIESMAaNu2LW+++SabNm3ioYceIigoiNDQUF588UUAJk+ezPjx40lKSmLOnDk1x23Xrl3NHdnS0tJq7vgGMH36dO644w4ee+wxQkNDee+99xg3bhzLli0jKyuLsLAwLr74Yn7/+9/z4IMPcvXVVzN9+nTOP//8On+O6667jgkTJtRMw927d28A+vXrx//7f/+PUaNGERwczKBBg3jttddq9vnv//5vJk2a5O1fq1f4dLI+ERkHPAsEA6+o6hO11j8EXOd6GwL0ARJVtVBEHgBuAxRYCdysqifeRspNU0zWp6q8lL2FP36xjlE9E3nx+kwiwyxnKyqrmL+5gI+X5fLl6j0cKq2gfdswLhmQxMSBnclMbReQ14IYm6zPn95//30++ugjpk+f3iSfFzDTfYtIMPA8cCGQAywSkY9VdU31Nqr6JPCka/sJwAOucOgM/ALoq6pHReRd4BrgNV/V2xCqyu9nruXlb7YyMSOZp67KICzE/kIGCAkOYlTPREb1TOR35f2Zs24fHy/P5e1FO3n9++2kxEUwISOZiRnJ9O4UbWFhWr17772Xzz//nJkzZ/q7lDr58k/focAmVd0CICLvAJcBa+rYfhLwdq3aIkSkHIgEcn1Y60lVVFbx8Acr+WBpDv8xoiuPT+hHUJB9yXkSHhrM+AFJjB+QxMGScmat3stHy3OZMm8LL87dTI8ObZmYkczEgcl0TYg6+QGNaYH++te/+ruEk/JlQHQGdrq9zwGGedpQRCKBccA9AKq6S0SeAnYAR4FZqjrLh7XWq6S8knv+8SOz1+7lgQt68osxZ9pfwA0UHR7KlYNTuHJwCvmHSvl85W4+Xp7Ln7/awJ+/2kBGl3ZMzEhmQnoSHez6Cr+p60wb03KcynCCLwPC07+2uiqcAMxX1UIAEYnDaW10A4qA90TkelV984QPEZkMTAZITU31QtnHO1BSzm2vL2bRtkJ+e1k/bhiR5vXPaC3at23DDSPSuGFEGruKjvLJ8lw+XpbLbz9dw/99toYR3ROYmJHM+P5JxEYG1gVDLVl4eDgFBQUkJCRYSLRQqkpBQQHh4Y37I8xng9QiMgL4tape5Hr/KICq/sHDtv8E3lPVf7jeXwWMU9VbXe9vBIar6t31faa3B6nzDpbyH1N/YOO+gzx99UAmZCR77djmmE37DvHx8lw+XraLbQVHCA0WRvVMZEJGMhf27WgnAfhYeXk5OTk5J5zjb1qW8PBwUlJSTrhau75Bal8GRAiwARgD7AIWAdeq6upa28UCW4EuqnrYtWwYMBUYgtPF9BqwWFXr7bTzZkDsLDzC9a8uZN+BUl66YTCjeiZ65bimbqrKyl3FfLwsl09X7GbPgRIiQoO5sG9HJmYkc27PRDspwBgv88tZTKpaISL3AF/inOY6VVVXi8idrvUvuTa9AmeM4bDbvgtF5H1gKVAB/AhM8VWtta3bc4AbX/2Bssoq3rp9GJmpcU310a2aiJCe0o70lHb86uI+/LCtkI+W5fL5KmfcIjYilPH9nWsshnVLINhOEjDGp3x6HURT80YLYvG2Qm55bRGRYSFMv3UoPTpGe6k6c6rKKqr4dlMeHy/LZdaavRwpq6RDdBsuTXfOhMpIibW+c2NOkV+6mPzhdANizrp93PXWEpJjI3jj1qGkxEV6sTrjDUfLKpm9di8fL88le30eZZVVdE2IZEJ6MpcNTLZAN6aRLCAa4F8/7uLB95bTJymG124eQkLbNl6uznhb8dFyvly1h4+X5/Ld5nyqFM5IjGJ0rw6M6pnI0G7xfr8HuDGBzgLiJKbN38pvPlnDWWck8PcbBhMdbqdYNjf7DpYwc8Vu/r1uHwu3FFJWWUV4aBAjuifUBEZae7soz5jaLCDqsf9wGRc8nc2QtHieuWag/cXZAhwpq2DBlgKy1+cxd0Me2wuc2TnTEiIZ1TOR0b06MLx7AhFh9t/aGAuIk9hRcITOcRF2VkwLtS3/MNkb8pi7fh/fbymgpLyKsJAghnWLrwmMMxKjbKDbtEoWEMa4lJRX8sPWwprA2JznnF2dEhdRM9ngWWe2txsgmVbDAsKYOuwsPEL2hjyyN+Tx3aZ8DpdVEhosZHWNZ3SvREb1SqRXR5t91rRcFhDGNEBZRRWLtxeSvd4JjHV7nLuUdYoJd3VFJTKyR3ti7CQG04JYQBhzCnYXH2Xehjzmrs/j2435HCytIDhIGJwax6heTndU36QYm/bdNGsWEMacpvLKKn7cUUT2hn3MXZ/H6twDgDND7aieTlfUuT3at6pbz5qWwQLCGC/bd7CEeRvyyd6Qxzcb8yg6Uk6QQEaXdozu2YFRvRJJ7xxrrQsT8CwgjPGhyipleU4Rc11jFytyilCF+KgwzunR3hns7tmB+ChrXZjAYwFhTBMqOFTKt5vymbs+j3kb8ig4XEZwkHDWGQlMSE/mon6d7IZIJmBYQBjjJ1VVyqrcYj5ftYdPV+Sys/AoocHCOT0SuWRAEhf262hnRRm/soAwJgBU3xDp0xW7+WzFbnYVHSUsOIhRvRK5ND2JC/p0JMou0DNNzALCmACjqizdUcRnK3bz2cpc9h4opU1IEOf37sCl6cmc37uDzRVlmoQFhDEBrKpKWbx9P5+uyGXmyj3kHyolMiyYMX06csmAJEb3SrRJJI3PWEAY00xUVikLtxbw6YrdfLFqD4WHy2jbJoQL+3bk0vQkzulh9+U23mUBYUwzVFFZxXebC/hsxW6+WL2H4qPlxISHMLZfJy5NT2Lkme0JDbawMKfHAsKYZq6soor5m/L5ZEUuX63ey8HSCuIiQxnXvxOXpiczrFs8IRYW5hRYQBjTgpRWVDJvQz6frshl9pq9HC6rpH3bsJqwGJIWb/c2MQ1mAWFMC1VSXsnc9fv4ZMVuvl67j6PllXSIbsPFA5K4ND2JzNQ4m+7D1MsCwphW4EhZBf9eu49PV+QyZ30eZRVVJMeGO2GRkUxGSqzd18KcwALCmFbmUGkFs9fs5dMVuWRvyKO8UkmJi+CS9CQmpCfTLznGwsIAfgwIERkHPAsEA6+o6hO11j8EXOd6GwL0ARJVtVBE2gGvAP0BBW5R1e/r+zwLCGNOVHy0nFmr9/DZyt18uzGfiiolLSGSS9OTGde/k93TopXzS0CISDCwAbgQyAEWAZNUdU0d208AHlDV813vXwe+UdVXRCQMiFTVovo+0wLCmPrtP1zGl66w+G5zAZVVave0aOXqCwhfTvwyFNikqltcRbwDXAZ4DAhgEvC2a9sY4FzgJgBVLQPKfFirMa1CXFQY1wxN5ZqhqRQcKq2Zovzf6/bywdKcmntaOLdY7cCAzrF2RlQr5ssWxE+Bcap6m+v9DcAwVb3Hw7aROK2MM13dSwOBKThhkgEsAe5T1cMe9p0MTAZITU0dvH37dp/8PMa0ZJVVyoqcIrJdt1hd7rqnRVxkKOf0cO7HfU6PRBKj2/i7VONl/mpBePqzo640mgDMV9VC1/sQIBO4V1UXisizwCPA/5xwQNUpOGFCVlZWyxlxN6YJBQcJg1LjGJQax/0X9GT/4TK+2ZTP3PX7mLchj4+X5wLQv3NMzR3zBnVpZxfntXC+DIgcoIvb+xQgt45tr8HVveS2b46qLnS9fx8nIIwxTSAuKoyJGclMzEimqkpZs/sA2RvyyF6fx4vZm/nbnE1Eh4dwTo/2zvhFzw50ig33d9nGy3wZEIuAHiLSDdiFEwLX1t5IRGKBUcD11ctUdY+I7BSRXqq6HhhD3WMXxhgfCgoS+neOpX/nWH5+3pkUHy3nu035Nd1RM1fuAaB3p+iawe6srvE2qWAL4OvTXC8GnsE5zXWqqv5ORO4EUNWXXNvchDNWcU2tfQfinOYaBmwBblbV/fV9np3FZEzTUlU27D3E3PX7yN6Qx6JthZRXKlFhwYw4o/p+3Il0iY/0d6mmDnahnDGmSRwureC7zQVkb9jH3PV55Ow/CsAZiVGMco1dDOsWb/e3CCAWEMaYJqeqbMk/TPb6POZuyGPBlgLKKqoIDw1iePeEmlNp0xIi7apuP7KAMMb43dGyShZuLWDu+jzmbchjS75z1npqfKQrLBIZcUYCkWF2X+6mZAFhjAk4OwqOkL3BGbuYv6mAo+WVhAUHMbRbPCPOSGBgl3YMSIklJjzU36W2aBYQxpiAVlpRyeJt+11nRu1jw95DNeu6J0YxMKUd6SmxZHRpR5+kGBvD8CILCGNMs1J0pIwVOcUs31nE8pxilucUkXewFIDQYKF3pxgyusSSntKOgV3acUZiW5sS5BRZQBhjmjVVZc+BkmOBsbOIlTnFHCytACAqLJj+nZ0WRoartZESF2GD3w3gr6k2jDHGK0SEpNgIkmIjGNc/CYCqKucsqRU5RSzfWcSynGJem7+NssoqABKiwmq6papDI6GtzSXVGBYQxphmKShIOLNDW87s0JafZKYAUFZRxbo9B2paGStyipi7IY/qjpKUuAhXYMSSkdKO/p1jiWpjX4N1sS4mY0yLdqi0glW7qgOjmGU7i9hV5FzAFyTQo0P0cS2NXp2iW9U0IdbFZIxptdq2CWF49wSGd0+oWZZ/qNTVNeUMgP973T7eW5IDQFhIEH2TYhjY5diZU90SolrlXfesBWGMafVUlZz9R1nuGs9YnlPMypxijpZXAhAdHkJ6SiyDusQxrHs8g7vGtZgL+uwsJmOMaaTKKmXTvkOuwHAea3cfpLJKCQkS0lNiGd49gWHdE8jqGtdsxzIsIIwxxgsOlVaweFshC7cWsmBLAStziqmoUoKDhAGdqwMjniFp8bRtJoFhAWGMMT5wuLSCJdv3s3BrAQu2FLIip4jySicw+ifH1ARGVlp8wE4ZYgFhjDFN4EhZBUu3F7kCo4BlO53ACBLolxzL8O7xDOuWwJBu8cRGBEZgnHZAiMhI4NdAV5wznwRQVe3uxTpPmwWEMSaQHC2r5Mcd+1ng6pJatqOIssoqRKBvUkzN2VVD0+KJjfRPYHgjINYBDwBLgMrq5apa4K0ivcECwhgTyErKK/lxRxELthSwcGsBS3cUUVbhBEafTjEM6x5fExhxUWFNUpM3AmKhqg7zemVeZgFhjGlOSsorWb6ziAVbClm4tYAl2/dTWuFMFdK7U7SrhRHP0G4JxPsoMLwREE/g3Ff6Q6C0ermqLvVWkd5gAWGMac5KKypZkVPMgs0FLNxayOLthZSUO4HRq2P0sRZGt3jae2leKW8ExBwPi1VVzz/d4rzJAsIY05KUVVSxcpfTwliwpYDF2/bXXLzXo0PbmsAY1i2BxOhTCww7i8kYY1qA8soqVu4qdsYwthSyeFshh8sqiW4TwrLHx57SPTFOey4mEYkFHgfOdS3KBv5XVYsbXY0xxphTEhocRGZqHJmpcdw9Gioqq1iVe4Bd+4/65IZJDZ2ycCpwELja9TgATPN6NcYYYxosJDiIgV3acUl6kk+O39CAOENVH1fVLa7Hb4CTXgMhIuNEZL2IbBKRRzysf0hElrkeq0SkUkTi3dYHi8iPIvJpw38kY4wx3tDQgDgqImdXv3FdOHe0vh1EJBh4HhgP9AUmiUhf921U9UlVHaiqA4FHgWxVLXTb5D5gbQNrNMYY40UNDYi7gOdFZJuIbAf+Btx5kn2GAptcLY4y4B3gsnq2nwS8Xf1GRFKAS4BXGlijMcYYL2rQILWqLgMyRCTG9f5AA3brDOx0e58DeLzYTkQigXHAPW6LnwH+C4huSI3GGGO8q96AEJHrVfVNEfllreUAqOrT9e3uYVld59ROAOZXdy+JyKXAPlVdIiKjT1LjZGAyQGpqan2bGmOMaYSTdTFFuZ6j63jUJwfo4vY+BcitY9trcOteAkYCE0VkG07X1Pki8qanHVV1iqpmqWpWYmLiSUoyxhjTUD67UE5EQoANwBhgF7AIuFZVV9faLhbYCnRR1cMejjMaeFBVLz3ZZ9qFcsYY0zj1XSjXoEFqEfmTiMSISKiI/FtE8kXk+vr2UdUKnDGFL3HORHpXVVeLyJ0i4j7AfQUwy1M4GGOM8Z+GzsW0TFUHisgVwOU4U3/PUdUMH9fXKNaCMMaYxjntFgRQfSeLi4G3a12rYIwxpgVq6F21P3HdNOgocLeIJAIlvivLGGOMvzWoBaGqjwAjgCxVLQcOU/9Fb8YYY5q5k10Hcb6qfi0iP3Fb5r7Jh74qzBhjjH+drItpFPA1zoVstSkWEMYY02LVGxCq+rjr+eamKccYY0ygaOh1EL8XkXZu7+NE5P98VpUxxhi/a+hpruNVtaj6jaruxznl1RhjTAvV0IAIFpGaO2KLSARwanfINsYY0yw09DqIN4F/i8g0nMHpW4DXfVaVMcYYv2vo/SD+JCIrgAtwpvH+rap+6dPKjDHG+FVDWxDgTLhXoaqzRSRSRKJV9aCvCjPGGONfDT2L6XbgfeDvrkWdgX/5qCZjjDEBoKGD1D/HuYnPAQBV3Qh08FVRxhhj/K+hAVGqqmXVb1w3A/LNnYaMMcYEhIYGRLaI/AqIEJELgfeAT3xXljHGGH9raEA8DOQBK4E7gJnAf/uqKGOMMf530rOYRCQIWKGq/YGXfV+SMcaYQHDSFoSqVgHLRSS1CeoxxhgTIBp6HUQSsFpEfsC5WRAAqjrRJ1UZY4zxu4YGxG98WoUxjaUKWgWV5VBVDlUVUFkBIWEQFg1BDR1eM8bU5WR3lAsH7gTOxBmgflVVK5qiMBNAyg7D4XznccT1XH7E9aVc/QVdeeKXdVWF8/64165ta7ZzPde8rnTbruLEbdw/sy4SBG1iIDy21qOd8xzRzsM6t0dYWzj+zonGtEona0G8DpQD3wDjgb7Afb4uyviYpy/8w3mu1wUnvq442vBjB4VCUAgEu55rXgc7605Y7nodGuF67do2ONTtWCG1Xoe4HavWcStKoaTY7VHkPBduObas7FD9P4ME1x8gEe2OhY2nR2ikBYxpEU4WEH1VdQCAiLwK/OD7kkyjlR12vsgPF7i+2PNqffnXel3XF35IOES2hyjXo32vY68j20NUout1gvNXdu0v8qDg5vHFWFkOpQfh6P5aYeIhWKof+XuPvS4/Uv/xg0JPDI2YZGjf03kk9oK4NOf3ZUwAO1lA1LTjVbVCGvk/v4iMA54FgoFXVPWJWusfAq5zq6UPkAhEAW8AnYAqYIqqPtuoD28JDu6F3cua5gs/KhHCoprHF/zpCg6FyHjncSoqSqHkQB1hUnRi2Bwtgk2zYdlbbjWEQcKZxwKjOjza93BaU8YEAFGte8YMEank2FlLAkQAR1yvVVVj6tk3GNgAXAjkAIuASaq6po7tJwAPqOr5IpIEJKnqUhGJBpYAl9e1b7WsrCxdvHhxfZs0H0eL4LlBcLTw2LLaX/gnvG6lX/jNxdEiyN/gPPLWH3su2u4MuAMg0C71WGgk9nKCvX2PUw80Y+ohIktUNcvTunpbEKp6Om3gocAmVd3iKuId4DKgri/5ScDbrs/dDex2vT4oImtxZpCtNyBalAUvOuHws7egYz/7wm8JItpBl6HOw115CRRsgvz1kL/xWHhsyYbK0mPbRSU6YZHY8/jnmGT7d2F8ojH3g2iszsBOt/c5wDBPG4pIJDAOuMfDujRgELDQ+yUGqKP7YcEL0PtS6HOpv6sxvhYaDp36Ow93VZVO6yLP1erIX++8XvWB03VVLayt08I4Ljx6QVw3Z1DfmFPky389nv6kqas/awIwX1UL3ReKSFvgA+B+VT3g8UNEJgOTAVJTW8jF3t+/AKUHYPSj/q7E+FNQMMR3dx69xh1brgqH9rlaHBtcAbIets6DFe+47R/q7OseGtXjHGFRTf/zmGbHlwGRA3Rxe58C5Nax7TW4upeqiUgoTji8paof1vUhqjoFmALOGMTpFBwQjhQ63Ut9Jp74F6Ux4HQnRXd0Ht3OPX5dyQGnm8o9PPathXUzQSuPbReb6gRFwpkQ1xXadT32HF7n0KJpZXwZEIuAHiLSDdiFEwLX1t5IRGKBUcD1bssEeBVYq6pP+7DGwPP981B2EEY/4u9KTHMUHgMpg52Hu4pS51qQvPXHAiRvPez8wfn3dtwx2p0YGnFpzuB5u1Q7y6oV8VlAuE6LvQf4Euc016mqulpE7nStf8m16RXALFU97Lb7SOAGYKWILHMt+5WqzvRVvQHhSCEsfAn6Xu4MTBvjLSFtoEMf5+FO1Rnz2r/NGe/Yvx2Kdjiv962FDV8eP1AO0LajW3ikHh8ksSnOacSmRaj3NNfmptmf5jr7N/DtX+Du70/8H9kYf6iqgkN7ncAo2uEKkG2u5+1QvOv4risJgpgUJzhOaIV0hbadbJ6sAHPKp7maJnS4AH6YAv2usHAwgSMoCGKSnEfq8BPXV1bAgV1urQ+3581fw8Hdx28fHAaxXWqFRyq0S3NeRybYKbsBxAIiUHz3nDNlxqiH/V2JMQ0XHOJ8scd1hW4e1peXQPHOWi0PVxdW7rLjLwQFCI1yjhWb4nRlRSe5BuSTnNZHdCdo28G6sZqIBUQgOJwPP7wM/a+EDr39XY0x3hMa7rpGo4fn9SUHjgVGTRfWdijOgd3LndN5Tzg7XpyWRk14dDoWHu6v23Z0pn83p8wCIhDMf9aZT8laD6a1CY/xfJFgtcoKZy6yg7udsZCDe5zHoT3HXu9d7ayrma7ETUR8rVaIW6vEPUhCw337czZTFhD+digPFr0C/X/qXNBkjDkmOOTYGEh9qiqdlnh9QZK33nl2H1SvFhHnCox6giS6U6s7xdcCwt/mPwMVJdZ6MOZ0BAUfu3iwPlVVcKSg/iAp+NZ59nRTqsj2kNjbuSq9Qx/nObGPM0lmCxxct4Dwp4N7YdGrMOBqaH+mv6sxpuULCoK2ic6jPlVVzvUhB3e7wmOv83r/NqclsvI9ZzqcahHxxwdGYi8nSNp2aNbBYQHhT/OfhcoyGPVf/q7EGOMuKAiiEpwHHsZHVJ3AyFsH+9Y5z3nrYOUHUOo2kWJEnKvF4Xp0cD237dgsgsMCwl8O7oHFr0L6zyDhDH9XY4xpDBFnmvWYZDjj/GPLVZ2uq31rnZZGnut59T+dm0lVC2/nuasqulNABYcFhL98+xfn1pejHvJ3JcYYbxE5NqB9xnnHllfPwFsdGNUtj7Ufw9LXj20XHnssONy7qvx0zw8LCH84kAuLp0HGJGc6ZmNMy+Y+A2/30ceWqzqn8dbuqlr7KSx949h2bWKOhYV7V1VMZ58GhwWEP3z7F+dUu3Mf9Hclxhh/EnEGstt2OHHq9sP5rq6q6uBYD+s/hx+nH9smLNoJjo79YMKzXg8LC4imVrwLlrzmaj14mpvAGGNwTp3tdo7zcHc4//jxjerxDh+0JCwgmtq3TztXfJ5rYw/GmFMQ1d55pI30+UfZvLtNqTjH6VcceJ0zIZkxxgQwC4im9M2fnUEpG3swxjQDFhBNpWgHLJ0Og6535r83xpgAZwHRVL75s/N8zn/6tw5jjGkgC4imsH87/PgmZN4I7br4uxpjjGkQC4im8M1Tzr16rfVgjGlGLCB8bf82WPYPyPwPiO3s72qMMabBLCB8bd6TIMFwzi/9XYkxxjSKBYQvFW6BZW/D4JucybaMMaYZsYDwpXlPQXAonP2AvysxxphG82lAiMg4EVkvIptE5BEP6x8SkWWuxyoRqRSR+IbsG/AKNsPyd2DwzSe/n64xxgQgnwWEiAQDzwPjgb7AJBHp676Nqj6pqgNVdSDwKJCtqoUN2TfgzXsSgsOs9WCMabZ82YIYCmxS1S2qWga8A1xWz/aTgLdPcd/Akr8JVsyAIbee/CbqxhgToHwZEJ2BnW7vc1zLTiAikcA44INT2HeyiCwWkcV5eXmnXbRXzPsTBLeBkff5uxJjjDllvgwIT5OTax3bTgDmq2phY/dV1SmqmqWqWYmJiadQppflb4SV78HQ25ybgBhjTDPly4DIAdznlUgBcuvY9hqOdS81dt/Akv1HCAmHs6z1YIxp3nwZEIuAHiLSTUTCcELg49obiUgsMAr4qLH7Bpy89bDyfRh6O7QNgNaMMcacBp/dUU5VK0TkHuBLIBiYqqqrReRO1/qXXJteAcxS1cMn29dXtXpN9h8hNNJaD8aYFsGntxxV1ZnAzFrLXqr1/jXgtYbsG9D2rYVVH8LZ90NUgr+rMcaY02ZXUntL9h8hLArO+oW/KzHGGK+wgPCGvWtg9b9g2B0QGe/vaowxxissILwh+wkIawsj7vF3JcYY4zUWEKdrzypY8xEMv9NaD8aYFsUC4nRlPwFtYmDEz/1diTHGeJUFxOnYvQLWfgLD74KIOH9XY4wxXmUBcTqy/whtYmH43f6uxBhjvM4C4lTtXg7rPoURd0NEO39XY4wxXmcBcarmPgHhsU73kjHGtEAWEKci90dYP9M5rTU81t/VGGOMT1hAnIq5T0B4Oxh2p78rMcYYn7GAaKxdS2DDF3DWPRAe4+9qjDHGZywgGmvuE84prUPv8HclxhjjUxYQjZGzGDbOgrPutdaDMabFs4BojLl/gIh4GDrZ35UYY4zPWUA01M4fYNNsGPkLaBPt72qMMcbnLCAaau4fIDIBhtzu70qMMaZJWEA0xI6FsPlrGHkftGnr72qMMaZJWEA0xNzfQ1QiDLnN35UYY0yTsYA4me3fw5a5TushLMrf1RhjTJOxgDiZub+HqA6Qdau/KzHGmCZlAVGfbfNh6zw4+34Ii/R3NcYY06QsIOoz9w/QtiNk3eLvSowxpsn5NCBEZJyIrBeRTSLySB3bjBaRZSKyWkSy3ZY/4Fq2SkTeFpFwX9Z6gq3fwLZv4OwHIDSiST/aGGMCgc8CQkSCgeeB8UBfYJKI9K21TTvgBWCiqvYDrnIt7wz8AshS1f5AMHCNr2o9gaqr9dAJBt/UZB9rjDGBxJctiKHAJlXdoqplwDvAZbW2uRb4UFV3AKjqPrd1IUCEiIQAkUCuD2s93tZ5sH0+nPNLaz0YY1otXwZEZ2Cn2/sc1zJ3PYE4EZkrIktE5EYAVd0FPAXsAHYDxao6y4e1HlPdeohOhsz/aJKPNMaYQOTLgBAPy7TW+xBgMHAJcBHwPyLSU0TicFob3YBkIEpErvf4ISKTRWSxiCzOy8s7/aq3zIUd37taD0077GGMMYHElwGRA3Rxe5/Cid1EOcAXqnpYVfOBeUAGcAGwVVXzVLUc+BA4y9OHqOoUVc1S1azExMTTq7i69RDTGTJvPL1jGWNMM+fLgFgE9BCRbiIShjPI/HGtbT4CzhGREBGJBIYBa3G6loaLSKSICDDGtdy3Nn8NOxc6rYeQNj7/OGOMCWQhvjqwqlaIyD3AlzhnIU1V1dUicqdr/UuqulZEvgBWAFXAK6q6CkBE3geWAhXAj8AUX9XqKtjVekiBQTf49KOMMaY5ENXawwLNV1ZWli5evPjUdt44G966Ei79i10YZ4xpNURkiapmeVpnV1KDq/Xwe4hNhYEex8KNMabVsYAA2PgV7FoC5/4nhIT5uxpjjAkIFhDVrYd2qTDwOn9XY4wxAcNng9TNRukB14R8t0JwqL+rMcaYgGEBER4L185wWhLGGGNqWBdTNfF04bcxxrReFhDGGGM8soAwxhjjkQWEMcYYjywgjDHGeGQBYYwxxiMLCGOMMR5ZQBhjjPGoRc3mKiJ5wPZT3L09kO/FcrzF6mocq6txrK7GaYl1dVVVj3dba1EBcTpEZHFdU976k9XVOFZX41hdjdPa6rIuJmOMMR5ZQBhjjPHIAuIY397S9NRZXY1jdTWO1dU4raouG4MwxhjjkbUgjDHGeNTqA0JEporIPhFZ5e9aqolIFxGZIyJrRWS1iNzn75oARCRcRH4QkeWuun7j75rciUiwiPwoIp/6uxZ3IrJNRFaKyDIRWezveqqJSDsReV9E1rn+rY0IgJp6uX5P1Y8DInK/v+sCEJEHXP/uV4nI2yIS7u+aAETkPldNq739u2r1XUwici5wCHhDVfv7ux4AEUkCklR1qYhEA0uAy1V1jZ/rEiBKVQ+JSCjwLXCfqi7wZ13VROSXQBYQo6qX+rueaiKyDchS1YA6f15EXge+UdVXRCQMiFTVIj+XVUNEgoFdwDBVPdXrm7xVS2ecf+99VfWoiLwLzFTV1/xcV3/gHWAoUAZ8Adylqhu9cfxW34JQ1XlAob/rcKequ1V1qev1QWAt0Nm/VYE6DrnehroeAfEXhoikAJcAr/i7luZARGKAc4FXAVS1LJDCwWUMsNnf4eAmBIgQkRAgEsj1cz0AfYAFqnpEVSuAbOAKbx281QdEoBORNGAQsNDPpQA13TjLgH3AV6oaEHUBzwD/BVT5uQ5PFJglIktEZLK/i3HpDuQB01zdcq+ISJS/i6rlGuBtfxcBoKq7gKeAHcBuoFhVZ/m3KgBWAeeKSIKIRAIXA128dXALiAAmIm2BD4D7VfWAv+sBUNVKVR0IpABDXU1cvxKRS4F9qrrE37XUYaSqZgLjgZ+7ujX9LQTIBF5U1UHAYeAR/5Z0jKvLayLwnr9rARCROOAyoBuQDESJyPX+rQpUdS3wR+ArnO6l5UCFt45vARGgXH38HwBvqeqH/q6nNld3xFxgnH8rAWAkMNHV1/8OcL6IvOnfko5R1VzX8z7gnzj9xf6WA+S4tQDfxwmMQDEeWKqqe/1diMsFwFZVzVPVcuBD4Cw/1wSAqr6qqpmqei5Od7lXxh/AAiIguQaDXwXWqurT/q6nmogkikg71+sInP9p1vm1KEBVH1XVFFVNw+mW+FpV/f7XHYCIRLlONMDVhTMWp1vAr1R1D7BTRHq5Fo0B/HoSRC2TCJDuJZcdwHARiXT9/zkGZ2zQ70Skg+s5FfgJXvy9hXjrQM2ViLwNjAbai0gO8LiqvurfqhgJ3ACsdPX3A/xKVWf6ryQAkoDXXWeXBAHvqmpAnVIagDoC/3S+UwgB/qGqX/i3pBr3Am+5unO2ADf7uR4AXH3pFwJ3+LuWaqq6UETeB5bidOH8SOBcVf2BiCQA5cDPVXW/tw7c6k9zNcYY45l1MRljjPHIAsIYY4xHFhDGGGM8soAwxhjjkQWEMcYYjywgjDHGeGQBYYyXiEiy61z5k213qI7lr4nIT71fmTGnxgLCGC9R1VxV9csXvGuGUWO8ygLCtCoikua6Oc7LrhuszHJNG+Jp27ki8kfXTZI2iMg5ruXBIvKkiCwSkRUicofbsVe5XkeKyLuu9TNEZKGIZLkd+3euGy8tEJGObh97gYh84/q8S13bhovINNdNh34UkfNcy28SkfdE5BOc2WKTRGSe60Y7q6rrNeZUWUCY1qgH8Lyq9gOKgCvr2TZEVYcC9wOPu5bdijPd8xBgCHC7iHSrtd/dwH5VTQd+Cwx2WxeFM4d/BjAPuN1tXRowCufeFi+57lr2cwBVHYAzR9HrbnczGwH8h6qeD1wLfOmabTcDWHayX4Qx9bFmqWmNtqrqMtfrJThfynX50MN2Y4F0t/GCWJzQ2eC239nAswCqukpEVritKwOq57BagjPvULV3VbUK2CgiW4DermP91XWsdSKyHejp2v4rVa2+4dUiYKprJuB/uf2MxpwSa0GY1qjU7XUl9f+hVOphOwHuVdWBrkc3DzePkXqOWa7HJkGr/fm1J0fTkxzrcM2Gzt0Rz8W5Ted0Ebmxnv2MOSkLCGMa70vgLtdf6ohITw93Y/sWuNq1vi8woIHHvkpEgkTkDJy7vq3H6Ya6rvqzgFTX8uOISFecGye9jDNdfCDd38E0Q9bFZEzjvYLT3bTUdW+APODyWtu8gDNWsAJnaugVQHEDjr0e577CHYE7VbVERF7AGY9YiTPV9E2qWuqaQtzdaOAhESkHDgHWgjCnxab7NsYHXPfMCHV9wZ8B/Bvoqaplfi7NmAazFoQxvhEJzHF1Qwlwl4WDaW6sBWFaPRF5Hucufu6eVdVp/qjHmEBhAWGMMcYjO4vJGGOMRxYQxhhjPLKAMMYY45EFhDHGGI8sIIwxxnj0/wFAQvyZ0R7HvQAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "training_precision = []\n",
    "test_precision = []\n",
    "# try n_neighbors from 1 to 10\n",
    "neighbors_settings = range(1, 10)\n",
    "for n_neighbors in neighbors_settings:\n",
    "    # build the model\n",
    "    knn = KNeighborsRegressor(n_neighbors=n_neighbors)\n",
    "    knn.fit(X_train, y_train)\n",
    "    \n",
    "    # record training set precision\n",
    "    training_precision.append(knn.score(X_train, y_train))\n",
    "    # record test set precision\n",
    "    test_precision.append(knn.score(X_test, y_test))\n",
    "\n",
    "plt.plot(neighbors_settings, training_precision, label=\"training accuracy\")\n",
    "plt.plot(neighbors_settings, test_precision, label=\"test accuracy\")\n",
    "plt.ylabel(\"Precision\")\n",
    "plt.xlabel(\"n_neighbors\")\n",
    "plt.legend()\n",
    "plt.savefig(\"knn_compare_model\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d0e4b32d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Precision of K-NN regressor on training set: 0.796\n",
      "Precision of K-NN regressor on test set: 0.721\n"
     ]
    }
   ],
   "source": [
    "knn = KNeighborsRegressor(n_neighbors=2)\n",
    "knn.fit(X_train, y_train)\n",
    "print(\"Precision of K-NN regressor on training set: {:.3f}\".format(knn.score(X_train, y_train)))\n",
    "print(\"Precision of K-NN regressor on test set: {:.3f}\".format(knn.score(X_test, y_test)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02fd7c74",
   "metadata": {},
   "source": [
    "### Decision Tree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1553536c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Precision of Decision Tree regressor on training set: 0.815\n",
      "Precision of Decision Tree regressor on test set: 0.719\n"
     ]
    }
   ],
   "source": [
    "dt = DecisionTreeRegressor(random_state=42)\n",
    "dt.fit(X_train, y_train)\n",
    "print(\"Precision of Decision Tree regressor on training set: {:.3f}\".format(dt.score(X_train, y_train)))\n",
    "print(\"Precision of Decision Tree regressor on test set: {:.3f}\".format(dt.score(X_test, y_test)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f94c2e58",
   "metadata": {},
   "source": [
    "### Random Forest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "60fb57cf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Precision of Random Forest regressor on training set: 0.807\n",
      "Precision of Random Forest regressor on test set: 0.723\n"
     ]
    }
   ],
   "source": [
    "rf = RandomForestRegressor(n_estimators=100, random_state=42)\n",
    "rf.fit(X_train, y_train)\n",
    "print(\"Precision of Random Forest regressor on training set: {:.3f}\".format(rf.score(X_train, y_train)))\n",
    "print(\"Precision of Random Forest regressor on test set: {:.3f}\".format(rf.score(X_test, y_test)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5a9a09c",
   "metadata": {},
   "source": [
    "### Evaluation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "63105add",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_regressor = regressor.predict(X_test)\n",
    "regressor_r = regressor.score(X_test, y_test)\n",
    "regressor_mse = mean_squared_error(y_pred_regressor, y_test)\n",
    "regressor_rmse = np.sqrt(regressor_mse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b1e897b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_knn = knn.predict(X_test)\n",
    "knn_r = knn.score(X_test, y_test)\n",
    "knn_mse = mean_squared_error(y_pred_knn, y_test)\n",
    "knn_rmse = np.sqrt(knn_mse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7b0650ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_dt = dt.predict(X_test)\n",
    "dt_r = dt.score(X_test, y_test)\n",
    "dt_mse = mean_squared_error(y_pred_dt, y_test)\n",
    "dt_rmse = np.sqrt(dt_mse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "baafdb73",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_rf = rf.predict(X_test)\n",
    "rf_r = rf.score(X_test, y_test)\n",
    "rf_mse = mean_squared_error(y_pred_rf, y_test)\n",
    "rf_rmse = np.sqrt(rf_mse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "44a8f6d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Algorithms</th>\n",
       "      <th>Tests RMSE</th>\n",
       "      <th>Tests R</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Random Forest</td>\n",
       "      <td>118.769960</td>\n",
       "      <td>0.722936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K-Nearest Neighbors</td>\n",
       "      <td>119.114519</td>\n",
       "      <td>0.721326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Decision Tree</td>\n",
       "      <td>119.695503</td>\n",
       "      <td>0.718601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Linear Regression</td>\n",
       "      <td>129.320140</td>\n",
       "      <td>0.671527</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Algorithms  Tests RMSE   Tests R\n",
       "3        Random Forest  118.769960  0.722936\n",
       "1  K-Nearest Neighbors  119.114519  0.721326\n",
       "2        Decision Tree  119.695503  0.718601\n",
       "0    Linear Regression  129.320140  0.671527"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "models = [\"Linear Regression\", \"K-Nearest Neighbors\", \"Decision Tree\", \"Random Forest\"]\n",
    "tests_rmse = [regressor_rmse, knn_rmse, dt_rmse, rf_rmse]\n",
    "tests_r = [regressor_r, knn_r, dt_r, rf_r]\n",
    "compare_models = pd.DataFrame({ \"Algorithms\": models, \"Tests RMSE\": tests_rmse, \"Tests R\":tests_r })\n",
    "compare_models.sort_values(by = \"Tests RMSE\", ascending = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b737a194",
   "metadata": {},
   "source": [
    "### Overall random forest have higher precision and lowest RMSE\n",
    "We can use random forest for further calculation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24161bc5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "5c02b6f1",
   "metadata": {},
   "source": [
    "## Part 2: predict next purchase date\n",
    "\n",
    "Using RFM scores and historical purchase behavior\n",
    "\n",
    "In Segmentation.ipynb we already put customers into clusters using KNN:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "340dc35b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>Recency</th>\n",
       "      <th>Frequency</th>\n",
       "      <th>Monetary</th>\n",
       "      <th>Cluster</th>\n",
       "      <th>RFM_Level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>113</td>\n",
       "      <td>1</td>\n",
       "      <td>141.90</td>\n",
       "      <td>0</td>\n",
       "      <td>Promising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>116</td>\n",
       "      <td>1</td>\n",
       "      <td>27.19</td>\n",
       "      <td>0</td>\n",
       "      <td>Promising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>538</td>\n",
       "      <td>1</td>\n",
       "      <td>86.22</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>322</td>\n",
       "      <td>1</td>\n",
       "      <td>43.62</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>289</td>\n",
       "      <td>1</td>\n",
       "      <td>196.89</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95768</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>448</td>\n",
       "      <td>1</td>\n",
       "      <td>2067.42</td>\n",
       "      <td>2</td>\n",
       "      <td>Potential Loyalist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95769</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "      <td>84.58</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95770</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>569</td>\n",
       "      <td>1</td>\n",
       "      <td>112.46</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95771</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>120</td>\n",
       "      <td>1</td>\n",
       "      <td>133.69</td>\n",
       "      <td>0</td>\n",
       "      <td>Promising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95772</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>485</td>\n",
       "      <td>1</td>\n",
       "      <td>71.56</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95773 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id  Recency  Frequency  Monetary  \\\n",
       "0      0000366f3b9a7992bf8c76cfdf3221e2      113          1    141.90   \n",
       "1      0000b849f77a49e4a4ce2b2a4ca5be3f      116          1     27.19   \n",
       "2      0000f46a3911fa3c0805444483337064      538          1     86.22   \n",
       "3      0000f6ccb0745a6a4b88665a16c9f078      322          1     43.62   \n",
       "4      0004aac84e0df4da2b147fca70cf8255      289          1    196.89   \n",
       "...                                 ...      ...        ...       ...   \n",
       "95768  fffcf5a5ff07b0908bd4e2dbc735a684      448          1   2067.42   \n",
       "95769  fffea47cd6d3cc0a88bd621562a9d061      263          1     84.58   \n",
       "95770  ffff371b4d645b6ecea244b27531430a      569          1    112.46   \n",
       "95771  ffff5962728ec6157033ef9805bacc48      120          1    133.69   \n",
       "95772  ffffd2657e2aad2907e67c3e9daecbeb      485          1     71.56   \n",
       "\n",
       "       Cluster           RFM_Level  \n",
       "0            0           Promising  \n",
       "1            0           Promising  \n",
       "2            3           Champions  \n",
       "3            3           Champions  \n",
       "4            3           Champions  \n",
       "...        ...                 ...  \n",
       "95768        2  Potential Loyalist  \n",
       "95769        3           Champions  \n",
       "95770        3           Champions  \n",
       "95771        0           Promising  \n",
       "95772        3           Champions  \n",
       "\n",
       "[95773 rows x 6 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_segment = pd.read_csv(\"segment_data.csv\")\n",
    "df_segment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "45fbada1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>Recency</th>\n",
       "      <th>Frequency</th>\n",
       "      <th>Monetary</th>\n",
       "      <th>Cluster</th>\n",
       "      <th>RFM_Level</th>\n",
       "      <th>CLV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>113</td>\n",
       "      <td>1</td>\n",
       "      <td>141.90</td>\n",
       "      <td>0</td>\n",
       "      <td>Promising</td>\n",
       "      <td>141.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>116</td>\n",
       "      <td>1</td>\n",
       "      <td>27.19</td>\n",
       "      <td>0</td>\n",
       "      <td>Promising</td>\n",
       "      <td>27.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>538</td>\n",
       "      <td>1</td>\n",
       "      <td>86.22</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "      <td>86.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>322</td>\n",
       "      <td>1</td>\n",
       "      <td>43.62</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>289</td>\n",
       "      <td>1</td>\n",
       "      <td>196.89</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "      <td>196.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95768</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>448</td>\n",
       "      <td>1</td>\n",
       "      <td>2067.42</td>\n",
       "      <td>2</td>\n",
       "      <td>Potential Loyalist</td>\n",
       "      <td>2067.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95769</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "      <td>84.58</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "      <td>84.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95770</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>569</td>\n",
       "      <td>1</td>\n",
       "      <td>112.46</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "      <td>112.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95771</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>120</td>\n",
       "      <td>1</td>\n",
       "      <td>133.69</td>\n",
       "      <td>0</td>\n",
       "      <td>Promising</td>\n",
       "      <td>133.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95772</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>485</td>\n",
       "      <td>1</td>\n",
       "      <td>71.56</td>\n",
       "      <td>3</td>\n",
       "      <td>Champions</td>\n",
       "      <td>71.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95773 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id  Recency  Frequency  Monetary  \\\n",
       "0      0000366f3b9a7992bf8c76cfdf3221e2      113          1    141.90   \n",
       "1      0000b849f77a49e4a4ce2b2a4ca5be3f      116          1     27.19   \n",
       "2      0000f46a3911fa3c0805444483337064      538          1     86.22   \n",
       "3      0000f6ccb0745a6a4b88665a16c9f078      322          1     43.62   \n",
       "4      0004aac84e0df4da2b147fca70cf8255      289          1    196.89   \n",
       "...                                 ...      ...        ...       ...   \n",
       "95768  fffcf5a5ff07b0908bd4e2dbc735a684      448          1   2067.42   \n",
       "95769  fffea47cd6d3cc0a88bd621562a9d061      263          1     84.58   \n",
       "95770  ffff371b4d645b6ecea244b27531430a      569          1    112.46   \n",
       "95771  ffff5962728ec6157033ef9805bacc48      120          1    133.69   \n",
       "95772  ffffd2657e2aad2907e67c3e9daecbeb      485          1     71.56   \n",
       "\n",
       "       Cluster           RFM_Level      CLV  \n",
       "0            0           Promising   141.90  \n",
       "1            0           Promising    27.19  \n",
       "2            3           Champions    86.22  \n",
       "3            3           Champions    43.62  \n",
       "4            3           Champions   196.89  \n",
       "...        ...                 ...      ...  \n",
       "95768        2  Potential Loyalist  2067.42  \n",
       "95769        3           Champions    84.58  \n",
       "95770        3           Champions   112.46  \n",
       "95771        0           Promising   133.69  \n",
       "95772        3           Champions    71.56  \n",
       "\n",
       "[95773 rows x 7 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_m2 = df_segment.merge(SALES[[\"customer_unique_id\", \"CLV\"]], how = \"left\", on = \"customer_unique_id\")\n",
    "df_m2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "ad453aca",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m2.drop([\"RFM_Level\"], axis = \"columns\", inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "09038e4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#plt.figure(figsize = (12,10))\n",
    "#sns.heatmap(df_m2.corr(), annot = True, linewidths=0.2, fmt=\".2f\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77b4e1ee",
   "metadata": {},
   "source": [
    "Find the days that each customer made purchase. If a customer made multiple purchase on the same day, keep the first one and drop the others."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "6be3e85f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52618</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>2018-05-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73628</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>2018-05-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26370</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>2017-03-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98148</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>2017-10-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41416</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>2017-11-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86701</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>2017-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20486</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>2017-12-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43791</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>2017-02-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96516</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>2018-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69771</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>2017-05-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98799 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id order_purchase_timestamp\n",
       "52618  0000366f3b9a7992bf8c76cfdf3221e2               2018-05-10\n",
       "73628  0000b849f77a49e4a4ce2b2a4ca5be3f               2018-05-07\n",
       "26370  0000f46a3911fa3c0805444483337064               2017-03-10\n",
       "98148  0000f6ccb0745a6a4b88665a16c9f078               2017-10-12\n",
       "41416  0004aac84e0df4da2b147fca70cf8255               2017-11-14\n",
       "...                                 ...                      ...\n",
       "86701  fffcf5a5ff07b0908bd4e2dbc735a684               2017-06-08\n",
       "20486  fffea47cd6d3cc0a88bd621562a9d061               2017-12-10\n",
       "43791  ffff371b4d645b6ecea244b27531430a               2017-02-07\n",
       "96516  ffff5962728ec6157033ef9805bacc48               2018-05-02\n",
       "69771  ffffd2657e2aad2907e67c3e9daecbeb               2017-05-02\n",
       "\n",
       "[98799 rows x 2 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "day_order = df_all[['customer_unique_id','order_purchase_timestamp']]\n",
    "day_order = day_order.sort_values(['customer_unique_id','order_purchase_timestamp'])\n",
    "day_order = day_order.drop_duplicates(subset=['customer_unique_id','order_purchase_timestamp'],keep='first')\n",
    "day_order['order_purchase_timestamp'] = day_order['order_purchase_timestamp'].dt.date\n",
    "day_order"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4fdc2029",
   "metadata": {},
   "source": [
    "Create new columns with the dates of last 2 purchases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "1fb119c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>date1</th>\n",
       "      <th>date2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52618</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>2018-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73628</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>2018-05-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26370</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>2017-03-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98148</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>2017-10-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41416</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>2017-11-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86701</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>2017-06-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20486</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>2017-12-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43791</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>2017-02-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96516</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>2018-05-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69771</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98799 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id order_purchase_timestamp date1 date2\n",
       "52618  0000366f3b9a7992bf8c76cfdf3221e2               2018-05-10   NaN   NaN\n",
       "73628  0000b849f77a49e4a4ce2b2a4ca5be3f               2018-05-07   NaN   NaN\n",
       "26370  0000f46a3911fa3c0805444483337064               2017-03-10   NaN   NaN\n",
       "98148  0000f6ccb0745a6a4b88665a16c9f078               2017-10-12   NaN   NaN\n",
       "41416  0004aac84e0df4da2b147fca70cf8255               2017-11-14   NaN   NaN\n",
       "...                                 ...                      ...   ...   ...\n",
       "86701  fffcf5a5ff07b0908bd4e2dbc735a684               2017-06-08   NaN   NaN\n",
       "20486  fffea47cd6d3cc0a88bd621562a9d061               2017-12-10   NaN   NaN\n",
       "43791  ffff371b4d645b6ecea244b27531430a               2017-02-07   NaN   NaN\n",
       "96516  ffff5962728ec6157033ef9805bacc48               2018-05-02   NaN   NaN\n",
       "69771  ffffd2657e2aad2907e67c3e9daecbeb               2017-05-02   NaN   NaN\n",
       "\n",
       "[98799 rows x 4 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "day_order['date1']= day_order.groupby('customer_unique_id')['order_purchase_timestamp'].shift(1)\n",
    "day_order['date2'] = day_order.groupby('customer_unique_id')['order_purchase_timestamp'].shift(2)\n",
    "day_order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c4931d8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>order_purchase_timestamp</th>\n",
       "      <th>date1</th>\n",
       "      <th>date2</th>\n",
       "      <th>DayDiff1</th>\n",
       "      <th>DayDiff2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52618</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>2018-05-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73628</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>2018-05-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26370</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>2017-03-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98148</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>2017-10-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41416</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>2017-11-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86701</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>2017-06-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20486</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>2017-12-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43791</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>2017-02-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96516</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>2018-05-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69771</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98799 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id order_purchase_timestamp date1 date2  \\\n",
       "52618  0000366f3b9a7992bf8c76cfdf3221e2               2018-05-10   NaN   NaN   \n",
       "73628  0000b849f77a49e4a4ce2b2a4ca5be3f               2018-05-07   NaN   NaN   \n",
       "26370  0000f46a3911fa3c0805444483337064               2017-03-10   NaN   NaN   \n",
       "98148  0000f6ccb0745a6a4b88665a16c9f078               2017-10-12   NaN   NaN   \n",
       "41416  0004aac84e0df4da2b147fca70cf8255               2017-11-14   NaN   NaN   \n",
       "...                                 ...                      ...   ...   ...   \n",
       "86701  fffcf5a5ff07b0908bd4e2dbc735a684               2017-06-08   NaN   NaN   \n",
       "20486  fffea47cd6d3cc0a88bd621562a9d061               2017-12-10   NaN   NaN   \n",
       "43791  ffff371b4d645b6ecea244b27531430a               2017-02-07   NaN   NaN   \n",
       "96516  ffff5962728ec6157033ef9805bacc48               2018-05-02   NaN   NaN   \n",
       "69771  ffffd2657e2aad2907e67c3e9daecbeb               2017-05-02   NaN   NaN   \n",
       "\n",
       "       DayDiff1  DayDiff2  \n",
       "52618       NaN       NaN  \n",
       "73628       NaN       NaN  \n",
       "26370       NaN       NaN  \n",
       "98148       NaN       NaN  \n",
       "41416       NaN       NaN  \n",
       "...         ...       ...  \n",
       "86701       NaN       NaN  \n",
       "20486       NaN       NaN  \n",
       "43791       NaN       NaN  \n",
       "96516       NaN       NaN  \n",
       "69771       NaN       NaN  \n",
       "\n",
       "[98799 rows x 6 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "day_order['DayDiff1'] = (day_order['order_purchase_timestamp'] - day_order['date1']).dt.days\n",
    "day_order['DayDiff2'] = (day_order['order_purchase_timestamp'] - day_order['date2']).dt.days\n",
    "day_order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "56df35f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>DayDiffMean</th>\n",
       "      <th>DayDiffStd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95768</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95769</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95770</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95771</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95772</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95773 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id  DayDiffMean  DayDiffStd\n",
       "0      0000366f3b9a7992bf8c76cfdf3221e2          NaN         NaN\n",
       "1      0000b849f77a49e4a4ce2b2a4ca5be3f          NaN         NaN\n",
       "2      0000f46a3911fa3c0805444483337064          NaN         NaN\n",
       "3      0000f6ccb0745a6a4b88665a16c9f078          NaN         NaN\n",
       "4      0004aac84e0df4da2b147fca70cf8255          NaN         NaN\n",
       "...                                 ...          ...         ...\n",
       "95768  fffcf5a5ff07b0908bd4e2dbc735a684          NaN         NaN\n",
       "95769  fffea47cd6d3cc0a88bd621562a9d061          NaN         NaN\n",
       "95770  ffff371b4d645b6ecea244b27531430a          NaN         NaN\n",
       "95771  ffff5962728ec6157033ef9805bacc48          NaN         NaN\n",
       "95772  ffffd2657e2aad2907e67c3e9daecbeb          NaN         NaN\n",
       "\n",
       "[95773 rows x 3 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "day_diff = day_order.groupby(['customer_unique_id']).agg({'DayDiff1':['mean','std']}).reset_index()\n",
    "day_diff.columns = ['customer_unique_id', 'DayDiffMean','DayDiffStd']\n",
    "day_diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "c328e3d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>date_min</th>\n",
       "      <th>date_max</th>\n",
       "      <th>NextPurchaseDay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000366f3b9a7992bf8c76cfdf3221e2</td>\n",
       "      <td>2018-05-10</td>\n",
       "      <td>2018-05-10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000b849f77a49e4a4ce2b2a4ca5be3f</td>\n",
       "      <td>2018-05-07</td>\n",
       "      <td>2018-05-07</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000f46a3911fa3c0805444483337064</td>\n",
       "      <td>2017-03-10</td>\n",
       "      <td>2017-03-10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000f6ccb0745a6a4b88665a16c9f078</td>\n",
       "      <td>2017-10-12</td>\n",
       "      <td>2017-10-12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0004aac84e0df4da2b147fca70cf8255</td>\n",
       "      <td>2017-11-14</td>\n",
       "      <td>2017-11-14</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95768</th>\n",
       "      <td>fffcf5a5ff07b0908bd4e2dbc735a684</td>\n",
       "      <td>2017-06-08</td>\n",
       "      <td>2017-06-08</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95769</th>\n",
       "      <td>fffea47cd6d3cc0a88bd621562a9d061</td>\n",
       "      <td>2017-12-10</td>\n",
       "      <td>2017-12-10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95770</th>\n",
       "      <td>ffff371b4d645b6ecea244b27531430a</td>\n",
       "      <td>2017-02-07</td>\n",
       "      <td>2017-02-07</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95771</th>\n",
       "      <td>ffff5962728ec6157033ef9805bacc48</td>\n",
       "      <td>2018-05-02</td>\n",
       "      <td>2018-05-02</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95772</th>\n",
       "      <td>ffffd2657e2aad2907e67c3e9daecbeb</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95773 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customer_unique_id    date_min    date_max  \\\n",
       "0      0000366f3b9a7992bf8c76cfdf3221e2  2018-05-10  2018-05-10   \n",
       "1      0000b849f77a49e4a4ce2b2a4ca5be3f  2018-05-07  2018-05-07   \n",
       "2      0000f46a3911fa3c0805444483337064  2017-03-10  2017-03-10   \n",
       "3      0000f6ccb0745a6a4b88665a16c9f078  2017-10-12  2017-10-12   \n",
       "4      0004aac84e0df4da2b147fca70cf8255  2017-11-14  2017-11-14   \n",
       "...                                 ...         ...         ...   \n",
       "95768  fffcf5a5ff07b0908bd4e2dbc735a684  2017-06-08  2017-06-08   \n",
       "95769  fffea47cd6d3cc0a88bd621562a9d061  2017-12-10  2017-12-10   \n",
       "95770  ffff371b4d645b6ecea244b27531430a  2017-02-07  2017-02-07   \n",
       "95771  ffff5962728ec6157033ef9805bacc48  2018-05-02  2018-05-02   \n",
       "95772  ffffd2657e2aad2907e67c3e9daecbeb  2017-05-02  2017-05-02   \n",
       "\n",
       "       NextPurchaseDay  \n",
       "0                    0  \n",
       "1                    0  \n",
       "2                    0  \n",
       "3                    0  \n",
       "4                    0  \n",
       "...                ...  \n",
       "95768                0  \n",
       "95769                0  \n",
       "95770                0  \n",
       "95771                0  \n",
       "95772                0  \n",
       "\n",
       "[95773 rows x 4 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "next_date = day_order.groupby('customer_unique_id').agg({'order_purchase_timestamp':[\"min\", \"max\"]}).reset_index()\n",
    "next_date.columns = ['customer_unique_id', 'date_min','date_max']\n",
    "next_date['NextPurchaseDay'] = (next_date['date_max'] - next_date['date_min']).dt.days\n",
    "next_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "265a23b0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_unique_id</th>\n",
       "      <th>Recency</th>\n",
       "      <th>Frequency</th>\n",
       "      <th>Monetary</th>\n",
       "      <th>Cluster</th>\n",
       "      <th>CLV</th>\n",
       "      <th>DayDiff1</th>\n",
       "      <th>DayDiff2</th>\n",
       "      <th>DayDiffMean</th>\n",
       "      <th>DayDiffStd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02e9109b7e0a985108b43e573b6afb23</td>\n",
       "      <td>109</td>\n",
       "      <td>3</td>\n",
       "      <td>602.73</td>\n",
       "      <td>1</td>\n",
       "      <td>602.73</td>\n",
       "      <td>1.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>120.208153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>041caba6a63ace5818387698ea85cdb2</td>\n",
       "      <td>99</td>\n",
       "      <td>3</td>\n",
       "      <td>153.46</td>\n",
       "      <td>0</td>\n",
       "      <td>153.46</td>\n",
       "      <td>188.0</td>\n",
       "      <td>435.0</td>\n",
       "      <td>217.5</td>\n",
       "      <td>41.719300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>043aee247e71edff7045664609f4d806</td>\n",
       "      <td>409</td>\n",
       "      <td>3</td>\n",
       "      <td>690.52</td>\n",
       "      <td>1</td>\n",
       "      <td>690.52</td>\n",
       "      <td>53.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>58.5</td>\n",
       "      <td>7.778175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>07b1b60bca2b6326fa2adb259ea4a55f</td>\n",
       "      <td>113</td>\n",
       "      <td>3</td>\n",
       "      <td>251.54</td>\n",
       "      <td>0</td>\n",
       "      <td>251.54</td>\n",
       "      <td>40.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>25.455844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>083ca1aa470c280236380973a48f77c6</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>320.26</td>\n",
       "      <td>1</td>\n",
       "      <td>320.26</td>\n",
       "      <td>362.0</td>\n",
       "      <td>373.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>203.521498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>fd8ccc89be43894d2553494c71a61fd8</td>\n",
       "      <td>134</td>\n",
       "      <td>3</td>\n",
       "      <td>258.03</td>\n",
       "      <td>0</td>\n",
       "      <td>258.03</td>\n",
       "      <td>37.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>19.5</td>\n",
       "      <td>24.748737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>fe59d5878cd80080edbd29b5a0a4e1cf</td>\n",
       "      <td>374</td>\n",
       "      <td>3</td>\n",
       "      <td>185.09</td>\n",
       "      <td>3</td>\n",
       "      <td>185.09</td>\n",
       "      <td>8.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>42.426407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>fe81bb32c243a86b2f86fbf053fe6140</td>\n",
       "      <td>71</td>\n",
       "      <td>5</td>\n",
       "      <td>1590.76</td>\n",
       "      <td>4</td>\n",
       "      <td>1590.76</td>\n",
       "      <td>7.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>60.5</td>\n",
       "      <td>60.649265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>ff36be26206fffe1eb37afd54c70e18b</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>278.28</td>\n",
       "      <td>0</td>\n",
       "      <td>278.28</td>\n",
       "      <td>3.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.142136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>ff922bdd6bafcdf99cb90d7f39cea5b3</td>\n",
       "      <td>351</td>\n",
       "      <td>3</td>\n",
       "      <td>139.60</td>\n",
       "      <td>3</td>\n",
       "      <td>139.60</td>\n",
       "      <td>22.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>113.137085</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>217 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customer_unique_id  Recency  Frequency  Monetary  Cluster  \\\n",
       "0    02e9109b7e0a985108b43e573b6afb23      109          3    602.73        1   \n",
       "1    041caba6a63ace5818387698ea85cdb2       99          3    153.46        0   \n",
       "2    043aee247e71edff7045664609f4d806      409          3    690.52        1   \n",
       "3    07b1b60bca2b6326fa2adb259ea4a55f      113          3    251.54        0   \n",
       "4    083ca1aa470c280236380973a48f77c6       17          4    320.26        1   \n",
       "..                                ...      ...        ...       ...      ...   \n",
       "212  fd8ccc89be43894d2553494c71a61fd8      134          3    258.03        0   \n",
       "213  fe59d5878cd80080edbd29b5a0a4e1cf      374          3    185.09        3   \n",
       "214  fe81bb32c243a86b2f86fbf053fe6140       71          5   1590.76        4   \n",
       "215  ff36be26206fffe1eb37afd54c70e18b        8          3    278.28        0   \n",
       "216  ff922bdd6bafcdf99cb90d7f39cea5b3      351          3    139.60        3   \n",
       "\n",
       "         CLV  DayDiff1  DayDiff2  DayDiffMean  DayDiffStd  \n",
       "0     602.73       1.0     172.0         86.0  120.208153  \n",
       "1     153.46     188.0     435.0        217.5   41.719300  \n",
       "2     690.52      53.0     117.0         58.5    7.778175  \n",
       "3     251.54      40.0      44.0         22.0   25.455844  \n",
       "4     320.26     362.0     373.0        127.0  203.521498  \n",
       "..       ...       ...       ...          ...         ...  \n",
       "212   258.03      37.0      39.0         19.5   24.748737  \n",
       "213   185.09       8.0      76.0         38.0   42.426407  \n",
       "214  1590.76       7.0     118.0         60.5   60.649265  \n",
       "215   278.28       3.0      26.0         13.0   14.142136  \n",
       "216   139.60      22.0     204.0        102.0  113.137085  \n",
       "\n",
       "[217 rows x 10 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "day_order_last = day_order.drop_duplicates(subset=['customer_unique_id'], keep='last')\n",
    "day_order_last = day_order_last.dropna()\n",
    "day_order_last = pd.merge(day_order_last, day_diff, on='customer_unique_id')\n",
    "df_m2 = df_m2.merge(day_order_last[['customer_unique_id','DayDiff1','DayDiff2',\n",
    "                                    'DayDiffMean','DayDiffStd']], on='customer_unique_id')\n",
    "df_m2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "1cc3eea8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m2 = df_m2.merge(next_date[['customer_unique_id', \"NextPurchaseDay\"]], how = \"left\", on = \"customer_unique_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "86ecc1d7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 217 entries, 0 to 216\n",
      "Data columns (total 11 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   customer_unique_id  217 non-null    object \n",
      " 1   Recency             217 non-null    int64  \n",
      " 2   Frequency           217 non-null    int64  \n",
      " 3   Monetary            217 non-null    float64\n",
      " 4   Cluster             217 non-null    int64  \n",
      " 5   CLV                 217 non-null    float64\n",
      " 6   DayDiff1            217 non-null    float64\n",
      " 7   DayDiff2            217 non-null    float64\n",
      " 8   DayDiffMean         217 non-null    float64\n",
      " 9   DayDiffStd          217 non-null    float64\n",
      " 10  NextPurchaseDay     217 non-null    int64  \n",
      "dtypes: float64(6), int64(4), object(1)\n",
      "memory usage: 20.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df_m2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "2e61d725",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    217.000000\n",
       "mean     165.811060\n",
       "std      137.539005\n",
       "min        0.000000\n",
       "25%       52.000000\n",
       "50%      134.000000\n",
       "75%      257.000000\n",
       "max      537.000000\n",
       "Name: NextPurchaseDay, dtype: float64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_m2[\"NextPurchaseDay\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e533552d",
   "metadata": {},
   "source": [
    "Based on distribution of time interval of 2 purchases, we decide that:\n",
    "- Class 2: Customers that might repurchase in 0–52 days\n",
    "- Class 1: Customers that might repurchase in 53–257 days\n",
    "- Class 0: Customers that might repurchase in more than 257 days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "f08dc57d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m2['NextPurchaseDayRange'] = 2\n",
    "df_m2.loc[df_m2[\"NextPurchaseDay\"] > 52,'NextPurchaseDayRange'] = 1\n",
    "df_m2.loc[df_m2[\"NextPurchaseDay\"] > 257,'NextPurchaseDayRange'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "0221808e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m2.drop(['NextPurchaseDay'], axis=1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "b229883f",
   "metadata": {},
   "outputs": [],
   "source": [
    "X, y = df_m2.drop(['customer_unique_id', 'NextPurchaseDayRange'],axis=1), df_m2[\"NextPurchaseDayRange\"]\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=44)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "19d7949f",
   "metadata": {},
   "outputs": [],
   "source": [
    "models = []\n",
    "models.append((\"LR\", LogisticRegression()))\n",
    "models.append((\"NB\", GaussianNB()))\n",
    "models.append((\"RF\", RandomForestClassifier()))\n",
    "models.append((\"SVC\", SVC()))\n",
    "models.append((\"Dtree\", DecisionTreeClassifier()))\n",
    "models.append((\"KNN\", KNeighborsClassifier()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "47a2d590",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LR [0.81034483 0.70689655 0.80701754]\n",
      "NB [0.84482759 0.79310345 0.98245614]\n",
      "RF [0.89655172 0.84482759 0.94736842]\n",
      "SVC [0.70689655 0.63793103 0.54385965]\n",
      "Dtree [0.89655172 0.82758621 0.92982456]\n",
      "KNN [0.72413793 0.79310345 0.77192982]\n"
     ]
    }
   ],
   "source": [
    "for name, model in models:\n",
    "    kfold = KFold(n_splits = 3)\n",
    "    cv_result = cross_val_score(model, X_train, y_train, cv = kfold, scoring = \"accuracy\")\n",
    "    print(name, cv_result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "6e005206",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======LR======\n",
      "\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.65      0.79      0.71        14\n",
      "           1       0.76      0.65      0.70        20\n",
      "           2       0.90      0.90      0.90        10\n",
      "\n",
      "    accuracy                           0.75        44\n",
      "   macro avg       0.77      0.78      0.77        44\n",
      "weighted avg       0.76      0.75      0.75        44\n",
      "\n",
      "======NB======\n",
      "\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.78      1.00      0.88        14\n",
      "           1       0.94      0.80      0.86        20\n",
      "           2       1.00      0.90      0.95        10\n",
      "\n",
      "    accuracy                           0.89        44\n",
      "   macro avg       0.91      0.90      0.90        44\n",
      "weighted avg       0.90      0.89      0.89        44\n",
      "\n",
      "======RF======\n",
      "\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       1.00      1.00      1.00        14\n",
      "           1       0.95      1.00      0.98        20\n",
      "           2       1.00      0.90      0.95        10\n",
      "\n",
      "    accuracy                           0.98        44\n",
      "   macro avg       0.98      0.97      0.97        44\n",
      "weighted avg       0.98      0.98      0.98        44\n",
      "\n",
      "======SVC======\n",
      "\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       1.00      0.79      0.88        14\n",
      "           1       0.67      1.00      0.80        20\n",
      "           2       1.00      0.30      0.46        10\n",
      "\n",
      "    accuracy                           0.77        44\n",
      "   macro avg       0.89      0.70      0.71        44\n",
      "weighted avg       0.85      0.77      0.75        44\n",
      "\n",
      "======Dtree======\n",
      "\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.93      1.00      0.97        14\n",
      "           1       1.00      0.95      0.97        20\n",
      "           2       1.00      1.00      1.00        10\n",
      "\n",
      "    accuracy                           0.98        44\n",
      "   macro avg       0.98      0.98      0.98        44\n",
      "weighted avg       0.98      0.98      0.98        44\n",
      "\n",
      "======KNN======\n",
      "\n",
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.92      0.86      0.89        14\n",
      "           1       0.74      0.85      0.79        20\n",
      "           2       0.75      0.60      0.67        10\n",
      "\n",
      "    accuracy                           0.80        44\n",
      "   macro avg       0.80      0.77      0.78        44\n",
      "weighted avg       0.80      0.80      0.79        44\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for name, model in models:\n",
    "    model_temp = model\n",
    "    model_temp.fit(X_train, y_train)\n",
    "    y_pred = model_temp.predict(X_test)\n",
    "    print(\"======\"+name+\"======\\n\")\n",
    "    print(classification_report(y_test, y_pred))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2192e3bd",
   "metadata": {},
   "source": [
    "Overall, Gaussian Naive Bayes model achieved best performance. We can use it for predicting next purchase day range."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "0cf76dc6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GaussianNB()"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_NB = GaussianNB()\n",
    "model_NB.fit(X_train, y_train)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}