NextHack (отчёт).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NextHack\n",
    "\n",
    "https://ds.sbidea.ru/data\n",
    "\n",
    "Участникам соревнования необходимо предсказать вероятность увольнения менеджера по продажам в следующие три месяца.\n",
    "\n",
    "Для построения модели предлагается использовать следующие данные:\n",
    "- SAP о структурной позиции сотрудника, наличии выговоров, отпусков, больничных, производительности и т. п. \n",
    "- Единый Профиль Клиента по сотруднику, как Клиенту Банка (активность использования продуктов Банка),\n",
    "- информацию о входах-выходах сотрудников во внутреннюю сеть Банка и суммарном нахождении в сети за день.\n",
    "\n",
    "Оценка алгоритма - ROC AUC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2717: DtypeWarning: Columns (0,2) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "train = pd.read_csv(\"train_small.csv\")\n",
    "test = pd.read_csv(\"test.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Анализ данных, заполнение пропусков"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C_Year</th>\n",
       "      <th>EtalonPosName</th>\n",
       "      <th>R_Year</th>\n",
       "      <th>age_qty</th>\n",
       "      <th>boss_pos_name</th>\n",
       "      <th>boss_subord_1y_max_qty</th>\n",
       "      <th>boss_subord_1y_min_qty</th>\n",
       "      <th>boss_subord_in_1m_qty</th>\n",
       "      <th>boss_subord_in_1y_qty</th>\n",
       "      <th>boss_subord_out_1m_qty</th>\n",
       "      <th>...</th>\n",
       "      <th>up_day_nv_sum_3m</th>\n",
       "      <th>up_day_nv_sum_6m</th>\n",
       "      <th>vl_nv_KBP_sum_3m</th>\n",
       "      <th>vl_nv_KBP_sum_6m</th>\n",
       "      <th>vl_nv_MP_sum_3m</th>\n",
       "      <th>vl_nv_MP_sum_6m</th>\n",
       "      <th>vl_nv_sum_3m</th>\n",
       "      <th>vl_nv_sum_6m</th>\n",
       "      <th>vsp_cd</th>\n",
       "      <th>work_rate_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C</td>\n",
       "      <td>0.0</td>\n",
       "      <td>D</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1189</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>44.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>543</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>371</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 397 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  C_Year  EtalonPosName R_Year  age_qty  boss_pos_name  \\\n",
       "0      C            0.0      D     26.0            0.0   \n",
       "1      C            0.0      C     25.0            0.0   \n",
       "2      C            0.0      C     44.0            0.0   \n",
       "3      B            0.0      C     24.0            0.0   \n",
       "4      C            0.0      C     25.0            0.0   \n",
       "\n",
       "   boss_subord_1y_max_qty  boss_subord_1y_min_qty  boss_subord_in_1m_qty  \\\n",
       "0                    33.0                    31.0                    0.0   \n",
       "1                    33.0                    28.0                    1.0   \n",
       "2                    17.0                    13.0                    2.0   \n",
       "3                    21.0                     1.0                    0.0   \n",
       "4                    15.0                    11.0                    1.0   \n",
       "\n",
       "   boss_subord_in_1y_qty  boss_subord_out_1m_qty      ...        \\\n",
       "0                    2.0                     0.0      ...         \n",
       "1                   14.0                     1.0      ...         \n",
       "2                   15.0                     2.0      ...         \n",
       "3                   33.0                     1.0      ...         \n",
       "4                    7.0                     0.0      ...         \n",
       "\n",
       "   up_day_nv_sum_3m  up_day_nv_sum_6m  vl_nv_KBP_sum_3m  vl_nv_KBP_sum_6m  \\\n",
       "0               0.0               0.0               0.0               0.0   \n",
       "1               0.0               0.0               0.0               0.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   \n",
       "\n",
       "   vl_nv_MP_sum_3m  vl_nv_MP_sum_6m  vl_nv_sum_3m  vl_nv_sum_6m  vsp_cd  \\\n",
       "0              0.0              0.0           0.0           0.0      17   \n",
       "1              0.0              0.0           0.0           0.0    1189   \n",
       "2              0.0              0.0           0.0           0.0       7   \n",
       "3              0.0              0.0           0.0           0.0     543   \n",
       "4              0.0              0.0           0.0           0.0     371   \n",
       "\n",
       "   work_rate_pct  \n",
       "0            1.0  \n",
       "1            1.0  \n",
       "2            1.0  \n",
       "3            1.0  \n",
       "4            1.0  \n",
       "\n",
       "[5 rows x 397 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "C_Year                            27672\n",
       "EtalonPosName                      1110\n",
       "R_Year                            27708\n",
       "age_qty                              14\n",
       "boss_pos_name                      1272\n",
       "boss_subord_1y_max_qty             1460\n",
       "boss_subord_1y_min_qty             1460\n",
       "boss_subord_in_1m_qty              1460\n",
       "boss_subord_in_1y_qty              1460\n",
       "boss_subord_out_1m_qty             1460\n",
       "boss_subord_out_1y_qty             1460\n",
       "boss_subord_qty                    1460\n",
       "boss_tabnum                           0\n",
       "crd_cc_act_qty                      238\n",
       "crd_cc_act_spend_qty                238\n",
       "crd_cc_ever_nflag                   127\n",
       "crd_cc_mnth_snc_issue_frac        34194\n",
       "crd_cc_mnth_snc_issue_qty         34193\n",
       "crd_cc_overdue_nflag                127\n",
       "crd_cc_own_bal_rub_amt            17776\n",
       "crd_cc_pos_auto_qty               87920\n",
       "crd_cc_pos_auto_rub_amt           87920\n",
       "crd_cc_pos_beuaty_qty             92464\n",
       "crd_cc_pos_beuaty_rub_amt         92464\n",
       "crd_cc_pos_clothes_qty            84938\n",
       "crd_cc_pos_clothes_rub_amt        84938\n",
       "crd_cc_pos_dept_stores_qty        88128\n",
       "crd_cc_pos_dept_stores_rub_amt    88128\n",
       "crd_cc_pos_eat_out_qty            83622\n",
       "crd_cc_pos_eat_out_rub_amt        83622\n",
       "                                  ...  \n",
       "tt_not_paid_sick_1y_qty               0\n",
       "tt_other_1m_qty                       0\n",
       "tt_other_1y_qty                       0\n",
       "tt_overwork_1m_qty                    0\n",
       "tt_overwork_1y_qty                    0\n",
       "tt_paid_holiday_1m_qty                0\n",
       "tt_paid_holiday_1y_qty                0\n",
       "tt_paid_sick_1m_qty                   0\n",
       "tt_paid_sick_1y_qty                   0\n",
       "up_day_nv_KBP_sum_3m                  0\n",
       "up_day_nv_KBP_sum_6m                  0\n",
       "up_day_nv_MP                       1835\n",
       "up_day_nv_MP_avg_3m                1030\n",
       "up_day_nv_MP_avg_6m                 876\n",
       "up_day_nv_MP_max3m                 1030\n",
       "up_day_nv_MP_max6m                  876\n",
       "up_day_nv_MP_min3m                 1030\n",
       "up_day_nv_MP_min6m                  876\n",
       "up_day_nv_MP_sum_3m                   0\n",
       "up_day_nv_MP_sum_6m                   0\n",
       "up_day_nv_sum_3m                      0\n",
       "up_day_nv_sum_6m                      0\n",
       "vl_nv_KBP_sum_3m                    234\n",
       "vl_nv_KBP_sum_6m                   1226\n",
       "vl_nv_MP_sum_3m                    9992\n",
       "vl_nv_MP_sum_6m                   18980\n",
       "vl_nv_sum_3m                          0\n",
       "vl_nv_sum_6m                          0\n",
       "vsp_cd                                0\n",
       "work_rate_pct                       105\n",
       "Length: 397, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В обучающих и тестовых данных много пропусков, нужно их заполнить. Вычисляем медианы по столбцам обучающей выборки:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "medians = train.median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.fillna(medians, inplace=True)\n",
    "test.fillna(medians, inplace=True);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Смотрим, какие столбцы остались с пропусками:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['C_Year', 'R_Year', 'crd_cc_pos_othr_qty', 'crd_cc_pos_othr_rub_amt',\n",
       "       'crd_dc_pos_othr_qty', 'crd_dc_pos_othr_rub_amt', 'gender',\n",
       "       'lbt_payroll_min_dt', 'srv_sbol_atm_1st_login_ever_dt',\n",
       "       'srv_sbol_mob_1st_login_ever_dt', 'srv_sbol_web_1st_login_ever_dt'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.columns[train.isnull().sum() > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Удаляем столбцы с датами и числовые столбцы, значения которых не удалось заполнить:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cols_to_delete = ['crd_cc_pos_othr_qty', 'crd_cc_pos_othr_rub_amt', 'crd_dc_pos_othr_qty', 'crd_dc_pos_othr_rub_amt', \n",
    "                  'lbt_payroll_min_dt', 'srv_sbol_atm_1st_login_ever_dt','srv_sbol_mob_1st_login_ever_dt', \n",
    "                  'srv_sbol_web_1st_login_ever_dt', 'report_dt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.drop(cols_to_delete, axis=1, inplace=True)\n",
    "test.drop(cols_to_delete, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Категориальные фичи кодируем с помощью one-hot encoding:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train = pd.get_dummies(train)\n",
    "test = pd.get_dummies(test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Выясняем, что выборка перекошенная &mdash; уволилось на два порядка меньше людей, чем осталось:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0    96402\n",
       "1.0     3598\n",
       "Name: target_churn_lag1m, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train[\"target_churn_lag1m\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Подготовка выборки для валидации"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Для валидации будем использовать те записи из тренировочной выборки, которые наиболее похожи на записи из тестовой выборки."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train[\"test\"] = 0\n",
    "test[\"test\"] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = train.append(test).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "X = data.drop([\"target_churn_lag1m\", \"test\"], axis=1)\n",
    "y = data[\"test\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "from xgboost import XGBClassifier\n",
    "from sklearn.model_selection import cross_val_predict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "clf = XGBClassifier()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "pred = cross_val_predict(clf, X, y, cv=5, verbose=2, method='predict_proba')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "th = 0.999"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "val_index = X[(pred[:, 1] > th) & (data[\"test\"] == 0)].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# train.loc[val_index, :].drop(\"test\", axis=1).to_csv(\"_validation.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# train[~train.index.isin(val_index)].drop(\"test\", axis=1).to_csv(\"_train2.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2717: DtypeWarning: Columns (0,2) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "train = pd.read_csv('_train2.csv')\n",
    "test = pd.read_csv('_validation.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Создание новых фич"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wtd = pd.read_csv(\"work_time_day.csv\", index_col='tabnum')\n",
    "wd = pd.read_csv(\"work_dur.csv\", index_col='tabnum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def prepare_data(data, has_churn=True):\n",
    "    data = data.merge(wtd, left_on='tabnum', right_index=True, how='left')\n",
    "    data.drop(['work_time_day_max', 'work_time_day_min'], axis=1, inplace=True)\n",
    "\n",
    "    data = data.merge(wd, left_on='tabnum', right_index=True, how='left')\n",
    "    data.drop(['work_dur_max', 'work_dur_min'], axis=1, inplace=True)\n",
    "\n",
    "    data.drop(['tabnum'], axis=1, inplace=True)\n",
    "\n",
    "    data[\"f3\"] = data['crd_inf_cash_adv_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f4\"] = data['crd_inf_ext_transf_rub_amt_sum_6m'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f5\"] = data['crd_inf_pos_ret_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f6\"] = data['crd_otf_cc_pos_spend_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f7\"] = data['crd_otf_cc_total_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f8\"] = data['crd_otf_dc_cash_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f9\"] = data['crd_otf_dc_pos_spend_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f10\"] = data['crd_otf_pos_spend_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f11\"] = data['crd_otf_total_rub_amt_sum_6m'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f12\"] = data['crd_otf_trnf_pers_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f13\"] = data['crd_otf_trnf_pers_sbol_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f14\"] = data['crd_txn_spnd_3m_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f15\"] = data['cred_mg_act_initial_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f16\"] = data['cred_mg_os_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f17\"] = data['cred_tot_act_initial_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f18\"] = data['cred_tot_mnth_pmt_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f19\"] = data['cred_tot_os_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f20\"] = data['ext_client_app_income_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f21\"] = data['lbt_acct_dc_bal_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f22\"] = data['lbt_acct_dep_ca_bal_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f23\"] = data['lbt_acct_dep_tot_bal_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f24\"] = data['lbt_acct_tot_bal_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f25\"] = data['lbt_acct_tot_davg_mnth_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f26\"] = data['lbt_inf_intr_cap_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f27\"] = data['lbt_inf_topup_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f28\"] = data['lbt_inf_total_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f29\"] = data['lbt_payroll_l6m_tot_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f30\"] = data['lbt_payroll_tax_payer_cd'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f31\"] = data['lne_mg_debt_os_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f32\"] = data['lne_pl_debt_os_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f33\"] = data['lne_tot_max_ovrd_ever_rub_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f34\"] = data['srv_ap_txn_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f35\"] = data['srv_sbol_txn_3m_amt'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f36\"] = data['tot_sld_up_avg_6m'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f37\"] = data['tot_sld_up_max6m'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f38\"] = data['tot_sld_up_min6m'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f39\"] = data['tot_sld_up_sum_6m'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "    data[\"f40\"] = data['total_sold_up'] / data[\"lbt_inf_payroll_rub_amt\"]\n",
    "\n",
    "    if has_churn:\n",
    "        return data.drop(['target_churn_lag1m'], axis=1), data['target_churn_lag1m']\n",
    "    else:\n",
    "        return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train, y_train = prepare_data(train)\n",
    "X_test, y_test = prepare_data(test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Отбор признаков"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Пробовались разные варианты:\n",
    "\n",
    "- алгоритм Boruta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from boruta import BorutaPy\n",
    "from sklearn.ensemble import RandomForestClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rf = RandomForestClassifier(max_depth=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "feat_selector = BorutaPy(rf, n_estimators='auto', random_state=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "feat_selector.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_names = X_train.columns[feat_selector.support_]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Recursive feature elimination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.feature_selection import RFE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rfe = RFE(XGBClassifier(), n_features_to_select=1, verbose=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "rfe.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ranking = rfe.ranking_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "X_train.columns[ranking.argsort()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- жадный поиск"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "max_score = 0\n",
    "col_mask = [False] * len(X_train.columns)\n",
    "ranking_argsort = ranking.argsort()\n",
    "\n",
    "for i in range(len(ranking_argsort)):\n",
    "    col_mask[ranking_argsort[i]] = True\n",
    "    clf = XGBClassifier()\n",
    "    clf.fit(X_train[X_train.columns[col_mask]], y_train)\n",
    "    score = roc_auc_score(y_test, clf.predict_proba(X_test[X_test.columns[col_mask]])[:, 1])\n",
    "    print(i+1, score, \"\" if score > max_score else \"skipping\")\n",
    "    if score > max_score:\n",
    "        max_score = score\n",
    "    else:\n",
    "        col_mask[ranking_argsort[i]] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_names = X_test.columns[col_mask]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Борьба с перекошенной обучающей выборкой"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from imblearn.under_sampling import RandomUnderSampler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "prediction = np.zeros(y_test.shape[0])\n",
    "n_estimators = 500\n",
    "\n",
    "for i in range(n_estimators):\n",
    "    if i % 100 == 0:\n",
    "        print(\"predictions \" + str(i + 1) + \"-\" + str(min(i + 100, n_estimators)))\n",
    "    rus = RandomUnderSampler()\n",
    "    X_resampled, y_resampled = rus.fit_resample(X_train, y_train)\n",
    "    clf = XGBClassifier()\n",
    "    clf.fit(X_resampled, y_resampled)\n",
    "    prediction += clf.predict_proba(X_test.values)[:,1]\n",
    "    \n",
    "prediction /= n_estimators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Подбор гиперпараметров"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "clf = XGBClassifier(n_estimators=10000, reg_lambda=70, gamma=4)\n",
    "\n",
    "clf.fit(X_train, y_train, early_stopping_rounds=30, eval_metric=\"auc\", \\\n",
    "        eval_set=[(X_train, y_train), (X_test, y_test)], verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "roc_auc_score(y_test, prediction)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Было проведено множество экспериментов, составлялась таблица:\n",
    "\n",
    "<img src=\"2019-09-09_192435.png\"/>\n",
    "\n",
    "Отправки в проверяющую систему кратко описывались:\n",
    "\n",
    "<img src=\"2019-09-09_192751.png\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Подготовка решения к отправке"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train = pd.read_csv('train.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "X_train, y_train = prepare_data(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "clf = XGBClassifier(n_estimators=240, reg_alpha=15)\n",
    "\n",
    "clf.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test = pd.read_csv(\"test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "X_test = prepare_data(test, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "prediction = clf.predict_proba(X_test)[:,1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.DataFrame({'tabnum': test['tabnum'], 'prob': prediction}).to_csv(\"submission.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}