CC_data_preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "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": 292,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the data shape is : (54393, 2)\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8H_N_K5ICM4NU7OG</td>\n",
       "      <td>9519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HXD9GCY3SPIYLJS0</td>\n",
       "      <td>9451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KSBOZ-TBPUDD9NMO</td>\n",
       "      <td>9502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MU8ROCNFDGSL0HJC</td>\n",
       "      <td>9549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>RGZZAUSNHNXXESM4</td>\n",
       "      <td>9561</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  TXN_DT\n",
       "0  8H_N_K5ICM4NU7OG    9519\n",
       "1  HXD9GCY3SPIYLJS0    9451\n",
       "2  KSBOZ-TBPUDD9NMO    9502\n",
       "3  MU8ROCNFDGSL0HJC    9549\n",
       "4  RGZZAUSNHNXXESM4    9561"
      ]
     },
     "execution_count": 292,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TBN_CC_APPLY.csv\n",
    "CC_df = pd.read_csv('TBN_CC_APPLY.csv')\n",
    "print(f'the data shape is : {CC_df.shape}')\n",
    "CC_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CUST_NO    0.0\n",
       "TXN_DT     0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 293,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check missing data\n",
    "CC_df.isnull().sum() / CC_df.isnull().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to int data type\n",
    "CC_df['TXN_DT'] = CC_df['TXN_DT'].values.astype(int) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {},
   "outputs": [],
   "source": [
    "month_boundary = [9477, 9507, 9537, 9567]\n",
    "\n",
    "def make_month_data_dict(whole_data=CC_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=CC_df, month_boundary=month_boundary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Browse Behavior Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "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": 296,
     "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": 297,
   "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": 298,
   "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": 299,
   "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": 299,
     "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": 300,
   "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": 300,
     "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": 301,
   "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": 301,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check missing data\n",
    "cust_persional_df.isnull().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {},
   "outputs": [],
   "source": [
    "del cust_persional_df['CHILDREN_CNT'] # almost zero value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "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": 304,
   "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",
    "    # 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",
    "    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": 305,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The training data shape : (748792, 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>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>TXN_DT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>---CHVW7DUN8SZLO</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28416.0</td>\n",
       "      <td>3.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>--KEX0HYP72TSBOQ</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9156.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>--ONGLWHY32XCGQQ</td>\n",
       "      <td>1.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>3</th>\n",
       "      <td>--RW6XQMCPW_UQU4</td>\n",
       "      <td>1.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>4</th>\n",
       "      <td>--W-DIDC-Q99XJHA</td>\n",
       "      <td>1.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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  VISITDATE  Browse_count  AGE  CUST_START_DT  \\\n",
       "0  ---CHVW7DUN8SZLO      2.0    28416.0           3.0  NaN            NaN   \n",
       "1  --KEX0HYP72TSBOQ      1.0        NaN           NaN  4.0         9156.0   \n",
       "2  --ONGLWHY32XCGQQ      1.0        NaN           NaN  NaN            NaN   \n",
       "3  --RW6XQMCPW_UQU4      1.0        NaN           NaN  NaN            NaN   \n",
       "4  --W-DIDC-Q99XJHA      1.0        NaN           NaN  NaN            NaN   \n",
       "\n",
       "   EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  TXN_DT  \n",
       "0       NaN          NaN                NaN        NaN     NaN  \n",
       "1       6.0          0.0                1.0        1.0     NaN  \n",
       "2       NaN          NaN                NaN        NaN     NaN  \n",
       "3       NaN          NaN                NaN        NaN     NaN  \n",
       "4       NaN          NaN                NaN        NaN     NaN  "
      ]
     },
     "execution_count": 305,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(f'The training data shape : {train_data.shape}')\n",
    "train_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_data['label'] = np.array(train_data.isna()['TXN_DT'] == False).astype(np.int)\n",
    "train_data = train_data.fillna(0)\n",
    "del train_data['TXN_DT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "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": 307,
     "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": 308,
   "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",
    "\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": 309,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The test data shape : (30000, 10)\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>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>0.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>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>0.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>0.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>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  VISITDATE  Browse_count  AGE  CUST_START_DT  \\\n",
       "0  _PT5HFBEZJKOZ934      0.0    57328.0           6.0  4.0         2912.0   \n",
       "1  6STXUMWZRDCGSDDU      0.0   114708.0          12.0  2.0         7686.0   \n",
       "2  JDVF4U8JUANEID68      0.0    57329.0           6.0  4.0         2484.0   \n",
       "3  8I6SQDGP9OQYUN1M      0.0    28659.0           3.0  1.0         7754.0   \n",
       "4  R-TRDUV3GHTID31I      0.0    28674.0           3.0  4.0         4062.0   \n",
       "\n",
       "   EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0       4.0          0.0                1.0        1.0  \n",
       "1       3.0          1.0                1.0        1.0  \n",
       "2       3.0          0.0                4.0        1.0  \n",
       "3       6.0          0.0                1.0        1.0  \n",
       "4       2.0          1.0                1.0        1.0  "
      ]
     },
     "execution_count": 309,
     "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": 310,
   "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": 311,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(748792, 10)\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>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>---CHVW7DUN8SZLO</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28416.0</td>\n",
       "      <td>3.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>--KEX0HYP72TSBOQ</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9156.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>--ONGLWHY32XCGQQ</td>\n",
       "      <td>1.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>3</th>\n",
       "      <td>--RW6XQMCPW_UQU4</td>\n",
       "      <td>1.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>--W-DIDC-Q99XJHA</td>\n",
       "      <td>1.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",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  VISITDATE  Browse_count  AGE  CUST_START_DT  \\\n",
       "0  ---CHVW7DUN8SZLO      2.0    28416.0           3.0  0.0            0.0   \n",
       "1  --KEX0HYP72TSBOQ      1.0        0.0           0.0  4.0         9156.0   \n",
       "2  --ONGLWHY32XCGQQ      1.0        0.0           0.0  0.0            0.0   \n",
       "3  --RW6XQMCPW_UQU4      1.0        0.0           0.0  0.0            0.0   \n",
       "4  --W-DIDC-Q99XJHA      1.0        0.0           0.0  0.0            0.0   \n",
       "\n",
       "   EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0       0.0          0.0                0.0        0.0  \n",
       "1       6.0          0.0                1.0        1.0  \n",
       "2       0.0          0.0                0.0        0.0  \n",
       "3       0.0          0.0                0.0        0.0  \n",
       "4       0.0          0.0                0.0        0.0  "
      ]
     },
     "execution_count": 311,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(train_X.shape)\n",
    "train_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(748792, 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>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   label\n",
       "0      0\n",
       "1      0\n",
       "2      0\n",
       "3      0\n",
       "4      0"
      ]
     },
     "execution_count": 312,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(train_y.shape)\n",
    "train_y.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(30000, 10)\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>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>0.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>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>0.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>0.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>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  VISITDATE  Browse_count  AGE  CUST_START_DT  \\\n",
       "0  _PT5HFBEZJKOZ934      0.0    57328.0           6.0  4.0         2912.0   \n",
       "1  6STXUMWZRDCGSDDU      0.0   114708.0          12.0  2.0         7686.0   \n",
       "2  JDVF4U8JUANEID68      0.0    57329.0           6.0  4.0         2484.0   \n",
       "3  8I6SQDGP9OQYUN1M      0.0    28659.0           3.0  1.0         7754.0   \n",
       "4  R-TRDUV3GHTID31I      0.0    28674.0           3.0  4.0         4062.0   \n",
       "\n",
       "   EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0       4.0          0.0                1.0        1.0  \n",
       "1       3.0          1.0                1.0        1.0  \n",
       "2       3.0          0.0                4.0        1.0  \n",
       "3       6.0          0.0                1.0        1.0  \n",
       "4       2.0          1.0                1.0        1.0  "
      ]
     },
     "execution_count": 313,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(test_X.shape)\n",
    "test_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_X.to_csv('./pre_data/CC_train_X.csv')\n",
    "train_y.to_csv('./pre_data/CC_train_y.csv')\n",
    "test_X.to_csv('./pre_data/CC_test_X.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deal with imbalance data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "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>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>---CHVW7DUN8SZLO</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28416.0</td>\n",
       "      <td>3.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>--KEX0HYP72TSBOQ</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9156.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>--ONGLWHY32XCGQQ</td>\n",
       "      <td>1.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>3</th>\n",
       "      <td>--RW6XQMCPW_UQU4</td>\n",
       "      <td>1.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>--W-DIDC-Q99XJHA</td>\n",
       "      <td>1.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",
       "</div>"
      ],
      "text/plain": [
       "            CUST_NO  count_x  VISITDATE  Browse_count  AGE  CUST_START_DT  \\\n",
       "0  ---CHVW7DUN8SZLO      2.0    28416.0           3.0  0.0            0.0   \n",
       "1  --KEX0HYP72TSBOQ      1.0        0.0           0.0  4.0         9156.0   \n",
       "2  --ONGLWHY32XCGQQ      1.0        0.0           0.0  0.0            0.0   \n",
       "3  --RW6XQMCPW_UQU4      1.0        0.0           0.0  0.0            0.0   \n",
       "4  --W-DIDC-Q99XJHA      1.0        0.0           0.0  0.0            0.0   \n",
       "\n",
       "   EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0       0.0          0.0                0.0        0.0  \n",
       "1       6.0          0.0                1.0        1.0  \n",
       "2       0.0          0.0                0.0        0.0  \n",
       "3       0.0          0.0                0.0        0.0  \n",
       "4       0.0          0.0                0.0        0.0  "
      ]
     },
     "execution_count": 319,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_X = pd.read_csv('./pre_data/CC_train_X.csv', index_col=0)\n",
    "train_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "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>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   label\n",
       "0      0\n",
       "1      0\n",
       "2      0\n",
       "3      0\n",
       "4      0"
      ]
     },
     "execution_count": 320,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_y = pd.read_csv('./pre_data/CC_train_y.csv', index_col=0)\n",
    "train_y.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "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>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>0.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>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>0.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>0.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>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  VISITDATE  Browse_count  AGE  CUST_START_DT  \\\n",
       "0  _PT5HFBEZJKOZ934      0.0    57328.0           6.0  4.0         2912.0   \n",
       "1  6STXUMWZRDCGSDDU      0.0   114708.0          12.0  2.0         7686.0   \n",
       "2  JDVF4U8JUANEID68      0.0    57329.0           6.0  4.0         2484.0   \n",
       "3  8I6SQDGP9OQYUN1M      0.0    28659.0           3.0  1.0         7754.0   \n",
       "4  R-TRDUV3GHTID31I      0.0    28674.0           3.0  4.0         4062.0   \n",
       "\n",
       "   EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  WORK_MTHS  \n",
       "0       4.0          0.0                1.0        1.0  \n",
       "1       3.0          1.0                1.0        1.0  \n",
       "2       3.0          0.0                4.0        1.0  \n",
       "3       6.0          0.0                1.0        1.0  \n",
       "4       2.0          1.0                1.0        1.0  "
      ]
     },
     "execution_count": 321,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_X = pd.read_csv('./pre_data/CC_test_X.csv', index_col=0)\n",
    "test_X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "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>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>2.0</td>\n",
       "      <td>3.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>0.0</td>\n",
       "      <td>4.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>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>3</th>\n",
       "      <td>1.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>1.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": [
       "   count_x  Browse_count  AGE  EDU_CODE  GENDER_CODE  INCOME_RANGE_CODE  \\\n",
       "0      2.0           3.0  0.0       0.0          0.0                0.0   \n",
       "1      1.0           0.0  4.0       6.0          0.0                1.0   \n",
       "2      1.0           0.0  0.0       0.0          0.0                0.0   \n",
       "3      1.0           0.0  0.0       0.0          0.0                0.0   \n",
       "4      1.0           0.0  0.0       0.0          0.0                0.0   \n",
       "\n",
       "   WORK_MTHS  \n",
       "0        0.0  \n",
       "1        1.0  \n",
       "2        0.0  \n",
       "3        0.0  \n",
       "4        0.0  "
      ]
     },
     "execution_count": 322,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del_col = ['CUST_NO', 'VISITDATE', 'CUST_START_DT',]\n",
    "scale_col = []\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": 323,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The percentage of 1 label:  label    0.044282\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"The percentage of 1 label: \", train_y.sum()/train_y.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filter= AGE    (577302, 7)  |  (577302, 1)\n",
      "Filter= WORK_MTHS    (470152, 7)  |  (470152, 1)\n",
      "Filter= EDU_CODE    (469937, 7)  |  (469937, 1)\n",
      "Filter= Browse_count    (118089, 7)  |  (118089, 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": 326,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The percentage of 1 label:  label    0.280788\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"The percentage of 1 label: \", train_y.sum()/train_y.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "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": 328,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(94471, 7) (23618, 7) (94471, 1) (23618, 1)\n"
     ]
    }
   ],
   "source": [
    "print(X_train.shape, X_valid.shape, y_train.shape, y_valid.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_train = y_train.reshape((-1,))\n",
    "y_valid = y_valid.reshape((-1,))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "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": 331,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "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": 332,
     "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": 333,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9771784232365145"
      ]
     },
     "execution_count": 333,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eval_result = clf.predict(X_valid)\n",
    "evalute(eval_result, y_valid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "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['CC_IND']= pred\n",
    "sub_df.to_csv('./pred/submi_CC.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.1968"
      ]
     },
     "execution_count": 338,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub_df['CC_IND'].sum() / sub_df['CC_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
}