ESG Risk Signals in Credit Prediction

Build a credit risk model that quantifies how ESG scores predict financial distress.

Introduction

30 Second Summary

When a company dumps toxic waste or mistreats its workforce, those headlines often foreshadow something deeper: financial trouble. Banks and investors increasingly look at ESG scores (Environmental, Social, and Governance ratings) as early warning signals that a company might default on its debts.

In this project, you will build a data analysis notebook that investigates whether companies with poor ESG performance face higher credit risk. You will load financial and ESG data into a database, write analytical SQL queries, engineer credit risk features, and train machine learning models that quantify the predictive power of ESG scores.

What You'll Build

A portfolio-ready Jupyter Notebook that combines SQL analytics, logistic regression modeling, and professional visualizations to answer the question: do ESG scores predict financial distress?

By the end of this project, you'll have:

  • A SQLite database loaded with structured ESG and financial data, queried using CTEs, window functions, and risk classification logic.
  • A logistic regression model comparing baseline (financial-only) predictions against an ESG-enhanced model, with quantified ROC-AUC improvement.
  • Professional visualizations including correlation heatmaps, ROC curves, sector analysis charts, and feature importance plots.
  • Secret Mission: a sector-level stress testing simulation that models how ESG score deterioration impacts portfolio-wide default probability.

Are there any prerequisites?

You should have basic Python knowledge (variables, functions, loops) and general familiarity with data analysis concepts. No prior experience with SQL, machine learning, or ESG data is required.

Set Up Your Environment and Load the Data

Your project folder is ready and your libraries are installed. Now you need the raw data that powers this entire analysis.

In this step, you will download a real-world-structured ESG dataset from Kaggle, create your analysis notebook, and verify the data loads cleanly into a pandas DataFrame.

In this step, get ready to:
  • Download the ESG and Financial Performance Dataset from Kaggle.
  • Create a new Jupyter Notebook for your analysis.
  • Write and run your first code cell to import libraries and load the data.
Download the dataset from Kaggle

The dataset you need simulates the financial and ESG performance of 1,000 global companies across 9 industries and 7 regions. It mirrors the structure of real ESG data you would find in tools like Sustainalytics.

  • Open your browser and navigate to the dataset page:
  • https://www.kaggle.com/datasets/shriyashjagtap/esg-and-financial-performance-dataset
  • Sign in to your Kaggle account. If you do not have one, click Sign Up and create a free account using Google or email.
  • Click the Download button on the dataset page. This downloads a ZIP file.
  • Extract the ZIP file. Inside you will find a CSV file.
  • Rename the CSV file to esg_financial_performance.csv and move it into the data/ subfolder inside your esg-credit-risk project folder.
  • Confirm the file is in place by running:
ls data/

You should see esg_financial_performance.csv listed in the output.

Don't see the CSV file?

Make sure you extracted the ZIP file before moving it. Kaggle downloads arrive as .zip archives containing the actual CSV inside.

Double-check that you moved the file into esg-credit-risk/data/ and not into a parent folder.

Help me find my downloaded Kaggle CSV file

Create the notebook and write your first cell

With the data in place, you need a Jupyter Notebook to hold your analysis code, visualizations, and narrative. Each section of your analysis will live in a separate notebook cell.

  • Switch back to the Jupyter interface in your browser.
  • Click New in the top right corner.
  • Select Python 3 from the dropdown to create a new notebook.
  • Click the notebook title at the top (it will say Untitled) and rename it to esg_credit_risk_analysis.

Your notebook is ready. Now add the imports and data loading code.

  • In the first cell of your notebook, paste the following code:
# ESG Risk Signals in Credit Default Prediction
# Portfolio Project - ESG-Integrated Credit Risk Analysis

# --- Cell 1: Imports and Data Loading ---
import pandas as pd
import numpy as np
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score, classification_report, roc_curve
from sklearn.preprocessing import StandardScaler
from sklearn.impute import SimpleImputer

# Load dataset
df = pd.read_csv('data/esg_financial_performance.csv')
print(f"Dataset shape: {df.shape}")
print(df.info())
print(df.head())

What does this code do?

  • The import block loads all libraries you will use throughout the project: pandas for data manipulation, numpy for numerical operations, sqlite3 for SQL queries, matplotlib and seaborn for visualization, and scikit-learn classes for modeling.
  • Importing everything upfront in one cell is standard practice for analysis notebooks. It makes dependencies visible at a glance.
  • The pd.read_csv() call loads the Kaggle dataset into a DataFrame called df.
  • The three print statements verify the data loaded correctly by showing the row/column count, data types, and a preview of the first five rows.
  • Run the cell by pressing Shift+Enter.

You should see output showing a dataset shape of approximately 1,000 rows and 16 columns, followed by column data types and a preview of the first five rows.

What columns should I see?

The dataset contains these 16 columns: CompanyID, CompanyName, Industry, Region, Year, Revenue, ProfitMargin, MarketCap, GrowthRate, ESG (overall score), ESG Environmental, ESG Social, ESG Governance, CarbonEmissions, WaterUsage, and EnergyConsumption.

The ESG columns are scored on a 0-100 scale. Financial columns like Revenue and MarketCap are numeric floats. The Year column spans 2015 to 2025.

Seeing a FileNotFoundError?

This means Python cannot find the CSV at the path data/esg_financial_performance.csv. Check two things:

  • Your Jupyter Notebook must be running from inside the esg-credit-risk folder. If you launched Jupyter from a different directory, the relative path will not resolve.
  • Make sure the CSV filename matches exactly: esg_financial_performance.csv (underscores, not hyphens).

Help me fix my FileNotFoundError when loading the CSV

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook.

ⓧ I'd like to double check the full code

Here is the complete code for Cell 1. Make sure your cell matches this exactly:

# ESG Risk Signals in Credit Default Prediction
# Portfolio Project - ESG-Integrated Credit Risk Analysis

# --- Cell 1: Imports and Data Loading ---
import pandas as pd
import numpy as np
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score, classification_report, roc_curve
from sklearn.preprocessing import StandardScaler
from sklearn.impute import SimpleImputer

# Load dataset
df = pd.read_csv('data/esg_financial_performance.csv')
print(f"Dataset shape: {df.shape}")
print(df.info())
print(df.head())

Your data is loaded and verified. Next up, you will load this data into a SQLite database and write analytical SQL queries that slice through the ESG-financial relationship from multiple angles.

Load Data into SQLite and Write Analytical Queries

Your data is loaded into Python, but credit risk analysts at financial institutions work primarily with structured databases.

In this step, you'll load your ESG data into a SQLite database and write five analytical queries that demonstrate CTEs, window functions, and risk classification logic.

In this step, get ready to:
  • Load the ESG dataset into a SQLite database.
  • Write CTE and window function queries to identify ESG risk outliers.
  • Create a risk tier classification system and measure distress rates across tiers.
Create the SQLite database and identify ESG risk outliers

SQL queries let you demonstrate structured data manipulation at the level financial institutions expect.

You'll start by loading your DataFrame into SQLite, then write two queries that use CTEs and window functions to surface ESG risk outliers.

  • In your notebook, create a new cell below Cell 1.
  • Add the following code to create the SQLite database and run the first analytical query:
# --- Cell 2: Load into SQLite ---
conn = sqlite3.connect('data/esg_credit_risk.db')
df.to_sql('companies_esg', conn, if_exists='replace', index=False)

# Query 1: ESG percentile rank within industry and year (CTE + Window Function)
query_percentile = """
WITH esg_ranked AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        PERCENT_RANK() OVER (PARTITION BY Industry, Year ORDER BY ESG) AS esg_percentile
    FROM companies_esg
)
SELECT *
FROM esg_ranked
WHERE esg_percentile <= 0.25
ORDER BY Industry, Year, esg_percentile
LIMIT 20;
"""
bottom_quartile = pd.read_sql_query(query_percentile, conn)
print("Bottom ESG Quartile (High Risk):")
print(bottom_quartile)

What does this code do?

The first two lines create a SQLite database file and load the entire DataFrame into a table called companies_esg. This gives you a real database to query with SQL.

Query 1 uses a CTE (Common Table Expression) named esg_ranked to calculate each company's ESG percentile rank within its industry and year. The outer query then filters to the bottom 25%, identifying the companies with the worst ESG performance relative to their peers.

What is a CTE?

A CTE (Common Table Expression) is a temporary named result set defined with the WITH keyword. It makes complex queries readable by breaking them into logical steps. Banks use CTEs extensively in risk reporting queries.

  • Run the cell.
  • You should see a DataFrame printed showing companies with esg_percentile values at or below 0.25, grouped by Industry and Year.

Seeing an OperationalError?

Make sure your data/ folder exists. The sqlite3.connect() call creates the database file, but it cannot create intermediate directories.

Also confirm that Cell 1 ran successfully and df is loaded. If you restarted the kernel, you need to re-run Cell 1 first.

Help me debug my SQLite connection error

Now add the second query directly below in the same cell. This one uses the LAG window function to detect companies whose ESG scores are declining year over year.

  • Add Query 2 below Query 1 in the same cell:
# Query 2: Year-over-year ESG changes with consecutive decline flags
query_yoy = """
WITH esg_changes AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) AS esg_change,
        CASE
            WHEN ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            AND LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year)
                - LAG(ESG, 2) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            THEN 1 ELSE 0
        END AS consecutive_decline
    FROM companies_esg
)
SELECT *
FROM esg_changes
WHERE consecutive_decline = 1
ORDER BY CompanyID, Year
LIMIT 20;
"""
declining_esg = pd.read_sql_query(query_yoy, conn)
print("\nCompanies with Consecutive ESG Declines:")
print(declining_esg)

What does this query do?

LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) looks at each company's ESG score from the previous year. Subtracting it from the current year's ESG gives the year-over-year change.

The CASE WHEN checks whether BOTH the current year AND the prior year had negative changes. A company flagged with consecutive_decline = 1 is deteriorating over multiple years, which is a stronger credit risk signal than a single dip.

  • Run the cell again.
  • Below the first query's output, you should now see a table of companies flagged with consecutive_decline = 1.

Getting an empty DataFrame for Query 2?

This can happen if the dataset has very few companies with two consecutive years of ESG decline. The query is correct. Check that the LAG(ESG, 2) syntax matches exactly. A typo in the partition clause would also produce empty results.

Help me understand why my consecutive decline query returns empty results

Find high-risk industries and classify risk tiers

Individual company risk matters, but portfolio-level credit analysis needs industry-wide patterns. The next two queries zoom out to the sector level.

  • Add Query 3 below Query 2 in the same cell. This query identifies industries with below-median ESG scores AND declining profit margins:
# Query 3: Industries with below-median ESG and declining margins
query_industry_risk = """
WITH global_median AS (
    SELECT AVG(ESG) AS median_esg FROM companies_esg
),
industry_trends AS (
    SELECT
        Industry,
        Year,
        AVG(ESG) AS avg_esg,
        AVG(ProfitMargin) AS avg_margin,
        AVG(ProfitMargin) - LAG(AVG(ProfitMargin)) OVER (PARTITION BY Industry ORDER BY Year) AS margin_change
    FROM companies_esg
    GROUP BY Industry, Year
)
SELECT
    it.Industry,
    it.Year,
    it.avg_esg,
    it.avg_margin,
    it.margin_change
FROM industry_trends it, global_median gm
WHERE it.avg_esg < gm.median_esg
AND it.margin_change < 0
ORDER BY it.margin_change ASC;
"""
high_risk_industries = pd.read_sql_query(query_industry_risk, conn)
print("\nHigh-Risk Industries (Low ESG + Declining Margins):")
print(high_risk_industries.head(15))

What does this query reveal?

This query chains two CTEs together. The first calculates the global ESG median. The second computes each industry's average ESG and margin trends using GROUP BY with a LAG window function to detect margin decline.

The final WHERE clause filters to industry-years where ESG is below the global median AND margins are shrinking. These are the sectors facing the dual headwind of poor sustainability practices and financial deterioration.

  • Run the cell.
  • You should see a table with columns Industry, Year, avg_esg, avg_margin, and margin_change showing the highest-risk industry-year combinations.

Next, add Query 4 to create a categorical risk tier system. This mirrors how Sustainalytics classifies ESG risk severity into tiers.

  • Add Query 4 below Query 3 in the same cell:
# Query 4: Risk tier classification
query_risk_tiers = """
SELECT
    CompanyID,
    CompanyName,
    Industry,
    Year,
    ESG,
    ProfitMargin,
    GrowthRate,
    CASE
        WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
        WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
        WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
        ELSE 'Low'
    END AS risk_tier
FROM companies_esg
ORDER BY risk_tier, ESG;
"""
risk_tiers = pd.read_sql_query(query_risk_tiers, conn)
print("\nRisk Tier Distribution:")
print(risk_tiers['risk_tier'].value_counts())

What does this classification do?

The CASE WHEN logic assigns each company-year to a risk tier based on combined ESG and financial thresholds. Critical means ESG below 30 with very thin margins. Low means the company passes all threshold checks.

This tiering structure mirrors how rating agencies bucket risk severity. You'll use these tiers again in the next query to measure actual distress rates per tier.

  • Run the cell.
  • You should see a value counts output showing how many company-year records fall into each tier: Critical, High, Moderate, and Low.

All companies landing in one tier?

Check that your CASE WHEN thresholds match the Complete Code exactly. The conditions are evaluated in order, so placing the ELSE 'Low' clause first would catch everything.

Help me fix my CASE WHEN risk tier classification

Measure distress rates across risk tiers

The final query ties everything together. It combines the risk tier classification with a financial distress flag to answer the core question: do companies in higher ESG risk tiers actually experience more financial distress?

  • Add Query 5 below Query 4, followed by the connection close:
# Query 5: Distress rate by ESG risk tier
query_distress_by_tier = """
WITH tiered AS (
    SELECT
        CompanyID,
        Industry,
        Year,
        ESG,
        ProfitMargin,
        GrowthRate,
        CASE
            WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
            WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
            WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
            ELSE 'Low'
        END AS risk_tier,
        CASE
            WHEN GrowthRate < 0 AND ProfitMargin < 0.1 THEN 1
            ELSE 0
        END AS is_distressed
    FROM companies_esg
)
SELECT
    risk_tier,
    COUNT(*) AS total_companies,
    SUM(is_distressed) AS distressed_count,
    ROUND(CAST(SUM(is_distressed) AS FLOAT) / COUNT(*) * 100, 2) AS distress_rate_pct
FROM tiered
GROUP BY risk_tier
ORDER BY distress_rate_pct DESC;
"""
distress_by_tier = pd.read_sql_query(query_distress_by_tier, conn)
print("\nDistress Rate by ESG Risk Tier:")
print(distress_by_tier)

conn.close()

What does this final query prove?

This query builds a CTE that assigns both a risk tier AND a distress flag to each company-year. A company is flagged as distressed when it has negative growth AND margins below 10%.

The outer query groups by risk tier and calculates the distress rate as a percentage. If ESG scores are meaningful credit risk signals, you should see higher distress rates in the Critical and High tiers compared to Moderate and Low.

conn.close() releases the database connection. The .db file persists on disk for future use.

  • Run the cell.
  • You should see a table with four rows (Critical, High, Moderate, Low), each showing total_companies, distressed_count, and distress_rate_pct.

Seeing a 'database is locked' error?

This happens if a previous cell run failed midway and left the connection open. Restart your kernel, then re-run Cell 1 and Cell 2 from scratch.

Help me fix my SQLite database lock error

Finally, add a markdown cell above or below Cell 2 explaining what these queries reveal about the relationship between ESG performance and financial distress. Note whether higher-risk ESG tiers correspond to higher distress rates.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook before moving on.

ⓧ I'd like to double check the full code

Here is the complete Cell 2 code. Make sure yours matches:

# --- Cell 2: Load into SQLite ---
conn = sqlite3.connect('data/esg_credit_risk.db')
df.to_sql('companies_esg', conn, if_exists='replace', index=False)

# Query 1: ESG percentile rank within industry and year (CTE + Window Function)
query_percentile = """
WITH esg_ranked AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        PERCENT_RANK() OVER (PARTITION BY Industry, Year ORDER BY ESG) AS esg_percentile
    FROM companies_esg
)
SELECT *
FROM esg_ranked
WHERE esg_percentile <= 0.25
ORDER BY Industry, Year, esg_percentile
LIMIT 20;
"""
bottom_quartile = pd.read_sql_query(query_percentile, conn)
print("Bottom ESG Quartile (High Risk):")
print(bottom_quartile)

# Query 2: Year-over-year ESG changes with consecutive decline flags
query_yoy = """
WITH esg_changes AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) AS esg_change,
        CASE
            WHEN ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            AND LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year)
                - LAG(ESG, 2) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            THEN 1 ELSE 0
        END AS consecutive_decline
    FROM companies_esg
)
SELECT *
FROM esg_changes
WHERE consecutive_decline = 1
ORDER BY CompanyID, Year
LIMIT 20;
"""
declining_esg = pd.read_sql_query(query_yoy, conn)
print("\nCompanies with Consecutive ESG Declines:")
print(declining_esg)

# Query 3: Industries with below-median ESG and declining margins
query_industry_risk = """
WITH global_median AS (
    SELECT AVG(ESG) AS median_esg FROM companies_esg
),
industry_trends AS (
    SELECT
        Industry,
        Year,
        AVG(ESG) AS avg_esg,
        AVG(ProfitMargin) AS avg_margin,
        AVG(ProfitMargin) - LAG(AVG(ProfitMargin)) OVER (PARTITION BY Industry ORDER BY Year) AS margin_change
    FROM companies_esg
    GROUP BY Industry, Year
)
SELECT
    it.Industry,
    it.Year,
    it.avg_esg,
    it.avg_margin,
    it.margin_change
FROM industry_trends it, global_median gm
WHERE it.avg_esg < gm.median_esg
AND it.margin_change < 0
ORDER BY it.margin_change ASC;
"""
high_risk_industries = pd.read_sql_query(query_industry_risk, conn)
print("\nHigh-Risk Industries (Low ESG + Declining Margins):")
print(high_risk_industries.head(15))

# Query 4: Risk tier classification
query_risk_tiers = """
SELECT
    CompanyID,
    CompanyName,
    Industry,
    Year,
    ESG,
    ProfitMargin,
    GrowthRate,
    CASE
        WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
        WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
        WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
        ELSE 'Low'
    END AS risk_tier
FROM companies_esg
ORDER BY risk_tier, ESG;
"""
risk_tiers = pd.read_sql_query(query_risk_tiers, conn)
print("\nRisk Tier Distribution:")
print(risk_tiers['risk_tier'].value_counts())

# Query 5: Distress rate by ESG risk tier
query_distress_by_tier = """
WITH tiered AS (
    SELECT
        CompanyID,
        Industry,
        Year,
        ESG,
        ProfitMargin,
        GrowthRate,
        CASE
            WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
            WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
            WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
            ELSE 'Low'
        END AS risk_tier,
        CASE
            WHEN GrowthRate < 0 AND ProfitMargin < 0.1 THEN 1
            ELSE 0
        END AS is_distressed
    FROM companies_esg
)
SELECT
    risk_tier,
    COUNT(*) AS total_companies,
    SUM(is_distressed) AS distressed_count,
    ROUND(CAST(SUM(is_distressed) AS FLOAT) / COUNT(*) * 100, 2) AS distress_rate_pct
FROM tiered
GROUP BY risk_tier
ORDER BY distress_rate_pct DESC;
"""
distress_by_tier = pd.read_sql_query(query_distress_by_tier, conn)
print("\nDistress Rate by ESG Risk Tier:")
print(distress_by_tier)

conn.close()

Your SQL queries are in place and the data tells a clear story. Next up, you'll engineer features from this data to feed into a predictive credit risk model.

Engineer Credit Risk Features

Your SQL queries revealed patterns between ESG scores and financial health at the industry level. Now you need to translate those patterns into a format a machine learning model can learn from.

In this step, you will engineer a binary credit risk target variable and build the features that your logistic regression model will use for prediction. You will also split the data temporally so the model trains on historical data and tests on future periods.

In this step, get ready to:
  • Engineer a binary financial distress target variable from profit margins and growth rates.
  • Create financial and ESG features that capture leverage, volatility, and relative performance.
  • Split data into temporal train and test sets mimicking real-world credit model validation.
Create the financial distress target variable

Since the dataset does not include actual default labels, you need a proxy for credit risk. You will define a company-year as "distressed" if its profit margin falls below the 25th percentile for its industry AND its growth rate is negative.

Why this proxy works for credit risk

Real credit default models use actual default events as labels. With synthetic data, you proxy default risk from observable financial deterioration. This approach aligns with how the Altman Z-score and similar models derive default risk from financial ratios.

Using the industry-specific 25th percentile (instead of a global threshold) ensures the label captures relative underperformance. A 5% margin might be healthy in retail but alarming in software.

  • Create a new cell in your notebook (Cell 3) and add the following code to engineer the distress target:
# --- Cell 3: Feature Engineering ---
# Create binary target: financial distress
industry_margin_25th = df.groupby('Industry')['ProfitMargin'].transform(
    lambda x: x.quantile(0.25)
)
df['financial_distress'] = (
    (df['ProfitMargin'] < industry_margin_25th) & (df['GrowthRate'] < 0)
).astype(int)

print(f"\nOverall distress rate: {df['financial_distress'].mean():.2%}")
print("\nDistress rate by industry:")
print(df.groupby('Industry')['financial_distress'].mean().sort_values(ascending=False))

What does this code do?

  • groupby('Industry')['ProfitMargin'].transform(lambda x: x.quantile(0.25)) calculates the 25th percentile margin for each industry and broadcasts it back to every row in that group.
  • The compound condition (ProfitMargin < threshold) & (GrowthRate < 0) flags companies that are both underperforming relative to peers AND shrinking.
  • .astype(int) converts the boolean result to 1 (distressed) or 0 (healthy) for model training.
  • Run the cell.
  • You should see the overall distress rate printed as a percentage, followed by a per-industry breakdown sorted from highest to lowest.

Seeing a KeyError?

If you see an error referencing ProfitMargin or GrowthRate, make sure Cell 1 ran successfully first. The df DataFrame must be in memory with all original columns from the CSV.

help me debug a KeyError in my feature engineering cell

Create financial features for the baseline model

Your baseline model will use only financial features to predict distress. These features capture a company's leverage, earnings stability, and relative size.

  • Add the following code to the same cell, below the print statements:
# Financial features
df['leverage_proxy'] = df['Revenue'] / (df['MarketCap'] + 1)
df['margin_volatility'] = df.groupby('CompanyID')['ProfitMargin'].transform(
    lambda x: x.rolling(3, min_periods=1).std()
)
df['size_decile'] = df.groupby('Year')['MarketCap'].transform(
    lambda x: pd.qcut(x, 10, labels=False, duplicates='drop')
)

What do these features capture?

  • leverage_proxy measures how much revenue a company generates relative to its market value. Higher values suggest the company is overleveraged or undervalued.
  • margin_volatility is the rolling 3-year standard deviation of profit margins. Unstable earnings signal higher credit risk.
  • size_decile ranks companies by market cap within each year. Smaller companies tend to face higher default risk.

You will not run this cell yet. The ESG features go into the same cell.

Create ESG features and split the data temporally

The ESG-enhanced model adds signals that capture momentum (is a company's ESG getting better or worse?) and relative positioning (is this company's ESG above or below its industry peers?).

  • Add the following code directly below the financial features in the same cell:
# ESG features
df['esg_momentum'] = df.groupby('CompanyID')['ESG'].transform(
    lambda x: x.diff()
)
industry_year_median_esg = df.groupby(['Industry', 'Year'])['ESG'].transform('median')
df['esg_industry_relative'] = df['ESG'] - industry_year_median_esg

# Rename ESG pillar columns for consistency
df = df.rename(columns={
    'ESG Environmental': 'ESG_Environmental',
    'ESG Social': 'ESG_Social',
    'ESG Governance': 'ESG_Governance'
})

What do these ESG features capture?

  • esg_momentum is the year-over-year change in a company's overall ESG score. A declining ESG trajectory may signal rising risk before financials deteriorate.
  • esg_industry_relative measures how far above or below the industry median a company sits. An absolute ESG score of 45 might be excellent in mining but poor in tech.
  • The rename step converts column names with spaces to underscores so they work cleanly as DataFrame column references throughout the modeling code.
  • Run the full Cell 3. You should see the overall distress rate and per-industry breakdown printed.

Now you need to split the data for model validation. Credit models are always validated on future data because you cannot predict the past.

  • Create a new cell (Cell 4) and add the temporal train-test split:
# --- Cell 4: Train-Test Split (Temporal) ---
train = df[df['Year'] <= 2022].copy()
test = df[df['Year'] > 2022].copy()

print(f"\nTrain set: {train.shape[0]} rows ({train['Year'].min()}-{train['Year'].max()})")
print(f"Test set: {test.shape[0]} rows ({test['Year'].min()}-{test['Year'].max()})")
print(f"Train distress rate: {train['financial_distress'].mean():.2%}")
print(f"Test distress rate: {test['financial_distress'].mean():.2%}")

Why a temporal split instead of random?

A random train-test split leaks future information into training data. If the model trains on 2024 data and predicts 2020 outcomes, it has an unfair advantage that would never exist in production.

Banks validate credit models using temporal splits because regulators (CCAR, CECL) require evidence that models perform on out-of-sample future periods. Training on 2015-2022 and testing on 2023-2025 mirrors this practice exactly.

  • Run Cell 4.
  • You should see the train and test set sizes, their year ranges, and the distress rate for each split printed.

Seeing an empty test set?

If your test DataFrame shows 0 rows, confirm that the Year column contains values above 2022. Run df['Year'].unique() to check. The dataset spans 2015-2025, so you should see years up to 2025.

help me debug why my temporal train-test split produces an empty test set

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook before moving on.

ⓧ I'd like to double check the full code

Here is the complete code for your notebook up through Cell 4. Compare your cells against this:

# ESG Risk Signals in Credit Default Prediction
# Portfolio Project - ESG-Integrated Credit Risk Analysis

# --- Cell 1: Imports and Data Loading ---
import pandas as pd
import numpy as np
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score, classification_report, roc_curve
from sklearn.preprocessing import StandardScaler
from sklearn.impute import SimpleImputer

# Load dataset
df = pd.read_csv('data/esg_financial_performance.csv')
print(f"Dataset shape: {df.shape}")
print(df.info())
print(df.head())

# --- Cell 2: Load into SQLite ---
conn = sqlite3.connect('data/esg_credit_risk.db')
df.to_sql('companies_esg', conn, if_exists='replace', index=False)

query_percentile = """
WITH esg_ranked AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        PERCENT_RANK() OVER (PARTITION BY Industry, Year ORDER BY ESG) AS esg_percentile
    FROM companies_esg
)
SELECT *
FROM esg_ranked
WHERE esg_percentile <= 0.25
ORDER BY Industry, Year, esg_percentile
LIMIT 20;
"""
bottom_quartile = pd.read_sql_query(query_percentile, conn)
print("Bottom ESG Quartile (High Risk):")
print(bottom_quartile)

query_yoy = """
WITH esg_changes AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) AS esg_change,
        CASE
            WHEN ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            AND LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year)
                - LAG(ESG, 2) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            THEN 1 ELSE 0
        END AS consecutive_decline
    FROM companies_esg
)
SELECT *
FROM esg_changes
WHERE consecutive_decline = 1
ORDER BY CompanyID, Year
LIMIT 20;
"""
declining_esg = pd.read_sql_query(query_yoy, conn)
print("\nCompanies with Consecutive ESG Declines:")
print(declining_esg)

query_industry_risk = """
WITH global_median AS (
    SELECT AVG(ESG) AS median_esg FROM companies_esg
),
industry_trends AS (
    SELECT
        Industry,
        Year,
        AVG(ESG) AS avg_esg,
        AVG(ProfitMargin) AS avg_margin,
        AVG(ProfitMargin) - LAG(AVG(ProfitMargin)) OVER (PARTITION BY Industry ORDER BY Year) AS margin_change
    FROM companies_esg
    GROUP BY Industry, Year
)
SELECT
    it.Industry,
    it.Year,
    it.avg_esg,
    it.avg_margin,
    it.margin_change
FROM industry_trends it, global_median gm
WHERE it.avg_esg < gm.median_esg
AND it.margin_change < 0
ORDER BY it.margin_change ASC;
"""
high_risk_industries = pd.read_sql_query(query_industry_risk, conn)
print("\nHigh-Risk Industries (Low ESG + Declining Margins):")
print(high_risk_industries.head(15))

query_risk_tiers = """
SELECT
    CompanyID,
    CompanyName,
    Industry,
    Year,
    ESG,
    ProfitMargin,
    GrowthRate,
    CASE
        WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
        WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
        WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
        ELSE 'Low'
    END AS risk_tier
FROM companies_esg
ORDER BY risk_tier, ESG;
"""
risk_tiers = pd.read_sql_query(query_risk_tiers, conn)
print("\nRisk Tier Distribution:")
print(risk_tiers['risk_tier'].value_counts())

query_distress_by_tier = """
WITH tiered AS (
    SELECT
        CompanyID,
        Industry,
        Year,
        ESG,
        ProfitMargin,
        GrowthRate,
        CASE
            WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
            WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
            WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
            ELSE 'Low'
        END AS risk_tier,
        CASE
            WHEN GrowthRate < 0 AND ProfitMargin < 0.1 THEN 1
            ELSE 0
        END AS is_distressed
    FROM companies_esg
)
SELECT
    risk_tier,
    COUNT(*) AS total_companies,
    SUM(is_distressed) AS distressed_count,
    ROUND(CAST(SUM(is_distressed) AS FLOAT) / COUNT(*) * 100, 2) AS distress_rate_pct
FROM tiered
GROUP BY risk_tier
ORDER BY distress_rate_pct DESC;
"""
distress_by_tier = pd.read_sql_query(query_distress_by_tier, conn)
print("\nDistress Rate by ESG Risk Tier:")
print(distress_by_tier)

conn.close()

# --- Cell 3: Feature Engineering ---
# Create binary target: financial distress
industry_margin_25th = df.groupby('Industry')['ProfitMargin'].transform(
    lambda x: x.quantile(0.25)
)
df['financial_distress'] = (
    (df['ProfitMargin'] < industry_margin_25th) & (df['GrowthRate'] < 0)
).astype(int)

print(f"\nOverall distress rate: {df['financial_distress'].mean():.2%}")
print("\nDistress rate by industry:")
print(df.groupby('Industry')['financial_distress'].mean().sort_values(ascending=False))

# Financial features
df['leverage_proxy'] = df['Revenue'] / (df['MarketCap'] + 1)
df['margin_volatility'] = df.groupby('CompanyID')['ProfitMargin'].transform(
    lambda x: x.rolling(3, min_periods=1).std()
)
df['size_decile'] = df.groupby('Year')['MarketCap'].transform(
    lambda x: pd.qcut(x, 10, labels=False, duplicates='drop')
)

# ESG features
df['esg_momentum'] = df.groupby('CompanyID')['ESG'].transform(
    lambda x: x.diff()
)
industry_year_median_esg = df.groupby(['Industry', 'Year'])['ESG'].transform('median')
df['esg_industry_relative'] = df['ESG'] - industry_year_median_esg

# Rename ESG pillar columns for consistency
df = df.rename(columns={
    'ESG Environmental': 'ESG_Environmental',
    'ESG Social': 'ESG_Social',
    'ESG Governance': 'ESG_Governance'
})

# --- Cell 4: Train-Test Split (Temporal) ---
train = df[df['Year'] <= 2022].copy()
test = df[df['Year'] > 2022].copy()

print(f"\nTrain set: {train.shape[0]} rows ({train['Year'].min()}-{train['Year'].max()})")
print(f"Test set: {test.shape[0]} rows ({test['Year'].min()}-{test['Year'].max()})")
print(f"Train distress rate: {train['financial_distress'].mean():.2%}")
print(f"Test distress rate: {test['financial_distress'].mean():.2%}")

Your features and train-test split are ready. Next up, you will train the baseline and ESG-enhanced logistic regression models and measure whether ESG data actually improves default prediction.

Build and Compare Credit Risk Models

You have clean features and a temporal train-test split ready. Now comes the central question of this project: does adding ESG data actually improve credit risk prediction?

To answer this rigorously, you will build two models with identical architecture. The only difference is the input features. If the ESG-enhanced model outperforms the baseline, you have quantitative evidence that ESG scores carry predictive signal for credit default.

In this step, get ready to:
  • Train a baseline logistic regression model using only financial features.
  • Train an ESG-enhanced model and quantify the ROC-AUC improvement.
  • Visualize feature importance to identify which ESG pillars drive credit risk prediction.
Build the baseline model with financial features only

A good experiment needs a control group. Your baseline model uses only traditional financial ratios to predict distress. This establishes the "before ESG" performance benchmark.

  • Create a new cell in your notebook (Cell 5) and add the following code to build the baseline model:
# --- Cell 5: Baseline Model (Financial Features Only) ---
financial_features = ['leverage_proxy', 'margin_volatility', 'size_decile',
                      'ProfitMargin', 'GrowthRate', 'Revenue']

# Impute missing values and scale features
imputer = SimpleImputer(strategy='median')
scaler = StandardScaler()

# Prepare training features
X_train_fin = pd.DataFrame(
    imputer.fit_transform(train[financial_features]),
    columns=financial_features
)
X_test_fin = pd.DataFrame(
    imputer.transform(test[financial_features]),
    columns=financial_features
)

# Scale for logistic regression
X_train_fin_scaled = scaler.fit_transform(X_train_fin)
X_test_fin_scaled = scaler.transform(X_test_fin)

# Extract target variable
y_train = train['financial_distress'].values
y_test = test['financial_distress'].values

# Train logistic regression
model_baseline = LogisticRegression(max_iter=1000, random_state=42)
model_baseline.fit(X_train_fin_scaled, y_train)

# Predict probabilities and evaluate
y_pred_baseline = model_baseline.predict_proba(X_test_fin_scaled)[:, 1]
auc_baseline = roc_auc_score(y_test, y_pred_baseline)
print(f"\nBaseline Model (Financial Only) ROC-AUC: {auc_baseline:.4f}")

What does this code do?

This cell establishes the performance you can achieve without any ESG data.

  • SimpleImputer fills any missing values with the column median, which is robust to outliers in financial data.
  • StandardScaler normalizes features to mean 0 and standard deviation 1. Logistic regression is sensitive to feature scale, so this prevents high-magnitude features like Revenue from dominating.
  • predict_proba returns probability estimates (not binary predictions), which is what ROC-AUC needs to measure ranking quality.

Why logistic regression instead of a more complex model?

Logistic regression is interpretable. Its coefficients tell a clear story about which factors increase or decrease distress risk. In credit risk interviews, demonstrating you understand the underlying statistics matters more than importing a complex package.

Banks also prefer interpretable models for regulatory compliance. A model that can explain its decisions is easier to defend during audits.

  • Run the cell.
  • You should see the baseline ROC-AUC score printed. Note this number down mentally. You will compare it against the ESG-enhanced model next.

Seeing a ValueError about array shapes?

  • Make sure Cell 3 (feature engineering) and Cell 4 (train-test split) both ran successfully before this cell.
  • Check that your train and test DataFrames are not empty by running train.shape in a new cell.

Help me debug a ValueError in my logistic regression baseline model

Build the ESG-enhanced model and compare performance

Now you add ESG features on top of the financial ones. If ESG scores carry real predictive signal for credit risk, you should see the ROC-AUC increase.

  • Create a new cell (Cell 6) and add the following code to build the ESG-enhanced model:
# --- Cell 6: ESG-Enhanced Model ---
esg_features = ['ESG', 'ESG_Environmental', 'ESG_Social', 'ESG_Governance',
                'esg_momentum', 'esg_industry_relative']

# Combine financial + ESG features
all_features = financial_features + esg_features

# Separate imputer and scaler for this model
imputer_esg = SimpleImputer(strategy='median')
scaler_esg = StandardScaler()

# Prepare combined feature set
X_train_all = pd.DataFrame(
    imputer_esg.fit_transform(train[all_features]),
    columns=all_features
)
X_test_all = pd.DataFrame(
    imputer_esg.transform(test[all_features]),
    columns=all_features
)

X_train_all_scaled = scaler_esg.fit_transform(X_train_all)
X_test_all_scaled = scaler_esg.transform(X_test_all)

# Train ESG-enhanced model
model_esg = LogisticRegression(max_iter=1000, random_state=42)
model_esg.fit(X_train_all_scaled, y_train)

# Predict and evaluate
y_pred_esg = model_esg.predict_proba(X_test_all_scaled)[:, 1]
auc_esg = roc_auc_score(y_test, y_pred_esg)
print(f"ESG-Enhanced Model ROC-AUC: {auc_esg:.4f}")
print(f"Improvement: +{(auc_esg - auc_baseline):.4f} ({((auc_esg - auc_baseline) / auc_baseline) * 100:.1f}%)")

# Side-by-side comparison
comparison = pd.DataFrame({
    'Model': ['Baseline (Financial Only)', 'ESG-Enhanced'],
    'ROC-AUC': [auc_baseline, auc_esg],
    'Features': [len(financial_features), len(all_features)]
})
print("\nModel Comparison:")
print(comparison.to_string(index=False))

What does this code do?

  • all_features combines the 6 financial features with 6 ESG features for a total of 12 predictors.
  • A separate imputer_esg and scaler_esg are used so the baseline model's preprocessing stays untouched. This keeps the comparison fair.
  • The improvement percentage tells you exactly how much predictive power ESG data adds on top of traditional credit metrics.
  • Run the cell.
  • You should see the ESG-Enhanced ROC-AUC printed alongside the improvement over baseline, plus a comparison table showing both models side by side.

ESG model showing no improvement?

  • Verify that your ESG feature columns exist by running train[esg_features].head() in a separate cell. If you see a KeyError, Cell 3's column rename may not have executed.
  • Confirm esg_momentum is not all NaN values. The first year per company will be NaN (no prior year to diff against), but subsequent years should have values.

Help me understand why my ESG-enhanced model shows no improvement over baseline

Visualize feature importance from the ESG-enhanced model

Knowing that ESG improves prediction is step one. Step two is understanding which specific factors matter most. The model coefficients reveal exactly which features have the strongest association with financial distress.

  • Create a new cell (Cell 7) and add the following code to extract and plot feature coefficients:
# --- Cell 7: Feature Importance ---
feature_importance = pd.DataFrame({
    'Feature': all_features,
    'Coefficient': model_esg.coef_[0]
}).sort_values('Coefficient', key=abs, ascending=True)

# Horizontal bar chart color-coded by direction
plt.figure(figsize=(10, 8))
colors = ['#2ecc71' if c < 0 else '#e74c3c' for c in feature_importance['Coefficient']]
plt.barh(feature_importance['Feature'], feature_importance['Coefficient'], color=colors)
plt.xlabel('Coefficient (Negative = Reduces Distress Risk)')
plt.title('Feature Importance: ESG-Enhanced Credit Risk Model')
plt.axvline(x=0, color='black', linestyle='-', linewidth=0.5)
plt.tight_layout()
plt.savefig('feature_importance.png', dpi=150, bbox_inches='tight')
plt.show()

What does this chart show?

  • Each bar represents a feature's coefficient from the logistic regression. The sign tells you the direction of the relationship.
  • Green bars (negative coefficients) indicate features that reduce distress risk. Red bars (positive coefficients) indicate features that increase distress risk.
  • Longer bars have stronger associations with the outcome. Look at which ESG pillars appear among the strongest signals.
  • Run the cell.
  • You should see a horizontal bar chart with all 12 features, color-coded by their relationship direction. The chart is also saved as feature_importance.png in your project folder.

How to interpret this for interviews

When presenting this chart, connect the results to domain knowledge. For example: "The Environmental pillar shows the strongest predictive signal, consistent with transition risk exposure in carbon-intensive sectors."

This is exactly the kind of narrative that bridges ESG domain expertise with quantitative analytics. You are not just running code. You are telling a story about what the data reveals.

Chart not displaying or looking empty?

  • If you see a blank chart, make sure Cell 6 ran without errors. The model_esg variable must exist for model_esg.coef_[0] to work.
  • If the chart appears in a separate window instead of inline, add %matplotlib inline at the top of your notebook.

Help me fix my feature importance chart

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all three new cells (5, 6, and 7) executed.

ⓧ I'd like to double check the full code

Here is the complete notebook code up to this point:

# ESG Risk Signals in Credit Default Prediction
# Portfolio Project - ESG-Integrated Credit Risk Analysis

# --- Cell 1: Imports and Data Loading ---
import pandas as pd
import numpy as np
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score, classification_report, roc_curve
from sklearn.preprocessing import StandardScaler
from sklearn.impute import SimpleImputer

# Load dataset
df = pd.read_csv('data/esg_financial_performance.csv')
print(f"Dataset shape: {df.shape}")
print(df.info())
print(df.head())

# --- Cell 2: Load into SQLite ---
conn = sqlite3.connect('data/esg_credit_risk.db')
df.to_sql('companies_esg', conn, if_exists='replace', index=False)

query_percentile = """
WITH esg_ranked AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        PERCENT_RANK() OVER (PARTITION BY Industry, Year ORDER BY ESG) AS esg_percentile
    FROM companies_esg
)
SELECT *
FROM esg_ranked
WHERE esg_percentile <= 0.25
ORDER BY Industry, Year, esg_percentile
LIMIT 20;
"""
bottom_quartile = pd.read_sql_query(query_percentile, conn)
print("Bottom ESG Quartile (High Risk):")
print(bottom_quartile)

query_yoy = """
WITH esg_changes AS (
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        Year,
        ESG,
        ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) AS esg_change,
        CASE
            WHEN ESG - LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            AND LAG(ESG) OVER (PARTITION BY CompanyID ORDER BY Year)
                - LAG(ESG, 2) OVER (PARTITION BY CompanyID ORDER BY Year) < 0
            THEN 1 ELSE 0
        END AS consecutive_decline
    FROM companies_esg
)
SELECT *
FROM esg_changes
WHERE consecutive_decline = 1
ORDER BY CompanyID, Year
LIMIT 20;
"""
declining_esg = pd.read_sql_query(query_yoy, conn)
print("\nCompanies with Consecutive ESG Declines:")
print(declining_esg)

query_industry_risk = """
WITH global_median AS (
    SELECT AVG(ESG) AS median_esg FROM companies_esg
),
industry_trends AS (
    SELECT
        Industry,
        Year,
        AVG(ESG) AS avg_esg,
        AVG(ProfitMargin) AS avg_margin,
        AVG(ProfitMargin) - LAG(AVG(ProfitMargin)) OVER (PARTITION BY Industry ORDER BY Year) AS margin_change
    FROM companies_esg
    GROUP BY Industry, Year
)
SELECT
    it.Industry,
    it.Year,
    it.avg_esg,
    it.avg_margin,
    it.margin_change
FROM industry_trends it, global_median gm
WHERE it.avg_esg < gm.median_esg
AND it.margin_change < 0
ORDER BY it.margin_change ASC;
"""
high_risk_industries = pd.read_sql_query(query_industry_risk, conn)
print("\nHigh-Risk Industries (Low ESG + Declining Margins):")
print(high_risk_industries.head(15))

query_risk_tiers = """
SELECT
    CompanyID,
    CompanyName,
    Industry,
    Year,
    ESG,
    ProfitMargin,
    GrowthRate,
    CASE
        WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
        WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
        WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
        ELSE 'Low'
    END AS risk_tier
FROM companies_esg
ORDER BY risk_tier, ESG;
"""
risk_tiers = pd.read_sql_query(query_risk_tiers, conn)
print("\nRisk Tier Distribution:")
print(risk_tiers['risk_tier'].value_counts())

query_distress_by_tier = """
WITH tiered AS (
    SELECT
        CompanyID,
        Industry,
        Year,
        ESG,
        ProfitMargin,
        GrowthRate,
        CASE
            WHEN ESG < 30 AND ProfitMargin < 0.1 THEN 'Critical'
            WHEN ESG < 50 AND ProfitMargin < 0.15 THEN 'High'
            WHEN ESG < 70 AND ProfitMargin < 0.2 THEN 'Moderate'
            ELSE 'Low'
        END AS risk_tier,
        CASE
            WHEN GrowthRate < 0 AND ProfitMargin < 0.1 THEN 1
            ELSE 0
        END AS is_distressed
    FROM companies_esg
)
SELECT
    risk_tier,
    COUNT(*) AS total_companies,
    SUM(is_distressed) AS distressed_count,
    ROUND(CAST(SUM(is_distressed) AS FLOAT) / COUNT(*) * 100, 2) AS distress_rate_pct
FROM tiered
GROUP BY risk_tier
ORDER BY distress_rate_pct DESC;
"""
distress_by_tier = pd.read_sql_query(query_distress_by_tier, conn)
print("\nDistress Rate by ESG Risk Tier:")
print(distress_by_tier)

conn.close()

# --- Cell 3: Feature Engineering ---
industry_margin_25th = df.groupby('Industry')['ProfitMargin'].transform(
    lambda x: x.quantile(0.25)
)
df['financial_distress'] = (
    (df['ProfitMargin'] < industry_margin_25th) & (df['GrowthRate'] < 0)
).astype(int)

print(f"\nOverall distress rate: {df['financial_distress'].mean():.2%}")
print("\nDistress rate by industry:")
print(df.groupby('Industry')['financial_distress'].mean().sort_values(ascending=False))

df['leverage_proxy'] = df['Revenue'] / (df['MarketCap'] + 1)
df['margin_volatility'] = df.groupby('CompanyID')['ProfitMargin'].transform(
    lambda x: x.rolling(3, min_periods=1).std()
)
df['size_decile'] = df.groupby('Year')['MarketCap'].transform(
    lambda x: pd.qcut(x, 10, labels=False, duplicates='drop')
)

df['esg_momentum'] = df.groupby('CompanyID')['ESG'].transform(
    lambda x: x.diff()
)
industry_year_median_esg = df.groupby(['Industry', 'Year'])['ESG'].transform('median')
df['esg_industry_relative'] = df['ESG'] - industry_year_median_esg

df = df.rename(columns={
    'ESG Environmental': 'ESG_Environmental',
    'ESG Social': 'ESG_Social',
    'ESG Governance': 'ESG_Governance'
})

# --- Cell 4: Train-Test Split (Temporal) ---
train = df[df['Year'] <= 2022].copy()
test = df[df['Year'] > 2022].copy()

print(f"\nTrain set: {train.shape[0]} rows ({train['Year'].min()}-{train['Year'].max()})")
print(f"Test set: {test.shape[0]} rows ({test['Year'].min()}-{test['Year'].max()})")
print(f"Train distress rate: {train['financial_distress'].mean():.2%}")
print(f"Test distress rate: {test['financial_distress'].mean():.2%}")

# --- Cell 5: Baseline Model (Financial Features Only) ---
financial_features = ['leverage_proxy', 'margin_volatility', 'size_decile',
                      'ProfitMargin', 'GrowthRate', 'Revenue']

imputer = SimpleImputer(strategy='median')
scaler = StandardScaler()

X_train_fin = pd.DataFrame(
    imputer.fit_transform(train[financial_features]),
    columns=financial_features
)
X_test_fin = pd.DataFrame(
    imputer.transform(test[financial_features]),
    columns=financial_features
)

X_train_fin_scaled = scaler.fit_transform(X_train_fin)
X_test_fin_scaled = scaler.transform(X_test_fin)

y_train = train['financial_distress'].values
y_test = test['financial_distress'].values

model_baseline = LogisticRegression(max_iter=1000, random_state=42)
model_baseline.fit(X_train_fin_scaled, y_train)

y_pred_baseline = model_baseline.predict_proba(X_test_fin_scaled)[:, 1]
auc_baseline = roc_auc_score(y_test, y_pred_baseline)
print(f"\nBaseline Model (Financial Only) ROC-AUC: {auc_baseline:.4f}")

# --- Cell 6: ESG-Enhanced Model ---
esg_features = ['ESG', 'ESG_Environmental', 'ESG_Social', 'ESG_Governance',
                'esg_momentum', 'esg_industry_relative']

all_features = financial_features + esg_features

imputer_esg = SimpleImputer(strategy='median')
scaler_esg = StandardScaler()

X_train_all = pd.DataFrame(
    imputer_esg.fit_transform(train[all_features]),
    columns=all_features
)
X_test_all = pd.DataFrame(
    imputer_esg.transform(test[all_features]),
    columns=all_features
)

X_train_all_scaled = scaler_esg.fit_transform(X_train_all)
X_test_all_scaled = scaler_esg.transform(X_test_all)

model_esg = LogisticRegression(max_iter=1000, random_state=42)
model_esg.fit(X_train_all_scaled, y_train)

y_pred_esg = model_esg.predict_proba(X_test_all_scaled)[:, 1]
auc_esg = roc_auc_score(y_test, y_pred_esg)
print(f"ESG-Enhanced Model ROC-AUC: {auc_esg:.4f}")
print(f"Improvement: +{(auc_esg - auc_baseline):.4f} ({((auc_esg - auc_baseline) / auc_baseline) * 100:.1f}%)")

comparison = pd.DataFrame({
    'Model': ['Baseline (Financial Only)', 'ESG-Enhanced'],
    'ROC-AUC': [auc_baseline, auc_esg],
    'Features': [len(financial_features), len(all_features)]
})
print("\nModel Comparison:")
print(comparison.to_string(index=False))

# --- Cell 7: Feature Importance ---
feature_importance = pd.DataFrame({
    'Feature': all_features,
    'Coefficient': model_esg.coef_[0]
}).sort_values('Coefficient', key=abs, ascending=True)

plt.figure(figsize=(10, 8))
colors = ['#2ecc71' if c < 0 else '#e74c3c' for c in feature_importance['Coefficient']]
plt.barh(feature_importance['Feature'], feature_importance['Coefficient'], color=colors)
plt.xlabel('Coefficient (Negative = Reduces Distress Risk)')
plt.title('Feature Importance: ESG-Enhanced Credit Risk Model')
plt.axvline(x=0, color='black', linestyle='-', linewidth=0.5)
plt.tight_layout()
plt.savefig('feature_importance.png', dpi=150, bbox_inches='tight')
plt.show()

You now have quantitative proof that ESG data adds predictive value to credit risk modeling. Next up, you will create publication-quality visualizations that present these findings in a format ready for stakeholder presentations.

Create Professional Visualizations and Write the Narrative

Your models are trained and the numbers tell a compelling story. Now you need to make that story visible to someone who will never read your code.

In this step, you will create publication-quality visualizations that communicate your findings at a glance. You will also write the narrative framework that turns a collection of charts into a coherent analytical argument suitable for an interview presentation.

In this step, get ready to:
  • Build a 2x2 dashboard figure with correlation heatmap, box plots, ROC curves, and risk tier analysis.
  • Create sector-level analysis visualizations comparing ESG scores and distress rates across industries.
  • Write an Executive Summary and Methodology section as markdown cells in your notebook.
Create the 2x2 dashboard figure

A single multi-panel figure is the gold standard for analytical presentations. Each subplot answers a different question, and together they build a complete argument about ESG-risk relationships.

  • Create a new cell in your notebook (Cell 8) and add the following code to build a 2x2 visualization grid:
# --- Cell 8: Professional Visualizations (2x2 Grid) ---
fig, axes = plt.subplots(2, 2, figsize=(14, 12))

# Plot 1: Correlation heatmap
corr_cols = ['ESG', 'ESG_Environmental', 'ESG_Social', 'ESG_Governance',
             'ProfitMargin', 'GrowthRate', 'Revenue', 'MarketCap']
corr_matrix = df[corr_cols].corr()
sns.heatmap(corr_matrix, annot=True, fmt='.2f', cmap='RdYlGn',
            center=0, ax=axes[0, 0], square=True)
axes[0, 0].set_title('ESG vs Financial Metrics Correlation')

# Plot 2: ESG scores by distress status
esg_by_distress = df.melt(
    id_vars=['financial_distress'],
    value_vars=['ESG_Environmental', 'ESG_Social', 'ESG_Governance'],
    var_name='ESG Pillar', value_name='Score'
)
sns.boxplot(data=esg_by_distress, x='ESG Pillar', y='Score',
            hue='financial_distress', ax=axes[0, 1], palette='Set2')
axes[0, 1].set_title('ESG Pillar Scores: Distressed vs Healthy')
axes[0, 1].legend(title='Distressed', labels=['No', 'Yes'])

# Plot 3: ROC curves comparison
fpr_base, tpr_base, _ = roc_curve(y_test, y_pred_baseline)
fpr_esg, tpr_esg, _ = roc_curve(y_test, y_pred_esg)
axes[1, 0].plot(fpr_base, tpr_base, label=f'Baseline (AUC={auc_baseline:.3f})', linewidth=2)
axes[1, 0].plot(fpr_esg, tpr_esg, label=f'ESG-Enhanced (AUC={auc_esg:.3f})', linewidth=2)
axes[1, 0].plot([0, 1], [0, 1], 'k--', alpha=0.5)
axes[1, 0].set_xlabel('False Positive Rate')
axes[1, 0].set_ylabel('True Positive Rate')
axes[1, 0].set_title('ROC Curve Comparison')
axes[1, 0].legend()

# Plot 4: Distress rate by risk tier
tier_distress = df.copy()
tier_distress['risk_tier'] = pd.cut(
    tier_distress['ESG'],
    bins=[0, 30, 50, 70, 100],
    labels=['Critical', 'High', 'Moderate', 'Low']
)
tier_rates = tier_distress.groupby('risk_tier', observed=True)['financial_distress'].mean()
tier_rates.plot(kind='bar', ax=axes[1, 1], color=['#e74c3c', '#f39c12', '#f1c40f', '#2ecc71'])
axes[1, 1].set_title('Distress Rate by ESG Risk Tier')
axes[1, 1].set_xlabel('ESG Risk Tier')
axes[1, 1].set_ylabel('Distress Rate')
axes[1, 1].tick_params(axis='x', rotation=0)

plt.suptitle('ESG-Integrated Credit Risk Analysis', fontsize=14, fontweight='bold', y=1.02)
plt.tight_layout()
plt.savefig('credit_risk_dashboard.png', dpi=150, bbox_inches='tight')
plt.show()

What does each subplot show?

  • Top-left (correlation heatmap): reveals how strongly each ESG pillar correlates with financial metrics. Red-green diverging colors make positive and negative relationships instantly visible.
  • Top-right (box plots): compares ESG pillar score distributions between distressed and healthy companies. If distressed companies cluster at lower ESG scores, you have visual proof of the relationship.
  • Bottom-left (ROC curves): overlays both models on the same axes so reviewers can immediately see the ESG-enhanced model's lift over baseline.
  • Bottom-right (bar chart): shows distress rates escalating from Low to Critical risk tiers, proving that your ESG-based tier classification has real predictive value.
  • Run the cell.
  • You should see a 2x2 figure rendered below the cell with all four subplots populated with data.

The file credit_risk_dashboard.png is saved in your project folder.

Seeing overlapping labels or a blank figure?

  • If labels overlap, make sure you have the plt.tight_layout() call before plt.show().
  • A blank figure usually means you called plt.show() before the plotting commands. Confirm the order matches the code above.
  • Check that y_test and y_pred_baseline still exist from your earlier cells. Re-run Cells 5 and 6 if you restarted the kernel.

Help me debug my matplotlib 2x2 dashboard figure

Create the sector-level analysis

The dashboard shows the overall story. Now you need a sector-specific view that answers: which industries are most exposed to ESG-driven credit risk?

  • Create a new cell (Cell 9) and add the following code to build side-by-side sector visualizations:
# --- Cell 9: Sector Analysis ---
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6))

sector_summary = df.groupby('Industry').agg(
    avg_esg=('ESG', 'mean'),
    distress_rate=('financial_distress', 'mean')
).sort_values('distress_rate', ascending=False)

# Grouped bar chart
x = np.arange(len(sector_summary))
width = 0.35
ax1.bar(x - width/2, sector_summary['avg_esg'] / 100, width, label='Avg ESG (scaled)', color='#3498db')
ax1.bar(x + width/2, sector_summary['distress_rate'], width, label='Distress Rate', color='#e74c3c')
ax1.set_xticks(x)
ax1.set_xticklabels(sector_summary.index, rotation=45, ha='right')
ax1.set_title('ESG Scores vs Distress Rate by Industry')
ax1.legend()

# Scatter plot
ax2.scatter(sector_summary['avg_esg'], sector_summary['distress_rate'],
            s=100, c='#2c3e50', edgecolors='black')
for idx, row in sector_summary.iterrows():
    ax2.annotate(idx, (row['avg_esg'], row['distress_rate']),
                 fontsize=8, ha='center', va='bottom')
ax2.set_xlabel('Average ESG Score')
ax2.set_ylabel('Distress Rate')
ax2.set_title('Sector ESG-Risk Relationship')

plt.tight_layout()
plt.savefig('sector_analysis.png', dpi=150, bbox_inches='tight')
plt.show()

print("\nAnalysis complete. All visualizations saved.")

What does this visualization reveal?

The left panel (grouped bar chart) places ESG scores and distress rates side by side for each industry, sorted by distress rate. Industries where the blue bar is short and the red bar is tall represent sectors where low ESG performance coincides with high financial distress.

The right panel (scatter plot) makes the inverse relationship explicit. If ESG scores genuinely predict credit risk, you should see a downward-sloping pattern with high-ESG sectors clustering in the bottom-right and high-distress sectors in the top-left.

  • Run the cell.
  • You should see a two-panel figure with industry names on the x-axis of the bar chart and annotated dots in the scatter plot.
  • Confirm the terminal prints Analysis complete. All visualizations saved.

Industry labels cut off or overlapping?

  • Make sure rotation=45 and ha='right' are set in the set_xticklabels() call. These angle the labels to prevent overlap.
  • If the scatter annotations overlap, the data has sectors with similar ESG/distress values. This is expected and does not indicate an error.

Help me fix my sector analysis matplotlib figure

Write the Executive Summary and Methodology markdown cells

Code and charts alone do not tell a story. An interviewer reviewing your notebook needs a clear narrative at the top that frames the business question, the approach, and the punchline.

  • Scroll to the very top of your notebook, above Cell 1.
  • Insert a new cell and change its type from **Code** to **Markdown** using the dropdown in the toolbar.
  • Paste the following Executive Summary content into the markdown cell:
# Executive Summary

## Business Question
Do companies with poor ESG (Environmental, Social, Governance) performance face higher credit risk? Can ESG scores serve as leading indicators of financial distress?

## Methodology
- **Dataset**: 1,000 global companies across 9 industries (2015-2025)
- **Credit Risk Proxy**: Binary distress label based on industry-relative profit margin (bottom 25th percentile) combined with negative growth rate
- **Model Comparison**: Logistic regression with financial-only features vs. financial + ESG features
- **Validation**: Temporal train-test split (train: 2015-2022, test: 2023-2025) mimicking real-world credit model validation

## Key Findings
- Adding ESG features improved ROC-AUC over the financial-only baseline
- ESG risk tier classification (Critical/High/Moderate/Low) shows a clear monotonic relationship with observed distress rates
- Sector-level analysis reveals industries where ESG scores have the strongest predictive signal

## Implications for Credit Risk Management
ESG scores provide incremental predictive power for credit default estimation. Integrating ESG data into existing credit risk frameworks can improve early warning systems for portfolio managers.

Why put the summary at the top?

Interview reviewers often spend less than 60 seconds on a notebook before deciding if it is worth reading in detail. The executive summary gives them your punchline immediately. If the findings are compelling, they will scroll down to see how you got there.

  • Insert another **Markdown** cell directly below the Executive Summary.
  • Paste the following Methodology section:
# Methodology

## Credit Risk Proxy
Since this dataset does not contain actual default labels, we engineer a **financial distress** binary variable. A company-year observation is labeled as distressed (1) if:
1. Its profit margin falls below the 25th percentile for its industry (indicating underperformance vs. peers)
2. Its growth rate is negative (indicating contraction)

This proxy captures companies showing simultaneous margin pressure and revenue decline, consistent with early-stage credit deterioration signals used in Altman Z-score style models.

## Temporal Train-Test Split
We split data chronologically rather than randomly:
- **Training set**: 2015-2022 (historical data)
- **Test set**: 2023-2025 (future periods)

This mirrors CCAR/CECL regulatory stress testing practices where models must demonstrate out-of-time predictive power. A random split would overstate model performance by allowing future data to leak into training.

## Model Comparison Approach
We build two logistic regression models:
1. **Baseline**: Financial features only (leverage proxy, margin volatility, size decile, profit margin, growth rate, revenue)
2. **ESG-Enhanced**: Financial features + ESG features (overall ESG, E/S/G pillars, ESG momentum, industry-relative ESG)

By comparing ROC-AUC scores, we isolate the incremental predictive value of ESG data for credit risk estimation.

What makes this methodology section effective?

It explains three decisions a reviewer would question: why you used a proxy instead of real defaults, why you split by time instead of randomly, and why you chose logistic regression. Each answer references an industry practice (Altman Z-score, CCAR/CECL, interpretability) rather than just a technical preference.

  • Run both markdown cells by pressing **Shift+Enter** on each.
  • You should see the headings, bullet points, and bold text rendered as formatted markdown at the top of your notebook.
  • Save your notebook.

Customize the findings with your actual numbers

After running all your code cells, go back to the Executive Summary and replace the generic "improved ROC-AUC" language with your actual numbers (e.g., "Adding ESG features improved ROC-AUC from 0.62 to 0.67, a +8% lift"). Specific numbers are far more credible than vague claims.

Markdown not rendering?

  • Make sure you changed the cell type to **Markdown** (not Code) using the dropdown in the Jupyter toolbar.
  • Press **Shift+Enter** to execute the markdown cell. Unexecuted markdown cells display raw text with hash marks.

Help me format my Jupyter markdown cells

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all cells executed.

ⓧ I'd like to double check the full code

Here is the complete code for Cells 8 and 9. Compare it against what you have in your notebook:

# --- Cell 8: Professional Visualizations (2x2 Grid) ---
fig, axes = plt.subplots(2, 2, figsize=(14, 12))

# Plot 1: Correlation heatmap
corr_cols = ['ESG', 'ESG_Environmental', 'ESG_Social', 'ESG_Governance',
             'ProfitMargin', 'GrowthRate', 'Revenue', 'MarketCap']
corr_matrix = df[corr_cols].corr()
sns.heatmap(corr_matrix, annot=True, fmt='.2f', cmap='RdYlGn',
            center=0, ax=axes[0, 0], square=True)
axes[0, 0].set_title('ESG vs Financial Metrics Correlation')

# Plot 2: ESG scores by distress status
esg_by_distress = df.melt(
    id_vars=['financial_distress'],
    value_vars=['ESG_Environmental', 'ESG_Social', 'ESG_Governance'],
    var_name='ESG Pillar', value_name='Score'
)
sns.boxplot(data=esg_by_distress, x='ESG Pillar', y='Score',
            hue='financial_distress', ax=axes[0, 1], palette='Set2')
axes[0, 1].set_title('ESG Pillar Scores: Distressed vs Healthy')
axes[0, 1].legend(title='Distressed', labels=['No', 'Yes'])

# Plot 3: ROC curves comparison
fpr_base, tpr_base, _ = roc_curve(y_test, y_pred_baseline)
fpr_esg, tpr_esg, _ = roc_curve(y_test, y_pred_esg)
axes[1, 0].plot(fpr_base, tpr_base, label=f'Baseline (AUC={auc_baseline:.3f})', linewidth=2)
axes[1, 0].plot(fpr_esg, tpr_esg, label=f'ESG-Enhanced (AUC={auc_esg:.3f})', linewidth=2)
axes[1, 0].plot([0, 1], [0, 1], 'k--', alpha=0.5)
axes[1, 0].set_xlabel('False Positive Rate')
axes[1, 0].set_ylabel('True Positive Rate')
axes[1, 0].set_title('ROC Curve Comparison')
axes[1, 0].legend()

# Plot 4: Distress rate by risk tier
tier_distress = df.copy()
tier_distress['risk_tier'] = pd.cut(
    tier_distress['ESG'],
    bins=[0, 30, 50, 70, 100],
    labels=['Critical', 'High', 'Moderate', 'Low']
)
tier_rates = tier_distress.groupby('risk_tier', observed=True)['financial_distress'].mean()
tier_rates.plot(kind='bar', ax=axes[1, 1], color=['#e74c3c', '#f39c12', '#f1c40f', '#2ecc71'])
axes[1, 1].set_title('Distress Rate by ESG Risk Tier')
axes[1, 1].set_xlabel('ESG Risk Tier')
axes[1, 1].set_ylabel('Distress Rate')
axes[1, 1].tick_params(axis='x', rotation=0)

plt.suptitle('ESG-Integrated Credit Risk Analysis', fontsize=14, fontweight='bold', y=1.02)
plt.tight_layout()
plt.savefig('credit_risk_dashboard.png', dpi=150, bbox_inches='tight')
plt.show()

# --- Cell 9: Sector Analysis ---
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6))

sector_summary = df.groupby('Industry').agg(
    avg_esg=('ESG', 'mean'),
    distress_rate=('financial_distress', 'mean')
).sort_values('distress_rate', ascending=False)

# Grouped bar chart
x = np.arange(len(sector_summary))
width = 0.35
ax1.bar(x - width/2, sector_summary['avg_esg'] / 100, width, label='Avg ESG (scaled)', color='#3498db')
ax1.bar(x + width/2, sector_summary['distress_rate'], width, label='Distress Rate', color='#e74c3c')
ax1.set_xticks(x)
ax1.set_xticklabels(sector_summary.index, rotation=45, ha='right')
ax1.set_title('ESG Scores vs Distress Rate by Industry')
ax1.legend()

# Scatter plot
ax2.scatter(sector_summary['avg_esg'], sector_summary['distress_rate'],
            s=100, c='#2c3e50', edgecolors='black')
for idx, row in sector_summary.iterrows():
    ax2.annotate(idx, (row['avg_esg'], row['distress_rate']),
                 fontsize=8, ha='center', va='bottom')
ax2.set_xlabel('Average ESG Score')
ax2.set_ylabel('Distress Rate')
ax2.set_title('Sector ESG-Risk Relationship')

plt.tight_layout()
plt.savefig('sector_analysis.png', dpi=150, bbox_inches='tight')
plt.show()

print("\nAnalysis complete. All visualizations saved.")

Your notebook now has a complete analytical narrative: an executive summary that hooks the reader, a methodology section that builds credibility, and professional visualizations that make your findings impossible to ignore. You have everything you need for a compelling portfolio piece.

Secret mission

Sector-Level ESG Stress Testing

Your model proves ESG scores improve credit risk prediction. But how would your portfolio perform if ESG scores suddenly dropped across the board? Build a CCAR-style stress testing simulation that models ESG deterioration scenarios and quantifies portfolio-level expected loss by sector.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources for portfolio use or delete them entirely.

Resources you used:

  • Project folder esg-credit-risk/ on your Desktop.
  • Kaggle dataset data/esg_financial_performance.csv inside the project folder.
  • SQLite database data/esg_credit_risk.db created from your analysis.
  • Jupyter Notebook esg_credit_risk_analysis.ipynb with all code and visualizations.
  • Saved PNG visualizations: feature_importance.png, credit_risk_dashboard.png, sector_analysis.png, stress_test_results.png.

Keep everything running

No action needed. Choose this if you plan to refine your notebook, add more visualizations, or push it to GitHub as a portfolio piece.

  • Your notebook and SQLite database remain in the esg-credit-risk/ folder on your Desktop.
  • Consider pushing the folder to a public GitHub repository for interview visibility.
  • The Python libraries you installed (pandas, scikit-learn, matplotlib, seaborn, jupyter) remain available for other projects.

Pause - I'll come back to this later

Shut down any running Jupyter Notebook server to free up memory. Your files stay on disk.

  • In the terminal where Jupyter is running, press Ctrl+C twice to stop the server.
  • Your esg-credit-risk/ folder remains intact. Restart anytime with jupyter notebook from inside the folder.

Delete - I don't want to use this again

Remove all project files from your system. This deletes your notebook, database, dataset, and all saved visualizations.

  • In the terminal where Jupyter is running, press Ctrl+C twice to stop the server.
  • Delete the entire project folder by running this command (macOS/Linux):
cd ~/Desktop
rm -rf esg-credit-risk
  • On Windows, run this instead:
cd ~\Desktop
rmdir /s /q esg-credit-risk
  • Confirm the folder is gone by running ls (macOS/Linux) or dir (Windows) on your Desktop. You should no longer see esg-credit-risk in the output.

Nice Work!

Nice Work!

You just built a complete ESG-integrated credit risk analysis from scratch. Your notebook tells a clear analytical story, from raw data through SQL exploration, feature engineering, predictive modeling, and professional visualization.

You've learned how to:

  • Built a SQLite database and wrote expert-level SQL queries using CTEs, window functions, and CASE WHEN risk classification logic to explore ESG-financial relationships.
  • Engineered a credit risk proxy and trained logistic regression models comparing baseline financial-only prediction against an ESG-enhanced model, quantifying the improvement in ROC-AUC.
  • Created publication-quality visualizations including correlation heatmaps, ROC curves, sector analysis charts, and an executive narrative connecting findings to real-world credit risk management.
  • Secret Mission: Built a sector-level stress testing simulation modeling how ESG score deterioration impacts portfolio-wide default probability under CCAR-style scenarios.

Ready to quiz yourself?