challenge, it’s typically tempting to leap to modeling. But step one and a very powerful one is to grasp the information.
In our earlier post, we offered 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 sorts 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 comprises 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, academic, {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, earnings, 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.
Right now, many instruments and an rising variety of AI brokers are able to mechanically producing statistical descriptions of datasets. Nonetheless, performing this evaluation manually stays a superb train for newcomers. 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 method to statistically describing every variable within the dataset.
- For categorical variables, we analyze the variety of observations and the default fee 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 unfair and might be changed by different discretization strategies. The aim is just 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 isn’t in default, and 1 if the shopper is in default.
Over 78% of consumers haven’t defaulted. The dataset is imbalanced, and it is very important account for this imbalance throughout modeling.
The following related variable to research could be a temporal one. It could enable us to review how the default fee evolves over time, confirm its stationarity, and assess its stability and its predictability.
Sadly, the dataset comprises no temporal info. We have no idea when every remark was recorded, which makes it unattainable to find out whether or not the loans have 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 differ 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 intervals.
The absence of a temporal dimension on this dataset due to this fact limits the scope of our evaluation. Particularly, it prevents us from learning 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 might 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 brief credit score histories.
Second, the default fee seems pretty steady throughout intervals, hovering round 21%. That stated, debtors with shorter credit score histories are likely to exhibit barely riskier habits than these with longer ones, as mirrored of their larger 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 due to this fact gives beneficial details about the previous credit score habits of the shopper.
It has two doable values:
- Y: the borrower has defaulted previously
- N: the borrower has by no means defaulted
On this dataset, greater than 80% of debtors don’t have 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 fee of about 38%, in contrast with round 18% for debtors who’ve by no means defaulted.
This result’s in line with what is usually noticed in credit score threat modeling: previous reimbursement habits is usually 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) fairly than company entities. To higher analyze this variable, we group debtors into age intervals based mostly 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 beneath 30 years outdated.
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 Revenue
Debtors’ annual earnings on this dataset ranges from $4,000 to $6,000,000. To investigate its relationship with default threat, we divide earnings into 4 intervals based mostly on quartiles.
The outcomes present that the best default charges are concentrated amongst debtors with the bottom incomes, significantly within the first quartile ($4,000–$385,00) and the second quartile ($385,00–$55,000).
As earnings will increase, the default fee regularly decreases. Debtors within the third quartile ($55,000–$792,000) and the fourth quartile ($792,000–$600,000) exhibit noticeably decrease default charges.
Total, this sample suggests an inverse relationship between annual earnings and default threat, which is in line with customary credit score threat expectations: debtors with larger incomes sometimes have larger reimbursement capability and monetary stability, making them much less prone to default.
Distribution by Residence Possession
This variable describes the borrower’s housing standing. The classes embody RENT (tenant), MORTGAGE (house owner with a mortgage), OWN (house owner with no mortgage), and OTHER (different housing preparations).
On this dataset, roughly 50% of debtors are renters, 40% are owners with a mortgage, 8% personal their house outright, and about 2% fall into the “OTHER” class.
The evaluation reveals that the best default charges are noticed amongst renters (RENT) and debtors labeled as “OTHER.” In distinction, owners with no mortgage (OWN) exhibit the bottom default charges, adopted by debtors with a mortgage (MORTGAGE).
Distributionby particular person 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 based mostly 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 best default charges are concentrated amongst debtors with the shortest employment histories, significantly these within the first quartile (0–2 years) and the second quartile (2–4 years).
As employment size will increase, the default fee tends to say no. Debtors within the third quartile (4–7 years) and the fourth quartile (7 years or extra) exhibit decrease default charges.
Total, this sample suggests an inverse relationship between employment size and default threat, indicating that debtors with longer employment histories could profit from larger earnings stability and monetary safety, which reduces their chance of default.
Distribution by mortgage intent
This categorical variable describes the objective of the mortgage requested by the borrower. The classes embody EDUCATION, MEDICAL, VENTURE (entrepreneurship), PERSONAL, DEBTCONSOLIDATION, and HOMEIMPROVEMENT.
The variety of debtors is pretty balanced throughout the totally different mortgage functions, with a barely larger 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 larger default charges. In distinction, loans supposed for training (EDUCATION) and entrepreneurial actions (VENTURE) are related to decrease default charges.
Total, 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 economic stability and reimbursement capability.
Distribution by mortgage grade
This categorical variable represents the mortgage grade assigned to every borrower, sometimes based mostly 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 larger 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 fee will increase because the mortgage grade deteriorates. In different phrases, debtors with decrease credit score grades are likely to exhibit larger chances of default.
This result’s in line with 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 best threat is concentrated amongst debtors within the higher vary of mortgage quantities, significantly within the fourth quartile ($20,000–$35,000), the place default charges are larger.
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 best default charges are concentrated within the higher vary of rates of interest, significantly within the fourth quartile (roughly 13%–24%).
Distribution by mortgage p.c earnings
This variable measures the share of a borrower’s annual earnings allotted to mortgage reimbursement. It signifies the monetary burdenassociated with the mortgage relative to the borrower’s earnings.
The evaluation exhibits that the best default charges are concentrated within the higher quartile, the place debtors allocate between 20% and 100% of their earnings 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.
Up to now, one of these evaluation was typically time-consuming and sometimes required the collaboration of a number of knowledge scientists to carry out the statistical exploration and produce the ultimate reporting. Whereas the interpretations of various variables could generally seem repetitive, such detailed documentation is usually required in regulated environments, significantly in fields like credit score threat modeling.
Right now, nevertheless, the rise of synthetic intelligence is reworking this workflow. Duties that beforehand required a number of days of labor can now be accomplished in lower than half-hour, beneath the supervision of a statistician or knowledge scientist. On this setting, the professional’s position shifts from manually performing the evaluation to guiding the method, validating the outcomes, and guaranteeing their reliability.
In observe, it’s doable to design two specialised AI brokers at this stage of the workflow. The primary agent assists with knowledge preparation and dataset building, whereas the second performs the exploratory evaluation and generates the descriptive reporting offered on this article.
A number of years in the past, it was already really useful to automate these duties every time doable. On this publish, the tables used all through the evaluation have been generated mechanically utilizing the Python capabilities offered on the finish of this text.
Within the subsequent article, we are going to take the evaluation a step additional by exploring variable remedy, detecting and dealing with outliers, analyzing relationships between variables, and performing an preliminary function choice.
Picture Credit
All photographs and visualizations on this article have been created by the creator utilizing Python (pandas, matplotlib, seaborn, and plotly) and excel, except in any other case acknowledged.
References
[1] Lorenzo Beretta and Alessandro Santaniello.
Nearest Neighbor Imputation Algorithms: A Vital Analysis.
Nationwide Library of Medication, 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 Information for Neural Networks.
Journal of Huge Information, 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.
Strong Information 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 Blended-Sort Information.Bioinformatics, 2011.
[7] Supriyanto Wibisono, Anwar, and Amin.
Multivariate Climate Anomaly Detection Utilizing the DBSCAN Clustering Algorithm.
Journal of Physics: Convention Sequence, 2021.
Information & Licensing
The dataset used on this article is licensed beneath 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 business use, supplied 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 creator’s duty. Suggestions and corrections are welcome.
Codes
import pandas as pd
from typing import Non-compulsory, Union
def build_default_summary(
df: pd.DataFrame,
category_col: str,
default_col: str,
category_label: Non-compulsory[str] = None,
include_na: bool = False,
sort_by: str = "rely",
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="rely"
Colonne de tri logique parmi {"rely", "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.")
knowledge = 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(knowledge[default_col]):
knowledge[default_col] = knowledge[default_col].astype(int)
# Gestion des NA de la variable catégorielle
if include_na:
knowledge[category_col] = knowledge[category_col].astype("object").fillna("Lacking")
else:
knowledge = knowledge[data[category_col].notna()].copy()
grouped = (
knowledge.groupby(category_col, dropna=False)[default_col]
.agg(rely="dimension", 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 = {
"rely": "rely",
"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 {'rely', '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"],
"rely": [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,
"rely": "Nb of obs",
"defaults": "Nb def",
"prop": "Prop",
"default_rate": "Default fee",
}
)
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": "middle",
"valign": "vcenter",
"font_size": 14,
"border": 1,
"bg_color": title_bg,
})
header_fmt = workbook.add_format({
"daring": True,
"align": "middle",
"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": "middle",
"valign": "vcenter",
"num_format": "# ##0",
"bg_color": white_bg,
})
pct_fmt = workbook.add_format({
"border": 1,
"align": "middle",
"valign": "vcenter",
"num_format": "0.00%",
"bg_color": white_bg,
})
total_text_fmt = workbook.add_format({
"daring": True,
"border": 1,
"align": "middle",
"valign": "vcenter",
"bg_color": total_bg,
})
total_int_fmt = workbook.add_format({
"daring": True,
"border": 1,
"align": "middle",
"valign": "vcenter",
"num_format": "# ##0",
"bg_color": total_bg,
})
total_pct_fmt = workbook.add_format({
"daring": True,
"border": 1,
"align": "middle",
"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 fee
}
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: Non-compulsory[str] = None,
include_na: bool = False,
sort_by: str = "rely",
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 based mostly on its quartiles.
The perform computes Q1, Q2 (median), and Q3 of the chosen variable and
creates 4 bins comparable 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, non-obligatory
Title of the brand new categorical variable created. If None,
the title "_quartile" is used.
Returns
-------
pd.DataFrame
A replica 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
knowledge = df.copy()
# If no title is supplied for the brand new variable, create one mechanically
if new_var is None:
new_var = f"{variable}_quartile"
# Compute the quartiles of the variable
q1, q2, q3 = knowledge[variable].quantile([0.25, 0.50, 0.75])
# Retrieve the minimal and most values of the variable
vmin = knowledge[variable].min()
vmax = knowledge[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.minimize to assign every remark to a quartile-based interval
knowledge[new_var] = pd.minimize(
knowledge[variable],
bins=bins,
labels=labels,
include_lowest=True
)
# Return the dataframe with the brand new discretized variable
return knowledge Instance of utility 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
)