FX_data_preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.Train data : total 120 days information ( 9448 ~ 9567, unit: day ) \n",
    "### 2.Test data : future 30 days information (9568 ~ 9597, unit: day ) \n",
    "### 3.Train label : transaction record with date\n",
    "\n",
    "#### First try:\n",
    "    - 30 days feature (X)   =>  next 30 days target Y{0,1}\n",
    "    - 4 binary classification models"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the data shape is : (507185, 3)\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>CUST_NO</th>\n",
       "      <th>TXN_DT</th>\n",
       "      <th>FX_TXN_AMT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MNEZK2O3OE-JHYBW</td>\n",
       "      <td>9562</td>\n",
       "      <td>26006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MNEZK2O3OE-JHYBW</td>\n",
       "      <td>9567</td>\n",
       "      <td>26009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MNEZK2O3OE-JHYBW</td>\n",
       "      <td>9557</td>\n",
       "      <td>26793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MNEZK2O3OE-JHYBW</td>\n",
       "      <td>9452</td>\n",
       "      <td>25981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MNEZK2O3OE-JHYBW</td>\n",
       "      <td>9451</td>\n",
       "      <td>26002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  TXN_DT  FX_TXN_AMT\n",
       "0  MNEZK2O3OE-JHYBW    9562       26006\n",
       "1  MNEZK2O3OE-JHYBW    9567       26009\n",
       "2  MNEZK2O3OE-JHYBW    9557       26793\n",
       "3  MNEZK2O3OE-JHYBW    9452       25981\n",
       "4  MNEZK2O3OE-JHYBW    9451       26002"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TBN_FX_TXN data (顧客外匯交易資料)\n",
    "FX_df = pd.read_csv('TBN_FX_TXN.csv')\n",
    "print(f'the data shape is : {FX_df.shape}')\n",
    "FX_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CUST_NO       0.0\n",
       "TXN_DT        0.0\n",
       "FX_TXN_AMT    0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check missing data\n",
    "FX_df.isnull().sum() / FX_df.isnull().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to int data type\n",
    "FX_df['TXN_DT'] = FX_df['TXN_DT'].values.astype(int) \n",
    "FX_df['FX_TXN_AMT'] = FX_df['FX_TXN_AMT'].values.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "month_boundary = [9477, 9507, 9537, 9567]\n",
    "\n",
    "def make_month_data_dict(whole_data=FX_df, \n",
    "                         month_boundary=month_boundary, \n",
    "                        target_col='TXN_DT',):\n",
    "    month_data_dict = {}\n",
    "    \n",
    "    for idx, day in enumerate(month_boundary):\n",
    "        month_df = whole_data[whole_data[target_col]<=day]\n",
    "        other_df = whole_data[whole_data[target_col]>day]\n",
    "        month_data_dict[idx+1] = month_df\n",
    "        whole_data = other_df\n",
    "    return month_data_dict\n",
    "\n",
    "month_data_dict = make_month_data_dict(whole_data=FX_df, month_boundary=month_boundary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Browse Behavior Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The browse data shape : (2209864, 3)\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>CUST_NO</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>PAGE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZTHNWQ_LXMGIMYG</td>\n",
       "      <td>9462</td>\n",
       "      <td>http://www.esunbank.com.tw/gygrt/e2c/iougkjr/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZTHNWQ_LXMGIMYG</td>\n",
       "      <td>9528</td>\n",
       "      <td>https://www.esunbank.com.tw/gygrt/wgdqth/gsxri...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3PY428CHUQBULFIG</td>\n",
       "      <td>9458</td>\n",
       "      <td>https://www.esunbank.com.tw/edrn/deoxt/rgws-cg...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JVPD1QUJWVLMZU8S</td>\n",
       "      <td>9457</td>\n",
       "      <td>https://www.esunbank.com.tw/edrn/pgusordq/fgpo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>JVPD1QUJWVLMZU8S</td>\n",
       "      <td>9485</td>\n",
       "      <td>https://www.esunbank.com.tw/edrn/pgusordq/fgpo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  VISITDATE  \\\n",
       "0  AZTHNWQ_LXMGIMYG       9462   \n",
       "1  AZTHNWQ_LXMGIMYG       9528   \n",
       "2  3PY428CHUQBULFIG       9458   \n",
       "3  JVPD1QUJWVLMZU8S       9457   \n",
       "4  JVPD1QUJWVLMZU8S       9485   \n",
       "\n",
       "                                                PAGE  \n",
       "0      http://www.esunbank.com.tw/gygrt/e2c/iougkjr/  \n",
       "1  https://www.esunbank.com.tw/gygrt/wgdqth/gsxri...  \n",
       "2  https://www.esunbank.com.tw/edrn/deoxt/rgws-cg...  \n",
       "3  https://www.esunbank.com.tw/edrn/pgusordq/fgpo...  \n",
       "4  https://www.esunbank.com.tw/edrn/pgusordq/fgpo...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TBN_CUST_BEHAVIOR.csv : Browse Behavior\n",
    "\n",
    "cust_browse_df = pd.read_csv('./TBN_CUST_BEHAVIOR.csv')\n",
    "print(f'The browse data shape : {cust_browse_df.shape}')\n",
    "cust_browse_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "browse_month_data_dict = make_month_data_dict(whole_data=cust_browse_df, \n",
    "                                              month_boundary=month_boundary,\n",
    "                                              target_col='VISITDATE',)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "for key in range(1, 5):\n",
    "    page_class = []\n",
    "    \n",
    "    \n",
    "    browse_month_data_dict[key] = browse_month_data_dict[key].reset_index(drop=True)\n",
    "\n",
    "    for idx in range(len(browse_month_data_dict[key])):\n",
    "        page_class.append( browse_month_data_dict[key]['PAGE'][idx].split('/')[3] )\n",
    "        \n",
    "    browse_month_data_dict[key]['page_class'] = page_class\n",
    "    del browse_month_data_dict[key]['PAGE']\n",
    "    \n",
    "    train_dummy = pd.get_dummies(browse_month_data_dict[key]['page_class'])\n",
    "    Browse_count = np.sum(train_dummy.values, axis=1)\n",
    "    browse_month_data_dict[key]['Browse_count'] = Browse_count\n",
    "        \n",
    "    browse_month_data_dict[key] = browse_month_data_dict[key].drop(columns='page_class')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>CUST_NO</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>Browse_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZTHNWQ_LXMGIMYG</td>\n",
       "      <td>9462</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3PY428CHUQBULFIG</td>\n",
       "      <td>9458</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JVPD1QUJWVLMZU8S</td>\n",
       "      <td>9457</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JVPD1QUJWVLMZU8S</td>\n",
       "      <td>9471</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>JVPD1QUJWVLMZU8S</td>\n",
       "      <td>9455</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  VISITDATE  Browse_count\n",
       "0  AZTHNWQ_LXMGIMYG       9462             1\n",
       "1  3PY428CHUQBULFIG       9458             1\n",
       "2  JVPD1QUJWVLMZU8S       9457             1\n",
       "3  JVPD1QUJWVLMZU8S       9471             1\n",
       "4  JVPD1QUJWVLMZU8S       9455             1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "browse_month_data_dict[1].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Custom personal data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The browse data shape : (187679, 8)\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>CUST_NO</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CHILDREN_CNT</th>\n",
       "      <th>CUST_START_DT</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UFUIMEGMK2KTIWI4</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</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>3HJN2X_LGBHBKILI</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9393.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MNEZK2O3OE-JHYBW</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2912.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>M</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>W7ADZSWYXEMHB7JQ</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8332.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>M</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ODB6BA6ORHL2UYZE</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>744.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>M</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  AGE  CHILDREN_CNT  CUST_START_DT  EDU_CODE GENDER_CODE  \\\n",
       "0  UFUIMEGMK2KTIWI4    1           NaN            2.0       NaN         NaN   \n",
       "1  3HJN2X_LGBHBKILI    3           0.0         9393.0       NaN         NaN   \n",
       "2  MNEZK2O3OE-JHYBW    4           0.0         2912.0       3.0           M   \n",
       "3  W7ADZSWYXEMHB7JQ    4           0.0         8332.0       5.0           M   \n",
       "4  ODB6BA6ORHL2UYZE    4           0.0          744.0       3.0           M   \n",
       "\n",
       "   INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0                NaN        NaN  \n",
       "1                1.0        NaN  \n",
       "2                2.0        2.0  \n",
       "3                2.0        1.0  \n",
       "4                2.0        1.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TBN_CIF.csv' \n",
    "\n",
    "cust_persional_df = pd.read_csv('./TBN_CIF.csv')\n",
    "print(f'The browse data shape : {cust_persional_df.shape}')\n",
    "cust_persional_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "WORK_MTHS            36190\n",
       "EDU_CODE             28988\n",
       "GENDER_CODE           1994\n",
       "CHILDREN_CNT           432\n",
       "INCOME_RANGE_CODE      382\n",
       "CUST_START_DT            0\n",
       "AGE                      0\n",
       "CUST_NO                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check missing data\n",
    "cust_persional_df.isnull().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "del cust_persional_df['CHILDREN_CNT'] # almost zero value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill missing data by 0\n",
    "\n",
    "# Fill gender missing data by 'M'\n",
    "cust_persional_df['GENDER_CODE']  = np.array(cust_persional_df['GENDER_CODE'] == 'M').astype(np.int)\n",
    "\n",
    "#Fill income data by mode (1)\n",
    "cust_persional_df['INCOME_RANGE_CODE'] = cust_persional_df['INCOME_RANGE_CODE'].fillna(1.)\n",
    "\n",
    "# Fill other missing data by 0\n",
    "cust_persional_df = cust_persional_df.fillna(0.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Make training data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx in range(1,4):\n",
    "    month_train_X = pd.DataFrame()\n",
    "    month_train_X['CUST_NO'] = month_data_dict[idx].groupby(['CUST_NO']).sum().index\n",
    "    month_train_X['count_x'] = list(month_data_dict[idx].groupby(['CUST_NO']).count()['TXN_DT'])\n",
    "    month_train_X['money_mean'] = list(month_data_dict[idx].groupby(['CUST_NO']).mean()['FX_TXN_AMT'])\n",
    "    # Note: Check each index after groupby is same\n",
    "    \n",
    "    #Add browse behavior\n",
    "    browse_train_x = browse_month_data_dict[idx].groupby('CUST_NO').sum()\n",
    "    month_train_X = month_train_X.merge(browse_train_x, how='outer', left_on='CUST_NO', right_on='CUST_NO') \n",
    "    \n",
    "    #Add cusotm persional data\n",
    "    month_train_X = month_train_X.merge(cust_persional_df,  how='outer', left_on='CUST_NO', right_on='CUST_NO')\n",
    "    \n",
    "    \n",
    "    \n",
    "    month_train_Y = month_data_dict[idx+1].groupby(['CUST_NO']).count()\n",
    "    del month_train_Y['TXN_DT']\n",
    "    \n",
    "    merge_df = month_train_X.merge(month_train_Y, how='outer', left_on='CUST_NO', right_on='CUST_NO')\n",
    "    \n",
    "    if idx == 1:\n",
    "        train_data = merge_df.copy()\n",
    "        continue\n",
    "        \n",
    "    train_data = pd.concat([train_data, merge_df], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The training data shape : (773737, 12)\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>CUST_NO</th>\n",
       "      <th>count_x</th>\n",
       "      <th>money_mean</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>Browse_count</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CUST_START_DT</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "      <th>FX_TXN_AMT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>--0PHCW9D_3SAAFS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>44877.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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>--1YNJZSSOXDF6UU</td>\n",
       "      <td>1.0</td>\n",
       "      <td>47505.0</td>\n",
       "      <td>47291.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5801.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>--8BVNAAVAUSW6X8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34669.0</td>\n",
       "      <td>18935.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1352.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>--DLGTMNFWJYWXKG</td>\n",
       "      <td>11.0</td>\n",
       "      <td>28472.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>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>--DQ9OSIQ6MMBHD0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13130.5</td>\n",
       "      <td>47271.0</td>\n",
       "      <td>5.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>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  money_mean  VISITDATE  Browse_count  AGE  \\\n",
       "0  --0PHCW9D_3SAAFS      1.0     44877.0        NaN           NaN  NaN   \n",
       "1  --1YNJZSSOXDF6UU      1.0     47505.0    47291.0           5.0  3.0   \n",
       "2  --8BVNAAVAUSW6X8      1.0     34669.0    18935.0           2.0  3.0   \n",
       "3  --DLGTMNFWJYWXKG     11.0     28472.0        NaN           NaN  NaN   \n",
       "4  --DQ9OSIQ6MMBHD0      2.0     13130.5    47271.0           5.0  NaN   \n",
       "\n",
       "   CUST_START_DT  EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \\\n",
       "0            NaN       NaN          NaN                NaN        NaN   \n",
       "1         5801.0       6.0          0.0                1.0        1.0   \n",
       "2         1352.0       3.0          0.0                1.0        1.0   \n",
       "3            NaN       NaN          NaN                NaN        NaN   \n",
       "4            NaN       NaN          NaN                NaN        NaN   \n",
       "\n",
       "   FX_TXN_AMT  \n",
       "0         NaN  \n",
       "1         NaN  \n",
       "2         NaN  \n",
       "3         2.0  \n",
       "4         3.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(f'The training data shape : {train_data.shape}')\n",
    "train_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_data['label'] = np.array(train_data.isna()['FX_TXN_AMT'] == False).astype(np.int)\n",
    "train_data = train_data.fillna(0)\n",
    "del train_data['FX_TXN_AMT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the data shape is : (30000, 1)\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>CUST_NO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>_PT5HFBEZJKOZ934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6STXUMWZRDCGSDDU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JDVF4U8JUANEID68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8I6SQDGP9OQYUN1M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R-TRDUV3GHTID31I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>J0DDOZLDFF03QBKW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>HWAZJ_IO2-GACG_C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TREOAHKDWDKDT3UQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SA1L7XU6FBENQNT8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>K0RCWGLXIKGPZNCI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO\n",
       "0  _PT5HFBEZJKOZ934\n",
       "1  6STXUMWZRDCGSDDU\n",
       "2  JDVF4U8JUANEID68\n",
       "3  8I6SQDGP9OQYUN1M\n",
       "4  R-TRDUV3GHTID31I\n",
       "5  J0DDOZLDFF03QBKW\n",
       "6  HWAZJ_IO2-GACG_C\n",
       "7  TREOAHKDWDKDT3UQ\n",
       "8  SA1L7XU6FBENQNT8\n",
       "9  K0RCWGLXIKGPZNCI"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check test Y\n",
    "test_data = pd.DataFrame()\n",
    "\n",
    "test_data['CUST_NO'] = pd.read_csv('./TBN_Y_ZERO.csv')['CUST_NO']\n",
    "print(f'the data shape is : {test_data.shape}')\n",
    "test_data[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "month_test_X = pd.DataFrame()\n",
    "month_test_X['CUST_NO'] = month_data_dict[4].groupby(['CUST_NO']).sum().index\n",
    "month_test_X['count_x'] = list(month_data_dict[4].groupby(['CUST_NO']).count()['TXN_DT'])\n",
    "month_test_X['money_mean'] = list(month_data_dict[4].groupby(['CUST_NO']).mean()['FX_TXN_AMT'])\n",
    "\n",
    "\n",
    "#Add browse behavior\n",
    "browse_test_x = browse_month_data_dict[4].groupby('CUST_NO').sum()\n",
    "month_test_X = month_test_X.merge(browse_test_x, how='outer', left_on='CUST_NO', right_on='CUST_NO') \n",
    "\n",
    "#Add cusotm persional data\n",
    "month_test_X = month_test_X.merge(cust_persional_df,  how='outer', left_on='CUST_NO', right_on='CUST_NO')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The test data shape : (30000, 11)\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>CUST_NO</th>\n",
       "      <th>count_x</th>\n",
       "      <th>money_mean</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>Browse_count</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CUST_START_DT</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>_PT5HFBEZJKOZ934</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19022.0</td>\n",
       "      <td>57328.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2912.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6STXUMWZRDCGSDDU</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>114708.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7686.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JDVF4U8JUANEID68</td>\n",
       "      <td>2.0</td>\n",
       "      <td>40195.0</td>\n",
       "      <td>57329.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2484.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8I6SQDGP9OQYUN1M</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14979.0</td>\n",
       "      <td>28659.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7754.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R-TRDUV3GHTID31I</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28674.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4062.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  money_mean  VISITDATE  Browse_count  AGE  \\\n",
       "0  _PT5HFBEZJKOZ934      5.0     19022.0    57328.0           6.0  4.0   \n",
       "1  6STXUMWZRDCGSDDU      0.0         0.0   114708.0          12.0  2.0   \n",
       "2  JDVF4U8JUANEID68      2.0     40195.0    57329.0           6.0  4.0   \n",
       "3  8I6SQDGP9OQYUN1M      1.0     14979.0    28659.0           3.0  1.0   \n",
       "4  R-TRDUV3GHTID31I      0.0         0.0    28674.0           3.0  4.0   \n",
       "\n",
       "   CUST_START_DT  EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0         2912.0       4.0          0.0                1.0        1.0  \n",
       "1         7686.0       3.0          1.0                1.0        1.0  \n",
       "2         2484.0       3.0          0.0                4.0        1.0  \n",
       "3         7754.0       6.0          0.0                1.0        1.0  \n",
       "4         4062.0       2.0          1.0                1.0        1.0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data = test_data.merge(month_test_X, how='left', left_on='CUST_NO', right_on='CUST_NO')\n",
    "print(f'The test data shape : {test_data.shape}')\n",
    "test_data = test_data.fillna(0)\n",
    "test_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Write data to .csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_same_order(train_df, test_df):\n",
    "    train_X = pd.DataFrame()\n",
    "    train_y = pd.DataFrame()\n",
    "    test_X = pd.DataFrame()\n",
    "    for col in test_df.columns:\n",
    "        train_X[col] = train_df[col]\n",
    "        test_X[col] = test_df[col]\n",
    "        \n",
    "    train_y['label'] = train_df['label']\n",
    "    return train_X, train_y, test_X\n",
    "\n",
    "train_X, train_y, test_X = make_same_order(train_df=train_data, test_df=test_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(773737, 11)\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>CUST_NO</th>\n",
       "      <th>count_x</th>\n",
       "      <th>money_mean</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>Browse_count</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CUST_START_DT</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>--0PHCW9D_3SAAFS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>44877.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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>--1YNJZSSOXDF6UU</td>\n",
       "      <td>1.0</td>\n",
       "      <td>47505.0</td>\n",
       "      <td>47291.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5801.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>--8BVNAAVAUSW6X8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34669.0</td>\n",
       "      <td>18935.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1352.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>--DLGTMNFWJYWXKG</td>\n",
       "      <td>11.0</td>\n",
       "      <td>28472.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",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>--DQ9OSIQ6MMBHD0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13130.5</td>\n",
       "      <td>47271.0</td>\n",
       "      <td>5.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",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  money_mean  VISITDATE  Browse_count  AGE  \\\n",
       "0  --0PHCW9D_3SAAFS      1.0     44877.0        0.0           0.0  0.0   \n",
       "1  --1YNJZSSOXDF6UU      1.0     47505.0    47291.0           5.0  3.0   \n",
       "2  --8BVNAAVAUSW6X8      1.0     34669.0    18935.0           2.0  3.0   \n",
       "3  --DLGTMNFWJYWXKG     11.0     28472.0        0.0           0.0  0.0   \n",
       "4  --DQ9OSIQ6MMBHD0      2.0     13130.5    47271.0           5.0  0.0   \n",
       "\n",
       "   CUST_START_DT  EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0            0.0       0.0          0.0                0.0        0.0  \n",
       "1         5801.0       6.0          0.0                1.0        1.0  \n",
       "2         1352.0       3.0          0.0                1.0        1.0  \n",
       "3            0.0       0.0          0.0                0.0        0.0  \n",
       "4            0.0       0.0          0.0                0.0        0.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(train_X.shape)\n",
    "train_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(773737, 1)\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>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   label\n",
       "0      0\n",
       "1      0\n",
       "2      0\n",
       "3      1\n",
       "4      1"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(train_y.shape)\n",
    "train_y.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(30000, 11)\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>CUST_NO</th>\n",
       "      <th>count_x</th>\n",
       "      <th>money_mean</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>Browse_count</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CUST_START_DT</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>_PT5HFBEZJKOZ934</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19022.0</td>\n",
       "      <td>57328.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2912.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6STXUMWZRDCGSDDU</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>114708.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7686.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JDVF4U8JUANEID68</td>\n",
       "      <td>2.0</td>\n",
       "      <td>40195.0</td>\n",
       "      <td>57329.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2484.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8I6SQDGP9OQYUN1M</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14979.0</td>\n",
       "      <td>28659.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7754.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R-TRDUV3GHTID31I</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28674.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4062.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  money_mean  VISITDATE  Browse_count  AGE  \\\n",
       "0  _PT5HFBEZJKOZ934      5.0     19022.0    57328.0           6.0  4.0   \n",
       "1  6STXUMWZRDCGSDDU      0.0         0.0   114708.0          12.0  2.0   \n",
       "2  JDVF4U8JUANEID68      2.0     40195.0    57329.0           6.0  4.0   \n",
       "3  8I6SQDGP9OQYUN1M      1.0     14979.0    28659.0           3.0  1.0   \n",
       "4  R-TRDUV3GHTID31I      0.0         0.0    28674.0           3.0  4.0   \n",
       "\n",
       "   CUST_START_DT  EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0         2912.0       4.0          0.0                1.0        1.0  \n",
       "1         7686.0       3.0          1.0                1.0        1.0  \n",
       "2         2484.0       3.0          0.0                4.0        1.0  \n",
       "3         7754.0       6.0          0.0                1.0        1.0  \n",
       "4         4062.0       2.0          1.0                1.0        1.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(test_X.shape)\n",
    "test_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_X.to_csv('./pre_data/FX_train_X.csv')\n",
    "train_y.to_csv('./pre_data/FX_train_y.csv')\n",
    "test_X.to_csv('./pre_data/FX_test_X.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deal with imbalance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>CUST_NO</th>\n",
       "      <th>count_x</th>\n",
       "      <th>money_mean</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>Browse_count</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CUST_START_DT</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>--0PHCW9D_3SAAFS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>44877.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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>--1YNJZSSOXDF6UU</td>\n",
       "      <td>1.0</td>\n",
       "      <td>47505.0</td>\n",
       "      <td>47291.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5801.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>--8BVNAAVAUSW6X8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34669.0</td>\n",
       "      <td>18935.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1352.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>--DLGTMNFWJYWXKG</td>\n",
       "      <td>11.0</td>\n",
       "      <td>28472.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",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>--DQ9OSIQ6MMBHD0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13130.5</td>\n",
       "      <td>47271.0</td>\n",
       "      <td>5.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",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  money_mean  VISITDATE  Browse_count  AGE  \\\n",
       "0  --0PHCW9D_3SAAFS      1.0     44877.0        0.0           0.0  0.0   \n",
       "1  --1YNJZSSOXDF6UU      1.0     47505.0    47291.0           5.0  3.0   \n",
       "2  --8BVNAAVAUSW6X8      1.0     34669.0    18935.0           2.0  3.0   \n",
       "3  --DLGTMNFWJYWXKG     11.0     28472.0        0.0           0.0  0.0   \n",
       "4  --DQ9OSIQ6MMBHD0      2.0     13130.5    47271.0           5.0  0.0   \n",
       "\n",
       "   CUST_START_DT  EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0            0.0       0.0          0.0                0.0        0.0  \n",
       "1         5801.0       6.0          0.0                1.0        1.0  \n",
       "2         1352.0       3.0          0.0                1.0        1.0  \n",
       "3            0.0       0.0          0.0                0.0        0.0  \n",
       "4            0.0       0.0          0.0                0.0        0.0  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_X = pd.read_csv('./pre_data/FX_train_X.csv', index_col=0)\n",
    "train_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   label\n",
       "0      0\n",
       "1      0\n",
       "2      0\n",
       "3      1\n",
       "4      1"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_y = pd.read_csv('./pre_data/FX_train_y.csv', index_col=0)\n",
    "train_y.head()"
   ]
  },
  {
   "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>CUST_NO</th>\n",
       "      <th>count_x</th>\n",
       "      <th>money_mean</th>\n",
       "      <th>VISITDATE</th>\n",
       "      <th>Browse_count</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CUST_START_DT</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>_PT5HFBEZJKOZ934</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19022.0</td>\n",
       "      <td>57328.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2912.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6STXUMWZRDCGSDDU</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>114708.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7686.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JDVF4U8JUANEID68</td>\n",
       "      <td>2.0</td>\n",
       "      <td>40195.0</td>\n",
       "      <td>57329.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2484.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8I6SQDGP9OQYUN1M</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14979.0</td>\n",
       "      <td>28659.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7754.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R-TRDUV3GHTID31I</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28674.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4062.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  money_mean  VISITDATE  Browse_count  AGE  \\\n",
       "0  _PT5HFBEZJKOZ934      5.0     19022.0    57328.0           6.0  4.0   \n",
       "1  6STXUMWZRDCGSDDU      0.0         0.0   114708.0          12.0  2.0   \n",
       "2  JDVF4U8JUANEID68      2.0     40195.0    57329.0           6.0  4.0   \n",
       "3  8I6SQDGP9OQYUN1M      1.0     14979.0    28659.0           3.0  1.0   \n",
       "4  R-TRDUV3GHTID31I      0.0         0.0    28674.0           3.0  4.0   \n",
       "\n",
       "   CUST_START_DT  EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0         2912.0       4.0          0.0                1.0        1.0  \n",
       "1         7686.0       3.0          1.0                1.0        1.0  \n",
       "2         2484.0       3.0          0.0                4.0        1.0  \n",
       "3         7754.0       6.0          0.0                1.0        1.0  \n",
       "4         4062.0       2.0          1.0                1.0        1.0  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_X = pd.read_csv('./pre_data/FX_test_X.csv', index_col=0)\n",
    "test_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_x</th>\n",
       "      <th>money_mean</th>\n",
       "      <th>Browse_count</th>\n",
       "      <th>AGE</th>\n",
       "      <th>EDU_CODE</th>\n",
       "      <th>GENDER_CODE</th>\n",
       "      <th>INCOME_RANGE_CODE</th>\n",
       "      <th>WORK_MTHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.609370</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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.781466</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.940893</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11.0</td>\n",
       "      <td>1.535079</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",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0.530433</td>\n",
       "      <td>5.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",
       "</div>"
      ],
      "text/plain": [
       "   count_x  money_mean  Browse_count  AGE  EDU_CODE  GENDER_CODE  \\\n",
       "0      1.0    2.609370           0.0  0.0       0.0          0.0   \n",
       "1      1.0    2.781466           5.0  3.0       6.0          0.0   \n",
       "2      1.0    1.940893           2.0  3.0       3.0          0.0   \n",
       "3     11.0    1.535079           0.0  0.0       0.0          0.0   \n",
       "4      2.0    0.530433           5.0  0.0       0.0          0.0   \n",
       "\n",
       "   INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0                0.0        0.0  \n",
       "1                1.0        1.0  \n",
       "2                1.0        1.0  \n",
       "3                0.0        0.0  \n",
       "4                0.0        0.0  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del_col = ['CUST_NO', 'VISITDATE', 'CUST_START_DT',]\n",
    "scale_col = ['money_mean']\n",
    "\n",
    "# Delete feature\n",
    "for col in del_col:\n",
    "    del train_X[col]\n",
    "    del test_X[col]\n",
    "\n",
    "# Standardizing\n",
    "from sklearn import preprocessing\n",
    "\n",
    "for col in scale_col:\n",
    "    train_X[col] = preprocessing.scale(train_X[col])\n",
    "    test_X[col] = preprocessing.scale(test_X[col])\n",
    "train_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The percentage of 1 label:  label    0.100649\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"The percentage of 1 label: \", train_y.sum()/train_y.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filter= AGE    (597605, 8)  |  (597605, 1)\n",
      "Filter= WORK_MTHS    (497020, 8)  |  (497020, 1)\n",
      "Filter= EDU_CODE    (496812, 8)  |  (496812, 1)\n",
      "Filter= Browse_count    (153544, 8)  |  (153544, 1)\n"
     ]
    }
   ],
   "source": [
    "filter_col = ['AGE', 'WORK_MTHS', 'EDU_CODE', 'Browse_count']\n",
    "\n",
    "for col in filter_col:\n",
    "    true_label = train_y['label'].values > 0\n",
    "    other = train_X[col].values>0\n",
    "    filt = true_label + other\n",
    "\n",
    "    train_X = train_X[filt]\n",
    "    train_y = train_y[filt]\n",
    "    print('Filter=', col,'  ',train_X.shape, ' | ',train_y.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The percentage of 1 label:  label    0.50719\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"The percentage of 1 label: \", train_y.sum()/train_y.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split training & validating set\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "X_train, X_valid, y_train, y_valid = train_test_split(train_X.values, train_y.values, test_size=0.2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(122835, 8) (30709, 8) (122835, 1) (30709, 1)\n"
     ]
    }
   ],
   "source": [
    "print(X_train.shape, X_valid.shape, y_train.shape, y_valid.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_train = y_train.reshape((-1,))\n",
    "y_valid = y_valid.reshape((-1,))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "def evalute(pred, y_valid):\n",
    "    return np.sum(pred == y_valid) / pred.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Random forest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RandomForestClassifier(bootstrap=True, class_weight=None, criterion='gini',\n",
       "            max_depth=5, max_features='auto', max_leaf_nodes=None,\n",
       "            min_impurity_decrease=0.0, min_impurity_split=None,\n",
       "            min_samples_leaf=1, min_samples_split=2,\n",
       "            min_weight_fraction_leaf=0.0, n_estimators=5000, n_jobs=None,\n",
       "            oob_score=False, random_state=0, verbose=0, warm_start=False)"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clf = RandomForestClassifier(n_estimators=5000, max_depth=5,\n",
    "                              random_state=0)\n",
    "clf.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9265361946009313"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eval_result = clf.predict(X_valid)\n",
    "evalute(eval_result, y_valid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred = clf.predict(test_X.values)\n",
    "sub_df = pd.DataFrame()\n",
    "sub_df['CUST_NO']=pd.read_csv(\"./TBN_Y_ZERO.csv\")['CUST_NO']\n",
    "sub_df['FX_IND']= pred\n",
    "sub_df.to_csv('./pred/submi_FX.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.202"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub_df['FX_IND'].sum() / sub_df['FX_IND'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}