Lending disparities using Logistic Regression#
The story: https://www.revealnews.org/article/for-people-of-color-banks-are-shutting-the-door-to-homeownership/
Author: Aaron Glantz and Emmanuel Martinez
Topics: Logistic regression, odds ratios
Datasets
- philadelphia-mortgages.csv: Philadelphia mortgage data for 2015
- A subset of HMDA LAR data from FFEIC
- Codebook is
2015HMDACodeSheet.pdf - A guide to HMDA reporting
- I've massaged it slightly to make processing a bit easier
- nhgis0006_ds233_20175_2017_tract.csv:
- Table B03002: Hispanic or Latino Origin by Race
- 2013-2017 American Community Survey data US Census Bureau, from NHGIS
- Codebook is
nhgis0006_ds233_20175_2017_tract_codebook.txt
- lending_disparities_whitepaper_180214.pdf: the whitepaper outlining Reveal's methodology
What's the goal?#
Do banks provide mortgages at disparate rates between white applicants and people of color? We're going to look at the following variables to find out:
- Race/Ethnicity
- Native American
- Asian
- Black
- Native Hawaiian
- Hispanic/Latino
- Race and ethnicity were not reported
- Sex
- Whether there was a co-applicant
- Applicant’s annual income (includes co-applicant income)
- Loan amount
- Ratio between the loan amount and the applicant’s income
- Ratio between the median income of the census tract and the median income of the Metro area
- Racial and ethnic breakdown by percentage for each census tract
- Regulating agency of the lending institution
Setup#
Import pandas as usual, but also import numpy. We'll need it for logarithms and exponents.
Some of our datasets have a lot of columns, so you'll also want to use pd.set_option to display up to 100 columns or so.
import numpy as np
import pandas as pd
pd.set_option("display.max_columns", 100)
pd.set_option("display.max_rows", 100)
pd.set_option("display.float_format",'{:,.5f}'.format)
What is each row of our data?#
If you aren't sure, you might need to look at either the whitepaper or the codebook. You'll need to look at them both eventually, so might as well get started now.
Read in your data#
Read in our Philadelphia mortgage data and take a peek at the first few rows.
- Tip: As always, census tract columns like to cause problems if they're read in as numbers. Make sure pandas reads it in as a string.
# We're just looking at Philly
mortgage = pd.read_csv("data/philadelphia-mortgages.csv", dtype={ 'census_tract': 'str'})
mortgage.head(5)
Check your column types#
I mentioned it above, but make sure census_tract is an object (a string) or merging isn't going to be any fun later on.
mortgage.dtypes
Engineering and cleaning up features#
Income-related columns#
When we plotted the number of applicants, how much money they made and the size of the loan, we found that it skewed to the left, meaning the majority of applicants were clustered on the lower end of the income and loan amount scales. This was especially true for applicants of color. We took the logarithm transformation of income and loan amount to normalize the distribution of those variables and limit the effect of extreme outliers.
Traditionally we would calculate these values and save them in new columns. Since it's just simple math, though, we'll be able to do it when we run the regression.
Co-applicants#
Right now we have a column about the co-applicant's sex (see the codebook for column details). We don't want the sex, though, we're interested in whether there is a co applicant or not. Use the co-applicant's sex to create a new column called co_applicant that is either 'yes', 'no', or 'unknown'.
- Hint: If the co-applicant's sex was not provided or is not applicable, count it as unknown.
- Hint: The easiest way is to use
.replaceon the co-applicant sex column, but store the result in your new column
mortgage['co_applicant'] = mortgage.co_applicant_sex.replace({
1: 'yes',
2: 'yes',
3: 'unknown',
4: 'unknown',
5: 'no'
})
mortgage.head()
Filter loan applicants#
If you read the whitepaper - lending_disparities_whitepaper_180214.pdf - many filters are used to get to the target dataset for analysis.
Loan type
While we recognize the substantial presence of applicants of color in the FHA market, we focused on conventional home loans for several reasons.
Property type
Prospective borrowers submit loan applications for various types of structures: one- to four-unit properties, multi-family properties and manufactured homes. For this analysis, we focused on one- to four-unit properties.
Occupancy
We included only borrowers who said they planned to live in the house they were looking to buy. We did this to exclude developers or individuals who were buying property as an investment or to subsequently flip it.
Action Type
We wanted to look at the reasons lending institutions deny people a mortgage. After conversations with former officials at HUD, we decided to include only those applications that resulted in originations (action type 1) or denials (action type 3)
Income
An applicant’s income isn’t always reported in the data. In other cases, the data cuts off any incomes over \$9.9 million and any loan amounts over \\$99.9 million, meaning there’s a value in the database, but it’s not precise. We focused only on those records where income and loan amount have an accurate estimation. This meant discarding about 1 percent of all conventional home loans in the country for 2016. [Note: I already edited this]
When we plotted the number of applicants, how much money they made and the size of the loan, we found that it skewed to the left, meaning the majority of applicants were clustered on the lower end of the income and loan amount scales. This was especially true for applicants of color. We took the logarithm transformation of income and loan amount to normalize the distribution of those variables and limit the effect of extreme outliers.
Lien status
We included all cases in our analysis regardless of lien status.
Race and ethnicity
At first, we looked at race separate from ethnicity, but that approach introduced too many instances in which either the ethnicity or race was unknown. So we decided to combine race and ethnicity. Applicants who marked their ethnicity as Hispanic were grouped together as Hispanic/Latino regardless of race. Non-Hispanic applicants, as well as those who didn’t provide an ethnicity, were grouped together by race: non-Hispanic white, non-Hispanic black, etc. [Note: This has already been taken care of]
Loan purpose
We decided to look at home purchase, home improvement and refinance loans separately from each other. [Note: please look at home purchase loans.]
Use the text above (it's from the whitepaper) and the 2015HMDACodeSheet.pdf code book to filter the dataset.
- Tip: there should be between 5-8 filters, depending on how you write them.
mortgage = mortgage[(mortgage.loan_type == 1) & \
(mortgage.property_type == 1) & \
(mortgage.occupancy == 1) & \
mortgage.action_type.isin([1,3]) & \
(mortgage.income != 9999) & \
(mortgage.loan_amount != 99999) & \
(mortgage.loan_purpose == 1)]
mortgage = mortgage.copy()
mortgage.shape
When you're done filtering, save your dataframe as a "copy" with df = df.copy() (if it's called df, of course). This will prevent irritating warnings when you're trying to create new columns.
Confirm that you have 10,107 loans with 19 columns#
mortgage.shape
Create a "loan denied" column#
Right now the action_type category reflects whether the loan was granted or not, and either has a value of 1 or 3. We'll need to create a new column called loan_denied, where the value is 0 if the loan was accepted and 1 if the loan was denied.
While we're eventually going to do a bunch of crazy comparisons and math inside of our statsmodels formula, we do need the target of our regression to be a number. You'll see what I mean later on!
mortgage['loan_denied'] = (mortgage.action_type == 3).astype(int)
Deal with categorical variables#
Let's go ahead and take a look at our categorical variables:
- Applicant sex (male, female, na)
- Applicant race
- Mortgage agency
- Co-applicant (yes, no, unknown)
Before we do anything crazy, let's use the codebook to turn them into strings.
- Tip: We already did this with the
co_applicantcolumn, you only need to do the rest - Tip: Just use
.replace
mortgage.applicant_sex = mortgage.applicant_sex.replace({
1: 'male',
2: 'female',
3: 'na'
})
mortgage.applicant_race = mortgage.applicant_race.replace({
1: 'native_amer',
2: 'asian',
3: 'black',
4: 'hawaiian',
5: 'white',
6: 'na',
7: 'na',
8: 'na',
99: 'latino'
})
mortgage.agency_code = mortgage.agency_code.replace({
1: 'OCC',
2: 'FRS',
3: 'FDIC',
5: 'NCUA',
7: 'HUD',
9: 'CFPB'
})
mortgage.head(3)
Double-check these columns match these values in the first three rows (and yes, you should have a lot of other columns, too).
| applicant_sex | agency_code | applicant_race | co_applicant |
|---|---|---|---|
| female | OCC | white | no |
| na | OCC | na | unknown |
| male | OCC | white | no |
Double-check our mortage data#
mortgage.head()
mortgage.shape
Census data#
Now we just need the final piece to the puzzle, the census data. Read in the census data file, calling the dataframe census.
Tip: As always, be sure to read the tract column in as a string. Interestingly, this time we don't need to worry about the state or county codes in the same way.
Tip: You're going to encounter a problem that you find every time you read in a file from the US government!
census = pd.read_csv("data/nhgis0007_ds215_20155_2015_tract.csv", encoding='latin-1', dtype={'TRACTA': 'str'})
census.head(2)
Rename some columns#
If you like to keep your data extra clean, feel free to rename the columns you're interested in. If not, feel free to skip it!
Tip: Make sure you're using the estimates columns, not the margin of error columns
# join on STATEA-state code, COUNTYA-county code, TRACTA-census tract (cleaned)
census = census.rename(columns={
'ADK5E001': 'pop_total',
'ADK5E003': 'pop_white',
'ADK5E004': 'pop_black',
'ADK5E005': 'pop_amer_indian',
'ADK5E006': 'pop_asian',
'ADK5E007': 'pop_pac_islander',
'ADK5E012': 'pop_hispanic'
})
census.head(2).T
Computed columns#
According to Reveal's regression output, you'll want to create the following columns:
- Percent Black in tract
- Percent Hispanic/Latino in tract (I hope you know how Hispanic/Latino + census data works by now)
- Percent Asian in tract
- Percent Native American in tract
- Percent Native Hawaiian in tract
Notice that we don't include percent white - because all of the other columns add up to percent white, we ignore it! It's similar to a reference category.
If we want to use buzzwords here, the technical reason we're not using percent white is called collinearity. We'll talk more about it on Friday.
mortgage.head(2)
census.head(2)
mortgage['census_tract'] = mortgage['census_tract'].str.replace(".", "")
mortgage.head(2)
Do the merge#
merged = mortgage.merge(census,
left_on=['state_code', 'county_code', 'census_tract'],
right_on=['STATEA', 'COUNTYA', 'TRACTA'])
merged.head()
Confirm you have 10107 rows and 96 columns in the merged dataframe.
merged.shape
merged.head(2).T
Performing our regression#
Note: When working with statsmodels formulas, you don't need to drop missing data. It's handled automatically as part of the .fit() process.
Working with statsmodels formulas#
Statsmodels formulas are a fun (yes, fun! exciting! amazing!) way to write regressions. For example, I can write a formula to say "calculate the relationship between a loan being denied in relation to the loan amount and the applicant's income."
import statsmodels.formula.api as smf
model = smf.logit("""
loan_denied ~ loan_amount + income
""", data=merged)
result = model.fit()
result.summary()
But that's child's play: we're here to get a little bit crazy.
Formulas and calculations in statsmodels formulas#
Let's do this! Instead of building new columns in pandas, we're just going to tell statsmodels to do it for us. This is using something called Patsy, imitating the programming language R.
| description | pandas style | formula style |
|---|---|---|
| Multiply column | df.colname * 100 |
np.multiply(colname, 100) |
| Divide columns | df.loan_amount / df.income |
np.divide(loan_amount, income) |
| Percentage | df.pop_black / pop_total * 100 |
np.multiply(pop_black / pop_total, 100) |
| Calculate log | np.log(income) |
np.log(income) |
| One-hot encoding | pd.get_dummies(df.agency_code).drop('FDIC', axis=1) |
C(agency_code, Treatment('FDIC') |
If you haven't heard of one-hot encoding before, I recommend reading the longer version of this notebook! Or looking at what happens down below and thinking it through.
If we follow Reveal's methodology, we have a nice long list of features to include in our formula. Turning them all into a statsmodels/Patsy formula, the result looks like this:
import statsmodels.formula.api as smf
model = smf.logit("""
loan_denied ~
tract_to_msa_income_percent
+ np.log(income)
+ np.log(loan_amount)
+ np.divide(loan_amount, income)
+ C(co_applicant, Treatment('no'))
+ C(applicant_sex, Treatment('female'))
+ C(applicant_race, Treatment('white'))
+ C(agency_code, Treatment('FDIC'))
+ np.multiply(pop_hispanic / pop_total, 100)
+ np.multiply(pop_black / pop_total, 100)
+ np.multiply(pop_amer_indian / pop_total, 100)
+ np.multiply(pop_asian / pop_total, 100)
+ np.multiply(pop_pac_islander / pop_total, 100)
""", data=merged)
result = model.fit()
result.summary()
It's beautiful! It's amazing! We just wrote our formulas inside of the statsmodels formula, and it did all the work for us.
I'm not crying, you're crying.
Sorting by odds ratio#
Since we're interested in the odds ratio, it makes sense to reformat our results, add an odds ratio column, and sort the output.
feature_names = result.params.index
coefficients = result.params.values
coefs = pd.DataFrame({
'coef': coefficients,
'odds ratio': np.exp(result.params.values),
'pvalue': result.pvalues
}).sort_values(by='odds ratio', ascending=False)
coefs
And there we go! Except, of course, it's very ugly.
While writing the formula was a lot nicer and less error-prone (I think) than building a dataframe of our features, the output is horrendous. Those features names are terrible! It looks so so so bad.
We thought we had a beautiful world ahead of us in terms of writing nice precise formulas instead of wrangling new columns, but it looks like it won't be quite so simple.
Renaming our output fields#
But don't worry: even if we love the formula method, we don't have to suffer through those results.
Yes, we can understand the columns, but it's a lot of work to really read what's going on. Life would be much nicer if C(applicant_race, Treatment('white'))[T.latino] were just applicant_race_latino. You'd have to remember what the reference category is, but if we think we can handle it, let's head onward.
While statsmodels doesn't make it easy, it's definitely possible to reach in and rename our features. We do it like this:
# Copy the names to a pd.Series for easy search/replace
# We'll also keep a safe copy to make double-checking easy later
names = pd.Series(model.data.xnames)
originals = list(names.copy())
# Reformat 'C(agency_code, Treatment('FDIC'))[T.FRS]' as 'agency_code_FRS'
names = names.str.replace(r", ?Treatment\(.*\)", r"")
names = names.str.replace(r"C\(([\w]+)", r"\1_")
names = names.str.replace(r"\[T.(.*)\]", r"\1")
# Manually replace other ones
names = names.replace({
'np.multiply(pop_hispanic / pop_total, 100)': 'pop_hispanic',
'np.multiply(pop_black / pop_total, 100)': 'pop_black',
'np.multiply(pop_amer_indian / pop_total, 100)': 'pop_amer_indian',
'np.multiply(pop_asian / pop_total, 100)': 'pct_asian',
'np.multiply(pop_pac_islander / pop_total, 100)': 'pop_pac_islander',
'np.log(income)': 'log_income',
'np.log(loan_amount)': 'log_loan',
'np.divide(loan_amount, income)': 'loan_income_ratio',
})
original_names = model.data.xnames
# Assign back into the model for display
model.data.xnames = list(names)
# Redo our summary, and we get nice output!
result.summary()
Everything still works great! We can rebuild our coefficient/odds ratio/p-value situation without any trouble at all.
feature_names = result.params.index
coefficients = result.params.values
coefs = pd.DataFrame({
'coef': coefficients,
'odds ratio': np.exp(result.params.values),
'pvalue': result.pvalues,
'original': originals
}).sort_values(by='odds ratio', ascending=False)
coefs
Interpreting and thinking about the analysis#
Question 1#
Our results aren't exactly the same as Reveal's, as I pulled a slightly different number of rows from the database and I'm not sure what exact dataset they used for census information. How are we feeling about this reproduction? You might want check their 2015 results in the whitepaper.
# I mean come on it's pretty close
Question 2#
In the opening paragraph to the flagship piece, Aaron and Emmanuel write:
Fifty years after the federal Fair Housing Act banned racial discrimination in lending, African Americans and Latinos continue to be routinely denied conventional mortgage loans at rates far higher than their white counterparts.
If you look at the results, Hawaiian/Pacific Islanders (and maybe Native Americans) have an even higher odds ratio. Why do they choose to talk about African Americans and Latinos instead?
# Not nearly as many of those two groups
# And I mean like REALLY not that many
train_df.loc[:,"pct_hispanic":"pct_pac_islander"].median()
Question 3#
Write a sentence expressing the meaning of the odds ratio statistic for Black mortgage applicants. Find a line in the Reveal piece where they use the odds ratio.
# “I had a fair amount of savings and still had so much trouble just left and
# right,” said Rachelle Faroul, a 33-year-old black woman who was rejected twice
# by lenders when she tried to buy a brick row house close to Malcolm X Park in
# Philadelphia, where Reveal found African Americans were 2.7 times as likely as
# whites to be denied a conventional mortgage.
Question 4#
Write a similar sentence about men.
# Men are 12% more likely to be denied a conventional mortgage
Question 5#
Why did Aaron and Emmanuel choose to include the loan-to-income ratio statistic? You might want to read the whitepaper.
# Loan-to-income ratio: Looking at the raw numbers of an applicant’s income and
# loan amount doesn’t tell the whole picture. We needed to look at how much money
# applicants wanted to take out in relation to their income. This provides a proxy
# for whether or not the loan amount was manageable compared with the applicant’s income.
# Experts agreed that this variable should be included.
Question 6#
Credit score is a common reason why loans are denied. Why are credit scores not included in our analysis? You might want to read the whitepaper.
# They aren't available!
Question 7#
This data was just sitting out there for anyone to look at, they didn't even need to FOIA it. Why do you think this issue had not come up before Reveal's analysis?
# This is just an opinion! but I asked them:
# emmanuel - the data is just so big
# aaron - it's a blind spot of journalism, business press only writes about profit
Question 8#
As a result of this series, a lot has happened, although recent changes don't look so good. If you were reporting this story, what groups of people would you want to talk to in order to make sure you're getting the story right?
# aaron and emmanuel talked to experts in research, along with enforcement officers and others at CFPB
Question 9#
When they were consulting experts, Aaron and Emmanuel received a lot of conflicting accounts about whether they should include the "N/A" values for race (they ended up including it). If the experts disagreed about something like that, why do you think they went forward with their analysis?
# Experts will rarely agree, it eventually comes down to editorial judgment. There's not always
# a very clear delineation of right/wrong
Question 10#
What if we were working on this story, and our logistic regression or input dataset were flawed? What would be the repercussions?
# We would be making pretty big claims that weren't backed up - it wouldn't just be us having
# to research more, it would be us actually staking our credibility on the line