Loan Default Risk at 2.26M Rows
Memory-efficient pandas over the full LendingClub book (2.26 million loans, 2007-2018) to quantify how grade, purpose, and debt-to-income stack the odds of charge-off.
View code & data on GitHub ↗The Problem
Peer-to-peer lenders price risk through letter grades, but how well do those grades, and the borrower attributes behind them, actually separate good loans from charge-offs? This analysis reconstructs the risk picture from loan outcomes across the full 2007-2018 book: default rates by grade, purpose, DTI band, and income verification status.
At 2.26 million rows and 151 columns the raw CSV is 1.6 GB, which does not fit comfortably in memory on a laptop. Half the project is engineering the load: chunked reading, dtype downcasting, and categorical encoding cut the working set to under 400 MB before any analysis starts.
Approach
Chunked ingestion with column triage
Read the 1.6 GB CSV in 250k-row chunks, keeping only 18 analysis-relevant columns of 151. Concatenating the filtered chunks avoids ever holding the full raw file in memory.
Memory diet via dtypes
Downcast floats to float32, encoded grade, purpose, and status as categoricals, and parsed dates on load. Working memory dropped from over 2.5 GB to under 400 MB, measured with memory_usage(deep=True).
Target construction
Derived a binary charged_off target from loan_status, keeping only resolved loans (Fully Paid or Charged Off, about 1.35M rows) to avoid survivorship distortion from loans still current.
Risk ladders by grade, purpose, and DTI
Groupby aggregations across the resolved book confirmed the grade ladder monotonically orders risk while purpose adds independent signal, then pd.qcut banding located the DTI inflection point.
Expected-loss framing
Combined default probability with average loan size per segment to estimate expected loss contribution, showing which segments drive dollar losses rather than just bad rates.
The Work
Representative excerpts from the analysis: the queries and transformations doing the heavy lifting. The full runnable project lives in the GitHub repo.
import pandas as pd
KEEP = ["loan_amnt", "term", "int_rate", "grade", "sub_grade",
"emp_length", "home_ownership", "annual_inc",
"verification_status", "purpose", "dti",
"loan_status", "issue_d"]
DTYPES = {
"loan_amnt": "float32", "int_rate": "float32",
"annual_inc": "float32", "dti": "float32",
"grade": "category", "sub_grade": "category",
"purpose": "category", "home_ownership": "category",
"verification_status": "category", "term": "category",
"loan_status": "category", "emp_length": "category",
}
chunks = pd.read_csv(
"accepted_2007_2018.csv",
usecols=KEEP, dtype=DTYPES,
parse_dates=["issue_d"],
chunksize=250_000, low_memory=True,
)
df = pd.concat(chunks, ignore_index=True)
mb = df.memory_usage(deep=True).sum() / 1e6
print(f"{len(df):,} loans loaded, {mb:.0f} MB in memory")
# 2,260,701 loans loaded, ~380 MB in memoryresolved = df[df["loan_status"].isin(
["Fully Paid", "Charged Off"])].copy()
resolved["charged_off"] = (
resolved["loan_status"] == "Charged Off").astype("int8")
def default_ladder(col, min_n=5_000):
g = (resolved.groupby(col, observed=True)
.agg(loans=("charged_off", "size"),
default_rate=("charged_off", "mean"),
avg_loan=("loan_amnt", "mean")))
g = g[g["loans"] >= min_n]
g["default_rate"] = (g["default_rate"] * 100).round(1)
g["exp_loss_share"] = (
g["loans"] * g["avg_loan"] * g["default_rate"])
g["exp_loss_share"] = (100 * g["exp_loss_share"]
/ g["exp_loss_share"].sum()).round(1)
return g.sort_values("default_rate", ascending=False)
print(default_ladder("grade"))
print(default_ladder("purpose").head(8))
resolved["dti_band"] = pd.qcut(resolved["dti"], q=5,
labels=["very low", "low", "mid", "high", "very high"])
print(default_ladder("dti_band"))What the Data Shows
Charge-off rate by loan grade
% of resolved loans charged off
The ladder is monotonic across 1.35M resolved loans. The D-to-E step is the steepest single jump, a natural pricing breakpoint.
Key Findings
Charge-off rates climb monotonically from 5.9% (grade A) to 32.5% (grade G) across 1.35M resolved loans. The ladder works, but the jump from D to E is the steepest single step in the curve.
Small-business loans default at about 27%, nearly double the book average, and carry larger balances, so they contribute an outsized share of expected dollar losses.
The highest DTI quintile defaults about 1.6 times more often than the lowest, with the inflection concentrated above a DTI of roughly 20.
'Verified income' loans default slightly more than unverified ones because verification was triggered for riskier applications: a textbook selection-bias trap for naive interpretation.
60-month terms default meaningfully more than 36-month terms at the same grade, compounding duration risk with credit risk.
Chunked reading plus dtype downcasting cut memory from over 2.5 GB to under 400 MB, the difference between 'needs a cluster' and 'runs on a laptop'.
Recommendations
Reprice or cap grade E-G small-business lending
The intersection of the steepest grade step and the worst purpose category concentrates expected loss. A cap costs little volume relative to loss avoided.
Treat DTI above 20 as a pricing breakpoint
The default inflection above DTI 20 justifies a rate adjustment tier rather than the current smooth gradient.
Never read verification status naively
Any model or dashboard consuming this data must treat verification as risk-triggered, not as a positive signal. Document it in the data dictionary.
Separate duration risk in reporting
Report 36 and 60-month cohorts separately. Blended default rates understate long-term risk while rates are climbing.