Electricity Revenue Loss Analysis

Analyze Nigerian feeder-level energy data with SQL and Python to find revenue leaks.

Introduction

30 Second Summary

Every time someone flips a light switch in southwestern Nigeria, the electricity powering their home has traveled through IBEDC's distribution network. About a third of the revenue from that electricity never makes it back to the company.

In this project, you will build an end-to-end data analytics pipeline that identifies exactly where IBEDC's revenue leaks at the feeder level, validates findings with statistical rigor, and quantifies how much money targeted fixes could recover.

What You'll Build

A MySQL database and Python analytics notebook that decompose feeder-level revenue losses, validate patterns with hypothesis tests and effect sizes, and train a Random Forest model to identify the dominant loss drivers.

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

  • A normalized MySQL database holding 12 months of simulated feeder-level energy and billing data calibrated to real NERC performance benchmarks.
  • SQL queries that validate data integrity and decompose ATC&C losses using the official NERC formula at the feeder level.
  • A Python analytics notebook with statistical tests (t-test, Mann-Whitney, ANOVA), effect sizes, and a trained Random Forest model identifying which feeder characteristics drive revenue loss.
  • Secret Mission: A naira-denominated recovery projection and executive summary written in DisCo language that a non-technical manager could act on.

Are there any prerequisites?

You should be comfortable with basic SQL syntax (SELECT, WHERE, GROUP BY) and have basic Python familiarity. You also need MySQL 8.0 CE, Python 3.14, and VS Code already installed on your machine.

Step 1 walks you through verifying your environment and installing the Python packages this project needs.

Set Up the Environment and Database Schema

Before you can analyze where IBEDC's revenue leaks, you need the tools and data layer in place. ATC&C losses across Nigerian electricity distribution companies averaged nearly 38% in 2025. That means for roughly every 3 naira of electricity delivered, more than 1 naira never comes back.

In this step, you will install the Python analytics libraries, connect to MySQL, and build a normalized database schema that models IBEDC's feeder network. This schema is the foundation every later step builds on.

In this step, get ready to:
  • Install the Python analytics packages (pandas, scipy, scikit-learn, and more).
  • Connect to MySQL and confirm you can sign in.
  • Create the database schema with a dimension table and a fact table.
Install the Python analytics packages

The analysis later in this project relies on several Python libraries for statistics, machine learning, and visualization. You need to install them before any notebooks or scripts can run.

  • Install all required packages by running this command in your VS Code terminal:
pip install mysql-connector-python pandas scipy statsmodels scikit-learn matplotlib seaborn

What do these packages do?

  • mysql-connector-python lets Python talk to your MySQL database.
  • pandas provides DataFrames for tabular data manipulation.
  • scipy and statsmodels supply hypothesis tests and statistical models.
  • scikit-learn provides the Random Forest regressor for predictive modeling.
  • matplotlib and seaborn handle charts and visualizations.
  • Verify the installation succeeded by running:
python -c "import pandas; print(pandas.__version__)"

You should see a version number printed (such as 3.0.3 or similar). This confirms pandas loaded correctly.

Seeing a ModuleNotFoundError?

  • Make sure you ran the pip install command in the same terminal session. If you have multiple Python versions, try pip3 instead of pip.
  • On some systems, VS Code's terminal may not use the expected Python. Check with which python (macOS/Linux) or where python (Windows) to confirm the path.

Help me troubleshoot my pip install failing.

Connect to MySQL

You need an active MySQL session to create the database. Let's sign in as the root user.

  • In your VS Code terminal, connect to MySQL by running:
mysql -u root -p
  • Enter your MySQL root password when prompted.
  • You should see the mysql> prompt, confirming you are signed in.

Access denied or command not found?

  • If you see "Access denied," double-check the root password you set when installing MySQL.
  • If the command is not found, MySQL's bin directory may not be in your system PATH. On macOS, try /usr/local/mysql/bin/mysql -u root -p. On Windows, find the MySQL install folder and add it to your PATH.

Help me connect to MySQL from the terminal.

Create the database schema

The schema separates static feeder attributes from time-varying monthly readings. This is called normalization. A feeder's hub and service band do not change month to month, so storing them once (in a dimension table) and referencing them by ID avoids repeating data across hundreds of rows.

  • In VS Code, create a new file by pressing Cmd+N (macOS) or Ctrl+N (Windows).
  • Save it immediately as schema.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Paste the following code into schema.sql. This creates the database and the feeders dimension table:
-- Stage 1: Problem framing
-- Guiding question: "Where does the money leak, and what is recovering it worth?"
-- Context: IBEDC's ATC&C losses averaged ~33% in 2024 (NERC Q3 2024 report).
-- That means for every 3 naira of electricity delivered, 1 naira never comes back.
-- This database models the feeder-level breakdown of that leakage.

CREATE DATABASE IF NOT EXISTS ibedc_revenue;
USE ibedc_revenue;

-- Dimension table: one row per feeder (static attributes)
-- Why normalize? Hub and band don't change month to month.
-- Repeating them on 600 rows wastes space and risks update anomalies.
CREATE TABLE feeders (
    feeder_id INT PRIMARY KEY,
    feeder_name VARCHAR(50) NOT NULL,
    hub VARCHAR(20) NOT NULL,
    service_band CHAR(1) NOT NULL
);

What does this code do?

  • CREATE DATABASE IF NOT EXISTS ibedc_revenue creates the database only if it does not already exist, making the script safe to re-run.
  • USE ibedc_revenue switches the active database so all following statements target the right schema.
  • The feeders table holds one row per physical feeder. Each feeder belongs to a hub (regional cluster) and a service band (A through E, reflecting hours of supply).
  • Below the feeders table, add the fact table that stores monthly performance readings:
-- Fact table: one row per feeder per month (time-varying measures)
CREATE TABLE monthly_readings (
    reading_id INT AUTO_INCREMENT PRIMARY KEY,
    feeder_id INT NOT NULL,
    month DATE NOT NULL,
    energy_received_mwh DECIMAL(10,2) NOT NULL,
    energy_billed_mwh DECIMAL(10,2) NOT NULL,
    revenue_billed_naira DECIMAL(15,2) NOT NULL,
    revenue_collected_naira DECIMAL(15,2) NOT NULL,
    total_customers INT NOT NULL,
    metered_customers INT NOT NULL,
    FOREIGN KEY (feeder_id) REFERENCES feeders(feeder_id)
);

What does this table capture?

Each row records one feeder's performance for one month. The columns track the full revenue chain: how much energy arrived, how much was billed, how much money was invoiced, and how much was actually collected.

  • FOREIGN KEY (feeder_id) REFERENCES feeders(feeder_id) enforces that every reading must belong to a valid feeder. This prevents orphaned data.
  • energy_received_mwh vs energy_billed_mwh captures billing efficiency (how much of what arrived actually got invoiced).
  • revenue_billed_naira vs revenue_collected_naira captures collection efficiency (how much of what was invoiced actually got paid).

Why two tables instead of one?

If you stored hub and service band on every monthly reading row, you would repeat the same values 12 times per feeder (once per month). That wastes space and creates update anomalies. If a feeder changes hub, you would need to update 12 rows instead of 1.

Separating static attributes (feeders) from time-varying measures (monthly_readings) is a core relational design pattern called normalization. It keeps your data consistent and your queries efficient.

  • Save schema.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Switch to your terminal where the mysql> prompt is active.
  • Run the schema file by entering:
source schema.sql;
  • Confirm both tables were created by running:
SHOW TABLES;

You should see feeders and monthly_readings listed.

  • Verify the fact table structure by running:
DESCRIBE monthly_readings;

You should see all nine columns with their types, plus the MUL key indicator on feeder_id confirming the foreign key relationship.

Seeing ERROR 1049 or file not found?

  • If MySQL says it cannot find schema.sql, make sure you started the mysql session from the same folder where the file is saved. Exit MySQL with exit;, navigate to the correct folder with cd, and reconnect.
  • If you see a syntax error, check that the file was saved completely. Open schema.sql in VS Code and confirm the last line ends with the closing ); of the monthly_readings table.

Help me troubleshoot running my schema.sql file in MySQL.

✔️ Awesome, I've got everything!

Great. Double check you have saved schema.sql and both tables appear in your MySQL session.

ⓧ I'd like to double check the full code

Here is the complete schema.sql file for comparison:

-- Stage 1: Problem framing
-- Guiding question: "Where does the money leak, and what is recovering it worth?"
-- Context: IBEDC's ATC&C losses averaged ~33% in 2024 (NERC Q3 2024 report).
-- That means for every 3 naira of electricity delivered, 1 naira never comes back.
-- This database models the feeder-level breakdown of that leakage.

CREATE DATABASE IF NOT EXISTS ibedc_revenue;
USE ibedc_revenue;

-- Dimension table: one row per feeder (static attributes)
-- Why normalize? Hub and band don't change month to month.
-- Repeating them on 600 rows wastes space and risks update anomalies.
CREATE TABLE feeders (
    feeder_id INT PRIMARY KEY,
    feeder_name VARCHAR(50) NOT NULL,
    hub VARCHAR(20) NOT NULL,
    service_band CHAR(1) NOT NULL
);

-- Fact table: one row per feeder per month (time-varying measures)
CREATE TABLE monthly_readings (
    reading_id INT AUTO_INCREMENT PRIMARY KEY,
    feeder_id INT NOT NULL,
    month DATE NOT NULL,
    energy_received_mwh DECIMAL(10,2) NOT NULL,
    energy_billed_mwh DECIMAL(10,2) NOT NULL,
    revenue_billed_naira DECIMAL(15,2) NOT NULL,
    revenue_collected_naira DECIMAL(15,2) NOT NULL,
    total_customers INT NOT NULL,
    metered_customers INT NOT NULL,
    FOREIGN KEY (feeder_id) REFERENCES feeders(feeder_id)
);

Your data layer is ready. Next up, you will generate realistic synthetic data calibrated to actual NERC performance benchmarks and load it into these tables.

Generate and Load Synthetic Data

Your database has the structure, but the tables are empty. Before any analysis can begin, you need data you can trust.

In this step, you will generate realistic synthetic data using Python's numpy distributions, load it into MySQL, and then run SQL audit queries to verify the data is logically sound. This mirrors how analytics teams at Nigerian DisCos actually work: Python builds the data layer, SQL validates it.

In this step, get ready to:
  • Create a Python script that generates ~50 feeders with realistic performance profiles across 6 IBEDC hubs.
  • Load 12 months of simulated readings into your MySQL tables.
  • Run SQL audit queries that confirm no logical violations exist in the data.
Create the data generation script

IBEDC does not publish feeder-level performance data publicly. NERC quarterly reports only aggregate to the DisCo level. Synthetic data calibrated to real NERC benchmarks is the standard approach for analytics projects in regulated industries.

  • In VS Code, create a new file called generate_and_load.py in the same folder as your schema.sql.
  • Add the script setup and hub configuration by pasting this code:
"""
Stage 2: Data Generation and Ingestion
Why Python? We need numpy distributions to build structured, realistic data.
SQL can't generate correlated random variables with controlled skew.
"""

import numpy as np
import mysql.connector

# Reproducibility
np.random.seed(42)

# --- Feeder definitions ---
# 6 hubs representing IBEDC regional clusters
# Band distribution reflects reality: fewer A feeders, more C/D/E
hubs_config = {
    "Ibadan": {"feeders": 12, "band_weights": [0.25, 0.25, 0.25, 0.15, 0.10]},
    "Abeokuta": {"feeders": 9, "band_weights": [0.20, 0.20, 0.30, 0.20, 0.10]},
    "Osogbo": {"feeders": 8, "band_weights": [0.15, 0.20, 0.30, 0.20, 0.15]},
    "Ilorin": {"feeders": 8, "band_weights": [0.10, 0.15, 0.25, 0.30, 0.20]},
    "Ogbomoso": {"feeders": 7, "band_weights": [0.10, 0.15, 0.20, 0.30, 0.25]},
    "Ijebu-Ode": {"feeders": 6, "band_weights": [0.20, 0.25, 0.30, 0.15, 0.10]},
}

bands = ["A", "B", "C", "D", "E"]

What does this code do?

The script uses numpy for random distributions and mysql.connector to load data directly into your database.

np.random.seed(42) locks the random number generator so everyone running this script gets identical data. This makes results reproducible.

The hubs_config dictionary defines 6 IBEDC regional clusters with realistic band distributions. Ilorin and Ogbomoso get heavier D/E weighting (worse-served areas), while Ibadan gets more Band A feeders (better infrastructure).

  • Below the bands list, add the simulation parameter dictionaries:

metering_ranges = {
    "A": (0.75, 0.95),
    "B": (0.60, 0.80),
    "C": (0.45, 0.65),
    "D": (0.30, 0.50),
    "E": (0.20, 0.40),
}

billing_eff_ranges = {
    "A": (0.88, 0.96),
    "B": (0.80, 0.90),
    "C": (0.72, 0.84),
    "D": (0.60, 0.76),
    "E": (0.50, 0.68),
}

collection_eff_ranges = {
    "A": (0.85, 0.95),
    "B": (0.75, 0.88),
    "C": (0.65, 0.80),
    "D": (0.55, 0.72),
    "E": (0.45, 0.65),
}

hub_collection_penalty = {
    "Ibadan": 0.00,
    "Abeokuta": -0.02,
    "Osogbo": -0.03,
    "Ilorin": -0.06,
    "Ogbomoso": -0.08,
    "Ijebu-Ode": -0.01,
}

What do these parameters control?

Each dictionary maps a service band (A through E) to a range of values the simulation samples from. Band A feeders get high metering (75-95%) and strong billing efficiency (88-96%). Band E feeders get low metering (20-40%) and weak billing (50-68%).

The hub_collection_penalty applies a systematic offset by region. Ogbomoso gets the harshest penalty (-0.08) because rural hubs historically have worse collection infrastructure. This builds geographic variation into the simulation.

  • Below hub_collection_penalty, add the feeder generation loop:

# --- Generate feeders ---
feeders = []
feeder_id = 1

for hub, config in hubs_config.items():
    n = config["feeders"]
    assigned_bands = np.random.choice(bands, size=n, p=config["band_weights"])
    for i, band in enumerate(assigned_bands):
        feeder_name = f"{hub[:3].upper()}-{band}-{i+1:02d}"
        feeders.append((feeder_id, feeder_name, hub, band))
        feeder_id += 1

What does this loop do?

This iterates through each hub and randomly assigns service bands to its feeders using the weighted probabilities from hubs_config. Each feeder gets a short name like IBA-A-01 (Ibadan, Band A, feeder 1).

The result is a list of ~50 feeders distributed realistically across hubs and bands.

  • Below the feeder generation loop, add the monthly readings generation:

# --- Generate monthly readings ---
months = [f"2024-{m:02d}-01" for m in range(1, 13)]
readings = []

for fid, fname, hub, band in feeders:
    base_metering = np.random.uniform(*metering_ranges[band])
    base_billing = np.random.uniform(*billing_eff_ranges[band])
    base_collection = np.random.uniform(*collection_eff_ranges[band])
    base_collection += hub_collection_penalty[hub]
    base_collection = np.clip(base_collection, 0.30, 0.98)

    if np.random.random() < 0.10:
        base_billing *= np.random.uniform(0.65, 0.80)
        base_collection *= np.random.uniform(0.60, 0.75)
        base_metering *= np.random.uniform(0.50, 0.70)

    base_customers = np.random.randint(800, 5000)
    base_energy = np.random.uniform(15, 80)

    for month in months:
        energy_received = max(5.0, base_energy * np.random.normal(1.0, 0.08))
        billing_eff = np.clip(base_billing * np.random.normal(1.0, 0.03), 0.30, 0.99)
        collection_eff = np.clip(base_collection * np.random.normal(1.0, 0.04), 0.25, 0.99)
        metering_rate = np.clip(base_metering * np.random.normal(1.0, 0.02), 0.10, 0.99)

        energy_billed = energy_received * billing_eff
        tariff_rates = {"A": 225.0, "B": 66.5, "C": 53.8, "D": 36.5, "E": 36.2}
        tariff = tariff_rates[band]
        revenue_billed = energy_billed * 1000 * tariff
        revenue_collected = revenue_billed * collection_eff

        total_customers = int(base_customers * np.random.normal(1.0, 0.01))
        metered_customers = int(total_customers * metering_rate)
        metered_customers = min(metered_customers, total_customers)

        readings.append((
            fid, month,
            round(energy_received, 2),
            round(energy_billed, 2),
            round(revenue_billed, 2),
            round(revenue_collected, 2),
            total_customers,
            metered_customers
        ))

What does this loop do?

For each feeder, the script samples a base performance profile from the band-specific ranges. It then applies the hub penalty and clips values to stay within realistic bounds.

The if np.random.random() < 0.10 block injects "bleeders." About 10% of feeders get dramatically worse billing, collection, and metering. This creates the right-skewed tail that makes the analysis interesting. A few feeders will have ATC&C loss above 50%.

The inner loop generates 12 monthly readings per feeder, adding small random noise around the base values. Tariff rates approximate NERC MYTO 2024 approved rates per band. Band A pays N225/kWh while Band E pays N36.2/kWh.

  • Finally, add the MySQL loading section at the bottom of the file:

# --- Load into MySQL ---
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your-mysql-password-here",
    database="ibedc_revenue"
)
cursor = conn.cursor()

cursor.execute("DELETE FROM monthly_readings")
cursor.execute("DELETE FROM feeders")

cursor.executemany(
    "INSERT INTO feeders (feeder_id, feeder_name, hub, service_band) VALUES (%s, %s, %s, %s)",
    feeders
)

cursor.executemany(
    """INSERT INTO monthly_readings
    (feeder_id, month, energy_received_mwh, energy_billed_mwh,
     revenue_billed_naira, revenue_collected_naira, total_customers, metered_customers)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""",
    readings
)

conn.commit()
print(f"Loaded {len(feeders)} feeders and {len(readings)} monthly readings.")
cursor.close()
conn.close()

What does this section do?

This connects to your ibedc_revenue database and clears any existing rows (making the script safe to re-run). It then bulk-inserts all feeders and readings using executemany() for efficiency.

Replace your-mysql-password-here with the root password you use to log into MySQL.

Why DELETE before INSERT?

The DELETE statements make this script idempotent. If you run it twice, you get the same result instead of duplicate rows. The foreign key constraint requires deleting monthly_readings first (child table) before feeders (parent table).

  • Save generate_and_load.py.

Seeing a syntax error?

Make sure you replaced your-mysql-password-here with your actual MySQL root password. Check that the password string is enclosed in quotes.

help me fix a connection error in my generate_and_load.py script

✔️ Awesome, I've got everything!

Great. Double check you have saved generate_and_load.py before moving on.

ⓧ I'd like to double check the full code

"""
Stage 2: Data Generation and Ingestion
Why Python? We need numpy distributions to build structured, realistic data.
SQL can't generate correlated random variables with controlled skew.
"""

import numpy as np
import mysql.connector

# Reproducibility
np.random.seed(42)

# --- Feeder definitions ---
# 6 hubs representing IBEDC regional clusters
# Band distribution reflects reality: fewer A feeders, more C/D/E
hubs_config = {
    "Ibadan": {"feeders": 12, "band_weights": [0.25, 0.25, 0.25, 0.15, 0.10]},
    "Abeokuta": {"feeders": 9, "band_weights": [0.20, 0.20, 0.30, 0.20, 0.10]},
    "Osogbo": {"feeders": 8, "band_weights": [0.15, 0.20, 0.30, 0.20, 0.15]},
    "Ilorin": {"feeders": 8, "band_weights": [0.10, 0.15, 0.25, 0.30, 0.20]},
    "Ogbomoso": {"feeders": 7, "band_weights": [0.10, 0.15, 0.20, 0.30, 0.25]},
    "Ijebu-Ode": {"feeders": 6, "band_weights": [0.20, 0.25, 0.30, 0.15, 0.10]},
}

bands = ["A", "B", "C", "D", "E"]

metering_ranges = {
    "A": (0.75, 0.95),
    "B": (0.60, 0.80),
    "C": (0.45, 0.65),
    "D": (0.30, 0.50),
    "E": (0.20, 0.40),
}

billing_eff_ranges = {
    "A": (0.88, 0.96),
    "B": (0.80, 0.90),
    "C": (0.72, 0.84),
    "D": (0.60, 0.76),
    "E": (0.50, 0.68),
}

collection_eff_ranges = {
    "A": (0.85, 0.95),
    "B": (0.75, 0.88),
    "C": (0.65, 0.80),
    "D": (0.55, 0.72),
    "E": (0.45, 0.65),
}

hub_collection_penalty = {
    "Ibadan": 0.00,
    "Abeokuta": -0.02,
    "Osogbo": -0.03,
    "Ilorin": -0.06,
    "Ogbomoso": -0.08,
    "Ijebu-Ode": -0.01,
}

# --- Generate feeders ---
feeders = []
feeder_id = 1

for hub, config in hubs_config.items():
    n = config["feeders"]
    assigned_bands = np.random.choice(bands, size=n, p=config["band_weights"])
    for i, band in enumerate(assigned_bands):
        feeder_name = f"{hub[:3].upper()}-{band}-{i+1:02d}"
        feeders.append((feeder_id, feeder_name, hub, band))
        feeder_id += 1

# --- Generate monthly readings ---
months = [f"2024-{m:02d}-01" for m in range(1, 13)]
readings = []

for fid, fname, hub, band in feeders:
    base_metering = np.random.uniform(*metering_ranges[band])
    base_billing = np.random.uniform(*billing_eff_ranges[band])
    base_collection = np.random.uniform(*collection_eff_ranges[band])
    base_collection += hub_collection_penalty[hub]
    base_collection = np.clip(base_collection, 0.30, 0.98)

    if np.random.random() < 0.10:
        base_billing *= np.random.uniform(0.65, 0.80)
        base_collection *= np.random.uniform(0.60, 0.75)
        base_metering *= np.random.uniform(0.50, 0.70)

    base_customers = np.random.randint(800, 5000)
    base_energy = np.random.uniform(15, 80)

    for month in months:
        energy_received = max(5.0, base_energy * np.random.normal(1.0, 0.08))
        billing_eff = np.clip(base_billing * np.random.normal(1.0, 0.03), 0.30, 0.99)
        collection_eff = np.clip(base_collection * np.random.normal(1.0, 0.04), 0.25, 0.99)
        metering_rate = np.clip(base_metering * np.random.normal(1.0, 0.02), 0.10, 0.99)

        energy_billed = energy_received * billing_eff
        tariff_rates = {"A": 225.0, "B": 66.5, "C": 53.8, "D": 36.5, "E": 36.2}
        tariff = tariff_rates[band]
        revenue_billed = energy_billed * 1000 * tariff
        revenue_collected = revenue_billed * collection_eff

        total_customers = int(base_customers * np.random.normal(1.0, 0.01))
        metered_customers = int(total_customers * metering_rate)
        metered_customers = min(metered_customers, total_customers)

        readings.append((
            fid, month,
            round(energy_received, 2),
            round(energy_billed, 2),
            round(revenue_billed, 2),
            round(revenue_collected, 2),
            total_customers,
            metered_customers
        ))

# --- Load into MySQL ---
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your-mysql-password-here",
    database="ibedc_revenue"
)
cursor = conn.cursor()

cursor.execute("DELETE FROM monthly_readings")
cursor.execute("DELETE FROM feeders")

cursor.executemany(
    "INSERT INTO feeders (feeder_id, feeder_name, hub, service_band) VALUES (%s, %s, %s, %s)",
    feeders
)

cursor.executemany(
    """INSERT INTO monthly_readings
    (feeder_id, month, energy_received_mwh, energy_billed_mwh,
     revenue_billed_naira, revenue_collected_naira, total_customers, metered_customers)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""",
    readings
)

conn.commit()
print(f"Loaded {len(feeders)} feeders and {len(readings)} monthly readings.")
cursor.close()
conn.close()
Run the script and verify row counts

With the script saved and your MySQL password in place, it is time to execute it and confirm the data landed correctly.

  • Run the data generation script in your terminal:
python generate_and_load.py
  • You should see output like: Loaded 50 feeders and 600 monthly readings. (the exact count depends on the hub configuration).

Getting a connection refused error?

Make sure your MySQL server is running. On macOS, check System Preferences > MySQL. On Windows, open Services and verify MySQL80 is started.

Also confirm the password in your script matches the one you use with mysql -u root -p.

help me debug why my generate_and_load.py cannot connect to MySQL

  • Verify the data in MySQL by switching to your MySQL session and running these queries:
SELECT COUNT(*) FROM feeders;
SELECT COUNT(*) FROM monthly_readings;
SELECT * FROM monthly_readings LIMIT 5;
  • The first query should return ~50 rows. The second should return ~600 rows (50 feeders x 12 months). The third lets you spot-check that values look reasonable.

What should I see?

The LIMIT 5 query shows sample rows with energy values in MWh, revenue values in naira, and customer counts. Energy received should range roughly 15-80 MWh. Revenue billed should be in the hundreds of thousands to millions of naira depending on the band and tariff.

Run SQL audit queries to validate the data

Before you analyze anything, you need to prove the data is logically sound. In production, a data engineer would run audit queries before any analysis begins. These checks flag impossible values that would corrupt downstream results.

What is the difference between a data error and a genuine extreme?

A data error is an impossible value you must fix or exclude. Energy billed exceeding energy received is physically impossible. Revenue collected exceeding revenue billed means something is broken in the billing system.

A genuine extreme is a feeder that legitimately has very high loss (say, ATC&C of 55%). That is unlikely but not impossible. You keep genuine extremes because they are the feeders you most want to study.

  • Create a new file called validate.sql in the same folder.
  • Add the following audit queries:
-- Stage 2 continued: Data Audit
-- Why SQL? Constraint checking across rows is exactly what WHERE clauses do.
-- These are the queries a data engineer runs before any analysis begins.

USE ibedc_revenue;

-- Check 1: Energy billed should never exceed energy received
SELECT COUNT(*) AS billed_exceeds_received
FROM monthly_readings
WHERE energy_billed_mwh > energy_received_mwh;

-- Check 2: Revenue collected should never exceed revenue billed
SELECT COUNT(*) AS collected_exceeds_billed
FROM monthly_readings
WHERE revenue_collected_naira > revenue_billed_naira;

-- Check 3: Metered customers should never exceed total customers
SELECT COUNT(*) AS metered_exceeds_total
FROM monthly_readings
WHERE metered_customers > total_customers;

-- Check 4: No NULL values in critical columns
SELECT COUNT(*) AS null_violations
FROM monthly_readings
WHERE energy_received_mwh IS NULL
   OR energy_billed_mwh IS NULL
   OR revenue_billed_naira IS NULL
   OR revenue_collected_naira IS NULL
   OR total_customers IS NULL
   OR metered_customers IS NULL;

-- Check 5: No negative values
SELECT COUNT(*) AS negative_violations
FROM monthly_readings
WHERE energy_received_mwh < 0
   OR energy_billed_mwh < 0
   OR revenue_billed_naira < 0
   OR revenue_collected_naira < 0
   OR total_customers < 0
   OR metered_customers < 0;

-- Summary: row count and date range
SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT feeder_id) AS distinct_feeders,
    MIN(month) AS first_month,
    MAX(month) AS last_month
FROM monthly_readings;

What do these checks catch?

  • Checks 1-3 flag physically impossible values (billing more energy than arrived, collecting more than invoiced, metering more people than exist).
  • Check 4 ensures no critical column is NULL, which would break calculations downstream.
  • Check 5 ensures no values are negative, which is meaningless for energy and revenue.
  • The summary query confirms you have the expected 12-month range across all feeders.
  • Save validate.sql.
  • Run the audit queries in your MySQL session:
mysql -u root -p ibedc_revenue < validate.sql
  • Every check should return 0 violations. The summary should show first_month as 2024-01-01 and last_month as 2024-12-01.

Why do all checks return 0?

Because you control the synthetic data. The generation script uses np.clip() and min() to enforce constraints. In a real IBEDC dataset, you would likely find violations and need to document how you treat each type (fix, exclude, or flag for manual review).

Seeing non-zero violation counts?

Re-run generate_and_load.py to regenerate the data from scratch. The script deletes existing rows before inserting, so a fresh run resets everything. If you modified the generation logic, check that you kept the np.clip() calls intact.

help me debug why my validate.sql audit queries are returning non-zero counts

✔️ Awesome, I've got everything!

Great. Double check you have saved validate.sql and confirmed all checks return 0.

ⓧ I'd like to double check the full code

-- Stage 2 continued: Data Audit
-- Why SQL? Constraint checking across rows is exactly what WHERE clauses do.
-- These are the queries a data engineer runs before any analysis begins.

USE ibedc_revenue;

-- Check 1: Energy billed should never exceed energy received
SELECT COUNT(*) AS billed_exceeds_received
FROM monthly_readings
WHERE energy_billed_mwh > energy_received_mwh;

-- Check 2: Revenue collected should never exceed revenue billed
SELECT COUNT(*) AS collected_exceeds_billed
FROM monthly_readings
WHERE revenue_collected_naira > revenue_billed_naira;

-- Check 3: Metered customers should never exceed total customers
SELECT COUNT(*) AS metered_exceeds_total
FROM monthly_readings
WHERE metered_customers > total_customers;

-- Check 4: No NULL values in critical columns
SELECT COUNT(*) AS null_violations
FROM monthly_readings
WHERE energy_received_mwh IS NULL
   OR energy_billed_mwh IS NULL
   OR revenue_billed_naira IS NULL
   OR revenue_collected_naira IS NULL
   OR total_customers IS NULL
   OR metered_customers IS NULL;

-- Check 5: No negative values
SELECT COUNT(*) AS negative_violations
FROM monthly_readings
WHERE energy_received_mwh < 0
   OR energy_billed_mwh < 0
   OR revenue_billed_naira < 0
   OR revenue_collected_naira < 0
   OR total_customers < 0
   OR metered_customers < 0;

-- Summary: row count and date range
SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT feeder_id) AS distinct_feeders,
    MIN(month) AS first_month,
    MAX(month) AS last_month
FROM monthly_readings;

Your database is now loaded with realistic, validated data. Next up, you will write SQL queries that collapse these 600 monthly readings into a single loss profile per feeder.

Engineer Features with SQL

Your data is clean and validated. Now you need to transform 600 raw monthly rows into something an analyst can actually work with: one defensible number per feeder that captures its full loss profile.

In this step, you will write a SQL aggregation query that collapses those 12 monthly observations into a single row per feeder, computing key performance indicators (KPIs) like billing efficiency, collection efficiency, and the official NERC ATC&C loss formula. Then you will pull that result into a Python DataFrame so it is ready for statistical analysis.

In this step, get ready to:
  • Write a SQL query that computes per-feeder KPIs using aggregation and joins.
  • Pull the feeder-level summary into a pandas DataFrame using read_sql().
  • Verify the DataFrame has the right shape and realistic ATC&C loss values.
Write the feature engineering query

Each feeder has 12 monthly rows in monthly_readings. For analysis, you need a single summary row per feeder that captures its average performance over the full year.

Why collapse to one row per feeder?

Monthly observations on the same feeder are not independent. The same infrastructure, same customers, and same theft patterns produce correlated measurements month after month.

Feeding 600 correlated rows into a t-test or Random Forest would violate independence assumptions and inflate statistical significance. One row per feeder gives you ~50 genuinely independent observations.

  • Create a new file called feature_engineering.sql in your project folder in VS Code.
  • Paste the following query that aggregates KPIs per feeder and joins in the static feeder attributes:
-- Stage 3: Feature Engineering
-- Why SQL? Aggregation + joins + derived metrics are what SQL is built for.
-- We collapse 12 monthly rows per feeder into 1 row per feeder.
-- WHY: Monthly observations on the same feeder are NOT independent
-- (same infrastructure, same theft, same metering gaps).
-- Feeding correlated rows into a t-test would violate independence
-- and inflate significance. One row per feeder = ~50 independent observations.

USE ibedc_revenue;

SELECT
    f.feeder_id,
    f.feeder_name,
    f.hub,
    f.service_band,
    SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh) AS billing_efficiency,
    SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira) AS collection_efficiency,
    1 - (
        (SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh))
        * (SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira))
    ) AS atcc_loss,
    SUM(mr.metered_customers) / SUM(mr.total_customers) AS metering_rate,
    SUM(mr.revenue_billed_naira) - SUM(mr.revenue_collected_naira) AS revenue_lost_naira,
    AVG(mr.total_customers) AS avg_customers
FROM monthly_readings mr
JOIN feeders f ON mr.feeder_id = f.feeder_id
GROUP BY f.feeder_id, f.feeder_name, f.hub, f.service_band;

What does this query do?

  • billing_efficiency: what fraction of energy received actually gets billed to customers.
  • collection_efficiency: what fraction of billed revenue actually gets collected.
  • atcc_loss uses the NERC formula: 1 - (billing_efficiency * collection_efficiency). This is the official measure of total loss for Nigerian DisCos.
  • metering_rate: proportion of customers who have meters installed.
  • revenue_lost_naira: the naira gap between what was billed and what was collected across 12 months.
  • The JOIN pulls hub and service_band from the feeders dimension table onto each row so you can group by location and band in Python.
  • Save feature_engineering.sql.
  • Run the query in your MySQL session to verify it executes. You should see ~50 rows, each with a feeder name and its computed KPIs.

Query returning an error?

  • Make sure you included USE ibedc_revenue; at the top. Without it, MySQL does not know which database to query.
  • Check that the GROUP BY clause includes all four non-aggregated columns: f.feeder_id, f.feeder_name, f.hub, f.service_band. MySQL will reject any non-aggregated column missing from the GROUP BY.
  • Confirm your tables have data by running SELECT COUNT(*) FROM monthly_readings; first. It should return ~600.

Help me debug my feature engineering SQL query

✔️ Awesome, I've got everything!

Great. Make sure you have saved feature_engineering.sql before moving on.

ⓧ I'd like to double check the full code

-- Stage 3: Feature Engineering
-- Why SQL? Aggregation + joins + derived metrics are what SQL is built for.
-- We collapse 12 monthly rows per feeder into 1 row per feeder.
-- WHY: Monthly observations on the same feeder are NOT independent
-- (same infrastructure, same theft, same metering gaps).
-- Feeding correlated rows into a t-test would violate independence
-- and inflate significance. One row per feeder = ~50 independent observations.

USE ibedc_revenue;

SELECT
    f.feeder_id,
    f.feeder_name,
    f.hub,
    f.service_band,
    SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh) AS billing_efficiency,
    SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira) AS collection_efficiency,
    1 - (
        (SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh))
        * (SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira))
    ) AS atcc_loss,
    SUM(mr.metered_customers) / SUM(mr.total_customers) AS metering_rate,
    SUM(mr.revenue_billed_naira) - SUM(mr.revenue_collected_naira) AS revenue_lost_naira,
    AVG(mr.total_customers) AS avg_customers
FROM monthly_readings mr
JOIN feeders f ON mr.feeder_id = f.feeder_id
GROUP BY f.feeder_id, f.feeder_name, f.hub, f.service_band;
Export the feature table to Python

SQL built the summary table. Now you need it inside Python where you can run statistical tests and train a machine learning model.

Why switch to Python now?

SQL excels at aggregation and joins. But visualization, hypothesis testing, and machine learning require specialized libraries (matplotlib, scipy, scikit-learn) that SQL does not have.

Using pandas.read_sql() lets you run the exact same query against MySQL and land the result directly in a DataFrame. No intermediate CSV files needed.

  • Create a new Jupyter Notebook by pressing Cmd+Shift+P (macOS) or Ctrl+Shift+P (Windows) to open the Command Palette.
  • Type Jupyter: Create New Jupyter Notebook and press Enter.
  • Save the notebook as analysis.ipynb in your project folder.
  • When prompted to select a kernel, choose the Python 3.14 interpreter you verified in Step 1.
  • In the first cell, paste the following code that imports your libraries and pulls the feeder summary into a DataFrame:
# Cell 1: Setup and Data Import

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from scipy import stats
from statsmodels.stats.multicomp import pairwise_tukeyhsd
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score, mean_absolute_error
from sklearn.preprocessing import OneHotEncoder
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your-mysql-password-here",
    database="ibedc_revenue"
)

query = """
SELECT
    f.feeder_id,
    f.feeder_name,
    f.hub,
    f.service_band,
    SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh) AS billing_efficiency,
    SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira) AS collection_efficiency,
    1 - (
        (SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh))
        * (SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira))
    ) AS atcc_loss,
    SUM(mr.metered_customers) / SUM(mr.total_customers) AS metering_rate,
    SUM(mr.revenue_billed_naira) - SUM(mr.revenue_collected_naira) AS revenue_lost_naira,
    AVG(mr.total_customers) AS avg_customers
FROM monthly_readings mr
JOIN feeders f ON mr.feeder_id = f.feeder_id
GROUP BY f.feeder_id, f.feeder_name, f.hub, f.service_band
"""

df = pd.read_sql(query, conn)
conn.close()

print(f"Shape: {df.shape}")
print(f"ATC&C loss range: {df['atcc_loss'].min():.3f} to {df['atcc_loss'].max():.3f}")
df.head()

What does this cell do?

  • The imports bring in every library you will use across the entire notebook: pandas for DataFrames, scipy for hypothesis tests, scikit-learn for the Random Forest model, and matplotlib/seaborn for plots.
  • mysql.connector.connect() opens a connection to your local MySQL database.
  • The query string is the same aggregation from feature_engineering.sql. Embedding it here means you get a fresh pull every time you run the cell.
  • pd.read_sql() executes the query and returns the result as a DataFrame df with ~50 rows.
  • The final print statements confirm the shape and that atcc_loss values fall in a realistic range for Nigerian DisCos (0.15 to 0.65).
  • Replace your-mysql-password-here with your actual MySQL root password.
  • Run the cell by pressing Shift+Enter.
  • You should see output like Shape: (50, 10) and an atcc_loss range between 0.15 and 0.65, followed by the first 5 rows of the DataFrame.

Cell throwing a connection error?

  • Confirm MySQL is still running. Return to your terminal and run mysql -u root -p to check you can connect.
  • Double-check the password value in the connection matches your actual MySQL root password.
  • If you see an import error for any library, your kernel may be pointing to the wrong Python. Click the kernel name in the top right of the notebook and select the Python 3.14 interpreter where you installed packages in Step 1.

Help me fix my Jupyter Notebook MySQL connection

✔️ Awesome, I've got everything!

Great. Make sure your notebook is saved and the cell output shows ~50 rows with realistic ATC&C loss values.

ⓧ I'd like to double check the full code

# Cell 1: Setup and Data Import

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from scipy import stats
from statsmodels.stats.multicomp import pairwise_tukeyhsd
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score, mean_absolute_error
from sklearn.preprocessing import OneHotEncoder
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your-mysql-password-here",
    database="ibedc_revenue"
)

query = """
SELECT
    f.feeder_id,
    f.feeder_name,
    f.hub,
    f.service_band,
    SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh) AS billing_efficiency,
    SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira) AS collection_efficiency,
    1 - (
        (SUM(mr.energy_billed_mwh) / SUM(mr.energy_received_mwh))
        * (SUM(mr.revenue_collected_naira) / SUM(mr.revenue_billed_naira))
    ) AS atcc_loss,
    SUM(mr.metered_customers) / SUM(mr.total_customers) AS metering_rate,
    SUM(mr.revenue_billed_naira) - SUM(mr.revenue_collected_naira) AS revenue_lost_naira,
    AVG(mr.total_customers) AS avg_customers
FROM monthly_readings mr
JOIN feeders f ON mr.feeder_id = f.feeder_id
GROUP BY f.feeder_id, f.feeder_name, f.hub, f.service_band
"""

df = pd.read_sql(query, conn)
conn.close()

print(f"Shape: {df.shape}")
print(f"ATC&C loss range: {df['atcc_loss'].min():.3f} to {df['atcc_loss'].max():.3f}")
df.head()

Your feeder-level analytics table is loaded and ready. Next up, you will visualize the loss landscape to see where the worst bleeders are hiding.

Explore the Loss Landscape

In the previous step, you engineered a feeder-level summary table using SQL and pulled it into a pandas DataFrame. You now have ~50 rows of clean, aggregated KPIs ready for analysis.

Before running any statistical tests, you need to understand what the loss landscape looks like. Visualizing the distribution of ATC&C loss across feeders will reveal whether the problem is spread evenly or concentrated in a few bad actors. This determines the entire intervention strategy.

In this step, get ready to:
  • Plot the distribution of ATC&C loss and identify the skew pattern.
  • Create comparison plots by service band and hub to locate the worst segments.
  • State a testable hypothesis about what drives the revenue leak.
Plot the distribution of ATC&C loss

The first question any analyst asks about a metric is: "What does the distribution look like?" A histogram with a KDE (kernel density estimate) overlay shows you the shape. The mean versus median comparison reveals whether the data is symmetric or skewed.

  • In your Jupyter Notebook, create a new cell below Cell 1 and add the following code:
# Cell 2: Stage 4 - Distribution of ATC&C Loss
"""
Business question: What does the loss landscape look like?
"""

fig, ax = plt.subplots(figsize=(10, 5))
sns.histplot(df["atcc_loss"], kde=True, bins=15, ax=ax)
ax.axvline(df["atcc_loss"].mean(), color="red", linestyle="--", label=f'Mean: {df["atcc_loss"].mean():.3f}')
ax.axvline(df["atcc_loss"].median(), color="green", linestyle="--", label=f'Median: {df["atcc_loss"].median():.3f}')
ax.set_xlabel("ATC&C Loss")
ax.set_title("Distribution of Feeder-Level ATC&C Loss")
ax.legend()
plt.tight_layout()
plt.savefig("atcc_distribution.png", dpi=150)
plt.show()

# The mean > median tells us the distribution is RIGHT-SKEWED.
# Interpretation: most feeders are "OK-ish" but a tail of bleeders
# drags the average up. This means the problem is CONCENTRATED,
# not uniform. Targeted intervention beats blanket policy.
print(f"Mean: {df['atcc_loss'].mean():.4f}")
print(f"Median: {df['atcc_loss'].median():.4f}")
print(f"Skewness: {df['atcc_loss'].skew():.4f}")

What does this code do?

This cell creates a histogram with a smooth density curve overlaid, showing how ATC&C loss values are distributed across all ~50 feeders.

  • The red dashed line marks the mean (average) loss. The green dashed line marks the median (middle value).
  • When mean > median, the distribution is right-skewed. A small number of extreme "bleeder" feeders drag the average up.
  • The plt.savefig() call saves the chart as atcc_distribution.png in your project folder for later reference.
  • Run the cell by pressing Shift+Enter.
  • You should see a histogram with a smooth curve, two vertical dashed lines (red for mean, green for median), and printed statistics below the chart showing mean, median, and skewness values.

What does right-skew mean for IBEDC?

Right-skew means the median feeder has acceptable losses, but a tail of 5-8 extreme feeders drags the network average up. The problem is concentrated, not uniform.

This is good news for IBEDC. Targeted intervention on a handful of feeders beats an expensive blanket policy rolled out across all 50.

Chart not showing?

  • Make sure you ran Cell 1 first. Cell 2 depends on the df DataFrame and the imported libraries from Cell 1.
  • If you see a NameError for sns or plt, go back and run Cell 1 to re-import everything.

Why isn't my histogram showing in Jupyter?

Create comparison plots by band and hub

Now that you know the overall distribution is skewed, the next question is: where do the worst feeders cluster? Boxplots split by service band and hub will reveal whether loss patterns follow NERC's band structure or geographic boundaries.

  • Create a new cell below Cell 2 and add this code to generate side-by-side boxplots:
# Cell 3: Stage 4 - Loss by Band and Hub
"""
Boxplots showing loss patterns across service bands and hubs.
"""

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# By service band
band_order = ["A", "B", "C", "D", "E"]
sns.boxplot(data=df, x="service_band", y="atcc_loss", order=band_order, ax=axes[0])
axes[0].set_title("ATC&C Loss by Service Band")
axes[0].set_xlabel("Service Band")
axes[0].set_ylabel("ATC&C Loss")

# By hub
sns.boxplot(data=df, x="hub", y="atcc_loss", ax=axes[1])
axes[1].set_title("ATC&C Loss by Hub")
axes[1].set_xlabel("Hub")
axes[1].set_ylabel("ATC&C Loss")
axes[1].tick_params(axis="x", rotation=30)

plt.tight_layout()
plt.savefig("loss_by_band_hub.png", dpi=150)
plt.show()

What does this code do?

  • The left boxplot groups feeders by NERC service band (A through E). Each box shows the median, interquartile range, and outliers for that band's loss values.
  • The right boxplot groups feeders by hub (geographic cluster). This reveals whether some IBEDC regions systematically underperform.
  • The band_order list ensures bands display in logical order (A to E) rather than alphabetical default.
  • Run the cell with Shift+Enter.
  • You should see two boxplots side by side. Band A feeders should cluster toward low loss (left side of the y-axis). Band D and E feeders should sit noticeably higher.

Boxplots look empty or have only one box?

  • Check that your df has multiple unique values in the service_band column by running df["service_band"].value_counts(). You should see counts for bands A through E.
  • If the hub labels overlap on the x-axis, the rotation=30 parameter should handle it. Make sure it is applied to axes[1], not axes[0].

Help me debug my seaborn boxplots in Jupyter.

Now you need a scatter plot that tests the visual relationship between metering and revenue collection. This is the plot that connects the two variables before formal statistical tests.

  • Create a new cell below Cell 3 and add this code to generate the scatter plot:
# Cell 4: Stage 4 - Metering Rate vs Collection Efficiency
"""
Scatter plot testing our visual intuition before formal stats.
"""

fig, ax = plt.subplots(figsize=(9, 6))
scatter = ax.scatter(
    df["metering_rate"],
    df["collection_efficiency"],
    c=df["service_band"].map({"A": 0, "B": 1, "C": 2, "D": 3, "E": 4}),
    cmap="RdYlGn_r",
    s=60,
    alpha=0.7
)
ax.set_xlabel("Metering Rate")
ax.set_ylabel("Collection Efficiency")
ax.set_title("Metering Rate vs Collection Efficiency (colored by band)")

# Annotate the 5 worst feeders
worst_5 = df.nlargest(5, "atcc_loss")
for _, row in worst_5.iterrows():
    ax.annotate(row["feeder_name"], (row["metering_rate"], row["collection_efficiency"]),
                fontsize=7, alpha=0.8)

plt.colorbar(scatter, label="Band (0=A, 4=E)")
plt.tight_layout()
plt.savefig("metering_vs_collection.png", dpi=150)
plt.show()

# HYPOTHESIS (stated explicitly):
# "Low metering rate drives high ATC&C loss."
# Everything in Stage 5 tries to KILL this claim.
# If it survives, we have evidence. If it dies, we learn something more interesting.

What does this code do?

  • Each dot represents one feeder. The x-axis is metering rate. The y-axis is collection efficiency. Color maps to service band (green = Band A, red = Band E).
  • The df.nlargest(5, "atcc_loss") call identifies the 5 worst-performing feeders. Their names get annotated on the chart so you can visually trace who the bleeders are.
  • A downward-right trend (higher metering = higher collection) supports the hypothesis that metering drives revenue recovery.
  • Run the cell with Shift+Enter.
  • You should see a scatter plot with dots colored along a red-green spectrum. Look for a negative trend (dots slope from lower-left to upper-right) with meaningful scatter (not a perfect line). The 5 worst feeders should be labelled in the lower-left corner.

All dots appear the same color?

  • Make sure the c= parameter uses .map() to convert band letters to numeric values. Without the mapping, matplotlib cannot interpret string values as a color scale.
  • If feeder name annotations overlap badly, that is expected with dense clusters. The important thing is identifying which corner the worst feeders occupy.

Help me fix the color mapping in my scatter plot.

State the central hypothesis

Look at the comments at the bottom of Cell 4. They state the central hypothesis explicitly: "Low metering rate drives high ATC&C loss."

Everything in the next step tries to kill this claim with formal statistics. If the hypothesis survives, you have evidence that IBEDC can act on. If it dies, you learn something more interesting about what actually drives the leak.

Why state the hypothesis before testing?

Writing the hypothesis down before running tests prevents "p-hacking." When you decide what to test after seeing the results, you can always find something that looks significant by chance. Stating your claim first makes the analysis honest.

Your three plots built the visual case. The scatter shows a negative trend between metering and loss. The boxplots show worse bands have worse loss. The histogram shows the problem is concentrated. Now you need numbers to prove these patterns are not just noise.

✔️ Awesome, I've got everything!

Great. Make sure your notebook is saved and all three charts rendered successfully.

ⓧ I'd like to double check the full code

Here are Cells 2 through 4 for comparison. Cell 1 remains unchanged from the previous step.

# Cell 2: Stage 4 - Distribution of ATC&C Loss
"""
Business question: What does the loss landscape look like?
"""

fig, ax = plt.subplots(figsize=(10, 5))
sns.histplot(df["atcc_loss"], kde=True, bins=15, ax=ax)
ax.axvline(df["atcc_loss"].mean(), color="red", linestyle="--", label=f'Mean: {df["atcc_loss"].mean():.3f}')
ax.axvline(df["atcc_loss"].median(), color="green", linestyle="--", label=f'Median: {df["atcc_loss"].median():.3f}')
ax.set_xlabel("ATC&C Loss")
ax.set_title("Distribution of Feeder-Level ATC&C Loss")
ax.legend()
plt.tight_layout()
plt.savefig("atcc_distribution.png", dpi=150)
plt.show()

# The mean > median tells us the distribution is RIGHT-SKEWED.
# Interpretation: most feeders are "OK-ish" but a tail of bleeders
# drags the average up. This means the problem is CONCENTRATED,
# not uniform. Targeted intervention beats blanket policy.
print(f"Mean: {df['atcc_loss'].mean():.4f}")
print(f"Median: {df['atcc_loss'].median():.4f}")
print(f"Skewness: {df['atcc_loss'].skew():.4f}")
# Cell 3: Stage 4 - Loss by Band and Hub
"""
Boxplots showing loss patterns across service bands and hubs.
"""

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# By service band
band_order = ["A", "B", "C", "D", "E"]
sns.boxplot(data=df, x="service_band", y="atcc_loss", order=band_order, ax=axes[0])
axes[0].set_title("ATC&C Loss by Service Band")
axes[0].set_xlabel("Service Band")
axes[0].set_ylabel("ATC&C Loss")

# By hub
sns.boxplot(data=df, x="hub", y="atcc_loss", ax=axes[1])
axes[1].set_title("ATC&C Loss by Hub")
axes[1].set_xlabel("Hub")
axes[1].set_ylabel("ATC&C Loss")
axes[1].tick_params(axis="x", rotation=30)

plt.tight_layout()
plt.savefig("loss_by_band_hub.png", dpi=150)
plt.show()
# Cell 4: Stage 4 - Metering Rate vs Collection Efficiency
"""
Scatter plot testing our visual intuition before formal stats.
"""

fig, ax = plt.subplots(figsize=(9, 6))
scatter = ax.scatter(
    df["metering_rate"],
    df["collection_efficiency"],
    c=df["service_band"].map({"A": 0, "B": 1, "C": 2, "D": 3, "E": 4}),
    cmap="RdYlGn_r",
    s=60,
    alpha=0.7
)
ax.set_xlabel("Metering Rate")
ax.set_ylabel("Collection Efficiency")
ax.set_title("Metering Rate vs Collection Efficiency (colored by band)")

# Annotate the 5 worst feeders
worst_5 = df.nlargest(5, "atcc_loss")
for _, row in worst_5.iterrows():
    ax.annotate(row["feeder_name"], (row["metering_rate"], row["collection_efficiency"]),
                fontsize=7, alpha=0.8)

plt.colorbar(scatter, label="Band (0=A, 4=E)")
plt.tight_layout()
plt.savefig("metering_vs_collection.png", dpi=150)
plt.show()

# HYPOTHESIS (stated explicitly):
# "Low metering rate drives high ATC&C loss."
# Everything in Stage 5 tries to KILL this claim.
# If it survives, we have evidence. If it dies, we learn something more interesting.

You now have a clear visual picture of IBEDC's loss landscape. The distribution is skewed, the worst feeders cluster in lower bands and specific hubs, and metering rate looks like a strong predictor. Next, you will put these visual patterns through formal statistical tests to determine whether they hold up under scrutiny.

Validate Findings Statistically and Train the Model

Your exploratory charts revealed a clear pattern: under-metered feeders cluster toward high ATC&C loss, and the distribution is right-skewed with a tail of bleeders. But patterns in charts can be noise.

In this step, you will put the hypothesis through formal statistical tests designed to kill it. If the claim survives, you have evidence. You will also train a machine learning model that identifies which feeder characteristics dominate the leak.

In this step, get ready to:
  • Run correlation and hypothesis tests to validate the metering-loss relationship.
  • Test band-level differences with ANOVA and post-hoc analysis.
  • Train a Random Forest model to identify the dominant loss drivers.
Run correlation analysis and hypothesis tests

The scatter plot from the previous step showed a visual trend. Now you need numbers. Two correlation metrics will quantify the strength of the metering-loss relationship, and hypothesis tests will determine whether the difference between under-metered and well-metered feeders is statistically significant.

  • In your Jupyter Notebook, create a new cell (Cell 5) and paste the following correlation analysis code:
# Cell 5: Stage 5a - Correlation Analysis
"""
Business question: Is the metering-loss relationship real and how strong?
Pearson = linear relationship. Spearman = monotonic/rank relationship.
If they disagree, that's a finding: evidence of non-linearity.
"""

pearson_r, pearson_p = stats.pearsonr(df["metering_rate"], df["atcc_loss"])
spearman_r, spearman_p = stats.spearmanr(df["metering_rate"], df["atcc_loss"])

print("=== Correlation: metering_rate vs atcc_loss ===")
print(f"Pearson r:  {pearson_r:.4f}  (p = {pearson_p:.2e})")
print(f"Spearman r: {spearman_r:.4f}  (p = {spearman_p:.2e})")
print()

if abs(spearman_r) > abs(pearson_r) + 0.05:
    print("FINDING: Spearman > Pearson suggests a non-linear monotonic relationship.")
    print("This justifies using Random Forest (captures non-linearity) over linear regression.")
else:
    print("Pearson and Spearman are close: relationship is approximately linear.")

What does this code do?

This cell computes two different correlation coefficients between metering rate and ATC&C loss.

  • Pearson r measures the linear relationship between two variables. A value near -1 means as one goes up, the other goes down in a straight-line pattern.
  • Spearman r measures the monotonic (rank-based) relationship. It catches non-linear trends that Pearson misses.
  • If Spearman is noticeably stronger than Pearson, that signals non-linearity, which justifies using a Random Forest later instead of simple linear regression.
  • Run the cell.
  • You should see both Pearson and Spearman r values as moderate-to-strong negatives (around -0.5 to -0.8), confirming that higher metering rate correlates with lower loss.

Seeing a NameError for stats?

Make sure Cell 1 ran successfully. The from scipy import stats import lives there. Re-run Cell 1, then re-run Cell 5.

Help me debug this import issue

The correlation tells you the relationship exists. Now you need to test whether the difference between groups is statistically significant. A hypothesis test answers: could this pattern have appeared by chance?

  • Create a new cell (Cell 6) and paste the following hypothesis testing code:
# Cell 6: Stage 5b - Hypothesis Tests
"""
Split feeders at median metering rate.
Test whether the two groups have genuinely different ATC&C loss.
"""

median_metering = df["metering_rate"].median()
under_metered = df[df["metering_rate"] < median_metering]["atcc_loss"]
well_metered = df[df["metering_rate"] >= median_metering]["atcc_loss"]

print(f"Under-metered group: n={len(under_metered)}, mean loss={under_metered.mean():.4f}")
print(f"Well-metered group:  n={len(well_metered)}, mean loss={well_metered.mean():.4f}")
print()

# Levene's test for equal variance
levene_stat, levene_p = stats.levene(under_metered, well_metered)
print(f"Levene's test: F={levene_stat:.3f}, p={levene_p:.4f}")
equal_var = levene_p > 0.05
print(f"Equal variance assumed: {equal_var}")
print()

# Welch's t-test (use equal_var=False if Levene's is significant)
t_stat, t_p = stats.ttest_ind(under_metered, well_metered, equal_var=equal_var)
print(f"{'Students' if equal_var else 'Welchs'} t-test: t={t_stat:.3f}, p={t_p:.2e}")

# Cohen's d (effect size)
pooled_std = np.sqrt(
    ((len(under_metered) - 1) * under_metered.std()**2 +
     (len(well_metered) - 1) * well_metered.std()**2) /
    (len(under_metered) + len(well_metered) - 2)
)
cohens_d = (under_metered.mean() - well_metered.mean()) / pooled_std
print(f"Cohen's d: {cohens_d:.3f}")
print()

# Interpretation guide:
# p-value: probability of seeing data this extreme IF the null (no difference) were true
# Cohen's d: |d| < 0.2 = trivial, 0.2-0.5 = small, 0.5-0.8 = medium, > 0.8 = large
if abs(cohens_d) > 0.8:
    print("LARGE practical effect: the metering gap produces a meaningful loss difference.")
elif abs(cohens_d) > 0.5:
    print("MEDIUM practical effect: metering matters, but other factors contribute too.")
else:
    print("SMALL practical effect: statistically significant but practically modest.")

print()

# Mann-Whitney U (non-parametric, does not assume normal distributions)
u_stat, u_p = stats.mannwhitneyu(under_metered, well_metered, alternative="two-sided")
print(f"Mann-Whitney U: U={u_stat:.1f}, p={u_p:.2e}")
print("(Non-parametric confirmation: agrees with t-test if both p-values are small)")

What does this code do?

This cell splits feeders into two groups at the median metering rate, then runs three tests to determine whether their ATC&C loss genuinely differs.

  • Levene's test checks if both groups have similar variance. This determines which version of the t-test to use.
  • Welch's t-test reports a p-value: the probability of seeing data this extreme if the null hypothesis (no difference) were true. A tiny p-value means the difference is unlikely noise.
  • Cohen's d is the effect size. It tells you whether the difference is practically large enough to care about. Values above 0.8 indicate a large practical effect.
  • Mann-Whitney U is a non-parametric fallback. It does not assume normal distributions, so it confirms the t-test result even when data is skewed.
  • Run the cell.
  • You should see Cohen's d exceeding 0.8 (a large effect), confirming that the metering gap produces a meaningful difference in revenue loss.

Cohen's d seems too small?

If you re-ran generate_and_load.py with a different random seed or modified the data generation parameters, the synthetic data will shift. Re-run the original script without changes to ensure the data matches the expected distribution.

Help me understand why my effect size is smaller than expected

Test band-level differences with ANOVA

The t-test compared two groups. But IBEDC has five service bands (A through E). You need a test that compares all five at once, then identifies which specific pairs differ.

  • Create a new cell (Cell 7) and paste the following ANOVA code:
# Cell 7: Stage 5b - ANOVA across Bands
"""
Test: do ALL bands differ in loss, or just some?
One-way ANOVA + Tukey HSD post-hoc.
"""

# Prepare groups
band_groups = [df[df["service_band"] == b]["atcc_loss"].values for b in band_order]
# Remove empty groups
band_groups_clean = [(b, g) for b, g in zip(band_order, band_groups) if len(g) > 0]

f_stat, anova_p = stats.f_oneway(*[g for _, g in band_groups_clean])
print(f"One-way ANOVA: F={f_stat:.3f}, p={anova_p:.2e}")
print()

if anova_p < 0.05:
    print("At least one band differs significantly. Running Tukey HSD post-hoc...")
    tukey = pairwise_tukeyhsd(df["atcc_loss"], df["service_band"], alpha=0.05)
    print(tukey)
else:
    print("No significant difference across bands (consider Kruskal-Wallis as fallback).")

print()
# Kruskal-Wallis (non-parametric ANOVA equivalent)
h_stat, kw_p = stats.kruskal(*[g for _, g in band_groups_clean])
print(f"Kruskal-Wallis: H={h_stat:.3f}, p={kw_p:.2e}")
print("(Non-parametric fallback: robust to non-normality within groups)")

What does this code do?

This cell tests whether ATC&C loss differs significantly across all five service bands, not just between two groups.

  • One-way ANOVA tests whether at least one band has a different mean loss from the others. A significant p-value means "at least one pair differs" but does not say which.
  • Tukey HSD post-hoc runs after ANOVA to identify exactly which band pairs differ. This tells IBEDC which specific bands need intervention.
  • Kruskal-Wallis is the non-parametric equivalent. It does not assume normality within groups, providing a robustness check.

Remember: a p-value is the probability of seeing data this extreme if the null were true. It is NOT the probability that the hypothesis is true.

  • Run the cell.
  • You should see the ANOVA p-value below 0.05, followed by the Tukey table showing which band pairs have significant differences. Look for pairs involving bands D and E showing the largest mean differences.

Seeing a NameError for band_order?

The variable band_order was defined in Cell 3 (your boxplot cell). Make sure Cell 3 ran successfully before running Cell 7.

Help me trace where band_order should be defined

Train a Random Forest to identify loss drivers

Correlation and hypothesis tests confirmed that metering rate matters. But they cannot capture non-linear interactions between multiple features. A Random Forest regressor handles this by learning decision rules across all features simultaneously, then reporting which ones contribute most to predictions.

  • Create a new cell (Cell 8) and paste the following Random Forest code:
# Cell 8: Stage 5c - Random Forest Model
"""
Why Random Forest? The correlation step hinted at non-linearity.
RF captures non-linear interactions between features that
Pearson correlation and t-tests cannot.
"""

# Prepare features
feature_cols = ["metering_rate", "avg_customers"]
categorical_cols = ["service_band", "hub"]

# One-hot encode categoricals
df_encoded = pd.get_dummies(df[categorical_cols], prefix=categorical_cols)
X = pd.concat([df[feature_cols], df_encoded], axis=1)
y = df["atcc_loss"]

# Train/test split (80/20)
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

print(f"Training samples: {len(X_train)}")
print(f"Test samples: {len(X_test)}")
print()

# Train Random Forest
rf = RandomForestRegressor(n_estimators=100, random_state=42, max_depth=5)
rf.fit(X_train, y_train)

# Evaluate
y_pred = rf.predict(X_test)
r2 = r2_score(y_test, y_pred)
mae = mean_absolute_error(y_test, y_pred)

print(f"R-squared (test): {r2:.4f}")
print(f"  -> Interpretation: {r2*100:.1f}% of variance in ATC&C loss is explained by our features")
print(f"MAE (test): {mae:.4f}")
print(f"  -> Interpretation: on average, predictions are off by {mae:.4f} loss points")
print()

# Feature importance (THE PAYOFF)
importances = pd.Series(rf.feature_importances_, index=X.columns).sort_values(ascending=False)
print("=== Feature Importances (top 10) ===")
print(importances.head(10))

# Plot
fig, ax = plt.subplots(figsize=(10, 5))
importances.head(10).plot(kind="barh", ax=ax)
ax.set_xlabel("Importance")
ax.set_title("Random Forest Feature Importances: What Drives ATC&C Loss?")
ax.invert_yaxis()
plt.tight_layout()
plt.savefig("feature_importance.png", dpi=150)
plt.show()

# This tells IBEDC: "metering_rate is the #1 lever you control.
# Fix metering on the worst feeders and you fix the leak."

What does this code do?

This cell trains a Random Forest regressor and reveals which features drive ATC&C loss the most.

  • One-hot encoding converts categorical columns (service_band, hub) into binary columns so the model can use them as numerical inputs.
  • 80/20 train/test split holds out 20% of feeders for evaluation. The model never sees these during training, so test performance reflects real predictive ability.
  • R-squared is the proportion of variance in ATC&C loss explained by the model. Above 0.5 means the model captures meaningful signal.
  • MAE (Mean Absolute Error) is the average prediction error in the same units as loss. It tells you how far off predictions typically land.
  • Feature importances reveal which inputs the model relies on most. This is the payoff: it tells IBEDC which lever to pull.
  • Run the cell.
  • You should see R-squared above 0.5 on the test set, and a horizontal bar chart with metering_rate ranked as the most important feature.

R-squared is very low or negative?

A negative R-squared means the model performs worse than predicting the mean. This usually means your DataFrame df has been altered between cells. Restart your kernel, then re-run all cells from Cell 1 through Cell 8 in order.

Help me debug my Random Forest model

Why Random Forest over linear regression?

The correlation step may have shown that Spearman is stronger than Pearson, signaling non-linearity. A linear model would miss this. Random Forest learns arbitrary decision boundaries between features, capturing interactions that a straight line cannot.

For example, a feeder in Band E with low metering might lose proportionally more than a Band C feeder with the same metering rate. The Random Forest captures this interaction automatically through its tree-splitting mechanism.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all four new cells (5 through 8) executed.

ⓧ I'd like to double check the full code

Here are all four cells added in this step. Cells 1-4 remain unchanged from the previous step.

# Cell 5: Stage 5a - Correlation Analysis
"""
Business question: Is the metering-loss relationship real and how strong?
Pearson = linear relationship. Spearman = monotonic/rank relationship.
If they disagree, that's a finding: evidence of non-linearity.
"""

pearson_r, pearson_p = stats.pearsonr(df["metering_rate"], df["atcc_loss"])
spearman_r, spearman_p = stats.spearmanr(df["metering_rate"], df["atcc_loss"])

print("=== Correlation: metering_rate vs atcc_loss ===")
print(f"Pearson r:  {pearson_r:.4f}  (p = {pearson_p:.2e})")
print(f"Spearman r: {spearman_r:.4f}  (p = {spearman_p:.2e})")
print()

if abs(spearman_r) > abs(pearson_r) + 0.05:
    print("FINDING: Spearman > Pearson suggests a non-linear monotonic relationship.")
    print("This justifies using Random Forest (captures non-linearity) over linear regression.")
else:
    print("Pearson and Spearman are close: relationship is approximately linear.")
# Cell 6: Stage 5b - Hypothesis Tests
"""
Split feeders at median metering rate.
Test whether the two groups have genuinely different ATC&C loss.
"""

median_metering = df["metering_rate"].median()
under_metered = df[df["metering_rate"] < median_metering]["atcc_loss"]
well_metered = df[df["metering_rate"] >= median_metering]["atcc_loss"]

print(f"Under-metered group: n={len(under_metered)}, mean loss={under_metered.mean():.4f}")
print(f"Well-metered group:  n={len(well_metered)}, mean loss={well_metered.mean():.4f}")
print()

# Levene's test for equal variance
levene_stat, levene_p = stats.levene(under_metered, well_metered)
print(f"Levene's test: F={levene_stat:.3f}, p={levene_p:.4f}")
equal_var = levene_p > 0.05
print(f"Equal variance assumed: {equal_var}")
print()

# Welch's t-test (use equal_var=False if Levene's is significant)
t_stat, t_p = stats.ttest_ind(under_metered, well_metered, equal_var=equal_var)
print(f"{'Students' if equal_var else 'Welchs'} t-test: t={t_stat:.3f}, p={t_p:.2e}")

# Cohen's d (effect size)
pooled_std = np.sqrt(
    ((len(under_metered) - 1) * under_metered.std()**2 +
     (len(well_metered) - 1) * well_metered.std()**2) /
    (len(under_metered) + len(well_metered) - 2)
)
cohens_d = (under_metered.mean() - well_metered.mean()) / pooled_std
print(f"Cohen's d: {cohens_d:.3f}")
print()

# Interpretation guide:
# p-value: probability of seeing data this extreme IF the null (no difference) were true
# Cohen's d: |d| < 0.2 = trivial, 0.2-0.5 = small, 0.5-0.8 = medium, > 0.8 = large
if abs(cohens_d) > 0.8:
    print("LARGE practical effect: the metering gap produces a meaningful loss difference.")
elif abs(cohens_d) > 0.5:
    print("MEDIUM practical effect: metering matters, but other factors contribute too.")
else:
    print("SMALL practical effect: statistically significant but practically modest.")

print()

# Mann-Whitney U (non-parametric, does not assume normal distributions)
u_stat, u_p = stats.mannwhitneyu(under_metered, well_metered, alternative="two-sided")
print(f"Mann-Whitney U: U={u_stat:.1f}, p={u_p:.2e}")
print("(Non-parametric confirmation: agrees with t-test if both p-values are small)")
# Cell 7: Stage 5b - ANOVA across Bands
"""
Test: do ALL bands differ in loss, or just some?
One-way ANOVA + Tukey HSD post-hoc.
"""

# Prepare groups
band_groups = [df[df["service_band"] == b]["atcc_loss"].values for b in band_order]
# Remove empty groups
band_groups_clean = [(b, g) for b, g in zip(band_order, band_groups) if len(g) > 0]

f_stat, anova_p = stats.f_oneway(*[g for _, g in band_groups_clean])
print(f"One-way ANOVA: F={f_stat:.3f}, p={anova_p:.2e}")
print()

if anova_p < 0.05:
    print("At least one band differs significantly. Running Tukey HSD post-hoc...")
    tukey = pairwise_tukeyhsd(df["atcc_loss"], df["service_band"], alpha=0.05)
    print(tukey)
else:
    print("No significant difference across bands (consider Kruskal-Wallis as fallback).")

print()
# Kruskal-Wallis (non-parametric ANOVA equivalent)
h_stat, kw_p = stats.kruskal(*[g for _, g in band_groups_clean])
print(f"Kruskal-Wallis: H={h_stat:.3f}, p={kw_p:.2e}")
print("(Non-parametric fallback: robust to non-normality within groups)")
# Cell 8: Stage 5c - Random Forest Model
"""
Why Random Forest? The correlation step hinted at non-linearity.
RF captures non-linear interactions between features that
Pearson correlation and t-tests cannot.
"""

# Prepare features
feature_cols = ["metering_rate", "avg_customers"]
categorical_cols = ["service_band", "hub"]

# One-hot encode categoricals
df_encoded = pd.get_dummies(df[categorical_cols], prefix=categorical_cols)
X = pd.concat([df[feature_cols], df_encoded], axis=1)
y = df["atcc_loss"]

# Train/test split (80/20)
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

print(f"Training samples: {len(X_train)}")
print(f"Test samples: {len(X_test)}")
print()

# Train Random Forest
rf = RandomForestRegressor(n_estimators=100, random_state=42, max_depth=5)
rf.fit(X_train, y_train)

# Evaluate
y_pred = rf.predict(X_test)
r2 = r2_score(y_test, y_pred)
mae = mean_absolute_error(y_test, y_pred)

print(f"R-squared (test): {r2:.4f}")
print(f"  -> Interpretation: {r2*100:.1f}% of variance in ATC&C loss is explained by our features")
print(f"MAE (test): {mae:.4f}")
print(f"  -> Interpretation: on average, predictions are off by {mae:.4f} loss points")
print()

# Feature importance (THE PAYOFF)
importances = pd.Series(rf.feature_importances_, index=X.columns).sort_values(ascending=False)
print("=== Feature Importances (top 10) ===")
print(importances.head(10))

# Plot
fig, ax = plt.subplots(figsize=(10, 5))
importances.head(10).plot(kind="barh", ax=ax)
ax.set_xlabel("Importance")
ax.set_title("Random Forest Feature Importances: What Drives ATC&C Loss?")
ax.invert_yaxis()
plt.tight_layout()
plt.savefig("feature_importance.png", dpi=150)
plt.show()

# This tells IBEDC: "metering_rate is the #1 lever you control.
# Fix metering on the worst feeders and you fix the leak."

You now have rigorous statistical evidence that metering rate is the dominant driver of ATC&C loss, confirmed by correlation, hypothesis tests, and a machine learning model. Next, you will translate these findings into naira and write an executive recommendation that IBEDC can act on.

Secret mission

Recovery Projection and Executive Summary

Your statistical analysis proved where the money leaks. Now translate that into something an IBEDC regional head could present to the board. Build a naira-denominated recovery projection for the worst feeders, then write a 5-sentence executive summary in DisCo language.

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 reference, or delete them to free up disk space.

Resources you used:

  • MySQL database ibedc_revenue with two tables (~600 rows total).
  • Project files: schema.sql, generate_and_load.py, validate.sql, feature_engineering.sql, analysis.ipynb, and four generated .png charts.
  • Python packages: mysql-connector-python, pandas, scipy, statsmodels, scikit-learn, matplotlib, seaborn.

Keep everything running

No action needed. Choose this if you plan to extend the analysis or reference your work later.

  • The ibedc_revenue database persists on your local MySQL server until you drop it. There is no ongoing cost.
  • Your project files remain on disk wherever you saved them.
  • The Python packages remain installed in your Python environment. They take up disk space but cost nothing.

Pause - I'll come back to this later

Since everything is local with no running processes, there is nothing to pause. Your database and files will be waiting when you come back.

  • Close the Jupyter Notebook and VS Code when you are done for the day.
  • The MySQL server continues running in the background. It uses minimal resources.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below.

  • Drop the MySQL database by opening a terminal and running:
mysql -u root -p -e "DROP DATABASE ibedc_revenue;"
  • Delete the project files by running:
rm schema.sql generate_and_load.py validate.sql feature_engineering.sql analysis.ipynb atcc_distribution.png loss_by_band_hub.png metering_vs_collection.png feature_importance.png
  • Uninstall the Python packages by running:
pip uninstall mysql-connector-python pandas scipy statsmodels scikit-learn matplotlib seaborn -y

Keep common packages

Packages like pandas, matplotlib, and scikit-learn are useful for many data projects. Only uninstall them if you are sure you will not need them again.

Nice Work!

Nice Work!

You just built a complete data analytics pipeline that answers a real business question for a Nigerian electricity distribution company: where does the money leak, and what is recovering it worth?

You've learned how to:

  • Build a normalized relational database with realistic synthetic data calibrated to NERC performance benchmarks, then validate its integrity with SQL audit queries.
  • Validate statistical findings with proper hypothesis tests (t-test, Mann-Whitney, ANOVA, Tukey HSD), always pairing p-values with effect sizes to measure practical significance.
  • Train a Random Forest regressor to identify dominant loss drivers, confirming metering rate as the single strongest predictor of ATC&C loss.
  • Secret Mission: Translated statistical findings into a naira-denominated recovery projection with an executive summary a DisCo manager could act on.

Ready to quiz yourself?