fraud-detection-1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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": 2,
   "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": 2,
     "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": 3,
   "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": 4,
   "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": 4,
     "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": 5,
   "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": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "target = 'isFraud'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((144233, 41), (590540, 394))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_identity.shape, train_transaction.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(590540, 434)"
      ]
     },
     "execution_count": 8,
     "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": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(590540, 220)"
      ]
     },
     "execution_count": 9,
     "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": 10,
   "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": 11,
   "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": 12,
   "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": 13,
   "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": 14,
   "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": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# not_cat_cols_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cat_cols_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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": 18,
   "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": 18,
     "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": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([   0.        ,  100.        ,   48.63809967, ..., 3816.        ,\n",
       "        680.5       , 1908.        ])"
      ]
     },
     "execution_count": 19,
     "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": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['timestamp'] = pd.to_datetime(df['TransactionDT'], unit='s')\n",
    "df[['TransactionDT', 'timestamp']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1970"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['timestamp'].iloc[0].year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df['timestamp']:\n",
    "    if x.year != 1970:\n",
    "        print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All rows are belonged to one year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 23,
     "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": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_month_agg</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\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": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['date_month_agg'] = date_month_agg\n",
    "df[['timestamp', 'date_month_agg']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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": 26,
   "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>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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>183</td>\n",
       "      <td>65</td>\n",
       "      <td>1</td>\n",
       "      <td>7962</td>\n",
       "      <td>0</td>\n",
       "      <td>42</td>\n",
       "      <td>4</td>\n",
       "      <td>108</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>332</td>\n",
       "      <td>74</td>\n",
       "      <td>16</td>\n",
       "      <td>5591</td>\n",
       "      <td>251</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>332</td>\n",
       "      <td>74</td>\n",
       "      <td>19</td>\n",
       "      <td>9234</td>\n",
       "      <td>274</td>\n",
       "      <td>73</td>\n",
       "      <td>2</td>\n",
       "      <td>106</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>65</td>\n",
       "      <td>2</td>\n",
       "      <td>6791</td>\n",
       "      <td>454</td>\n",
       "      <td>42</td>\n",
       "      <td>4</td>\n",
       "      <td>108</td>\n",
       "      <td>2</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  card5  \\\n",
       "0          1    241     65             16   9262    413     42      2      2   \n",
       "1          1    183     65              1   7962      0     42      4    108   \n",
       "2          0    332     74             16   5591    251     11      2     30   \n",
       "3          0    332     74             19   9234    274     73      2    106   \n",
       "4          1     52     65              2   6791    454     42      4    108   \n",
       "\n",
       "   card6  M1  M2  M3  M4  M6  \n",
       "0      1   2   2   2   3   2  \n",
       "1      2   2   2   2   3   2  \n",
       "2      1   2   2   2   0   2  \n",
       "3      2   2   2   2   0   2  \n",
       "4      2   2   2   2   3   2  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[cat_cols_tr].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop('timestamp', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[3.0196630e+06, 8.1539400e+05, 1.6050000e+02, ..., 0.0000000e+00,\n",
       "        0.0000000e+00, 1.9000000e+02],\n",
       "       [3.0477640e+06, 1.3785480e+06, 7.5000000e+01, ..., 0.0000000e+00,\n",
       "        0.0000000e+00, 1.4000000e+01],\n",
       "       [3.0774250e+06, 1.8877770e+06, 4.0000000e+01, ..., 0.0000000e+00,\n",
       "        0.0000000e+00, 2.0000000e+01],\n",
       "       ...,\n",
       "       [3.0165230e+06, 7.5191200e+05, 3.1195000e+02, ..., 0.0000000e+00,\n",
       "        0.0000000e+00, 1.8900000e+02],\n",
       "       [3.1934710e+06, 4.7449050e+06, 5.0000000e+01, ..., 0.0000000e+00,\n",
       "        0.0000000e+00, 5.3000000e+01],\n",
       "       [3.5412320e+06, 1.4658362e+07, 2.8000000e+02, ..., 0.0000000e+00,\n",
       "        0.0000000e+00, 3.5000000e+02]])"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_train.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9247878856047431"
      ]
     },
     "execution_count": 30,
     "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": 48,
   "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": 48,
     "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": 49,
   "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": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "tr_cols = list(train_transaction.columns)\n",
    "tr_cols.remove(target)\n",
    "#tr_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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": 51,
     "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": 52,
   "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": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(506691, 433)"
      ]
     },
     "execution_count": 53,
     "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": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(506691, 219)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.drop(drop_cols, axis=1, inplace=True)\n",
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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": 55,
     "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": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = '1970-07-03'\n",
    "i = 0 \n",
    "j = 364\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": 57,
   "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",
       "    </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\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": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['date_month_agg'] = date_month_agg\n",
    "test[['timestamp', 'date_month_agg']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.drop('timestamp', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(506691, 220)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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.027992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3663550</td>\n",
       "      <td>0.006342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3663551</td>\n",
       "      <td>0.035330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3663552</td>\n",
       "      <td>0.002153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3663553</td>\n",
       "      <td>0.006720</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.007904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506687</th>\n",
       "      <td>4170236</td>\n",
       "      <td>0.011818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506688</th>\n",
       "      <td>4170237</td>\n",
       "      <td>0.011201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506689</th>\n",
       "      <td>4170238</td>\n",
       "      <td>0.015952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506690</th>\n",
       "      <td>4170239</td>\n",
       "      <td>0.010854</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>506691 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       TransactionID   isFraud\n",
       "0            3663549  0.027992\n",
       "1            3663550  0.006342\n",
       "2            3663551  0.035330\n",
       "3            3663552  0.002153\n",
       "4            3663553  0.006720\n",
       "...              ...       ...\n",
       "506686       4170235  0.007904\n",
       "506687       4170236  0.011818\n",
       "506688       4170237  0.011201\n",
       "506689       4170238  0.015952\n",
       "506690       4170239  0.010854\n",
       "\n",
       "[506691 rows x 2 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proba_test = clf.predict_proba(test.values)[:, 1]\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": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ans_df.drop_duplicates('TransactionID', keep='first', inplace=True)\n",
    "# ans_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans_df.to_csv('/home/other/kaggle/submission_1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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
}