fraud-detection-4.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "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>TransactionID</th>\n",
       "      <th>id_01</th>\n",
       "      <th>id_02</th>\n",
       "      <th>id_03</th>\n",
       "      <th>id_04</th>\n",
       "      <th>id_05</th>\n",
       "      <th>id_06</th>\n",
       "      <th>id_07</th>\n",
       "      <th>id_08</th>\n",
       "      <th>id_09</th>\n",
       "      <th>...</th>\n",
       "      <th>id_31</th>\n",
       "      <th>id_32</th>\n",
       "      <th>id_33</th>\n",
       "      <th>id_34</th>\n",
       "      <th>id_35</th>\n",
       "      <th>id_36</th>\n",
       "      <th>id_37</th>\n",
       "      <th>id_38</th>\n",
       "      <th>DeviceType</th>\n",
       "      <th>DeviceInfo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2987004</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70787.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>samsung browser 6.2</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2220x1080</td>\n",
       "      <td>match_status:2</td>\n",
       "      <td>T</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>mobile</td>\n",
       "      <td>SAMSUNG SM-G892A Build/NRD90M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2987008</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>98945.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>mobile safari 11.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>1334x750</td>\n",
       "      <td>match_status:1</td>\n",
       "      <td>T</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>mobile</td>\n",
       "      <td>iOS Device</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2987010</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>191631.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>chrome 62.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>desktop</td>\n",
       "      <td>Windows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2987011</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>221832.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>chrome 62.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>desktop</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2987016</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7460.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>chrome 62.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1280x800</td>\n",
       "      <td>match_status:2</td>\n",
       "      <td>T</td>\n",
       "      <td>F</td>\n",
       "      <td>T</td>\n",
       "      <td>T</td>\n",
       "      <td>desktop</td>\n",
       "      <td>MacOS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   TransactionID  id_01     id_02  id_03  id_04  id_05  id_06  id_07  id_08  \\\n",
       "0        2987004    0.0   70787.0    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "1        2987008   -5.0   98945.0    NaN    NaN    0.0   -5.0    NaN    NaN   \n",
       "2        2987010   -5.0  191631.0    0.0    0.0    0.0    0.0    NaN    NaN   \n",
       "3        2987011   -5.0  221832.0    NaN    NaN    0.0   -6.0    NaN    NaN   \n",
       "4        2987016    0.0    7460.0    0.0    0.0    1.0    0.0    NaN    NaN   \n",
       "\n",
       "   id_09  ...                id_31  id_32      id_33           id_34  id_35  \\\n",
       "0    NaN  ...  samsung browser 6.2   32.0  2220x1080  match_status:2      T   \n",
       "1    NaN  ...   mobile safari 11.0   32.0   1334x750  match_status:1      T   \n",
       "2    0.0  ...          chrome 62.0    NaN        NaN             NaN      F   \n",
       "3    NaN  ...          chrome 62.0    NaN        NaN             NaN      F   \n",
       "4    0.0  ...          chrome 62.0   24.0   1280x800  match_status:2      T   \n",
       "\n",
       "  id_36 id_37  id_38  DeviceType                     DeviceInfo  \n",
       "0     F     T      T      mobile  SAMSUNG SM-G892A Build/NRD90M  \n",
       "1     F     F      T      mobile                     iOS Device  \n",
       "2     F     T      T     desktop                        Windows  \n",
       "3     F     T      T     desktop                            NaN  \n",
       "4     F     T      T     desktop                          MacOS  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_identity = pd.read_csv('/home/other/kaggle/data/train_identity.csv')\n",
    "train_identity.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for col in train_identity.columns:\n",
    "#     if type(train_identity[col].iloc[0]) == np.float64:\n",
    "#         train_identity[col] = train_identity[col].astype(np.float16)\n",
    "# #train_identity.to_csv('/kaggle/low_dim/train_transaction.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>TransactionID</th>\n",
       "      <th>isFraud</th>\n",
       "      <th>TransactionDT</th>\n",
       "      <th>TransactionAmt</th>\n",
       "      <th>ProductCD</th>\n",
       "      <th>card1</th>\n",
       "      <th>card2</th>\n",
       "      <th>card3</th>\n",
       "      <th>card4</th>\n",
       "      <th>card5</th>\n",
       "      <th>...</th>\n",
       "      <th>V330</th>\n",
       "      <th>V331</th>\n",
       "      <th>V332</th>\n",
       "      <th>V333</th>\n",
       "      <th>V334</th>\n",
       "      <th>V335</th>\n",
       "      <th>V336</th>\n",
       "      <th>V337</th>\n",
       "      <th>V338</th>\n",
       "      <th>V339</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2987000</td>\n",
       "      <td>0</td>\n",
       "      <td>86400</td>\n",
       "      <td>68.5</td>\n",
       "      <td>W</td>\n",
       "      <td>13926</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150.0</td>\n",
       "      <td>discover</td>\n",
       "      <td>142.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2987001</td>\n",
       "      <td>0</td>\n",
       "      <td>86401</td>\n",
       "      <td>29.0</td>\n",
       "      <td>W</td>\n",
       "      <td>2755</td>\n",
       "      <td>404.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>mastercard</td>\n",
       "      <td>102.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2987002</td>\n",
       "      <td>0</td>\n",
       "      <td>86469</td>\n",
       "      <td>59.0</td>\n",
       "      <td>W</td>\n",
       "      <td>4663</td>\n",
       "      <td>490.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>visa</td>\n",
       "      <td>166.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2987003</td>\n",
       "      <td>0</td>\n",
       "      <td>86499</td>\n",
       "      <td>50.0</td>\n",
       "      <td>W</td>\n",
       "      <td>18132</td>\n",
       "      <td>567.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>mastercard</td>\n",
       "      <td>117.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2987004</td>\n",
       "      <td>0</td>\n",
       "      <td>86506</td>\n",
       "      <td>50.0</td>\n",
       "      <td>H</td>\n",
       "      <td>4497</td>\n",
       "      <td>514.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>mastercard</td>\n",
       "      <td>102.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 394 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   TransactionID  isFraud  TransactionDT  TransactionAmt ProductCD  card1  \\\n",
       "0        2987000        0          86400            68.5         W  13926   \n",
       "1        2987001        0          86401            29.0         W   2755   \n",
       "2        2987002        0          86469            59.0         W   4663   \n",
       "3        2987003        0          86499            50.0         W  18132   \n",
       "4        2987004        0          86506            50.0         H   4497   \n",
       "\n",
       "   card2  card3       card4  card5  ... V330  V331  V332  V333  V334 V335  \\\n",
       "0    NaN  150.0    discover  142.0  ...  NaN   NaN   NaN   NaN   NaN  NaN   \n",
       "1  404.0  150.0  mastercard  102.0  ...  NaN   NaN   NaN   NaN   NaN  NaN   \n",
       "2  490.0  150.0        visa  166.0  ...  NaN   NaN   NaN   NaN   NaN  NaN   \n",
       "3  567.0  150.0  mastercard  117.0  ...  NaN   NaN   NaN   NaN   NaN  NaN   \n",
       "4  514.0  150.0  mastercard  102.0  ...  0.0   0.0   0.0   0.0   0.0  0.0   \n",
       "\n",
       "  V336  V337  V338  V339  \n",
       "0  NaN   NaN   NaN   NaN  \n",
       "1  NaN   NaN   NaN   NaN  \n",
       "2  NaN   NaN   NaN   NaN  \n",
       "3  NaN   NaN   NaN   NaN  \n",
       "4  0.0   0.0   0.0   0.0  \n",
       "\n",
       "[5 rows x 394 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_transaction = pd.read_csv('/home/other/kaggle/data/train_transaction.csv')\n",
    "train_transaction.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for col in train_transaction.columns:\n",
    "#     if type(train_transaction[col].iloc[0]) == np.float64:\n",
    "#         train_transaction[col] = train_transaction[col].astype(np.float16)\n",
    "# train_transaction.to_csv('/kaggle/low_dim/train_transaction.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "target = 'isFraud'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((144233, 41), (590540, 394))"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_identity.shape, train_transaction.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(590540, 434)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(train_identity, train_transaction, on='TransactionID', how='outer')\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(590540, 220)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop columns where more than 50% of NaNs\n",
    "\n",
    "drop_cols = df.isna().sum()[df.isna().sum() > (df.shape[0] / 2)].index.tolist()\n",
    "df.drop(drop_cols, axis=1, inplace=True)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cat_cols_transaction:  ['ProductCD', 'addr1', 'addr2', 'P_emaildomain', 'R_emaildomain', 'card1', 'card2', 'card3', 'card4', 'card5', 'card6', 'M1', 'M2', 'M3', 'M4', 'M5', 'M6', 'M7', 'M8', 'M9']\n",
      "cat_cols_identity:  ['DeviceType', 'DeviceInfo', 'id_12', 'id_13', 'id_14', 'id_15', 'id_16', 'id_17', 'id_18', 'id_19', 'id_20', 'id_21', 'id_22', 'id_23', 'id_24', 'id_25', 'id_26', 'id_27', 'id_28', 'id_29', 'id_30', 'id_31', 'id_32', 'id_33', 'id_34', 'id_35', 'id_36', 'id_37', 'id_38']\n"
     ]
    }
   ],
   "source": [
    "cat_cols_transaction = ['ProductCD',  \n",
    "                        'addr1', \n",
    "                        'addr2',\n",
    "                        'P_emaildomain',\n",
    "                        'R_emaildomain']\n",
    "cat_cols_transaction.extend(['card{}'.format(i) for i in range(1, 7)])\n",
    "cat_cols_transaction.extend(['M{}'.format(i) for i in range(1, 10)])\n",
    "\n",
    "cat_cols_identity = ['DeviceType',\n",
    "                     'DeviceInfo']\n",
    "cat_cols_identity.extend(['id_{}'.format(i) for i in range(12, 39)])\n",
    "\n",
    "print('cat_cols_transaction: ', cat_cols_transaction)\n",
    "print('cat_cols_identity: ', cat_cols_identity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cat_cols_tr:  ['ProductCD', 'addr1', 'addr2', 'P_emaildomain', 'card1', 'card2', 'card3', 'card4', 'card5', 'card6', 'M1', 'M2', 'M3', 'M4', 'M6']\n",
      "cat_cols_id:  []\n"
     ]
    }
   ],
   "source": [
    "cat_cols_tr = []\n",
    "for col in cat_cols_transaction:\n",
    "    if col not in drop_cols:\n",
    "        cat_cols_tr.append(col)\n",
    "\n",
    "cat_cols_id = []\n",
    "for col in cat_cols_identity:\n",
    "    if col not in drop_cols:\n",
    "        cat_cols_id.append(col)\n",
    "        \n",
    "print('cat_cols_tr: ', cat_cols_tr)\n",
    "print('cat_cols_id: ', cat_cols_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for col in cat_cols_id:\n",
    "#     if df[col].nunique() < 3:\n",
    "#         print(col, df[col].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "M1 [nan 'T' 'F']\n",
      "M2 [nan 'T' 'F']\n",
      "M3 [nan 'T' 'F']\n",
      "M6 [nan 'T' 'F']\n"
     ]
    }
   ],
   "source": [
    "for col in cat_cols_tr:\n",
    "    if df[col].nunique() < 3:\n",
    "        print(col, df[col].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/pandas/core/frame.py:4323: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  downcast=downcast,\n"
     ]
    }
   ],
   "source": [
    "# train_identity[cat_cols_id].fillna(-1, inplace=True)\n",
    "df[cat_cols_tr].fillna(-1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "# not_cat_cols_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cat_cols_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# not_cat_cols_id = [col for col in df.columns if col not in cat_cols_id and col != target]\n",
    "not_cat_cols_tr = [col for col in df.columns if col not in cat_cols_tr and col != target]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TransactionID     None\n",
       "TransactionDT     None\n",
       "TransactionAmt    None\n",
       "C1                None\n",
       "C2                None\n",
       "                  ... \n",
       "V317              None\n",
       "V318              None\n",
       "V319              None\n",
       "V320              None\n",
       "V321              None\n",
       "Length: 204, dtype: object"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df[not_cat_cols_id].apply(lambda x: x.fillna(x.values.mean(), inplace=True))\n",
    "df[not_cat_cols_tr].apply(lambda x: x.fillna(x.values.mean(), inplace=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([   0.        ,  100.        ,   48.63809967, ..., 3816.        ,\n",
       "        680.5       , 1908.        ])"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['V317'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TransactionDT - timedelta. Suggest that it is the number of seconds since 1st January 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>TransactionDT</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>86506</td>\n",
       "      <td>1970-01-02 00:01:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>86535</td>\n",
       "      <td>1970-01-02 00:02:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>86549</td>\n",
       "      <td>1970-01-02 00:02:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>86555</td>\n",
       "      <td>1970-01-02 00:02:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>86620</td>\n",
       "      <td>1970-01-02 00:03:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590535</th>\n",
       "      <td>15811047</td>\n",
       "      <td>1970-07-02 23:57:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590536</th>\n",
       "      <td>15811049</td>\n",
       "      <td>1970-07-02 23:57:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590537</th>\n",
       "      <td>15811079</td>\n",
       "      <td>1970-07-02 23:57:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590538</th>\n",
       "      <td>15811088</td>\n",
       "      <td>1970-07-02 23:58:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590539</th>\n",
       "      <td>15811131</td>\n",
       "      <td>1970-07-02 23:58:51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>590540 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        TransactionDT           timestamp\n",
       "0               86506 1970-01-02 00:01:46\n",
       "1               86535 1970-01-02 00:02:15\n",
       "2               86549 1970-01-02 00:02:29\n",
       "3               86555 1970-01-02 00:02:35\n",
       "4               86620 1970-01-02 00:03:40\n",
       "...               ...                 ...\n",
       "590535       15811047 1970-07-02 23:57:27\n",
       "590536       15811049 1970-07-02 23:57:29\n",
       "590537       15811079 1970-07-02 23:57:59\n",
       "590538       15811088 1970-07-02 23:58:08\n",
       "590539       15811131 1970-07-02 23:58:51\n",
       "\n",
       "[590540 rows x 2 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['timestamp'] = pd.to_datetime(df['TransactionDT'], unit='s')\n",
    "df[['TransactionDT', 'timestamp']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1970"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['timestamp'].iloc[0].year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df['timestamp']:\n",
    "    if x.year != 1970:\n",
    "        print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = df['timestamp'].iloc[0].date()\n",
    "i = 0 \n",
    "j = 0\n",
    "date_month_agg_not_sort = []\n",
    "while i < len(df):\n",
    "    #print(df['timestamp'].iloc[i].date(), start_date, df['timestamp'].iloc[i].date() == start_date)\n",
    "    if df['timestamp'].iloc[i].date() == start_date:\n",
    "        date_month_agg_not_sort.append(j)\n",
    "    else:\n",
    "        j += 1\n",
    "        start_date = df['timestamp'].iloc[i].date()\n",
    "        date_month_agg_not_sort.append(j)\n",
    "    i += 1\n",
    "len(date_month_agg_not_sort) == len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>timestamp</th>\n",
       "      <th>date_month_agg_not_sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1970-01-02 00:01:46</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1970-01-02 00:02:15</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1970-01-02 00:02:29</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1970-01-02 00:02:35</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1970-01-02 00:03:40</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590535</th>\n",
       "      <td>1970-07-02 23:57:27</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590536</th>\n",
       "      <td>1970-07-02 23:57:29</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590537</th>\n",
       "      <td>1970-07-02 23:57:59</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590538</th>\n",
       "      <td>1970-07-02 23:58:08</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590539</th>\n",
       "      <td>1970-07-02 23:58:51</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>590540 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 timestamp  date_month_agg_not_sort\n",
       "0      1970-01-02 00:01:46                        0\n",
       "1      1970-01-02 00:02:15                        0\n",
       "2      1970-01-02 00:02:29                        0\n",
       "3      1970-01-02 00:02:35                        0\n",
       "4      1970-01-02 00:03:40                        0\n",
       "...                    ...                      ...\n",
       "590535 1970-07-02 23:57:27                      363\n",
       "590536 1970-07-02 23:57:29                      363\n",
       "590537 1970-07-02 23:57:59                      363\n",
       "590538 1970-07-02 23:58:08                      363\n",
       "590539 1970-07-02 23:58:51                      363\n",
       "\n",
       "[590540 rows x 2 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['date_month_agg_not_sort'] = date_month_agg_not_sort\n",
    "df[['timestamp', 'date_month_agg_not_sort']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>TransactionID</th>\n",
       "      <th>isFraud</th>\n",
       "      <th>TransactionDT</th>\n",
       "      <th>TransactionAmt</th>\n",
       "      <th>ProductCD</th>\n",
       "      <th>card1</th>\n",
       "      <th>card2</th>\n",
       "      <th>card3</th>\n",
       "      <th>card4</th>\n",
       "      <th>card5</th>\n",
       "      <th>...</th>\n",
       "      <th>V314</th>\n",
       "      <th>V315</th>\n",
       "      <th>V316</th>\n",
       "      <th>V317</th>\n",
       "      <th>V318</th>\n",
       "      <th>V319</th>\n",
       "      <th>V320</th>\n",
       "      <th>V321</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_month_agg_not_sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>144233</th>\n",
       "      <td>2987000</td>\n",
       "      <td>0</td>\n",
       "      <td>86400</td>\n",
       "      <td>68.5</td>\n",
       "      <td>W</td>\n",
       "      <td>13926</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150.0</td>\n",
       "      <td>discover</td>\n",
       "      <td>142.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-01-02 00:00:00</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144234</th>\n",
       "      <td>2987001</td>\n",
       "      <td>0</td>\n",
       "      <td>86401</td>\n",
       "      <td>29.0</td>\n",
       "      <td>W</td>\n",
       "      <td>2755</td>\n",
       "      <td>404.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>mastercard</td>\n",
       "      <td>102.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-01-02 00:00:01</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144235</th>\n",
       "      <td>2987002</td>\n",
       "      <td>0</td>\n",
       "      <td>86469</td>\n",
       "      <td>59.0</td>\n",
       "      <td>W</td>\n",
       "      <td>4663</td>\n",
       "      <td>490.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>visa</td>\n",
       "      <td>166.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-01-02 00:01:09</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144236</th>\n",
       "      <td>2987003</td>\n",
       "      <td>0</td>\n",
       "      <td>86499</td>\n",
       "      <td>50.0</td>\n",
       "      <td>W</td>\n",
       "      <td>18132</td>\n",
       "      <td>567.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>mastercard</td>\n",
       "      <td>117.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>790.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-01-02 00:01:39</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2987004</td>\n",
       "      <td>0</td>\n",
       "      <td>86506</td>\n",
       "      <td>50.0</td>\n",
       "      <td>H</td>\n",
       "      <td>4497</td>\n",
       "      <td>514.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>mastercard</td>\n",
       "      <td>102.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-01-02 00:01:46</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 222 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        TransactionID  isFraud  TransactionDT  TransactionAmt ProductCD  \\\n",
       "144233        2987000        0          86400            68.5         W   \n",
       "144234        2987001        0          86401            29.0         W   \n",
       "144235        2987002        0          86469            59.0         W   \n",
       "144236        2987003        0          86499            50.0         W   \n",
       "0             2987004        0          86506            50.0         H   \n",
       "\n",
       "        card1  card2  card3       card4  card5  ... V314  V315  V316    V317  \\\n",
       "144233  13926    NaN  150.0    discover  142.0  ...  0.0   0.0   0.0   117.0   \n",
       "144234   2755  404.0  150.0  mastercard  102.0  ...  0.0   0.0   0.0     0.0   \n",
       "144235   4663  490.0  150.0        visa  166.0  ...  0.0   0.0   0.0     0.0   \n",
       "144236  18132  567.0  150.0  mastercard  117.0  ...  0.0   0.0  50.0  1404.0   \n",
       "0        4497  514.0  150.0  mastercard  102.0  ...  0.0   0.0   0.0     0.0   \n",
       "\n",
       "         V318  V319  V320  V321           timestamp  date_month_agg_not_sort  \n",
       "144233    0.0   0.0   0.0   0.0 1970-01-02 00:00:00                      182  \n",
       "144234    0.0   0.0   0.0   0.0 1970-01-02 00:00:01                      182  \n",
       "144235    0.0   0.0   0.0   0.0 1970-01-02 00:01:09                      182  \n",
       "144236  790.0   0.0   0.0   0.0 1970-01-02 00:01:39                      182  \n",
       "0         0.0   0.0   0.0   0.0 1970-01-02 00:01:46                        0  \n",
       "\n",
       "[5 rows x 222 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by=['timestamp'], inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All rows are belonged to one year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = df['timestamp'].iloc[0].date()\n",
    "i = 0 \n",
    "j = 0\n",
    "date_month_agg = []\n",
    "while i < len(df):\n",
    "    #print(df['timestamp'].iloc[i].date(), start_date, df['timestamp'].iloc[i].date() == start_date)\n",
    "    if df['timestamp'].iloc[i].date() == start_date:\n",
    "        date_month_agg.append(j)\n",
    "    else:\n",
    "        j += 1\n",
    "        start_date = df['timestamp'].iloc[i].date()\n",
    "        date_month_agg.append(j)\n",
    "    i += 1\n",
    "len(date_month_agg) == len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>timestamp</th>\n",
       "      <th>date_month_agg</th>\n",
       "      <th>date_month_agg_not_sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>144233</th>\n",
       "      <td>1970-01-02 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144234</th>\n",
       "      <td>1970-01-02 00:00:01</td>\n",
       "      <td>0</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144235</th>\n",
       "      <td>1970-01-02 00:01:09</td>\n",
       "      <td>0</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144236</th>\n",
       "      <td>1970-01-02 00:01:39</td>\n",
       "      <td>0</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1970-01-02 00:01:46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590535</th>\n",
       "      <td>1970-07-02 23:57:27</td>\n",
       "      <td>181</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590536</th>\n",
       "      <td>1970-07-02 23:57:29</td>\n",
       "      <td>181</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590537</th>\n",
       "      <td>1970-07-02 23:57:59</td>\n",
       "      <td>181</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590538</th>\n",
       "      <td>1970-07-02 23:58:08</td>\n",
       "      <td>181</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590539</th>\n",
       "      <td>1970-07-02 23:58:51</td>\n",
       "      <td>181</td>\n",
       "      <td>363</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>590540 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 timestamp  date_month_agg  date_month_agg_not_sort\n",
       "144233 1970-01-02 00:00:00               0                      182\n",
       "144234 1970-01-02 00:00:01               0                      182\n",
       "144235 1970-01-02 00:01:09               0                      182\n",
       "144236 1970-01-02 00:01:39               0                      182\n",
       "0      1970-01-02 00:01:46               0                        0\n",
       "...                    ...             ...                      ...\n",
       "590535 1970-07-02 23:57:27             181                      363\n",
       "590536 1970-07-02 23:57:29             181                      363\n",
       "590537 1970-07-02 23:57:59             181                      363\n",
       "590538 1970-07-02 23:58:08             181                      363\n",
       "590539 1970-07-02 23:58:51             181                      363\n",
       "\n",
       "[590540 rows x 3 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['date_month_agg'] = date_month_agg\n",
    "df[['timestamp', 'date_month_agg', 'date_month_agg_not_sort']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Encoding of categorical features\n",
    "\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "le = LabelEncoder()\n",
    "df[cat_cols_tr] = df[cat_cols_tr].apply(lambda x: le.fit_transform(x.astype(str)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>ProductCD</th>\n",
       "      <th>addr1</th>\n",
       "      <th>addr2</th>\n",
       "      <th>P_emaildomain</th>\n",
       "      <th>card1</th>\n",
       "      <th>card2</th>\n",
       "      <th>card3</th>\n",
       "      <th>card4</th>\n",
       "      <th>card5</th>\n",
       "      <th>card6</th>\n",
       "      <th>M1</th>\n",
       "      <th>M2</th>\n",
       "      <th>M3</th>\n",
       "      <th>M4</th>\n",
       "      <th>M6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>144233</th>\n",
       "      <td>4</td>\n",
       "      <td>166</td>\n",
       "      <td>65</td>\n",
       "      <td>32</td>\n",
       "      <td>3417</td>\n",
       "      <td>500</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144234</th>\n",
       "      <td>4</td>\n",
       "      <td>173</td>\n",
       "      <td>65</td>\n",
       "      <td>16</td>\n",
       "      <td>7922</td>\n",
       "      <td>303</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144235</th>\n",
       "      <td>4</td>\n",
       "      <td>178</td>\n",
       "      <td>65</td>\n",
       "      <td>36</td>\n",
       "      <td>9383</td>\n",
       "      <td>389</td>\n",
       "      <td>42</td>\n",
       "      <td>4</td>\n",
       "      <td>58</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144236</th>\n",
       "      <td>4</td>\n",
       "      <td>282</td>\n",
       "      <td>65</td>\n",
       "      <td>54</td>\n",
       "      <td>6991</td>\n",
       "      <td>466</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>241</td>\n",
       "      <td>65</td>\n",
       "      <td>16</td>\n",
       "      <td>9262</td>\n",
       "      <td>413</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ProductCD  addr1  addr2  P_emaildomain  card1  card2  card3  card4  \\\n",
       "144233          4    166     65             32   3417    500     42      1   \n",
       "144234          4    173     65             16   7922    303     42      2   \n",
       "144235          4    178     65             36   9383    389     42      4   \n",
       "144236          4    282     65             54   6991    466     42      2   \n",
       "0               1    241     65             16   9262    413     42      2   \n",
       "\n",
       "        card5  card6  M1  M2  M3  M4  M6  \n",
       "144233     38      1   1   1   1   2   1  \n",
       "144234      2      1   2   2   2   0   1  \n",
       "144235     58      2   1   1   1   0   0  \n",
       "144236     14      2   2   2   2   0   0  \n",
       "0           2      1   2   2   2   3   2  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[cat_cols_tr].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop('timestamp', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "X_train, X_val, y_train, y_val = train_test_split(df.drop(target, axis=1), \n",
    "                                                  df[target], \n",
    "                                                  test_size=0.33, \n",
    "                                                  shuffle=True, \n",
    "                                                  stratify=df[target])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[3.0633770e+06, 1.6643730e+06, 2.0995000e+02, ..., 0.0000000e+00,\n",
       "        2.0000000e+02, 1.8000000e+01],\n",
       "       [3.2797050e+06, 7.2312720e+06, 1.1700000e+02, ..., 0.0000000e+00,\n",
       "        2.6400000e+02, 8.2000000e+01],\n",
       "       [3.5150510e+06, 1.3893113e+07, 7.0272000e+01, ..., 0.0000000e+00,\n",
       "        3.4100000e+02, 1.5900000e+02],\n",
       "       ...,\n",
       "       [3.1187860e+06, 2.6080220e+06, 5.9480000e+01, ..., 0.0000000e+00,\n",
       "        2.1100000e+02, 2.9000000e+01],\n",
       "       [3.1081920e+06, 2.3886260e+06, 2.0595000e+02, ..., 0.0000000e+00,\n",
       "        2.0800000e+02, 2.6000000e+01],\n",
       "       [3.1449440e+06, 3.2855560e+06, 2.3100000e+02, ..., 0.0000000e+00,\n",
       "        2.1900000e+02, 3.7000000e+01]])"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_train.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9270689956497645"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from lightgbm import LGBMClassifier\n",
    "from sklearn.metrics import roc_auc_score\n",
    "\n",
    "clf = LGBMClassifier()\n",
    "clf.fit(X_train.values, y_train.values)\n",
    "proba = clf.predict_proba(X_val.values)[:, 1]\n",
    "\n",
    "roc_auc_score(y_val, proba)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Make a prediction for test dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (0,1,2,3,4,5,6,7,8,9,10,11,13,14,17,18,19,20,21,22,24,25,26,32) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(141907, 41)"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_identity = pd.read_csv('/home/other/kaggle/data/test_identity.csv', names=train_identity.columns)\n",
    "test_identity.drop(0, axis=0, inplace=True)\n",
    "test_identity.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for col in test_identity.columns:\n",
    "#     if type(test_identity[col].iloc[0]) == np.float64:\n",
    "#         test_identity[col] = test_identity[col].astype(np.float16)\n",
    "# test_identity.to_csv('/kaggle/low_dim/test_identity.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "tr_cols = list(train_transaction.columns)\n",
    "tr_cols.remove(target)\n",
    "#tr_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (0,1,2,4,5,6,8,10,11,12,13,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(506691, 393)"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_transaction = pd.read_csv('/home/other/kaggle/data/test_transaction.csv', names=tr_cols)\n",
    "test_transaction.drop(0, axis=0, inplace=True)\n",
    "test_transaction.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for col in test_transaction.columns:\n",
    "#     if type(test_transaction[col].iloc[0]) == np.float64:\n",
    "#         test_transaction[col] = test_transaction[col].astype(np.float16)\n",
    "# test_transaction.to_csv('/kaggle/low_dim/test_transaction.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(506691, 433)"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.merge(test_identity, test_transaction, on='TransactionID', how='right')\n",
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(506691, 219)"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.drop(drop_cols, axis=1, inplace=True)\n",
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/pandas/core/frame.py:4323: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  downcast=downcast,\n"
     ]
    },
    {
     "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>TransactionDT</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18403224</td>\n",
       "      <td>1970-08-02 00:00:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18403263</td>\n",
       "      <td>1970-08-02 00:01:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18403310</td>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18403310</td>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18403317</td>\n",
       "      <td>1970-08-02 00:01:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506686</th>\n",
       "      <td>34214279</td>\n",
       "      <td>1971-01-31 23:57:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506687</th>\n",
       "      <td>34214287</td>\n",
       "      <td>1971-01-31 23:58:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506688</th>\n",
       "      <td>34214326</td>\n",
       "      <td>1971-01-31 23:58:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506689</th>\n",
       "      <td>34214337</td>\n",
       "      <td>1971-01-31 23:58:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506690</th>\n",
       "      <td>34214345</td>\n",
       "      <td>1971-01-31 23:59:05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>506691 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       TransactionDT           timestamp\n",
       "0           18403224 1970-08-02 00:00:24\n",
       "1           18403263 1970-08-02 00:01:03\n",
       "2           18403310 1970-08-02 00:01:50\n",
       "3           18403310 1970-08-02 00:01:50\n",
       "4           18403317 1970-08-02 00:01:57\n",
       "...              ...                 ...\n",
       "506686      34214279 1971-01-31 23:57:59\n",
       "506687      34214287 1971-01-31 23:58:07\n",
       "506688      34214326 1971-01-31 23:58:46\n",
       "506689      34214337 1971-01-31 23:58:57\n",
       "506690      34214345 1971-01-31 23:59:05\n",
       "\n",
       "[506691 rows x 2 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test[cat_cols_tr].fillna(-1, inplace=True)\n",
    "test[not_cat_cols_tr].apply(lambda x: x.fillna(x.values.astype(np.float32).mean(), inplace=True))\n",
    "\n",
    "test['timestamp'] = pd.to_datetime(test['TransactionDT'], unit='s')\n",
    "test[['TransactionDT', 'timestamp']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1970-07-02 23:58:51\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = pd.to_datetime(df['TransactionDT'].iloc[-1], unit='s')\n",
    "print(start_date)\n",
    "i = 0 \n",
    "j = date_month_agg_not_sort[-1] + 1\n",
    "date_month_agg_not_sort = []\n",
    "while i < len(test):\n",
    "    #print(df['timestamp'].iloc[i].date(), start_date, df['timestamp'].iloc[i].date() == start_date)\n",
    "    if test['timestamp'].iloc[i].date() == start_date:\n",
    "        date_month_agg_not_sort.append(j)\n",
    "    else:\n",
    "        j += 1\n",
    "        start_date = test['timestamp'].iloc[i].date()\n",
    "        date_month_agg_not_sort.append(j)\n",
    "    i += 1\n",
    "len(date_month_agg_not_sort) == len(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_month_agg_not_sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1970-08-02 00:00:24</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1970-08-02 00:01:03</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1970-08-02 00:01:57</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506686</th>\n",
       "      <td>1971-01-31 23:57:59</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506687</th>\n",
       "      <td>1971-01-31 23:58:07</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506688</th>\n",
       "      <td>1971-01-31 23:58:46</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506689</th>\n",
       "      <td>1971-01-31 23:58:57</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506690</th>\n",
       "      <td>1971-01-31 23:59:05</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>506691 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 timestamp  date_month_agg_not_sort\n",
       "0      1970-08-02 00:00:24                      365\n",
       "1      1970-08-02 00:01:03                      365\n",
       "2      1970-08-02 00:01:50                      365\n",
       "3      1970-08-02 00:01:50                      365\n",
       "4      1970-08-02 00:01:57                      365\n",
       "...                    ...                      ...\n",
       "506686 1971-01-31 23:57:59                      547\n",
       "506687 1971-01-31 23:58:07                      547\n",
       "506688 1971-01-31 23:58:46                      547\n",
       "506689 1971-01-31 23:58:57                      547\n",
       "506690 1971-01-31 23:59:05                      547\n",
       "\n",
       "[506691 rows x 2 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['date_month_agg_not_sort'] = date_month_agg_not_sort\n",
    "test[['timestamp', 'date_month_agg_not_sort']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "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>TransactionID</th>\n",
       "      <th>TransactionDT</th>\n",
       "      <th>TransactionAmt</th>\n",
       "      <th>ProductCD</th>\n",
       "      <th>card1</th>\n",
       "      <th>card2</th>\n",
       "      <th>card3</th>\n",
       "      <th>card4</th>\n",
       "      <th>card5</th>\n",
       "      <th>card6</th>\n",
       "      <th>...</th>\n",
       "      <th>V314</th>\n",
       "      <th>V315</th>\n",
       "      <th>V316</th>\n",
       "      <th>V317</th>\n",
       "      <th>V318</th>\n",
       "      <th>V319</th>\n",
       "      <th>V320</th>\n",
       "      <th>V321</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_month_agg_not_sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3663549</td>\n",
       "      <td>18403224</td>\n",
       "      <td>31.95</td>\n",
       "      <td>W</td>\n",
       "      <td>10409</td>\n",
       "      <td>111.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>visa</td>\n",
       "      <td>226.0</td>\n",
       "      <td>debit</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-08-02 00:00:24</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3663550</td>\n",
       "      <td>18403263</td>\n",
       "      <td>49.0</td>\n",
       "      <td>W</td>\n",
       "      <td>4272</td>\n",
       "      <td>111.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>visa</td>\n",
       "      <td>226.0</td>\n",
       "      <td>debit</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-08-02 00:01:03</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3663551</td>\n",
       "      <td>18403310</td>\n",
       "      <td>171.0</td>\n",
       "      <td>W</td>\n",
       "      <td>4476</td>\n",
       "      <td>574.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>visa</td>\n",
       "      <td>226.0</td>\n",
       "      <td>debit</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>263.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3663552</td>\n",
       "      <td>18403310</td>\n",
       "      <td>284.95</td>\n",
       "      <td>W</td>\n",
       "      <td>10989</td>\n",
       "      <td>360.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>visa</td>\n",
       "      <td>166.0</td>\n",
       "      <td>debit</td>\n",
       "      <td>...</td>\n",
       "      <td>282.5400085449219</td>\n",
       "      <td>282.5400085449219</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3663553</td>\n",
       "      <td>18403317</td>\n",
       "      <td>67.95</td>\n",
       "      <td>W</td>\n",
       "      <td>18018</td>\n",
       "      <td>452.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>mastercard</td>\n",
       "      <td>117.0</td>\n",
       "      <td>debit</td>\n",
       "      <td>...</td>\n",
       "      <td>183.8500061035156</td>\n",
       "      <td>67.94999694824219</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1970-08-02 00:01:57</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 221 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  TransactionID TransactionDT TransactionAmt ProductCD  card1  card2  card3  \\\n",
       "0       3663549      18403224          31.95         W  10409  111.0  150.0   \n",
       "1       3663550      18403263           49.0         W   4272  111.0  150.0   \n",
       "2       3663551      18403310          171.0         W   4476  574.0  150.0   \n",
       "3       3663552      18403310         284.95         W  10989  360.0  150.0   \n",
       "4       3663553      18403317          67.95         W  18018  452.0  150.0   \n",
       "\n",
       "        card4  card5  card6  ...               V314               V315 V316  \\\n",
       "0        visa  226.0  debit  ...                0.0                0.0  0.0   \n",
       "1        visa  226.0  debit  ...                0.0                0.0  0.0   \n",
       "2        visa  226.0  debit  ...                0.0                0.0  0.0   \n",
       "3        visa  166.0  debit  ...  282.5400085449219  282.5400085449219  0.0   \n",
       "4  mastercard  117.0  debit  ...  183.8500061035156  67.94999694824219  0.0   \n",
       "\n",
       "  V317 V318 V319   V320 V321           timestamp date_month_agg_not_sort  \n",
       "0  0.0  0.0  0.0    0.0  0.0 1970-08-02 00:00:24                     365  \n",
       "1  0.0  0.0  0.0    0.0  0.0 1970-08-02 00:01:03                     365  \n",
       "2  0.0  0.0  0.0  263.0  0.0 1970-08-02 00:01:50                     365  \n",
       "3  0.0  0.0  0.0    0.0  0.0 1970-08-02 00:01:50                     365  \n",
       "4  0.0  0.0  0.0    0.0  0.0 1970-08-02 00:01:57                     365  \n",
       "\n",
       "[5 rows x 221 columns]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.sort_values(by=['timestamp'], inplace=True)\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "181"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_month_agg[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1970-07-02 23:58:51\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = pd.to_datetime(df['TransactionDT'].iloc[-1], unit='s')\n",
    "print(start_date)\n",
    "i = 0 \n",
    "j = date_month_agg[-1] + 1\n",
    "date_month_agg = []\n",
    "while i < len(test):\n",
    "    #print(df['timestamp'].iloc[i].date(), start_date, df['timestamp'].iloc[i].date() == start_date)\n",
    "    if test['timestamp'].iloc[i].date() == start_date:\n",
    "        date_month_agg.append(j)\n",
    "    else:\n",
    "        j += 1\n",
    "        start_date = test['timestamp'].iloc[i].date()\n",
    "        date_month_agg.append(j)\n",
    "    i += 1\n",
    "len(date_month_agg) == len(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "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>timestamp</th>\n",
       "      <th>date_month_agg_not_sort</th>\n",
       "      <th>date_month_agg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1970-08-02 00:00:24</td>\n",
       "      <td>365</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1970-08-02 00:01:03</td>\n",
       "      <td>365</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "      <td>365</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1970-08-02 00:01:50</td>\n",
       "      <td>365</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1970-08-02 00:01:57</td>\n",
       "      <td>365</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506686</th>\n",
       "      <td>1971-01-31 23:57:59</td>\n",
       "      <td>547</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506687</th>\n",
       "      <td>1971-01-31 23:58:07</td>\n",
       "      <td>547</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506688</th>\n",
       "      <td>1971-01-31 23:58:46</td>\n",
       "      <td>547</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506689</th>\n",
       "      <td>1971-01-31 23:58:57</td>\n",
       "      <td>547</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506690</th>\n",
       "      <td>1971-01-31 23:59:05</td>\n",
       "      <td>547</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>506691 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 timestamp  date_month_agg_not_sort  date_month_agg\n",
       "0      1970-08-02 00:00:24                      365             183\n",
       "1      1970-08-02 00:01:03                      365             183\n",
       "2      1970-08-02 00:01:50                      365             183\n",
       "3      1970-08-02 00:01:50                      365             183\n",
       "4      1970-08-02 00:01:57                      365             183\n",
       "...                    ...                      ...             ...\n",
       "506686 1971-01-31 23:57:59                      547             365\n",
       "506687 1971-01-31 23:58:07                      547             365\n",
       "506688 1971-01-31 23:58:46                      547             365\n",
       "506689 1971-01-31 23:58:57                      547             365\n",
       "506690 1971-01-31 23:59:05                      547             365\n",
       "\n",
       "[506691 rows x 3 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['date_month_agg'] = date_month_agg\n",
    "test[['timestamp', 'date_month_agg_not_sort', 'date_month_agg']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "test[cat_cols_tr] = test[cat_cols_tr].apply(lambda x: le.fit_transform(x.astype(str)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.drop('timestamp', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(506691, 221)"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>TransactionID</th>\n",
       "      <th>isFraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3663549</td>\n",
       "      <td>0.002257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3663550</td>\n",
       "      <td>0.005420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3663551</td>\n",
       "      <td>0.010665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3663552</td>\n",
       "      <td>0.002760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3663553</td>\n",
       "      <td>0.004064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506686</th>\n",
       "      <td>4170235</td>\n",
       "      <td>0.013973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506687</th>\n",
       "      <td>4170236</td>\n",
       "      <td>0.010759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506688</th>\n",
       "      <td>4170237</td>\n",
       "      <td>0.009284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506689</th>\n",
       "      <td>4170238</td>\n",
       "      <td>0.014482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506690</th>\n",
       "      <td>4170239</td>\n",
       "      <td>0.008832</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>506691 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       TransactionID   isFraud\n",
       "0            3663549  0.002257\n",
       "1            3663550  0.005420\n",
       "2            3663551  0.010665\n",
       "3            3663552  0.002760\n",
       "4            3663553  0.004064\n",
       "...              ...       ...\n",
       "506686       4170235  0.013973\n",
       "506687       4170236  0.010759\n",
       "506688       4170237  0.009284\n",
       "506689       4170238  0.014482\n",
       "506690       4170239  0.008832\n",
       "\n",
       "[506691 rows x 2 columns]"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clf1 = LGBMClassifier()\n",
    "clf1.fit(df.drop(target, axis=1), df[target])\n",
    "proba_test = (clf1.predict_proba(test.values)[:, 1] + clf.predict_proba(test.values)[:, 1]) / 2\n",
    "ans_df = pd.DataFrame(columns=['TransactionID', 'isFraud'])\n",
    "ans_df['TransactionID'] = test['TransactionID']\n",
    "ans_df['isFraud'] = proba_test\n",
    "ans_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ans_df.drop_duplicates('TransactionID', keep='first', inplace=True)\n",
    "# ans_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans_df.to_csv('/home/other/kaggle/submission_4.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !kaggle competitions submit -c ieee-fraud-detection -f submission.csv -m \"submission_1\""
   ]
  },
  {
   "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}