Data Compiler.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bdde34e9",
   "metadata": {},
   "source": [
    "##### The entire code must be run from top to bottom in order to avoid any problems."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ea05709",
   "metadata": {},
   "source": [
    "### Deal with Loans Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9c101549",
   "metadata": {},
   "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>account_id</th>\n",
       "      <th>date_loan</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",
       "      <th>0</th>\n",
       "      <td>5314</td>\n",
       "      <td>1787</td>\n",
       "      <td>1993-07-05</td>\n",
       "      <td>96396</td>\n",
       "      <td>12</td>\n",
       "      <td>8033</td>\n",
       "      <td>unsuccessful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5316</td>\n",
       "      <td>1801</td>\n",
       "      <td>1993-07-11</td>\n",
       "      <td>165960</td>\n",
       "      <td>36</td>\n",
       "      <td>4610</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6863</td>\n",
       "      <td>9188</td>\n",
       "      <td>1993-07-28</td>\n",
       "      <td>127080</td>\n",
       "      <td>60</td>\n",
       "      <td>2118</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5325</td>\n",
       "      <td>1843</td>\n",
       "      <td>1993-08-03</td>\n",
       "      <td>105804</td>\n",
       "      <td>36</td>\n",
       "      <td>2939</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7240</td>\n",
       "      <td>11013</td>\n",
       "      <td>1993-09-06</td>\n",
       "      <td>274740</td>\n",
       "      <td>60</td>\n",
       "      <td>4579</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <th>323</th>\n",
       "      <td>6818</td>\n",
       "      <td>9030</td>\n",
       "      <td>1996-12-12</td>\n",
       "      <td>155616</td>\n",
       "      <td>48</td>\n",
       "      <td>3242</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>5625</td>\n",
       "      <td>3189</td>\n",
       "      <td>1996-12-15</td>\n",
       "      <td>222180</td>\n",
       "      <td>60</td>\n",
       "      <td>3703</td>\n",
       "      <td>unsuccessful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>6805</td>\n",
       "      <td>8972</td>\n",
       "      <td>1996-12-21</td>\n",
       "      <td>45024</td>\n",
       "      <td>48</td>\n",
       "      <td>938</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>7233</td>\n",
       "      <td>10963</td>\n",
       "      <td>1996-12-25</td>\n",
       "      <td>115812</td>\n",
       "      <td>36</td>\n",
       "      <td>3217</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>7308</td>\n",
       "      <td>11362</td>\n",
       "      <td>1996-12-27</td>\n",
       "      <td>129408</td>\n",
       "      <td>24</td>\n",
       "      <td>5392</td>\n",
       "      <td>successful</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     loan_id  account_id  date_loan  amount  duration  payments        status\n",
       "0       5314        1787 1993-07-05   96396        12      8033  unsuccessful\n",
       "1       5316        1801 1993-07-11  165960        36      4610    successful\n",
       "2       6863        9188 1993-07-28  127080        60      2118    successful\n",
       "3       5325        1843 1993-08-03  105804        36      2939    successful\n",
       "4       7240       11013 1993-09-06  274740        60      4579    successful\n",
       "..       ...         ...        ...     ...       ...       ...           ...\n",
       "323     6818        9030 1996-12-12  155616        48      3242    successful\n",
       "324     5625        3189 1996-12-15  222180        60      3703  unsuccessful\n",
       "325     6805        8972 1996-12-21   45024        48       938    successful\n",
       "326     7233       10963 1996-12-25  115812        36      3217    successful\n",
       "327     7308       11362 1996-12-27  129408        24      5392    successful\n",
       "\n",
       "[328 rows x 7 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# The following variable must be set to False if we are to run the entire script on the \"Test\" data\n",
    "isTrainData = True\n",
    "fileName = \"train\" if isTrainData else \"test\"\n",
    "\n",
    "# Convert Date from numerical to a Date format.\n",
    "output = pd.read_csv('BankData/loan_' + fileName + '.csv', delimiter = \";\")\n",
    "output['date'] = 19000000 + output['date']\n",
    "output['date'] = pd.to_datetime(output['date'], format = '%Y%m%d')\n",
    "output['status'] = output['status'].replace(-1,'unsuccessful')\n",
    "output['status'] = output['status'].replace(1,'successful')\n",
    "\n",
    "# Change the Date variable\n",
    "loans = output.rename(columns = {'date' : 'date_loan'}, inplace = False)\n",
    "loans"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e903fea9",
   "metadata": {},
   "source": [
    "### Deal with the Accounts Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "875be7e9",
   "metadata": {},
   "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>account_id</th>\n",
       "      <th>district_id_account</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date_account</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3818</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>704</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2378</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2632</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4495</th>\n",
       "      <td>124</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "      <td>1997-12-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4496</th>\n",
       "      <td>3958</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>1997-12-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4497</th>\n",
       "      <td>777</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>1997-12-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4498</th>\n",
       "      <td>1573</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>1997-12-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4499</th>\n",
       "      <td>3276</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1997-12-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4500 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      account_id  district_id_account  frequency date_account\n",
       "0            576                   55          0   1993-01-01\n",
       "1           3818                   74          0   1993-01-01\n",
       "2            704                   55          0   1993-01-01\n",
       "3           2378                   16          0   1993-01-01\n",
       "4           2632                   24          0   1993-01-02\n",
       "...          ...                  ...        ...          ...\n",
       "4495         124                   55          0   1997-12-28\n",
       "4496        3958                   59          0   1997-12-28\n",
       "4497         777                   30          0   1997-12-28\n",
       "4498        1573                   63          0   1997-12-29\n",
       "4499        3276                    1          0   1997-12-29\n",
       "\n",
       "[4500 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert Date from numerical to a Date format.\n",
    "output = pd.read_csv('BankData/account.csv', delimiter = \";\")\n",
    "output['date'] = 19000000 + output['date']\n",
    "output['date'] = pd.to_datetime(output['date'], format = '%Y%m%d')\n",
    "\n",
    "# Change the Date variable\n",
    "output.rename(columns = {'date' : 'date_account', 'district_id' : 'district_id_account'}, inplace = True)\n",
    "output.replace(to_replace = 'monthly issuance', value = 0, inplace = True)\n",
    "output.replace(to_replace = 'issuance after transaction', value = 1, inplace = True)\n",
    "output.replace(to_replace = 'weekly issuance', value = 2, inplace = True)\n",
    "accounts = output\n",
    "accounts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1b6eeeb",
   "metadata": {},
   "source": [
    "### Merge the Accounts and Loans tables Together (Inner Join)\n",
    "\n",
    "#### Note: This step also creates the difference between the loan date and the account date (in days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b203c503",
   "metadata": {
    "scrolled": true
   },
   "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>account_id</th>\n",
       "      <th>date_loan</th>\n",
       "      <th>amount</th>\n",
       "      <th>duration</th>\n",
       "      <th>payments</th>\n",
       "      <th>status</th>\n",
       "      <th>district_id_account</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date_account</th>\n",
       "      <th>days_between</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5314</td>\n",
       "      <td>1787</td>\n",
       "      <td>1993-07-05</td>\n",
       "      <td>96396</td>\n",
       "      <td>12</td>\n",
       "      <td>8033</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-03-22</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5316</td>\n",
       "      <td>1801</td>\n",
       "      <td>1993-07-11</td>\n",
       "      <td>165960</td>\n",
       "      <td>36</td>\n",
       "      <td>4610</td>\n",
       "      <td>successful</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-13</td>\n",
       "      <td>148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6863</td>\n",
       "      <td>9188</td>\n",
       "      <td>1993-07-28</td>\n",
       "      <td>127080</td>\n",
       "      <td>60</td>\n",
       "      <td>2118</td>\n",
       "      <td>successful</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-08</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5325</td>\n",
       "      <td>1843</td>\n",
       "      <td>1993-08-03</td>\n",
       "      <td>105804</td>\n",
       "      <td>36</td>\n",
       "      <td>2939</td>\n",
       "      <td>successful</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-30</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7240</td>\n",
       "      <td>11013</td>\n",
       "      <td>1993-09-06</td>\n",
       "      <td>274740</td>\n",
       "      <td>60</td>\n",
       "      <td>4579</td>\n",
       "      <td>successful</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-02-14</td>\n",
       "      <td>204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <th>323</th>\n",
       "      <td>6818</td>\n",
       "      <td>9030</td>\n",
       "      <td>1996-12-12</td>\n",
       "      <td>155616</td>\n",
       "      <td>48</td>\n",
       "      <td>3242</td>\n",
       "      <td>successful</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-01-21</td>\n",
       "      <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>5625</td>\n",
       "      <td>3189</td>\n",
       "      <td>1996-12-15</td>\n",
       "      <td>222180</td>\n",
       "      <td>60</td>\n",
       "      <td>3703</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-11-29</td>\n",
       "      <td>382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>6805</td>\n",
       "      <td>8972</td>\n",
       "      <td>1996-12-21</td>\n",
       "      <td>45024</td>\n",
       "      <td>48</td>\n",
       "      <td>938</td>\n",
       "      <td>successful</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1996-05-21</td>\n",
       "      <td>214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>7233</td>\n",
       "      <td>10963</td>\n",
       "      <td>1996-12-25</td>\n",
       "      <td>115812</td>\n",
       "      <td>36</td>\n",
       "      <td>3217</td>\n",
       "      <td>successful</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-05-20</td>\n",
       "      <td>585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>7308</td>\n",
       "      <td>11362</td>\n",
       "      <td>1996-12-27</td>\n",
       "      <td>129408</td>\n",
       "      <td>24</td>\n",
       "      <td>5392</td>\n",
       "      <td>successful</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-10-14</td>\n",
       "      <td>440</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     loan_id  account_id  date_loan  amount  duration  payments        status  \\\n",
       "0       5314        1787 1993-07-05   96396        12      8033  unsuccessful   \n",
       "1       5316        1801 1993-07-11  165960        36      4610    successful   \n",
       "2       6863        9188 1993-07-28  127080        60      2118    successful   \n",
       "3       5325        1843 1993-08-03  105804        36      2939    successful   \n",
       "4       7240       11013 1993-09-06  274740        60      4579    successful   \n",
       "..       ...         ...        ...     ...       ...       ...           ...   \n",
       "323     6818        9030 1996-12-12  155616        48      3242    successful   \n",
       "324     5625        3189 1996-12-15  222180        60      3703  unsuccessful   \n",
       "325     6805        8972 1996-12-21   45024        48       938    successful   \n",
       "326     7233       10963 1996-12-25  115812        36      3217    successful   \n",
       "327     7308       11362 1996-12-27  129408        24      5392    successful   \n",
       "\n",
       "     district_id_account  frequency date_account  days_between  \n",
       "0                     30          2   1993-03-22           105  \n",
       "1                     46          0   1993-02-13           148  \n",
       "2                     45          0   1993-02-08           170  \n",
       "3                     12          0   1993-01-30           185  \n",
       "4                      1          2   1993-02-14           204  \n",
       "..                   ...        ...          ...           ...  \n",
       "323                   72          0   1995-01-21           691  \n",
       "324                   29          0   1995-11-29           382  \n",
       "325                   70          0   1996-05-21           214  \n",
       "326                   16          0   1995-05-20           585  \n",
       "327                   67          0   1995-10-14           440  \n",
       "\n",
       "[328 rows x 11 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loanAccounts = pd.merge(loans,accounts,on='account_id')\n",
    "loanAccounts['days_between'] = (loanAccounts['date_loan'] - loanAccounts['date_account']).dt.days  # Get days difference\n",
    "loanAccounts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "485cc670",
   "metadata": {},
   "source": [
    "### Set up the Transactions Table (Compiled information)\n",
    "\n",
    "##### This process usually takes some time (aprox 2-5 minutes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "20ecc8ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Programs\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3441: DtypeWarning: Columns (8) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\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>transaction_count</th>\n",
       "      <th>max_amount</th>\n",
       "      <th>min_amount</th>\n",
       "      <th>finalBalance</th>\n",
       "      <th>deltaBalance</th>\n",
       "      <th>creditCount</th>\n",
       "      <th>withdrawalCount</th>\n",
       "      <th>amount_std</th>\n",
       "      <th>balance_std</th>\n",
       "      <th>amount_mean</th>\n",
       "      <th>balance_mean</th>\n",
       "      <th>credit_std</th>\n",
       "      <th>credit_mean</th>\n",
       "      <th>withdrawal_std</th>\n",
       "      <th>withdrawal_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5270.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>67124.0</td>\n",
       "      <td>14.6</td>\n",
       "      <td>103745.7</td>\n",
       "      <td>-27900.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>19189.33</td>\n",
       "      <td>24870.23</td>\n",
       "      <td>17600.36</td>\n",
       "      <td>69954.57</td>\n",
       "      <td>24223.20</td>\n",
       "      <td>23558.29</td>\n",
       "      <td>14048.02</td>\n",
       "      <td>12885.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11265.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>17920.0</td>\n",
       "      <td>14.6</td>\n",
       "      <td>21029.0</td>\n",
       "      <td>-1900.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3862.23</td>\n",
       "      <td>6652.64</td>\n",
       "      <td>3714.90</td>\n",
       "      <td>22198.18</td>\n",
       "      <td>3880.56</td>\n",
       "      <td>4304.04</td>\n",
       "      <td>3848.85</td>\n",
       "      <td>3152.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10364.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>21739.5</td>\n",
       "      <td>14.6</td>\n",
       "      <td>49083.3</td>\n",
       "      <td>-43479.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6350.49</td>\n",
       "      <td>10171.03</td>\n",
       "      <td>5957.54</td>\n",
       "      <td>34272.21</td>\n",
       "      <td>7695.53</td>\n",
       "      <td>7551.74</td>\n",
       "      <td>4615.54</td>\n",
       "      <td>4562.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3834.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>14.6</td>\n",
       "      <td>28531.7</td>\n",
       "      <td>-414000.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>123.0</td>\n",
       "      <td>5017.81</td>\n",
       "      <td>8912.02</td>\n",
       "      <td>3618.33</td>\n",
       "      <td>23771.97</td>\n",
       "      <td>7256.61</td>\n",
       "      <td>6742.35</td>\n",
       "      <td>3075.70</td>\n",
       "      <td>2399.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9307.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>51700.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>39204.7</td>\n",
       "      <td>-59500.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>11103.84</td>\n",
       "      <td>20303.82</td>\n",
       "      <td>6720.90</td>\n",
       "      <td>36080.39</td>\n",
       "      <td>14136.50</td>\n",
       "      <td>7938.84</td>\n",
       "      <td>7659.41</td>\n",
       "      <td>5228.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <th>3365</th>\n",
       "      <td>2362.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>700.00</td>\n",
       "      <td>700.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>700.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3366</th>\n",
       "      <td>528.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>300.00</td>\n",
       "      <td>300.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>300.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3367</th>\n",
       "      <td>3190.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1000.00</td>\n",
       "      <td>1000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3368</th>\n",
       "      <td>1988.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>300.00</td>\n",
       "      <td>300.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>300.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3369</th>\n",
       "      <td>1390.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>400.00</td>\n",
       "      <td>400.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>400.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3370 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      account_id  transaction_count  max_amount  min_amount  finalBalance  \\\n",
       "0         5270.0               53.0     67124.0        14.6      103745.7   \n",
       "1        11265.0               43.0     17920.0        14.6       21029.0   \n",
       "2        10364.0               45.0     21739.5        14.6       49083.3   \n",
       "3         3834.0              171.0     24000.0        14.6       28531.7   \n",
       "4         9307.0              143.0     51700.0         0.8       39204.7   \n",
       "...          ...                ...         ...         ...           ...   \n",
       "3365      2362.0                1.0       700.0       700.0         700.0   \n",
       "3366       528.0                1.0       300.0       300.0         300.0   \n",
       "3367      3190.0                1.0      1000.0      1000.0        1000.0   \n",
       "3368      1988.0                1.0       300.0       300.0         300.0   \n",
       "3369      1390.0                1.0       400.0       400.0         400.0   \n",
       "\n",
       "      deltaBalance  creditCount  withdrawalCount  amount_std  balance_std  \\\n",
       "0         -27900.0         22.0             28.0    19189.33     24870.23   \n",
       "1          -1900.0         21.0             22.0     3862.23      6652.64   \n",
       "2         -43479.0         21.0             24.0     6350.49     10171.03   \n",
       "3        -414000.0         48.0            123.0     5017.81      8912.02   \n",
       "4         -59500.0         63.0             76.0    11103.84     20303.82   \n",
       "...            ...          ...              ...         ...          ...   \n",
       "3365         700.0          1.0              0.0        0.00         0.00   \n",
       "3366         300.0          1.0              0.0        0.00         0.00   \n",
       "3367        1000.0          1.0              0.0        0.00         0.00   \n",
       "3368         300.0          1.0              0.0        0.00         0.00   \n",
       "3369         400.0          1.0              0.0        0.00         0.00   \n",
       "\n",
       "      amount_mean  balance_mean  credit_std  credit_mean  withdrawal_std  \\\n",
       "0        17600.36      69954.57    24223.20     23558.29        14048.02   \n",
       "1         3714.90      22198.18     3880.56      4304.04         3848.85   \n",
       "2         5957.54      34272.21     7695.53      7551.74         4615.54   \n",
       "3         3618.33      23771.97     7256.61      6742.35         3075.70   \n",
       "4         6720.90      36080.39    14136.50      7938.84         7659.41   \n",
       "...           ...           ...         ...          ...             ...   \n",
       "3365       700.00        700.00        0.00       700.00            0.00   \n",
       "3366       300.00        300.00        0.00       300.00            0.00   \n",
       "3367      1000.00       1000.00        0.00      1000.00            0.00   \n",
       "3368       300.00        300.00        0.00       300.00            0.00   \n",
       "3369       400.00        400.00        0.00       400.00            0.00   \n",
       "\n",
       "      withdrawal_mean  \n",
       "0            12885.27  \n",
       "1             3152.54  \n",
       "2             4562.63  \n",
       "3             2399.20  \n",
       "4             5228.34  \n",
       "...               ...  \n",
       "3365             0.00  \n",
       "3366             0.00  \n",
       "3367             0.00  \n",
       "3368             0.00  \n",
       "3369             0.00  \n",
       "\n",
       "[3370 rows x 16 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output = pd.read_csv('BankData/trans_' + fileName + '.csv', delimiter=\";\")\n",
    "\n",
    "# The columns that will be present in the final Transactions Table\n",
    "column_names = [\"account_id\", \"transaction_count\", \"max_amount\", \"min_amount\", \"finalBalance\", \"deltaBalance\", \"creditCount\", \"withdrawalCount\", 'amount_std', 'balance_std', 'amount_mean', 'balance_mean', 'credit_std', 'credit_mean', 'withdrawal_std', 'withdrawal_mean']\n",
    "transactions = pd.DataFrame(columns = column_names)\n",
    "\n",
    "# ########################################################################################################\n",
    "\n",
    "\n",
    "def acquireDelta(query):\n",
    "    delta = 0\n",
    "    for index, row in query.iterrows():\n",
    "        if row['type'] == 'credit':\n",
    "            delta = delta + lockRow.iloc[0]['amount']\n",
    "        else:\n",
    "            delta = delta - lockRow.iloc[0]['amount']\n",
    "    return round(delta, 2)\n",
    "\n",
    "\n",
    "# ########################################################################################################\n",
    "\n",
    "# Acquire ALL distinct clients\n",
    "\n",
    "account_id = output['account_id'].unique().tolist()\n",
    "\n",
    "# For each client acquire the remaining variables\n",
    "for accID in account_id:\n",
    "    query = output.loc[output['account_id'] == accID]      # Returns the rows whose account_id == accID\n",
    "    transactionCount = len(query.index)                  # Number of transactions per account\n",
    "    max_amount = query['amount'].max()                   # Highest transaction amount ever done\n",
    "    min_amount = query['amount'].min()                   # Smallest transaction amount ever done\n",
    "    \n",
    "    # Standard Deviation + Mean calculation\n",
    "    \n",
    "    amountSTD = 0\n",
    "    balanceSTD = 0\n",
    "    amountMean = 0\n",
    "    balanceMean = 0\n",
    "    if( len(query) > 1 ):\n",
    "        amountSTD = round(query['amount'].std(), 2)\n",
    "        balanceSTD = round(query['balance'].std(), 2)\n",
    "        amountMean = round(query['amount'].mean(), 2)\n",
    "        balanceMean = round(query['balance'].mean(), 2)\n",
    "    if( len(query) == 1 ):\n",
    "        balanceMean = round(query['balance'].iloc[0], 2)\n",
    "        amountMean = round(query['amount'].iloc[0], 2)\n",
    "    \n",
    "    # Credit Deviation + Mean calculation\n",
    "    queryR = query.loc[query['type'] == 'credit']\n",
    "    creditSTD = 0\n",
    "    creditMean = 0\n",
    "    if( len(queryR) > 1 ):\n",
    "        creditSTD = round(queryR['amount'].std(), 2)\n",
    "        creditMean = round(queryR['amount'].mean(), 2)\n",
    "    if( len(queryR) == 1 ):\n",
    "        creditMean = round(queryR['amount'].iloc[0], 2)\n",
    "    \n",
    "    # Withdrawal Deviation + Mean calculation\n",
    "    queryR = query.loc[query['type'] == 'withdrawal']\n",
    "    withdrawalSTD = 0\n",
    "    withdrawalMean = 0\n",
    "    if( len(queryR) > 1 ):\n",
    "        withdrawalSTD = round(queryR['amount'].std(), 2)\n",
    "        withdrawalMean = round(queryR['amount'].mean(), 2)\n",
    "    if( len(queryR) == 1 ):\n",
    "        withdrawalMean = round(queryR['amount'].iloc[0], 2)\n",
    "    \n",
    "    # The following code acquires the finalBalance (smallest date)\n",
    "    lockRow = query[query.date == query.date.max()]    # Get the row with the highest date value\n",
    "    finalBalance = lockRow.iloc[0]['balance']\n",
    "    \n",
    "    # Get the \"Delta\" of each account\n",
    "    deltaBalance = acquireDelta(query)\n",
    "    \n",
    "    \n",
    "    # Get Credit and Withdrawal amonunts\n",
    "    withdrawalCount = 0\n",
    "    creditCount = 0\n",
    "    \n",
    "    if 'credit' in query.type.value_counts():\n",
    "        creditCount = query.type.value_counts().credit.item()\n",
    "    \n",
    "    if 'withdrawal' in query.type.value_counts():\n",
    "        withdrawalCount = query.type.value_counts().withdrawal.item()\n",
    "    \n",
    "    storeData = {'account_id':accID, \n",
    "                 'transaction_count':transactionCount, \n",
    "                 'max_amount':max_amount,\n",
    "                 'min_amount':min_amount,\n",
    "                 'finalBalance':finalBalance,\n",
    "                 'deltaBalance':deltaBalance,\n",
    "                 'creditCount':creditCount,\n",
    "                 'withdrawalCount':withdrawalCount,\n",
    "                 'amount_std' : amountSTD, \n",
    "                 'balance_std' : balanceSTD, \n",
    "                 'amount_mean' : amountMean, \n",
    "                 'balance_mean': balanceMean,\n",
    "                 'credit_std' : creditSTD, \n",
    "                 'credit_mean' : creditMean, \n",
    "                 'withdrawal_std' : withdrawalSTD, \n",
    "                 'withdrawal_mean' : withdrawalMean}\n",
    "    \n",
    "    transactions = transactions.append(storeData, ignore_index=True)\n",
    "\n",
    "transactions\n",
    "#dataframe.to_csv(\"data/processed/test_transactions.csv\", index=False, sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "adda242a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join the Transactions table with the Accounts + Loans table\n",
    "\n",
    "compiledData = pd.merge(loanAccounts, transactions, on='account_id')\n",
    "compiledData\n",
    "\n",
    "# Clean memory ::\n",
    "loanAccounts = None\n",
    "transactions = None\n",
    "account_id = None\n",
    "accounts = None\n",
    "loans = None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c906fb8",
   "metadata": {},
   "source": [
    "### Disposition Table handler + Card Table handler\n",
    "\n",
    "#### This step acquires the number of owners associated to each Account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1edf450c",
   "metadata": {},
   "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>disp_id</th>\n",
       "      <th>client_id</th>\n",
       "      <th>account_id</th>\n",
       "      <th>num_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4495</th>\n",
       "      <td>13623</td>\n",
       "      <td>13931</td>\n",
       "      <td>11333</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4496</th>\n",
       "      <td>13647</td>\n",
       "      <td>13955</td>\n",
       "      <td>11349</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4497</th>\n",
       "      <td>13660</td>\n",
       "      <td>13968</td>\n",
       "      <td>11359</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4498</th>\n",
       "      <td>13663</td>\n",
       "      <td>13971</td>\n",
       "      <td>11362</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4499</th>\n",
       "      <td>13690</td>\n",
       "      <td>13998</td>\n",
       "      <td>11382</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4500 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      disp_id  client_id  account_id  num_users\n",
       "0           1          1           1          1\n",
       "1           2          2           2          2\n",
       "2           4          4           3          2\n",
       "3           6          6           4          1\n",
       "4           7          7           5          1\n",
       "...       ...        ...         ...        ...\n",
       "4495    13623      13931       11333          1\n",
       "4496    13647      13955       11349          2\n",
       "4497    13660      13968       11359          1\n",
       "4498    13663      13971       11362          1\n",
       "4499    13690      13998       11382          1\n",
       "\n",
       "[4500 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output = pd.read_csv('BankData/disp.csv', delimiter=\";\")\n",
    "ownerListing = output.loc[output['type'] == 'OWNER']\n",
    "\n",
    "freq = output['account_id'].value_counts().rename_axis('account_id').reset_index(name='num_users')\n",
    "freq.sort_values('account_id')\n",
    "\n",
    "disp = pd.merge(ownerListing, freq, on='account_id')\n",
    "disp.drop(columns = ['type'], inplace = True)\n",
    "disp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5a9a6db6",
   "metadata": {},
   "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>account_id</th>\n",
       "      <th>date_loan</th>\n",
       "      <th>amount</th>\n",
       "      <th>duration</th>\n",
       "      <th>payments</th>\n",
       "      <th>status</th>\n",
       "      <th>district_id_account</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date_account</th>\n",
       "      <th>...</th>\n",
       "      <th>balance_std</th>\n",
       "      <th>amount_mean</th>\n",
       "      <th>balance_mean</th>\n",
       "      <th>credit_std</th>\n",
       "      <th>credit_mean</th>\n",
       "      <th>withdrawal_std</th>\n",
       "      <th>withdrawal_mean</th>\n",
       "      <th>disp_id</th>\n",
       "      <th>client_id</th>\n",
       "      <th>num_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5314</td>\n",
       "      <td>1787</td>\n",
       "      <td>1993-07-05</td>\n",
       "      <td>96396</td>\n",
       "      <td>12</td>\n",
       "      <td>8033</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-03-22</td>\n",
       "      <td>...</td>\n",
       "      <td>8330.87</td>\n",
       "      <td>5025.00</td>\n",
       "      <td>12250.00</td>\n",
       "      <td>3774.81</td>\n",
       "      <td>5025.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2166</td>\n",
       "      <td>2166</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5316</td>\n",
       "      <td>1801</td>\n",
       "      <td>1993-07-11</td>\n",
       "      <td>165960</td>\n",
       "      <td>36</td>\n",
       "      <td>4610</td>\n",
       "      <td>successful</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-13</td>\n",
       "      <td>...</td>\n",
       "      <td>29122.06</td>\n",
       "      <td>11015.64</td>\n",
       "      <td>52083.86</td>\n",
       "      <td>13998.09</td>\n",
       "      <td>13523.16</td>\n",
       "      <td>14855.93</td>\n",
       "      <td>9111.27</td>\n",
       "      <td>2181</td>\n",
       "      <td>2181</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6863</td>\n",
       "      <td>9188</td>\n",
       "      <td>1993-07-28</td>\n",
       "      <td>127080</td>\n",
       "      <td>60</td>\n",
       "      <td>2118</td>\n",
       "      <td>successful</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-08</td>\n",
       "      <td>...</td>\n",
       "      <td>11520.18</td>\n",
       "      <td>5417.46</td>\n",
       "      <td>30060.95</td>\n",
       "      <td>6801.23</td>\n",
       "      <td>5009.73</td>\n",
       "      <td>5390.57</td>\n",
       "      <td>6097.00</td>\n",
       "      <td>11006</td>\n",
       "      <td>11314</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5325</td>\n",
       "      <td>1843</td>\n",
       "      <td>1993-08-03</td>\n",
       "      <td>105804</td>\n",
       "      <td>36</td>\n",
       "      <td>2939</td>\n",
       "      <td>successful</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-30</td>\n",
       "      <td>...</td>\n",
       "      <td>14151.26</td>\n",
       "      <td>8253.08</td>\n",
       "      <td>41297.48</td>\n",
       "      <td>9818.59</td>\n",
       "      <td>9254.60</td>\n",
       "      <td>4919.10</td>\n",
       "      <td>7168.10</td>\n",
       "      <td>2235</td>\n",
       "      <td>2235</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7240</td>\n",
       "      <td>11013</td>\n",
       "      <td>1993-09-06</td>\n",
       "      <td>274740</td>\n",
       "      <td>60</td>\n",
       "      <td>4579</td>\n",
       "      <td>successful</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-02-14</td>\n",
       "      <td>...</td>\n",
       "      <td>25256.67</td>\n",
       "      <td>18945.97</td>\n",
       "      <td>57188.21</td>\n",
       "      <td>24264.23</td>\n",
       "      <td>21255.93</td>\n",
       "      <td>13981.92</td>\n",
       "      <td>16801.00</td>\n",
       "      <td>13231</td>\n",
       "      <td>13539</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <th>323</th>\n",
       "      <td>6818</td>\n",
       "      <td>9030</td>\n",
       "      <td>1996-12-12</td>\n",
       "      <td>155616</td>\n",
       "      <td>48</td>\n",
       "      <td>3242</td>\n",
       "      <td>successful</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-01-21</td>\n",
       "      <td>...</td>\n",
       "      <td>11044.49</td>\n",
       "      <td>4895.97</td>\n",
       "      <td>44197.51</td>\n",
       "      <td>9143.30</td>\n",
       "      <td>6448.58</td>\n",
       "      <td>5002.16</td>\n",
       "      <td>3830.46</td>\n",
       "      <td>10813</td>\n",
       "      <td>11121</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>5625</td>\n",
       "      <td>3189</td>\n",
       "      <td>1996-12-15</td>\n",
       "      <td>222180</td>\n",
       "      <td>60</td>\n",
       "      <td>3703</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-11-29</td>\n",
       "      <td>...</td>\n",
       "      <td>26510.56</td>\n",
       "      <td>11725.49</td>\n",
       "      <td>55230.44</td>\n",
       "      <td>15547.01</td>\n",
       "      <td>13417.56</td>\n",
       "      <td>12458.77</td>\n",
       "      <td>10109.59</td>\n",
       "      <td>3855</td>\n",
       "      <td>3855</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>6805</td>\n",
       "      <td>8972</td>\n",
       "      <td>1996-12-21</td>\n",
       "      <td>45024</td>\n",
       "      <td>48</td>\n",
       "      <td>938</td>\n",
       "      <td>successful</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1996-05-21</td>\n",
       "      <td>...</td>\n",
       "      <td>13151.51</td>\n",
       "      <td>7779.82</td>\n",
       "      <td>41994.91</td>\n",
       "      <td>12066.49</td>\n",
       "      <td>8544.93</td>\n",
       "      <td>7718.59</td>\n",
       "      <td>6974.43</td>\n",
       "      <td>10742</td>\n",
       "      <td>11050</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>7233</td>\n",
       "      <td>10963</td>\n",
       "      <td>1996-12-25</td>\n",
       "      <td>115812</td>\n",
       "      <td>36</td>\n",
       "      <td>3217</td>\n",
       "      <td>successful</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-05-20</td>\n",
       "      <td>...</td>\n",
       "      <td>21971.16</td>\n",
       "      <td>13280.08</td>\n",
       "      <td>56646.52</td>\n",
       "      <td>17170.85</td>\n",
       "      <td>16554.99</td>\n",
       "      <td>12640.02</td>\n",
       "      <td>10667.40</td>\n",
       "      <td>13172</td>\n",
       "      <td>13480</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>7308</td>\n",
       "      <td>11362</td>\n",
       "      <td>1996-12-27</td>\n",
       "      <td>129408</td>\n",
       "      <td>24</td>\n",
       "      <td>5392</td>\n",
       "      <td>successful</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-10-14</td>\n",
       "      <td>...</td>\n",
       "      <td>11709.14</td>\n",
       "      <td>4550.30</td>\n",
       "      <td>30869.78</td>\n",
       "      <td>9224.07</td>\n",
       "      <td>8811.76</td>\n",
       "      <td>3683.66</td>\n",
       "      <td>2965.92</td>\n",
       "      <td>13663</td>\n",
       "      <td>13971</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     loan_id  account_id  date_loan  amount  duration  payments        status  \\\n",
       "0       5314        1787 1993-07-05   96396        12      8033  unsuccessful   \n",
       "1       5316        1801 1993-07-11  165960        36      4610    successful   \n",
       "2       6863        9188 1993-07-28  127080        60      2118    successful   \n",
       "3       5325        1843 1993-08-03  105804        36      2939    successful   \n",
       "4       7240       11013 1993-09-06  274740        60      4579    successful   \n",
       "..       ...         ...        ...     ...       ...       ...           ...   \n",
       "323     6818        9030 1996-12-12  155616        48      3242    successful   \n",
       "324     5625        3189 1996-12-15  222180        60      3703  unsuccessful   \n",
       "325     6805        8972 1996-12-21   45024        48       938    successful   \n",
       "326     7233       10963 1996-12-25  115812        36      3217    successful   \n",
       "327     7308       11362 1996-12-27  129408        24      5392    successful   \n",
       "\n",
       "     district_id_account  frequency date_account  ...  balance_std  \\\n",
       "0                     30          2   1993-03-22  ...      8330.87   \n",
       "1                     46          0   1993-02-13  ...     29122.06   \n",
       "2                     45          0   1993-02-08  ...     11520.18   \n",
       "3                     12          0   1993-01-30  ...     14151.26   \n",
       "4                      1          2   1993-02-14  ...     25256.67   \n",
       "..                   ...        ...          ...  ...          ...   \n",
       "323                   72          0   1995-01-21  ...     11044.49   \n",
       "324                   29          0   1995-11-29  ...     26510.56   \n",
       "325                   70          0   1996-05-21  ...     13151.51   \n",
       "326                   16          0   1995-05-20  ...     21971.16   \n",
       "327                   67          0   1995-10-14  ...     11709.14   \n",
       "\n",
       "     amount_mean  balance_mean  credit_std  credit_mean  withdrawal_std  \\\n",
       "0        5025.00      12250.00     3774.81      5025.00            0.00   \n",
       "1       11015.64      52083.86    13998.09     13523.16        14855.93   \n",
       "2        5417.46      30060.95     6801.23      5009.73         5390.57   \n",
       "3        8253.08      41297.48     9818.59      9254.60         4919.10   \n",
       "4       18945.97      57188.21    24264.23     21255.93        13981.92   \n",
       "..           ...           ...         ...          ...             ...   \n",
       "323      4895.97      44197.51     9143.30      6448.58         5002.16   \n",
       "324     11725.49      55230.44    15547.01     13417.56        12458.77   \n",
       "325      7779.82      41994.91    12066.49      8544.93         7718.59   \n",
       "326     13280.08      56646.52    17170.85     16554.99        12640.02   \n",
       "327      4550.30      30869.78     9224.07      8811.76         3683.66   \n",
       "\n",
       "     withdrawal_mean  disp_id  client_id  num_users  \n",
       "0               0.00     2166       2166          1  \n",
       "1            9111.27     2181       2181          1  \n",
       "2            6097.00    11006      11314          1  \n",
       "3            7168.10     2235       2235          1  \n",
       "4           16801.00    13231      13539          1  \n",
       "..               ...      ...        ...        ...  \n",
       "323          3830.46    10813      11121          2  \n",
       "324         10109.59     3855       3855          1  \n",
       "325          6974.43    10742      11050          1  \n",
       "326         10667.40    13172      13480          1  \n",
       "327          2965.92    13663      13971          1  \n",
       "\n",
       "[328 rows x 29 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge Disposition Table with the other merged contents:\n",
    "\n",
    "compiledData = pd.merge(compiledData, disp, on='account_id')\n",
    "compiledData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "816cf0f3",
   "metadata": {},
   "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>account_id</th>\n",
       "      <th>date_loan</th>\n",
       "      <th>amount</th>\n",
       "      <th>duration</th>\n",
       "      <th>payments</th>\n",
       "      <th>status</th>\n",
       "      <th>district_id_account</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date_account</th>\n",
       "      <th>...</th>\n",
       "      <th>amount_mean</th>\n",
       "      <th>balance_mean</th>\n",
       "      <th>credit_std</th>\n",
       "      <th>credit_mean</th>\n",
       "      <th>withdrawal_std</th>\n",
       "      <th>withdrawal_mean</th>\n",
       "      <th>disp_id</th>\n",
       "      <th>client_id</th>\n",
       "      <th>num_users</th>\n",
       "      <th>card_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5314</td>\n",
       "      <td>1787</td>\n",
       "      <td>1993-07-05</td>\n",
       "      <td>96396</td>\n",
       "      <td>12</td>\n",
       "      <td>8033</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-03-22</td>\n",
       "      <td>...</td>\n",
       "      <td>5025.00</td>\n",
       "      <td>12250.00</td>\n",
       "      <td>3774.81</td>\n",
       "      <td>5025.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2166</td>\n",
       "      <td>2166</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5316</td>\n",
       "      <td>1801</td>\n",
       "      <td>1993-07-11</td>\n",
       "      <td>165960</td>\n",
       "      <td>36</td>\n",
       "      <td>4610</td>\n",
       "      <td>successful</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-13</td>\n",
       "      <td>...</td>\n",
       "      <td>11015.64</td>\n",
       "      <td>52083.86</td>\n",
       "      <td>13998.09</td>\n",
       "      <td>13523.16</td>\n",
       "      <td>14855.93</td>\n",
       "      <td>9111.27</td>\n",
       "      <td>2181</td>\n",
       "      <td>2181</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6863</td>\n",
       "      <td>9188</td>\n",
       "      <td>1993-07-28</td>\n",
       "      <td>127080</td>\n",
       "      <td>60</td>\n",
       "      <td>2118</td>\n",
       "      <td>successful</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-08</td>\n",
       "      <td>...</td>\n",
       "      <td>5417.46</td>\n",
       "      <td>30060.95</td>\n",
       "      <td>6801.23</td>\n",
       "      <td>5009.73</td>\n",
       "      <td>5390.57</td>\n",
       "      <td>6097.00</td>\n",
       "      <td>11006</td>\n",
       "      <td>11314</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5325</td>\n",
       "      <td>1843</td>\n",
       "      <td>1993-08-03</td>\n",
       "      <td>105804</td>\n",
       "      <td>36</td>\n",
       "      <td>2939</td>\n",
       "      <td>successful</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-30</td>\n",
       "      <td>...</td>\n",
       "      <td>8253.08</td>\n",
       "      <td>41297.48</td>\n",
       "      <td>9818.59</td>\n",
       "      <td>9254.60</td>\n",
       "      <td>4919.10</td>\n",
       "      <td>7168.10</td>\n",
       "      <td>2235</td>\n",
       "      <td>2235</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7240</td>\n",
       "      <td>11013</td>\n",
       "      <td>1993-09-06</td>\n",
       "      <td>274740</td>\n",
       "      <td>60</td>\n",
       "      <td>4579</td>\n",
       "      <td>successful</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-02-14</td>\n",
       "      <td>...</td>\n",
       "      <td>18945.97</td>\n",
       "      <td>57188.21</td>\n",
       "      <td>24264.23</td>\n",
       "      <td>21255.93</td>\n",
       "      <td>13981.92</td>\n",
       "      <td>16801.00</td>\n",
       "      <td>13231</td>\n",
       "      <td>13539</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <th>323</th>\n",
       "      <td>6818</td>\n",
       "      <td>9030</td>\n",
       "      <td>1996-12-12</td>\n",
       "      <td>155616</td>\n",
       "      <td>48</td>\n",
       "      <td>3242</td>\n",
       "      <td>successful</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-01-21</td>\n",
       "      <td>...</td>\n",
       "      <td>4895.97</td>\n",
       "      <td>44197.51</td>\n",
       "      <td>9143.30</td>\n",
       "      <td>6448.58</td>\n",
       "      <td>5002.16</td>\n",
       "      <td>3830.46</td>\n",
       "      <td>10813</td>\n",
       "      <td>11121</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>5625</td>\n",
       "      <td>3189</td>\n",
       "      <td>1996-12-15</td>\n",
       "      <td>222180</td>\n",
       "      <td>60</td>\n",
       "      <td>3703</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-11-29</td>\n",
       "      <td>...</td>\n",
       "      <td>11725.49</td>\n",
       "      <td>55230.44</td>\n",
       "      <td>15547.01</td>\n",
       "      <td>13417.56</td>\n",
       "      <td>12458.77</td>\n",
       "      <td>10109.59</td>\n",
       "      <td>3855</td>\n",
       "      <td>3855</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>6805</td>\n",
       "      <td>8972</td>\n",
       "      <td>1996-12-21</td>\n",
       "      <td>45024</td>\n",
       "      <td>48</td>\n",
       "      <td>938</td>\n",
       "      <td>successful</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1996-05-21</td>\n",
       "      <td>...</td>\n",
       "      <td>7779.82</td>\n",
       "      <td>41994.91</td>\n",
       "      <td>12066.49</td>\n",
       "      <td>8544.93</td>\n",
       "      <td>7718.59</td>\n",
       "      <td>6974.43</td>\n",
       "      <td>10742</td>\n",
       "      <td>11050</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>7233</td>\n",
       "      <td>10963</td>\n",
       "      <td>1996-12-25</td>\n",
       "      <td>115812</td>\n",
       "      <td>36</td>\n",
       "      <td>3217</td>\n",
       "      <td>successful</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-05-20</td>\n",
       "      <td>...</td>\n",
       "      <td>13280.08</td>\n",
       "      <td>56646.52</td>\n",
       "      <td>17170.85</td>\n",
       "      <td>16554.99</td>\n",
       "      <td>12640.02</td>\n",
       "      <td>10667.40</td>\n",
       "      <td>13172</td>\n",
       "      <td>13480</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>7308</td>\n",
       "      <td>11362</td>\n",
       "      <td>1996-12-27</td>\n",
       "      <td>129408</td>\n",
       "      <td>24</td>\n",
       "      <td>5392</td>\n",
       "      <td>successful</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-10-14</td>\n",
       "      <td>...</td>\n",
       "      <td>4550.30</td>\n",
       "      <td>30869.78</td>\n",
       "      <td>9224.07</td>\n",
       "      <td>8811.76</td>\n",
       "      <td>3683.66</td>\n",
       "      <td>2965.92</td>\n",
       "      <td>13663</td>\n",
       "      <td>13971</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     loan_id  account_id  date_loan  amount  duration  payments        status  \\\n",
       "0       5314        1787 1993-07-05   96396        12      8033  unsuccessful   \n",
       "1       5316        1801 1993-07-11  165960        36      4610    successful   \n",
       "2       6863        9188 1993-07-28  127080        60      2118    successful   \n",
       "3       5325        1843 1993-08-03  105804        36      2939    successful   \n",
       "4       7240       11013 1993-09-06  274740        60      4579    successful   \n",
       "..       ...         ...        ...     ...       ...       ...           ...   \n",
       "323     6818        9030 1996-12-12  155616        48      3242    successful   \n",
       "324     5625        3189 1996-12-15  222180        60      3703  unsuccessful   \n",
       "325     6805        8972 1996-12-21   45024        48       938    successful   \n",
       "326     7233       10963 1996-12-25  115812        36      3217    successful   \n",
       "327     7308       11362 1996-12-27  129408        24      5392    successful   \n",
       "\n",
       "     district_id_account  frequency date_account  ...  amount_mean  \\\n",
       "0                     30          2   1993-03-22  ...      5025.00   \n",
       "1                     46          0   1993-02-13  ...     11015.64   \n",
       "2                     45          0   1993-02-08  ...      5417.46   \n",
       "3                     12          0   1993-01-30  ...      8253.08   \n",
       "4                      1          2   1993-02-14  ...     18945.97   \n",
       "..                   ...        ...          ...  ...          ...   \n",
       "323                   72          0   1995-01-21  ...      4895.97   \n",
       "324                   29          0   1995-11-29  ...     11725.49   \n",
       "325                   70          0   1996-05-21  ...      7779.82   \n",
       "326                   16          0   1995-05-20  ...     13280.08   \n",
       "327                   67          0   1995-10-14  ...      4550.30   \n",
       "\n",
       "     balance_mean  credit_std  credit_mean  withdrawal_std  withdrawal_mean  \\\n",
       "0        12250.00     3774.81      5025.00            0.00             0.00   \n",
       "1        52083.86    13998.09     13523.16        14855.93          9111.27   \n",
       "2        30060.95     6801.23      5009.73         5390.57          6097.00   \n",
       "3        41297.48     9818.59      9254.60         4919.10          7168.10   \n",
       "4        57188.21    24264.23     21255.93        13981.92         16801.00   \n",
       "..            ...         ...          ...             ...              ...   \n",
       "323      44197.51     9143.30      6448.58         5002.16          3830.46   \n",
       "324      55230.44    15547.01     13417.56        12458.77         10109.59   \n",
       "325      41994.91    12066.49      8544.93         7718.59          6974.43   \n",
       "326      56646.52    17170.85     16554.99        12640.02         10667.40   \n",
       "327      30869.78     9224.07      8811.76         3683.66          2965.92   \n",
       "\n",
       "     disp_id  client_id  num_users  card_type  \n",
       "0       2166       2166          1          0  \n",
       "1       2181       2181          1          0  \n",
       "2      11006      11314          1          0  \n",
       "3       2235       2235          1          0  \n",
       "4      13231      13539          1          0  \n",
       "..       ...        ...        ...        ...  \n",
       "323    10813      11121          2          0  \n",
       "324     3855       3855          1          0  \n",
       "325    10742      11050          1          0  \n",
       "326    13172      13480          1          0  \n",
       "327    13663      13971          1          0  \n",
       "\n",
       "[328 rows x 30 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Push the \"Card\" data into the compiledData table ::\n",
    "\n",
    "output = pd.read_csv('BankData/card_' + fileName + '.csv', delimiter=\";\")\n",
    "output = output.rename(columns = {'type' : 'card_type'})\n",
    "output = output.drop(columns = ['card_id', 'issued'])\n",
    "\n",
    "# Merge it into the compiledData column ::\n",
    "\n",
    "compiledVersion = pd.merge(compiledData, output, on='disp_id', how='left')\n",
    "compiledVersion['card_type'] = compiledVersion['card_type'].fillna(value = \"no card\")\n",
    "compiledVersion['card_type'] = compiledVersion['card_type'].map({'no card': 0, 'junior': 1, 'classic': 2, 'gold': 3})\n",
    "compiledVersion"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a2a075d",
   "metadata": {},
   "source": [
    "### Client Table handler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f2979906",
   "metadata": {},
   "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>account_id</th>\n",
       "      <th>date_loan</th>\n",
       "      <th>amount</th>\n",
       "      <th>duration</th>\n",
       "      <th>payments</th>\n",
       "      <th>status</th>\n",
       "      <th>district_id_account</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date_account</th>\n",
       "      <th>...</th>\n",
       "      <th>withdrawal_std</th>\n",
       "      <th>withdrawal_mean</th>\n",
       "      <th>disp_id</th>\n",
       "      <th>client_id</th>\n",
       "      <th>num_users</th>\n",
       "      <th>card_type</th>\n",
       "      <th>birth_number</th>\n",
       "      <th>district_id_client</th>\n",
       "      <th>owner_age</th>\n",
       "      <th>owner_sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5314</td>\n",
       "      <td>1787</td>\n",
       "      <td>1993-07-05</td>\n",
       "      <td>96396</td>\n",
       "      <td>12</td>\n",
       "      <td>8033</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-03-22</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2166</td>\n",
       "      <td>2166</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>475722</td>\n",
       "      <td>30</td>\n",
       "      <td>53</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5316</td>\n",
       "      <td>1801</td>\n",
       "      <td>1993-07-11</td>\n",
       "      <td>165960</td>\n",
       "      <td>36</td>\n",
       "      <td>4610</td>\n",
       "      <td>successful</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-13</td>\n",
       "      <td>...</td>\n",
       "      <td>14855.93</td>\n",
       "      <td>9111.27</td>\n",
       "      <td>2181</td>\n",
       "      <td>2181</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>680722</td>\n",
       "      <td>46</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6863</td>\n",
       "      <td>9188</td>\n",
       "      <td>1993-07-28</td>\n",
       "      <td>127080</td>\n",
       "      <td>60</td>\n",
       "      <td>2118</td>\n",
       "      <td>successful</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-08</td>\n",
       "      <td>...</td>\n",
       "      <td>5390.57</td>\n",
       "      <td>6097.00</td>\n",
       "      <td>11006</td>\n",
       "      <td>11314</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>360602</td>\n",
       "      <td>45</td>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5325</td>\n",
       "      <td>1843</td>\n",
       "      <td>1993-08-03</td>\n",
       "      <td>105804</td>\n",
       "      <td>36</td>\n",
       "      <td>2939</td>\n",
       "      <td>successful</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-01-30</td>\n",
       "      <td>...</td>\n",
       "      <td>4919.10</td>\n",
       "      <td>7168.10</td>\n",
       "      <td>2235</td>\n",
       "      <td>2235</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>405420</td>\n",
       "      <td>14</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7240</td>\n",
       "      <td>11013</td>\n",
       "      <td>1993-09-06</td>\n",
       "      <td>274740</td>\n",
       "      <td>60</td>\n",
       "      <td>4579</td>\n",
       "      <td>successful</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-02-14</td>\n",
       "      <td>...</td>\n",
       "      <td>13981.92</td>\n",
       "      <td>16801.00</td>\n",
       "      <td>13231</td>\n",
       "      <td>13539</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>780907</td>\n",
       "      <td>63</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <th>323</th>\n",
       "      <td>6818</td>\n",
       "      <td>9030</td>\n",
       "      <td>1996-12-12</td>\n",
       "      <td>155616</td>\n",
       "      <td>48</td>\n",
       "      <td>3242</td>\n",
       "      <td>successful</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-01-21</td>\n",
       "      <td>...</td>\n",
       "      <td>5002.16</td>\n",
       "      <td>3830.46</td>\n",
       "      <td>10813</td>\n",
       "      <td>11121</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>715410</td>\n",
       "      <td>72</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>5625</td>\n",
       "      <td>3189</td>\n",
       "      <td>1996-12-15</td>\n",
       "      <td>222180</td>\n",
       "      <td>60</td>\n",
       "      <td>3703</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-11-29</td>\n",
       "      <td>...</td>\n",
       "      <td>12458.77</td>\n",
       "      <td>10109.59</td>\n",
       "      <td>3855</td>\n",
       "      <td>3855</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>390320</td>\n",
       "      <td>29</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>6805</td>\n",
       "      <td>8972</td>\n",
       "      <td>1996-12-21</td>\n",
       "      <td>45024</td>\n",
       "      <td>48</td>\n",
       "      <td>938</td>\n",
       "      <td>successful</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1996-05-21</td>\n",
       "      <td>...</td>\n",
       "      <td>7718.59</td>\n",
       "      <td>6974.43</td>\n",
       "      <td>10742</td>\n",
       "      <td>11050</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>575504</td>\n",
       "      <td>70</td>\n",
       "      <td>43</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>7233</td>\n",
       "      <td>10963</td>\n",
       "      <td>1996-12-25</td>\n",
       "      <td>115812</td>\n",
       "      <td>36</td>\n",
       "      <td>3217</td>\n",
       "      <td>successful</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-05-20</td>\n",
       "      <td>...</td>\n",
       "      <td>12640.02</td>\n",
       "      <td>10667.40</td>\n",
       "      <td>13172</td>\n",
       "      <td>13480</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>530601</td>\n",
       "      <td>16</td>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>7308</td>\n",
       "      <td>11362</td>\n",
       "      <td>1996-12-27</td>\n",
       "      <td>129408</td>\n",
       "      <td>24</td>\n",
       "      <td>5392</td>\n",
       "      <td>successful</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-10-14</td>\n",
       "      <td>...</td>\n",
       "      <td>3683.66</td>\n",
       "      <td>2965.92</td>\n",
       "      <td>13663</td>\n",
       "      <td>13971</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>626019</td>\n",
       "      <td>67</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     loan_id  account_id  date_loan  amount  duration  payments        status  \\\n",
       "0       5314        1787 1993-07-05   96396        12      8033  unsuccessful   \n",
       "1       5316        1801 1993-07-11  165960        36      4610    successful   \n",
       "2       6863        9188 1993-07-28  127080        60      2118    successful   \n",
       "3       5325        1843 1993-08-03  105804        36      2939    successful   \n",
       "4       7240       11013 1993-09-06  274740        60      4579    successful   \n",
       "..       ...         ...        ...     ...       ...       ...           ...   \n",
       "323     6818        9030 1996-12-12  155616        48      3242    successful   \n",
       "324     5625        3189 1996-12-15  222180        60      3703  unsuccessful   \n",
       "325     6805        8972 1996-12-21   45024        48       938    successful   \n",
       "326     7233       10963 1996-12-25  115812        36      3217    successful   \n",
       "327     7308       11362 1996-12-27  129408        24      5392    successful   \n",
       "\n",
       "     district_id_account  frequency date_account  ...  withdrawal_std  \\\n",
       "0                     30          2   1993-03-22  ...            0.00   \n",
       "1                     46          0   1993-02-13  ...        14855.93   \n",
       "2                     45          0   1993-02-08  ...         5390.57   \n",
       "3                     12          0   1993-01-30  ...         4919.10   \n",
       "4                      1          2   1993-02-14  ...        13981.92   \n",
       "..                   ...        ...          ...  ...             ...   \n",
       "323                   72          0   1995-01-21  ...         5002.16   \n",
       "324                   29          0   1995-11-29  ...        12458.77   \n",
       "325                   70          0   1996-05-21  ...         7718.59   \n",
       "326                   16          0   1995-05-20  ...        12640.02   \n",
       "327                   67          0   1995-10-14  ...         3683.66   \n",
       "\n",
       "     withdrawal_mean  disp_id  client_id  num_users  card_type  birth_number  \\\n",
       "0               0.00     2166       2166          1          0        475722   \n",
       "1            9111.27     2181       2181          1          0        680722   \n",
       "2            6097.00    11006      11314          1          0        360602   \n",
       "3            7168.10     2235       2235          1          0        405420   \n",
       "4           16801.00    13231      13539          1          0        780907   \n",
       "..               ...      ...        ...        ...        ...           ...   \n",
       "323          3830.46    10813      11121          2          0        715410   \n",
       "324         10109.59     3855       3855          1          0        390320   \n",
       "325          6974.43    10742      11050          1          0        575504   \n",
       "326         10667.40    13172      13480          1          0        530601   \n",
       "327          2965.92    13663      13971          1          0        626019   \n",
       "\n",
       "     district_id_client  owner_age  owner_sex  \n",
       "0                    30         53          1  \n",
       "1                    46         32          0  \n",
       "2                    45         64          0  \n",
       "3                    14         60          1  \n",
       "4                    63         22          0  \n",
       "..                  ...        ...        ...  \n",
       "323                  72         29          1  \n",
       "324                  29         61          0  \n",
       "325                  70         43          1  \n",
       "326                  16         47          0  \n",
       "327                  67         38          1  \n",
       "\n",
       "[328 rows x 34 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "output = pd.read_csv('BankData/client.csv', delimiter=\";\")\n",
    "output['owner_age'] = 2000 - ( 1900 + np.floor(output['birth_number']/10000) )\n",
    "output['owner_sex'] = round(output['birth_number']/10000) - np.floor(output['birth_number']/10000)\n",
    "\n",
    "output['owner_age'] = output['owner_age'].astype(int)\n",
    "output['owner_sex'] = output['owner_sex'].astype(int)\n",
    "output.rename(columns = {'district_id' : 'district_id_client'}, inplace = True)\n",
    "output\n",
    "\n",
    "compiledData = pd.merge(compiledVersion, output, on='client_id')\n",
    "compiledData"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5971269b",
   "metadata": {},
   "source": [
    "### DistrictTable handler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "95321c96",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\UP2018~2\\AppData\\Local\\Temp/ipykernel_3444/975999107.py:47: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  output['crime_95'][unknownRow] = ( int(output['crime_95'][specificRow.id.iloc[0] - 1]) + int( output['crime_95'][specificRow2.id.iloc[0] - 1] ) ) / 2\n",
      "C:\\Users\\UP2018~2\\AppData\\Local\\Temp/ipykernel_3444/975999107.py:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  output['unemployment_95'][unknownRow] = ( float(output['unemployment_95'][specificRow.id.iloc[0]]) + float(output['unemployment_95'][specificRow2.id.iloc[0]])) / 2.0\n"
     ]
    }
   ],
   "source": [
    "from sklearn import preprocessing\n",
    "\n",
    "def normalize_Dataframe(dataframe):\n",
    "    x = dataframe.values\n",
    "    min_max_scaler = preprocessing.MinMaxScaler()\n",
    "    x_scaled = min_max_scaler.fit_transform(x)\n",
    "    df = pd.DataFrame(x_scaled)\n",
    "    return df\n",
    "\n",
    "output = pd.read_csv('BankData/district.csv', delimiter=\";\")\n",
    "output = output.rename(columns = {'code' : 'district_id',\n",
    "                     'no. of inhabitants' : 'inhabitants', \n",
    "                     'no. of municipalities with inhabitants < 499' : 'municipalities_499',\n",
    "                     'no. of municipalities with inhabitants 500-1999' : 'municipalities_500-1999',\n",
    "                     'no. of municipalities with inhabitants 2000-9999' : 'municipalities_2000-9999',\n",
    "                     'no. of municipalities with inhabitants >10000' : 'municipalities_10000',\n",
    "                     'no. of cities' : 'num_cities',\n",
    "                     \"unemploymant rate '95\" : 'unemployment_95',\n",
    "                     \"unemploymant rate '96\" : 'unemployment_96',\n",
    "                     \"no. of commited crimes '95\" : 'crime_95',\n",
    "                     \"no. of commited crimes '96\" : 'crime_96',\n",
    "                    })\n",
    "\n",
    "##### ====================== Obtain the approximate values for '?' ================================================\n",
    "\n",
    "dist_df = pd.DataFrame(columns=[\"id\", \"difference\"])\n",
    "toAnalyse = output.copy()\n",
    "toAnalyse = toAnalyse.drop(columns = ['district_id', 'district_name', 'region', 'crime_95', 'unemployment_95'])\n",
    "toAnalyse = normalize_Dataframe(toAnalyse)\n",
    "\n",
    "# Find the row index with the '?'\n",
    "unknownRow = (output.loc[output['crime_95'] == '?'])['district_id'] - 1\n",
    "\n",
    "idValue = 1\n",
    "for row in toAnalyse.iterrows():\n",
    "    distance = np.linalg.norm(toAnalyse.iloc[unknownRow] - toAnalyse.iloc[idValue - 1])\n",
    "    dist_df = dist_df.append({'id':idValue, 'difference':distance}, ignore_index = True)\n",
    "    idValue = idValue + 1\n",
    "\n",
    "dist_df = dist_df.drop(unknownRow)\n",
    "specificRow = dist_df[dist_df.difference == dist_df.difference.min()]\n",
    "dist_df = dist_df.drop(specificRow.index)\n",
    "specificRow2 = dist_df[dist_df.difference == dist_df.difference.min()]\n",
    "dist_df = dist_df.drop(specificRow2.index)\n",
    "##### ===========================================================================================================\n",
    "\n",
    "output['crime_95'][unknownRow] = ( int(output['crime_95'][specificRow.id.iloc[0] - 1]) + int( output['crime_95'][specificRow2.id.iloc[0] - 1] ) ) / 2\n",
    "output['unemployment_95'][unknownRow] = ( float(output['unemployment_95'][specificRow.id.iloc[0]]) + float(output['unemployment_95'][specificRow2.id.iloc[0]])) / 2.0\n",
    "\n",
    "# Change dataset types\n",
    "output['crime_95'] = output['crime_95'].astype(float)\n",
    "output['unemployment_95'] = output['unemployment_95'].astype(float)\n",
    "\n",
    "output['average_crime'] = round(( output['crime_95'] + output['crime_96'] )/2)\n",
    "output['average_unemployment'] = round(( output['unemployment_95'] + output['unemployment_95'] )/2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ef81b772",
   "metadata": {},
   "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>account_id</th>\n",
       "      <th>date_loan</th>\n",
       "      <th>amount</th>\n",
       "      <th>duration</th>\n",
       "      <th>payments</th>\n",
       "      <th>status</th>\n",
       "      <th>district_id_account</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date_account</th>\n",
       "      <th>...</th>\n",
       "      <th>num_cities</th>\n",
       "      <th>ratio_urban_inhabitants</th>\n",
       "      <th>avg_salary</th>\n",
       "      <th>unemployment_95</th>\n",
       "      <th>unemployment_96</th>\n",
       "      <th>enterpreneurs</th>\n",
       "      <th>crime_95</th>\n",
       "      <th>crime_96</th>\n",
       "      <th>average_crime</th>\n",
       "      <th>average_unemployment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5314</td>\n",
       "      <td>1787</td>\n",
       "      <td>1993-07-05</td>\n",
       "      <td>96396</td>\n",
       "      <td>12</td>\n",
       "      <td>8033</td>\n",
       "      <td>unsuccessful</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>1993-03-22</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>81.8</td>\n",
       "      <td>9650</td>\n",
       "      <td>3.38</td>\n",
       "      <td>3.67</td>\n",
       "      <td>100</td>\n",
       "      <td>2985.0</td>\n",
       "      <td>2804</td>\n",
       "      <td>2894.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5316</td>\n",
       "      <td>1801</td>\n",
       "      <td>1993-07-11</td>\n",
       "      <td>165960</td>\n",
       "      <td>36</td>\n",
       "      <td>4610</td>\n",
       "      <td>successful</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-13</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>73.5</td>\n",
       "      <td>8369</td>\n",
       "      <td>1.79</td>\n",
       "      <td>2.31</td>\n",
       "      <td>117</td>\n",
       "      <td>2854.0</td>\n",
       "      <td>2618</td>\n",
       "      <td>2736.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6456</td>\n",
       "      <td>7123</td>\n",
       "      <td>1993-12-09</td>\n",
       "      <td>47016</td>\n",
       "      <td>12</td>\n",
       "      <td>3918</td>\n",
       "      <td>successful</td>\n",
       "      <td>64</td>\n",
       "      <td>1</td>\n",
       "      <td>1993-04-14</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>73.5</td>\n",
       "      <td>8369</td>\n",
       "      <td>1.79</td>\n",
       "      <td>2.31</td>\n",
       "      <td>117</td>\n",
       "      <td>2854.0</td>\n",
       "      <td>2618</td>\n",
       "      <td>2736.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7249</td>\n",
       "      <td>11065</td>\n",
       "      <td>1994-06-12</td>\n",
       "      <td>69360</td>\n",
       "      <td>48</td>\n",
       "      <td>1445</td>\n",
       "      <td>successful</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-03-19</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>73.5</td>\n",
       "      <td>8369</td>\n",
       "      <td>1.79</td>\n",
       "      <td>2.31</td>\n",
       "      <td>117</td>\n",
       "      <td>2854.0</td>\n",
       "      <td>2618</td>\n",
       "      <td>2736.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6863</td>\n",
       "      <td>9188</td>\n",
       "      <td>1993-07-28</td>\n",
       "      <td>127080</td>\n",
       "      <td>60</td>\n",
       "      <td>2118</td>\n",
       "      <td>successful</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>1993-02-08</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>53.5</td>\n",
       "      <td>8390</td>\n",
       "      <td>2.28</td>\n",
       "      <td>2.89</td>\n",
       "      <td>132</td>\n",
       "      <td>2080.0</td>\n",
       "      <td>2122</td>\n",
       "      <td>2101.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <th>323</th>\n",
       "      <td>5148</td>\n",
       "      <td>940</td>\n",
       "      <td>1996-05-29</td>\n",
       "      <td>284280</td>\n",
       "      <td>60</td>\n",
       "      <td>4738</td>\n",
       "      <td>successful</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>1994-11-15</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>62.3</td>\n",
       "      <td>9065</td>\n",
       "      <td>4.46</td>\n",
       "      <td>5.39</td>\n",
       "      <td>123</td>\n",
       "      <td>4147.0</td>\n",
       "      <td>4166</td>\n",
       "      <td>4156.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>6667</td>\n",
       "      <td>8127</td>\n",
       "      <td>1996-07-15</td>\n",
       "      <td>155880</td>\n",
       "      <td>24</td>\n",
       "      <td>6495</td>\n",
       "      <td>successful</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>1994-10-13</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>62.3</td>\n",
       "      <td>9065</td>\n",
       "      <td>4.46</td>\n",
       "      <td>5.39</td>\n",
       "      <td>123</td>\n",
       "      <td>4147.0</td>\n",
       "      <td>4166</td>\n",
       "      <td>4156.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>5741</td>\n",
       "      <td>3754</td>\n",
       "      <td>1996-11-03</td>\n",
       "      <td>68544</td>\n",
       "      <td>24</td>\n",
       "      <td>2856</td>\n",
       "      <td>successful</td>\n",
       "      <td>37</td>\n",
       "      <td>2</td>\n",
       "      <td>1996-02-19</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>62.3</td>\n",
       "      <td>9065</td>\n",
       "      <td>4.46</td>\n",
       "      <td>5.39</td>\n",
       "      <td>123</td>\n",
       "      <td>4147.0</td>\n",
       "      <td>4166</td>\n",
       "      <td>4156.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>7036</td>\n",
       "      <td>10019</td>\n",
       "      <td>1996-09-25</td>\n",
       "      <td>194940</td>\n",
       "      <td>36</td>\n",
       "      <td>5415</td>\n",
       "      <td>successful</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-06-15</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>69.4</td>\n",
       "      <td>11277</td>\n",
       "      <td>1.25</td>\n",
       "      <td>1.44</td>\n",
       "      <td>127</td>\n",
       "      <td>5179.0</td>\n",
       "      <td>4987</td>\n",
       "      <td>5083.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>6015</td>\n",
       "      <td>5001</td>\n",
       "      <td>1996-11-09</td>\n",
       "      <td>110112</td>\n",
       "      <td>24</td>\n",
       "      <td>4588</td>\n",
       "      <td>successful</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>1995-12-15</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>50.9</td>\n",
       "      <td>8240</td>\n",
       "      <td>2.53</td>\n",
       "      <td>3.56</td>\n",
       "      <td>99</td>\n",
       "      <td>1850.0</td>\n",
       "      <td>1903</td>\n",
       "      <td>1876.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     loan_id  account_id  date_loan  amount  duration  payments        status  \\\n",
       "0       5314        1787 1993-07-05   96396        12      8033  unsuccessful   \n",
       "1       5316        1801 1993-07-11  165960        36      4610    successful   \n",
       "2       6456        7123 1993-12-09   47016        12      3918    successful   \n",
       "3       7249       11065 1994-06-12   69360        48      1445    successful   \n",
       "4       6863        9188 1993-07-28  127080        60      2118    successful   \n",
       "..       ...         ...        ...     ...       ...       ...           ...   \n",
       "323     5148         940 1996-05-29  284280        60      4738    successful   \n",
       "324     6667        8127 1996-07-15  155880        24      6495    successful   \n",
       "325     5741        3754 1996-11-03   68544        24      2856    successful   \n",
       "326     7036       10019 1996-09-25  194940        36      5415    successful   \n",
       "327     6015        5001 1996-11-09  110112        24      4588    successful   \n",
       "\n",
       "     district_id_account  frequency date_account  ...  num_cities  \\\n",
       "0                     30          2   1993-03-22  ...          10   \n",
       "1                     46          0   1993-02-13  ...          10   \n",
       "2                     64          1   1993-04-14  ...          10   \n",
       "3                     11          0   1993-03-19  ...          10   \n",
       "4                     45          0   1993-02-08  ...           5   \n",
       "..                   ...        ...          ...  ...         ...   \n",
       "323                   37          0   1994-11-15  ...           9   \n",
       "324                   37          0   1994-10-13  ...           9   \n",
       "325                   37          2   1996-02-19  ...           9   \n",
       "326                    8          0   1995-06-15  ...           8   \n",
       "327                   53          0   1995-12-15  ...           7   \n",
       "\n",
       "     ratio_urban_inhabitants  avg_salary  unemployment_95  unemployment_96  \\\n",
       "0                       81.8        9650             3.38             3.67   \n",
       "1                       73.5        8369             1.79             2.31   \n",
       "2                       73.5        8369             1.79             2.31   \n",
       "3                       73.5        8369             1.79             2.31   \n",
       "4                       53.5        8390             2.28             2.89   \n",
       "..                       ...         ...              ...              ...   \n",
       "323                     62.3        9065             4.46             5.39   \n",
       "324                     62.3        9065             4.46             5.39   \n",
       "325                     62.3        9065             4.46             5.39   \n",
       "326                     69.4       11277             1.25             1.44   \n",
       "327                     50.9        8240             2.53             3.56   \n",
       "\n",
       "     enterpreneurs  crime_95  crime_96  average_crime  average_unemployment  \n",
       "0              100    2985.0      2804         2894.0                   3.0  \n",
       "1              117    2854.0      2618         2736.0                   2.0  \n",
       "2              117    2854.0      2618         2736.0                   2.0  \n",
       "3              117    2854.0      2618         2736.0                   2.0  \n",
       "4              132    2080.0      2122         2101.0                   2.0  \n",
       "..             ...       ...       ...            ...                   ...  \n",
       "323            123    4147.0      4166         4156.0                   4.0  \n",
       "324            123    4147.0      4166         4156.0                   4.0  \n",
       "325            123    4147.0      4166         4156.0                   4.0  \n",
       "326            127    5179.0      4987         5083.0                   1.0  \n",
       "327             99    1850.0      1903         1876.0                   3.0  \n",
       "\n",
       "[328 rows x 52 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge Districts with the main dataset\n",
    "\n",
    "compiledVersion = pd.merge(compiledData, output, left_on='district_id_client', right_on='district_id')\n",
    "compiledVersion"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "617bf2b0",
   "metadata": {},
   "source": [
    "# Export the CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5cf70213",
   "metadata": {},
   "outputs": [],
   "source": [
    "compiledVersion.to_csv('compiledData_' + fileName + '.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "967293ac",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}