notebooks/eda-bureau.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",
    "bureau = pd.read_csv(os.path.join(DIR, 'files/unzipped_data/bureau.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureau.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing\n",
    "## Solution 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(bureau['AMT_CREDIT_SUM'] == 0).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This shows that imputing with nan with 0 is probably a bad idea"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering\n",
    "## Solution 3\n",
    "### Hand crafted features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureau[bureau['SK_ID_CURR']==215354]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### First build helper columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureau['bureau_credit_active_binary'] = (bureau['CREDIT_ACTIVE'] != 'Closed').astype(int)\n",
    "bureau['bureau_credit_enddate_binary'] = (bureau['DAYS_CREDIT_ENDDATE'] > 0).astype(int)\n",
    "\n",
    "groupby_SK_ID_CURR = bureau.groupby(by=['SK_ID_CURR'])"
   ]
  },
  {
   "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':bureau['SK_ID_CURR'].unique()})\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = groupby_SK_ID_CURR['DAYS_CREDIT'].agg('count').reset_index()\n",
    "group_object.rename(index=str, columns={'DAYS_CREDIT': 'bureau_number_of_past_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": [
    "group_object = groupby_SK_ID_CURR['CREDIT_TYPE'].agg('nunique').reset_index()\n",
    "group_object.rename(index=str, columns={'CREDIT_TYPE': 'bureau_number_of_loan_types'},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['bureau_average_of_past_loans_per_type'] = \\\n",
    "    features['bureau_number_of_past_loans'] / features['bureau_number_of_loan_types']\n",
    "    \n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = groupby_SK_ID_CURR['bureau_credit_active_binary'].agg('mean').reset_index()\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 = groupby_SK_ID_CURR['AMT_CREDIT_SUM_DEBT'].agg('sum').reset_index()\n",
    "group_object.rename(index=str, columns={'AMT_CREDIT_SUM_DEBT': 'bureau_total_customer_debt'},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 = groupby_SK_ID_CURR['AMT_CREDIT_SUM'].agg('sum').reset_index()\n",
    "group_object.rename(index=str, columns={'AMT_CREDIT_SUM': 'bureau_total_customer_credit'},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['bureau_debt_credit_ratio'] = \\\n",
    "    features['bureau_total_customer_debt'] / features['bureau_total_customer_credit']\n",
    "    \n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = groupby_SK_ID_CURR['AMT_CREDIT_SUM_OVERDUE'].agg('sum').reset_index()\n",
    "group_object.rename(index=str, columns={'AMT_CREDIT_SUM_OVERDUE': 'bureau_total_customer_overdue'},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['bureau_overdue_debt_ratio'] = \\\n",
    "    features['bureau_total_customer_overdue'] / features['bureau_total_customer_debt']\n",
    "    \n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_object = groupby_SK_ID_CURR['CNT_CREDIT_PROLONG'].agg('sum').reset_index()\n",
    "group_object.rename(index=str, columns={'CNT_CREDIT_PROLONG': 'bureau_average_creditdays_prolonged'},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 = groupby_SK_ID_CURR['bureau_credit_enddate_binary'].agg('mean').reset_index()\n",
    "group_object.rename(index=str, columns={'bureau_credit_enddate_binary': 'bureau_credit_enddate_percentage'},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": [
    "bureau_ONE = features[features['SK_ID_CURR']==215354]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureau_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",
    "bureau_eng = application[engineered_numerical_columns + ['TARGET']]\n",
    "bureau_eng_corr = abs(bureau_eng.corr())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureau_eng_corr.sort_values('TARGET', ascending=False)['TARGET']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.heatmap(bureau_eng_corr, \n",
    "            xticklabels=bureau_eng_corr.columns,\n",
    "            yticklabels=bureau_eng_corr.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "BUREAU_AGGREGATION_RECIPIES = [('CREDIT_TYPE', 'count'),\n",
    "                               ('CREDIT_ACTIVE', 'size')\n",
    "                               ]\n",
    "for agg in ['mean', 'min', 'max', 'sum', 'var']:\n",
    "    for select in ['AMT_ANNUITY',\n",
    "                   'AMT_CREDIT_SUM',\n",
    "                   'AMT_CREDIT_SUM_DEBT',\n",
    "                   'AMT_CREDIT_SUM_LIMIT',\n",
    "                   'AMT_CREDIT_SUM_OVERDUE',\n",
    "                   'AMT_CREDIT_MAX_OVERDUE',\n",
    "                   'CNT_CREDIT_PROLONG',\n",
    "                   'CREDIT_DAY_OVERDUE',\n",
    "                   'DAYS_CREDIT',\n",
    "                   'DAYS_CREDIT_ENDDATE',\n",
    "                   'DAYS_CREDIT_UPDATE'\n",
    "                   ]:\n",
    "        BUREAU_AGGREGATION_RECIPIES.append((select, agg))\n",
    "BUREAU_AGGREGATION_RECIPIES = [(['SK_ID_CURR'], BUREAU_AGGREGATION_RECIPIES)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby_aggregate_names = []\n",
    "for groupby_cols, specs in tqdm(BUREAU_AGGREGATION_RECIPIES):\n",
    "    group_object = bureau.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
}