data_pipeline_assignment_answers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Assigment solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# !pip install sqlalchemy psycopg2 pandas sklearn nltk numpy boto3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 01 data ingestion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import zipfile\n",
    "import boto3\n",
    "import psycopg2\n",
    "import csv\n",
    "from io import StringIO\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "\n",
    "#\n",
    "# Here is some code to help you get started. This will connect to \n",
    "# s3 and retrieve earlier loans from the file area, they are \n",
    "# zipped into jsons.zip. The code will also extract the json \n",
    "# files to the /jsons folder. There is one json file per \n",
    "# load application, in total 10000 st. \n",
    "# \n",
    "# \n",
    "\n",
    "folder = \"jsons\"\n",
    "\n",
    "if folder not in os.listdir():\n",
    "    os.mkdir(folder)\n",
    "\n",
    "    s3 = boto3.resource('s3')\n",
    "    bucket = s3.Bucket(\"arn:aws:s3:eu-north-1:705545259095:accesspoint/bizware\")\n",
    "    parsed = []\n",
    "\n",
    "    for obj in bucket.objects.all():\n",
    "        bucket.download_file(obj.key, obj.key)\n",
    "        print(f\"downloaded {obj.key}\")\n",
    "        if \".zip\" in obj.key:\n",
    "            with zipfile.ZipFile(obj.key, 'r') as zip_ref:\n",
    "                zip_ref.extractall()\n",
    "                print(f\"extracted {obj.key}\")\n",
    "\n",
    "# now the task is to parse the json files and collect \n",
    "# them into a pandas df. \n",
    "\n",
    "# useful functions here is \n",
    "# os.listdir(\"mapp\") # list all files in the folder \n",
    "#     called \"mapp\" located in the current working directory\n",
    "#\n",
    "# pd.read_json(file) parse a json file\n",
    "# \n",
    "# pd.DataFrame(list_of_series) # try to create a \n",
    "#     dataframe from a list of pandas series\n",
    "# \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Print random json file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                                 34051894\n",
       "loan_amnt                             30000\n",
       "term                              36 months\n",
       "emp_title             Senior Vice President\n",
       "emp_length                        10+ years\n",
       "home_ownership                     MORTGAGE\n",
       "annual_inc                           200000\n",
       "loan_status                      Fully Paid\n",
       "desc                                   None\n",
       "purpose                  debt_consolidation\n",
       "title                    Debt consolidation\n",
       "addr_state                               NC\n",
       "nr_payment_remarks                        0\n",
       "nr_loans                                 19\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "random_json_file = os.listdir(folder)[123]\n",
    "json_path = os.path.join(folder, random_json_file)\n",
    "pd.read_json(json_path, typ=\"series\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## solution to data ingestion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 files parsed\n",
      "2000 files parsed\n",
      "3000 files parsed\n",
      "4000 files parsed\n",
      "5000 files parsed\n",
      "6000 files parsed\n",
      "7000 files parsed\n",
      "8000 files parsed\n",
      "9000 files parsed\n",
      "10000  rows in dataset\n"
     ]
    }
   ],
   "source": [
    "#------------------------------\n",
    "#-------\n",
    "                \n",
    "files = os.listdir(folder)\n",
    "parsed = []\n",
    "for i, file in enumerate(files):\n",
    "    with open(f\"{folder}/{file}\") as f:\n",
    "        parsed.append(pd.read_json(f, typ=\"series\"))\n",
    "    \n",
    "    if (i > 0) & (i % 1000 == 0):\n",
    "        print(f\"{i} files parsed\")\n",
    "\n",
    "\n",
    "df = pd.DataFrame(parsed)\n",
    "print(df.shape[0], \" rows in dataset\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>id</th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>term</th>\n",
       "      <th>emp_title</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>desc</th>\n",
       "      <th>purpose</th>\n",
       "      <th>title</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>nr_payment_remarks</th>\n",
       "      <th>nr_loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>970906</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>covanta energy</td>\n",
       "      <td>3 years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>75000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>Borrower added on 10/27/11 &gt; purchace new ai...</td>\n",
       "      <td>major_purchase</td>\n",
       "      <td>personal</td>\n",
       "      <td>VA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>634792</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>None</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>Borrower added on 12/20/10 &gt; renovations and...</td>\n",
       "      <td>home_improvement</td>\n",
       "      <td>dukeberbatim</td>\n",
       "      <td>IL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16371827</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>Sales Engineer</td>\n",
       "      <td>3 years</td>\n",
       "      <td>RENT</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>None</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>FL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45644356</td>\n",
       "      <td>32000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>IT Manager</td>\n",
       "      <td>2 years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>125000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>None</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>Credit card refinancing</td>\n",
       "      <td>NJ</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11946125</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>store manager</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>RENT</td>\n",
       "      <td>53000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>None</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>Credit card refinancing</td>\n",
       "      <td>CA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id  loan_amnt        term       emp_title emp_length home_ownership  \\\n",
       "0    970906    14400.0   36 months  covanta energy    3 years       MORTGAGE   \n",
       "1    634792    15800.0   36 months            None  10+ years       MORTGAGE   \n",
       "2  16371827    12000.0   36 months  Sales Engineer    3 years           RENT   \n",
       "3  45644356    32000.0   60 months      IT Manager    2 years       MORTGAGE   \n",
       "4  11946125     6000.0   36 months  store manager   10+ years           RENT   \n",
       "\n",
       "   annual_inc loan_status                                               desc  \\\n",
       "0     75000.0  Fully Paid    Borrower added on 10/27/11 > purchace new ai...   \n",
       "1     36000.0  Fully Paid    Borrower added on 12/20/10 > renovations and...   \n",
       "2     88000.0  Fully Paid                                               None   \n",
       "3    125000.0  Fully Paid                                               None   \n",
       "4     53000.0  Fully Paid                                               None   \n",
       "\n",
       "              purpose                    title addr_state  nr_payment_remarks  \\\n",
       "0      major_purchase                 personal         VA                 0.0   \n",
       "1    home_improvement             dukeberbatim         IL                 0.0   \n",
       "2  debt_consolidation       Debt consolidation         FL                 0.0   \n",
       "3         credit_card  Credit card refinancing         NJ                 0.0   \n",
       "4         credit_card  Credit card refinancing         CA                 0.0   \n",
       "\n",
       "   nr_loans  \n",
       "0      19.0  \n",
       "1       7.0  \n",
       "2      31.0  \n",
       "3      47.0  \n",
       "4      23.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 02 data cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                       0\n",
      "loan_amnt                0\n",
      "term                     0\n",
      "emp_title              504\n",
      "emp_length             367\n",
      "home_ownership           0\n",
      "annual_inc               0\n",
      "loan_status              0\n",
      "desc                  6692\n",
      "purpose                  0\n",
      "title                    1\n",
      "addr_state               0\n",
      "nr_payment_remarks       0\n",
      "nr_loans                 0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# in this section the task is to handle null values. \n",
    "# The strategy used here is to replace the null values \n",
    "# in the numerical column with the median, and replace the \n",
    "# categorcial (non-numerical columns) with \"Unknown\"\n",
    "\n",
    "# Commands that can come in handy here is \n",
    "# \n",
    "# df.isna().sum(axis=0) # show the number of nan's per column \n",
    "#     (axis=1 for row-wise)\n",
    "# \n",
    "# df[\"col\"] or df.col # retrieve the values in the \"column\" \n",
    "#     column of the dataframe df\n",
    "#\n",
    "# df.col.mean() # retrive the mean value of the column \n",
    "#     \"col\" in the dataframe \"df\"\n",
    "#\n",
    "# df.col.fillna(val) #replace nan values with value: val\n",
    "#\n",
    "#\n",
    "print(df.isna().sum(axis=0))\n",
    "\n",
    "numeric_cols=[\"annual_inc\", \"loan_amnt\", \"nr_payment_remarks\", \"nr_loans\"]\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## solution to data cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                    0\n",
      "loan_amnt             0\n",
      "term                  0\n",
      "emp_title             0\n",
      "emp_length            0\n",
      "home_ownership        0\n",
      "annual_inc            0\n",
      "loan_status           0\n",
      "desc                  0\n",
      "purpose               0\n",
      "title                 0\n",
      "addr_state            0\n",
      "nr_payment_remarks    0\n",
      "nr_loans              0\n",
      "dtype: int64\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>id</th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>term</th>\n",
       "      <th>emp_title</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>desc</th>\n",
       "      <th>purpose</th>\n",
       "      <th>title</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>nr_payment_remarks</th>\n",
       "      <th>nr_loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>970906</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>covanta energy</td>\n",
       "      <td>3 years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>75000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>Borrower added on 10/27/11 &gt; purchace new ai...</td>\n",
       "      <td>major_purchase</td>\n",
       "      <td>personal</td>\n",
       "      <td>VA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>634792</td>\n",
       "      <td>15800.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>Borrower added on 12/20/10 &gt; renovations and...</td>\n",
       "      <td>home_improvement</td>\n",
       "      <td>dukeberbatim</td>\n",
       "      <td>IL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16371827</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>Sales Engineer</td>\n",
       "      <td>3 years</td>\n",
       "      <td>RENT</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>FL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45644356</td>\n",
       "      <td>32000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>IT Manager</td>\n",
       "      <td>2 years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>125000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>Credit card refinancing</td>\n",
       "      <td>NJ</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11946125</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>store manager</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>RENT</td>\n",
       "      <td>53000.0</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>Credit card refinancing</td>\n",
       "      <td>CA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id  loan_amnt        term       emp_title emp_length home_ownership  \\\n",
       "0    970906    14400.0   36 months  covanta energy    3 years       MORTGAGE   \n",
       "1    634792    15800.0   36 months         Unknown  10+ years       MORTGAGE   \n",
       "2  16371827    12000.0   36 months  Sales Engineer    3 years           RENT   \n",
       "3  45644356    32000.0   60 months      IT Manager    2 years       MORTGAGE   \n",
       "4  11946125     6000.0   36 months  store manager   10+ years           RENT   \n",
       "\n",
       "   annual_inc loan_status                                               desc  \\\n",
       "0     75000.0  Fully Paid    Borrower added on 10/27/11 > purchace new ai...   \n",
       "1     36000.0  Fully Paid    Borrower added on 12/20/10 > renovations and...   \n",
       "2     88000.0  Fully Paid                                            Unknown   \n",
       "3    125000.0  Fully Paid                                            Unknown   \n",
       "4     53000.0  Fully Paid                                            Unknown   \n",
       "\n",
       "              purpose                    title addr_state  nr_payment_remarks  \\\n",
       "0      major_purchase                 personal         VA                 0.0   \n",
       "1    home_improvement             dukeberbatim         IL                 0.0   \n",
       "2  debt_consolidation       Debt consolidation         FL                 0.0   \n",
       "3         credit_card  Credit card refinancing         NJ                 0.0   \n",
       "4         credit_card  Credit card refinancing         CA                 0.0   \n",
       "\n",
       "   nr_loans  \n",
       "0      19.0  \n",
       "1       7.0  \n",
       "2      31.0  \n",
       "3      47.0  \n",
       "4      23.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def fill_nans(df, numeric_cols):\n",
    "    \"\"\"\n",
    "    Function to fill all null values \n",
    "        numeric columns get a fill value = 0\n",
    "        non-numeric columns get a fill value = Unknown\n",
    "    \"\"\"\n",
    "    for column in df.columns:\n",
    "        if column in numeric_cols:\n",
    "            df[column]=df[column].fillna(df[column].median())\n",
    "        else:\n",
    "            df[column]=df[column].fillna(\"Unknown\")\n",
    "    return df\n",
    "\n",
    "\n",
    "\n",
    "df = fill_nans(df, numeric_cols)\n",
    "\n",
    "print(df.isna().sum(axis=0))\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 03 create new features and a label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "## The task here is to create one new feature, namely \n",
    "# the loan_income_ratio, which is defined as\n",
    "#\n",
    "#                           loan amount\n",
    "#  loan_income_ratio = -----------------------\n",
    "#                          annual income \n",
    "#\n",
    "#\n",
    "# also, the label needs creation. This column should be = 1\n",
    "# if the \"loan_status\" column is one of these values:\n",
    "#\n",
    "#     \"Default\", \"In Grace Period\", \"Late (31-120 days)\"\n",
    "# \n",
    "# and = 0 otherwise\n",
    "#\n",
    "# Handy commands could be\n",
    "# \n",
    "# df.groupby([\"loan_status\"])[\"id\"].count() # count the number of rows for different loan statuses\n",
    "# \n",
    "# df.loc[df[\"column\"] == \"value\",] #filter a dataframe to only retrieve \n",
    "#     rows where the column \"column\" equals \"value\" \n",
    "#\n",
    "# df[\"column\"] = 0 assign value 0 to all rows in column \"column\"\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:ylabel='Frequency'>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYUAAAD4CAYAAAAD6PrjAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAATk0lEQVR4nO3df7RlZX3f8fdH/G2IRBnpOMw4aEddmCiQK9pl06LEBrERrYbCWioa4iQRVuOKfzAaV7W1rJI00ZpqbUaxDjYGiMY4qSYtUIzLriIOOP6YIdQRxzojwohE1CQQ8Ns/zp7NAe7cu8/M3Wefe+/7tdZZd+9n733uh8OZ+z3P8+y9T6oKSZIAHjZ0AEnS7LAoSJJaFgVJUsuiIElqWRQkSa2HDx3gSBx77LG1cePGoWNI0rJyww03fLeq1sy3bVkXhY0bN7Jjx46hY0jSspLkm4fa5vCRJKllUZAktSwKkqSWRUGS1LIoSJJaFgVJUsuiIElqWRQkSS2LgiSptayvaNbysXHLp9rlvZe8dMAkkhZiT0GS1LIoSJJaFgVJUsuiIElqWRQkSS2LgiSpZVGQJLUsCpKklkVBktSyKEiSWr0VhSSPTnJ9ki8l2ZXk3zTtJyT5fJI9Sa5I8sim/VHN+p5m+8a+skmS5tdnT+Fu4EVV9RzgJOCMJM8Hfht4d1X9Q+BO4Pxm//OBO5v2dzf7SZKmqLeiUCM/bFYf0TwKeBHwsaZ9G/DyZvmsZp1m++lJ0lc+SdJD9TqnkOSoJDuB24GrgK8Df11V9za77APWNcvrgG8BNNu/DzxxnufcnGRHkh0HDhzoM74krTq9FoWquq+qTgKOB04FnrkEz7m1quaqam7NmjVH+nSSpDFTOfuoqv4auBb4R8AxSQ5+j8PxwP5meT+wHqDZ/njgjmnkkySN9Hn20ZokxzTLjwFeDNzEqDi8qtntPOCTzfL2Zp1m+/+qquornyTpofr85rW1wLYkRzEqPldW1X9Pshu4PMm/A74IXNrsfynwkSR7gO8B5/SYTZI0j96KQlV9GTh5nvZbGM0vPLj974Bf6iuPJGlxXtEsSWpZFCRJLYuCJKllUZAktSwKkqSWRUGS1LIoSJJaFgVJUsuiIElq9XmbCw1k45ZPtct7L3npgEkkLTf2FCRJLYuCJKllUZAktSwKkqSWE806Yk5sSyuHPQVJUsuiIElqWRQkSS2LgiSpZVGQJLU8+2iV8owhSfOxpyBJalkUJEmt3opCkvVJrk2yO8muJL/RtL8jyf4kO5vHmWPHvCXJniQ3J/mFvrJJkubX55zCvcCbq+rGJEcDNyS5qtn27qr63fGdk5wInAM8C3gycHWSp1fVfT1mXNacF5C01HrrKVTVrVV1Y7P8A+AmYN0Ch5wFXF5Vd1fVN4A9wKl95ZMkPdRUzj5KshE4Gfg88ALgwiSvBXYw6k3cyahgXDd22D4WLiKaMnsm0srX+0Rzkp8APg68qaruAt4PPA04CbgV+L0Jn29zkh1Jdhw4cGCp40rSqtZrUUjyCEYF4Q+r6k8Aquq2qrqvqn4MfID7h4j2A+vHDj++aXuAqtpaVXNVNbdmzZo+40vSqtPn2UcBLgVuqqp3jbWvHdvtFcBXm+XtwDlJHpXkBGATcH1f+SRJD9XnnMILgNcAX0mys2l7K3BukpOAAvYCvwpQVbuSXAnsZnTm0gWeeSRJ09VbUaiqzwGZZ9OnFzjmYuDivjJJkhbmFc2SpJZFQZLUsihIkloWBUlSy6IgSWr5JTvLzPitJvp4zq63r+gjh6Th2VOQJLUsCpKklkVBktRyTmEVmfV5AG/NLQ3PnoIkqWVRkCS1LAqSpJZzClpSzgtIy5s9BUlSy6IgSWpZFCRJLYuCJKnlRPOMcsJW0hAsCjqkWb8CWtLSc/hIktSyp6AVweE2aWnYU5AktToVhSQ/M+kTJ1mf5Noku5PsSvIbTfsTklyV5GvNz59q2pPk95PsSfLlJKdM+jslSUema0/hPye5Pskbkzy+4zH3Am+uqhOB5wMXJDkR2AJcU1WbgGuadYCXAJuax2bg/V3/IyRJS6PTnEJV/VySTcAvAzckuR74r1V11QLH3Arc2iz/IMlNwDrgLOC0ZrdtwGeAi5r2y6qqgOuSHJNkbfM8WoZm4ewl5xqkyXSeU6iqrwFvY/QH/J8Cv5/kr5L8i8WOTbIROBn4PHDc2B/67wDHNcvrgG+NHbavaZMkTUmnnkKSZwOvB14KXAX8YlXdmOTJwP8B/mSBY38C+Djwpqq6K0m7raoqSU0SOMlmRsNLbNiwYZJDV7RZ+FR+OCbN7Sd/qV9dT0n9T8AHgbdW1d8ebKyqbyd526EOSvIIRgXhD6vqYOG47eCwUJK1wO1N+35g/djhxzdtD1BVW4GtAHNzcxMVlFk3C3/YZyGDpOF0HT56KfDRgwUhycOSPBagqj4y3wEZdQkuBW6qqneNbdoOnNcsnwd8cqz9tc1ZSM8Hvu98giRNV9eicDXwmLH1xzZtC3kB8BrgRUl2No8zgUuAFyf5GvDzzTrAp4FbgD3AB4A3dswmSVoiXYePHl1VPzy4UlU/PNhTOJSq+hyQQ2w+fZ79C7igYx5JUg+69hR+NH4xWZKfBf52gf0lSctQ157Cm4A/TvJtRp/+/wHwL/sKJUkaRteL176Q5JnAM5qmm6vq7/uLJd3PM6Kk6ZnkLqnPBTY2x5yShKq6rJdUkqRBdL147SPA04CdwH1NcwEWhRnnp2xJk+jaU5gDTmzOEJIkrVBdzz76KqPJZUnSCta1p3AssLu5O+rdBxur6mW9pJIkDaJrUXhHnyEkSbOh6ympf5nkKcCmqrq6uZr5qH6jSZKmrevXcb4B+BjwB03TOuBPe8okSRpI1+GjC4BTGX1JDlX1tSRP6i2VNAP87gatRl3PPrq7qu45uJLk4YyuU5AkrSBdewp/meStwGOSvJjRba3/rL9YK8+RfOr0AjRJ09K1p7AFOAB8BfhVRt99cMhvXJMkLU9dzz76MaMvvvlAv3GkkZXWO3J+QstF13sffYN55hCq6qlLnkgr3kr7gy+tJJPc++igRwO/BDxh6eNIkobUaU6hqu4Ye+yvqv8I2AeWpBWm6/DRKWOrD2PUc5jkuxgkSctA1z/svze2fC+wFzh7ydOsAE4oSlrOup599MK+g6xWTrpKmiVdh49+c6HtVfWupYkjSRpS14vX5oBfZ3QjvHXArwGnAEc3j4dI8qEktyf56ljbO5LsT7KzeZw5tu0tSfYkuTnJLxzuf5Ak6fB1nVM4Hjilqn4Aoz/uwKeq6tULHPNh4L089Huc311VvzvekORE4BzgWcCTgauTPL2q7mMFcshI0qzq2lM4DrhnbP2epu2QquqzwPc6Pv9ZwOVVdXdVfQPYw+iurJKkKeraU7gMuD7JJ5r1lwPbDvN3XpjktcAO4M1VdSejIanrxvbZ17RJkqao69lHFyf5c+DnmqbXV9UXD+P3vR94J6NbZryT0amuvzzJEyTZDGwG2LBhw2FEkDx1WDqUrsNHAI8F7qqq9wD7kpww6S+rqtuq6r6xG+wdHCLaD6wf2/X4pm2+59haVXNVNbdmzZpJI0iSFtD16zjfDlwEvKVpegTw3yb9ZUnWjq2+Ajh4ZtJ24Jwkj2qKzSbg+kmfX5J0ZLrOKbwCOBm4EaCqvp1k3lNRD0ryR8BpwLFJ9gFvB05LchKj4aO9jL6bgaraleRKYDejK6YvWKlnHknSLOtaFO6pqkpSAEket9gBVXXuPM2XLrD/xcDFHfNIknrQtShcmeQPgGOSvIHR5LBfuKMVx2tItNotWhSSBLgCeCZwF/AM4F9X1VU9Z5MkTdmiRaEZNvp0Vf0MYCHQimPvQLpf11NSb0zy3F6TSJIG13VO4XnAq5PsBX4EhFEn4tl9BZMkTd+CRSHJhqr6f4B3LZWkVWCxnsKfMro76jeTfLyqXjmFTJKkgSxWFDK2/NQ+g0h9c0JZWtxiE811iGVJ0gq0WE/hOUnuYtRjeEyzDPdPNP9kr+mWOT+ZSlpuFiwKVXXUtIJIkoY3ya2zJUkrXNfrFCT1wC/70ayxpyBJalkUJEkti4IkqWVRkCS1nGiWjpCTxVpJLApacbxoUDp8Dh9Jklr2FKQJ2RPRSmZPQZLUsihIkloOH0kzwrOYNAt6KwpJPgT8c+D2qvrppu0JwBXARmAvcHZV3ZkkwHuAM4G/AV5XVTf2lU1aLSw0mlSfPYUPA+8FLhtr2wJcU1WXJNnSrF8EvATY1DyeB7y/+SkdkhO+0tLrbU6hqj4LfO9BzWcB25rlbcDLx9ovq5HrgGOSrO0rmyRpftOeUziuqm5tlr8DHNcsrwO+NbbfvqbtVh4kyWZgM8CGDRv6Syqp5TDU6jHYRHNVVZKJv/e5qrYCWwHm5uZm4nujHcbQfA71vvD9olk27aJwW5K1VXVrMzx0e9O+H1g/tt/xTZu0KvnJXEOZdlHYDpwHXNL8/ORY+4VJLmc0wfz9sWGmmeE/VEkrXZ+npP4RcBpwbJJ9wNsZFYMrk5wPfBM4u9n904xOR93D6JTU1/eVS5J0aL0Vhao69xCbTp9n3wIu6CuLJKkbb3MhSWp5mwtpCa2GM4ucW1vZ7ClIklr2FA7TavhEKGn1sacgSWpZFCRJLYuCJKllUZAktSwKkqSWRUGS1LIoSJJaFgVJUsuL1xbhRWqSVhOLgrTC+EFGR8LhI0lSy6IgSWpZFCRJLecUpA4cp9dqYVGQlim/7EZ9cPhIktSyKEiSWhYFSVLLOQVplXNuQuMGKQpJ9gI/AO4D7q2quSRPAK4ANgJ7gbOr6s4h8knSajVkT+GFVfXdsfUtwDVVdUmSLc36RcNEk2aHn+Q1TbM0fHQWcFqzvA34DBYF6QGW6noJr7vQoQw10VzA/0xyQ5LNTdtxVXVrs/wd4Lj5DkyyOcmOJDsOHDgwjayStGoM1VP4x1W1P8mTgKuS/NX4xqqqJDXfgVW1FdgKMDc3N+8+kqTDM0hPoar2Nz9vBz4BnArclmQtQPPz9iGySdJqNvWikORxSY4+uAz8M+CrwHbgvGa384BPTjubJK12QwwfHQd8IsnB3//RqvqLJF8ArkxyPvBN4OwBsknSqjb1olBVtwDPmaf9DuD0aeeRJN3P21xIklqzdJ2CpMPkdQdaKhaFefgPTNJq5fCRJKllT0HSzPF+T8OxKEhaEg8edvWP+fLk8JEkqWVRkCS1LAqSpJZFQZLUcqJZUsuzfmRPQZLUsihIkloOH0maV1+3e3GIarbZU5AktewpSDps3jxy5bEoSKuEf8DVhUUB/7FI0kHOKUiSWvYUJPXCs4yWJ3sKkqSWPQVJM8G5vdlgUZA0GAvB7Jm5opDkDOA9wFHAB6vqkj5+j29GaXpm+d+bcx8PNFNzCkmOAt4HvAQ4ETg3yYnDppKk1WPWegqnAnuq6haAJJcDZwG7B00laTB99DKWqkfQpZex0HdXT9pLmUavJlXVyxMfjiSvAs6oql9p1l8DPK+qLhzbZzOwuVl9BnDzYfyqY4HvHmHcoZh9GGYfhtn78ZSqWjPfhlnrKSyqqrYCW4/kOZLsqKq5JYo0VWYfhtmHYfbpm6k5BWA/sH5s/fimTZI0BbNWFL4AbEpyQpJHAucA2wfOJEmrxkwNH1XVvUkuBP4Ho1NSP1RVu3r4VUc0/DQwsw/D7MMw+5TN1ESzJGlYszZ8JEkakEVBktRa0UUhyRlJbk6yJ8mWebY/KskVzfbPJ9k4QMx5dcj+T5LcmOTe5vqOmdEh+28m2Z3ky0muSfKUIXLOp0P2X0vylSQ7k3xulq64Xyz72H6vTFJJZuZ0yQ6v++uSHGhe951JfmWInPPp8ronObt5z+9K8tFpZ5xIVa3IB6OJ6q8DTwUeCXwJOPFB+7wR+C/N8jnAFUPnniD7RuDZwGXAq4bOPGH2FwKPbZZ/fZm97j85tvwy4C+Gzt01e7Pf0cBngeuAuaFzT/C6vw5479BZDzP7JuCLwE81608aOvdCj5XcU2hvmVFV9wAHb5kx7ixgW7P8MeD0JJlixkNZNHtV7a2qLwM/HiLgArpkv7aq/qZZvY7R9SizoEv2u8ZWHwfMypkaXd7vAO8Efhv4u2mGW0TX7LOoS/Y3AO+rqjsBqur2KWecyEouCuuAb42t72va5t2nqu4Fvg88cSrpFtYl+6yaNPv5wJ/3mqi7TtmTXJDk68DvAP9qStkWs2j2JKcA66tq1m5Z2vU988pmyPFjSdbPs30IXbI/HXh6kv+d5LrmTtAzayUXBc24JK8G5oD/MHSWSVTV+6rqacBFwNuGztNFkocB7wLePHSWw/RnwMaqejZwFff38JeDhzMaQjoNOBf4QJJjhgy0kJVcFLrcMqPdJ8nDgccDd0wl3cKW8+0+OmVP8vPAbwEvq6q7p5RtMZO+7pcDL+8z0AQWy3408NPAZ5LsBZ4PbJ+RyeZFX/equmPsffJB4GenlG0xXd4z+4DtVfX3VfUN4P8yKhKzaehJjb4ejKrzLcAJ3D8B9KwH7XMBD5xovnLo3F2zj+37YWZrornL634yo8m5TUPnPYzsm8aWfxHYMXTuSd8zzf6fYXYmmru87mvHll8BXDd07gmynwFsa5aPZTTc9MShsx/yv2noAD3/DzuTUVX+OvBbTdu/ZfTpFODRwB8De4DrgacOnXmC7M9l9AnkR4x6N7uGzjxB9quB24CdzWP70JknyP4eYFeT+9qF/vDOWvYH7TszRaHj6/7vm9f9S83r/syhM0+QPYyG7nYDXwHOGTrzQg9vcyFJaq3kOQVJ0oQsCpKklkVBktSyKEiSWhYFSVLLoiBJalkUJEmt/w+kTxV027totgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#\n",
    "#                           loan amount\n",
    "#  loan_income_ratio = -----------------------\n",
    "#                          annual income \n",
    "#\n",
    "#\n",
    "\n",
    "\n",
    "# df[\"loan_income_ratio\"] = ?\n",
    "df[\"loan_income_ratio\"] = df[\"loan_amnt\"]/(df[\"annual_inc\"]+1)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "df[\"loan_income_ratio\"].plot(kind=\"hist\", bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fraction postitives in dataset: 8.60 %\n"
     ]
    }
   ],
   "source": [
    "# also, the label needs creation. This column should be = 1\n",
    "# if the \"loan_status\" column is one of these values:\n",
    "#\n",
    "#     \"Default\", \"In Grace Period\", \"Late (31-120 days)\"\n",
    "# \n",
    "# and = 0 otherwise\n",
    "# df[\"target\"] = ?\n",
    "\n",
    "\n",
    "\n",
    "bad_loan = [\"Default\", \n",
    "            \"In Grace Period\", \n",
    "            \"Late (31-120 days)\"] # label value 1\n",
    "\n",
    "good_loan = [\"Fully Paid\"] # label value 0\n",
    "\n",
    "df[\"target\"] = 0\n",
    "df.loc[df[\"loan_status\"].isin(bad_loan), \"target\"] = 1\n",
    "df.drop(columns=[\"loan_status\"], inplace=True)\n",
    "\n",
    "fraction = df.target.mean()*100\n",
    "print(f\"fraction postitives in dataset: {fraction:.2f} %\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 04 feature engineering\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "## 4.1 Scale numerical features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# \n",
    "# The task here is to scale all numerical features. You are free to \n",
    "# choose how you scale, some suggestions are minmax, standard \n",
    "# scaling (mean=0 and variance = 1) or similar\n",
    "# \n",
    "#\n",
    "# either you can do this by direct calculation (i.e df[col]-df[col].mean() etc )\n",
    "# or by using a popular package called scikit-learn. Here, you may want to look \n",
    "# into the sklearn.preprocessing library \n",
    "# https://scikit-learn.org/stable/modules/classes.html#module-sklearn.preprocessing\n",
    "#\n",
    "\n",
    "df[numeric_cols] = ((df[numeric_cols]-df[numeric_cols].min())/\n",
    "                    (df[numeric_cols].max() - df[numeric_cols].min()))\n",
    "\n",
    "### OR ###\n",
    "\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "scaler = MinMaxScaler()\n",
    "df[numeric_cols] = scaler.fit_transform(df[numeric_cols])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 4.2 Onehot encode categorical columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>id</th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>emp_title</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>desc</th>\n",
       "      <th>title</th>\n",
       "      <th>nr_payment_remarks</th>\n",
       "      <th>nr_loans</th>\n",
       "      <th>loan_income_ratio</th>\n",
       "      <th>target</th>\n",
       "      <th>...</th>\n",
       "      <th>addr_state_SD</th>\n",
       "      <th>addr_state_TN</th>\n",
       "      <th>addr_state_TX</th>\n",
       "      <th>addr_state_UT</th>\n",
       "      <th>addr_state_VA</th>\n",
       "      <th>addr_state_VT</th>\n",
       "      <th>addr_state_WA</th>\n",
       "      <th>addr_state_WI</th>\n",
       "      <th>addr_state_WV</th>\n",
       "      <th>addr_state_WY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>970906</td>\n",
       "      <td>0.394118</td>\n",
       "      <td>covanta energy</td>\n",
       "      <td>0.035184</td>\n",
       "      <td>Borrower added on 10/27/11 &gt; purchace new ai...</td>\n",
       "      <td>personal</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.146552</td>\n",
       "      <td>0.191997</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>634792</td>\n",
       "      <td>0.435294</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0.015638</td>\n",
       "      <td>Borrower added on 12/20/10 &gt; renovations and...</td>\n",
       "      <td>dukeberbatim</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.043103</td>\n",
       "      <td>0.438877</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16371827</td>\n",
       "      <td>0.323529</td>\n",
       "      <td>Sales Engineer</td>\n",
       "      <td>0.041700</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.136362</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45644356</td>\n",
       "      <td>0.911765</td>\n",
       "      <td>IT Manager</td>\n",
       "      <td>0.060245</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Credit card refinancing</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.387931</td>\n",
       "      <td>0.255998</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11946125</td>\n",
       "      <td>0.147059</td>\n",
       "      <td>store manager</td>\n",
       "      <td>0.024158</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Credit card refinancing</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.181034</td>\n",
       "      <td>0.113205</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 89 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id  loan_amnt       emp_title  annual_inc  \\\n",
       "0    970906   0.394118  covanta energy    0.035184   \n",
       "1    634792   0.435294         Unknown    0.015638   \n",
       "2  16371827   0.323529  Sales Engineer    0.041700   \n",
       "3  45644356   0.911765      IT Manager    0.060245   \n",
       "4  11946125   0.147059  store manager     0.024158   \n",
       "\n",
       "                                                desc                    title  \\\n",
       "0    Borrower added on 10/27/11 > purchace new ai...                 personal   \n",
       "1    Borrower added on 12/20/10 > renovations and...             dukeberbatim   \n",
       "2                                            Unknown       Debt consolidation   \n",
       "3                                            Unknown  Credit card refinancing   \n",
       "4                                            Unknown  Credit card refinancing   \n",
       "\n",
       "   nr_payment_remarks  nr_loans  loan_income_ratio  target  ...  \\\n",
       "0                 0.0  0.146552           0.191997       0  ...   \n",
       "1                 0.0  0.043103           0.438877       0  ...   \n",
       "2                 0.0  0.250000           0.136362       0  ...   \n",
       "3                 0.0  0.387931           0.255998       0  ...   \n",
       "4                 0.0  0.181034           0.113205       0  ...   \n",
       "\n",
       "   addr_state_SD  addr_state_TN  addr_state_TX  addr_state_UT  addr_state_VA  \\\n",
       "0            0.0            0.0            0.0            0.0            1.0   \n",
       "1            0.0            0.0            0.0            0.0            0.0   \n",
       "2            0.0            0.0            0.0            0.0            0.0   \n",
       "3            0.0            0.0            0.0            0.0            0.0   \n",
       "4            0.0            0.0            0.0            0.0            0.0   \n",
       "\n",
       "   addr_state_VT  addr_state_WA  addr_state_WI  addr_state_WV  addr_state_WY  \n",
       "0            0.0            0.0            0.0            0.0            0.0  \n",
       "1            0.0            0.0            0.0            0.0            0.0  \n",
       "2            0.0            0.0            0.0            0.0            0.0  \n",
       "3            0.0            0.0            0.0            0.0            0.0  \n",
       "4            0.0            0.0            0.0            0.0            0.0  \n",
       "\n",
       "[5 rows x 89 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categorical_cols = [\"term\", \"emp_length\", \"home_ownership\", \"purpose\", \"addr_state\"]\n",
    "\n",
    "\n",
    "# Now, the task is to one-hot encode the categorical columns. \n",
    "# for this, pd.get_dummies may be one useful function. Or you may want to look at the \n",
    "# https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.OneHotEncoder.html\n",
    "    \n",
    "# \n",
    "df_onehot = pd.get_dummies(df[categorical_cols])\n",
    "\n",
    "### OR ###\n",
    "\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "ohe = OneHotEncoder(sparse =False)\n",
    "df_onehot = ohe.fit_transform(df[categorical_cols])\n",
    "df_onehot = pd.DataFrame(df_onehot, columns=ohe.get_feature_names(categorical_cols))\n",
    "\n",
    "df = df.merge(df_onehot, left_index=True, right_index=True)\n",
    "df.drop(columns=categorical_cols, inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 4.3 Bonus - text features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "### Helper functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[nltk_data] Downloading package stopwords to /home/gurra/nltk_data...\n",
      "[nltk_data]   Package stopwords is already up-to-date!\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "#\n",
    "# In this section, you are supposed to make the text infomation more explicit for the ML model.\n",
    "# The end goal is to make a numerical representation of the text data, and you are free to \n",
    "# choose whatever method you feel is suitable. \n",
    "# For example, you can read up on the TFIDF approach (Term Frequency-Inverse Document Freuency)\n",
    "# (e.g. https://scikit-learn.org/stable/modules/generated/sklearn.feature_extraction.text.TfidfVectorizer.html)\n",
    "# \n",
    "# or use a USE, universal sentence encoder, such as examplified here: \n",
    "# https://tfhub.dev/google/universal-sentence-encoder/1\n",
    "#\n",
    "# However the method, you will need to clean the text data. This function may come in handy for this:\n",
    "#\n",
    "#\n",
    "# def clean_text_values(df, rx='[^0-9a-zA-Z]+', rep = \"_\"):\n",
    "#     \"\"\"\n",
    "#     clean text data in df, using regex specified in rx. \n",
    "#     Substitute with the value of argument \"rep\"\n",
    "#     \"\"\"\n",
    "#         df = df.replace(\n",
    "#             rx, rep, regex=True).astype(str)\n",
    "        \n",
    "#         return df\n",
    "#\n",
    "#  \n",
    "# \n",
    "    \n",
    "\n",
    "import nltk\n",
    "from nltk.corpus import stopwords\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "nltk.download('stopwords')\n",
    "\n",
    "\n",
    "def clean_text_values(df, rx='[^0-9a-zA-Z]+', rep = \"_\"):\n",
    "    \"\"\"\n",
    "    clean text data in df, using regex specified in rx. \n",
    "    Substitute with the value of argument \"rep\"\n",
    "    \"\"\"\n",
    "    df = df.replace(\n",
    "        rx, rep, regex=True).astype(str).str.lower()\n",
    "\n",
    "    return df\n",
    "\n",
    "    \n",
    "class DenseTfidfVectorizer(TfidfVectorizer):\n",
    "\n",
    "    def transform(self, raw_documents, copy=True, id_str=''):\n",
    "        X = super().transform(raw_documents, copy=copy)\n",
    "        df = pd.DataFrame(X.toarray(), columns=self.get_feature_names())\n",
    "        df.columns = [id_str + x.replace(' ', '_') for x in df.columns.values]\n",
    "        return df\n",
    "\n",
    "    def fit_transform(self, raw_documents, y=None, id_str=''):\n",
    "        X = super().fit_transform(raw_documents, y=y)\n",
    "        df = pd.DataFrame(X.toarray(), columns=self.get_feature_names())\n",
    "        df.columns = [id_str + x.replace(' ', '_') for x in df.columns.values]\n",
    "        return df\n",
    "    \n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### clean text fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      Borrower added on 10/27/11 > purchace new ai...\n",
      "1      Borrower added on 12/20/10 > renovations and...\n",
      "2                           Unknown Debt consolidation\n",
      "3                      Unknown Credit card refinancing\n",
      "4                      Unknown Credit card refinancing\n",
      "5     558864 added on 10/17/09 > This loan will hel...\n",
      "6                      Unknown Credit card refinancing\n",
      "7      Borrower added on 12/15/11 > This loan will ...\n",
      "8                             Unknown Home improvement\n",
      "9                           Unknown Debt consolidation\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0     borrower added on 10 27 11 purchace new aircr...\n",
       "1     borrower added on 12 20 10 renovations and im...\n",
       "2                           unknown debt consolidation\n",
       "3                      unknown credit card refinancing\n",
       "4                      unknown credit card refinancing\n",
       "5     558864 added on 10 17 09 this loan will help ...\n",
       "6                      unknown credit card refinancing\n",
       "7     borrower added on 12 15 11 this loan will be ...\n",
       "8                             unknown home improvement\n",
       "9                           unknown debt consolidation\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "textfield_1 = df[\"desc\"] + ' ' + df[\"title\"]\n",
    "textfield_2 = df[\"emp_title\"]\n",
    "\n",
    "print(textfield_1.head(10))\n",
    "\n",
    "textfield_1 = clean_text_values(textfield_1, rep=' ')\n",
    "textfield_2 = clean_text_values(textfield_2, rep=' ')\n",
    "\n",
    "textfield_1.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### define the most common words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'a',\n",
       " 'about',\n",
       " 'above',\n",
       " 'added',\n",
       " 'after',\n",
       " 'again',\n",
       " 'against',\n",
       " 'ain',\n",
       " 'all',\n",
       " 'am',\n",
       " 'an',\n",
       " 'and',\n",
       " 'any',\n",
       " 'are',\n",
       " 'aren',\n",
       " \"aren't\",\n",
       " 'as',\n",
       " 'at',\n",
       " 'be',\n",
       " 'because',\n",
       " 'been',\n",
       " 'before',\n",
       " 'being',\n",
       " 'below',\n",
       " 'between',\n",
       " 'borrower',\n",
       " 'both',\n",
       " 'br',\n",
       " 'but',\n",
       " 'by',\n",
       " 'can',\n",
       " 'couldn',\n",
       " \"couldn't\",\n",
       " 'd',\n",
       " 'did',\n",
       " 'didn',\n",
       " \"didn't\",\n",
       " 'do',\n",
       " 'does',\n",
       " 'doesn',\n",
       " \"doesn't\",\n",
       " 'doing',\n",
       " 'don',\n",
       " \"don't\",\n",
       " 'down',\n",
       " 'during',\n",
       " 'each',\n",
       " 'few',\n",
       " 'for',\n",
       " 'from',\n",
       " 'further',\n",
       " 'had',\n",
       " 'hadn',\n",
       " \"hadn't\",\n",
       " 'has',\n",
       " 'hasn',\n",
       " \"hasn't\",\n",
       " 'have',\n",
       " 'haven',\n",
       " \"haven't\",\n",
       " 'having',\n",
       " 'he',\n",
       " 'her',\n",
       " 'here',\n",
       " 'hers',\n",
       " 'herself',\n",
       " 'him',\n",
       " 'himself',\n",
       " 'his',\n",
       " 'how',\n",
       " 'i',\n",
       " 'if',\n",
       " 'in',\n",
       " 'into',\n",
       " 'is',\n",
       " 'isn',\n",
       " \"isn't\",\n",
       " 'it',\n",
       " \"it's\",\n",
       " 'its',\n",
       " 'itself',\n",
       " 'just',\n",
       " 'll',\n",
       " 'm',\n",
       " 'ma',\n",
       " 'me',\n",
       " 'mightn',\n",
       " \"mightn't\",\n",
       " 'more',\n",
       " 'most',\n",
       " 'mustn',\n",
       " \"mustn't\",\n",
       " 'my',\n",
       " 'myself',\n",
       " 'needn',\n",
       " \"needn't\",\n",
       " 'no',\n",
       " 'nor',\n",
       " 'not',\n",
       " 'now',\n",
       " 'o',\n",
       " 'of',\n",
       " 'off',\n",
       " 'on',\n",
       " 'once',\n",
       " 'only',\n",
       " 'or',\n",
       " 'other',\n",
       " 'our',\n",
       " 'ours',\n",
       " 'ourselves',\n",
       " 'out',\n",
       " 'over',\n",
       " 'own',\n",
       " 're',\n",
       " 's',\n",
       " 'same',\n",
       " 'shan',\n",
       " \"shan't\",\n",
       " 'she',\n",
       " \"she's\",\n",
       " 'should',\n",
       " \"should've\",\n",
       " 'shouldn',\n",
       " \"shouldn't\",\n",
       " 'so',\n",
       " 'some',\n",
       " 'such',\n",
       " 't',\n",
       " 'than',\n",
       " 'that',\n",
       " \"that'll\",\n",
       " 'the',\n",
       " 'their',\n",
       " 'theirs',\n",
       " 'them',\n",
       " 'themselves',\n",
       " 'then',\n",
       " 'there',\n",
       " 'these',\n",
       " 'they',\n",
       " 'this',\n",
       " 'those',\n",
       " 'through',\n",
       " 'to',\n",
       " 'too',\n",
       " 'under',\n",
       " 'unknown',\n",
       " 'until',\n",
       " 'up',\n",
       " 've',\n",
       " 'very',\n",
       " 'was',\n",
       " 'wasn',\n",
       " \"wasn't\",\n",
       " 'we',\n",
       " 'were',\n",
       " 'weren',\n",
       " \"weren't\",\n",
       " 'what',\n",
       " 'when',\n",
       " 'where',\n",
       " 'which',\n",
       " 'while',\n",
       " 'who',\n",
       " 'whom',\n",
       " 'why',\n",
       " 'will',\n",
       " 'with',\n",
       " 'won',\n",
       " \"won't\",\n",
       " 'wouldn',\n",
       " \"wouldn't\",\n",
       " 'y',\n",
       " 'you',\n",
       " \"you'd\",\n",
       " \"you'll\",\n",
       " \"you're\",\n",
       " \"you've\",\n",
       " 'your',\n",
       " 'yours',\n",
       " 'yourself',\n",
       " 'yourselves'}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "  \n",
    "    \n",
    "stopWords = set(stopwords.words('english') + [\"br\", \"unknown\", \"added\", \"borrower\"])\n",
    "\n",
    "stopWords"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### TFIDF-transformation of text fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>Job_title_include_account</th>\n",
       "      <th>Job_title_include_account_manager</th>\n",
       "      <th>Job_title_include_accountant</th>\n",
       "      <th>Job_title_include_accounting</th>\n",
       "      <th>Job_title_include_administration</th>\n",
       "      <th>Job_title_include_administrative</th>\n",
       "      <th>Job_title_include_administrative_assistant</th>\n",
       "      <th>Job_title_include_administrator</th>\n",
       "      <th>Job_title_include_advisor</th>\n",
       "      <th>Job_title_include_agent</th>\n",
       "      <th>...</th>\n",
       "      <th>Job_title_include_usaf</th>\n",
       "      <th>Job_title_include_valley</th>\n",
       "      <th>Job_title_include_vice</th>\n",
       "      <th>Job_title_include_vice_president</th>\n",
       "      <th>Job_title_include_vp</th>\n",
       "      <th>Job_title_include_warehouse</th>\n",
       "      <th>Job_title_include_wells</th>\n",
       "      <th>Job_title_include_wells_fargo</th>\n",
       "      <th>Job_title_include_worker</th>\n",
       "      <th>Job_title_include_york</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 200 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Job_title_include_account  Job_title_include_account_manager  \\\n",
       "0                        0.0                                0.0   \n",
       "1                        0.0                                0.0   \n",
       "2                        0.0                                0.0   \n",
       "3                        0.0                                0.0   \n",
       "4                        0.0                                0.0   \n",
       "\n",
       "   Job_title_include_accountant  Job_title_include_accounting  \\\n",
       "0                           0.0                           0.0   \n",
       "1                           0.0                           0.0   \n",
       "2                           0.0                           0.0   \n",
       "3                           0.0                           0.0   \n",
       "4                           0.0                           0.0   \n",
       "\n",
       "   Job_title_include_administration  Job_title_include_administrative  \\\n",
       "0                               0.0                               0.0   \n",
       "1                               0.0                               0.0   \n",
       "2                               0.0                               0.0   \n",
       "3                               0.0                               0.0   \n",
       "4                               0.0                               0.0   \n",
       "\n",
       "   Job_title_include_administrative_assistant  \\\n",
       "0                                         0.0   \n",
       "1                                         0.0   \n",
       "2                                         0.0   \n",
       "3                                         0.0   \n",
       "4                                         0.0   \n",
       "\n",
       "   Job_title_include_administrator  Job_title_include_advisor  \\\n",
       "0                              0.0                        0.0   \n",
       "1                              0.0                        0.0   \n",
       "2                              0.0                        0.0   \n",
       "3                              0.0                        0.0   \n",
       "4                              0.0                        0.0   \n",
       "\n",
       "   Job_title_include_agent  ...  Job_title_include_usaf  \\\n",
       "0                      0.0  ...                     0.0   \n",
       "1                      0.0  ...                     0.0   \n",
       "2                      0.0  ...                     0.0   \n",
       "3                      0.0  ...                     0.0   \n",
       "4                      0.0  ...                     0.0   \n",
       "\n",
       "   Job_title_include_valley  Job_title_include_vice  \\\n",
       "0                       0.0                     0.0   \n",
       "1                       0.0                     0.0   \n",
       "2                       0.0                     0.0   \n",
       "3                       0.0                     0.0   \n",
       "4                       0.0                     0.0   \n",
       "\n",
       "   Job_title_include_vice_president  Job_title_include_vp  \\\n",
       "0                               0.0                   0.0   \n",
       "1                               0.0                   0.0   \n",
       "2                               0.0                   0.0   \n",
       "3                               0.0                   0.0   \n",
       "4                               0.0                   0.0   \n",
       "\n",
       "   Job_title_include_warehouse  Job_title_include_wells  \\\n",
       "0                          0.0                      0.0   \n",
       "1                          0.0                      0.0   \n",
       "2                          0.0                      0.0   \n",
       "3                          0.0                      0.0   \n",
       "4                          0.0                      0.0   \n",
       "\n",
       "   Job_title_include_wells_fargo  Job_title_include_worker  \\\n",
       "0                            0.0                       0.0   \n",
       "1                            0.0                       0.0   \n",
       "2                            0.0                       0.0   \n",
       "3                            0.0                       0.0   \n",
       "4                            0.0                       0.0   \n",
       "\n",
       "   Job_title_include_york  \n",
       "0                     0.0  \n",
       "1                     0.0  \n",
       "2                     0.0  \n",
       "3                     0.0  \n",
       "4                     0.0  \n",
       "\n",
       "[5 rows x 200 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tf1 = DenseTfidfVectorizer(max_features=200, max_df=0.1, stop_words=stopWords, \n",
    "                          use_idf=True, binary=False, norm=None, ngram_range=(1,5))\n",
    "\n",
    "df_text1 = tf1.fit_transform(textfield_1, id_str=\"Description_include_\")\n",
    "tf2 = DenseTfidfVectorizer(max_features=200, max_df=0.1, stop_words=stopWords, \n",
    "                          use_idf=True, binary=False, norm=None, ngram_range=(1,5))\n",
    "\n",
    "df_text2 = tf2.fit_transform(textfield_2, id_str=\"Job_title_include_\")\n",
    "\n",
    "\n",
    "df_text2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### merge all data into one dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 486)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = (\n",
    "    df.merge(df_text1, left_index=True, right_index=True)\n",
    "    .merge(df_text2, left_index=True, right_index=True)\n",
    ")\n",
    "df.drop(columns=[\"emp_title\", \"desc\", \"title\"], inplace=True)\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>id</th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>nr_payment_remarks</th>\n",
       "      <th>nr_loans</th>\n",
       "      <th>loan_income_ratio</th>\n",
       "      <th>target</th>\n",
       "      <th>term_ 36 months</th>\n",
       "      <th>term_ 60 months</th>\n",
       "      <th>emp_length_1 year</th>\n",
       "      <th>...</th>\n",
       "      <th>Job_title_include_usaf</th>\n",
       "      <th>Job_title_include_valley</th>\n",
       "      <th>Job_title_include_vice</th>\n",
       "      <th>Job_title_include_vice_president</th>\n",
       "      <th>Job_title_include_vp</th>\n",
       "      <th>Job_title_include_warehouse</th>\n",
       "      <th>Job_title_include_wells</th>\n",
       "      <th>Job_title_include_wells_fargo</th>\n",
       "      <th>Job_title_include_worker</th>\n",
       "      <th>Job_title_include_york</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>970906</td>\n",
       "      <td>0.394118</td>\n",
       "      <td>0.035184</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.146552</td>\n",
       "      <td>0.191997</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>634792</td>\n",
       "      <td>0.435294</td>\n",
       "      <td>0.015638</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.043103</td>\n",
       "      <td>0.438877</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16371827</td>\n",
       "      <td>0.323529</td>\n",
       "      <td>0.041700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.136362</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45644356</td>\n",
       "      <td>0.911765</td>\n",
       "      <td>0.060245</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.387931</td>\n",
       "      <td>0.255998</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11946125</td>\n",
       "      <td>0.147059</td>\n",
       "      <td>0.024158</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.181034</td>\n",
       "      <td>0.113205</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 486 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id  loan_amnt  annual_inc  nr_payment_remarks  nr_loans  \\\n",
       "0    970906   0.394118    0.035184                 0.0  0.146552   \n",
       "1    634792   0.435294    0.015638                 0.0  0.043103   \n",
       "2  16371827   0.323529    0.041700                 0.0  0.250000   \n",
       "3  45644356   0.911765    0.060245                 0.0  0.387931   \n",
       "4  11946125   0.147059    0.024158                 0.0  0.181034   \n",
       "\n",
       "   loan_income_ratio  target  term_ 36 months  term_ 60 months  \\\n",
       "0           0.191997       0              1.0              0.0   \n",
       "1           0.438877       0              1.0              0.0   \n",
       "2           0.136362       0              1.0              0.0   \n",
       "3           0.255998       0              0.0              1.0   \n",
       "4           0.113205       0              1.0              0.0   \n",
       "\n",
       "   emp_length_1 year  ...  Job_title_include_usaf  Job_title_include_valley  \\\n",
       "0                0.0  ...                     0.0                       0.0   \n",
       "1                0.0  ...                     0.0                       0.0   \n",
       "2                0.0  ...                     0.0                       0.0   \n",
       "3                0.0  ...                     0.0                       0.0   \n",
       "4                0.0  ...                     0.0                       0.0   \n",
       "\n",
       "   Job_title_include_vice  Job_title_include_vice_president  \\\n",
       "0                     0.0                               0.0   \n",
       "1                     0.0                               0.0   \n",
       "2                     0.0                               0.0   \n",
       "3                     0.0                               0.0   \n",
       "4                     0.0                               0.0   \n",
       "\n",
       "   Job_title_include_vp  Job_title_include_warehouse  Job_title_include_wells  \\\n",
       "0                   0.0                          0.0                      0.0   \n",
       "1                   0.0                          0.0                      0.0   \n",
       "2                   0.0                          0.0                      0.0   \n",
       "3                   0.0                          0.0                      0.0   \n",
       "4                   0.0                          0.0                      0.0   \n",
       "\n",
       "   Job_title_include_wells_fargo  Job_title_include_worker  \\\n",
       "0                            0.0                       0.0   \n",
       "1                            0.0                       0.0   \n",
       "2                            0.0                       0.0   \n",
       "3                            0.0                       0.0   \n",
       "4                            0.0                       0.0   \n",
       "\n",
       "   Job_title_include_york  \n",
       "0                     0.0  \n",
       "1                     0.0  \n",
       "2                     0.0  \n",
       "3                     0.0  \n",
       "4                     0.0  \n",
       "\n",
       "[5 rows x 486 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 05 write back table to a database "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "#\n",
    "# Now the task is to upload the prepared data to the database. \n",
    "# for this we have a postgres db hosted on AWS RDS\n",
    "# Below is the credentials you need to connect. now the task is to upload \n",
    "# the table to the \"de_course\" database (\"public\" schema). \n",
    "# You may use the pd.to_sql command with the egine directly, \n",
    "# however you may find faster options in the link below...\n",
    "# https://stackoverflow.com/questions/23103962/how-to-write-dataframe-to-postgres-table\n",
    "#\n",
    "# # NOTE: Name the table \"prepared_data_{NAME}\" where the name is you name.\n",
    "\n",
    "\n",
    "\n",
    "port='5432' \n",
    "user= \"postgres\"\n",
    "password= \"bizware_training_2020\"\n",
    "server_url = \"data-engineering.c83yueos1s3z.eu-north-1.rds.amazonaws.com\"\n",
    "db = \"de_course\"\n",
    "engine = create_engine(f'postgresql+psycopg2://{user}:{password}@{server_url}:{port}/{db}')\n",
    "\n",
    "\n",
    "\n",
    "def postgres_insert_copy(table, conn, keys, data_iter):\n",
    "    # gets a DBAPI connection that can provide a cursor\n",
    "    dbapi_conn = conn.connection\n",
    "    with dbapi_conn.cursor() as cur:\n",
    "        s_buf = StringIO()\n",
    "        writer = csv.writer(s_buf)\n",
    "        writer.writerows(data_iter)\n",
    "        s_buf.seek(0)\n",
    "\n",
    "        columns = ', '.join('\"{}\"'.format(k) for k in keys)\n",
    "        if table.schema:\n",
    "            table_name = '{}.{}'.format(table.schema, table.name)\n",
    "        else:\n",
    "            table_name = table.name\n",
    "\n",
    "        sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(\n",
    "            table_name, columns)\n",
    "        cur.copy_expert(sql=sql, file=s_buf)\n",
    "\n",
    "df.to_sql('processed_data_gustav_eklund', engine, method=postgres_insert_copy, if_exists=\"replace\")\n"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}