project-competition/pre_processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# necessary libraries for pre-processing\n",
    "import utils\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import math\n",
    "import os\n",
    "\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import statsmodels.api as sm\n",
    "from sklearn import preprocessing\n",
    "from sklearn import metrics\n",
    "from sklearn.model_selection import StratifiedKFold\n",
    "\n",
    "from sklearn.feature_selection import SelectFromModel\n",
    "from sklearn.feature_selection import RFECV\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', 200)\n",
    "\n",
    "SEED = 42"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Statistical Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_col_max(df, col):\n",
    "    '''Get the maximum value of a given column'''\n",
    "    return df[col].max()\n",
    "\n",
    "def get_col_min(df, col):\n",
    "    '''Get the minimum value of a given column'''\n",
    "    return df[col].min()\n",
    "\n",
    "def get_col_count(df, col):\n",
    "    '''Get the number of elements of a given column'''\n",
    "    return df[col].count()\n",
    "\n",
    "def get_col_avg(df, col):\n",
    "    '''Get the average value of a given column'''\n",
    "    return df[col].mean()\n",
    "\n",
    "def get_col_std(df, col):\n",
    "    '''Get the standard deviation value of a given column'''\n",
    "    return df[col].std()\n",
    "\n",
    "def get_first(df):\n",
    "    '''Get the first entry of a dataframe'''\n",
    "    return df.iloc[0]\n",
    "\n",
    "def get_last(df):\n",
    "    '''Get the last entry of a dataframe'''\n",
    "    return df.iloc[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Useful functions for preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_date(df, column, date_format='%y%m%d'):\n",
    "    '''Convert the given column containg dates in the given format\n",
    "    to the standard date format and type'''\n",
    "    copy_df = df.copy()\n",
    "    copy_df[column] = pd.to_datetime(copy_df[column], format=date_format)\n",
    "\n",
    "    return copy_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def encode_column(df, column):\n",
    "    '''Encode the given column of the given dataframe.'''\n",
    "    copy_df = df.copy()\n",
    "\n",
    "    le = preprocessing.LabelEncoder()\n",
    "    le.fit(df[column].unique())\n",
    "    copy_df[column] = le.transform(copy_df[column])\n",
    "    \n",
    "    return copy_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_df_correlation(df, size=(11, 9)):\n",
    "    '''Get the correlation between the dataframe features'''\n",
    "    # Compute the correlation matrix\n",
    "    corr = df.corr()\n",
    "\n",
    "    # Generate a mask for the upper triangle\n",
    "    mask = np.zeros_like(corr, dtype=np.bool)\n",
    "    mask[np.triu_indices_from(mask)] = True\n",
    "    \n",
    "    # Set up the matplotlib figure\n",
    "    plt.subplots(figsize=size)\n",
    "\n",
    "    # Generate a custom diverging colormap\n",
    "    cmap = sns.diverging_palette(220, 10, as_cmap=True)\n",
    "\n",
    "    # Draw the heatmap with the mask and correct aspect ratio\n",
    "    ax = sns.heatmap(corr, mask=mask, cmap=cmap, center=0,\n",
    "                     square=True, linewidths=.1, cbar_kws={\"shrink\": .5})\n",
    "    \n",
    "    y_lim = ax.get_ylim();\n",
    "    ax.set_ylim(np.ceil(y_lim[0]), np.floor(y_lim[1]))\n",
    "\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def column_density_plot(df, col):\n",
    "    '''Get a density plot for the given column in the given dataframe.\n",
    "    Useful for outlier detection'''\n",
    "    sns.kdeplot(df[col], shade=True)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def column_bar_plot(df, col):\n",
    "    '''Present the given feature of the given df as a barplot,\n",
    "    using count as the y value'''\n",
    "    col_count = '%s_count' % col\n",
    "    agg_df = df.groupby([col])\\\n",
    "               .agg({\n",
    "                   col: ['count']\n",
    "               })\\\n",
    "               .reset_index()\n",
    "    agg_df.columns = [col, col_count]\n",
    "    sns.barplot(x=agg_df[col], y=agg_df[col_count], palette=\"rocket\")\n",
    "    # ax1.axhline(0, color=\"k\", clip_on=False)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def scatterplot_two_cols(df, col1, col2):\n",
    "    '''Get a scatterplot for the given two columns'''\n",
    "    # Set up the matplotlib figure\n",
    "    plt.subplots(figsize=(11, 9))\n",
    "    \n",
    "    # Generate a custom diverging colormap\n",
    "    cmap = sns.diverging_palette(220, 10, as_cmap=True)\n",
    "\n",
    "    sns.scatterplot(data=df, x=col1, y=col2,\n",
    "                    hue='status',palette=cmap, sizes=(47,47))\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bubbleplot_three_cols(df, col1, col2, col3):\n",
    "    '''Bubble plot the given columns.\n",
    "    Column1 is X. Column2 is Y. Column3 is hue and size'''\n",
    "    cmap = sns.diverging_palette(220, 10, as_cmap=True)\n",
    "    sns.scatterplot(data=df, x=col1, y=col2,\n",
    "                    hue=col3, size=col3,\n",
    "                    palette=cmap, sizes=(50, 300))\n",
    "\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_features_histogram(df, figsize_val = (20, 25)):\n",
    "    '''Get a histogram relating the features of the given dataframe'''\n",
    "    fig = plt.figure(figsize = figsize_val)\n",
    "    loan = df[df.status==1]\n",
    "    do_not_loan = df[df.status==-1]\n",
    "    j = 0\n",
    "\n",
    "    for i in range(len(df.columns)):\n",
    "        plt.subplot(math.ceil(len(df.columns) / 3), 3, j+1)\n",
    "        j += 1\n",
    "        sns.distplot(loan.iloc[:, i], color='g', label = 'loan',\n",
    "                    kde = False if len(loan.iloc[:, i].unique()) is 1 else True)\n",
    "        sns.distplot(do_not_loan.iloc[:, i], color='r', label = 'no loan',\n",
    "                    kde = False if len(do_not_loan.iloc[:, i].unique()) is 1 else True)\n",
    "        plt.legend(loc='best')\n",
    "\n",
    "    fig.suptitle('Feature Analysis')\n",
    "    fig.tight_layout()\n",
    "    fig.subplots_adjust(top=0.95)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imputation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_null_summary(dataset):\n",
    "    '''Get a null summary display'''\n",
    "    display(dataset.isnull().mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_nulls(dataset, threshold=0.7):\n",
    "    '''Clean nulls from the given table.\n",
    "    If the nulls in a column are higher than the given threshold the entire column is deleted.\n",
    "    If the nulls in a row are higher than the row, the row is also deleted.\n",
    "    The threshold is a value between 0 and 1'''\n",
    "    #Dropping columns with missing value rate higher than threshold\n",
    "    dataset = dataset[dataset.columns[dataset.isnull().mean() < threshold]]\n",
    "\n",
    "    #Dropping rows with missing value rate higher than threshold\n",
    "    dataset = dataset.loc[dataset.isnull().mean(axis=1) < threshold]\n",
    "    \n",
    "    return dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def numerical_imputation(dataset, replacer=None):\n",
    "    '''When null values exist, set them using the median of the colum,\n",
    "    or a replacer, if one was given'''\n",
    "    dataset = dataset.fillna(replacer if replacer is not None else dataset.median())\n",
    "\n",
    "    return dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def categorical_imputation(dataset, column_name, replacer=None):\n",
    "    '''Replace the inexistent values of the given column with the given replacer.\n",
    "    If None replacer was ginve, use the column maximum value'''\n",
    "    #Max fill function for categorical columns\n",
    "    dataset[column_name].fillna(replacer if replacer is not None else \\\n",
    "                                dataset[column_name].value_counts()\n",
    "                                                    .idxmax(),\n",
    "                                inplace=True)\n",
    "    \n",
    "    return dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling Outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_to_drop_std(dataset, column, mult_factor=3):\n",
    "    '''Display the rows that will be dropped using the std approach'''\n",
    "    upper_lim = dataset[column].mean() + dataset[column].std() * mult_factor\n",
    "    lower_lim = dataset[column].mean() - dataset[column].std() * mult_factor\n",
    "\n",
    "    display(dataset[(dataset[column] >= upper_lim) & (dataset[column] <= lower_lim)])\n",
    "\n",
    "def drop_outliers_std(dataset, column, mult_factor=3):\n",
    "    '''Drop the outlier rows with standard deviation'''\n",
    "    upper_lim = dataset[column].mean() + dataset[column].std() * mult_factor\n",
    "    lower_lim = dataset[column].mean() - dataset[column].std() * mult_factor\n",
    "\n",
    "    return dataset[(dataset[column] < upper_lim) & (dataset[column] > lower_lim)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_to_drop_percentile(dataset, column):\n",
    "    '''Display the rows that will be dropped with Percentiles approach'''\n",
    "    upper_lim = dataset[column].quantile(.95)\n",
    "    lower_lim = dataset[column].quantile(.05)\n",
    "\n",
    "    display(dataset[(dataset[column] >= upper_lim) & (dataset[column] <= lower_lim)])\n",
    "\n",
    "def drop_outliers_percentile(dataset, column):\n",
    "    '''Drop the outlier rows with Percentiles approach'''\n",
    "    upper_lim = dataset[column].quantile(.95)\n",
    "    lower_lim = dataset[column].quantile(.05)\n",
    "\n",
    "    data = dataset[(dataset[column] < upper_lim) & (dataset[column] > lower_lim)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Log Transform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def log_transform(series):\n",
    "    '''Get a log_transformation of the given series'''\n",
    "    min_val = series.min()\n",
    "    return (series - min_val + 1).transform(np.log)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature selection algorithms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def auc_scorer(y_true, y_pred):\n",
    "    '''Scorer of Area Under Curve value'''\n",
    "    fpr, tpr, _ = metrics.roc_curve(y_true, y_pred)\n",
    "    return metrics.auc(fpr, tpr)\n",
    "\n",
    "def extract_X_y(df, y_col_name='status'):\n",
    "    '''Extract the X and Y subsets from the given dataframe'''\n",
    "    y_col = df.columns.get_loc(y_col_name)\n",
    "    return (df.iloc[:, 0:y_col],\n",
    "            df.iloc[:, [y_col]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_features = []\n",
    "\n",
    "def select_features_FF(origin_df, corr_threshold= 0.15, debug=False):\n",
    "\n",
    "    # Only run in train\n",
    "    if len(filter_features) is 0:\n",
    "        status_corr = origin_df.corr().tail(1).drop(['status'], axis=1)\n",
    "        if debug:\n",
    "            print(' ::: Filter Feature Selection Output :::')\n",
    "            print(' > Correlation between status and other features')\n",
    "            display(status_corr)\n",
    "\n",
    "        status_corr = status_corr.loc[:, (abs(status_corr) > corr_threshold).any()]\n",
    "\n",
    "        if debug:\n",
    "            print('The chosen features whose correlatino value is above the threshold:')\n",
    "            display(status_corr)\n",
    "\n",
    "        for col in status_corr:\n",
    "            filter_features.append(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "be_features = []\n",
    "\n",
    "def select_features_BE(origin_df, pthreshold=0.02, debug=False):\n",
    "    '''Select features using Backward Elimination,\n",
    "    on the given dataframe using the given p threshold'''\n",
    "\n",
    "    # Only run in train\n",
    "    if len(be_features) is 0:\n",
    "        df = origin_df.drop(['loan_id'], axis=1)\n",
    "        X, y = extract_X_y(df)\n",
    "        X_1 = sm.add_constant(X)\n",
    "        \n",
    "        # Applying backward elimination\n",
    "        cols = list(X.columns)\n",
    "        pmax = 1\n",
    "        while (len(cols) > 0):\n",
    "            p= []\n",
    "            X_1 = X[cols]\n",
    "            X_1 = sm.add_constant(X_1)\n",
    "            model = sm.OLS(y, X_1).fit()\n",
    "            p = pd.Series(model.pvalues.values[1:], index = cols)      \n",
    "            pmax = max(p)\n",
    "            feature_with_p_max = p.idxmax()\n",
    "            if(pmax > pthreshold):\n",
    "                cols.remove(feature_with_p_max)\n",
    "            else:\n",
    "                break\n",
    "\n",
    "        for col in cols:\n",
    "            be_features.append(col)\n",
    "\n",
    "        if debug:\n",
    "            print(' ::: BE Feature Selection Output :::')\n",
    "            print(' Selected features:')\n",
    "            display(be_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "rfe_features = []\n",
    "\n",
    "def select_features_RFE(origin_df, debug=False):\n",
    "    '''Select features using Recursive Feature\n",
    "    Elimination on the given dataframe'''\n",
    "    \n",
    "    # Only run on train\n",
    "    if len(rfe_features) is 0:\n",
    "        df = origin_df.drop(['loan_id'], axis=1)\n",
    "        X, y = extract_X_y(df)\n",
    "        model=DecisionTreeClassifier(random_state=SEED,\n",
    "                                     criterion='entropy',\n",
    "                                     class_weight='balanced',\n",
    "                                     min_impurity_split=0.23,\n",
    "                                     min_samples_leaf=4,\n",
    "                                     min_samples_split=4,\n",
    "                                     max_depth=6,\n",
    "                                     splitter='best')\n",
    "\n",
    "        rfecv = RFECV(estimator=model,\n",
    "                      step=1,\n",
    "                      cv=StratifiedKFold(5),\n",
    "                      scoring=metrics.make_scorer(auc_scorer,\n",
    "                                                  greater_is_better=True))\n",
    "        rfecv.fit(X, y)\n",
    "        \n",
    "        for col in  X.columns[(rfecv.support_)]:\n",
    "            rfe_features.append(col)\n",
    "\n",
    "        if debug:\n",
    "            print(' ::: RFE Output :::')\n",
    "            print(\"Optimal number of features : %d\" % rfecv.n_features_)\n",
    "\n",
    "            # Plot number of features VS. cross-validation scores\n",
    "            plt.figure()\n",
    "            plt.xlabel(\"Number of features selected\")\n",
    "            plt.ylabel(\"Cross validation score (AUC score)\")\n",
    "            plt.plot(range(1, len(rfecv.grid_scores_) + 1), rfecv.grid_scores_)\n",
    "            plt.show()\n",
    "            \n",
    "            print(' Selected features:')\n",
    "            display(rfe_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "rf_features = []\n",
    "\n",
    "def select_features_RF(origin_df, debug=False):\n",
    "    '''Select features from the given dataframe using\n",
    "    Random Forest algorithm'''\n",
    "\n",
    "    # Only run on train\n",
    "    if len(rf_features) is 0:\n",
    "        df = origin_df.drop(['loan_id'], axis=1)\n",
    "        X, y = extract_X_y(df)\n",
    "        cv = StratifiedKFold(n_splits=5, random_state=SEED, shuffle=True)\n",
    "\n",
    "        for train_index, test_index in cv.split(X, y):\n",
    "            X_train, X_test = X.iloc[train_index], X.iloc[test_index]\n",
    "            y_train, y_test = y.iloc[train_index], y.iloc[test_index]\n",
    "\n",
    "            sel = SelectFromModel(RandomForestClassifier(n_estimators = 8,\n",
    "                                                         class_weight = 'balanced',\n",
    "                                                         criterion = 'entropy',\n",
    "                                                         max_depth = 2,\n",
    "                                                         min_samples_leaf = 2,\n",
    "                                                         min_samples_split = 2,\n",
    "                                                         min_impurity_split = 0.23,\n",
    "                                                         random_state=SEED))\n",
    "            sel.fit(X_train, y_train)\n",
    "\n",
    "            # Save selected columns\n",
    "            for col in X_train.columns[(sel.get_support())]:\n",
    "                if col not in rf_features:\n",
    "                    rf_features.append(col)\n",
    "\n",
    "        if debug:\n",
    "            print(' ::: RF Feature Selection Output :::')\n",
    "            print(' Selected features:')\n",
    "            display(rf_features)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering\n",
    "\n",
    "### Load all tables to memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " ::: Tables Scheme :::\n",
      "\n",
      "\n",
      "\t LOAN TABLE\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/edgarcarneiro/Documents/University/feup-ecac/project-competition/env/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3249: DtypeWarning: Columns (8) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_id</th>\n",
       "      <th>account_id</th>\n",
       "      <th>date</th>\n",
       "      <th>amount</th>\n",
       "      <th>duration</th>\n",
       "      <th>payments</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5314</td>\n",
       "      <td>1787</td>\n",
       "      <td>930705</td>\n",
       "      <td>96396</td>\n",
       "      <td>12</td>\n",
       "      <td>8033</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5316</td>\n",
       "      <td>1801</td>\n",
       "      <td>930711</td>\n",
       "      <td>165960</td>\n",
       "      <td>36</td>\n",
       "      <td>4610</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>6863</td>\n",
       "      <td>9188</td>\n",
       "      <td>930728</td>\n",
       "      <td>127080</td>\n",
       "      <td>60</td>\n",
       "      <td>2118</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5325</td>\n",
       "      <td>1843</td>\n",
       "      <td>930803</td>\n",
       "      <td>105804</td>\n",
       "      <td>36</td>\n",
       "      <td>2939</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>7240</td>\n",
       "      <td>11013</td>\n",
       "      <td>930906</td>\n",
       "      <td>274740</td>\n",
       "      <td>60</td>\n",
       "      <td>4579</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_id  account_id    date  amount  duration  payments  status\n",
       "0     5314        1787  930705   96396        12      8033      -1\n",
       "1     5316        1801  930711  165960        36      4610       1\n",
       "2     6863        9188  930728  127080        60      2118       1\n",
       "3     5325        1843  930803  105804        36      2939       1\n",
       "4     7240       11013  930906  274740        60      4579       1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\t ACCOUNT TABLE\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>account_id</th>\n",
       "      <th>district_id</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>576</td>\n",
       "      <td>55</td>\n",
       "      <td>monthly issuance</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>3818</td>\n",
       "      <td>74</td>\n",
       "      <td>monthly issuance</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>704</td>\n",
       "      <td>55</td>\n",
       "      <td>monthly issuance</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2378</td>\n",
       "      <td>16</td>\n",
       "      <td>monthly issuance</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2632</td>\n",
       "      <td>24</td>\n",
       "      <td>monthly issuance</td>\n",
       "      <td>930102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account_id  district_id         frequency    date\n",
       "0         576           55  monthly issuance  930101\n",
       "1        3818           74  monthly issuance  930101\n",
       "2         704           55  monthly issuance  930101\n",
       "3        2378           16  monthly issuance  930101\n",
       "4        2632           24  monthly issuance  930102"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\t DISPOSITION TABLE\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>disp_id</th>\n",
       "      <th>client_id</th>\n",
       "      <th>account_id</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>OWNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>OWNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>DISPONENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>OWNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>DISPONENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   disp_id  client_id  account_id       type\n",
       "0        1          1           1      OWNER\n",
       "1        2          2           2      OWNER\n",
       "2        3          3           2  DISPONENT\n",
       "3        4          4           3      OWNER\n",
       "4        5          5           3  DISPONENT"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\t CREDIT CARD TABLE\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>card_id</th>\n",
       "      <th>disp_id</th>\n",
       "      <th>type</th>\n",
       "      <th>issued</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1005</td>\n",
       "      <td>9285</td>\n",
       "      <td>classic</td>\n",
       "      <td>931107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>104</td>\n",
       "      <td>588</td>\n",
       "      <td>classic</td>\n",
       "      <td>940119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>747</td>\n",
       "      <td>4915</td>\n",
       "      <td>classic</td>\n",
       "      <td>940205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>439</td>\n",
       "      <td>classic</td>\n",
       "      <td>940208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>577</td>\n",
       "      <td>3687</td>\n",
       "      <td>classic</td>\n",
       "      <td>940215</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   card_id  disp_id     type  issued\n",
       "0     1005     9285  classic  931107\n",
       "1      104      588  classic  940119\n",
       "2      747     4915  classic  940205\n",
       "3       70      439  classic  940208\n",
       "4      577     3687  classic  940215"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\t CLIENT TABLE\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>client_id</th>\n",
       "      <th>birth_number</th>\n",
       "      <th>district_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>706213</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>450204</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>406009</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>561201</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>605703</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   client_id  birth_number  district_id\n",
       "0          1        706213           18\n",
       "1          2        450204            1\n",
       "2          3        406009            1\n",
       "3          4        561201            5\n",
       "4          5        605703            5"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\tTRANSACTIONS TABLE\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trans_id</th>\n",
       "      <th>account_id</th>\n",
       "      <th>date</th>\n",
       "      <th>type</th>\n",
       "      <th>operation</th>\n",
       "      <th>amount</th>\n",
       "      <th>balance</th>\n",
       "      <th>k_symbol</th>\n",
       "      <th>bank</th>\n",
       "      <th>account</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1548749</td>\n",
       "      <td>5270</td>\n",
       "      <td>930113</td>\n",
       "      <td>credit</td>\n",
       "      <td>credit in cash</td>\n",
       "      <td>800.0</td>\n",
       "      <td>800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1548750</td>\n",
       "      <td>5270</td>\n",
       "      <td>930114</td>\n",
       "      <td>credit</td>\n",
       "      <td>collection from another bank</td>\n",
       "      <td>44749.0</td>\n",
       "      <td>45549.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IJ</td>\n",
       "      <td>80269753.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3393738</td>\n",
       "      <td>11265</td>\n",
       "      <td>930114</td>\n",
       "      <td>credit</td>\n",
       "      <td>credit in cash</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3122924</td>\n",
       "      <td>10364</td>\n",
       "      <td>930117</td>\n",
       "      <td>credit</td>\n",
       "      <td>credit in cash</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1121963</td>\n",
       "      <td>3834</td>\n",
       "      <td>930119</td>\n",
       "      <td>credit</td>\n",
       "      <td>credit in cash</td>\n",
       "      <td>700.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trans_id  account_id    date    type                     operation  \\\n",
       "0   1548749        5270  930113  credit                credit in cash   \n",
       "1   1548750        5270  930114  credit  collection from another bank   \n",
       "2   3393738       11265  930114  credit                credit in cash   \n",
       "3   3122924       10364  930117  credit                credit in cash   \n",
       "4   1121963        3834  930119  credit                credit in cash   \n",
       "\n",
       "    amount  balance k_symbol bank     account  \n",
       "0    800.0    800.0      NaN  NaN         NaN  \n",
       "1  44749.0  45549.0      NaN   IJ  80269753.0  \n",
       "2   1000.0   1000.0      NaN  NaN         NaN  \n",
       "3   1100.0   1100.0      NaN  NaN         NaN  \n",
       "4    700.0    700.0      NaN  NaN         NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\tDEMOGRAPHIC TABLE\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>region</th>\n",
       "      <th>no. of inhabitants</th>\n",
       "      <th>no. of municipalities with inhabitants &lt; 499</th>\n",
       "      <th>no. of municipalities with inhabitants 500-1999</th>\n",
       "      <th>no. of municipalities with inhabitants 2000-9999</th>\n",
       "      <th>no. of municipalities with inhabitants &gt;10000</th>\n",
       "      <th>no. of cities</th>\n",
       "      <th>ratio of urban inhabitants</th>\n",
       "      <th>average salary</th>\n",
       "      <th>unemploymant rate '95</th>\n",
       "      <th>unemploymant rate '96</th>\n",
       "      <th>no. of enterpreneurs per 1000 inhabitants</th>\n",
       "      <th>no. of commited crimes '95</th>\n",
       "      <th>no. of commited crimes '96</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Hl.m. Praha</td>\n",
       "      <td>Prague</td>\n",
       "      <td>1204953</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>12541</td>\n",
       "      <td>0.29</td>\n",
       "      <td>0.43</td>\n",
       "      <td>167</td>\n",
       "      <td>85677</td>\n",
       "      <td>99107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Benesov</td>\n",
       "      <td>central Bohemia</td>\n",
       "      <td>88884</td>\n",
       "      <td>80</td>\n",
       "      <td>26</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>46.7</td>\n",
       "      <td>8507</td>\n",
       "      <td>1.67</td>\n",
       "      <td>1.85</td>\n",
       "      <td>132</td>\n",
       "      <td>2159</td>\n",
       "      <td>2674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Beroun</td>\n",
       "      <td>central Bohemia</td>\n",
       "      <td>75232</td>\n",
       "      <td>55</td>\n",
       "      <td>26</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>41.7</td>\n",
       "      <td>8980</td>\n",
       "      <td>1.95</td>\n",
       "      <td>2.21</td>\n",
       "      <td>111</td>\n",
       "      <td>2824</td>\n",
       "      <td>2813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Kladno</td>\n",
       "      <td>central Bohemia</td>\n",
       "      <td>149893</td>\n",
       "      <td>63</td>\n",
       "      <td>29</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>67.4</td>\n",
       "      <td>9753</td>\n",
       "      <td>4.64</td>\n",
       "      <td>5.05</td>\n",
       "      <td>109</td>\n",
       "      <td>5244</td>\n",
       "      <td>5892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Kolin</td>\n",
       "      <td>central Bohemia</td>\n",
       "      <td>95616</td>\n",
       "      <td>65</td>\n",
       "      <td>30</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>51.4</td>\n",
       "      <td>9307</td>\n",
       "      <td>3.85</td>\n",
       "      <td>4.43</td>\n",
       "      <td>118</td>\n",
       "      <td>2616</td>\n",
       "      <td>3040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code         name            region  no. of inhabitants  \\\n",
       "0      1  Hl.m. Praha           Prague             1204953   \n",
       "1      2      Benesov  central Bohemia               88884   \n",
       "2      3       Beroun  central Bohemia               75232   \n",
       "3      4       Kladno  central Bohemia              149893   \n",
       "4      5        Kolin  central Bohemia               95616   \n",
       "\n",
       "   no. of municipalities with inhabitants < 499   \\\n",
       "0                                              0   \n",
       "1                                             80   \n",
       "2                                             55   \n",
       "3                                             63   \n",
       "4                                             65   \n",
       "\n",
       "   no. of municipalities with inhabitants 500-1999  \\\n",
       "0                                                0   \n",
       "1                                               26   \n",
       "2                                               26   \n",
       "3                                               29   \n",
       "4                                               30   \n",
       "\n",
       "   no. of municipalities with inhabitants 2000-9999   \\\n",
       "0                                                  0   \n",
       "1                                                  6   \n",
       "2                                                  4   \n",
       "3                                                  6   \n",
       "4                                                  4   \n",
       "\n",
       "   no. of municipalities with inhabitants >10000   no. of cities   \\\n",
       "0                                               1               1   \n",
       "1                                               2               5   \n",
       "2                                               1               5   \n",
       "3                                               2               6   \n",
       "4                                               1               6   \n",
       "\n",
       "   ratio of urban inhabitants   average salary  unemploymant rate '95   \\\n",
       "0                        100.0            12541                   0.29   \n",
       "1                         46.7             8507                   1.67   \n",
       "2                         41.7             8980                   1.95   \n",
       "3                         67.4             9753                   4.64   \n",
       "4                         51.4             9307                   3.85   \n",
       "\n",
       "   unemploymant rate '96   no. of enterpreneurs per 1000 inhabitants   \\\n",
       "0                    0.43                                         167   \n",
       "1                    1.85                                         132   \n",
       "2                    2.21                                         111   \n",
       "3                    5.05                                         109   \n",
       "4                    4.43                                         118   \n",
       "\n",
       "  no. of commited crimes '95   no. of commited crimes '96   \n",
       "0                       85677                        99107  \n",
       "1                        2159                         2674  \n",
       "2                        2824                         2813  \n",
       "3                        5244                         5892  \n",
       "4                        2616                         3040  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Loading the tables both used for train and test\n",
    "account_df = utils.read_csv_to_df('competition_dataset/account.csv', delimiter=';')\n",
    "disp_df = utils.read_csv_to_df('competition_dataset/disp.csv', delimiter=';')\n",
    "client_df = utils.read_csv_to_df('competition_dataset/client.csv', delimiter=';')\n",
    "demogra_df = utils.read_csv_to_df('competition_dataset/district.csv', delimiter=';')\n",
    "\n",
    "# Loading the train tables\n",
    "loan_df = utils.read_csv_to_df('competition_dataset/loan_train.csv', delimiter=';')\n",
    "card_df = utils.read_csv_to_df('competition_dataset/card_train.csv', delimiter=';')\n",
    "trans_df = utils.read_csv_to_df('competition_dataset/trans_train.csv', delimiter=';')\n",
    "\n",
    "# Loading the test tables\n",
    "loan_test_df = utils.read_csv_to_df('competition_dataset/loan_test.csv', delimiter=';')\n",
    "card_test_df = pd.concat([utils.read_csv_to_df('competition_dataset/card_test.csv', delimiter=';'),\n",
    "                          card_df])\n",
    "trans_test_df = pd.concat([utils.read_csv_to_df('competition_dataset/trans_test.csv', delimiter=';'),\n",
    "                            trans_df])\n",
    "\n",
    "print(' ::: Tables Scheme :::')\n",
    "print('\\n\\n\\t LOAN TABLE')\n",
    "display(loan_df.head())\n",
    "print('\\n\\n\\t ACCOUNT TABLE')\n",
    "display(account_df.head())\n",
    "print('\\n\\n\\t DISPOSITION TABLE')\n",
    "display(disp_df.head())\n",
    "print('\\n\\n\\t CREDIT CARD TABLE')\n",
    "display(card_df.head())\n",
    "print('\\n\\n\\t CLIENT TABLE')\n",
    "display(client_df.head())\n",
    "print('\\n\\n\\tTRANSACTIONS TABLE')\n",
    "display(trans_df.head())\n",
    "print('\\n\\n\\tDEMOGRAPHIC TABLE')\n",
    "display(demogra_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Individual pre processement of tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def process_loans(loans_df, debug=False):\n",
    "    '''Pre process the loan table'''\n",
    "    if debug:\n",
    "        print(' > Raw loan table representation')\n",
    "        display(loans_df)\n",
    "        print(' > Raw loan table correlations')\n",
    "        get_df_correlation(loans_df)\n",
    "        \n",
    "    processed_df = convert_date(loans_df, 'date')\n",
    "    \n",
    "    # As there is a correlation between amount, duration & payments\n",
    "    if debug:\n",
    "        print(' > There is naturally a correlation between the amount, duration and payments columns,'+\n",
    "              'since amount = columns * payments')\n",
    "        scatterplot_two_cols(processed_df, 'amount', 'duration')\n",
    "        scatterplot_two_cols(processed_df, 'amount', 'payments')\n",
    "        print(' > Natural correlations, since the larger the amount the larger the monthly payment or the duration.')\n",
    "        \n",
    "        print(' > We can also use this correlation to inquiry if there is bank interest rate:')\n",
    "        processed_df['payment_duration'] = processed_df['duration'] * processed_df['payments']\n",
    "        scatterplot_two_cols(processed_df, 'amount', 'payment_duration')\n",
    "\n",
    "        processed_df = processed_df.drop(['payment_duration'], axis=1)\n",
    "        \n",
    "    # No User has a history of loanning, at least in the train dataset\n",
    "    # Since amount = payments * duration we can conclude that there is no interest rate to the bank\n",
    "        \n",
    "    return processed_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def process_account(accounts_df, debug=False):\n",
    "    '''Pre process the accounts table'''\n",
    "    if debug:\n",
    "        print(' > Raw accounts table representation')\n",
    "        display(accounts_df)\n",
    "        print(' > Raw accounts table correlations')\n",
    "        get_df_correlation(accounts_df)\n",
    "        \n",
    "    processed_account = convert_date(account_df, 'date')\n",
    "    \n",
    "    # Encode categorical column\n",
    "    df = encode_column(processed_account, 'frequency')\n",
    "        \n",
    "    if debug:\n",
    "        print(' > Lets encode the frequency column:')\n",
    "        display(df)\n",
    "        print(' > And the features correlation:')\n",
    "        get_df_correlation(df)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def process_dispostition(disp_df, debug=False):\n",
    "    '''Pre process the dispositions table'''\n",
    "    if debug:\n",
    "        print(' > Raw disposition table representation')\n",
    "        display(disp_df)\n",
    "        print(' > Raw disposition table correlations')\n",
    "        get_df_correlation(disp_df)\n",
    "        \n",
    "    processed_disp = disp_df.copy()\n",
    "    \n",
    "    # Renaming disp attributes\n",
    "    processed_disp.loc[processed_disp[\"type\"]==\"OWNER\",\"type\"] = \"O\"\n",
    "    processed_disp.loc[processed_disp[\"type\"]==\"DISPONENT\",\"type\"] = \"U\"\n",
    "    \n",
    "    # Transform numerical into categorical\n",
    "    df = pd.get_dummies(processed_disp)\n",
    "    \n",
    "    if debug:\n",
    "        print(' > Transformed the categorical type column into numerical respective columns')\n",
    "        display(df)\n",
    "        print(' > Since the 2 categories are depedent, we can remove one of them, for removing redundant data')\n",
    "        get_df_correlation(df)\n",
    "        \n",
    "    # Cannot process further as needs merging with other columns\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def process_card(card_df, debug=False):\n",
    "    '''Pre process the credit card table'''\n",
    "    if debug:\n",
    "        print(' > Raw credit card table representation')\n",
    "        display(card_df)\n",
    "        print(' > Raw credit card table correlations')\n",
    "        get_df_correlation(card_df)\n",
    "\n",
    "    processed_card = convert_date(card_df, 'issued')\n",
    "    if debug:\n",
    "        print(' > The distribution of type of cards per client:')\n",
    "        column_bar_plot(processed_card, 'type')\n",
    "\n",
    "    df = encode_column(processed_card, 'type')\n",
    "    df = df.drop(['card_id'], axis=1)\n",
    "    \n",
    "    if debug:\n",
    "        print(' > Encoded the type column:')\n",
    "        display(df)\n",
    "        \n",
    "    # Cannot process further as needs merging with other columns\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def process_client(client_df, debug=False):\n",
    "    '''Pre process the client table'''\n",
    "    if debug:\n",
    "        print(' > Raw client table representation')\n",
    "        display(client_df)\n",
    "        print(' > Raw client table correlations')\n",
    "        get_df_correlation(client_df)\n",
    "        \n",
    "    processed_df = client_df.copy()\n",
    "        \n",
    "    # Getting year, day, and month+50 if women\n",
    "    processed_df['year'] = 1900 + (processed_df['birth_number'] // 10000)\n",
    "    processed_df['month_gender'] = (processed_df['birth_number'] % 10000) // 100\n",
    "    processed_df['day'] = processed_df['birth_number'] % 100\n",
    "\n",
    "    # Extracting gender and month\n",
    "    processed_df['gender'] = np.where(processed_df['month_gender']>=50, 1, 0)\n",
    "    processed_df['month'] = np.where(processed_df['month_gender']>=50, processed_df['month_gender']-50, processed_df['month_gender'])\n",
    "\n",
    "    # Composing data\n",
    "    processed_df['birth_date'] = processed_df['year'] * 10000 +\\\n",
    "                                 processed_df['month'] * 100 +\\\n",
    "                                 processed_df['day']\n",
    "    df = convert_date(processed_df, 'birth_date', '%Y%m%d')\n",
    "    \n",
    "    # Dropping useless columns\n",
    "    df = df.drop(['birth_number', 'year', 'month_gender', 'month', 'day'], axis=1)\n",
    "    \n",
    "    if debug:\n",
    "        print(' > After extracting the gender from the date we have:')\n",
    "        display(df)\n",
    "        print(' > Notice the gender representation:\\n\\t * 1 if female\\n\\t * 0 if male')\n",
    "        get_df_correlation(df)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def process_transactions(transactions_df, debug=False):\n",
    "    '''Pre process the transactions table'''\n",
    "    if debug:\n",
    "        print(' > Raw transactions table representation')\n",
    "        display(transactions_df)\n",
    "        print(' > Raw transactions table correlations')\n",
    "        get_df_correlation(transactions_df)\n",
    "        \n",
    "    if debug:\n",
    "        print(' > Null evaluation in transactions rows')\n",
    "        get_null_summary(transactions_df)\n",
    "        \n",
    "    # Removing the null columns & processing data\n",
    "    processed_df = clean_nulls(transactions_df)\n",
    "    processed_df = convert_date(processed_df, 'date')\n",
    "    \n",
    "    # Filling null columns with too much nulls\n",
    "    processed_df = categorical_imputation(processed_df, 'k_symbol', ' ')\n",
    "    \n",
    "    if debug:\n",
    "        print(' > Type of operations on Transactions:')\n",
    "        display(processed_df['operation'].unique())\n",
    "        print(' > Type of k_symbol on Transactions:')\n",
    "        display(processed_df['k_symbol'].unique())\n",
    "    \n",
    "    # Renaming 'withdrawal in cash' to 'withdrawal'\n",
    "    processed_df.loc[processed_df[\"type\"]==\"withdrawal in cash\",\"type\"] = \"withdrawal\"\n",
    "\n",
    "    # Renaming of operations\n",
    "    processed_df.loc[processed_df[\"operation\"]==\"credit in cash\", \"operation\"] = \"CC\"\n",
    "    processed_df.loc[processed_df[\"operation\"]==\"collection from another bank\", \"operation\"] = \"CAB\"\n",
    "    processed_df.loc[processed_df[\"operation\"]==\"withdrawal in cash\", \"operation\"] = \"WC\"\n",
    "    processed_df.loc[processed_df[\"operation\"]==\"remittance to another bank\", \"operation\"] = \"RAB\"\n",
    "    processed_df.loc[processed_df[\"operation\"]==\"credit card withdrawal\", \"operation\"] = \"CCW\"\n",
    "    processed_df = categorical_imputation(processed_df, 'operation', 'IC') # Interest credited\n",
    "    \n",
    "    # Making withdrawals amount negatives\n",
    "    processed_df.loc[processed_df[\"type\"]==\"withdrawal\", \"amount\"] *=-1 \n",
    "    \n",
    "    if debug:\n",
    "        print(' > Dataframe after renamings & deletion of empty columns')\n",
    "        display(processed_df.sort_values(by=['account_id', 'date'],\n",
    "                                         ascending=[True, False]))\n",
    "        display(processed_df['operation'].unique())\n",
    "    \n",
    "    # Aggregatting transaction balances\n",
    "    agg_ballance = processed_df.sort_values(by=['account_id', 'date'],\n",
    "                                            ascending=[True, False])\\\n",
    "                               .groupby(['account_id'])\\\n",
    "                               .agg({\n",
    "                                    'balance': ['mean', 'max', 'min', 'std', get_first, get_last],\n",
    "                                    'date': get_first,\n",
    "                                    'amount': get_first\n",
    "                               })\\\n",
    "                               .reset_index()\n",
    "    agg_ballance.columns = ['account_id', 'balance_mean', 'balance_max', 'balance_min', 'balance_std',\n",
    "                            'last_balance', 'first_balance', 'last_trans_date', 'last_trans']\n",
    "    agg_ballance['reached_negative_balance'] = agg_ballance['balance_min']\n",
    "    agg_ballance.loc[agg_ballance[\"balance_min\"] >= 0, \"reached_negative_balance\"] = 1\n",
    "    agg_ballance.loc[agg_ballance[\"balance_min\"] < 0, \"reached_negative_balance\"] = -1\n",
    "\n",
    "    # Agrregatting credits &. withdrawals\n",
    "    agg_types = processed_df.sort_values(by=['account_id', 'date'],\n",
    "                                            ascending=[True, False])\\\n",
    "                              .groupby(['account_id', 'type'])\\\n",
    "                              .agg({\n",
    "                                  'amount': ['mean', 'count', 'max', 'min', 'std'],\n",
    "                              })\\\n",
    "                              .reset_index()\n",
    "    agg_types.columns = ['account_id', 'type', 'type_mean', 'type_count',\n",
    "                           'type_max', 'type_min', 'type_std']\n",
    "\n",
    "    agg_credits = agg_types[agg_types['type'] == 'credit']\n",
    "    agg_credits.columns = ['account_id', 'type', 'credit_mean', 'credit_count',\n",
    "                           'credit_max', 'credit_min', 'credit_std']\n",
    "    agg_credits.drop(['type'], axis=1)\n",
    "    \n",
    "    agg_withdrawals = agg_types[agg_types['type'] == 'withdrawal']\n",
    "    agg_withdrawals.columns = ['account_id', 'type', 'withdrawal_mean', 'withdrawal_count',\n",
    "                           'withdrawal_max', 'withdrawal_min', 'withdrawal_std']\n",
    "    agg_withdrawals.drop(['type'], axis=1)\n",
    "    \n",
    "    # Aggregatting k_symbols\n",
    "    agg_k_symbol = processed_df.groupby(['account_id', 'k_symbol'])\\\n",
    "                           .agg({\n",
    "                               'amount': ['mean', 'sum', 'count', 'std']\n",
    "                           })\\\n",
    "                           .reset_index()\n",
    "    agg_k_symbol.columns = ['account_id', 'k_symbol', 'amount_mean',\n",
    "                            'amount_sum', 'amount_count', 'amount_std']\n",
    "\n",
    "    \n",
    "    # Geetting households - only mean is interesting\n",
    "    households = agg_k_symbol[agg_k_symbol['k_symbol'] == 'household']\n",
    "    households.columns = ['account_id', 'k_symbol', 'household', '_1', '_2', '_3']\n",
    "    households = households.drop(['k_symbol', '_1', '_2', '_3'], axis=1)\n",
    "    \n",
    "    # Geetting pensions - only mean is interesting\n",
    "    pensions = agg_k_symbol[agg_k_symbol['k_symbol'] == 'old-age pension']\n",
    "    pensions.columns = ['account_id', 'k_symbol', 'pension', '_1', '_2', '_3']\n",
    "    pensions = pensions.drop(['k_symbol', '_1', '_2', '_3'], axis=1)\n",
    "    \n",
    "    # Geetting unknown stats\n",
    "    unknown = agg_k_symbol[agg_k_symbol['k_symbol'] == ' ']\n",
    "    unknown.columns = ['account_id', 'k_symbol', 'unknown_mean', 'unknown_sum', 'unknown_count', 'unknown_std']\n",
    "    unknown = unknown.drop(['k_symbol'], axis=1)\n",
    "\n",
    "    # Gettting payments for statement stats\n",
    "    payment_st = agg_k_symbol[agg_k_symbol['k_symbol'] == 'payment for statement']\n",
    "    payment_st.columns = ['account_id', 'k_symbol', 'payment_statement_mean', 'payment_statement_sum',\n",
    "                        'payment_statement_count', '_2']\n",
    "    payment_st = payment_st.drop(['k_symbol', '_2'], axis=1)\n",
    "    \n",
    "    # Getting insurance payment stats\n",
    "    ins_payment = agg_k_symbol[agg_k_symbol['k_symbol'] == 'insurrance payment']\n",
    "    ins_payment.columns = ['account_id', 'k_symbol', 'ins_payment_mean', 'ins_payment_sum',\n",
    "                        'ins_payment_count', '_2']\n",
    "    ins_payment = ins_payment.drop(['k_symbol', '_2'], axis=1)\n",
    "    \n",
    "    # Getting insurance payment stats\n",
    "    sanctions = agg_k_symbol[agg_k_symbol['k_symbol'] == 'sanction interest if negative balance']\n",
    "    sanctions.columns = ['account_id', 'k_symbol', 'sanctions_mean', 'sanctions_sum',\n",
    "                        'sanctions_count', 'sanctions_std']\n",
    "    sanctions = sanctions.drop(['k_symbol'], axis=1)\n",
    "\n",
    "    # Aggregatting all the 5 tables into one - agg_ballance, agg_credits, agg_withdrawals, households & pensions\n",
    "    df = agg_ballance.merge(agg_credits, on='account_id', how='left')\\\n",
    "                     .merge(agg_withdrawals, on='account_id', how='left')\\\n",
    "                     .merge(households, on='account_id', how='left')\\\n",
    "                     .merge(pensions, on='account_id', how='left')\\\n",
    "                     .merge(unknown, on='account_id', how='left')\\\n",
    "                     .merge(payment_st, on='account_id', how='left')\\\n",
    "                     .merge(ins_payment, on='account_id', how='left')\\\n",
    "                     .merge(sanctions, on='account_id', how='left')\n",
    "\n",
    "    \n",
    "    # Cleaning nulls\n",
    "    df = df.drop(['type_x', 'type_y'], axis=1)\n",
    "    df = numerical_imputation(df, 0)\n",
    "\n",
    "    \n",
    "    # Cleaning nulls and performing aggreggation on table containing operations\n",
    "    operations = processed_df.groupby(['account_id', 'operation'])\\\n",
    "                             .agg({\n",
    "                                 'amount': ['count', 'mean', 'sum', 'max', 'std', 'min'],\n",
    "                             })\\\n",
    "                             .reset_index()\n",
    "    operations.columns = ['account_id', 'operation', 'amount_count', 'amount_mean', 'amount_sum',\n",
    "                          'amount_max', 'amount_std', 'amount_min']\n",
    "    \n",
    "    # Getting CC\n",
    "    cc = operations[operations['operation'] == 'CC']\n",
    "    cc.columns = ['account_id', 'operation', 'CC', 'CC_mean', 'CC_sum', 'CC_max', 'CC_std', 'CC_min']\n",
    "    cc = cc.drop(['operation'], axis=1)\n",
    "    \n",
    "    # Getting CAB\n",
    "    cab = operations[operations['operation'] == 'CAB']\n",
    "    cab.columns = ['account_id', 'operation', 'CAB', 'CAB_mean', 'CAB_sum', 'CAB_max', 'CAB_std', 'CAB_min']\n",
    "    cab =cab.drop(['operation'], axis=1)\n",
    "    \n",
    "    # Getting IC\n",
    "    ic = operations[operations['operation'] == 'IC']\n",
    "    ic.columns = ['account_id', 'operation', 'IC', 'IC_mean', 'IC_sum', 'IC_max', 'IC_std', 'IC_min']\n",
    "    ic = ic.drop(['operation'], axis=1)\n",
    "    \n",
    "    # Getting WC\n",
    "    wc = operations[operations['operation'] == 'WC']\n",
    "    wc.columns = ['account_id', 'operation', 'WC', 'WC_mean', 'WC_sum', 'WC_max', 'WC_std', 'WC_min']\n",
    "    wc = wc.drop(['operation'], axis=1)\n",
    "    \n",
    "    # Getting RAB\n",
    "    rab = operations[operations['operation'] == 'RAB']\n",
    "    rab.columns = ['account_id', 'operation', 'RAB', 'RAB_mean', 'RAB_sum', 'RAB_max', 'RAB_std', 'RAB_min']\n",
    "    rab = rab.drop(['operation'], axis=1)\n",
    "    \n",
    "    # Getting CCW\n",
    "    ccw = operations[operations['operation'] == 'CCW']\n",
    "    ccw.columns = ['account_id', 'operation', 'CCW', 'CCW_mean', 'CCW_sum', 'CCW_max', 'CCW_std', 'CCW_min']\n",
    "    ccw = ccw.drop(['operation'], axis=1)\n",
    "    \n",
    "    # Joining all the operations\n",
    "    operations_df = cc.merge(cab, on='account_id', how='left')\\\n",
    "                      .merge(ic, on='account_id', how='left')\\\n",
    "                      .merge(wc, on='account_id', how='left')\\\n",
    "                      .merge(rab, on='account_id', how='left')\\\n",
    "                      .merge(ccw, on='account_id', how='left')\n",
    "    # Cleaning nulls\n",
    "    operations_df = numerical_imputation(operations_df, 0)\n",
    "    \n",
    "    if debug:\n",
    "        print(' > We can also compute the type operations for each account, and the respective statistics:')\n",
    "        display(operations_df)\n",
    "        \n",
    "    # Join operations with the numeric data\n",
    "    df = df.merge(operations_df, on='account_id', how='left')\n",
    "    \n",
    "    # Adding extra columns\n",
    "    df['mean_trans_profit'] = df['credit_mean'] + df['withdrawal_mean']\n",
    "    df['total_ops'] = df['CC'] + df['CAB'] + df['WC'] + df['RAB'] + df['CCW'] + df['IC']\n",
    "    df['credit_ratio'] = df['credit_count'] / df['total_ops']\n",
    "    df['withdrawal_ratio'] = df['withdrawal_count'] / df['total_ops']\n",
    "    df['balance_range'] = df['balance_max'] - df['balance_min']\n",
    "    df['last_first_balance_ratio'] = df['last_balance'] / df['first_balance']\n",
    "    df['last_max_balance_ratio'] = df['last_balance'] / df['balance_max']\n",
    "\n",
    "    # OPs as ratios\n",
    "    df['ratio_CC'] = df['CC'] / df['total_ops']\n",
    "    df['ratio_CAB'] = df['CAB'] / df['total_ops']\n",
    "    df['ratio_WC'] = df['WC'] / df['total_ops']\n",
    "    df['ratio_RAB'] = df['RAB'] / df['total_ops']\n",
    "    df['ratio_CCW'] = df['CCW'] / df['total_ops']\n",
    "    df['ratio_IC'] = df['IC'] / df['total_ops']\n",
    "\n",
    "    # df = df.drop(['CC', 'CAB', 'WC', 'RAB', 'CCW', 'IC', 'credit_count', 'withdrawal_count'], axis=1)\n",
    "\n",
    "    if debug:\n",
    "        print(' > Table after processment of balance, credits, withdrawals, households, pensions and operations')\n",
    "        display(df)\n",
    "        get_null_summary(df)\n",
    "        print(' > And the corresponding correlation matrix')\n",
    "        get_df_correlation(df)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def process_demographic(demographic_df, debug=False):\n",
    "    '''Pre process the demographic table'''\n",
    "    if debug:\n",
    "        print(' > Raw demographic table representation')\n",
    "        display(demographic_df)\n",
    "        print(' > Raw demographic table correlations')\n",
    "        get_df_correlation(demographic_df)\n",
    "        \n",
    "    if debug:\n",
    "        print(' > Null evaluation in demographic rows')\n",
    "        get_null_summary(demographic_df)\n",
    "        \n",
    "    if debug:\n",
    "        print(' > Lets see the impact of urbanization on salaries')\n",
    "        bubbleplot_three_cols(demographic_df, 'no. of inhabitants',\n",
    "                              'ratio of urban inhabitants ', 'average salary ')\n",
    "    \n",
    "    # Encode categorical columns\n",
    "    df = encode_column(demographic_df, 'name ')\n",
    "    df = encode_column(df, 'region')\n",
    "    \n",
    "    if debug:\n",
    "        print('First, lets start by encoding the categorical columns')\n",
    "        display(df)\n",
    "        display(df.columns)\n",
    "\n",
    "    # Replacing '?' by the average value\n",
    "    median_crimes_95 = pd.to_numeric(df[df['no. of commited crimes \\'95 '] != '?']\\\n",
    "                                     ['no. of commited crimes \\'95 ']).median()\n",
    "    df.loc[df['no. of commited crimes \\'95 ']==\"?\", 'no. of commited crimes \\'95 '] = median_crimes_95\n",
    "\n",
    "    median_unemploymant_95 = (df[df['unemploymant rate \\'95 '] != '?']\\\n",
    "                              ['unemploymant rate \\'95 ']).astype(float).median()\n",
    "    df.loc[df['unemploymant rate \\'95 ']==\"?\", 'unemploymant rate \\'95 '] = median_unemploymant_95\n",
    "    \n",
    "    # Transforming data to ints/ floats\n",
    "    df['unemploymant rate \\'95 '] = df['unemploymant rate \\'95 '].astype(float)\n",
    "    df['no. of commited crimes \\'95 '] = pd.to_numeric(df['no. of commited crimes \\'95 '])\n",
    "    df['unemploymant rate \\'96 '] = df['unemploymant rate \\'96 '].astype(float)\n",
    "    df['no. of commited crimes \\'96 '] = pd.to_numeric(df['no. of commited crimes \\'96 '])\n",
    "    df['no. of enterpreneurs per 1000 inhabitants '] = pd.to_numeric(df['no. of enterpreneurs per 1000 inhabitants '])\n",
    "    df['ratio of urban inhabitants '] = df['ratio of urban inhabitants '].astype(float)\n",
    "\n",
    "    # Now lets create new features:\n",
    "    df['ratio entrepeneurs'] = df['no. of enterpreneurs per 1000 inhabitants '] / 1000\n",
    "    df['ratio of urban inhabitants '] = df['ratio of urban inhabitants '] / 100\n",
    "    \n",
    "    # Growths\n",
    "    df['criminality_growth'] = (df['no. of commited crimes \\'96 '] - df['no. of commited crimes \\'95 ']) /\\\n",
    "                              df['no. of inhabitants']\n",
    "    df['unemploymant_growth'] = df['unemploymant rate \\'96 '] - df['unemploymant rate \\'95 ']\n",
    "    \n",
    "    df = df.drop(['no. of enterpreneurs per 1000 inhabitants ', \n",
    "                 'unemploymant rate \\'96 ', 'no. of commited crimes \\'96 ',\n",
    "                  'unemploymant rate \\'95 ', 'no. of commited crimes \\'95 '], axis=1)\n",
    "    \n",
    "    if debug:\n",
    "        print('The final demographic plot, with additional features and having converted some features to ratios:')\n",
    "        display(df)\n",
    "        print('And the corresponding correlation matrix:')\n",
    "        get_df_correlation(df)\n",
    "\n",
    "    return df.rename(columns={'code ': 'district_id'}).drop(['name '], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Composite pre processment of tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_id</th>\n",
       "      <th>payments</th>\n",
       "      <th>balance_mean</th>\n",
       "      <th>balance_min</th>\n",
       "      <th>reached_negative_balance</th>\n",
       "      <th>unknown_sum</th>\n",
       "      <th>sanctions_mean</th>\n",
       "      <th>sanctions_sum</th>\n",
       "      <th>sanctions_count</th>\n",
       "      <th>sanctions_std</th>\n",
       "      <th>CC_max</th>\n",
       "      <th>CC_std</th>\n",
       "      <th>CAB_mean</th>\n",
       "      <th>CAB_sum</th>\n",
       "      <th>CAB_max</th>\n",
       "      <th>CAB_std</th>\n",
       "      <th>CAB_min</th>\n",
       "      <th>IC_mean</th>\n",
       "      <th>IC_min</th>\n",
       "      <th>RAB</th>\n",
       "      <th>RAB_mean</th>\n",
       "      <th>RAB_sum</th>\n",
       "      <th>RAB_max</th>\n",
       "      <th>RAB_min</th>\n",
       "      <th>mean_trans_profit</th>\n",
       "      <th>credit_ratio</th>\n",
       "      <th>withdrawal_ratio</th>\n",
       "      <th>ratio_CC</th>\n",
       "      <th>ratio_RAB</th>\n",
       "      <th>owner_count</th>\n",
       "      <th>disponent_count</th>\n",
       "      <th>salary_from_month_records</th>\n",
       "      <th>withdrawal_to_credit_month_ratio</th>\n",
       "      <th>actual_month_income</th>\n",
       "      <th>actual_income_to_payments_ratio</th>\n",
       "      <th>log_sanctions_std</th>\n",
       "      <th>log_max_value_in_account_to_loan_ratio</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5314</td>\n",
       "      <td>8033</td>\n",
       "      <td>12250.000000</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9900.0</td>\n",
       "      <td>3774.806838</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5025.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5025.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5025.000000</td>\n",
       "      <td>0.625545</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.113178</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5316</td>\n",
       "      <td>4610</td>\n",
       "      <td>52083.859459</td>\n",
       "      <td>700.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>62612.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36574.0</td>\n",
       "      <td>13044.711945</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>168.540000</td>\n",
       "      <td>2.9</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-2682.375000</td>\n",
       "      <td>-21459.0</td>\n",
       "      <td>-956.0</td>\n",
       "      <td>-4167.0</td>\n",
       "      <td>4638.918824</td>\n",
       "      <td>0.459459</td>\n",
       "      <td>0.540541</td>\n",
       "      <td>0.324324</td>\n",
       "      <td>0.216216</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4638.918824</td>\n",
       "      <td>-0.656965</td>\n",
       "      <td>4291.668824</td>\n",
       "      <td>0.930948</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.493154</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>6863</td>\n",
       "      <td>2118</td>\n",
       "      <td>30060.954167</td>\n",
       "      <td>800.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27534.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3700.0</td>\n",
       "      <td>2050.609665</td>\n",
       "      <td>13981.000000</td>\n",
       "      <td>69905.0</td>\n",
       "      <td>19065.0</td>\n",
       "      <td>2842.042399</td>\n",
       "      <td>12710.0</td>\n",
       "      <td>92.625000</td>\n",
       "      <td>48.6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-2824.333333</td>\n",
       "      <td>-8473.0</td>\n",
       "      <td>-66.0</td>\n",
       "      <td>-7936.0</td>\n",
       "      <td>-1087.266667</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>0.083333</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1087.266667</td>\n",
       "      <td>-1.217031</td>\n",
       "      <td>-1748.600000</td>\n",
       "      <td>-0.825590</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.263553</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5325</td>\n",
       "      <td>2939</td>\n",
       "      <td>41297.480000</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>47508.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3900.0</td>\n",
       "      <td>2050.609665</td>\n",
       "      <td>19101.333333</td>\n",
       "      <td>114608.0</td>\n",
       "      <td>26448.0</td>\n",
       "      <td>3599.116929</td>\n",
       "      <td>17632.0</td>\n",
       "      <td>160.360000</td>\n",
       "      <td>132.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>-6994.000000</td>\n",
       "      <td>-13988.0</td>\n",
       "      <td>-6994.0</td>\n",
       "      <td>-6994.0</td>\n",
       "      <td>2086.500000</td>\n",
       "      <td>0.520000</td>\n",
       "      <td>0.480000</td>\n",
       "      <td>0.080000</td>\n",
       "      <td>0.080000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2086.500000</td>\n",
       "      <td>-0.774545</td>\n",
       "      <td>1503.666667</td>\n",
       "      <td>0.511625</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.427978</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>7240</td>\n",
       "      <td>4579</td>\n",
       "      <td>57188.211111</td>\n",
       "      <td>600.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40788.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>45764.333333</td>\n",
       "      <td>274586.0</td>\n",
       "      <td>63366.0</td>\n",
       "      <td>8623.020391</td>\n",
       "      <td>42244.0</td>\n",
       "      <td>190.183333</td>\n",
       "      <td>77.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-6584.666667</td>\n",
       "      <td>-19754.0</td>\n",
       "      <td>-756.0</td>\n",
       "      <td>-9499.0</td>\n",
       "      <td>4454.930769</td>\n",
       "      <td>0.481481</td>\n",
       "      <td>0.518519</td>\n",
       "      <td>0.037037</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4454.930769</td>\n",
       "      <td>-0.790415</td>\n",
       "      <td>4391.930769</td>\n",
       "      <td>0.959146</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.306472</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>323</td>\n",
       "      <td>6818</td>\n",
       "      <td>3242</td>\n",
       "      <td>44197.509884</td>\n",
       "      <td>200.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>101122.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26388.0</td>\n",
       "      <td>6517.394689</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>145.472727</td>\n",
       "      <td>23.7</td>\n",
       "      <td>36.0</td>\n",
       "      <td>-1709.000000</td>\n",
       "      <td>-61524.0</td>\n",
       "      <td>-831.0</td>\n",
       "      <td>-2587.0</td>\n",
       "      <td>2618.124034</td>\n",
       "      <td>0.406977</td>\n",
       "      <td>0.593023</td>\n",
       "      <td>0.151163</td>\n",
       "      <td>0.209302</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2618.124034</td>\n",
       "      <td>-0.594000</td>\n",
       "      <td>2402.540700</td>\n",
       "      <td>0.741067</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.333010</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>324</td>\n",
       "      <td>5625</td>\n",
       "      <td>3703</td>\n",
       "      <td>55230.444068</td>\n",
       "      <td>800.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>56990.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44352.0</td>\n",
       "      <td>13832.527973</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>223.050000</td>\n",
       "      <td>103.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3220.376498</td>\n",
       "      <td>0.474576</td>\n",
       "      <td>0.525424</td>\n",
       "      <td>0.271186</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3220.376498</td>\n",
       "      <td>-0.759988</td>\n",
       "      <td>3220.376498</td>\n",
       "      <td>0.869667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.405061</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>325</td>\n",
       "      <td>6805</td>\n",
       "      <td>938</td>\n",
       "      <td>41994.907692</td>\n",
       "      <td>800.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>51200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31636.5</td>\n",
       "      <td>9517.351233</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>114.216667</td>\n",
       "      <td>41.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-3387.500000</td>\n",
       "      <td>-20325.0</td>\n",
       "      <td>-2056.0</td>\n",
       "      <td>-4719.0</td>\n",
       "      <td>1570.498421</td>\n",
       "      <td>0.512821</td>\n",
       "      <td>0.487179</td>\n",
       "      <td>0.205128</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1570.498421</td>\n",
       "      <td>-0.816207</td>\n",
       "      <td>1177.248421</td>\n",
       "      <td>1.255062</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.843812</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>326</td>\n",
       "      <td>7233</td>\n",
       "      <td>3217</td>\n",
       "      <td>56646.516129</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>38211.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49887.0</td>\n",
       "      <td>14644.196076</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>206.068421</td>\n",
       "      <td>51.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5562.846548</td>\n",
       "      <td>0.411290</td>\n",
       "      <td>0.588710</td>\n",
       "      <td>0.258065</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5562.846548</td>\n",
       "      <td>-0.663978</td>\n",
       "      <td>5562.846548</td>\n",
       "      <td>1.729203</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.664437</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>327</td>\n",
       "      <td>7308</td>\n",
       "      <td>5392</td>\n",
       "      <td>30869.781308</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>73977.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24212.0</td>\n",
       "      <td>6318.812124</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>111.846154</td>\n",
       "      <td>50.1</td>\n",
       "      <td>40.0</td>\n",
       "      <td>-1323.750000</td>\n",
       "      <td>-52950.0</td>\n",
       "      <td>-56.0</td>\n",
       "      <td>-4780.0</td>\n",
       "      <td>5845.843236</td>\n",
       "      <td>0.271028</td>\n",
       "      <td>0.728972</td>\n",
       "      <td>0.149533</td>\n",
       "      <td>0.373832</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5845.843236</td>\n",
       "      <td>-0.336586</td>\n",
       "      <td>5447.509903</td>\n",
       "      <td>1.010295</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.296012</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     loan_id  payments  balance_mean  balance_min  reached_negative_balance  \\\n",
       "0       5314      8033  12250.000000       1100.0                       1.0   \n",
       "1       5316      4610  52083.859459        700.0                       1.0   \n",
       "2       6863      2118  30060.954167        800.0                       1.0   \n",
       "3       5325      2939  41297.480000       1000.0                       1.0   \n",
       "4       7240      4579  57188.211111        600.0                       1.0   \n",
       "..       ...       ...           ...          ...                       ...   \n",
       "323     6818      3242  44197.509884        200.0                       1.0   \n",
       "324     5625      3703  55230.444068        800.0                       1.0   \n",
       "325     6805       938  41994.907692        800.0                       1.0   \n",
       "326     7233      3217  56646.516129       1100.0                       1.0   \n",
       "327     7308      5392  30869.781308       1000.0                       1.0   \n",
       "\n",
       "     unknown_sum  sanctions_mean  sanctions_sum  sanctions_count  \\\n",
       "0        20100.0             0.0            0.0              0.0   \n",
       "1        62612.0             0.0            0.0              0.0   \n",
       "2        27534.0             0.0            0.0              0.0   \n",
       "3        47508.0             0.0            0.0              0.0   \n",
       "4        40788.0             0.0            0.0              0.0   \n",
       "..           ...             ...            ...              ...   \n",
       "323     101122.0             0.0            0.0              0.0   \n",
       "324      56990.0             0.0            0.0              0.0   \n",
       "325      51200.0             0.0            0.0              0.0   \n",
       "326      38211.0             0.0            0.0              0.0   \n",
       "327      73977.0             0.0            0.0              0.0   \n",
       "\n",
       "     sanctions_std   CC_max        CC_std      CAB_mean   CAB_sum  CAB_max  \\\n",
       "0              0.0   9900.0   3774.806838      0.000000       0.0      0.0   \n",
       "1              0.0  36574.0  13044.711945      0.000000       0.0      0.0   \n",
       "2              0.0   3700.0   2050.609665  13981.000000   69905.0  19065.0   \n",
       "3              0.0   3900.0   2050.609665  19101.333333  114608.0  26448.0   \n",
       "4              0.0    600.0      0.000000  45764.333333  274586.0  63366.0   \n",
       "..             ...      ...           ...           ...       ...      ...   \n",
       "323            0.0  26388.0   6517.394689      0.000000       0.0      0.0   \n",
       "324            0.0  44352.0  13832.527973      0.000000       0.0      0.0   \n",
       "325            0.0  31636.5   9517.351233      0.000000       0.0      0.0   \n",
       "326            0.0  49887.0  14644.196076      0.000000       0.0      0.0   \n",
       "327            0.0  24212.0   6318.812124      0.000000       0.0      0.0   \n",
       "\n",
       "         CAB_std  CAB_min     IC_mean  IC_min   RAB     RAB_mean  RAB_sum  \\\n",
       "0       0.000000      0.0    0.000000     0.0   0.0     0.000000      0.0   \n",
       "1       0.000000      0.0  168.540000     2.9   8.0 -2682.375000 -21459.0   \n",
       "2    2842.042399  12710.0   92.625000    48.6   3.0 -2824.333333  -8473.0   \n",
       "3    3599.116929  17632.0  160.360000   132.8   2.0 -6994.000000 -13988.0   \n",
       "4    8623.020391  42244.0  190.183333    77.1   3.0 -6584.666667 -19754.0   \n",
       "..           ...      ...         ...     ...   ...          ...      ...   \n",
       "323     0.000000      0.0  145.472727    23.7  36.0 -1709.000000 -61524.0   \n",
       "324     0.000000      0.0  223.050000   103.2   0.0     0.000000      0.0   \n",
       "325     0.000000      0.0  114.216667    41.0   6.0 -3387.500000 -20325.0   \n",
       "326     0.000000      0.0  206.068421    51.1   0.0     0.000000      0.0   \n",
       "327     0.000000      0.0  111.846154    50.1  40.0 -1323.750000 -52950.0   \n",
       "\n",
       "     RAB_max  RAB_min  mean_trans_profit  credit_ratio  withdrawal_ratio  \\\n",
       "0        0.0      0.0        5025.000000      1.000000          0.000000   \n",
       "1     -956.0  -4167.0        4638.918824      0.459459          0.540541   \n",
       "2      -66.0  -7936.0       -1087.266667      0.625000          0.375000   \n",
       "3    -6994.0  -6994.0        2086.500000      0.520000          0.480000   \n",
       "4     -756.0  -9499.0        4454.930769      0.481481          0.518519   \n",
       "..       ...      ...                ...           ...               ...   \n",
       "323   -831.0  -2587.0        2618.124034      0.406977          0.593023   \n",
       "324      0.0      0.0        3220.376498      0.474576          0.525424   \n",
       "325  -2056.0  -4719.0        1570.498421      0.512821          0.487179   \n",
       "326      0.0      0.0        5562.846548      0.411290          0.588710   \n",
       "327    -56.0  -4780.0        5845.843236      0.271028          0.728972   \n",
       "\n",
       "     ratio_CC  ratio_RAB  owner_count  disponent_count  \\\n",
       "0    1.000000   0.000000            1                1   \n",
       "1    0.324324   0.216216            1                1   \n",
       "2    0.083333   0.125000            1                1   \n",
       "3    0.080000   0.080000            1                1   \n",
       "4    0.037037   0.111111            1                1   \n",
       "..        ...        ...          ...              ...   \n",
       "323  0.151163   0.209302            2                2   \n",
       "324  0.271186   0.000000            1                1   \n",
       "325  0.205128   0.153846            1                1   \n",
       "326  0.258065   0.000000            1                1   \n",
       "327  0.149533   0.373832            1                1   \n",
       "\n",
       "     salary_from_month_records  withdrawal_to_credit_month_ratio  \\\n",
       "0                  5025.000000                          0.000000   \n",
       "1                  4638.918824                         -0.656965   \n",
       "2                 -1087.266667                         -1.217031   \n",
       "3                  2086.500000                         -0.774545   \n",
       "4                  4454.930769                         -0.790415   \n",
       "..                         ...                               ...   \n",
       "323                2618.124034                         -0.594000   \n",
       "324                3220.376498                         -0.759988   \n",
       "325                1570.498421                         -0.816207   \n",
       "326                5562.846548                         -0.663978   \n",
       "327                5845.843236                         -0.336586   \n",
       "\n",
       "     actual_month_income  actual_income_to_payments_ratio  log_sanctions_std  \\\n",
       "0            5025.000000                         0.625545                0.0   \n",
       "1            4291.668824                         0.930948                0.0   \n",
       "2           -1748.600000                        -0.825590                0.0   \n",
       "3            1503.666667                         0.511625                0.0   \n",
       "4            4391.930769                         0.959146                0.0   \n",
       "..                   ...                              ...                ...   \n",
       "323          2402.540700                         0.741067                0.0   \n",
       "324          3220.376498                         0.869667                0.0   \n",
       "325          1177.248421                         1.255062                0.0   \n",
       "326          5562.846548                         1.729203                0.0   \n",
       "327          5447.509903                         1.010295                0.0   \n",
       "\n",
       "     log_max_value_in_account_to_loan_ratio  status  \n",
       "0                                  0.113178      -1  \n",
       "1                                  0.493154       1  \n",
       "2                                  0.263553       1  \n",
       "3                                  0.427978       1  \n",
       "4                                  0.306472       1  \n",
       "..                                      ...     ...  \n",
       "323                                0.333010       1  \n",
       "324                                0.405061      -1  \n",
       "325                                0.843812       1  \n",
       "326                                0.664437       1  \n",
       "327                                0.296012       1  \n",
       "\n",
       "[328 rows x 38 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def compose_dataset(loan_df, account_df, disp_df, card_df, client_df, trans_df, demogra_df,\n",
    "                    debug=False, pre_debug=False):\n",
    "    '''Join the different tables and apply feature engineering'''\n",
    "\n",
    "    # Pre processment of all the necessary tables\n",
    "    processed_loan = process_loans(loan_df, pre_debug)\n",
    "    processed_account = process_account(account_df, pre_debug)\n",
    "    processed_trans = process_transactions(trans_df, pre_debug)\n",
    "    processed_disp = process_dispostition(disp_df, pre_debug)\n",
    "    processed_client = process_client(client_df, pre_debug)\n",
    "    processed_card = process_card(card_df, pre_debug)\n",
    "    processed_demogra = process_demographic(demogra_df, pre_debug)\n",
    "\n",
    "    # Joining the different tables\n",
    "    main_df = processed_loan.merge(processed_account.rename(columns={'date': 'account_creation_date'}),\n",
    "                                   on='account_id', how='left')\\\n",
    "                            .merge(processed_trans,\n",
    "                                   on='account_id', how='left')\n",
    "\n",
    "    # Adding all bank demographic info has negative impact on model performance,\n",
    "    # since we are feeding irrelevant features\n",
    "    main_df = main_df.drop(['district_id'], axis=1)\n",
    "\n",
    "    if debug:\n",
    "        print(' > Joint table of loans, account, transaction:')\n",
    "        display(main_df)\n",
    "        \n",
    "    ################################################\n",
    "\n",
    "    df_disp_client_card = processed_disp.merge(processed_client,\n",
    "                                              on='client_id', how='left')\\\n",
    "                                        .merge(processed_card,\n",
    "                                              on='disp_id', how='left')\n",
    "    if debug:\n",
    "        print(' > Joint table of disposition, client & card:')\n",
    "        display(df_disp_client_card)\n",
    "        get_null_summary(df_disp_client_card)\n",
    "    \n",
    "    # Since there are so many, we disconsider the card date columns\n",
    "    df_disp_client = df_disp_client_card.drop(['issued'], axis=1)\n",
    "    df_disp_client = numerical_imputation(df_disp_client, -1)\n",
    "    \n",
    "    # Now we aggreggate the remaining data\n",
    "    # TODO: not yet assure how to handle agg on birthdate and Gender -> using account and district id to get owner\n",
    "    df_disp_client = df_disp_client.sort_values(by=['account_id', 'type_O'], ascending=[True, False])\\\n",
    "                                   .groupby(['account_id'])\\\n",
    "                                   .agg({\n",
    "                                       'type_O': ['count'],\n",
    "                                       'type_U': ['count'],\n",
    "                                       'gender': get_first,\n",
    "                                       'birth_date': get_first,\n",
    "                                       'district_id': get_first,\n",
    "                                       'type': get_first\n",
    "                                   })\\\n",
    "                                   .reset_index()\n",
    "    df_disp_client.columns= ['account_id', 'owner_count', 'disponent_count',\n",
    "                             'owner_gender', 'owner_birthdate', 'district_id', 'card_type']\n",
    "    df_disp_client['has_card'] = df_disp_client['card_type']\n",
    "    df_disp_client.loc[df_disp_client[\"card_type\"] > 0, \"has_card\"] = 1\n",
    "    df_disp_client.loc[df_disp_client[\"card_type\"] == 0, \"has_card\"] = -1\n",
    "    \n",
    "    \n",
    "    if debug:\n",
    "        print(' > Table after aggreggating data by account_id:')\n",
    "        display(df_disp_client)\n",
    "        \n",
    "    # Now we join the district information regarding the owner\n",
    "    df_secondary = df_disp_client.merge(processed_demogra, on='district_id')\n",
    "    \n",
    "    if debug:\n",
    "        print(' > Table after joining with the demographics table:')\n",
    "        display(df_secondary)\n",
    "    \n",
    "    ################################################\n",
    "    \n",
    "    # Joining the previously built two major tables\n",
    "    df = main_df.merge(df_secondary, on='account_id')\n",
    "\n",
    "    # Creating new columns using previous ones\n",
    "    df['account_age_on_loan'] = (df['date'] - df['account_creation_date']).dt.days\n",
    "    df['days_since_last_transaction'] = (df['date'] - df['last_trans_date']).dt.days\n",
    "    df['owner_age_on_loan'] = (df['date'] - df['owner_birthdate']).dt.days / 365\n",
    "    df[\"max_value_in_account_to_loan_ratio\"] = df[\"balance_max\"] / df[\"amount\"]\n",
    "    df[\"last_value_in_account_to_loan_ratio\"] = df[\"last_balance\"] / df[\"amount\"]\n",
    "    df[\"date\"] = df[\"date\"].astype(int)\n",
    "\n",
    "    # Stats per month\n",
    "    df['account_age_months'] = df['account_age_on_loan'] / 30\n",
    "    df[\"trans_per_month\"] = df['total_ops'] / df[\"account_age_months\"]\n",
    "    df['credit_per_month'] = df['credit_mean'] / df[\"account_age_months\"]\n",
    "    df['withdrawal_per_month'] = df['withdrawal_mean'] / df[\"account_age_months\"]\n",
    "    df['salary_from_month_records'] = df['credit_mean'] + df['withdrawal_mean']\n",
    "    df['withdrawal_to_credit_month_ratio'] = df['withdrawal_per_month'] / df['credit_per_month']\n",
    "\n",
    "    # Average salary, pension and household are anual- household value is negative\n",
    "    df['expected_month_income'] = (df['average salary '] + df['household']) / 12\n",
    "    df['actual_month_income'] = df['salary_from_month_records'] + (df['household'] / 12)\n",
    "    df.loc[df[\"pension\"] > 0, \"expected_month_income\"] = (df[\"pension\"] + df['household']) / 12\n",
    "    df.loc[df[\"pension\"] > 0, \"actual_month_income\"] = df[\"actual_month_income\"] + (df['pension'] / 12)\n",
    "    df['expected_income_to_payments_ratio'] = df['expected_month_income'] / df['payments']\n",
    "    df['actual_income_to_payments_ratio'] = df['actual_month_income'] / df['payments']\n",
    "    df['ratio_actual_salary_to_expected'] = df['actual_month_income'] / df['expected_month_income']\n",
    "\n",
    "    # Using Log transformation on skewed data\n",
    "    df['log_credit_min'] = log_transform(df['credit_min'])\n",
    "    df['log_sanctions_std'] = log_transform(df['sanctions_std']) \n",
    "    df['log_CAB_sum'] = log_transform(df['CAB_sum']) \n",
    "    df['log_CAB_mean'] = log_transform(df['CAB_mean'])\n",
    "    df['log_max_value_in_account_to_loan_ratio'] = log_transform(df['max_value_in_account_to_loan_ratio'])\n",
    "    df['log_criminality_growth'] = log_transform(df['criminality_growth'])\n",
    "\n",
    "    # Showing some stats regarding the new features\n",
    "    if pre_debug:\n",
    "        print(' > Age of users when requesting a loan, in years')\n",
    "        column_density_plot(df, 'owner_age_on_loan')\n",
    "    \n",
    "    # Dropping useless columns & normalizing others\n",
    "    df = df.drop(['district_id', 'account_id', 'owner_birthdate',\n",
    "                 'pension', 'household', 'account_creation_date',\n",
    "                 'last_trans_date'], axis=1)\n",
    "\n",
    "    if debug:\n",
    "        print(' > Obtained dataframe after joining the previous tables and doing some feature engineering over them')\n",
    "        display(df)\n",
    "        print(' > And the respective correlations')\n",
    "        get_df_correlation(df, (25, 25))\n",
    "        print(' > And the respective histograms for the given features')\n",
    "        get_features_histogram(df, (20, 75))\n",
    "        \n",
    "    ###############################################\n",
    "    \n",
    "    # Setting status as last column\n",
    "    df =  df[[col for col in df if col not in ['status']] + ['status']]\n",
    "    \n",
    "    # Automatic and manual features selection\n",
    "    \n",
    "    select_features_FF(df)\n",
    "    if debug:\n",
    "        print(' > Applying feature selection')\n",
    "        print(' > Obtained features from feature selection:')\n",
    "        display(filter_features)\n",
    "        \n",
    "    df = df[['loan_id'] + filter_features + ['status']]\n",
    "    \n",
    "    ###############################################\n",
    "\n",
    "    if debug:\n",
    "        print(' >>> Final table that will serve as input to the model:')\n",
    "        display(df)\n",
    "        print(' > And the respective correlations')\n",
    "        get_df_correlation(df, (25, 25))\n",
    "\n",
    "    return df\n",
    "\n",
    "dataset = compose_dataset(loan_df, account_df, disp_df, card_df, client_df,\n",
    "                          trans_df, demogra_df, debug=False, pre_debug=False)\n",
    "display(dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Outputting the resultant table to a final csv\n",
    "utils.write_df_to_csv(dataset, 'dataset', 'preprocessed_data.csv')\n",
    "\n",
    "test_dataset = compose_dataset(loan_test_df, account_df, disp_df,\n",
    "                               card_test_df, client_df, trans_test_df, demogra_df)\n",
    "utils.write_df_to_csv(test_dataset, 'dataset', 'test_dataset.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}