Elo feature engineering 3.690.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=\"#Latest-&amp;-First-5-col-aggregates\" data-toc-modified-id=\"Latest-&amp;-First-5-col-aggregates-9\"><span class=\"toc-item-num\">9&nbsp;&nbsp;</span>Latest &amp; First 5 col aggregates</a></span></li><li><span><a href=\"#Aggregate-by-card_id\" data-toc-modified-id=\"Aggregate-by-card_id-10\"><span class=\"toc-item-num\">10&nbsp;&nbsp;</span>Aggregate by card_id</a></span></li><li><span><a href=\"#Add-exta-interpreted-columns-on-aggregates\" data-toc-modified-id=\"Add-exta-interpreted-columns-on-aggregates-11\"><span class=\"toc-item-num\">11&nbsp;&nbsp;</span>Add exta interpreted columns on aggregates</a></span></li><li><span><a href=\"#Aggregate-on-categories\" data-toc-modified-id=\"Aggregate-on-categories-12\"><span class=\"toc-item-num\">12&nbsp;&nbsp;</span>Aggregate on categories</a></span></li><li><span><a href=\"#Aggregate-on-month\" data-toc-modified-id=\"Aggregate-on-month-13\"><span class=\"toc-item-num\">13&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-14\"><span class=\"toc-item-num\">14&nbsp;&nbsp;</span>Load test &amp; train DFs</a></span></li><li><span><a href=\"#Add-observed-date\" data-toc-modified-id=\"Add-observed-date-15\"><span class=\"toc-item-num\">15&nbsp;&nbsp;</span>Add observed date</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-16\"><span class=\"toc-item-num\">16&nbsp;&nbsp;</span>Merge train &amp; test with new &amp; old transactions history</a></span></li><li><span><a href=\"#Adding-train-days\" data-toc-modified-id=\"Adding-train-days-17\"><span class=\"toc-item-num\">17&nbsp;&nbsp;</span>Adding train days</a></span></li><li><span><a href=\"#Add-extra-columns-like-age,-total-transactions,-installments,-purchase-amount,-first-buy-etc\" data-toc-modified-id=\"Add-extra-columns-like-age,-total-transactions,-installments,-purchase-amount,-first-buy-etc-18\"><span class=\"toc-item-num\">18&nbsp;&nbsp;</span>Add extra columns like age, total transactions, installments, purchase amount, first buy etc</a></span></li><li><span><a href=\"#Mark-the-outliers\" data-toc-modified-id=\"Mark-the-outliers-19\"><span class=\"toc-item-num\">19&nbsp;&nbsp;</span>Mark the outliers</a></span></li><li><span><a href=\"#Target-encode-the-outliers\" data-toc-modified-id=\"Target-encode-the-outliers-20\"><span class=\"toc-item-num\">20&nbsp;&nbsp;</span>Target encode the outliers</a></span></li><li><span><a href=\"#Transform-purchase_value-to-actual-amount\" data-toc-modified-id=\"Transform-purchase_value-to-actual-amount-21\"><span class=\"toc-item-num\">21&nbsp;&nbsp;</span>Transform purchase_value to actual amount</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\n",
    "import gc"
   ]
  },
  {
   "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",
    "    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",
    "    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",
    "#     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": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 42), (1963031, 52))"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rolling aggregate on month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans['purchase_amount'] = np.round(new_hist_trans['purchase_amount'] / 0.00150265118 + 497.06,2)\n",
    "hist_trans['purchase_amount'] = np.round(hist_trans['purchase_amount'] / 0.00150265118 + 497.06,2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "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": 17,
   "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": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [time_diff(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": 3,
   "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": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [additional_feats(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 52), (1963031, 52))"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Latest & First 5 col aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def head_sum(x):\n",
    "    return x.head().sum()\n",
    "\n",
    "def head_max(x):\n",
    "    return x.head().max()\n",
    "\n",
    "def tail_sum(x):\n",
    "    return x.tail().sum()\n",
    "\n",
    "def tail_max(x):\n",
    "    return x.tail().max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "334"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.9 s, sys: 1.55 s, total: 3.45 s\n",
      "Wall time: 1.14 s\n"
     ]
    }
   ],
   "source": [
    "%time new_hist_trans = new_hist_trans.sort_values('purchase_date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 24 s, sys: 1.53 s, total: 25.6 s\n",
      "Wall time: 23.2 s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans = hist_trans.sort_values('purchase_date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans = new_hist_trans.reset_index().drop('index', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans.drop('index', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans.drop('level_0', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans = feather.read_dataframe('hist_trans_beta')\n",
    "new_hist_trans = feather.read_dataframe('new_hist_trans_beta')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate by card_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2017-01-01 00:00:08\n",
       "1   2017-01-01 00:00:59\n",
       "2   2017-01-01 00:01:41\n",
       "3   2017-01-01 00:02:03\n",
       "4   2017-01-01 00:02:12\n",
       "Name: purchase_date, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans['purchase_date'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2017-03-01 03:24:51\n",
       "1   2017-03-01 11:01:06\n",
       "2   2017-03-01 11:27:39\n",
       "3   2017-03-01 11:55:11\n",
       "4   2017-03-01 12:37:26\n",
       "Name: purchase_date, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans['purchase_date'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 52), (1963031, 52))"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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', 'sum'), ('pct_75', lambda x: np.percentile(x, q = 75)), \n",
    "                               ('pct_25', lambda x: np.percentile(x, q = 25)), ('mean', 'mean'), \n",
    "                               ('median', 'median'), ('max', 'max'), ('min', 'min'), ('var', 'var'), \n",
    "                               ('skew', 'skew'), ('head_sum', head_sum), ('head_max', head_max), \n",
    "                               ('tail_sum', tail_sum), ('tail_max', tail_max)]\n",
    "    aggs['installments'] = [('sum', 'sum'), ('pct_75', lambda x: np.percentile(x, q = 75)), \n",
    "                               ('pct_25', lambda x: np.percentile(x, q = 25)), ('mean', 'mean'), \n",
    "                               ('median', 'median'), ('max', 'max'), ('min', 'min'), ('var', 'var'), \n",
    "                               ('skew', 'skew'), ('head_sum', head_sum), ('head_max', head_max), \n",
    "                               ('tail_sum', tail_sum), ('tail_max', tail_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'] = ['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', 'skew']\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",
    "    #exta\n",
    "    aggs['purchase_date_successive_diff'] = ['mean']\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": 8,
   "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', 'sum'), ('pct_75', lambda x: np.percentile(x, q = 75)), \n",
    "                               ('pct_25', lambda x: np.percentile(x, q = 25)), ('mean', 'mean'), \n",
    "                               ('median', 'median'), ('max', 'max'), ('min', 'min'), ('var', 'var'), \n",
    "                               ('skew', 'skew'), ('head_sum', head_sum), ('head_max', head_max), \n",
    "                               ('tail_sum', tail_sum), ('tail_max', tail_max)]\n",
    "    aggs['installments'] = [('sum', 'sum'), ('pct_75', lambda x: np.percentile(x, q = 75)), \n",
    "                               ('pct_25', lambda x: np.percentile(x, q = 25)), ('mean', 'mean'), \n",
    "                               ('median', 'median'), ('max', 'max'), ('min', 'min'), ('var', 'var'), \n",
    "                               ('skew', 'skew'), ('head_sum', head_sum), ('head_max', head_max), \n",
    "                               ('tail_sum', tail_sum), ('tail_max', tail_max)]\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",
    "    #extra\n",
    "    aggs['purchase_date_successive_diff'] = ['mean']\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": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gc\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/numpy/lib/function_base.py:3652: RuntimeWarning: Invalid value encountered in percentile\n",
      "  interpolation=interpolation)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 12min 27s, sys: 10.4 s, total: 12min 37s\n",
      "Wall time: 11min 36s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans_agg = aggregate_hist_trans(hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7min 52s, sys: 16.4 s, total: 8min 8s\n",
      "Wall time: 7min 44s\n"
     ]
    }
   ],
   "source": [
    "%time new_hist_trans_agg = aggregate_new_trans(new_hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "142"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add exta interpreted columns on aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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": 14,
   "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": 15,
   "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": 16,
   "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": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 103), (290001, 97))"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg.shape, new_hist_trans_agg.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate on categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "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": 19,
   "metadata": {},
   "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": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 30.4 s, sys: 9.67 s, total: 40.1 s\n",
      "Wall time: 27.9 s\n"
     ]
    }
   ],
   "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": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 24), (290001, 22))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg_cat.shape, new_hist_trans_agg_cat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>card_id</th>\n",
       "      <th>C_ID_00007093c1</th>\n",
       "      <th>C_ID_0001238066</th>\n",
       "      <th>C_ID_0001506ef0</th>\n",
       "      <th>C_ID_0001793786</th>\n",
       "      <th>C_ID_000183fdda</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>category_1_0_purchase_amount_mean</th>\n",
       "      <td>-0.664262</td>\n",
       "      <td>-0.564558</td>\n",
       "      <td>-0.723677</td>\n",
       "      <td>-0.007407</td>\n",
       "      <td>-0.599162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_1_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.650332</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_0_purchase_amount_sum</th>\n",
       "      <td>-1.328524</td>\n",
       "      <td>-13.549391</td>\n",
       "      <td>-1.447354</td>\n",
       "      <td>-0.229620</td>\n",
       "      <td>-6.590778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_1_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.300665</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_1.0_purchase_amount_mean</th>\n",
       "      <td>-0.656749</td>\n",
       "      <td>-0.580966</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.139747</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_2.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.344766</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_3.0_purchase_amount_mean</th>\n",
       "      <td>-0.671775</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.723677</td>\n",
       "      <td>0.102887</td>\n",
       "      <td>-0.599162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_4.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_5.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.495945</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.361628</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_1.0_purchase_amount_sum</th>\n",
       "      <td>-0.656749</td>\n",
       "      <td>-13.362220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.375707</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_2.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.758131</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_3.0_purchase_amount_sum</th>\n",
       "      <td>-0.671775</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.447354</td>\n",
       "      <td>0.514433</td>\n",
       "      <td>-6.590778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_4.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_5.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.487835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.361628</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.152008</td>\n",
       "      <td>-0.723677</td>\n",
       "      <td>-0.007407</td>\n",
       "      <td>-0.107680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_1_purchase_amount_mean</th>\n",
       "      <td>-0.664262</td>\n",
       "      <td>-0.625781</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.696173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_2_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.389160</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.576515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.152008</td>\n",
       "      <td>-1.447354</td>\n",
       "      <td>-0.229620</td>\n",
       "      <td>-0.107680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_1_purchase_amount_sum</th>\n",
       "      <td>-1.328524</td>\n",
       "      <td>-13.141406</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.177040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_2_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.556641</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.306059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_1_purchase_amount_mean</th>\n",
       "      <td>-0.664262</td>\n",
       "      <td>-0.571156</td>\n",
       "      <td>-0.723677</td>\n",
       "      <td>-0.007407</td>\n",
       "      <td>-0.599162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_1_purchase_amount_sum</th>\n",
       "      <td>-1.328524</td>\n",
       "      <td>-14.850055</td>\n",
       "      <td>-1.447354</td>\n",
       "      <td>-0.229620</td>\n",
       "      <td>-6.590778</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "card_id                                 C_ID_00007093c1  C_ID_0001238066  \\\n",
       "category_1_0_purchase_amount_mean             -0.664262        -0.564558   \n",
       "category_1_1_purchase_amount_mean                   NaN        -0.650332   \n",
       "category_1_0_purchase_amount_sum              -1.328524       -13.549391   \n",
       "category_1_1_purchase_amount_sum                    NaN        -1.300665   \n",
       "category_2_1.0_purchase_amount_mean           -0.656749        -0.580966   \n",
       "category_2_2.0_purchase_amount_mean                 NaN              NaN   \n",
       "category_2_3.0_purchase_amount_mean           -0.671775              NaN   \n",
       "category_2_4.0_purchase_amount_mean                 NaN              NaN   \n",
       "category_2_5.0_purchase_amount_mean                 NaN        -0.495945   \n",
       "category_2_1.0_purchase_amount_sum            -0.656749       -13.362220   \n",
       "category_2_2.0_purchase_amount_sum                  NaN              NaN   \n",
       "category_2_3.0_purchase_amount_sum            -0.671775              NaN   \n",
       "category_2_4.0_purchase_amount_sum                  NaN              NaN   \n",
       "category_2_5.0_purchase_amount_sum                  NaN        -1.487835   \n",
       "category_3_0_purchase_amount_mean                   NaN        -0.152008   \n",
       "category_3_1_purchase_amount_mean             -0.664262        -0.625781   \n",
       "category_3_2_purchase_amount_mean                   NaN        -0.389160   \n",
       "category_3_0_purchase_amount_sum                    NaN        -0.152008   \n",
       "category_3_1_purchase_amount_sum              -1.328524       -13.141406   \n",
       "category_3_2_purchase_amount_sum                    NaN        -1.556641   \n",
       "authorized_flag_1_purchase_amount_mean        -0.664262        -0.571156   \n",
       "authorized_flag_1_purchase_amount_sum         -1.328524       -14.850055   \n",
       "\n",
       "card_id                                 C_ID_0001506ef0  C_ID_0001793786  \\\n",
       "category_1_0_purchase_amount_mean             -0.723677        -0.007407   \n",
       "category_1_1_purchase_amount_mean                   NaN              NaN   \n",
       "category_1_0_purchase_amount_sum              -1.447354        -0.229620   \n",
       "category_1_1_purchase_amount_sum                    NaN              NaN   \n",
       "category_2_1.0_purchase_amount_mean                 NaN         0.139747   \n",
       "category_2_2.0_purchase_amount_mean                 NaN        -0.344766   \n",
       "category_2_3.0_purchase_amount_mean           -0.723677         0.102887   \n",
       "category_2_4.0_purchase_amount_mean                 NaN              NaN   \n",
       "category_2_5.0_purchase_amount_mean                 NaN        -0.361628   \n",
       "category_2_1.0_purchase_amount_sum                  NaN         2.375707   \n",
       "category_2_2.0_purchase_amount_sum                  NaN        -2.758131   \n",
       "category_2_3.0_purchase_amount_sum            -1.447354         0.514433   \n",
       "category_2_4.0_purchase_amount_sum                  NaN              NaN   \n",
       "category_2_5.0_purchase_amount_sum                  NaN        -0.361628   \n",
       "category_3_0_purchase_amount_mean             -0.723677        -0.007407   \n",
       "category_3_1_purchase_amount_mean                   NaN              NaN   \n",
       "category_3_2_purchase_amount_mean                   NaN              NaN   \n",
       "category_3_0_purchase_amount_sum              -1.447354        -0.229620   \n",
       "category_3_1_purchase_amount_sum                    NaN              NaN   \n",
       "category_3_2_purchase_amount_sum                    NaN              NaN   \n",
       "authorized_flag_1_purchase_amount_mean        -0.723677        -0.007407   \n",
       "authorized_flag_1_purchase_amount_sum         -1.447354        -0.229620   \n",
       "\n",
       "card_id                                 C_ID_000183fdda  \n",
       "category_1_0_purchase_amount_mean             -0.599162  \n",
       "category_1_1_purchase_amount_mean                   NaN  \n",
       "category_1_0_purchase_amount_sum              -6.590778  \n",
       "category_1_1_purchase_amount_sum                    NaN  \n",
       "category_2_1.0_purchase_amount_mean                 NaN  \n",
       "category_2_2.0_purchase_amount_mean                 NaN  \n",
       "category_2_3.0_purchase_amount_mean           -0.599162  \n",
       "category_2_4.0_purchase_amount_mean                 NaN  \n",
       "category_2_5.0_purchase_amount_mean                 NaN  \n",
       "category_2_1.0_purchase_amount_sum                  NaN  \n",
       "category_2_2.0_purchase_amount_sum                  NaN  \n",
       "category_2_3.0_purchase_amount_sum            -6.590778  \n",
       "category_2_4.0_purchase_amount_sum                  NaN  \n",
       "category_2_5.0_purchase_amount_sum                  NaN  \n",
       "category_3_0_purchase_amount_mean             -0.107680  \n",
       "category_3_1_purchase_amount_mean             -0.696173  \n",
       "category_3_2_purchase_amount_mean             -0.576515  \n",
       "category_3_0_purchase_amount_sum              -0.107680  \n",
       "category_3_1_purchase_amount_sum              -4.177040  \n",
       "category_3_2_purchase_amount_sum              -2.306059  \n",
       "authorized_flag_1_purchase_amount_mean        -0.599162  \n",
       "authorized_flag_1_purchase_amount_sum         -6.590778  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_agg_cat.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg_cat.reset_index().to_feather('hist_trans_agg_cat')\n",
    "new_hist_trans_agg_cat.reset_index().to_feather('new_hist_trans_agg_cat')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg_cat = feather.read_dataframe('hist_trans_agg_cat')\n",
    "new_hist_trans_agg_cat = feather.read_dataframe('new_hist_trans_agg_cat')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate on month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "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 = ['_month_diff_'.join(col).strip() for col in intermediate_group.columns.values]\n",
    "    intermediate_group.reset_index(inplace=True)\n",
    "    \n",
    "    return intermediate_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# aggregate_per_month(new_hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "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 36s, sys: 12.5 s, total: 3min 49s\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": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((618851, 6), (431268, 6))"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg_month.shape, new_hist_trans_agg_month.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>card_id</th>\n",
       "      <td>C_ID_00007093c1</td>\n",
       "      <td>C_ID_0001238066</td>\n",
       "      <td>C_ID_0001238066</td>\n",
       "      <td>C_ID_0001506ef0</td>\n",
       "      <td>C_ID_0001793786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff</th>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_month_diff_count</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_month_diff_sum</th>\n",
       "      <td>110</td>\n",
       "      <td>857.42</td>\n",
       "      <td>2183.57</td>\n",
       "      <td>30.92</td>\n",
       "      <td>12340.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_month_diff_max</th>\n",
       "      <td>60</td>\n",
       "      <td>250</td>\n",
       "      <td>444.94</td>\n",
       "      <td>21</td>\n",
       "      <td>2580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_month_diff_mean</th>\n",
       "      <td>55</td>\n",
       "      <td>171.484</td>\n",
       "      <td>103.98</td>\n",
       "      <td>15.46</td>\n",
       "      <td>685.599</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                0                1  \\\n",
       "card_id                           C_ID_00007093c1  C_ID_0001238066   \n",
       "month_diff                                     12               11   \n",
       "purchase_amount_month_diff_count                2                5   \n",
       "purchase_amount_month_diff_sum                110           857.42   \n",
       "purchase_amount_month_diff_max                 60              250   \n",
       "purchase_amount_month_diff_mean                55          171.484   \n",
       "\n",
       "                                                2                3  \\\n",
       "card_id                           C_ID_0001238066  C_ID_0001506ef0   \n",
       "month_diff                                     12               12   \n",
       "purchase_amount_month_diff_count               21                2   \n",
       "purchase_amount_month_diff_sum            2183.57            30.92   \n",
       "purchase_amount_month_diff_max             444.94               21   \n",
       "purchase_amount_month_diff_mean            103.98            15.46   \n",
       "\n",
       "                                                4  \n",
       "card_id                           C_ID_0001793786  \n",
       "month_diff                                     15  \n",
       "purchase_amount_month_diff_count               18  \n",
       "purchase_amount_month_diff_sum            12340.8  \n",
       "purchase_amount_month_diff_max               2580  \n",
       "purchase_amount_month_diff_mean           685.599  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_agg_month.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for df in [hist_trans_agg_month, new_hist_trans_agg_month]:\n",
    "#     df.drop(['month_diff_mean', 'month_diff_sum', 'month_diff_ptp', 'month_diff_max'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg_month.to_feather('hist_trans_agg_month')\n",
    "new_hist_trans_agg_month.to_feather('new_hist_trans_agg_month')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans_agg_month = feather.read_dataframe('new_hist_trans_agg_month')\n",
    "hist_trans_agg_month = feather.read_dataframe('hist_trans_agg_month')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load test & train DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "train, test = [pd.read_csv(f'{PATH}{c}') for c in ['train.csv', 'test.csv']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add observed date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "last_hist_transaction = hist_trans.groupby('card_id').agg({'month_lag' : 'max', 'purchase_date' : 'max'}).reset_index()\n",
    "last_hist_transaction.columns = ['card_id', 'hist_month_lag', 'hist_purchase_date']\n",
    "first_new_transaction = new_hist_trans.groupby('card_id').agg({'month_lag' : 'min', 'purchase_date' : 'min'}).reset_index()\n",
    "first_new_transaction.columns = ['card_id', 'new_month_lag', 'new_purchase_date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "last_hist_transaction['hist_purchase_date'] = pd.to_datetime(last_hist_transaction['hist_purchase_date']) \n",
    "first_new_transaction['new_purchase_date'] = pd.to_datetime(first_new_transaction['new_purchase_date']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "last_hist_transaction['observation_date'] = \\\n",
    "    last_hist_transaction.apply(lambda x: x['hist_purchase_date']  - pd.DateOffset(months=x['hist_month_lag']), axis=1)\n",
    "\n",
    "first_new_transaction['observation_date'] = \\\n",
    "    first_new_transaction.apply(lambda x: x['new_purchase_date']  - pd.DateOffset(months=x['new_month_lag']-1), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# last_hist_transaction.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first_new_transaction.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "last_hist_transaction['observation_date'] = last_hist_transaction['observation_date'].dt.to_period('M').dt.to_timestamp() + pd.DateOffset(months=1)\n",
    "first_new_transaction['observation_date'] = first_new_transaction['observation_date'].dt.to_period('M').dt.to_timestamp()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# last_hist_transaction.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first_new_transaction.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_new_transaction.drop(['new_month_lag', 'new_purchase_date'], axis=1, inplace=True)\n",
    "last_hist_transaction.drop(['hist_month_lag', 'hist_purchase_date'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "validate = last_hist_transaction.merge(first_new_transaction, on = 'card_id')\n",
    "all(validate['observation_date_x'] == validate['observation_date_y'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 25), (618851, 6), (325540, 103))"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg_cat.shape, hist_trans_agg_month.shape, hist_trans_agg.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((290001, 97), (431268, 6), (290001, 23))"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_agg.shape, new_hist_trans_agg_month.shape, new_hist_trans_agg_cat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>card_id</th>\n",
       "      <td>C_ID_00007093c1</td>\n",
       "      <td>C_ID_0001238066</td>\n",
       "      <td>C_ID_0001506ef0</td>\n",
       "      <td>C_ID_0001793786</td>\n",
       "      <td>C_ID_000183fdda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_0_purchase_amount_mean</th>\n",
       "      <td>55</td>\n",
       "      <td>121.352</td>\n",
       "      <td>15.46</td>\n",
       "      <td>492.131</td>\n",
       "      <td>98.3236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_1_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>64.27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_0_purchase_amount_sum</th>\n",
       "      <td>110</td>\n",
       "      <td>2912.45</td>\n",
       "      <td>30.92</td>\n",
       "      <td>15256.1</td>\n",
       "      <td>1081.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_1_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>128.54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_1.0_purchase_amount_mean</th>\n",
       "      <td>60</td>\n",
       "      <td>110.433</td>\n",
       "      <td>NaN</td>\n",
       "      <td>590.061</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_2.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>267.621</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_3.0_purchase_amount_mean</th>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.46</td>\n",
       "      <td>565.53</td>\n",
       "      <td>98.3236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_4.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_5.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>167.013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>256.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_1.0_purchase_amount_sum</th>\n",
       "      <td>60</td>\n",
       "      <td>2539.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10031</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_2.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2140.97</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_3.0_purchase_amount_sum</th>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.92</td>\n",
       "      <td>2827.65</td>\n",
       "      <td>1081.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_4.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_5.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>501.04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>256.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>395.9</td>\n",
       "      <td>15.46</td>\n",
       "      <td>492.131</td>\n",
       "      <td>425.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_1_purchase_amount_mean</th>\n",
       "      <td>55</td>\n",
       "      <td>80.6086</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.7633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_2_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>238.077</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>113.395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>395.9</td>\n",
       "      <td>30.92</td>\n",
       "      <td>15256.1</td>\n",
       "      <td>425.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_1_purchase_amount_sum</th>\n",
       "      <td>110</td>\n",
       "      <td>1692.78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>202.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_2_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>952.31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>453.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_1_purchase_amount_mean</th>\n",
       "      <td>55</td>\n",
       "      <td>116.961</td>\n",
       "      <td>15.46</td>\n",
       "      <td>492.131</td>\n",
       "      <td>98.3236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_1_purchase_amount_sum</th>\n",
       "      <td>110</td>\n",
       "      <td>3040.99</td>\n",
       "      <td>30.92</td>\n",
       "      <td>15256.1</td>\n",
       "      <td>1081.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      0                1  \\\n",
       "card_id                                 C_ID_00007093c1  C_ID_0001238066   \n",
       "category_1_0_purchase_amount_mean                    55          121.352   \n",
       "category_1_1_purchase_amount_mean                   NaN            64.27   \n",
       "category_1_0_purchase_amount_sum                    110          2912.45   \n",
       "category_1_1_purchase_amount_sum                    NaN           128.54   \n",
       "category_2_1.0_purchase_amount_mean                  60          110.433   \n",
       "category_2_2.0_purchase_amount_mean                 NaN              NaN   \n",
       "category_2_3.0_purchase_amount_mean                  50              NaN   \n",
       "category_2_4.0_purchase_amount_mean                 NaN              NaN   \n",
       "category_2_5.0_purchase_amount_mean                 NaN          167.013   \n",
       "category_2_1.0_purchase_amount_sum                   60          2539.95   \n",
       "category_2_2.0_purchase_amount_sum                  NaN              NaN   \n",
       "category_2_3.0_purchase_amount_sum                   50              NaN   \n",
       "category_2_4.0_purchase_amount_sum                  NaN              NaN   \n",
       "category_2_5.0_purchase_amount_sum                  NaN           501.04   \n",
       "category_3_0_purchase_amount_mean                   NaN            395.9   \n",
       "category_3_1_purchase_amount_mean                    55          80.6086   \n",
       "category_3_2_purchase_amount_mean                   NaN          238.077   \n",
       "category_3_0_purchase_amount_sum                    NaN            395.9   \n",
       "category_3_1_purchase_amount_sum                    110          1692.78   \n",
       "category_3_2_purchase_amount_sum                    NaN           952.31   \n",
       "authorized_flag_1_purchase_amount_mean               55          116.961   \n",
       "authorized_flag_1_purchase_amount_sum               110          3040.99   \n",
       "\n",
       "                                                      2                3  \\\n",
       "card_id                                 C_ID_0001506ef0  C_ID_0001793786   \n",
       "category_1_0_purchase_amount_mean                 15.46          492.131   \n",
       "category_1_1_purchase_amount_mean                   NaN              NaN   \n",
       "category_1_0_purchase_amount_sum                  30.92          15256.1   \n",
       "category_1_1_purchase_amount_sum                    NaN              NaN   \n",
       "category_2_1.0_purchase_amount_mean                 NaN          590.061   \n",
       "category_2_2.0_purchase_amount_mean                 NaN          267.621   \n",
       "category_2_3.0_purchase_amount_mean               15.46           565.53   \n",
       "category_2_4.0_purchase_amount_mean                 NaN              NaN   \n",
       "category_2_5.0_purchase_amount_mean                 NaN            256.4   \n",
       "category_2_1.0_purchase_amount_sum                  NaN            10031   \n",
       "category_2_2.0_purchase_amount_sum                  NaN          2140.97   \n",
       "category_2_3.0_purchase_amount_sum                30.92          2827.65   \n",
       "category_2_4.0_purchase_amount_sum                  NaN              NaN   \n",
       "category_2_5.0_purchase_amount_sum                  NaN            256.4   \n",
       "category_3_0_purchase_amount_mean                 15.46          492.131   \n",
       "category_3_1_purchase_amount_mean                   NaN              NaN   \n",
       "category_3_2_purchase_amount_mean                   NaN              NaN   \n",
       "category_3_0_purchase_amount_sum                  30.92          15256.1   \n",
       "category_3_1_purchase_amount_sum                    NaN              NaN   \n",
       "category_3_2_purchase_amount_sum                    NaN              NaN   \n",
       "authorized_flag_1_purchase_amount_mean            15.46          492.131   \n",
       "authorized_flag_1_purchase_amount_sum             30.92          15256.1   \n",
       "\n",
       "                                                      4  \n",
       "card_id                                 C_ID_000183fdda  \n",
       "category_1_0_purchase_amount_mean               98.3236  \n",
       "category_1_1_purchase_amount_mean                   NaN  \n",
       "category_1_0_purchase_amount_sum                1081.56  \n",
       "category_1_1_purchase_amount_sum                    NaN  \n",
       "category_2_1.0_purchase_amount_mean                 NaN  \n",
       "category_2_2.0_purchase_amount_mean                 NaN  \n",
       "category_2_3.0_purchase_amount_mean             98.3236  \n",
       "category_2_4.0_purchase_amount_mean                 NaN  \n",
       "category_2_5.0_purchase_amount_mean                 NaN  \n",
       "category_2_1.0_purchase_amount_sum                  NaN  \n",
       "category_2_2.0_purchase_amount_sum                  NaN  \n",
       "category_2_3.0_purchase_amount_sum              1081.56  \n",
       "category_2_4.0_purchase_amount_sum                  NaN  \n",
       "category_2_5.0_purchase_amount_sum                  NaN  \n",
       "category_3_0_purchase_amount_mean                 425.4  \n",
       "category_3_1_purchase_amount_mean               33.7633  \n",
       "category_3_2_purchase_amount_mean               113.395  \n",
       "category_3_0_purchase_amount_sum                  425.4  \n",
       "category_3_1_purchase_amount_sum                 202.58  \n",
       "category_3_2_purchase_amount_sum                 453.58  \n",
       "authorized_flag_1_purchase_amount_mean          98.3236  \n",
       "authorized_flag_1_purchase_amount_sum           1081.56  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_agg_cat.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_00007093c1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_0_purchase_amount_mean</th>\n",
       "      <td>-0.469471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_1_purchase_amount_mean</th>\n",
       "      <td>-0.71568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_0_purchase_amount_sum</th>\n",
       "      <td>-56.806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_1_purchase_amount_sum</th>\n",
       "      <td>-20.039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_1.0_purchase_amount_mean</th>\n",
       "      <td>-0.71568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_2.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_3.0_purchase_amount_mean</th>\n",
       "      <td>-0.470114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_4.0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_5.0_purchase_amount_mean</th>\n",
       "      <td>-0.392282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_1.0_purchase_amount_sum</th>\n",
       "      <td>-20.039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_2.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_3.0_purchase_amount_sum</th>\n",
       "      <td>-56.4137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_4.0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_5.0_purchase_amount_sum</th>\n",
       "      <td>-0.392282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_0_purchase_amount_mean</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_1_purchase_amount_mean</th>\n",
       "      <td>-0.57415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_2_purchase_amount_mean</th>\n",
       "      <td>-0.211513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_0_purchase_amount_sum</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_1_purchase_amount_sum</th>\n",
       "      <td>-71.7687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_2_purchase_amount_sum</th>\n",
       "      <td>-5.07632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_0_purchase_amount_mean</th>\n",
       "      <td>-0.411485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_1_purchase_amount_mean</th>\n",
       "      <td>-0.547746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_0_purchase_amount_sum</th>\n",
       "      <td>-14.402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_1_purchase_amount_sum</th>\n",
       "      <td>-62.4431</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      0\n",
       "card_id                                 C_ID_00007093c1\n",
       "category_1_0_purchase_amount_mean             -0.469471\n",
       "category_1_1_purchase_amount_mean              -0.71568\n",
       "category_1_0_purchase_amount_sum                -56.806\n",
       "category_1_1_purchase_amount_sum                -20.039\n",
       "category_2_1.0_purchase_amount_mean            -0.71568\n",
       "category_2_2.0_purchase_amount_mean                 NaN\n",
       "category_2_3.0_purchase_amount_mean           -0.470114\n",
       "category_2_4.0_purchase_amount_mean                 NaN\n",
       "category_2_5.0_purchase_amount_mean           -0.392282\n",
       "category_2_1.0_purchase_amount_sum              -20.039\n",
       "category_2_2.0_purchase_amount_sum                  NaN\n",
       "category_2_3.0_purchase_amount_sum             -56.4137\n",
       "category_2_4.0_purchase_amount_sum                  NaN\n",
       "category_2_5.0_purchase_amount_sum            -0.392282\n",
       "category_3_0_purchase_amount_mean                   NaN\n",
       "category_3_1_purchase_amount_mean              -0.57415\n",
       "category_3_2_purchase_amount_mean             -0.211513\n",
       "category_3_0_purchase_amount_sum                    NaN\n",
       "category_3_1_purchase_amount_sum               -71.7687\n",
       "category_3_2_purchase_amount_sum               -5.07632\n",
       "authorized_flag_0_purchase_amount_mean        -0.411485\n",
       "authorized_flag_1_purchase_amount_mean        -0.547746\n",
       "authorized_flag_0_purchase_amount_sum           -14.402\n",
       "authorized_flag_1_purchase_amount_sum          -62.4431"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg_cat.head(1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 103), (325540, 25), (325540, 2), (618851, 6))"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg.shape, hist_trans_agg_cat.shape, last_hist_transaction.shape, hist_trans_agg_month.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge train & test with new & old transactions history"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 6), (123623, 5))"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape, test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train, new_hist_trans_agg, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "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')\n",
    "train_df = join_dfs(train_df, hist_trans_agg_cat, left_on='card_id', suffix='_old')\n",
    "train_df = join_dfs(train_df, last_hist_transaction, left_on='card_id', suffix='_old')\n",
    "# train_df = join_dfs(train_df, new_hist_trans_agg_month, left_on='card_id')\n",
    "train_df = join_dfs(train_df, new_hist_trans_agg_cat, left_on='card_id')\n",
    "train_df = join_dfs(train_df, first_new_transaction, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df = join_dfs(test, new_hist_trans_agg, left_on='card_id')\n",
    "test_df = join_dfs(test_df, first_new_transaction, left_on='card_id')\n",
    "# test_df = join_dfs(test_df, new_hist_trans_agg_month, left_on='card_id')\n",
    "test_df = join_dfs(test_df, new_hist_trans_agg_cat, left_on='card_id')\n",
    "# test_df = join_dfs(test_df, hist_trans_agg_month, left_on='card_id', suffix='_old')\n",
    "test_df = join_dfs(test_df, hist_trans_agg_cat, left_on='card_id', suffix='_old')\n",
    "test_df = join_dfs(test_df, hist_trans_agg, left_on='card_id', suffix='_old')\n",
    "test_df = join_dfs(test_df, last_hist_transaction, left_on='card_id', suffix='_old')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((123623, 251), (201917, 252))"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df.shape, train_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding train days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "observation_date    0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df[['observation_date']].isnull().sum().sort_values(ascending=False)*100/len(train_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_df[['observation_date_old', 'observation_date']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hist_trans_agg_cat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in [train_df, test_df]:\n",
    "    df.drop('observation_date', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "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",
    "    df['observation_date_old'] = pd.to_datetime(df['observation_date_old'])\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",
    "    df['observed_elapsed_time'] = (df['observation_date_old'] - df['first_active_month']).dt.days\n",
    "    df['days_feature1'] = df['observed_elapsed_time'] * df['feature_1']\n",
    "    df['days_feature2'] = df['observed_elapsed_time'] * df['feature_2']\n",
    "    df['days_feature3'] = df['observed_elapsed_time'] * df['feature_3']\n",
    "\n",
    "    df['days_feature1_ratio'] = df['feature_1'] / df['observed_elapsed_time']\n",
    "    df['days_feature2_ratio'] = df['feature_2'] / df['observed_elapsed_time']\n",
    "    df['days_feature3_ratio'] = df['feature_3'] / df['observed_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": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df, test_df = [add_days_feature_interaction(d) for d in [train_df, test_df]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add extra columns like age, total transactions, installments, purchase amount, first buy etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 264), (123623, 263))"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "310"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_df[['elapsed_time','observed_elapsed_time']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_df = train_df.loc[:,~train_df.columns.duplicated()]\n",
    "# test_df = test_df.loc[:,~test_df.columns.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "scrolled": false
   },
   "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_total'] = 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_total'] = df['month_lag_mean']+df['month_lag_mean_old']\n",
    "    df['month_lag_max_total'] = df['month_lag_max']+df['month_lag_max_old']\n",
    "    df['month_lag_min_total'] = df['month_lag_min']+df['month_lag_min_old']\n",
    "    df['category_1_mean_total'] = df['category_1_mean']+df['category_1_mean_old']\n",
    "    df['installments_total'] = df['installments_sum']+df['installments_sum_old']\n",
    "    df['installments_mean_total'] = df['installments_mean']+df['installments_mean_old']\n",
    "    df['installments_max_total'] = 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_total'] = df['duration_mean']+df['duration_mean_old']\n",
    "    df['duration_min_total'] = df['duration_min']+df['duration_min_old']\n",
    "    df['duration_max_total'] = df['duration_max']+df['duration_max_old']\n",
    "    df['amount_month_ratio_mean_total']=df['amount_month_ratio_mean']+df['amount_month_ratio_mean_old']\n",
    "    df['amount_month_ratio_min_total']=df['amount_month_ratio_min']+df['amount_month_ratio_min_old']\n",
    "    df['amount_month_ratio_max_total']=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']\n",
    "    df['category_1_0_purchase_amount_mean_total'] = df['category_1_0_purchase_amount_mean'] + df['category_1_0_purchase_amount_mean_old']\n",
    "    df['category_1_1_purchase_amount_mean_total'] = df['category_1_1_purchase_amount_mean'] + df['category_1_1_purchase_amount_mean_old']\n",
    "    df['category_1_0_purchase_amount_sum_total'] = df['category_1_0_purchase_amount_sum'] + df['category_1_0_purchase_amount_sum_old']\n",
    "    df['category_1_1_purchase_amount_sum_total'] = df['category_1_1_purchase_amount_sum'] + df['category_1_1_purchase_amount_sum_old']\n",
    "    df['category_2_1.0_purchase_amount_mean_total'] = df['category_2_1.0_purchase_amount_mean'] + df['category_2_1.0_purchase_amount_mean_old']\n",
    "    df['category_2_2.0_purchase_amount_mean_total'] = df['category_2_2.0_purchase_amount_mean'] + df['category_2_2.0_purchase_amount_mean_old']\n",
    "    df['category_2_3.0_purchase_amount_mean_total'] = df['category_2_3.0_purchase_amount_mean'] + df['category_2_3.0_purchase_amount_mean_old']\n",
    "    df['category_2_4.0_purchase_amount_mean_total'] = df['category_2_4.0_purchase_amount_mean'] + df['category_2_4.0_purchase_amount_mean_old']\n",
    "    df['category_2_5.0_purchase_amount_mean_total'] = df['category_2_5.0_purchase_amount_mean'] + df['category_2_5.0_purchase_amount_mean_old']\n",
    "    df['category_2_1.0_purchase_amount_sum_total'] = df['category_2_1.0_purchase_amount_sum'] + df['category_2_1.0_purchase_amount_sum_old']\n",
    "    df['category_2_2.0_purchase_amount_sum_total'] = df['category_2_2.0_purchase_amount_sum'] + df['category_2_2.0_purchase_amount_sum_old']\n",
    "    df['category_2_3.0_purchase_amount_sum_total'] = df['category_2_3.0_purchase_amount_sum'] + df['category_2_3.0_purchase_amount_sum_old']\n",
    "    df['category_2_4.0_purchase_amount_sum_total'] = df['category_2_4.0_purchase_amount_sum'] + df['category_2_4.0_purchase_amount_sum_old']\n",
    "    df['category_2_5.0_purchase_amount_sum_total'] = df['category_2_5.0_purchase_amount_sum'] + df['category_2_5.0_purchase_amount_sum_old']\n",
    "    df['category_3_0_purchase_amount_mean_total'] = df['category_3_0_purchase_amount_mean'] + df['category_3_0_purchase_amount_mean_old']\n",
    "    df['category_3_1_purchase_amount_mean_total'] = df['category_3_1_purchase_amount_mean'] + df['category_3_1_purchase_amount_mean_old']\n",
    "    df['category_3_2_purchase_amount_mean_total'] = df['category_3_2_purchase_amount_mean'] + df['category_3_2_purchase_amount_mean_old']\n",
    "    df['category_3_0_purchase_amount_sum_total'] = df['category_3_0_purchase_amount_sum'] + df['category_3_0_purchase_amount_sum_old']\n",
    "    df['category_3_1_purchase_amount_sum_total'] = df['category_3_1_purchase_amount_sum'] + df['category_3_1_purchase_amount_sum_old']\n",
    "    df['category_3_2_purchase_amount_sum_total'] = df['category_3_2_purchase_amount_sum'] + df['category_3_2_purchase_amount_sum_old']\n",
    "    df['category_1_0_purchase_amount_mean_ratio']  = df['category_1_0_purchase_amount_mean'] / df['category_1_0_purchase_amount_mean_old']\n",
    "    df['category_1_1_purchase_amount_mean_ratio']  = df['category_1_1_purchase_amount_mean'] / df['category_1_1_purchase_amount_mean_old']\n",
    "    df['category_1_0_purchase_amount_sum_ratio']  = df['category_1_0_purchase_amount_sum'] / df['category_1_0_purchase_amount_sum_old']\n",
    "    df['category_1_1_purchase_amount_sum_ratio']  = df['category_1_1_purchase_amount_sum'] / df['category_1_1_purchase_amount_sum_old']\n",
    "    df['category_2_1.0_purchase_amount_mean_ratio']  = df['category_2_1.0_purchase_amount_mean'] / df['category_2_1.0_purchase_amount_mean_old']\n",
    "    df['category_2_2.0_purchase_amount_mean_ratio']  = df['category_2_2.0_purchase_amount_mean'] / df['category_2_2.0_purchase_amount_mean_old']\n",
    "    df['category_2_3.0_purchase_amount_mean_ratio']  = df['category_2_3.0_purchase_amount_mean'] / df['category_2_3.0_purchase_amount_mean_old']\n",
    "    df['category_2_4.0_purchase_amount_mean_ratio']  = df['category_2_4.0_purchase_amount_mean'] / df['category_2_4.0_purchase_amount_mean_old']\n",
    "    df['category_2_5.0_purchase_amount_mean_ratio']  = df['category_2_5.0_purchase_amount_mean'] / df['category_2_5.0_purchase_amount_mean_old']\n",
    "    df['category_2_1.0_purchase_amount_sum_ratio']  = df['category_2_1.0_purchase_amount_sum'] / df['category_2_1.0_purchase_amount_sum_old']\n",
    "    df['category_2_2.0_purchase_amount_sum_ratio']  = df['category_2_2.0_purchase_amount_sum'] / df['category_2_2.0_purchase_amount_sum_old']\n",
    "    df['category_2_3.0_purchase_amount_sum_ratio']  = df['category_2_3.0_purchase_amount_sum'] / df['category_2_3.0_purchase_amount_sum_old']\n",
    "    df['category_2_4.0_purchase_amount_sum_ratio']  = df['category_2_4.0_purchase_amount_sum'] / df['category_2_4.0_purchase_amount_sum_old']\n",
    "    df['category_2_5.0_purchase_amount_sum_ratio']  = df['category_2_5.0_purchase_amount_sum'] / df['category_2_5.0_purchase_amount_sum_old']\n",
    "    df['category_3_0_purchase_amount_mean_ratio']  = df['category_3_0_purchase_amount_mean'] / df['category_3_0_purchase_amount_mean_old']\n",
    "    df['category_3_1_purchase_amount_mean_ratio']  = df['category_3_1_purchase_amount_mean'] / df['category_3_1_purchase_amount_mean_old']\n",
    "    df['category_3_2_purchase_amount_mean_ratio']  = df['category_3_2_purchase_amount_mean'] / df['category_3_2_purchase_amount_mean_old']\n",
    "    df['category_3_0_purchase_amount_sum_ratio']  = df['category_3_0_purchase_amount_sum'] / df['category_3_0_purchase_amount_sum_old']\n",
    "    df['category_3_1_purchase_amount_sum_ratio']  = df['category_3_1_purchase_amount_sum'] / df['category_3_1_purchase_amount_sum_old']\n",
    "    df['category_3_2_purchase_amount_sum_ratio']  = df['category_3_2_purchase_amount_sum'] / df['category_3_2_purchase_amount_sum_old']    \n",
    "    df['purchase_amount_sum_total'] = df['purchase_amount_sum'] + df['purchase_amount_sum_old']\n",
    "    df['purchase_amount_sum_ratio'] = df['purchase_amount_sum'] / df['purchase_amount_sum_old']\n",
    "    df['purchase_amount_pct_75_ratio'] = df['purchase_amount_pct_75'] / df['purchase_amount_pct_75_old']\n",
    "    df['purchase_amount_pct_25_ratio'] = df['purchase_amount_pct_25'] / df['purchase_amount_pct_25_old']\n",
    "    df['purchase_amount_inter_quartile'] = df['purchase_amount_pct_75'] - df['purchase_amount_pct_25']\n",
    "    df['purchase_amount_inter_quartile_old'] = df['purchase_amount_pct_75_old'] - df['purchase_amount_pct_25_old']\n",
    "    df['purchase_amount_inter_quartile_ratio'] = df['purchase_amount_inter_quartile'] / df['purchase_amount_inter_quartile_old']\n",
    "    df['purchase_amount_mean_total'] = df['purchase_amount_mean'] + df['purchase_amount_mean_old']\n",
    "    df['purchase_amount_mean_ratio'] = df['purchase_amount_mean'] / df['purchase_amount_mean_old']\n",
    "    df['purchase_amount_median_ratio'] = df['purchase_amount_median'] / df['purchase_amount_median_old']\n",
    "    df['purchase_amount_max_total'] = df['purchase_amount_max'] + df['purchase_amount_max_old']\n",
    "    df['purchase_amount_min_total'] = df['purchase_amount_min'] + df['purchase_amount_min_old']\n",
    "    df['purchase_amount_skew_ratio'] = df['purchase_amount_skew'] / df['purchase_amount_skew_old']\n",
    "    df['purchase_amount_before_after_ratio'] = df['purchase_amount_head_sum'] / df['purchase_amount_tail_sum_old']\n",
    "    df['purchase_amount_first_last_diff'] = df['purchase_amount_tail_sum'] - df['purchase_amount_head_max_old']\n",
    "    df['purchase_amount_fi_last_old_total'] = df['purchase_amount_tail_sum'] -  df['purchase_amount_head_sum']\n",
    "    df['purchase_amount_fi_last_new_total'] = df['purchase_amount_tail_sum_old'] -  df['purchase_amount_head_max_old']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mark the outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    199710\n",
       "1      2207\n",
       "Name: outliers, dtype: int64"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df['outliers'] = 0\n",
    "train_df.loc[train_df['target'] < -30, 'outliers'] = 1\n",
    "train_df['outliers'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Target encode the outliers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 354), (123623, 352))"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "266"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "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": 119,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "train_df.to_feather('train_df_alpha')\n",
    "test_df.to_feather('test_df_alpha')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = feather.read_dataframe('train_df_alpha')\n",
    "test_df = feather.read_dataframe('test_df_alpha')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Transform purchase_value to actual amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans['purchase_amount_new'] = np.round(new_hist_trans['purchase_amount'] / 0.00150265118 + 497.06,2)\n",
    "hist_trans['purchase_amount_new'] = np.round(hist_trans['purchase_amount'] / 0.00150265118 + 497.06,2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans_purchase_aggs = new_hist_trans.groupby('card_id').agg({\n",
    "    'purchase_amount_new': [('sum', 'sum'), ('pct_75', lambda x: np.percentile(x, q = 75)), \n",
    "                            ('pct_25', lambda x: np.percentile(x, q = 25)), ('mean', 'mean'), \n",
    "                            ('median', 'median'), ('max', 'max'), ('min', 'min'), ('var', 'var'), \n",
    "                            ('skew', 'skew'), ('head_sum', head_sum), ('head_max', head_max), \n",
    "                            ('tail_sum', tail_sum), ('tail_max', tail_max), ('gmean', scipy.stats.gmean ),\n",
    "                            ('hmean', scipy.stats.hmean)]\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans_purchase_aggs.columns = [ '_'.join(c).strip() for c in new_hist_trans_purchase_aggs.columns.values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "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>purchase_amount_new_sum</th>\n",
       "      <th>purchase_amount_new_pct_75</th>\n",
       "      <th>purchase_amount_new_pct_25</th>\n",
       "      <th>purchase_amount_new_mean</th>\n",
       "      <th>purchase_amount_new_median</th>\n",
       "      <th>purchase_amount_new_max</th>\n",
       "      <th>purchase_amount_new_min</th>\n",
       "      <th>purchase_amount_new_var</th>\n",
       "      <th>purchase_amount_new_skew</th>\n",
       "      <th>purchase_amount_new_head_sum</th>\n",
       "      <th>purchase_amount_new_head_max</th>\n",
       "      <th>purchase_amount_new_tail_sum</th>\n",
       "      <th>purchase_amount_new_tail_max</th>\n",
       "      <th>purchase_amount_new_gmean</th>\n",
       "      <th>purchase_amount_new_hmean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_00007093c1</th>\n",
       "      <td>110.00</td>\n",
       "      <td>57.50</td>\n",
       "      <td>52.5000</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>55.00</td>\n",
       "      <td>60.00</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110.00</td>\n",
       "      <td>60.00</td>\n",
       "      <td>110.00</td>\n",
       "      <td>60.0</td>\n",
       "      <td>54.772256</td>\n",
       "      <td>54.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001238066</th>\n",
       "      <td>3040.99</td>\n",
       "      <td>165.75</td>\n",
       "      <td>35.9375</td>\n",
       "      <td>116.961154</td>\n",
       "      <td>65.00</td>\n",
       "      <td>444.94</td>\n",
       "      <td>4.00</td>\n",
       "      <td>13321.705107</td>\n",
       "      <td>1.565684</td>\n",
       "      <td>396.11</td>\n",
       "      <td>262.95</td>\n",
       "      <td>643.32</td>\n",
       "      <td>250.0</td>\n",
       "      <td>71.052981</td>\n",
       "      <td>35.144788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001506ef0</th>\n",
       "      <td>30.92</td>\n",
       "      <td>18.23</td>\n",
       "      <td>12.6900</td>\n",
       "      <td>15.460000</td>\n",
       "      <td>15.46</td>\n",
       "      <td>21.00</td>\n",
       "      <td>9.92</td>\n",
       "      <td>61.383200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.92</td>\n",
       "      <td>21.00</td>\n",
       "      <td>30.92</td>\n",
       "      <td>21.0</td>\n",
       "      <td>14.433295</td>\n",
       "      <td>13.474774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001793786</th>\n",
       "      <td>15256.05</td>\n",
       "      <td>599.30</td>\n",
       "      <td>115.4350</td>\n",
       "      <td>492.130645</td>\n",
       "      <td>249.00</td>\n",
       "      <td>2580.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>397363.952280</td>\n",
       "      <td>2.196684</td>\n",
       "      <td>1678.47</td>\n",
       "      <td>619.60</td>\n",
       "      <td>2754.59</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>230.090246</td>\n",
       "      <td>73.192245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000183fdda</th>\n",
       "      <td>1081.56</td>\n",
       "      <td>107.69</td>\n",
       "      <td>28.4900</td>\n",
       "      <td>98.323636</td>\n",
       "      <td>54.00</td>\n",
       "      <td>425.40</td>\n",
       "      <td>9.70</td>\n",
       "      <td>14811.537265</td>\n",
       "      <td>2.277065</td>\n",
       "      <td>345.90</td>\n",
       "      <td>191.20</td>\n",
       "      <td>665.66</td>\n",
       "      <td>425.4</td>\n",
       "      <td>55.401860</td>\n",
       "      <td>33.052496</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 purchase_amount_new_sum  purchase_amount_new_pct_75  \\\n",
       "card_id                                                                \n",
       "C_ID_00007093c1                   110.00                       57.50   \n",
       "C_ID_0001238066                  3040.99                      165.75   \n",
       "C_ID_0001506ef0                    30.92                       18.23   \n",
       "C_ID_0001793786                 15256.05                      599.30   \n",
       "C_ID_000183fdda                  1081.56                      107.69   \n",
       "\n",
       "                 purchase_amount_new_pct_25  purchase_amount_new_mean  \\\n",
       "card_id                                                                 \n",
       "C_ID_00007093c1                     52.5000                 55.000000   \n",
       "C_ID_0001238066                     35.9375                116.961154   \n",
       "C_ID_0001506ef0                     12.6900                 15.460000   \n",
       "C_ID_0001793786                    115.4350                492.130645   \n",
       "C_ID_000183fdda                     28.4900                 98.323636   \n",
       "\n",
       "                 purchase_amount_new_median  purchase_amount_new_max  \\\n",
       "card_id                                                                \n",
       "C_ID_00007093c1                       55.00                    60.00   \n",
       "C_ID_0001238066                       65.00                   444.94   \n",
       "C_ID_0001506ef0                       15.46                    21.00   \n",
       "C_ID_0001793786                      249.00                  2580.00   \n",
       "C_ID_000183fdda                       54.00                   425.40   \n",
       "\n",
       "                 purchase_amount_new_min  purchase_amount_new_var  \\\n",
       "card_id                                                             \n",
       "C_ID_00007093c1                    50.00                50.000000   \n",
       "C_ID_0001238066                     4.00             13321.705107   \n",
       "C_ID_0001506ef0                     9.92                61.383200   \n",
       "C_ID_0001793786                     6.00            397363.952280   \n",
       "C_ID_000183fdda                     9.70             14811.537265   \n",
       "\n",
       "                 purchase_amount_new_skew  purchase_amount_new_head_sum  \\\n",
       "card_id                                                                   \n",
       "C_ID_00007093c1                       NaN                        110.00   \n",
       "C_ID_0001238066                  1.565684                        396.11   \n",
       "C_ID_0001506ef0                       NaN                         30.92   \n",
       "C_ID_0001793786                  2.196684                       1678.47   \n",
       "C_ID_000183fdda                  2.277065                        345.90   \n",
       "\n",
       "                 purchase_amount_new_head_max  purchase_amount_new_tail_sum  \\\n",
       "card_id                                                                       \n",
       "C_ID_00007093c1                         60.00                        110.00   \n",
       "C_ID_0001238066                        262.95                        643.32   \n",
       "C_ID_0001506ef0                         21.00                         30.92   \n",
       "C_ID_0001793786                        619.60                       2754.59   \n",
       "C_ID_000183fdda                        191.20                        665.66   \n",
       "\n",
       "                 purchase_amount_new_tail_max  purchase_amount_new_gmean  \\\n",
       "card_id                                                                    \n",
       "C_ID_00007093c1                          60.0                  54.772256   \n",
       "C_ID_0001238066                         250.0                  71.052981   \n",
       "C_ID_0001506ef0                          21.0                  14.433295   \n",
       "C_ID_0001793786                        1500.0                 230.090246   \n",
       "C_ID_000183fdda                         425.4                  55.401860   \n",
       "\n",
       "                 purchase_amount_new_hmean  \n",
       "card_id                                     \n",
       "C_ID_00007093c1                  54.545455  \n",
       "C_ID_0001238066                  35.144788  \n",
       "C_ID_0001506ef0                  13.474774  \n",
       "C_ID_0001793786                  73.192245  \n",
       "C_ID_000183fdda                  33.052496  "
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_purchase_aggs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>purchase_amount_new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.91124e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>521.284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>747693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>17.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>38.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>95.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>counts</th>\n",
       "      <td>29112361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uniques</th>\n",
       "      <td>215014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>missing</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>missing_perc</th>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>types</th>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             purchase_amount_new\n",
       "count                2.91124e+07\n",
       "mean                     521.284\n",
       "std                       747693\n",
       "min                           -0\n",
       "25%                        17.67\n",
       "50%                        38.97\n",
       "75%                         95.6\n",
       "max                        4e+09\n",
       "counts                  29112361\n",
       "uniques                   215014\n",
       "missing                        0\n",
       "missing_perc                  0%\n",
       "types                    numeric"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrameSummary(hist_trans[['purchase_amount_new']]).summary()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans.loc[hist_trans['purchase_amount_new'] == 0, 'purchase_amount_new'] = 0.0001"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_purchase_aggs = hist_trans.groupby('card_id').agg({\n",
    "        'purchase_amount_new': [('sum', 'sum'), ('pct_75', lambda x: np.percentile(x, q = 75)), \n",
    "                                ('pct_25', lambda x: np.percentile(x, q = 25)), ('mean', 'mean'), \n",
    "                                ('median', 'median'), ('max', 'max'), ('min', 'min'), ('var', 'var'), \n",
    "                                ('skew', 'skew'), ('head_sum', head_sum), ('head_max', head_max), \n",
    "                                ('tail_sum', tail_sum), ('tail_max', tail_max), ('gmean', scipy.stats.gmean ),\n",
    "                                ('hmean', scipy.stats.hmean)]\n",
    "    })\n",
    "hist_trans_purchase_aggs.columns = [ '_'.join(c).strip() for c in hist_trans_purchase_aggs.columns.values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "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>purchase_amount_new_sum</th>\n",
       "      <th>purchase_amount_new_pct_75</th>\n",
       "      <th>purchase_amount_new_pct_25</th>\n",
       "      <th>purchase_amount_new_mean</th>\n",
       "      <th>purchase_amount_new_median</th>\n",
       "      <th>purchase_amount_new_max</th>\n",
       "      <th>purchase_amount_new_min</th>\n",
       "      <th>purchase_amount_new_var</th>\n",
       "      <th>purchase_amount_new_skew</th>\n",
       "      <th>purchase_amount_new_head_sum</th>\n",
       "      <th>purchase_amount_new_head_max</th>\n",
       "      <th>purchase_amount_new_tail_sum</th>\n",
       "      <th>purchase_amount_new_tail_max</th>\n",
       "      <th>purchase_amount_new_gmean</th>\n",
       "      <th>purchase_amount_new_hmean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_00007093c1</th>\n",
       "      <td>22922.30</td>\n",
       "      <td>175.00</td>\n",
       "      <td>49.0000</td>\n",
       "      <td>153.840940</td>\n",
       "      <td>106.000</td>\n",
       "      <td>1500.00</td>\n",
       "      <td>12.00</td>\n",
       "      <td>39366.639148</td>\n",
       "      <td>3.966544</td>\n",
       "      <td>1275.20</td>\n",
       "      <td>521.80</td>\n",
       "      <td>571.35</td>\n",
       "      <td>207.44</td>\n",
       "      <td>91.317804</td>\n",
       "      <td>55.062068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001238066</th>\n",
       "      <td>12925.46</td>\n",
       "      <td>130.64</td>\n",
       "      <td>31.3100</td>\n",
       "      <td>105.085041</td>\n",
       "      <td>65.700</td>\n",
       "      <td>1008.22</td>\n",
       "      <td>8.00</td>\n",
       "      <td>16027.499309</td>\n",
       "      <td>4.057432</td>\n",
       "      <td>413.13</td>\n",
       "      <td>140.95</td>\n",
       "      <td>387.41</td>\n",
       "      <td>150.00</td>\n",
       "      <td>65.544268</td>\n",
       "      <td>40.911087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001506ef0</th>\n",
       "      <td>9778.74</td>\n",
       "      <td>67.25</td>\n",
       "      <td>16.1575</td>\n",
       "      <td>148.162727</td>\n",
       "      <td>28.750</td>\n",
       "      <td>1491.00</td>\n",
       "      <td>4.27</td>\n",
       "      <td>98784.967756</td>\n",
       "      <td>2.692444</td>\n",
       "      <td>138.98</td>\n",
       "      <td>51.46</td>\n",
       "      <td>1582.00</td>\n",
       "      <td>1491.00</td>\n",
       "      <td>38.244143</td>\n",
       "      <td>18.058872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001793786</th>\n",
       "      <td>82884.22</td>\n",
       "      <td>417.50</td>\n",
       "      <td>84.5500</td>\n",
       "      <td>383.723241</td>\n",
       "      <td>172.360</td>\n",
       "      <td>3527.80</td>\n",
       "      <td>1.00</td>\n",
       "      <td>309559.393304</td>\n",
       "      <td>3.001742</td>\n",
       "      <td>3125.48</td>\n",
       "      <td>1390.00</td>\n",
       "      <td>1529.41</td>\n",
       "      <td>643.71</td>\n",
       "      <td>177.770755</td>\n",
       "      <td>50.410725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000183fdda</th>\n",
       "      <td>25765.65</td>\n",
       "      <td>132.50</td>\n",
       "      <td>29.9775</td>\n",
       "      <td>178.928125</td>\n",
       "      <td>56.975</td>\n",
       "      <td>2337.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>122043.717880</td>\n",
       "      <td>3.840939</td>\n",
       "      <td>2112.00</td>\n",
       "      <td>719.40</td>\n",
       "      <td>323.83</td>\n",
       "      <td>164.89</td>\n",
       "      <td>72.398621</td>\n",
       "      <td>41.244599</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 purchase_amount_new_sum  purchase_amount_new_pct_75  \\\n",
       "card_id                                                                \n",
       "C_ID_00007093c1                 22922.30                      175.00   \n",
       "C_ID_0001238066                 12925.46                      130.64   \n",
       "C_ID_0001506ef0                  9778.74                       67.25   \n",
       "C_ID_0001793786                 82884.22                      417.50   \n",
       "C_ID_000183fdda                 25765.65                      132.50   \n",
       "\n",
       "                 purchase_amount_new_pct_25  purchase_amount_new_mean  \\\n",
       "card_id                                                                 \n",
       "C_ID_00007093c1                     49.0000                153.840940   \n",
       "C_ID_0001238066                     31.3100                105.085041   \n",
       "C_ID_0001506ef0                     16.1575                148.162727   \n",
       "C_ID_0001793786                     84.5500                383.723241   \n",
       "C_ID_000183fdda                     29.9775                178.928125   \n",
       "\n",
       "                 purchase_amount_new_median  purchase_amount_new_max  \\\n",
       "card_id                                                                \n",
       "C_ID_00007093c1                     106.000                  1500.00   \n",
       "C_ID_0001238066                      65.700                  1008.22   \n",
       "C_ID_0001506ef0                      28.750                  1491.00   \n",
       "C_ID_0001793786                     172.360                  3527.80   \n",
       "C_ID_000183fdda                      56.975                  2337.00   \n",
       "\n",
       "                 purchase_amount_new_min  purchase_amount_new_var  \\\n",
       "card_id                                                             \n",
       "C_ID_00007093c1                    12.00             39366.639148   \n",
       "C_ID_0001238066                     8.00             16027.499309   \n",
       "C_ID_0001506ef0                     4.27             98784.967756   \n",
       "C_ID_0001793786                     1.00            309559.393304   \n",
       "C_ID_000183fdda                     6.00            122043.717880   \n",
       "\n",
       "                 purchase_amount_new_skew  purchase_amount_new_head_sum  \\\n",
       "card_id                                                                   \n",
       "C_ID_00007093c1                  3.966544                       1275.20   \n",
       "C_ID_0001238066                  4.057432                        413.13   \n",
       "C_ID_0001506ef0                  2.692444                        138.98   \n",
       "C_ID_0001793786                  3.001742                       3125.48   \n",
       "C_ID_000183fdda                  3.840939                       2112.00   \n",
       "\n",
       "                 purchase_amount_new_head_max  purchase_amount_new_tail_sum  \\\n",
       "card_id                                                                       \n",
       "C_ID_00007093c1                        521.80                        571.35   \n",
       "C_ID_0001238066                        140.95                        387.41   \n",
       "C_ID_0001506ef0                         51.46                       1582.00   \n",
       "C_ID_0001793786                       1390.00                       1529.41   \n",
       "C_ID_000183fdda                        719.40                        323.83   \n",
       "\n",
       "                 purchase_amount_new_tail_max  purchase_amount_new_gmean  \\\n",
       "card_id                                                                    \n",
       "C_ID_00007093c1                        207.44                  91.317804   \n",
       "C_ID_0001238066                        150.00                  65.544268   \n",
       "C_ID_0001506ef0                       1491.00                  38.244143   \n",
       "C_ID_0001793786                        643.71                 177.770755   \n",
       "C_ID_000183fdda                        164.89                  72.398621   \n",
       "\n",
       "                 purchase_amount_new_hmean  \n",
       "card_id                                     \n",
       "C_ID_00007093c1                  55.062068  \n",
       "C_ID_0001238066                  40.911087  \n",
       "C_ID_0001506ef0                  18.058872  \n",
       "C_ID_0001793786                  50.410725  \n",
       "C_ID_000183fdda                  41.244599  "
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_purchase_aggs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_purchase_aggs.reset_index().to_feather('hist_trans_purchase_aggs_alpha')\n",
    "new_hist_trans_purchase_aggs.reset_index().to_feather('new_hist_trans_purchase_aggs_alpha')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 357), (123623, 355), (325540, 15), (290001, 15))"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape, hist_trans_purchase_aggs.shape, new_hist_trans_purchase_aggs.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train_df, hist_trans_purchase_aggs, left_on='card_id', suffix='_old')\n",
    "train_df = join_dfs(train_df, new_hist_trans_purchase_aggs, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df = join_dfs(test_df, hist_trans_purchase_aggs, left_on='card_id', suffix='_old')\n",
    "test_df = join_dfs(test_df, new_hist_trans_purchase_aggs, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['purchase_amount_new_sum', 'purchase_amount_new_pct_75', 'purchase_amount_new_pct_25',\n",
       "       'purchase_amount_new_mean', 'purchase_amount_new_median', 'purchase_amount_new_max',\n",
       "       'purchase_amount_new_min', 'purchase_amount_new_var', 'purchase_amount_new_skew',\n",
       "       'purchase_amount_new_head_sum', 'purchase_amount_new_head_max', 'purchase_amount_new_tail_sum',\n",
       "       'purchase_amount_new_tail_max', 'purchase_amount_new_gmean', 'purchase_amount_new_hmean'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_purchase_aggs.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in [train_df, test_df]:\n",
    "    df['purchase_amount_new_sum_total'] = df['purchase_amount_new_sum'] + df['purchase_amount_new_sum_old']\n",
    "    df['purchase_amount_new_sum_ratio'] = df['purchase_amount_new_sum'] / df['purchase_amount_new_sum_old']\n",
    "    df['purchase_amount_new_pct_75_ratio'] = df['purchase_amount_new_pct_75'] / df['purchase_amount_new_pct_75_old']\n",
    "    df['purchase_amount_new_pct_25_ratio'] = df['purchase_amount_new_pct_25'] / df['purchase_amount_new_pct_25_old']\n",
    "    df['purchase_amount_new_inter_quartile'] = df['purchase_amount_new_pct_75'] - df['purchase_amount_new_pct_25']\n",
    "    df['purchase_amount_new_inter_quartile_old'] = df['purchase_amount_new_pct_75_old'] - df['purchase_amount_new_pct_25_old']\n",
    "    df['purchase_amount_new_inter_quartile_ratio'] = df['purchase_amount_new_inter_quartile'] / df['purchase_amount_new_inter_quartile_old']\n",
    "    df['purchase_amount_new_mean_total'] = df['purchase_amount_new_mean'] + df['purchase_amount_new_mean_old']\n",
    "    df['purchase_amount_new_mean_ratio'] = df['purchase_amount_new_mean'] / df['purchase_amount_new_mean_old']\n",
    "    df['purchase_amount_new_median_ratio'] = df['purchase_amount_new_median'] / df['purchase_amount_new_median_old']\n",
    "    df['purchase_amount_new_max_total'] = df['purchase_amount_new_max'] + df['purchase_amount_new_max_old']\n",
    "    df['purchase_amount_new_min_total'] = df['purchase_amount_new_min'] + df['purchase_amount_new_min_old']\n",
    "    df['purchase_amount_new_skew_ratio'] = df['purchase_amount_new_skew'] / df['purchase_amount_new_skew_old']\n",
    "    df['purchase_amount_new_before_after_ratio'] = df['purchase_amount_new_head_sum'] / df['purchase_amount_new_tail_sum_old']\n",
    "    df['purchase_amount_new_first_last_diff'] = df['purchase_amount_new_tail_sum'] - df['purchase_amount_new_head_max_old']\n",
    "    df['purchase_amount_new_fi_last_old_total'] = df['purchase_amount_new_tail_sum'] -  df['purchase_amount_new_head_sum']\n",
    "    df['purchase_amount_new_fi_last_new_total'] = df['purchase_amount_new_tail_sum_old'] -  df['purchase_amount_new_head_max_old']\n",
    "    df['purchase_amount_new_gmean_total'] = df['purchase_amount_new_gmean'] + df['purchase_amount_new_gmean_old']\n",
    "    df['purchase_amount_new_hmean_total'] = df['purchase_amount_new_hmean'] + df['purchase_amount_new_hmean_old']\n",
    "    df['purchase_amount_new_gmean_ratio'] = df['purchase_amount_new_gmean'] / df['purchase_amount_new_gmean_old']\n",
    "    df['purchase_amount_new_hmean_ratio'] = df['purchase_amount_new_hmean'] / df['purchase_amount_new_hmean_old']\n",
    "    df['purchase_amount_sum_total'] = df['purchase_amount_sum'] + df['purchase_amount_sum_old']\n",
    "    df['purchase_amount_sum_ratio'] = df['purchase_amount_sum'] / df['purchase_amount_sum_old']\n",
    "    df['purchase_amount_pct_75_ratio'] = df['purchase_amount_pct_75'] / df['purchase_amount_pct_75_old']\n",
    "    df['purchase_amount_pct_25_ratio'] = df['purchase_amount_pct_25'] / df['purchase_amount_pct_25_old']\n",
    "    df['purchase_amount_inter_quartile'] = df['purchase_amount_pct_75'] - df['purchase_amount_pct_25']\n",
    "    df['purchase_amount_inter_quartile_old'] = df['purchase_amount_pct_75_old'] - df['purchase_amount_pct_25_old']\n",
    "    df['purchase_amount_inter_quartile_ratio'] = df['purchase_amount_inter_quartile'] / df['purchase_amount_inter_quartile_old']\n",
    "    df['purchase_amount_mean_total'] = df['purchase_amount_mean'] + df['purchase_amount_mean_old']\n",
    "    df['purchase_amount_mean_ratio'] = df['purchase_amount_mean'] / df['purchase_amount_mean_old']\n",
    "    df['purchase_amount_median_ratio'] = df['purchase_amount_median'] / df['purchase_amount_median_old']\n",
    "    df['purchase_amount_max_total'] = df['purchase_amount_max'] + df['purchase_amount_max_old']\n",
    "    df['purchase_amount_min_total'] = df['purchase_amount_min'] + df['purchase_amount_min_old']\n",
    "    df['purchase_amount_skew_ratio'] = df['purchase_amount_skew'] / df['purchase_amount_skew_old']\n",
    "    df['purchase_amount_before_after_ratio'] = df['purchase_amount_head_sum'] / df['purchase_amount_tail_sum_old']\n",
    "    df['purchase_amount_first_last_diff'] = df['purchase_amount_tail_sum'] - df['purchase_amount_head_max_old']\n",
    "    df['purchase_amount_fi_last_old_total'] = df['purchase_amount_tail_sum'] -  df['purchase_amount_head_sum']\n",
    "    df['purchase_amount_fi_last_new_total'] = df['purchase_amount_tail_sum_old'] -  df['purchase_amount_head_max_old']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 424), (123623, 422))"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.to_feather('train_df_alpha')\n",
    "test_df.to_feather('test_df_alpha')"
   ]
  },
  {
   "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.8"
  },
  "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
}