notebooks/eda-credit_card.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from tqdm import tqdm_notebook as tqdm\n",
    "from sklearn.externals import joblib\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "\n",
    "DIR = '/mnt/ml-team/minerva/open-solutions/home-credit'\n",
    "description = pd.read_csv(os.path.join(DIR,'data/HomeCredit_columns_description.csv'),encoding = 'latin1')\n",
    "application = pd.read_csv(os.path.join(DIR, 'files/unzipped_data/application_train.csv'))\n",
    "credit_card = pd.read_csv(os.path.join(DIR, 'files/unzipped_data/credit_card_balance.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_card.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing\n",
    "## Solution 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering\n",
    "## Solution 3\n",
    "### Hand crafted features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### First build helper columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_card['number_of_instalments'] = credit_card.groupby(\n",
    "    by=['SK_ID_CURR', 'SK_ID_PREV'])['CNT_INSTALMENT_MATURE_CUM'].agg('max').reset_index()[\n",
    "    'CNT_INSTALMENT_MATURE_CUM']\n",
    "\n",
    "credit_card['credit_card_max_loading_of_credit_limit'] = credit_card.groupby(\n",
    "    by=['SK_ID_CURR', 'SK_ID_PREV', 'AMT_CREDIT_LIMIT_ACTUAL']).apply(\n",
    "    lambda x: x.AMT_BALANCE.max() / x.AMT_CREDIT_LIMIT_ACTUAL.max()).reset_index()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_card.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "description[description['Row'] == 'DAYS_CREDIT'].Description.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "features = pd.DataFrame({'SK_ID_CURR':credit_card['SK_ID_CURR'].unique()})\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = credit_card.groupby(by=['SK_ID_CURR'])['SK_ID_PREV'].agg('nunique').reset_index()\n",
    "group_object.rename(index=str, columns={'SK_ID_PREV': 'credit_card_number_of_loans'},inplace=True)\n",
    "\n",
    "features = features.merge(group_object, on=['SK_ID_CURR'], how='left')\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "features['credit_card_number_of_loans'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Note\n",
    "It is worth exploring `credit_card_number_of_loans>1` binary version of this variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object= credit_card.groupby(by=['SK_ID_CURR'])['number_of_instalments'].sum().reset_index()\n",
    "group_object.rename(index=str, columns={'number_of_instalments': 'credit_card_total_instalments'},inplace=True)\n",
    "\n",
    "features = features.merge(group_object, on=['SK_ID_CURR'], how='left')\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "features['credit_card_total_instalments'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.distplot(features['credit_card_total_instalments'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Note\n",
    "* Maybe adding a is zero variabl maxes sens"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "features['credit_card_installments_per_loan'] = (\n",
    "    features['credit_card_total_instalments'] / features['credit_card_number_of_loans'])\n",
    "    \n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = credit_card.groupby(by=['SK_ID_CURR'])['credit_card_max_loading_of_credit_limit'].agg('mean').reset_index()\n",
    "group_object.rename(index=str, columns={'credit_card_max_loading_of_credit_limit': 'credit_card_avg_loading_of_credit_limit'},inplace=True)\n",
    "\n",
    "features = features.merge(group_object, on=['SK_ID_CURR'], how='left')\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = credit_card.groupby(\n",
    "    by=['SK_ID_CURR'])['SK_DPD'].agg('mean').reset_index()\n",
    "group_object.rename(index=str, columns={'SK_DPD': 'credit_card_average_of_days_past_due'},inplace=True)\n",
    "\n",
    "features = features.merge(group_object, on=['SK_ID_CURR'], how='left')\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = credit_card.groupby(by=['SK_ID_CURR'])['AMT_DRAWINGS_ATM_CURRENT'].agg('sum').reset_index()\n",
    "group_object.rename(index=str, columns={'AMT_DRAWINGS_ATM_CURRENT': 'credit_card_drawings_atm'},inplace=True)\n",
    "\n",
    "features = features.merge(group_object, on=['SK_ID_CURR'], how='left')\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = credit_card.groupby(by=['SK_ID_CURR'])['AMT_DRAWINGS_CURRENT'].agg('sum').reset_index()\n",
    "group_object.rename(index=str, columns={'AMT_DRAWINGS_CURRENT': 'credit_card_drawings_total'},inplace=True)\n",
    "\n",
    "features = features.merge(group_object, on=['SK_ID_CURR'], how='left')\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "features['credit_card_cash_card_ratio'] = features['credit_card_drawings_atm'] / features['credit_card_drawings_total']\n",
    "\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_ONE = features[features['SK_ID_CURR']==215354]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_ONE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "application = application.merge(features,\n",
    "                                left_on=['SK_ID_CURR'],\n",
    "                                right_on=['SK_ID_CURR'],\n",
    "                                how='left',\n",
    "                                validate='one_to_one')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engineered_numerical_columns = list(features.columns)\n",
    "engineered_numerical_columns.remove('SK_ID_CURR')\n",
    "credit_eng = application[engineered_numerical_columns + ['TARGET']]\n",
    "credit_eng_corr = abs(credit_eng.corr())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_eng_corr.sort_values('TARGET', ascending=False)['TARGET']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.heatmap(credit_eng_corr, \n",
    "            xticklabels=credit_eng_corr.columns,\n",
    "            yticklabels=credit_eng_corr.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREDIT_CARD_BALANCE_AGGREGATION_RECIPIES = []\n",
    "for agg in ['mean', 'min', 'max', 'sum', 'var']:\n",
    "    for select in ['AMT_BALANCE',\n",
    "                   'AMT_CREDIT_LIMIT_ACTUAL',\n",
    "                   'AMT_DRAWINGS_ATM_CURRENT',\n",
    "                   'AMT_DRAWINGS_CURRENT',\n",
    "                   'AMT_DRAWINGS_OTHER_CURRENT',\n",
    "                   'AMT_DRAWINGS_POS_CURRENT',\n",
    "                   'AMT_PAYMENT_CURRENT',\n",
    "                   'CNT_DRAWINGS_ATM_CURRENT',\n",
    "                   'CNT_DRAWINGS_CURRENT',\n",
    "                   'CNT_DRAWINGS_OTHER_CURRENT',\n",
    "                   'CNT_INSTALMENT_MATURE_CUM',\n",
    "                   'MONTHS_BALANCE',\n",
    "                   'SK_DPD',\n",
    "                   'SK_DPD_DEF'\n",
    "                   ]:\n",
    "        CREDIT_CARD_BALANCE_AGGREGATION_RECIPIES.append((select, agg))\n",
    "CREDIT_CARD_BALANCE_AGGREGATION_RECIPIES = [(['SK_ID_CURR'], CREDIT_CARD_BALANCE_AGGREGATION_RECIPIES)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby_aggregate_names = []\n",
    "for groupby_cols, specs in tqdm(CREDIT_CARD_BALANCE_AGGREGATION_RECIPIES):\n",
    "    group_object = credit.groupby(groupby_cols)\n",
    "    for select, agg in tqdm(specs):\n",
    "        groupby_aggregate_name = '{}_{}_{}'.format('_'.join(groupby_cols), agg, select)\n",
    "        application = application.merge(group_object[select]\n",
    "                              .agg(agg)\n",
    "                              .reset_index()\n",
    "                              .rename(index=str,\n",
    "                                      columns={select: groupby_aggregate_name})\n",
    "                              [groupby_cols + [groupby_aggregate_name]],\n",
    "                              on=groupby_cols,\n",
    "                              how='left')\n",
    "        groupby_aggregate_names.append(groupby_aggregate_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "application.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "application_agg = application[groupby_aggregate_names + ['TARGET']]\n",
    "application_agg_corr = abs(application_agg.corr())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "application_agg_corr.sort_values('TARGET', ascending=False)['TARGET']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Solution 4\n",
    "## Hand Crafted Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group = bureau[bureau['bureau_credit_enddate_binary'] == 1].groupby(\n",
    "#     by=['SK_ID_CURR']).apply(\n",
    "#     lambda x: x.sort_values(['DAYS_CREDIT_ENDDATE'], ascending=True)).reset_index(drop=True)\n",
    "# group['bureau_days_enddate_diff'] = group.groupby(by=['SK_ID_CURR'])['DAYS_CREDIT_ENDDATE'].diff()\n",
    "# group['bureau_days_enddate_diff'] = group['bureau_days_enddate_diff'].fillna(0).astype('uint32')\n",
    "\n",
    "# bureau = bureau.merge(group[['bureau_days_enddate_diff', 'SK_ID_BUREAU']], on=['SK_ID_BUREAU'], how='left')\n",
    "# bureau['bureau_average_enddate_future'] = bureau.groupby(\n",
    "#     by=['SK_ID_CURR'])['bureau_days_enddate_diff'].agg('mean').reset_index()['bureau_days_enddate_diff']\n",
    "\n",
    "# bureau['bureau_days_credit_diff'] = bureau.groupby(\n",
    "#     by=['SK_ID_CURR']).apply(\n",
    "#     lambda x: x.sort_values(['DAYS_CREDIT'], ascending=False)).reset_index(drop=True)['DAYS_CREDIT']\n",
    "# bureau['bureau_days_credit_diff'] *= -1\n",
    "# bureau['bureau_days_credit_diff'] = bureau.groupby(by=['SK_ID_CURR'])['bureau_days_credit_diff'].diff()\n",
    "# bureau['bureau_days_credit_diff'] = bureau['bureau_days_credit_diff'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "cpu py3",
   "language": "python",
   "name": "cpu_py3"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}