Elo feature engineering-proc_df - new.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=\"#Add-date-part\" data-toc-modified-id=\"Add-date-part-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Add date part</a></span></li><li><span><a href=\"#Train-cats-for-the-DFs\" data-toc-modified-id=\"Train-cats-for-the-DFs-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Train cats for the DFs</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=\"#Fill-NAs-with-mode\" data-toc-modified-id=\"Fill-NAs-with-mode-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Fill NAs with mode</a></span></li><li><span><a href=\"#Separate-the-authorised-and-non-authorised-transactions\" data-toc-modified-id=\"Separate-the-authorised-and-non-authorised-transactions-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Separate the authorised and non-authorised transactions</a></span></li><li><span><a href=\"#proc_df-to-fill-nas,-one-hot-encode-cats\" data-toc-modified-id=\"proc_df-to-fill-nas,-one-hot-encode-cats-8\"><span class=\"toc-item-num\">8&nbsp;&nbsp;</span>proc_df to fill nas, one hot encode cats</a></span></li><li><span><a href=\"#Drop-unnecessary-columns\" data-toc-modified-id=\"Drop-unnecessary-columns-9\"><span class=\"toc-item-num\">9&nbsp;&nbsp;</span>Drop unnecessary columns</a></span></li><li><span><a href=\"#Aggregate-by-card_id\" data-toc-modified-id=\"Aggregate-by-card_id-10\"><span class=\"toc-item-num\">10&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-11\"><span class=\"toc-item-num\">11&nbsp;&nbsp;</span>Add exta interpreted columns on aggregates</a></span></li><li><span><a href=\"#Add-monthly-aggregates\" data-toc-modified-id=\"Add-monthly-aggregates-12\"><span class=\"toc-item-num\">12&nbsp;&nbsp;</span>Add monthly aggregates</a></span></li><li><span><a href=\"#Add-successive-aggregates\" data-toc-modified-id=\"Add-successive-aggregates-13\"><span class=\"toc-item-num\">13&nbsp;&nbsp;</span>Add successive aggregates</a></span></li><li><span><a href=\"#Merge-monthly-aggregates-and-successive-aggregates-dfs-with-other-aggregate-dfs\" data-toc-modified-id=\"Merge-monthly-aggregates-and-successive-aggregates-dfs-with-other-aggregate-dfs-14\"><span class=\"toc-item-num\">14&nbsp;&nbsp;</span>Merge monthly aggregates and successive aggregates dfs with other aggregate dfs</a></span></li><li><span><a href=\"#Load-test-&amp;-train-DFs\" data-toc-modified-id=\"Load-test-&amp;-train-DFs-15\"><span class=\"toc-item-num\">15&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-16\"><span class=\"toc-item-num\">16&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-17\"><span class=\"toc-item-num\">17&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-18\"><span class=\"toc-item-num\">18&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-19\"><span class=\"toc-item-num\">19&nbsp;&nbsp;</span>Mark the outliers</a></span></li><li><span><a href=\"#Target-encode-the-outliers---no-improvements\" data-toc-modified-id=\"Target-encode-the-outliers---no-improvements-20\"><span class=\"toc-item-num\">20&nbsp;&nbsp;</span>Target encode the outliers - no improvements</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": 2,
   "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": 3,
   "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": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = '../data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = ['historical_transactions', 'new_merchant_transactions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [pd.read_csv(f'{PATH}{c}.csv') for c in files]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add date part"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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": [
    "### Train cats for the DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_cats(hist_trans)\n",
    "train_cats(new_hist_trans)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add extra columns (purchased on weekend, monthend, month_diff etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [add_extra_cols(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 29), (1963031, 29))"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill NAs with mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_nas_for_transactions_df(df):\n",
    "    # Fill nas for category_3 with mode\n",
    "    cols = ['merchant_id', 'category_3', 'category_2']\n",
    "    for col in cols:\n",
    "        df[col] = df[col].fillna(df[col].mode()[0])\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [fill_nas_for_transactions_df(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Separate the authorised and non-authorised transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "authorised_trans = hist_trans.loc[hist_trans['authorized_flag'] == 'Y']\n",
    "non_authorised_trans = hist_trans.loc[hist_trans['authorized_flag'] == 'N']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(29112361, 29)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((26595452, 29), (2516909, 29), (1963031, 29))"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans.shape, non_authorised_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [authorised_trans, non_authorised_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(new_hist_trans).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(authorised_trans).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(non_authorised_trans).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new_hist_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# authorised_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# non_authorised_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [authorised_trans, non_authorised_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### proc_df to fill nas, one hot encode cats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 43.2 s, sys: 7.81 s, total: 51 s\n",
      "Wall time: 10.8 s\n"
     ]
    }
   ],
   "source": [
    "%time authorised_trans_proc, y_authorised_trans, nas_authorised_trans = proc_df(authorised_trans, max_n_cat=7,ignore_flds=['purchase_date','card_id', 'merchant_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.29 s, sys: 619 ms, total: 5.91 s\n",
      "Wall time: 984 ms\n"
     ]
    }
   ],
   "source": [
    "%time non_authorised_trans_proc, y_non_authorised_trans, nas_non_authorised_trans = proc_df(non_authorised_trans, max_n_cat=7,ignore_flds=['purchase_date','card_id', 'merchant_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.84 s, sys: 556 ms, total: 3.4 s\n",
      "Wall time: 772 ms\n"
     ]
    }
   ],
   "source": [
    "%time new_trans_proc, y_new, nas_new = proc_df(new_hist_trans, max_n_cat=7,ignore_flds=['purchase_date','card_id', 'merchant_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((26595452, 36), (2516909, 36), (1963031, 35))"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc.shape, non_authorised_trans_proc.shape, new_trans_proc.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['purchase_date', 'card_id', 'merchant_id', 'city_id', 'installments', 'merchant_category_id',\n",
       "        'month_lag', 'purchase_amount', 'category_2', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "        'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear',\n",
       "        'purchase_Is_month_end', 'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "        'purchase_Is_quarter_start', 'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Elapsed',\n",
       "        'purchased_on_weekend', 'month_diff', 'authorized_flag_N', 'authorized_flag_Y', 'authorized_flag_nan',\n",
       "        'category_1_N', 'category_1_Y', 'category_1_nan', 'category_3_A', 'category_3_B', 'category_3_C',\n",
       "        'category_3_nan'], dtype=object),\n",
       " array(['purchase_date', 'card_id', 'merchant_id', 'city_id', 'installments', 'merchant_category_id',\n",
       "        'month_lag', 'purchase_amount', 'category_2', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "        'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear',\n",
       "        'purchase_Is_month_end', 'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "        'purchase_Is_quarter_start', 'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Elapsed',\n",
       "        'purchased_on_weekend', 'month_diff', 'authorized_flag_N', 'authorized_flag_Y', 'authorized_flag_nan',\n",
       "        'category_1_N', 'category_1_Y', 'category_1_nan', 'category_3_A', 'category_3_B', 'category_3_C',\n",
       "        'category_3_nan'], dtype=object),\n",
       " array(['purchase_date', 'card_id', 'merchant_id', 'city_id', 'installments', 'merchant_category_id',\n",
       "        'month_lag', 'purchase_amount', 'category_2', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "        'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear',\n",
       "        'purchase_Is_month_end', 'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "        'purchase_Is_quarter_start', 'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Elapsed',\n",
       "        'purchased_on_weekend', 'month_diff', 'authorized_flag_Y', 'authorized_flag_nan', 'category_1_N',\n",
       "        'category_1_Y', 'category_1_nan', 'category_3_A', 'category_3_B', 'category_3_C', 'category_3_nan'],\n",
       "       dtype=object))"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc.columns.values,non_authorised_trans_proc.columns.values, new_trans_proc.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "109"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(authorised_trans_proc).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(non_authorised_trans_proc).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(new_trans_proc).summary().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop unnecessary columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [authorised_trans_proc, non_authorised_trans_proc, new_trans_proc]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_to_drop = ['authorized_flag_Y', 'authorized_flag_nan', 'category_1_nan', 'category_3_nan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "authorised_trans_proc.drop(cols_to_drop, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "non_authorised_trans_proc.drop(cols_to_drop, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_trans_proc.drop(cols_to_drop, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "non_authorised_trans_proc.drop('authorized_flag_N', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "authorised_trans_proc.drop('authorized_flag_N', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate by card_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['purchase_date', 'card_id', 'merchant_id', 'city_id', 'installments', 'merchant_category_id',\n",
       "       'month_lag', 'purchase_amount', 'category_2', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "       'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear',\n",
       "       'purchase_Is_month_end', 'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "       'purchase_Is_quarter_start', 'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Elapsed',\n",
       "       'purchased_on_weekend', 'month_diff', 'category_1_N', 'category_1_Y', 'category_3_A', 'category_3_B',\n",
       "       'category_3_C'], dtype=object)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((26595452, 31), (2516909, 31), (1963031, 31))"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc.shape,non_authorised_trans_proc.shape, new_trans_proc.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((26595452, 31), (2516909, 31), (1963031, 31))"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc.shape,non_authorised_trans_proc.shape, new_trans_proc.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new_trans_proc.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_by_card_id(df):\n",
    "    unique_cols = ['city_id', 'merchant_category_id', 'merchant_id', 'state_id', 'purchase_Year',\n",
    "                   'subsector_id']\n",
    "    aggs = {}\n",
    "    for c in unique_cols:\n",
    "        aggs[c] = ['nunique'] \n",
    "    aggs['purchase_amount'] = ['sum', 'max', 'min', 'mean', 'var', 'median']\n",
    "    aggs['installments'] = ['sum', 'max', 'mean', 'var', 'median']\n",
    "    aggs['month_lag'] = ['sum', 'max', 'min', 'mean', 'var', 'median']\n",
    "    aggs['purchase_date'] = [np.min, np.max, np.ptp]\n",
    "    aggs['card_id'] = ['size']\n",
    "    aggs['purchased_on_weekend'] = ['sum', 'mean']\n",
    "    aggs['month_diff'] = ['sum', 'max', 'min', 'mean', 'var', 'median']\n",
    "    aggs['category_1_N'] = ['sum', 'mean']\n",
    "    aggs['category_1_Y'] = ['sum', 'mean']\n",
    "    aggs['category_2'] = ['mean', 'sum', 'size']\n",
    "    aggs['category_3_A'] = ['mean', 'sum' ,'size']\n",
    "    aggs['category_3_B' ] = ['mean', 'sum', 'size']\n",
    "    aggs['category_3_C' ] = ['mean', 'sum', 'size']\n",
    "    \n",
    "    for col in ['category_2', 'category_3_A', 'category_3_B', 'category_3_C']:\n",
    "        df[col+'_mean2'] = df.groupby([col])['purchase_amount'].transform('mean')\n",
    "        aggs[col+'_mean2'] = ['mean']    \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",
    "    new_df[\"purchase_date_ptp\"] = new_df[\"purchase_date_ptp\"].dt.days    \n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs_e = [authorised_trans_proc, non_authorised_trans_proc, new_trans_proc]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4min 6s, sys: 10.2 s, total: 4min 16s\n",
      "Wall time: 2min 46s\n"
     ]
    }
   ],
   "source": [
    "%time authorised_trans_proc_agg, non_authorised_trans_proc_agg, new_trans_proc_agg = [aggregate_by_card_id(df) for df in dfs_e]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add exta interpreted columns on aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs_agg = [authorised_trans_proc_agg, non_authorised_trans_proc_agg, new_trans_proc_agg]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 57), (325540, 57), (290001, 57))"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc_agg.shape, non_authorised_trans_proc_agg.shape, new_trans_proc_agg.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# authorised_trans_proc_agg.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_extra_cols_on_agg(df):\n",
    "    df['inverse_avg_transactions_per_day'] = df['purchase_date_ptp']/df['card_id_size']\n",
    "    df['days_since_last_transaction'] = (datetime.datetime.today() - df['purchase_date_amax']).dt.days\n",
    "    df['category_3_sum_total'] = df['category_3_A_sum'] + df['category_3_B_sum'] + df['category_3_C_sum']\n",
    "    df['category_3_mean_total'] = (df['category_3_A_mean'] + df['category_3_B_mean'] + df['category_3_C_mean'])/3\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "[authorised_trans_proc_agg, non_authorised_trans_proc_agg, new_trans_proc_agg] = [add_extra_cols_on_agg(df) for df in dfs_agg]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "authorised_trans_proc_agg.to_feather('authorised_trans_proc_agg')\n",
    "non_authorised_trans_proc_agg.to_feather('non_authorised_trans_proc_agg')\n",
    "new_trans_proc_agg.to_feather('new_trans_proc_agg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "authorised_trans_proc_agg = feather.read_dataframe('authorised_trans_proc_agg')\n",
    "non_authorised_trans_proc_agg = feather.read_dataframe('non_authorised_trans_proc_agg')\n",
    "new_trans_proc_agg = feather.read_dataframe('new_trans_proc_agg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 61), (325540, 61), (290001, 61))"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc_agg.shape, non_authorised_trans_proc_agg.shape, new_trans_proc_agg.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add monthly aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_per_month(history):\n",
    "    grouped = history.groupby(['card_id', 'month_lag'])\n",
    "\n",
    "    agg_func = {\n",
    "            'purchase_amount': ['count', 'sum', 'mean', 'min', 'max', 'std'],\n",
    "            'installments': ['count', 'sum', 'mean', 'min', 'max', 'std'],\n",
    "            }\n",
    "\n",
    "    intermediate_group = grouped.agg(agg_func)\n",
    "    intermediate_group.columns = ['_'.join(col).strip() for col in intermediate_group.columns.values]\n",
    "    intermediate_group.reset_index(inplace=True)\n",
    "\n",
    "    final_group = intermediate_group.groupby('card_id').agg(['mean', 'std'])\n",
    "    final_group.columns = ['_'.join(col).strip() for col in final_group.columns.values]\n",
    "    final_group.reset_index(inplace=True)\n",
    "    \n",
    "    return final_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "[authorised_trans_proc_m, non_authorised_trans_proc_m, new_trans_proc_m] = [aggregate_per_month(d) for d in dfs_e]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add successive aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def successive_aggregates(df, field1, field2):\n",
    "    t = df.groupby(['card_id', field1])[field2].mean()\n",
    "    u = pd.DataFrame(t).reset_index().groupby('card_id')[field2].agg(['mean', 'min', 'max', 'std'])\n",
    "    u.columns = [field1 + '_' + field2 + '_' + col for col in u.columns.values]\n",
    "    u.reset_index(inplace=True)\n",
    "    return u"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "# authorised_trans_proc.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "additional_fields_at = successive_aggregates(authorised_trans_proc, 'category_1_Y', 'purchase_amount')\n",
    "additional_fields_at = additional_fields_at.merge(successive_aggregates(authorised_trans_proc, 'installments', 'purchase_amount'),\n",
    "                                            on = 'card_id', how='left')\n",
    "additional_fields_at = additional_fields_at.merge(successive_aggregates(authorised_trans_proc, 'city_id', 'purchase_amount'),\n",
    "                                            on = 'card_id', how='left')\n",
    "additional_fields_at = additional_fields_at.merge(successive_aggregates(authorised_trans_proc, 'category_1_Y', 'installments'),\n",
    "                                            on = 'card_id', how='left')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "# non_authorised_trans_proc.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "additional_fields_nat = successive_aggregates(non_authorised_trans_proc, 'category_1_Y', 'purchase_amount')\n",
    "additional_fields_nat = additional_fields_nat.merge(successive_aggregates(non_authorised_trans_proc, 'installments', 'purchase_amount'),\n",
    "                                            on = 'card_id', how='left')\n",
    "additional_fields_nat = additional_fields_nat.merge(successive_aggregates(non_authorised_trans_proc, 'city_id', 'purchase_amount'),\n",
    "                                            on = 'card_id', how='left')\n",
    "additional_fields_nat = additional_fields_nat.merge(successive_aggregates(non_authorised_trans_proc, 'category_1_Y', 'installments'),\n",
    "                                            on = 'card_id', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "additional_fields_new = successive_aggregates(new_trans_proc, 'category_1_Y', 'purchase_amount')\n",
    "additional_fields_new = additional_fields_new.merge(successive_aggregates(new_trans_proc, 'installments', 'purchase_amount'),\n",
    "                                            on = 'card_id', how='left')\n",
    "additional_fields_new = additional_fields_new.merge(successive_aggregates(new_trans_proc, 'city_id', 'purchase_amount'),\n",
    "                                            on = 'card_id', how='left')\n",
    "additional_fields_new = additional_fields_new.merge(successive_aggregates(new_trans_proc, 'category_1_Y', 'installments'),\n",
    "                                            on = 'card_id', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge monthly aggregates and successive aggregates dfs with other aggregate dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((290001, 17), (325540, 17), (325540, 17))"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "additional_fields_new.shape, additional_fields_nat.shape, additional_fields_at.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 27), (325540, 27), (290001, 27))"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc_m.shape, non_authorised_trans_proc_m.shape, new_trans_proc_m.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 61), (325540, 61), (290001, 61))"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans_proc_agg.shape, non_authorised_trans_proc_agg.shape, new_trans_proc_agg.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = join_dfs(additional_fields_new, new_trans_proc_agg, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = join_dfs(df_new, new_trans_proc_m, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_auth = join_dfs(additional_fields_at, authorised_trans_proc_agg, left_on='card_id')\n",
    "df_auth = join_dfs(df_auth, authorised_trans_proc_m, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_non_auth = join_dfs(additional_fields_nat, non_authorised_trans_proc_agg, left_on='card_id')\n",
    "df_non_auth = join_dfs(df_non_auth, non_authorised_trans_proc_m, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((290001, 103), (325540, 103), (325540, 103))"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new.shape, df_auth.shape, df_non_auth.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new.to_feather('df_new')\n",
    "df_auth.to_feather('df_auth')\n",
    "df_non_auth.to_feather('df_non_auth')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = feather.read_dataframe('df_new')\n",
    "df_auth = feather.read_dataframe('df_auth')\n",
    "df_non_auth= feather.read_dataframe('df_non_auth')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load test & train DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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": 89,
   "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": 90,
   "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": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# authorised_trans_proc_agg.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "# non_authorised_trans_proc.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(                card_id  transactions_count  city_id_nunique  \\\n",
       " 0       C_ID_00007093c1                 114                4   \n",
       " 1       C_ID_0001238066                 120               18   \n",
       " 2       C_ID_0001506ef0                  62                3   \n",
       " 3       C_ID_0001793786                 189                9   \n",
       " 4       C_ID_000183fdda                 137                9   \n",
       " 5       C_ID_00024e244b                  53                3   \n",
       " 6       C_ID_0002709b5a                  67                4   \n",
       " 7       C_ID_00027503e2                  24                2   \n",
       " 8       C_ID_000298032a                  28                5   \n",
       " 9       C_ID_0002ba3c2e                  55                3   \n",
       " 10      C_ID_0002c7c2c1                  58                3   \n",
       " 11      C_ID_00032df08f                 125                5   \n",
       " 12      C_ID_0003754056                  35                3   \n",
       " 13      C_ID_000377f6a0                  22                2   \n",
       " 14      C_ID_0003be3c83                 146                6   \n",
       " 15      C_ID_0003f41435                 257               12   \n",
       " 16      C_ID_00042d509c                  18                4   \n",
       " 17      C_ID_0004587331                  12                1   \n",
       " 18      C_ID_0004725b87                  71                2   \n",
       " 19      C_ID_0004888ddd                  36                2   \n",
       " 20      C_ID_0004b68c49                   5                2   \n",
       " 21      C_ID_0004c2a5ab                  70                5   \n",
       " 22      C_ID_00057b99fe                  33                5   \n",
       " 23      C_ID_000599daf9                  33                3   \n",
       " 24      C_ID_0005b2f279                  16                4   \n",
       " 25      C_ID_0005b5804f                   8                2   \n",
       " 26      C_ID_0005f16cc8                 136                8   \n",
       " 27      C_ID_0006152db8                  76                5   \n",
       " 28      C_ID_000616f4a8                  36                8   \n",
       " 29      C_ID_000664aa02                  35                4   \n",
       " ...                 ...                 ...              ...   \n",
       " 325510  C_ID_fffb79fb56                  21                1   \n",
       " 325511  C_ID_fffb9ea3f6                  42                2   \n",
       " 325512  C_ID_fffba72dc5                  72                5   \n",
       " 325513  C_ID_fffbdf036b                  78                2   \n",
       " 325514  C_ID_fffbee5c24                  20                2   \n",
       " 325515  C_ID_fffc96bf24                 201                3   \n",
       " 325516  C_ID_fffcb74f49                  96                7   \n",
       " 325517  C_ID_fffcf66e12                  33                3   \n",
       " 325518  C_ID_fffd070e0e                  36                5   \n",
       " 325519  C_ID_fffd1207a8                 766                7   \n",
       " 325520  C_ID_fffd24dcf3                  13                2   \n",
       " 325521  C_ID_fffd337484                  31                6   \n",
       " 325522  C_ID_fffd345048                  58                6   \n",
       " 325523  C_ID_fffd351e7f                 210               42   \n",
       " 325524  C_ID_fffd93c8bf                 127                6   \n",
       " 325525  C_ID_fffd943b91                  49                4   \n",
       " 325526  C_ID_fffdad8aa0                 118                8   \n",
       " 325527  C_ID_fffde15ab6                  72                7   \n",
       " 325528  C_ID_fffdf34798                  57                1   \n",
       " 325529  C_ID_fffe29a04a                  84                8   \n",
       " 325530  C_ID_fffe78b232                 343               26   \n",
       " 325531  C_ID_fffe866457                  55                4   \n",
       " 325532  C_ID_fffea6de74                 395                8   \n",
       " 325533  C_ID_fffeced303                 166                4   \n",
       " 325534  C_ID_fffeed3a89                 165               15   \n",
       " 325535  C_ID_ffff1d9928                   9                2   \n",
       " 325536  C_ID_ffff579d3a                  99                7   \n",
       " 325537  C_ID_ffff756266                  14                2   \n",
       " 325538  C_ID_ffff828181                 166               11   \n",
       " 325539  C_ID_fffffd5772                  75                4   \n",
       " \n",
       "         merchant_category_id_nunique  merchant_id_nunique  state_id_nunique  \\\n",
       " 0                                 18                   28                 3   \n",
       " 1                                 29                   65                 6   \n",
       " 2                                 19                   28                 2   \n",
       " 3                                 45                  114                 4   \n",
       " 4                                 34                   71                 7   \n",
       " 5                                 10                   18                 2   \n",
       " 6                                 17                   32                 4   \n",
       " 7                                  6                   11                 2   \n",
       " 8                                 12                   20                 2   \n",
       " 9                                 21                   32                 2   \n",
       " 10                                25                   38                 1   \n",
       " 11                                40                   58                 3   \n",
       " 12                                12                   14                 2   \n",
       " 13                                12                   14                 2   \n",
       " 14                                33                   67                 2   \n",
       " 15                                33                  111                 7   \n",
       " 16                                 9                   10                 3   \n",
       " 17                                 5                    8                 1   \n",
       " 18                                18                   31                 2   \n",
       " 19                                12                   19                 2   \n",
       " 20                                 5                    5                 2   \n",
       " 21                                22                   40                 3   \n",
       " 22                                12                   25                 1   \n",
       " 23                                18                   22                 2   \n",
       " 24                                 6                    7                 3   \n",
       " 25                                 5                    5                 2   \n",
       " 26                                32                   65                 8   \n",
       " 27                                19                   26                 3   \n",
       " 28                                18                   29                 4   \n",
       " 29                                15                   25                 1   \n",
       " ...                              ...                  ...               ...   \n",
       " 325510                             4                    7                 1   \n",
       " 325511                            13                   17                 1   \n",
       " 325512                            18                   30                 1   \n",
       " 325513                            12                   31                 1   \n",
       " 325514                            11                   16                 1   \n",
       " 325515                            44                   77                 3   \n",
       " 325516                            16                   34                 2   \n",
       " 325517                             9                   11                 2   \n",
       " 325518                            15                   23                 1   \n",
       " 325519                            39                  125                 3   \n",
       " 325520                             9                   11                 2   \n",
       " 325521                            18                   20                 4   \n",
       " 325522                            18                   29                 3   \n",
       " 325523                            24                  135                14   \n",
       " 325524                            28                   56                 4   \n",
       " 325525                            15                   22                 2   \n",
       " 325526                            29                   52                 1   \n",
       " 325527                            17                   32                 3   \n",
       " 325528                             9                   18                 1   \n",
       " 325529                            28                   52                 2   \n",
       " 325530                            25                  143                 4   \n",
       " 325531                            13                   26                 2   \n",
       " 325532                            51                  148                 5   \n",
       " 325533                            21                   32                 3   \n",
       " 325534                            42                  115                 9   \n",
       " 325535                             4                    7                 2   \n",
       " 325536                            27                   61                 2   \n",
       " 325537                            10                   10                 2   \n",
       " 325538                            42                   85                 7   \n",
       " 325539                            14                   19                 3   \n",
       " \n",
       "         purchase_Year_nunique  subsector_id_nunique  purchase_amount_sum  \\\n",
       " 0                           2                    13           -62.443076   \n",
       " 1                           2                    17           -70.657273   \n",
       " 2                           2                    12           -31.696098   \n",
       " 3                           1                    22           -24.960674   \n",
       " 4                           2                    20           -67.896557   \n",
       " 5                           2                     8           -33.246142   \n",
       " 6                           2                    12           -43.940841   \n",
       " 7                           2                     6           -17.774786   \n",
       " 8                           2                    10           -14.756500   \n",
       " 9                           1                    15           -35.058310   \n",
       " 10                          2                    11           -38.129924   \n",
       " 11                          1                    16           -47.693608   \n",
       " 12                          1                     8           -24.200483   \n",
       " 13                          2                     9           -13.029909   \n",
       " 14                          2                    20           -93.405609   \n",
       " 15                          2                    18          -183.623508   \n",
       " 16                          1                     8           -11.649093   \n",
       " 17                          2                     5            -8.570341   \n",
       " 18                          2                    10           -44.262258   \n",
       " 19                          2                     9           -25.343655   \n",
       " 20                          1                     5            -2.522185   \n",
       " 21                          2                    13           -44.206390   \n",
       " 22                          2                     9           -23.851567   \n",
       " 23                          2                    12           -19.236490   \n",
       " 24                          1                     5           -10.090693   \n",
       " 25                          2                     4            -5.430055   \n",
       " 26                          2                    18           -82.918967   \n",
       " 27                          2                    11           -54.030603   \n",
       " 28                          2                    13           -20.894034   \n",
       " 29                          2                     9           -22.401839   \n",
       " ...                       ...                   ...                  ...   \n",
       " 325510                      1                     4            -8.558260   \n",
       " 325511                      2                     9           -29.152695   \n",
       " 325512                      2                    13           -33.545741   \n",
       " 325513                      1                    10           -55.166938   \n",
       " 325514                      1                     7            -7.319850   \n",
       " 325515                      2                    22          -118.633289   \n",
       " 325516                      2                    10           -58.879974   \n",
       " 325517                      2                     7           -22.670063   \n",
       " 325518                      2                     9           -22.654766   \n",
       " 325519                      2                    19          -534.895299   \n",
       " 325520                      2                     8            10.669785   \n",
       " 325521                      1                    13            -1.272685   \n",
       " 325522                      2                    12           -32.851321   \n",
       " 325523                      2                    13          -114.223879   \n",
       " 325524                      2                    17           -75.175445   \n",
       " 325525                      2                    12           -33.866347   \n",
       " 325526                      2                    15           -81.411958   \n",
       " 325527                      2                    13           -48.764246   \n",
       " 325528                      2                     8           -37.961011   \n",
       " 325529                      2                    16           -53.045600   \n",
       " 325530                      2                    12          -224.697517   \n",
       " 325531                      2                     7           -31.634248   \n",
       " 325532                      2                    24          -233.201711   \n",
       " 325533                      2                     8          -114.100091   \n",
       " 325534                      2                    20           -89.904642   \n",
       " 325535                      2                     4            -5.537901   \n",
       " 325536                      2                    15           -69.678732   \n",
       " 325537                      1                     7            -6.745010   \n",
       " 325538                      2                    23          -104.608520   \n",
       " 325539                      2                    10           -51.409273   \n",
       " \n",
       "         purchase_amount_max          ...            category_3_C_sum  \\\n",
       " 0                  1.206539          ...                        18.0   \n",
       " 1                  0.768095          ...                        32.0   \n",
       " 2                  1.493545          ...                         0.0   \n",
       " 3                  4.554145          ...                         0.0   \n",
       " 4                  2.764788          ...                        33.0   \n",
       " 5                  2.258395          ...                         0.0   \n",
       " 6                 -0.336684          ...                        12.0   \n",
       " 7                 -0.734135          ...                         0.0   \n",
       " 8                  2.577843          ...                         0.0   \n",
       " 9                 -0.097011          ...                         0.0   \n",
       " 10                -0.296112          ...                         0.0   \n",
       " 11                 2.679137          ...                        14.0   \n",
       " 12                -0.482501          ...                         0.0   \n",
       " 13                -0.281086          ...                         0.0   \n",
       " 14                 1.134802          ...                        25.0   \n",
       " 15                -0.489894          ...                         0.0   \n",
       " 16                 0.135885          ...                         2.0   \n",
       " 17                -0.624742          ...                         0.0   \n",
       " 18                -0.145847          ...                        20.0   \n",
       " 19                -0.626696          ...                         0.0   \n",
       " 20                 0.277735          ...                         1.0   \n",
       " 21                -0.296112          ...                        23.0   \n",
       " 22                -0.566590          ...                         0.0   \n",
       " 23                -0.145847          ...                         0.0   \n",
       " 24                -0.466213          ...                         0.0   \n",
       " 25                -0.604156          ...                         0.0   \n",
       " 26                 0.267547          ...                         0.0   \n",
       " 27                -0.644728          ...                         0.0   \n",
       " 28                 0.223805          ...                         5.0   \n",
       " 29                 0.034471          ...                         0.0   \n",
       " ...                     ...          ...                         ...   \n",
       " 325510            -0.075974          ...                         1.0   \n",
       " 325511            -0.491427          ...                         0.0   \n",
       " 325512             1.631023          ...                         0.0   \n",
       " 325513            -0.583720          ...                         0.0   \n",
       " 325514             1.083321          ...                        11.0   \n",
       " 325515             1.666019          ...                        12.0   \n",
       " 325516             0.024298          ...                         0.0   \n",
       " 325517            -0.560714          ...                         0.0   \n",
       " 325518            -0.296112          ...                         0.0   \n",
       " 325519             1.054771          ...                         0.0   \n",
       " 325520             5.248670          ...                        11.0   \n",
       " 325521             1.657334          ...                        20.0   \n",
       " 325522             2.181098          ...                         0.0   \n",
       " 325523             1.639182          ...                         0.0   \n",
       " 325524             0.528843          ...                         0.0   \n",
       " 325525            -0.386332          ...                         5.0   \n",
       " 325526            -0.371245          ...                         0.0   \n",
       " 325527             0.440187          ...                         3.0   \n",
       " 325528             2.859455          ...                         0.0   \n",
       " 325529             0.410134          ...                         0.0   \n",
       " 325530             0.626666          ...                         3.0   \n",
       " 325531             0.016214          ...                         4.0   \n",
       " 325532             3.761046          ...                        23.0   \n",
       " 325533            -0.145847          ...                         8.0   \n",
       " 325534             3.758641          ...                        16.0   \n",
       " 325535             0.004418          ...                         1.0   \n",
       " 325536            -0.491457          ...                         0.0   \n",
       " 325537             0.229740          ...                         5.0   \n",
       " 325538             0.109528          ...                        19.0   \n",
       " 325539            -0.311139          ...                         4.0   \n",
       " \n",
       "         category_3_C_size  category_2_mean2_mean  category_3_A_mean2_mean  \\\n",
       " 0                     114              -0.576449                -0.534588   \n",
       " 1                     120              -0.591905                -0.537080   \n",
       " 2                      62              -0.572625                -0.634275   \n",
       " 3                     189              -0.587557                -0.634275   \n",
       " 4                     137              -0.573808                -0.537499   \n",
       " 5                      53              -0.573128                -0.634275   \n",
       " 6                      67              -0.584185                -0.536076   \n",
       " 7                      24              -0.573681                -0.634275   \n",
       " 8                      28              -0.592634                -0.634275   \n",
       " 9                      55              -0.582362                -0.634275   \n",
       " 10                     58              -0.592634                -0.634275   \n",
       " 11                    125              -0.592469                -0.534588   \n",
       " 12                     35              -0.592634                -0.634275   \n",
       " 13                     22              -0.574777                -0.634275   \n",
       " 14                    146              -0.592634                -0.535271   \n",
       " 15                    257              -0.592436                -0.634275   \n",
       " 16                     18              -0.584593                -0.540126   \n",
       " 17                     12              -0.571958                -0.634275   \n",
       " 18                     71              -0.592634                -0.534588   \n",
       " 19                     36              -0.572532                -0.634275   \n",
       " 20                      5              -0.592634                -0.534588   \n",
       " 21                     70              -0.592565                -0.536012   \n",
       " 22                     33              -0.592634                -0.634275   \n",
       " 23                     33              -0.573211                -0.634275   \n",
       " 24                     16              -0.592331                -0.534588   \n",
       " 25                      8              -0.574542                -0.634275   \n",
       " 26                    136              -0.574766                -0.634275   \n",
       " 27                     76              -0.591853                -0.634275   \n",
       " 28                     36              -0.591689                -0.534588   \n",
       " 29                     35              -0.592634                -0.634275   \n",
       " ...                   ...                    ...                      ...   \n",
       " 325510                 21              -0.592634                -0.534588   \n",
       " 325511                 42              -0.571958                -0.634275   \n",
       " 325512                 72              -0.592634                -0.634275   \n",
       " 325513                 78              -0.592634                -0.634275   \n",
       " 325514                 20              -0.582172                -0.534588   \n",
       " 325515                201              -0.592144                -0.534588   \n",
       " 325516                 96              -0.588111                -0.634275   \n",
       " 325517                 33              -0.588069                -0.634275   \n",
       " 325518                 36              -0.592634                -0.634275   \n",
       " 325519                766              -0.592580                -0.634275   \n",
       " 325520                 13              -0.592634                -0.534588   \n",
       " 325521                 31              -0.577294                -0.534588   \n",
       " 325522                 58              -0.592550                -0.634275   \n",
       " 325523                210              -0.587594                -0.634275   \n",
       " 325524                127              -0.575940                -0.634275   \n",
       " 325525                 49              -0.592634                -0.534588   \n",
       " 325526                118              -0.592634                -0.634275   \n",
       " 325527                 72              -0.592634                -0.534588   \n",
       " 325528                 57              -0.582172                -0.634275   \n",
       " 325529                 84              -0.592634                -0.634275   \n",
       " 325530                343              -0.592599                -0.534588   \n",
       " 325531                 55              -0.592634                -0.534588   \n",
       " 325532                395              -0.576290                -0.536102   \n",
       " 325533                166              -0.592634                -0.535189   \n",
       " 325534                165              -0.589987                -0.537609   \n",
       " 325535                  9              -0.592163                -0.545664   \n",
       " 325536                 99              -0.592585                -0.634275   \n",
       " 325537                 14              -0.592634                -0.534588   \n",
       " 325538                166              -0.584074                -0.535189   \n",
       " 325539                 75              -0.581331                -0.535917   \n",
       " \n",
       "         category_3_B_mean2_mean  category_3_C_mean2_mean  \\\n",
       " 0                     -0.604662                -0.527947   \n",
       " 1                     -0.599878                -0.461678   \n",
       " 2                     -0.574547                -0.624142   \n",
       " 3                     -0.574547                -0.624142   \n",
       " 4                     -0.600651                -0.477391   \n",
       " 5                     -0.574547                -0.624142   \n",
       " 6                     -0.603370                -0.515025   \n",
       " 7                     -0.574547                -0.624142   \n",
       " 8                     -0.574547                -0.624142   \n",
       " 9                     -0.574547                -0.624142   \n",
       " 10                    -0.574547                -0.624142   \n",
       " 11                    -0.606304                -0.555907   \n",
       " 12                    -0.574547                -0.624142   \n",
       " 13                    -0.574547                -0.624142   \n",
       " 14                    -0.603941                -0.519820   \n",
       " 15                    -0.574547                -0.624142   \n",
       " 16                    -0.604349                -0.556449   \n",
       " 17                    -0.574547                -0.624142   \n",
       " 18                    -0.600235                -0.452525   \n",
       " 19                    -0.574547                -0.624142   \n",
       " 20                    -0.603157                -0.502294   \n",
       " 21                    -0.598048                -0.423963   \n",
       " 22                    -0.574547                -0.624142   \n",
       " 23                    -0.574547                -0.624142   \n",
       " 24                    -0.610309                -0.624142   \n",
       " 25                    -0.574547                -0.624142   \n",
       " 26                    -0.574547                -0.624142   \n",
       " 27                    -0.574547                -0.624142   \n",
       " 28                    -0.605342                -0.539526   \n",
       " 29                    -0.574547                -0.624142   \n",
       " ...                         ...                      ...   \n",
       " 325510                -0.608606                -0.595131   \n",
       " 325511                -0.574547                -0.624142   \n",
       " 325512                -0.574547                -0.624142   \n",
       " 325513                -0.574547                -0.624142   \n",
       " 325514                -0.590640                -0.289060   \n",
       " 325515                -0.608174                -0.587770   \n",
       " 325516                -0.574547                -0.624142   \n",
       " 325517                -0.574547                -0.624142   \n",
       " 325518                -0.574547                -0.624142   \n",
       " 325519                -0.574547                -0.624142   \n",
       " 325520                -0.580049                -0.108631   \n",
       " 325521                -0.587237                -0.231084   \n",
       " 325522                -0.574547                -0.624142   \n",
       " 325523                -0.574547                -0.624142   \n",
       " 325524                -0.574547                -0.624142   \n",
       " 325525                -0.606660                -0.561975   \n",
       " 325526                -0.574547                -0.624142   \n",
       " 325527                -0.608819                -0.598757   \n",
       " 325528                -0.574547                -0.624142   \n",
       " 325529                -0.574547                -0.624142   \n",
       " 325530                -0.609996                -0.618814   \n",
       " 325531                -0.607708                -0.579834   \n",
       " 325532                -0.607684                -0.588668   \n",
       " 325533                -0.608370                -0.594781   \n",
       " 325534                -0.605758                -0.565065   \n",
       " 325535                -0.602362                -0.556449   \n",
       " 325536                -0.574547                -0.624142   \n",
       " 325537                -0.597537                -0.406557   \n",
       " 325538                -0.606000                -0.554410   \n",
       " 325539                -0.607925                -0.591650   \n",
       " \n",
       "         inverse_avg_transactions_per_day  days_since_last_transaction  \\\n",
       " 0                               3.307018                          323   \n",
       " 1                               1.258333                          323   \n",
       " 2                               6.419355                          333   \n",
       " 3                               1.497354                          442   \n",
       " 4                               1.277372                          325   \n",
       " 5                               7.528302                          344   \n",
       " 6                               4.417910                          331   \n",
       " 7                               9.875000                          362   \n",
       " 8                              12.321429                          357   \n",
       " 9                               4.418182                          497   \n",
       " 10                              4.896552                          356   \n",
       " 11                              2.552000                          421   \n",
       " 12                              8.057143                          392   \n",
       " 13                              3.545455                          356   \n",
       " 14                              1.219178                          338   \n",
       " 15                              1.003891                          323   \n",
       " 16                              8.444444                          387   \n",
       " 17                             10.083333                          354   \n",
       " 18                              5.154930                          372   \n",
       " 19                              5.861111                          350   \n",
       " 20                             19.200000                          382   \n",
       " 21                              5.800000                          330   \n",
       " 22                             11.666667                          339   \n",
       " 23                              9.606061                          323   \n",
       " 24                             20.875000                          383   \n",
       " 25                             24.000000                          358   \n",
       " 26                              3.029412                          322   \n",
       " 27                              3.118421                          335   \n",
       " 28                              9.666667                          379   \n",
       " 29                              1.771429                          329   \n",
       " ...                                  ...                          ...   \n",
       " 325510                          6.952381                          596   \n",
       " 325511                          5.333333                          323   \n",
       " 325512                          1.805556                          323   \n",
       " 325513                          1.538462                          595   \n",
       " 325514                         13.500000                          396   \n",
       " 325515                          2.094527                          324   \n",
       " 325516                          2.572917                          323   \n",
       " 325517                         11.575758                          356   \n",
       " 325518                         10.500000                          323   \n",
       " 325519                          0.550914                          322   \n",
       " 325520                         18.846154                          358   \n",
       " 325521                          5.516129                          503   \n",
       " 325522                          3.517241                          322   \n",
       " 325523                          1.280952                          330   \n",
       " 325524                          1.086614                          329   \n",
       " 325525                          6.530612                          359   \n",
       " 325526                          1.110169                          322   \n",
       " 325527                          3.388889                          326   \n",
       " 325528                          5.333333                          330   \n",
       " 325529                          3.845238                          324   \n",
       " 325530                          1.221574                          322   \n",
       " 325531                          2.145455                          322   \n",
       " 325532                          0.686076                          322   \n",
       " 325533                          1.921687                          325   \n",
       " 325534                          0.703030                          321   \n",
       " 325535                          8.222222                          373   \n",
       " 325536                          2.010101                          327   \n",
       " 325537                         18.571429                          461   \n",
       " 325538                          2.469880                          331   \n",
       " 325539                          2.506667                          324   \n",
       " \n",
       "         category_3_sum_total  category_3_mean_total  \n",
       " 0                      114.0               0.333333  \n",
       " 1                      120.0               0.333333  \n",
       " 2                       62.0               0.333333  \n",
       " 3                      189.0               0.333333  \n",
       " 4                      137.0               0.333333  \n",
       " 5                       53.0               0.333333  \n",
       " 6                       67.0               0.333333  \n",
       " 7                       24.0               0.333333  \n",
       " 8                       28.0               0.333333  \n",
       " 9                       55.0               0.333333  \n",
       " 10                      58.0               0.333333  \n",
       " 11                     125.0               0.333333  \n",
       " 12                      35.0               0.333333  \n",
       " 13                      22.0               0.333333  \n",
       " 14                     146.0               0.333333  \n",
       " 15                     257.0               0.333333  \n",
       " 16                      18.0               0.333333  \n",
       " 17                      12.0               0.333333  \n",
       " 18                      71.0               0.333333  \n",
       " 19                      36.0               0.333333  \n",
       " 20                       5.0               0.333333  \n",
       " 21                      70.0               0.333333  \n",
       " 22                      33.0               0.333333  \n",
       " 23                      33.0               0.333333  \n",
       " 24                      16.0               0.333333  \n",
       " 25                       8.0               0.333333  \n",
       " 26                     136.0               0.333333  \n",
       " 27                      76.0               0.333333  \n",
       " 28                      36.0               0.333333  \n",
       " 29                      35.0               0.333333  \n",
       " ...                      ...                    ...  \n",
       " 325510                  21.0               0.333333  \n",
       " 325511                  42.0               0.333333  \n",
       " 325512                  72.0               0.333333  \n",
       " 325513                  78.0               0.333333  \n",
       " 325514                  20.0               0.333333  \n",
       " 325515                 201.0               0.333333  \n",
       " 325516                  96.0               0.333333  \n",
       " 325517                  33.0               0.333333  \n",
       " 325518                  36.0               0.333333  \n",
       " 325519                 766.0               0.333333  \n",
       " 325520                  13.0               0.333333  \n",
       " 325521                  31.0               0.333333  \n",
       " 325522                  58.0               0.333333  \n",
       " 325523                 210.0               0.333333  \n",
       " 325524                 127.0               0.333333  \n",
       " 325525                  49.0               0.333333  \n",
       " 325526                 118.0               0.333333  \n",
       " 325527                  72.0               0.333333  \n",
       " 325528                  57.0               0.333333  \n",
       " 325529                  84.0               0.333333  \n",
       " 325530                 343.0               0.333333  \n",
       " 325531                  55.0               0.333333  \n",
       " 325532                 395.0               0.333333  \n",
       " 325533                 166.0               0.333333  \n",
       " 325534                 165.0               0.333333  \n",
       " 325535                   9.0               0.333333  \n",
       " 325536                  99.0               0.333333  \n",
       " 325537                  14.0               0.333333  \n",
       " 325538                 166.0               0.333333  \n",
       " 325539                  75.0               0.333333  \n",
       " \n",
       " [325540 rows x 61 columns],\n",
       "                purchase_date          card_id      merchant_id  city_id  \\\n",
       " 115      2017-04-07 12:58:09  C_ID_4e6213e9bc  M_ID_22c9cfa265       88   \n",
       " 132      2017-09-17 22:40:27  C_ID_4e6213e9bc  M_ID_86ec983688       88   \n",
       " 148      2017-09-17 22:40:26  C_ID_4e6213e9bc  M_ID_86ec983688       88   \n",
       " 168      2018-02-20 10:57:50  C_ID_4e6213e9bc  M_ID_c2ae34c2ef      333   \n",
       " 213      2017-07-08 07:33:31  C_ID_4e6213e9bc  M_ID_e6d5ae8ea6       88   \n",
       " 294      2017-03-22 15:53:08  C_ID_4e6213e9bc  M_ID_21e1552dab       88   \n",
       " 306      2017-07-08 07:32:30  C_ID_4e6213e9bc  M_ID_e6d5ae8ea6       88   \n",
       " 365      2017-07-08 07:32:49  C_ID_4e6213e9bc  M_ID_e6d5ae8ea6       88   \n",
       " 368      2017-03-22 15:52:52  C_ID_4e6213e9bc  M_ID_21e1552dab       88   \n",
       " 400      2017-09-07 20:57:19  C_ID_5037ff576e  M_ID_b61c7d1be0      322   \n",
       " 407      2017-11-09 04:33:33  C_ID_5037ff576e  M_ID_16cf5cd707      221   \n",
       " 410      2017-09-18 20:08:26  C_ID_5037ff576e  M_ID_d23623b922      211   \n",
       " 414      2017-07-19 17:54:52  C_ID_5037ff576e  M_ID_a21fe5f7ad       11   \n",
       " 438      2017-06-18 14:37:17  C_ID_5037ff576e  M_ID_d5a0b353e8      344   \n",
       " 455      2017-12-25 21:03:22  C_ID_5037ff576e  M_ID_86a4e23281       19   \n",
       " 465      2017-07-16 00:05:52  C_ID_5037ff576e  M_ID_b0ec3307f7      330   \n",
       " 467      2017-06-27 19:41:10  C_ID_5037ff576e  M_ID_3d775a00f7      344   \n",
       " 481      2017-07-27 18:08:49  C_ID_5037ff576e  M_ID_41dc9e6bd3      107   \n",
       " 493      2017-01-11 16:21:12  C_ID_5037ff576e  M_ID_00a6ca8a8a       69   \n",
       " 496      2017-09-27 14:15:48  C_ID_5037ff576e  M_ID_fe69229f24      138   \n",
       " 505      2017-11-13 00:00:00  C_ID_5037ff576e  M_ID_6929a11279       17   \n",
       " 508      2017-06-27 19:41:30  C_ID_5037ff576e  M_ID_3d775a00f7      344   \n",
       " 517      2017-06-23 18:55:07  C_ID_5037ff576e  M_ID_d23623b922       20   \n",
       " 522      2017-04-12 23:31:50  C_ID_0e171c1b48  M_ID_0f8e964477      277   \n",
       " 562      2017-03-20 00:57:58  C_ID_0e171c1b48  M_ID_0f8e964477      277   \n",
       " 566      2017-04-20 23:50:48  C_ID_0e171c1b48  M_ID_0f8e964477      277   \n",
       " 599      2017-02-25 09:46:33  C_ID_0e171c1b48  M_ID_bf8f6ad79b      277   \n",
       " 615      2017-02-25 09:40:21  C_ID_0e171c1b48  M_ID_bf8f6ad79b      277   \n",
       " 630      2017-11-18 18:46:07  C_ID_0e171c1b48  M_ID_cbf911cbb0      117   \n",
       " 631      2017-12-23 18:10:22  C_ID_0e171c1b48  M_ID_39fd61764d      277   \n",
       " ...                      ...              ...              ...      ...   \n",
       " 29111862 2017-10-09 16:16:10  C_ID_40adbff44a  M_ID_69089f87c8      179   \n",
       " 29111880 2017-10-02 19:17:55  C_ID_a8a4b70bf9  M_ID_6495f1c97d      137   \n",
       " 29111890 2017-11-20 08:35:19  C_ID_ca8d705fff  M_ID_031acd56e5      137   \n",
       " 29111931 2017-08-06 19:57:13  C_ID_161e22a7b0  M_ID_843f0ebf30      307   \n",
       " 29111954 2017-10-31 00:00:00  C_ID_a33c51f8c8  M_ID_50b022fc7a      158   \n",
       " 29111955 2017-10-31 00:00:00  C_ID_a33c51f8c8  M_ID_50b022fc7a      158   \n",
       " 29111976 2017-06-27 17:00:52  C_ID_7bb86ef184  M_ID_a568fdcaab       80   \n",
       " 29112004 2017-06-07 09:18:37  C_ID_638a2a177a  M_ID_e382105399      209   \n",
       " 29112011 2017-10-17 18:43:24  C_ID_d6b145c5cb  M_ID_00a6ca8a8a       69   \n",
       " 29112026 2017-05-11 13:06:42  C_ID_8d04b783fe  M_ID_147c1ac98b      259   \n",
       " 29112043 2017-10-28 15:16:46  C_ID_05042ebd55  M_ID_ce1e7a7a85       -1   \n",
       " 29112047 2018-02-07 12:33:36  C_ID_05042ebd55  M_ID_9ecff0beda       -1   \n",
       " 29112063 2018-02-05 20:09:43  C_ID_2a73e65860  M_ID_fc7d7969c3       -1   \n",
       " 29112067 2017-10-26 13:16:09  C_ID_1cc5bee514  M_ID_c87fc39442      266   \n",
       " 29112079 2017-01-13 22:50:46  C_ID_3340b9914c  M_ID_f0df40273c       21   \n",
       " 29112098 2017-11-17 19:59:11  C_ID_108bdc6e35  M_ID_ebe08ac7bf      344   \n",
       " 29112115 2017-11-18 10:43:40  C_ID_c463588249  M_ID_00a6ca8a8a       -1   \n",
       " 29112130 2017-04-06 16:21:37  C_ID_b9b0acc610  M_ID_9e84cda3b1      289   \n",
       " 29112142 2017-01-04 11:45:21  C_ID_283db870bf  M_ID_88abb11228      186   \n",
       " 29112187 2017-10-27 00:15:53  C_ID_b6af420785  M_ID_49d4a8510e       -1   \n",
       " 29112191 2017-10-27 00:15:12  C_ID_b6af420785  M_ID_49d4a8510e       -1   \n",
       " 29112195 2017-08-04 12:31:03  C_ID_a51e74c7c1  M_ID_699dff9a4b        4   \n",
       " 29112199 2017-08-04 12:32:17  C_ID_a51e74c7c1  M_ID_699dff9a4b        4   \n",
       " 29112200 2017-08-04 12:32:02  C_ID_a51e74c7c1  M_ID_699dff9a4b        4   \n",
       " 29112201 2017-08-04 12:33:15  C_ID_a51e74c7c1  M_ID_699dff9a4b        4   \n",
       " 29112210 2017-07-29 19:32:29  C_ID_570d5b0d4f  M_ID_6523fec105      117   \n",
       " 29112213 2017-10-12 19:03:34  C_ID_570d5b0d4f  M_ID_6523fec105      117   \n",
       " 29112230 2018-01-15 10:34:33  C_ID_925880baa8  M_ID_09d7a200d1       -1   \n",
       " 29112306 2017-03-04 20:46:25  C_ID_5a397de34c  M_ID_fc7d7969c3       -1   \n",
       " 29112313 2017-07-15 08:17:49  C_ID_4fe4bff0a8  M_ID_b98db225f5       19   \n",
       " \n",
       "           installments  merchant_category_id  month_lag  purchase_amount  \\\n",
       " 115                  0                   842        -10        -0.730379   \n",
       " 132                  0                   367         -5        -0.723782   \n",
       " 148                  0                   367         -5        -0.723782   \n",
       " 168                  0                   605          0        -0.664262   \n",
       " 213                  0                   560         -7        -0.738132   \n",
       " 294                  0                   278        -11        -0.734135   \n",
       " 306                  0                   560         -7        -0.738132   \n",
       " 365                  0                   560         -7        -0.738132   \n",
       " 368                  0                   278        -11        -0.734135   \n",
       " 400                  1                   278         -3        -0.595260   \n",
       " 407                  1                   307         -1        -0.658296   \n",
       " 410                  1                   307         -3         0.075749   \n",
       " 414                  1                   333         -5         0.495199   \n",
       " 438                  1                   307         -6         1.470705   \n",
       " 455                  1                   307          0         1.339088   \n",
       " 465                  1                   307         -5         0.906008   \n",
       " 467                  1                   307         -6         1.356804   \n",
       " 481                  1                   290         -5         1.567175   \n",
       " 493                  5                   511        -11         1.421418   \n",
       " 496                  1                   307         -3         0.485266   \n",
       " 505                  1                   307         -1         0.004418   \n",
       " 508                  1                   307         -6         0.755743   \n",
       " 517                  1                   307         -6         0.755743   \n",
       " 522                  0                   511        -10        -0.740146   \n",
       " 562                  0                   511        -11        -0.737141   \n",
       " 566                  0                   511        -10        -0.739770   \n",
       " 599                  0                    68        -12        -0.566590   \n",
       " 615                  0                    68        -12        -0.566590   \n",
       " 630                  0                    80         -3        -0.728876   \n",
       " 631                  0                   705         -2        -0.716855   \n",
       " ...                ...                   ...        ...              ...   \n",
       " 29111862             2                   437         -2        -0.390178   \n",
       " 29111880            10                   434         -4         2.838268   \n",
       " 29111890             0                   307         -3        -0.626696   \n",
       " 29111931             0                   783         -6        -0.694315   \n",
       " 29111954            -1                   273         -1         0.303445   \n",
       " 29111955            -1                   273         -1         1.806097   \n",
       " 29111976             0                   690         -7         0.273392   \n",
       " 29112004             0                   222         -1        -0.713143   \n",
       " 29112011             1                   879         -1        -0.476431   \n",
       " 29112026             0                   278         -4        -0.715878   \n",
       " 29112043             0                   437         -4        -0.643976   \n",
       " 29112047             0                    80          0        -0.705134   \n",
       " 29112063             1                   661          0        -0.745405   \n",
       " 29112067             1                   705          0        -0.626696   \n",
       " 29112079             0                   454         -8        -0.737892   \n",
       " 29112098             0                   705         -3        -0.518069   \n",
       " 29112115            12                   879         -1         0.513516   \n",
       " 29112130             1                   661          0        -0.448196   \n",
       " 29112142             0                   705         -5        -0.716765   \n",
       " 29112187             1                   224         -3        -0.450886   \n",
       " 29112191             1                   224         -3        -0.450886   \n",
       " 29112195             6                    34         -6         0.755743   \n",
       " 29112199             0                    34         -6         0.154683   \n",
       " 29112200             0                    34         -6         0.755743   \n",
       " 29112201             0                    34         -6         0.154683   \n",
       " 29112210             0                   367         -3        -0.733384   \n",
       " 29112213             0                   367          0        -0.733955   \n",
       " 29112230             1                   550          0         0.973312   \n",
       " 29112306             1                   661          0        -0.745405   \n",
       " 29112313             0                   705         -3        -0.620986   \n",
       " \n",
       "           category_2  state_id         ...          month_diff  category_1_N  \\\n",
       " 115              1.0        16         ...                  11             1   \n",
       " 132              1.0        16         ...                  11             1   \n",
       " 148              1.0        16         ...                  11             1   \n",
       " 168              1.0         9         ...                  11             1   \n",
       " 213              1.0        16         ...                  11             1   \n",
       " 294              1.0        16         ...                  11             1   \n",
       " 306              1.0        16         ...                  11             1   \n",
       " 365              1.0        16         ...                  11             1   \n",
       " 368              1.0        16         ...                  11             1   \n",
       " 400              3.0        11         ...                  13             1   \n",
       " 407              2.0        24         ...                  13             1   \n",
       " 410              3.0        19         ...                  13             1   \n",
       " 414              2.0        23         ...                  13             1   \n",
       " 438              2.0        18         ...                  13             1   \n",
       " 455              1.0         9         ...                  12             1   \n",
       " 465              3.0        17         ...                  13             1   \n",
       " 467              2.0        18         ...                  12             1   \n",
       " 481              4.0         4         ...                  12             1   \n",
       " 493              1.0         9         ...                  13             1   \n",
       " 496              1.0        15         ...                  12             1   \n",
       " 505              4.0        22         ...                  13             1   \n",
       " 508              2.0        18         ...                  12             1   \n",
       " 517              3.0        19         ...                  13             1   \n",
       " 522              4.0        13         ...                  11             1   \n",
       " 562              4.0        13         ...                  11             1   \n",
       " 566              4.0        13         ...                  11             1   \n",
       " 599              4.0        13         ...                  11             1   \n",
       " 615              4.0        13         ...                  11             1   \n",
       " 630              4.0        13         ...                  11             1   \n",
       " 631              4.0        13         ...                  10             1   \n",
       " ...              ...       ...         ...                 ...           ...   \n",
       " 29111862         1.0        -1         ...                  13             1   \n",
       " 29111880         3.0        19         ...                  11             1   \n",
       " 29111890         3.0        19         ...                  11             1   \n",
       " 29111931         2.0        24         ...                  11             1   \n",
       " 29111954         1.0        15         ...                  13             1   \n",
       " 29111955         1.0        15         ...                  13             1   \n",
       " 29111976         4.0         4         ...                  11             1   \n",
       " 29112004         1.0         9         ...                  18             1   \n",
       " 29112011         1.0         9         ...                  14             1   \n",
       " 29112026         5.0        21         ...                  16             1   \n",
       " 29112043         1.0        -1         ...                  10             1   \n",
       " 29112047         1.0        -1         ...                  11             1   \n",
       " 29112063         1.0        -1         ...                  11             0   \n",
       " 29112067         2.0        10         ...                  14             1   \n",
       " 29112079         1.0         9         ...                  16             1   \n",
       " 29112098         2.0        18         ...                  11             1   \n",
       " 29112115         1.0        -1         ...                  13             0   \n",
       " 29112130         5.0         5         ...                  21             1   \n",
       " 29112142         1.0         9         ...                  19             1   \n",
       " 29112187         1.0        -1         ...                  11             0   \n",
       " 29112191         1.0        -1         ...                  11             0   \n",
       " 29112195         2.0        10         ...                  11             1   \n",
       " 29112199         2.0        10         ...                  11             1   \n",
       " 29112200         2.0        10         ...                  11             1   \n",
       " 29112201         2.0        10         ...                  11             1   \n",
       " 29112210         1.0         9         ...                  14             1   \n",
       " 29112213         1.0         9         ...                  15             1   \n",
       " 29112230         1.0        -1         ...                  12             0   \n",
       " 29112306         1.0        -1         ...                  22             0   \n",
       " 29112313         1.0         9         ...                  15             1   \n",
       " \n",
       "           category_1_Y  category_3_A  category_3_B  category_3_C  \\\n",
       " 115                  0             1             0             0   \n",
       " 132                  0             1             0             0   \n",
       " 148                  0             1             0             0   \n",
       " 168                  0             1             0             0   \n",
       " 213                  0             1             0             0   \n",
       " 294                  0             1             0             0   \n",
       " 306                  0             1             0             0   \n",
       " 365                  0             1             0             0   \n",
       " 368                  0             1             0             0   \n",
       " 400                  0             0             1             0   \n",
       " 407                  0             0             1             0   \n",
       " 410                  0             0             1             0   \n",
       " 414                  0             0             1             0   \n",
       " 438                  0             0             1             0   \n",
       " 455                  0             0             1             0   \n",
       " 465                  0             0             1             0   \n",
       " 467                  0             0             1             0   \n",
       " 481                  0             0             1             0   \n",
       " 493                  0             0             0             1   \n",
       " 496                  0             0             1             0   \n",
       " 505                  0             0             1             0   \n",
       " 508                  0             0             1             0   \n",
       " 517                  0             0             1             0   \n",
       " 522                  0             1             0             0   \n",
       " 562                  0             1             0             0   \n",
       " 566                  0             1             0             0   \n",
       " 599                  0             1             0             0   \n",
       " 615                  0             1             0             0   \n",
       " 630                  0             1             0             0   \n",
       " 631                  0             1             0             0   \n",
       " ...                ...           ...           ...           ...   \n",
       " 29111862             0             0             0             1   \n",
       " 29111880             0             0             0             1   \n",
       " 29111890             0             1             0             0   \n",
       " 29111931             0             1             0             0   \n",
       " 29111954             0             1             0             0   \n",
       " 29111955             0             1             0             0   \n",
       " 29111976             0             1             0             0   \n",
       " 29112004             0             1             0             0   \n",
       " 29112011             0             0             1             0   \n",
       " 29112026             0             1             0             0   \n",
       " 29112043             0             1             0             0   \n",
       " 29112047             0             1             0             0   \n",
       " 29112063             1             0             1             0   \n",
       " 29112067             0             0             1             0   \n",
       " 29112079             0             1             0             0   \n",
       " 29112098             0             1             0             0   \n",
       " 29112115             1             0             0             1   \n",
       " 29112130             0             0             1             0   \n",
       " 29112142             0             1             0             0   \n",
       " 29112187             1             0             1             0   \n",
       " 29112191             1             0             1             0   \n",
       " 29112195             0             0             0             1   \n",
       " 29112199             0             1             0             0   \n",
       " 29112200             0             1             0             0   \n",
       " 29112201             0             1             0             0   \n",
       " 29112210             0             1             0             0   \n",
       " 29112213             0             1             0             0   \n",
       " 29112230             1             0             1             0   \n",
       " 29112306             1             0             1             0   \n",
       " 29112313             0             1             0             0   \n",
       " \n",
       "           category_2_mean2  category_3_A_mean2  category_3_B_mean2  \\\n",
       " 115               6.535858           13.067624           10.466082   \n",
       " 132               6.535858           13.067624           10.466082   \n",
       " 148               6.535858           13.067624           10.466082   \n",
       " 168               6.535858           13.067624           10.466082   \n",
       " 213               6.535858           13.067624           10.466082   \n",
       " 294               6.535858           13.067624           10.466082   \n",
       " 306               6.535858           13.067624           10.466082   \n",
       " 365               6.535858           13.067624           10.466082   \n",
       " 368               6.535858           13.067624           10.466082   \n",
       " 400               8.699729            1.411758            1.602647   \n",
       " 407               3.859131            1.411758            1.602647   \n",
       " 410               8.699729            1.411758            1.602647   \n",
       " 414               3.859131            1.411758            1.602647   \n",
       " 438               3.859131            1.411758            1.602647   \n",
       " 455               6.535858            1.411758            1.602647   \n",
       " 465               8.699729            1.411758            1.602647   \n",
       " 467               3.859131            1.411758            1.602647   \n",
       " 481               5.393454            1.411758            1.602647   \n",
       " 493               6.535858            1.411758           10.466082   \n",
       " 496               6.535858            1.411758            1.602647   \n",
       " 505               5.393454            1.411758            1.602647   \n",
       " 508               3.859131            1.411758            1.602647   \n",
       " 517               8.699729            1.411758            1.602647   \n",
       " 522               5.393454           13.067624           10.466082   \n",
       " 562               5.393454           13.067624           10.466082   \n",
       " 566               5.393454           13.067624           10.466082   \n",
       " 599               5.393454           13.067624           10.466082   \n",
       " 615               5.393454           13.067624           10.466082   \n",
       " 630               5.393454           13.067624           10.466082   \n",
       " 631               5.393454           13.067624           10.466082   \n",
       " ...                    ...                 ...                 ...   \n",
       " 29111862          6.535858            1.411758           10.466082   \n",
       " 29111880          8.699729            1.411758           10.466082   \n",
       " 29111890          8.699729           13.067624           10.466082   \n",
       " 29111931          3.859131           13.067624           10.466082   \n",
       " 29111954          6.535858           13.067624           10.466082   \n",
       " 29111955          6.535858           13.067624           10.466082   \n",
       " 29111976          5.393454           13.067624           10.466082   \n",
       " 29112004          6.535858           13.067624           10.466082   \n",
       " 29112011          6.535858            1.411758            1.602647   \n",
       " 29112026          6.764233           13.067624           10.466082   \n",
       " 29112043          6.535858           13.067624           10.466082   \n",
       " 29112047          6.535858           13.067624           10.466082   \n",
       " 29112063          6.535858            1.411758            1.602647   \n",
       " 29112067          3.859131            1.411758            1.602647   \n",
       " 29112079          6.535858           13.067624           10.466082   \n",
       " 29112098          3.859131           13.067624           10.466082   \n",
       " 29112115          6.535858            1.411758           10.466082   \n",
       " 29112130          6.764233            1.411758            1.602647   \n",
       " 29112142          6.535858           13.067624           10.466082   \n",
       " 29112187          6.535858            1.411758            1.602647   \n",
       " 29112191          6.535858            1.411758            1.602647   \n",
       " 29112195          3.859131            1.411758           10.466082   \n",
       " 29112199          3.859131           13.067624           10.466082   \n",
       " 29112200          3.859131           13.067624           10.466082   \n",
       " 29112201          3.859131           13.067624           10.466082   \n",
       " 29112210          6.535858           13.067624           10.466082   \n",
       " 29112213          6.535858           13.067624           10.466082   \n",
       " 29112230          6.535858            1.411758            1.602647   \n",
       " 29112306          6.535858            1.411758            1.602647   \n",
       " 29112313          6.535858           13.067624           10.466082   \n",
       " \n",
       "           category_3_C_mean2  \n",
       " 115                 7.448447  \n",
       " 132                 7.448447  \n",
       " 148                 7.448447  \n",
       " 168                 7.448447  \n",
       " 213                 7.448447  \n",
       " 294                 7.448447  \n",
       " 306                 7.448447  \n",
       " 365                 7.448447  \n",
       " 368                 7.448447  \n",
       " 400                 7.448447  \n",
       " 407                 7.448447  \n",
       " 410                 7.448447  \n",
       " 414                 7.448447  \n",
       " 438                 7.448447  \n",
       " 455                 7.448447  \n",
       " 465                 7.448447  \n",
       " 467                 7.448447  \n",
       " 481                 7.448447  \n",
       " 493                 0.724690  \n",
       " 496                 7.448447  \n",
       " 505                 7.448447  \n",
       " 508                 7.448447  \n",
       " 517                 7.448447  \n",
       " 522                 7.448447  \n",
       " 562                 7.448447  \n",
       " 566                 7.448447  \n",
       " 599                 7.448447  \n",
       " 615                 7.448447  \n",
       " 630                 7.448447  \n",
       " 631                 7.448447  \n",
       " ...                      ...  \n",
       " 29111862            0.724690  \n",
       " 29111880            0.724690  \n",
       " 29111890            7.448447  \n",
       " 29111931            7.448447  \n",
       " 29111954            7.448447  \n",
       " 29111955            7.448447  \n",
       " 29111976            7.448447  \n",
       " 29112004            7.448447  \n",
       " 29112011            7.448447  \n",
       " 29112026            7.448447  \n",
       " 29112043            7.448447  \n",
       " 29112047            7.448447  \n",
       " 29112063            7.448447  \n",
       " 29112067            7.448447  \n",
       " 29112079            7.448447  \n",
       " 29112098            7.448447  \n",
       " 29112115            0.724690  \n",
       " 29112130            7.448447  \n",
       " 29112142            7.448447  \n",
       " 29112187            7.448447  \n",
       " 29112191            7.448447  \n",
       " 29112195            0.724690  \n",
       " 29112199            7.448447  \n",
       " 29112200            7.448447  \n",
       " 29112201            7.448447  \n",
       " 29112210            7.448447  \n",
       " 29112213            7.448447  \n",
       " 29112230            7.448447  \n",
       " 29112306            7.448447  \n",
       " 29112313            7.448447  \n",
       " \n",
       " [2516909 rows x 35 columns],\n",
       "                 card_id  transactions_count  city_id_nunique  \\\n",
       " 0       C_ID_00007093c1                   2                2   \n",
       " 1       C_ID_0001238066                  26                8   \n",
       " 2       C_ID_0001506ef0                   2                1   \n",
       " 3       C_ID_0001793786                  31                7   \n",
       " 4       C_ID_000183fdda                  11                2   \n",
       " 5       C_ID_0002709b5a                   3                3   \n",
       " 6       C_ID_000298032a                   1                1   \n",
       " 7       C_ID_0002ba3c2e                   3                1   \n",
       " 8       C_ID_0002c7c2c1                   6                3   \n",
       " 9       C_ID_00032df08f                   8                1   \n",
       " 10      C_ID_0003754056                   1                1   \n",
       " 11      C_ID_000377f6a0                   1                1   \n",
       " 12      C_ID_0003be3c83                   6                2   \n",
       " 13      C_ID_0003f41435                   2                2   \n",
       " 14      C_ID_00042d509c                   2                2   \n",
       " 15      C_ID_0004587331                   2                1   \n",
       " 16      C_ID_0004725b87                   2                2   \n",
       " 17      C_ID_0004888ddd                   2                1   \n",
       " 18      C_ID_0004b68c49                   2                2   \n",
       " 19      C_ID_0004c2a5ab                   2                2   \n",
       " 20      C_ID_00057b99fe                   1                1   \n",
       " 21      C_ID_000599daf9                   9                2   \n",
       " 22      C_ID_0005f16cc8                   3                2   \n",
       " 23      C_ID_0006152db8                   2                1   \n",
       " 24      C_ID_000616f4a8                   5                2   \n",
       " 25      C_ID_000664aa02                   8                4   \n",
       " 26      C_ID_00079fec55                   1                1   \n",
       " 27      C_ID_0007a60a33                   1                1   \n",
       " 28      C_ID_0007fe3157                   9                1   \n",
       " 29      C_ID_000844d8f1                  15                5   \n",
       " ...                 ...                 ...              ...   \n",
       " 289971  C_ID_fffaed41f0                  18                6   \n",
       " 289972  C_ID_fffb79fb56                   1                1   \n",
       " 289973  C_ID_fffba72dc5                   2                2   \n",
       " 289974  C_ID_fffbdf036b                  21                2   \n",
       " 289975  C_ID_fffbee5c24                   5                1   \n",
       " 289976  C_ID_fffc96bf24                   3                1   \n",
       " 289977  C_ID_fffcb74f49                   3                2   \n",
       " 289978  C_ID_fffcf66e12                   1                1   \n",
       " 289979  C_ID_fffd070e0e                   3                2   \n",
       " 289980  C_ID_fffd1207a8                  10                5   \n",
       " 289981  C_ID_fffd24dcf3                   9                4   \n",
       " 289982  C_ID_fffd337484                   4                3   \n",
       " 289983  C_ID_fffd345048                  17                3   \n",
       " 289984  C_ID_fffd351e7f                  30               12   \n",
       " 289985  C_ID_fffd93c8bf                  13                6   \n",
       " 289986  C_ID_fffd943b91                   3                3   \n",
       " 289987  C_ID_fffdad8aa0                  14                7   \n",
       " 289988  C_ID_fffde15ab6                   1                1   \n",
       " 289989  C_ID_fffdf34798                   5                2   \n",
       " 289990  C_ID_fffe29a04a                   4                1   \n",
       " 289991  C_ID_fffe78b232                   8                2   \n",
       " 289992  C_ID_fffe866457                   8                3   \n",
       " 289993  C_ID_fffea6de74                  12                2   \n",
       " 289994  C_ID_fffeced303                   2                1   \n",
       " 289995  C_ID_fffeed3a89                  51                7   \n",
       " 289996  C_ID_ffff1d9928                   4                2   \n",
       " 289997  C_ID_ffff579d3a                   1                1   \n",
       " 289998  C_ID_ffff756266                   1                1   \n",
       " 289999  C_ID_ffff828181                   8                6   \n",
       " 290000  C_ID_fffffd5772                   3                1   \n",
       " \n",
       "         merchant_category_id_nunique  merchant_id_nunique  state_id_nunique  \\\n",
       " 0                                  2                    2                 2   \n",
       " 1                                 15                   25                 4   \n",
       " 2                                  2                    2                 1   \n",
       " 3                                 21                   31                 5   \n",
       " 4                                  9                   11                 2   \n",
       " 5                                  3                    3                 3   \n",
       " 6                                  1                    1                 1   \n",
       " 7                                  2                    3                 1   \n",
       " 8                                  5                    6                 1   \n",
       " 9                                  7                    8                 1   \n",
       " 10                                 1                    1                 1   \n",
       " 11                                 1                    1                 1   \n",
       " 12                                 6                    6                 1   \n",
       " 13                                 2                    2                 1   \n",
       " 14                                 2                    2                 2   \n",
       " 15                                 2                    2                 1   \n",
       " 16                                 2                    2                 2   \n",
       " 17                                 2                    2                 1   \n",
       " 18                                 2                    2                 2   \n",
       " 19                                 1                    2                 2   \n",
       " 20                                 1                    1                 1   \n",
       " 21                                 8                    9                 1   \n",
       " 22                                 3                    3                 2   \n",
       " 23                                 2                    2                 1   \n",
       " 24                                 5                    5                 2   \n",
       " 25                                 8                    8                 1   \n",
       " 26                                 1                    1                 1   \n",
       " 27                                 1                    1                 1   \n",
       " 28                                 8                    9                 1   \n",
       " 29                                11                   15                 2   \n",
       " ...                              ...                  ...               ...   \n",
       " 289971                            15                   18                 4   \n",
       " 289972                             1                    1                 1   \n",
       " 289973                             2                    2                 1   \n",
       " 289974                            15                   21                 1   \n",
       " 289975                             3                    5                 1   \n",
       " 289976                             3                    3                 1   \n",
       " 289977                             3                    3                 1   \n",
       " 289978                             1                    1                 1   \n",
       " 289979                             3                    3                 1   \n",
       " 289980                             7                   10                 2   \n",
       " 289981                             7                    9                 2   \n",
       " 289982                             4                    4                 2   \n",
       " 289983                            13                   17                 1   \n",
       " 289984                             8                   30                 8   \n",
       " 289985                            10                   13                 4   \n",
       " 289986                             3                    3                 2   \n",
       " 289987                            13                   14                 1   \n",
       " 289988                             1                    1                 1   \n",
       " 289989                             5                    5                 2   \n",
       " 289990                             3                    4                 1   \n",
       " 289991                             3                    8                 1   \n",
       " 289992                             6                    8                 1   \n",
       " 289993                             9                   12                 2   \n",
       " 289994                             2                    2                 1   \n",
       " 289995                            28                   51                 5   \n",
       " 289996                             4                    4                 2   \n",
       " 289997                             1                    1                 1   \n",
       " 289998                             1                    1                 1   \n",
       " 289999                             6                    8                 4   \n",
       " 290000                             3                    3                 1   \n",
       " \n",
       "         purchase_Year_nunique  subsector_id_nunique  purchase_amount_sum  \\\n",
       " 0                           1                     2            -1.328524   \n",
       " 1                           1                     9           -14.850055   \n",
       " 2                           1                     2            -1.447354   \n",
       " 3                           1                    14            -0.229620   \n",
       " 4                           1                     6            -6.590778   \n",
       " 5                           1                     3            -2.018827   \n",
       " 6                           1                     1            -0.641872   \n",
       " 7                           1                     2            -2.071059   \n",
       " 8                           1                     4            -3.929448   \n",
       " 9                           1                     6            -2.791535   \n",
       " 10                          1                     1            -0.709342   \n",
       " 11                          1                     1            -0.613172   \n",
       " 12                          1                     5            -3.441312   \n",
       " 13                          1                     1            -1.465641   \n",
       " 14                          1                     2            -1.187951   \n",
       " 15                          1                     2            -1.468541   \n",
       " 16                          1                     2            -1.238395   \n",
       " 17                          1                     2            -1.386226   \n",
       " 18                          1                     2            -1.222903   \n",
       " 19                          1                     1            -1.179115   \n",
       " 20                          1                     1            -0.701828   \n",
       " 21                          1                     8            -5.232908   \n",
       " 22                          1                     3            -1.758493   \n",
       " 23                          1                     2            -1.066462   \n",
       " 24                          1                     5            -2.274788   \n",
       " 25                          1                     6            -4.240256   \n",
       " 26                          1                     1            -0.326466   \n",
       " 27                          1                     1            -0.737892   \n",
       " 28                          1                     7            -6.044384   \n",
       " 29                          1                     9           -10.655360   \n",
       " ...                       ...                   ...                  ...   \n",
       " 289971                      1                    10           -11.716877   \n",
       " 289972                      1                     1            -0.671775   \n",
       " 289973                      1                     2            -0.870215   \n",
       " 289974                      1                     9           -13.873543   \n",
       " 289975                      1                     1            -2.858794   \n",
       " 289976                      1                     3             0.792047   \n",
       " 289977                      1                     3            -1.947030   \n",
       " 289978                      1                     1            -0.685825   \n",
       " 289979                      1                     2             5.385231   \n",
       " 289980                      1                     6            -6.791247   \n",
       " 289981                      1                     4            -3.165605   \n",
       " 289982                      1                     3            -1.548737   \n",
       " 289983                      1                     9           -11.339216   \n",
       " 289984                      1                     6           -15.998261   \n",
       " 289985                      1                     7            -7.907957   \n",
       " 289986                      1                     3            -1.913371   \n",
       " 289987                      1                    10            -9.770884   \n",
       " 289988                      1                     1            -0.706486   \n",
       " 289989                      1                     4            -3.452056   \n",
       " 289990                      1                     3            -2.788981   \n",
       " 289991                      1                     3            -4.452611   \n",
       " 289992                      1                     5            -4.342917   \n",
       " 289993                      1                     8            -8.133580   \n",
       " 289994                      1                     2            -1.434912   \n",
       " 289995                      1                    14           -28.437944   \n",
       " 289996                      1                     4            -2.460351   \n",
       " 289997                      1                     1            -0.664262   \n",
       " 289998                      1                     1             0.378578   \n",
       " 289999                      1                     6            -4.964204   \n",
       " 290000                      1                     3            -2.125470   \n",
       " \n",
       "         purchase_amount_max          ...            category_3_C_sum  \\\n",
       " 0                 -0.656749          ...                           0   \n",
       " 1                 -0.078318          ...                           4   \n",
       " 2                 -0.715352          ...                           0   \n",
       " 3                  3.129932          ...                           0   \n",
       " 4                 -0.107680          ...                           4   \n",
       " 5                 -0.611669          ...                           0   \n",
       " 6                 -0.641872          ...                           0   \n",
       " 7                 -0.667883          ...                           0   \n",
       " 8                 -0.497768          ...                           0   \n",
       " 9                  0.449203          ...                           0   \n",
       " 10                -0.709342          ...                           0   \n",
       " 11                -0.613172          ...                           0   \n",
       " 12                -0.167936          ...                           2   \n",
       " 13                -0.726998          ...                           0   \n",
       " 14                -0.467264          ...                           1   \n",
       " 15                -0.728876          ...                           0   \n",
       " 16                -0.574103          ...                           2   \n",
       " 17                -0.692812          ...                           0   \n",
       " 18                -0.596943          ...                           0   \n",
       " 19                -0.535725          ...                           1   \n",
       " 20                -0.701828          ...                           0   \n",
       " 21                -0.222483          ...                           0   \n",
       " 22                -0.461404          ...                           0   \n",
       " 23                -0.326165          ...                           0   \n",
       " 24                -0.329561          ...                           3   \n",
       " 25                -0.255541          ...                           0   \n",
       " 26                -0.326466          ...                           0   \n",
       " 27                -0.737892          ...                           0   \n",
       " 28                -0.563554          ...                           0   \n",
       " 29                -0.638717          ...                           0   \n",
       " ...                     ...          ...                         ...   \n",
       " 289971            -0.025635          ...                           0   \n",
       " 289972            -0.671775          ...                           0   \n",
       " 289973            -0.311139          ...                           0   \n",
       " 289974            -0.064749          ...                           0   \n",
       " 289975            -0.467415          ...                           2   \n",
       " 289976             2.183142          ...                           1   \n",
       " 289977            -0.633623          ...                           0   \n",
       " 289978            -0.685825          ...                           0   \n",
       " 289979             3.280197          ...                           0   \n",
       " 289980            -0.536537          ...                           0   \n",
       " 289981             0.154683          ...                           1   \n",
       " 289982             0.290778          ...                           2   \n",
       " 289983            -0.494432          ...                           0   \n",
       " 289984             0.763046          ...                           0   \n",
       " 289985            -0.319223          ...                           0   \n",
       " 289986            -0.607236          ...                           1   \n",
       " 289987            -0.535740          ...                           0   \n",
       " 289988            -0.706486          ...                           0   \n",
       " 289989            -0.653743          ...                           0   \n",
       " 289990            -0.667718          ...                           0   \n",
       " 289991            -0.187922          ...                           1   \n",
       " 289992             0.061519          ...                           0   \n",
       " 289993            -0.500939          ...                           0   \n",
       " 289994            -0.707839          ...                           0   \n",
       " 289995             1.957864          ...                           4   \n",
       " 289996            -0.285594          ...                           1   \n",
       " 289997            -0.664262          ...                           0   \n",
       " 289998             0.378578          ...                           0   \n",
       " 289999            -0.468166          ...                           1   \n",
       " 290000            -0.665765          ...                           0   \n",
       " \n",
       "         category_3_C_size  category_2_mean2_mean  category_3_A_mean2_mean  \\\n",
       " 0                       2              -0.550506                -0.509231   \n",
       " 1                      26              -0.550028                -0.512452   \n",
       " 2                       2              -0.550852                -0.592993   \n",
       " 3                      31              -0.551649                -0.592993   \n",
       " 4                      11              -0.550852                -0.516846   \n",
       " 5                       3              -0.551605                -0.537152   \n",
       " 6                       1              -0.550160                -0.592993   \n",
       " 7                       3              -0.555640                -0.592993   \n",
       " 8                       6              -0.550160                -0.592993   \n",
       " 9                       8              -0.550160                -0.509231   \n",
       " 10                      1              -0.550160                -0.592993   \n",
       " 11                      1              -0.550852                -0.592993   \n",
       " 12                      6              -0.550160                -0.509231   \n",
       " 13                      2              -0.550160                -0.592993   \n",
       " 14                      2              -0.550506                -0.509231   \n",
       " 15                      2              -0.550852                -0.592993   \n",
       " 16                      2              -0.550160                -0.509231   \n",
       " 17                      2              -0.550852                -0.592993   \n",
       " 18                      2              -0.550160                -0.509231   \n",
       " 19                      2              -0.550160                -0.509231   \n",
       " 20                      1              -0.550160                -0.592993   \n",
       " 21                      9              -0.550852                -0.592993   \n",
       " 22                      3              -0.550621                -0.592993   \n",
       " 23                      2              -0.557578                -0.592993   \n",
       " 24                      5              -0.550160                -0.509231   \n",
       " 25                      8              -0.550160                -0.592993   \n",
       " 26                      1              -0.550160                -0.509231   \n",
       " 27                      1              -0.549015                -0.592993   \n",
       " 28                      9              -0.550160                -0.509231   \n",
       " 29                     15              -0.550084                -0.592993   \n",
       " ...                   ...                    ...                      ...   \n",
       " 289971                 18              -0.549142                -0.513884   \n",
       " 289972                  1              -0.550160                -0.509231   \n",
       " 289973                  2              -0.550160                -0.592993   \n",
       " 289974                 21              -0.550160                -0.592993   \n",
       " 289975                  5              -0.555640                -0.509231   \n",
       " 289976                  3              -0.557578                -0.509231   \n",
       " 289977                  3              -0.550160                -0.592993   \n",
       " 289978                  1              -0.549015                -0.592993   \n",
       " 289979                  3              -0.550160                -0.592993   \n",
       " 289980                 10              -0.550160                -0.592993   \n",
       " 289981                  9              -0.550160                -0.537152   \n",
       " 289982                  4              -0.550852                -0.509231   \n",
       " 289983                 17              -0.550160                -0.592993   \n",
       " 289984                 30              -0.553114                -0.592993   \n",
       " 289985                 13              -0.554474                -0.592993   \n",
       " 289986                  3              -0.550160                -0.509231   \n",
       " 289987                 14              -0.550160                -0.592993   \n",
       " 289988                  1              -0.550160                -0.509231   \n",
       " 289989                  5              -0.554315                -0.592993   \n",
       " 289990                  4              -0.550160                -0.592993   \n",
       " 289991                  8              -0.550160                -0.509231   \n",
       " 289992                  8              -0.550160                -0.530171   \n",
       " 289993                 12              -0.550699                -0.509231   \n",
       " 289994                  2              -0.550160                -0.509231   \n",
       " 289995                 51              -0.550352                -0.510873   \n",
       " 289996                  4              -0.555724                -0.509231   \n",
       " 289997                  1              -0.550160                -0.592993   \n",
       " 289998                  1              -0.550160                -0.592993   \n",
       " 289999                  8              -0.554069                -0.519701   \n",
       " 290000                  3              -0.550852                -0.509231   \n",
       " \n",
       "         category_3_B_mean2_mean  category_3_C_mean2_mean  \\\n",
       " 0                     -0.606486                -0.599212   \n",
       " 1                     -0.587887                -0.501224   \n",
       " 2                     -0.509773                -0.599212   \n",
       " 3                     -0.509773                -0.599212   \n",
       " 4                     -0.562525                -0.367604   \n",
       " 5                     -0.574248                -0.599212   \n",
       " 6                     -0.509773                -0.599212   \n",
       " 7                     -0.509773                -0.599212   \n",
       " 8                     -0.509773                -0.599212   \n",
       " 9                     -0.606486                -0.599212   \n",
       " 10                    -0.509773                -0.599212   \n",
       " 11                    -0.509773                -0.599212   \n",
       " 12                    -0.574248                -0.386905   \n",
       " 13                    -0.509773                -0.599212   \n",
       " 14                    -0.558129                -0.280752   \n",
       " 15                    -0.509773                -0.599212   \n",
       " 16                    -0.509773                 0.037708   \n",
       " 17                    -0.509773                -0.599212   \n",
       " 18                    -0.606486                -0.599212   \n",
       " 19                    -0.558129                -0.280752   \n",
       " 20                    -0.509773                -0.599212   \n",
       " 21                    -0.509773                -0.599212   \n",
       " 22                    -0.509773                -0.599212   \n",
       " 23                    -0.509773                -0.599212   \n",
       " 24                    -0.548458                -0.217060   \n",
       " 25                    -0.509773                -0.599212   \n",
       " 26                    -0.606486                -0.599212   \n",
       " 27                    -0.509773                -0.599212   \n",
       " 28                    -0.606486                -0.599212   \n",
       " 29                    -0.509773                -0.599212   \n",
       " ...                         ...                      ...   \n",
       " 289971                -0.601113                -0.599212   \n",
       " 289972                -0.606486                -0.599212   \n",
       " 289973                -0.509773                -0.599212   \n",
       " 289974                -0.509773                -0.599212   \n",
       " 289975                -0.567801                -0.344444   \n",
       " 289976                -0.574248                -0.386905   \n",
       " 289977                -0.509773                -0.599212   \n",
       " 289978                -0.509773                -0.599212   \n",
       " 289979                -0.509773                -0.599212   \n",
       " 289980                -0.509773                -0.599212   \n",
       " 289981                -0.563502                -0.528443   \n",
       " 289982                -0.558129                -0.280752   \n",
       " 289983                -0.509773                -0.599212   \n",
       " 289984                -0.509773                -0.599212   \n",
       " 289985                -0.509773                -0.599212   \n",
       " 289986                -0.574248                -0.386905   \n",
       " 289987                -0.509773                -0.599212   \n",
       " 289988                -0.606486                -0.599212   \n",
       " 289989                -0.509773                -0.599212   \n",
       " 289990                -0.509773                -0.599212   \n",
       " 289991                -0.594397                -0.519597   \n",
       " 289992                -0.582308                -0.599212   \n",
       " 289993                -0.606486                -0.599212   \n",
       " 289994                -0.606486                -0.599212   \n",
       " 289995                -0.597004                -0.549257   \n",
       " 289996                -0.582308                -0.439982   \n",
       " 289997                -0.509773                -0.599212   \n",
       " 289998                -0.509773                -0.599212   \n",
       " 289999                -0.582308                -0.519597   \n",
       " 290000                -0.606486                -0.599212   \n",
       " \n",
       "         inverse_avg_transactions_per_day  days_since_last_transaction  \\\n",
       " 0                               3.000000                          282   \n",
       " 1                               2.307692                          261   \n",
       " 2                               2.500000                          300   \n",
       " 3                               1.483871                          381   \n",
       " 4                               5.363636                          261   \n",
       " 5                              19.333333                          261   \n",
       " 6                               0.000000                          293   \n",
       " 7                              13.333333                          400   \n",
       " 8                               7.333333                          298   \n",
       " 9                               2.875000                          352   \n",
       " 10                              0.000000                          339   \n",
       " 11                              0.000000                          292   \n",
       " 12                              6.666667                          272   \n",
       " 13                             16.000000                          279   \n",
       " 14                              6.000000                          329   \n",
       " 15                              6.500000                          328   \n",
       " 16                              0.000000                          265   \n",
       " 17                              0.000000                          312   \n",
       " 18                             17.000000                          337   \n",
       " 19                             24.500000                          271   \n",
       " 20                              0.000000                          293   \n",
       " 21                              5.777778                          264   \n",
       " 22                             11.000000                          278   \n",
       " 23                             20.000000                          275   \n",
       " 24                              6.000000                          273   \n",
       " 25                              5.625000                          275   \n",
       " 26                              0.000000                          338   \n",
       " 27                              0.000000                          308   \n",
       " 28                              5.777778                          269   \n",
       " 29                              3.266667                          270   \n",
       " ...                                  ...                          ...   \n",
       " 289971                          2.888889                          266   \n",
       " 289972                          0.000000                          559   \n",
       " 289973                          0.500000                          301   \n",
       " 289974                          2.809524                          534   \n",
       " 289975                          3.600000                          319   \n",
       " 289976                          7.333333                          279   \n",
       " 289977                         14.666667                          267   \n",
       " 289978                          0.000000                          271   \n",
       " 289979                          9.333333                          265   \n",
       " 289980                          5.900000                          261   \n",
       " 289981                          4.666667                          263   \n",
       " 289982                          4.750000                          477   \n",
       " 289983                          2.941176                          270   \n",
       " 289984                          1.800000                          265   \n",
       " 289985                          3.692308                          261   \n",
       " 289986                         15.666667                          295   \n",
       " 289987                          3.642857                          266   \n",
       " 289988                          0.000000                          318   \n",
       " 289989                          5.400000                          267   \n",
       " 289990                          8.750000                          280   \n",
       " 289991                          7.125000                          264   \n",
       " 289992                          6.750000                          261   \n",
       " 289993                          4.666667                          264   \n",
       " 289994                         27.500000                          264   \n",
       " 289995                          1.176471                          261   \n",
       " 289996                          7.750000                          271   \n",
       " 289997                          0.000000                          301   \n",
       " 289998                          0.000000                          281   \n",
       " 289999                          5.625000                          262   \n",
       " 290000                          8.000000                          295   \n",
       " \n",
       "         category_3_sum_total  category_3_mean_total  \n",
       " 0                          2               0.333333  \n",
       " 1                         26               0.333333  \n",
       " 2                          2               0.333333  \n",
       " 3                         31               0.333333  \n",
       " 4                         11               0.333333  \n",
       " 5                          3               0.333333  \n",
       " 6                          1               0.333333  \n",
       " 7                          3               0.333333  \n",
       " 8                          6               0.333333  \n",
       " 9                          8               0.333333  \n",
       " 10                         1               0.333333  \n",
       " 11                         1               0.333333  \n",
       " 12                         6               0.333333  \n",
       " 13                         2               0.333333  \n",
       " 14                         2               0.333333  \n",
       " 15                         2               0.333333  \n",
       " 16                         2               0.333333  \n",
       " 17                         2               0.333333  \n",
       " 18                         2               0.333333  \n",
       " 19                         2               0.333333  \n",
       " 20                         1               0.333333  \n",
       " 21                         9               0.333333  \n",
       " 22                         3               0.333333  \n",
       " 23                         2               0.333333  \n",
       " 24                         5               0.333333  \n",
       " 25                         8               0.333333  \n",
       " 26                         1               0.333333  \n",
       " 27                         1               0.333333  \n",
       " 28                         9               0.333333  \n",
       " 29                        15               0.333333  \n",
       " ...                      ...                    ...  \n",
       " 289971                    18               0.333333  \n",
       " 289972                     1               0.333333  \n",
       " 289973                     2               0.333333  \n",
       " 289974                    21               0.333333  \n",
       " 289975                     5               0.333333  \n",
       " 289976                     3               0.333333  \n",
       " 289977                     3               0.333333  \n",
       " 289978                     1               0.333333  \n",
       " 289979                     3               0.333333  \n",
       " 289980                    10               0.333333  \n",
       " 289981                     9               0.333333  \n",
       " 289982                     4               0.333333  \n",
       " 289983                    17               0.333333  \n",
       " 289984                    30               0.333333  \n",
       " 289985                    13               0.333333  \n",
       " 289986                     3               0.333333  \n",
       " 289987                    14               0.333333  \n",
       " 289988                     1               0.333333  \n",
       " 289989                     5               0.333333  \n",
       " 289990                     4               0.333333  \n",
       " 289991                     8               0.333333  \n",
       " 289992                     8               0.333333  \n",
       " 289993                    12               0.333333  \n",
       " 289994                     2               0.333333  \n",
       " 289995                    51               0.333333  \n",
       " 289996                     4               0.333333  \n",
       " 289997                     1               0.333333  \n",
       " 289998                     1               0.333333  \n",
       " 289999                     8               0.333333  \n",
       " 290000                     3               0.333333  \n",
       " \n",
       " [290001 rows x 61 columns])"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# authorised_trans_proc_agg, non_authorised_trans_proc, new_trans_proc_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train, df_new, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train_df, df_auth, left_on='card_id', suffix='_auth')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_df = join_dfs(train_df, df_non_auth, left_on='card_id', suffix='_n_auth')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df = join_dfs(test, df_new, left_on='card_id')\n",
    "test_df = join_dfs(test_df, df_auth, left_on='card_id', suffix='_auth')\n",
    "# test_df = join_dfs(test_df, df_non_auth, left_on='card_id', suffix='_n_auth')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((123623, 324), (201917, 325))"
      ]
     },
     "execution_count": 98,
     "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": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "# display(train_df.columns.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "code_folding": []
   },
   "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_auth']+df['card_id_size_n_auth']\n",
    "    df[\"month_lag_sum_total\"]= df[\"month_lag_sum_auth\"]+ df[\"month_lag_sum\"]\n",
    "    df['purchase_amount_total'] = df['purchase_amount_sum']+df['purchase_amount_sum_auth'] + df['purchase_amount_sum_n_auth']\n",
    "    df['installments_total'] = df['installments_sum'] + df['installments_sum_auth'] + df['installments_sum_n_auth']\n",
    "    df[\"category_1_Y_sum_total\"] = df[\"category_1_Y_sum_auth\"]+ df[\"category_1_Y_sum\"] + df[\"category_1_Y_sum_n_auth\"]\n",
    "    df[\"category_2_sum_total\"]= df[\"category_2_sum_auth\"]+ df[\"category_2_sum\"] + df[\"category_2_sum_n_auth\"]\n",
    "    df[\"category_3_A_total\"]= df[\"category_3_A_sum\"]+ df[\"category_3_A_sum_auth\"] + df[\"category_3_A_sum_n_auth\"]\n",
    "    df[\"category_3_B_total\"]= df[\"category_3_B_sum\"]+ df[\"category_3_B_sum_auth\"] + df[\"category_3_B_sum_n_auth\"]\n",
    "    df[\"category_3_C_total\"]= df[\"category_3_C_sum\"]+ df[\"category_3_C_sum_auth\"] + df[\"category_3_C_sum_n_auth\"]\n",
    "    df[\"category_3_sum_total_all\"]= df[\"category_3_sum_total\"]+ df[\"category_3_sum_total_auth\"] + df[\"category_3_sum_total_n_auth\"]\n",
    "    df['hist_first_buy'] = (df[['purchase_date_amin_auth', 'purchase_date_amin_n_auth']].min(axis=1) - df['first_active_month']).dt.days\n",
    "    df['new_first_buy'] = (df['purchase_date_amin'] - df['first_active_month']).dt.days"
   ]
  },
  {
   "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>merchant_id_nunique</th>\n",
       "      <th>merchant_id_nunique_auth</th>\n",
       "      <th>merchant_id_nunique_n_auth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23.0</td>\n",
       "      <td>93</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.0</td>\n",
       "      <td>141</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.0</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36.0</td>\n",
       "      <td>65</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   merchant_id_nunique  merchant_id_nunique_auth  merchant_id_nunique_n_auth\n",
       "0                 23.0                        93                          13\n",
       "1                  6.0                       141                           9\n",
       "2                  1.0                        13                           1\n",
       "3                  7.0                        50                           0\n",
       "4                 36.0                        65                           2"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df[['merchant_id_nunique', 'merchant_id_nunique_auth', 'merchant_id_nunique_n_auth']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 338), (123623, 337))"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_df.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mark the outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_df.to_feather('train_df_proc_new_m')\n",
    "# test_df.to_feather('test_df_proc_new_m')\n",
    "train_df = feather.read_dataframe('train_df_proc_new_m')\n",
    "test_df = feather.read_dataframe('test_df_proc_new_m')"
   ]
  },
  {
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>first_active_month</th>\n",
       "      <td>2017-06-01 00:00:00</td>\n",
       "      <td>2017-01-01 00:00:00</td>\n",
       "      <td>2016-08-01 00:00:00</td>\n",
       "      <td>2017-09-01 00:00:00</td>\n",
       "      <td>2017-11-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_92a2005557</td>\n",
       "      <td>C_ID_3d0044924f</td>\n",
       "      <td>C_ID_d639edf6cd</td>\n",
       "      <td>C_ID_186d6a6901</td>\n",
       "      <td>C_ID_cdbd2c0db2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_1</th>\n",
       "      <td>0.0131446</td>\n",
       "      <td>0.0107116</td>\n",
       "      <td>0.0106099</td>\n",
       "      <td>0.0107116</td>\n",
       "      <td>0.00805849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_2</th>\n",
       "      <td>0.00875212</td>\n",
       "      <td>0.0113848</td>\n",
       "      <td>0.00875212</td>\n",
       "      <td>0.0141664</td>\n",
       "      <td>0.0141664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_3</th>\n",
       "      <td>0.0114275</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>target</th>\n",
       "      <td>-0.820283</td>\n",
       "      <td>0.392913</td>\n",
       "      <td>0.688056</td>\n",
       "      <td>0.142495</td>\n",
       "      <td>-0.159749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthYear</th>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthMonth</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthWeek</th>\n",
       "      <td>22</td>\n",
       "      <td>52</td>\n",
       "      <td>31</td>\n",
       "      <td>35</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthDay</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthDayofweek</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthDayofyear</th>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>214</td>\n",
       "      <td>244</td>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_month_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_month_start</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_quarter_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_quarter_start</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_year_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_year_start</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthElapsed</th>\n",
       "      <td>1496275200</td>\n",
       "      <td>1483228800</td>\n",
       "      <td>1470009600</td>\n",
       "      <td>1504224000</td>\n",
       "      <td>1509494400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_purchase_amount_mean</th>\n",
       "      <td>-0.575835</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.664077</td>\n",
       "      <td>-0.492765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_purchase_amount_min</th>\n",
       "      <td>-0.575835</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.665244</td>\n",
       "      <td>-0.561099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_purchase_amount_max</th>\n",
       "      <td>-0.575835</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.66291</td>\n",
       "      <td>-0.424431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_purchase_amount_std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00165047</td>\n",
       "      <td>0.0966388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_purchase_amount_mean</th>\n",
       "      <td>-0.575835</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.624006</td>\n",
       "      <td>-0.358858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_purchase_amount_min</th>\n",
       "      <td>-0.575835</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.681272</td>\n",
       "      <td>-0.571202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_purchase_amount_max</th>\n",
       "      <td>-0.575835</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.56674</td>\n",
       "      <td>-0.175915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_purchase_amount_std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0809864</td>\n",
       "      <td>0.199277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_purchase_amount_mean</th>\n",
       "      <td>-0.458993</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.664077</td>\n",
       "      <td>-0.53472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_purchase_amount_min</th>\n",
       "      <td>-0.606593</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.665244</td>\n",
       "      <td>-0.671174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_purchase_amount_max</th>\n",
       "      <td>-0.296112</td>\n",
       "      <td>-0.725956</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-0.66291</td>\n",
       "      <td>-0.326762</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>purchase_amount_max_mean_n_auth</th>\n",
       "      <td>-0.608962</td>\n",
       "      <td>0.15432</td>\n",
       "      <td>-0.669484</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.34005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_max_std_n_auth</th>\n",
       "      <td>0.127612</td>\n",
       "      <td>0.970537</td>\n",
       "      <td>0.0452109</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.44895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_std_mean_n_auth</th>\n",
       "      <td>0.0510777</td>\n",
       "      <td>0.535838</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0578573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_std_std_n_auth</th>\n",
       "      <td>0.0502026</td>\n",
       "      <td>0.75779</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0818226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_count_mean_n_auth</th>\n",
       "      <td>2.16667</td>\n",
       "      <td>1.83333</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_count_std_n_auth</th>\n",
       "      <td>1.16905</td>\n",
       "      <td>1.60208</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.707107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_sum_mean_n_auth</th>\n",
       "      <td>0.666667</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_sum_std_n_auth</th>\n",
       "      <td>0.816497</td>\n",
       "      <td>7.64199</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.0416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_mean_mean_n_auth</th>\n",
       "      <td>0.319444</td>\n",
       "      <td>4.03333</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_mean_std_n_auth</th>\n",
       "      <td>0.423007</td>\n",
       "      <td>3.52231</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.77817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_min_mean_n_auth</th>\n",
       "      <td>0.166667</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_min_std_n_auth</th>\n",
       "      <td>0.408248</td>\n",
       "      <td>3.72827</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.77817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_max_mean_n_auth</th>\n",
       "      <td>0.5</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_max_std_n_auth</th>\n",
       "      <td>0.547723</td>\n",
       "      <td>4.28952</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.77817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_std_mean_n_auth</th>\n",
       "      <td>0.269338</td>\n",
       "      <td>1.78185</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_std_std_n_auth</th>\n",
       "      <td>0.312603</td>\n",
       "      <td>2.51992</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>elapsed_time</th>\n",
       "      <td>594</td>\n",
       "      <td>745</td>\n",
       "      <td>898</td>\n",
       "      <td>502</td>\n",
       "      <td>441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_total</th>\n",
       "      <td>283</td>\n",
       "      <td>356</td>\n",
       "      <td>44</td>\n",
       "      <td>84</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_sum_total</th>\n",
       "      <td>-925</td>\n",
       "      <td>-1703</td>\n",
       "      <td>-346</td>\n",
       "      <td>-206</td>\n",
       "      <td>-113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_total</th>\n",
       "      <td>-179.213</td>\n",
       "      <td>-214.362</td>\n",
       "      <td>-29.8677</td>\n",
       "      <td>-54.1457</td>\n",
       "      <td>-68.6139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_total</th>\n",
       "      <td>4</td>\n",
       "      <td>549</td>\n",
       "      <td>0</td>\n",
       "      <td>89</td>\n",
       "      <td>217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_sum_total</th>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_sum_total</th>\n",
       "      <td>295</td>\n",
       "      <td>356</td>\n",
       "      <td>204</td>\n",
       "      <td>261</td>\n",
       "      <td>583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_total</th>\n",
       "      <td>279</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_total</th>\n",
       "      <td>4</td>\n",
       "      <td>282</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_total</th>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_sum_total_all</th>\n",
       "      <td>283</td>\n",
       "      <td>356</td>\n",
       "      <td>44</td>\n",
       "      <td>84</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hist_first_buy</th>\n",
       "      <td>26</td>\n",
       "      <td>5</td>\n",
       "      <td>163</td>\n",
       "      <td>25</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new_first_buy</th>\n",
       "      <td>277</td>\n",
       "      <td>396</td>\n",
       "      <td>635</td>\n",
       "      <td>187</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>outliers</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>339 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      0                    1  \\\n",
       "first_active_month                  2017-06-01 00:00:00  2017-01-01 00:00:00   \n",
       "card_id                                 C_ID_92a2005557      C_ID_3d0044924f   \n",
       "feature_1                                     0.0131446            0.0107116   \n",
       "feature_2                                    0.00875212            0.0113848   \n",
       "feature_3                                     0.0114275            0.0102828   \n",
       "target                                        -0.820283             0.392913   \n",
       "first_active_monthYear                             2017                 2017   \n",
       "first_active_monthMonth                               6                    1   \n",
       "first_active_monthWeek                               22                   52   \n",
       "first_active_monthDay                                 1                    1   \n",
       "first_active_monthDayofweek                           3                    6   \n",
       "first_active_monthDayofyear                         152                    1   \n",
       "first_active_monthIs_month_end                    False                False   \n",
       "first_active_monthIs_month_start                   True                 True   \n",
       "first_active_monthIs_quarter_end                  False                False   \n",
       "first_active_monthIs_quarter_start                False                 True   \n",
       "first_active_monthIs_year_end                     False                False   \n",
       "first_active_monthIs_year_start                   False                 True   \n",
       "first_active_monthElapsed                    1496275200           1483228800   \n",
       "category_1_Y_purchase_amount_mean             -0.575835            -0.725956   \n",
       "category_1_Y_purchase_amount_min              -0.575835            -0.725956   \n",
       "category_1_Y_purchase_amount_max              -0.575835            -0.725956   \n",
       "category_1_Y_purchase_amount_std                    NaN                  NaN   \n",
       "installments_purchase_amount_mean             -0.575835            -0.725956   \n",
       "installments_purchase_amount_min              -0.575835            -0.725956   \n",
       "installments_purchase_amount_max              -0.575835            -0.725956   \n",
       "installments_purchase_amount_std                    NaN                  NaN   \n",
       "city_id_purchase_amount_mean                  -0.458993            -0.725956   \n",
       "city_id_purchase_amount_min                   -0.606593            -0.725956   \n",
       "city_id_purchase_amount_max                   -0.296112            -0.725956   \n",
       "...                                                 ...                  ...   \n",
       "purchase_amount_max_mean_n_auth               -0.608962              0.15432   \n",
       "purchase_amount_max_std_n_auth                 0.127612             0.970537   \n",
       "purchase_amount_std_mean_n_auth               0.0510777             0.535838   \n",
       "purchase_amount_std_std_n_auth                0.0502026              0.75779   \n",
       "installments_count_mean_n_auth                  2.16667              1.83333   \n",
       "installments_count_std_n_auth                   1.16905              1.60208   \n",
       "installments_sum_mean_n_auth                   0.666667                    7   \n",
       "installments_sum_std_n_auth                    0.816497              7.64199   \n",
       "installments_mean_mean_n_auth                  0.319444              4.03333   \n",
       "installments_mean_std_n_auth                   0.423007              3.52231   \n",
       "installments_min_mean_n_auth                   0.166667                  3.5   \n",
       "installments_min_std_n_auth                    0.408248              3.72827   \n",
       "installments_max_mean_n_auth                        0.5                    5   \n",
       "installments_max_std_n_auth                    0.547723              4.28952   \n",
       "installments_std_mean_n_auth                   0.269338              1.78185   \n",
       "installments_std_std_n_auth                    0.312603              2.51992   \n",
       "elapsed_time                                        594                  745   \n",
       "card_id_total                                       283                  356   \n",
       "month_lag_sum_total                                -925                -1703   \n",
       "purchase_amount_total                          -179.213             -214.362   \n",
       "installments_total                                    4                  549   \n",
       "category_1_Y_sum_total                                0                   31   \n",
       "category_2_sum_total                                295                  356   \n",
       "category_3_A_total                                  279                    2   \n",
       "category_3_B_total                                    4                  282   \n",
       "category_3_C_total                                    0                   72   \n",
       "category_3_sum_total_all                            283                  356   \n",
       "hist_first_buy                                       26                    5   \n",
       "new_first_buy                                       277                  396   \n",
       "outliers                                              0                    0   \n",
       "\n",
       "                                                      2                    3  \\\n",
       "first_active_month                  2016-08-01 00:00:00  2017-09-01 00:00:00   \n",
       "card_id                                 C_ID_d639edf6cd      C_ID_186d6a6901   \n",
       "feature_1                                     0.0106099            0.0107116   \n",
       "feature_2                                    0.00875212            0.0141664   \n",
       "feature_3                                     0.0102828            0.0102828   \n",
       "target                                         0.688056             0.142495   \n",
       "first_active_monthYear                             2016                 2017   \n",
       "first_active_monthMonth                               8                    9   \n",
       "first_active_monthWeek                               31                   35   \n",
       "first_active_monthDay                                 1                    1   \n",
       "first_active_monthDayofweek                           0                    4   \n",
       "first_active_monthDayofyear                         214                  244   \n",
       "first_active_monthIs_month_end                    False                False   \n",
       "first_active_monthIs_month_start                   True                 True   \n",
       "first_active_monthIs_quarter_end                  False                False   \n",
       "first_active_monthIs_quarter_start                False                False   \n",
       "first_active_monthIs_year_end                     False                False   \n",
       "first_active_monthIs_year_start                   False                False   \n",
       "first_active_monthElapsed                    1470009600           1504224000   \n",
       "category_1_Y_purchase_amount_mean             -0.700326            -0.664077   \n",
       "category_1_Y_purchase_amount_min              -0.700326            -0.665244   \n",
       "category_1_Y_purchase_amount_max              -0.700326             -0.66291   \n",
       "category_1_Y_purchase_amount_std                    NaN           0.00165047   \n",
       "installments_purchase_amount_mean             -0.700326            -0.624006   \n",
       "installments_purchase_amount_min              -0.700326            -0.681272   \n",
       "installments_purchase_amount_max              -0.700326             -0.56674   \n",
       "installments_purchase_amount_std                    NaN            0.0809864   \n",
       "city_id_purchase_amount_mean                  -0.700326            -0.664077   \n",
       "city_id_purchase_amount_min                   -0.700326            -0.665244   \n",
       "city_id_purchase_amount_max                   -0.700326             -0.66291   \n",
       "...                                                 ...                  ...   \n",
       "purchase_amount_max_mean_n_auth               -0.669484                  NaN   \n",
       "purchase_amount_max_std_n_auth                0.0452109                  NaN   \n",
       "purchase_amount_std_mean_n_auth                     NaN                  NaN   \n",
       "purchase_amount_std_std_n_auth                      NaN                  NaN   \n",
       "installments_count_mean_n_auth                        1                  NaN   \n",
       "installments_count_std_n_auth                         0                  NaN   \n",
       "installments_sum_mean_n_auth                          0                  NaN   \n",
       "installments_sum_std_n_auth                           0                  NaN   \n",
       "installments_mean_mean_n_auth                         0                  NaN   \n",
       "installments_mean_std_n_auth                          0                  NaN   \n",
       "installments_min_mean_n_auth                          0                  NaN   \n",
       "installments_min_std_n_auth                           0                  NaN   \n",
       "installments_max_mean_n_auth                          0                  NaN   \n",
       "installments_max_std_n_auth                           0                  NaN   \n",
       "installments_std_mean_n_auth                        NaN                  NaN   \n",
       "installments_std_std_n_auth                         NaN                  NaN   \n",
       "elapsed_time                                        898                  502   \n",
       "card_id_total                                        44                   84   \n",
       "month_lag_sum_total                                -346                 -206   \n",
       "purchase_amount_total                          -29.8677             -54.1457   \n",
       "installments_total                                    0                   89   \n",
       "category_1_Y_sum_total                                0                   13   \n",
       "category_2_sum_total                                204                  261   \n",
       "category_3_A_total                                   44                    3   \n",
       "category_3_B_total                                    0                   74   \n",
       "category_3_C_total                                    0                    7   \n",
       "category_3_sum_total_all                             44                   84   \n",
       "hist_first_buy                                      163                   25   \n",
       "new_first_buy                                       635                  187   \n",
       "outliers                                              0                    0   \n",
       "\n",
       "                                                      4  \n",
       "first_active_month                  2017-11-01 00:00:00  \n",
       "card_id                                 C_ID_cdbd2c0db2  \n",
       "feature_1                                    0.00805849  \n",
       "feature_2                                     0.0141664  \n",
       "feature_3                                     0.0102828  \n",
       "target                                        -0.159749  \n",
       "first_active_monthYear                             2017  \n",
       "first_active_monthMonth                              11  \n",
       "first_active_monthWeek                               44  \n",
       "first_active_monthDay                                 1  \n",
       "first_active_monthDayofweek                           2  \n",
       "first_active_monthDayofyear                         305  \n",
       "first_active_monthIs_month_end                    False  \n",
       "first_active_monthIs_month_start                   True  \n",
       "first_active_monthIs_quarter_end                  False  \n",
       "first_active_monthIs_quarter_start                False  \n",
       "first_active_monthIs_year_end                     False  \n",
       "first_active_monthIs_year_start                   False  \n",
       "first_active_monthElapsed                    1509494400  \n",
       "category_1_Y_purchase_amount_mean             -0.492765  \n",
       "category_1_Y_purchase_amount_min              -0.561099  \n",
       "category_1_Y_purchase_amount_max              -0.424431  \n",
       "category_1_Y_purchase_amount_std              0.0966388  \n",
       "installments_purchase_amount_mean             -0.358858  \n",
       "installments_purchase_amount_min              -0.571202  \n",
       "installments_purchase_amount_max              -0.175915  \n",
       "installments_purchase_amount_std               0.199277  \n",
       "city_id_purchase_amount_mean                   -0.53472  \n",
       "city_id_purchase_amount_min                   -0.671174  \n",
       "city_id_purchase_amount_max                   -0.326762  \n",
       "...                                                 ...  \n",
       "purchase_amount_max_mean_n_auth                 3.34005  \n",
       "purchase_amount_max_std_n_auth                  5.44895  \n",
       "purchase_amount_std_mean_n_auth               0.0578573  \n",
       "purchase_amount_std_std_n_auth                0.0818226  \n",
       "installments_count_mean_n_auth                      2.5  \n",
       "installments_count_std_n_auth                  0.707107  \n",
       "installments_sum_mean_n_auth                         19  \n",
       "installments_sum_std_n_auth                     24.0416  \n",
       "installments_mean_mean_n_auth                       6.5  \n",
       "installments_mean_std_n_auth                    7.77817  \n",
       "installments_min_mean_n_auth                        6.5  \n",
       "installments_min_std_n_auth                     7.77817  \n",
       "installments_max_mean_n_auth                        6.5  \n",
       "installments_max_std_n_auth                     7.77817  \n",
       "installments_std_mean_n_auth                          0  \n",
       "installments_std_std_n_auth                           0  \n",
       "elapsed_time                                        441  \n",
       "card_id_total                                       169  \n",
       "month_lag_sum_total                                -113  \n",
       "purchase_amount_total                          -68.6139  \n",
       "installments_total                                  217  \n",
       "category_1_Y_sum_total                               17  \n",
       "category_2_sum_total                                583  \n",
       "category_3_A_total                                    1  \n",
       "category_3_B_total                                  160  \n",
       "category_3_C_total                                    8  \n",
       "category_3_sum_total_all                            169  \n",
       "hist_first_buy                                       11  \n",
       "new_first_buy                                       121  \n",
       "outliers                                              0  \n",
       "\n",
       "[339 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    199710\n",
       "1      2207\n",
       "Name: outliers, dtype: int64"
      ]
     },
     "execution_count": 104,
     "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 - no improvements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.to_feather('train_df_proc_new_m')\n",
    "test_df.to_feather('test_df_proc_new_m')"
   ]
  },
  {
   "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.7"
  },
  "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
}