Mortgage Data Exercise (6/22/21)

Mortgage Data Exercise (6/22/21) | Mathaus Silva

In preparation for next class' introduction to machine learning, we'll be cleaning the mortgage testing dataset. First, we import the appropriate libraries, load in the mortgage data, and check the data types of the columns.

import pandas as pd import numpy as np # load in data df = pd.read_csv('mortgage-testing-data.csv') # check data types of the columns df.dtypes

In order to use this data for a future numeric model, we will be replacing categorical columns seen above (labeled 'object') with boolean columns.

First, we replace the original 'conforming_loan_limit' column with two boolean columns, where 'conforming_loan_limit_c' means conforming loan limit is C (conforming) and 'conforming_loan_limit_nc' means conforming loan limit is NC (not conforming).

# create new columns df['conforming_loan_limit_c'] = np.where( df['conforming_loan_limit'] == 'C', 1, 0 ) df['conforming_loan_limit_nc'] = np.where( df['conforming_loan_limit'] == 'NC', 1, 0 ) # delete original column del df['conforming_loan_limit'] df

Since a loan can only either be conforming or non-conforming, 'conforming_loan_limit_c' and 'conforming_loan_limit_nc' will never store the same value. A similar process will be repeated for 'derived_sex' and 'action_taken' columns.

We replace the original 'derived_sex' column with two boolean columns, where 'derived_sex_m' means derived sex is Male and 'derived_sex_f' means derived sex is Female.

# create new columns df['derived_sex_m'] = np.where( df['derived_sex'] == 'Male', 1, 0 ) df['derived_sex_f'] = np.where( df['derived_sex'] == 'Female', 1, 0 ) # delete original column del df['derived_sex'] df

As for the action_taken column, it only contains 1s and 2s. This is because the dataset was filtered to only include accepted or rejected mortgages (no withdrawals, pre-approvals, etc.). We are replacing this column with another boolean column, using 0s and 1s for False/True, meaning "application accepted."

# create new column df['application_accepted'] = np.where( df['action_taken'] == 1, 1, 0 ) # delete original column del df['action_taken'] df

Now, column 'application_accepted' outputs 0 for every denied application, and 1 for every approved application.

The 'debt_to_income_ratio' column is categorical as opposed to numeric. We can make it numeric by replacing each category with a central value in that category. In this case, we'll be using a dictionary to assign a value to a range of percentages and later mapping it to the 'debt_to_income_ratio' column. Values that don't fall into the following ranges will remain untouched.

dict = {'<20%': 10, '20%-<30%' : 25, '30%-<36%' : 33, '50%-60%' : 55, '>60%': 70} df['debt_to_income_ratio'] = df['debt_to_income_ratio'].map(dict)\ .fillna(df['debt_to_income_ratio']).astype('float64') df

Now that we've replaced all our categorical columns, we use 'df.dtypes' once again to check that our columns have changed to numeric data types.

df.dtypes

As they are all now numeric columns, our DataFrame is cleaned and ready to export as a CSV file.

df.to_csv( 'cleaned-mortgage-testing-data.csv' )