src-5/eland_script-2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import eland as ed\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Connect to an Elasticsearch instance\n",
    "from elasticsearch import Elasticsearch\n",
    "es = Elasticsearch(\n",
    "  ['http://localhost:9200'],\n",
    "  http_auth=(\"es_kbn\", \"changeme\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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_id</th>\n",
       "      <th>next_purchase_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17664</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17663</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17659</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17658</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17624</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  next_purchase_day\n",
       "0        17664                NaN\n",
       "1        17663               43.0\n",
       "2        17659               14.0\n",
       "3        17658                NaN\n",
       "4        17624                NaN"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_6m = ed.DataFrame(es, es_index_pattern=\"es-invoices-6m\").to_pandas()\n",
    "\n",
    "tx_user = pd.DataFrame(tx_6m['customer_id'].unique())\n",
    "tx_user.columns = ['customer_id']\n",
    "\n",
    "#create a dataframe with customer id and first purchase date in tx_next\n",
    "tx_next_first_purchase = ed.DataFrame(es, es_index_pattern=\"es-customers-3m\").to_pandas()\n",
    "\n",
    "#create a dataframe with customer id and last purchase date in tx_6m\n",
    "tx_last_purchase = ed.DataFrame(es, es_index_pattern=\"es-customers-6m\").to_pandas()\n",
    "\n",
    "#merge two dataframes\n",
    "tx_purchase_dates = pd.merge(tx_last_purchase,tx_next_first_purchase,on='customer_id',how='left')\n",
    "\n",
    "#calculate the time difference in days:\n",
    "tx_purchase_dates['next_purchase_day'] = (tx_purchase_dates['min_purchase_date'] - tx_purchase_dates['max_purchase_date']).dt.days\n",
    "\n",
    "#merge with tx_user\n",
    "tx_purchase_dates['customer_id'] = tx_purchase_dates['customer_id'].astype(int)\n",
    "tx_user = pd.merge(tx_user, tx_purchase_dates[['customer_id','next_purchase_day']],on='customer_id',how='left')\n",
    "\n",
    "#print tx_user\n",
    "tx_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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_id</th>\n",
       "      <th>next_purchase_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17664</td>\n",
       "      <td>999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17663</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17659</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17658</td>\n",
       "      <td>999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17624</td>\n",
       "      <td>999.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  next_purchase_day\n",
       "0        17664              999.0\n",
       "1        17663               43.0\n",
       "2        17659               14.0\n",
       "3        17658              999.0\n",
       "4        17624              999.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#fill NA values with 999\n",
    "tx_user = tx_user.fillna(999)\n",
    "\n",
    "#print tx_user\n",
    "tx_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'eland.dataframe.DataFrame'>\n",
      "Index: 2992 entries, 0 to 2991\n",
      "Data columns (total 2 columns):\n",
      " #   Column             Non-Null Count  Dtype\n",
      "---  ------             --------------  -----\n",
      " 0   customer_id        2992 non-null   int64\n",
      " 1   next_purchase_day  2992 non-null   int64\n",
      "dtypes: int64(2)\n",
      "memory usage: 64.0 bytes\n"
     ]
    }
   ],
   "source": [
    "# Load the data into elasticsearch\n",
    "ed_users = ed.pandas_to_eland(\n",
    "    pd_df=tx_user,\n",
    "    es_client=es,\n",
    "\n",
    "    # Where the data will live in Elasticsearch\n",
    "    es_dest_index=\"es-users\",\n",
    "\n",
    "    # Type overrides for certain columns, this can be used to customize index mapping before ingest\n",
    "    es_type_overrides={\n",
    "        \"invoice_id\": \"keyword\",\n",
    "        \"next_purchase_day\": \"integer\"\n",
    "    },\n",
    "\n",
    "    # If the index already exists what should we do?\n",
    "    es_if_exists=\"replace\",\n",
    "\n",
    "    # Wait for data to be indexed before returning\n",
    "    es_refresh=True,\n",
    ")\n",
    "ed_users.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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_id</th>\n",
       "      <th>next_purchase_day</th>\n",
       "      <th>recency</th>\n",
       "      <th>count_orders</th>\n",
       "      <th>total_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17664.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>20</td>\n",
       "      <td>66</td>\n",
       "      <td>7506.200006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17663.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>6</td>\n",
       "      <td>129</td>\n",
       "      <td>18651.299901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17659.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>7</td>\n",
       "      <td>212</td>\n",
       "      <td>23740.800010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17658.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>19</td>\n",
       "      <td>32</td>\n",
       "      <td>4580.899989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17624.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>25</td>\n",
       "      <td>75</td>\n",
       "      <td>9529.399958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2987</th>\n",
       "      <td>17498.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>1032.400017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2988</th>\n",
       "      <td>17495.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>12</td>\n",
       "      <td>27</td>\n",
       "      <td>3221.899990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2989</th>\n",
       "      <td>17509.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>14</td>\n",
       "      <td>369</td>\n",
       "      <td>54487.000170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2990</th>\n",
       "      <td>17506.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "      <td>1138.000010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2991</th>\n",
       "      <td>17510.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>14</td>\n",
       "      <td>20</td>\n",
       "      <td>1621.599987</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2992 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      customer_id  next_purchase_day  recency  count_orders  total_revenue\n",
       "0         17664.0              999.0       20            66    7506.200006\n",
       "1         17663.0               43.0        6           129   18651.299901\n",
       "2         17659.0               14.0        7           212   23740.800010\n",
       "3         17658.0              999.0       19            32    4580.899989\n",
       "4         17624.0              999.0       25            75    9529.399958\n",
       "...           ...                ...      ...           ...            ...\n",
       "2987      17498.0              999.0       12            17    1032.400017\n",
       "2988      17495.0              999.0       12            27    3221.899990\n",
       "2989      17509.0              999.0       14           369   54487.000170\n",
       "2990      17506.0              999.0       14            16    1138.000010\n",
       "2991      17510.0              999.0       14            20    1621.599987\n",
       "\n",
       "[2992 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#RFM Calculations\n",
    "tx_user = ed.DataFrame(es, es_index_pattern=\"es-users\").to_pandas()\n",
    "customers = ed.DataFrame(es, es_index_pattern=\"es-customers-6m\").to_pandas()\n",
    "customers['customer_id'] = customers['customer_id'].astype(int)\n",
    "tx_user = pd.merge(tx_user, customers[['customer_id','recency','count_orders','total_revenue']], on='customer_id')\n",
    "tx_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>recency_cluster</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>466.0</td>\n",
       "      <td>161.603004</td>\n",
       "      <td>15.752628</td>\n",
       "      <td>133.0</td>\n",
       "      <td>144.00</td>\n",
       "      <td>166.5</td>\n",
       "      <td>174.75</td>\n",
       "      <td>181.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>538.0</td>\n",
       "      <td>103.249071</td>\n",
       "      <td>15.376626</td>\n",
       "      <td>80.0</td>\n",
       "      <td>89.25</td>\n",
       "      <td>102.0</td>\n",
       "      <td>118.00</td>\n",
       "      <td>132.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>756.0</td>\n",
       "      <td>56.633598</td>\n",
       "      <td>11.591131</td>\n",
       "      <td>39.0</td>\n",
       "      <td>46.00</td>\n",
       "      <td>55.5</td>\n",
       "      <td>67.00</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1232.0</td>\n",
       "      <td>15.014610</td>\n",
       "      <td>9.198936</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.00</td>\n",
       "      <td>14.0</td>\n",
       "      <td>22.00</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  count        mean        std    min     25%    50%     75%  \\\n",
       "recency_cluster                                                                \n",
       "0                 466.0  161.603004  15.752628  133.0  144.00  166.5  174.75   \n",
       "1                 538.0  103.249071  15.376626   80.0   89.25  102.0  118.00   \n",
       "2                 756.0   56.633598  11.591131   39.0   46.00   55.5   67.00   \n",
       "3                1232.0   15.014610   9.198936    0.0    7.00   14.0   22.00   \n",
       "\n",
       "                   max  \n",
       "recency_cluster         \n",
       "0                181.0  \n",
       "1                132.0  \n",
       "2                 79.0  \n",
       "3                 35.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.cluster import KMeans\n",
    "#clustering for Recency\n",
    "kmeans = KMeans(n_clusters=4)\n",
    "kmeans.fit(tx_user[['recency']])\n",
    "tx_user['recency_cluster'] = kmeans.predict(tx_user[['recency']])\n",
    "\n",
    "#order cluster method\n",
    "def order_cluster(cluster_field_name, target_field_name,df,ascending):\n",
    "    new_cluster_field_name = 'new_' + cluster_field_name\n",
    "    df_new = df.groupby(cluster_field_name)[target_field_name].mean().reset_index()\n",
    "    df_new = df_new.sort_values(by=target_field_name,ascending=ascending).reset_index(drop=True)\n",
    "    df_new['index'] = df_new.index\n",
    "    df_final = pd.merge(df,df_new[[cluster_field_name,'index']], on=cluster_field_name)\n",
    "    df_final = df_final.drop([cluster_field_name],axis=1)\n",
    "    df_final = df_final.rename(columns={\"index\":cluster_field_name})\n",
    "    return df_final\n",
    "\n",
    "\n",
    "#order recency clusters\n",
    "tx_user = order_cluster('recency_cluster', 'recency',tx_user,False)\n",
    "\n",
    "#print cluster characteristics\n",
    "tx_user.groupby('recency_cluster')['recency'].describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>frequency_cluster</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2643.0</td>\n",
       "      <td>36.425653</td>\n",
       "      <td>31.185789</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.5</td>\n",
       "      <td>26.0</td>\n",
       "      <td>53.50</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>320.0</td>\n",
       "      <td>236.390625</td>\n",
       "      <td>98.626527</td>\n",
       "      <td>137.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>203.5</td>\n",
       "      <td>270.25</td>\n",
       "      <td>595.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28.0</td>\n",
       "      <td>964.535714</td>\n",
       "      <td>357.593239</td>\n",
       "      <td>608.0</td>\n",
       "      <td>707.0</td>\n",
       "      <td>858.5</td>\n",
       "      <td>1077.25</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10104.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10104.0</td>\n",
       "      <td>10104.0</td>\n",
       "      <td>10104.0</td>\n",
       "      <td>10104.00</td>\n",
       "      <td>10104.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    count          mean         std      min      25%  \\\n",
       "frequency_cluster                                                       \n",
       "0                  2643.0     36.425653   31.185789      1.0     12.5   \n",
       "1                   320.0    236.390625   98.626527    137.0    168.0   \n",
       "2                    28.0    964.535714  357.593239    608.0    707.0   \n",
       "3                     1.0  10104.000000         NaN  10104.0  10104.0   \n",
       "\n",
       "                       50%       75%      max  \n",
       "frequency_cluster                              \n",
       "0                     26.0     53.50    136.0  \n",
       "1                    203.5    270.25    595.0  \n",
       "2                    858.5   1077.25   2021.0  \n",
       "3                  10104.0  10104.00  10104.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#k-means\n",
    "kmeans = KMeans(n_clusters=4)\n",
    "kmeans.fit(tx_user[['count_orders']])\n",
    "tx_user['frequency_cluster'] = kmeans.predict(tx_user[['count_orders']])\n",
    "\n",
    "#order the frequency cluster\n",
    "tx_user = order_cluster('frequency_cluster', 'count_orders',tx_user,True)\n",
    "\n",
    "#see details of each cluster\n",
    "tx_user.groupby('frequency_cluster')['count_orders'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>revenue_cluster</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2808.0</td>\n",
       "      <td>6731.384155</td>\n",
       "      <td>11901.392070</td>\n",
       "      <td>-363218.004614</td>\n",
       "      <td>2172.000000</td>\n",
       "      <td>4401.000002</td>\n",
       "      <td>9331.575039</td>\n",
       "      <td>3.410100e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>164.0</td>\n",
       "      <td>61543.615249</td>\n",
       "      <td>28601.151203</td>\n",
       "      <td>34622.399963</td>\n",
       "      <td>41695.700100</td>\n",
       "      <td>50736.549900</td>\n",
       "      <td>72519.774924</td>\n",
       "      <td>1.704938e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16.0</td>\n",
       "      <td>297943.150509</td>\n",
       "      <td>77374.875298</td>\n",
       "      <td>198983.099792</td>\n",
       "      <td>259772.975627</td>\n",
       "      <td>280798.749974</td>\n",
       "      <td>334628.603682</td>\n",
       "      <td>5.029020e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>827010.601580</td>\n",
       "      <td>218624.093847</td>\n",
       "      <td>621149.499636</td>\n",
       "      <td>672733.525359</td>\n",
       "      <td>790105.851940</td>\n",
       "      <td>944382.928162</td>\n",
       "      <td>1.106681e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  count           mean            std            min  \\\n",
       "revenue_cluster                                                        \n",
       "0                2808.0    6731.384155   11901.392070 -363218.004614   \n",
       "1                 164.0   61543.615249   28601.151203   34622.399963   \n",
       "2                  16.0  297943.150509   77374.875298  198983.099792   \n",
       "3                   4.0  827010.601580  218624.093847  621149.499636   \n",
       "\n",
       "                           25%            50%            75%           max  \n",
       "revenue_cluster                                                             \n",
       "0                  2172.000000    4401.000002    9331.575039  3.410100e+04  \n",
       "1                 41695.700100   50736.549900   72519.774924  1.704938e+05  \n",
       "2                259772.975627  280798.749974  334628.603682  5.029020e+05  \n",
       "3                672733.525359  790105.851940  944382.928162  1.106681e+06  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_revenue = tx_user.filter(['customer_id','total_revenue'])\n",
    "\n",
    "#k-means\n",
    "kmeans = KMeans(n_clusters=4)\n",
    "kmeans.fit(total_revenue)\n",
    "tx_user['revenue_cluster'] = kmeans.predict(total_revenue)\n",
    "\n",
    "#order the revenue cluster\n",
    "tx_user = order_cluster('revenue_cluster', 'total_revenue',tx_user,True)\n",
    "\n",
    "#see details of each cluster\n",
    "tx_user.groupby('revenue_cluster')['total_revenue'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>recency</th>\n",
       "      <th>count_orders</th>\n",
       "      <th>total_revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>overall_score</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>161.721239</td>\n",
       "      <td>23.975664</td>\n",
       "      <td>2.581670e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>104.424184</td>\n",
       "      <td>29.589251</td>\n",
       "      <td>4.340444e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58.700855</td>\n",
       "      <td>40.769231</td>\n",
       "      <td>6.398289e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18.765226</td>\n",
       "      <td>55.188605</td>\n",
       "      <td>9.015273e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16.205405</td>\n",
       "      <td>205.362162</td>\n",
       "      <td>3.307154e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12.464286</td>\n",
       "      <td>297.214286</td>\n",
       "      <td>6.430368e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>11.000000</td>\n",
       "      <td>739.947368</td>\n",
       "      <td>1.590108e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10.555556</td>\n",
       "      <td>1117.222222</td>\n",
       "      <td>3.816294e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>798.000000</td>\n",
       "      <td>1.106681e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>10104.000000</td>\n",
       "      <td>8.902835e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  recency  count_orders  total_revenue\n",
       "overall_score                                         \n",
       "0              161.721239     23.975664   2.581670e+03\n",
       "1              104.424184     29.589251   4.340444e+03\n",
       "2               58.700855     40.769231   6.398289e+03\n",
       "3               18.765226     55.188605   9.015273e+03\n",
       "4               16.205405    205.362162   3.307154e+04\n",
       "5               12.464286    297.214286   6.430368e+04\n",
       "6               11.000000    739.947368   1.590108e+05\n",
       "7               10.555556   1117.222222   3.816294e+05\n",
       "8                5.000000    798.000000   1.106681e+06\n",
       "9                0.000000  10104.000000   8.902835e+05"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calculate overall score and use mean() to see details\n",
    "tx_user['overall_score'] = tx_user['recency_cluster'] + tx_user['frequency_cluster'] + tx_user['revenue_cluster']\n",
    "score = tx_user.groupby('overall_score').mean().loc[:, ['recency','count_orders','total_revenue']]\n",
    "score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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_id</th>\n",
       "      <th>invoice_date</th>\n",
       "      <th>invoice_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>406827</th>\n",
       "      <td>12346</td>\n",
       "      <td>2019-01-18 10:01:00</td>\n",
       "      <td>2019-01-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406645</th>\n",
       "      <td>12347</td>\n",
       "      <td>2018-12-07 14:57:00</td>\n",
       "      <td>2018-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406676</th>\n",
       "      <td>12347</td>\n",
       "      <td>2019-01-26 14:30:00</td>\n",
       "      <td>2019-01-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406705</th>\n",
       "      <td>12347</td>\n",
       "      <td>2019-04-07 10:43:00</td>\n",
       "      <td>2019-04-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406614</th>\n",
       "      <td>12348</td>\n",
       "      <td>2018-12-16 19:09:00</td>\n",
       "      <td>2018-12-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-02-28 10:30:00</td>\n",
       "      <td>2019-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-04-21 16:37:00</td>\n",
       "      <td>2019-04-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-05-23 11:33:00</td>\n",
       "      <td>2019-05-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406829</th>\n",
       "      <td>18287</td>\n",
       "      <td>2018-12-01 11:52:00</td>\n",
       "      <td>2018-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18287</td>\n",
       "      <td>2019-05-22 10:39:00</td>\n",
       "      <td>2019-05-22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8542 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customer_id        invoice_date invoice_day\n",
       "406827        12346 2019-01-18 10:01:00  2019-01-18\n",
       "406645        12347 2018-12-07 14:57:00  2018-12-07\n",
       "406676        12347 2019-01-26 14:30:00  2019-01-26\n",
       "406705        12347 2019-04-07 10:43:00  2019-04-07\n",
       "406614        12348 2018-12-16 19:09:00  2018-12-16\n",
       "...             ...                 ...         ...\n",
       "172           18283 2019-02-28 10:30:00  2019-02-28\n",
       "202           18283 2019-04-21 16:37:00  2019-04-21\n",
       "258           18283 2019-05-23 11:33:00  2019-05-23\n",
       "406829        18287 2018-12-01 11:52:00  2018-12-01\n",
       "0             18287 2019-05-22 10:39:00  2019-05-22\n",
       "\n",
       "[8542 rows x 3 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_6m = ed.DataFrame(es, es_index_pattern=\"es-invoices-6m\").to_pandas()\n",
    "#create a dataframe with CustomerID and Invoice Date\n",
    "tx_day_order = tx_6m.loc[:, ['customer_id','invoice_date']]\n",
    "#convert Invoice Datetime to day\n",
    "tx_day_order['invoice_day'] = tx_6m['invoice_date'].dt.date\n",
    "tx_day_order = tx_day_order.sort_values(['customer_id','invoice_date'])\n",
    "#drop duplicates\n",
    "tx_day_order = tx_day_order.drop_duplicates(subset=['customer_id','invoice_day'],keep='first')\n",
    "tx_day_order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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_id</th>\n",
       "      <th>invoice_date</th>\n",
       "      <th>invoice_day</th>\n",
       "      <th>prev_invoice_date</th>\n",
       "      <th>t2_invoice_date</th>\n",
       "      <th>t3_invoice_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>406827</th>\n",
       "      <td>12346</td>\n",
       "      <td>2019-01-18 10:01:00</td>\n",
       "      <td>2019-01-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406645</th>\n",
       "      <td>12347</td>\n",
       "      <td>2018-12-07 14:57:00</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406676</th>\n",
       "      <td>12347</td>\n",
       "      <td>2019-01-26 14:30:00</td>\n",
       "      <td>2019-01-26</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406705</th>\n",
       "      <td>12347</td>\n",
       "      <td>2019-04-07 10:43:00</td>\n",
       "      <td>2019-04-07</td>\n",
       "      <td>2019-01-26</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406614</th>\n",
       "      <td>12348</td>\n",
       "      <td>2018-12-16 19:09:00</td>\n",
       "      <td>2018-12-16</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>172</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-02-28 10:30:00</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-01-23</td>\n",
       "      <td>2019-01-06</td>\n",
       "      <td>2018-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-04-21 16:37:00</td>\n",
       "      <td>2019-04-21</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-01-23</td>\n",
       "      <td>2019-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-05-23 11:33:00</td>\n",
       "      <td>2019-05-23</td>\n",
       "      <td>2019-04-21</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-01-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406829</th>\n",
       "      <td>18287</td>\n",
       "      <td>2018-12-01 11:52:00</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18287</td>\n",
       "      <td>2019-05-22 10:39:00</td>\n",
       "      <td>2019-05-22</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8542 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customer_id        invoice_date invoice_day prev_invoice_date  \\\n",
       "406827        12346 2019-01-18 10:01:00  2019-01-18               NaN   \n",
       "406645        12347 2018-12-07 14:57:00  2018-12-07               NaN   \n",
       "406676        12347 2019-01-26 14:30:00  2019-01-26        2018-12-07   \n",
       "406705        12347 2019-04-07 10:43:00  2019-04-07        2019-01-26   \n",
       "406614        12348 2018-12-16 19:09:00  2018-12-16               NaN   \n",
       "...             ...                 ...         ...               ...   \n",
       "172           18283 2019-02-28 10:30:00  2019-02-28        2019-01-23   \n",
       "202           18283 2019-04-21 16:37:00  2019-04-21        2019-02-28   \n",
       "258           18283 2019-05-23 11:33:00  2019-05-23        2019-04-21   \n",
       "406829        18287 2018-12-01 11:52:00  2018-12-01               NaN   \n",
       "0             18287 2019-05-22 10:39:00  2019-05-22        2018-12-01   \n",
       "\n",
       "       t2_invoice_date t3_invoice_date  \n",
       "406827             NaN             NaN  \n",
       "406645             NaN             NaN  \n",
       "406676             NaN             NaN  \n",
       "406705      2018-12-07             NaN  \n",
       "406614             NaN             NaN  \n",
       "...                ...             ...  \n",
       "172         2019-01-06      2018-12-01  \n",
       "202         2019-01-23      2019-01-06  \n",
       "258         2019-02-28      2019-01-23  \n",
       "406829             NaN             NaN  \n",
       "0                  NaN             NaN  \n",
       "\n",
       "[8542 rows x 6 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#shifting last 3 purchase dates\n",
    "tx_day_order['prev_invoice_date'] = tx_day_order.groupby('customer_id')['invoice_day'].shift(1)\n",
    "tx_day_order['t2_invoice_date'] = tx_day_order.groupby('customer_id')['invoice_day'].shift(2)\n",
    "tx_day_order['t3_invoice_date'] = tx_day_order.groupby('customer_id')['invoice_day'].shift(3)\n",
    "tx_day_order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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_id</th>\n",
       "      <th>invoice_date</th>\n",
       "      <th>invoice_day</th>\n",
       "      <th>prev_invoice_date</th>\n",
       "      <th>t2_invoice_date</th>\n",
       "      <th>t3_invoice_date</th>\n",
       "      <th>day_diff</th>\n",
       "      <th>day_diff_2</th>\n",
       "      <th>day_diff_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>406827</th>\n",
       "      <td>12346</td>\n",
       "      <td>2019-01-18 10:01:00</td>\n",
       "      <td>2019-01-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406645</th>\n",
       "      <td>12347</td>\n",
       "      <td>2018-12-07 14:57:00</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406676</th>\n",
       "      <td>12347</td>\n",
       "      <td>2019-01-26 14:30:00</td>\n",
       "      <td>2019-01-26</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406705</th>\n",
       "      <td>12347</td>\n",
       "      <td>2019-04-07 10:43:00</td>\n",
       "      <td>2019-04-07</td>\n",
       "      <td>2019-01-26</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406614</th>\n",
       "      <td>12348</td>\n",
       "      <td>2018-12-16 19:09:00</td>\n",
       "      <td>2018-12-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-02-28 10:30:00</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-01-23</td>\n",
       "      <td>2019-01-06</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>36.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-04-21 16:37:00</td>\n",
       "      <td>2019-04-21</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-01-23</td>\n",
       "      <td>2019-01-06</td>\n",
       "      <td>52.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>105.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-05-23 11:33:00</td>\n",
       "      <td>2019-05-23</td>\n",
       "      <td>2019-04-21</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-01-23</td>\n",
       "      <td>32.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406829</th>\n",
       "      <td>18287</td>\n",
       "      <td>2018-12-01 11:52:00</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18287</td>\n",
       "      <td>2019-05-22 10:39:00</td>\n",
       "      <td>2019-05-22</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8542 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customer_id        invoice_date invoice_day prev_invoice_date  \\\n",
       "406827        12346 2019-01-18 10:01:00  2019-01-18               NaN   \n",
       "406645        12347 2018-12-07 14:57:00  2018-12-07               NaN   \n",
       "406676        12347 2019-01-26 14:30:00  2019-01-26        2018-12-07   \n",
       "406705        12347 2019-04-07 10:43:00  2019-04-07        2019-01-26   \n",
       "406614        12348 2018-12-16 19:09:00  2018-12-16               NaN   \n",
       "...             ...                 ...         ...               ...   \n",
       "172           18283 2019-02-28 10:30:00  2019-02-28        2019-01-23   \n",
       "202           18283 2019-04-21 16:37:00  2019-04-21        2019-02-28   \n",
       "258           18283 2019-05-23 11:33:00  2019-05-23        2019-04-21   \n",
       "406829        18287 2018-12-01 11:52:00  2018-12-01               NaN   \n",
       "0             18287 2019-05-22 10:39:00  2019-05-22        2018-12-01   \n",
       "\n",
       "       t2_invoice_date t3_invoice_date  day_diff  day_diff_2  day_diff_3  \n",
       "406827             NaN             NaN       NaN         NaN         NaN  \n",
       "406645             NaN             NaN       NaN         NaN         NaN  \n",
       "406676             NaN             NaN      50.0         NaN         NaN  \n",
       "406705      2018-12-07             NaN      71.0       121.0         NaN  \n",
       "406614             NaN             NaN       NaN         NaN         NaN  \n",
       "...                ...             ...       ...         ...         ...  \n",
       "172         2019-01-06      2018-12-01      36.0        53.0        89.0  \n",
       "202         2019-01-23      2019-01-06      52.0        88.0       105.0  \n",
       "258         2019-02-28      2019-01-23      32.0        84.0       120.0  \n",
       "406829             NaN             NaN       NaN         NaN         NaN  \n",
       "0                  NaN             NaN     172.0         NaN         NaN  \n",
       "\n",
       "[8542 rows x 9 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_day_order['day_diff'] = (tx_day_order['invoice_day'] - tx_day_order['prev_invoice_date']).dt.days\n",
    "tx_day_order['day_diff_2'] = (tx_day_order['invoice_day'] - tx_day_order['t2_invoice_date']).dt.days\n",
    "tx_day_order['day_diff_3'] = (tx_day_order['invoice_day'] - tx_day_order['t3_invoice_date']).dt.days\n",
    "tx_day_order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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_id</th>\n",
       "      <th>day_diff_mean</th>\n",
       "      <th>day_diff_std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12346</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12347</td>\n",
       "      <td>60.500000</td>\n",
       "      <td>14.849242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12348</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>21.213203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12350</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12352</td>\n",
       "      <td>11.333333</td>\n",
       "      <td>5.686241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2987</th>\n",
       "      <td>18280</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2988</th>\n",
       "      <td>18281</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2989</th>\n",
       "      <td>18282</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2990</th>\n",
       "      <td>18283</td>\n",
       "      <td>34.600000</td>\n",
       "      <td>12.481987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2991</th>\n",
       "      <td>18287</td>\n",
       "      <td>172.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2992 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      customer_id  day_diff_mean  day_diff_std\n",
       "0           12346            NaN           NaN\n",
       "1           12347      60.500000     14.849242\n",
       "2           12348      55.000000     21.213203\n",
       "3           12350            NaN           NaN\n",
       "4           12352      11.333333      5.686241\n",
       "...           ...            ...           ...\n",
       "2987        18280      96.000000           NaN\n",
       "2988        18281            NaN           NaN\n",
       "2989        18282            NaN           NaN\n",
       "2990        18283      34.600000     12.481987\n",
       "2991        18287     172.000000           NaN\n",
       "\n",
       "[2992 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#can also be done with transformation, policy/enrich, ingest as described earlier\n",
    "tx_day_diff = tx_day_order.groupby('customer_id').agg({'day_diff': ['mean','std']}).reset_index()\n",
    "tx_day_diff.columns = ['customer_id', 'day_diff_mean','day_diff_std']\n",
    "tx_day_diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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_id</th>\n",
       "      <th>invoice_date</th>\n",
       "      <th>invoice_day</th>\n",
       "      <th>prev_invoice_date</th>\n",
       "      <th>t2_invoice_date</th>\n",
       "      <th>t3_invoice_date</th>\n",
       "      <th>day_diff</th>\n",
       "      <th>day_diff_2</th>\n",
       "      <th>day_diff_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>406827</th>\n",
       "      <td>12346</td>\n",
       "      <td>2019-01-18 10:01:00</td>\n",
       "      <td>2019-01-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406705</th>\n",
       "      <td>12347</td>\n",
       "      <td>2019-04-07 10:43:00</td>\n",
       "      <td>2019-04-07</td>\n",
       "      <td>2019-01-26</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406637</th>\n",
       "      <td>12348</td>\n",
       "      <td>2019-04-05 10:47:00</td>\n",
       "      <td>2019-04-05</td>\n",
       "      <td>2019-01-25</td>\n",
       "      <td>2018-12-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406524</th>\n",
       "      <td>12350</td>\n",
       "      <td>2019-02-02 16:01:00</td>\n",
       "      <td>2019-02-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406462</th>\n",
       "      <td>12352</td>\n",
       "      <td>2019-03-22 16:07:00</td>\n",
       "      <td>2019-03-22</td>\n",
       "      <td>2019-03-17</td>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>2019-02-16</td>\n",
       "      <td>5.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>34.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>846</th>\n",
       "      <td>18280</td>\n",
       "      <td>2019-03-07 09:52:00</td>\n",
       "      <td>2019-03-07</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>407668</th>\n",
       "      <td>18281</td>\n",
       "      <td>2018-12-01 17:06:00</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>407655</th>\n",
       "      <td>18282</td>\n",
       "      <td>2018-12-01 17:06:00</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>18283</td>\n",
       "      <td>2019-05-23 11:33:00</td>\n",
       "      <td>2019-05-23</td>\n",
       "      <td>2019-04-21</td>\n",
       "      <td>2019-02-28</td>\n",
       "      <td>2019-01-23</td>\n",
       "      <td>32.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18287</td>\n",
       "      <td>2019-05-22 10:39:00</td>\n",
       "      <td>2019-05-22</td>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>172.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2992 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customer_id        invoice_date invoice_day prev_invoice_date  \\\n",
       "406827        12346 2019-01-18 10:01:00  2019-01-18               NaN   \n",
       "406705        12347 2019-04-07 10:43:00  2019-04-07        2019-01-26   \n",
       "406637        12348 2019-04-05 10:47:00  2019-04-05        2019-01-25   \n",
       "406524        12350 2019-02-02 16:01:00  2019-02-02               NaN   \n",
       "406462        12352 2019-03-22 16:07:00  2019-03-22        2019-03-17   \n",
       "...             ...                 ...         ...               ...   \n",
       "846           18280 2019-03-07 09:52:00  2019-03-07        2018-12-01   \n",
       "407668        18281 2018-12-01 17:06:00  2018-12-01               NaN   \n",
       "407655        18282 2018-12-01 17:06:00  2018-12-01               NaN   \n",
       "258           18283 2019-05-23 11:33:00  2019-05-23        2019-04-21   \n",
       "0             18287 2019-05-22 10:39:00  2019-05-22        2018-12-01   \n",
       "\n",
       "       t2_invoice_date t3_invoice_date  day_diff  day_diff_2  day_diff_3  \n",
       "406827             NaN             NaN       NaN         NaN         NaN  \n",
       "406705      2018-12-07             NaN      71.0       121.0         NaN  \n",
       "406637      2018-12-16             NaN      70.0       110.0         NaN  \n",
       "406524             NaN             NaN       NaN         NaN         NaN  \n",
       "406462      2019-03-01      2019-02-16       5.0        21.0        34.0  \n",
       "...                ...             ...       ...         ...         ...  \n",
       "846                NaN             NaN      96.0         NaN         NaN  \n",
       "407668             NaN             NaN       NaN         NaN         NaN  \n",
       "407655             NaN             NaN       NaN         NaN         NaN  \n",
       "258         2019-02-28      2019-01-23      32.0        84.0       120.0  \n",
       "0                  NaN             NaN     172.0         NaN         NaN  \n",
       "\n",
       "[2992 rows x 9 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#for logstash version : https://www.elastic.co/blog/how-to-find-and-remove-duplicate-documents-in-elasticsearch\n",
    "tx_day_order_last = tx_day_order.drop_duplicates(subset=['customer_id'],keep='last')\n",
    "tx_day_order_last"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tx_day_order_last = tx_day_order_last.dropna()\n",
    "tx_day_order_last = pd.merge(tx_day_order_last, tx_day_diff, on='customer_id')\n",
    "tx_user = pd.merge(tx_user, tx_day_order_last[['customer_id','day_diff','day_diff_2','day_diff_3','day_diff_mean','day_diff_std']], on='customer_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['customer_id', 'next_purchase_day', 'recency', 'count_orders',\n",
       "       'total_revenue', 'recency_cluster', 'frequency_cluster',\n",
       "       'revenue_cluster', 'overall_score', 'day_diff', 'day_diff_2',\n",
       "       'day_diff_3', 'day_diff_mean', 'day_diff_std'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_user.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'eland.dataframe.DataFrame'>\n",
      "Index: 671 entries, 0 to 670\n",
      "Data columns (total 14 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   count_orders       671 non-null    int64  \n",
      " 1   customer_id        671 non-null    float64\n",
      " 2   day_diff           671 non-null    float64\n",
      " 3   day_diff_2         671 non-null    float64\n",
      " 4   day_diff_3         671 non-null    float64\n",
      " 5   day_diff_mean      671 non-null    float64\n",
      " 6   day_diff_std       671 non-null    float64\n",
      " 7   frequency_cluster  671 non-null    int64  \n",
      " 8   next_purchase_day  671 non-null    int64  \n",
      " 9   overall_score      671 non-null    int64  \n",
      " 10  recency            671 non-null    int64  \n",
      " 11  recency_cluster    671 non-null    int64  \n",
      " 12  revenue_cluster    671 non-null    int64  \n",
      " 13  total_revenue      671 non-null    float64\n",
      "dtypes: float64(7), int64(7)\n",
      "memory usage: 64.0 bytes\n"
     ]
    }
   ],
   "source": [
    "# Load the data into elasticsearch\n",
    "ed_users = ed.pandas_to_eland(\n",
    "    pd_df=tx_user,\n",
    "    es_client=es,\n",
    "\n",
    "    # Where the data will live in Elasticsearch\n",
    "    es_dest_index=\"es-users\",\n",
    "\n",
    "    # Type overrides for certain columns, this can be used to customize index mapping before ingest\n",
    "    es_type_overrides={\n",
    "        \"invoice_id\": \"keyword\",\n",
    "        \"next_purchase_day\": \"integer\"\n",
    "    },\n",
    "\n",
    "    # If the index already exists what should we do?\n",
    "    es_if_exists=\"replace\",\n",
    "\n",
    "    # Wait for data to be indexed before returning\n",
    "    es_refresh=True,\n",
    ")\n",
    "ed_users.info()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}