thuan-hist-features.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns \n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "from util import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('max_columns', None)\n",
    "pd.set_option(\"max_rows\", None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>trans_amount</th>\n",
       "      <th>ben_cust_name</th>\n",
       "      <th>msisdn</th>\n",
       "      <th>process_code</th>\n",
       "      <th>error_code</th>\n",
       "      <th>correct_code</th>\n",
       "      <th>request_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000.0</td>\n",
       "      <td>NGUYEN THI LIEN</td>\n",
       "      <td>0001caa88ddf200efd2c</td>\n",
       "      <td>641000</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>2020-08-14 12:33:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>300000.0</td>\n",
       "      <td>PHAM THI LAN HUONG</td>\n",
       "      <td>0001238e025a0f131d4e</td>\n",
       "      <td>640001</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>2020-08-14 09:09:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>400000.0</td>\n",
       "      <td>TRAN PHUONG THAO</td>\n",
       "      <td>0001a233794b9745400c</td>\n",
       "      <td>640000</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>2020-08-14 02:47:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29940000.0</td>\n",
       "      <td>CONG TY TNHH SAO XANH</td>\n",
       "      <td>0001ceb9bebc5fbcdfb8</td>\n",
       "      <td>640001</td>\n",
       "      <td>00</td>\n",
       "      <td>00</td>\n",
       "      <td>2020-08-14 08:17:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>162864.0</td>\n",
       "      <td>None</td>\n",
       "      <td>0001aff517869188ab1e</td>\n",
       "      <td>300001</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>2020-08-14 02:32:02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trans_amount          ben_cust_name                msisdn process_code  \\\n",
       "0     1000000.0        NGUYEN THI LIEN  0001caa88ddf200efd2c       641000   \n",
       "1      300000.0     PHAM THI LAN HUONG  0001238e025a0f131d4e       640001   \n",
       "2      400000.0       TRAN PHUONG THAO  0001a233794b9745400c       640000   \n",
       "3    29940000.0  CONG TY TNHH SAO XANH  0001ceb9bebc5fbcdfb8       640001   \n",
       "4      162864.0                   None  0001aff517869188ab1e       300001   \n",
       "\n",
       "  error_code correct_code        request_date  \n",
       "0         00           00 2020-08-14 12:33:05  \n",
       "1         00           00 2020-08-14 09:09:30  \n",
       "2         00           00 2020-08-14 02:47:13  \n",
       "3         00           00 2020-08-14 08:17:05  \n",
       "4         16           16 2020-08-14 02:32:02  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_df = pd.read_parquet(\"data/trans_hist_1m.snappy.parquet\")\n",
    "hist_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(14733736, 7)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>msisdn</th>\n",
       "      <th>ben_cust_name</th>\n",
       "      <th colspan=\"2\" halign=\"left\">trans_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00010000021da335cd1c</td>\n",
       "      <td>HAN-LE-Vinhomes Times city Park Hill ca 2</td>\n",
       "      <td>18033000.0</td>\n",
       "      <td>9016500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00010000029e7e24958a</td>\n",
       "      <td>VÀNG MÍ VỪ</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0001000012afca2aff7c</td>\n",
       "      <td>DUONG XUAN LINH</td>\n",
       "      <td>1200000.0</td>\n",
       "      <td>1200000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0001000012afca2aff7c</td>\n",
       "      <td>NGUYEN TRAN THAO</td>\n",
       "      <td>11740000.0</td>\n",
       "      <td>2348000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0001000012afca2aff7c</td>\n",
       "      <td>Nguyen Tran Thao</td>\n",
       "      <td>649106.0</td>\n",
       "      <td>649106.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 msisdn                              ben_cust_name  \\\n",
       "                                                                     \n",
       "0  00010000021da335cd1c  HAN-LE-Vinhomes Times city Park Hill ca 2   \n",
       "1  00010000029e7e24958a                                 VÀNG MÍ VỪ   \n",
       "2  0001000012afca2aff7c                            DUONG XUAN LINH   \n",
       "3  0001000012afca2aff7c                           NGUYEN TRAN THAO   \n",
       "4  0001000012afca2aff7c                           Nguyen Tran Thao   \n",
       "\n",
       "  trans_amount             \n",
       "           sum       mean  \n",
       "0   18033000.0  9016500.0  \n",
       "1      50000.0    50000.0  \n",
       "2    1200000.0  1200000.0  \n",
       "3   11740000.0  2348000.0  \n",
       "4     649106.0   649106.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_df = hist_df.groupby(['msisdn', 'ben_cust_name'], as_index=False).agg({'trans_amount':['sum', 'mean']})\n",
    "hist_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_df.columns = ['_'.join(col) for col in hist_df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['msisdn_', 'ben_cust_name_', 'trans_amount_sum', 'trans_amount_mean'], dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>province_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>viettel_bank_code</th>\n",
       "      <th>channel_type_id</th>\n",
       "      <th>channel_code</th>\n",
       "      <th>staff_code</th>\n",
       "      <th>trans_amount</th>\n",
       "      <th>trans_fee</th>\n",
       "      <th>trans_type</th>\n",
       "      <th>process_code</th>\n",
       "      <th>channel_fee</th>\n",
       "      <th>shop_code</th>\n",
       "      <th>customer_fee</th>\n",
       "      <th>cust_name</th>\n",
       "      <th>ben_cust_name</th>\n",
       "      <th>msisdn</th>\n",
       "      <th>ben_msisdn</th>\n",
       "      <th>msisdn_channel</th>\n",
       "      <th>staff_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D501</td>\n",
       "      <td>D501008</td>\n",
       "      <td>MB</td>\n",
       "      <td>164</td>\n",
       "      <td>HOANT35_VTPOST_DKN</td>\n",
       "      <td>HOANT35_VTPOST_DKN</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>8182.0</td>\n",
       "      <td>1</td>\n",
       "      <td>645200</td>\n",
       "      <td>5400.0</td>\n",
       "      <td>1800170007</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>HỒ THỊ KIM TRANG</td>\n",
       "      <td>MAI VAN QUY</td>\n",
       "      <td>0001b3807ce1879a06eb</td>\n",
       "      <td>0001b3807ce1879a06eb</td>\n",
       "      <td>000106f0196c5e4f0853</td>\n",
       "      <td>Nguyễn Thị Hoa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>T008</td>\n",
       "      <td>T00807</td>\n",
       "      <td>MB</td>\n",
       "      <td>164</td>\n",
       "      <td>TRANGNNT2_VTPOST_HCM</td>\n",
       "      <td>TRANGNNT2_VTPOST_HCM</td>\n",
       "      <td>3000000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>400300</td>\n",
       "      <td>5500.0</td>\n",
       "      <td>1800590021</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>vu thi thanh giang</td>\n",
       "      <td>LE TUNG LAM</td>\n",
       "      <td>0001c4bc0350dc5c8c80</td>\n",
       "      <td>0001c4bc0350dc5c8c80</td>\n",
       "      <td>000145eb7b76f4e77de5</td>\n",
       "      <td>Nguyễn Ngọc Thùy Trang</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D061</td>\n",
       "      <td>D061003</td>\n",
       "      <td>VTT</td>\n",
       "      <td>6</td>\n",
       "      <td>2200190007</td>\n",
       "      <td>DAUNT2_DNI_DLXNK</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>400100</td>\n",
       "      <td>1260.0</td>\n",
       "      <td>2200190007</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NGUYỄN VĂN TIỀN</td>\n",
       "      <td>NGUYỄN VĂN TIỀN</td>\n",
       "      <td>00011341b2c0b40071f7</td>\n",
       "      <td>00011341b2c0b40071f7</td>\n",
       "      <td>0001d5dcf92a0dab9da1</td>\n",
       "      <td>Nguyễn Tranh Đấu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>T008</td>\n",
       "      <td>T00801</td>\n",
       "      <td>KPP</td>\n",
       "      <td>197</td>\n",
       "      <td>6600240001</td>\n",
       "      <td>6600240001</td>\n",
       "      <td>393692.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>PAY_BILL</td>\n",
       "      <td>770.0</td>\n",
       "      <td>6600240001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0001c1333ab419853e05</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C026</td>\n",
       "      <td>C026001</td>\n",
       "      <td>KPP</td>\n",
       "      <td>1</td>\n",
       "      <td>1000113100_00004_DBCN</td>\n",
       "      <td>1000113100_00004_DBCN</td>\n",
       "      <td>349384.0</td>\n",
       "      <td>909.0</td>\n",
       "      <td>3</td>\n",
       "      <td>KPP301</td>\n",
       "      <td>700.0</td>\n",
       "      <td>1000113100_VTP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>00019784c19ed12c546c</td>\n",
       "      <td>None</td>\n",
       "      <td>00019784c19ed12c546c</td>\n",
       "      <td>Diem CCDVVT LD_Dang Dinh Lam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  province_code district_code viettel_bank_code channel_type_id  \\\n",
       "0          D501       D501008                MB             164   \n",
       "1          T008        T00807                MB             164   \n",
       "2          D061       D061003               VTT               6   \n",
       "3          T008        T00801               KPP             197   \n",
       "4          C026       C026001               KPP               1   \n",
       "\n",
       "            channel_code             staff_code  trans_amount  trans_fee  \\\n",
       "0     HOANT35_VTPOST_DKN     HOANT35_VTPOST_DKN      500000.0     8182.0   \n",
       "1   TRANGNNT2_VTPOST_HCM   TRANGNNT2_VTPOST_HCM     3000000.0    10000.0   \n",
       "2             2200190007       DAUNT2_DNI_DLXNK      600000.0        0.0   \n",
       "3             6600240001             6600240001      393692.0     1000.0   \n",
       "4  1000113100_00004_DBCN  1000113100_00004_DBCN      349384.0      909.0   \n",
       "\n",
       "  trans_type process_code  channel_fee       shop_code  customer_fee  \\\n",
       "0          1       645200       5400.0      1800170007       18000.0   \n",
       "1          2       400300       5500.0      1800590021       11000.0   \n",
       "2          2       400100       1260.0      2200190007           0.0   \n",
       "3          3     PAY_BILL        770.0      6600240001           0.0   \n",
       "4          3       KPP301        700.0  1000113100_VTP           0.0   \n",
       "\n",
       "            cust_name    ben_cust_name                msisdn  \\\n",
       "0    HỒ THỊ KIM TRANG      MAI VAN QUY  0001b3807ce1879a06eb   \n",
       "1  vu thi thanh giang      LE TUNG LAM  0001c4bc0350dc5c8c80   \n",
       "2     NGUYỄN VĂN TIỀN  NGUYỄN VĂN TIỀN  00011341b2c0b40071f7   \n",
       "3                None             None                  None   \n",
       "4                None             None  00019784c19ed12c546c   \n",
       "\n",
       "             ben_msisdn        msisdn_channel                    staff_name  \n",
       "0  0001b3807ce1879a06eb  000106f0196c5e4f0853                Nguyễn Thị Hoa  \n",
       "1  0001c4bc0350dc5c8c80  000145eb7b76f4e77de5        Nguyễn Ngọc Thùy Trang  \n",
       "2  00011341b2c0b40071f7  0001d5dcf92a0dab9da1              Nguyễn Tranh Đấu  \n",
       "3                  None  0001c1333ab419853e05                          None  \n",
       "4                  None  00019784c19ed12c546c  Diem CCDVVT LD_Dang Dinh Lam  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "use_cols = [\n",
    "    \"province_code\",\n",
    "    \"district_code\",\n",
    "    \"viettel_bank_code\",\n",
    "    \"channel_type_id\",\n",
    "    \"channel_code\",\n",
    "    \"staff_code\",\n",
    "    \"trans_amount\",\n",
    "    \"trans_fee\",\n",
    "    \"trans_type\",\n",
    "    \"process_code\",\n",
    "    \"channel_fee\",\n",
    "    \"shop_code\",\n",
    "    \"customer_fee\",\n",
    "    \"cust_name\",\n",
    "    \"ben_cust_name\",\n",
    "    \"msisdn\",\n",
    "    \"ben_msisdn\",\n",
    "    \"msisdn_channel\",\n",
    "    \"staff_name\"\n",
    "]\n",
    "train_df = pd.read_parquet(\"data/data-202008.snappy.parquet\", columns=use_cols)\n",
    "train_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7193506, 19)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape"
   ]
  },
  {
   "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>province_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>viettel_bank_code</th>\n",
       "      <th>channel_type_id</th>\n",
       "      <th>channel_code</th>\n",
       "      <th>staff_code</th>\n",
       "      <th>trans_amount</th>\n",
       "      <th>trans_fee</th>\n",
       "      <th>trans_type</th>\n",
       "      <th>process_code</th>\n",
       "      <th>channel_fee</th>\n",
       "      <th>shop_code</th>\n",
       "      <th>customer_fee</th>\n",
       "      <th>cust_name</th>\n",
       "      <th>ben_cust_name</th>\n",
       "      <th>msisdn</th>\n",
       "      <th>ben_msisdn</th>\n",
       "      <th>msisdn_channel</th>\n",
       "      <th>staff_name</th>\n",
       "      <th>msisdn_</th>\n",
       "      <th>ben_cust_name_</th>\n",
       "      <th>trans_amount_sum</th>\n",
       "      <th>trans_amount_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D501</td>\n",
       "      <td>D501008</td>\n",
       "      <td>MB</td>\n",
       "      <td>164</td>\n",
       "      <td>HOANT35_VTPOST_DKN</td>\n",
       "      <td>HOANT35_VTPOST_DKN</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>8182.0</td>\n",
       "      <td>1</td>\n",
       "      <td>645200</td>\n",
       "      <td>5400.0</td>\n",
       "      <td>1800170007</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>HỒ THỊ KIM TRANG</td>\n",
       "      <td>MAI VAN QUY</td>\n",
       "      <td>0001b3807ce1879a06eb</td>\n",
       "      <td>0001b3807ce1879a06eb</td>\n",
       "      <td>000106f0196c5e4f0853</td>\n",
       "      <td>Nguyễn Thị Hoa</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>T008</td>\n",
       "      <td>T00807</td>\n",
       "      <td>MB</td>\n",
       "      <td>164</td>\n",
       "      <td>TRANGNNT2_VTPOST_HCM</td>\n",
       "      <td>TRANGNNT2_VTPOST_HCM</td>\n",
       "      <td>3000000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>400300</td>\n",
       "      <td>5500.0</td>\n",
       "      <td>1800590021</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>vu thi thanh giang</td>\n",
       "      <td>LE TUNG LAM</td>\n",
       "      <td>0001c4bc0350dc5c8c80</td>\n",
       "      <td>0001c4bc0350dc5c8c80</td>\n",
       "      <td>000145eb7b76f4e77de5</td>\n",
       "      <td>Nguyễn Ngọc Thùy Trang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D061</td>\n",
       "      <td>D061003</td>\n",
       "      <td>VTT</td>\n",
       "      <td>6</td>\n",
       "      <td>2200190007</td>\n",
       "      <td>DAUNT2_DNI_DLXNK</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>400100</td>\n",
       "      <td>1260.0</td>\n",
       "      <td>2200190007</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NGUYỄN VĂN TIỀN</td>\n",
       "      <td>NGUYỄN VĂN TIỀN</td>\n",
       "      <td>00011341b2c0b40071f7</td>\n",
       "      <td>00011341b2c0b40071f7</td>\n",
       "      <td>0001d5dcf92a0dab9da1</td>\n",
       "      <td>Nguyễn Tranh Đấu</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>T008</td>\n",
       "      <td>T00801</td>\n",
       "      <td>KPP</td>\n",
       "      <td>197</td>\n",
       "      <td>6600240001</td>\n",
       "      <td>6600240001</td>\n",
       "      <td>393692.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>PAY_BILL</td>\n",
       "      <td>770.0</td>\n",
       "      <td>6600240001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0001c1333ab419853e05</td>\n",
       "      <td>None</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>C026</td>\n",
       "      <td>C026001</td>\n",
       "      <td>KPP</td>\n",
       "      <td>1</td>\n",
       "      <td>1000113100_00004_DBCN</td>\n",
       "      <td>1000113100_00004_DBCN</td>\n",
       "      <td>349384.0</td>\n",
       "      <td>909.0</td>\n",
       "      <td>3</td>\n",
       "      <td>KPP301</td>\n",
       "      <td>700.0</td>\n",
       "      <td>1000113100_VTP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>00019784c19ed12c546c</td>\n",
       "      <td>None</td>\n",
       "      <td>00019784c19ed12c546c</td>\n",
       "      <td>Diem CCDVVT LD_Dang Dinh Lam</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": [
       "  province_code district_code viettel_bank_code channel_type_id  \\\n",
       "0          D501       D501008                MB             164   \n",
       "1          T008        T00807                MB             164   \n",
       "2          D061       D061003               VTT               6   \n",
       "3          T008        T00801               KPP             197   \n",
       "4          C026       C026001               KPP               1   \n",
       "\n",
       "            channel_code             staff_code  trans_amount  trans_fee  \\\n",
       "0     HOANT35_VTPOST_DKN     HOANT35_VTPOST_DKN      500000.0     8182.0   \n",
       "1   TRANGNNT2_VTPOST_HCM   TRANGNNT2_VTPOST_HCM     3000000.0    10000.0   \n",
       "2             2200190007       DAUNT2_DNI_DLXNK      600000.0        0.0   \n",
       "3             6600240001             6600240001      393692.0     1000.0   \n",
       "4  1000113100_00004_DBCN  1000113100_00004_DBCN      349384.0      909.0   \n",
       "\n",
       "  trans_type process_code  channel_fee       shop_code  customer_fee  \\\n",
       "0          1       645200       5400.0      1800170007       18000.0   \n",
       "1          2       400300       5500.0      1800590021       11000.0   \n",
       "2          2       400100       1260.0      2200190007           0.0   \n",
       "3          3     PAY_BILL        770.0      6600240001           0.0   \n",
       "4          3       KPP301        700.0  1000113100_VTP           0.0   \n",
       "\n",
       "            cust_name    ben_cust_name                msisdn  \\\n",
       "0    HỒ THỊ KIM TRANG      MAI VAN QUY  0001b3807ce1879a06eb   \n",
       "1  vu thi thanh giang      LE TUNG LAM  0001c4bc0350dc5c8c80   \n",
       "2     NGUYỄN VĂN TIỀN  NGUYỄN VĂN TIỀN  00011341b2c0b40071f7   \n",
       "3                None             None                  None   \n",
       "4                None             None  00019784c19ed12c546c   \n",
       "\n",
       "             ben_msisdn        msisdn_channel                    staff_name  \\\n",
       "0  0001b3807ce1879a06eb  000106f0196c5e4f0853                Nguyễn Thị Hoa   \n",
       "1  0001c4bc0350dc5c8c80  000145eb7b76f4e77de5        Nguyễn Ngọc Thùy Trang   \n",
       "2  00011341b2c0b40071f7  0001d5dcf92a0dab9da1              Nguyễn Tranh Đấu   \n",
       "3                  None  0001c1333ab419853e05                          None   \n",
       "4                  None  00019784c19ed12c546c  Diem CCDVVT LD_Dang Dinh Lam   \n",
       "\n",
       "  msisdn_ ben_cust_name_  trans_amount_sum  trans_amount_mean  \n",
       "0     NaN            NaN               NaN                NaN  \n",
       "1     NaN            NaN               NaN                NaN  \n",
       "2     NaN            NaN               NaN                NaN  \n",
       "3     NaN            NaN               NaN                NaN  \n",
       "4     NaN            NaN               NaN                NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df = train_df.merge(hist_df, how='left', left_on=['ben_msisdn', 'staff_name'], right_on=['msisdn_', 'ben_cust_name_'])\n",
    "train_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "object     17\n",
       "float64     6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.dtypes.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['viettel_bank_code',\n",
       " 'msisdn_channel',\n",
       " 'msisdn_',\n",
       " 'msisdn',\n",
       " 'district_code',\n",
       " 'ben_cust_name_',\n",
       " 'process_code',\n",
       " 'staff_code',\n",
       " 'ben_msisdn',\n",
       " 'shop_code',\n",
       " 'province_code',\n",
       " 'channel_code',\n",
       " 'cust_name',\n",
       " 'trans_type',\n",
       " 'staff_name',\n",
       " 'ben_cust_name',\n",
       " 'channel_type_id']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_cols = train_df._get_numeric_data().columns\n",
    "cat_cols = list(set(train_df.columns) - set(num_cols))\n",
    "cat_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_features = ['viettel_bank_code',\n",
    " 'msisdn_channel',\n",
    " 'msisdn_',\n",
    " 'msisdn',\n",
    " 'district_code',\n",
    " 'ben_cust_name_',\n",
    " 'process_code',\n",
    " 'staff_code',\n",
    " 'ben_msisdn',\n",
    " 'shop_code',\n",
    " 'province_code',\n",
    " 'channel_code',\n",
    " 'cust_name',\n",
    " 'trans_type',\n",
    " 'staff_name',\n",
    " 'ben_cust_name',\n",
    " 'channel_type_id']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['request_date_dt'] = pd.to_datetime(train['request_date'], format='%Y-%m-%d %H:%M:%S')\n",
    "train['date_diff'] = (train['request_date_dt'] - train['request_date_dt'].min()).dt.days\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['is_fraud'].value_counts().sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train[['is_fraud' > 1]].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['is_fraud'].astype(int).plot.hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def missing_values_table(df):\n",
    "        # Total missing values\n",
    "        mis_val = df.isnull().sum()\n",
    "        \n",
    "        # Percentage of missing values\n",
    "        mis_val_percent = 100 * df.isnull().sum() / len(df)\n",
    "        \n",
    "        # Make a table with the results\n",
    "        mis_val_table = pd.concat([mis_val, mis_val_percent], axis=1)\n",
    "        \n",
    "        # Rename the columns\n",
    "        mis_val_table_ren_columns = mis_val_table.rename(\n",
    "        columns = {0 : 'Missing Values', 1 : '% of Total Values'})\n",
    "        \n",
    "        # Sort the table by percentage of missing descending\n",
    "        mis_val_table_ren_columns = mis_val_table_ren_columns[\n",
    "            mis_val_table_ren_columns.iloc[:,1] != 0].sort_values(\n",
    "        '% of Total Values', ascending=False).round(1)\n",
    "        \n",
    "        # Print some summary information\n",
    "        print (\"Your selected dataframe has \" + str(df.shape[1]) + \" columns.\\n\"      \n",
    "            \"There are \" + str(mis_val_table_ren_columns.shape[0]) +\n",
    "              \" columns that have missing values.\")\n",
    "        \n",
    "        # Return the dataframe with missing information\n",
    "        return mis_val_table_ren_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Missing values statistics\n",
    "missing_values = missing_values_table(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_values.head(200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.dtypes.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.select_dtypes('object').apply(pd.Series.nunique, axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of NaNs for each object\n",
    "data.isnull().sum(axis=1).head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of NaNs for each column\n",
    "data.isnull().sum(axis=0).head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove constant features\n",
    "# `dropna = False` makes nunique treat NaNs as a distinct value\n",
    "feats_counts = data.nunique(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feats_counts.sort_values()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# constant_features = feats_counts.loc[feats_counts==1].index.tolist()\n",
    "# print (constant_features)\n",
    "\n",
    "# data.drop(constant_features,axis = 1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove duplicated features\n",
    "data.fillna('NaN', inplace=True)\n",
    "train_enc =  pd.DataFrame(index = data.index)\n",
    "\n",
    "for col in tqdm_notebook(data.columns):\n",
    "    train_enc[col] = data[col].factorize()[0]\n",
    "    \n",
    "dup_cols = {}\n",
    "\n",
    "for i, c1 in enumerate(tqdm_notebook(train_enc.columns)):\n",
    "    for c2 in train_enc.columns[i + 1:]:\n",
    "        if c2 not in dup_cols and np.all(train_enc[c1] == train_enc[c2]):\n",
    "            dup_cols[c2] = c1\n",
    "dup_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# examine the number of unique values.\n",
    "nunique = data.nunique(dropna=False)\n",
    "nunique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(14,6))\n",
    "_ = plt.hist(nunique.astype(float)/data.shape[0], bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = (nunique.astype(float)/data.shape[0] > 0.25)\n",
    "data.loc[:, mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = (nunique.astype(float)/data.shape[0] < 0.4) & (nunique.astype(float)/data.shape[0] > 0.2)\n",
    "data.loc[:25, mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_cols = list(data.select_dtypes(include=['object']).columns)\n",
    "num_cols = list(data.select_dtypes(exclude=['object']).columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.replace('NaN', -999, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def autolabel(arrayA):\n",
    "    ''' label each colored square with the corresponding data value. \n",
    "    If value > 20, the text is in black, else in white.\n",
    "    '''\n",
    "    arrayA = np.array(arrayA)\n",
    "    for i in range(arrayA.shape[0]):\n",
    "        for j in range(arrayA.shape[1]):\n",
    "                plt.text(j,i, \"%.2f\"%arrayA[i,j], ha='center', va='bottom',color='w')\n",
    "\n",
    "def hist_it(feat):\n",
    "    plt.figure(figsize=(16,4))\n",
    "    feat[Y==0].hist(bins=range(int(feat.min()),int(feat.max()+2)),normed=True,alpha=0.8)\n",
    "    feat[Y==1].hist(bins=range(int(feat.min()),int(feat.max()+2)),normed=True,alpha=0.5)\n",
    "    plt.ylim((0,1))\n",
    "    \n",
    "def gt_matrix(feats,sz=16):\n",
    "    a = []\n",
    "    for i,c1 in enumerate(feats):\n",
    "        b = [] \n",
    "        for j,c2 in enumerate(feats):\n",
    "            mask = (~data[c1].isnull()) & (~data[c2].isnull())\n",
    "            if i>=j:\n",
    "                b.append((data.loc[mask,c1].values>=data.loc[mask,c2].values).mean())\n",
    "            else:\n",
    "                b.append((data.loc[mask,c1].values>data.loc[mask,c2].values).mean())\n",
    "\n",
    "        a.append(b)\n",
    "\n",
    "    plt.figure(figsize = (sz,sz))\n",
    "    plt.imshow(a, interpolation = 'None')\n",
    "    _ = plt.xticks(range(len(feats)),feats,rotation = 90)\n",
    "    _ = plt.yticks(range(len(feats)),feats,rotation = 0)\n",
    "    autolabel(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # select first 42 numeric features\n",
    "# feats = num_cols[:42]\n",
    "\n",
    "# # build 'mean(feat1 > feat2)' plot\n",
    "# gt_matrix(feats,16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.matshow(data.corr())\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.heatmap(data.corr(), annot=True)\n",
    "plt.show()"
   ]
  },
  {
   "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.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}