Elo feature engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Load-dependencies\" data-toc-modified-id=\"Load-dependencies-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Load dependencies</a></span></li><li><span><a href=\"#Read-csv-files-to-DFs\" data-toc-modified-id=\"Read-csv-files-to-DFs-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Read csv files to DFs</a></span></li><li><span><a href=\"#Fill-missing-values\" data-toc-modified-id=\"Fill-missing-values-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Fill missing values</a></span></li><li><span><a href=\"#Add-date-part\" data-toc-modified-id=\"Add-date-part-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Add date part</a></span></li><li><span><a href=\"#Add-extra-columns-(purchased-on-weekend,-monthend,-month_diff-etc.\" data-toc-modified-id=\"Add-extra-columns-(purchased-on-weekend,-monthend,-month_diff-etc.-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Add extra columns (purchased on weekend, monthend, month_diff etc.</a></span></li><li><span><a href=\"#Mark-as-categorical-variables\" data-toc-modified-id=\"Mark-as-categorical-variables-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Mark as categorical variables</a></span></li><li><span><a href=\"#One-hot-encoding-categories-(not-working)\" data-toc-modified-id=\"One-hot-encoding-categories-(not-working)-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>One hot encoding categories (not working)</a></span></li><li><span><a href=\"#Aggregate-by-card_id\" data-toc-modified-id=\"Aggregate-by-card_id-8\"><span class=\"toc-item-num\">8&nbsp;&nbsp;</span>Aggregate by card_id</a></span></li><li><span><a href=\"#Add-exta-interpreted-columns-on-aggregates\" data-toc-modified-id=\"Add-exta-interpreted-columns-on-aggregates-9\"><span class=\"toc-item-num\">9&nbsp;&nbsp;</span>Add exta interpreted columns on aggregates</a></span></li><li><span><a href=\"#Load-test-&amp;-train-DFs\" data-toc-modified-id=\"Load-test-&amp;-train-DFs-10\"><span class=\"toc-item-num\">10&nbsp;&nbsp;</span>Load test &amp; train DFs</a></span></li><li><span><a href=\"#Add-date-part-to-test-&amp;-train-dfs\" data-toc-modified-id=\"Add-date-part-to-test-&amp;-train-dfs-11\"><span class=\"toc-item-num\">11&nbsp;&nbsp;</span>Add date part to test &amp; train dfs</a></span></li><li><span><a href=\"#Merge-train-&amp;-test-with-new-&amp;-old-transactions-history\" data-toc-modified-id=\"Merge-train-&amp;-test-with-new-&amp;-old-transactions-history-12\"><span class=\"toc-item-num\">12&nbsp;&nbsp;</span>Merge train &amp; test with new &amp; old transactions history</a></span></li><li><span><a href=\"#Add-extra-columns-like-age,-total-transactions,-installments,-purchase-amount,-first-buy-etc\" data-toc-modified-id=\"Add-extra-columns-like-age,-total-transactions,-installments,-purchase-amount,-first-buy-etc-13\"><span class=\"toc-item-num\">13&nbsp;&nbsp;</span>Add extra columns like age, total transactions, installments, purchase amount, first buy etc</a></span></li><li><span><a href=\"#Mark-the-outliers\" data-toc-modified-id=\"Mark-the-outliers-14\"><span class=\"toc-item-num\">14&nbsp;&nbsp;</span>Mark the outliers</a></span></li><li><span><a href=\"#Target-encode-the-outliers\" data-toc-modified-id=\"Target-encode-the-outliers-15\"><span class=\"toc-item-num\">15&nbsp;&nbsp;</span>Target encode the outliers</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Load dependencies\n",
    "2. Read csv files to dataframes\n",
    "3. Fill missing values\n",
    "4. Add date part \n",
    "5. Add extra columns (purchased on weekend, monthend, month_diff etc.\n",
    "6. Aggregate by card_id\n",
    "7. Aggregate by categories\n",
    "8. Mark categorical columns\n",
    "9. Add exta interpreted columns on aggregates\n",
    "10. Load test & train csvs to dfs\n",
    "11. Add date part to test & train dfs\n",
    "12. Merge train & test with new & old transactions history\n",
    "13. Add extra columns like age, total transactions, installments, purchase amount, first buy etc\n",
    "14. Mark the outliers\n",
    "15. Target encode the outliers \n",
    "16. Save to feather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.notebook.set_autosave_interval(120000)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Autosaving every 120 seconds\n"
     ]
    }
   ],
   "source": [
    "%matplotlib inline\n",
    "%load_ext autoreload\n",
    "%autoreload 4\n",
    "%autosave 120"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from fastai.io import *\n",
    "from fastai.structured import *\n",
    "from sklearn.ensemble import RandomForestRegressor, RandomForestClassifier\n",
    "from pandas_summary import DataFrameSummary\n",
    "from IPython.display import display\n",
    "from sklearn import metrics\n",
    "import feather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read csv files to DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = '../data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = ['historical_transactions', 'new_merchant_transactions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [pd.read_csv(f'{PATH}{c}.csv') for c in files]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_nas_for_transactions_df(df):\n",
    "    # Fill nas for category_3 with mode\n",
    "    df['category_2'].fillna(1.0,inplace=True)\n",
    "    df['category_3'].fillna('A',inplace=True)\n",
    "    df['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [fill_nas_for_transactions_df(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add date part"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_datepart(hist_trans, 'purchase_date', drop=False)\n",
    "add_datepart(new_hist_trans, 'purchase_date', drop=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add extra columns (purchased on weekend, monthend, month_diff etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_extra_cols(df):\n",
    "    df['purchased_on_weekend'] = (df.purchase_Dayofweek >=5).astype(int)\n",
    "    df['month_diff'] = ((datetime.datetime.today() - df['purchase_date']).dt.days)//30\n",
    "    df['month_diff'] += df['month_lag']\n",
    "    df['authorized_flag'] = df['authorized_flag'].map({'Y':1, 'N':0})\n",
    "    df['category_1'] = df['category_1'].map({'Y':1, 'N':0}) \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [add_extra_cols(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 29), (1963031, 29))"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mark as categorical variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mark_categorical_cols_for_transactions(df):\n",
    "    categorical_cols = ['authorized_flag', 'card_id', 'city_id',\n",
    "       'merchant_category_id', 'merchant_id',\n",
    "       'state_id', 'subsector_id', 'purchase_Year', 'purchase_Month']\n",
    "    for c in categorical_cols:\n",
    "        df[c] = df[c].astype('category').cat.as_ordered()\n",
    "    for col in ['authorized_flag', 'category_1']:\n",
    "        df[col] = df[col].map({'Y':1, 'N':0})\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_c, new_hist_trans_c = [mark_categorical_cols_for_transactions(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 29), (1963031, 29))"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans.to_feather('hist_trans')\n",
    "new_hist_trans.to_feather('new_hist_trans')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans = feather.read_dataframe('hist_trans')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans = feather.read_dataframe('new_hist_trans')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### One hot encoding categories (not working)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "hidden": 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>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>category_1</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>...</th>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <th>purchase_Is_month_start</th>\n",
       "      <th>purchase_Is_quarter_end</th>\n",
       "      <th>purchase_Is_quarter_start</th>\n",
       "      <th>purchase_Is_year_end</th>\n",
       "      <th>purchase_Is_year_start</th>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <th>month_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.96303e+06</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",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0321421</td>\n",
       "      <td>0.682964</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.47652</td>\n",
       "      <td>-0.550969</td>\n",
       "      <td>...</td>\n",
       "      <td>109.884</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",
       "      <td>1.51935e+09</td>\n",
       "      <td>0.307438</td>\n",
       "      <td>11.772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.176378</td>\n",
       "      <td>1.58407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.499448</td>\n",
       "      <td>0.694004</td>\n",
       "      <td>...</td>\n",
       "      <td>75.1097</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",
       "      <td>6.6753e+06</td>\n",
       "      <td>0.461433</td>\n",
       "      <td>2.55921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.746893</td>\n",
       "      <td>...</td>\n",
       "      <td>1</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",
       "      <td>1.48834e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.716629</td>\n",
       "      <td>...</td>\n",
       "      <td>71</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",
       "      <td>1.51967e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.674841</td>\n",
       "      <td>...</td>\n",
       "      <td>90</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",
       "      <td>1.52146e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.581616</td>\n",
       "      <td>...</td>\n",
       "      <td>112</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",
       "      <td>1.52322e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>263.157</td>\n",
       "      <td>...</td>\n",
       "      <td>365</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",
       "      <td>1.52513e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>counts</th>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>...</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uniques</th>\n",
       "      <td>1</td>\n",
       "      <td>290001</td>\n",
       "      <td>308</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>314</td>\n",
       "      <td>226129</td>\n",
       "      <td>2</td>\n",
       "      <td>75190</td>\n",
       "      <td>...</td>\n",
       "      <td>365</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1667025</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>missing</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>missing_perc</th>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>...</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>types</th>\n",
       "      <td>constant</td>\n",
       "      <td>categorical</td>\n",
       "      <td>categorical</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "      <td>categorical</td>\n",
       "      <td>categorical</td>\n",
       "      <td>categorical</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "      <td>...</td>\n",
       "      <td>numeric</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             authorized_flag      card_id      city_id   category_1  \\\n",
       "count            1.96303e+06          NaN          NaN  1.96303e+06   \n",
       "mean                       1          NaN          NaN    0.0321421   \n",
       "std                        0          NaN          NaN     0.176378   \n",
       "min                        1          NaN          NaN            0   \n",
       "25%                        1          NaN          NaN            0   \n",
       "50%                        1          NaN          NaN            0   \n",
       "75%                        1          NaN          NaN            0   \n",
       "max                        1          NaN          NaN            1   \n",
       "counts               1963031      1963031      1963031      1963031   \n",
       "uniques                    1       290001          308            2   \n",
       "missing                    0            0            0            0   \n",
       "missing_perc              0%           0%           0%           0%   \n",
       "types               constant  categorical  categorical         bool   \n",
       "\n",
       "             installments   category_3 merchant_category_id  merchant_id  \\\n",
       "count         1.96303e+06          NaN                  NaN          NaN   \n",
       "mean             0.682964          NaN                  NaN          NaN   \n",
       "std               1.58407          NaN                  NaN          NaN   \n",
       "min                    -1          NaN                  NaN          NaN   \n",
       "25%                     0          NaN                  NaN          NaN   \n",
       "50%                     1          NaN                  NaN          NaN   \n",
       "75%                     1          NaN                  NaN          NaN   \n",
       "max                   999          NaN                  NaN          NaN   \n",
       "counts            1963031      1963031              1963031      1963031   \n",
       "uniques                15            3                  314       226129   \n",
       "missing                 0            0                    0            0   \n",
       "missing_perc           0%           0%                   0%           0%   \n",
       "types             numeric  categorical          categorical  categorical   \n",
       "\n",
       "                month_lag purchase_amount     ...      purchase_Dayofyear  \\\n",
       "count         1.96303e+06     1.96303e+06     ...             1.96303e+06   \n",
       "mean              1.47652       -0.550969     ...                 109.884   \n",
       "std              0.499448        0.694004     ...                 75.1097   \n",
       "min                     1       -0.746893     ...                       1   \n",
       "25%                     1       -0.716629     ...                      71   \n",
       "50%                     1       -0.674841     ...                      90   \n",
       "75%                     2       -0.581616     ...                     112   \n",
       "max                     2         263.157     ...                     365   \n",
       "counts            1963031         1963031     ...                 1963031   \n",
       "uniques                 2           75190     ...                     365   \n",
       "missing                 0               0     ...                       0   \n",
       "missing_perc           0%              0%     ...                      0%   \n",
       "types                bool         numeric     ...                 numeric   \n",
       "\n",
       "             purchase_Is_month_end purchase_Is_month_start  \\\n",
       "count                          NaN                     NaN   \n",
       "mean                           NaN                     NaN   \n",
       "std                            NaN                     NaN   \n",
       "min                            NaN                     NaN   \n",
       "25%                            NaN                     NaN   \n",
       "50%                            NaN                     NaN   \n",
       "75%                            NaN                     NaN   \n",
       "max                            NaN                     NaN   \n",
       "counts                     1963031                 1963031   \n",
       "uniques                          2                       2   \n",
       "missing                          0                       0   \n",
       "missing_perc                    0%                      0%   \n",
       "types                         bool                    bool   \n",
       "\n",
       "             purchase_Is_quarter_end purchase_Is_quarter_start  \\\n",
       "count                            NaN                       NaN   \n",
       "mean                             NaN                       NaN   \n",
       "std                              NaN                       NaN   \n",
       "min                              NaN                       NaN   \n",
       "25%                              NaN                       NaN   \n",
       "50%                              NaN                       NaN   \n",
       "75%                              NaN                       NaN   \n",
       "max                              NaN                       NaN   \n",
       "counts                       1963031                   1963031   \n",
       "uniques                            2                         2   \n",
       "missing                            0                         0   \n",
       "missing_perc                      0%                        0%   \n",
       "types                           bool                      bool   \n",
       "\n",
       "             purchase_Is_year_end purchase_Is_year_start purchase_Elapsed  \\\n",
       "count                         NaN                    NaN      1.96303e+06   \n",
       "mean                          NaN                    NaN      1.51935e+09   \n",
       "std                           NaN                    NaN       6.6753e+06   \n",
       "min                           NaN                    NaN      1.48834e+09   \n",
       "25%                           NaN                    NaN      1.51967e+09   \n",
       "50%                           NaN                    NaN      1.52146e+09   \n",
       "75%                           NaN                    NaN      1.52322e+09   \n",
       "max                           NaN                    NaN      1.52513e+09   \n",
       "counts                    1963031                1963031          1963031   \n",
       "uniques                         2                      2          1667025   \n",
       "missing                         0                      0                0   \n",
       "missing_perc                   0%                     0%               0%   \n",
       "types                        bool                   bool          numeric   \n",
       "\n",
       "             purchased_on_weekend   month_diff  \n",
       "count                 1.96303e+06  1.96303e+06  \n",
       "mean                     0.307438       11.772  \n",
       "std                      0.461433      2.55921  \n",
       "min                             0           10  \n",
       "25%                             0           10  \n",
       "50%                             0           11  \n",
       "75%                             1           12  \n",
       "max                             1           23  \n",
       "counts                    1963031      1963031  \n",
       "uniques                         2           14  \n",
       "missing                         0            0  \n",
       "missing_perc                   0%           0%  \n",
       "types                        bool      numeric  \n",
       "\n",
       "[13 rows x 29 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrameSummary(new_hist_trans_c).summary()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def one_hot_encode_dfs(df):\n",
    "    return pd.get_dummies(df, columns=['category_2', 'category_3'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 29), (1963031, 29))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_c.shape, new_hist_trans_c.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "hist_trans_c_e, new_hist_trans_c_e = [one_hot_encode_dfs(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 35), (1963031, 35))"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_c_e.shape, new_hist_trans_c_e.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "hidden": true,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['authorized_flag', 'card_id', 'city_id', 'category_1', 'installments',\n",
       "       'merchant_category_id', 'merchant_id', 'month_lag', 'purchase_amount',\n",
       "       'purchase_date', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "       'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek',\n",
       "       'purchase_Dayofyear', 'purchase_Is_month_end',\n",
       "       'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "       'purchase_Is_quarter_start', 'purchase_Is_year_end',\n",
       "       'purchase_Is_year_start', 'purchase_Elapsed', 'purchased_on_weekend',\n",
       "       'month_diff', 'category_2_1.0', 'category_2_2.0', 'category_2_3.0',\n",
       "       'category_2_4.0', 'category_2_5.0', 'category_3_A', 'category_3_B',\n",
       "       'category_3_C'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_c_e.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "## Not working\n",
    "def aggregate_by_category(df):\n",
    "    features = ['category_2_1.0', 'category_2_2.0', 'category_2_3.0',\n",
    "       'category_2_4.0', 'category_2_5.0', 'category_3_A', 'category_3_B',\n",
    "       'category_3_C']\n",
    "    cat_agg = {\"purchase_amount\": [\"sum\", \"mean\"], \"installments\": [\"sum\", \"mean\"]}\n",
    "    other_df = pd.DataFrame()\n",
    "    for fe in features:\n",
    "        g = df.groupby(['card_id', fe]).agg(cat_agg)\n",
    "        g.columns = ['_'+fe+'_'.join(c).strip() for c in g.columns.values]\n",
    "        g.reset_index(inplace=True)\n",
    "        if other_df.empty:\n",
    "            other_df = g\n",
    "        else:\n",
    "            other_df = pd.merge(other_df, g, on='card_id', how='left')\n",
    "    return other_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate by card_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_by_card_id(df):\n",
    "    unique_cols = ['city_id', 'merchant_category_id', 'merchant_id', 'state_id', \n",
    "                   'subsector_id', 'purchase_Year', 'purchase_Month', 'purchase_Week', 'purchase_Day']\n",
    "    aggs = {}\n",
    "    for c in unique_cols:\n",
    "        aggs[c] = ['nunique'] \n",
    "    aggs['purchase_amount'] = ['sum','max','min','mean','var']\n",
    "    aggs['installments'] = ['sum','max','min','mean','var']\n",
    "    aggs['purchase_date'] = ['max','min']\n",
    "    aggs['month_lag'] = ['max','min','mean','var']\n",
    "    aggs['month_diff'] = ['mean', 'std', 'var']\n",
    "    aggs['authorized_flag'] = ['sum', 'mean']\n",
    "    aggs['purchased_on_weekend'] = ['sum', 'mean']\n",
    "    aggs['category_1'] = ['sum', 'mean']\n",
    "    aggs['card_id'] = ['size']\n",
    "    for col in ['category_2','category_3']:\n",
    "        df[col+'_mean'] = df.groupby([col])['purchase_amount'].transform('mean')\n",
    "        df[col+'_sum'] = df.groupby([col])['purchase_amount'].transform('sum')\n",
    "        aggs[col+'_mean'] = ['mean']\n",
    "        aggs[col+'_sum'] = ['sum']\n",
    "\n",
    "    new_df = df.groupby(['card_id']).agg(aggs)\n",
    "    new_df.columns = ['_'.join(col).strip() for col in new_df.columns.values]\n",
    "    new_df.reset_index(inplace=True)\n",
    "    other_df = (df.groupby('card_id')\n",
    "          .size()\n",
    "          .reset_index(name='transactions_count'))\n",
    "    \n",
    "    new_df = pd.merge(other_df, new_df, on='card_id', how='left')\n",
    "\n",
    "    new_df['purchase_date_diff'] = (new_df['purchase_date_max'] - new_df['purchase_date_min']).dt.days\n",
    "    new_df['purchase_date_average'] = new_df['purchase_date_diff']/new_df['card_id_size']\n",
    "    new_df['purchase_date_uptonow'] = (datetime.datetime.today() - new_df['purchase_date_max']).dt.days\n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dfs_e = [hist_trans_c_e, new_hist_trans_c_e]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gc\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 15s, sys: 20.5 s, total: 2min 35s\n",
      "Wall time: 1min 36s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans_agg, new_hist_trans_agg = [aggregate_by_card_id(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add exta interpreted columns on aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_extra_cols_on_agg(df):\n",
    "    df['inverse_avg_transactions_per_day'] = df['purchase_date_diff']/df['card_id_size']\n",
    "    df['days_since_last_transaction'] = (datetime.datetime.today() - df['purchase_date_max']).dt.days\n",
    "    df['repurchase_merchant_rate'] = df['transactions_count']/df['merchant_id_nunique']\n",
    "    df['merchant_category_repurchase'] = df['merchant_category_id_nunique']/df['merchant_id_nunique']\n",
    "    df['category_2_sum_sum'] = df['category_2_sum_sum']/df['transactions_count']\n",
    "    df['category_3_sum_sum'] = df['category_3_sum_sum']/df['transactions_count']\n",
    "    df['avg_spend_per_merchant'] = df['purchase_amount_sum']/df['merchant_id_nunique']\n",
    "    df['avg_trans_per_merchant'] = df['transactions_count']/df['merchant_id_nunique']\n",
    "    df['avg_spend_per_transaction'] = df['purchase_amount_sum']/df['transactions_count']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "[hist_trans_agg, new_hist_trans_agg] = [add_extra_cols_on_agg(df) for df in [hist_trans_agg, \n",
    "                                                                             new_hist_trans_agg]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg.to_feather('hist_trans_agg')\n",
    "new_hist_trans_agg.to_feather('new_hist_trans_agg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg = feather.read_dataframe('hist_trans_agg')\n",
    "new_hist_trans_agg = feather.read_dataframe('new_hist_trans_agg')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load test & train DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "train, test = [pd.read_csv(f'{PATH}{c}') for c in ['train.csv', 'test.csv']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add date part to test & train dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_datepart(train, 'first_active_month', drop=False)\n",
    "add_datepart(test, 'first_active_month', drop=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge train & test with new & old transactions history"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def join_dfs(left, right, left_on, right_on=None, suffix='_old'):\n",
    "    if right_on is None: right_on = left_on\n",
    "    return left.merge(right, how='left', left_on=left_on, right_on=right_on, suffixes=(\"\", suffix))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train, new_hist_trans_agg, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train_df, hist_trans_agg, left_on='card_id', suffix='_old')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df = join_dfs(test, new_hist_trans_agg, left_on='card_id')\n",
    "test_df = join_dfs(test_df, hist_trans_agg, left_on='card_id', suffix='_old')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((123623, 118), (201917, 119))"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df.shape, train_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add extra columns like age, total transactions, installments, purchase amount, first buy etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in [train_df, test_df]:\n",
    "    df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n",
    "    df['card_id_total'] = df['card_id_size']+df['card_id_size_old']\n",
    "    df['purchase_amount_total'] = df['purchase_amount_sum']+df['purchase_amount_sum_old']\n",
    "    df['installments_total'] = df['installments_sum'] + df['installments_sum_old']\n",
    "    df['hist_first_buy'] = (df['purchase_date_min_old'] - df['first_active_month']).dt.days\n",
    "    df['new_first_buy'] = (df['purchase_date_min'] - df['first_active_month']).dt.days\n",
    "    df['avg_spend_per_transaction'] = df['purchase_amount_total']/df['card_id_total']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mark the outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    199710\n",
       "1      2207\n",
       "Name: outliers, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df['outliers'] = 0\n",
    "train_df.loc[train_df['target'] < -30, 'outliers'] = 1\n",
    "train_df['outliers'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Target encode the outliers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 126), (123623, 124))"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "for f in ['feature_1','feature_2','feature_3']:\n",
    "    order_label = train_df.groupby([f])['outliers'].mean()\n",
    "    train_df[f] = train_df[f].map(order_label)\n",
    "    test_df[f] = test_df[f].map(order_label)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.to_feather('train_df')\n",
    "test_df.to_feather('test_df')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}