challenge, it’s typically tempting to leap to modeling. But step one and crucial one is to grasp the information.
In our earlier publish, we introduced how the databases used to construct credit score scoring fashions are constructed. We additionally spotlight the significance of asking proper questions:
- Who’re the purchasers?
- What kinds of loans are they granted?
- What traits seem to clarify default threat?
On this article, we illustrate this foundational step utilizing an open-source dataset accessible on Kaggle: the Credit score Scoring Dataset. This dataset accommodates 32,581 observations and 12 variables describing loans issued by a financial institution to particular person debtors.
These loans cowl a variety of financing wants — medical, private, instructional, {and professional} — in addition to debt consolidation operations. Mortgage quantities vary from $500 to $35,000.
The variables seize two dimensions:
- contract traits (mortgage quantity, rate of interest, objective of financing, credit score grade, and time elapsed since mortgage origination),
- borrower traits (age, revenue, years {of professional} expertise, and housing standing).
The mannequin’s goal variable is default, which takes the worth 1 if the shopper is in default and 0 in any other case.
In the present day, many instruments and an rising variety of AI brokers are able to routinely producing statistical descriptions of datasets. However, performing this evaluation manually stays a superb train for freshmen. It builds a deeper understanding of the information construction, helps spotlight potential anomalies, and helps the identification of variables which may be predictive of threat.
On this article, we take a easy tutorial strategy to statistically describing every variable within the dataset.
- For categorical variables, we analyze the variety of observations and the default price for every class.
- For steady variables, we discretize them into 4 intervals outlined by the quartiles:
- ]min; Q1], ]Q1; Q2], ]Q2; Q3] and ]Q3; max]
We then apply the identical descriptive evaluation to those intervals as for categorical variables. This segmentation is bigoted and may very well be changed by different discretization strategies. The aim is solely to get an preliminary learn on how threat behaves throughout the totally different mortgage and borrower traits.
Descriptive Statistics of the Modeling Dataset
Distribution of the Goal Variable (loan_status)
This variable signifies whether or not the mortgage granted to a counterparty has resulted in a reimbursement default. It takes two values: 0 if the shopper is just not in default, and 1 if the shopper is in default.
Over 78% of shoppers haven’t defaulted. The dataset is imbalanced, and it is very important account for this imbalance throughout modeling.
The subsequent related variable to investigate can be a temporal one. It might enable us to review how the default price evolves over time, confirm its stationarity, and assess its stability and its predictability.
Sadly, the dataset accommodates no temporal info. We have no idea when every commentary was recorded, which makes it unattainable to find out whether or not the loans had been issued throughout a interval of financial stability or throughout a downturn.
This info is nonetheless important in credit score threat modeling. Borrower habits can fluctuate considerably relying on the macroeconomic surroundings. As an illustration, throughout monetary crises — such because the 2008 subprime disaster or the COVID-19 pandemic — default charges sometimes rise sharply in comparison with extra favorable financial durations.
The absence of a temporal dimension on this dataset subsequently limits the scope of our evaluation. Particularly, it prevents us from finding out how threat dynamics evolve over time and from evaluating the potential robustness of a mannequin in opposition to financial cycles.
We do, nevertheless, have entry to the variable cb_person_cred_hist_length, which represents the size of a buyer’s credit score historical past, expressed in years.
Distribution by Credit score Historical past Size (cb_person_cred_hist_length)
This variable has 29 distinct values, starting from 2 to 30 years. We’ll deal with it as a steady variable and discretize it utilizing quantiles.
A number of observations could be drawn from the desk above. First, greater than 56% of debtors have a credit score historical past of 4 years or much less, indicating that a big proportion of shoppers within the dataset have comparatively quick credit score histories.
Second, the default price seems pretty steady throughout intervals, hovering round 21%. That stated, debtors with shorter credit score histories are inclined to exhibit barely riskier habits than these with longer ones, as mirrored of their greater default charges.
Distribution by Earlier Default (cb_person_default_on_file)
This variable signifies whether or not the borrower has beforehand defaulted on a mortgage. It subsequently offers useful details about the previous credit score habits of the shopper.
It has two attainable values:
- Y: the borrower has defaulted previously
- N: the borrower has by no means defaulted
On this dataset, greater than 80% of debtors haven’t any historical past of default, suggesting that almost all of shoppers have maintained a passable reimbursement document.
Nevertheless, a transparent distinction in threat emerges between the 2 teams. Debtors with a earlier default historical past are considerably riskier, with a default price of about 38%, in contrast with round 18% for debtors who’ve by no means defaulted.
This result’s according to what is often noticed in credit score threat modeling: previous reimbursement habits is commonly one of many strongest predictors of future default.
Distribution by Age
The presence of the age variable on this dataset signifies that the loans are granted to particular person debtors (retail shoppers) quite than company entities. To higher analyze this variable, we group debtors into age intervals primarily based on quartiles.
The dataset contains debtors throughout a variety of ages. Nevertheless, the distribution is strongly skewed towards youthful people: greater than 70% of debtors are underneath 30 years previous.
The evaluation of default charges throughout the age teams reveals that the highest threat is concentrated within the first quartile, adopted by the second quartile. In different phrases, youthful debtors look like the riskiest phase on this dataset.
Distribution by Annual Earnings
Debtors’ annual revenue on this dataset ranges from $4,000 to $6,000,000. To investigate its relationship with default threat, we divide revenue into 4 intervals primarily based on quartiles.
The outcomes present that the very best default charges are concentrated amongst debtors with the bottom incomes, notably within the first quartile ($4,000–$385,00) and the second quartile ($385,00–$55,000).
As revenue will increase, the default price steadily decreases. Debtors within the third quartile ($55,000–$792,000) and the fourth quartile ($792,000–$600,000) exhibit noticeably decrease default charges.
General, this sample suggests an inverse relationship between annual revenue and default threat, which is according to normal credit score threat expectations: debtors with greater incomes sometimes have higher reimbursement capability and monetary stability, making them much less more likely to default.
Distribution by Dwelling Possession
This variable describes the borrower’s housing standing. The classes embrace RENT (tenant), MORTGAGE (home-owner with a mortgage), OWN (home-owner with out a mortgage), and OTHER (different housing preparations).
On this dataset, roughly 50% of debtors are renters, 40% are householders with a mortgage, 8% personal their dwelling outright, and about 2% fall into the “OTHER” class.
The evaluation reveals that the very best default charges are noticed amongst renters (RENT) and debtors labeled as “OTHER.” In distinction, householders with out a mortgage (OWN) exhibit the bottom default charges, adopted by debtors with a mortgage (MORTGAGE).
Distributionby individual employment size person_emp_length
This variable measures the borrower’s employment size in years. To investigate its relationship with default threat, debtors are grouped into 4 intervals primarily based on quartiles: the first quartile (0–2 years), the second quartile (2–4 years), the third quartile (4–7 years), and the fourth quartile (7 years or extra).
The evaluation exhibits that the very best default charges are concentrated amongst debtors with the shortest employment histories, notably these within the first quartile (0–2 years) and the second quartile (2–4 years).
As employment size will increase, the default price tends to say no. Debtors within the third quartile (4–7 years) and the fourth quartile (7 years or extra) exhibit decrease default charges.
General, this sample suggests an inverse relationship between employment size and default threat, indicating that debtors with longer employment histories might profit from higher revenue stability and monetary safety, which reduces their probability of default.
Distribution by mortgage intent
This categorical variable describes the objective of the mortgage requested by the borrower. The classes embrace EDUCATION, MEDICAL, VENTURE (entrepreneurship), PERSONAL, DEBTCONSOLIDATION, and HOMEIMPROVEMENT.
The variety of debtors is pretty balanced throughout the totally different mortgage functions, with a barely greater share of loans used for training (EDUCATION) and medical bills (MEDICAL).
Nevertheless, the evaluation reveals notable variations in threat throughout classes. Debtors in search of loans for debt consolidation (DEBTCONSOLIDATION) and medical functions (MEDICAL) exhibit greater default charges. In distinction, loans meant for training (EDUCATION) and entrepreneurial actions (VENTURE) are related to decrease default charges.
General, these outcomes recommend that the objective of the mortgage could also be an necessary threat indicator, as totally different financing wants can mirror various ranges of monetary stability and reimbursement capability.
Distribution by mortgage grade
This categorical variable represents the mortgage grade assigned to every borrower, sometimes primarily based on an evaluation of their credit score threat profile. The grades vary from A to G, the place A corresponds to the lowest-risk loans and G to the highest-risk loans.
On this dataset, greater than 80% of debtors are assigned grades A, B, or C, indicating that almost all of loans are thought of comparatively low threat. In distinction, grades D, E, F, and G correspond to debtors with greater credit score threat, and these classes account for a a lot smaller share of the observations.
The distribution of default charges throughout the grades exhibits a transparent sample: the default price will increase because the mortgage grade deteriorates. In different phrases, debtors with decrease credit score grades are inclined to exhibit greater chances of default.
This result’s according to the aim of the grading system itself, as mortgage grades are designed to summarize the borrower’s creditworthiness and related threat stage.
Distribution by Mortgage Quantity
This variable represents the mortgage quantity requested by the borrower. On this dataset, mortgage quantities vary from $500 to $35,000, which corresponds to comparatively small shopper loans.
The evaluation of default charges throughout the quartiles exhibits that the very best threat is concentrated amongst debtors within the higher vary of mortgage quantities, notably within the fourth quartile ($20,000–$35,000), the place default charges are greater.
Distribution by mortgage rate of interest (loan_int_rate)
This variable represents the rate of interest utilized to the mortgage granted to the borrower. On this dataset, rates of interest vary from 5% to 24%.
To investigate the connection between rates of interest and default threat, we group the observations into quartiles. The outcomes present that the very best default charges are concentrated within the higher vary of rates of interest, notably within the fourth quartile (roughly 13%–24%).
Distribution by mortgage % revenue
This variable measures the share of a borrower’s annual revenue allotted to mortgage reimbursement. It signifies the monetary burdenassociated with the mortgage relative to the borrower’s revenue.
The evaluation exhibits that the very best default charges are concentrated within the higher quartile, the place debtors allocate between 20% and 100% of their revenue to mortgage reimbursement.
Conclusion
On this evaluation, we’ve got described every of the 12 variables within the dataset. This exploratory step allowed us to construct a transparent understanding of the information and shortly summarize its key traits within the introduction.
Previously, this kind of evaluation was typically time-consuming and sometimes required the collaboration of a number of information scientists to carry out the statistical exploration and produce the ultimate reporting. Whereas the interpretations of various variables might typically seem repetitive, such detailed documentation is commonly required in regulated environments, notably in fields like credit score threat modeling.
In the present day, nevertheless, the rise of synthetic intelligence is remodeling this workflow. Duties that beforehand required a number of days of labor can now be accomplished in lower than half-hour, underneath the supervision of a statistician or information scientist. On this setting, the knowledgeable’s position shifts from manually performing the evaluation to guiding the method, validating the outcomes, and making certain their reliability.
In observe, it’s attainable to design two specialised AI brokers at this stage of the workflow. The primary agent assists with information preparation and dataset development, whereas the second performs the exploratory evaluation and generates the descriptive reporting introduced on this article.
A number of years in the past, it was already really helpful to automate these duties each time attainable. On this publish, the tables used all through the evaluation had been generated routinely utilizing the Python capabilities introduced on the finish of this text.
Within the subsequent article, we are going to take the evaluation a step additional by exploring variable therapy, detecting and dealing with outliers, analyzing relationships between variables, and performing an preliminary characteristic choice.
Picture Credit
All pictures and visualizations on this article had been created by the writer utilizing Python (pandas, matplotlib, seaborn, and plotly) and excel, except in any other case said.
References
[1] Lorenzo Beretta and Alessandro Santaniello.
Nearest Neighbor Imputation Algorithms: A Important Analysis.
Nationwide Library of Drugs, 2016.
[2] Nexialog Consulting.
Traitement des données manquantes dans le milieu bancaire.
Working paper, 2022.
[3] John T. Hancock and Taghi M. Khoshgoftaar.
Survey on Categorical Knowledge for Neural Networks.
Journal of Huge Knowledge, 7(28), 2020.
[4] Melissa J. Azur, Elizabeth A. Stuart, Constantine Frangakis, and Philip J. Leaf.
A number of Imputation by Chained Equations: What Is It and How Does It Work?
Worldwide Journal of Strategies in Psychiatric Analysis, 2011.
[5] Majid Sarmad.
Sturdy Knowledge Evaluation for Factorial Experimental Designs: Improved Strategies and Software program.
Division of Mathematical Sciences, College of Durham, England, 2006.
[6] Daniel J. Stekhoven and Peter Bühlmann.
MissForest—Non-Parametric Lacking Worth Imputation for Combined-Sort Knowledge.Bioinformatics, 2011.
[7] Supriyanto Wibisono, Anwar, and Amin.
Multivariate Climate Anomaly Detection Utilizing the DBSCAN Clustering Algorithm.
Journal of Physics: Convention Collection, 2021.
Knowledge & Licensing
The dataset used on this article is licensed underneath the Inventive Commons Attribution 4.0 Worldwide (CC BY 4.0) license.
This license permits anybody to share and adapt the dataset for any objective, together with industrial use, offered that correct attribution is given to the supply.
For extra particulars, see the official license textual content: CC0: Public Domain.
Disclaimer
Any remaining errors or inaccuracies are the writer’s accountability. Suggestions and corrections are welcome.
Codes
import pandas as pd
from typing import Optionally available, Union
def build_default_summary(
df: pd.DataFrame,
category_col: str,
default_col: str,
category_label: Optionally available[str] = None,
include_na: bool = False,
sort_by: str = "depend",
ascending: bool = False,
) -> pd.DataFrame:
"""
Construit un tableau de synthèse pour une variable catégorielle.
Paramètres
----------
df : pd.DataFrame
DataFrame supply.
category_col : str
Nom de la variable catégorielle.
default_col : str
Colonne binaire indiquant le défaut (0/1 ou booléen).
category_label : str, optionnel
Libellé à afficher pour la première colonne.
Par défaut : category_col.
include_na : bool, default=False
Si True, preserve les valeurs manquantes comme catégorie.
sort_by : str, default="depend"
Colonne de tri logique parmi {"depend", "defaults", "prop", "default_rate", "class"}.
ascending : bool, default=False
Sens du tri.
Retour
------
pd.DataFrame
Tableau prêt à exporter.
"""
if category_col not in df.columns:
elevate KeyError(f"La colonne catégorielle '{category_col}' est introuvable.")
if default_col not in df.columns:
elevate KeyError(f"La colonne défaut '{default_col}' est introuvable.")
information = df[[category_col, default_col]].copy()
# Validation minimale sur la cible
# On convertit bool -> int ; sinon on suppose 0/1 documenté
if pd.api.sorts.is_bool_dtype(information[default_col]):
information[default_col] = information[default_col].astype(int)
# Gestion des NA de la variable catégorielle
if include_na:
information[category_col] = information[category_col].astype("object").fillna("Lacking")
else:
information = information[data[category_col].notna()].copy()
grouped = (
information.groupby(category_col, dropna=False)[default_col]
.agg(depend="measurement", defaults="sum")
.reset_index()
)
total_obs = grouped["count"].sum()
total_def = grouped["defaults"].sum()
grouped["prop"] = grouped["count"] / total_obs if total_obs > 0 else 0.0
grouped["default_rate"] = grouped["defaults"] / grouped["count"]
sort_mapping = {
"depend": "depend",
"defaults": "defaults",
"prop": "prop",
"default_rate": "default_rate",
"class": category_col,
}
if sort_by not in sort_mapping:
elevate ValueError(
"sort_by doit être parmi {'depend', 'defaults', 'prop', 'default_rate', 'class'}."
)
grouped = grouped.sort_values(sort_mapping[sort_by], ascending=ascending).reset_index(drop=True)
total_row = pd.DataFrame(
{
category_col: ["Total"],
"depend": [total_obs],
"defaults": [total_def],
"prop": [1.0 if total_obs > 0 else 0.0],
"default_rate": [total_def / total_obs if total_obs > 0 else 0.0],
}
)
abstract = pd.concat([grouped, total_row], ignore_index=True)
abstract = abstract.rename(
columns={
category_col: category_label or category_col,
"depend": "Nb of obs",
"defaults": "Nb def",
"prop": "Prop",
"default_rate": "Default price",
}
)
abstract = abstract[[category_label or category_col, "Nb of obs", "Prop", "Nb def", "Default rate"]]
return abstract
def export_summary_to_excel(
abstract: pd.DataFrame,
output_path: str,
sheet_name: str = "Abstract",
title: str = "All perimeters",
) -> None:
"""
Exporte le tableau de synthèse dans un fichier Excel avec mise en forme.
Nécessite le moteur xlsxwriter.
"""
with pd.ExcelWriter(output_path, engine="xlsxwriter") as author:
#
workbook = author.e-book
worksheet = workbook.add_worksheet(sheet_name)
nrows, ncols = abstract.form
total_excel_row = 2 + nrows # +1 implicite Excel automotive index 0-based côté xlsxwriter pour set_row
# Détail :
# ligne 0 : titre fusionné
# ligne 2 : header
# données commencent ligne 3 (Excel visuel), mais xlsxwriter manipule en base 0
# -------- Codecs --------
border_color = "#4F4F4F"
header_bg = "#D9EAF7"
title_bg = "#CFE2F3"
total_bg = "#D9D9D9"
white_bg = "#FFFFFF"
title_fmt = workbook.add_format({
"daring": True,
"align": "heart",
"valign": "vcenter",
"font_size": 14,
"border": 1,
"bg_color": title_bg,
})
header_fmt = workbook.add_format({
"daring": True,
"align": "heart",
"valign": "vcenter",
"border": 1,
"bg_color": header_bg,
})
text_fmt = workbook.add_format({
"border": 1,
"align": "left",
"valign": "vcenter",
"bg_color": white_bg,
})
int_fmt = workbook.add_format({
"border": 1,
"align": "heart",
"valign": "vcenter",
"num_format": "# ##0",
"bg_color": white_bg,
})
pct_fmt = workbook.add_format({
"border": 1,
"align": "heart",
"valign": "vcenter",
"num_format": "0.00%",
"bg_color": white_bg,
})
total_text_fmt = workbook.add_format({
"daring": True,
"border": 1,
"align": "heart",
"valign": "vcenter",
"bg_color": total_bg,
})
total_int_fmt = workbook.add_format({
"daring": True,
"border": 1,
"align": "heart",
"valign": "vcenter",
"num_format": "# ##0",
"bg_color": total_bg,
})
total_pct_fmt = workbook.add_format({
"daring": True,
"border": 1,
"align": "heart",
"valign": "vcenter",
"num_format": "0.00%",
"bg_color": total_bg,
})
# -------- Titre fusionné --------
worksheet.merge_range(0, 0, 0, ncols - 1, title, title_fmt)
# -------- Header --------
worksheet.set_row(2, 28)
for col_idx, col_name in enumerate(abstract.columns):
worksheet.write(1, col_idx, col_name, header_fmt)
# -------- Largeurs de colonnes --------
column_widths = {
0: 24, # catégorie
1: 14, # Nb of obs
2: 12, # Nb def
3: 10, # Prop
4: 14, # Default price
}
for col_idx in vary(ncols):
worksheet.set_column(col_idx, col_idx, column_widths.get(col_idx, 15))
# -------- Mise en forme cellule par cellule --------
last_row_idx = nrows - 1
for row_idx in vary(nrows):
excel_row = 2 + row_idx # données à partir de la ligne 3 (0-based xlsxwriter)
is_total = row_idx == last_row_idx
for col_idx, col_name in enumerate(abstract.columns):
worth = abstract.iloc[row_idx, col_idx]
if col_idx == 0:
fmt = total_text_fmt if is_total else text_fmt
elif col_name in ["Nb of obs", "Nb def"]:
fmt = total_int_fmt if is_total else int_fmt
elif col_name in ["Prop", "Default rate"]:
fmt = total_pct_fmt if is_total else pct_fmt
else:
fmt = total_text_fmt if is_total else text_fmt
worksheet.write(excel_row, col_idx, worth, fmt)
# Optionnel : figer le header
#worksheet.freeze_panes(3, 1)
worksheet.set_default_row(24)
def generate_categorical_report_excel(
df: pd.DataFrame,
category_col: str,
default_col: str,
output_path: str,
sheet_name: str = "Abstract",
title: str = "All perimeters",
category_label: Optionally available[str] = None,
include_na: bool = False,
sort_by: str = "depend",
ascending: bool = False,
) -> pd.DataFrame:
"""
1. calcule le tableau
2. l'exporte vers Excel
3. renvoie aussi le DataFrame récapitulatif
"""
abstract = build_default_summary(
df=df,
category_col=category_col,
default_col=default_col,
category_label=category_label,
include_na=include_na,
sort_by=sort_by,
ascending=ascending,
)
export_summary_to_excel(
abstract=abstract,
output_path=output_path,
sheet_name=sheet_name,
title=title,
)
return abstract
def discretize_variable_by_quartiles(
df: pd.DataFrame,
variable: str,
new_var: str | None = None
) -> pd.DataFrame:
"""
Discretize a steady variable into 4 intervals primarily based on its quartiles.
The operate computes Q1, Q2 (median), and Q3 of the chosen variable and
creates 4 bins akin to the next intervals:
]min ; Q1], ]Q1 ; Q2], ]Q2 ; Q3], ]Q3 ; max]
Parameters
----------
df : pd.DataFrame
Enter dataframe containing the variable to discretize.
variable : str
Title of the continual variable to be discretized.
new_var : str, elective
Title of the brand new categorical variable created. If None,
the identify "<variable>_quartile" is used.
Returns
-------
pd.DataFrame
A duplicate of the dataframe with the brand new quartile-based categorical variable.
"""
# Create a duplicate of the dataframe to keep away from modifying the unique dataset
information = df.copy()
# If no identify is offered for the brand new variable, create one routinely
if new_var is None:
new_var = f"{variable}_quartile"
# Compute the quartiles of the variable
q1, q2, q3 = information[variable].quantile([0.25, 0.50, 0.75])
# Retrieve the minimal and most values of the variable
vmin = information[variable].min()
vmax = information[variable].max()
# Outline the bin edges
# A small epsilon is subtracted from the minimal worth to make sure it's included
bins = [vmin - 1e-9, q1, q2, q3, vmax]
# Outline human-readable labels for every interval
labels = [
f"]{vmin:.2f} ; {q1:.2f}]",
f"]{q1:.2f} ; {q2:.2f}]",
f"]{q2:.2f} ; {q3:.2f}]",
f"]{q3:.2f} ; {vmax:.2f}]",
]
# Use pandas.reduce to assign every commentary to a quartile-based interval
information[new_var] = pd.reduce(
information[variable],
bins=bins,
labels=labels,
include_lowest=True
)
# Return the dataframe with the brand new discretized variable
return informationInstance of software for a steady variable
# Distribution by age (person_age)
# Discretize the variable into quartiles
df_with_age_bins = create_quartile_bins(
df,
variable="person_age",
new_var="age_quartile"
)
abstract = generate_categorical_report_excel(
df=df_with_age_bins,
category_col="age_quartile",
default_col="def",
output_path="age_quartile_report.xlsx",
sheet_name="Age Quartiles",
title="Distribution by Age (Quartiles)",
category_label="Age Quartiles",
sort_by="default_rate",
ascending=False
)
