Elo feature engineering-proc_df-OLD.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Load-dependencies\" data-toc-modified-id=\"Load-dependencies-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Load dependencies</a></span></li><li><span><a href=\"#Read-csv-files-to-DFs\" data-toc-modified-id=\"Read-csv-files-to-DFs-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Read csv files to DFs</a></span></li><li><span><a href=\"#Add-date-part\" data-toc-modified-id=\"Add-date-part-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Add date part</a></span></li><li><span><a href=\"#Train-cats-for-the-DFs\" data-toc-modified-id=\"Train-cats-for-the-DFs-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Train cats for the DFs</a></span></li><li><span><a href=\"#Add-extra-columns-(purchased-on-weekend,-monthend,-month_diff-etc.\" data-toc-modified-id=\"Add-extra-columns-(purchased-on-weekend,-monthend,-month_diff-etc.-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Add extra columns (purchased on weekend, monthend, month_diff etc.</a></span></li><li><span><a href=\"#Fill-NAs-with-mode\" data-toc-modified-id=\"Fill-NAs-with-mode-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Fill NAs with mode</a></span></li><li><span><a href=\"#Separate-the-authorised-and-non-authorised-transactions\" data-toc-modified-id=\"Separate-the-authorised-and-non-authorised-transactions-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Separate the authorised and non-authorised transactions</a></span></li><li><span><a href=\"#proc_df-to-fill-nas,-one-hot-encode-cats\" data-toc-modified-id=\"proc_df-to-fill-nas,-one-hot-encode-cats-8\"><span class=\"toc-item-num\">8&nbsp;&nbsp;</span>proc_df to fill nas, one hot encode cats</a></span></li><li><span><a href=\"#Mark-as-categorical-variables-(not-required)\" data-toc-modified-id=\"Mark-as-categorical-variables-(not-required)-9\"><span class=\"toc-item-num\">9&nbsp;&nbsp;</span>Mark as categorical variables (not required)</a></span></li><li><span><a href=\"#One-hot-encoding-categories-(not-required)\" data-toc-modified-id=\"One-hot-encoding-categories-(not-required)-10\"><span class=\"toc-item-num\">10&nbsp;&nbsp;</span>One hot encoding categories (not required)</a></span></li><li><span><a href=\"#Aggregate-by-card_id\" data-toc-modified-id=\"Aggregate-by-card_id-11\"><span class=\"toc-item-num\">11&nbsp;&nbsp;</span>Aggregate by card_id</a></span></li><li><span><a href=\"#Add-exta-interpreted-columns-on-aggregates\" data-toc-modified-id=\"Add-exta-interpreted-columns-on-aggregates-12\"><span class=\"toc-item-num\">12&nbsp;&nbsp;</span>Add exta interpreted columns on aggregates</a></span></li><li><span><a href=\"#Load-test-&amp;-train-DFs\" data-toc-modified-id=\"Load-test-&amp;-train-DFs-13\"><span class=\"toc-item-num\">13&nbsp;&nbsp;</span>Load test &amp; train DFs</a></span></li><li><span><a href=\"#Add-date-part-to-test-&amp;-train-dfs\" data-toc-modified-id=\"Add-date-part-to-test-&amp;-train-dfs-14\"><span class=\"toc-item-num\">14&nbsp;&nbsp;</span>Add date part to test &amp; train dfs</a></span></li><li><span><a href=\"#Merge-train-&amp;-test-with-new-&amp;-old-transactions-history\" data-toc-modified-id=\"Merge-train-&amp;-test-with-new-&amp;-old-transactions-history-15\"><span class=\"toc-item-num\">15&nbsp;&nbsp;</span>Merge train &amp; test with new &amp; old transactions history</a></span></li><li><span><a href=\"#Add-extra-columns-like-age,-total-transactions,-installments,-purchase-amount,-first-buy-etc\" data-toc-modified-id=\"Add-extra-columns-like-age,-total-transactions,-installments,-purchase-amount,-first-buy-etc-16\"><span class=\"toc-item-num\">16&nbsp;&nbsp;</span>Add extra columns like age, total transactions, installments, purchase amount, first buy etc</a></span></li><li><span><a href=\"#Mark-the-outliers\" data-toc-modified-id=\"Mark-the-outliers-17\"><span class=\"toc-item-num\">17&nbsp;&nbsp;</span>Mark the outliers</a></span></li><li><span><a href=\"#Target-encode-the-outliers\" data-toc-modified-id=\"Target-encode-the-outliers-18\"><span class=\"toc-item-num\">18&nbsp;&nbsp;</span>Target encode the outliers</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Load dependencies\n",
    "2. Read csv files to dataframes\n",
    "3. Fill missing values\n",
    "4. Add date part \n",
    "5. Add extra columns (purchased on weekend, monthend, month_diff etc.\n",
    "6. Aggregate by card_id\n",
    "7. Aggregate by categories\n",
    "8. Mark categorical columns\n",
    "9. Add exta interpreted columns on aggregates\n",
    "10. Load test & train csvs to dfs\n",
    "11. Add date part to test & train dfs\n",
    "12. Merge train & test with new & old transactions history\n",
    "13. Add extra columns like age, total transactions, installments, purchase amount, first buy etc\n",
    "14. Mark the outliers\n",
    "15. Target encode the outliers \n",
    "16. Save to feather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.notebook.set_autosave_interval(120000)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Autosaving every 120 seconds\n"
     ]
    }
   ],
   "source": [
    "%matplotlib inline\n",
    "%load_ext autoreload\n",
    "%autoreload 4\n",
    "%autosave 120"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from fastai.io import *\n",
    "from fastai.structured import *\n",
    "from sklearn.ensemble import RandomForestRegressor, RandomForestClassifier\n",
    "from pandas_summary import DataFrameSummary\n",
    "from IPython.display import display\n",
    "from sklearn import metrics\n",
    "import feather"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read csv files to DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = '../data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = ['historical_transactions', 'new_merchant_transactions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [pd.read_csv(f'{PATH}{c}.csv') for c in files]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add date part"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_datepart(hist_trans, 'purchase_date', drop=False)\n",
    "add_datepart(new_hist_trans, 'purchase_date', drop=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Train cats for the DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_cats(hist_trans)\n",
    "train_cats(new_hist_trans)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add extra columns (purchased on weekend, monthend, month_diff etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_extra_cols(df):\n",
    "    df['purchased_on_weekend'] = (df.purchase_Dayofweek >=5).astype(int)\n",
    "    df['month_diff'] = ((datetime.datetime.today() - df['purchase_date']).dt.days)//30\n",
    "    df['month_diff'] += df['month_lag']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [add_extra_cols(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 29), (1963031, 29))"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill NAs with mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_nas_for_transactions_df(df):\n",
    "    # Fill nas for category_3 with mode\n",
    "    cols = ['merchant_id', 'category_3', 'category_2']\n",
    "    for col in cols:\n",
    "        df[col] = df[col].fillna(df[col].mode()[0])\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [hist_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans, new_hist_trans = [fill_nas_for_transactions_df(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Separate the authorised and non-authorised transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_4e6213e9bc</td>\n",
       "      <td>C_ID_4e6213e9bc</td>\n",
       "      <td>C_ID_4e6213e9bc</td>\n",
       "      <td>C_ID_4e6213e9bc</td>\n",
       "      <td>C_ID_4e6213e9bc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id</th>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1</th>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments</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>category_3</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id</th>\n",
       "      <td>80</td>\n",
       "      <td>367</td>\n",
       "      <td>80</td>\n",
       "      <td>560</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id</th>\n",
       "      <td>M_ID_e020e9b302</td>\n",
       "      <td>M_ID_86ec983688</td>\n",
       "      <td>M_ID_979ed661fc</td>\n",
       "      <td>M_ID_e6d5ae8ea6</td>\n",
       "      <td>M_ID_e020e9b302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag</th>\n",
       "      <td>-8</td>\n",
       "      <td>-7</td>\n",
       "      <td>-6</td>\n",
       "      <td>-5</td>\n",
       "      <td>-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount</th>\n",
       "      <td>-0.703331</td>\n",
       "      <td>-0.733128</td>\n",
       "      <td>-0.720386</td>\n",
       "      <td>-0.735352</td>\n",
       "      <td>-0.722865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date</th>\n",
       "      <td>2017-06-25 15:33:07</td>\n",
       "      <td>2017-07-15 12:10:45</td>\n",
       "      <td>2017-08-09 22:04:29</td>\n",
       "      <td>2017-09-02 10:06:26</td>\n",
       "      <td>2017-03-10 01:14:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id</th>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id</th>\n",
       "      <td>37</td>\n",
       "      <td>16</td>\n",
       "      <td>37</td>\n",
       "      <td>34</td>\n",
       "      <td>37</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>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Week</th>\n",
       "      <td>25</td>\n",
       "      <td>28</td>\n",
       "      <td>32</td>\n",
       "      <td>35</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Day</th>\n",
       "      <td>25</td>\n",
       "      <td>15</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofweek</th>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <td>176</td>\n",
       "      <td>196</td>\n",
       "      <td>221</td>\n",
       "      <td>245</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_start</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_start</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_start</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <td>1498404787</td>\n",
       "      <td>1500120645</td>\n",
       "      <td>1502316269</td>\n",
       "      <td>1504346786</td>\n",
       "      <td>1489108459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff</th>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             0                    1  \\\n",
       "authorized_flag                              Y                    Y   \n",
       "card_id                        C_ID_4e6213e9bc      C_ID_4e6213e9bc   \n",
       "city_id                                     88                   88   \n",
       "category_1                                   N                    N   \n",
       "installments                                 0                    0   \n",
       "category_3                                   A                    A   \n",
       "merchant_category_id                        80                  367   \n",
       "merchant_id                    M_ID_e020e9b302      M_ID_86ec983688   \n",
       "month_lag                                   -8                   -7   \n",
       "purchase_amount                      -0.703331            -0.733128   \n",
       "purchase_date              2017-06-25 15:33:07  2017-07-15 12:10:45   \n",
       "category_2                                   1                    1   \n",
       "state_id                                    16                   16   \n",
       "subsector_id                                37                   16   \n",
       "purchase_Year                             2017                 2017   \n",
       "purchase_Month                               6                    7   \n",
       "purchase_Week                               25                   28   \n",
       "purchase_Day                                25                   15   \n",
       "purchase_Dayofweek                           6                    5   \n",
       "purchase_Dayofyear                         176                  196   \n",
       "purchase_Is_month_end                    False                False   \n",
       "purchase_Is_month_start                  False                False   \n",
       "purchase_Is_quarter_end                  False                False   \n",
       "purchase_Is_quarter_start                False                False   \n",
       "purchase_Is_year_end                     False                False   \n",
       "purchase_Is_year_start                   False                False   \n",
       "purchase_Elapsed                    1498404787           1500120645   \n",
       "purchased_on_weekend                         1                    1   \n",
       "month_diff                                  10                   11   \n",
       "\n",
       "                                             2                    3  \\\n",
       "authorized_flag                              Y                    Y   \n",
       "card_id                        C_ID_4e6213e9bc      C_ID_4e6213e9bc   \n",
       "city_id                                     88                   88   \n",
       "category_1                                   N                    N   \n",
       "installments                                 0                    0   \n",
       "category_3                                   A                    A   \n",
       "merchant_category_id                        80                  560   \n",
       "merchant_id                    M_ID_979ed661fc      M_ID_e6d5ae8ea6   \n",
       "month_lag                                   -6                   -5   \n",
       "purchase_amount                      -0.720386            -0.735352   \n",
       "purchase_date              2017-08-09 22:04:29  2017-09-02 10:06:26   \n",
       "category_2                                   1                    1   \n",
       "state_id                                    16                   16   \n",
       "subsector_id                                37                   34   \n",
       "purchase_Year                             2017                 2017   \n",
       "purchase_Month                               8                    9   \n",
       "purchase_Week                               32                   35   \n",
       "purchase_Day                                 9                    2   \n",
       "purchase_Dayofweek                           2                    5   \n",
       "purchase_Dayofyear                         221                  245   \n",
       "purchase_Is_month_end                    False                False   \n",
       "purchase_Is_month_start                  False                False   \n",
       "purchase_Is_quarter_end                  False                False   \n",
       "purchase_Is_quarter_start                False                False   \n",
       "purchase_Is_year_end                     False                False   \n",
       "purchase_Is_year_start                   False                False   \n",
       "purchase_Elapsed                    1502316269           1504346786   \n",
       "purchased_on_weekend                         0                    1   \n",
       "month_diff                                  11                   11   \n",
       "\n",
       "                                             4  \n",
       "authorized_flag                              Y  \n",
       "card_id                        C_ID_4e6213e9bc  \n",
       "city_id                                     88  \n",
       "category_1                                   N  \n",
       "installments                                 0  \n",
       "category_3                                   A  \n",
       "merchant_category_id                        80  \n",
       "merchant_id                    M_ID_e020e9b302  \n",
       "month_lag                                  -11  \n",
       "purchase_amount                      -0.722865  \n",
       "purchase_date              2017-03-10 01:14:19  \n",
       "category_2                                   1  \n",
       "state_id                                    16  \n",
       "subsector_id                                37  \n",
       "purchase_Year                             2017  \n",
       "purchase_Month                               3  \n",
       "purchase_Week                               10  \n",
       "purchase_Day                                10  \n",
       "purchase_Dayofweek                           4  \n",
       "purchase_Dayofyear                          69  \n",
       "purchase_Is_month_end                    False  \n",
       "purchase_Is_month_start                  False  \n",
       "purchase_Is_quarter_end                  False  \n",
       "purchase_Is_quarter_start                False  \n",
       "purchase_Is_year_end                     False  \n",
       "purchase_Is_year_start                   False  \n",
       "purchase_Elapsed                    1489108459  \n",
       "purchased_on_weekend                         0  \n",
       "month_diff                                  11  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "authorised_trans = hist_trans.loc[hist_trans['authorized_flag'] == 'Y']\n",
    "non_authorised_trans = hist_trans.loc[hist_trans['authorized_flag'] == 'N']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(29112361, 29)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((26595452, 29), (2516909, 29))"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authorised_trans.shape, non_authorised_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [authorised_trans, non_authorised_trans, new_hist_trans]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "      <th>counts</th>\n",
       "      <th>uniques</th>\n",
       "      <th>missing</th>\n",
       "      <th>missing_perc</th>\n",
       "      <th>types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>authorized_flag</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>constant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>290001</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>134.387</td>\n",
       "      <td>101.515</td>\n",
       "      <td>-1</td>\n",
       "      <td>69</td>\n",
       "      <td>110</td>\n",
       "      <td>212</td>\n",
       "      <td>347</td>\n",
       "      <td>1963031</td>\n",
       "      <td>308</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>0.682964</td>\n",
       "      <td>1.58407</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>999</td>\n",
       "      <td>1963031</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>430.97</td>\n",
       "      <td>246.339</td>\n",
       "      <td>-1</td>\n",
       "      <td>278</td>\n",
       "      <td>367</td>\n",
       "      <td>683</td>\n",
       "      <td>891</td>\n",
       "      <td>1963031</td>\n",
       "      <td>314</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>226129</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.47652</td>\n",
       "      <td>0.499448</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>-0.550969</td>\n",
       "      <td>0.694004</td>\n",
       "      <td>-0.746893</td>\n",
       "      <td>-0.716629</td>\n",
       "      <td>-0.674841</td>\n",
       "      <td>-0.581616</td>\n",
       "      <td>263.157</td>\n",
       "      <td>1963031</td>\n",
       "      <td>75190</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1667025</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>2.12965</td>\n",
       "      <td>1.50972</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1963031</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>10.8807</td>\n",
       "      <td>6.03854</td>\n",
       "      <td>-1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>24</td>\n",
       "      <td>1963031</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>25.9762</td>\n",
       "      <td>10.1291</td>\n",
       "      <td>-1</td>\n",
       "      <td>19</td>\n",
       "      <td>29</td>\n",
       "      <td>34</td>\n",
       "      <td>41</td>\n",
       "      <td>1963031</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Year</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>2017.85</td>\n",
       "      <td>0.361522</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Month</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>4.13606</td>\n",
       "      <td>2.44582</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>1963031</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Week</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>16.0857</td>\n",
       "      <td>10.6864</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "      <td>52</td>\n",
       "      <td>1963031</td>\n",
       "      <td>52</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Day</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>15.7556</td>\n",
       "      <td>8.78678</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>31</td>\n",
       "      <td>1963031</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofweek</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>3.12933</td>\n",
       "      <td>1.92773</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>1963031</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>109.884</td>\n",
       "      <td>75.1097</td>\n",
       "      <td>1</td>\n",
       "      <td>71</td>\n",
       "      <td>90</td>\n",
       "      <td>112</td>\n",
       "      <td>365</td>\n",
       "      <td>1963031</td>\n",
       "      <td>365</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.51935e+09</td>\n",
       "      <td>6.6753e+06</td>\n",
       "      <td>1.48834e+09</td>\n",
       "      <td>1.51967e+09</td>\n",
       "      <td>1.52146e+09</td>\n",
       "      <td>1.52322e+09</td>\n",
       "      <td>1.52513e+09</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1667025</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>0.307438</td>\n",
       "      <td>0.461433</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1963031</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>11.8757</td>\n",
       "      <td>2.55491</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>23</td>\n",
       "      <td>1963031</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 count         mean         std          min  \\\n",
       "authorized_flag                    NaN          NaN         NaN          NaN   \n",
       "card_id                            NaN          NaN         NaN          NaN   \n",
       "city_id                    1.96303e+06      134.387     101.515           -1   \n",
       "category_1                         NaN          NaN         NaN          NaN   \n",
       "installments               1.96303e+06     0.682964     1.58407           -1   \n",
       "category_3                         NaN          NaN         NaN          NaN   \n",
       "merchant_category_id       1.96303e+06       430.97     246.339           -1   \n",
       "merchant_id                        NaN          NaN         NaN          NaN   \n",
       "month_lag                  1.96303e+06      1.47652    0.499448            1   \n",
       "purchase_amount            1.96303e+06    -0.550969    0.694004    -0.746893   \n",
       "purchase_date                      NaN          NaN         NaN          NaN   \n",
       "category_2                 1.96303e+06      2.12965     1.50972            1   \n",
       "state_id                   1.96303e+06      10.8807     6.03854           -1   \n",
       "subsector_id               1.96303e+06      25.9762     10.1291           -1   \n",
       "purchase_Year              1.96303e+06      2017.85    0.361522         2017   \n",
       "purchase_Month             1.96303e+06      4.13606     2.44582            1   \n",
       "purchase_Week              1.96303e+06      16.0857     10.6864            1   \n",
       "purchase_Day               1.96303e+06      15.7556     8.78678            1   \n",
       "purchase_Dayofweek         1.96303e+06      3.12933     1.92773            0   \n",
       "purchase_Dayofyear         1.96303e+06      109.884     75.1097            1   \n",
       "purchase_Is_month_end              NaN          NaN         NaN          NaN   \n",
       "purchase_Is_month_start            NaN          NaN         NaN          NaN   \n",
       "purchase_Is_quarter_end            NaN          NaN         NaN          NaN   \n",
       "purchase_Is_quarter_start          NaN          NaN         NaN          NaN   \n",
       "purchase_Is_year_end               NaN          NaN         NaN          NaN   \n",
       "purchase_Is_year_start             NaN          NaN         NaN          NaN   \n",
       "purchase_Elapsed           1.96303e+06  1.51935e+09  6.6753e+06  1.48834e+09   \n",
       "purchased_on_weekend       1.96303e+06     0.307438    0.461433            0   \n",
       "month_diff                 1.96303e+06      11.8757     2.55491           10   \n",
       "\n",
       "                                   25%          50%          75%          max  \\\n",
       "authorized_flag                    NaN          NaN          NaN          NaN   \n",
       "card_id                            NaN          NaN          NaN          NaN   \n",
       "city_id                             69          110          212          347   \n",
       "category_1                         NaN          NaN          NaN          NaN   \n",
       "installments                         0            1            1          999   \n",
       "category_3                         NaN          NaN          NaN          NaN   \n",
       "merchant_category_id               278          367          683          891   \n",
       "merchant_id                        NaN          NaN          NaN          NaN   \n",
       "month_lag                            1            1            2            2   \n",
       "purchase_amount              -0.716629    -0.674841    -0.581616      263.157   \n",
       "purchase_date                      NaN          NaN          NaN          NaN   \n",
       "category_2                           1            1            3            5   \n",
       "state_id                             9            9           15           24   \n",
       "subsector_id                        19           29           34           41   \n",
       "purchase_Year                     2018         2018         2018         2018   \n",
       "purchase_Month                       3            3            4           12   \n",
       "purchase_Week                       11           13           16           52   \n",
       "purchase_Day                         8           16           23           31   \n",
       "purchase_Dayofweek                   1            3            5            6   \n",
       "purchase_Dayofyear                  71           90          112          365   \n",
       "purchase_Is_month_end              NaN          NaN          NaN          NaN   \n",
       "purchase_Is_month_start            NaN          NaN          NaN          NaN   \n",
       "purchase_Is_quarter_end            NaN          NaN          NaN          NaN   \n",
       "purchase_Is_quarter_start          NaN          NaN          NaN          NaN   \n",
       "purchase_Is_year_end               NaN          NaN          NaN          NaN   \n",
       "purchase_Is_year_start             NaN          NaN          NaN          NaN   \n",
       "purchase_Elapsed           1.51967e+09  1.52146e+09  1.52322e+09  1.52513e+09   \n",
       "purchased_on_weekend                 0            0            1            1   \n",
       "month_diff                          11           11           12           23   \n",
       "\n",
       "                            counts  uniques missing missing_perc        types  \n",
       "authorized_flag            1963031        1       0           0%     constant  \n",
       "card_id                    1963031   290001       0           0%  categorical  \n",
       "city_id                    1963031      308       0           0%      numeric  \n",
       "category_1                 1963031        2       0           0%         bool  \n",
       "installments               1963031       15       0           0%      numeric  \n",
       "category_3                 1963031        3       0           0%  categorical  \n",
       "merchant_category_id       1963031      314       0           0%      numeric  \n",
       "merchant_id                1963031   226129       0           0%  categorical  \n",
       "month_lag                  1963031        2       0           0%         bool  \n",
       "purchase_amount            1963031    75190       0           0%      numeric  \n",
       "purchase_date              1963031  1667025       0           0%         date  \n",
       "category_2                 1963031        5       0           0%      numeric  \n",
       "state_id                   1963031       25       0           0%      numeric  \n",
       "subsector_id               1963031       41       0           0%      numeric  \n",
       "purchase_Year              1963031        2       0           0%         bool  \n",
       "purchase_Month             1963031       12       0           0%      numeric  \n",
       "purchase_Week              1963031       52       0           0%      numeric  \n",
       "purchase_Day               1963031       31       0           0%      numeric  \n",
       "purchase_Dayofweek         1963031        7       0           0%      numeric  \n",
       "purchase_Dayofyear         1963031      365       0           0%      numeric  \n",
       "purchase_Is_month_end      1963031        2       0           0%         bool  \n",
       "purchase_Is_month_start    1963031        2       0           0%         bool  \n",
       "purchase_Is_quarter_end    1963031        2       0           0%         bool  \n",
       "purchase_Is_quarter_start  1963031        2       0           0%         bool  \n",
       "purchase_Is_year_end       1963031        2       0           0%         bool  \n",
       "purchase_Is_year_start     1963031        2       0           0%         bool  \n",
       "purchase_Elapsed           1963031  1667025       0           0%      numeric  \n",
       "purchased_on_weekend       1963031        2       0           0%         bool  \n",
       "month_diff                 1963031       14       0           0%      numeric  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrameSummary(new_hist_trans).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "      <th>counts</th>\n",
       "      <th>uniques</th>\n",
       "      <th>missing</th>\n",
       "      <th>missing_perc</th>\n",
       "      <th>types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>authorized_flag</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>constant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>325540</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>130.971</td>\n",
       "      <td>103.744</td>\n",
       "      <td>-1</td>\n",
       "      <td>57</td>\n",
       "      <td>96</td>\n",
       "      <td>212</td>\n",
       "      <td>347</td>\n",
       "      <td>26595452</td>\n",
       "      <td>308</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>0.607713</td>\n",
       "      <td>1.18037</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>999</td>\n",
       "      <td>26595452</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>477.67</td>\n",
       "      <td>248.967</td>\n",
       "      <td>-1</td>\n",
       "      <td>302</td>\n",
       "      <td>454</td>\n",
       "      <td>705</td>\n",
       "      <td>891</td>\n",
       "      <td>26595452</td>\n",
       "      <td>327</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>324322</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>-4.47717</td>\n",
       "      <td>3.58501</td>\n",
       "      <td>-13</td>\n",
       "      <td>-7</td>\n",
       "      <td>-4</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>26595452</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>-0.588789</td>\n",
       "      <td>0.598016</td>\n",
       "      <td>-0.746893</td>\n",
       "      <td>-0.720251</td>\n",
       "      <td>-0.689206</td>\n",
       "      <td>-0.607943</td>\n",
       "      <td>430.17</td>\n",
       "      <td>26595452</td>\n",
       "      <td>201089</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>15510567</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>2.10277</td>\n",
       "      <td>1.50847</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>26595452</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>10.7217</td>\n",
       "      <td>6.25845</td>\n",
       "      <td>-1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>26595452</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>27.0693</td>\n",
       "      <td>9.57686</td>\n",
       "      <td>-1</td>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>34</td>\n",
       "      <td>41</td>\n",
       "      <td>26595452</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Year</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>2017.18</td>\n",
       "      <td>0.386816</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Month</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>6.51383</td>\n",
       "      <td>3.87787</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>26595452</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Week</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>26.4646</td>\n",
       "      <td>16.8616</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>28</td>\n",
       "      <td>42</td>\n",
       "      <td>52</td>\n",
       "      <td>26595452</td>\n",
       "      <td>52</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Day</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>15.8219</td>\n",
       "      <td>8.66288</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>31</td>\n",
       "      <td>26595452</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofweek</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>3.03147</td>\n",
       "      <td>1.91751</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>26595452</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>182.956</td>\n",
       "      <td>118.327</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>195</td>\n",
       "      <td>292</td>\n",
       "      <td>365</td>\n",
       "      <td>26595452</td>\n",
       "      <td>365</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>1.50478e+09</td>\n",
       "      <td>1.00158e+07</td>\n",
       "      <td>1.48323e+09</td>\n",
       "      <td>1.49709e+09</td>\n",
       "      <td>1.50636e+09</td>\n",
       "      <td>1.51328e+09</td>\n",
       "      <td>1.51986e+09</td>\n",
       "      <td>26595452</td>\n",
       "      <td>15510567</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>0.281836</td>\n",
       "      <td>0.449894</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>26595452</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff</th>\n",
       "      <td>2.65955e+07</td>\n",
       "      <td>11.5454</td>\n",
       "      <td>2.02063</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>23</td>\n",
       "      <td>26595452</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 count         mean          std          min  \\\n",
       "authorized_flag                    NaN          NaN          NaN          NaN   \n",
       "card_id                            NaN          NaN          NaN          NaN   \n",
       "city_id                    2.65955e+07      130.971      103.744           -1   \n",
       "category_1                         NaN          NaN          NaN          NaN   \n",
       "installments               2.65955e+07     0.607713      1.18037           -1   \n",
       "category_3                         NaN          NaN          NaN          NaN   \n",
       "merchant_category_id       2.65955e+07       477.67      248.967           -1   \n",
       "merchant_id                        NaN          NaN          NaN          NaN   \n",
       "month_lag                  2.65955e+07     -4.47717      3.58501          -13   \n",
       "purchase_amount            2.65955e+07    -0.588789     0.598016    -0.746893   \n",
       "purchase_date                      NaN          NaN          NaN          NaN   \n",
       "category_2                 2.65955e+07      2.10277      1.50847            1   \n",
       "state_id                   2.65955e+07      10.7217      6.25845           -1   \n",
       "subsector_id               2.65955e+07      27.0693      9.57686           -1   \n",
       "purchase_Year              2.65955e+07      2017.18     0.386816         2017   \n",
       "purchase_Month             2.65955e+07      6.51383      3.87787            1   \n",
       "purchase_Week              2.65955e+07      26.4646      16.8616            1   \n",
       "purchase_Day               2.65955e+07      15.8219      8.66288            1   \n",
       "purchase_Dayofweek         2.65955e+07      3.03147      1.91751            0   \n",
       "purchase_Dayofyear         2.65955e+07      182.956      118.327            1   \n",
       "purchase_Is_month_end              NaN          NaN          NaN          NaN   \n",
       "purchase_Is_month_start            NaN          NaN          NaN          NaN   \n",
       "purchase_Is_quarter_end            NaN          NaN          NaN          NaN   \n",
       "purchase_Is_quarter_start          NaN          NaN          NaN          NaN   \n",
       "purchase_Is_year_end               NaN          NaN          NaN          NaN   \n",
       "purchase_Is_year_start             NaN          NaN          NaN          NaN   \n",
       "purchase_Elapsed           2.65955e+07  1.50478e+09  1.00158e+07  1.48323e+09   \n",
       "purchased_on_weekend       2.65955e+07     0.281836     0.449894            0   \n",
       "month_diff                 2.65955e+07      11.5454      2.02063           10   \n",
       "\n",
       "                                   25%          50%          75%          max  \\\n",
       "authorized_flag                    NaN          NaN          NaN          NaN   \n",
       "card_id                            NaN          NaN          NaN          NaN   \n",
       "city_id                             57           96          212          347   \n",
       "category_1                         NaN          NaN          NaN          NaN   \n",
       "installments                         0            0            1          999   \n",
       "category_3                         NaN          NaN          NaN          NaN   \n",
       "merchant_category_id               302          454          705          891   \n",
       "merchant_id                        NaN          NaN          NaN          NaN   \n",
       "month_lag                           -7           -4           -2            0   \n",
       "purchase_amount              -0.720251    -0.689206    -0.607943       430.17   \n",
       "purchase_date                      NaN          NaN          NaN          NaN   \n",
       "category_2                           1            1            3            5   \n",
       "state_id                             9            9           16           24   \n",
       "subsector_id                        19           30           34           41   \n",
       "purchase_Year                     2017         2017         2017         2018   \n",
       "purchase_Month                       2            7           10           12   \n",
       "purchase_Week                        8           28           42           52   \n",
       "purchase_Day                         8           16           23           31   \n",
       "purchase_Dayofweek                   1            3            5            6   \n",
       "purchase_Dayofyear                  56          195          292          365   \n",
       "purchase_Is_month_end              NaN          NaN          NaN          NaN   \n",
       "purchase_Is_month_start            NaN          NaN          NaN          NaN   \n",
       "purchase_Is_quarter_end            NaN          NaN          NaN          NaN   \n",
       "purchase_Is_quarter_start          NaN          NaN          NaN          NaN   \n",
       "purchase_Is_year_end               NaN          NaN          NaN          NaN   \n",
       "purchase_Is_year_start             NaN          NaN          NaN          NaN   \n",
       "purchase_Elapsed           1.49709e+09  1.50636e+09  1.51328e+09  1.51986e+09   \n",
       "purchased_on_weekend                 0            0            1            1   \n",
       "month_diff                          11           11           11           23   \n",
       "\n",
       "                             counts   uniques missing missing_perc  \\\n",
       "authorized_flag            26595452         1       0           0%   \n",
       "card_id                    26595452    325540       0           0%   \n",
       "city_id                    26595452       308       0           0%   \n",
       "category_1                 26595452         2       0           0%   \n",
       "installments               26595452        15       0           0%   \n",
       "category_3                 26595452         3       0           0%   \n",
       "merchant_category_id       26595452       327       0           0%   \n",
       "merchant_id                26595452    324322       0           0%   \n",
       "month_lag                  26595452        14       0           0%   \n",
       "purchase_amount            26595452    201089       0           0%   \n",
       "purchase_date              26595452  15510567       0           0%   \n",
       "category_2                 26595452         5       0           0%   \n",
       "state_id                   26595452        25       0           0%   \n",
       "subsector_id               26595452        41       0           0%   \n",
       "purchase_Year              26595452         2       0           0%   \n",
       "purchase_Month             26595452        12       0           0%   \n",
       "purchase_Week              26595452        52       0           0%   \n",
       "purchase_Day               26595452        31       0           0%   \n",
       "purchase_Dayofweek         26595452         7       0           0%   \n",
       "purchase_Dayofyear         26595452       365       0           0%   \n",
       "purchase_Is_month_end      26595452         2       0           0%   \n",
       "purchase_Is_month_start    26595452         2       0           0%   \n",
       "purchase_Is_quarter_end    26595452         2       0           0%   \n",
       "purchase_Is_quarter_start  26595452         2       0           0%   \n",
       "purchase_Is_year_end       26595452         2       0           0%   \n",
       "purchase_Is_year_start     26595452         2       0           0%   \n",
       "purchase_Elapsed           26595452  15510567       0           0%   \n",
       "purchased_on_weekend       26595452         2       0           0%   \n",
       "month_diff                 26595452        14       0           0%   \n",
       "\n",
       "                                 types  \n",
       "authorized_flag               constant  \n",
       "card_id                    categorical  \n",
       "city_id                        numeric  \n",
       "category_1                        bool  \n",
       "installments                   numeric  \n",
       "category_3                 categorical  \n",
       "merchant_category_id           numeric  \n",
       "merchant_id                categorical  \n",
       "month_lag                      numeric  \n",
       "purchase_amount                numeric  \n",
       "purchase_date                     date  \n",
       "category_2                     numeric  \n",
       "state_id                       numeric  \n",
       "subsector_id                   numeric  \n",
       "purchase_Year                     bool  \n",
       "purchase_Month                 numeric  \n",
       "purchase_Week                  numeric  \n",
       "purchase_Day                   numeric  \n",
       "purchase_Dayofweek             numeric  \n",
       "purchase_Dayofyear             numeric  \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_Elapsed               numeric  \n",
       "purchased_on_weekend              bool  \n",
       "month_diff                     numeric  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrameSummary(authorised_trans).summary().T"
   ]
  },
  {
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "      <th>counts</th>\n",
       "      <th>uniques</th>\n",
       "      <th>missing</th>\n",
       "      <th>missing_perc</th>\n",
       "      <th>types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>authorized_flag</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>constant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>275701</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>111.939</td>\n",
       "      <td>108.007</td>\n",
       "      <td>-1</td>\n",
       "      <td>17</td>\n",
       "      <td>69</td>\n",
       "      <td>179</td>\n",
       "      <td>347</td>\n",
       "      <td>2516909</td>\n",
       "      <td>308</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>1.07943</td>\n",
       "      <td>8.68741</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>999</td>\n",
       "      <td>2516909</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>516.336</td>\n",
       "      <td>250.946</td>\n",
       "      <td>-1</td>\n",
       "      <td>307</td>\n",
       "      <td>560</td>\n",
       "      <td>705</td>\n",
       "      <td>891</td>\n",
       "      <td>2516909</td>\n",
       "      <td>311</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>186435</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>categorical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>-4.59422</td>\n",
       "      <td>3.6269</td>\n",
       "      <td>-13</td>\n",
       "      <td>-7</td>\n",
       "      <td>-4</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>2516909</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>6.64261</td>\n",
       "      <td>3821.08</td>\n",
       "      <td>-0.746908</td>\n",
       "      <td>-0.721363</td>\n",
       "      <td>-0.682294</td>\n",
       "      <td>-0.553066</td>\n",
       "      <td>6.0106e+06</td>\n",
       "      <td>2516909</td>\n",
       "      <td>100130</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2331243</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>date</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>1.90557</td>\n",
       "      <td>1.399</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2516909</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>8.93004</td>\n",
       "      <td>7.21645</td>\n",
       "      <td>-1</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>24</td>\n",
       "      <td>2516909</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>24.5143</td>\n",
       "      <td>10.5638</td>\n",
       "      <td>-1</td>\n",
       "      <td>18</td>\n",
       "      <td>29</td>\n",
       "      <td>33</td>\n",
       "      <td>41</td>\n",
       "      <td>2516909</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Year</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>2017.16</td>\n",
       "      <td>0.370173</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Month</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>6.49697</td>\n",
       "      <td>3.83615</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>2516909</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Week</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>26.3722</td>\n",
       "      <td>16.6669</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>28</td>\n",
       "      <td>42</td>\n",
       "      <td>52</td>\n",
       "      <td>2516909</td>\n",
       "      <td>52</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Day</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>15.5765</td>\n",
       "      <td>8.72277</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>31</td>\n",
       "      <td>2516909</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofweek</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>2.91514</td>\n",
       "      <td>1.92072</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2516909</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>182.169</td>\n",
       "      <td>116.911</td>\n",
       "      <td>1</td>\n",
       "      <td>59</td>\n",
       "      <td>191</td>\n",
       "      <td>290</td>\n",
       "      <td>365</td>\n",
       "      <td>2516909</td>\n",
       "      <td>365</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_quarter_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_end</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_year_start</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>1.5041e+09</td>\n",
       "      <td>1.0096e+07</td>\n",
       "      <td>1.48323e+09</td>\n",
       "      <td>1.49608e+09</td>\n",
       "      <td>1.5054e+09</td>\n",
       "      <td>1.5127e+09</td>\n",
       "      <td>1.51986e+09</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2331243</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>0.258979</td>\n",
       "      <td>0.438074</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2516909</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>bool</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff</th>\n",
       "      <td>2.51691e+06</td>\n",
       "      <td>11.6874</td>\n",
       "      <td>2.10657</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>23</td>\n",
       "      <td>2516909</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0%</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 count        mean         std          min  \\\n",
       "authorized_flag                    NaN         NaN         NaN          NaN   \n",
       "card_id                            NaN         NaN         NaN          NaN   \n",
       "city_id                    2.51691e+06     111.939     108.007           -1   \n",
       "category_1                         NaN         NaN         NaN          NaN   \n",
       "installments               2.51691e+06     1.07943     8.68741           -1   \n",
       "category_3                         NaN         NaN         NaN          NaN   \n",
       "merchant_category_id       2.51691e+06     516.336     250.946           -1   \n",
       "merchant_id                        NaN         NaN         NaN          NaN   \n",
       "month_lag                  2.51691e+06    -4.59422      3.6269          -13   \n",
       "purchase_amount            2.51691e+06     6.64261     3821.08    -0.746908   \n",
       "purchase_date                      NaN         NaN         NaN          NaN   \n",
       "category_2                 2.51691e+06     1.90557       1.399            1   \n",
       "state_id                   2.51691e+06     8.93004     7.21645           -1   \n",
       "subsector_id               2.51691e+06     24.5143     10.5638           -1   \n",
       "purchase_Year              2.51691e+06     2017.16    0.370173         2017   \n",
       "purchase_Month             2.51691e+06     6.49697     3.83615            1   \n",
       "purchase_Week              2.51691e+06     26.3722     16.6669            1   \n",
       "purchase_Day               2.51691e+06     15.5765     8.72277            1   \n",
       "purchase_Dayofweek         2.51691e+06     2.91514     1.92072            0   \n",
       "purchase_Dayofyear         2.51691e+06     182.169     116.911            1   \n",
       "purchase_Is_month_end              NaN         NaN         NaN          NaN   \n",
       "purchase_Is_month_start            NaN         NaN         NaN          NaN   \n",
       "purchase_Is_quarter_end            NaN         NaN         NaN          NaN   \n",
       "purchase_Is_quarter_start          NaN         NaN         NaN          NaN   \n",
       "purchase_Is_year_end               NaN         NaN         NaN          NaN   \n",
       "purchase_Is_year_start             NaN         NaN         NaN          NaN   \n",
       "purchase_Elapsed           2.51691e+06  1.5041e+09  1.0096e+07  1.48323e+09   \n",
       "purchased_on_weekend       2.51691e+06    0.258979    0.438074            0   \n",
       "month_diff                 2.51691e+06     11.6874     2.10657           10   \n",
       "\n",
       "                                   25%         50%         75%          max  \\\n",
       "authorized_flag                    NaN         NaN         NaN          NaN   \n",
       "card_id                            NaN         NaN         NaN          NaN   \n",
       "city_id                             17          69         179          347   \n",
       "category_1                         NaN         NaN         NaN          NaN   \n",
       "installments                         0           1           1          999   \n",
       "category_3                         NaN         NaN         NaN          NaN   \n",
       "merchant_category_id               307         560         705          891   \n",
       "merchant_id                        NaN         NaN         NaN          NaN   \n",
       "month_lag                           -7          -4          -2            0   \n",
       "purchase_amount              -0.721363   -0.682294   -0.553066   6.0106e+06   \n",
       "purchase_date                      NaN         NaN         NaN          NaN   \n",
       "category_2                           1           1           3            5   \n",
       "state_id                             3           9          15           24   \n",
       "subsector_id                        18          29          33           41   \n",
       "purchase_Year                     2017        2017        2017         2018   \n",
       "purchase_Month                       2           7          10           12   \n",
       "purchase_Week                        9          28          42           52   \n",
       "purchase_Day                         8          16          23           31   \n",
       "purchase_Dayofweek                   1           3           5            6   \n",
       "purchase_Dayofyear                  59         191         290          365   \n",
       "purchase_Is_month_end              NaN         NaN         NaN          NaN   \n",
       "purchase_Is_month_start            NaN         NaN         NaN          NaN   \n",
       "purchase_Is_quarter_end            NaN         NaN         NaN          NaN   \n",
       "purchase_Is_quarter_start          NaN         NaN         NaN          NaN   \n",
       "purchase_Is_year_end               NaN         NaN         NaN          NaN   \n",
       "purchase_Is_year_start             NaN         NaN         NaN          NaN   \n",
       "purchase_Elapsed           1.49608e+09  1.5054e+09  1.5127e+09  1.51986e+09   \n",
       "purchased_on_weekend                 0           0           1            1   \n",
       "month_diff                          11          11          12           23   \n",
       "\n",
       "                            counts  uniques missing missing_perc        types  \n",
       "authorized_flag            2516909        1       0           0%     constant  \n",
       "card_id                    2516909   275701       0           0%  categorical  \n",
       "city_id                    2516909      308       0           0%      numeric  \n",
       "category_1                 2516909        2       0           0%         bool  \n",
       "installments               2516909       15       0           0%      numeric  \n",
       "category_3                 2516909        3       0           0%  categorical  \n",
       "merchant_category_id       2516909      311       0           0%      numeric  \n",
       "merchant_id                2516909   186435       0           0%  categorical  \n",
       "month_lag                  2516909       14       0           0%      numeric  \n",
       "purchase_amount            2516909   100130       0           0%      numeric  \n",
       "purchase_date              2516909  2331243       0           0%         date  \n",
       "category_2                 2516909        5       0           0%      numeric  \n",
       "state_id                   2516909       25       0           0%      numeric  \n",
       "subsector_id               2516909       41       0           0%      numeric  \n",
       "purchase_Year              2516909        2       0           0%         bool  \n",
       "purchase_Month             2516909       12       0           0%      numeric  \n",
       "purchase_Week              2516909       52       0           0%      numeric  \n",
       "purchase_Day               2516909       31       0           0%      numeric  \n",
       "purchase_Dayofweek         2516909        7       0           0%      numeric  \n",
       "purchase_Dayofyear         2516909      365       0           0%      numeric  \n",
       "purchase_Is_month_end      2516909        2       0           0%         bool  \n",
       "purchase_Is_month_start    2516909        2       0           0%         bool  \n",
       "purchase_Is_quarter_end    2516909        2       0           0%         bool  \n",
       "purchase_Is_quarter_start  2516909        2       0           0%         bool  \n",
       "purchase_Is_year_end       2516909        2       0           0%         bool  \n",
       "purchase_Is_year_start     2516909        2       0           0%         bool  \n",
       "purchase_Elapsed           2516909  2331243       0           0%      numeric  \n",
       "purchased_on_weekend       2516909        2       0           0%         bool  \n",
       "month_diff                 2516909       14       0           0%      numeric  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrameSummary(non_authorised_trans).summary().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### proc_df to fill nas, one hot encode cats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 48.6 s, sys: 9.59 s, total: 58.2 s\n",
      "Wall time: 12.8 s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans_proc, y_hist, nas = proc_df(hist_trans, max_n_cat=7,ignore_flds=['purchase_date','card_id', 'merchant_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.82 s, sys: 0 ns, total: 3.82 s\n",
      "Wall time: 697 ms\n"
     ]
    }
   ],
   "source": [
    "%time new_trans_proc, y_new, nas_new = proc_df(new_hist_trans, max_n_cat=7,ignore_flds=['purchase_date','card_id', 'merchant_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 37), (1963031, 36))"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_proc.shape, new_trans_proc.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['purchase_date', 'card_id', 'merchant_id', 'city_id', 'installments', 'merchant_category_id',\n",
       "        'month_lag', 'purchase_amount', 'category_2', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "        'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear',\n",
       "        'purchase_Is_month_end', 'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "        'purchase_Is_quarter_start', 'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Elapsed',\n",
       "        'purchased_on_weekend', 'month_diff', 'category_2_na', 'authorized_flag_N', 'authorized_flag_Y',\n",
       "        'authorized_flag_nan', 'category_1_N', 'category_1_Y', 'category_1_nan', 'category_3_A',\n",
       "        'category_3_B', 'category_3_C', 'category_3_nan'], dtype=object),\n",
       " array(['purchase_date', 'card_id', 'merchant_id', 'city_id', 'installments', 'merchant_category_id',\n",
       "        'month_lag', 'purchase_amount', 'category_2', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "        'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek', 'purchase_Dayofyear',\n",
       "        'purchase_Is_month_end', 'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "        'purchase_Is_quarter_start', 'purchase_Is_year_end', 'purchase_Is_year_start', 'purchase_Elapsed',\n",
       "        'purchased_on_weekend', 'month_diff', 'category_2_na', 'authorized_flag_Y', 'authorized_flag_nan',\n",
       "        'category_1_N', 'category_1_Y', 'category_1_nan', 'category_3_A', 'category_3_B', 'category_3_C',\n",
       "        'category_3_nan'], dtype=object))"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_proc.columns.values, new_trans_proc.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "77"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Mark as categorical variables (not required)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def mark_categorical_cols_for_transactions(df):\n",
    "    categorical_cols = ['authorized_flag', 'card_id', 'city_id',\n",
    "       'merchant_category_id', 'merchant_id',\n",
    "       'state_id', 'subsector_id', 'purchase_Year', 'purchase_Month']\n",
    "    for c in categorical_cols:\n",
    "        df[c] = df[c].astype('category').cat.as_ordered()\n",
    "    for col in ['authorized_flag', 'category_1']:\n",
    "        df[col] = df[col].map({'Y':1, 'N':0})\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "hist_trans_c, new_hist_trans_c = [mark_categorical_cols_for_transactions(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 29), (1963031, 29))"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.shape, new_hist_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "hist_trans.to_feather('hist_trans')\n",
    "new_hist_trans.to_feather('new_hist_trans')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "hist_trans = feather.read_dataframe('hist_trans')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "new_hist_trans = feather.read_dataframe('new_hist_trans')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### One hot encoding categories (not required)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>authorized_flag</th>\n",
       "      <th>card_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>category_1</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>...</th>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <th>purchase_Is_month_start</th>\n",
       "      <th>purchase_Is_quarter_end</th>\n",
       "      <th>purchase_Is_quarter_start</th>\n",
       "      <th>purchase_Is_year_end</th>\n",
       "      <th>purchase_Is_year_start</th>\n",
       "      <th>purchase_Elapsed</th>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <th>month_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "      <td>1.96303e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0321421</td>\n",
       "      <td>0.682964</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.47652</td>\n",
       "      <td>-0.550969</td>\n",
       "      <td>...</td>\n",
       "      <td>109.884</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.51935e+09</td>\n",
       "      <td>0.307438</td>\n",
       "      <td>11.772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.176378</td>\n",
       "      <td>1.58407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.499448</td>\n",
       "      <td>0.694004</td>\n",
       "      <td>...</td>\n",
       "      <td>75.1097</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.6753e+06</td>\n",
       "      <td>0.461433</td>\n",
       "      <td>2.55921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.746893</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.48834e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.716629</td>\n",
       "      <td>...</td>\n",
       "      <td>71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.51967e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.674841</td>\n",
       "      <td>...</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.52146e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>-0.581616</td>\n",
       "      <td>...</td>\n",
       "      <td>112</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.52322e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>263.157</td>\n",
       "      <td>...</td>\n",
       "      <td>365</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.52513e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>counts</th>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>...</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "      <td>1963031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uniques</th>\n",
       "      <td>1</td>\n",
       "      <td>290001</td>\n",
       "      <td>308</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>314</td>\n",
       "      <td>226129</td>\n",
       "      <td>2</td>\n",
       "      <td>75190</td>\n",
       "      <td>...</td>\n",
       "      <td>365</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1667025</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>missing</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>missing_perc</th>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</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>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>types</th>\n",
       "      <td>constant</td>\n",
       "      <td>categorical</td>\n",
       "      <td>categorical</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "      <td>categorical</td>\n",
       "      <td>categorical</td>\n",
       "      <td>categorical</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "      <td>...</td>\n",
       "      <td>numeric</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "      <td>bool</td>\n",
       "      <td>numeric</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             authorized_flag      card_id      city_id   category_1  \\\n",
       "count            1.96303e+06          NaN          NaN  1.96303e+06   \n",
       "mean                       1          NaN          NaN    0.0321421   \n",
       "std                        0          NaN          NaN     0.176378   \n",
       "min                        1          NaN          NaN            0   \n",
       "25%                        1          NaN          NaN            0   \n",
       "50%                        1          NaN          NaN            0   \n",
       "75%                        1          NaN          NaN            0   \n",
       "max                        1          NaN          NaN            1   \n",
       "counts               1963031      1963031      1963031      1963031   \n",
       "uniques                    1       290001          308            2   \n",
       "missing                    0            0            0            0   \n",
       "missing_perc              0%           0%           0%           0%   \n",
       "types               constant  categorical  categorical         bool   \n",
       "\n",
       "             installments   category_3 merchant_category_id  merchant_id  \\\n",
       "count         1.96303e+06          NaN                  NaN          NaN   \n",
       "mean             0.682964          NaN                  NaN          NaN   \n",
       "std               1.58407          NaN                  NaN          NaN   \n",
       "min                    -1          NaN                  NaN          NaN   \n",
       "25%                     0          NaN                  NaN          NaN   \n",
       "50%                     1          NaN                  NaN          NaN   \n",
       "75%                     1          NaN                  NaN          NaN   \n",
       "max                   999          NaN                  NaN          NaN   \n",
       "counts            1963031      1963031              1963031      1963031   \n",
       "uniques                15            3                  314       226129   \n",
       "missing                 0            0                    0            0   \n",
       "missing_perc           0%           0%                   0%           0%   \n",
       "types             numeric  categorical          categorical  categorical   \n",
       "\n",
       "                month_lag purchase_amount     ...      purchase_Dayofyear  \\\n",
       "count         1.96303e+06     1.96303e+06     ...             1.96303e+06   \n",
       "mean              1.47652       -0.550969     ...                 109.884   \n",
       "std              0.499448        0.694004     ...                 75.1097   \n",
       "min                     1       -0.746893     ...                       1   \n",
       "25%                     1       -0.716629     ...                      71   \n",
       "50%                     1       -0.674841     ...                      90   \n",
       "75%                     2       -0.581616     ...                     112   \n",
       "max                     2         263.157     ...                     365   \n",
       "counts            1963031         1963031     ...                 1963031   \n",
       "uniques                 2           75190     ...                     365   \n",
       "missing                 0               0     ...                       0   \n",
       "missing_perc           0%              0%     ...                      0%   \n",
       "types                bool         numeric     ...                 numeric   \n",
       "\n",
       "             purchase_Is_month_end purchase_Is_month_start  \\\n",
       "count                          NaN                     NaN   \n",
       "mean                           NaN                     NaN   \n",
       "std                            NaN                     NaN   \n",
       "min                            NaN                     NaN   \n",
       "25%                            NaN                     NaN   \n",
       "50%                            NaN                     NaN   \n",
       "75%                            NaN                     NaN   \n",
       "max                            NaN                     NaN   \n",
       "counts                     1963031                 1963031   \n",
       "uniques                          2                       2   \n",
       "missing                          0                       0   \n",
       "missing_perc                    0%                      0%   \n",
       "types                         bool                    bool   \n",
       "\n",
       "             purchase_Is_quarter_end purchase_Is_quarter_start  \\\n",
       "count                            NaN                       NaN   \n",
       "mean                             NaN                       NaN   \n",
       "std                              NaN                       NaN   \n",
       "min                              NaN                       NaN   \n",
       "25%                              NaN                       NaN   \n",
       "50%                              NaN                       NaN   \n",
       "75%                              NaN                       NaN   \n",
       "max                              NaN                       NaN   \n",
       "counts                       1963031                   1963031   \n",
       "uniques                            2                         2   \n",
       "missing                            0                         0   \n",
       "missing_perc                      0%                        0%   \n",
       "types                           bool                      bool   \n",
       "\n",
       "             purchase_Is_year_end purchase_Is_year_start purchase_Elapsed  \\\n",
       "count                         NaN                    NaN      1.96303e+06   \n",
       "mean                          NaN                    NaN      1.51935e+09   \n",
       "std                           NaN                    NaN       6.6753e+06   \n",
       "min                           NaN                    NaN      1.48834e+09   \n",
       "25%                           NaN                    NaN      1.51967e+09   \n",
       "50%                           NaN                    NaN      1.52146e+09   \n",
       "75%                           NaN                    NaN      1.52322e+09   \n",
       "max                           NaN                    NaN      1.52513e+09   \n",
       "counts                    1963031                1963031          1963031   \n",
       "uniques                         2                      2          1667025   \n",
       "missing                         0                      0                0   \n",
       "missing_perc                   0%                     0%               0%   \n",
       "types                        bool                   bool          numeric   \n",
       "\n",
       "             purchased_on_weekend   month_diff  \n",
       "count                 1.96303e+06  1.96303e+06  \n",
       "mean                     0.307438       11.772  \n",
       "std                      0.461433      2.55921  \n",
       "min                             0           10  \n",
       "25%                             0           10  \n",
       "50%                             0           11  \n",
       "75%                             1           12  \n",
       "max                             1           23  \n",
       "counts                    1963031      1963031  \n",
       "uniques                         2           14  \n",
       "missing                         0            0  \n",
       "missing_perc                   0%           0%  \n",
       "types                        bool      numeric  \n",
       "\n",
       "[13 rows x 29 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrameSummary(new_hist_trans_c).summary()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def one_hot_encode_dfs(df):\n",
    "    return pd.get_dummies(df, columns=['category_2', 'category_3'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 29), (1963031, 29))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_c.shape, new_hist_trans_c.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "hist_trans_c_e, new_hist_trans_c_e = [one_hot_encode_dfs(df) for df in dfs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((29112361, 35), (1963031, 35))"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_c_e.shape, new_hist_trans_c_e.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "hidden": true,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['authorized_flag', 'card_id', 'city_id', 'category_1', 'installments',\n",
       "       'merchant_category_id', 'merchant_id', 'month_lag', 'purchase_amount',\n",
       "       'purchase_date', 'state_id', 'subsector_id', 'purchase_Year',\n",
       "       'purchase_Month', 'purchase_Week', 'purchase_Day', 'purchase_Dayofweek',\n",
       "       'purchase_Dayofyear', 'purchase_Is_month_end',\n",
       "       'purchase_Is_month_start', 'purchase_Is_quarter_end',\n",
       "       'purchase_Is_quarter_start', 'purchase_Is_year_end',\n",
       "       'purchase_Is_year_start', 'purchase_Elapsed', 'purchased_on_weekend',\n",
       "       'month_diff', 'category_2_1.0', 'category_2_2.0', 'category_2_3.0',\n",
       "       'category_2_4.0', 'category_2_5.0', 'category_3_A', 'category_3_B',\n",
       "       'category_3_C'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans_c_e.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "## Not working\n",
    "def aggregate_by_category(df):\n",
    "    features = ['category_2_1.0', 'category_2_2.0', 'category_2_3.0',\n",
    "       'category_2_4.0', 'category_2_5.0', 'category_3_A', 'category_3_B',\n",
    "       'category_3_C']\n",
    "    cat_agg = {\"purchase_amount\": [\"sum\", \"mean\"], \"installments\": [\"sum\", \"mean\"]}\n",
    "    other_df = pd.DataFrame()\n",
    "    for fe in features:\n",
    "        g = df.groupby(['card_id', fe]).agg(cat_agg)\n",
    "        g.columns = ['_'+fe+'_'.join(c).strip() for c in g.columns.values]\n",
    "        g.reset_index(inplace=True)\n",
    "        if other_df.empty:\n",
    "            other_df = g\n",
    "        else:\n",
    "            other_df = pd.merge(other_df, g, on='card_id', how='left')\n",
    "    return other_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate by card_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_trans_proc['authorized_flag_N'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1963031, 37), (29112361, 37))"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_trans_proc.shape, hist_trans_proc.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(new_trans_proc).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new_trans_proc.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_by_card_id(df):\n",
    "    unique_cols = ['city_id', 'merchant_category_id', 'merchant_id', 'state_id', \n",
    "                   'subsector_id']\n",
    "    aggs = {}\n",
    "    for c in unique_cols:\n",
    "        aggs[c] = ['nunique'] \n",
    "    aggs['authorized_flag_Y'] = ['mean', 'sum']\n",
    "    aggs['authorized_flag_N'] = ['mean', 'sum']\n",
    "    aggs['purchase_amount'] = ['sum', 'max', 'min', 'mean', 'std', 'median']\n",
    "    aggs['installments'] = ['sum', 'max', 'mean', 'std', 'median']\n",
    "    aggs['month_lag'] = ['sum', 'max', 'min', 'mean', 'std', 'median']\n",
    "    aggs['purchase_date'] = [np.min, np.max, np.ptp]\n",
    "    aggs['card_id'] = ['size']\n",
    "    aggs['purchased_on_weekend'] = ['sum', 'mean']\n",
    "    aggs['month_diff'] = ['mean', 'std', 'var']\n",
    "    aggs['category_1_N'] = ['sum', 'mean']\n",
    "    aggs['category_1_Y'] = ['sum', 'mean']\n",
    "    aggs['category_2'] = ['mean', 'sum']\n",
    "    aggs['category_2_na'] = ['mean', 'sum']\n",
    "    aggs['category_3_A'] = ['mean', 'sum']\n",
    "    aggs['category_3_B' ] = ['mean', 'sum']\n",
    "    aggs['category_3_C' ] = ['mean', 'sum']\n",
    "    \n",
    "    for col in ['category_2','category_2_na','category_3_nan', 'category_3_A', 'category_3_B', 'category_3_C']:\n",
    "        df[col+'_mean2'] = df.groupby([col])['purchase_amount'].transform('mean')\n",
    "        aggs[col+'_mean2'] = ['mean']    \n",
    "\n",
    "    new_df = df.groupby(['card_id']).agg(aggs)\n",
    "    new_df.columns = ['_'.join(col).strip() for col in new_df.columns.values]\n",
    "    new_df.reset_index(inplace=True)\n",
    "    other_df = (df.groupby('card_id')\n",
    "          .size()\n",
    "          .reset_index(name='transactions_count'))\n",
    "    \n",
    "    new_df = pd.merge(other_df, new_df, on='card_id', how='left')\n",
    "    new_df[\"purchase_date_ptp\"] = new_df[\"purchase_date_ptp\"].dt.days    \n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs_e = [hist_trans_proc, new_trans_proc]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 56s, sys: 12.2 s, total: 3min 8s\n",
      "Wall time: 1min 54s\n"
     ]
    }
   ],
   "source": [
    "%time hist_trans_agg, new_hist_trans_agg = [aggregate_by_card_id(df) for df in dfs_e]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add exta interpreted columns on aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_extra_cols_on_agg(df):\n",
    "    df['inverse_avg_transactions_per_day'] = df['purchase_date_ptp']/df['card_id_size']\n",
    "    df['days_since_last_transaction'] = (datetime.datetime.today() - df['purchase_date_amax']).dt.days\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "[hist_trans_agg, new_hist_trans_agg] = [add_extra_cols_on_agg(df) for df in [hist_trans_agg, \n",
    "                                                                             new_hist_trans_agg]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg.to_feather('hist_trans_agg_proc')\n",
    "new_hist_trans_agg.to_feather('new_trans_agg_proc')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans_agg_proc = feather.read_dataframe('hist_trans_agg_proc')\n",
    "new_trans_agg_proc = feather.read_dataframe('new_trans_agg_proc')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((325540, 59), (290001, 59))"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg_proc.shape, new_trans_agg_proc.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load test & train DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "train, test = [pd.read_csv(f'{PATH}{c}') for c in ['train.csv', 'test.csv']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add date part to test & train dfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_datepart(train, 'first_active_month', drop=False)\n",
    "add_datepart(test, 'first_active_month', drop=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge train & test with new & old transactions history"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def join_dfs(left, right, left_on, right_on=None, suffix='_old'):\n",
    "    if right_on is None: right_on = left_on\n",
    "    return left.merge(right, how='left', left_on=left_on, right_on=right_on, suffixes=(\"\", suffix))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_00007093c1</td>\n",
       "      <td>C_ID_0001238066</td>\n",
       "      <td>C_ID_0001506ef0</td>\n",
       "      <td>C_ID_0001793786</td>\n",
       "      <td>C_ID_000183fdda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transactions_count</th>\n",
       "      <td>149</td>\n",
       "      <td>123</td>\n",
       "      <td>66</td>\n",
       "      <td>216</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_nunique</th>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id_nunique</th>\n",
       "      <td>18</td>\n",
       "      <td>29</td>\n",
       "      <td>19</td>\n",
       "      <td>48</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id_nunique</th>\n",
       "      <td>29</td>\n",
       "      <td>65</td>\n",
       "      <td>28</td>\n",
       "      <td>119</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id_nunique</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id_nunique</th>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_Y_mean</th>\n",
       "      <td>0.765101</td>\n",
       "      <td>0.97561</td>\n",
       "      <td>0.939394</td>\n",
       "      <td>0.875</td>\n",
       "      <td>0.951389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_Y_sum</th>\n",
       "      <td>114</td>\n",
       "      <td>120</td>\n",
       "      <td>62</td>\n",
       "      <td>189</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_N_mean</th>\n",
       "      <td>0.234899</td>\n",
       "      <td>0.0243902</td>\n",
       "      <td>0.0606061</td>\n",
       "      <td>0.125</td>\n",
       "      <td>0.0486111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_N_sum</th>\n",
       "      <td>35</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_sum</th>\n",
       "      <td>-76.845</td>\n",
       "      <td>-72.4472</td>\n",
       "      <td>-34.6019</td>\n",
       "      <td>-36.786</td>\n",
       "      <td>-68.8379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_max</th>\n",
       "      <td>1.50707</td>\n",
       "      <td>0.768095</td>\n",
       "      <td>1.49355</td>\n",
       "      <td>4.55415</td>\n",
       "      <td>2.76479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_min</th>\n",
       "      <td>-0.728876</td>\n",
       "      <td>-0.734887</td>\n",
       "      <td>-0.740491</td>\n",
       "      <td>-0.745405</td>\n",
       "      <td>-0.737892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_mean</th>\n",
       "      <td>-0.515739</td>\n",
       "      <td>-0.589002</td>\n",
       "      <td>-0.524271</td>\n",
       "      <td>-0.170306</td>\n",
       "      <td>-0.478041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_std</th>\n",
       "      <td>0.298141</td>\n",
       "      <td>0.190235</td>\n",
       "      <td>0.472284</td>\n",
       "      <td>0.836046</td>\n",
       "      <td>0.524948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_median</th>\n",
       "      <td>-0.587627</td>\n",
       "      <td>-0.648184</td>\n",
       "      <td>-0.703707</td>\n",
       "      <td>-0.487911</td>\n",
       "      <td>-0.661294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_sum</th>\n",
       "      <td>192</td>\n",
       "      <td>198</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_max</th>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_mean</th>\n",
       "      <td>1.28859</td>\n",
       "      <td>1.60976</td>\n",
       "      <td>0.0151515</td>\n",
       "      <td>0.0231481</td>\n",
       "      <td>1.83333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_std</th>\n",
       "      <td>0.7649</td>\n",
       "      <td>1.48564</td>\n",
       "      <td>0.123091</td>\n",
       "      <td>0.150723</td>\n",
       "      <td>2.11885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_median</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_sum</th>\n",
       "      <td>-872</td>\n",
       "      <td>-223</td>\n",
       "      <td>-319</td>\n",
       "      <td>-719</td>\n",
       "      <td>-353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_max</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_lag_min</th>\n",
       "      <td>-12</td>\n",
       "      <td>-5</td>\n",
       "      <td>-13</td>\n",
       "      <td>-9</td>\n",
       "      <td>-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_mean</th>\n",
       "      <td>-5.85235</td>\n",
       "      <td>-1.81301</td>\n",
       "      <td>-4.83333</td>\n",
       "      <td>-3.3287</td>\n",
       "      <td>-2.45139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_std</th>\n",
       "      <td>3.45311</td>\n",
       "      <td>1.28898</td>\n",
       "      <td>4.2375</td>\n",
       "      <td>2.30637</td>\n",
       "      <td>1.89526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_median</th>\n",
       "      <td>-6</td>\n",
       "      <td>-2</td>\n",
       "      <td>-3</td>\n",
       "      <td>-3</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_amin</th>\n",
       "      <td>2017-02-14 14:00:43</td>\n",
       "      <td>2017-09-28 22:25:14</td>\n",
       "      <td>2017-01-14 16:16:01</td>\n",
       "      <td>2017-01-21 10:15:21</td>\n",
       "      <td>2017-08-07 09:49:14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_amax</th>\n",
       "      <td>2018-02-27 05:14:57</td>\n",
       "      <td>2018-02-27 16:18:59</td>\n",
       "      <td>2018-02-17 12:33:56</td>\n",
       "      <td>2017-10-31 20:20:18</td>\n",
       "      <td>2018-02-25 20:57:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_ptp</th>\n",
       "      <td>377</td>\n",
       "      <td>151</td>\n",
       "      <td>398</td>\n",
       "      <td>283</td>\n",
       "      <td>202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_size</th>\n",
       "      <td>149</td>\n",
       "      <td>123</td>\n",
       "      <td>66</td>\n",
       "      <td>216</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_sum</th>\n",
       "      <td>25</td>\n",
       "      <td>52</td>\n",
       "      <td>32</td>\n",
       "      <td>37</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_mean</th>\n",
       "      <td>0.167785</td>\n",
       "      <td>0.422764</td>\n",
       "      <td>0.484848</td>\n",
       "      <td>0.171296</td>\n",
       "      <td>0.229167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_mean</th>\n",
       "      <td>10.8121</td>\n",
       "      <td>10.5528</td>\n",
       "      <td>10.8788</td>\n",
       "      <td>14.7778</td>\n",
       "      <td>10.7292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_std</th>\n",
       "      <td>0.391965</td>\n",
       "      <td>0.499233</td>\n",
       "      <td>0.328875</td>\n",
       "      <td>0.416705</td>\n",
       "      <td>0.445941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_var</th>\n",
       "      <td>0.153637</td>\n",
       "      <td>0.249234</td>\n",
       "      <td>0.108159</td>\n",
       "      <td>0.173643</td>\n",
       "      <td>0.198864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_N_sum</th>\n",
       "      <td>121</td>\n",
       "      <td>121</td>\n",
       "      <td>66</td>\n",
       "      <td>214</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_N_mean</th>\n",
       "      <td>0.812081</td>\n",
       "      <td>0.98374</td>\n",
       "      <td>1</td>\n",
       "      <td>0.990741</td>\n",
       "      <td>0.972222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_sum</th>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_mean</th>\n",
       "      <td>0.187919</td>\n",
       "      <td>0.0162602</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00925926</td>\n",
       "      <td>0.0277778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_mean</th>\n",
       "      <td>2.63758</td>\n",
       "      <td>1.65041</td>\n",
       "      <td>2.93939</td>\n",
       "      <td>1.49074</td>\n",
       "      <td>2.85417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_sum</th>\n",
       "      <td>393</td>\n",
       "      <td>203</td>\n",
       "      <td>194</td>\n",
       "      <td>322</td>\n",
       "      <td>411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_mean</th>\n",
       "      <td>0.187919</td>\n",
       "      <td>0.0650407</td>\n",
       "      <td>0</td>\n",
       "      <td>0.527778</td>\n",
       "      <td>0.0277778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_sum</th>\n",
       "      <td>28</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>114</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.984848</td>\n",
       "      <td>0.976852</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>211</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_mean</th>\n",
       "      <td>0.838926</td>\n",
       "      <td>0.715447</td>\n",
       "      <td>0.0151515</td>\n",
       "      <td>0.0231481</td>\n",
       "      <td>0.715278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_sum</th>\n",
       "      <td>125</td>\n",
       "      <td>88</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_mean</th>\n",
       "      <td>0.161074</td>\n",
       "      <td>0.260163</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.256944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_sum</th>\n",
       "      <td>24</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_mean2_mean</th>\n",
       "      <td>0.15858</td>\n",
       "      <td>0.0454582</td>\n",
       "      <td>0.177169</td>\n",
       "      <td>-0.00261963</td>\n",
       "      <td>0.167912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_mean2_mean</th>\n",
       "      <td>-0.00523215</td>\n",
       "      <td>0.0476203</td>\n",
       "      <td>0.0755954</td>\n",
       "      <td>-0.151411</td>\n",
       "      <td>0.0636477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_nan_mean2_mean</th>\n",
       "      <td>0.0362652</td>\n",
       "      <td>0.0368062</td>\n",
       "      <td>0.0362652</td>\n",
       "      <td>0.0362652</td>\n",
       "      <td>0.0368813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_mean2_mean</th>\n",
       "      <td>-0.329781</td>\n",
       "      <td>-0.329781</td>\n",
       "      <td>0.351446</td>\n",
       "      <td>0.345914</td>\n",
       "      <td>-0.329781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_mean2_mean</th>\n",
       "      <td>-0.285957</td>\n",
       "      <td>-0.195039</td>\n",
       "      <td>0.320589</td>\n",
       "      <td>0.314702</td>\n",
       "      <td>-0.194915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_mean2_mean</th>\n",
       "      <td>0.0436633</td>\n",
       "      <td>0.0510288</td>\n",
       "      <td>0.0316902</td>\n",
       "      <td>0.0316902</td>\n",
       "      <td>0.0507896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inverse_avg_transactions_per_day</th>\n",
       "      <td>2.5302</td>\n",
       "      <td>1.22764</td>\n",
       "      <td>6.0303</td>\n",
       "      <td>1.31019</td>\n",
       "      <td>1.40278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_since_last_transaction</th>\n",
       "      <td>321</td>\n",
       "      <td>321</td>\n",
       "      <td>331</td>\n",
       "      <td>439</td>\n",
       "      <td>322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    0                    1  \\\n",
       "card_id                               C_ID_00007093c1      C_ID_0001238066   \n",
       "transactions_count                                149                  123   \n",
       "city_id_nunique                                     4                   18   \n",
       "merchant_category_id_nunique                       18                   29   \n",
       "merchant_id_nunique                                29                   65   \n",
       "state_id_nunique                                    3                    6   \n",
       "subsector_id_nunique                               13                   17   \n",
       "authorized_flag_Y_mean                       0.765101              0.97561   \n",
       "authorized_flag_Y_sum                             114                  120   \n",
       "authorized_flag_N_mean                       0.234899            0.0243902   \n",
       "authorized_flag_N_sum                              35                    3   \n",
       "purchase_amount_sum                           -76.845             -72.4472   \n",
       "purchase_amount_max                           1.50707             0.768095   \n",
       "purchase_amount_min                         -0.728876            -0.734887   \n",
       "purchase_amount_mean                        -0.515739            -0.589002   \n",
       "purchase_amount_std                          0.298141             0.190235   \n",
       "purchase_amount_median                      -0.587627            -0.648184   \n",
       "installments_sum                                  192                  198   \n",
       "installments_max                                    6                   10   \n",
       "installments_mean                             1.28859              1.60976   \n",
       "installments_std                               0.7649              1.48564   \n",
       "installments_median                                 1                    1   \n",
       "month_lag_sum                                    -872                 -223   \n",
       "month_lag_max                                       0                    0   \n",
       "month_lag_min                                     -12                   -5   \n",
       "month_lag_mean                               -5.85235             -1.81301   \n",
       "month_lag_std                                 3.45311              1.28898   \n",
       "month_lag_median                                   -6                   -2   \n",
       "purchase_date_amin                2017-02-14 14:00:43  2017-09-28 22:25:14   \n",
       "purchase_date_amax                2018-02-27 05:14:57  2018-02-27 16:18:59   \n",
       "purchase_date_ptp                                 377                  151   \n",
       "card_id_size                                      149                  123   \n",
       "purchased_on_weekend_sum                           25                   52   \n",
       "purchased_on_weekend_mean                    0.167785             0.422764   \n",
       "month_diff_mean                               10.8121              10.5528   \n",
       "month_diff_std                               0.391965             0.499233   \n",
       "month_diff_var                               0.153637             0.249234   \n",
       "category_1_N_sum                                  121                  121   \n",
       "category_1_N_mean                            0.812081              0.98374   \n",
       "category_1_Y_sum                                   28                    2   \n",
       "category_1_Y_mean                            0.187919            0.0162602   \n",
       "category_2_mean                               2.63758              1.65041   \n",
       "category_2_sum                                    393                  203   \n",
       "category_2_na_mean                           0.187919            0.0650407   \n",
       "category_2_na_sum                                  28                    8   \n",
       "category_3_A_mean                                   0                    0   \n",
       "category_3_A_sum                                    0                    0   \n",
       "category_3_B_mean                            0.838926             0.715447   \n",
       "category_3_B_sum                                  125                   88   \n",
       "category_3_C_mean                            0.161074             0.260163   \n",
       "category_3_C_sum                                   24                   32   \n",
       "category_2_mean2_mean                         0.15858            0.0454582   \n",
       "category_2_na_mean2_mean                  -0.00523215            0.0476203   \n",
       "category_3_nan_mean2_mean                   0.0362652            0.0368062   \n",
       "category_3_A_mean2_mean                     -0.329781            -0.329781   \n",
       "category_3_B_mean2_mean                     -0.285957            -0.195039   \n",
       "category_3_C_mean2_mean                     0.0436633            0.0510288   \n",
       "inverse_avg_transactions_per_day               2.5302              1.22764   \n",
       "days_since_last_transaction                       321                  321   \n",
       "\n",
       "                                                    2                    3  \\\n",
       "card_id                               C_ID_0001506ef0      C_ID_0001793786   \n",
       "transactions_count                                 66                  216   \n",
       "city_id_nunique                                     3                   10   \n",
       "merchant_category_id_nunique                       19                   48   \n",
       "merchant_id_nunique                                28                  119   \n",
       "state_id_nunique                                    2                    4   \n",
       "subsector_id_nunique                               12                   24   \n",
       "authorized_flag_Y_mean                       0.939394                0.875   \n",
       "authorized_flag_Y_sum                              62                  189   \n",
       "authorized_flag_N_mean                      0.0606061                0.125   \n",
       "authorized_flag_N_sum                               4                   27   \n",
       "purchase_amount_sum                          -34.6019              -36.786   \n",
       "purchase_amount_max                           1.49355              4.55415   \n",
       "purchase_amount_min                         -0.740491            -0.745405   \n",
       "purchase_amount_mean                        -0.524271            -0.170306   \n",
       "purchase_amount_std                          0.472284             0.836046   \n",
       "purchase_amount_median                      -0.703707            -0.487911   \n",
       "installments_sum                                    1                    5   \n",
       "installments_max                                    1                    1   \n",
       "installments_mean                           0.0151515            0.0231481   \n",
       "installments_std                             0.123091             0.150723   \n",
       "installments_median                                 0                    0   \n",
       "month_lag_sum                                    -319                 -719   \n",
       "month_lag_max                                       0                    0   \n",
       "month_lag_min                                     -13                   -9   \n",
       "month_lag_mean                               -4.83333              -3.3287   \n",
       "month_lag_std                                  4.2375              2.30637   \n",
       "month_lag_median                                   -3                   -3   \n",
       "purchase_date_amin                2017-01-14 16:16:01  2017-01-21 10:15:21   \n",
       "purchase_date_amax                2018-02-17 12:33:56  2017-10-31 20:20:18   \n",
       "purchase_date_ptp                                 398                  283   \n",
       "card_id_size                                       66                  216   \n",
       "purchased_on_weekend_sum                           32                   37   \n",
       "purchased_on_weekend_mean                    0.484848             0.171296   \n",
       "month_diff_mean                               10.8788              14.7778   \n",
       "month_diff_std                               0.328875             0.416705   \n",
       "month_diff_var                               0.108159             0.173643   \n",
       "category_1_N_sum                                   66                  214   \n",
       "category_1_N_mean                                   1             0.990741   \n",
       "category_1_Y_sum                                    0                    2   \n",
       "category_1_Y_mean                                   0           0.00925926   \n",
       "category_2_mean                               2.93939              1.49074   \n",
       "category_2_sum                                    194                  322   \n",
       "category_2_na_mean                                  0             0.527778   \n",
       "category_2_na_sum                                   0                  114   \n",
       "category_3_A_mean                            0.984848             0.976852   \n",
       "category_3_A_sum                                   65                  211   \n",
       "category_3_B_mean                           0.0151515            0.0231481   \n",
       "category_3_B_sum                                    1                    5   \n",
       "category_3_C_mean                                   0                    0   \n",
       "category_3_C_sum                                    0                    0   \n",
       "category_2_mean2_mean                        0.177169          -0.00261963   \n",
       "category_2_na_mean2_mean                    0.0755954            -0.151411   \n",
       "category_3_nan_mean2_mean                   0.0362652            0.0362652   \n",
       "category_3_A_mean2_mean                      0.351446             0.345914   \n",
       "category_3_B_mean2_mean                      0.320589             0.314702   \n",
       "category_3_C_mean2_mean                     0.0316902            0.0316902   \n",
       "inverse_avg_transactions_per_day               6.0303              1.31019   \n",
       "days_since_last_transaction                       331                  439   \n",
       "\n",
       "                                                    4  \n",
       "card_id                               C_ID_000183fdda  \n",
       "transactions_count                                144  \n",
       "city_id_nunique                                     9  \n",
       "merchant_category_id_nunique                       36  \n",
       "merchant_id_nunique                                73  \n",
       "state_id_nunique                                    7  \n",
       "subsector_id_nunique                               21  \n",
       "authorized_flag_Y_mean                       0.951389  \n",
       "authorized_flag_Y_sum                             137  \n",
       "authorized_flag_N_mean                      0.0486111  \n",
       "authorized_flag_N_sum                               7  \n",
       "purchase_amount_sum                          -68.8379  \n",
       "purchase_amount_max                           2.76479  \n",
       "purchase_amount_min                         -0.737892  \n",
       "purchase_amount_mean                        -0.478041  \n",
       "purchase_amount_std                          0.524948  \n",
       "purchase_amount_median                      -0.661294  \n",
       "installments_sum                                  264  \n",
       "installments_max                                   10  \n",
       "installments_mean                             1.83333  \n",
       "installments_std                              2.11885  \n",
       "installments_median                                 1  \n",
       "month_lag_sum                                    -353  \n",
       "month_lag_max                                       0  \n",
       "month_lag_min                                      -6  \n",
       "month_lag_mean                               -2.45139  \n",
       "month_lag_std                                 1.89526  \n",
       "month_lag_median                                   -2  \n",
       "purchase_date_amin                2017-08-07 09:49:14  \n",
       "purchase_date_amax                2018-02-25 20:57:08  \n",
       "purchase_date_ptp                                 202  \n",
       "card_id_size                                      144  \n",
       "purchased_on_weekend_sum                           33  \n",
       "purchased_on_weekend_mean                    0.229167  \n",
       "month_diff_mean                               10.7292  \n",
       "month_diff_std                               0.445941  \n",
       "month_diff_var                               0.198864  \n",
       "category_1_N_sum                                  140  \n",
       "category_1_N_mean                            0.972222  \n",
       "category_1_Y_sum                                    4  \n",
       "category_1_Y_mean                           0.0277778  \n",
       "category_2_mean                               2.85417  \n",
       "category_2_sum                                    411  \n",
       "category_2_na_mean                          0.0277778  \n",
       "category_2_na_sum                                   4  \n",
       "category_3_A_mean                                   0  \n",
       "category_3_A_sum                                    0  \n",
       "category_3_B_mean                            0.715278  \n",
       "category_3_B_sum                                  103  \n",
       "category_3_C_mean                            0.256944  \n",
       "category_3_C_sum                                   37  \n",
       "category_2_mean2_mean                        0.167912  \n",
       "category_2_na_mean2_mean                    0.0636477  \n",
       "category_3_nan_mean2_mean                   0.0368813  \n",
       "category_3_A_mean2_mean                     -0.329781  \n",
       "category_3_B_mean2_mean                     -0.194915  \n",
       "category_3_C_mean2_mean                     0.0507896  \n",
       "inverse_avg_transactions_per_day              1.40278  \n",
       "days_since_last_transaction                       322  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans_agg.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_00007093c1</td>\n",
       "      <td>C_ID_0001238066</td>\n",
       "      <td>C_ID_0001506ef0</td>\n",
       "      <td>C_ID_0001793786</td>\n",
       "      <td>C_ID_000183fdda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transactions_count</th>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_nunique</th>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id_nunique</th>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id_nunique</th>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id_nunique</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id_nunique</th>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_Y_mean</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>authorized_flag_Y_sum</th>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_N_mean</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>authorized_flag_N_sum</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</th>\n",
       "      <td>-1.32852</td>\n",
       "      <td>-14.8501</td>\n",
       "      <td>-1.44735</td>\n",
       "      <td>-0.22962</td>\n",
       "      <td>-6.59078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_max</th>\n",
       "      <td>-0.656749</td>\n",
       "      <td>-0.0783182</td>\n",
       "      <td>-0.715352</td>\n",
       "      <td>3.12993</td>\n",
       "      <td>-0.10768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_min</th>\n",
       "      <td>-0.671775</td>\n",
       "      <td>-0.740897</td>\n",
       "      <td>-0.732001</td>\n",
       "      <td>-0.737892</td>\n",
       "      <td>-0.732332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_mean</th>\n",
       "      <td>-0.664262</td>\n",
       "      <td>-0.571156</td>\n",
       "      <td>-0.723677</td>\n",
       "      <td>-0.0074071</td>\n",
       "      <td>-0.599162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_std</th>\n",
       "      <td>0.0106254</td>\n",
       "      <td>0.173436</td>\n",
       "      <td>0.0117729</td>\n",
       "      <td>0.947223</td>\n",
       "      <td>0.182877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_median</th>\n",
       "      <td>-0.664262</td>\n",
       "      <td>-0.649235</td>\n",
       "      <td>-0.723677</td>\n",
       "      <td>-0.372748</td>\n",
       "      <td>-0.665765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_sum</th>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_max</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_mean</th>\n",
       "      <td>1</td>\n",
       "      <td>1.61538</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.45455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_std</th>\n",
       "      <td>0</td>\n",
       "      <td>2.07994</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.29334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_median</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_sum</th>\n",
       "      <td>4</td>\n",
       "      <td>35</td>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_max</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_min</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_mean</th>\n",
       "      <td>2</td>\n",
       "      <td>1.34615</td>\n",
       "      <td>1</td>\n",
       "      <td>1.32258</td>\n",
       "      <td>1.27273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_std</th>\n",
       "      <td>0</td>\n",
       "      <td>0.485165</td>\n",
       "      <td>0</td>\n",
       "      <td>0.475191</td>\n",
       "      <td>0.467099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_median</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_amin</th>\n",
       "      <td>2018-04-03 11:13:35</td>\n",
       "      <td>2018-03-01 16:48:27</td>\n",
       "      <td>2018-03-16 22:21:58</td>\n",
       "      <td>2017-11-15 15:44:20</td>\n",
       "      <td>2018-03-02 12:26:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_amax</th>\n",
       "      <td>2018-04-09 16:23:59</td>\n",
       "      <td>2018-04-30 19:57:30</td>\n",
       "      <td>2018-03-22 09:14:30</td>\n",
       "      <td>2017-12-31 17:35:56</td>\n",
       "      <td>2018-04-30 14:59:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_ptp</th>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "      <td>5</td>\n",
       "      <td>46</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_size</th>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>0</td>\n",
       "      <td>0.451613</td>\n",
       "      <td>0.181818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_mean</th>\n",
       "      <td>11</td>\n",
       "      <td>10.5</td>\n",
       "      <td>10.5</td>\n",
       "      <td>14.2903</td>\n",
       "      <td>10.7273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_std</th>\n",
       "      <td>0</td>\n",
       "      <td>0.509902</td>\n",
       "      <td>0.707107</td>\n",
       "      <td>0.461414</td>\n",
       "      <td>0.467099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_var</th>\n",
       "      <td>0</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.212903</td>\n",
       "      <td>0.218182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_N_sum</th>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_N_mean</th>\n",
       "      <td>1</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0769231</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_mean</th>\n",
       "      <td>2</td>\n",
       "      <td>1.46154</td>\n",
       "      <td>3</td>\n",
       "      <td>1.70968</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_sum</th>\n",
       "      <td>4</td>\n",
       "      <td>38</td>\n",
       "      <td>6</td>\n",
       "      <td>53</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0.115385</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0645161</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_mean</th>\n",
       "      <td>1</td>\n",
       "      <td>0.807692</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_sum</th>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.363636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_mean2_mean</th>\n",
       "      <td>-0.550506</td>\n",
       "      <td>-0.550028</td>\n",
       "      <td>-0.550852</td>\n",
       "      <td>-0.551649</td>\n",
       "      <td>-0.550852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_mean2_mean</th>\n",
       "      <td>-0.561925</td>\n",
       "      <td>-0.539717</td>\n",
       "      <td>-0.561925</td>\n",
       "      <td>-0.549508</td>\n",
       "      <td>-0.561925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_nan_mean2_mean</th>\n",
       "      <td>-0.568123</td>\n",
       "      <td>-0.544963</td>\n",
       "      <td>-0.568123</td>\n",
       "      <td>-0.568123</td>\n",
       "      <td>-0.513382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_mean2_mean</th>\n",
       "      <td>-0.480041</td>\n",
       "      <td>-0.480041</td>\n",
       "      <td>-0.631014</td>\n",
       "      <td>-0.631014</td>\n",
       "      <td>-0.480041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_mean2_mean</th>\n",
       "      <td>-0.606486</td>\n",
       "      <td>-0.587887</td>\n",
       "      <td>-0.509773</td>\n",
       "      <td>-0.509773</td>\n",
       "      <td>-0.562525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_mean2_mean</th>\n",
       "      <td>-0.599212</td>\n",
       "      <td>-0.501224</td>\n",
       "      <td>-0.599212</td>\n",
       "      <td>-0.599212</td>\n",
       "      <td>-0.367604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inverse_avg_transactions_per_day</th>\n",
       "      <td>3</td>\n",
       "      <td>2.30769</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.48387</td>\n",
       "      <td>5.36364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_since_last_transaction</th>\n",
       "      <td>280</td>\n",
       "      <td>258</td>\n",
       "      <td>298</td>\n",
       "      <td>379</td>\n",
       "      <td>259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    0                    1  \\\n",
       "card_id                               C_ID_00007093c1      C_ID_0001238066   \n",
       "transactions_count                                  2                   26   \n",
       "city_id_nunique                                     2                    8   \n",
       "merchant_category_id_nunique                        2                   15   \n",
       "merchant_id_nunique                                 2                   25   \n",
       "state_id_nunique                                    2                    4   \n",
       "subsector_id_nunique                                2                    9   \n",
       "authorized_flag_Y_mean                              1                    1   \n",
       "authorized_flag_Y_sum                               2                   26   \n",
       "authorized_flag_N_mean                              0                    0   \n",
       "authorized_flag_N_sum                               0                    0   \n",
       "purchase_amount_sum                          -1.32852             -14.8501   \n",
       "purchase_amount_max                         -0.656749           -0.0783182   \n",
       "purchase_amount_min                         -0.671775            -0.740897   \n",
       "purchase_amount_mean                        -0.664262            -0.571156   \n",
       "purchase_amount_std                         0.0106254             0.173436   \n",
       "purchase_amount_median                      -0.664262            -0.649235   \n",
       "installments_sum                                    2                   42   \n",
       "installments_max                                    1                   10   \n",
       "installments_mean                                   1              1.61538   \n",
       "installments_std                                    0              2.07994   \n",
       "installments_median                                 1                    1   \n",
       "month_lag_sum                                       4                   35   \n",
       "month_lag_max                                       2                    2   \n",
       "month_lag_min                                       2                    1   \n",
       "month_lag_mean                                      2              1.34615   \n",
       "month_lag_std                                       0             0.485165   \n",
       "month_lag_median                                    2                    1   \n",
       "purchase_date_amin                2018-04-03 11:13:35  2018-03-01 16:48:27   \n",
       "purchase_date_amax                2018-04-09 16:23:59  2018-04-30 19:57:30   \n",
       "purchase_date_ptp                                   6                   60   \n",
       "card_id_size                                        2                   26   \n",
       "purchased_on_weekend_sum                            0                   12   \n",
       "purchased_on_weekend_mean                           0             0.461538   \n",
       "month_diff_mean                                    11                 10.5   \n",
       "month_diff_std                                      0             0.509902   \n",
       "month_diff_var                                      0                 0.26   \n",
       "category_1_N_sum                                    2                   24   \n",
       "category_1_N_mean                                   1             0.923077   \n",
       "category_1_Y_sum                                    0                    2   \n",
       "category_1_Y_mean                                   0            0.0769231   \n",
       "category_2_mean                                     2              1.46154   \n",
       "category_2_sum                                      4                   38   \n",
       "category_2_na_mean                                  0             0.115385   \n",
       "category_2_na_sum                                   0                    3   \n",
       "category_3_A_mean                                   0                    0   \n",
       "category_3_A_sum                                    0                    0   \n",
       "category_3_B_mean                                   1             0.807692   \n",
       "category_3_B_sum                                    2                   21   \n",
       "category_3_C_mean                                   0             0.153846   \n",
       "category_3_C_sum                                    0                    4   \n",
       "category_2_mean2_mean                       -0.550506            -0.550028   \n",
       "category_2_na_mean2_mean                    -0.561925            -0.539717   \n",
       "category_3_nan_mean2_mean                   -0.568123            -0.544963   \n",
       "category_3_A_mean2_mean                     -0.480041            -0.480041   \n",
       "category_3_B_mean2_mean                     -0.606486            -0.587887   \n",
       "category_3_C_mean2_mean                     -0.599212            -0.501224   \n",
       "inverse_avg_transactions_per_day                    3              2.30769   \n",
       "days_since_last_transaction                       280                  258   \n",
       "\n",
       "                                                    2                    3  \\\n",
       "card_id                               C_ID_0001506ef0      C_ID_0001793786   \n",
       "transactions_count                                  2                   31   \n",
       "city_id_nunique                                     1                    7   \n",
       "merchant_category_id_nunique                        2                   21   \n",
       "merchant_id_nunique                                 1                   31   \n",
       "state_id_nunique                                    1                    5   \n",
       "subsector_id_nunique                                2                   14   \n",
       "authorized_flag_Y_mean                              1                    1   \n",
       "authorized_flag_Y_sum                               2                   31   \n",
       "authorized_flag_N_mean                              0                    0   \n",
       "authorized_flag_N_sum                               0                    0   \n",
       "purchase_amount_sum                          -1.44735             -0.22962   \n",
       "purchase_amount_max                         -0.715352              3.12993   \n",
       "purchase_amount_min                         -0.732001            -0.737892   \n",
       "purchase_amount_mean                        -0.723677           -0.0074071   \n",
       "purchase_amount_std                         0.0117729             0.947223   \n",
       "purchase_amount_median                      -0.723677            -0.372748   \n",
       "installments_sum                                    0                    0   \n",
       "installments_max                                    0                    0   \n",
       "installments_mean                                   0                    0   \n",
       "installments_std                                    0                    0   \n",
       "installments_median                                 0                    0   \n",
       "month_lag_sum                                       2                   41   \n",
       "month_lag_max                                       1                    2   \n",
       "month_lag_min                                       1                    1   \n",
       "month_lag_mean                                      1              1.32258   \n",
       "month_lag_std                                       0             0.475191   \n",
       "month_lag_median                                    1                    1   \n",
       "purchase_date_amin                2018-03-16 22:21:58  2017-11-15 15:44:20   \n",
       "purchase_date_amax                2018-03-22 09:14:30  2017-12-31 17:35:56   \n",
       "purchase_date_ptp                                   5                   46   \n",
       "card_id_size                                        2                   31   \n",
       "purchased_on_weekend_sum                            0                   14   \n",
       "purchased_on_weekend_mean                           0             0.451613   \n",
       "month_diff_mean                                  10.5              14.2903   \n",
       "month_diff_std                               0.707107             0.461414   \n",
       "month_diff_var                                    0.5             0.212903   \n",
       "category_1_N_sum                                    2                   31   \n",
       "category_1_N_mean                                   1                    1   \n",
       "category_1_Y_sum                                    0                    0   \n",
       "category_1_Y_mean                                   0                    0   \n",
       "category_2_mean                                     3              1.70968   \n",
       "category_2_sum                                      6                   53   \n",
       "category_2_na_mean                                  0            0.0645161   \n",
       "category_2_na_sum                                   0                    2   \n",
       "category_3_A_mean                                   1                    1   \n",
       "category_3_A_sum                                    2                   31   \n",
       "category_3_B_mean                                   0                    0   \n",
       "category_3_B_sum                                    0                    0   \n",
       "category_3_C_mean                                   0                    0   \n",
       "category_3_C_sum                                    0                    0   \n",
       "category_2_mean2_mean                       -0.550852            -0.551649   \n",
       "category_2_na_mean2_mean                    -0.561925            -0.549508   \n",
       "category_3_nan_mean2_mean                   -0.568123            -0.568123   \n",
       "category_3_A_mean2_mean                     -0.631014            -0.631014   \n",
       "category_3_B_mean2_mean                     -0.509773            -0.509773   \n",
       "category_3_C_mean2_mean                     -0.599212            -0.599212   \n",
       "inverse_avg_transactions_per_day                  2.5              1.48387   \n",
       "days_since_last_transaction                       298                  379   \n",
       "\n",
       "                                                    4  \n",
       "card_id                               C_ID_000183fdda  \n",
       "transactions_count                                 11  \n",
       "city_id_nunique                                     2  \n",
       "merchant_category_id_nunique                        9  \n",
       "merchant_id_nunique                                11  \n",
       "state_id_nunique                                    2  \n",
       "subsector_id_nunique                                6  \n",
       "authorized_flag_Y_mean                              1  \n",
       "authorized_flag_Y_sum                              11  \n",
       "authorized_flag_N_mean                              0  \n",
       "authorized_flag_N_sum                               0  \n",
       "purchase_amount_sum                          -6.59078  \n",
       "purchase_amount_max                          -0.10768  \n",
       "purchase_amount_min                         -0.732332  \n",
       "purchase_amount_mean                        -0.599162  \n",
       "purchase_amount_std                          0.182877  \n",
       "purchase_amount_median                      -0.665765  \n",
       "installments_sum                                   16  \n",
       "installments_max                                    4  \n",
       "installments_mean                             1.45455  \n",
       "installments_std                              1.29334  \n",
       "installments_median                                 1  \n",
       "month_lag_sum                                      14  \n",
       "month_lag_max                                       2  \n",
       "month_lag_min                                       1  \n",
       "month_lag_mean                                1.27273  \n",
       "month_lag_std                                0.467099  \n",
       "month_lag_median                                    1  \n",
       "purchase_date_amin                2018-03-02 12:26:26  \n",
       "purchase_date_amax                2018-04-30 14:59:53  \n",
       "purchase_date_ptp                                  59  \n",
       "card_id_size                                       11  \n",
       "purchased_on_weekend_sum                            2  \n",
       "purchased_on_weekend_mean                    0.181818  \n",
       "month_diff_mean                               10.7273  \n",
       "month_diff_std                               0.467099  \n",
       "month_diff_var                               0.218182  \n",
       "category_1_N_sum                                   11  \n",
       "category_1_N_mean                                   1  \n",
       "category_1_Y_sum                                    0  \n",
       "category_1_Y_mean                                   0  \n",
       "category_2_mean                                     3  \n",
       "category_2_sum                                     33  \n",
       "category_2_na_mean                                  0  \n",
       "category_2_na_sum                                   0  \n",
       "category_3_A_mean                                   0  \n",
       "category_3_A_sum                                    0  \n",
       "category_3_B_mean                            0.545455  \n",
       "category_3_B_sum                                    6  \n",
       "category_3_C_mean                            0.363636  \n",
       "category_3_C_sum                                    4  \n",
       "category_2_mean2_mean                       -0.550852  \n",
       "category_2_na_mean2_mean                    -0.561925  \n",
       "category_3_nan_mean2_mean                   -0.513382  \n",
       "category_3_A_mean2_mean                     -0.480041  \n",
       "category_3_B_mean2_mean                     -0.562525  \n",
       "category_3_C_mean2_mean                     -0.367604  \n",
       "inverse_avg_transactions_per_day              5.36364  \n",
       "days_since_last_transaction                       259  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_trans_agg_proc.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train, new_trans_agg_proc, left_on='card_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = join_dfs(train_df, hist_trans_agg, left_on='card_id', suffix='_old')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df = join_dfs(test, new_hist_trans_agg, left_on='card_id')\n",
    "test_df = join_dfs(test_df, hist_trans_agg, left_on='card_id', suffix='_old')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((123623, 134), (201917, 135))"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df.shape, train_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>first_active_month</th>\n",
       "      <td>2017-06-01 00:00:00</td>\n",
       "      <td>2017-01-01 00:00:00</td>\n",
       "      <td>2016-08-01 00:00:00</td>\n",
       "      <td>2017-09-01 00:00:00</td>\n",
       "      <td>2017-11-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_92a2005557</td>\n",
       "      <td>C_ID_3d0044924f</td>\n",
       "      <td>C_ID_d639edf6cd</td>\n",
       "      <td>C_ID_186d6a6901</td>\n",
       "      <td>C_ID_cdbd2c0db2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_1</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_3</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>target</th>\n",
       "      <td>-0.820283</td>\n",
       "      <td>0.392913</td>\n",
       "      <td>0.688056</td>\n",
       "      <td>0.142495</td>\n",
       "      <td>-0.159749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthYear</th>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthMonth</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthWeek</th>\n",
       "      <td>22</td>\n",
       "      <td>52</td>\n",
       "      <td>31</td>\n",
       "      <td>35</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthDay</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthDayofweek</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthDayofyear</th>\n",
       "      <td>152</td>\n",
       "      <td>1</td>\n",
       "      <td>214</td>\n",
       "      <td>244</td>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_month_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_month_start</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_quarter_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_quarter_start</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_year_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthIs_year_start</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_active_monthElapsed</th>\n",
       "      <td>1496275200</td>\n",
       "      <td>1483228800</td>\n",
       "      <td>1470009600</td>\n",
       "      <td>1504224000</td>\n",
       "      <td>1509494400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transactions_count</th>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_nunique</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id_nunique</th>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id_nunique</th>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id_nunique</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id_nunique</th>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_Y_mean</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>authorized_flag_Y_sum</th>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_N_mean</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>authorized_flag_N_sum</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</th>\n",
       "      <td>-13.2442</td>\n",
       "      <td>-4.35573</td>\n",
       "      <td>-0.700326</td>\n",
       "      <td>-4.65437</td>\n",
       "      <td>-19.9262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_amax_old</th>\n",
       "      <td>2018-02-25 09:31:15</td>\n",
       "      <td>2018-01-31 22:31:09</td>\n",
       "      <td>2018-02-27 19:08:25</td>\n",
       "      <td>2018-02-28 11:44:40</td>\n",
       "      <td>2018-02-28 20:40:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_ptp_old</th>\n",
       "      <td>242</td>\n",
       "      <td>390</td>\n",
       "      <td>412</td>\n",
       "      <td>154</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_size_old</th>\n",
       "      <td>260</td>\n",
       "      <td>350</td>\n",
       "      <td>43</td>\n",
       "      <td>77</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_sum_old</th>\n",
       "      <td>90</td>\n",
       "      <td>132</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_mean_old</th>\n",
       "      <td>0.346154</td>\n",
       "      <td>0.377143</td>\n",
       "      <td>0.255814</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.315789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_mean_old</th>\n",
       "      <td>10.7231</td>\n",
       "      <td>11.6771</td>\n",
       "      <td>10.6279</td>\n",
       "      <td>10.6104</td>\n",
       "      <td>10.5714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_std_old</th>\n",
       "      <td>0.448341</td>\n",
       "      <td>0.468238</td>\n",
       "      <td>0.489083</td>\n",
       "      <td>0.49086</td>\n",
       "      <td>0.496743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_var_old</th>\n",
       "      <td>0.20101</td>\n",
       "      <td>0.219247</td>\n",
       "      <td>0.239203</td>\n",
       "      <td>0.240943</td>\n",
       "      <td>0.246753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_N_sum_old</th>\n",
       "      <td>260</td>\n",
       "      <td>319</td>\n",
       "      <td>43</td>\n",
       "      <td>65</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_N_mean_old</th>\n",
       "      <td>1</td>\n",
       "      <td>0.911429</td>\n",
       "      <td>1</td>\n",
       "      <td>0.844156</td>\n",
       "      <td>0.887218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_sum_old</th>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_Y_mean_old</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0885714</td>\n",
       "      <td>0</td>\n",
       "      <td>0.155844</td>\n",
       "      <td>0.112782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_mean_old</th>\n",
       "      <td>1.04615</td>\n",
       "      <td>1</td>\n",
       "      <td>4.62791</td>\n",
       "      <td>3.06494</td>\n",
       "      <td>3.44361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_sum_old</th>\n",
       "      <td>272</td>\n",
       "      <td>350</td>\n",
       "      <td>199</td>\n",
       "      <td>236</td>\n",
       "      <td>458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_mean_old</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0885714</td>\n",
       "      <td>0</td>\n",
       "      <td>0.155844</td>\n",
       "      <td>0.112782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_sum_old</th>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_mean_old</th>\n",
       "      <td>0.984615</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_sum_old</th>\n",
       "      <td>256</td>\n",
       "      <td>0</td>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_mean_old</th>\n",
       "      <td>0.0153846</td>\n",
       "      <td>0.788571</td>\n",
       "      <td>0</td>\n",
       "      <td>0.883117</td>\n",
       "      <td>0.947368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_sum_old</th>\n",
       "      <td>4</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>68</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_mean_old</th>\n",
       "      <td>0</td>\n",
       "      <td>0.205714</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0909091</td>\n",
       "      <td>0.0526316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_sum_old</th>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_mean2_mean_old</th>\n",
       "      <td>0.0725022</td>\n",
       "      <td>0.0745679</td>\n",
       "      <td>-0.0878033</td>\n",
       "      <td>-0.0861658</td>\n",
       "      <td>-0.114647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_na_mean2_mean_old</th>\n",
       "      <td>0.0755954</td>\n",
       "      <td>0.0374992</td>\n",
       "      <td>0.0755954</td>\n",
       "      <td>0.00856402</td>\n",
       "      <td>0.0270859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_nan_mean2_mean_old</th>\n",
       "      <td>0.0362652</td>\n",
       "      <td>0.0363919</td>\n",
       "      <td>0.0362652</td>\n",
       "      <td>0.0368413</td>\n",
       "      <td>0.0362652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_A_mean2_mean_old</th>\n",
       "      <td>0.351284</td>\n",
       "      <td>-0.329781</td>\n",
       "      <td>0.361926</td>\n",
       "      <td>-0.329781</td>\n",
       "      <td>-0.329781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_B_mean2_mean_old</th>\n",
       "      <td>0.320418</td>\n",
       "      <td>-0.248881</td>\n",
       "      <td>0.331746</td>\n",
       "      <td>-0.318495</td>\n",
       "      <td>-0.365803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_C_mean2_mean_old</th>\n",
       "      <td>0.0316902</td>\n",
       "      <td>0.0469815</td>\n",
       "      <td>0.0316902</td>\n",
       "      <td>0.0384477</td>\n",
       "      <td>0.0356025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>inverse_avg_transactions_per_day_old</th>\n",
       "      <td>0.930769</td>\n",
       "      <td>1.11429</td>\n",
       "      <td>9.5814</td>\n",
       "      <td>2</td>\n",
       "      <td>0.81203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days_since_last_transaction_old</th>\n",
       "      <td>323</td>\n",
       "      <td>347</td>\n",
       "      <td>320</td>\n",
       "      <td>320</td>\n",
       "      <td>319</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>135 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        0  \\\n",
       "first_active_month                    2017-06-01 00:00:00   \n",
       "card_id                                   C_ID_92a2005557   \n",
       "feature_1                                               5   \n",
       "feature_2                                               2   \n",
       "feature_3                                               1   \n",
       "target                                          -0.820283   \n",
       "first_active_monthYear                               2017   \n",
       "first_active_monthMonth                                 6   \n",
       "first_active_monthWeek                                 22   \n",
       "first_active_monthDay                                   1   \n",
       "first_active_monthDayofweek                             3   \n",
       "first_active_monthDayofyear                           152   \n",
       "first_active_monthIs_month_end                      False   \n",
       "first_active_monthIs_month_start                     True   \n",
       "first_active_monthIs_quarter_end                    False   \n",
       "first_active_monthIs_quarter_start                  False   \n",
       "first_active_monthIs_year_end                       False   \n",
       "first_active_monthIs_year_start                     False   \n",
       "first_active_monthElapsed                      1496275200   \n",
       "transactions_count                                     23   \n",
       "city_id_nunique                                         3   \n",
       "merchant_category_id_nunique                           14   \n",
       "merchant_id_nunique                                    23   \n",
       "state_id_nunique                                        1   \n",
       "subsector_id_nunique                                   10   \n",
       "authorized_flag_Y_mean                                  1   \n",
       "authorized_flag_Y_sum                                  23   \n",
       "authorized_flag_N_mean                                  0   \n",
       "authorized_flag_N_sum                                   0   \n",
       "purchase_amount_sum                              -13.2442   \n",
       "...                                                   ...   \n",
       "purchase_date_amax_old                2018-02-25 09:31:15   \n",
       "purchase_date_ptp_old                                 242   \n",
       "card_id_size_old                                      260   \n",
       "purchased_on_weekend_sum_old                           90   \n",
       "purchased_on_weekend_mean_old                    0.346154   \n",
       "month_diff_mean_old                               10.7231   \n",
       "month_diff_std_old                               0.448341   \n",
       "month_diff_var_old                                0.20101   \n",
       "category_1_N_sum_old                                  260   \n",
       "category_1_N_mean_old                                   1   \n",
       "category_1_Y_sum_old                                    0   \n",
       "category_1_Y_mean_old                                   0   \n",
       "category_2_mean_old                               1.04615   \n",
       "category_2_sum_old                                    272   \n",
       "category_2_na_mean_old                                  0   \n",
       "category_2_na_sum_old                                   0   \n",
       "category_3_A_mean_old                            0.984615   \n",
       "category_3_A_sum_old                                  256   \n",
       "category_3_B_mean_old                           0.0153846   \n",
       "category_3_B_sum_old                                    4   \n",
       "category_3_C_mean_old                                   0   \n",
       "category_3_C_sum_old                                    0   \n",
       "category_2_mean2_mean_old                       0.0725022   \n",
       "category_2_na_mean2_mean_old                    0.0755954   \n",
       "category_3_nan_mean2_mean_old                   0.0362652   \n",
       "category_3_A_mean2_mean_old                      0.351284   \n",
       "category_3_B_mean2_mean_old                      0.320418   \n",
       "category_3_C_mean2_mean_old                     0.0316902   \n",
       "inverse_avg_transactions_per_day_old             0.930769   \n",
       "days_since_last_transaction_old                       323   \n",
       "\n",
       "                                                        1  \\\n",
       "first_active_month                    2017-01-01 00:00:00   \n",
       "card_id                                   C_ID_3d0044924f   \n",
       "feature_1                                               4   \n",
       "feature_2                                               1   \n",
       "feature_3                                               0   \n",
       "target                                           0.392913   \n",
       "first_active_monthYear                               2017   \n",
       "first_active_monthMonth                                 1   \n",
       "first_active_monthWeek                                 52   \n",
       "first_active_monthDay                                   1   \n",
       "first_active_monthDayofweek                             6   \n",
       "first_active_monthDayofyear                             1   \n",
       "first_active_monthIs_month_end                      False   \n",
       "first_active_monthIs_month_start                     True   \n",
       "first_active_monthIs_quarter_end                    False   \n",
       "first_active_monthIs_quarter_start                   True   \n",
       "first_active_monthIs_year_end                       False   \n",
       "first_active_monthIs_year_start                      True   \n",
       "first_active_monthElapsed                      1483228800   \n",
       "transactions_count                                      6   \n",
       "city_id_nunique                                         1   \n",
       "merchant_category_id_nunique                            5   \n",
       "merchant_id_nunique                                     6   \n",
       "state_id_nunique                                        1   \n",
       "subsector_id_nunique                                    4   \n",
       "authorized_flag_Y_mean                                  1   \n",
       "authorized_flag_Y_sum                                   6   \n",
       "authorized_flag_N_mean                                  0   \n",
       "authorized_flag_N_sum                                   0   \n",
       "purchase_amount_sum                              -4.35573   \n",
       "...                                                   ...   \n",
       "purchase_date_amax_old                2018-01-31 22:31:09   \n",
       "purchase_date_ptp_old                                 390   \n",
       "card_id_size_old                                      350   \n",
       "purchased_on_weekend_sum_old                          132   \n",
       "purchased_on_weekend_mean_old                    0.377143   \n",
       "month_diff_mean_old                               11.6771   \n",
       "month_diff_std_old                               0.468238   \n",
       "month_diff_var_old                               0.219247   \n",
       "category_1_N_sum_old                                  319   \n",
       "category_1_N_mean_old                            0.911429   \n",
       "category_1_Y_sum_old                                   31   \n",
       "category_1_Y_mean_old                           0.0885714   \n",
       "category_2_mean_old                                     1   \n",
       "category_2_sum_old                                    350   \n",
       "category_2_na_mean_old                          0.0885714   \n",
       "category_2_na_sum_old                                  31   \n",
       "category_3_A_mean_old                                   0   \n",
       "category_3_A_sum_old                                    0   \n",
       "category_3_B_mean_old                            0.788571   \n",
       "category_3_B_sum_old                                  276   \n",
       "category_3_C_mean_old                            0.205714   \n",
       "category_3_C_sum_old                                   72   \n",
       "category_2_mean2_mean_old                       0.0745679   \n",
       "category_2_na_mean2_mean_old                    0.0374992   \n",
       "category_3_nan_mean2_mean_old                   0.0363919   \n",
       "category_3_A_mean2_mean_old                     -0.329781   \n",
       "category_3_B_mean2_mean_old                     -0.248881   \n",
       "category_3_C_mean2_mean_old                     0.0469815   \n",
       "inverse_avg_transactions_per_day_old              1.11429   \n",
       "days_since_last_transaction_old                       347   \n",
       "\n",
       "                                                        2  \\\n",
       "first_active_month                    2016-08-01 00:00:00   \n",
       "card_id                                   C_ID_d639edf6cd   \n",
       "feature_1                                               2   \n",
       "feature_2                                               2   \n",
       "feature_3                                               0   \n",
       "target                                           0.688056   \n",
       "first_active_monthYear                               2016   \n",
       "first_active_monthMonth                                 8   \n",
       "first_active_monthWeek                                 31   \n",
       "first_active_monthDay                                   1   \n",
       "first_active_monthDayofweek                             0   \n",
       "first_active_monthDayofyear                           214   \n",
       "first_active_monthIs_month_end                      False   \n",
       "first_active_monthIs_month_start                     True   \n",
       "first_active_monthIs_quarter_end                    False   \n",
       "first_active_monthIs_quarter_start                  False   \n",
       "first_active_monthIs_year_end                       False   \n",
       "first_active_monthIs_year_start                     False   \n",
       "first_active_monthElapsed                      1470009600   \n",
       "transactions_count                                      1   \n",
       "city_id_nunique                                         1   \n",
       "merchant_category_id_nunique                            1   \n",
       "merchant_id_nunique                                     1   \n",
       "state_id_nunique                                        1   \n",
       "subsector_id_nunique                                    1   \n",
       "authorized_flag_Y_mean                                  1   \n",
       "authorized_flag_Y_sum                                   1   \n",
       "authorized_flag_N_mean                                  0   \n",
       "authorized_flag_N_sum                                   0   \n",
       "purchase_amount_sum                             -0.700326   \n",
       "...                                                   ...   \n",
       "purchase_date_amax_old                2018-02-27 19:08:25   \n",
       "purchase_date_ptp_old                                 412   \n",
       "card_id_size_old                                       43   \n",
       "purchased_on_weekend_sum_old                           11   \n",
       "purchased_on_weekend_mean_old                    0.255814   \n",
       "month_diff_mean_old                               10.6279   \n",
       "month_diff_std_old                               0.489083   \n",
       "month_diff_var_old                               0.239203   \n",
       "category_1_N_sum_old                                   43   \n",
       "category_1_N_mean_old                                   1   \n",
       "category_1_Y_sum_old                                    0   \n",
       "category_1_Y_mean_old                                   0   \n",
       "category_2_mean_old                               4.62791   \n",
       "category_2_sum_old                                    199   \n",
       "category_2_na_mean_old                                  0   \n",
       "category_2_na_sum_old                                   0   \n",
       "category_3_A_mean_old                                   1   \n",
       "category_3_A_sum_old                                   43   \n",
       "category_3_B_mean_old                                   0   \n",
       "category_3_B_sum_old                                    0   \n",
       "category_3_C_mean_old                                   0   \n",
       "category_3_C_sum_old                                    0   \n",
       "category_2_mean2_mean_old                      -0.0878033   \n",
       "category_2_na_mean2_mean_old                    0.0755954   \n",
       "category_3_nan_mean2_mean_old                   0.0362652   \n",
       "category_3_A_mean2_mean_old                      0.361926   \n",
       "category_3_B_mean2_mean_old                      0.331746   \n",
       "category_3_C_mean2_mean_old                     0.0316902   \n",
       "inverse_avg_transactions_per_day_old               9.5814   \n",
       "days_since_last_transaction_old                       320   \n",
       "\n",
       "                                                        3                    4  \n",
       "first_active_month                    2017-09-01 00:00:00  2017-11-01 00:00:00  \n",
       "card_id                                   C_ID_186d6a6901      C_ID_cdbd2c0db2  \n",
       "feature_1                                               4                    1  \n",
       "feature_2                                               3                    3  \n",
       "feature_3                                               0                    0  \n",
       "target                                           0.142495            -0.159749  \n",
       "first_active_monthYear                               2017                 2017  \n",
       "first_active_monthMonth                                 9                   11  \n",
       "first_active_monthWeek                                 35                   44  \n",
       "first_active_monthDay                                   1                    1  \n",
       "first_active_monthDayofweek                             4                    2  \n",
       "first_active_monthDayofyear                           244                  305  \n",
       "first_active_monthIs_month_end                      False                False  \n",
       "first_active_monthIs_month_start                     True                 True  \n",
       "first_active_monthIs_quarter_end                    False                False  \n",
       "first_active_monthIs_quarter_start                  False                False  \n",
       "first_active_monthIs_year_end                       False                False  \n",
       "first_active_monthIs_year_start                     False                False  \n",
       "first_active_monthElapsed                      1504224000           1509494400  \n",
       "transactions_count                                      7                   36  \n",
       "city_id_nunique                                         2                    5  \n",
       "merchant_category_id_nunique                            6                   17  \n",
       "merchant_id_nunique                                     7                   36  \n",
       "state_id_nunique                                        2                    5  \n",
       "subsector_id_nunique                                    5                   10  \n",
       "authorized_flag_Y_mean                                  1                    1  \n",
       "authorized_flag_Y_sum                                   7                   36  \n",
       "authorized_flag_N_mean                                  0                    0  \n",
       "authorized_flag_N_sum                                   0                    0  \n",
       "purchase_amount_sum                              -4.65437             -19.9262  \n",
       "...                                                   ...                  ...  \n",
       "purchase_date_amax_old                2018-02-28 11:44:40  2018-02-28 20:40:41  \n",
       "purchase_date_ptp_old                                 154                  108  \n",
       "card_id_size_old                                       77                  133  \n",
       "purchased_on_weekend_sum_old                           11                   42  \n",
       "purchased_on_weekend_mean_old                    0.142857             0.315789  \n",
       "month_diff_mean_old                               10.6104              10.5714  \n",
       "month_diff_std_old                                0.49086             0.496743  \n",
       "month_diff_var_old                               0.240943             0.246753  \n",
       "category_1_N_sum_old                                   65                  118  \n",
       "category_1_N_mean_old                            0.844156             0.887218  \n",
       "category_1_Y_sum_old                                   12                   15  \n",
       "category_1_Y_mean_old                            0.155844             0.112782  \n",
       "category_2_mean_old                               3.06494              3.44361  \n",
       "category_2_sum_old                                    236                  458  \n",
       "category_2_na_mean_old                           0.155844             0.112782  \n",
       "category_2_na_sum_old                                  12                   15  \n",
       "category_3_A_mean_old                                   0                    0  \n",
       "category_3_A_sum_old                                    0                    0  \n",
       "category_3_B_mean_old                            0.883117             0.947368  \n",
       "category_3_B_sum_old                                   68                  126  \n",
       "category_3_C_mean_old                           0.0909091            0.0526316  \n",
       "category_3_C_sum_old                                    7                    7  \n",
       "category_2_mean2_mean_old                      -0.0861658            -0.114647  \n",
       "category_2_na_mean2_mean_old                   0.00856402            0.0270859  \n",
       "category_3_nan_mean2_mean_old                   0.0368413            0.0362652  \n",
       "category_3_A_mean2_mean_old                     -0.329781            -0.329781  \n",
       "category_3_B_mean2_mean_old                     -0.318495            -0.365803  \n",
       "category_3_C_mean2_mean_old                     0.0384477            0.0356025  \n",
       "inverse_avg_transactions_per_day_old                    2              0.81203  \n",
       "days_since_last_transaction_old                       320                  319  \n",
       "\n",
       "[135 rows x 5 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add extra columns like age, total transactions, installments, purchase amount, first buy etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['first_active_month', 'card_id', 'feature_1', 'feature_2', 'feature_3', 'target',\n",
       "       'first_active_monthYear', 'first_active_monthMonth', 'first_active_monthWeek',\n",
       "       'first_active_monthDay', 'first_active_monthDayofweek', 'first_active_monthDayofyear',\n",
       "       'first_active_monthIs_month_end', 'first_active_monthIs_month_start',\n",
       "       'first_active_monthIs_quarter_end', 'first_active_monthIs_quarter_start',\n",
       "       'first_active_monthIs_year_end', 'first_active_monthIs_year_start', 'first_active_monthElapsed',\n",
       "       'transactions_count', 'city_id_nunique', 'merchant_category_id_nunique', 'merchant_id_nunique',\n",
       "       'state_id_nunique', 'subsector_id_nunique', 'authorized_flag_Y_mean', 'authorized_flag_Y_sum',\n",
       "       'authorized_flag_N_mean', 'authorized_flag_N_sum', 'purchase_amount_sum', 'purchase_amount_max',\n",
       "       'purchase_amount_min', 'purchase_amount_mean', 'purchase_amount_std', 'purchase_amount_median',\n",
       "       'installments_sum', 'installments_max', 'installments_mean', 'installments_std',\n",
       "       'installments_median', 'month_lag_sum', 'month_lag_max', 'month_lag_min', 'month_lag_mean',\n",
       "       'month_lag_std', 'month_lag_median', 'purchase_date_amin', 'purchase_date_amax', 'purchase_date_ptp',\n",
       "       'card_id_size', 'purchased_on_weekend_sum', 'purchased_on_weekend_mean', 'month_diff_mean',\n",
       "       'month_diff_std', 'month_diff_var', 'category_1_N_sum', 'category_1_N_mean', 'category_1_Y_sum',\n",
       "       'category_1_Y_mean', 'category_2_mean', 'category_2_sum', 'category_2_na_mean', 'category_2_na_sum',\n",
       "       'category_3_A_mean', 'category_3_A_sum', 'category_3_B_mean', 'category_3_B_sum', 'category_3_C_mean',\n",
       "       'category_3_C_sum', 'category_2_mean2_mean', 'category_2_na_mean2_mean', 'category_3_nan_mean2_mean',\n",
       "       'category_3_A_mean2_mean', 'category_3_B_mean2_mean', 'category_3_C_mean2_mean',\n",
       "       'inverse_avg_transactions_per_day', 'days_since_last_transaction', 'transactions_count_old',\n",
       "       'city_id_nunique_old', 'merchant_category_id_nunique_old', 'merchant_id_nunique_old',\n",
       "       'state_id_nunique_old', 'subsector_id_nunique_old', 'authorized_flag_Y_mean_old',\n",
       "       'authorized_flag_Y_sum_old', 'authorized_flag_N_mean_old', 'authorized_flag_N_sum_old',\n",
       "       'purchase_amount_sum_old', 'purchase_amount_max_old', 'purchase_amount_min_old',\n",
       "       'purchase_amount_mean_old', 'purchase_amount_std_old', 'purchase_amount_median_old',\n",
       "       'installments_sum_old', 'installments_max_old', 'installments_mean_old', 'installments_std_old',\n",
       "       'installments_median_old', 'month_lag_sum_old', 'month_lag_max_old', 'month_lag_min_old',\n",
       "       'month_lag_mean_old', 'month_lag_std_old', 'month_lag_median_old', 'purchase_date_amin_old',\n",
       "       'purchase_date_amax_old', 'purchase_date_ptp_old', 'card_id_size_old', 'purchased_on_weekend_sum_old',\n",
       "       'purchased_on_weekend_mean_old', 'month_diff_mean_old', 'month_diff_std_old', 'month_diff_var_old',\n",
       "       'category_1_N_sum_old', 'category_1_N_mean_old', 'category_1_Y_sum_old', 'category_1_Y_mean_old',\n",
       "       'category_2_mean_old', 'category_2_sum_old', 'category_2_na_mean_old', 'category_2_na_sum_old',\n",
       "       'category_3_A_mean_old', 'category_3_A_sum_old', 'category_3_B_mean_old', 'category_3_B_sum_old',\n",
       "       'category_3_C_mean_old', 'category_3_C_sum_old', 'category_2_mean2_mean_old',\n",
       "       'category_2_na_mean2_mean_old', 'category_3_nan_mean2_mean_old', 'category_3_A_mean2_mean_old',\n",
       "       'category_3_B_mean2_mean_old', 'category_3_C_mean2_mean_old', 'inverse_avg_transactions_per_day_old',\n",
       "       'days_since_last_transaction_old'], dtype=object)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(train_df.columns.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in [train_df, test_df]:\n",
    "    df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n",
    "    df['card_id_total'] = df['card_id_size']+df['card_id_size_old']\n",
    "    df[\"authorized_flag_Y_mean_total\"]= df[\"authorized_flag_Y_mean_old\"]+ df[\"authorized_flag_Y_mean\"]\n",
    "    df[\"authorized_flag_Y_sum_total\"]= df[\"authorized_flag_Y_sum_old\"]+ df[\"authorized_flag_Y_sum\"]\n",
    "    df[\"authorized_flag_N_mean_total\"]= df[\"authorized_flag_N_mean_old\"]+ df[\"authorized_flag_N_mean\"]\n",
    "    df[\"authorized_flag_N_sum_total\"]= df[\"authorized_flag_N_sum_old\"]+ df[\"authorized_flag_N_sum\"]\n",
    "    df[\"month_lag_sum_total\"]= df[\"month_lag_sum_old\"]+ df[\"month_lag_sum\"]\n",
    "    df['purchase_amount_total'] = df['purchase_amount_sum']+df['purchase_amount_sum_old']\n",
    "    df['installments_total'] = df['installments_sum'] + df['installments_sum_old']\n",
    "    df[\"category_1_Y_sum_total\"] = df[\"category_1_Y_sum_old\"]+ df[\"category_1_Y_sum\"]\n",
    "    df[\"category_2_sum_total\"]= df[\"category_2_sum_old\"]+ df[\"category_2_sum\"]\n",
    "    df[\"category_3_A_total\"]= df[\"category_3_A_sum\"]+ df[\"category_3_A_sum_old\"]\n",
    "    df[\"category_3_B_total\"]= df[\"category_3_B_sum\"]+ df[\"category_3_B_sum_old\"]\n",
    "    df[\"category_3_C_total\"]= df[\"category_3_C_sum\"]+ df[\"category_3_C_sum_old\"]\n",
    "    df['hist_first_buy'] = (df['purchase_date_amin_old'] - df['first_active_month']).dt.days\n",
    "    df['new_first_buy'] = (df['purchase_date_amin'] - df['first_active_month']).dt.days"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mark the outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    199710\n",
       "1      2207\n",
       "Name: outliers, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df['outliers'] = 0\n",
    "train_df.loc[train_df['target'] < -30, 'outliers'] = 1\n",
    "train_df['outliers'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Target encode the outliers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "for f in ['feature_1','feature_2','feature_3']:\n",
    "    order_label = train_df.groupby([f])['outliers'].mean()\n",
    "    train_df[f] = train_df[f].map(order_label)\n",
    "    test_df[f] = test_df[f].map(order_label)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.to_feather('train_df_proc')\n",
    "test_df.to_feather('test_df_proc')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}