Continuous data processing.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\"></ul></div>"
   ]
  },
  {
   "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": [
    "%matplotlib inline\n",
    "\n",
    "from fastai.imports import *\n",
    "from fastai.torch_imports import *\n",
    "from fastai.dataset import *\n",
    "from fastai.learner import *\n",
    "from fastai.structured import *\n",
    "from fastai.column_data import *\n",
    "import feather\n",
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = '../data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/pyarrow/pandas_compat.py:752: FutureWarning: .labels was deprecated in version 0.24.0. Use .codes instead.\n",
      "  labels, = index.labels\n"
     ]
    }
   ],
   "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": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['authorized_flag', 'card_id', 'city_id', 'category_1', 'installments',\n",
       "       'category_3', 'merchant_category_id', 'merchant_id', 'month_lag',\n",
       "       'purchase_amount', 'purchase_date', 'category_2', 'state_id',\n",
       "       'subsector_id', 'purchase_Year', 'purchase_Month', 'purchase_Week',\n",
       "       'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear',\n",
       "       'purchase_Is_month_end', 'purchase_Is_month_start',\n",
       "       'purchase_Is_quarter_end', 'purchase_Is_quarter_start',\n",
       "       'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Hour',\n",
       "       'purchase_Minute', 'purchase_Second', 'purchase_Elapsed',\n",
       "       'purchased_on_weekend', 'purchased_on_weekday', 'month_diff',\n",
       "       'purchase_date_successive_diff', 'price', 'Christmas_Day_2017',\n",
       "       'Mothers_Day_2017', 'fathers_day_2017', 'Children_day_2017',\n",
       "       'Valentine_Day_2017', 'Black_Friday_2017', 'Mothers_Day_2018',\n",
       "       'duration', 'amount_month_ratio', 'category_2_mean', 'category_2_min',\n",
       "       'category_2_max', 'category_2_sum', 'category_3_mean', 'category_3_min',\n",
       "       'category_3_max', 'category_3_sum', 'category_4', 'merchant_address_id',\n",
       "       'numerical_range', 'merchant_rating'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_cols = ['category_2_mean', 'category_2_min',\n",
    "       'category_2_max', 'category_2_sum', 'category_3_mean', 'category_3_min',\n",
    "       'category_3_max', 'category_3_sum', 'category_4', 'city_id', 'state_id', \n",
    "             'category_2', 'category_1', 'subsector_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in [hist_trans, new_hist_trans]:\n",
    "    df.drop(drop_cols, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "merchants = pd.read_csv(f'{PATH}merchants.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['authorized_flag', 'card_id', 'installments', 'category_3', 'merchant_category_id', 'merchant_id',\n",
       "       'month_lag', 'purchase_amount', 'purchase_date', 'purchase_Year', 'purchase_Month', 'purchase_Week',\n",
       "       'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear', 'purchase_Is_month_end',\n",
       "       'purchase_Is_month_start', 'purchase_Is_quarter_end', 'purchase_Is_quarter_start',\n",
       "       'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Hour', 'purchase_Minute',\n",
       "       'purchase_Second', 'purchase_Elapsed', 'purchased_on_weekend', 'purchased_on_weekday', 'month_diff',\n",
       "       'purchase_date_successive_diff', 'price', 'Christmas_Day_2017', 'Mothers_Day_2017',\n",
       "       'fathers_day_2017', 'Children_day_2017', 'Valentine_Day_2017', 'Black_Friday_2017',\n",
       "       'Mothers_Day_2018', 'duration', 'amount_month_ratio', 'merchant_address_id', 'numerical_range',\n",
       "       'merchant_rating'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_cols = ['authorized_flag', 'category_3', 'merchant_category_id', 'purchase_Year', 'purchase_Month', 'purchase_Week',\n",
    "       'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear', 'purchase_Hour', 'purchase_Minute',\n",
    "       'purchase_Second', 'purchased_on_weekend', 'purchased_on_weekday', 'Christmas_Day_2017', 'Mothers_Day_2017',\n",
    "       'fathers_day_2017', 'Children_day_2017', 'Valentine_Day_2017', 'Black_Friday_2017',\n",
    "       'Mothers_Day_2018', 'merchant_rating']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in cat_cols: hist_trans[c] = hist_trans[c].astype('category') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 29112368 entries, 0 to 29112367\n",
      "Data columns (total 42 columns):\n",
      "authorized_flag                  category\n",
      "card_id                          object\n",
      "installments                     float64\n",
      "category_3                       category\n",
      "merchant_category_id             category\n",
      "merchant_id                      object\n",
      "month_lag                        int64\n",
      "purchase_amount                  float64\n",
      "purchase_date                    datetime64[ns]\n",
      "purchase_Year                    category\n",
      "purchase_Month                   category\n",
      "purchase_Week                    category\n",
      "purchase_Day                     category\n",
      "purchase_Dayofweek               category\n",
      "purchase_Dayofyear               category\n",
      "purchase_Is_month_end            bool\n",
      "purchase_Is_month_start          bool\n",
      "purchase_Is_quarter_end          bool\n",
      "purchase_Is_quarter_start        bool\n",
      "purchase_Is_year_end             bool\n",
      "purchase_Is_year_start           bool\n",
      "purchase_Hour                    category\n",
      "purchase_Minute                  category\n",
      "purchase_Second                  category\n",
      "purchase_Elapsed                 int64\n",
      "purchased_on_weekend             category\n",
      "purchased_on_weekday             category\n",
      "month_diff                       int64\n",
      "purchase_date_successive_diff    float64\n",
      "price                            float64\n",
      "Christmas_Day_2017               category\n",
      "Mothers_Day_2017                 category\n",
      "fathers_day_2017                 category\n",
      "Children_day_2017                category\n",
      "Valentine_Day_2017               category\n",
      "Black_Friday_2017                category\n",
      "Mothers_Day_2018                 category\n",
      "duration                         float64\n",
      "amount_month_ratio               float64\n",
      "merchant_address_id              object\n",
      "numerical_range                  float64\n",
      "merchant_rating                  category\n",
      "dtypes: bool(6), category(22), datetime64[ns](1), float64(7), int64(3), object(3)\n",
      "memory usage: 3.9+ GB\n"
     ]
    }
   ],
   "source": [
    "hist_trans.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "apply_cats(new_hist_trans, hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1963031 entries, 0 to 1963030\n",
      "Data columns (total 42 columns):\n",
      "authorized_flag                  category\n",
      "card_id                          object\n",
      "installments                     float64\n",
      "category_3                       category\n",
      "merchant_category_id             category\n",
      "merchant_id                      object\n",
      "month_lag                        int64\n",
      "purchase_amount                  float64\n",
      "purchase_date                    datetime64[ns]\n",
      "purchase_Year                    category\n",
      "purchase_Month                   category\n",
      "purchase_Week                    category\n",
      "purchase_Day                     category\n",
      "purchase_Dayofweek               category\n",
      "purchase_Dayofyear               category\n",
      "purchase_Is_month_end            bool\n",
      "purchase_Is_month_start          bool\n",
      "purchase_Is_quarter_end          bool\n",
      "purchase_Is_quarter_start        bool\n",
      "purchase_Is_year_end             bool\n",
      "purchase_Is_year_start           bool\n",
      "purchase_Hour                    category\n",
      "purchase_Minute                  category\n",
      "purchase_Second                  category\n",
      "purchase_Elapsed                 int64\n",
      "purchased_on_weekend             category\n",
      "purchased_on_weekday             category\n",
      "month_diff                       int64\n",
      "purchase_date_successive_diff    float64\n",
      "price                            float64\n",
      "Christmas_Day_2017               category\n",
      "Mothers_Day_2017                 category\n",
      "fathers_day_2017                 category\n",
      "Children_day_2017                category\n",
      "Valentine_Day_2017               category\n",
      "Black_Friday_2017                category\n",
      "Mothers_Day_2018                 category\n",
      "duration                         float64\n",
      "amount_month_ratio               float64\n",
      "merchant_address_id              object\n",
      "numerical_range                  float64\n",
      "merchant_rating                  category\n",
      "dtypes: bool(6), category(22), datetime64[ns](1), float64(7), int64(3), object(3)\n",
      "memory usage: 265.9+ MB\n"
     ]
    }
   ],
   "source": [
    "new_hist_trans.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['merchant_id', 'merchant_group_id', 'merchant_category_id', 'subsector_id', 'numerical_1',\n",
       "       'numerical_2', 'category_1', 'most_recent_sales_range', 'most_recent_purchases_range',\n",
       "       'avg_sales_lag3', 'avg_purchases_lag3', 'active_months_lag3', 'avg_sales_lag6', 'avg_purchases_lag6',\n",
       "       'active_months_lag6', 'avg_sales_lag12', 'avg_purchases_lag12', 'active_months_lag12', 'category_4',\n",
       "       'city_id', 'state_id', 'category_2'], dtype=object)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchants.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 334696 entries, 0 to 334695\n",
      "Data columns (total 22 columns):\n",
      "merchant_id                    334696 non-null object\n",
      "merchant_group_id              334696 non-null int64\n",
      "merchant_category_id           334696 non-null int64\n",
      "subsector_id                   334696 non-null int64\n",
      "numerical_1                    334696 non-null float64\n",
      "numerical_2                    334696 non-null float64\n",
      "category_1                     334696 non-null object\n",
      "most_recent_sales_range        334696 non-null object\n",
      "most_recent_purchases_range    334696 non-null object\n",
      "avg_sales_lag3                 334683 non-null float64\n",
      "avg_purchases_lag3             334696 non-null float64\n",
      "active_months_lag3             334696 non-null int64\n",
      "avg_sales_lag6                 334683 non-null float64\n",
      "avg_purchases_lag6             334696 non-null float64\n",
      "active_months_lag6             334696 non-null int64\n",
      "avg_sales_lag12                334683 non-null float64\n",
      "avg_purchases_lag12            334696 non-null float64\n",
      "active_months_lag12            334696 non-null int64\n",
      "category_4                     334696 non-null object\n",
      "city_id                        334696 non-null int64\n",
      "state_id                       334696 non-null int64\n",
      "category_2                     322809 non-null float64\n",
      "dtypes: float64(9), int64(8), object(5)\n",
      "memory usage: 56.2+ MB\n"
     ]
    }
   ],
   "source": [
    "merchants.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "merchant_id                    334633\n",
       "merchant_group_id              109391\n",
       "merchant_category_id              324\n",
       "subsector_id                       41\n",
       "numerical_1                       954\n",
       "numerical_2                       947\n",
       "category_1                          2\n",
       "most_recent_sales_range             5\n",
       "most_recent_purchases_range         5\n",
       "avg_sales_lag3                   3372\n",
       "avg_purchases_lag3             100003\n",
       "active_months_lag3                  3\n",
       "avg_sales_lag6                   4507\n",
       "avg_purchases_lag6             135202\n",
       "active_months_lag6                  6\n",
       "avg_sales_lag12                  5009\n",
       "avg_purchases_lag12            172917\n",
       "active_months_lag12                12\n",
       "category_4                          2\n",
       "city_id                           271\n",
       "state_id                           25\n",
       "category_2                          5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchants.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "merchants['avg_sales_lag3'].fillna(merchants['avg_sales_lag3'].median(), inplace=True)\n",
    "merchants['avg_sales_lag6'].fillna(merchants['avg_sales_lag3'].median(), inplace=True)\n",
    "merchants['avg_sales_lag12'].fillna(merchants['avg_sales_lag3'].median(), inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "merch_cat_cols = ['city_id', 'state_id', 'category_2', 'category_1', 'merchant_group_id', 'subsector_id', \n",
    "                 'category_4', 'most_recent_sales_range', 'most_recent_sales_range', 'most_recent_purchases_range',\n",
    "                 'active_months_lag3', 'active_months_lag6', 'active_months_lag12']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in merch_cat_cols: merchants[c] = merchants[c].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 334696 entries, 0 to 334695\n",
      "Data columns (total 22 columns):\n",
      "merchant_id                    334696 non-null object\n",
      "merchant_group_id              334696 non-null category\n",
      "merchant_category_id           334696 non-null int64\n",
      "subsector_id                   334696 non-null category\n",
      "numerical_1                    334696 non-null float64\n",
      "numerical_2                    334696 non-null float64\n",
      "category_1                     334696 non-null category\n",
      "most_recent_sales_range        334696 non-null category\n",
      "most_recent_purchases_range    334696 non-null category\n",
      "avg_sales_lag3                 334696 non-null float64\n",
      "avg_purchases_lag3             334696 non-null float64\n",
      "active_months_lag3             334696 non-null category\n",
      "avg_sales_lag6                 334696 non-null float64\n",
      "avg_purchases_lag6             334696 non-null float64\n",
      "active_months_lag6             334696 non-null category\n",
      "avg_sales_lag12                334696 non-null float64\n",
      "avg_purchases_lag12            334696 non-null float64\n",
      "active_months_lag12            334696 non-null category\n",
      "category_4                     334696 non-null category\n",
      "city_id                        334696 non-null category\n",
      "state_id                       334696 non-null category\n",
      "category_2                     322809 non-null category\n",
      "dtypes: category(12), float64(8), int64(1), object(1)\n",
      "memory usage: 36.5+ MB\n"
     ]
    }
   ],
   "source": [
    "merchants.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in cat_cols:\n",
    "    uniq_vals = hist_trans[c].unique()\n",
    "    uniq_vals.sort()\n",
    "    hist_trans[c]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "merchant_id                    0.000000\n",
       "merchant_group_id              0.000000\n",
       "merchant_category_id           0.000000\n",
       "subsector_id                   0.000000\n",
       "numerical_1                    0.000000\n",
       "numerical_2                    0.000000\n",
       "category_1                     0.000000\n",
       "most_recent_sales_range        0.000000\n",
       "most_recent_purchases_range    0.000000\n",
       "avg_sales_lag3                 0.000000\n",
       "avg_purchases_lag3             0.000000\n",
       "active_months_lag3             0.000000\n",
       "avg_sales_lag6                 0.000000\n",
       "avg_purchases_lag6             0.000000\n",
       "active_months_lag6             0.000000\n",
       "avg_sales_lag12                0.000000\n",
       "avg_purchases_lag12            0.000000\n",
       "active_months_lag12            0.000000\n",
       "category_4                     0.000000\n",
       "city_id                        0.000000\n",
       "state_id                       0.000000\n",
       "category_2                     0.035516\n",
       "dtype: float64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchants.isnull().mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "authorized_flag                  0.000000\n",
       "card_id                          0.000000\n",
       "installments                     0.006126\n",
       "category_3                       0.000000\n",
       "merchant_category_id             0.000000\n",
       "merchant_id                      0.000000\n",
       "month_lag                        0.000000\n",
       "purchase_amount                  0.000000\n",
       "purchase_date                    0.000000\n",
       "purchase_Year                    0.000000\n",
       "purchase_Month                   0.000000\n",
       "purchase_Week                    0.000000\n",
       "purchase_Day                     0.000000\n",
       "purchase_Dayofweek               0.000000\n",
       "purchase_Dayofyear               0.000000\n",
       "purchase_Is_month_end            0.000000\n",
       "purchase_Is_month_start          0.000000\n",
       "purchase_Is_quarter_end          0.000000\n",
       "purchase_Is_quarter_start        0.000000\n",
       "purchase_Is_year_end             0.000000\n",
       "purchase_Is_year_start           0.000000\n",
       "purchase_Hour                    0.000000\n",
       "purchase_Minute                  0.000000\n",
       "purchase_Second                  0.000000\n",
       "purchase_Elapsed                 0.000000\n",
       "purchased_on_weekend             0.000000\n",
       "purchased_on_weekday             0.000000\n",
       "month_diff                       0.000000\n",
       "purchase_date_successive_diff    0.000000\n",
       "price                            0.006127\n",
       "Christmas_Day_2017               0.000000\n",
       "Mothers_Day_2017                 0.000000\n",
       "fathers_day_2017                 0.000000\n",
       "Children_day_2017                0.000000\n",
       "Valentine_Day_2017               0.000000\n",
       "Black_Friday_2017                0.000000\n",
       "Mothers_Day_2018                 0.000000\n",
       "duration                         0.000000\n",
       "amount_month_ratio               0.000000\n",
       "merchant_address_id              0.000000\n",
       "numerical_range                  0.000000\n",
       "merchant_rating                  0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.isnull().mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((334696, 22), (1963031, 42), (29112368, 42))"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchants.shape, new_hist_trans.shape, hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans_merged = pd.merge(new_hist_trans, merchants, 'inner', on='merchant_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_merged = pd.merge(hist_trans, merchants, 'inner', on='merchant_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1963031, 42), (29112368, 42))"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans.shape, hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>purchase_Year</th>\n",
       "      <th>...</th>\n",
       "      <th>fathers_day_2017</th>\n",
       "      <th>Children_day_2017</th>\n",
       "      <th>Valentine_Day_2017</th>\n",
       "      <th>Black_Friday_2017</th>\n",
       "      <th>Mothers_Day_2018</th>\n",
       "      <th>duration</th>\n",
       "      <th>amount_month_ratio</th>\n",
       "      <th>merchant_address_id</th>\n",
       "      <th>numerical_range</th>\n",
       "      <th>merchant_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1963026</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_251e1fe58f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>307</td>\n",
       "      <td>M_ID_a0eef20623</td>\n",
       "      <td>2</td>\n",
       "      <td>105.00</td>\n",
       "      <td>2018-04-30 23:59:07</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1155.00</td>\n",
       "      <td>9.545455</td>\n",
       "      <td>M_ID_a0eef20623_307_19_0_5_5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963027</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_246d26c2bd</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>818</td>\n",
       "      <td>M_ID_5bf40b6a6f</td>\n",
       "      <td>2</td>\n",
       "      <td>1029.00</td>\n",
       "      <td>2018-04-30 23:59:15</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11319.00</td>\n",
       "      <td>93.545455</td>\n",
       "      <td>M_ID_5bf40b6a6f_818_12_69_9_1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963028</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_0d0b14e74d</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_08b7605111</td>\n",
       "      <td>2</td>\n",
       "      <td>40.00</td>\n",
       "      <td>2018-04-30 23:59:17</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>440.00</td>\n",
       "      <td>3.636364</td>\n",
       "      <td>M_ID_08b7605111_842_37_8_12_1</td>\n",
       "      <td>390.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963029</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_5a05b5e813</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>80</td>\n",
       "      <td>M_ID_2bc04b5299</td>\n",
       "      <td>2</td>\n",
       "      <td>43.33</td>\n",
       "      <td>2018-04-30 23:59:38</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>476.63</td>\n",
       "      <td>3.939091</td>\n",
       "      <td>M_ID_2bc04b5299_80_37_0_9_1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963030</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_77fd0634b1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_066936e35c</td>\n",
       "      <td>2</td>\n",
       "      <td>41.79</td>\n",
       "      <td>2018-04-30 23:59:59</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>459.69</td>\n",
       "      <td>3.799091</td>\n",
       "      <td>M_ID_066936e35c_842_37_136_4_4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        authorized_flag          card_id  installments category_3  \\\n",
       "1963026               1  C_ID_251e1fe58f           0.0          0   \n",
       "1963027               1  C_ID_246d26c2bd           1.0          1   \n",
       "1963028               1  C_ID_0d0b14e74d           0.0          0   \n",
       "1963029               1  C_ID_5a05b5e813           1.0          1   \n",
       "1963030               1  C_ID_77fd0634b1           0.0          0   \n",
       "\n",
       "        merchant_category_id      merchant_id  month_lag  purchase_amount  \\\n",
       "1963026                  307  M_ID_a0eef20623          2           105.00   \n",
       "1963027                  818  M_ID_5bf40b6a6f          2          1029.00   \n",
       "1963028                  842  M_ID_08b7605111          2            40.00   \n",
       "1963029                   80  M_ID_2bc04b5299          2            43.33   \n",
       "1963030                  842  M_ID_066936e35c          2            41.79   \n",
       "\n",
       "              purchase_date purchase_Year  ... fathers_day_2017  \\\n",
       "1963026 2018-04-30 23:59:07          2018  ...                0   \n",
       "1963027 2018-04-30 23:59:15          2018  ...                0   \n",
       "1963028 2018-04-30 23:59:17          2018  ...                0   \n",
       "1963029 2018-04-30 23:59:38          2018  ...                0   \n",
       "1963030 2018-04-30 23:59:59          2018  ...                0   \n",
       "\n",
       "        Children_day_2017 Valentine_Day_2017 Black_Friday_2017  \\\n",
       "1963026                 0                  0                 0   \n",
       "1963027                 0                  0                 0   \n",
       "1963028                 0                  0                 0   \n",
       "1963029                 0                  0                 0   \n",
       "1963030                 0                  0                 0   \n",
       "\n",
       "        Mothers_Day_2018  duration  amount_month_ratio  \\\n",
       "1963026              NaN   1155.00            9.545455   \n",
       "1963027              NaN  11319.00           93.545455   \n",
       "1963028              NaN    440.00            3.636364   \n",
       "1963029              NaN    476.63            3.939091   \n",
       "1963030              NaN    459.69            3.799091   \n",
       "\n",
       "                    merchant_address_id  numerical_range  merchant_rating  \n",
       "1963026    M_ID_a0eef20623_307_19_0_5_5              3.0               12  \n",
       "1963027   M_ID_5bf40b6a6f_818_12_69_9_1              4.0               13  \n",
       "1963028   M_ID_08b7605111_842_37_8_12_1            390.0               13  \n",
       "1963029     M_ID_2bc04b5299_80_37_0_9_1              1.0               12  \n",
       "1963030  M_ID_066936e35c_842_37_136_4_4              0.0               12  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id_x</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>purchase_Year</th>\n",
       "      <th>...</th>\n",
       "      <th>avg_sales_lag6</th>\n",
       "      <th>avg_purchases_lag6</th>\n",
       "      <th>active_months_lag6</th>\n",
       "      <th>avg_sales_lag12</th>\n",
       "      <th>avg_purchases_lag12</th>\n",
       "      <th>active_months_lag12</th>\n",
       "      <th>category_4</th>\n",
       "      <th>city_id</th>\n",
       "      <th>state_id</th>\n",
       "      <th>category_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021106</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_30173ab6df</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>178</td>\n",
       "      <td>M_ID_1da2c0d633</td>\n",
       "      <td>2</td>\n",
       "      <td>56.0</td>\n",
       "      <td>2018-04-30 23:07:09</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>7.08</td>\n",
       "      <td>5.277778</td>\n",
       "      <td>6</td>\n",
       "      <td>7.43</td>\n",
       "      <td>5.750000</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>-1</td>\n",
       "      <td>21</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021107</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_f09c06c3fe</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_1289688081</td>\n",
       "      <td>2</td>\n",
       "      <td>103.0</td>\n",
       "      <td>2018-04-30 23:10:12</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>1.02</td>\n",
       "      <td>0.961071</td>\n",
       "      <td>6</td>\n",
       "      <td>0.84</td>\n",
       "      <td>0.813869</td>\n",
       "      <td>8</td>\n",
       "      <td>N</td>\n",
       "      <td>-1</td>\n",
       "      <td>20</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021108</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_3729d6f002</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>498</td>\n",
       "      <td>M_ID_852a7b45ca</td>\n",
       "      <td>2</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2018-04-30 23:24:40</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>1.71</td>\n",
       "      <td>1.055556</td>\n",
       "      <td>6</td>\n",
       "      <td>2.31</td>\n",
       "      <td>1.194444</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>115</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021109</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_f4831bf61f</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>683</td>\n",
       "      <td>M_ID_b4aa40ab2f</td>\n",
       "      <td>2</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2018-04-30 23:28:46</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>1.03</td>\n",
       "      <td>1.023148</td>\n",
       "      <td>6</td>\n",
       "      <td>1.75</td>\n",
       "      <td>1.527778</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td>-1</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021110</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_63ae7a7ed7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>68</td>\n",
       "      <td>M_ID_bd8d33c133</td>\n",
       "      <td>2</td>\n",
       "      <td>55.0</td>\n",
       "      <td>2018-04-30 23:32:55</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0.32</td>\n",
       "      <td>0.332589</td>\n",
       "      <td>4</td>\n",
       "      <td>0.32</td>\n",
       "      <td>0.332589</td>\n",
       "      <td>4</td>\n",
       "      <td>Y</td>\n",
       "      <td>87</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 63 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        authorized_flag          card_id  installments category_3  \\\n",
       "2021106               1  C_ID_30173ab6df           0.0          0   \n",
       "2021107               1  C_ID_f09c06c3fe           0.0          0   \n",
       "2021108               1  C_ID_3729d6f002           1.0          1   \n",
       "2021109               1  C_ID_f4831bf61f           1.0          1   \n",
       "2021110               1  C_ID_63ae7a7ed7           0.0          0   \n",
       "\n",
       "        merchant_category_id_x      merchant_id  month_lag  purchase_amount  \\\n",
       "2021106                    178  M_ID_1da2c0d633          2             56.0   \n",
       "2021107                    842  M_ID_1289688081          2            103.0   \n",
       "2021108                    498  M_ID_852a7b45ca          2             20.0   \n",
       "2021109                    683  M_ID_b4aa40ab2f          2             41.0   \n",
       "2021110                     68  M_ID_bd8d33c133          2             55.0   \n",
       "\n",
       "              purchase_date purchase_Year  ... avg_sales_lag6  \\\n",
       "2021106 2018-04-30 23:07:09          2018  ...           7.08   \n",
       "2021107 2018-04-30 23:10:12          2018  ...           1.02   \n",
       "2021108 2018-04-30 23:24:40          2018  ...           1.71   \n",
       "2021109 2018-04-30 23:28:46          2018  ...           1.03   \n",
       "2021110 2018-04-30 23:32:55          2018  ...           0.32   \n",
       "\n",
       "        avg_purchases_lag6 active_months_lag6 avg_sales_lag12  \\\n",
       "2021106           5.277778                  6            7.43   \n",
       "2021107           0.961071                  6            0.84   \n",
       "2021108           1.055556                  6            2.31   \n",
       "2021109           1.023148                  6            1.75   \n",
       "2021110           0.332589                  4            0.32   \n",
       "\n",
       "        avg_purchases_lag12  active_months_lag12  category_4  city_id  \\\n",
       "2021106            5.750000                   12           N       -1   \n",
       "2021107            0.813869                    8           N       -1   \n",
       "2021108            1.194444                   12           N      115   \n",
       "2021109            1.527778                   12           N       -1   \n",
       "2021110            0.332589                    4           Y       87   \n",
       "\n",
       "         state_id  category_2  \n",
       "2021106        21         5.0  \n",
       "2021107        20         5.0  \n",
       "2021108         9         1.0  \n",
       "2021109         4         4.0  \n",
       "2021110        11         3.0  \n",
       "\n",
       "[5 rows x 63 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_merged.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(334696, 22)"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchants.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'new_hist_trans_merged' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-13-c110c26cebc8>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mnew_hist_trans_merged\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhist_trans_merged\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'new_hist_trans_merged' is not defined"
     ]
    }
   ],
   "source": [
    "new_hist_trans_merged.shape, hist_trans_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2021111 entries, 0 to 2021110\n",
      "Data columns (total 63 columns):\n",
      "authorized_flag                  category\n",
      "card_id                          object\n",
      "installments                     float64\n",
      "category_3                       category\n",
      "merchant_category_id_x           category\n",
      "merchant_id                      object\n",
      "month_lag                        int64\n",
      "purchase_amount                  float64\n",
      "purchase_date                    datetime64[ns]\n",
      "purchase_Year                    category\n",
      "purchase_Month                   category\n",
      "purchase_Week                    category\n",
      "purchase_Day                     category\n",
      "purchase_Dayofweek               category\n",
      "purchase_Dayofyear               category\n",
      "purchase_Is_month_end            bool\n",
      "purchase_Is_month_start          bool\n",
      "purchase_Is_quarter_end          bool\n",
      "purchase_Is_quarter_start        bool\n",
      "purchase_Is_year_end             bool\n",
      "purchase_Is_year_start           bool\n",
      "purchase_Hour                    category\n",
      "purchase_Minute                  category\n",
      "purchase_Second                  category\n",
      "purchase_Elapsed                 int64\n",
      "purchased_on_weekend             category\n",
      "purchased_on_weekday             category\n",
      "month_diff                       int64\n",
      "purchase_date_successive_diff    float64\n",
      "price                            float64\n",
      "Christmas_Day_2017               category\n",
      "Mothers_Day_2017                 category\n",
      "fathers_day_2017                 category\n",
      "Children_day_2017                category\n",
      "Valentine_Day_2017               category\n",
      "Black_Friday_2017                category\n",
      "Mothers_Day_2018                 category\n",
      "duration                         float64\n",
      "amount_month_ratio               float64\n",
      "merchant_address_id              object\n",
      "numerical_range                  float64\n",
      "merchant_rating                  category\n",
      "merchant_group_id                category\n",
      "merchant_category_id_y           int64\n",
      "subsector_id                     category\n",
      "numerical_1                      float64\n",
      "numerical_2                      float64\n",
      "category_1                       category\n",
      "most_recent_sales_range          category\n",
      "most_recent_purchases_range      category\n",
      "avg_sales_lag3                   float64\n",
      "avg_purchases_lag3               float64\n",
      "active_months_lag3               category\n",
      "avg_sales_lag6                   float64\n",
      "avg_purchases_lag6               float64\n",
      "active_months_lag6               category\n",
      "avg_sales_lag12                  float64\n",
      "avg_purchases_lag12              float64\n",
      "active_months_lag12              category\n",
      "category_4                       category\n",
      "city_id                          category\n",
      "state_id                         category\n",
      "category_2                       category\n",
      "dtypes: bool(6), category(34), datetime64[ns](1), float64(15), int64(4), object(3)\n",
      "memory usage: 464.7+ MB\n"
     ]
    }
   ],
   "source": [
    "new_hist_trans_merged.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_merged.to_feather('hist_trans_merged')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans_merged.to_feather('new_hist_trans_merged')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 30472275 entries, 0 to 30472274\n",
      "Data columns (total 63 columns):\n",
      "authorized_flag                  category\n",
      "card_id                          object\n",
      "installments                     float64\n",
      "category_3                       category\n",
      "merchant_category_id_x           category\n",
      "merchant_id                      object\n",
      "month_lag                        int64\n",
      "purchase_amount                  float64\n",
      "purchase_date                    datetime64[ns]\n",
      "purchase_Year                    category\n",
      "purchase_Month                   category\n",
      "purchase_Week                    category\n",
      "purchase_Day                     category\n",
      "purchase_Dayofweek               category\n",
      "purchase_Dayofyear               category\n",
      "purchase_Is_month_end            bool\n",
      "purchase_Is_month_start          bool\n",
      "purchase_Is_quarter_end          bool\n",
      "purchase_Is_quarter_start        bool\n",
      "purchase_Is_year_end             bool\n",
      "purchase_Is_year_start           bool\n",
      "purchase_Hour                    category\n",
      "purchase_Minute                  category\n",
      "purchase_Second                  category\n",
      "purchase_Elapsed                 int64\n",
      "purchased_on_weekend             category\n",
      "purchased_on_weekday             category\n",
      "month_diff                       int64\n",
      "purchase_date_successive_diff    float64\n",
      "price                            float64\n",
      "Christmas_Day_2017               category\n",
      "Mothers_Day_2017                 category\n",
      "fathers_day_2017                 category\n",
      "Children_day_2017                category\n",
      "Valentine_Day_2017               category\n",
      "Black_Friday_2017                category\n",
      "Mothers_Day_2018                 category\n",
      "duration                         float64\n",
      "amount_month_ratio               float64\n",
      "merchant_address_id              object\n",
      "numerical_range                  float64\n",
      "merchant_rating                  category\n",
      "merchant_group_id                category\n",
      "merchant_category_id_y           int64\n",
      "subsector_id                     category\n",
      "numerical_1                      float64\n",
      "numerical_2                      float64\n",
      "category_1                       category\n",
      "most_recent_sales_range          category\n",
      "most_recent_purchases_range      category\n",
      "avg_sales_lag3                   float64\n",
      "avg_purchases_lag3               float64\n",
      "active_months_lag3               category\n",
      "avg_sales_lag6                   float64\n",
      "avg_purchases_lag6               float64\n",
      "active_months_lag6               category\n",
      "avg_sales_lag12                  float64\n",
      "avg_purchases_lag12              float64\n",
      "active_months_lag12              category\n",
      "category_4                       category\n",
      "city_id                          category\n",
      "state_id                         category\n",
      "category_2                       category\n",
      "dtypes: bool(6), category(34), datetime64[ns](1), float64(15), int64(4), object(3)\n",
      "memory usage: 6.8+ GB\n"
     ]
    }
   ],
   "source": [
    "hist_trans_merged.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "authorized_flag                        0\n",
       "card_id                                0\n",
       "installments                       58169\n",
       "category_3                             0\n",
       "merchant_category_id_x                 6\n",
       "merchant_id                            0\n",
       "month_lag                              0\n",
       "purchase_amount                        0\n",
       "purchase_date                          0\n",
       "purchase_Year                          0\n",
       "purchase_Month                         0\n",
       "purchase_Week                          0\n",
       "purchase_Day                           0\n",
       "purchase_Dayofweek                     0\n",
       "purchase_Dayofyear                     0\n",
       "purchase_Is_month_end                  0\n",
       "purchase_Is_month_start                0\n",
       "purchase_Is_quarter_end                0\n",
       "purchase_Is_quarter_start              0\n",
       "purchase_Is_year_end                   0\n",
       "purchase_Is_year_start                 0\n",
       "purchase_Hour                          0\n",
       "purchase_Minute                        0\n",
       "purchase_Second                        0\n",
       "purchase_Elapsed                       0\n",
       "purchased_on_weekend                   0\n",
       "purchased_on_weekday                   0\n",
       "month_diff                             0\n",
       "purchase_date_successive_diff          0\n",
       "price                              58169\n",
       "                                  ...   \n",
       "Children_day_2017                      0\n",
       "Valentine_Day_2017                     0\n",
       "Black_Friday_2017                      0\n",
       "Mothers_Day_2018                 1506773\n",
       "duration                               0\n",
       "amount_month_ratio                     0\n",
       "merchant_address_id                    0\n",
       "numerical_range                        0\n",
       "merchant_rating                        0\n",
       "merchant_group_id                      0\n",
       "merchant_category_id_y                 0\n",
       "subsector_id                           0\n",
       "numerical_1                            0\n",
       "numerical_2                            0\n",
       "category_1                             0\n",
       "most_recent_sales_range                0\n",
       "most_recent_purchases_range            0\n",
       "avg_sales_lag3                         0\n",
       "avg_purchases_lag3                     0\n",
       "active_months_lag3                     0\n",
       "avg_sales_lag6                         0\n",
       "avg_purchases_lag6                     0\n",
       "active_months_lag6                     0\n",
       "avg_sales_lag12                        0\n",
       "avg_purchases_lag12                    0\n",
       "active_months_lag12                    0\n",
       "category_4                             0\n",
       "city_id                                0\n",
       "state_id                               0\n",
       "category_2                        280961\n",
       "Length: 63, dtype: int64"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_merged.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = pd.concat([hist_trans, new_hist_trans])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "l = join_df(new_hist_trans, merchants, 'merchant_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2021111, 63)"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1963031, 42)"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2021111, 1963031)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(l), len(new_hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gc\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(31075399, 42)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1963031, 42), (29112368, 42))"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans.shape, hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31075399"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans.shape[0] + hist_trans.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>purchase_Year</th>\n",
       "      <th>...</th>\n",
       "      <th>fathers_day_2017</th>\n",
       "      <th>Children_day_2017</th>\n",
       "      <th>Valentine_Day_2017</th>\n",
       "      <th>Black_Friday_2017</th>\n",
       "      <th>Mothers_Day_2018</th>\n",
       "      <th>duration</th>\n",
       "      <th>amount_month_ratio</th>\n",
       "      <th>merchant_address_id</th>\n",
       "      <th>numerical_range</th>\n",
       "      <th>merchant_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1963026</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_251e1fe58f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>307</td>\n",
       "      <td>M_ID_a0eef20623</td>\n",
       "      <td>2</td>\n",
       "      <td>105.00</td>\n",
       "      <td>2018-04-30 23:59:07</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1155.00</td>\n",
       "      <td>9.545455</td>\n",
       "      <td>M_ID_a0eef20623_307_19_0_5_5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963027</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_246d26c2bd</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>818</td>\n",
       "      <td>M_ID_5bf40b6a6f</td>\n",
       "      <td>2</td>\n",
       "      <td>1029.00</td>\n",
       "      <td>2018-04-30 23:59:15</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11319.00</td>\n",
       "      <td>93.545455</td>\n",
       "      <td>M_ID_5bf40b6a6f_818_12_69_9_1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963028</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_0d0b14e74d</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_08b7605111</td>\n",
       "      <td>2</td>\n",
       "      <td>40.00</td>\n",
       "      <td>2018-04-30 23:59:17</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>440.00</td>\n",
       "      <td>3.636364</td>\n",
       "      <td>M_ID_08b7605111_842_37_8_12_1</td>\n",
       "      <td>390.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963029</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_5a05b5e813</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>80</td>\n",
       "      <td>M_ID_2bc04b5299</td>\n",
       "      <td>2</td>\n",
       "      <td>43.33</td>\n",
       "      <td>2018-04-30 23:59:38</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>476.63</td>\n",
       "      <td>3.939091</td>\n",
       "      <td>M_ID_2bc04b5299_80_37_0_9_1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963030</th>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_77fd0634b1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_066936e35c</td>\n",
       "      <td>2</td>\n",
       "      <td>41.79</td>\n",
       "      <td>2018-04-30 23:59:59</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>459.69</td>\n",
       "      <td>3.799091</td>\n",
       "      <td>M_ID_066936e35c_842_37_136_4_4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         authorized_flag          card_id  installments  category_3  \\\n",
       "1963026                1  C_ID_251e1fe58f           0.0           0   \n",
       "1963027                1  C_ID_246d26c2bd           1.0           1   \n",
       "1963028                1  C_ID_0d0b14e74d           0.0           0   \n",
       "1963029                1  C_ID_5a05b5e813           1.0           1   \n",
       "1963030                1  C_ID_77fd0634b1           0.0           0   \n",
       "\n",
       "        merchant_category_id      merchant_id  month_lag  purchase_amount  \\\n",
       "1963026                  307  M_ID_a0eef20623          2           105.00   \n",
       "1963027                  818  M_ID_5bf40b6a6f          2          1029.00   \n",
       "1963028                  842  M_ID_08b7605111          2            40.00   \n",
       "1963029                   80  M_ID_2bc04b5299          2            43.33   \n",
       "1963030                  842  M_ID_066936e35c          2            41.79   \n",
       "\n",
       "              purchase_date  purchase_Year  ...  fathers_day_2017  \\\n",
       "1963026 2018-04-30 23:59:07           2018  ...                 0   \n",
       "1963027 2018-04-30 23:59:15           2018  ...                 0   \n",
       "1963028 2018-04-30 23:59:17           2018  ...                 0   \n",
       "1963029 2018-04-30 23:59:38           2018  ...                 0   \n",
       "1963030 2018-04-30 23:59:59           2018  ...                 0   \n",
       "\n",
       "         Children_day_2017  Valentine_Day_2017  Black_Friday_2017  \\\n",
       "1963026                  0                   0                  0   \n",
       "1963027                  0                   0                  0   \n",
       "1963028                  0                   0                  0   \n",
       "1963029                  0                   0                  0   \n",
       "1963030                  0                   0                  0   \n",
       "\n",
       "         Mothers_Day_2018  duration  amount_month_ratio  \\\n",
       "1963026               NaN   1155.00            9.545455   \n",
       "1963027               NaN  11319.00           93.545455   \n",
       "1963028               NaN    440.00            3.636364   \n",
       "1963029               NaN    476.63            3.939091   \n",
       "1963030               NaN    459.69            3.799091   \n",
       "\n",
       "                    merchant_address_id  numerical_range  merchant_rating  \n",
       "1963026    M_ID_a0eef20623_307_19_0_5_5              3.0               12  \n",
       "1963027   M_ID_5bf40b6a6f_818_12_69_9_1              4.0               13  \n",
       "1963028   M_ID_08b7605111_842_37_8_12_1            390.0               13  \n",
       "1963029     M_ID_2bc04b5299_80_37_0_9_1              1.0               12  \n",
       "1963030  M_ID_066936e35c_842_37_136_4_4              0.0               12  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index                                     int64\n",
       "authorized_flag                           int64\n",
       "card_id                                  object\n",
       "installments                            float64\n",
       "category_3                                int64\n",
       "merchant_category_id                     object\n",
       "merchant_id                              object\n",
       "month_lag                                 int64\n",
       "purchase_amount                         float64\n",
       "purchase_date                    datetime64[ns]\n",
       "purchase_Year                             int64\n",
       "purchase_Month                            int64\n",
       "purchase_Week                             int64\n",
       "purchase_Day                              int64\n",
       "purchase_Dayofweek                        int64\n",
       "purchase_Dayofyear                        int64\n",
       "purchase_Is_month_end                      bool\n",
       "purchase_Is_month_start                    bool\n",
       "purchase_Is_quarter_end                    bool\n",
       "purchase_Is_quarter_start                  bool\n",
       "purchase_Is_year_end                       bool\n",
       "purchase_Is_year_start                     bool\n",
       "purchase_Hour                             int64\n",
       "purchase_Minute                           int64\n",
       "purchase_Second                           int64\n",
       "purchase_Elapsed                          int64\n",
       "purchased_on_weekend                      int64\n",
       "purchased_on_weekday                      int64\n",
       "month_diff                                int64\n",
       "purchase_date_successive_diff           float64\n",
       "price                                   float64\n",
       "Christmas_Day_2017                        int64\n",
       "Mothers_Day_2017                          int64\n",
       "fathers_day_2017                          int64\n",
       "Children_day_2017                         int64\n",
       "Valentine_Day_2017                        int64\n",
       "Black_Friday_2017                         int64\n",
       "Mothers_Day_2018                         object\n",
       "duration                                float64\n",
       "amount_month_ratio                      float64\n",
       "merchant_address_id                      object\n",
       "numerical_range                         float64\n",
       "merchant_rating                           int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "apply_cats(df_all, hist_trans)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index                                     int64\n",
       "authorized_flag                        category\n",
       "card_id                                  object\n",
       "installments                            float64\n",
       "category_3                             category\n",
       "merchant_category_id                   category\n",
       "merchant_id                              object\n",
       "month_lag                                 int64\n",
       "purchase_amount                         float64\n",
       "purchase_date                    datetime64[ns]\n",
       "purchase_Year                          category\n",
       "purchase_Month                         category\n",
       "purchase_Week                          category\n",
       "purchase_Day                           category\n",
       "purchase_Dayofweek                     category\n",
       "purchase_Dayofyear                     category\n",
       "purchase_Is_month_end                      bool\n",
       "purchase_Is_month_start                    bool\n",
       "purchase_Is_quarter_end                    bool\n",
       "purchase_Is_quarter_start                  bool\n",
       "purchase_Is_year_end                       bool\n",
       "purchase_Is_year_start                     bool\n",
       "purchase_Hour                          category\n",
       "purchase_Minute                        category\n",
       "purchase_Second                        category\n",
       "purchase_Elapsed                          int64\n",
       "purchased_on_weekend                   category\n",
       "purchased_on_weekday                   category\n",
       "month_diff                                int64\n",
       "purchase_date_successive_diff           float64\n",
       "price                                   float64\n",
       "Christmas_Day_2017                     category\n",
       "Mothers_Day_2017                       category\n",
       "fathers_day_2017                       category\n",
       "Children_day_2017                      category\n",
       "Valentine_Day_2017                     category\n",
       "Black_Friday_2017                      category\n",
       "Mothers_Day_2018                       category\n",
       "duration                                float64\n",
       "amount_month_ratio                      float64\n",
       "merchant_address_id                      object\n",
       "numerical_range                         float64\n",
       "merchant_rating                        category\n",
       "dtype: object"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merch_cat_cols = `"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 31075399 entries, 0 to 31075398\n",
      "Data columns (total 43 columns):\n",
      "index                            int64\n",
      "authorized_flag                  category\n",
      "card_id                          object\n",
      "installments                     float64\n",
      "category_3                       category\n",
      "merchant_category_id             category\n",
      "merchant_id                      object\n",
      "month_lag                        int64\n",
      "purchase_amount                  float64\n",
      "purchase_date                    datetime64[ns]\n",
      "purchase_Year                    category\n",
      "purchase_Month                   category\n",
      "purchase_Week                    category\n",
      "purchase_Day                     category\n",
      "purchase_Dayofweek               category\n",
      "purchase_Dayofyear               category\n",
      "purchase_Is_month_end            bool\n",
      "purchase_Is_month_start          bool\n",
      "purchase_Is_quarter_end          bool\n",
      "purchase_Is_quarter_start        bool\n",
      "purchase_Is_year_end             bool\n",
      "purchase_Is_year_start           bool\n",
      "purchase_Hour                    category\n",
      "purchase_Minute                  category\n",
      "purchase_Second                  category\n",
      "purchase_Elapsed                 int64\n",
      "purchased_on_weekend             category\n",
      "purchased_on_weekday             category\n",
      "month_diff                       int64\n",
      "purchase_date_successive_diff    float64\n",
      "price                            float64\n",
      "Christmas_Day_2017               category\n",
      "Mothers_Day_2017                 category\n",
      "fathers_day_2017                 category\n",
      "Children_day_2017                category\n",
      "Valentine_Day_2017               category\n",
      "Black_Friday_2017                category\n",
      "Mothers_Day_2018                 category\n",
      "duration                         float64\n",
      "amount_month_ratio               float64\n",
      "merchant_address_id              object\n",
      "numerical_range                  float64\n",
      "merchant_rating                  category\n",
      "dtypes: bool(6), category(22), datetime64[ns](1), float64(7), int64(4), object(3)\n",
      "memory usage: 4.3+ GB\n"
     ]
    }
   ],
   "source": [
    "df_all.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>index</th>\n",
       "      <th>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>...</th>\n",
       "      <th>fathers_day_2017</th>\n",
       "      <th>Children_day_2017</th>\n",
       "      <th>Valentine_Day_2017</th>\n",
       "      <th>Black_Friday_2017</th>\n",
       "      <th>Mothers_Day_2018</th>\n",
       "      <th>duration</th>\n",
       "      <th>amount_month_ratio</th>\n",
       "      <th>merchant_address_id</th>\n",
       "      <th>numerical_range</th>\n",
       "      <th>merchant_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_da2090f28e</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>623</td>\n",
       "      <td>M_ID_f001319a61</td>\n",
       "      <td>-11</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2017-01-01 00:00:08</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>560.0</td>\n",
       "      <td>2.857143</td>\n",
       "      <td>M_ID_f001319a61_623_4_0_9_1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_efced389a0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_18038b5ae7</td>\n",
       "      <td>-12</td>\n",
       "      <td>120.0</td>\n",
       "      <td>2017-01-01 00:00:59</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1560.0</td>\n",
       "      <td>9.230769</td>\n",
       "      <td>M_ID_18038b5ae7_842_37_76_2_3</td>\n",
       "      <td>189.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_83561fe74a</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>661</td>\n",
       "      <td>M_ID_52d3026407</td>\n",
       "      <td>-13</td>\n",
       "      <td>124.9</td>\n",
       "      <td>2017-01-01 00:01:41</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1498.8</td>\n",
       "      <td>10.408333</td>\n",
       "      <td>M_ID_52d3026407_661_8_0_0_0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_479fd6392a</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>839</td>\n",
       "      <td>M_ID_e5374dabc0</td>\n",
       "      <td>-1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-01-01 00:02:03</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>M_ID_e5374dabc0_839_29_0_0_0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_1cf6056088</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>278</td>\n",
       "      <td>M_ID_2cf6dc1f6f</td>\n",
       "      <td>-4</td>\n",
       "      <td>500.0</td>\n",
       "      <td>2017-01-01 00:02:12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10500.0</td>\n",
       "      <td>23.809524</td>\n",
       "      <td>M_ID_2cf6dc1f6f_278_37_69_9_1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index authorized_flag          card_id  installments category_3  \\\n",
       "0      0               1  C_ID_da2090f28e           0.0          0   \n",
       "1      1               1  C_ID_efced389a0           1.0          1   \n",
       "2      2               1  C_ID_83561fe74a           1.0          1   \n",
       "3      3               1  C_ID_479fd6392a           1.0          1   \n",
       "4      4               1  C_ID_1cf6056088           0.0          0   \n",
       "\n",
       "  merchant_category_id      merchant_id  month_lag  purchase_amount  \\\n",
       "0                  623  M_ID_f001319a61        -11             40.0   \n",
       "1                  842  M_ID_18038b5ae7        -12            120.0   \n",
       "2                  661  M_ID_52d3026407        -13            124.9   \n",
       "3                  839  M_ID_e5374dabc0         -1              6.0   \n",
       "4                  278  M_ID_2cf6dc1f6f         -4            500.0   \n",
       "\n",
       "        purchase_date  ... fathers_day_2017 Children_day_2017  \\\n",
       "0 2017-01-01 00:00:08  ...                0                 0   \n",
       "1 2017-01-01 00:00:59  ...                0                 0   \n",
       "2 2017-01-01 00:01:41  ...                0                 0   \n",
       "3 2017-01-01 00:02:03  ...                0                 0   \n",
       "4 2017-01-01 00:02:12  ...                0                 0   \n",
       "\n",
       "  Valentine_Day_2017 Black_Friday_2017 Mothers_Day_2018 duration  \\\n",
       "0                  0                 0                0    560.0   \n",
       "1                  0                 0                0   1560.0   \n",
       "2                  0                 0                0   1498.8   \n",
       "3                  0                 0                0    144.0   \n",
       "4                  0                 0                0  10500.0   \n",
       "\n",
       "   amount_month_ratio            merchant_address_id  numerical_range  \\\n",
       "0            2.857143    M_ID_f001319a61_623_4_0_9_1              0.0   \n",
       "1            9.230769  M_ID_18038b5ae7_842_37_76_2_3            189.0   \n",
       "2           10.408333    M_ID_52d3026407_661_8_0_0_0              1.0   \n",
       "3            0.250000   M_ID_e5374dabc0_839_29_0_0_0              0.0   \n",
       "4           23.809524  M_ID_2cf6dc1f6f_278_37_69_9_1              8.0   \n",
       "\n",
       "   merchant_rating  \n",
       "0               12  \n",
       "1               12  \n",
       "2               11  \n",
       "3                6  \n",
       "4               11  \n",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.drop('index', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.to_feather('all_trans')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "merchants.to_feather('merchants_minimised')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/pyarrow/pandas_compat.py:752: FutureWarning: .labels was deprecated in version 0.24.0. Use .codes instead.\n",
      "  labels, = index.labels\n"
     ]
    }
   ],
   "source": [
    "all_trans = feather.read_dataframe('all_trans')\n",
    "merchants = feather.read_dataframe('merchants_minimised')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "def join_df(left, right, left_on, right_on=None, suffix='_y'):\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, \n",
    "                      suffixes=(\"\", suffix))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "l = join_df(all_trans, merchants, 'merchant_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_all' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-abfe8bfce859>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf_all\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'df_all' is not defined"
     ]
    }
   ],
   "source": [
    "df_all.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((31075399, 42), (334696, 22), (32493386, 63))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_trans.shape, merchants.shape, l.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "334633"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchants['merchant_id'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "merchants.drop_duplicates('merchant_id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(334633, 22)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchants.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2968"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "l = join_df(all_trans, merchants, 'merchant_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((31075399, 42), (334633, 22), (31075399, 63))"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_trans.shape, merchants.shape, l.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "l.to_feather('all_trans_merch')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/pyarrow/pandas_compat.py:752: FutureWarning: .labels was deprecated in version 0.24.0. Use .codes instead.\n",
      "  labels, = index.labels\n"
     ]
    }
   ],
   "source": [
    "all_trans_merch = feather.read_dataframe('all_trans_merch')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = '../data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.read_csv(f'{PATH}train.csv')\n",
    "test = pd.read_csv(f'{PATH}test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/ipykernel_launcher.py:1: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "all_cards = pd.concat([train, test])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 6), (123623, 5), (201917, 6))"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.shape, test.shape, train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "card_id                object\n",
       "feature_1               int64\n",
       "feature_2               int64\n",
       "feature_3               int64\n",
       "first_active_month     object\n",
       "target                float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = feather.read_dataframe('train_df_alpha')\n",
    "test_df = feather.read_dataframe('test_df_alpha')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "reqd_cols = ['card_id','hist_first_buy','new_first_buy','hist_last_buy','new_last_buy',\n",
    "             'month_diff_ratio','installments_ratio','CLV','CLV_old',\n",
    "             'CLV_ratio', 'card_id_count_ratio',\n",
    "             'purchase_amount_sum_ratio','quarter','observed_elapsed_time',\n",
    "             'days_feature1','days_feature2','days_feature3','days_feature1_ratio',\n",
    "             'days_feature2_ratio','days_feature3_ratio','feature_sum','feature_mean',\n",
    "             'feature_max','feature_min','feature_var', 'feature_1','feature_2','feature_3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = train_df[reqd_cols + ['target', 'outliers']]\n",
    "test = test_df[reqd_cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((201917, 30), (123623, 28))"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape, test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['card_id', 'hist_first_buy', 'new_first_buy', 'hist_last_buy', 'new_last_buy', 'month_diff_ratio',\n",
       "       'installments_ratio', 'CLV', 'CLV_old', 'CLV_ratio', 'card_id_count_ratio',\n",
       "       'purchase_amount_sum_ratio', 'quarter', 'observed_elapsed_time', 'days_feature1', 'days_feature2',\n",
       "       'days_feature3', 'days_feature1_ratio', 'days_feature2_ratio', 'days_feature3_ratio', 'feature_sum',\n",
       "       'feature_mean', 'feature_max', 'feature_min', 'feature_var', 'feature_1', 'feature_2', 'feature_3',\n",
       "       'target', 'outliers'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "test['outliers'] = -9999\n",
    "test['target'] = -9999"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/ipykernel_launcher.py:1: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "all_cards = pd.concat([train,test])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(325540, 30)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CLV                          float64\n",
       "CLV_old                      float64\n",
       "CLV_ratio                    float64\n",
       "card_id                       object\n",
       "card_id_count_ratio          float64\n",
       "days_feature1                float64\n",
       "days_feature1_ratio          float64\n",
       "days_feature2                float64\n",
       "days_feature2_ratio          float64\n",
       "days_feature3                float64\n",
       "days_feature3_ratio          float64\n",
       "feature_1                    float64\n",
       "feature_2                    float64\n",
       "feature_3                    float64\n",
       "feature_max                    int64\n",
       "feature_mean                 float64\n",
       "feature_min                    int64\n",
       "feature_sum                    int64\n",
       "feature_var                  float64\n",
       "hist_first_buy               float64\n",
       "hist_last_buy                float64\n",
       "installments_ratio           float64\n",
       "month_diff_ratio             float64\n",
       "new_first_buy                float64\n",
       "new_last_buy                 float64\n",
       "observed_elapsed_time        float64\n",
       "outliers                       int64\n",
       "purchase_amount_sum_ratio    float64\n",
       "quarter                      float64\n",
       "target                       float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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>CLV</th>\n",
       "      <td>5149.37</td>\n",
       "      <td>39.1138</td>\n",
       "      <td>2.81818</td>\n",
       "      <td>222.822</td>\n",
       "      <td>14096.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLV_old</th>\n",
       "      <td>412964</td>\n",
       "      <td>930755</td>\n",
       "      <td>7116.69</td>\n",
       "      <td>34891.5</td>\n",
       "      <td>381239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLV_ratio</th>\n",
       "      <td>0.0124693</td>\n",
       "      <td>4.20237e-05</td>\n",
       "      <td>0.000395996</td>\n",
       "      <td>0.00638613</td>\n",
       "      <td>0.0369745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_92a2005557</td>\n",
       "      <td>C_ID_3d0044924f</td>\n",
       "      <td>C_ID_d639edf6cd</td>\n",
       "      <td>C_ID_186d6a6901</td>\n",
       "      <td>C_ID_cdbd2c0db2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_count_ratio</th>\n",
       "      <td>0.0884615</td>\n",
       "      <td>0.0171429</td>\n",
       "      <td>0.0232558</td>\n",
       "      <td>0.0909091</td>\n",
       "      <td>0.270677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature1</th>\n",
       "      <td>1365</td>\n",
       "      <td>1584</td>\n",
       "      <td>1154</td>\n",
       "      <td>724</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature1_ratio</th>\n",
       "      <td>0.018315</td>\n",
       "      <td>0.010101</td>\n",
       "      <td>0.0034662</td>\n",
       "      <td>0.0220994</td>\n",
       "      <td>0.00833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature2</th>\n",
       "      <td>546</td>\n",
       "      <td>396</td>\n",
       "      <td>1154</td>\n",
       "      <td>543</td>\n",
       "      <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature2_ratio</th>\n",
       "      <td>0.00732601</td>\n",
       "      <td>0.00252525</td>\n",
       "      <td>0.0034662</td>\n",
       "      <td>0.0165746</td>\n",
       "      <td>0.025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature3</th>\n",
       "      <td>273</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature3_ratio</th>\n",
       "      <td>0.003663</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_1</th>\n",
       "      <td>0.0131446</td>\n",
       "      <td>0.0107116</td>\n",
       "      <td>0.0106099</td>\n",
       "      <td>0.0107116</td>\n",
       "      <td>0.00805849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_2</th>\n",
       "      <td>0.00875212</td>\n",
       "      <td>0.0113848</td>\n",
       "      <td>0.00875212</td>\n",
       "      <td>0.0141664</td>\n",
       "      <td>0.0141664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_3</th>\n",
       "      <td>0.0114275</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_max</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_mean</th>\n",
       "      <td>2.66667</td>\n",
       "      <td>1.66667</td>\n",
       "      <td>1.33333</td>\n",
       "      <td>2.33333</td>\n",
       "      <td>1.33333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_min</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_sum</th>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_var</th>\n",
       "      <td>2.08167</td>\n",
       "      <td>2.08167</td>\n",
       "      <td>1.1547</td>\n",
       "      <td>2.08167</td>\n",
       "      <td>1.52753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hist_first_buy</th>\n",
       "      <td>26</td>\n",
       "      <td>5</td>\n",
       "      <td>163</td>\n",
       "      <td>25</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hist_last_buy</th>\n",
       "      <td>269</td>\n",
       "      <td>395</td>\n",
       "      <td>575</td>\n",
       "      <td>180</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_ratio</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0110092</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0697674</td>\n",
       "      <td>0.197802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_ratio</th>\n",
       "      <td>0.988901</td>\n",
       "      <td>0.997483</td>\n",
       "      <td>0.927451</td>\n",
       "      <td>1.01874</td>\n",
       "      <td>1.00629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new_first_buy</th>\n",
       "      <td>277</td>\n",
       "      <td>396</td>\n",
       "      <td>635</td>\n",
       "      <td>187</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new_last_buy</th>\n",
       "      <td>332</td>\n",
       "      <td>453</td>\n",
       "      <td>635</td>\n",
       "      <td>229</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>observed_elapsed_time</th>\n",
       "      <td>273</td>\n",
       "      <td>396</td>\n",
       "      <td>577</td>\n",
       "      <td>181</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>outliers</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_sum_ratio</th>\n",
       "      <td>0.139393</td>\n",
       "      <td>0.00244521</td>\n",
       "      <td>0.0157925</td>\n",
       "      <td>0.0715641</td>\n",
       "      <td>0.137459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>quarter</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>target</th>\n",
       "      <td>-0.820283</td>\n",
       "      <td>0.392913</td>\n",
       "      <td>0.688056</td>\n",
       "      <td>0.142495</td>\n",
       "      <td>-0.159749</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         0                1                2  \\\n",
       "CLV                                5149.37          39.1138          2.81818   \n",
       "CLV_old                             412964           930755          7116.69   \n",
       "CLV_ratio                        0.0124693      4.20237e-05      0.000395996   \n",
       "card_id                    C_ID_92a2005557  C_ID_3d0044924f  C_ID_d639edf6cd   \n",
       "card_id_count_ratio              0.0884615        0.0171429        0.0232558   \n",
       "days_feature1                         1365             1584             1154   \n",
       "days_feature1_ratio               0.018315         0.010101        0.0034662   \n",
       "days_feature2                          546              396             1154   \n",
       "days_feature2_ratio             0.00732601       0.00252525        0.0034662   \n",
       "days_feature3                          273                0                0   \n",
       "days_feature3_ratio               0.003663                0                0   \n",
       "feature_1                        0.0131446        0.0107116        0.0106099   \n",
       "feature_2                       0.00875212        0.0113848       0.00875212   \n",
       "feature_3                        0.0114275        0.0102828        0.0102828   \n",
       "feature_max                              5                4                2   \n",
       "feature_mean                       2.66667          1.66667          1.33333   \n",
       "feature_min                              1                0                0   \n",
       "feature_sum                              8                5                4   \n",
       "feature_var                        2.08167          2.08167           1.1547   \n",
       "hist_first_buy                          26                5              163   \n",
       "hist_last_buy                          269              395              575   \n",
       "installments_ratio                       0        0.0110092              NaN   \n",
       "month_diff_ratio                  0.988901         0.997483         0.927451   \n",
       "new_first_buy                          277              396              635   \n",
       "new_last_buy                           332              453              635   \n",
       "observed_elapsed_time                  273              396              577   \n",
       "outliers                                 0                0                0   \n",
       "purchase_amount_sum_ratio         0.139393       0.00244521        0.0157925   \n",
       "quarter                                  2                1                3   \n",
       "target                           -0.820283         0.392913         0.688056   \n",
       "\n",
       "                                         3                4  \n",
       "CLV                                222.822          14096.1  \n",
       "CLV_old                            34891.5           381239  \n",
       "CLV_ratio                       0.00638613        0.0369745  \n",
       "card_id                    C_ID_186d6a6901  C_ID_cdbd2c0db2  \n",
       "card_id_count_ratio              0.0909091         0.270677  \n",
       "days_feature1                          724              120  \n",
       "days_feature1_ratio              0.0220994       0.00833333  \n",
       "days_feature2                          543              360  \n",
       "days_feature2_ratio              0.0165746            0.025  \n",
       "days_feature3                            0                0  \n",
       "days_feature3_ratio                      0                0  \n",
       "feature_1                        0.0107116       0.00805849  \n",
       "feature_2                        0.0141664        0.0141664  \n",
       "feature_3                        0.0102828        0.0102828  \n",
       "feature_max                              4                3  \n",
       "feature_mean                       2.33333          1.33333  \n",
       "feature_min                              0                0  \n",
       "feature_sum                              7                4  \n",
       "feature_var                        2.08167          1.52753  \n",
       "hist_first_buy                          25               11  \n",
       "hist_last_buy                          180              119  \n",
       "installments_ratio               0.0697674         0.197802  \n",
       "month_diff_ratio                   1.01874          1.00629  \n",
       "new_first_buy                          187              121  \n",
       "new_last_buy                           229              178  \n",
       "observed_elapsed_time                  181              120  \n",
       "outliers                                 0                0  \n",
       "purchase_amount_sum_ratio        0.0715641         0.137459  \n",
       "quarter                                  3                4  \n",
       "target                            0.142495        -0.159749  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CLV                          239808\n",
       "CLV_old                      325246\n",
       "CLV_ratio                    289997\n",
       "card_id                      325540\n",
       "card_id_count_ratio           11182\n",
       "days_feature1                   643\n",
       "days_feature1_ratio             646\n",
       "days_feature2                   487\n",
       "days_feature2_ratio             504\n",
       "days_feature3                   232\n",
       "days_feature3_ratio             233\n",
       "feature_1                         5\n",
       "feature_2                         3\n",
       "feature_3                         2\n",
       "feature_max                       5\n",
       "feature_mean                      7\n",
       "feature_min                       2\n",
       "feature_sum                       7\n",
       "feature_var                       9\n",
       "hist_first_buy                 2004\n",
       "hist_last_buy                  1954\n",
       "installments_ratio            11082\n",
       "month_diff_ratio              86507\n",
       "new_first_buy                  1962\n",
       "new_last_buy                   1990\n",
       "observed_elapsed_time           232\n",
       "outliers                          3\n",
       "purchase_amount_sum_ratio    289968\n",
       "quarter                           4\n",
       "target                       197111\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "card_cat_cols = ['hist_first_buy','new_first_buy','hist_last_buy','new_last_buy',\n",
    "                 'quarter','observed_elapsed_time','days_feature1','days_feature2',\n",
    "                 'days_feature3','days_feature1_ratio','days_feature2_ratio','days_feature3_ratio',\n",
    "                 'feature_sum','feature_mean','feature_max','feature_min','feature_var', 'feature_1',\n",
    "                 'feature_2','feature_3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "hist_first_buy           2004\n",
       "new_first_buy            1962\n",
       "hist_last_buy            1954\n",
       "new_last_buy             1990\n",
       "quarter                     4\n",
       "observed_elapsed_time     232\n",
       "days_feature1             643\n",
       "days_feature2             487\n",
       "days_feature3             232\n",
       "days_feature1_ratio       646\n",
       "days_feature2_ratio       504\n",
       "days_feature3_ratio       233\n",
       "feature_sum                 7\n",
       "feature_mean                7\n",
       "feature_max                 5\n",
       "feature_min                 2\n",
       "feature_var                 9\n",
       "feature_1                   5\n",
       "feature_2                   3\n",
       "feature_3                   2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards[card_cat_cols].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in card_cat_cols: all_cards[c] = all_cards[c].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_cards.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>index</th>\n",
       "      <th>CLV</th>\n",
       "      <th>CLV_old</th>\n",
       "      <th>CLV_ratio</th>\n",
       "      <th>card_id</th>\n",
       "      <th>card_id_count_ratio</th>\n",
       "      <th>days_feature1</th>\n",
       "      <th>days_feature1_ratio</th>\n",
       "      <th>days_feature2</th>\n",
       "      <th>days_feature2_ratio</th>\n",
       "      <th>...</th>\n",
       "      <th>hist_last_buy</th>\n",
       "      <th>installments_ratio</th>\n",
       "      <th>month_diff_ratio</th>\n",
       "      <th>new_first_buy</th>\n",
       "      <th>new_last_buy</th>\n",
       "      <th>observed_elapsed_time</th>\n",
       "      <th>outliers</th>\n",
       "      <th>purchase_amount_sum_ratio</th>\n",
       "      <th>quarter</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5149.372528</td>\n",
       "      <td>412964.333008</td>\n",
       "      <td>0.012469</td>\n",
       "      <td>C_ID_92a2005557</td>\n",
       "      <td>0.088462</td>\n",
       "      <td>1365.0</td>\n",
       "      <td>0.018315</td>\n",
       "      <td>546.0</td>\n",
       "      <td>0.007326</td>\n",
       "      <td>...</td>\n",
       "      <td>269.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.988901</td>\n",
       "      <td>277.0</td>\n",
       "      <td>332.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.139393</td>\n",
       "      <td>2.0</td>\n",
       "      <td>-0.820283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>39.113766</td>\n",
       "      <td>930754.891184</td>\n",
       "      <td>0.000042</td>\n",
       "      <td>C_ID_3d0044924f</td>\n",
       "      <td>0.017143</td>\n",
       "      <td>1584.0</td>\n",
       "      <td>0.010101</td>\n",
       "      <td>396.0</td>\n",
       "      <td>0.002525</td>\n",
       "      <td>...</td>\n",
       "      <td>395.0</td>\n",
       "      <td>0.011009</td>\n",
       "      <td>0.997483</td>\n",
       "      <td>396.0</td>\n",
       "      <td>453.0</td>\n",
       "      <td>396.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.002445</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.392913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2.818182</td>\n",
       "      <td>7116.692235</td>\n",
       "      <td>0.000396</td>\n",
       "      <td>C_ID_d639edf6cd</td>\n",
       "      <td>0.023256</td>\n",
       "      <td>1154.0</td>\n",
       "      <td>0.003466</td>\n",
       "      <td>1154.0</td>\n",
       "      <td>0.003466</td>\n",
       "      <td>...</td>\n",
       "      <td>575.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.927451</td>\n",
       "      <td>635.0</td>\n",
       "      <td>635.0</td>\n",
       "      <td>577.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.015792</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.688056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>222.821667</td>\n",
       "      <td>34891.478622</td>\n",
       "      <td>0.006386</td>\n",
       "      <td>C_ID_186d6a6901</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>724.0</td>\n",
       "      <td>0.022099</td>\n",
       "      <td>543.0</td>\n",
       "      <td>0.016575</td>\n",
       "      <td>...</td>\n",
       "      <td>180.0</td>\n",
       "      <td>0.069767</td>\n",
       "      <td>1.018743</td>\n",
       "      <td>187.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.071564</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.142495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>14096.099155</td>\n",
       "      <td>381238.779469</td>\n",
       "      <td>0.036974</td>\n",
       "      <td>C_ID_cdbd2c0db2</td>\n",
       "      <td>0.270677</td>\n",
       "      <td>120.0</td>\n",
       "      <td>0.008333</td>\n",
       "      <td>360.0</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>...</td>\n",
       "      <td>119.0</td>\n",
       "      <td>0.197802</td>\n",
       "      <td>1.006287</td>\n",
       "      <td>121.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.137459</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-0.159749</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index           CLV        CLV_old  CLV_ratio          card_id  \\\n",
       "0      0   5149.372528  412964.333008   0.012469  C_ID_92a2005557   \n",
       "1      1     39.113766  930754.891184   0.000042  C_ID_3d0044924f   \n",
       "2      2      2.818182    7116.692235   0.000396  C_ID_d639edf6cd   \n",
       "3      3    222.821667   34891.478622   0.006386  C_ID_186d6a6901   \n",
       "4      4  14096.099155  381238.779469   0.036974  C_ID_cdbd2c0db2   \n",
       "\n",
       "   card_id_count_ratio days_feature1 days_feature1_ratio days_feature2  \\\n",
       "0             0.088462        1365.0            0.018315         546.0   \n",
       "1             0.017143        1584.0            0.010101         396.0   \n",
       "2             0.023256        1154.0            0.003466        1154.0   \n",
       "3             0.090909         724.0            0.022099         543.0   \n",
       "4             0.270677         120.0            0.008333         360.0   \n",
       "\n",
       "  days_feature2_ratio  ... hist_last_buy installments_ratio month_diff_ratio  \\\n",
       "0            0.007326  ...         269.0           0.000000         0.988901   \n",
       "1            0.002525  ...         395.0           0.011009         0.997483   \n",
       "2            0.003466  ...         575.0                NaN         0.927451   \n",
       "3            0.016575  ...         180.0           0.069767         1.018743   \n",
       "4            0.025000  ...         119.0           0.197802         1.006287   \n",
       "\n",
       "  new_first_buy new_last_buy observed_elapsed_time outliers  \\\n",
       "0         277.0        332.0                 273.0        0   \n",
       "1         396.0        453.0                 396.0        0   \n",
       "2         635.0        635.0                 577.0        0   \n",
       "3         187.0        229.0                 181.0        0   \n",
       "4         121.0        178.0                 120.0        0   \n",
       "\n",
       "  purchase_amount_sum_ratio quarter    target  \n",
       "0                  0.139393     2.0 -0.820283  \n",
       "1                  0.002445     1.0  0.392913  \n",
       "2                  0.015792     3.0  0.688056  \n",
       "3                  0.071564     3.0  0.142495  \n",
       "4                  0.137459     4.0 -0.159749  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_cards.drop('index', inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "installments_ratio           106614\n",
       "purchase_amount_sum_ratio     35539\n",
       "new_last_buy                  35539\n",
       "new_first_buy                 35539\n",
       "month_diff_ratio              35539\n",
       "card_id_count_ratio           35539\n",
       "days_feature3_ratio           21932\n",
       "days_feature1                 21932\n",
       "days_feature2_ratio           21932\n",
       "observed_elapsed_time         21932\n",
       "days_feature2                 21932\n",
       "days_feature3                 21932\n",
       "days_feature1_ratio           21932\n",
       "quarter                           1\n",
       "hist_first_buy                    1\n",
       "hist_last_buy                     1\n",
       "card_id                           0\n",
       "CLV_ratio                         0\n",
       "CLV_old                           0\n",
       "target                            0\n",
       "feature_max                       0\n",
       "feature_1                         0\n",
       "feature_2                         0\n",
       "feature_3                         0\n",
       "feature_mean                      0\n",
       "feature_min                       0\n",
       "feature_sum                       0\n",
       "feature_var                       0\n",
       "outliers                          0\n",
       "CLV                               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.isnull().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_cards.to_feather('all_cards')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 30), (31075399, 63))"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cards.shape, all_trans_merch.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "null_cols = ['installments_ratio',\n",
    "'purchase_amount_sum_ratio',\n",
    "'new_last_buy',\n",
    "'new_first_buy',\n",
    "'month_diff_ratio',\n",
    "'card_id_count_ratio',\n",
    "'days_feature3_ratio',\n",
    "'days_feature1',\n",
    "'days_feature2_ratio',\n",
    "'observed_elapsed_time',\n",
    "'days_feature2',\n",
    "'days_feature3',\n",
    "'days_feature1_ratio',\n",
    "'quarter',\n",
    "'hist_first_buy',\n",
    "'hist_last_buy']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "null_cols = [c for c in null_cols if c not in card_cat_cols]\n",
    "#     all_cards[c].fillna(all_cards[c].median(), inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in null_cols:\n",
    "    all_cards[c].fillna(all_cards[c].median(), inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3059"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "lala = join_df(all_trans_merch, all_cards, 'card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((31075399, 92), (325540, 30), (31075399, 63))"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lala.shape, all_cards.shape, all_trans_merch.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>authorized_flag</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_da2090f28e</td>\n",
       "      <td>C_ID_efced389a0</td>\n",
       "      <td>C_ID_83561fe74a</td>\n",
       "      <td>C_ID_479fd6392a</td>\n",
       "      <td>C_ID_1cf6056088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id</th>\n",
       "      <td>623</td>\n",
       "      <td>842</td>\n",
       "      <td>661</td>\n",
       "      <td>839</td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id</th>\n",
       "      <td>M_ID_f001319a61</td>\n",
       "      <td>M_ID_18038b5ae7</td>\n",
       "      <td>M_ID_52d3026407</td>\n",
       "      <td>M_ID_e5374dabc0</td>\n",
       "      <td>M_ID_2cf6dc1f6f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag</th>\n",
       "      <td>-11</td>\n",
       "      <td>-12</td>\n",
       "      <td>-13</td>\n",
       "      <td>-1</td>\n",
       "      <td>-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount</th>\n",
       "      <td>40</td>\n",
       "      <td>120</td>\n",
       "      <td>124.9</td>\n",
       "      <td>6</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date</th>\n",
       "      <td>2017-01-01 00:00:08</td>\n",
       "      <td>2017-01-01 00:00:59</td>\n",
       "      <td>2017-01-01 00:01:41</td>\n",
       "      <td>2017-01-01 00:02:03</td>\n",
       "      <td>2017-01-01 00:02:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Year</th>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Month</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Week</th>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Day</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofweek</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_start</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_start</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_start</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Hour</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Minute</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Second</th>\n",
       "      <td>8</td>\n",
       "      <td>59</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <td>1483228808</td>\n",
       "      <td>1483228859</td>\n",
       "      <td>1483228901</td>\n",
       "      <td>1483228923</td>\n",
       "      <td>1483228932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekday</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff</th>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_successive_diff</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>inf</td>\n",
       "      <td>120</td>\n",
       "      <td>124.9</td>\n",
       "      <td>6</td>\n",
       "      <td>inf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLV</th>\n",
       "      <td>843.02</td>\n",
       "      <td>6449.36</td>\n",
       "      <td>1335.53</td>\n",
       "      <td>134.591</td>\n",
       "      <td>1692.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLV_old</th>\n",
       "      <td>216088</td>\n",
       "      <td>277773</td>\n",
       "      <td>706730</td>\n",
       "      <td>70.3312</td>\n",
       "      <td>189696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLV_ratio</th>\n",
       "      <td>0.00390128</td>\n",
       "      <td>0.0232181</td>\n",
       "      <td>0.00188973</td>\n",
       "      <td>0.00560192</td>\n",
       "      <td>0.0089233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_count_ratio</th>\n",
       "      <td>0.0451389</td>\n",
       "      <td>0.125</td>\n",
       "      <td>0.0171569</td>\n",
       "      <td>0.0789474</td>\n",
       "      <td>0.10687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature1</th>\n",
       "      <td>3747</td>\n",
       "      <td>427</td>\n",
       "      <td>1824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature1_ratio</th>\n",
       "      <td>0.00240192</td>\n",
       "      <td>0.00234192</td>\n",
       "      <td>0.00219298</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.018315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature2</th>\n",
       "      <td>3747</td>\n",
       "      <td>1281</td>\n",
       "      <td>912</td>\n",
       "      <td>NaN</td>\n",
       "      <td>546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature2_ratio</th>\n",
       "      <td>0.00240192</td>\n",
       "      <td>0.00702576</td>\n",
       "      <td>0.00109649</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00732601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature3</th>\n",
       "      <td>1249</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_feature3_ratio</th>\n",
       "      <td>0.000800641</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_1</th>\n",
       "      <td>0.0104794</td>\n",
       "      <td>0.00805849</td>\n",
       "      <td>0.0106099</td>\n",
       "      <td>0.00805849</td>\n",
       "      <td>0.0131446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_2</th>\n",
       "      <td>0.0141664</td>\n",
       "      <td>0.0141664</td>\n",
       "      <td>0.0113848</td>\n",
       "      <td>0.0113848</td>\n",
       "      <td>0.00875212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_3</th>\n",
       "      <td>0.0114275</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0102828</td>\n",
       "      <td>0.0114275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_max</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_mean</th>\n",
       "      <td>2.33333</td>\n",
       "      <td>1.33333</td>\n",
       "      <td>1</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>2.66667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_min</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_sum</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_var</th>\n",
       "      <td>1.1547</td>\n",
       "      <td>1.52753</td>\n",
       "      <td>1</td>\n",
       "      <td>0.57735</td>\n",
       "      <td>2.08167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hist_first_buy</th>\n",
       "      <td>884</td>\n",
       "      <td>31</td>\n",
       "      <td>488</td>\n",
       "      <td>92</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hist_last_buy</th>\n",
       "      <td>1247</td>\n",
       "      <td>412</td>\n",
       "      <td>911</td>\n",
       "      <td>149</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_ratio</th>\n",
       "      <td>0.0408163</td>\n",
       "      <td>0.072327</td>\n",
       "      <td>0.0159091</td>\n",
       "      <td>0.0408163</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_ratio</th>\n",
       "      <td>0.976801</td>\n",
       "      <td>1.00398</td>\n",
       "      <td>0.99685</td>\n",
       "      <td>1.00117</td>\n",
       "      <td>0.987112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new_first_buy</th>\n",
       "      <td>1263</td>\n",
       "      <td>445</td>\n",
       "      <td>928</td>\n",
       "      <td>NaN</td>\n",
       "      <td>287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new_last_buy</th>\n",
       "      <td>1289</td>\n",
       "      <td>478</td>\n",
       "      <td>970</td>\n",
       "      <td>NaN</td>\n",
       "      <td>332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>observed_elapsed_time</th>\n",
       "      <td>1249</td>\n",
       "      <td>427</td>\n",
       "      <td>912</td>\n",
       "      <td>NaN</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>outliers</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_sum_ratio</th>\n",
       "      <td>0.0844233</td>\n",
       "      <td>0.186483</td>\n",
       "      <td>0.109797</td>\n",
       "      <td>0.0712785</td>\n",
       "      <td>0.0824205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>quarter</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>target</th>\n",
       "      <td>-1.99163</td>\n",
       "      <td>-1.84033</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0.89228</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 0                    1  \\\n",
       "authorized_flag                                  1                    1   \n",
       "card_id                            C_ID_da2090f28e      C_ID_efced389a0   \n",
       "installments                                     0                    1   \n",
       "category_3                                       0                    1   \n",
       "merchant_category_id                           623                  842   \n",
       "merchant_id                        M_ID_f001319a61      M_ID_18038b5ae7   \n",
       "month_lag                                      -11                  -12   \n",
       "purchase_amount                                 40                  120   \n",
       "purchase_date                  2017-01-01 00:00:08  2017-01-01 00:00:59   \n",
       "purchase_Year                                 2017                 2017   \n",
       "purchase_Month                                   1                    1   \n",
       "purchase_Week                                   52                   52   \n",
       "purchase_Day                                     1                    1   \n",
       "purchase_Dayofweek                               6                    6   \n",
       "purchase_Dayofyear                               1                    1   \n",
       "purchase_Is_month_end                        False                False   \n",
       "purchase_Is_month_start                       True                 True   \n",
       "purchase_Is_quarter_end                      False                False   \n",
       "purchase_Is_quarter_start                     True                 True   \n",
       "purchase_Is_year_end                         False                False   \n",
       "purchase_Is_year_start                        True                 True   \n",
       "purchase_Hour                                    0                    0   \n",
       "purchase_Minute                                  0                    0   \n",
       "purchase_Second                                  8                   59   \n",
       "purchase_Elapsed                        1483228808           1483228859   \n",
       "purchased_on_weekend                             1                    1   \n",
       "purchased_on_weekday                             0                    0   \n",
       "month_diff                                      14                   13   \n",
       "purchase_date_successive_diff                    0                    0   \n",
       "price                                          inf                  120   \n",
       "...                                            ...                  ...   \n",
       "category_2                                       1                    3   \n",
       "CLV                                         843.02              6449.36   \n",
       "CLV_old                                     216088               277773   \n",
       "CLV_ratio                               0.00390128            0.0232181   \n",
       "card_id_count_ratio                      0.0451389                0.125   \n",
       "days_feature1                                 3747                  427   \n",
       "days_feature1_ratio                     0.00240192           0.00234192   \n",
       "days_feature2                                 3747                 1281   \n",
       "days_feature2_ratio                     0.00240192           0.00702576   \n",
       "days_feature3                                 1249                    0   \n",
       "days_feature3_ratio                    0.000800641                    0   \n",
       "feature_1                                0.0104794           0.00805849   \n",
       "feature_2                                0.0141664            0.0141664   \n",
       "feature_3                                0.0114275            0.0102828   \n",
       "feature_max                                      3                    3   \n",
       "feature_mean                               2.33333              1.33333   \n",
       "feature_min                                      1                    0   \n",
       "feature_sum                                      7                    4   \n",
       "feature_var                                 1.1547              1.52753   \n",
       "hist_first_buy                                 884                   31   \n",
       "hist_last_buy                                 1247                  412   \n",
       "installments_ratio                       0.0408163             0.072327   \n",
       "month_diff_ratio                          0.976801              1.00398   \n",
       "new_first_buy                                 1263                  445   \n",
       "new_last_buy                                  1289                  478   \n",
       "observed_elapsed_time                         1249                  427   \n",
       "outliers                                         0                    0   \n",
       "purchase_amount_sum_ratio                0.0844233             0.186483   \n",
       "quarter                                          3                    4   \n",
       "target                                    -1.99163             -1.84033   \n",
       "\n",
       "                                                 2                    3  \\\n",
       "authorized_flag                                  1                    1   \n",
       "card_id                            C_ID_83561fe74a      C_ID_479fd6392a   \n",
       "installments                                     1                    1   \n",
       "category_3                                       1                    1   \n",
       "merchant_category_id                           661                  839   \n",
       "merchant_id                        M_ID_52d3026407      M_ID_e5374dabc0   \n",
       "month_lag                                      -13                   -1   \n",
       "purchase_amount                              124.9                    6   \n",
       "purchase_date                  2017-01-01 00:01:41  2017-01-01 00:02:03   \n",
       "purchase_Year                                 2017                 2017   \n",
       "purchase_Month                                   1                    1   \n",
       "purchase_Week                                   52                   52   \n",
       "purchase_Day                                     1                    1   \n",
       "purchase_Dayofweek                               6                    6   \n",
       "purchase_Dayofyear                               1                    1   \n",
       "purchase_Is_month_end                        False                False   \n",
       "purchase_Is_month_start                       True                 True   \n",
       "purchase_Is_quarter_end                      False                False   \n",
       "purchase_Is_quarter_start                     True                 True   \n",
       "purchase_Is_year_end                         False                False   \n",
       "purchase_Is_year_start                        True                 True   \n",
       "purchase_Hour                                    0                    0   \n",
       "purchase_Minute                                  1                    2   \n",
       "purchase_Second                                 41                    3   \n",
       "purchase_Elapsed                        1483228901           1483228923   \n",
       "purchased_on_weekend                             1                    1   \n",
       "purchased_on_weekday                             0                    0   \n",
       "month_diff                                      12                   24   \n",
       "purchase_date_successive_diff                    0                    0   \n",
       "price                                        124.9                    6   \n",
       "...                                            ...                  ...   \n",
       "category_2                                     NaN                  NaN   \n",
       "CLV                                        1335.53              134.591   \n",
       "CLV_old                                     706730              70.3312   \n",
       "CLV_ratio                               0.00188973           0.00560192   \n",
       "card_id_count_ratio                      0.0171569            0.0789474   \n",
       "days_feature1                                 1824                  NaN   \n",
       "days_feature1_ratio                     0.00219298                  NaN   \n",
       "days_feature2                                  912                  NaN   \n",
       "days_feature2_ratio                     0.00109649                  NaN   \n",
       "days_feature3                                    0                  NaN   \n",
       "days_feature3_ratio                              0                  NaN   \n",
       "feature_1                                0.0106099           0.00805849   \n",
       "feature_2                                0.0113848            0.0113848   \n",
       "feature_3                                0.0102828            0.0102828   \n",
       "feature_max                                      2                    1   \n",
       "feature_mean                                     1             0.666667   \n",
       "feature_min                                      0                    0   \n",
       "feature_sum                                      3                    2   \n",
       "feature_var                                      1              0.57735   \n",
       "hist_first_buy                                 488                   92   \n",
       "hist_last_buy                                  911                  149   \n",
       "installments_ratio                       0.0159091            0.0408163   \n",
       "month_diff_ratio                           0.99685              1.00117   \n",
       "new_first_buy                                  928                  NaN   \n",
       "new_last_buy                                   970                  NaN   \n",
       "observed_elapsed_time                          912                  NaN   \n",
       "outliers                                     -9999                    0   \n",
       "purchase_amount_sum_ratio                 0.109797            0.0712785   \n",
       "quarter                                          3                    4   \n",
       "target                                       -9999              0.89228   \n",
       "\n",
       "                                                 4  \n",
       "authorized_flag                                  1  \n",
       "card_id                            C_ID_1cf6056088  \n",
       "installments                                     0  \n",
       "category_3                                       0  \n",
       "merchant_category_id                           278  \n",
       "merchant_id                        M_ID_2cf6dc1f6f  \n",
       "month_lag                                       -4  \n",
       "purchase_amount                                500  \n",
       "purchase_date                  2017-01-01 00:02:12  \n",
       "purchase_Year                                 2017  \n",
       "purchase_Month                                   1  \n",
       "purchase_Week                                   52  \n",
       "purchase_Day                                     1  \n",
       "purchase_Dayofweek                               6  \n",
       "purchase_Dayofyear                               1  \n",
       "purchase_Is_month_end                        False  \n",
       "purchase_Is_month_start                       True  \n",
       "purchase_Is_quarter_end                      False  \n",
       "purchase_Is_quarter_start                     True  \n",
       "purchase_Is_year_end                         False  \n",
       "purchase_Is_year_start                        True  \n",
       "purchase_Hour                                    0  \n",
       "purchase_Minute                                  2  \n",
       "purchase_Second                                 12  \n",
       "purchase_Elapsed                        1483228932  \n",
       "purchased_on_weekend                             1  \n",
       "purchased_on_weekday                             0  \n",
       "month_diff                                      21  \n",
       "purchase_date_successive_diff                    0  \n",
       "price                                          inf  \n",
       "...                                            ...  \n",
       "category_2                                       1  \n",
       "CLV                                        1692.72  \n",
       "CLV_old                                     189696  \n",
       "CLV_ratio                                0.0089233  \n",
       "card_id_count_ratio                        0.10687  \n",
       "days_feature1                                 1365  \n",
       "days_feature1_ratio                       0.018315  \n",
       "days_feature2                                  546  \n",
       "days_feature2_ratio                     0.00732601  \n",
       "days_feature3                                  273  \n",
       "days_feature3_ratio                       0.003663  \n",
       "feature_1                                0.0131446  \n",
       "feature_2                               0.00875212  \n",
       "feature_3                                0.0114275  \n",
       "feature_max                                      5  \n",
       "feature_mean                               2.66667  \n",
       "feature_min                                      1  \n",
       "feature_sum                                      8  \n",
       "feature_var                                2.08167  \n",
       "hist_first_buy                                 122  \n",
       "hist_last_buy                                  272  \n",
       "installments_ratio                               0  \n",
       "month_diff_ratio                          0.987112  \n",
       "new_first_buy                                  287  \n",
       "new_last_buy                                   332  \n",
       "observed_elapsed_time                          273  \n",
       "outliers                                     -9999  \n",
       "purchase_amount_sum_ratio                0.0824205  \n",
       "quarter                                          3  \n",
       "target                                       -9999  \n",
       "\n",
       "[92 rows x 5 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lala.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "lala.to_feather('all_card_merch')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/pyarrow/pandas_compat.py:752: FutureWarning: .labels was deprecated in version 0.24.0. Use .codes instead.\n",
      "  labels, = index.labels\n"
     ]
    }
   ],
   "source": [
    "all_card_merch = feather.read_dataframe('all_card_merch')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train_new = all_card_merch[all_card_merch['target'] != -9999]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test_new = all_card_merch[all_card_merch['target'] == -9999]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((19249698, 92), (11825701, 92), (31075399, 92))"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_new.shape, df_test_new.shape, all_card_merch.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_train_new.reset_index(inplace=True)\n",
    "df_test_new.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>index</th>\n",
       "      <th>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>...</th>\n",
       "      <th>hist_last_buy</th>\n",
       "      <th>installments_ratio</th>\n",
       "      <th>month_diff_ratio</th>\n",
       "      <th>new_first_buy</th>\n",
       "      <th>new_last_buy</th>\n",
       "      <th>observed_elapsed_time</th>\n",
       "      <th>outliers</th>\n",
       "      <th>purchase_amount_sum_ratio</th>\n",
       "      <th>quarter</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_83561fe74a</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>661</td>\n",
       "      <td>M_ID_52d3026407</td>\n",
       "      <td>-13</td>\n",
       "      <td>124.9</td>\n",
       "      <td>2017-01-01 00:01:41</td>\n",
       "      <td>...</td>\n",
       "      <td>911.0</td>\n",
       "      <td>0.015909</td>\n",
       "      <td>0.996850</td>\n",
       "      <td>928.0</td>\n",
       "      <td>970.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0.109797</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_1cf6056088</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>278</td>\n",
       "      <td>M_ID_2cf6dc1f6f</td>\n",
       "      <td>-4</td>\n",
       "      <td>500.0</td>\n",
       "      <td>2017-01-01 00:02:12</td>\n",
       "      <td>...</td>\n",
       "      <td>272.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.987112</td>\n",
       "      <td>287.0</td>\n",
       "      <td>332.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0.082420</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_dc5b78f973</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>307</td>\n",
       "      <td>M_ID_22123f98fc</td>\n",
       "      <td>-5</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2017-01-01 00:05:37</td>\n",
       "      <td>...</td>\n",
       "      <td>777.0</td>\n",
       "      <td>0.034483</td>\n",
       "      <td>0.993762</td>\n",
       "      <td>805.0</td>\n",
       "      <td>850.0</td>\n",
       "      <td>792.0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0.016940</td>\n",
       "      <td>2.0</td>\n",
       "      <td>-9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_4c5ee92630</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>307</td>\n",
       "      <td>M_ID_b4769b47e3</td>\n",
       "      <td>-4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2017-01-01 00:07:59</td>\n",
       "      <td>...</td>\n",
       "      <td>179.0</td>\n",
       "      <td>0.050847</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>223.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0.044924</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_a022409ce4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>683</td>\n",
       "      <td>M_ID_5d7f7ad37b</td>\n",
       "      <td>-11</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2017-01-01 00:08:40</td>\n",
       "      <td>...</td>\n",
       "      <td>388.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.001484</td>\n",
       "      <td>401.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>396.0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>0.006574</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-9999.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 93 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index authorized_flag          card_id  installments category_3  \\\n",
       "0      2               1  C_ID_83561fe74a           1.0          1   \n",
       "1      4               1  C_ID_1cf6056088           0.0          0   \n",
       "2      9               1  C_ID_dc5b78f973           1.0          1   \n",
       "3     13               1  C_ID_4c5ee92630           1.0          1   \n",
       "4     16               1  C_ID_a022409ce4           0.0          0   \n",
       "\n",
       "  merchant_category_id      merchant_id  month_lag  purchase_amount  \\\n",
       "0                  661  M_ID_52d3026407        -13            124.9   \n",
       "1                  278  M_ID_2cf6dc1f6f         -4            500.0   \n",
       "2                  307  M_ID_22123f98fc         -5             30.0   \n",
       "3                  307  M_ID_b4769b47e3         -4            100.0   \n",
       "4                  683  M_ID_5d7f7ad37b        -11              2.5   \n",
       "\n",
       "        purchase_date  ... hist_last_buy installments_ratio month_diff_ratio  \\\n",
       "0 2017-01-01 00:01:41  ...         911.0           0.015909         0.996850   \n",
       "1 2017-01-01 00:02:12  ...         272.0           0.000000         0.987112   \n",
       "2 2017-01-01 00:05:37  ...         777.0           0.034483         0.993762   \n",
       "3 2017-01-01 00:07:59  ...         179.0           0.050847         1.000000   \n",
       "4 2017-01-01 00:08:40  ...         388.0           0.000000         1.001484   \n",
       "\n",
       "  new_first_buy new_last_buy observed_elapsed_time  outliers  \\\n",
       "0         928.0        970.0                 912.0     -9999   \n",
       "1         287.0        332.0                 273.0     -9999   \n",
       "2         805.0        850.0                 792.0     -9999   \n",
       "3         223.0        232.0                 182.0     -9999   \n",
       "4         401.0        410.0                 396.0     -9999   \n",
       "\n",
       "   purchase_amount_sum_ratio  quarter  target  \n",
       "0                   0.109797      3.0 -9999.0  \n",
       "1                   0.082420      3.0 -9999.0  \n",
       "2                   0.016940      2.0 -9999.0  \n",
       "3                   0.044924      4.0 -9999.0  \n",
       "4                   0.006574      4.0 -9999.0  \n",
       "\n",
       "[5 rows x 93 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>...</th>\n",
       "      <th>hist_last_buy</th>\n",
       "      <th>installments_ratio</th>\n",
       "      <th>month_diff_ratio</th>\n",
       "      <th>new_first_buy</th>\n",
       "      <th>new_last_buy</th>\n",
       "      <th>observed_elapsed_time</th>\n",
       "      <th>outliers</th>\n",
       "      <th>purchase_amount_sum_ratio</th>\n",
       "      <th>quarter</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_da2090f28e</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>623</td>\n",
       "      <td>M_ID_f001319a61</td>\n",
       "      <td>-11</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2017-01-01 00:00:08</td>\n",
       "      <td>...</td>\n",
       "      <td>1247.0</td>\n",
       "      <td>0.040816</td>\n",
       "      <td>0.976801</td>\n",
       "      <td>1263.0</td>\n",
       "      <td>1289.0</td>\n",
       "      <td>1249.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.084423</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-1.991626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_efced389a0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_18038b5ae7</td>\n",
       "      <td>-12</td>\n",
       "      <td>120.0</td>\n",
       "      <td>2017-01-01 00:00:59</td>\n",
       "      <td>...</td>\n",
       "      <td>412.0</td>\n",
       "      <td>0.072327</td>\n",
       "      <td>1.003975</td>\n",
       "      <td>445.0</td>\n",
       "      <td>478.0</td>\n",
       "      <td>427.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.186483</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-1.840332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_479fd6392a</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>839</td>\n",
       "      <td>M_ID_e5374dabc0</td>\n",
       "      <td>-1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-01-01 00:02:03</td>\n",
       "      <td>...</td>\n",
       "      <td>149.0</td>\n",
       "      <td>0.040816</td>\n",
       "      <td>1.001172</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0.071279</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.892280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>C_ID_79296614ed</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>M_ID_257157f6ee</td>\n",
       "      <td>-3</td>\n",
       "      <td>108.5</td>\n",
       "      <td>2017-01-01 00:02:14</td>\n",
       "      <td>...</td>\n",
       "      <td>539.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000812</td>\n",
       "      <td>550.0</td>\n",
       "      <td>592.0</td>\n",
       "      <td>547.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.442168</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-1.349757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_f43ae18887</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>511</td>\n",
       "      <td>M_ID_00a6ca8a8a</td>\n",
       "      <td>-13</td>\n",
       "      <td>39.0</td>\n",
       "      <td>2017-01-01 00:02:25</td>\n",
       "      <td>...</td>\n",
       "      <td>454.0</td>\n",
       "      <td>0.040816</td>\n",
       "      <td>0.998291</td>\n",
       "      <td>455.0</td>\n",
       "      <td>513.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.037864</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-0.964822</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 93 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index authorized_flag          card_id  installments category_3  \\\n",
       "0      0               1  C_ID_da2090f28e           0.0          0   \n",
       "1      1               1  C_ID_efced389a0           1.0          1   \n",
       "2      3               1  C_ID_479fd6392a           1.0          1   \n",
       "3      5               0  C_ID_79296614ed           0.0          0   \n",
       "4      6               1  C_ID_f43ae18887           0.0          0   \n",
       "\n",
       "  merchant_category_id      merchant_id  month_lag  purchase_amount  \\\n",
       "0                  623  M_ID_f001319a61        -11             40.0   \n",
       "1                  842  M_ID_18038b5ae7        -12            120.0   \n",
       "2                  839  M_ID_e5374dabc0         -1              6.0   \n",
       "3                  360  M_ID_257157f6ee         -3            108.5   \n",
       "4                  511  M_ID_00a6ca8a8a        -13             39.0   \n",
       "\n",
       "        purchase_date  ... hist_last_buy installments_ratio month_diff_ratio  \\\n",
       "0 2017-01-01 00:00:08  ...        1247.0           0.040816         0.976801   \n",
       "1 2017-01-01 00:00:59  ...         412.0           0.072327         1.003975   \n",
       "2 2017-01-01 00:02:03  ...         149.0           0.040816         1.001172   \n",
       "3 2017-01-01 00:02:14  ...         539.0           0.000000         1.000812   \n",
       "4 2017-01-01 00:02:25  ...         454.0           0.040816         0.998291   \n",
       "\n",
       "  new_first_buy new_last_buy observed_elapsed_time  outliers  \\\n",
       "0        1263.0       1289.0                1249.0         0   \n",
       "1         445.0        478.0                 427.0         0   \n",
       "2           NaN          NaN                   NaN         0   \n",
       "3         550.0        592.0                 547.0         0   \n",
       "4         455.0        513.0                 455.0         0   \n",
       "\n",
       "   purchase_amount_sum_ratio  quarter    target  \n",
       "0                   0.084423      3.0 -1.991626  \n",
       "1                   0.186483      4.0 -1.840332  \n",
       "2                   0.071279      4.0  0.892280  \n",
       "3                   0.442168      4.0 -1.349757  \n",
       "4                   0.037864      4.0 -0.964822  \n",
       "\n",
       "[5 rows x 93 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>index</th>\n",
       "      <th>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>installments</th>\n",
       "      <th>category_3</th>\n",
       "      <th>merchant_category_id</th>\n",
       "      <th>merchant_id</th>\n",
       "      <th>month_lag</th>\n",
       "      <th>purchase_amount</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>...</th>\n",
       "      <th>hist_last_buy</th>\n",
       "      <th>installments_ratio</th>\n",
       "      <th>month_diff_ratio</th>\n",
       "      <th>new_first_buy</th>\n",
       "      <th>new_last_buy</th>\n",
       "      <th>observed_elapsed_time</th>\n",
       "      <th>outliers</th>\n",
       "      <th>purchase_amount_sum_ratio</th>\n",
       "      <th>quarter</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_da2090f28e</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>623</td>\n",
       "      <td>M_ID_f001319a61</td>\n",
       "      <td>-11</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2017-01-01 00:00:08</td>\n",
       "      <td>...</td>\n",
       "      <td>1247.0</td>\n",
       "      <td>0.040816</td>\n",
       "      <td>0.976801</td>\n",
       "      <td>1263.0</td>\n",
       "      <td>1289.0</td>\n",
       "      <td>1249.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.084423</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-1.991626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_efced389a0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>842</td>\n",
       "      <td>M_ID_18038b5ae7</td>\n",
       "      <td>-12</td>\n",
       "      <td>120.0</td>\n",
       "      <td>2017-01-01 00:00:59</td>\n",
       "      <td>...</td>\n",
       "      <td>412.0</td>\n",
       "      <td>0.072327</td>\n",
       "      <td>1.003975</td>\n",
       "      <td>445.0</td>\n",
       "      <td>478.0</td>\n",
       "      <td>427.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.186483</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-1.840332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_479fd6392a</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>839</td>\n",
       "      <td>M_ID_e5374dabc0</td>\n",
       "      <td>-1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-01-01 00:02:03</td>\n",
       "      <td>...</td>\n",
       "      <td>149.0</td>\n",
       "      <td>0.040816</td>\n",
       "      <td>1.001172</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0.071279</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.892280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>C_ID_79296614ed</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>M_ID_257157f6ee</td>\n",
       "      <td>-3</td>\n",
       "      <td>108.5</td>\n",
       "      <td>2017-01-01 00:02:14</td>\n",
       "      <td>...</td>\n",
       "      <td>539.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000812</td>\n",
       "      <td>550.0</td>\n",
       "      <td>592.0</td>\n",
       "      <td>547.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.442168</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-1.349757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>C_ID_f43ae18887</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>511</td>\n",
       "      <td>M_ID_00a6ca8a8a</td>\n",
       "      <td>-13</td>\n",
       "      <td>39.0</td>\n",
       "      <td>2017-01-01 00:02:25</td>\n",
       "      <td>...</td>\n",
       "      <td>454.0</td>\n",
       "      <td>0.040816</td>\n",
       "      <td>0.998291</td>\n",
       "      <td>455.0</td>\n",
       "      <td>513.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.037864</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-0.964822</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 93 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index authorized_flag          card_id  installments category_3  \\\n",
       "0      0               1  C_ID_da2090f28e           0.0          0   \n",
       "1      1               1  C_ID_efced389a0           1.0          1   \n",
       "2      3               1  C_ID_479fd6392a           1.0          1   \n",
       "3      5               0  C_ID_79296614ed           0.0          0   \n",
       "4      6               1  C_ID_f43ae18887           0.0          0   \n",
       "\n",
       "  merchant_category_id      merchant_id  month_lag  purchase_amount  \\\n",
       "0                  623  M_ID_f001319a61        -11             40.0   \n",
       "1                  842  M_ID_18038b5ae7        -12            120.0   \n",
       "2                  839  M_ID_e5374dabc0         -1              6.0   \n",
       "3                  360  M_ID_257157f6ee         -3            108.5   \n",
       "4                  511  M_ID_00a6ca8a8a        -13             39.0   \n",
       "\n",
       "        purchase_date  ... hist_last_buy installments_ratio month_diff_ratio  \\\n",
       "0 2017-01-01 00:00:08  ...        1247.0           0.040816         0.976801   \n",
       "1 2017-01-01 00:00:59  ...         412.0           0.072327         1.003975   \n",
       "2 2017-01-01 00:02:03  ...         149.0           0.040816         1.001172   \n",
       "3 2017-01-01 00:02:14  ...         539.0           0.000000         1.000812   \n",
       "4 2017-01-01 00:02:25  ...         454.0           0.040816         0.998291   \n",
       "\n",
       "  new_first_buy new_last_buy observed_elapsed_time  outliers  \\\n",
       "0        1263.0       1289.0                1249.0         0   \n",
       "1         445.0        478.0                 427.0         0   \n",
       "2           NaN          NaN                   NaN         0   \n",
       "3         550.0        592.0                 547.0         0   \n",
       "4         455.0        513.0                 455.0         0   \n",
       "\n",
       "   purchase_amount_sum_ratio  quarter    target  \n",
       "0                   0.084423      3.0 -1.991626  \n",
       "1                   0.186483      4.0 -1.840332  \n",
       "2                   0.071279      4.0  0.892280  \n",
       "3                   0.442168      4.0 -1.349757  \n",
       "4                   0.037864      4.0 -0.964822  \n",
       "\n",
       "[5 rows x 93 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/frame.py:3940: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  errors=errors)\n"
     ]
    }
   ],
   "source": [
    "# df_train_new.drop('index', inplace=True, axis=1)\n",
    "df_test_new.drop('index',inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train_new.to_feather('all_train_cont')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test_new.to_feather('all_test_cont')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((11825701, 92), (19249698, 93), (31075399, 92))"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_new.shape, df_test_new.shape, all_card_merch.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chandrasekhar/anaconda3/envs/fastai/lib/python3.6/site-packages/pyarrow/pandas_compat.py:752: FutureWarning: .labels was deprecated in version 0.24.0. Use .codes instead.\n",
      "  labels, = index.labels\n"
     ]
    }
   ],
   "source": [
    "all_train_cont = feather.read_dataframe('all_train_cont')\n",
    "all_test_cont = feather.read_dataframe('all_test_cont')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((11825701, 92), (19249698, 92))"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_test_cont.shape, all_train_cont.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(201917, 123623)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_train_cont['card_id'].nunique(), all_test_cont['card_id'].nunique()"
   ]
  },
  {
   "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": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}