part0_wrangling_da.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deriving and Communicating Insights with Data Visualization\n",
    "## Part 0: Wrangling of Prosper Loan Data\n",
    "### Jong Min Lee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import all packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import copy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Wrangling\n",
    "`prosperLoanData.csv` which contains the Prosper loan data was made available by [Udacity](https://www.udacity.com/) for download. The file was manually downloaded to the local `data` directory and imported to this project as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load .csv file containing the prosper loan data\n",
    "df = pd.read_csv('data/prosperLoanData.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Structure of the Dataset\n",
    "The loan dataset contains 113,937 rows of data for 81 variables, ranging from the `ListingKey` which uniquely identifies each listing posted in [Prosper](www.prosper.com) for the loans requested by borrowers to the `Investors` which indicates the number of investors who funded the loan associated with the listing in Prosper. Review of the key information on and the first five rows of the dataset revealed several aspects of the dataset such as columns with inappropriate data types and/or missing values which render data cleaning necessary. Systematic approach to cleaning the dataset, which includes defining the necessary cleaning operations, coding and performing these operations, and verifying the results are documented in the next two sections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 113937 entries, 0 to 113936\n",
      "Data columns (total 81 columns):\n",
      "ListingKey                             113937 non-null object\n",
      "ListingNumber                          113937 non-null int64\n",
      "ListingCreationDate                    113937 non-null object\n",
      "CreditGrade                            28953 non-null object\n",
      "Term                                   113937 non-null int64\n",
      "LoanStatus                             113937 non-null object\n",
      "ClosedDate                             55089 non-null object\n",
      "BorrowerAPR                            113912 non-null float64\n",
      "BorrowerRate                           113937 non-null float64\n",
      "LenderYield                            113937 non-null float64\n",
      "EstimatedEffectiveYield                84853 non-null float64\n",
      "EstimatedLoss                          84853 non-null float64\n",
      "EstimatedReturn                        84853 non-null float64\n",
      "ProsperRating (numeric)                84853 non-null float64\n",
      "ProsperRating (Alpha)                  84853 non-null object\n",
      "ProsperScore                           84853 non-null float64\n",
      "ListingCategory (numeric)              113937 non-null int64\n",
      "BorrowerState                          108422 non-null object\n",
      "Occupation                             110349 non-null object\n",
      "EmploymentStatus                       111682 non-null object\n",
      "EmploymentStatusDuration               106312 non-null float64\n",
      "IsBorrowerHomeowner                    113937 non-null bool\n",
      "CurrentlyInGroup                       113937 non-null bool\n",
      "GroupKey                               13341 non-null object\n",
      "DateCreditPulled                       113937 non-null object\n",
      "CreditScoreRangeLower                  113346 non-null float64\n",
      "CreditScoreRangeUpper                  113346 non-null float64\n",
      "FirstRecordedCreditLine                113240 non-null object\n",
      "CurrentCreditLines                     106333 non-null float64\n",
      "OpenCreditLines                        106333 non-null float64\n",
      "TotalCreditLinespast7years             113240 non-null float64\n",
      "OpenRevolvingAccounts                  113937 non-null int64\n",
      "OpenRevolvingMonthlyPayment            113937 non-null float64\n",
      "InquiriesLast6Months                   113240 non-null float64\n",
      "TotalInquiries                         112778 non-null float64\n",
      "CurrentDelinquencies                   113240 non-null float64\n",
      "AmountDelinquent                       106315 non-null float64\n",
      "DelinquenciesLast7Years                112947 non-null float64\n",
      "PublicRecordsLast10Years               113240 non-null float64\n",
      "PublicRecordsLast12Months              106333 non-null float64\n",
      "RevolvingCreditBalance                 106333 non-null float64\n",
      "BankcardUtilization                    106333 non-null float64\n",
      "AvailableBankcardCredit                106393 non-null float64\n",
      "TotalTrades                            106393 non-null float64\n",
      "TradesNeverDelinquent (percentage)     106393 non-null float64\n",
      "TradesOpenedLast6Months                106393 non-null float64\n",
      "DebtToIncomeRatio                      105383 non-null float64\n",
      "IncomeRange                            113937 non-null object\n",
      "IncomeVerifiable                       113937 non-null bool\n",
      "StatedMonthlyIncome                    113937 non-null float64\n",
      "LoanKey                                113937 non-null object\n",
      "TotalProsperLoans                      22085 non-null float64\n",
      "TotalProsperPaymentsBilled             22085 non-null float64\n",
      "OnTimeProsperPayments                  22085 non-null float64\n",
      "ProsperPaymentsLessThanOneMonthLate    22085 non-null float64\n",
      "ProsperPaymentsOneMonthPlusLate        22085 non-null float64\n",
      "ProsperPrincipalBorrowed               22085 non-null float64\n",
      "ProsperPrincipalOutstanding            22085 non-null float64\n",
      "ScorexChangeAtTimeOfListing            18928 non-null float64\n",
      "LoanCurrentDaysDelinquent              113937 non-null int64\n",
      "LoanFirstDefaultedCycleNumber          16952 non-null float64\n",
      "LoanMonthsSinceOrigination             113937 non-null int64\n",
      "LoanNumber                             113937 non-null int64\n",
      "LoanOriginalAmount                     113937 non-null int64\n",
      "LoanOriginationDate                    113937 non-null object\n",
      "LoanOriginationQuarter                 113937 non-null object\n",
      "MemberKey                              113937 non-null object\n",
      "MonthlyLoanPayment                     113937 non-null float64\n",
      "LP_CustomerPayments                    113937 non-null float64\n",
      "LP_CustomerPrincipalPayments           113937 non-null float64\n",
      "LP_InterestandFees                     113937 non-null float64\n",
      "LP_ServiceFees                         113937 non-null float64\n",
      "LP_CollectionFees                      113937 non-null float64\n",
      "LP_GrossPrincipalLoss                  113937 non-null float64\n",
      "LP_NetPrincipalLoss                    113937 non-null float64\n",
      "LP_NonPrincipalRecoverypayments        113937 non-null float64\n",
      "PercentFunded                          113937 non-null float64\n",
      "Recommendations                        113937 non-null int64\n",
      "InvestmentFromFriendsCount             113937 non-null int64\n",
      "InvestmentFromFriendsAmount            113937 non-null float64\n",
      "Investors                              113937 non-null int64\n",
      "dtypes: bool(3), float64(50), int64(11), object(17)\n",
      "memory usage: 68.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# summary of the dataframe object\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ListingKey</th>\n",
       "      <th>ListingNumber</th>\n",
       "      <th>ListingCreationDate</th>\n",
       "      <th>CreditGrade</th>\n",
       "      <th>Term</th>\n",
       "      <th>LoanStatus</th>\n",
       "      <th>ClosedDate</th>\n",
       "      <th>BorrowerAPR</th>\n",
       "      <th>BorrowerRate</th>\n",
       "      <th>LenderYield</th>\n",
       "      <th>...</th>\n",
       "      <th>LP_ServiceFees</th>\n",
       "      <th>LP_CollectionFees</th>\n",
       "      <th>LP_GrossPrincipalLoss</th>\n",
       "      <th>LP_NetPrincipalLoss</th>\n",
       "      <th>LP_NonPrincipalRecoverypayments</th>\n",
       "      <th>PercentFunded</th>\n",
       "      <th>Recommendations</th>\n",
       "      <th>InvestmentFromFriendsCount</th>\n",
       "      <th>InvestmentFromFriendsAmount</th>\n",
       "      <th>Investors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1021339766868145413AB3B</td>\n",
       "      <td>193129</td>\n",
       "      <td>2007-08-26 19:09:29.263000000</td>\n",
       "      <td>C</td>\n",
       "      <td>36</td>\n",
       "      <td>Completed</td>\n",
       "      <td>2009-08-14 00:00:00</td>\n",
       "      <td>0.16516</td>\n",
       "      <td>0.1580</td>\n",
       "      <td>0.1380</td>\n",
       "      <td>...</td>\n",
       "      <td>-133.18</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</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10273602499503308B223C1</td>\n",
       "      <td>1209647</td>\n",
       "      <td>2014-02-27 08:28:07.900000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36</td>\n",
       "      <td>Current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.12016</td>\n",
       "      <td>0.0920</td>\n",
       "      <td>0.0820</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</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</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0EE9337825851032864889A</td>\n",
       "      <td>81716</td>\n",
       "      <td>2007-01-05 15:00:47.090000000</td>\n",
       "      <td>HR</td>\n",
       "      <td>36</td>\n",
       "      <td>Completed</td>\n",
       "      <td>2009-12-17 00:00:00</td>\n",
       "      <td>0.28269</td>\n",
       "      <td>0.2750</td>\n",
       "      <td>0.2400</td>\n",
       "      <td>...</td>\n",
       "      <td>-24.20</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</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0EF5356002482715299901A</td>\n",
       "      <td>658116</td>\n",
       "      <td>2012-10-22 11:02:35.010000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36</td>\n",
       "      <td>Current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.12528</td>\n",
       "      <td>0.0974</td>\n",
       "      <td>0.0874</td>\n",
       "      <td>...</td>\n",
       "      <td>-108.01</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</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0F023589499656230C5E3E2</td>\n",
       "      <td>909464</td>\n",
       "      <td>2013-09-14 18:38:39.097000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36</td>\n",
       "      <td>Current</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.24614</td>\n",
       "      <td>0.2085</td>\n",
       "      <td>0.1985</td>\n",
       "      <td>...</td>\n",
       "      <td>-60.27</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</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 81 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ListingKey  ListingNumber            ListingCreationDate  \\\n",
       "0  1021339766868145413AB3B         193129  2007-08-26 19:09:29.263000000   \n",
       "1  10273602499503308B223C1        1209647  2014-02-27 08:28:07.900000000   \n",
       "2  0EE9337825851032864889A          81716  2007-01-05 15:00:47.090000000   \n",
       "3  0EF5356002482715299901A         658116  2012-10-22 11:02:35.010000000   \n",
       "4  0F023589499656230C5E3E2         909464  2013-09-14 18:38:39.097000000   \n",
       "\n",
       "  CreditGrade  Term LoanStatus           ClosedDate  BorrowerAPR  \\\n",
       "0           C    36  Completed  2009-08-14 00:00:00      0.16516   \n",
       "1         NaN    36    Current                  NaN      0.12016   \n",
       "2          HR    36  Completed  2009-12-17 00:00:00      0.28269   \n",
       "3         NaN    36    Current                  NaN      0.12528   \n",
       "4         NaN    36    Current                  NaN      0.24614   \n",
       "\n",
       "   BorrowerRate  LenderYield  ...  LP_ServiceFees  LP_CollectionFees  \\\n",
       "0        0.1580       0.1380  ...         -133.18                0.0   \n",
       "1        0.0920       0.0820  ...            0.00                0.0   \n",
       "2        0.2750       0.2400  ...          -24.20                0.0   \n",
       "3        0.0974       0.0874  ...         -108.01                0.0   \n",
       "4        0.2085       0.1985  ...          -60.27                0.0   \n",
       "\n",
       "   LP_GrossPrincipalLoss  LP_NetPrincipalLoss LP_NonPrincipalRecoverypayments  \\\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",
       "   PercentFunded  Recommendations InvestmentFromFriendsCount  \\\n",
       "0            1.0                0                          0   \n",
       "1            1.0                0                          0   \n",
       "2            1.0                0                          0   \n",
       "3            1.0                0                          0   \n",
       "4            1.0                0                          0   \n",
       "\n",
       "  InvestmentFromFriendsAmount Investors  \n",
       "0                         0.0       258  \n",
       "1                         0.0         1  \n",
       "2                         0.0        41  \n",
       "3                         0.0       158  \n",
       "4                         0.0        20  \n",
       "\n",
       "[5 rows x 81 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first five rows of the data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Key Features related to a Borrower's APR\n",
    "This project explores the loan data to identify the correlation of different variables associated with the listings for each loan with a borrower's APR. Insights from this data analysis would be applicable for not only answering such potential questions as whether a loan with a longer term for paying back is associated with a lower APR or if specific categories for which Prosper provides borrowers with its online platform for requesting a loan are associated with a high or low APR but comparing the correlations with the APR across these variables.\n",
    "\n",
    "To streamline this data analysis, 16 features listed below were selected from the dataset. While few of these columns such as `ListingKey`, `BorrowerState` or `ListingCategory` serve to facilitate the operations for cleaning the dataset or define groups for which further trends in correlations with the APR can be identified, other columns are expected to directly correlate with the APR in differing degrees. For instance, with other conditions being equal, the loan of a borrower who is employed full-time but showed frequent delinquencies may require a higher APR than that for an unemployed borrower with no past delinquency.\n",
    "* `ListingKey`\n",
    "* `Term`\n",
    "* `BorrowerAPR`\n",
    "* `ListingCategory (name)`\n",
    "* `BorrowerState`\n",
    "* `EmploymentStatus`\n",
    "* `EmploymentstatusDuration`\n",
    "* `CreditScoreRangeLower`\n",
    "* `CreditScoreRangeUpper`\n",
    "* `TotalCreditLinespast7years`\n",
    "* `TotalInquiries`\n",
    "* `DelinquenciesLast7Years`\n",
    "* `BankcardUtilization`\n",
    "* `DebtToIncomeRatio`\n",
    "* `StatedMonthlyIncome`\n",
    "* `LoanOriginalAmount`\n",
    "\n",
    "A new dataframe object `df_clean` containing only these _main_ features was created from the original dataframe object `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create sub-dataset from Prosper loan data which contains only the 16 features\n",
    "df_clean = df.copy()\n",
    "df_clean = df_clean.iloc[:, np.r_[0, 4, 7, 16, 17, 19, 20, 25, 26, 30, 34, 37, 41, 46, 49, 63]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Data Cleaning\n",
    "#### 3.1 Assessments\n",
    "Ten observations listed below are those which render data cleaning necessary.\n",
    "* Data type of the `Term` column is integer, not ordinal categorical variable. \\[see _assessments 1_ and _3_\\]\n",
    "* Name of the column, `ListingCategory (numeric)` includes a space. \\[see _assessment 1_\\]\n",
    "* Data type of the `ListingCategory (numeric)` column is integer, not nominal categorical variable. \\[see _assessment 1_\\]\n",
    "* The `EmploymentStatus` column includes two categories which may indicate the same status of employment: `Not available` and `NaN`. \\[see _assessment 4_\\]\n",
    "* Data types of the six columns, `EmploymentStatusDuration`, `CreditScoreRangeLower`, `CreditScoreRangeUpper`, `TotalCreditLinespast7years`, `TotalInquiries`, and `DelinquenciesLast7Years`, are float, not integer as defined in `data_dictionary.xlsx`. \\[see _assessments 1_ and _2_\\]\n",
    "* Values under the `StatedMonthlyIncome` column have more than two digits after the decimal although the column represents a monetary amount. \\[see _assessment 2_\\]\n",
    "* Data type of the `LoanOriginalAmount` column, which represents a monetary amount, is an integer, not float. \\[see _assessments 1_ and _2_\\]\n",
    "* There are 871 duplicate rows of data in the dataframe object `df_clean`. \\[see _assessment 5_\\]\n",
    "* 11 of the 16 features in the dataframe object `df_clean` include multiple `NaN` values. \\[see _assessments 1_ and _6_\\]\n",
    "* Range of each borrower's credit score is split into lower and upper boundaries although either boundary alone would not provide any statistical significance especially if the difference between the lower and upper boundaries is 19 for all borrowers' credit scores. \\[see _assessments 2_ and _7_\\]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 113937 entries, 0 to 113936\n",
      "Data columns (total 16 columns):\n",
      "ListingKey                    113937 non-null object\n",
      "Term                          113937 non-null int64\n",
      "BorrowerAPR                   113912 non-null float64\n",
      "ListingCategory (numeric)     113937 non-null int64\n",
      "BorrowerState                 108422 non-null object\n",
      "EmploymentStatus              111682 non-null object\n",
      "EmploymentStatusDuration      106312 non-null float64\n",
      "CreditScoreRangeLower         113346 non-null float64\n",
      "CreditScoreRangeUpper         113346 non-null float64\n",
      "TotalCreditLinespast7years    113240 non-null float64\n",
      "TotalInquiries                112778 non-null float64\n",
      "DelinquenciesLast7Years       112947 non-null float64\n",
      "BankcardUtilization           106333 non-null float64\n",
      "DebtToIncomeRatio             105383 non-null float64\n",
      "StatedMonthlyIncome           113937 non-null float64\n",
      "LoanOriginalAmount            113937 non-null int64\n",
      "dtypes: float64(10), int64(3), object(3)\n",
      "memory usage: 13.9+ MB\n"
     ]
    }
   ],
   "source": [
    "# assessment 1: summary of the dataframe object\n",
    "df_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ListingKey</th>\n",
       "      <th>Term</th>\n",
       "      <th>BorrowerAPR</th>\n",
       "      <th>ListingCategory (numeric)</th>\n",
       "      <th>BorrowerState</th>\n",
       "      <th>EmploymentStatus</th>\n",
       "      <th>EmploymentStatusDuration</th>\n",
       "      <th>CreditScoreRangeLower</th>\n",
       "      <th>CreditScoreRangeUpper</th>\n",
       "      <th>TotalCreditLinespast7years</th>\n",
       "      <th>TotalInquiries</th>\n",
       "      <th>DelinquenciesLast7Years</th>\n",
       "      <th>BankcardUtilization</th>\n",
       "      <th>DebtToIncomeRatio</th>\n",
       "      <th>StatedMonthlyIncome</th>\n",
       "      <th>LoanOriginalAmount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1021339766868145413AB3B</td>\n",
       "      <td>36</td>\n",
       "      <td>0.16516</td>\n",
       "      <td>0</td>\n",
       "      <td>CO</td>\n",
       "      <td>Self-employed</td>\n",
       "      <td>2.0</td>\n",
       "      <td>640.0</td>\n",
       "      <td>659.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.17</td>\n",
       "      <td>3083.333333</td>\n",
       "      <td>9425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10273602499503308B223C1</td>\n",
       "      <td>36</td>\n",
       "      <td>0.12016</td>\n",
       "      <td>2</td>\n",
       "      <td>CO</td>\n",
       "      <td>Employed</td>\n",
       "      <td>44.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.18</td>\n",
       "      <td>6125.000000</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0EE9337825851032864889A</td>\n",
       "      <td>36</td>\n",
       "      <td>0.28269</td>\n",
       "      <td>0</td>\n",
       "      <td>GA</td>\n",
       "      <td>Not available</td>\n",
       "      <td>NaN</td>\n",
       "      <td>480.0</td>\n",
       "      <td>499.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.06</td>\n",
       "      <td>2083.333333</td>\n",
       "      <td>3001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0EF5356002482715299901A</td>\n",
       "      <td>36</td>\n",
       "      <td>0.12528</td>\n",
       "      <td>16</td>\n",
       "      <td>GA</td>\n",
       "      <td>Employed</td>\n",
       "      <td>113.0</td>\n",
       "      <td>800.0</td>\n",
       "      <td>819.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2875.000000</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0F023589499656230C5E3E2</td>\n",
       "      <td>36</td>\n",
       "      <td>0.24614</td>\n",
       "      <td>2</td>\n",
       "      <td>MN</td>\n",
       "      <td>Employed</td>\n",
       "      <td>44.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.26</td>\n",
       "      <td>9583.333333</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ListingKey  Term  BorrowerAPR  ListingCategory (numeric)  \\\n",
       "0  1021339766868145413AB3B    36      0.16516                          0   \n",
       "1  10273602499503308B223C1    36      0.12016                          2   \n",
       "2  0EE9337825851032864889A    36      0.28269                          0   \n",
       "3  0EF5356002482715299901A    36      0.12528                         16   \n",
       "4  0F023589499656230C5E3E2    36      0.24614                          2   \n",
       "\n",
       "  BorrowerState EmploymentStatus  EmploymentStatusDuration  \\\n",
       "0            CO    Self-employed                       2.0   \n",
       "1            CO         Employed                      44.0   \n",
       "2            GA    Not available                       NaN   \n",
       "3            GA         Employed                     113.0   \n",
       "4            MN         Employed                      44.0   \n",
       "\n",
       "   CreditScoreRangeLower  CreditScoreRangeUpper  TotalCreditLinespast7years  \\\n",
       "0                  640.0                  659.0                        12.0   \n",
       "1                  680.0                  699.0                        29.0   \n",
       "2                  480.0                  499.0                         3.0   \n",
       "3                  800.0                  819.0                        29.0   \n",
       "4                  680.0                  699.0                        49.0   \n",
       "\n",
       "   TotalInquiries  DelinquenciesLast7Years  BankcardUtilization  \\\n",
       "0             3.0                      4.0                 0.00   \n",
       "1             5.0                      0.0                 0.21   \n",
       "2             1.0                      0.0                  NaN   \n",
       "3             1.0                     14.0                 0.04   \n",
       "4             9.0                      0.0                 0.81   \n",
       "\n",
       "   DebtToIncomeRatio  StatedMonthlyIncome  LoanOriginalAmount  \n",
       "0               0.17          3083.333333                9425  \n",
       "1               0.18          6125.000000               10000  \n",
       "2               0.06          2083.333333                3001  \n",
       "3               0.15          2875.000000               10000  \n",
       "4               0.26          9583.333333               15000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assessment 2: first five rows\n",
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([36, 60, 12], dtype=int64)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assessment 3: unique values for Term\n",
    "df_clean.Term.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Self-employed', 'Employed', 'Not available', 'Full-time', 'Other',\n",
       "       nan, 'Not employed', 'Part-time', 'Retired'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assessment 4: unique values for EmploymentStatus\n",
    "df_clean.EmploymentStatus.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "871"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assessment 5: number of duplicate rows\n",
    "df_clean.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ListingKey                       0\n",
       "Term                             0\n",
       "BorrowerAPR                     25\n",
       "ListingCategory (numeric)        0\n",
       "BorrowerState                 5515\n",
       "EmploymentStatus              2255\n",
       "EmploymentStatusDuration      7625\n",
       "CreditScoreRangeLower          591\n",
       "CreditScoreRangeUpper          591\n",
       "TotalCreditLinespast7years     697\n",
       "TotalInquiries                1159\n",
       "DelinquenciesLast7Years        990\n",
       "BankcardUtilization           7604\n",
       "DebtToIncomeRatio             8554\n",
       "StatedMonthlyIncome              0\n",
       "LoanOriginalAmount               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assessment 6: number of null values in each column\n",
    "df_clean.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19.0    113346\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assessment 7: range of credit scores\n",
    "(df_clean['CreditScoreRangeUpper'] - df_clean['CreditScoreRangeLower']).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2 Define, Clean, and Test\n",
    "For each of the ten assessments documented above, a cleaning operation was defined, coded, performed, and verified.\n",
    "\n",
    "__Data type of the `Term` column is integer, not ordinal categorical variable.__\n",
    "\n",
    "Convert the data type of the `Term` column from integer to (ordinal) category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CategoricalDtype(categories=[12, 36, 60], ordered=True)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ordered list of unique values in Term\n",
    "terms = list(df_clean.Term.unique())\n",
    "terms.sort()\n",
    "\n",
    "# convert data type of Term to (ordered) category\n",
    "loan_terms = pd.api.types.CategoricalDtype(ordered = True, categories = terms)\n",
    "df_clean.Term = df_clean.Term.astype(loan_terms)\n",
    "\n",
    "# verify that data type of Term is (ordered) category\n",
    "df_clean.Term.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Name of the column, `ListingCategory (numeric)` includes a space.__\n",
    "\n",
    "Change the name of the column from `ListingCategory (numeric)` to `ListingCategory`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ListingKey', 'Term', 'BorrowerAPR', 'ListingCategory', 'BorrowerState',\n",
       "       'EmploymentStatus', 'EmploymentStatusDuration', 'CreditScoreRangeLower',\n",
       "       'CreditScoreRangeUpper', 'TotalCreditLinespast7years', 'TotalInquiries',\n",
       "       'DelinquenciesLast7Years', 'BankcardUtilization', 'DebtToIncomeRatio',\n",
       "       'StatedMonthlyIncome', 'LoanOriginalAmount'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change column name\n",
    "df_clean.rename(columns = {'ListingCategory (numeric)':'ListingCategory'}, inplace = True)\n",
    "\n",
    "# verify that 'ListingCategory', instead of 'ListingCategory (numeric)', is among the available columns\n",
    "df_clean.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Data type of the `ListingCategory (numeric)` column is integer, not nominal categorical variable.__\n",
    "\n",
    "Convert the data type of the `ListingCategory` from integer to (nominal) category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CategoricalDtype(categories=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,\n",
       "                  18, 19, 20],\n",
       "                 ordered=False)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ordered list of unique values in ListingCategory\n",
    "categories = list(df_clean.ListingCategory.unique())\n",
    "categories.sort()\n",
    "\n",
    "# convert data type of ListingCategory to (nominal) category\n",
    "listing_categories = pd.api.types.CategoricalDtype(categories = categories)\n",
    "df_clean.ListingCategory = df_clean.ListingCategory.astype(listing_categories)\n",
    "\n",
    "# verify that data type of ListingCategory is (nominal) category\n",
    "df_clean.ListingCategory.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__The `EmploymentStatus` column includes two categories which may indicate the same status of employment: `Not available` and `NaN`.__\n",
    "\n",
    "Change all instances of `Not available` in the `EmploymentStatus` column to `NaN`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5347, 0, 5347)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total number of instances of 'Not available' for EmploymentStatus\n",
    "count_status_before = df_clean.query('EmploymentStatus == \"Not available\"').shape[0]\n",
    "count_nan_before = df_clean.EmploymentStatus.isna().sum()\n",
    "\n",
    "# replace 'Not available' with NaN\n",
    "df_clean.EmploymentStatus.replace(to_replace = 'Not available', value = np.nan, inplace = True)\n",
    "\n",
    "# verify that all instances of 'Not available' were replaced with NaN\n",
    "count_status_after = df_clean.query('EmploymentStatus == \"Not available\"').shape[0]\n",
    "count_nan_replaced = df_clean.EmploymentStatus.isna().sum() - count_nan_before\n",
    "count_status_before, count_status_after, count_nan_replaced"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__There are 871 duplicate rows of data in the dataframe object `df_clean`.__\n",
    "\n",
    "Remove all duplicate rows from the dataframe object `df_clean`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove all duplicate rows\n",
    "df_clean.drop_duplicates(inplace = True)\n",
    "\n",
    "# verify that none of the rows is duplicate\n",
    "df_clean.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__11 of the 16 features in the dataframe object `df_clean` include multiple `NaN` values.__\n",
    "\n",
    "Remove all rows from the the dataframe object `df_clean`, which include at least one instance of `NaN`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ListingKey                    0\n",
       "Term                          0\n",
       "BorrowerAPR                   0\n",
       "ListingCategory               0\n",
       "BorrowerState                 0\n",
       "EmploymentStatus              0\n",
       "EmploymentStatusDuration      0\n",
       "CreditScoreRangeLower         0\n",
       "CreditScoreRangeUpper         0\n",
       "TotalCreditLinespast7years    0\n",
       "TotalInquiries                0\n",
       "DelinquenciesLast7Years       0\n",
       "BankcardUtilization           0\n",
       "DebtToIncomeRatio             0\n",
       "StatedMonthlyIncome           0\n",
       "LoanOriginalAmount            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop all rows with NaN\n",
    "df_clean.dropna(inplace = True)\n",
    "\n",
    "# verify that all rows have non-NaN values across columns\n",
    "df_clean.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a result of dropping rows which either are duplicates or have missing values, the indices of the dataframe object `df_clean` are no longer evenly spaced by a single unit. Reset the index so that the indices increase by 1 from 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([     0,      1,      3,      4,      5,      6,      7,      8,\n",
       "                10,     12,\n",
       "            ...\n",
       "            113927, 113928, 113929, 113930, 113931, 113932, 113933, 113934,\n",
       "            113935, 113936],\n",
       "           dtype='int64', length=95381)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# indices of the dataframe object being cleaned\n",
    "df_clean.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ListingKey</th>\n",
       "      <th>Term</th>\n",
       "      <th>BorrowerAPR</th>\n",
       "      <th>ListingCategory</th>\n",
       "      <th>BorrowerState</th>\n",
       "      <th>EmploymentStatus</th>\n",
       "      <th>EmploymentStatusDuration</th>\n",
       "      <th>CreditScoreRangeLower</th>\n",
       "      <th>CreditScoreRangeUpper</th>\n",
       "      <th>TotalCreditLinespast7years</th>\n",
       "      <th>TotalInquiries</th>\n",
       "      <th>DelinquenciesLast7Years</th>\n",
       "      <th>BankcardUtilization</th>\n",
       "      <th>DebtToIncomeRatio</th>\n",
       "      <th>StatedMonthlyIncome</th>\n",
       "      <th>LoanOriginalAmount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1021339766868145413AB3B</td>\n",
       "      <td>36</td>\n",
       "      <td>0.16516</td>\n",
       "      <td>0</td>\n",
       "      <td>CO</td>\n",
       "      <td>Self-employed</td>\n",
       "      <td>2.0</td>\n",
       "      <td>640.0</td>\n",
       "      <td>659.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.17</td>\n",
       "      <td>3083.333333</td>\n",
       "      <td>9425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10273602499503308B223C1</td>\n",
       "      <td>36</td>\n",
       "      <td>0.12016</td>\n",
       "      <td>2</td>\n",
       "      <td>CO</td>\n",
       "      <td>Employed</td>\n",
       "      <td>44.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.18</td>\n",
       "      <td>6125.000000</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0EF5356002482715299901A</td>\n",
       "      <td>36</td>\n",
       "      <td>0.12528</td>\n",
       "      <td>16</td>\n",
       "      <td>GA</td>\n",
       "      <td>Employed</td>\n",
       "      <td>113.0</td>\n",
       "      <td>800.0</td>\n",
       "      <td>819.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2875.000000</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0F023589499656230C5E3E2</td>\n",
       "      <td>36</td>\n",
       "      <td>0.24614</td>\n",
       "      <td>2</td>\n",
       "      <td>MN</td>\n",
       "      <td>Employed</td>\n",
       "      <td>44.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.26</td>\n",
       "      <td>9583.333333</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0F05359734824199381F61D</td>\n",
       "      <td>60</td>\n",
       "      <td>0.15425</td>\n",
       "      <td>1</td>\n",
       "      <td>NM</td>\n",
       "      <td>Employed</td>\n",
       "      <td>82.0</td>\n",
       "      <td>740.0</td>\n",
       "      <td>759.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.36</td>\n",
       "      <td>8333.333333</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ListingKey Term  BorrowerAPR ListingCategory BorrowerState  \\\n",
       "0  1021339766868145413AB3B   36      0.16516               0            CO   \n",
       "1  10273602499503308B223C1   36      0.12016               2            CO   \n",
       "2  0EF5356002482715299901A   36      0.12528              16            GA   \n",
       "3  0F023589499656230C5E3E2   36      0.24614               2            MN   \n",
       "4  0F05359734824199381F61D   60      0.15425               1            NM   \n",
       "\n",
       "  EmploymentStatus  EmploymentStatusDuration  CreditScoreRangeLower  \\\n",
       "0    Self-employed                       2.0                  640.0   \n",
       "1         Employed                      44.0                  680.0   \n",
       "2         Employed                     113.0                  800.0   \n",
       "3         Employed                      44.0                  680.0   \n",
       "4         Employed                      82.0                  740.0   \n",
       "\n",
       "   CreditScoreRangeUpper  TotalCreditLinespast7years  TotalInquiries  \\\n",
       "0                  659.0                        12.0             3.0   \n",
       "1                  699.0                        29.0             5.0   \n",
       "2                  819.0                        29.0             1.0   \n",
       "3                  699.0                        49.0             9.0   \n",
       "4                  759.0                        49.0             2.0   \n",
       "\n",
       "   DelinquenciesLast7Years  BankcardUtilization  DebtToIncomeRatio  \\\n",
       "0                      4.0                 0.00               0.17   \n",
       "1                      0.0                 0.21               0.18   \n",
       "2                     14.0                 0.04               0.15   \n",
       "3                      0.0                 0.81               0.26   \n",
       "4                      0.0                 0.39               0.36   \n",
       "\n",
       "   StatedMonthlyIncome  LoanOriginalAmount  \n",
       "0          3083.333333                9425  \n",
       "1          6125.000000               10000  \n",
       "2          2875.000000               10000  \n",
       "3          9583.333333               15000  \n",
       "4          8333.333333               15000  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reset index\n",
    "df_clean.reset_index(drop = True, inplace = True)\n",
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Data types of the six columns, `EmploymentStatusDuration`, `CreditScoreRangeLower`, `CreditScoreRangeUpper`, `TotalCreditLinespast7years`, `TotalInquiries`, and `DelinquenciesLast7Years`, are float, not integer as defined in `data_dictionary.xlsx`.__\n",
    "\n",
    "Convert the data types of the six columns from float to integer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EmploymentStatusDuration int32\n",
      "CreditScoreRangeLower int32\n",
      "CreditScoreRangeUpper int32\n",
      "TotalCreditLinespast7years int32\n",
      "TotalInquiries int32\n",
      "DelinquenciesLast7Years int32\n"
     ]
    }
   ],
   "source": [
    "# list of columns the data types of which need to be converted\n",
    "column_list = ['EmploymentStatusDuration', 'CreditScoreRangeLower', 'CreditScoreRangeUpper', 'TotalCreditLinespast7years', 'TotalInquiries', 'DelinquenciesLast7Years']\n",
    "\n",
    "# change the data type of each of the six columns to integer\n",
    "for column in column_list:\n",
    "    df_clean[column] = df_clean[column].astype(dtype = 'int')\n",
    "    \n",
    "    # verify that the data type of the column is integer\n",
    "    print(column, df_clean[column].dtype)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Values under the `StatedMonthlyIncome` column have more than two digits after the decimal although the column represents a monetary amount.__\n",
    "\n",
    "Round the values under the `StatedMonthlyIncome` column to two decimal places."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72522     4000.00\n",
       "84033     7500.00\n",
       "44105     7916.67\n",
       "65635     3591.75\n",
       "73093     3750.00\n",
       "4770     10000.00\n",
       "60113     3166.67\n",
       "23429     3333.33\n",
       "30349     1600.00\n",
       "4356      7000.00\n",
       "Name: StatedMonthlyIncome, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# round StatedMonthlyIncome to two decimal places\n",
    "df_clean.StatedMonthlyIncome = df_clean.StatedMonthlyIncome.round(decimals = 2)\n",
    "\n",
    "# verify that StatedMonthlyIncome shows values for two decimal places\n",
    "df_clean.StatedMonthlyIncome.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Data type of the `LoanOriginalAmount` column, which represents a monetary amount, is an integer, not float.__\n",
    "\n",
    "Convert the data type of the `LoanOriginalAmount` column from integer to float."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('float64')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert the data type of LoanOriginalAmount to float\n",
    "df_clean.LoanOriginalAmount = df_clean.LoanOriginalAmount.astype(dtype = 'float')\n",
    "\n",
    "# verify that the data type of LoanOriginalAmount is float\n",
    "df_clean.LoanOriginalAmount.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Range of each borrower's credit score is split into lower and upper boundaries.__\n",
    "\n",
    "* Create a new column `CreditScoreRange` from the existing columns `CreditScoreRangeLower` and `CreditScoreRangeUpper`.\n",
    "* Change the data type of the `CreditScoreRange` column to (ordinal) category.\n",
    "* Drop the two columns `CreditScoreRangeLower` and `CreditScoreRangeUpper`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CategoricalDtype(categories=['520-539', '540-559', '560-579', '580-599', '600-619',\n",
       "                  '620-639', '640-659', '660-679', '680-699', '700-719',\n",
       "                  '720-739', '740-759', '760-779', '780-799', '800-819',\n",
       "                  '820-839', '840-859', '860-879', '880-899'],\n",
       "                 ordered=True)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = ['CreditScoreRangeLower', 'CreditScoreRangeUpper']\n",
    "\n",
    "# convert the data types of existing columns from integer to string\n",
    "# this conversion is required for the following join operation\n",
    "for column in columns:\n",
    "    df_clean[column] = df_clean[column].astype(dtype = 'str')\n",
    "\n",
    "# join the two boundaries with -\n",
    "df_clean['CreditScoreRange'] = df_clean[columns].apply(lambda x: '-'.join(x), axis = 1)\n",
    "\n",
    "# ordered list of unique values in CreditScoreRange\n",
    "ranges = list(df_clean.CreditScoreRange.unique())\n",
    "ranges.sort()\n",
    "\n",
    "# convert data type of CreditScoreRange to (ordinal) category\n",
    "creditscore_ranges = pd.api.types.CategoricalDtype(ordered = True, categories = ranges)\n",
    "df_clean.CreditScoreRange = df_clean.CreditScoreRange.astype(creditscore_ranges)\n",
    "\n",
    "# verify that data type of CreditScoreRange is (ordinal) category\n",
    "df_clean.CreditScoreRange.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column \"CreditScoreRangeLower\" cannot be found.\n",
      "Column \"CreditScoreRangeUpper\" cannot be found.\n"
     ]
    }
   ],
   "source": [
    "# drop two columns\n",
    "df_clean.drop(columns = columns, inplace = True)\n",
    "\n",
    "# verify that the two columns for the lower and upper credit scores are no longer available\n",
    "for column in columns:\n",
    "    if column not in df_clean.columns:\n",
    "        print('Column \"{}\" cannot be found.'.format(column))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.3 Store Cleaned Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean.to_csv('data/prosperLoanData_clean.csv', index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}