loan_data_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.set_printoptions(suppress=True, linewidth=100, precision=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### import data\n",
    "\n",
    "Our CSV is ill formatted, i.e. separarted by ';' . Hence we will load the text."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[        nan,         nan,         nan, ...,         nan,         nan,         nan],\n",
       "       [48010226.  ,         nan,    35000.  , ...,         nan,         nan,     9452.96],\n",
       "       [57693261.  ,         nan,    30000.  , ...,         nan,         nan,     4679.7 ],\n",
       "       ...,\n",
       "       [50415990.  ,         nan,    10000.  , ...,         nan,         nan,     2185.64],\n",
       "       [46154151.  ,         nan,         nan, ...,         nan,         nan,     3199.4 ],\n",
       "       [66055249.  ,         nan,    10000.  , ...,         nan,         nan,      301.9 ]])"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data_np = np.genfromtxt(\"datasets/loan-data.csv\", delimiter=';', autostrip=True, encoding = \"cp855\")\n",
    "raw_data_np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking for incomplete data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "88019"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.isnan(raw_data_np).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1r/3db931yx4ys8zd46l5tbk6x80000gn/T/ipykernel_9005/3416427304.py:2: RuntimeWarning: Mean of empty slice\n",
      "  temp_mean = np.nanmean(raw_data_np, axis=0)\n"
     ]
    }
   ],
   "source": [
    "temp_fill = np.nanmax(raw_data_np)+1\n",
    "temp_mean = np.nanmean(raw_data_np, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at the warning, we can say that there exists a column full of NAN. The genfromtext method casts string to NAN, which could have led to \n",
    "such large NANs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([54015809.19,         nan,    15273.46,         nan,    15311.04,         nan,       16.62,\n",
       "            440.92,         nan,         nan,         nan,         nan,         nan,     3143.85])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_mean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets store the raw data stats in temporary variable. Create a new array and store the minimum, average, and the maximum values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3, 14)\n",
      "[[  373332.           nan     1000.           nan     1000.           nan        6.         31.42\n",
      "          nan         nan         nan         nan         nan        0.  ]\n",
      " [54015809.19         nan    15273.46         nan    15311.04         nan       16.62      440.92\n",
      "          nan         nan         nan         nan         nan     3143.85]\n",
      " [68616519.           nan    35000.           nan    35000.           nan       28.99     1372.97\n",
      "          nan         nan         nan         nan         nan    41913.62]]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1r/3db931yx4ys8zd46l5tbk6x80000gn/T/ipykernel_9005/445344237.py:1: RuntimeWarning: All-NaN slice encountered\n",
      "  temp_stats = np.array([np.nanmin(raw_data_np, axis=0), temp_mean, np.nanmax(raw_data_np, axis=0)])\n"
     ]
    }
   ],
   "source": [
    "temp_stats = np.array([np.nanmin(raw_data_np, axis=0), temp_mean, np.nanmax(raw_data_np, axis=0)])\n",
    "print(temp_stats.shape)\n",
    "print(temp_stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Splitting the datasetes (numeric and text)\n",
    "\n",
    "First, Split the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1,  3,  5,  8,  9, 10, 11, 12])"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_string = np.argwhere(np.isnan(temp_mean)).squeeze()\n",
    "columns_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0,  2,  4,  6,  7, 13])"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_numeric = np.argwhere(np.isnan(temp_mean)== False).squeeze()\n",
    "columns_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importing data based on type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1r/3db931yx4ys8zd46l5tbk6x80000gn/T/ipykernel_9005/83039153.py:1: DeprecationWarning: `np.str` is a deprecated alias for the builtin `str`. To silence this warning, use `str` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.str_` here.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  loan_data_string = np.genfromtxt(\"datasets/loan-data.csv\", delimiter=';', skip_header=1, usecols=columns_string, dtype=np.str, encoding = \"cp855\")\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([['May-15', 'Current', ' 36 months', ..., 'Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=48010226', 'CA'],\n",
       "       ['', 'Current', ' 36 months', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=57693261', 'NY'],\n",
       "       ['Sep-15', 'Current', ' 36 months', ..., 'Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=59432726', 'PA'],\n",
       "       ...,\n",
       "       ['Jun-15', 'Current', ' 36 months', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=50415990', 'CA'],\n",
       "       ['Apr-15', 'Current', ' 36 months', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=46154151', 'OH'],\n",
       "       ['Dec-15', 'Current', ' 36 months', ..., '',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=66055249', 'IL']],\n",
       "      dtype='<U69')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string = np.genfromtxt(\"datasets/loan-data.csv\", delimiter=';', skip_header=1, usecols=columns_string, dtype=np.str, encoding = \"cp855\")\n",
    "loan_data_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    35000.  ,       13.33,     1184.86,     9452.96],\n",
       "       [57693261.  ,    30000.  ,    30000.  , 68616520.  ,      938.57,     4679.7 ],\n",
       "       [59432726.  ,    15000.  ,    15000.  , 68616520.  ,      494.86,     1969.83],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,    10000.  , 68616520.  , 68616520.  ,     2185.64],\n",
       "       [46154151.  , 68616520.  ,    10000.  ,       16.55,      354.3 ,     3199.4 ],\n",
       "       [66055249.  ,    10000.  ,    10000.  , 68616520.  ,      309.97,      301.9 ]])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric = np.genfromtxt(\"datasets/loan-data.csv\", delimiter=';', skip_header=1, usecols=columns_numeric, filling_values=temp_fill, encoding = \"cp855\")\n",
    "loan_data_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Column Names "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1r/3db931yx4ys8zd46l5tbk6x80000gn/T/ipykernel_9005/961697541.py:1: DeprecationWarning: `np.str` is a deprecated alias for the builtin `str`. To silence this warning, use `str` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.str_` here.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  all_header = np.genfromtxt(\"datasets/loan-data.csv\", delimiter=';', autostrip=True, skip_footer=raw_data_np.shape[0]-1, dtype=np.str, encoding=\"cp855\")\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array(['id', 'issue_d', 'loan_amnt', 'loan_status', 'funded_amnt', 'term', 'int_rate',\n",
       "       'installment', 'grade', 'sub_grade', 'verification_status', 'url', 'addr_state',\n",
       "       'total_pymnt'], dtype='<U19')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_header = np.genfromtxt(\"datasets/loan-data.csv\", delimiter=';', autostrip=True, skip_footer=raw_data_np.shape[0]-1, dtype=np.str, encoding=\"cp855\")\n",
    "all_header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Numeric cols: ['id' 'loan_amnt' 'funded_amnt' 'int_rate' 'installment' 'total_pymnt']\n",
      "String cols: ['issue_d' 'loan_status' 'term' 'grade' 'sub_grade' 'verification_status' 'url' 'addr_state']\n"
     ]
    }
   ],
   "source": [
    "header_numeric, header_string = all_header[columns_numeric], all_header[columns_string]\n",
    "print(f\"Numeric cols: {header_numeric}\\nString cols: {header_string}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checkpoints\n",
    "Saving a copy of data, Just to avoid resourceful computations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def checkpoint(file_name, header, data):\n",
    "    np.savez(\"checkpoints/\"+file_name, header=header, data=data)\n",
    "    checkpoint_variables = np.load(\"checkpoints/\"+file_name + '.npz')\n",
    "    return(checkpoint_variables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "checkpoint_test = checkpoint(\"checkpoint_test\", header_string, loan_data_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['May-15', 'Current', ' 36 months', ..., 'Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=48010226', 'CA'],\n",
       "       ['', 'Current', ' 36 months', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=57693261', 'NY'],\n",
       "       ['Sep-15', 'Current', ' 36 months', ..., 'Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=59432726', 'PA'],\n",
       "       ...,\n",
       "       ['Jun-15', 'Current', ' 36 months', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=50415990', 'CA'],\n",
       "       ['Apr-15', 'Current', ' 36 months', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=46154151', 'OH'],\n",
       "       ['Dec-15', 'Current', ' 36 months', ..., '',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=66055249', 'IL']],\n",
       "      dtype='<U69')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checkpoint_test['data']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.array_equal(checkpoint_test['data'], loan_data_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulationg string columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issue_d', 'loan_status', 'term', 'grade', 'sub_grade', 'verification_status', 'url',\n",
       "       'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lets make a variable descriptive\n",
    "header_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Issue date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', 'Apr-15', 'Aug-15', 'Dec-15', 'Feb-15', 'Jan-15', 'Jul-15', 'Jun-15', 'Mar-15',\n",
       "       'May-15', 'Nov-15', 'Oct-15', 'Sep-15'], dtype='<U69')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_string[0] = \"issued_date\"\n",
    "np.unique(loan_data_string[:,0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see \"\", i.e. empty value. We can see that the year is 2015. Hence we can strip the last 15 part of data without loosing insights."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['May', '', 'Sep', ..., 'Jun', 'Apr', 'Dec'], dtype='<U69')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string[:,0] = np.chararray.strip(loan_data_string[:,0], \"-15\")\n",
    "loan_data_string[:,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we represent the month values as an integer. (In order 1-12). It is memory efficient too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['5', '0', '9', ..., '6', '4', '12'], dtype='<U69')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a int type month to map\n",
    "months = np.array(['', 'Jan', 'Feb', 'Mar','Apr', 'May', 'Jun', 'Jul','Aug', 'Sep', 'Oct', 'Nov', 'Dec'])\n",
    "\n",
    "# transforming data\n",
    "for i in range(13):\n",
    "    loan_data_string[:,0] = np.where(loan_data_string[:,0] == months[i], i, loan_data_string[:,0])\n",
    "\n",
    "loan_data_string[:,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Loan Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issued_date', 'loan_status', 'term', 'grade', 'sub_grade', 'verification_status', 'url',\n",
       "       'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', 'Charged Off', 'Current', 'Default', 'Fully Paid', 'In Grace Period', 'Issued',\n",
       "       'Late (16-30 days)', 'Late (31-120 days)'], dtype='<U69')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have nine possible loam states, including a missing data. We can use the previous approach to assign a corresponding number to each status. But, Here our main concerns is the customer is in stable financial conditions that is either good or bad. Hence, we assign a simple dummy indicator whether a customer is bad or good.\n",
    "\n",
    "- Grouping techniques: \n",
    "\n",
    "1 GOOD: Fully Paid, Current, In grace Period, Issued, Short Latency\n",
    "\n",
    "0 BAD: Charged Off, Defaulted, and Missing,  Long Latency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_status = np.array(['', 'Charged Off', 'Default', 'Late (31-120 days)'])\n",
    "good_status = np.array(['Current', 'Fully Paid', 'In Grace Period', 'Issued', 'Late (16-30 days)'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_string[:,1] = np.where(np.isin(loan_data_string[:,1], bad_status), 0,1 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['0' '1']\n",
      "['1' '1' '1' ... '1' '1' '1']\n"
     ]
    }
   ],
   "source": [
    "print(np.unique(loan_data_string[:,1]))\n",
    "print(loan_data_string[:,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Term"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issued_date', 'loan_status', 'term', 'grade', 'sub_grade', 'verification_status', 'url',\n",
       "       'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', ' 36 months', ' 60 months'], dtype='<U69')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we can strip the space and months text, and rename the column name just to better understand.\n",
    "\n",
    "Since 6o months is worse term, and we take that to impute missing values.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['36', '36', '36', ..., '36', '36', '36'], dtype='<U69')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string[:,2] = np.chararray.strip(loan_data_string[:,2], \" months\")\n",
    "header_string[2] = \"term_months\"\n",
    "loan_data_string[:, 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['36', '36', '36', ..., '36', '36', '36'], dtype='<U69')"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string[:,2] = np.where(loan_data_string[:,2] == '', 60, loan_data_string[:,2])\n",
    "loan_data_string[:,2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['36', '60'], dtype='<U69')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the missing values\n",
    "np.unique(loan_data_string[:,2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The term months values require more domain knowledge, we need to know why there are just a 36 and 60 months. At this situation, you might tempted to encode the values into binary. But, we will let it be as it is.\n",
    "\n",
    "#### Grade and Subgrade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issued_date', 'loan_status', 'term_months', 'grade', 'sub_grade', 'verification_status',\n",
       "       'url', 'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', 'A', 'B', 'C', 'D', 'E', 'F', 'G'], dtype='<U69')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', 'A1', 'A2', 'A3', 'A4', 'A5', 'B1', 'B2', 'B3', 'B4', 'B5', 'C1', 'C2', 'C3', 'C4',\n",
       "       'C5', 'D1', 'D2', 'D3', 'D4', 'D5', 'E1', 'E2', 'E3', 'E4', 'E5', 'F1', 'F2', 'F3', 'F4',\n",
       "       'F5', 'G1', 'G2', 'G3', 'G4', 'G5'], dtype='<U69')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,4])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have seven categories in Grade and subgrade is further categorized into 5 different of each subgroup. Any information from subgroup can also be obtained from group, which makes the group redundant. For instance, if a subgroup is missing for group B data, It would be wise to impute B5 rather than D5."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['C3', 'A5', 'B5', ..., 'A5', 'D2', 'A4'], dtype='<U69')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i in np.unique(loan_data_string[:,3])[1:]:\n",
    "    loan_data_string[:,4] = np.where((loan_data_string[:,4] == '')& (loan_data_string[:,3] == i),\n",
    "                                       i + '5', loan_data_string[:,4]\n",
    "                                    )\n",
    "loan_data_string[:,4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['', 'A1', 'A2', 'A3', 'A4', 'A5', 'B1', 'B2', 'B3', 'B4', 'B5', 'C1', 'C2', 'C3', 'C4',\n",
       "        'C5', 'D1', 'D2', 'D3', 'D4', 'D5', 'E1', 'E2', 'E3', 'E4', 'E5', 'F1', 'F2', 'F3', 'F4',\n",
       "        'F5', 'G1', 'G2', 'G3', 'G4', 'G5'], dtype='<U69'),\n",
       " array([  9, 285, 278, 239, 323, 592, 509, 517, 530, 553, 633, 629, 567, 586, 564, 577, 391, 267,\n",
       "        250, 255, 288, 235, 162, 171, 139, 160,  94,  52,  34,  43,  24,  19,  10,   3,   7,   5]))"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,4], return_counts=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that there is still 9 empty data instances. Since it is small in number, we can drop it. But, Here is the better idea, we can assign new \n",
    "category for that data instances."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['A1', 'A2', 'A3', 'A4', 'A5', 'B1', 'B2', 'B3', 'B4', 'B5', 'C1', 'C2', 'C3', 'C4', 'C5',\n",
       "        'D1', 'D2', 'D3', 'D4', 'D5', 'E1', 'E2', 'E3', 'E4', 'E5', 'F1', 'F2', 'F3', 'F4', 'F5',\n",
       "        'G1', 'G2', 'G3', 'G4', 'G5', 'H1'], dtype='<U69'),\n",
       " array([285, 278, 239, 323, 592, 509, 517, 530, 553, 633, 629, 567, 586, 564, 577, 391, 267, 250,\n",
       "        255, 288, 235, 162, 171, 139, 160,  94,  52,  34,  43,  24,  19,  10,   3,   7,   5,   9]))"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i in np.unique(loan_data_string[:,3])[1:]:\n",
    "    loan_data_string[:,4] = np.where(loan_data_string[:,4] == '',\n",
    "                                       'H1', loan_data_string[:,4]\n",
    "                                    )\n",
    "np.unique(loan_data_string[:,4], return_counts = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we can remove the Grade column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['5', '1', '36', ..., 'Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=48010226', 'CA'],\n",
       "       ['0', '1', '36', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=57693261', 'NY'],\n",
       "       ['9', '1', '36', ..., 'Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=59432726', 'PA'],\n",
       "       ...,\n",
       "       ['6', '1', '36', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=50415990', 'CA'],\n",
       "       ['4', '1', '36', ..., 'Source Verified',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=46154151', 'OH'],\n",
       "       ['12', '1', '36', ..., '',\n",
       "        'https://www.lendingclub.com/browse/loanDetail.action?loan_id=66055249', 'IL']],\n",
       "      dtype='<U69')"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string = np.delete(loan_data_string, 3, axis=1)\n",
    "loan_data_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['issued_date' 'loan_status' 'term_months' 'grade' 'sub_grade' 'verification_status' 'url'\n",
      " 'addr_state']\n",
      "['issued_date' 'loan_status' 'term_months' 'sub_grade' 'verification_status' 'url' 'addr_state']\n"
     ]
    }
   ],
   "source": [
    "print(header_string)\n",
    "header_string = np.delete(header_string, 3)\n",
    "print(header_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets encode the subgroup. We are going to use key-value dict to map its value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'A1': 1,\n",
       " 'A2': 2,\n",
       " 'A3': 3,\n",
       " 'A4': 4,\n",
       " 'A5': 5,\n",
       " 'B1': 6,\n",
       " 'B2': 7,\n",
       " 'B3': 8,\n",
       " 'B4': 9,\n",
       " 'B5': 10,\n",
       " 'C1': 11,\n",
       " 'C2': 12,\n",
       " 'C3': 13,\n",
       " 'C4': 14,\n",
       " 'C5': 15,\n",
       " 'D1': 16,\n",
       " 'D2': 17,\n",
       " 'D3': 18,\n",
       " 'D4': 19,\n",
       " 'D5': 20,\n",
       " 'E1': 21,\n",
       " 'E2': 22,\n",
       " 'E3': 23,\n",
       " 'E4': 24,\n",
       " 'E5': 25,\n",
       " 'F1': 26,\n",
       " 'F2': 27,\n",
       " 'F3': 28,\n",
       " 'F4': 29,\n",
       " 'F5': 30,\n",
       " 'G1': 31,\n",
       " 'G2': 32,\n",
       " 'G3': 33,\n",
       " 'G4': 34,\n",
       " 'G5': 35,\n",
       " 'H1': 36}"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "keys = list(np.unique(loan_data_string[:,3]))\n",
    "values = list(range(1, np.unique(loan_data_string[:,3]).shape[0] + 1))\n",
    "mapping = dict(zip(keys, values))\n",
    "mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['13', '5', '10', ..., '5', '17', '4'], dtype='<U69')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i in np.unique(loan_data_string[:,3]):\n",
    "    loan_data_string[:,3] = np.where(loan_data_string[:,3] == i, mapping[i], loan_data_string[:,3])\n",
    "\n",
    "loan_data_string[:,3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '2', '20', '21', '22',\n",
       "       '23', '24', '25', '26', '27', '28', '29', '3', '30', '31', '32', '33', '34', '35', '36',\n",
       "       '4', '5', '6', '7', '8', '9'], dtype='<U69')"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Verification Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issued_date', 'loan_status', 'term_months', 'sub_grade', 'verification_status', 'url',\n",
       "       'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', 'Not Verified', 'Source Verified', 'Verified'], dtype='<U69')"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,4])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we can assume the missing values are not verified source. Hence, we group them into good and bad source, as we did for loan status. \n",
    "\n",
    "1 Verified \n",
    "\n",
    "0 Not verified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0', '1'], dtype='<U69')"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string[:,4] = np.where((loan_data_string[:,4] == '') | (loan_data_string[:,4] == 'Not Verified'), 0 , 1)\n",
    "np.unique(loan_data_string[:,4])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['https://www.lendingclub.com/browse/loanDetail.action?loan_id=48010226',\n",
       "       'https://www.lendingclub.com/browse/loanDetail.action?loan_id=57693261',\n",
       "       'https://www.lendingclub.com/browse/loanDetail.action?loan_id=59432726', ...,\n",
       "       'https://www.lendingclub.com/browse/loanDetail.action?loan_id=50415990',\n",
       "       'https://www.lendingclub.com/browse/loanDetail.action?loan_id=46154151',\n",
       "       'https://www.lendingclub.com/browse/loanDetail.action?loan_id=66055249'], dtype='<U69')"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string[:,5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we can see the domain name is exact for url data. But, we have different loan_id associated with it. Lets verify this with loan ID, whether they are same or not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['48010226', '57693261', '59432726', ..., '50415990', '46154151', '66055249'], dtype='<U69')"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# strip the data\n",
    "loan_data_string[:,5] = np.chararray.strip(loan_data_string[:,5], \"https://www.lendingclub.com/browse/loanDetail.action?loan_id=\")\n",
    "loan_data_string[:,5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'issue_d', 'loan_amnt', 'loan_status', 'funded_amnt', 'term', 'int_rate',\n",
       "       'installment', 'grade', 'sub_grade', 'verification_status', 'url', 'addr_state',\n",
       "       'total_pymnt'], dtype='<U19')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([48010226., 57693261., 59432726., 53222800., 57803010.])"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric[:,0][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['48010226', '57693261', '59432726', '53222800', '57803010'], dtype='<U69')"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string[:,5][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are confirmed that both are exact except ID in numeric and URL id is text. Now can convert it into int but we can extract same information using ID column. Hence, we drop the URL column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['0' '1' '36' '5' '1' 'NY']\n",
      "['issued_date' 'loan_status' 'term_months' 'sub_grade' 'verification_status' 'addr_state']\n"
     ]
    }
   ],
   "source": [
    "loan_data_string = np.delete(loan_data_string, 5, axis=1)\n",
    "header_string = np.delete(header_string, 5)\n",
    "print(loan_data_string[1,:])\n",
    "print(header_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### State Address "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issued_date', 'loan_status', 'term_months', 'sub_grade', 'verification_status',\n",
       "       'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['', 'AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'IL', 'IN',\n",
       "        'KS', 'KY', 'LA', 'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH',\n",
       "        'NJ', 'NM', 'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA',\n",
       "        'VT', 'WA', 'WI', 'WV', 'WY'], dtype='<U69'),\n",
       " array([ 500,   26,  119,   74,  220, 1336,  201,  143,   27,   27,  690,  321,   44,  389,  152,\n",
       "          84,   84,  116,  210,  222,   10,  267,  156,  160,   61,   28,  261,   16,   25,   58,\n",
       "         341,   57,  130,  777,  312,   83,  108,  320,   40,  107,   24,  143,  758,   74,  242,\n",
       "          17,  216,  148,   49,   27]))"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,-1], return_counts=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have missing state, IA. The values above are sorted based on State name. Lets, do sort them based on number of application.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['CA', 'NY', 'TX', 'FL', '', 'IL', 'NJ', 'GA', 'PA', 'OH', 'MI', 'NC', 'VA', 'MD', 'AZ',\n",
       "        'WA', 'MA', 'CO', 'MO', 'MN', 'IN', 'WI', 'CT', 'TN', 'NV', 'AL', 'LA', 'OR', 'SC', 'KY',\n",
       "        'KS', 'OK', 'UT', 'AR', 'MS', 'NH', 'NM', 'WV', 'HI', 'RI', 'MT', 'DE', 'DC', 'WY', 'AK',\n",
       "        'NE', 'SD', 'VT', 'ND', 'ME'], dtype='<U69'),\n",
       " array([1336,  777,  758,  690,  500,  389,  341,  321,  320,  312,  267,  261,  242,  222,  220,\n",
       "         216,  210,  201,  160,  156,  152,  148,  143,  143,  130,  119,  116,  108,  107,   84,\n",
       "          84,   83,   74,   74,   61,   58,   57,   49,   44,   40,   28,   27,   27,   27,   26,\n",
       "          25,   24,   17,   16,   10]))"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_names, states_count = np.unique(loan_data_string[:,-1], return_counts=True)\n",
    "states_count_sorted = np.argsort(-states_count)\n",
    "states_names[states_count_sorted], states_count[states_count_sorted]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we can see the most of the accounts are from highly populated and wealthy area like CA, NY, TX, and FL. In contrast, we have very little data for too many states to examine individually. if we assign unique values to each state, this will allow outliers to have a big influence on the coefficients. Lets group them based on their characateristics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "states_west = np.array(['WA', 'OR','CA','NV','ID','MT', 'WY','UT','CO', 'AZ','NM','HI','AK'])\n",
    "states_south = np.array(['TX','OK','AR','LA','MS','AL','TN','KY','FL','GA','SC','NC','VA','WV','MD','DE','DC'])\n",
    "states_midwest = np.array(['ND','SD','NE','KS','MN','IA','MO','WI','IL','IN','MI','OH'])\n",
    "states_east = np.array(['PA','NY','NJ','CT','MA','VT','NH','ME','RI'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0', 'AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'IL', 'IN',\n",
       "       'KS', 'KY', 'LA', 'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH',\n",
       "       'NJ', 'NM', 'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA',\n",
       "       'VT', 'WA', 'WI', 'WV', 'WY'], dtype='<U69')"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lets take care of missing values\n",
    "loan_data_string[:,-1] = np.where(loan_data_string[:,-1] == '', 0 , loan_data_string[:,-1])\n",
    "np.unique(loan_data_string[:,-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_string[:,-1] = np.where(np.isin(loan_data_string[:,-1], states_west), 1, loan_data_string[:,-1])\n",
    "loan_data_string[:,-1] = np.where(np.isin(loan_data_string[:,-1], states_south), 2, loan_data_string[:,-1])\n",
    "loan_data_string[:,-1] = np.where(np.isin(loan_data_string[:,-1], states_midwest), 3, loan_data_string[:,-1])\n",
    "loan_data_string[:,-1] = np.where(np.isin(loan_data_string[:,-1], states_east), 4, loan_data_string[:,-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0', '1', '2', '3', '4'], dtype='<U69')"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.unique(loan_data_string[:,-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['5', '1', '36', '13', '1', '1'],\n",
       "       ['0', '1', '36', '5', '1', '4'],\n",
       "       ['9', '1', '36', '10', '1', '4'],\n",
       "       ...,\n",
       "       ['6', '1', '36', '5', '1', '1'],\n",
       "       ['4', '1', '36', '17', '1', '3'],\n",
       "       ['12', '1', '36', '4', '0', '3']], dtype='<U69')"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, eventhough the data are numeric, they are represented as string. Lets convert them and save at a checkpoint."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1r/3db931yx4ys8zd46l5tbk6x80000gn/T/ipykernel_9005/4083221643.py:1: DeprecationWarning: `np.int` is a deprecated alias for the builtin `int`. To silence this warning, use `int` by itself. Doing this will not modify any behavior and is safe. When replacing `np.int`, you may wish to use e.g. `np.int64` or `np.int32` to specify the precision. If you wish to review your current use, check the release note link for additional information.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  loan_data_string = loan_data_string.astype(np.int)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([[ 5,  1, 36, 13,  1,  1],\n",
       "       [ 0,  1, 36,  5,  1,  4],\n",
       "       [ 9,  1, 36, 10,  1,  4],\n",
       "       ...,\n",
       "       [ 6,  1, 36,  5,  1,  1],\n",
       "       [ 4,  1, 36, 17,  1,  3],\n",
       "       [12,  1, 36,  4,  0,  3]])"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string = loan_data_string.astype(np.int)\n",
    "loan_data_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "checkpoint_str = checkpoint(\"checkpoint_string\", header_string, loan_data_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issued_date', 'loan_status', 'term_months', 'sub_grade', 'verification_status',\n",
       "       'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checkpoint_str['header']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compare\n",
    "np.array_equal(checkpoint_str['data'], loan_data_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulating Numerical Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    35000.  ,       13.33,     1184.86,     9452.96],\n",
       "       [57693261.  ,    30000.  ,    30000.  , 68616520.  ,      938.57,     4679.7 ],\n",
       "       [59432726.  ,    15000.  ,    15000.  , 68616520.  ,      494.86,     1969.83],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,    10000.  , 68616520.  , 68616520.  ,     2185.64],\n",
       "       [46154151.  , 68616520.  ,    10000.  ,       16.55,      354.3 ,     3199.4 ],\n",
       "       [66055249.  ,    10000.  ,    10000.  , 68616520.  ,      309.97,      301.9 ]])"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.isnan(loan_data_numeric).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We already substituted the missing values with worst possible values. we need to track them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt', 'funded_amnt', 'int_rate', 'installment', 'total_pymnt'], dtype='<U19')"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ID\n",
    "Here we need to compare ID values with temporary fill.\n",
    "\n",
    "temp_fill = max  + 1 \n",
    "\n",
    "temp_mean = data mean\n",
    "\n",
    "\n",
    "#### Substituting filler values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "68616520.0"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_fill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False, False, False, ..., False, False, False])"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.isin(loan_data_numeric[:,0], temp_fill)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.isin(loan_data_numeric[:,0], temp_fill).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[  373332.  ,         nan,     1000.  ,         nan,     1000.  ,         nan,        6.  ,\n",
       "              31.42,         nan,         nan,         nan,         nan,         nan,        0.  ],\n",
       "       [54015809.19,         nan,    15273.46,         nan,    15311.04,         nan,       16.62,\n",
       "             440.92,         nan,         nan,         nan,         nan,         nan,     3143.85],\n",
       "       [68616519.  ,         nan,    35000.  ,         nan,    35000.  ,         nan,       28.99,\n",
       "            1372.97,         nan,         nan,         nan,         nan,         nan,    41913.62]])"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extracting only numeric data [min, mean, max]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[  373332.  ,     1000.  ,     1000.  ,        6.  ,       31.42,        0.  ],\n",
       "       [54015809.19,    15273.46,    15311.04,       16.62,      440.92,     3143.85],\n",
       "       [68616519.  ,    35000.  ,    35000.  ,       28.99,     1372.97,    41913.62]])"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_stats[:, columns_numeric]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt', 'funded_amnt', 'int_rate', 'installment', 'total_pymnt'], dtype='<U19')"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Funded Amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0,  2,  4,  6,  7, 13])"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we are substituting the temp_fill values with minimum values of the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([35000., 30000., 15000., ..., 10000., 10000., 10000.])"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric[:,2] = np.where(loan_data_numeric[:,2] == temp_fill, temp_stats[0,columns_numeric[2]], loan_data_numeric[:,2])\n",
    "loan_data_numeric[:,2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Loaned amount, interest rate, Total Payment, Installment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt', 'funded_amnt', 'int_rate', 'installment', 'total_pymnt'], dtype='<U19')"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in [1,3,4,5]:\n",
    "    loan_data_numeric[:,i] = np.where(loan_data_numeric[:,i] == temp_fill, temp_stats[0, columns_numeric[i]], loan_data_numeric[:,i])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    35000.  ,       13.33,     1184.86,     9452.96],\n",
       "       [57693261.  ,    30000.  ,    30000.  ,        6.  ,      938.57,     4679.7 ],\n",
       "       [59432726.  ,    15000.  ,    15000.  ,        6.  ,      494.86,     1969.83],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,    10000.  ,        6.  ,       31.42,     2185.64],\n",
       "       [46154151.  ,     1000.  ,    10000.  ,       16.55,      354.3 ,     3199.4 ],\n",
       "       [66055249.  ,    10000.  ,    10000.  ,        6.  ,      309.97,      301.9 ]])"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Currency Change\n",
    "\n",
    "The exchange rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1r/3db931yx4ys8zd46l5tbk6x80000gn/T/ipykernel_9005/393597384.py:1: DeprecationWarning: `np.str` is a deprecated alias for the builtin `str`. To silence this warning, use `str` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.str_` here.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  EUR_USD = np.genfromtxt(\"datasets/EUR-USD.csv\", autostrip=True, dtype=np.str)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array(['Open,High,Low,Close,Volume',\n",
       "       '1.2098628282546997,1.2098628282546997,1.11055588722229,1.1287955045700073,0',\n",
       "       '1.1287955045700073,1.1484194993972778,1.117680549621582,1.1205360889434814,0',\n",
       "       '1.119795799255371,1.1240400075912476,1.0460032224655151,1.0830246210098267,0',\n",
       "       '1.0741022825241089,1.1247594356536865,1.0521597862243652,1.1114321947097778,0',\n",
       "       '1.1215037107467651,1.145304799079895,1.0821995735168457,1.0960345268249512,0',\n",
       "       '1.095902442932129,1.1428401470184326,1.0888904333114624,1.122296690940857,0',\n",
       "       '1.1134989261627197,1.1219995021820068,1.081270456314087,1.0939244031906128,0',\n",
       "       '1.0969001054763794,1.1705996990203857,1.0850305557250977,1.1340054273605347,0',\n",
       "       '1.1225990056991577,1.1460003852844238,1.1089695692062378,1.1255937814712524,0',\n",
       "       '1.1171561479568481,1.1494200229644775,1.0910003185272217,1.100897192955017,0',\n",
       "       '1.1024993658065796,1.1060001850128174,1.056400179862976,1.0583018064498901,0',\n",
       "       '1.0572947263717651,1.107000470161438,1.0541995763778687,1.093398094177246,0'], dtype='<U77')"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "EUR_USD = np.genfromtxt(\"datasets/EUR-USD.csv\", autostrip=True, dtype=np.str)\n",
    "EUR_USD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now know, we only need closing price, and where it is. we will extract required data using following techniue. The exchange rates are in order. Hence, it will be easier to convert."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1.13, 1.12, 1.08, 1.11, 1.1 , 1.12, 1.09, 1.13, 1.13, 1.1 , 1.06, 1.09])"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "EUR_USD = np.genfromtxt(\"datasets/EUR-USD.csv\",delimiter=',', autostrip=True, skip_header=1, usecols=3)\n",
    "EUR_USD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['issued_date', 'loan_status', 'term_months', 'sub_grade', 'verification_status',\n",
       "       'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lets look at string column which have date\n",
    "header_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "issued date is at 0 index. lets create an exchange rate for each month of 2015."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 5,  0,  9, ...,  6,  4, 12])"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_string[:,0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1.1 , 0.  , 1.13, ..., 1.12, 1.11, 1.09])"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exchange_rate = loan_data_string[:,0]\n",
    "\n",
    "for i in range(1,13):\n",
    "    exchange_rate = np.where(exchange_rate == i , EUR_USD[i-1], exchange_rate)\n",
    "\n",
    "exchange_rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we still have a data with no issued date i.e, 0. we will impute the values with annual exchange rate i.e, average of exchange rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1.1 , 1.11, 1.13, ..., 1.12, 1.11, 1.09])"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exchange_rate = np.where(exchange_rate == 0 , np.mean(EUR_USD), exchange_rate)\n",
    "exchange_rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to concatenate the exchange rate to numeric data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10000,)\n",
      "(10000, 6)\n"
     ]
    }
   ],
   "source": [
    "# lets check the shape\n",
    "print(exchange_rate.shape)\n",
    "print(loan_data_numeric.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reshape, and concat\n",
    "\n",
    "exchange_rate = np.reshape(exchange_rate, (10000,1))\n",
    "loan_data_numeric = np.hstack((loan_data_numeric, exchange_rate))\n",
    "header_numeric = np.concatenate((header_numeric, np.array(['exchange_rate'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    35000.  , ...,     1184.86,     9452.96,        1.1 ],\n",
       "       [57693261.  ,    30000.  ,    30000.  , ...,      938.57,     4679.7 ,        1.11],\n",
       "       [59432726.  ,    15000.  ,    15000.  , ...,      494.86,     1969.83,        1.13],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,    10000.  , ...,       31.42,     2185.64,        1.12],\n",
       "       [46154151.  ,     1000.  ,    10000.  , ...,      354.3 ,     3199.4 ,        1.11],\n",
       "       [66055249.  ,    10000.  ,    10000.  , ...,      309.97,      301.9 ,        1.09]])"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt', 'funded_amnt', 'int_rate', 'installment', 'total_pymnt', 'exchange_rate'],\n",
       "      dtype='<U19')"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### From USD to EUR\n",
    "\n",
    "Now, we are converting USD to EUR. The columns that are required to convert: 'loan_amount', 'funded_amount', 'installment', 'total_payment'. Lets select them and convert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[[35000.  , 35000.  ,  1184.86,  9452.96]],\n",
       "\n",
       "       [[30000.  , 30000.  ,   938.57,  4679.7 ]],\n",
       "\n",
       "       [[15000.  , 15000.  ,   494.86,  1969.83]],\n",
       "\n",
       "       ...,\n",
       "\n",
       "       [[10000.  , 10000.  ,    31.42,  2185.64]],\n",
       "\n",
       "       [[ 1000.  , 10000.  ,   354.3 ,  3199.4 ]],\n",
       "\n",
       "       [[10000.  , 10000.  ,   309.97,   301.9 ]]])"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dollar_columns = np.array([1,2,4,5])\n",
    "loan_data_numeric[:, [dollar_columns]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1.1 , 1.11, 1.13, ..., 1.12, 1.11, 1.09])"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric[:,6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we have EUR-USD exchange rate, and we need to convert USD to EUR. We will devide the USD amount buy exchange rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in dollar_columns:\n",
    "    loan_data_numeric = np.hstack((loan_data_numeric, np.reshape(loan_data_numeric[:,i] / loan_data_numeric[:,6], (10000,1)) ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 11)"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Expand the header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['loan_amnt_EUR', 'funded_amnt_EUR', 'installment_EUR', 'total_pymnt_EUR'], dtype='<U15')"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "additional_header = np.array([col + '_EUR' for col in header_numeric[dollar_columns]])\n",
    "additional_header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt', 'funded_amnt', 'int_rate', 'installment', 'total_pymnt', 'exchange_rate',\n",
       "       'loan_amnt_EUR', 'funded_amnt_EUR', 'installment_EUR', 'total_pymnt_EUR'], dtype='<U19')"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let add the header to orignal header\n",
    "header_numeric = np.concatenate((header_numeric, additional_header))\n",
    "header_numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt _USD', 'funded_amnt _USD', 'int_rate', 'installment _USD',\n",
       "       'total_pymnt _USD', 'exchange_rate', 'loan_amnt_EUR', 'funded_amnt_EUR', 'installment_EUR',\n",
       "       'total_pymnt_EUR'], dtype='<U19')"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_numeric[dollar_columns] = np.array([col +' _USD' for col in header_numeric[dollar_columns]])\n",
    "header_numeric\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "lets re-arrange the header."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "index_order = [0,1,7,2,8,3,4,9,5,10,6]\n",
    "header_numeric = header_numeric[index_order]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt _USD', 'loan_amnt_EUR', 'funded_amnt _USD', 'funded_amnt_EUR', 'int_rate',\n",
       "       'installment _USD', 'installment_EUR', 'total_pymnt _USD', 'total_pymnt_EUR',\n",
       "       'exchange_rate'], dtype='<U19')"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    31933.3 , ...,     9452.96,     8624.69,        1.1 ],\n",
       "       [57693261.  ,    30000.  ,    27132.46, ...,     4679.7 ,     4232.39,        1.11],\n",
       "       [59432726.  ,    15000.  ,    13326.3 , ...,     1969.83,     1750.04,        1.13],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,     8910.3 , ...,     2185.64,     1947.47,        1.12],\n",
       "       [46154151.  ,     1000.  ,      899.74, ...,     3199.4 ,     2878.63,        1.11],\n",
       "       [66055249.  ,    10000.  ,     9145.8 , ...,      301.9 ,      276.11,        1.09]])"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric[:, index_order]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    31933.3 , ...,     9452.96,     8624.69,        1.1 ],\n",
       "       [57693261.  ,    30000.  ,    27132.46, ...,     4679.7 ,     4232.39,        1.11],\n",
       "       [59432726.  ,    15000.  ,    13326.3 , ...,     1969.83,     1750.04,        1.13],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,     8910.3 , ...,     2185.64,     1947.47,        1.12],\n",
       "       [46154151.  ,     1000.  ,      899.74, ...,     3199.4 ,     2878.63,        1.11],\n",
       "       [66055249.  ,    10000.  ,     9145.8 , ...,      301.9 ,      276.11,        1.09]])"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric = loan_data_numeric[:,index_order]\n",
    "loan_data_numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Interest Rate\n",
    "Lets find out the column in belongs to."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt _USD', 'loan_amnt_EUR', 'funded_amnt _USD', 'funded_amnt_EUR', 'int_rate',\n",
       "       'installment _USD', 'installment_EUR', 'total_pymnt _USD', 'total_pymnt_EUR',\n",
       "       'exchange_rate'], dtype='<U19')"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([13.33,  6.  ,  6.  , ...,  6.  , 16.55,  6.  ])"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric[:,5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The interest rates are not stored in 0-1 (fractual precentage). Lets transform them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.13, 0.06, 0.06, ..., 0.06, 0.17, 0.06])"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_numeric[:,5] = loan_data_numeric[:,5] /100 \n",
    "loan_data_numeric[:,5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checkpoint 2: Numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "checkpoint_numeric = checkpoint('checkpoint_numeric', header_numeric, loan_data_numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt _USD', 'loan_amnt_EUR', 'funded_amnt _USD', 'funded_amnt_EUR', 'int_rate',\n",
       "       'installment _USD', 'installment_EUR', 'total_pymnt _USD', 'total_pymnt_EUR',\n",
       "       'exchange_rate'], dtype='<U19')"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checkpoint_numeric['header']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    31933.3 , ...,     9452.96,     8624.69,        1.1 ],\n",
       "       [57693261.  ,    30000.  ,    27132.46, ...,     4679.7 ,     4232.39,        1.11],\n",
       "       [59432726.  ,    15000.  ,    13326.3 , ...,     1969.83,     1750.04,        1.13],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,     8910.3 , ...,     2185.64,     1947.47,        1.12],\n",
       "       [46154151.  ,     1000.  ,      899.74, ...,     3199.4 ,     2878.63,        1.11],\n",
       "       [66055249.  ,    10000.  ,     9145.8 , ...,      301.9 ,      276.11,        1.09]])"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checkpoint_numeric['data']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.array_equal(checkpoint_numeric['data'], loan_data_numeric)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Completing the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 6)"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checkpoint_str['data'].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 11)"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checkpoint_numeric['data'].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data = np.hstack((checkpoint_numeric['data'], checkpoint_str['data']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[48010226.  ,    35000.  ,    31933.3 , ...,       13.  ,        1.  ,        1.  ],\n",
       "       [57693261.  ,    30000.  ,    27132.46, ...,        5.  ,        1.  ,        4.  ],\n",
       "       [59432726.  ,    15000.  ,    13326.3 , ...,       10.  ,        1.  ,        4.  ],\n",
       "       ...,\n",
       "       [50415990.  ,    10000.  ,     8910.3 , ...,        5.  ,        1.  ,        1.  ],\n",
       "       [46154151.  ,     1000.  ,      899.74, ...,       17.  ,        1.  ,        3.  ],\n",
       "       [66055249.  ,    10000.  ,     9145.8 , ...,        4.  ,        0.  ,        3.  ]])"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.isnan(loan_data).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['id', 'loan_amnt _USD', 'loan_amnt_EUR', 'funded_amnt _USD', 'funded_amnt_EUR', 'int_rate',\n",
       "       'installment _USD', 'installment_EUR', 'total_pymnt _USD', 'total_pymnt_EUR',\n",
       "       'exchange_rate', 'issued_date', 'loan_status', 'term_months', 'sub_grade',\n",
       "       'verification_status', 'addr_state'], dtype='<U19')"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header_full = np.concatenate((checkpoint_numeric['header'], checkpoint_str['header']))\n",
    "header_full"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Sorting the data\n",
    "\n",
    "We wil use argsort on ID cloumn to sort the entire dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[  373332.  ,     9950.  ,     9038.08, ...,       21.  ,        0.  ,        1.  ],\n",
       "       [  575239.  ,    12000.  ,    10900.2 , ...,       25.  ,        1.  ,        2.  ],\n",
       "       [  707689.  ,    10000.  ,     8924.3 , ...,       13.  ,        1.  ,        0.  ],\n",
       "       ...,\n",
       "       [68614880.  ,     5600.  ,     5121.65, ...,        8.  ,        1.  ,        1.  ],\n",
       "       [68615915.  ,     4000.  ,     3658.32, ...,       10.  ,        1.  ,        2.  ],\n",
       "       [68616519.  ,    21600.  ,    19754.93, ...,        3.  ,        0.  ,        2.  ]])"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data[np.argsort(loan_data[:,0])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data = loan_data[np.argsort(loan_data[:,0])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([   0,    1,    2, ..., 9997, 9998, 9999])"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lets verify\n",
    "np.argsort(loan_data[:,0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Stacking and storing the complete data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['id', 'loan_amnt _USD', 'loan_amnt_EUR', ..., 'sub_grade', 'verification_status',\n",
       "        'addr_state'],\n",
       "       ['373332.0', '9950.0', '9038.082814338286', ..., '21.0', '0.0', '1.0'],\n",
       "       ['575239.0', '12000.0', '10900.20037910145', ..., '25.0', '1.0', '2.0'],\n",
       "       ...,\n",
       "       ['68614880.0', '5600.0', '5121.647851612413', ..., '8.0', '1.0', '1.0'],\n",
       "       ['68615915.0', '4000.0', '3658.319894008867', ..., '10.0', '1.0', '2.0'],\n",
       "       ['68616519.0', '21600.0', '19754.927427647883', ..., '3.0', '0.0', '2.0']], dtype='<U32')"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data = np.vstack((header_full, loan_data))\n",
    "loan_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save data\n",
    "np.savetxt(\"datasets/loan_data_preprocesssed.txt\", loan_data, fmt=\"%s\", delimiter=\",\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Its is now ready for model building\n"
     ]
    }
   ],
   "source": [
    "print(\"Its is now ready for model building\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "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.9.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "3d597f4c481aa0f25dceb95d2a0067e73c0966dcbd003d741d821a7208527ecf"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}