mission, it’s typically tempting to leap to modeling. But step one and crucial one is to know the info.
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 varieties 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 incorporates 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, goal 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 client is in default and 0 in any other case.
At the moment, many instruments and an growing variety of AI brokers are able to robotically producing statistical descriptions of datasets. Nonetheless, performing this evaluation manually stays a wonderful train for rookies. It builds a deeper understanding of the info 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 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 could possibly be changed by different discretization strategies. The objective 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 client isn’t in default, and 1 if the client 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 research could be a temporal one. It will permit us to review how the default charge evolves over time, confirm its stationarity, and assess its stability and its predictability.
Sadly, the dataset incorporates no temporal info. We have no idea when every commentary 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 atmosphere. 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 durations.
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 in opposition to 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 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 purchasers within the dataset have comparatively brief credit score histories.
Second, the default charge seems pretty secure throughout intervals, hovering round 21%. That stated, 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 supplies helpful details about the previous credit score conduct of the consumer.
It has two potential values:
- Y: the borrower has defaulted prior to now
- 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 purchasers 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 charge of about 38%, in contrast with round 18% for debtors who’ve by no means defaulted.
This result’s in step with what is often noticed in credit score threat modeling: previous reimbursement 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 purchasers) 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 below 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 seem 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 research its relationship with default threat, we divide earnings 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 earnings will increase, the default charge regularly 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 earnings and default threat, which is in step with normal credit score threat expectations: debtors with larger incomes usually have larger reimbursement capability and monetary stability, making them much less prone to default.
Distribution by Dwelling Possession
This variable describes the borrower’s housing standing. The classes embrace RENT (tenant), MORTGAGE (house owner with a mortgage), OWN (house owner and not using 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 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, owners and not using 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 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 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 larger earnings 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 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 larger share of loans used for training (EDUCATION) and medical bills (MEDICAL).
Nevertheless, the evaluation reveals notable variations in threat throughout classes. Debtors searching for loans for debt consolidation (DEBTCONSOLIDATION) and medical functions (MEDICAL) exhibit larger 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 goal of the mortgage could also be an essential 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, usually 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 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 charge will increase because the mortgage grade deteriorates. In different phrases, debtors with decrease credit score grades are inclined to exhibit larger chances of default.
This result’s in step 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 client 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 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 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 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 very 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 now have described every of the 12 variables within the dataset. This exploratory step allowed us to construct a transparent understanding of the info and shortly summarize its key traits within the introduction.
Previously, this sort of evaluation was typically time-consuming and usually 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 might typically seem repetitive, such detailed documentation is usually required in regulated environments, notably in fields like credit score threat modeling.
At the 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, below the supervision of a statistician or knowledge scientist. On this setting, the professional’s function shifts from manually performing the evaluation to guiding the method, validating the outcomes, and guaranteeing their reliability.
In apply, it’s potential to design two specialised AI brokers at this stage of the workflow. The primary agent assists with knowledge 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 beneficial to automate these duties every time potential. On this publish, the tables used all through the evaluation had been generated robotically utilizing the Python features 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 creator utilizing Python (pandas, matplotlib, seaborn, and plotly) and excel, until in any other case acknowledged.
References
[1] Lorenzo Beretta and Alessandro Santaniello.
Nearest Neighbor Imputation Algorithms: A Crucial 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 Information for Neural Networks.
Journal of Large 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 Collection, 2021.
Information & Licensing
The dataset used on this article is licensed below the Artistic 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 industrial use, offered that correct attribution is given to the supply.
For extra particulars, see the official license textual content: CC0: Public Area.
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-obligatory, Union
def build_default_summary(
df: pd.DataFrame,
category_col: str,
default_col: str,
category_label: Non-obligatory[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.")
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.varieties.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(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 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.guide
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 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: Non-obligatory[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 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
Identify of the continual variable to be discretized.
new_var : str, elective
Identify 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 offered for the brand new variable, create one robotically
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.reduce to assign every commentary to a quartile-based interval
knowledge[new_var] = pd.reduce(
knowledge[variable],
bins=bins,
labels=labels,
include_lowest=True
)
# Return the dataframe with the brand new discretized variable
return knowledge Instance 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
)
