Kaggle_home_credit-Copy1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:22:39.439085Z",
     "start_time": "2018-08-09T05:22:37.604806Z"
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1ac45d8e90444f489f77894a87886af6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/html": [
       "<p>Failed to display Jupyter Widget of type <code>HBox</code>.</p>\n",
       "<p>\n",
       "  If you're reading this message in the Jupyter Notebook or JupyterLab Notebook, it may mean\n",
       "  that the widgets JavaScript is still loading. If this message persists, it\n",
       "  likely means that the widgets JavaScript library is either not installed or\n",
       "  not enabled. See the <a href=\"https://ipywidgets.readthedocs.io/en/stable/user_install.html\">Jupyter\n",
       "  Widgets Documentation</a> for setup instructions.\n",
       "</p>\n",
       "<p>\n",
       "  If you're reading this message in another frontend (for example, a static\n",
       "  rendering on GitHub or <a href=\"https://nbviewer.jupyter.org/\">NBViewer</a>),\n",
       "  it may mean that your frontend doesn't currently support widgets.\n",
       "</p>\n"
      ],
      "text/plain": [
       "HBox(children=(IntProgress(value=1, bar_style=u'info', max=1), HTML(value=u'')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import re\n",
    "# import xgboost\n",
    "\n",
    "import sklearn.metrics as metrics\n",
    "\n",
    "\n",
    "from sklearn.preprocessing import LabelEncoder, StandardScaler\n",
    "\n",
    "# from imblearn.over_sampling import SMOTE\n",
    "\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "\n",
    "from sklearn.ensemble import RandomForestClassifier, GradientBoostingClassifier, ExtraTreesClassifier\n",
    "\n",
    "from sklearn.linear_model import LinearRegression\n",
    "import datetime\n",
    "import lightgbm\n",
    "\n",
    "from sklearn import model_selection\n",
    "\n",
    "from itertools import permutations, combinations\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:53.432632Z",
     "start_time": "2018-08-09T05:23:53.426333Z"
    }
   },
   "outputs": [],
   "source": [
    "def encode(uniques):\n",
    "    codes = {}\n",
    "    for i, u in enumerate(uniques):\n",
    "        codes[u] = i\n",
    "    return codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:22:44.009158Z",
     "start_time": "2018-08-09T05:22:44.003346Z"
    }
   },
   "outputs": [],
   "source": [
    "dir_ = '/Users/aakashkerawat/.kaggle/competitions/home-credit-default-risk/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:22:50.405620Z",
     "start_time": "2018-08-09T05:22:44.186136Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(dir_+'/application_train.csv.zip', compression='zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:22:59.668775Z",
     "start_time": "2018-08-09T05:22:50.409864Z"
    }
   },
   "outputs": [],
   "source": [
    "df_pa = pd.read_csv(dir_+'previous_application.csv.zip', compression='zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:33:50.303859Z",
     "start_time": "2018-08-09T10:33:37.432908Z"
    }
   },
   "outputs": [],
   "source": [
    "df_cr = pd.read_csv(dir_+'credit_card_balance.csv.zip', compression='zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T06:55:00.498103Z",
     "start_time": "2018-08-09T06:54:42.250687Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ins = pd.read_csv(dir_+ 'installments_payments.csv.zip', compression='zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:39.340632Z",
     "start_time": "2018-08-09T05:23:29.056698Z"
    }
   },
   "outputs": [],
   "source": [
    "df_pos = pd.read_csv(dir_+ 'POS_CASH_balance.csv.zip', compression='zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:48.504256Z",
     "start_time": "2018-08-09T05:23:39.345162Z"
    }
   },
   "outputs": [],
   "source": [
    "df_bb = pd.read_csv(dir_+ 'bureau_balance.csv.zip', compression='zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:52.395594Z",
     "start_time": "2018-08-09T05:23:48.509715Z"
    }
   },
   "outputs": [],
   "source": [
    "df_bur = pd.read_csv(dir_+'bureau.csv.zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:53.219257Z",
     "start_time": "2018-08-09T05:23:52.422064Z"
    }
   },
   "outputs": [],
   "source": [
    "df_t = pd.read_csv('/Users/aakashkerawat/.kaggle/competitions/home-credit-default-risk/application_test.csv.zip', compression='zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'all files loaded.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:54.091754Z",
     "start_time": "2018-08-09T05:23:53.438036Z"
    }
   },
   "outputs": [],
   "source": [
    "df_c = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:54.191994Z",
     "start_time": "2018-08-09T05:23:54.095455Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = df_t.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df_t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'Label encoding...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:55.031808Z",
     "start_time": "2018-08-09T05:23:54.197091Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NAME_CONTRACT_TYPE\n",
      "CODE_GENDER\n",
      "FLAG_OWN_CAR\n",
      "FLAG_OWN_REALTY\n",
      "NAME_TYPE_SUITE\n",
      "NAME_INCOME_TYPE\n",
      "NAME_EDUCATION_TYPE\n",
      "NAME_FAMILY_STATUS\n",
      "NAME_HOUSING_TYPE\n",
      "OCCUPATION_TYPE\n",
      "WEEKDAY_APPR_PROCESS_START\n",
      "ORGANIZATION_TYPE\n",
      "FONDKAPREMONT_MODE\n",
      "HOUSETYPE_MODE\n",
      "WALLSMATERIAL_MODE\n",
      "EMERGENCYSTATE_MODE\n"
     ]
    }
   ],
   "source": [
    "le_dict = {}\n",
    "for col in df_c:\n",
    "    if df_c[col].dtype=='O':\n",
    "        print col\n",
    "        dict_ = encode(df_c[col].unique())\n",
    "        df_c[col] = df_c[col].map(dict_)\n",
    "        df_ct[col] = df_ct[col].map(dict_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:55.241358Z",
     "start_time": "2018-08-09T05:23:55.199727Z"
    }
   },
   "outputs": [],
   "source": [
    "def add_features(df_temp):\n",
    "#     df_temp['EXT_SOURCE_23'] = (df_temp['EXT_SOURCE_2']*df_temp['EXT_SOURCE_3'])\n",
    "\n",
    "    df_temp['EXT_SOURCE_1_NULL'] = df_temp.EXT_SOURCE_1.isnull()\n",
    "\n",
    "    df_temp['AGE'] = np.round(np.abs(df_temp['DAYS_BIRTH'])/365)\n",
    "    \n",
    "    docs = [_f for _f in df.columns if 'FLAG_DOC' in _f]\n",
    "    live = [_f for _f in df.columns if ('FLAG_' in _f) & ('FLAG_DOC' not in _f) & ('_FLAG_' not in _f)]\n",
    "    inc_by_org = df[['AMT_INCOME_TOTAL', 'ORGANIZATION_TYPE']].groupby('ORGANIZATION_TYPE').median()['AMT_INCOME_TOTAL']\n",
    "\n",
    "    df_temp['EMPLOYED_AFTER_APP'] = df_temp['DAYS_EMPLOYED']>0\n",
    "    df_temp['INCOME_CREDIT_RATIO'] = df_temp['AMT_CREDIT']/df_temp['AMT_INCOME_TOTAL']\n",
    "    df_temp['ANNUITY_UNDER_100K'] = df_temp['AMT_ANNUITY']<100000\n",
    "    df_temp['AMT_CREDIT_ANNUITY_RATIO'] = df_temp['AMT_CREDIT']/df_temp['AMT_ANNUITY']\n",
    "    df_temp['AMT_ANNUITY_INCOME_RATIO'] = df_temp['AMT_ANNUITY']/(1+df_temp['AMT_INCOME_TOTAL'])\n",
    "    \n",
    "    \n",
    "#     df_temp['NEW_CREDIT_TO_ANNUITY_RATIO'] = df_temp['AMT_CREDIT'] / df_temp['AMT_ANNUITY']\n",
    "    df_temp['NEW_CREDIT_TO_GOODS_RATIO'] = df_temp['AMT_CREDIT'] / df_temp['AMT_GOODS_PRICE']\n",
    "    df_temp['NEW_ANNUITY_TO_GOODS_RATIO'] = df_temp['AMT_ANNUITY'] / df_temp['AMT_GOODS_PRICE']\n",
    "    df_temp['NEW_DOC_IND_KURT'] = df_temp[docs].kurtosis(axis=1)\n",
    "    df_temp['NEW_LIVE_IND_SUM'] = df_temp[live].sum(axis=1)\n",
    "    df_temp['NEW_INC_PER_CHLD'] = df_temp['AMT_INCOME_TOTAL'] / (1 + df_temp['CNT_CHILDREN'])\n",
    "    df_temp['NEW_INC_BY_ORG'] = df_temp['ORGANIZATION_TYPE'].map(inc_by_org)\n",
    "    df_temp['NEW_EMPLOY_TO_BIRTH_RATIO'] = df_temp['DAYS_EMPLOYED'] / df_temp['DAYS_BIRTH']\n",
    "#     df_temp['NEW_ANNUITY_TO_INCOME_RATIO'] = df_temp['AMT_ANNUITY'] / (1 + df_temp['AMT_INCOME_TOTAL'])\n",
    "    df_temp['NEW_SOURCES_PROD'] = df_temp['EXT_SOURCE_1'] * df_temp['EXT_SOURCE_2'] * df_temp['EXT_SOURCE_3']\n",
    "    df_temp['NEW_EXT_SOURCES_MEAN'] = df_temp[['EXT_SOURCE_1', 'EXT_SOURCE_2', 'EXT_SOURCE_3']].mean(axis=1)\n",
    "    df_temp['NEW_SCORES_STD'] = df_temp[['EXT_SOURCE_1', 'EXT_SOURCE_2', 'EXT_SOURCE_3']].std(axis=1)\n",
    "    df_temp['NEW_SCORES_STD'] = df_temp['NEW_SCORES_STD'].fillna(df_temp['NEW_SCORES_STD'].mean())\n",
    "    df_temp['NEW_CAR_TO_BIRTH_RATIO'] = df_temp['OWN_CAR_AGE'] / df_temp['DAYS_BIRTH']\n",
    "    df_temp['NEW_CAR_TO_EMPLOY_RATIO'] = df_temp['OWN_CAR_AGE'] / df_temp['DAYS_EMPLOYED']\n",
    "    df_temp['NEW_PHONE_TO_BIRTH_RATIO'] = df_temp['DAYS_LAST_PHONE_CHANGE'] / df_temp['DAYS_BIRTH']\n",
    "    df_temp['NEW_PHONE_TO_BIRTH_RATIO'] = df_temp['DAYS_LAST_PHONE_CHANGE'] / df_temp['DAYS_EMPLOYED']\n",
    "#     df_temp['NEW_CREDIT_TO_INCOME_RATIO'] = df_temp['AMT_CREDIT'] / df_temp['AMT_INCOME_TOTAL']\n",
    "    \n",
    "#     for c in permutations([1,2,3], 2):\n",
    "#         num, den = c[0], c[1]\n",
    "#         df_temp['EXT_SOURCE_{}{}'.format(num, den)] = (df_temp['EXT_SOURCE_{}'.format(num)]*df_temp['EXT_SOURCE_{}'.format(den)]).replace(np.inf, np.nan)\n",
    "    return df_temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bureau data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:55.272870Z",
     "start_time": "2018-08-09T05:23:55.246429Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_bureau_features(df_bur_):\n",
    "    # credit bureau.csv features\n",
    "# DAYS_CREDIT - min, max, avg\n",
    "# CREDIT_DAY_OVERDUE - non-zero count, max, min ,mean\n",
    "# DAYS_CREDIT_ENDDATE - max\n",
    "# DAYS_ENDDATE_FACT - min\n",
    "# AMT_CREDIT_MAX_OVERDUE - max\n",
    "# CNT_CREDIT_PROLONG - max\n",
    "# AMT_CREDIT_SUM - max, min, avg\n",
    "# AMT_CREDIT_SUM_DEBT - max\n",
    "# AMT_CREDIT_SUM_LIMIT - max\n",
    "# AMT_CREDIT_SUM_OVERDUE - max\n",
    "# CREDIT_TYPE - unique count\n",
    "# DAYS_CREDIT_UPDATE - max\n",
    "# AMT_ANNUITY - mean, max\n",
    "    \n",
    "    df_bur_['DAYS_CREDIT_PREV_DIFF'] = df_bur_['DAYS_CREDIT']-df_bur_['DAYS_CREDIT'].shift()\n",
    "\n",
    "    df_bur_['temp_same_id'] = df_bur_['SK_ID_CURR']!=df_bur_['SK_ID_CURR'].shift()\n",
    "\n",
    "    df_bur_.loc[df_bur_['temp_same_id']==True, 'DAYS_CREDIT_PREV_DIFF'] = np.nan\n",
    "    \n",
    "    df_bur_['DAYS_CREDIT_TENURE'] = df_bur_['DAYS_CREDIT_ENDDATE']-df_bur_['DAYS_CREDIT']\n",
    "\n",
    "#     del df_bur['temp_same_id']\n",
    "    df_bur_['AMT_ANNUITY_CREDIT_RATIO'] = (df_bur_['AMT_CREDIT_SUM']/df_bur_['AMT_ANNUITY']).replace(np.inf, np.nan)\n",
    "    df_bur_['AMT_ANNUITY_CREDIT_OVERDUE_RATIO'] = (df_bur_['AMT_CREDIT_SUM_OVERDUE']/df_bur_['AMT_ANNUITY']).replace(np.inf, np.nan)\n",
    "    df_bur_['AMT_ANNUITY_CREDIT_DUE_RATIO'] = (df_bur_['AMT_CREDIT_SUM_DEBT']/df_bur_['AMT_ANNUITY']).replace(np.inf, np.nan)\n",
    "    agg_dict = {'DAYS_CREDIT':['max', 'min', 'mean'], 'CREDIT_DAY_OVERDUE':'max', 'DAYS_CREDIT_ENDDATE':'max',\n",
    "               'DAYS_ENDDATE_FACT':'min', 'AMT_CREDIT_MAX_OVERDUE':'max', 'CNT_CREDIT_PROLONG':'max',\n",
    "               'AMT_CREDIT_SUM':['max', 'min', 'mean'], 'AMT_CREDIT_SUM_DEBT':'max', 'AMT_CREDIT_SUM_LIMIT':'max',\n",
    "               'AMT_CREDIT_SUM_OVERDUE':'max', 'CREDIT_TYPE':'nunique', 'DAYS_CREDIT_UPDATE':'max',\n",
    "                'AMT_ANNUITY':['max', 'mean'], \n",
    "                'AMT_ANNUITY_CREDIT_RATIO':['max', 'mean']\n",
    "                , 'AMT_ANNUITY_CREDIT_OVERDUE_RATIO':['max', 'mean'] , 'AMT_ANNUITY_CREDIT_DUE_RATIO':['max', 'mean']\n",
    "               ,'DAYS_CREDIT_PREV_DIFF':['mean', 'max'], 'DAYS_CREDIT_TENURE':['min', 'max']}\n",
    "    agg_feats = df_bur_.groupby(['SK_ID_CURR']).agg(agg_dict).reset_index()\n",
    "    agg_feats.columns = ['BUR_'+'_'.join(cols) for cols in agg_feats.columns.values]\n",
    "    agg_feats.rename(columns={'BUR_SK_ID_CURR_':'SK_ID_CURR'}, inplace=True)\n",
    "    \n",
    "    df_bur_['BUR_CREDIT_DAY_OVERDUE_nonzero'] = df_bur_['CREDIT_DAY_OVERDUE']!=0\n",
    "    cdo = df_bur_.groupby('SK_ID_CURR')['BUR_CREDIT_DAY_OVERDUE_nonzero'].sum().reset_index()\n",
    "    agg_feats = agg_feats.merge(cdo, 'left', 'SK_ID_CURR')\n",
    "    \n",
    "\n",
    "\n",
    "    return agg_feats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'adding bureau features...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:57.048848Z",
     "start_time": "2018-08-09T05:23:55.277423Z"
    }
   },
   "outputs": [],
   "source": [
    "df_bur = df_bur.sort_values(['SK_ID_CURR', 'DAYS_CREDIT', 'SK_ID_BUREAU']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:23:57.395356Z",
     "start_time": "2018-08-09T05:23:57.055404Z"
    }
   },
   "outputs": [],
   "source": [
    "ulim = df_bur.loc[df_bur['DAYS_CREDIT_ENDDATE']>5000].index\n",
    "df_bur = df_bur.drop(ulim).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:00.302408Z",
     "start_time": "2018-08-09T05:23:57.401506Z"
    }
   },
   "outputs": [],
   "source": [
    "df_b = get_bureau_features(df_bur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:02.155966Z",
     "start_time": "2018-08-09T05:24:00.328617Z"
    }
   },
   "outputs": [],
   "source": [
    "df_bur = df_bur.sort_values(['SK_ID_CURR', 'SK_ID_BUREAU']).reset_index(drop=True)\n",
    "\n",
    "active_count = df_bur.groupby(['SK_ID_CURR'])['CREDIT_ACTIVE'].value_counts().unstack().reset_index()\n",
    "\n",
    "active_count = active_count[['SK_ID_CURR', 'Active', 'Sold']]\n",
    "\n",
    "active_count.fillna(0.0,inplace=True)\n",
    "\n",
    "active_count = active_count.rename(columns = lambda x: 'BUR_'+x if x!='SK_ID_CURR' else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:02.289100Z",
     "start_time": "2018-08-09T05:24:02.162144Z"
    }
   },
   "outputs": [],
   "source": [
    "df_b = df_b.merge(active_count, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prev_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:02.338204Z",
     "start_time": "2018-08-09T05:24:02.305236Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_previous_data(df_temp):\n",
    "    final_agg = df_temp['SK_ID_CURR'].drop_duplicates().reset_index()\n",
    "    df_temp['DAYS_actual_vs_supposed_first_due'] = df_temp['DAYS_FIRST_DUE']-df_pa['DAYS_LAST_DUE_1ST_VERSION']\n",
    "    amt_cols = [col for col in df_temp if col.startswith('AMT')]\n",
    "    days_cols = [col for col in df_temp if col.startswith('DAYS')]\n",
    "    \n",
    "    amt_agg = df_temp.groupby('SK_ID_CURR')[amt_cols].agg(['mean', 'max']).reset_index()\n",
    "    amt_agg.columns = [v[0]+v[1] for v in amt_agg.columns.values]\n",
    "    final_agg = final_agg.merge(amt_agg, 'left', 'SK_ID_CURR')\n",
    "    print 'amt agg merged.'\n",
    "    \n",
    "    days_agg = df_temp.groupby('SK_ID_CURR')[days_cols].agg(['max', 'min']).reset_index()\n",
    "    days_agg.columns = [v[0]+v[1] for v in days_agg.columns.values]\n",
    "    final_agg = final_agg.merge(days_agg, 'left', 'SK_ID_CURR')\n",
    "    print 'days agg merged.'\n",
    "    nflag_mean = df_temp.groupby('SK_ID_CURR')['NFLAG_INSURED_ON_APPROVAL'].mean().reset_index().rename(columns={'NFALG_INSURED_ON_APPROVAL':'NFALG_INSURED_ON_APPROVAL_mean'})\n",
    "    final_agg = final_agg.merge(nflag_mean, 'left', 'SK_ID_CURR')\n",
    "    \n",
    "    print 'taking type count'\n",
    "    type_count_cols = ['NAME_CLIENT_TYPE', 'NAME_CONTRACT_TYPE', 'NAME_CONTRACT_STATUS', \n",
    "                       'NAME_YIELD_GROUP', 'NAME_PRODUCT_TYPE'] #'CHANNEL_TYPE'\n",
    "    for col in type_count_cols:\n",
    "        print '\\t', col\n",
    "        temp_cnt = df_temp.groupby('SK_ID_CURR')[col].value_counts().unstack().reset_index().fillna(0)\n",
    "#         print temp_cnt.columns\n",
    "        temp_cnt = temp_cnt.rename(columns = lambda x: x+'_'+col if x!='SK_ID_CURR' else x)\n",
    "#         print temp_cnt.columns\n",
    "        final_agg = final_agg.merge(temp_cnt, 'left', 'SK_ID_CURR')\n",
    "    \n",
    "    \n",
    "#     for i, c in enumerate(permutations(range(len(amt_cols)), 2)):\n",
    "#         num, den = amt_cols[c[0]], amt_cols[c[1]]\n",
    "#         df_temp['{}_{}_RATIO'.format(num, den)] = (df_temp['{}'.format(num)]*df_temp['{}'.format(den)]).replace(np.inf, np.nan)\n",
    "#         print '{}_{}_RATIO'.format(num, den)\n",
    "\n",
    "#     all ratio permutations can be removed except one or two which might add value (still very little), check f_imp\n",
    "    \n",
    "    return final_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'adding prev application features...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:02.494588Z",
     "start_time": "2018-08-09T05:24:02.343537Z"
    }
   },
   "outputs": [],
   "source": [
    "size_pa = df_pa.groupby('SK_ID_CURR').size().reset_index().rename(columns={0:'count_PA'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:09.927732Z",
     "start_time": "2018-08-09T05:24:02.500566Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amt agg merged.\n",
      "days agg merged.\n",
      "taking type count\n",
      "\tNAME_CLIENT_TYPE\n",
      "\tNAME_CONTRACT_TYPE\n",
      "\tNAME_CONTRACT_STATUS\n",
      "\tNAME_YIELD_GROUP\n",
      "\tNAME_PRODUCT_TYPE\n"
     ]
    }
   ],
   "source": [
    "df_prev = get_previous_data(df_pa)\n",
    "\n",
    "null_cols = [col for col in df_prev if 'XNA' in col]\n",
    "\n",
    "df_prev.drop(null_cols, 1, inplace=True)\n",
    "\n",
    "df_prev = df_prev.merge(size_pa, 'left', 'SK_ID_CURR')\n",
    "\n",
    "del df_prev['index']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:10.166509Z",
     "start_time": "2018-08-09T05:24:09.933854Z"
    }
   },
   "outputs": [],
   "source": [
    "df_prev = df_prev.rename(columns= lambda x: x+'_'+'PREV' if x!='SK_ID_CURR' else x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### credit balance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'Adding credit balance features...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:33:54.358010Z",
     "start_time": "2018-08-09T10:33:50.308992Z"
    }
   },
   "outputs": [],
   "source": [
    "df_cr = df_cr.sort_values('SK_ID_CURR').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:13.949386Z",
     "start_time": "2018-08-09T05:24:13.923650Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_credit_features(df_temp):\n",
    "    amt_cols = [col for col in df_temp if col.startswith('AMT')]\n",
    "    amt_aggs = df_temp.groupby('SK_ID_CURR')[amt_cols].agg(['mean', 'max']).reset_index()\n",
    "    amt_aggs.columns = [v[0]+v[1] for v in amt_aggs.columns]\n",
    "    \n",
    "    cnt_cols = [col for col in df_temp if col.startswith('CNT')]\n",
    "    cnt_aggs = df_temp.groupby('SK_ID_CURR')[cnt_cols].agg(['max']).reset_index()\n",
    "    cnt_aggs.columns = [v[0]+v[1] for v in cnt_aggs.columns]\n",
    "    \n",
    "    m_max = df_temp.groupby('SK_ID_CURR')['MONTHS_BALANCE'].idxmax().reset_index()\n",
    "    \n",
    "    final_agg = amt_aggs.merge(cnt_aggs, 'outer', 'SK_ID_CURR')\n",
    "    final_agg = final_agg.merge(m_max, 'left', 'SK_ID_CURR')\n",
    "    \n",
    "    return final_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:37:35.680650Z",
     "start_time": "2018-08-09T10:37:35.643399Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_credit_features2(df_temp):\n",
    "    df_temp['LIM_BALANCE_DIFF'] = df_temp['AMT_CREDIT_LIMIT_ACTUAL']-df_temp['AMT_BALANCE']\n",
    "\n",
    "    df_temp['LIM_BALANCE_DIFF_RATIO'] = (df_temp['LIM_BALANCE_DIFF']/df_temp['AMT_CREDIT_LIMIT_ACTUAL']).replace(np.inf, np.nan)\n",
    "    agg_dict = {'MONTHS_BALANCE':['min'],\n",
    "            'AMT_BALANCE':['mean', 'min', 'max'],\n",
    "            'AMT_CREDIT_LIMIT_ACTUAL':['mean'],\n",
    "            'AMT_DRAWINGS_ATM_CURRENT':['mean', 'max', 'min', 'sum'],\n",
    "            'AMT_DRAWINGS_CURRENT':['mean', 'max', 'min', 'sum'],\n",
    "            'AMT_DRAWINGS_OTHER_CURRENT':['mean', 'max', 'min', 'sum'],\n",
    "            'AMT_DRAWINGS_POS_CURRENT':['mean', 'max', 'min', 'sum'],\n",
    "            'AMT_INST_MIN_REGULARITY':['mean', 'max','sum'],\n",
    "            'AMT_PAYMENT_CURRENT':['mean', 'max', 'min', 'sum'],\n",
    "            'AMT_PAYMENT_TOTAL_CURRENT':['mean', 'max', 'min', 'sum'],\n",
    "            'AMT_RECEIVABLE_PRINCIPAL':['mean', 'max'],\n",
    "            'AMT_RECIVABLE':['mean', 'max'],\n",
    "            'AMT_TOTAL_RECEIVABLE':['mean', 'max'],\n",
    "            'CNT_DRAWINGS_ATM_CURRENT':['sum', 'mean', 'max'],\n",
    "            'CNT_DRAWINGS_CURRENT':['sum', 'mean', 'max'],\n",
    "            'CNT_DRAWINGS_OTHER_CURRENT':['sum', 'mean', 'max'],\n",
    "            'CNT_DRAWINGS_POS_CURRENT':['sum', 'mean', 'max'],\n",
    "            'CNT_INSTALMENT_MATURE_CUM':['sum', 'mean', 'max'],\n",
    "            'SK_DPD':['max', 'sum'],\n",
    "            'SK_DPD_DEF':['max', 'sum'],\n",
    "            'LIM_BALANCE_DIFF':['min', 'mean'],\n",
    "            'LIM_BALANCE_DIFF_RATIO':['mean', 'max', 'min']}\n",
    "    int_agg = df_temp.groupby('SK_ID_PREV').agg(agg_dict).reset_index()\n",
    "    int_agg.columns = [v[0]+v[1] for v in int_agg.columns.values]\n",
    "    \n",
    "    int_df = df_temp[['SK_ID_CURR', 'SK_ID_PREV']].drop_duplicates()\n",
    "    \n",
    "    int_df = int_df.merge(int_agg, 'left', 'SK_ID_PREV')\n",
    "    final_agg = int_df.groupby('SK_ID_CURR').mean().reset_index()\n",
    "    \n",
    "#     final_agg = amt_aggs.merge(cnt_aggs, 'outer', 'SK_ID_CURR')\n",
    "#     final_agg = final_agg.merge(m_max, 'left', 'SK_ID_CURR')\n",
    "    \n",
    "    return final_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:37:41.293692Z",
     "start_time": "2018-08-09T10:37:38.855860Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cr_feats2 = get_credit_features2(df_cr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:40:29.009850Z",
     "start_time": "2018-08-09T10:40:28.852954Z"
    }
   },
   "outputs": [],
   "source": [
    "size_cr = df_cr.groupby('SK_ID_CURR').size().reset_index().rename(columns={0:'count_CR'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:26.264512Z",
     "start_time": "2018-08-09T05:24:14.061929Z"
    }
   },
   "outputs": [],
   "source": [
    "# cr_feats = get_credit_features(df_cr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:26.324166Z",
     "start_time": "2018-08-09T05:24:26.270605Z"
    }
   },
   "outputs": [],
   "source": [
    "# cr_feats = cr_feats.merge(size_cr, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:40:42.955977Z",
     "start_time": "2018-08-09T10:40:42.883658Z"
    }
   },
   "outputs": [],
   "source": [
    "cr_feats2 = cr_feats2.merge(size_cr, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### bureau balance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:32.543113Z",
     "start_time": "2018-08-09T05:24:26.330619Z"
    }
   },
   "outputs": [],
   "source": [
    "# res = df_bb.groupby(['SK_ID_BUREAU', 'STATUS'])['MONTHS_BALANCE'].agg(['max', 'min'])\n",
    "\n",
    "# res = res.unstack()\n",
    "\n",
    "# res.columns = [v[0]+v[1] for v in res.columns.values]\n",
    "\n",
    "# res = res.reset_index()\n",
    "\n",
    "# res_c = df_bb.groupby('SK_ID_BUREAU')['STATUS'].value_counts().unstack().reset_index()\n",
    "\n",
    "# res_f = res.merge(res_c, 'left', 'SK_ID_BUREAU')\n",
    "\n",
    "# df_bb_fm = res_f.merge(df_bur[['SK_ID_BUREAU', 'SK_ID_CURR']], 'left', ['SK_ID_BUREAU'])\n",
    "\n",
    "# df_bb_fm = df_bb_fm.groupby('SK_ID_CURR').agg(['max', 'min'])\n",
    "\n",
    "# df_bb_fm.columns = [v[0]+v[1] for v in df_bb_fm.columns.values]\n",
    "\n",
    "# df_bb_fm.reset_index(inplace=True)\n",
    "\n",
    "# df_bb_fm.drop([col for col in df_bb_fm if col.startswith('SK_ID_BUREAU')], 1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:24:41.708638Z",
     "start_time": "2018-08-09T05:24:41.692819Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_bb_fm.rename(columns = lambda x: 'BB_'+x if x!='SK_ID_CURR' else x, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### pos cash balance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'adding pos cash balance features...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:02.374589Z",
     "start_time": "2018-08-09T05:24:41.713158Z"
    }
   },
   "outputs": [],
   "source": [
    "df_pos = df_pos.sort_values(['SK_ID_CURR', 'MONTHS_BALANCE']).reset_index(drop=True)\n",
    "df_size = df_pos.groupby(['SK_ID_CURR', 'MONTHS_BALANCE', 'NAME_CONTRACT_STATUS']).size().reset_index()\n",
    "\n",
    "size_f = df_size.sort_values(['SK_ID_CURR', 0], ascending=False).drop_duplicates(subset=['SK_ID_CURR', 'NAME_CONTRACT_STATUS'])\n",
    "\n",
    "ac_com = size_f.pivot('SK_ID_CURR', 'NAME_CONTRACT_STATUS', 0)[['Active', 'Completed']]\n",
    "\n",
    "ac_com = ac_com.reset_index()\n",
    "\n",
    "df_pos_fm = ac_com.copy()\n",
    "\n",
    "df_pos_fm['total'] = df_pos_fm['Active']+df_pos_fm['Completed'].fillna(0)\n",
    "\n",
    "df_pos_fm.loc[(df_pos_fm['Active'].isnull()) & (df_pos_fm['Completed'].notnull()), 'total'] = -1\n",
    "\n",
    "df_pos_fm.loc[(df_pos_fm['Active'].isnull()) & (df_pos_fm['Completed'].isnull()), 'total'] = -2\n",
    "\n",
    "df_pos_fm.rename(columns = lambda x: x+'_aat' if x!='SK_ID_CURR' else x, inplace=True)\n",
    "\n",
    "n_loans = df_pos.groupby('SK_ID_CURR')['SK_ID_PREV'].nunique().reset_index().rename(columns={'SK_ID_PREV':'total_taken'})\n",
    "\n",
    "df_pos_fm = df_pos_fm.merge(n_loans, 'left', 'SK_ID_CURR')\n",
    "\n",
    "com_loans = df_pos.groupby('SK_ID_CURR')['NAME_CONTRACT_STATUS'].value_counts().unstack().reset_index()\n",
    "\n",
    "df_pos_fm = df_pos_fm.merge(com_loans[['SK_ID_CURR', 'Completed']], 'left', 'SK_ID_CURR')\n",
    "\n",
    "df_pos_fm.fillna(0, inplace=True)\n",
    "\n",
    "df_pos_fm['still_active'] = df_pos_fm['total_taken']-df_pos_fm['Completed'].fillna(0)\n",
    "\n",
    "# avg loan tenure\n",
    "loan_tenure = df_pos.groupby(['SK_ID_CURR', 'SK_ID_PREV'])['CNT_INSTALMENT_FUTURE'].max().reset_index()\n",
    "\n",
    "loan_tenure = loan_tenure.groupby('SK_ID_CURR')['CNT_INSTALMENT_FUTURE'].mean().reset_index().rename(columns = {'CNT_INSTALMENT_FUTURE':'avg_loan_tenure'})\n",
    "\n",
    "df_pos_fm = df_pos_fm.merge(loan_tenure, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:02.388607Z",
     "start_time": "2018-08-09T05:25:02.382207Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_pos = df_pos.sort_values(['SK_ID_CURR', 'SK_ID_PREV']).reset_index(drop=True)\n",
    "\n",
    "# size_pos = df_pos.groupby('SK_ID_CURR').size().reset_index().rename(columns={0:'count_POS'})\n",
    "\n",
    "# req_cols = df_pos.columns.drop(['SK_ID_CURR', 'SK_ID_PREV', 'NAME_CONTRACT_STATUS'])\n",
    "\n",
    "# # mean changed to min after EDA\n",
    "# df_pos_fm = df_pos.groupby('SK_ID_CURR')[req_cols].agg(['min', 'max']).reset_index()\n",
    "\n",
    "# df_pos_fm.columns = [v[0]+v[1] for v in df_pos_fm.columns.values]\n",
    "\n",
    "# df_pos_fm = df_pos_fm.merge(size_pos, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### installment payment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'adding installment payment features...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T06:55:02.512482Z",
     "start_time": "2018-08-09T06:55:00.527172Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ins = df_ins.loc[df_ins['NUM_INSTALMENT_VERSION']!=0].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:05.053544Z",
     "start_time": "2018-08-09T05:25:05.031566Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_ins_features(df_temp):\n",
    "#     df_temp = df_temp_.copy()\n",
    "    df_temp['INSTALLMENT_PAYMENT_RATIO'] = (df_temp['AMT_INSTALMENT']/df_temp['AMT_PAYMENT']).replace(np.inf, np.nan)\n",
    "    df_temp['INSTALLMENT_PAYMENT_DIFF'] = df_temp['AMT_INSTALMENT']-df_temp['AMT_PAYMENT']\n",
    "    df_ins['DAYS_SUPPOSED_ACTUAL_INS_DIFF'] = df_ins['DAYS_ENTRY_PAYMENT']-df_ins['DAYS_INSTALMENT']\n",
    "    df_ins['PAYMENT_AFTER_SUPPOSED_DAY'] = ((df_ins['DAYS_SUPPOSED_ACTUAL_INS_DIFF']>0).astype(int))*df_ins['DAYS_SUPPOSED_ACTUAL_INS_DIFF']\n",
    "    df_ins['PAYMENT_BEFORE_SUPPOSED_DAY'] = ((df_ins['DAYS_SUPPOSED_ACTUAL_INS_DIFF']<0).astype(int))*np.abs(df_ins['DAYS_SUPPOSED_ACTUAL_INS_DIFF'])\n",
    "    size_ins = df_temp.groupby('SK_ID_CURR').size().reset_index().rename(columns={0:'count_INS'})\n",
    "    del df_temp['SK_ID_PREV']\n",
    "    f2 = df_temp.groupby('SK_ID_CURR')['NUM_INSTALMENT_VERSION'].nunique().reset_index().rename(columns={'NUM_INSTALMENT_VERSION':'NUM_INSTALMENT_VERSIONnunique'})\n",
    "    del df_temp['NUM_INSTALMENT_VERSION']\n",
    "    f3 = df_temp.groupby('SK_ID_CURR').agg(['mean', 'max', 'min', 'sum']).reset_index()\n",
    "    f3.columns = [v[0]+v[1] for v in f3.columns.values]\n",
    "    \n",
    "    df_ins_fm = f3.merge(f2, 'left', 'SK_ID_CURR')\n",
    "    df_ins_fm = df_ins_fm.merge(size_ins, 'left', 'SK_ID_CURR')\n",
    "    return df_ins_fm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T06:55:02.673594Z",
     "start_time": "2018-08-09T06:55:02.537750Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_ins_features2(df_temp):\n",
    "#     df_temp = df_temp_.copy()\n",
    "    df_temp['INSTALLMENT_PAYMENT_RATIO'] = (df_temp['AMT_INSTALMENT']/df_temp['AMT_PAYMENT']).replace(np.inf, np.nan)\n",
    "    df_temp['INSTALLMENT_PAYMENT_DIFF'] = df_temp['AMT_INSTALMENT']-df_temp['AMT_PAYMENT']\n",
    "    df_temp['DAYS_SUPPOSED_ACTUAL_INS_DIFF'] = df_temp['DAYS_ENTRY_PAYMENT']-df_temp['DAYS_INSTALMENT']\n",
    "    df_temp['PAYMENT_AFTER_SUPPOSED_DAY'] = ((df_temp['DAYS_SUPPOSED_ACTUAL_INS_DIFF']>0).astype(int))*df_temp['DAYS_SUPPOSED_ACTUAL_INS_DIFF']\n",
    "    df_temp['PAYMENT_BEFORE_SUPPOSED_DAY'] = ((df_temp['DAYS_SUPPOSED_ACTUAL_INS_DIFF']<0).astype(int))*np.abs(df_temp['DAYS_SUPPOSED_ACTUAL_INS_DIFF'])\n",
    "    size_ins = df_temp.groupby('SK_ID_CURR').size().reset_index().rename(columns={0:'count_INS'})\n",
    "    df_temp['DQ_AMOUNT_INSTALMENT_AFTER'] = (df_temp['DAYS_SUPPOSED_ACTUAL_INS_DIFF']>0).astype(int)*df_temp['INSTALLMENT_PAYMENT_DIFF']\n",
    "    df_temp['DQ_AMOUNT_INSTALMENT_BEFORE'] = (df_temp['DAYS_SUPPOSED_ACTUAL_INS_DIFF']<0).astype(int)*df_temp['INSTALLMENT_PAYMENT_DIFF']\n",
    "    \n",
    "    p_std = df_temp.groupby('SK_ID_PREV')['AMT_PAYMENT'].std().reset_index()\n",
    "    i_std = df_temp.groupby('SK_ID_PREV')['AMT_INSTALMENT'].std().reset_index()\n",
    "    i_std = i_std.merge(p_std, 'left', 'SK_ID_PREV')\n",
    "    i_std['AMT_PAY_INS_STD_DIFF'] = i_std['AMT_INSTALMENT']-i_std['AMT_PAYMENT']\n",
    "    df_temp = df_temp.merge(i_std, 'left', 'SK_ID_PREV', suffixes=('', '_std'))\n",
    "#     del df_temp['SK_ID_PREV']\n",
    "#     f2 = df_temp.groupby('SK_ID_CURR')['NUM_INSTALMENT_VERSION'].nunique().reset_index().rename(columns={'NUM_INSTALMENT_VERSION':'NUM_INSTALMENT_VERSIONnunique'})\n",
    "#     del df_temp['NUM_INSTALMENT_VERSION']\n",
    "    \n",
    "    agg_dict = {'NUM_INSTALMENT_NUMBER':['count'], \n",
    "                'DAYS_INSTALMENT': ['min', 'max'],\n",
    "                'DAYS_ENTRY_PAYMENT':['min', 'max'],\n",
    "                'AMT_INSTALMENT':['min', 'max', 'sum', 'median'],\n",
    "                'AMT_PAYMENT':['min', 'max', 'sum', 'median'],\n",
    "                'INSTALLMENT_PAYMENT_RATIO':['max', 'mean'],\n",
    "                'INSTALLMENT_PAYMENT_DIFF':['max', 'min','sum', 'mean'], \n",
    "                'DAYS_SUPPOSED_ACTUAL_INS_DIFF':['sum'],\n",
    "                'PAYMENT_AFTER_SUPPOSED_DAY':['mean', 'max', 'min', 'sum'],\n",
    "                'PAYMENT_BEFORE_SUPPOSED_DAY':['mean', 'max', 'min', 'sum'],\n",
    "                'AMT_PAY_INS_STD_DIFF':['mean', 'max', 'min'],\n",
    "                'AMT_PAYMENT_std':['mean', 'max', 'min'],\n",
    "                'AMT_INSTALMENT_std':['mean', 'max', 'min'],\n",
    "                'DQ_AMOUNT_INSTALMENT_AFTER':['max', 'mean', 'sum'],\n",
    "                'DQ_AMOUNT_INSTALMENT_BEFORE':['max', 'mean', 'sum']}\n",
    "    f1 = df_temp.groupby('SK_ID_CURR').agg(agg_dict).reset_index()\n",
    "    f1.columns = [v[0]+v[1] for v in f1.columns.values]\n",
    "    \n",
    "    df_temp_fm = f1.merge(size_ins, 'left', 'SK_ID_CURR')\n",
    "    return df_temp_fm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T06:56:27.545310Z",
     "start_time": "2018-08-09T06:56:05.849643Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ins_feats = get_ins_features2(df_ins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'Merging all to train data...'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### merging all data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:26.775838Z",
     "start_time": "2018-08-09T05:25:24.514319Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_c = add_features(df_c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:26.787847Z",
     "start_time": "2018-08-09T05:25:26.781762Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_c.drop([col for col in df_c if col.startswith('BUR_')], 1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:27.668695Z",
     "start_time": "2018-08-09T05:25:26.802287Z"
    }
   },
   "outputs": [],
   "source": [
    "df_c = df_c.merge(df_b, 'left', 'SK_ID_CURR') # adding bureau features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:27.680530Z",
     "start_time": "2018-08-09T05:25:27.674503Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_c.drop([col for col in df_c if col.endswith('_PREV')], 1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:28.621973Z",
     "start_time": "2018-08-09T05:25:27.685860Z"
    }
   },
   "outputs": [],
   "source": [
    "df_c = df_c.merge(df_prev, 'left', 'SK_ID_CURR', suffixes=('', '_PREV'))  # adding prev application features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:29.207220Z",
     "start_time": "2018-08-09T05:25:28.628042Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_c = df_c.merge(cr_feats, 'left', 'SK_ID_CURR', suffixes=('', '_CR'))  # adding credit card features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:41:05.812657Z",
     "start_time": "2018-08-09T10:41:02.581723Z"
    }
   },
   "outputs": [],
   "source": [
    "df_c = df_c.merge(cr_feats2, 'left', 'SK_ID_CURR', suffixes=('', '_CR'))  # adding credit card features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:31.800679Z",
     "start_time": "2018-08-09T05:25:29.213130Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_c = df_c.merge(df_bb_fm, 'left', 'SK_ID_CURR', suffixes=('', '_BB'))  # adding bureau balance features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:34.956876Z",
     "start_time": "2018-08-09T05:25:31.807586Z"
    }
   },
   "outputs": [],
   "source": [
    "df_c = df_c.merge(df_pos_fm, 'left', 'SK_ID_CURR', suffixes=('', '_POS'))  # adding pos cash balance features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T06:56:51.864371Z",
     "start_time": "2018-08-09T06:56:48.345492Z"
    }
   },
   "outputs": [],
   "source": [
    "df_c = df_c.merge(ins_feats, 'left', 'SK_ID_CURR', suffixes=('', '_INS'))  # adding installment features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:38.683788Z",
     "start_time": "2018-08-09T05:25:38.562858Z"
    }
   },
   "outputs": [],
   "source": [
    "bur_size = df_bur.groupby('SK_ID_CURR').size().reset_index().rename(columns={0:'count_bur'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:42.461289Z",
     "start_time": "2018-08-09T05:25:38.688892Z"
    }
   },
   "outputs": [],
   "source": [
    "df_c = df_c.merge(bur_size, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'merging all to test data...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:42.869849Z",
     "start_time": "2018-08-09T05:25:42.466948Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = add_features(df_ct)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:43.046296Z",
     "start_time": "2018-08-09T05:25:42.875786Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = df_ct.merge(df_b, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:43.140844Z",
     "start_time": "2018-08-09T05:25:43.051100Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = df_ct.merge(bur_size, 'left', 'SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:43.391903Z",
     "start_time": "2018-08-09T05:25:43.145805Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = df_ct.merge(df_prev, 'left', 'SK_ID_CURR', suffixes=('', '_PREV'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:43.533617Z",
     "start_time": "2018-08-09T05:25:43.398187Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_ct = df_ct.merge(cr_feats, 'left', 'SK_ID_CURR', suffixes=('', '_CR'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:43.533617Z",
     "start_time": "2018-08-09T05:25:43.398187Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = df_ct.merge(cr_feats2, 'left', 'SK_ID_CURR', suffixes=('', '_CR'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:43.925235Z",
     "start_time": "2018-08-09T05:25:43.539857Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_ct = df_ct.merge(df_bb_fm, 'left', 'SK_ID_CURR', suffixes=('', '_BB'))  # adding bureau balance features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:44.355117Z",
     "start_time": "2018-08-09T05:25:43.931834Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = df_ct.merge(df_pos_fm, 'left', 'SK_ID_CURR', suffixes=('', '_POS'))  # adding pos cash balance features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:44.898463Z",
     "start_time": "2018-08-09T05:25:44.360029Z"
    }
   },
   "outputs": [],
   "source": [
    "df_ct = df_ct.merge(ins_feats, 'left', 'SK_ID_CURR', suffixes=('', '_INS'))  # adding installment features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### modeling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T11:10:10.678496Z",
     "start_time": "2018-08-09T11:10:10.398217Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_c' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-584faa3c1cb4>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mpredictors\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'SK_ID_CURR'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'TARGET'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'DAYS_BIRTH'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'NEW_EXT_SOURCES_MEAN'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'NEW_SOURCES_PROD'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'df_c' is not defined"
     ]
    }
   ],
   "source": [
    "predictors = df_c.columns.drop(['SK_ID_CURR', 'TARGET', 'DAYS_BIRTH', 'NEW_EXT_SOURCES_MEAN', 'NEW_SOURCES_PROD'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T11:10:10.680620Z",
     "start_time": "2018-08-09T11:10:10.517Z"
    }
   },
   "outputs": [],
   "source": [
    "# predictors_wr = df_c.columns.drop(['SK_ID_CURR', 'TARGET', 'DAYS_BIRTH']+[col for col in df_c if col.endswith('RATIO_PREV_D')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:41:29.223987Z",
     "start_time": "2018-08-09T10:41:29.212480Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "326"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print 'total features - {}'.format(len(predictors))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 398,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-06T08:40:26.028399Z",
     "start_time": "2018-08-06T08:40:26.021231Z"
    }
   },
   "outputs": [],
   "source": [
    "# lgb = lightgbm.LGBMClassifier(n_estimators=300, learning_rate=0.05, max_depth=6)\n",
    "\n",
    "# lgb = lightgbm.LGBMClassifier(n_estimators=1000, learning_rate=0.02, max_depth=7, reg_lambda=15, reg_alpha=10,feature_fraction=0.6)\n",
    "\n",
    "# lgb = lightgbm.LGBMClassifier(n_estimators=2500, learning_rate=0.015, max_depth=8, reg_lambda=20, reg_alpha=10, feature_fraction=0.6)\n",
    "\n",
    "# # curr best\n",
    "# lgb = lightgbm.LGBMClassifier(n_estimators=2500, learning_rate=0.02, max_depth=8, reg_lambda=20, reg_alpha=10, feature_fraction=0.6, subsample=0.9, random_state=2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T10:41:47.760427Z",
     "start_time": "2018-08-09T10:41:47.754179Z"
    }
   },
   "outputs": [],
   "source": [
    "# curr best 2\n",
    "lgb = lightgbm.LGBMClassifier(n_estimators=3000, learning_rate=0.01, max_depth=8, reg_lambda=25, reg_alpha=10, feature_fraction=0.6, subsample=0.9, random_state=2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T07:44:12.217435Z",
     "start_time": "2018-08-09T07:44:12.209270Z"
    }
   },
   "outputs": [],
   "source": [
    "# lgb = lightgbm.LGBMClassifier(n_estimators=3000, learning_rate=0.015, max_depth=8, reg_lambda=25, reg_alpha=10, feature_fraction=0.6, subsample=0.7, random_state=2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T05:25:51.934853Z",
     "start_time": "2018-08-09T05:25:51.910118Z"
    }
   },
   "outputs": [],
   "source": [
    "def run_cv(clf, df_temp, predictors_, target, metric_func, predict_proba=False, shuffle_=False, max_run=1):\n",
    "    kf = model_selection.KFold(shuffle=shuffle_, random_state=2018)\n",
    "    all_scores = []\n",
    "    all_scores_tr = []\n",
    "    count = 1\n",
    "    for train_i, cv_i in kf.split(df_temp):\n",
    "        df_tr = df_temp.loc[train_i]\n",
    "#         org_type = df_tr.groupby('ORGANIZATION_TYPE')['TARGET'].mean()\n",
    "        \n",
    "#         df_tr = df_tr.drop(df_tr.loc[df_tr['DAYS_EMPLOYED']>0].index).reset_index(drop=True)\n",
    "        df_cv = df_temp.loc[cv_i]\n",
    "#         df_cv['ORGANIZATION_TYPE'] = df_cv['ORGANIZATION_TYPE'].map(org_type)\n",
    "#         print df_cv['ORGANIZATION_TYPE'].head()\n",
    "        clf.fit(df_tr[predictors_], df_tr[target])\n",
    "#         df_temp.loc[cv_i, 'MODEL_0'] = clf.predict_proba(df_cv[predictors_])[:,1]\n",
    "        if predict_proba:\n",
    "            preds = clf.predict_proba(df_cv[predictors_])[:,1]\n",
    "            preds_tr = clf.predict_proba(df_tr[predictors_])[:,1]\n",
    "        else:\n",
    "            preds = clf.predict(df_cv[predictors_])\n",
    "            preds = clf.predict(df_tr[predictors_])\n",
    "        score = metric_func(df_cv[target], preds)\n",
    "        score_tr = metric_func(df_tr[target], preds_tr)\n",
    "        print 'Test score - ', score\n",
    "        print 'Train score - ', score_tr, '\\n'\n",
    "        all_scores.append(score)\n",
    "        all_scores_tr.append(score_tr)\n",
    "        if (count==max_run) and (max_run>0):\n",
    "            break\n",
    "        count+=1\n",
    "    print 'Mean test score =', np.mean(all_scores)\n",
    "    print 'Mean train score =', np.mean(all_scores_tr)\n",
    "    return clf, df_temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T08:10:56.083941Z",
     "start_time": "2018-08-09T08:03:12.263753Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Test score -  0.7903906843211165\n",
      "Train score -  0.8648447609859049 \n",
      "\n",
      "Mean test score = 0.7903906843211165\n",
      "Mean train score = 0.8648447609859049\n"
     ]
    }
   ],
   "source": [
    "clf, df_c = run_cv(lgb, df_c, predictors, 'TARGET', metrics.roc_auc_score, predict_proba=True, max_run=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T08:41:51.407954Z",
     "start_time": "2018-08-09T08:41:51.388920Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "f_imp = pd.Series(clf.booster_.feature_importance(importance_type='gain'), predictors).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print 'Feature importance...'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-08-09T07:12:08.475532Z",
     "start_time": "2018-08-09T07:12:08.447094Z"
    }
   },
   "outputs": [],
   "source": [
    "print f_imp[:170]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}