Mortgage Application Rates Part I: Utilizing Pivot Tables

Dataset Used (Part I,II, III):

Project III Blog Dataset Used.xlsx

Download XLSX • 5.78MB

In today's internship/bootcamp we were assigned a Project to analyze loan origination/denial rates with a variety of different features; so, let's take a look at the findings:

1. Which Mortgage Agency had the highest portion of loans in the Market?

Process: In order to accomplish this task, I decided to utilize the pivot table; it's a quick easy way to visualize and sort the information.

Conclusion: My findings resulted in the above table, with the Department of Housing and Urban Development with an overwhelming share of handling Mortgage Loan applications in the market.

2. What were the percentages of each action type in the loan process?

FYI: It is important to note, in our original data set we had a column entitled "loan action type", which categorized every completed step in the loan process.

Process: Once again, I found the easiest way to communicate this information to a company stakeholder would be by utilizing a pivot table; it is clean, easy, and communicates the data without any further explanation. Conclusion: A majority of loan applications resulted in loan origination.

3. Where did the loan originations occur (Who actually received money)?

FYI: A business runs on dollars and cents. Having this information in mind, digging deeper into loan originations and application denials would provide important information for stakeholder decision-making. Given this information, let's separate the total loan action types into either loan origination or loan denial (the sum of these two action types add up to 100%).

Process: Once again, we want to know who is receiving money from the company (loans originated) and who is not (application denials). For this information, let's utilize the....you guessed it: The Pivot Table. Action type is split into two categories, so the rows would be a great place to fit this information, while more labels generally benefit in the columns. For the values, placing a count of each action type given an applicant's race.

Conclusion: From our result above, we are noticing a bias right off-the-bat. In order to confirm this, we need to look a little deeper at the data.

4. African American vs. White Loan Approval by Income Level

FYI: Because African American applicants revealed to have the lowest loan origination percentage in the previous table, and White applicants were among the highest, let's take a look at the income level to see whether a bias truly exists.

Process: It is important to note, that because we are comparing subcategories of both applicants, we want the total action types of each race's income class to add up to 100%. This will provide us with the information we want to see: if there is a difference in the social classes of each race. For this, because there are only two action types, let's put them in the columns of our pivot table. Alternatively, because we are separating each applicant race into subcategories of social class, let's put that information in the rows. Finally, because we want to see the number of occurrences of each action type, let's use that in the values using COUNT. Conclusion: Across the board, African Americans had higher application denials in every income class than White Applicants, and White applicants had higher origination rates in every income class than African Americans.

SUMMARY: Our analysis resulted in the conclusion that there is bias in applicant approvals/denials when comparing White applicants and African American applicants. Knowing this, are there any other portions of the loan process where we could check for bias?

Coming up Next... We'll be testing the impact of an applicant's race on interest rates.