venture, it’s usually tempting to leap to modeling. But step one and an important 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 elucidate 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, 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.
At the moment, many instruments and an growing variety of AI brokers are able to mechanically producing statistical descriptions of datasets. Nonetheless, performing this evaluation manually stays a wonderful train for novices. It builds a deeper understanding of the info construction, helps spotlight potential anomalies, and helps the identification of variables that could 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 could possibly be changed by different discretization strategies. The purpose 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 client shouldn’t be in default, and 1 if the client 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 will enable us to review how the default price evolves over time, confirm its stationarity, and assess its stability and its predictability.
Sadly, the dataset comprises no temporal data. We have no idea when every statement was recorded, which makes it inconceivable to find out whether or not the loans had been issued throughout a interval of financial stability or throughout a downturn.
This data is nonetheless important in credit score threat modeling. Borrower conduct can differ considerably relying on the macroeconomic setting. As an 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. 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 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 purchasers within the dataset have comparatively brief credit score histories.
Second, the default price seems pretty steady 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 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 due to this fact supplies worthwhile details about the previous credit score conduct of the shopper.
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 don’t have any historical past of default, suggesting that almost all of purchasers have maintained a passable reimbursement report.
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 per 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) somewhat than company entities. To higher analyze this variable, we group debtors into age intervals based mostly on quartiles.
The dataset consists of 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 look like the riskiest section on this dataset.
Distribution by Annual Revenue
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 very 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 price 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 customary credit score threat expectations: debtors with greater incomes usually have higher reimbursement capability and monetary stability, making them much less prone to default.
Distribution by House 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 owners with a mortgage, 8% personal their residence 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 with out a 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 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 very 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 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 chance 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 greater share of loans used for training (EDUCATION) and medical bills (MEDICAL).
Nevertheless, the evaluation reveals notable variations in threat throughout classes. Debtors looking for loans for debt consolidation (DEBTCONSOLIDATION) and medical functions (MEDICAL) exhibit greater default charges. In distinction, loans supposed for training (EDUCATION) and entrepreneurial actions (VENTURE) are related to decrease default charges.
General, these outcomes counsel that the goal of the mortgage could also be an essential 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, 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-about 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 reveals 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 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 very 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 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 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, 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 reimbursement. It signifies the monetary burdenassociated with the mortgage relative to the borrower’s revenue.
The evaluation reveals 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 have now 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.
Up to now, such a 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 typically seem repetitive, such detailed documentation is usually required in regulated environments, significantly in fields like credit score threat modeling.
At the moment, 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, below 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 potential 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 every time potential. On this publish, the tables used all through the evaluation had been generated mechanically 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 photos 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 Essential 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 Knowledge for Neural Networks.
Journal of Massive 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.
Strong 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-Kind 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 below 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, supplied 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-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 = "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.varieties.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: Non-compulsory[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 operate computes Q1, Q2 (median), and Q3 of the chosen variable and
creates 4 bins similar 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
information = 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 = 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.lower to assign every statement to a quartile-based interval
information[new_var] = pd.lower(
information[variable],
bins=bins,
labels=labels,
include_lowest=True
)
# Return the dataframe with the brand new discretized variable
return information 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
)
