Elo feature engineering - 3.691 - beta.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": 6,
   "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": 7,
   "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": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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": 10,
   "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": 11,
   "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": 12,
   "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": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [add_extra_cols(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 33), (1963031, 33))"
      ]
     },
     "execution_count": 14,
     "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": null,
   "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": 15,
   "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": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [time_diff(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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": 18,
   "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": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [additional_feats(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 44), (1963031, 44))"
      ]
     },
     "execution_count": 22,
     "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": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_hist_trans(df):\n",
    "    unique_cols = ['subsector_id', 'merchant_id', 'merchant_category_id']\n",
    "\n",
    "    col_seas = ['purchase_Month','purchase_Week', 'purchase_Dayofweek', 'purchase_Day', 'purchase_Hour']\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']\n",
    "    aggs['installments'] = ['sum','max','mean','var','skew']\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'] = ['mean']\n",
    "    aggs['purchased_on_weekend'] = ['mean'] # overwrite\n",
    "    aggs['purchase_Dayofweek'] = ['mean'] # overwrite\n",
    "    aggs['purchase_Day'] = ['nunique', 'mean', 'min'] # overwrite\n",
    "    aggs['category_1'] = ['mean']\n",
    "    aggs['category_2'] = ['mean']\n",
    "    aggs['category_3'] = ['mean']\n",
    "    aggs['card_id'] = ['size','count']\n",
    "    aggs['price'] = ['sum','mean','max','min','var']\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['amount_month_ratio']=['mean','min','max','var','skew']\n",
    "\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": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_new_trans(df):\n",
    "    unique_cols = ['subsector_id', 'merchant_id', 'merchant_category_id']\n",
    "\n",
    "    col_seas = ['purchase_Month', 'purchase_Week', 'purchase_Dayofweek','purchase_Day', 'purchase_Hour']\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']\n",
    "    aggs['installments'] = ['sum','max','mean','var','skew']\n",
    "    aggs['purchase_date'] = ['max','min']\n",
    "    aggs['month_lag'] = ['max','min','mean','var','skew']\n",
    "    aggs['month_diff'] = ['mean','var','skew']\n",
    "    aggs['purchased_on_weekend'] = ['mean']\n",
    "    aggs['purchase_Month'] = ['mean', 'min', 'max']\n",
    "    aggs['purchase_Dayofweek'] = ['mean', 'min', 'max']\n",
    "    aggs['category_1'] = ['mean']\n",
    "    aggs['category_2'] = ['mean']\n",
    "    aggs['category_3'] = ['mean']\n",
    "    aggs['card_id'] = ['size','count']\n",
    "    aggs['price'] = ['mean','max','min','var']\n",
    "    aggs['Christmas_Day_2017'] = ['mean']\n",
    "    aggs['Children_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['amount_month_ratio']=['mean','min','max','var','skew']\n",
    "\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": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gc\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5min 11s, sys: 52.3 s, total: 6min 3s\n",
      "Wall time: 4min 49s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans_agg = aggregate_hist_trans(hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3min 16s, sys: 13.2 s, total: 3min 29s\n",
      "Wall time: 3min 7s\n"
     ]
    }
   ],
   "source": [
    "%time new_hist_trans_agg = aggregate_new_trans(new_hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "128"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add exta interpreted columns on aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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": 31,
   "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": 32,
   "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": 33,
   "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": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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": 36,
   "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": 37,
   "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": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train, new_hist_trans_agg, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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": 40,
   "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": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((123623, 169), (201917, 170))"
      ]
     },
     "execution_count": 41,
     "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": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in [train_df, test_df]:\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'] / df['card_id_count_old']\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'] / df['purchase_amount_sum_old']\n",
    "    df['hist_first_buy'] = (df['purchase_date_min_old'] - df['first_active_month']).dt.days\n",
    "    df['new_first_buy'] = (df['purchase_date_min'] - df['first_active_month']).dt.days\n",
    "    df['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['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": "markdown",
   "metadata": {},
   "source": [
    "### Mark the outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    199710\n",
       "1      2207\n",
       "Name: outliers, dtype: int64"
      ]
     },
     "execution_count": 43,
     "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": 44,
   "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",
    "    \n",
    "    df['feature_sum'] = 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": 45,
   "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": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 203), (123623, 201))"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "train_df.to_feather('train_df_3691')\n",
    "test_df.to_feather('test_df_3691')"
   ]
  },
  {
   "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
}