venture, it’s usually tempting to leap to modeling. But step one and a very powerful one is to know the information.
In our earlier post, 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 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 obtainable 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, goal 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 client is in default and 0 in any other case.
Right this moment, 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 wonderful train for newbies. 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 charge 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 might be changed by different discretization strategies. The aim is just to get an preliminary learn on how threat behaves throughout the completely 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 compensation default. It takes two values: 0 if the client shouldn’t be in default, and 1 if the client is in default.
Over 78% of shoppers haven’t defaulted. The dataset is imbalanced, and you will need to account for this imbalance throughout modeling.
The following related variable to investigate could be a temporal one. It might enable us to review how the default charge 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 statement was recorded, which makes it not possible 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 conduct can fluctuate considerably relying on the macroeconomic setting. For example, throughout monetary crises — such because the 2008 subprime disaster or the COVID-19 pandemic — default charges usually 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. Specifically, it prevents us from learning how threat dynamics evolve over time and from evaluating the potential robustness of a mannequin towards financial cycles.
We do, nonetheless, 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 are going to deal with it as a steady variable and discretize it utilizing quantiles.
A number of observations will 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 charge seems pretty secure throughout intervals, hovering round 21%. That mentioned, debtors with shorter credit score histories are inclined to exhibit barely riskier conduct 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 useful details about the previous credit score conduct 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 haven’t any historical past of default, suggesting that almost all of shoppers have maintained a passable compensation file.
Nevertheless, a transparent distinction in threat emerges between the 2 teams. Debtors with a earlier default historical past are considerably riskier, with a default charge of about 38%, in contrast with round 18% for debtors who’ve by no means defaulted.
This result’s per what is usually noticed in credit score threat modeling: previous compensation conduct 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) reasonably than company entities. To raised 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 section on this dataset.
Distribution by Annual Earnings
Debtors’ annual revenue on this dataset ranges from $4,000 to $6,000,000. To research its relationship with default threat, we divide revenue 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 revenue will increase, the default charge progressively 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 per normal credit score threat expectations: debtors with larger incomes usually have better compensation capability and monetary stability, making them much less more likely to default.
Distribution by Residence Possession
This variable describes the borrower’s housing standing. The classes embody 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 owners with a mortgage, 8% personal their residence outright, and about 2% fall into the “OTHER” class.
The evaluation reveals that the best default charges are noticed amongst renters (RENT) and debtors categorised as “OTHER.” In distinction, owners 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 research 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 reveals 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 charge 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 better revenue stability and monetary safety, which reduces their probability of default.
Distribution by mortgage intent
This categorical variable describes the goal 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 completely different mortgage functions, with a barely larger share of loans used for schooling (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 schooling (EDUCATION) and entrepreneurial actions (VENTURE) are related to decrease default charges.
General, these outcomes recommend that the goal of the mortgage could also be an essential threat indicator, as completely different financing wants can replicate various ranges of economic stability and compensation capability.
Distribution by mortgage grade
This categorical variable represents the mortgage grade assigned to every borrower, usually 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 reveals a transparent sample: the default charge will increase because the mortgage grade deteriorates. In different phrases, debtors with decrease credit score grades are inclined to exhibit larger possibilities of default.
This result’s per the aim of the grading system itself, as mortgage grades are designed to summarize the borrower’s creditworthiness and related threat degree.
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 reveals 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 research 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 % revenue
This variable measures the proportion of a borrower’s annual revenue allotted to mortgage compensation. It signifies the monetary burdenassociated with the mortgage relative to the borrower’s revenue.
The evaluation reveals that the best default charges are concentrated within the higher quartile, the place debtors allocate between 20% and 100% of their revenue to mortgage compensation.
Conclusion
On this evaluation, we have now described every of the 12 variables within the dataset. This exploratory step allowed us to construct a transparent understanding of the information and rapidly summarize its key traits within the introduction.
Previously, this sort of evaluation was usually time-consuming and usually 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 generally seem repetitive, such detailed documentation is usually required in regulated environments, significantly in fields like credit score threat modeling.
Right this moment, nonetheless, 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 information scientist. On this setting, the knowledgeable’s function shifts from manually performing the evaluation to guiding the method, validating the outcomes, and making certain their reliability.
In follow, it’s doable to design two specialised AI brokers at this stage of the workflow. The primary agent assists with information preparation and dataset building, 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 at any time when doable. 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’ll take the evaluation a step additional by exploring variable remedy, detecting and dealing with outliers, analyzing relationships between variables, and performing an preliminary characteristic choice.
Picture Credit
All photos 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 acknowledged.
References
[1] Lorenzo Beretta and Alessandro Santaniello.
Nearest Neighbor Imputation Algorithms: A Essential 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 Blended-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 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 goal, together with business 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 duty. 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:
increase KeyError(f"La colonne catégorielle '{category_col}' est introuvable.")
if default_col not in df.columns:
increase 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="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 = {
"depend": "depend",
"defaults": "defaults",
"prop": "prop",
"default_rate": "default_rate",
"class": category_col,
}
if sort_by not in sort_mapping:
increase 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 charge",
}
)
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.ebook
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 charge
}
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 based mostly on its quartiles.
The perform computes Q1, Q2 (median), and Q3 of the chosen variable and
creates 4 bins equivalent 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-compulsory
Title of the brand new categorical variable created. If None,
the title "<variable>_quartile" is used.
Returns
-------
pd.DataFrame
A replica of the dataframe with the brand new quartile-based categorical variable.
"""
# Create a replica of the dataframe to keep away from modifying the unique dataset
information = df.copy()
# If no title 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 statement 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 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
)