Using Feature tools.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"></ul></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.notebook.set_autosave_interval(120000)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Autosaving every 120 seconds\n"
     ]
    }
   ],
   "source": [
    "%matplotlib inline\n",
    "%load_ext autoreload\n",
    "%autoreload 4\n",
    "%autosave 120"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "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": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_trans = feather.read_dataframe('hist_trans')\n",
    "new_hist_trans = feather.read_dataframe('new_hist_trans')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(new_hist_trans).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    " import featuretools as ft"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'data/elo/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "train, test = [pd.read_csv(f'{PATH}{c}') for c in ['train.csv', 'test.csv']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>first_active_month</th>\n",
       "      <td>2017-06</td>\n",
       "      <td>2017-01</td>\n",
       "      <td>2016-08</td>\n",
       "      <td>2017-09</td>\n",
       "      <td>2017-11</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  0                1                2  \\\n",
       "first_active_month          2017-06          2017-01          2016-08   \n",
       "card_id             C_ID_92a2005557  C_ID_3d0044924f  C_ID_d639edf6cd   \n",
       "feature_1                         5                4                2   \n",
       "feature_2                         2                1                2   \n",
       "feature_3                         1                0                0   \n",
       "target                    -0.820283         0.392913         0.688056   \n",
       "\n",
       "                                  3                4  \n",
       "first_active_month          2017-09          2017-11  \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  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>authorized_flag</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>11</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                              1                    1   \n",
       "card_id                        C_ID_4e6213e9bc      C_ID_4e6213e9bc   \n",
       "city_id                                     88                   88   \n",
       "category_1                                   0                    0   \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                                  11                   11   \n",
       "\n",
       "                                             2                    3  \\\n",
       "authorized_flag                              1                    1   \n",
       "card_id                        C_ID_4e6213e9bc      C_ID_4e6213e9bc   \n",
       "city_id                                     88                   88   \n",
       "category_1                                   0                    0   \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                              1  \n",
       "card_id                        C_ID_4e6213e9bc  \n",
       "city_id                                     88  \n",
       "category_1                                   0  \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": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = hist_trans.loc[hist_trans['card_id'].isin(['C_ID_92a2005557'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['authorized_flag', 'card_id', 'city_id', 'category_1', 'installments', 'category_3',\n",
       "       'merchant_category_id', 'merchant_id', 'month_lag', 'purchase_amount', 'purchase_date', 'category_2',\n",
       "       'state_id', 'subsector_id', 'purchase_Year', 'purchase_Month', 'purchase_Week', 'purchase_Day',\n",
       "       'purchase_Dayofweek', 'purchase_Dayofyear', 'purchase_Is_month_end', 'purchase_Is_month_start',\n",
       "       'purchase_Is_quarter_end', 'purchase_Is_quarter_start', 'purchase_Is_year_end',\n",
       "       'purchase_Is_year_start', 'purchase_Elapsed', 'purchased_on_weekend', 'month_diff'], dtype=object)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp.drop(['purchase_Year', 'purchase_Month', 'purchase_Week', 'purchase_Day',\n",
    "       'purchase_Dayofweek', 'purchase_Dayofyear', 'purchase_Is_month_end', 'purchase_Is_month_start',\n",
    "       'purchase_Is_quarter_end', 'purchase_Is_quarter_start', 'purchase_Is_year_end',\n",
    "       'purchase_Is_year_start', 'purchase_Elapsed'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['authorized_flag', 'card_id', 'city_id', 'category_1', 'installments', 'category_3',\n",
       "       'merchant_category_id', 'merchant_id', 'month_lag', 'purchase_amount', 'purchase_date', 'category_2',\n",
       "       'state_id', 'subsector_id', 'purchased_on_weekend', 'month_diff'], dtype=object)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_cols = ['authorized_flag', 'card_id', 'city_id', 'category_1', 'category_3',\n",
    "       'merchant_category_id', 'merchant_id', 'category_2',\n",
    "       'state_id', 'subsector_id', 'purchased_on_weekend', 'month_diff']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_dict = {c:ft.variable_types.Categorical for c in cat_cols}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'authorized_flag': featuretools.variable_types.variable.Categorical,\n",
       " 'card_id': featuretools.variable_types.variable.Categorical,\n",
       " 'city_id': featuretools.variable_types.variable.Categorical,\n",
       " 'category_1': featuretools.variable_types.variable.Categorical,\n",
       " 'category_3': featuretools.variable_types.variable.Categorical,\n",
       " 'merchant_category_id': featuretools.variable_types.variable.Categorical,\n",
       " 'merchant_id': featuretools.variable_types.variable.Categorical,\n",
       " 'category_2': featuretools.variable_types.variable.Categorical,\n",
       " 'state_id': featuretools.variable_types.variable.Categorical,\n",
       " 'subsector_id': featuretools.variable_types.variable.Categorical,\n",
       " 'purchased_on_weekend': featuretools.variable_types.variable.Categorical,\n",
       " 'month_diff': featuretools.variable_types.variable.Categorical,\n",
       " 'index': featuretools.variable_types.variable.Index}"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "es = ft.EntitySet()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-01-19 21:48:42,534 featuretools.entityset - WARNING    index index not found in dataframe, creating new integer column\n"
     ]
    }
   ],
   "source": [
    "new_trans_es = es.entity_from_dataframe('new_trans', temp, 'index', time_index='purchase_date',\n",
    "                                        variable_types=cat_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Entityset: None\n",
       "  Entities:\n",
       "    new_trans [Rows: 260, Columns: 17]\n",
       "  Relationships:\n",
       "    No relationships"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_trans_es"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Entity: new_trans\n",
       "  Variables:\n",
       "    index (dtype: index)\n",
       "    installments (dtype: numeric)\n",
       "    month_lag (dtype: numeric)\n",
       "    purchase_amount (dtype: numeric)\n",
       "    purchase_date (dtype: datetime_time_index)\n",
       "    authorized_flag (dtype: categorical)\n",
       "    card_id (dtype: categorical)\n",
       "    city_id (dtype: categorical)\n",
       "    category_1 (dtype: categorical)\n",
       "    category_3 (dtype: categorical)\n",
       "    merchant_category_id (dtype: categorical)\n",
       "    merchant_id (dtype: categorical)\n",
       "    category_2 (dtype: categorical)\n",
       "    state_id (dtype: categorical)\n",
       "    subsector_id (dtype: categorical)\n",
       "    purchased_on_weekend (dtype: categorical)\n",
       "    month_diff (dtype: categorical)\n",
       "  Shape:\n",
       "    (Rows: 260, Columns: 17)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_trans_es['new_trans']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_temp = train[train['card_id'].isin(['C_ID_92a2005557'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['first_active_month', 'card_id', 'feature_1', 'feature_2', 'feature_3', 'target'], dtype=object)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_temp.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_cat_cols = ['card_id', 'feature_1', 'feature_2', 'feature_3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_cat_cols = {c:ft.variable_types.Categorical for c in train_cat_cols}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_trans_es = es.entity_from_dataframe('train', train_temp, 'card_id', time_index='first_active_month',\n",
    "                                        variable_types=train_cat_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Entityset: None\n",
       "  Entities:\n",
       "    new_trans [Rows: 260, Columns: 17]\n",
       "    train [Rows: 1, Columns: 6]\n",
       "  Relationships:\n",
       "    No relationships"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_trans_es"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_r = ft.Relationship( new_trans_es['train']['card_id'],new_trans_es['new_trans']['card_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_trans_es = new_trans_es.add_relationship(new_r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Entityset: None\n",
       "  Entities:\n",
       "    new_trans [Rows: 260, Columns: 17]\n",
       "    train [Rows: 1, Columns: 6]\n",
       "  Relationships:\n",
       "    new_trans.card_id -> train.card_id"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_trans_es"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "fm, fd = ft.dfs(entityset=new_trans_es, target_entity='train')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>card_id</th>\n",
       "      <th>C_ID_92a2005557</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>target</th>\n",
       "      <td>-0.820283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_1</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_2</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_3</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SUM(new_trans.installments)</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SUM(new_trans.month_lag)</th>\n",
       "      <td>-1017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SUM(new_trans.purchase_amount)</th>\n",
       "      <td>-165.969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STD(new_trans.installments)</th>\n",
       "      <td>0.123314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STD(new_trans.month_lag)</th>\n",
       "      <td>2.39769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STD(new_trans.purchase_amount)</th>\n",
       "      <td>0.212139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MAX(new_trans.installments)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MAX(new_trans.month_lag)</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MAX(new_trans.purchase_amount)</th>\n",
       "      <td>2.25839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SKEW(new_trans.installments)</th>\n",
       "      <td>7.92077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SKEW(new_trans.month_lag)</th>\n",
       "      <td>0.0659603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SKEW(new_trans.purchase_amount)</th>\n",
       "      <td>10.2431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIN(new_trans.installments)</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIN(new_trans.month_lag)</th>\n",
       "      <td>-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIN(new_trans.purchase_amount)</th>\n",
       "      <td>-0.739395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MEAN(new_trans.installments)</th>\n",
       "      <td>0.0153846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MEAN(new_trans.month_lag)</th>\n",
       "      <td>-3.91154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MEAN(new_trans.purchase_amount)</th>\n",
       "      <td>-0.638341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COUNT(new_trans)</th>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.authorized_flag)</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.city_id)</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.category_1)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.category_3)</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.merchant_category_id)</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.merchant_id)</th>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.category_2)</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.state_id)</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.subsector_id)</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.purchased_on_weekend)</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.month_diff)</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.authorized_flag)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.city_id)</th>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.category_1)</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.category_3)</th>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.merchant_category_id)</th>\n",
       "      <td>560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.merchant_id)</th>\n",
       "      <td>M_ID_1a81c358a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.category_2)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.state_id)</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.subsector_id)</th>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.purchased_on_weekend)</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.month_diff)</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DAY(first_active_month)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YEAR(first_active_month)</th>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MONTH(first_active_month)</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WEEKDAY(first_active_month)</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.DAY(purchase_date))</th>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.YEAR(purchase_date))</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.MONTH(purchase_date))</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUM_UNIQUE(new_trans.WEEKDAY(purchase_date))</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.DAY(purchase_date))</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.YEAR(purchase_date))</th>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.MONTH(purchase_date))</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MODE(new_trans.WEEKDAY(purchase_date))</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "card_id                                       C_ID_92a2005557\n",
       "target                                              -0.820283\n",
       "feature_1                                                   5\n",
       "feature_2                                                   2\n",
       "feature_3                                                   1\n",
       "SUM(new_trans.installments)                                 4\n",
       "SUM(new_trans.month_lag)                                -1017\n",
       "SUM(new_trans.purchase_amount)                       -165.969\n",
       "STD(new_trans.installments)                          0.123314\n",
       "STD(new_trans.month_lag)                              2.39769\n",
       "STD(new_trans.purchase_amount)                       0.212139\n",
       "MAX(new_trans.installments)                                 1\n",
       "MAX(new_trans.month_lag)                                    0\n",
       "MAX(new_trans.purchase_amount)                        2.25839\n",
       "SKEW(new_trans.installments)                          7.92077\n",
       "SKEW(new_trans.month_lag)                           0.0659603\n",
       "SKEW(new_trans.purchase_amount)                       10.2431\n",
       "MIN(new_trans.installments)                                 0\n",
       "MIN(new_trans.month_lag)                                   -8\n",
       "MIN(new_trans.purchase_amount)                      -0.739395\n",
       "MEAN(new_trans.installments)                        0.0153846\n",
       "MEAN(new_trans.month_lag)                            -3.91154\n",
       "MEAN(new_trans.purchase_amount)                     -0.638341\n",
       "COUNT(new_trans)                                          260\n",
       "NUM_UNIQUE(new_trans.authorized_flag)                       2\n",
       "NUM_UNIQUE(new_trans.city_id)                               7\n",
       "NUM_UNIQUE(new_trans.category_1)                            1\n",
       "NUM_UNIQUE(new_trans.category_3)                            2\n",
       "NUM_UNIQUE(new_trans.merchant_category_id)                 41\n",
       "NUM_UNIQUE(new_trans.merchant_id)                          94\n",
       "NUM_UNIQUE(new_trans.category_2)                            2\n",
       "NUM_UNIQUE(new_trans.state_id)                              3\n",
       "NUM_UNIQUE(new_trans.subsector_id)                         21\n",
       "NUM_UNIQUE(new_trans.purchased_on_weekend)                  2\n",
       "NUM_UNIQUE(new_trans.month_diff)                            2\n",
       "MODE(new_trans.authorized_flag)                             1\n",
       "MODE(new_trans.city_id)                                    69\n",
       "MODE(new_trans.category_1)                                  0\n",
       "MODE(new_trans.category_3)                                  A\n",
       "MODE(new_trans.merchant_category_id)                      560\n",
       "MODE(new_trans.merchant_id)                   M_ID_1a81c358a3\n",
       "MODE(new_trans.category_2)                                  1\n",
       "MODE(new_trans.state_id)                                    9\n",
       "MODE(new_trans.subsector_id)                               34\n",
       "MODE(new_trans.purchased_on_weekend)                        0\n",
       "MODE(new_trans.month_diff)                                 11\n",
       "DAY(first_active_month)                                     1\n",
       "YEAR(first_active_month)                                 2017\n",
       "MONTH(first_active_month)                                   6\n",
       "WEEKDAY(first_active_month)                                 3\n",
       "NUM_UNIQUE(new_trans.DAY(purchase_date))                   31\n",
       "NUM_UNIQUE(new_trans.YEAR(purchase_date))                   2\n",
       "NUM_UNIQUE(new_trans.MONTH(purchase_date))                  9\n",
       "NUM_UNIQUE(new_trans.WEEKDAY(purchase_date))                7\n",
       "MODE(new_trans.DAY(purchase_date))                         11\n",
       "MODE(new_trans.YEAR(purchase_date))                      2017\n",
       "MODE(new_trans.MONTH(purchase_date))                       12\n",
       "MODE(new_trans.WEEKDAY(purchase_date))                      5"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fm.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrameSummary(temp).summary().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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['purchase_amount'] = ['sum','max','min','mean','var']\n",
    "    aggs['installments'] = ['sum','max','min','mean','var']\n",
    "    aggs['purchase_date'] = ['max','min', np.ptp]\n",
    "    aggs['month_lag'] = ['max','min','mean','var']\n",
    "    aggs['month_diff'] = ['mean', 'std', 'var']\n",
    "    aggs['authorized_flag'] = ['sum', 'mean']\n",
    "    aggs['purchased_on_weekend'] = ['sum', 'mean']\n",
    "    aggs['category_1'] = ['sum', 'mean']\n",
    "    aggs['card_id'] = ['size']\n",
    "    cols_vals = df.columns.values\n",
    "    for col in ['category_2','category_3', 'authorized_flag']:\n",
    "        if col in cols_vals:\n",
    "            df[col+'_amount_mean'] = df.groupby([col])['purchase_amount'].transform('mean')\n",
    "            df[col+'_amount_sum'] = df.groupby([col])['purchase_amount'].transform('sum')\n",
    "            aggs[col+'_amount_mean'] = ['mean']\n",
    "            aggs[col+'_amount_sum'] = ['sum']\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",
    "    new_df['purchase_date_diff'] = (new_df['purchase_date_max'] - new_df['purchase_date_min']).dt.days\n",
    "    new_df['purchase_date_average'] = new_df['purchase_date_diff']/new_df['card_id_size']\n",
    "    new_df['purchase_date_uptonow'] = (datetime.datetime.today() - new_df['purchase_date_max']).dt.days\n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_92a2005557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transactions_count</th>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_nunique</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id_nunique</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id_nunique</th>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id_nunique</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id_nunique</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_sum</th>\n",
       "      <td>-165.969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_max</th>\n",
       "      <td>2.25839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_min</th>\n",
       "      <td>-0.739395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_mean</th>\n",
       "      <td>-0.638341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_var</th>\n",
       "      <td>0.0450031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_sum</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_max</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_min</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_mean</th>\n",
       "      <td>0.0153846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_var</th>\n",
       "      <td>0.0152064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_max</th>\n",
       "      <td>2018-02-25 09:31:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_min</th>\n",
       "      <td>2017-06-27 14:18:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_ptp</th>\n",
       "      <td>242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_max</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_min</th>\n",
       "      <td>-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_mean</th>\n",
       "      <td>-3.91154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_var</th>\n",
       "      <td>5.7489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_mean</th>\n",
       "      <td>10.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_std</th>\n",
       "      <td>0.400771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_var</th>\n",
       "      <td>0.160618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_sum</th>\n",
       "      <td>247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_mean</th>\n",
       "      <td>0.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_sum</th>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_mean</th>\n",
       "      <td>0.346154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_sum</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_mean</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_size</th>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_amount_mean_mean</th>\n",
       "      <td>-0.638341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_amount_sum_sum</th>\n",
       "      <td>-42175.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_amount_mean_mean</th>\n",
       "      <td>-0.638341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_amount_sum_sum</th>\n",
       "      <td>-41811.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_amount_mean_mean</th>\n",
       "      <td>-0.638341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_amount_sum_sum</th>\n",
       "      <td>-38988.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_diff</th>\n",
       "      <td>242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_average</th>\n",
       "      <td>0.930769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_uptonow</th>\n",
       "      <td>328</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    0\n",
       "card_id                               C_ID_92a2005557\n",
       "transactions_count                                260\n",
       "city_id_nunique                                     7\n",
       "merchant_category_id_nunique                       41\n",
       "merchant_id_nunique                                94\n",
       "state_id_nunique                                    3\n",
       "subsector_id_nunique                               21\n",
       "purchase_amount_sum                          -165.969\n",
       "purchase_amount_max                           2.25839\n",
       "purchase_amount_min                         -0.739395\n",
       "purchase_amount_mean                        -0.638341\n",
       "purchase_amount_var                         0.0450031\n",
       "installments_sum                                    4\n",
       "installments_max                                    1\n",
       "installments_min                                    0\n",
       "installments_mean                           0.0153846\n",
       "installments_var                            0.0152064\n",
       "purchase_date_max                 2018-02-25 09:31:15\n",
       "purchase_date_min                 2017-06-27 14:18:08\n",
       "purchase_date_ptp                                 242\n",
       "month_lag_max                                       0\n",
       "month_lag_min                                      -8\n",
       "month_lag_mean                               -3.91154\n",
       "month_lag_var                                  5.7489\n",
       "month_diff_mean                                  10.8\n",
       "month_diff_std                               0.400771\n",
       "month_diff_var                               0.160618\n",
       "authorized_flag_sum                               247\n",
       "authorized_flag_mean                             0.95\n",
       "purchased_on_weekend_sum                           90\n",
       "purchased_on_weekend_mean                    0.346154\n",
       "category_1_sum                                      0\n",
       "category_1_mean                                     0\n",
       "card_id_size                                      260\n",
       "category_2_amount_mean_mean                 -0.638341\n",
       "category_2_amount_sum_sum                    -42175.5\n",
       "category_3_amount_mean_mean                 -0.638341\n",
       "category_3_amount_sum_sum                    -41811.6\n",
       "authorized_flag_amount_mean_mean            -0.638341\n",
       "authorized_flag_amount_sum_sum               -38988.5\n",
       "purchase_date_diff                                242\n",
       "purchase_date_average                        0.930769\n",
       "purchase_date_uptonow                             328"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aggregate_by_card_id(temp).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0    17830063\n",
       "3.0     3911795\n",
       "5.0     3725915\n",
       "4.0     2618053\n",
       "2.0     1026535\n",
       "Name: category_2, dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans['category_2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0    1169987\n",
       "3.0     289525\n",
       "5.0     259266\n",
       "4.0     178590\n",
       "2.0      65663\n",
       "Name: category_2, dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans['category_2'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    15589906\n",
       "B    11677522\n",
       "C     1844933\n",
       "Name: category_3, dtype: int64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans['category_3'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    27028332\n",
       "1     2084029\n",
       "Name: category_1, dtype: int64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_trans['category_1'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1899935\n",
       "1      63096\n",
       "Name: category_1, dtype: int64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_hist_trans['category_1'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp1 = hist_trans.loc[hist_trans['card_id'].isin(['C_ID_05042ebd55', 'C_ID_5c240d6e3c'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>29112043</th>\n",
       "      <th>29112044</th>\n",
       "      <th>29112045</th>\n",
       "      <th>29112046</th>\n",
       "      <th>29112047</th>\n",
       "      <th>29112048</th>\n",
       "      <th>29112358</th>\n",
       "      <th>29112359</th>\n",
       "      <th>29112360</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>authorized_flag</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>C_ID_5c240d6e3c</td>\n",
       "      <td>C_ID_5c240d6e3c</td>\n",
       "      <td>C_ID_5c240d6e3c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id</th>\n",
       "      <td>-1</td>\n",
       "      <td>20</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>3</td>\n",
       "      <td>331</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1</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",
       "    </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",
       "      <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",
       "      <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>437</td>\n",
       "      <td>422</td>\n",
       "      <td>703</td>\n",
       "      <td>80</td>\n",
       "      <td>80</td>\n",
       "      <td>278</td>\n",
       "      <td>278</td>\n",
       "      <td>514</td>\n",
       "      <td>514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id</th>\n",
       "      <td>M_ID_ce1e7a7a85</td>\n",
       "      <td>M_ID_0de7fd2bef</td>\n",
       "      <td>M_ID_051eacb2a3</td>\n",
       "      <td>M_ID_9ecff0beda</td>\n",
       "      <td>M_ID_9ecff0beda</td>\n",
       "      <td>M_ID_814793f74b</td>\n",
       "      <td>M_ID_9cdcfe8673</td>\n",
       "      <td>M_ID_1a75f94f92</td>\n",
       "      <td>M_ID_1a75f94f92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag</th>\n",
       "      <td>-4</td>\n",
       "      <td>-1</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount</th>\n",
       "      <td>-0.643976</td>\n",
       "      <td>-0.692948</td>\n",
       "      <td>-0.716855</td>\n",
       "      <td>-0.705585</td>\n",
       "      <td>-0.705134</td>\n",
       "      <td>-0.725871</td>\n",
       "      <td>-0.65774</td>\n",
       "      <td>0.77062</td>\n",
       "      <td>1.13441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date</th>\n",
       "      <td>2017-10-28 15:16:46</td>\n",
       "      <td>2018-01-14 21:22:48</td>\n",
       "      <td>2017-12-31 16:27:35</td>\n",
       "      <td>2018-02-07 12:34:06</td>\n",
       "      <td>2018-02-07 12:33:36</td>\n",
       "      <td>2018-02-09 15:04:41</td>\n",
       "      <td>2017-12-26 18:37:51</td>\n",
       "      <td>2017-11-24 14:18:15</td>\n",
       "      <td>2017-10-26 14:09:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\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>-1</td>\n",
       "      <td>19</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id</th>\n",
       "      <td>15</td>\n",
       "      <td>27</td>\n",
       "      <td>29</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Year</th>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2017</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Month</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Week</th>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>52</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>52</td>\n",
       "      <td>47</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Day</th>\n",
       "      <td>28</td>\n",
       "      <td>14</td>\n",
       "      <td>31</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>26</td>\n",
       "      <td>24</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofweek</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Dayofyear</th>\n",
       "      <td>301</td>\n",
       "      <td>14</td>\n",
       "      <td>365</td>\n",
       "      <td>38</td>\n",
       "      <td>38</td>\n",
       "      <td>40</td>\n",
       "      <td>360</td>\n",
       "      <td>328</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_Is_month_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\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",
       "      <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>True</td>\n",
       "      <td>False</td>\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",
       "      <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>True</td>\n",
       "      <td>False</td>\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",
       "      <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>1509203806</td>\n",
       "      <td>1515964968</td>\n",
       "      <td>1514737655</td>\n",
       "      <td>1518006846</td>\n",
       "      <td>1518006816</td>\n",
       "      <td>1518188681</td>\n",
       "      <td>1514313471</td>\n",
       "      <td>1511533095</td>\n",
       "      <td>1509026980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>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>month_diff</th>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      29112043             29112044  \\\n",
       "authorized_flag                              0                    1   \n",
       "card_id                        C_ID_05042ebd55      C_ID_05042ebd55   \n",
       "city_id                                     -1                   20   \n",
       "category_1                                   0                    0   \n",
       "installments                                 0                    0   \n",
       "category_3                                   A                    A   \n",
       "merchant_category_id                       437                  422   \n",
       "merchant_id                    M_ID_ce1e7a7a85      M_ID_0de7fd2bef   \n",
       "month_lag                                   -4                   -1   \n",
       "purchase_amount                      -0.643976            -0.692948   \n",
       "purchase_date              2017-10-28 15:16:46  2018-01-14 21:22:48   \n",
       "category_2                                   1                    3   \n",
       "state_id                                    -1                   19   \n",
       "subsector_id                                15                   27   \n",
       "purchase_Year                             2017                 2018   \n",
       "purchase_Month                              10                    1   \n",
       "purchase_Week                               43                    2   \n",
       "purchase_Day                                28                   14   \n",
       "purchase_Dayofweek                           5                    6   \n",
       "purchase_Dayofyear                         301                   14   \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                    1509203806           1515964968   \n",
       "purchased_on_weekend                         1                    1   \n",
       "month_diff                                  10                   11   \n",
       "\n",
       "                                      29112045             29112046  \\\n",
       "authorized_flag                              1                    1   \n",
       "card_id                        C_ID_05042ebd55      C_ID_05042ebd55   \n",
       "city_id                                     -1                   -1   \n",
       "category_1                                   0                    0   \n",
       "installments                                 0                    0   \n",
       "category_3                                   A                    A   \n",
       "merchant_category_id                       703                   80   \n",
       "merchant_id                    M_ID_051eacb2a3      M_ID_9ecff0beda   \n",
       "month_lag                                   -2                    0   \n",
       "purchase_amount                      -0.716855            -0.705585   \n",
       "purchase_date              2017-12-31 16:27:35  2018-02-07 12:34:06   \n",
       "category_2                                   1                    1   \n",
       "state_id                                    -1                   -1   \n",
       "subsector_id                                29                   37   \n",
       "purchase_Year                             2017                 2018   \n",
       "purchase_Month                              12                    2   \n",
       "purchase_Week                               52                    6   \n",
       "purchase_Day                                31                    7   \n",
       "purchase_Dayofweek                           6                    2   \n",
       "purchase_Dayofyear                         365                   38   \n",
       "purchase_Is_month_end                     True                False   \n",
       "purchase_Is_month_start                  False                False   \n",
       "purchase_Is_quarter_end                   True                False   \n",
       "purchase_Is_quarter_start                False                False   \n",
       "purchase_Is_year_end                      True                False   \n",
       "purchase_Is_year_start                   False                False   \n",
       "purchase_Elapsed                    1514737655           1518006846   \n",
       "purchased_on_weekend                         1                    0   \n",
       "month_diff                                  10                   11   \n",
       "\n",
       "                                      29112047             29112048  \\\n",
       "authorized_flag                              0                    1   \n",
       "card_id                        C_ID_05042ebd55      C_ID_05042ebd55   \n",
       "city_id                                     -1                   -1   \n",
       "category_1                                   0                    0   \n",
       "installments                                 0                    0   \n",
       "category_3                                   A                    A   \n",
       "merchant_category_id                        80                  278   \n",
       "merchant_id                    M_ID_9ecff0beda      M_ID_814793f74b   \n",
       "month_lag                                    0                    0   \n",
       "purchase_amount                      -0.705134            -0.725871   \n",
       "purchase_date              2018-02-07 12:33:36  2018-02-09 15:04:41   \n",
       "category_2                                   1                    1   \n",
       "state_id                                    -1                   -1   \n",
       "subsector_id                                37                   37   \n",
       "purchase_Year                             2018                 2018   \n",
       "purchase_Month                               2                    2   \n",
       "purchase_Week                                6                    6   \n",
       "purchase_Day                                 7                    9   \n",
       "purchase_Dayofweek                           2                    4   \n",
       "purchase_Dayofyear                          38                   40   \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                    1518006816           1518188681   \n",
       "purchased_on_weekend                         0                    0   \n",
       "month_diff                                  11                   11   \n",
       "\n",
       "                                      29112358             29112359  \\\n",
       "authorized_flag                              1                    1   \n",
       "card_id                        C_ID_5c240d6e3c      C_ID_5c240d6e3c   \n",
       "city_id                                      3                  331   \n",
       "category_1                                   0                    0   \n",
       "installments                                 0                    0   \n",
       "category_3                                   A                    A   \n",
       "merchant_category_id                       278                  514   \n",
       "merchant_id                    M_ID_9cdcfe8673      M_ID_1a75f94f92   \n",
       "month_lag                                    0                   -1   \n",
       "purchase_amount                       -0.65774              0.77062   \n",
       "purchase_date              2017-12-26 18:37:51  2017-11-24 14:18:15   \n",
       "category_2                                   1                    1   \n",
       "state_id                                    16                   16   \n",
       "subsector_id                                37                    9   \n",
       "purchase_Year                             2017                 2017   \n",
       "purchase_Month                              12                   11   \n",
       "purchase_Week                               52                   47   \n",
       "purchase_Day                                26                   24   \n",
       "purchase_Dayofweek                           1                    4   \n",
       "purchase_Dayofyear                         360                  328   \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                    1514313471           1511533095   \n",
       "purchased_on_weekend                         0                    0   \n",
       "month_diff                                  12                   13   \n",
       "\n",
       "                                      29112360  \n",
       "authorized_flag                              1  \n",
       "card_id                        C_ID_5c240d6e3c  \n",
       "city_id                                    331  \n",
       "category_1                                   0  \n",
       "installments                                 0  \n",
       "category_3                                   A  \n",
       "merchant_category_id                       514  \n",
       "merchant_id                    M_ID_1a75f94f92  \n",
       "month_lag                                   -2  \n",
       "purchase_amount                        1.13441  \n",
       "purchase_date              2017-10-26 14:09:40  \n",
       "category_2                                   1  \n",
       "state_id                                    16  \n",
       "subsector_id                                 9  \n",
       "purchase_Year                             2017  \n",
       "purchase_Month                              10  \n",
       "purchase_Week                               43  \n",
       "purchase_Day                                26  \n",
       "purchase_Dayofweek                           3  \n",
       "purchase_Dayofyear                         299  \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                    1509026980  \n",
       "purchased_on_weekend                         0  \n",
       "month_diff                                  12  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>category_2</th>\n",
       "      <th>category_3</th>\n",
       "      <th>category_1</th>\n",
       "      <th>authorized_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     category_2  category_3  category_1  authorized_flag\n",
       "1.0         8.0         NaN         NaN              7.0\n",
       "3.0         1.0         NaN         NaN              NaN\n",
       "A           NaN         9.0         NaN              NaN\n",
       "0           NaN         NaN         9.0              2.0"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1[['category_2','category_3', 'category_1', 'authorized_flag']].apply(pd.value_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "k =temp1.groupby(['card_id', 'category_2']).agg({\"purchase_amount\":'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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></th>\n",
       "      <th>purchase_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th>category_2</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_ID_05042ebd55</th>\n",
       "      <th>1.0</th>\n",
       "      <td>-3.497421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <td>-0.692948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <th>1.0</th>\n",
       "      <td>1.247291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            purchase_amount\n",
       "card_id         category_2                 \n",
       "C_ID_05042ebd55 1.0               -3.497421\n",
       "                3.0               -0.692948\n",
       "C_ID_5c240d6e3c 1.0                1.247291"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['p_u_r_c_h_a_s_e___a_m_o_u_n_t']"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "['_'.join(c) for c in k.columns.values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['purchase_amount'], dtype=object)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k.columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['purchase_amount'], dtype='object')"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "l = temp1.pivot_table(index='card_id',columns='category_2',aggfunc={\"purchase_amount\": ['sum']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "l.columns = ['cat_2_{0[2]}_{0[0]}_{0[1]}'.format(col) if col[2] else col[2] for col in l.columns.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "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>card_id</th>\n",
       "      <th>cat_2_1.0_purchase_amount_sum</th>\n",
       "      <th>cat_2_3.0_purchase_amount_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>-3.497421</td>\n",
       "      <td>-0.692948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C_ID_5c240d6e3c</td>\n",
       "      <td>1.247291</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           card_id  cat_2_1.0_purchase_amount_sum  \\\n",
       "0  C_ID_05042ebd55                      -3.497421   \n",
       "1  C_ID_5c240d6e3c                       1.247291   \n",
       "\n",
       "   cat_2_3.0_purchase_amount_sum  \n",
       "0                      -0.692948  \n",
       "1                            NaN  "
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "p = temp1.pivot_table(index='card_id',columns='category_2' ,aggfunc={\"purchase_amount\": ['sum', 'mean']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">purchase_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <th>1.0</th>\n",
       "      <th>3.0</th>\n",
       "      <th>1.0</th>\n",
       "      <th>3.0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>-0.699484</td>\n",
       "      <td>-0.692948</td>\n",
       "      <td>-3.497421</td>\n",
       "      <td>-0.692948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>0.415764</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.247291</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                purchase_amount                              \n",
       "                           mean                 sum          \n",
       "category_2                  1.0       3.0       1.0       3.0\n",
       "card_id                                                      \n",
       "C_ID_05042ebd55       -0.699484 -0.692948 -3.497421 -0.692948\n",
       "C_ID_5c240d6e3c        0.415764       NaN  1.247291       NaN"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['cat_2_1.0_purchase_amount_mean',\n",
       " 'cat_2_3.0_purchase_amount_mean',\n",
       " 'cat_2_1.0_purchase_amount_sum',\n",
       " 'cat_2_3.0_purchase_amount_sum']"
      ]
     },
     "execution_count": 202,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "['cat_2_{0[2]}_{0[0]}_{0[1]}'.format(col) if col[2] else col[2] for col in p.columns.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <td>C_ID_05042ebd55</td>\n",
       "      <td>C_ID_5c240d6e3c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transactions_count</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city_id_nunique</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_category_id_nunique</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>merchant_id_nunique</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_id_nunique</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subsector_id_nunique</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_sum</th>\n",
       "      <td>-4.19037</td>\n",
       "      <td>1.24729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_max</th>\n",
       "      <td>-0.643976</td>\n",
       "      <td>1.13441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_min</th>\n",
       "      <td>-0.725871</td>\n",
       "      <td>-0.65774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_mean</th>\n",
       "      <td>-0.698395</td>\n",
       "      <td>0.415764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_amount_var</th>\n",
       "      <td>0.000836773</td>\n",
       "      <td>0.897394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_max</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_min</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>installments_var</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_max</th>\n",
       "      <td>2018-02-09 15:04:41</td>\n",
       "      <td>2017-12-26 18:37:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_min</th>\n",
       "      <td>2017-10-28 15:16:46</td>\n",
       "      <td>2017-10-26 14:09:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_ptp</th>\n",
       "      <td>103</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_max</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_min</th>\n",
       "      <td>-4</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_mean</th>\n",
       "      <td>-1.16667</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_lag_var</th>\n",
       "      <td>2.56667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_mean</th>\n",
       "      <td>10.6667</td>\n",
       "      <td>12.3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_std</th>\n",
       "      <td>0.516398</td>\n",
       "      <td>0.57735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month_diff_var</th>\n",
       "      <td>0.266667</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_sum</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_mean</th>\n",
       "      <td>0.666667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_sum</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchased_on_weekend_mean</th>\n",
       "      <td>0.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_sum</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_1_mean</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id_size</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_amount_mean_mean</th>\n",
       "      <td>-0.34988</td>\n",
       "      <td>-0.281266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2_amount_sum_sum</th>\n",
       "      <td>-11.9436</td>\n",
       "      <td>-6.75039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_amount_mean_mean</th>\n",
       "      <td>-0.327009</td>\n",
       "      <td>-0.327009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_3_amount_sum_sum</th>\n",
       "      <td>-17.6585</td>\n",
       "      <td>-8.82923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_amount_mean_mean</th>\n",
       "      <td>-0.376658</td>\n",
       "      <td>-0.22771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag_amount_sum_sum</th>\n",
       "      <td>-9.07409</td>\n",
       "      <td>-4.7819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_diff</th>\n",
       "      <td>103</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_average</th>\n",
       "      <td>17.1667</td>\n",
       "      <td>20.3333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchase_date_uptonow</th>\n",
       "      <td>344</td>\n",
       "      <td>389</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    0                    1\n",
       "card_id                               C_ID_05042ebd55      C_ID_5c240d6e3c\n",
       "transactions_count                                  6                    3\n",
       "city_id_nunique                                     2                    2\n",
       "merchant_category_id_nunique                        5                    2\n",
       "merchant_id_nunique                                 5                    2\n",
       "state_id_nunique                                    2                    1\n",
       "subsector_id_nunique                                4                    2\n",
       "purchase_amount_sum                          -4.19037              1.24729\n",
       "purchase_amount_max                         -0.643976              1.13441\n",
       "purchase_amount_min                         -0.725871             -0.65774\n",
       "purchase_amount_mean                        -0.698395             0.415764\n",
       "purchase_amount_var                       0.000836773             0.897394\n",
       "installments_sum                                    0                    0\n",
       "installments_max                                    0                    0\n",
       "installments_min                                    0                    0\n",
       "installments_mean                                   0                    0\n",
       "installments_var                                    0                    0\n",
       "purchase_date_max                 2018-02-09 15:04:41  2017-12-26 18:37:51\n",
       "purchase_date_min                 2017-10-28 15:16:46  2017-10-26 14:09:40\n",
       "purchase_date_ptp                                 103                   61\n",
       "month_lag_max                                       0                    0\n",
       "month_lag_min                                      -4                   -2\n",
       "month_lag_mean                               -1.16667                   -1\n",
       "month_lag_var                                 2.56667                    1\n",
       "month_diff_mean                               10.6667              12.3333\n",
       "month_diff_std                               0.516398              0.57735\n",
       "month_diff_var                               0.266667             0.333333\n",
       "authorized_flag_sum                                 4                    3\n",
       "authorized_flag_mean                         0.666667                    1\n",
       "purchased_on_weekend_sum                            3                    0\n",
       "purchased_on_weekend_mean                         0.5                    0\n",
       "category_1_sum                                      0                    0\n",
       "category_1_mean                                     0                    0\n",
       "card_id_size                                        6                    3\n",
       "category_2_amount_mean_mean                  -0.34988            -0.281266\n",
       "category_2_amount_sum_sum                    -11.9436             -6.75039\n",
       "category_3_amount_mean_mean                 -0.327009            -0.327009\n",
       "category_3_amount_sum_sum                    -17.6585             -8.82923\n",
       "authorized_flag_amount_mean_mean            -0.376658             -0.22771\n",
       "authorized_flag_amount_sum_sum               -9.07409              -4.7819\n",
       "purchase_date_diff                                103                   61\n",
       "purchase_date_average                         17.1667              20.3333\n",
       "purchase_date_uptonow                             344                  389"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aggregate_by_card_id(temp1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">purchase_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>authorized_flag</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>-0.674555</td>\n",
       "      <td>-0.710314</td>\n",
       "      <td>-1.34911</td>\n",
       "      <td>-2.841258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.415764</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.247291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                purchase_amount                             \n",
       "                           mean                sum          \n",
       "authorized_flag               0         1        0         1\n",
       "card_id                                                     \n",
       "C_ID_05042ebd55       -0.674555 -0.710314 -1.34911 -2.841258\n",
       "C_ID_5c240d6e3c             NaN  0.415764      NaN  1.247291"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1.pivot_table(index='card_id',columns='authorized_flag' ,aggfunc={\"purchase_amount\": ['sum', 'mean']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    7\n",
       "0    2\n",
       "Name: authorized_flag, dtype: int64"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1['authorized_flag'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [],
   "source": [
    "def agg_on_cat(df, category, feature):\n",
    "    temp_df = df.pivot_table(index='card_id', columns=category, aggfunc={feature: ['sum', 'mean']})\n",
    "    cols = [category + '_{0[2]}_{0[0]}_{0[1]}'.format(col) for col in temp_df.columns.tolist()]\n",
    "    temp_df.columns = cols\n",
    "    return temp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "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>category_2_1.0_purchase_amount_mean</th>\n",
       "      <th>category_2_3.0_purchase_amount_mean</th>\n",
       "      <th>category_2_1.0_purchase_amount_sum</th>\n",
       "      <th>category_2_3.0_purchase_amount_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>-0.699484</td>\n",
       "      <td>-0.692948</td>\n",
       "      <td>-3.497421</td>\n",
       "      <td>-0.692948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>0.415764</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.247291</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 category_2_1.0_purchase_amount_mean  \\\n",
       "card_id                                                \n",
       "C_ID_05042ebd55                            -0.699484   \n",
       "C_ID_5c240d6e3c                             0.415764   \n",
       "\n",
       "                 category_2_3.0_purchase_amount_mean  \\\n",
       "card_id                                                \n",
       "C_ID_05042ebd55                            -0.692948   \n",
       "C_ID_5c240d6e3c                                  NaN   \n",
       "\n",
       "                 category_2_1.0_purchase_amount_sum  \\\n",
       "card_id                                               \n",
       "C_ID_05042ebd55                           -3.497421   \n",
       "C_ID_5c240d6e3c                            1.247291   \n",
       "\n",
       "                 category_2_3.0_purchase_amount_sum  \n",
       "card_id                                              \n",
       "C_ID_05042ebd55                           -0.692948  \n",
       "C_ID_5c240d6e3c                                 NaN  "
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_on_cat(temp1, 'category_2', 'purchase_amount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "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>category_3_A_purchase_amount_mean</th>\n",
       "      <th>category_3_A_purchase_amount_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>-0.698395</td>\n",
       "      <td>-4.190368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>0.415764</td>\n",
       "      <td>1.247291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 category_3_A_purchase_amount_mean  \\\n",
       "card_id                                              \n",
       "C_ID_05042ebd55                          -0.698395   \n",
       "C_ID_5c240d6e3c                           0.415764   \n",
       "\n",
       "                 category_3_A_purchase_amount_sum  \n",
       "card_id                                            \n",
       "C_ID_05042ebd55                         -4.190368  \n",
       "C_ID_5c240d6e3c                          1.247291  "
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_on_cat(temp1, 'category_3', 'purchase_amount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>authorized_flag_0_purchase_amount_mean</th>\n",
       "      <th>authorized_flag_1_purchase_amount_mean</th>\n",
       "      <th>authorized_flag_0_purchase_amount_sum</th>\n",
       "      <th>authorized_flag_1_purchase_amount_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>-0.674555</td>\n",
       "      <td>-0.710314</td>\n",
       "      <td>-1.34911</td>\n",
       "      <td>-2.841258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.415764</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.247291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 authorized_flag_0_purchase_amount_mean  \\\n",
       "card_id                                                   \n",
       "C_ID_05042ebd55                               -0.674555   \n",
       "C_ID_5c240d6e3c                                     NaN   \n",
       "\n",
       "                 authorized_flag_1_purchase_amount_mean  \\\n",
       "card_id                                                   \n",
       "C_ID_05042ebd55                               -0.710314   \n",
       "C_ID_5c240d6e3c                                0.415764   \n",
       "\n",
       "                 authorized_flag_0_purchase_amount_sum  \\\n",
       "card_id                                                  \n",
       "C_ID_05042ebd55                               -1.34911   \n",
       "C_ID_5c240d6e3c                                    NaN   \n",
       "\n",
       "                 authorized_flag_1_purchase_amount_sum  \n",
       "card_id                                                 \n",
       "C_ID_05042ebd55                              -2.841258  \n",
       "C_ID_5c240d6e3c                               1.247291  "
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_on_cat(temp1, 'authorized_flag', 'purchase_amount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_hist_trans = feather.read_dataframe('new_hist_trans')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_2_1.0_purchase_amount_mean</th>\n",
       "      <th>category_2_2.0_purchase_amount_mean</th>\n",
       "      <th>category_2_3.0_purchase_amount_mean</th>\n",
       "      <th>category_2_4.0_purchase_amount_mean</th>\n",
       "      <th>category_2_5.0_purchase_amount_mean</th>\n",
       "      <th>category_2_1.0_purchase_amount_sum</th>\n",
       "      <th>category_2_2.0_purchase_amount_sum</th>\n",
       "      <th>category_2_3.0_purchase_amount_sum</th>\n",
       "      <th>category_2_4.0_purchase_amount_sum</th>\n",
       "      <th>category_2_5.0_purchase_amount_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_00007093c1</th>\n",
       "      <td>-0.656749</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.671775</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.656749</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.671775</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001238066</th>\n",
       "      <td>-0.580966</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.495945</td>\n",
       "      <td>-13.362220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.487835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001506ef0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.723677</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.447354</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0001793786</th>\n",
       "      <td>0.139747</td>\n",
       "      <td>-0.344766</td>\n",
       "      <td>0.102887</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.361628</td>\n",
       "      <td>2.375707</td>\n",
       "      <td>-2.758131</td>\n",
       "      <td>0.514433</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.361628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000183fdda</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.599162</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6.590778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0002709b5a</th>\n",
       "      <td>-0.680791</td>\n",
       "      <td>-0.726367</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.611669</td>\n",
       "      <td>-0.680791</td>\n",
       "      <td>-0.726367</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.611669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000298032a</th>\n",
       "      <td>-0.641872</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.641872</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0002ba3c2e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.690353</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.071059</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0002c7c2c1</th>\n",
       "      <td>-0.654908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.929448</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_00032df08f</th>\n",
       "      <td>-0.348942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.791535</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0003754056</th>\n",
       "      <td>-0.709342</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.709342</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000377f6a0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.613172</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.613172</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0003be3c83</th>\n",
       "      <td>-0.573552</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.441312</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0003f41435</th>\n",
       "      <td>-0.732820</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.465641</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_00042d509c</th>\n",
       "      <td>-0.467264</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.720687</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.467264</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.720687</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0004587331</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.734271</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.468541</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0004725b87</th>\n",
       "      <td>-0.619197</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.238395</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0004888ddd</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.693113</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.386226</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0004b68c49</th>\n",
       "      <td>-0.611451</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.222903</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0004c2a5ab</th>\n",
       "      <td>-0.589558</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.179115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_00057b99fe</th>\n",
       "      <td>-0.701828</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.701828</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000599daf9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.581434</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-5.232908</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0005f16cc8</th>\n",
       "      <td>-0.607161</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.575666</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.607161</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.151331</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0006152db8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.533231</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.066462</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000616f4a8</th>\n",
       "      <td>-0.454958</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.274788</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000664aa02</th>\n",
       "      <td>-0.530032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.240256</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_00079fec55</th>\n",
       "      <td>-0.326466</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.326466</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0007a60a33</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.737892</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.737892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_0007fe3157</th>\n",
       "      <td>-0.671598</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6.044384</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_000844d8f1</th>\n",
       "      <td>-0.708820</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.731881</td>\n",
       "      <td>-9.923478</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.731881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffaed41f0</th>\n",
       "      <td>-0.687328</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.646389</td>\n",
       "      <td>-1.374655</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-10.342222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffb79fb56</th>\n",
       "      <td>-0.671775</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.671775</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffba72dc5</th>\n",
       "      <td>-0.435108</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.870215</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffbdf036b</th>\n",
       "      <td>-0.660645</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-13.873543</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffbee5c24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.571759</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.858794</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffc96bf24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.264016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.792047</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffcb74f49</th>\n",
       "      <td>-0.649010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.947030</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffcf66e12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.685825</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.685825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd070e0e</th>\n",
       "      <td>1.795077</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.385231</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd1207a8</th>\n",
       "      <td>-0.679125</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6.791247</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd24dcf3</th>\n",
       "      <td>-0.351734</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.165605</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd337484</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.387184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.548737</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd345048</th>\n",
       "      <td>-0.667013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-11.339216</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd351e7f</th>\n",
       "      <td>-0.612987</td>\n",
       "      <td>-0.296212</td>\n",
       "      <td>-0.687668</td>\n",
       "      <td>-0.695066</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-6.129870</td>\n",
       "      <td>-2.962116</td>\n",
       "      <td>-4.126010</td>\n",
       "      <td>-2.780265</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd93c8bf</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.661382</td>\n",
       "      <td>-0.562809</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.968291</td>\n",
       "      <td>-3.939666</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffd943b91</th>\n",
       "      <td>-0.637790</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.913371</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffdad8aa0</th>\n",
       "      <td>-0.697920</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-9.770884</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffde15ab6</th>\n",
       "      <td>-0.706486</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.706486</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffdf34798</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.691915</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.684398</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.767658</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.684398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffe29a04a</th>\n",
       "      <td>-0.697245</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.788981</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffe78b232</th>\n",
       "      <td>-0.556576</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.452611</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffe866457</th>\n",
       "      <td>-0.542865</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-4.342917</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffea6de74</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.674931</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.709342</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-7.424239</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.709342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffeced303</th>\n",
       "      <td>-0.717456</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.434912</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffeed3a89</th>\n",
       "      <td>-0.038712</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.730401</td>\n",
       "      <td>-0.564688</td>\n",
       "      <td>-0.601828</td>\n",
       "      <td>-0.154848</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.460802</td>\n",
       "      <td>-3.952814</td>\n",
       "      <td>-22.869479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_ffff1d9928</th>\n",
       "      <td>-0.728876</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.577158</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.728876</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.731475</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_ffff579d3a</th>\n",
       "      <td>-0.664262</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.664262</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_ffff756266</th>\n",
       "      <td>0.378578</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.378578</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_ffff828181</th>\n",
       "      <td>-0.622448</td>\n",
       "      <td>-0.679659</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.528941</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.867345</td>\n",
       "      <td>-2.038977</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.057881</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_fffffd5772</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.708490</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.125470</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>290001 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 category_2_1.0_purchase_amount_mean  \\\n",
       "card_id                                                \n",
       "C_ID_00007093c1                            -0.656749   \n",
       "C_ID_0001238066                            -0.580966   \n",
       "C_ID_0001506ef0                                  NaN   \n",
       "C_ID_0001793786                             0.139747   \n",
       "C_ID_000183fdda                                  NaN   \n",
       "C_ID_0002709b5a                            -0.680791   \n",
       "C_ID_000298032a                            -0.641872   \n",
       "C_ID_0002ba3c2e                                  NaN   \n",
       "C_ID_0002c7c2c1                            -0.654908   \n",
       "C_ID_00032df08f                            -0.348942   \n",
       "C_ID_0003754056                            -0.709342   \n",
       "C_ID_000377f6a0                                  NaN   \n",
       "C_ID_0003be3c83                            -0.573552   \n",
       "C_ID_0003f41435                            -0.732820   \n",
       "C_ID_00042d509c                            -0.467264   \n",
       "C_ID_0004587331                                  NaN   \n",
       "C_ID_0004725b87                            -0.619197   \n",
       "C_ID_0004888ddd                                  NaN   \n",
       "C_ID_0004b68c49                            -0.611451   \n",
       "C_ID_0004c2a5ab                            -0.589558   \n",
       "C_ID_00057b99fe                            -0.701828   \n",
       "C_ID_000599daf9                                  NaN   \n",
       "C_ID_0005f16cc8                            -0.607161   \n",
       "C_ID_0006152db8                                  NaN   \n",
       "C_ID_000616f4a8                            -0.454958   \n",
       "C_ID_000664aa02                            -0.530032   \n",
       "C_ID_00079fec55                            -0.326466   \n",
       "C_ID_0007a60a33                                  NaN   \n",
       "C_ID_0007fe3157                            -0.671598   \n",
       "C_ID_000844d8f1                            -0.708820   \n",
       "...                                              ...   \n",
       "C_ID_fffaed41f0                            -0.687328   \n",
       "C_ID_fffb79fb56                            -0.671775   \n",
       "C_ID_fffba72dc5                            -0.435108   \n",
       "C_ID_fffbdf036b                            -0.660645   \n",
       "C_ID_fffbee5c24                                  NaN   \n",
       "C_ID_fffc96bf24                                  NaN   \n",
       "C_ID_fffcb74f49                            -0.649010   \n",
       "C_ID_fffcf66e12                                  NaN   \n",
       "C_ID_fffd070e0e                             1.795077   \n",
       "C_ID_fffd1207a8                            -0.679125   \n",
       "C_ID_fffd24dcf3                            -0.351734   \n",
       "C_ID_fffd337484                                  NaN   \n",
       "C_ID_fffd345048                            -0.667013   \n",
       "C_ID_fffd351e7f                            -0.612987   \n",
       "C_ID_fffd93c8bf                                  NaN   \n",
       "C_ID_fffd943b91                            -0.637790   \n",
       "C_ID_fffdad8aa0                            -0.697920   \n",
       "C_ID_fffde15ab6                            -0.706486   \n",
       "C_ID_fffdf34798                                  NaN   \n",
       "C_ID_fffe29a04a                            -0.697245   \n",
       "C_ID_fffe78b232                            -0.556576   \n",
       "C_ID_fffe866457                            -0.542865   \n",
       "C_ID_fffea6de74                                  NaN   \n",
       "C_ID_fffeced303                            -0.717456   \n",
       "C_ID_fffeed3a89                            -0.038712   \n",
       "C_ID_ffff1d9928                            -0.728876   \n",
       "C_ID_ffff579d3a                            -0.664262   \n",
       "C_ID_ffff756266                             0.378578   \n",
       "C_ID_ffff828181                            -0.622448   \n",
       "C_ID_fffffd5772                                  NaN   \n",
       "\n",
       "                 category_2_2.0_purchase_amount_mean  \\\n",
       "card_id                                                \n",
       "C_ID_00007093c1                                  NaN   \n",
       "C_ID_0001238066                                  NaN   \n",
       "C_ID_0001506ef0                                  NaN   \n",
       "C_ID_0001793786                            -0.344766   \n",
       "C_ID_000183fdda                                  NaN   \n",
       "C_ID_0002709b5a                            -0.726367   \n",
       "C_ID_000298032a                                  NaN   \n",
       "C_ID_0002ba3c2e                            -0.690353   \n",
       "C_ID_0002c7c2c1                                  NaN   \n",
       "C_ID_00032df08f                                  NaN   \n",
       "C_ID_0003754056                                  NaN   \n",
       "C_ID_000377f6a0                                  NaN   \n",
       "C_ID_0003be3c83                                  NaN   \n",
       "C_ID_0003f41435                                  NaN   \n",
       "C_ID_00042d509c                                  NaN   \n",
       "C_ID_0004587331                                  NaN   \n",
       "C_ID_0004725b87                                  NaN   \n",
       "C_ID_0004888ddd                                  NaN   \n",
       "C_ID_0004b68c49                                  NaN   \n",
       "C_ID_0004c2a5ab                                  NaN   \n",
       "C_ID_00057b99fe                                  NaN   \n",
       "C_ID_000599daf9                                  NaN   \n",
       "C_ID_0005f16cc8                                  NaN   \n",
       "C_ID_0006152db8                                  NaN   \n",
       "C_ID_000616f4a8                                  NaN   \n",
       "C_ID_000664aa02                                  NaN   \n",
       "C_ID_00079fec55                                  NaN   \n",
       "C_ID_0007a60a33                                  NaN   \n",
       "C_ID_0007fe3157                                  NaN   \n",
       "C_ID_000844d8f1                                  NaN   \n",
       "...                                              ...   \n",
       "C_ID_fffaed41f0                                  NaN   \n",
       "C_ID_fffb79fb56                                  NaN   \n",
       "C_ID_fffba72dc5                                  NaN   \n",
       "C_ID_fffbdf036b                                  NaN   \n",
       "C_ID_fffbee5c24                            -0.571759   \n",
       "C_ID_fffc96bf24                                  NaN   \n",
       "C_ID_fffcb74f49                                  NaN   \n",
       "C_ID_fffcf66e12                                  NaN   \n",
       "C_ID_fffd070e0e                                  NaN   \n",
       "C_ID_fffd1207a8                                  NaN   \n",
       "C_ID_fffd24dcf3                                  NaN   \n",
       "C_ID_fffd337484                                  NaN   \n",
       "C_ID_fffd345048                                  NaN   \n",
       "C_ID_fffd351e7f                            -0.296212   \n",
       "C_ID_fffd93c8bf                                  NaN   \n",
       "C_ID_fffd943b91                                  NaN   \n",
       "C_ID_fffdad8aa0                                  NaN   \n",
       "C_ID_fffde15ab6                                  NaN   \n",
       "C_ID_fffdf34798                            -0.691915   \n",
       "C_ID_fffe29a04a                                  NaN   \n",
       "C_ID_fffe78b232                                  NaN   \n",
       "C_ID_fffe866457                                  NaN   \n",
       "C_ID_fffea6de74                                  NaN   \n",
       "C_ID_fffeced303                                  NaN   \n",
       "C_ID_fffeed3a89                                  NaN   \n",
       "C_ID_ffff1d9928                                  NaN   \n",
       "C_ID_ffff579d3a                                  NaN   \n",
       "C_ID_ffff756266                                  NaN   \n",
       "C_ID_ffff828181                            -0.679659   \n",
       "C_ID_fffffd5772                                  NaN   \n",
       "\n",
       "                 category_2_3.0_purchase_amount_mean  \\\n",
       "card_id                                                \n",
       "C_ID_00007093c1                            -0.671775   \n",
       "C_ID_0001238066                                  NaN   \n",
       "C_ID_0001506ef0                            -0.723677   \n",
       "C_ID_0001793786                             0.102887   \n",
       "C_ID_000183fdda                            -0.599162   \n",
       "C_ID_0002709b5a                                  NaN   \n",
       "C_ID_000298032a                                  NaN   \n",
       "C_ID_0002ba3c2e                                  NaN   \n",
       "C_ID_0002c7c2c1                                  NaN   \n",
       "C_ID_00032df08f                                  NaN   \n",
       "C_ID_0003754056                                  NaN   \n",
       "C_ID_000377f6a0                            -0.613172   \n",
       "C_ID_0003be3c83                                  NaN   \n",
       "C_ID_0003f41435                                  NaN   \n",
       "C_ID_00042d509c                            -0.720687   \n",
       "C_ID_0004587331                            -0.734271   \n",
       "C_ID_0004725b87                                  NaN   \n",
       "C_ID_0004888ddd                            -0.693113   \n",
       "C_ID_0004b68c49                                  NaN   \n",
       "C_ID_0004c2a5ab                                  NaN   \n",
       "C_ID_00057b99fe                                  NaN   \n",
       "C_ID_000599daf9                            -0.581434   \n",
       "C_ID_0005f16cc8                            -0.575666   \n",
       "C_ID_0006152db8                                  NaN   \n",
       "C_ID_000616f4a8                                  NaN   \n",
       "C_ID_000664aa02                                  NaN   \n",
       "C_ID_00079fec55                                  NaN   \n",
       "C_ID_0007a60a33                                  NaN   \n",
       "C_ID_0007fe3157                                  NaN   \n",
       "C_ID_000844d8f1                                  NaN   \n",
       "...                                              ...   \n",
       "C_ID_fffaed41f0                                  NaN   \n",
       "C_ID_fffb79fb56                                  NaN   \n",
       "C_ID_fffba72dc5                                  NaN   \n",
       "C_ID_fffbdf036b                                  NaN   \n",
       "C_ID_fffbee5c24                                  NaN   \n",
       "C_ID_fffc96bf24                                  NaN   \n",
       "C_ID_fffcb74f49                                  NaN   \n",
       "C_ID_fffcf66e12                                  NaN   \n",
       "C_ID_fffd070e0e                                  NaN   \n",
       "C_ID_fffd1207a8                                  NaN   \n",
       "C_ID_fffd24dcf3                                  NaN   \n",
       "C_ID_fffd337484                            -0.387184   \n",
       "C_ID_fffd345048                                  NaN   \n",
       "C_ID_fffd351e7f                            -0.687668   \n",
       "C_ID_fffd93c8bf                            -0.661382   \n",
       "C_ID_fffd943b91                                  NaN   \n",
       "C_ID_fffdad8aa0                                  NaN   \n",
       "C_ID_fffde15ab6                                  NaN   \n",
       "C_ID_fffdf34798                                  NaN   \n",
       "C_ID_fffe29a04a                                  NaN   \n",
       "C_ID_fffe78b232                                  NaN   \n",
       "C_ID_fffe866457                                  NaN   \n",
       "C_ID_fffea6de74                            -0.674931   \n",
       "C_ID_fffeced303                                  NaN   \n",
       "C_ID_fffeed3a89                            -0.730401   \n",
       "C_ID_ffff1d9928                                  NaN   \n",
       "C_ID_ffff579d3a                                  NaN   \n",
       "C_ID_ffff756266                                  NaN   \n",
       "C_ID_ffff828181                                  NaN   \n",
       "C_ID_fffffd5772                            -0.708490   \n",
       "\n",
       "                 category_2_4.0_purchase_amount_mean  \\\n",
       "card_id                                                \n",
       "C_ID_00007093c1                                  NaN   \n",
       "C_ID_0001238066                                  NaN   \n",
       "C_ID_0001506ef0                                  NaN   \n",
       "C_ID_0001793786                                  NaN   \n",
       "C_ID_000183fdda                                  NaN   \n",
       "C_ID_0002709b5a                                  NaN   \n",
       "C_ID_000298032a                                  NaN   \n",
       "C_ID_0002ba3c2e                                  NaN   \n",
       "C_ID_0002c7c2c1                                  NaN   \n",
       "C_ID_00032df08f                                  NaN   \n",
       "C_ID_0003754056                                  NaN   \n",
       "C_ID_000377f6a0                                  NaN   \n",
       "C_ID_0003be3c83                                  NaN   \n",
       "C_ID_0003f41435                                  NaN   \n",
       "C_ID_00042d509c                                  NaN   \n",
       "C_ID_0004587331                                  NaN   \n",
       "C_ID_0004725b87                                  NaN   \n",
       "C_ID_0004888ddd                                  NaN   \n",
       "C_ID_0004b68c49                                  NaN   \n",
       "C_ID_0004c2a5ab                                  NaN   \n",
       "C_ID_00057b99fe                                  NaN   \n",
       "C_ID_000599daf9                                  NaN   \n",
       "C_ID_0005f16cc8                                  NaN   \n",
       "C_ID_0006152db8                            -0.533231   \n",
       "C_ID_000616f4a8                                  NaN   \n",
       "C_ID_000664aa02                                  NaN   \n",
       "C_ID_00079fec55                                  NaN   \n",
       "C_ID_0007a60a33                                  NaN   \n",
       "C_ID_0007fe3157                                  NaN   \n",
       "C_ID_000844d8f1                                  NaN   \n",
       "...                                              ...   \n",
       "C_ID_fffaed41f0                                  NaN   \n",
       "C_ID_fffb79fb56                                  NaN   \n",
       "C_ID_fffba72dc5                                  NaN   \n",
       "C_ID_fffbdf036b                                  NaN   \n",
       "C_ID_fffbee5c24                                  NaN   \n",
       "C_ID_fffc96bf24                             0.264016   \n",
       "C_ID_fffcb74f49                                  NaN   \n",
       "C_ID_fffcf66e12                                  NaN   \n",
       "C_ID_fffd070e0e                                  NaN   \n",
       "C_ID_fffd1207a8                                  NaN   \n",
       "C_ID_fffd24dcf3                                  NaN   \n",
       "C_ID_fffd337484                                  NaN   \n",
       "C_ID_fffd345048                                  NaN   \n",
       "C_ID_fffd351e7f                            -0.695066   \n",
       "C_ID_fffd93c8bf                            -0.562809   \n",
       "C_ID_fffd943b91                                  NaN   \n",
       "C_ID_fffdad8aa0                                  NaN   \n",
       "C_ID_fffde15ab6                                  NaN   \n",
       "C_ID_fffdf34798                                  NaN   \n",
       "C_ID_fffe29a04a                                  NaN   \n",
       "C_ID_fffe78b232                                  NaN   \n",
       "C_ID_fffe866457                                  NaN   \n",
       "C_ID_fffea6de74                                  NaN   \n",
       "C_ID_fffeced303                                  NaN   \n",
       "C_ID_fffeed3a89                            -0.564688   \n",
       "C_ID_ffff1d9928                            -0.577158   \n",
       "C_ID_ffff579d3a                                  NaN   \n",
       "C_ID_ffff756266                                  NaN   \n",
       "C_ID_ffff828181                            -0.528941   \n",
       "C_ID_fffffd5772                                  NaN   \n",
       "\n",
       "                 category_2_5.0_purchase_amount_mean  \\\n",
       "card_id                                                \n",
       "C_ID_00007093c1                                  NaN   \n",
       "C_ID_0001238066                            -0.495945   \n",
       "C_ID_0001506ef0                                  NaN   \n",
       "C_ID_0001793786                            -0.361628   \n",
       "C_ID_000183fdda                                  NaN   \n",
       "C_ID_0002709b5a                            -0.611669   \n",
       "C_ID_000298032a                                  NaN   \n",
       "C_ID_0002ba3c2e                                  NaN   \n",
       "C_ID_0002c7c2c1                                  NaN   \n",
       "C_ID_00032df08f                                  NaN   \n",
       "C_ID_0003754056                                  NaN   \n",
       "C_ID_000377f6a0                                  NaN   \n",
       "C_ID_0003be3c83                                  NaN   \n",
       "C_ID_0003f41435                                  NaN   \n",
       "C_ID_00042d509c                                  NaN   \n",
       "C_ID_0004587331                                  NaN   \n",
       "C_ID_0004725b87                                  NaN   \n",
       "C_ID_0004888ddd                                  NaN   \n",
       "C_ID_0004b68c49                                  NaN   \n",
       "C_ID_0004c2a5ab                                  NaN   \n",
       "C_ID_00057b99fe                                  NaN   \n",
       "C_ID_000599daf9                                  NaN   \n",
       "C_ID_0005f16cc8                                  NaN   \n",
       "C_ID_0006152db8                                  NaN   \n",
       "C_ID_000616f4a8                                  NaN   \n",
       "C_ID_000664aa02                                  NaN   \n",
       "C_ID_00079fec55                                  NaN   \n",
       "C_ID_0007a60a33                            -0.737892   \n",
       "C_ID_0007fe3157                                  NaN   \n",
       "C_ID_000844d8f1                            -0.731881   \n",
       "...                                              ...   \n",
       "C_ID_fffaed41f0                            -0.646389   \n",
       "C_ID_fffb79fb56                                  NaN   \n",
       "C_ID_fffba72dc5                                  NaN   \n",
       "C_ID_fffbdf036b                                  NaN   \n",
       "C_ID_fffbee5c24                                  NaN   \n",
       "C_ID_fffc96bf24                                  NaN   \n",
       "C_ID_fffcb74f49                                  NaN   \n",
       "C_ID_fffcf66e12                            -0.685825   \n",
       "C_ID_fffd070e0e                                  NaN   \n",
       "C_ID_fffd1207a8                                  NaN   \n",
       "C_ID_fffd24dcf3                                  NaN   \n",
       "C_ID_fffd337484                                  NaN   \n",
       "C_ID_fffd345048                                  NaN   \n",
       "C_ID_fffd351e7f                                  NaN   \n",
       "C_ID_fffd93c8bf                                  NaN   \n",
       "C_ID_fffd943b91                                  NaN   \n",
       "C_ID_fffdad8aa0                                  NaN   \n",
       "C_ID_fffde15ab6                                  NaN   \n",
       "C_ID_fffdf34798                            -0.684398   \n",
       "C_ID_fffe29a04a                                  NaN   \n",
       "C_ID_fffe78b232                                  NaN   \n",
       "C_ID_fffe866457                                  NaN   \n",
       "C_ID_fffea6de74                            -0.709342   \n",
       "C_ID_fffeced303                                  NaN   \n",
       "C_ID_fffeed3a89                            -0.601828   \n",
       "C_ID_ffff1d9928                                  NaN   \n",
       "C_ID_ffff579d3a                                  NaN   \n",
       "C_ID_ffff756266                                  NaN   \n",
       "C_ID_ffff828181                                  NaN   \n",
       "C_ID_fffffd5772                                  NaN   \n",
       "\n",
       "                 category_2_1.0_purchase_amount_sum  \\\n",
       "card_id                                               \n",
       "C_ID_00007093c1                           -0.656749   \n",
       "C_ID_0001238066                          -13.362220   \n",
       "C_ID_0001506ef0                                 NaN   \n",
       "C_ID_0001793786                            2.375707   \n",
       "C_ID_000183fdda                                 NaN   \n",
       "C_ID_0002709b5a                           -0.680791   \n",
       "C_ID_000298032a                           -0.641872   \n",
       "C_ID_0002ba3c2e                                 NaN   \n",
       "C_ID_0002c7c2c1                           -3.929448   \n",
       "C_ID_00032df08f                           -2.791535   \n",
       "C_ID_0003754056                           -0.709342   \n",
       "C_ID_000377f6a0                                 NaN   \n",
       "C_ID_0003be3c83                           -3.441312   \n",
       "C_ID_0003f41435                           -1.465641   \n",
       "C_ID_00042d509c                           -0.467264   \n",
       "C_ID_0004587331                                 NaN   \n",
       "C_ID_0004725b87                           -1.238395   \n",
       "C_ID_0004888ddd                                 NaN   \n",
       "C_ID_0004b68c49                           -1.222903   \n",
       "C_ID_0004c2a5ab                           -1.179115   \n",
       "C_ID_00057b99fe                           -0.701828   \n",
       "C_ID_000599daf9                                 NaN   \n",
       "C_ID_0005f16cc8                           -0.607161   \n",
       "C_ID_0006152db8                                 NaN   \n",
       "C_ID_000616f4a8                           -2.274788   \n",
       "C_ID_000664aa02                           -4.240256   \n",
       "C_ID_00079fec55                           -0.326466   \n",
       "C_ID_0007a60a33                                 NaN   \n",
       "C_ID_0007fe3157                           -6.044384   \n",
       "C_ID_000844d8f1                           -9.923478   \n",
       "...                                             ...   \n",
       "C_ID_fffaed41f0                           -1.374655   \n",
       "C_ID_fffb79fb56                           -0.671775   \n",
       "C_ID_fffba72dc5                           -0.870215   \n",
       "C_ID_fffbdf036b                          -13.873543   \n",
       "C_ID_fffbee5c24                                 NaN   \n",
       "C_ID_fffc96bf24                                 NaN   \n",
       "C_ID_fffcb74f49                           -1.947030   \n",
       "C_ID_fffcf66e12                                 NaN   \n",
       "C_ID_fffd070e0e                            5.385231   \n",
       "C_ID_fffd1207a8                           -6.791247   \n",
       "C_ID_fffd24dcf3                           -3.165605   \n",
       "C_ID_fffd337484                                 NaN   \n",
       "C_ID_fffd345048                          -11.339216   \n",
       "C_ID_fffd351e7f                           -6.129870   \n",
       "C_ID_fffd93c8bf                                 NaN   \n",
       "C_ID_fffd943b91                           -1.913371   \n",
       "C_ID_fffdad8aa0                           -9.770884   \n",
       "C_ID_fffde15ab6                           -0.706486   \n",
       "C_ID_fffdf34798                                 NaN   \n",
       "C_ID_fffe29a04a                           -2.788981   \n",
       "C_ID_fffe78b232                           -4.452611   \n",
       "C_ID_fffe866457                           -4.342917   \n",
       "C_ID_fffea6de74                                 NaN   \n",
       "C_ID_fffeced303                           -1.434912   \n",
       "C_ID_fffeed3a89                           -0.154848   \n",
       "C_ID_ffff1d9928                           -0.728876   \n",
       "C_ID_ffff579d3a                           -0.664262   \n",
       "C_ID_ffff756266                            0.378578   \n",
       "C_ID_ffff828181                           -1.867345   \n",
       "C_ID_fffffd5772                                 NaN   \n",
       "\n",
       "                 category_2_2.0_purchase_amount_sum  \\\n",
       "card_id                                               \n",
       "C_ID_00007093c1                                 NaN   \n",
       "C_ID_0001238066                                 NaN   \n",
       "C_ID_0001506ef0                                 NaN   \n",
       "C_ID_0001793786                           -2.758131   \n",
       "C_ID_000183fdda                                 NaN   \n",
       "C_ID_0002709b5a                           -0.726367   \n",
       "C_ID_000298032a                                 NaN   \n",
       "C_ID_0002ba3c2e                           -2.071059   \n",
       "C_ID_0002c7c2c1                                 NaN   \n",
       "C_ID_00032df08f                                 NaN   \n",
       "C_ID_0003754056                                 NaN   \n",
       "C_ID_000377f6a0                                 NaN   \n",
       "C_ID_0003be3c83                                 NaN   \n",
       "C_ID_0003f41435                                 NaN   \n",
       "C_ID_00042d509c                                 NaN   \n",
       "C_ID_0004587331                                 NaN   \n",
       "C_ID_0004725b87                                 NaN   \n",
       "C_ID_0004888ddd                                 NaN   \n",
       "C_ID_0004b68c49                                 NaN   \n",
       "C_ID_0004c2a5ab                                 NaN   \n",
       "C_ID_00057b99fe                                 NaN   \n",
       "C_ID_000599daf9                                 NaN   \n",
       "C_ID_0005f16cc8                                 NaN   \n",
       "C_ID_0006152db8                                 NaN   \n",
       "C_ID_000616f4a8                                 NaN   \n",
       "C_ID_000664aa02                                 NaN   \n",
       "C_ID_00079fec55                                 NaN   \n",
       "C_ID_0007a60a33                                 NaN   \n",
       "C_ID_0007fe3157                                 NaN   \n",
       "C_ID_000844d8f1                                 NaN   \n",
       "...                                             ...   \n",
       "C_ID_fffaed41f0                                 NaN   \n",
       "C_ID_fffb79fb56                                 NaN   \n",
       "C_ID_fffba72dc5                                 NaN   \n",
       "C_ID_fffbdf036b                                 NaN   \n",
       "C_ID_fffbee5c24                           -2.858794   \n",
       "C_ID_fffc96bf24                                 NaN   \n",
       "C_ID_fffcb74f49                                 NaN   \n",
       "C_ID_fffcf66e12                                 NaN   \n",
       "C_ID_fffd070e0e                                 NaN   \n",
       "C_ID_fffd1207a8                                 NaN   \n",
       "C_ID_fffd24dcf3                                 NaN   \n",
       "C_ID_fffd337484                                 NaN   \n",
       "C_ID_fffd345048                                 NaN   \n",
       "C_ID_fffd351e7f                           -2.962116   \n",
       "C_ID_fffd93c8bf                                 NaN   \n",
       "C_ID_fffd943b91                                 NaN   \n",
       "C_ID_fffdad8aa0                                 NaN   \n",
       "C_ID_fffde15ab6                                 NaN   \n",
       "C_ID_fffdf34798                           -2.767658   \n",
       "C_ID_fffe29a04a                                 NaN   \n",
       "C_ID_fffe78b232                                 NaN   \n",
       "C_ID_fffe866457                                 NaN   \n",
       "C_ID_fffea6de74                                 NaN   \n",
       "C_ID_fffeced303                                 NaN   \n",
       "C_ID_fffeed3a89                                 NaN   \n",
       "C_ID_ffff1d9928                                 NaN   \n",
       "C_ID_ffff579d3a                                 NaN   \n",
       "C_ID_ffff756266                                 NaN   \n",
       "C_ID_ffff828181                           -2.038977   \n",
       "C_ID_fffffd5772                                 NaN   \n",
       "\n",
       "                 category_2_3.0_purchase_amount_sum  \\\n",
       "card_id                                               \n",
       "C_ID_00007093c1                           -0.671775   \n",
       "C_ID_0001238066                                 NaN   \n",
       "C_ID_0001506ef0                           -1.447354   \n",
       "C_ID_0001793786                            0.514433   \n",
       "C_ID_000183fdda                           -6.590778   \n",
       "C_ID_0002709b5a                                 NaN   \n",
       "C_ID_000298032a                                 NaN   \n",
       "C_ID_0002ba3c2e                                 NaN   \n",
       "C_ID_0002c7c2c1                                 NaN   \n",
       "C_ID_00032df08f                                 NaN   \n",
       "C_ID_0003754056                                 NaN   \n",
       "C_ID_000377f6a0                           -0.613172   \n",
       "C_ID_0003be3c83                                 NaN   \n",
       "C_ID_0003f41435                                 NaN   \n",
       "C_ID_00042d509c                           -0.720687   \n",
       "C_ID_0004587331                           -1.468541   \n",
       "C_ID_0004725b87                                 NaN   \n",
       "C_ID_0004888ddd                           -1.386226   \n",
       "C_ID_0004b68c49                                 NaN   \n",
       "C_ID_0004c2a5ab                                 NaN   \n",
       "C_ID_00057b99fe                                 NaN   \n",
       "C_ID_000599daf9                           -5.232908   \n",
       "C_ID_0005f16cc8                           -1.151331   \n",
       "C_ID_0006152db8                                 NaN   \n",
       "C_ID_000616f4a8                                 NaN   \n",
       "C_ID_000664aa02                                 NaN   \n",
       "C_ID_00079fec55                                 NaN   \n",
       "C_ID_0007a60a33                                 NaN   \n",
       "C_ID_0007fe3157                                 NaN   \n",
       "C_ID_000844d8f1                                 NaN   \n",
       "...                                             ...   \n",
       "C_ID_fffaed41f0                                 NaN   \n",
       "C_ID_fffb79fb56                                 NaN   \n",
       "C_ID_fffba72dc5                                 NaN   \n",
       "C_ID_fffbdf036b                                 NaN   \n",
       "C_ID_fffbee5c24                                 NaN   \n",
       "C_ID_fffc96bf24                                 NaN   \n",
       "C_ID_fffcb74f49                                 NaN   \n",
       "C_ID_fffcf66e12                                 NaN   \n",
       "C_ID_fffd070e0e                                 NaN   \n",
       "C_ID_fffd1207a8                                 NaN   \n",
       "C_ID_fffd24dcf3                                 NaN   \n",
       "C_ID_fffd337484                           -1.548737   \n",
       "C_ID_fffd345048                                 NaN   \n",
       "C_ID_fffd351e7f                           -4.126010   \n",
       "C_ID_fffd93c8bf                           -3.968291   \n",
       "C_ID_fffd943b91                                 NaN   \n",
       "C_ID_fffdad8aa0                                 NaN   \n",
       "C_ID_fffde15ab6                                 NaN   \n",
       "C_ID_fffdf34798                                 NaN   \n",
       "C_ID_fffe29a04a                                 NaN   \n",
       "C_ID_fffe78b232                                 NaN   \n",
       "C_ID_fffe866457                                 NaN   \n",
       "C_ID_fffea6de74                           -7.424239   \n",
       "C_ID_fffeced303                                 NaN   \n",
       "C_ID_fffeed3a89                           -1.460802   \n",
       "C_ID_ffff1d9928                                 NaN   \n",
       "C_ID_ffff579d3a                                 NaN   \n",
       "C_ID_ffff756266                                 NaN   \n",
       "C_ID_ffff828181                                 NaN   \n",
       "C_ID_fffffd5772                           -2.125470   \n",
       "\n",
       "                 category_2_4.0_purchase_amount_sum  \\\n",
       "card_id                                               \n",
       "C_ID_00007093c1                                 NaN   \n",
       "C_ID_0001238066                                 NaN   \n",
       "C_ID_0001506ef0                                 NaN   \n",
       "C_ID_0001793786                                 NaN   \n",
       "C_ID_000183fdda                                 NaN   \n",
       "C_ID_0002709b5a                                 NaN   \n",
       "C_ID_000298032a                                 NaN   \n",
       "C_ID_0002ba3c2e                                 NaN   \n",
       "C_ID_0002c7c2c1                                 NaN   \n",
       "C_ID_00032df08f                                 NaN   \n",
       "C_ID_0003754056                                 NaN   \n",
       "C_ID_000377f6a0                                 NaN   \n",
       "C_ID_0003be3c83                                 NaN   \n",
       "C_ID_0003f41435                                 NaN   \n",
       "C_ID_00042d509c                                 NaN   \n",
       "C_ID_0004587331                                 NaN   \n",
       "C_ID_0004725b87                                 NaN   \n",
       "C_ID_0004888ddd                                 NaN   \n",
       "C_ID_0004b68c49                                 NaN   \n",
       "C_ID_0004c2a5ab                                 NaN   \n",
       "C_ID_00057b99fe                                 NaN   \n",
       "C_ID_000599daf9                                 NaN   \n",
       "C_ID_0005f16cc8                                 NaN   \n",
       "C_ID_0006152db8                           -1.066462   \n",
       "C_ID_000616f4a8                                 NaN   \n",
       "C_ID_000664aa02                                 NaN   \n",
       "C_ID_00079fec55                                 NaN   \n",
       "C_ID_0007a60a33                                 NaN   \n",
       "C_ID_0007fe3157                                 NaN   \n",
       "C_ID_000844d8f1                                 NaN   \n",
       "...                                             ...   \n",
       "C_ID_fffaed41f0                                 NaN   \n",
       "C_ID_fffb79fb56                                 NaN   \n",
       "C_ID_fffba72dc5                                 NaN   \n",
       "C_ID_fffbdf036b                                 NaN   \n",
       "C_ID_fffbee5c24                                 NaN   \n",
       "C_ID_fffc96bf24                            0.792047   \n",
       "C_ID_fffcb74f49                                 NaN   \n",
       "C_ID_fffcf66e12                                 NaN   \n",
       "C_ID_fffd070e0e                                 NaN   \n",
       "C_ID_fffd1207a8                                 NaN   \n",
       "C_ID_fffd24dcf3                                 NaN   \n",
       "C_ID_fffd337484                                 NaN   \n",
       "C_ID_fffd345048                                 NaN   \n",
       "C_ID_fffd351e7f                           -2.780265   \n",
       "C_ID_fffd93c8bf                           -3.939666   \n",
       "C_ID_fffd943b91                                 NaN   \n",
       "C_ID_fffdad8aa0                                 NaN   \n",
       "C_ID_fffde15ab6                                 NaN   \n",
       "C_ID_fffdf34798                                 NaN   \n",
       "C_ID_fffe29a04a                                 NaN   \n",
       "C_ID_fffe78b232                                 NaN   \n",
       "C_ID_fffe866457                                 NaN   \n",
       "C_ID_fffea6de74                                 NaN   \n",
       "C_ID_fffeced303                                 NaN   \n",
       "C_ID_fffeed3a89                           -3.952814   \n",
       "C_ID_ffff1d9928                           -1.731475   \n",
       "C_ID_ffff579d3a                                 NaN   \n",
       "C_ID_ffff756266                                 NaN   \n",
       "C_ID_ffff828181                           -1.057881   \n",
       "C_ID_fffffd5772                                 NaN   \n",
       "\n",
       "                 category_2_5.0_purchase_amount_sum  \n",
       "card_id                                              \n",
       "C_ID_00007093c1                                 NaN  \n",
       "C_ID_0001238066                           -1.487835  \n",
       "C_ID_0001506ef0                                 NaN  \n",
       "C_ID_0001793786                           -0.361628  \n",
       "C_ID_000183fdda                                 NaN  \n",
       "C_ID_0002709b5a                           -0.611669  \n",
       "C_ID_000298032a                                 NaN  \n",
       "C_ID_0002ba3c2e                                 NaN  \n",
       "C_ID_0002c7c2c1                                 NaN  \n",
       "C_ID_00032df08f                                 NaN  \n",
       "C_ID_0003754056                                 NaN  \n",
       "C_ID_000377f6a0                                 NaN  \n",
       "C_ID_0003be3c83                                 NaN  \n",
       "C_ID_0003f41435                                 NaN  \n",
       "C_ID_00042d509c                                 NaN  \n",
       "C_ID_0004587331                                 NaN  \n",
       "C_ID_0004725b87                                 NaN  \n",
       "C_ID_0004888ddd                                 NaN  \n",
       "C_ID_0004b68c49                                 NaN  \n",
       "C_ID_0004c2a5ab                                 NaN  \n",
       "C_ID_00057b99fe                                 NaN  \n",
       "C_ID_000599daf9                                 NaN  \n",
       "C_ID_0005f16cc8                                 NaN  \n",
       "C_ID_0006152db8                                 NaN  \n",
       "C_ID_000616f4a8                                 NaN  \n",
       "C_ID_000664aa02                                 NaN  \n",
       "C_ID_00079fec55                                 NaN  \n",
       "C_ID_0007a60a33                           -0.737892  \n",
       "C_ID_0007fe3157                                 NaN  \n",
       "C_ID_000844d8f1                           -0.731881  \n",
       "...                                             ...  \n",
       "C_ID_fffaed41f0                          -10.342222  \n",
       "C_ID_fffb79fb56                                 NaN  \n",
       "C_ID_fffba72dc5                                 NaN  \n",
       "C_ID_fffbdf036b                                 NaN  \n",
       "C_ID_fffbee5c24                                 NaN  \n",
       "C_ID_fffc96bf24                                 NaN  \n",
       "C_ID_fffcb74f49                                 NaN  \n",
       "C_ID_fffcf66e12                           -0.685825  \n",
       "C_ID_fffd070e0e                                 NaN  \n",
       "C_ID_fffd1207a8                                 NaN  \n",
       "C_ID_fffd24dcf3                                 NaN  \n",
       "C_ID_fffd337484                                 NaN  \n",
       "C_ID_fffd345048                                 NaN  \n",
       "C_ID_fffd351e7f                                 NaN  \n",
       "C_ID_fffd93c8bf                                 NaN  \n",
       "C_ID_fffd943b91                                 NaN  \n",
       "C_ID_fffdad8aa0                                 NaN  \n",
       "C_ID_fffde15ab6                                 NaN  \n",
       "C_ID_fffdf34798                           -0.684398  \n",
       "C_ID_fffe29a04a                                 NaN  \n",
       "C_ID_fffe78b232                                 NaN  \n",
       "C_ID_fffe866457                                 NaN  \n",
       "C_ID_fffea6de74                           -0.709342  \n",
       "C_ID_fffeced303                                 NaN  \n",
       "C_ID_fffeed3a89                          -22.869479  \n",
       "C_ID_ffff1d9928                                 NaN  \n",
       "C_ID_ffff579d3a                                 NaN  \n",
       "C_ID_ffff756266                                 NaN  \n",
       "C_ID_ffff828181                                 NaN  \n",
       "C_ID_fffffd5772                                 NaN  \n",
       "\n",
       "[290001 rows x 10 columns]"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_on_cat(new_hist_trans, 'category_2', 'purchase_amount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">purchase_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category_2</th>\n",
       "      <th>1.0</th>\n",
       "      <th>3.0</th>\n",
       "      <th>1.0</th>\n",
       "      <th>3.0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>-0.699484</td>\n",
       "      <td>-0.692948</td>\n",
       "      <td>-3.497421</td>\n",
       "      <td>-0.692948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>0.415764</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.247291</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                purchase_amount                              \n",
       "                           mean                 sum          \n",
       "category_2                  1.0       3.0       1.0       3.0\n",
       "card_id                                                      \n",
       "C_ID_05042ebd55       -0.699484 -0.692948 -3.497421 -0.692948\n",
       "C_ID_5c240d6e3c        0.415764       NaN  1.247291       NaN"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "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>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>-4.190368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>1.247291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      sum\n",
       "card_id                  \n",
       "C_ID_05042ebd55 -4.190368\n",
       "C_ID_5c240d6e3c  1.247291"
      ]
     },
     "execution_count": 224,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1.groupby(['card_id','category_2'])['purchase_amount'].agg(['sum']).groupby('card_id').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "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>category_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>card_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C_ID_05042ebd55</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_ID_5c240d6e3c</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 category_2\n",
       "card_id                    \n",
       "C_ID_05042ebd55           6\n",
       "C_ID_5c240d6e3c           3"
      ]
     },
     "execution_count": 225,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp1.groupby('card_id').agg({'category_2': 'count'})"
   ]
  },
  {
   "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.6"
  },
  "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
}