Elo feature engineering 3.693 .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Load-dependencies\" data-toc-modified-id=\"Load-dependencies-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Load dependencies</a></span></li><li><span><a href=\"#Read-csv-files-to-DFs\" data-toc-modified-id=\"Read-csv-files-to-DFs-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Read csv files to DFs</a></span></li><li><span><a href=\"#Fill-missing-values\" data-toc-modified-id=\"Fill-missing-values-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Fill missing values</a></span></li><li><span><a href=\"#Add-date-part\" data-toc-modified-id=\"Add-date-part-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Add date part</a></span></li><li><span><a href=\"#Add-extra-columns-(purchased-on-weekend,-monthend,-month_diff-etc.\" data-toc-modified-id=\"Add-extra-columns-(purchased-on-weekend,-monthend,-month_diff-etc.-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Add extra columns (purchased on weekend, monthend, month_diff etc.</a></span></li><li><span><a href=\"#Rolling-aggregate-on-month\" data-toc-modified-id=\"Rolling-aggregate-on-month-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Rolling aggregate on month</a></span></li><li><span><a href=\"#Time-between-successive-transactions\" data-toc-modified-id=\"Time-between-successive-transactions-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Time between successive transactions</a></span></li><li><span><a href=\"#Additional-features\" data-toc-modified-id=\"Additional-features-8\"><span class=\"toc-item-num\">8&nbsp;&nbsp;</span>Additional features</a></span></li><li><span><a href=\"#Aggregate-by-card_id\" data-toc-modified-id=\"Aggregate-by-card_id-9\"><span class=\"toc-item-num\">9&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-10\"><span class=\"toc-item-num\">10&nbsp;&nbsp;</span>Add exta interpreted columns on aggregates</a></span></li><li><span><a href=\"#Aggregate-on-categories\" data-toc-modified-id=\"Aggregate-on-categories-11\"><span class=\"toc-item-num\">11&nbsp;&nbsp;</span>Aggregate on categories</a></span></li><li><span><a href=\"#Aggregate-on-month\" data-toc-modified-id=\"Aggregate-on-month-12\"><span class=\"toc-item-num\">12&nbsp;&nbsp;</span>Aggregate on month</a></span></li><li><span><a href=\"#Load-test-&amp;-train-DFs\" data-toc-modified-id=\"Load-test-&amp;-train-DFs-13\"><span class=\"toc-item-num\">13&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-14\"><span class=\"toc-item-num\">14&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-15\"><span class=\"toc-item-num\">15&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-16\"><span class=\"toc-item-num\">16&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-17\"><span class=\"toc-item-num\">17&nbsp;&nbsp;</span>Mark the outliers</a></span></li><li><span><a href=\"#Adding-train-days\" data-toc-modified-id=\"Adding-train-days-18\"><span class=\"toc-item-num\">18&nbsp;&nbsp;</span>Adding train days</a></span></li><li><span><a href=\"#Target-encode-the-outliers\" data-toc-modified-id=\"Target-encode-the-outliers-19\"><span class=\"toc-item-num\">19&nbsp;&nbsp;</span>Target encode the outliers</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Load dependencies\n",
    "2. Read csv files to dataframes\n",
    "3. Fill missing values\n",
    "4. Add date part \n",
    "5. Add extra columns (purchased on weekend, monthend, month_diff etc.\n",
    "6. Aggregate by card_id\n",
    "7. Aggregate by categories\n",
    "8. Mark categorical columns\n",
    "9. Add exta interpreted columns on aggregates\n",
    "10. Load test & train csvs to dfs\n",
    "11. Add date part to test & train dfs\n",
    "12. Merge train & test with new & old transactions history\n",
    "13. Add extra columns like age, total transactions, installments, purchase amount, first buy etc\n",
    "14. Mark the outliers\n",
    "15. Target encode the outliers \n",
    "16. Save to feather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.notebook.set_autosave_interval(120000)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Autosaving every 120 seconds\n"
     ]
    }
   ],
   "source": [
    "%matplotlib inline\n",
    "%load_ext autoreload\n",
    "%autoreload 4\n",
    "%autosave 120"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from fastai.io import *\n",
    "from fastai.structured import *\n",
    "from sklearn.ensemble import RandomForestRegressor, RandomForestClassifier\n",
    "from pandas_summary import DataFrameSummary\n",
    "from IPython.display import display\n",
    "from sklearn import metrics\n",
    "import feather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read csv files to DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = '../data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = ['historical_transactions', 'new_merchant_transactions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [pd.read_csv(f'{PATH}{c}.csv') for c in files]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_nas_for_transactions_df(df):\n",
    "    # Fill nas for category_3 with mode\n",
    "    df['category_2'].fillna(1.0,inplace=True)\n",
    "    df['category_3'].fillna('A',inplace=True)\n",
    "    df['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n",
    "    df['installments'].replace(-1, np.nan,inplace=True)\n",
    "    df['installments'].replace(999, np.nan,inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [fill_nas_for_transactions_df(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add date part"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_datepart(hist_trans, 'purchase_date', drop=False, time=True)\n",
    "add_datepart(new_hist_trans, 'purchase_date', drop=False, time=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add extra columns (purchased on weekend, monthend, month_diff etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/scipy/stats/stats.py:1713: FutureWarning: Using a non-tuple sequence for multidimensional indexing is deprecated; use `arr[tuple(seq)]` instead of `arr[seq]`. In the future this will be interpreted as an array index, `arr[np.array(seq)]`, which will result either in an error or a different result.\n",
      "  return np.add.reduce(sorted[indexer] * weights, axis=axis) / sumval\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYAAAAELCAYAAADQsFGkAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4xLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvDW2N/gAAHvRJREFUeJzt3X+cVXW97/HXe++BGVREBbSOQIMP8Qckao5wyx9ZpGE3JQ0TtSum5gO9lHUfZnoqU8vT0es9Po6mcbhqIZpi/jhNhg/KH2SaBwF/oIhcR6Sc8BgKDWLMwMx87h97DWez28PsYTbuPa738/GYx17ru75r789abPZ7/dh7LUUEZmaWPplKF2BmZpXhADAzSykHgJlZSjkAzMxSygFgZpZSDgAzs5RyAJiZpZQDwMwspRwAZmYpVVPpAgoNGzYs6uvrK12GmVm/snTp0rcjYnhv5qm6AKivr2fJkiWVLsPMrF+R9MfezuNDQGZmKeUAMDNLKQeAmVlKVd05gGK2bNlCc3Mzra2tlS7F3id1dXWMGDGCAQMGVLoUsw+sfhEAzc3NDB48mPr6eiRVuhzbySKCd955h+bmZkaPHl3pcsw+sPrFIaDW1laGDh3qD/+UkMTQoUO9x2e2k/WLAAD84Z8y/vc22/n6TQCYmVl5OQDMzFKqX5wE/iDr+uXzsGHDKl3K+27hwoUMHDiQT3ziE5UuZVtLflrpCmxHNXyl0hX0K94DeB+0t7dXuoSqtHDhQv7whz9Uugyz1Op3ewBX/Wo5L6/ZUNbnHPsPu/P9k8Ztt8/q1auZPHkyEydO5LnnnuOAAw7gjjvuYOzYsVu34JcsWcIll1zCwoULufLKK1mzZg2rV69m2LBhzJ07l29/+9ssWLAASXz1q1/la1/7GgA33XQTv/rVr9iyZQu/+MUvOOigg3jmmWf4xje+waZNmxg0aBA//elPOfDAA1m+fDlf+cpX2Lx5M52dndx///2MGTOGO++8kxtvvJHNmzczceJEbrnlFrLZbNFlufDCC1m8eDGbNm1i6tSpXHXVVUBub+TMM8/k8ccfZ8uWLcyePZvLL7+cpqYmvvWtbzFjxgwigksvvZSHH34YSXz3u9/l9NNPZ+HChVx//fU89NBDAMycOZOGhgbOOecc6uvrmT59+jbLWFdXx6xZs8hms9x5553cdNNNHHPMMWX8VzWznvS7AKiklStXctttt3HUUUdx7rnncsstt2y3/9KlS3nyyScZNGgQP/nJT3j99dd57rnnqKmpYd26dVv7DRs2jGeffZZbbrmF66+/nltvvZWDDjqIJ554gpqaGh555BH+8R//kfvvv59Zs2Zx8cUXc9ZZZ7F582Y6OjpYsWIF8+bN46mnnmLAgAFcdNFF3HXXXZx99tlF67rmmmvYa6+96OjoYNKkSSxbtozx48cDMHLkSJ5++mm++c1vcs455/DUU0/R2trKuHHjmDFjBg888ADPP/88L7zwAm+//TZHHnkkxx57bI/rrtgyzpgxg912241LLrmkF/8KZlYu/S4AetpS35lGjhzJUUcdBcCXv/xlbrzxxu32P/nkkxk0aBAAjzzyCDNmzKCmJrfK99prr639Tj31VACOOOIIHnjgAQBaWlqYPn06r776KpLYsmULAB//+Me55ppraG5u5tRTT2XMmDE8+uijLF26lCOPPBKATZs2sffee3db17333svs2bNpb2/nzTff5OWXX94aACeffDIAhxxyCBs3bmTw4MEMHjyYuro6/vrXv/Lkk09yxhlnkM1m2WefffjkJz/J4sWL2X333be7Looto5lVVr8LgEoq/G66JGpqaujs7AT4ux8u7brrrluHI6Lb77bX1tYCkM1mt54v+N73vsenPvUpHnzwQVavXs1xxx0HwJlnnsnEiRP59a9/zWc/+1luvfVWIoLp06fzox/9qMdleP3117n++utZvHgxe+65J+ecc842dXfVkslktg53jbe3txMRRZ83fz0UWxfFltHMKssngXvhT3/6E08//TQAd999N0cffTT19fUsXboUgPvvv7/beU844QRmzZq19cMv/xBQMS0tLey7774A/OxnP9vavmrVKvbbbz++/vWvc/LJJ7Ns2TImTZrEfffdx1/+8petz/3HPxa/NPiGDRvYddddGTJkCG+99RYPP/xwaQufOPbYY5k3bx4dHR2sXbuWJ554ggkTJvCRj3yEl19+mba2NlpaWnj00Ud7fK7Bgwfz7rvv9ur1zax8HAC9cPDBBzNnzhzGjx/PunXruPDCC/n+97/PxRdfzDHHHNPtSVeA888/n1GjRjF+/HgOPfRQfv7zn2/3tS699FIuv/xyjjrqKDo6Ora2z5s3j49+9KMcdthhvPLKK5x99tmMHTuWH/7wh5xwwgmMHz+e448/njfffLPo8x566KEcfvjhjBs3jnPPPXfrIa1SnXLKKVuX4dOf/jTXXXcdH/rQhxg5ciRf+tKXGD9+PGeddRaHH354j8910kkn8eCDD3LYYYfx+9//vld1mFnfqbtd+kppaGiIwjuCrVixgoMPPrhCFeWsXr2az3/+87z00ksVrSNNKvbv7t8B9F8p/h2ApKUR0dCbebwHYGaWUj4JXKL6+vp+t/U/ceJE2tratmmbO3cuhxxySIUqMrNq0m8CYHvforHiFi1aVOkSdli1HZo0+yAq6RCQpMmSVkpqknRZkem1kuYl0xdJqk/az5L0fN5fp6TDeltkXV0d77zzjj8UUqLrhjB1dXWVLsXsA63HPQBJWeBm4HigGVgsqTEiXs7rdh6wPiL2lzQNuBY4PSLuAu5KnucQ4JcR8XxvixwxYgTNzc2sXbu2t7NaP9V1S0gz23lKOQQ0AWiKiFUAku4BpgD5ATAFuDIZvg/4sSTFtpvsZwB370iRAwYM8K0BzczKrJRDQPsCb+SNNydtRftERDvQAgwt6HM6OxgAZmZWfqUEQLEzr4UH47fbR9JE4G8RUfRrNJIukLRE0hIf5jEze3+UEgDNwMi88RHAmu76SKoBhgD51zqYxna2/iNidkQ0RETD8OHDS6nbzMz6qJQAWAyMkTRa0kByH+aNBX0agenJ8FTgsa7j/5IywGnAPeUp2czMyqHHk8AR0S5pJrAAyAK3R8RySVcDSyKiEbgNmCupidyW/7S8pzgWaO46iWxmZtWhpB+CRcR8YH5B2xV5w63ktvKLzbsQ+G87XqKZme0MvhaQmVlKOQDMzFLKAWBmllIOADOzlHIAmJmllAPAzCylHABmZinlADAzSykHgJlZSjkAzMxSygFgZpZSDgAzs5RyAJiZpZQDwMwspRwAZmYp5QAwM0spB4CZWUo5AMzMUqqkAJA0WdJKSU2SLisyvVbSvGT6Ikn1edPGS3pa0nJJL0qqK1/5Zma2o3oMAElZ4GbgRGAscIaksQXdzgPWR8T+wA3Atcm8NcCdwIyIGAccB2wpW/VmZrbDStkDmAA0RcSqiNgM3ANMKegzBZiTDN8HTJIk4ARgWUS8ABAR70RER3lKNzOzviglAPYF3sgbb07aivaJiHagBRgKHACEpAWSnpV0ad9LNjOzcqgpoY+KtEWJfWqAo4Ejgb8Bj0paGhGPbjOzdAFwAcCoUaNKKMnMzPqqlD2AZmBk3vgIYE13fZLj/kOAdUn77yLi7Yj4GzAf+FjhC0TE7IhoiIiG4cOH934pzMys10oJgMXAGEmjJQ0EpgGNBX0agenJ8FTgsYgIYAEwXtIuSTB8Eni5PKWbmVlf9HgIKCLaJc0k92GeBW6PiOWSrgaWREQjcBswV1ITuS3/acm86yX9C7kQCWB+RPx6Jy2LmZn1QinnAIiI+eQO3+S3XZE33Aqc1s28d5L7KqiZmVUR/xLYzCylHABmZinlADAzSykHgJlZSjkAzMxSygFgZpZSDgAzs5RyAJiZpZQDwMwspRwAZmYp5QAwM0spB4CZWUo5AMzMUsoBYGaWUg4AM7OUcgCYmaWUA8DMLKUcAGZmKVVSAEiaLGmlpCZJlxWZXitpXjJ9kaT6pL1e0iZJzyd/s8pbvpmZ7age7wksKQvcDBwPNAOLJTVGxMt53c4D1kfE/pKmAdcCpyfTXouIw8pct5mZ9VEpewATgKaIWBURm4F7gCkFfaYAc5Lh+4BJklS+Ms3MrNxKCYB9gTfyxpuTtqJ9IqIdaAGGJtNGS3pO0u8kHdPHes3MrEx6PAQEFNuSjxL7vAmMioh3JB0B/LukcRGxYZuZpQuACwBGjRpVQklmZtZXpewBNAMj88ZHAGu66yOpBhgCrIuItoh4ByAilgKvAQcUvkBEzI6IhohoGD58eO+XwszMeq2UAFgMjJE0WtJAYBrQWNCnEZieDE8FHouIkDQ8OYmMpP2AMcCq8pRuZmZ90eMhoIholzQTWABkgdsjYrmkq4ElEdEI3AbMldQErCMXEgDHAldLagc6gBkRsW5nLIiZmfVOKecAiIj5wPyCtivyhluB04rMdz9wfx9rNDOzncC/BDYzSykHgJlZSjkAzMxSygFgZpZSDgAzs5RyAJiZpZQDwMwspRwAZmYp5QAwM0spB4CZWUo5AMzMUsoBYGaWUg4AM7OUcgCYmaWUA8DMLKUcAGZmKeUAMDNLKQeAmVlKOQDMzFKqpACQNFnSSklNki4rMr1W0rxk+iJJ9QXTR0naKOmS8pRtZmZ91WMASMoCNwMnAmOBMySNLeh2HrA+IvYHbgCuLZh+A/Bw38s1M7NyKWUPYALQFBGrImIzcA8wpaDPFGBOMnwfMEmSACR9AVgFLC9PyWZmVg6lBMC+wBt5481JW9E+EdEOtABDJe0KfBu4qu+lmplZOZUSACrSFiX2uQq4ISI2bvcFpAskLZG0ZO3atSWUZGZmfVVTQp9mYGTe+AhgTTd9miXVAEOAdcBEYKqk64A9gE5JrRHx4/yZI2I2MBugoaGhMFzMzGwnKCUAFgNjJI0G/gxMA84s6NMITAeeBqYCj0VEAMd0dZB0JbCx8MPfzMwqo8cAiIh2STOBBUAWuD0ilku6GlgSEY3AbcBcSU3ktvyn7cyizcys70rZAyAi5gPzC9quyBtuBU7r4Tmu3IH6zMxsJ/Evgc3MUsoBYGaWUg4AM7OUcgCYmaWUA8DMLKUcAGZmKeUAMDNLKQeAmVlKOQDMzFLKAWBmllIOADOzlHIAmJmllAPAzCylHABmZinlADAzSykHgJlZSjkAzMxSygFgZpZSJQWApMmSVkpqknRZkem1kuYl0xdJqk/aJ0h6Pvl7QdIp5S3fzMx2VI8BICkL3AycCIwFzpA0tqDbecD6iNgfuAG4Nml/CWiIiMOAycC/SSrpPsRmZrZzlbIHMAFoiohVEbEZuAeYUtBnCjAnGb4PmCRJEfG3iGhP2uuAKEfRZmbWd6UEwL7AG3njzUlb0T7JB34LMBRA0kRJy4EXgRl5gWBmZhVUSgCoSFvhlny3fSJiUUSMA44ELpdU93cvIF0gaYmkJWvXri2hJDMz66tSAqAZGJk3PgJY012f5Bj/EGBdfoeIWAG8B3y08AUiYnZENEREw/Dhw0uv3szMdlgpAbAYGCNptKSBwDSgsaBPIzA9GZ4KPBYRkcxTAyDpI8CBwOqyVG5mZn3S4zdyIqJd0kxgAZAFbo+I5ZKuBpZERCNwGzBXUhO5Lf9pyexHA5dJ2gJ0AhdFxNs7Y0HMzKx3SvpKZkTMB+YXtF2RN9wKnFZkvrnA3D7WaGZmO4F/CWxmllIOADOzlHIAmJmllAPAzCylHABmZinlADAzSykHgJlZSjkAzMxSygFgZpZSDgAzs5RyAJiZpZQDwMwspRwAZmYp5QAwM0spB4CZWUo5AMzMUsoBYGaWUg4AM7OUKikAJE2WtFJSk6TLikyvlTQvmb5IUn3SfrykpZJeTB4/Xd7yzcxsR/UYAJKywM3AicBY4AxJYwu6nQesj4j9gRuAa5P2t4GTIuIQYDq+P7CZWdUoZQ9gAtAUEasiYjNwDzCloM8UYE4yfB8wSZIi4rmIWJO0LwfqJNWWo3AzM+ubUgJgX+CNvPHmpK1on4hoB1qAoQV9vgg8FxFtO1aqmZmVU00JfVSkLXrTR9I4coeFTij6AtIFwAUAo0aNKqEkMzPrq1L2AJqBkXnjI4A13fWRVAMMAdYl4yOAB4GzI+K1Yi8QEbMjoiEiGoYPH967JTAzsx1SSgAsBsZIGi1pIDANaCzo00juJC/AVOCxiAhJewC/Bi6PiKfKVbSZmfVdjwGQHNOfCSwAVgD3RsRySVdLOjnpdhswVFIT8L+Arq+KzgT2B74n6fnkb++yL4WZmfVaKecAiIj5wPyCtivyhluB04rM90Pgh32s0czMdgL/EtjMLKUcAGZmKeUAMDNLKQeAmVlKOQDMzFLKAWBmllIOADOzlHIAmJmllAPAzCylHABmZinlADAzSykHgJlZSjkAzMxSygFgZpZSDgAzs5RyAJiZpZQDwMwspRwAZmYp5QAwM0upkgJA0mRJKyU1SbqsyPRaSfOS6Ysk1SftQyU9LmmjpB+Xt3QzM+uLHgNAUha4GTgRGAucIWlsQbfzgPURsT9wA3Bt0t4KfA+4pGwVm5lZWZSyBzABaIqIVRGxGbgHmFLQZwowJxm+D5gkSRHxXkQ8SS4IzMysipQSAPsCb+SNNydtRftERDvQAgwtR4FmZrZzlBIAKtIWO9Cn+xeQLpC0RNKStWvXljqbmZn1QSkB0AyMzBsfAazpro+kGmAIsK7UIiJidkQ0RETD8OHDS53NzMz6oJQAWAyMkTRa0kBgGtBY0KcRmJ4MTwUei4iS9wDMzOz9V9NTh4holzQTWABkgdsjYrmkq4ElEdEI3AbMldREbst/Wtf8klYDuwMDJX0BOCEiXi7/opiZWW/0GAAAETEfmF/QdkXecCtwWjfz1vehPjMz20n8S2Azs5RyAJiZpZQDwMwspRwAZmYp5QAwM0spB4CZWUo5AMzMUsoBYGaWUg4AM7OUcgCYmaWUA8DMLKUcAGZmKeUAMDNLKQeAmVlKOQDMzFLKAWBmllIOADOzlHIAmJmlVEkBIGmypJWSmiRdVmR6raR5yfRFkurzpl2etK+U9NnylW5mZn3R4z2BJWWBm4HjgWZgsaTGghu7nwesj4j9JU0DrgVOlzSW3A3ixwH/ADwi6YCI6Cj3gpj1Vx2dnazf8B7rN2xg48YNbH5vA52tLdS0tZDtbCWUpVM1hLK5v0wNKJM8ZlEmC8mfMjUok03+MmQyWTLZGmoGDKR24EBqa2sZVFvLoNqB7FI7gEzGBwHSrJSbwk8AmiJiFYCke4ApQH4ATAGuTIbvA34sSUn7PRHRBrwuqSl5vqfLU75ZebVueo+29zaRzWTIZkRGucdsJoMy6vXzbdzUxjstG3h347ts2riB9k0tZFpbGLj5r+za/lf2iPXsFS0MUyfD8uZrjwzvaA82aRDZ6CBLOzV0UBMd1NDOgGR8gPq2LfVe1PE31dFKLW2qo011bMnW0Z6poyNbR2RriZpalK1F2QGQyeTCRZmtAZN7zJBRFmVzj5msyGayZDJZspkMmWwm95jJIAGIrrUpKfeXG0laY5v2rt7K5Obtmm/r/F2PG1uStkxuevIc+W25v8y2r53SICwlAPYF3sgbbwYmdtcnItoltQBDk/b/KJh33x2u1mwne+GXNzLxlX8uOq0zRAcZOskkj8obztCp5JEMgdg93mU3tbJbwfNsiF1Yl9mTdzN78McBH+a12iFkBu3BwF0Gs9tuu7Pn7ruz5+67sk8JH0qdnZ1sae+kvbOT9o52trR30N7RSUdHB+0dHbnH9g42b9lM++Y22re00dHeRmxpI9rbUHsbmY5Wsh1t1HS2MqCzjd3a/0pdtFIXrexCK7uorQxr9n3yyKV9forOEAEEIpKwCYBkPJJ+207ftm9ISf/Cvt3P35NS+vRWKQFQ7FWjxD6lzIukC4ALktE2SS+VUFelDQPernQRJXCd5VWGOjcA/1mOWranP6zP/lAj9J86D+ztDKUEQDMwMm98BLCmmz7NkmqAIcC6EuclImYDswEkLYmIhlIXoFJcZ3m5zvLqD3X2hxqhf9XZ23lKOfC1GBgjabSkgeRO6jYW9GkEpifDU4HHIiKS9mnJt4RGA2OAZ3pbpJmZlV+PewDJMf2ZwAIgC9weEcslXQ0siYhG4DZgbnKSdx25kCDpdy+5E8btwP/0N4DMzKpDKYeAiIj5wPyCtivyhluB07qZ9xrgml7UNLsXfSvJdZaX6yyv/lBnf6gRPsB1KnekxszM0iadX341M7PqCQBJ/1vSK5KWSXpQ0h5506rmchKSTpO0XFKnpIa89npJmyQ9n/zNqsY6k2lVsz7zSbpS0p/z1uHnKl1Tl54uh1ItJK2W9GKy/nr9rZCdRdLtkv6S/xVvSXtJ+q2kV5PHPStZY1JTsTqr7n0paaSkxyWtSP6fX5y0926dRkRV/AEnADXJ8LXAtcnwWOAFoBYYDbwGZCtY58Hkvm+7EGjIa68HXqr0eiyhzqpanwU1XwlcUuk6itSVTdbTfsDAZP2NrXRd3dS6GhhW6TqK1HUs8LH8/yPAdcBlyfBlXf/nq7DOqntfAh8GPpYMDwb+X/J/u1frtGr2ACLiNxHRnoz+B7nfDEDe5SQi4nWg63ISFRERKyJiZaVev1TbqbOq1mc/sfVyKBGxGei6HIqVKCKeIPcNwXxTgDnJ8BzgC+9rUUV0U2fViYg3I+LZZPhdYAW5qyz0ap1WTQAUOBd4OBkudimKar2cxGhJz0n6naRjKl1MN6p9fc5MDgPeXg2HBBLVvs7yBfAbSUuTX9hXs30i4k3IfaABe1e4nu2pxvclkDv8DBwOLKKX67Skr4GWi6RHgA8VmfSdiPhl0uc75H4zcFfXbEX679SvLpVSZxFvAqMi4h1JRwD/LmlcRGyosjrf9/W5zYtvp2bgJ8APknp+APwfchsDlVbRddZLR0XEGkl7A7+V9EqyVWs7rlrfl0jaDbgf+EZEbJB6d72g9zUAIuIz25suaTrweWBSJAexKPFyEuXUU53dzNMGtCXDSyW9BhwA7LQTcTtSJxVYn/lKrVnS/wUe2snllKqi66w3ImJN8vgXSQ+SO3xVrQHwlqQPR8Sbkj4M/KXSBRUTEW91DVfT+1LSAHIf/ndFxANJc6/WadUcApI0Gfg2cHJE/C1vUr+4nISk4crdOwFJ+5Grc1Vlqyqqatdn8obtcgpQLRcFLOVyKBUnaVdJg7uGyX2xolrWYTH5l5CZDnS311pR1fi+VG5T/zZgRUT8S96k3q3TSp/Nzjur3UTuOOvzyd+svGnfIfctjJXAiRWu8xRyW4RtwFvAgqT9i8Byct8QeRY4qRrrrLb1WVDzXOBFYFnyRv5wpWvKq+1z5L5p8Rq5Q2wVr6lIjfsl778Xkvdi1dQJ3E3uMOmW5H15HrlLxj8KvJo87lWldVbd+xI4mtwhqWV5n5mf6+069S+BzcxSqmoOAZmZ2fvLAWBmllIOADOzlHIAmJmllAPAzCylHABmZinlALAPpOSyyMMqXUclSDpO0icqXYdVPweA9VuS3tdLmfQjxwEOAOuRA8AqKrmRziuS5iRXW7xP0i75W/CSGiQtTIavlDRb0m+AOyRlJV2f3ARlmaSv5T391yQ9m0w7KJl/gqQ/JFdt/YOkA5P2cZKeSW74sUzSmKT9y3nt/9Z1uY9uluUnkpYkN+i4Kq99taR/kvR0Mv1jkhZIek3SjKSPlLsp0ktJvacn7cdJeijvuX4s6Zy8570qfxmTK0POAL6Z1FytV6W1KuAtKKsGBwLnRcRTkm4HLuqh/xHA0RGxSdKF5G5sc3hEtEvaK6/f2xHxMUkXAZcA5wOvAMcmfT8D/BO5y3jMAP41Iu5KrveTlXQwcDq5K2xukXQLcBZwRzd1fSci1iUh8aik8RGxLJn2RkR8XNINwM+Ao4A6cpdsmAWcChwGHAoMAxZLKuUibtssY0Scr9zd6DZGxPUlzG8p5gCwavBGRDyVDN8JfL2H/o0RsSkZ/gy560a1A0RE/s08uq6QuJTcByzAEGBOsoUfwICk/WngO5JGAA9ExKuSJpELm8XJZXYHsf2rK35JuWvw15C7Y9NYctdqgf+6eNyLwG6Ru4nHu5Jalbv96dHA3RHRQe6Kjr8DjgR6upx4sWU0K4kDwKpB4QWpgtw9IboOUdYVTH8vb1hF5u/Sljx28F/v9R8Aj0fEKcnhkoUAEfFzSYuA/w4skHR+8txzIuLynhYgubLqJcCREbFe0s8K6u6qpTNvuGu8huL3HIBt1wP8/bootoxmJfE5AKsGoyR9PBk+A3iS3L1tj0javrideX8DzOg6IVxwCKiYIcCfk+FzuhqTS3iviogbyW2tjyd3NcWpyt1cpeuG2x/p5nl3JxdMLZL2AU7soY5CTwCnJ+c0hpO7N+0zwB+Bscnlu4cAk0p4rnfJ3SfWbLscAFYNVgDTJS0D9iJ3B6argH+V9HtyW7fduRX4E7BM0gvAmT281nXAjyQ9Re5m711OB16S9DxwEHBHRLwMfJfcLRaXAb8ld2jn70TEC8Bz5I7p3w48VazfdjxI7nDRC8BjwKUR8Z8R8QZwbzLtruQ1evIr4BSfBLae+HLQVlHJYZiHIuKjFS7FLHW8B2BmllLeAzDrpeRkcW1B8/+IiBcrUY/ZjnIAmJmllA8BmZmllAPAzCylHABmZinlADAzSykHgJlZSv1/Y55iMKFboKoAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.kdeplot(new_hist_trans['purchase_amount'])\n",
    "# sns.kdeplot(new_hist_trans['purchase_amount'], bw=.2, label=\"bw: 0.2\")\n",
    "# sns.kdeplot(new_hist_trans['purchase_amount'])\n",
    "plt.legend();\n",
    "plt.xlim(-20,20)\n",
    "sns.distplot(new_hist_trans['purchase_amount'], bins=20, kde=True, rug=False);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_extra_cols(df):\n",
    "    df['purchased_on_weekend'] = (df.purchase_Dayofweek >=5).astype(int)\n",
    "    df['purchased_on_weekday'] = (df.purchase_Dayofweek <5).astype(int)\n",
    "    df['month_diff'] = ((datetime.datetime.today() - df['purchase_date']).dt.days)//30\n",
    "    df['month_diff'] += df['month_lag']\n",
    "    df['authorized_flag'] = df['authorized_flag'].map({'Y':1, 'N':0})\n",
    "    df['category_1'] = df['category_1'].map({'Y':1, 'N':0}) \n",
    "    df['category_3'] = df['category_3'].map({'A':0, 'B':1, 'C':2})\n",
    "#     Trim the purchase_amount\n",
    "#     df['purchase_amount'] = df['purchase_amount'].apply(lambda x: min(x, 0.8)) \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [add_extra_cols(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 33), (1963031, 33))"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Rolling aggregate on month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def aggregate_per_month(history):\n",
    "    grouped = history.groupby(['card_id', 'month_diff'])['purchase_amount']\n",
    "\n",
    "    agg_func = {\n",
    "            'purchase_amount': ['count', 'sum', 'max', 'mean'],\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', 'sum', np.ptp, 'max'])\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": "markdown",
   "metadata": {},
   "source": [
    "### Time between successive transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def time_diff(df):\n",
    "    df['purchase_date_successive_diff'] = df.sort_values('purchase_date').groupby('card_id')['purchase_date'].diff().dt.total_seconds()\n",
    "    df['purchase_date_successive_diff'].fillna(0, inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [time_diff(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans.to_feather('hist_trans_beta')\n",
    "new_hist_trans.to_feather('new_hist_trans_beta')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans = feather.read_dataframe('hist_trans_beta')\n",
    "new_hist_trans = feather.read_dataframe('new_hist_trans_beta')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>authorized_flag</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>card_id</th>\n",
       "      <td>C_ID_415bb3a509</td>\n",
       "      <td>C_ID_415bb3a509</td>\n",
       "      <td>C_ID_415bb3a509</td>\n",
       "      <td>C_ID_415bb3a509</td>\n",
       "      <td>C_ID_ef55cf8d4b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id</th>\n",
       "      <td>107</td>\n",
       "      <td>140</td>\n",
       "      <td>330</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments</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>category_3</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>merchant_category_id</th>\n",
       "      <td>307</td>\n",
       "      <td>307</td>\n",
       "      <td>507</td>\n",
       "      <td>661</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id</th>\n",
       "      <td>M_ID_b0c793002c</td>\n",
       "      <td>M_ID_88920c89e8</td>\n",
       "      <td>M_ID_ad5237ef6b</td>\n",
       "      <td>M_ID_9e84cda3b1</td>\n",
       "      <td>M_ID_3c86fa3831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount</th>\n",
       "      <td>-0.557574</td>\n",
       "      <td>-0.56958</td>\n",
       "      <td>-0.551037</td>\n",
       "      <td>-0.671925</td>\n",
       "      <td>-0.659904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date</th>\n",
       "      <td>2018-03-11 14:57:36</td>\n",
       "      <td>2018-03-19 18:53:37</td>\n",
       "      <td>2018-04-26 14:08:44</td>\n",
       "      <td>2018-03-07 09:43:21</td>\n",
       "      <td>2018-03-22 21:07:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</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>state_id</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id</th>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Year</th>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Month</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Week</th>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Day</th>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofweek</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>116</td>\n",
       "      <td>66</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_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>purchase_Is_month_start</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>purchase_Is_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>purchase_Is_quarter_start</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>purchase_Is_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>purchase_Is_year_start</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>purchase_Hour</th>\n",
       "      <td>14</td>\n",
       "      <td>18</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Minute</th>\n",
       "      <td>57</td>\n",
       "      <td>53</td>\n",
       "      <td>8</td>\n",
       "      <td>43</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Second</th>\n",
       "      <td>36</td>\n",
       "      <td>37</td>\n",
       "      <td>44</td>\n",
       "      <td>21</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <td>1520780256</td>\n",
       "      <td>1521485617</td>\n",
       "      <td>1524751724</td>\n",
       "      <td>1520415801</td>\n",
       "      <td>1521752873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekday</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff</th>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_successive_diff</th>\n",
       "      <td>364455</td>\n",
       "      <td>705361</td>\n",
       "      <td>3.26611e+06</td>\n",
       "      <td>0</td>\n",
       "      <td>40230</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 0                    1  \\\n",
       "authorized_flag                                  1                    1   \n",
       "card_id                            C_ID_415bb3a509      C_ID_415bb3a509   \n",
       "city_id                                        107                  140   \n",
       "category_1                                       0                    0   \n",
       "installments                                     1                    1   \n",
       "category_3                                       1                    1   \n",
       "merchant_category_id                           307                  307   \n",
       "merchant_id                        M_ID_b0c793002c      M_ID_88920c89e8   \n",
       "month_lag                                        1                    1   \n",
       "purchase_amount                          -0.557574             -0.56958   \n",
       "purchase_date                  2018-03-11 14:57:36  2018-03-19 18:53:37   \n",
       "category_2                                       1                    1   \n",
       "state_id                                         9                    9   \n",
       "subsector_id                                    19                   19   \n",
       "purchase_Year                                 2018                 2018   \n",
       "purchase_Month                                   3                    3   \n",
       "purchase_Week                                   10                   12   \n",
       "purchase_Day                                    11                   19   \n",
       "purchase_Dayofweek                               6                    0   \n",
       "purchase_Dayofyear                              70                   78   \n",
       "purchase_Is_month_end                        False                False   \n",
       "purchase_Is_month_start                      False                False   \n",
       "purchase_Is_quarter_end                      False                False   \n",
       "purchase_Is_quarter_start                    False                False   \n",
       "purchase_Is_year_end                         False                False   \n",
       "purchase_Is_year_start                       False                False   \n",
       "purchase_Hour                                   14                   18   \n",
       "purchase_Minute                                 57                   53   \n",
       "purchase_Second                                 36                   37   \n",
       "purchase_Elapsed                        1520780256           1521485617   \n",
       "purchased_on_weekend                             1                    0   \n",
       "purchased_on_weekday                             0                    1   \n",
       "month_diff                                      12                   11   \n",
       "purchase_date_successive_diff               364455               705361   \n",
       "\n",
       "                                                 2                    3  \\\n",
       "authorized_flag                                  1                    1   \n",
       "card_id                            C_ID_415bb3a509      C_ID_415bb3a509   \n",
       "city_id                                        330                   -1   \n",
       "category_1                                       0                    1   \n",
       "installments                                     1                    1   \n",
       "category_3                                       1                    1   \n",
       "merchant_category_id                           507                  661   \n",
       "merchant_id                        M_ID_ad5237ef6b      M_ID_9e84cda3b1   \n",
       "month_lag                                        2                    1   \n",
       "purchase_amount                          -0.551037            -0.671925   \n",
       "purchase_date                  2018-04-26 14:08:44  2018-03-07 09:43:21   \n",
       "category_2                                       1                    1   \n",
       "state_id                                         9                   -1   \n",
       "subsector_id                                    14                    8   \n",
       "purchase_Year                                 2018                 2018   \n",
       "purchase_Month                                   4                    3   \n",
       "purchase_Week                                   17                   10   \n",
       "purchase_Day                                    26                    7   \n",
       "purchase_Dayofweek                               3                    2   \n",
       "purchase_Dayofyear                             116                   66   \n",
       "purchase_Is_month_end                        False                False   \n",
       "purchase_Is_month_start                      False                False   \n",
       "purchase_Is_quarter_end                      False                False   \n",
       "purchase_Is_quarter_start                    False                False   \n",
       "purchase_Is_year_end                         False                False   \n",
       "purchase_Is_year_start                       False                False   \n",
       "purchase_Hour                                   14                    9   \n",
       "purchase_Minute                                  8                   43   \n",
       "purchase_Second                                 44                   21   \n",
       "purchase_Elapsed                        1524751724           1520415801   \n",
       "purchased_on_weekend                             0                    0   \n",
       "purchased_on_weekday                             1                    1   \n",
       "month_diff                                      11                   12   \n",
       "purchase_date_successive_diff          3.26611e+06                    0   \n",
       "\n",
       "                                                 4  \n",
       "authorized_flag                                  1  \n",
       "card_id                            C_ID_ef55cf8d4b  \n",
       "city_id                                         -1  \n",
       "category_1                                       1  \n",
       "installments                                     1  \n",
       "category_3                                       1  \n",
       "merchant_category_id                           166  \n",
       "merchant_id                        M_ID_3c86fa3831  \n",
       "month_lag                                        1  \n",
       "purchase_amount                          -0.659904  \n",
       "purchase_date                  2018-03-22 21:07:53  \n",
       "category_2                                       1  \n",
       "state_id                                        -1  \n",
       "subsector_id                                    29  \n",
       "purchase_Year                                 2018  \n",
       "purchase_Month                                   3  \n",
       "purchase_Week                                   12  \n",
       "purchase_Day                                    22  \n",
       "purchase_Dayofweek                               3  \n",
       "purchase_Dayofyear                              81  \n",
       "purchase_Is_month_end                        False  \n",
       "purchase_Is_month_start                      False  \n",
       "purchase_Is_quarter_end                      False  \n",
       "purchase_Is_quarter_start                    False  \n",
       "purchase_Is_year_end                         False  \n",
       "purchase_Is_year_start                       False  \n",
       "purchase_Hour                                   21  \n",
       "purchase_Minute                                  7  \n",
       "purchase_Second                                 53  \n",
       "purchase_Elapsed                        1521752873  \n",
       "purchased_on_weekend                             0  \n",
       "purchased_on_weekday                             1  \n",
       "month_diff                                      11  \n",
       "purchase_date_successive_diff                40230  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def additional_feats(hist_df):\n",
    "    hist_df['price'] = hist_df['purchase_amount'] / hist_df['installments']\n",
    "    #Christmas : December 25 2017\n",
    "    hist_df['Christmas_Day_2017']=(pd.to_datetime('2017-12-25')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n",
    "    #Mothers Day: May 14 2017\n",
    "    hist_df['Mothers_Day_2017']=(pd.to_datetime('2017-06-04')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n",
    "    #fathers day: August 13 2017\n",
    "    hist_df['fathers_day_2017']=(pd.to_datetime('2017-08-13')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n",
    "    #Childrens day: October 12 2017\n",
    "    hist_df['Children_day_2017']=(pd.to_datetime('2017-10-12')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n",
    "    #Valentine's Day : 12th June, 2017\n",
    "    hist_df['Valentine_Day_2017']=(pd.to_datetime('2017-06-12')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n",
    "    #Black Friday : 24th November 2017\n",
    "    hist_df['Black_Friday_2017']=(pd.to_datetime('2017-11-24') - hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n",
    "    #2018\n",
    "    #Mothers Day: May 13 2018\n",
    "    hist_df['Mothers_Day_2018']=(pd.to_datetime('2018-05-13')-hist_df['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n",
    "    hist_df['duration'] = hist_df['purchase_amount']*hist_df['month_diff']\n",
    "    hist_df['amount_month_ratio'] = hist_df['purchase_amount']/hist_df['month_diff']\n",
    "    return hist_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [additional_feats(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 44), (1963031, 44))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate by card_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_by_card_id(df):\n",
    "    unique_cols = ['city_id', 'merchant_category_id', 'merchant_id', 'state_id', 'subsector_id']\n",
    "\n",
    "    col_seas = ['purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Hour', 'purchase_Minute']\n",
    "\n",
    "    aggs = {}\n",
    "    for c in unique_cols:\n",
    "        aggs[c] = ['nunique'] \n",
    "    for c in col_seas:\n",
    "        aggs[c] = ['nunique', 'mean', 'min', 'max']\n",
    "    \n",
    "    aggs['purchase_amount'] = ['sum','max','min','mean','var','skew','first', 'last']\n",
    "    aggs['installments'] = ['sum','max','mean']\n",
    "    aggs['price'] = ['sum','mean','max']\n",
    "    aggs['purchase_date'] = ['max','min']\n",
    "    aggs['month_lag'] = ['max','min','mean','var','skew']\n",
    "    aggs['month_diff'] = ['max','min','mean','var','skew']\n",
    "    aggs['authorized_flag'] = ['sum', 'mean']\n",
    "    aggs['purchased_on_weekend'] = ['mean']\n",
    "    aggs['purchased_on_weekday'] = ['mean']\n",
    "    aggs['category_1'] = ['sum', 'mean']\n",
    "    aggs['category_2'] = ['mean']\n",
    "    aggs['category_3'] = ['mean']\n",
    "    aggs['card_id'] = ['size', 'count']\n",
    "    aggs['Christmas_Day_2017'] = ['mean']\n",
    "    aggs['Mothers_Day_2017'] = ['mean']\n",
    "    aggs['fathers_day_2017'] = ['mean']\n",
    "    aggs['Children_day_2017'] = ['mean']\n",
    "    aggs['Valentine_Day_2017'] = ['mean']\n",
    "    aggs['Black_Friday_2017'] = ['mean']\n",
    "    aggs['Mothers_Day_2018'] = ['mean']\n",
    "    aggs['duration']=['mean','min','max','var','skew']\n",
    "    aggs['purchase_Elapsed'] = ['max', 'mean', 'skew']\n",
    "    aggs['amount_month_ratio']=['mean','min','max','var','skew']\n",
    "#     aggs['purchase_date_successive_diff'] = ['mean', 'median', 'max', 'min', 'var', 'skew']\n",
    "    for col in ['category_2','category_3']:\n",
    "        df[col+'_mean'] = df.groupby([col])['purchase_amount'].transform('mean')\n",
    "        df[col+'_min'] = df.groupby([col])['purchase_amount'].transform('min')\n",
    "        df[col+'_max'] = df.groupby([col])['purchase_amount'].transform('max')\n",
    "        df[col+'_sum'] = df.groupby([col])['purchase_amount'].transform('sum')\n",
    "        aggs[col+'_mean'] = ['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",
    "\n",
    "    new_df['purchase_date_diff'] = (new_df['purchase_date_max'] - new_df['purchase_date_min']).dt.days\n",
    "    new_df['purchase_date_average'] = new_df['purchase_date_diff']/new_df['card_id_size']\n",
    "    new_df['purchase_date_uptonow'] = (datetime.datetime.today() - new_df['purchase_date_max']).dt.days\n",
    "    new_df['purchase_date_uptomin'] = (datetime.datetime.today() - new_df['purchase_date_min']).dt.days\n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dfs_e = [hist_trans_c_e, new_hist_trans_c_e]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gc\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<function scipy.stats.stats.hmean(a, axis=0, dtype=None)>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scipy.stats.hmean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9min 11s, sys: 1min 13s, total: 10min 25s\n",
      "Wall time: 8min 36s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans_agg, new_hist_trans_agg = [aggregate_by_card_id(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add exta interpreted columns on aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_extra_cols_on_agg(df):\n",
    "    df['inverse_avg_transactions_per_day'] = df['purchase_date_diff']/df['card_id_size']\n",
    "    df['days_since_last_transaction'] = (datetime.datetime.today() - df['purchase_date_max']).dt.days\n",
    "    df['repurchase_merchant_rate'] = df['transactions_count']/df['merchant_id_nunique']\n",
    "#     df['merchant_category_repurchase'] = df['merchant_category_id_nunique']/df['merchant_id_nunique']\n",
    "    df['avg_spend_per_merchant'] = df['purchase_amount_sum']/df['merchant_id_nunique']\n",
    "#     df['avg_trans_per_merchant'] = df['transactions_count']/df['merchant_id_nunique']\n",
    "    df['avg_spend_per_transaction'] = df['purchase_amount_sum']/df['transactions_count']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "[hist_trans_agg, new_hist_trans_agg] = [add_extra_cols_on_agg(df) for df in [hist_trans_agg, \n",
    "                                                                             new_hist_trans_agg]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg.to_feather('hist_trans_agg_beta')\n",
    "new_hist_trans_agg.to_feather('new_hist_trans_agg_beta')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg = feather.read_dataframe('hist_trans_agg_beta')\n",
    "new_hist_trans_agg = feather.read_dataframe('new_hist_trans_agg_beta')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Aggregate on categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def agg_on_cat(df, category, feature):\n",
    "    temp_df = df.pivot_table(index='card_id', columns=category, aggfunc={feature: ['sum', 'mean']})\n",
    "    cols = [category + '_{0[2]}_{0[0]}_{0[1]}'.format(col) for col in temp_df.columns.tolist()]\n",
    "    temp_df.columns = cols\n",
    "    return temp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def get_cat_agg(df):\n",
    "    agg_df = agg_on_cat(df, 'category_1', 'purchase_amount')\n",
    "    agg_df = pd.merge(agg_df, agg_on_cat(df, 'category_2', 'purchase_amount'), on='card_id', how='left')\n",
    "    agg_df = pd.merge(agg_df, agg_on_cat(df, 'category_3', 'purchase_amount'), on='card_id', how='left')\n",
    "    agg_df = pd.merge(agg_df, agg_on_cat(df, 'authorized_flag', 'purchase_amount'), on='card_id', how='left')\n",
    "    return agg_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# %time hist_trans_agg_cat, new_hist_trans_agg_cat = [get_cat_agg(df) for df in [hist_trans, new_hist_trans]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# hist_trans_agg_cat.shape, new_hist_trans_agg_cat.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Aggregate on month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def aggregate_per_month(history):\n",
    "    grouped = history.groupby(['card_id', 'month_diff'])['purchase_amount']\n",
    "\n",
    "    agg_func = {\n",
    "            'purchase_amount': ['count', 'sum', 'max', 'mean'],\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', 'sum', np.ptp, 'max'])\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": 55,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/ipykernel_launcher.py:8: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3min 35s, sys: 10.9 s, total: 3min 45s\n",
      "Wall time: 3min 29s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans_agg_month, new_hist_trans_agg_month = [aggregate_per_month(df) for df in [hist_trans, new_hist_trans]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 21), (290001, 21))"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg_month.shape, new_hist_trans_agg_month.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load test & train DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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": 32,
   "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": 33,
   "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": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train, new_hist_trans_agg, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train_df, hist_trans_agg, left_on='card_id', suffix='_old')\n",
    "# train_df = join_dfs(train_df, hist_trans_agg_month, left_on='card_id', suffix='_old')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df = join_dfs(test, new_hist_trans_agg, left_on='card_id')\n",
    "test_df = join_dfs(test_df, hist_trans_agg, left_on='card_id', suffix='_old')\n",
    "# test_df = join_dfs(test_df, new_hist_trans_agg_month, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((123623, 204), (201917, 205))"
      ]
     },
     "execution_count": 37,
     "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": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in [train_df, test_df]:\n",
    "    df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n",
    "    df['card_id_total'] = df['card_id_size']+df['card_id_size_old']\n",
    "    df['card_id_count_total'] = df['card_id_count']+df['card_id_count_old']\n",
    "    df['card_id_count_ratio'] = df['card_id_count_old']/df['card_id_count']\n",
    "    df['purchase_amount_total'] = df['purchase_amount_sum_old']+df['purchase_amount_sum']\n",
    "    df['purchase_amount_total_mean'] = df['purchase_amount_mean']+df['purchase_amount_mean_old']\n",
    "    df['purchase_amount_total_max'] = df['purchase_amount_max']+df['purchase_amount_max_old']\n",
    "    df['purchase_amount_total_min'] = df['purchase_amount_min']+df['purchase_amount_min_old']\n",
    "    df['purchase_amount_sum_ratio'] = df['purchase_amount_sum_old']/df['purchase_amount_sum']\n",
    "    df['hist_first_buy'] = (df['purchase_date_min_old'] - df['first_active_month']).dt.days\n",
    "    df['new_first_buy'] = (df['purchase_date_min'] - df['first_active_month']).dt.days\n",
    "    df['hist_last_buy'] = (df['purchase_date_max_old'] - df['first_active_month']).dt.days\n",
    "    df['new_last_buy'] = (df['purchase_date_max'] - df['first_active_month']).dt.days\n",
    "    df['purchase_amount_max_total'] = df['purchase_amount_max'] + df['purchase_amount_max_old']\n",
    "    df['avg_spend_per_transaction'] = df['purchase_amount_total']/df['card_id_total']\n",
    "    df['purchased_before_issue'] = df['hist_first_buy'] < 0\n",
    "    df['month_diff_mean'] = df['month_diff_mean']+df['month_diff_mean_old']\n",
    "    df['month_diff_ratio'] = df['month_diff_mean']/df['month_diff_mean_old']\n",
    "    df['month_lag_mean'] = df['month_lag_mean']+df['month_lag_mean_old']\n",
    "    df['month_lag_max'] = df['month_lag_max']+df['month_lag_max_old']\n",
    "    df['month_lag_min'] = df['month_lag_min']+df['month_lag_min_old']\n",
    "    df['category_1_mean'] = df['category_1_mean']+df['category_1_mean_old']\n",
    "    df['installments_total'] = df['installments_sum']+df['installments_sum_old']\n",
    "    df['installments_mean'] = df['installments_mean']+df['installments_mean_old']\n",
    "    df['installments_max'] = df['installments_max']+df['installments_max_old']\n",
    "    df['installments_ratio'] = df['installments_sum']/df['installments_sum_old']\n",
    "    df['price_total'] = df['purchase_amount_total'] / df['installments_total']\n",
    "    df['price_mean'] = df['purchase_amount_mean'] / df['installments_mean']\n",
    "    df['price_max'] = df['purchase_amount_max'] / df['installments_max']\n",
    "    df['duration_mean'] = df['duration_mean']+df['duration_mean_old']\n",
    "    df['duration_min'] = df['duration_min']+df['duration_min_old']\n",
    "    df['duration_max'] = df['duration_max']+df['duration_max_old']\n",
    "    df['amount_month_ratio_mean']=df['amount_month_ratio_mean']+df['amount_month_ratio_mean_old']\n",
    "    df['amount_month_ratio_min']=df['amount_month_ratio_min']+df['amount_month_ratio_min_old']\n",
    "    df['amount_month_ratio_max']=df['amount_month_ratio_max']+df['amount_month_ratio_max_old']\n",
    "    df['CLV'] = df['card_id_count'] * df['purchase_amount_sum'] / df['month_diff_mean']\n",
    "    df['CLV_old'] = df['card_id_count_old'] * df['purchase_amount_sum_old'] / df['month_diff_mean_old']\n",
    "    df['CLV_ratio'] = df['CLV'] / df['CLV_old']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3309"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(test_df['purchased_before_issue']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mark the outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    199710\n",
       "1      2207\n",
       "Name: outliers, dtype: int64"
      ]
     },
     "execution_count": 40,
     "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": [
    "### Adding train days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_days_feature_interaction(df):\n",
    "    # to datetime\n",
    "    df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n",
    "\n",
    "    # datetime features\n",
    "    df['quarter'] = df['first_active_month'].dt.quarter\n",
    "    df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n",
    "\n",
    "    df['days_feature1'] = df['elapsed_time'] * df['feature_1']\n",
    "    df['days_feature2'] = df['elapsed_time'] * df['feature_2']\n",
    "    df['days_feature3'] = df['elapsed_time'] * df['feature_3']\n",
    "\n",
    "    df['days_feature1_ratio'] = df['feature_1'] / df['elapsed_time']\n",
    "    df['days_feature2_ratio'] = df['feature_2'] / df['elapsed_time']\n",
    "    df['days_feature3_ratio'] = df['feature_3'] / df['elapsed_time']\n",
    "    df['feature_sum'] = df['feature_1'] + df['feature_2'] + df['feature_3']\n",
    "    df['feature_prod'] = df['feature_1'] * df['feature_2'] * df['feature_3']\n",
    "    df['feature_mean'] = df['feature_sum']/3\n",
    "    df['feature_max'] = df[['feature_1', 'feature_2', 'feature_3']].max(axis=1)\n",
    "    df['feature_min'] = df[['feature_1', 'feature_2', 'feature_3']].min(axis=1)\n",
    "    df['feature_var'] = df[['feature_1', 'feature_2', 'feature_3']].std(axis=1)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df, test_df = [add_days_feature_interaction(d) for d in [train_df, test_df]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Target encode the outliers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 241), (123623, 239))"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "train_df.to_feather('train_df_beta')\n",
    "test_df.to_feather('test_df_beta')"
   ]
  },
  {
   "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": {
    "height": "624px",
    "left": "114px",
    "top": "111.133px",
    "width": "288px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}