Wrangle HCV Data with SQL and Python
Clean real clinical lab data in MySQL and compute descriptive statistics in Python.
Introduction
30 Second Summary
When you look at a set of numbers, your first instinct is to take the average. But on real-world data, the average can lie to you. A handful of extreme values can drag the mean far from what a "typical" value actually looks like.
In this project, you will load real hepatitis C patient lab results into a MySQL database, clean them using SQL, and compute descriptive statistics in Python to discover exactly when the mean misleads you and which measure to trust instead.
What You'll Build
You will run a single Python script that pulls cleaned patient data from MySQL, computes every core statistic, and produces histograms that show you (and anyone you demo it to) exactly why bilirubin's average is a lie but its median tells the truth.
By the end of this project, you'll have:
- A MySQL database loaded with 615 real hepatitis C patient records, cleaned with CTEs and CASE expressions, where you can query any patient's lab values by disease group and see their rank using window functions.
- A Python analysis script that computes mean, median, mode, standard deviation, IQR, and skewness for every biomarker in one run, then prints a decision table telling you which statistic to trust for each one.
- Side-by-side histograms that visually prove why bilirubin's mean is misleading but its median tells the truth, so you can explain to someone else exactly when to use each measure.
- Secret Mission: Build an automated Statistical Profile Report that detects skewness and recommends the appropriate summary statistic for each biomarker.
Are there any prerequisites?
This project assumes you already have Anaconda (Python), VS Code, MySQL 8.0 Community Edition, and mysql-connector-python installed on your machine.
You should also be comfortable writing basic Python scripts and running simple SQL queries. No statistics background is required.
Before We Start
Before we dive in, let's lock in what you're about to build and why it matters.
Picking the wrong summary statistic for your data can lead to completely misleading conclusions. This project gives you the instinct to choose correctly every time.
Download Data and Set Up Your MySQL Database
Before you can practice SQL or statistics on real clinical data, you need the data itself sitting in a queryable database.
In this step, you will download real hepatitis C patient lab results from the UCI Machine Learning Repository and load them into a MySQL database using a Python script.
In this step, get ready to:
- Download the UCI HCV dataset into your project folder.
- Create a Python script that builds a MySQL database and loads the CSV data.
- Verify that all 615 patient rows loaded correctly.
Download the HCV dataset
The dataset you will use contains lab results from 615 patients at the Medical University Hannover. It includes blood donors and patients at various stages of hepatitis C progression.
- Open your browser and navigate to the UCI HCV dataset page:
- https://archive.ics.uci.edu/dataset/571/hcv+data
- Click the Download button on that page to download the dataset.
- Extract the downloaded zip file and locate hcvdat0.csv inside it.
- Move hcvdat0.csv into your project folder.
- Confirm the file is in place by running:
lsYou should see hcvdat0.csv listed in the output.
What is this dataset?
The HCV dataset contains 615 patient records with 12 lab value features (ALB, ALP, ALT, AST, BIL, CHE, CHOL, CREA, GGT, PROT) plus Age and Sex. The Category column classifies each patient as a Blood Donor, Suspect Donor, Hepatitis, Fibrosis, or Cirrhosis case.
Some columns have missing values, which makes it a realistic dataset for practicing data cleaning.
Don't see hcvdat0.csv?
Make sure you extracted the zip file. The download from UCI comes as a compressed archive containing hcvdat0.csv inside. Also confirm you moved the file into the correct folder.
Help me find my downloaded file and move it to my project folder.
Create the database loader script
You will write a Python script that reads the CSV, connects to your MySQL server, creates a database and table, then inserts each patient row. This gives you a clean, queryable starting point for all the SQL work ahead.
- In VS Code, create a new file called setup_db.py in your project folder.
- Add the imports, CSV read, and database connection by pasting this code:
import pandas as pd
import mysql.connector
import numpy as np
# Read the CSV file
df = pd.read_csv('hcvdat0.csv')
# Connect to MySQL server
conn = mysql.connector.connect(
host="localhost",
user="root",
password="your-password-here"
)
cursor = conn.cursor()
# Create database
cursor.execute("CREATE DATABASE IF NOT EXISTS hcv_biochemistry")
cursor.execute("USE hcv_biochemistry")What does this code do?
pandas reads the CSV into a DataFrame so you can iterate over rows easily. mysql.connector opens a connection to your local MySQL server.
CREATE DATABASE IF NOT EXISTS ensures the database is created without erroring if it already exists. This makes the script re-runnable.
- Replace your-password-here with the root password you set when installing MySQL.
- Below the code you just added, paste the table creation:
# Drop table if it exists (for re-runnability)
cursor.execute("DROP TABLE IF EXISTS lab_results")
# Create table with appropriate types
cursor.execute("""
CREATE TABLE lab_results (
patient_id INT PRIMARY KEY,
category VARCHAR(50),
age INT,
sex CHAR(1),
alb FLOAT NULL,
alp FLOAT NULL,
alt FLOAT NULL,
ast FLOAT NULL,
bil FLOAT NULL,
che FLOAT NULL,
chol FLOAT NULL,
crea FLOAT NULL,
ggt FLOAT NULL,
prot FLOAT NULL
)
""")What does this code do?
DROP TABLE IF EXISTS removes any previous version of the table so you can re-run the script cleanly. The CREATE TABLE statement defines each column with its data type.
Lab value columns use FLOAT NULL because some patients have missing values in the original data. The NULL keyword allows those gaps to be stored honestly rather than defaulting to zero.
- Save setup_db.py and run the partial script to confirm your MySQL connection works:
python setup_db.pyThe script should exit with no output and no errors. That silence means it successfully connected to MySQL and created the database and table.
- Verify the empty table exists by running this in your terminal:
mysql -u root -p -e "SHOW TABLES IN hcv_biochemistry;"Enter your MySQL password when prompted. You should see lab_results listed as a table.
Seeing Access denied or connection refused?
Confirm your MySQL server is running. On macOS, check System Settings > MySQL and ensure the server is active. On Windows, open Services and confirm the MySQL service is running.
Double-check that the password in your script matches the root password you set during MySQL installation.
Help me fix my MySQL connection error.
- Back in setup_db.py in VS Code, add the data insertion logic below the CREATE TABLE block:
# Insert data row by row
for idx, row in df.iterrows():
patient_id = int(row.iloc[0]) if pd.notna(row.iloc[0]) else idx + 1
values = [patient_id]
for col in ['Category', 'Age', 'Sex', 'ALB', 'ALP', 'ALT', 'AST',
'BIL', 'CHE', 'CHOL', 'CREA', 'GGT', 'PROT']:
val = row[col]
if pd.isna(val):
values.append(None)
elif col in ['Age']:
values.append(int(val))
elif col in ['Category', 'Sex']:
values.append(str(val))
else:
values.append(float(val))
cursor.execute("""
INSERT INTO lab_results
(patient_id, category, age, sex, alb, alp, alt, ast, bil, che, chol, crea, ggt, prot)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""", values)
conn.commit()
print(f"Loaded {len(df)} rows into hcv_biochemistry.lab_results")What does this code do?
The loop reads each row from the pandas DataFrame and builds a list of properly typed values. Missing values (NaN in pandas) are converted to None, which mysql-connector translates to SQL NULL.
conn.commit() finalises all the inserts in one batch. Without it, the rows would be discarded when the connection closes.
- Finally, add the verification and cleanup code at the bottom of the file:
# Quick verification
cursor.execute("SELECT COUNT(*) FROM lab_results")
count = cursor.fetchone()[0]
print(f"Verification: {count} rows in table")
cursor.close()
conn.close()What does this code do?
The SELECT COUNT query confirms that rows actually landed in the table. The script prints the count so you can see it without opening a separate MySQL client.
cursor.close() and conn.close() release the database connection cleanly.
- Save setup_db.py.
✔️ Awesome, I've got everything!
Great. Make sure you have saved setup_db.py with all four sections in order: imports/connect, create table, insert loop, and verification/close.
ⓧ I'd like to double check the full code
Scroll up and confirm your setup_db.py contains these four chunks in order:
- Imports + CSV read + MySQL connection + CREATE DATABASE.
- DROP TABLE + CREATE TABLE with all 14 columns.
- The for idx, row in df.iterrows() loop that inserts each row, followed by conn.commit() and a print statement.
- SELECT COUNT(*) verification, then cursor.close() and conn.close().
Run the script and verify your data
- Run the complete loader script:
python setup_db.pyYou should see two lines of output confirming the load:
Loaded 615 rows into hcv_biochemistry.lab_results
Verification: 615 rows in tableBefore you run the next command, predict: how many rows will your SELECT COUNT query return?
- Verify independently in MySQL by running:
mysql -u root -p -e "SELECT COUNT(*) FROM hcv_biochemistry.lab_results;"You should see 615 returned. That confirms all patient records are loaded and ready to query.
Seeing fewer than 615 rows or a duplicate key error?
If you get a duplicate key error, the script may have partially run before. Since the script includes DROP TABLE IF EXISTS, re-running it should clear previous data. Run python setup_db.py again from scratch.
If you see a different row count, confirm hcvdat0.csv is the correct file from UCI (45.1 KB, 615 data rows plus a header).
Help me debug why my row count doesn't match 615.
Your database is loaded with 615 real patient records. Next up, you will query this raw data and discover exactly why it needs cleaning before you can trust any statistics.
Query Raw Data and Hit the Wall
Your database is loaded with 615 patient records. The natural next move is to start computing statistics on it.
In this step, you will try exactly that. You will query the raw data, attempt some averages, and discover two problems that make every number you compute untrustworthy. This is the wall that makes data cleaning non-optional.
In this step, get ready to:
- Create an analysis script that connects to your MySQL database.
- Query raw data and observe ugly category codes and NULL values.
- Attempt grouped averages and discover that NULLs silently skew your results.
Create analysis.py and connect to MySQL
You will build all your analysis work in a single Python script. This script will grow across the remaining steps as you add cleaning, ranking, and statistics.
- In VS Code, create a new file called analysis.py in the same project folder as setup_db.py.
- Add the imports and database connection by pasting this code:
import mysql.connector
import pandas as pd
import numpy as np
from scipy import stats
import matplotlib.pyplot as plt
# --- Connect to MySQL ---
conn = mysql.connector.connect(
host="localhost",
user="root",
password="your-password-here",
database="hcv_biochemistry"
)
cursor = conn.cursor()What does this code do?
The imports bring in the libraries you will use across this project. mysql.connector talks to your MySQL database. pandas and numpy handle data and math. scipy.stats provides statistical functions. matplotlib draws charts.
The connection block points at your hcv_biochemistry database directly so every query runs against the table you loaded in Step 1.
- Replace your-password-here with your actual MySQL root password (the same one you used in setup_db.py).
- Save analysis.py.
Peek at the raw data
Before you compute anything, you need to see what the raw data actually looks like. Category values, column names, missing values. Let the data show you its problems.
- Add this code below the connection block in analysis.py:
# ============================================================
# STEP 2: Query Raw Data and Hit the Wall
# ============================================================
print("=" * 60)
print("STEP 2: RAW DATA EXPLORATION")
print("=" * 60)
# First 10 rows - see the mess
cursor.execute("SELECT * FROM lab_results LIMIT 10")
rows = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
print("\nFirst 10 rows:")
print(f"Columns: {columns}")
for row in rows[:3]:
print(row)What does this code do?
- cursor.execute("SELECT * FROM lab_results LIMIT 10") fetches the first 10 rows from your table.
- cursor.description gives you the column names so you know what each position means.
- The loop prints the first 3 rows so you can eyeball the raw category strings and spot any None values (Python's representation of SQL NULLs).
- Save analysis.py and run it by executing this in your terminal:
python analysis.pyYou should see column names printed, followed by tuples containing values like '0=Blood Donor', '1=Hepatitis', and possibly None scattered in the numeric columns.
Seeing a connection error?
- Make sure your MySQL server is running. If you restarted your computer since Step 1, you may need to start it again.
- Check that the password in analysis.py matches your MySQL root password exactly.
- Confirm the database exists by running setup_db.py again if needed.
Help me debug my MySQL connection error in analysis.py
Attempt grouped averages and discover the NULL trap
Now try what seems like the obvious next step: compute average lab values per disease category. This is where the data fights back.
- Add this query below the first-10-rows code in analysis.py:
# The trap: COUNT(*) vs COUNT(alb)
cursor.execute("""
SELECT
category,
COUNT(*) AS total_patients,
COUNT(alb) AS patients_with_alb,
COUNT(alp) AS patients_with_alp,
ROUND(AVG(alb), 2) AS avg_alb
FROM lab_results
GROUP BY category
""")
print("\nNULL trap - notice COUNT(*) vs COUNT(alb):")
print(f"{'Category':<30} {'Total':<8} {'Has ALB':<10} {'Has ALP':<10} {'Avg ALB':<10}")
print("-" * 68)
for row in cursor.fetchall():
print(f"{row[0]:<30} {row[1]:<8} {row[2]:<10} {row[3]:<10} {row[4]:<10}")What does this query reveal?
COUNT(*) counts every row regardless of NULLs. COUNT(alb) counts only rows where alb is NOT NULL.
When these two numbers differ, it means AVG(alb) was computed on fewer patients than you think. MySQL silently skips NULLs in aggregate functions. No warning, no error.
- Save analysis.py.
Before you run this, think about it: if a category has 24 patients, do you expect COUNT(alb) to also return 24?
- Run the script again:
python analysis.pyLook at the output table. COUNT(*) and COUNT(alb) do not match for some categories. That gap is invisible patients whose lab values were silently excluded from your averages.
Why is this a problem?
Your "average albumin for Hepatitis patients" is not computed on all Hepatitis patients. It is computed on whichever subset happened to have a non-NULL albumin value. You cannot trust any statistic built on this raw table.
On top of that, the category labels are coded strings like '0=Blood Donor' and '0s=suspect Blood Donor'. They are hard to read, hard to filter, and will clutter every chart you build. Both problems need fixing before real analysis can begin.
Not seeing different counts?
- Make sure you loaded the original hcvdat0.csv without modifications. The UCI dataset has confirmed missing values in the ALB, ALP, AST, and CHOL columns.
- Check that setup_db.py inserted None for missing values (not 0 or an empty string).
Help me figure out why my COUNT(*) and COUNT(alb) return the same number
✔️ Awesome, I've got everything!
Great. Double check you have saved analysis.py with both query blocks in place.
ⓧ I'd like to double check the full code
Scroll up and confirm your analysis.py contains these sections in order:
- Imports (mysql.connector, pandas, numpy, scipy.stats, matplotlib.pyplot).
- MySQL connection block pointing at hcv_biochemistry with a cursor.
- Step 2 header prints and the SELECT * FROM lab_results LIMIT 10 query with the loop printing 3 rows.
- The NULL trap query with COUNT(*), COUNT(alb), COUNT(alp), and AVG(alb) grouped by category, with the formatted print loop.
You have named the enemy: ugly category codes and invisible NULLs that silently shrink your sample sizes. Next, you will fix both problems using CTEs and CASE expressions to build a clean, labeled version of your data without touching the original table.
Clean and Categorize with CTEs and CASE
In the last step you exposed two problems hiding in your raw data: ugly category codes like '0=Blood Donor' and NULLs that silently skew aggregate functions.
Now you'll fix both problems using two SQL features designed for exactly this situation. CTEs let you build a cleaned version of your table in named, layered steps. CASE expressions transform coded values into meaningful labels without touching the original data.
In this step, get ready to:
- Map raw category codes to readable disease stage labels using CASE.
- Flag incomplete records with a has_missing column.
- Filter to complete cases using a chained CTE and verify the counts per group.
Write the CTE that maps category codes to clinical labels
A CTE (Common Table Expression) creates a named temporary result set that you can reference like a table. You define it with a WITH clause, and each CTE in the clause is separated by a comma.
A CASE expression evaluates conditions top-to-bottom and returns the value for the first match. Together, they let you rename messy values step-by-step without modifying the original table.
- In analysis.py, scroll to the bottom of the file (after the Step 2 for loop).
- Add the following section header and the cleaning query by pasting this code at the bottom:
# ============================================================
# STEP 3: Clean and Categorize with CTEs and CASE
# ============================================================
print("\n" + "=" * 60)
print("STEP 3: CTEs + CASE - CLEANING THE DATA")
print("=" * 60)- Directly below those print statements, add the CTE query definition:
# CTE with CASE to create clean labels + missing flag
cte_query = """
WITH cleaned AS (
SELECT
patient_id,
CASE
WHEN category = '0=Blood Donor' THEN 'Healthy'
WHEN category = '0s=suspect Blood Donor' THEN 'Suspect Donor'
WHEN category = '1=Hepatitis' THEN 'Hepatitis'
WHEN category = '2=Fibrosis' THEN 'Fibrosis'
WHEN category = '3=Cirrhosis' THEN 'Cirrhosis'
ELSE 'Unknown'
END AS disease_stage,
age,
sex,
alb, alp, alt, ast, bil, che, chol, crea, ggt, prot,
CASE
WHEN alb IS NULL OR alp IS NULL OR alt IS NULL
OR ast IS NULL OR chol IS NULL THEN 1
ELSE 0
END AS has_missing
FROM lab_results
),
complete_cases AS (
SELECT * FROM cleaned WHERE has_missing = 0
)
SELECT disease_stage, COUNT(*) AS patient_count
FROM complete_cases
GROUP BY disease_stage
ORDER BY patient_count DESC
"""What does this query do?
This single query has three layers, each building on the last:
- The first CTE cleaned uses CASE to convert coded strings ('0=Blood Donor', '1=Hepatitis') into plain English labels stored in a new column called disease_stage.
- A second CASE inside the same CTE creates has_missing. It flags any row where a key lab column is NULL so you can decide per-analysis whether to include it.
- The second CTE complete_cases references cleaned and filters to only the rows with no missing values.
- The final SELECT counts patients per disease stage from the complete cases only.
Why CTEs instead of subqueries?
You could nest everything as subqueries, but CTEs let you name each layer (cleaned, complete_cases) and read the logic top-to-bottom. You can also reference a CTE multiple times in the same query without duplicating the subquery.
Execute the query and verify clean labels
- Add the execution code directly below the cte_query variable:
cursor.execute(cte_query)
print("\nComplete cases per disease stage:")
print(f"{'Disease Stage':<20} {'Count':<10}")
print("-" * 30)
for row in cursor.fetchall():
print(f"{row[0]:<20} {row[1]:<10}")What does this code do?
This sends the CTE query to MySQL, then loops over the result rows and prints each disease stage alongside its patient count. The formatted output gives you a clear table showing how many complete records exist in each group.
Before you run this, think: what disease stage names do you expect to see instead of the original coded strings?
- Save analysis.py.
- Run the script by executing this in your terminal:
python analysis.pyAt the bottom of the output, you should see the Step 3 section with a table like this:
STEP 3: CTEs + CASE - CLEANING THE DATA
============================================================
Complete cases per disease stage:
Disease Stage Count
------------------------------
Healthy 533
Suspect Donor 24
Hepatitis 20
Fibrosis 15
Cirrhosis 14The ugly coded strings are gone. Each group now has a readable clinical label, and the counts reflect only patients with complete lab records.
Not seeing clean labels?
- If you see a syntax error mentioning the triple-quoted string, check that the opening """ and closing """ for cte_query are each on their own line with no trailing characters.
- If you see Unknown as a disease stage, double-check that the category strings in your CASE match exactly: '0=Blood Donor', '0s=suspect Blood Donor', '1=Hepatitis', '2=Fibrosis', '3=Cirrhosis'. These are case-sensitive.
- If you get fewer total patients than expected, remember: complete_cases intentionally excludes rows with any NULL in the checked columns.
Why isn't my CTE query returning the expected results?
✔️ Awesome, I've got everything!
Great. Double check you have saved analysis.py with all the Step 3 code.
ⓧ I'd like to double check the full code
Your analysis.py should now contain three sections. Scroll up through the file and confirm these are present in order:
- The imports and MySQL connection block at the top.
- The Step 2 section: raw data exploration with the SELECT * FROM lab_results LIMIT 10 query and the NULL trap grouped AVG query.
- The Step 3 section: the section header prints, the cte_query variable containing the full WITH cleaned AS (...) and complete_cases AS (...) CTEs, and the cursor.execute(cte_query) with print formatting.
Your data is now clean, labelled, and filtered to complete records. Next up, you'll use window functions to rank patients within their disease groups and answer questions like "where does this patient's ALT sit compared to everyone else with Hepatitis?"
Rank and Compare with Window Functions
Your cleaned CTEs give you group totals and tidy labels. You can count how many Hepatitis patients have complete records.
But you cannot yet answer a more interesting question: where does a specific patient sit relative to others in their disease group? Window functions compute per-row rankings without collapsing the result set the way GROUP BY does.
In this step, get ready to:
- Use ROW_NUMBER to rank patients by ALT enzyme level within each disease group.
- Use NTILE to assign quartile buckets by bilirubin level.
- Load the ranked results into a pandas DataFrame for the next step.
Write the window function query
A GROUP BY query collapses rows into one summary per group. You lose individual patient data. Window functions keep every row intact and add a new computed column alongside the original values.
Why window functions instead of GROUP BY?
With GROUP BY, asking "what's the average ALT for Hepatitis patients?" destroys your ability to also see each patient's individual ALT. Window functions let you compute rankings and percentiles on each row without losing detail.
The syntax is FUNCTION() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY creates separate "windows" per group. ORDER BY defines what "rank" means within each window.
- In analysis.py, add the Step 4 section header and the first half of the window function query below your Step 3 code:
# ============================================================
# STEP 4: Window Functions - Rank and Compare
# ============================================================
print("\n" + "=" * 60)
print("STEP 4: WINDOW FUNCTIONS - RANKING PATIENTS")
print("=" * 60)
window_query = """
WITH cleaned AS (
SELECT
patient_id,
CASE
WHEN category = '0=Blood Donor' THEN 'Healthy'
WHEN category = '0s=suspect Blood Donor' THEN 'Suspect Donor'
WHEN category = '1=Hepatitis' THEN 'Hepatitis'
WHEN category = '2=Fibrosis' THEN 'Fibrosis'
WHEN category = '3=Cirrhosis' THEN 'Cirrhosis'
ELSE 'Unknown'
END AS disease_stage,
age, sex, alb, alp, alt, ast, bil, che, chol, crea, ggt, prot,
CASE
WHEN alb IS NULL OR alp IS NULL OR alt IS NULL
OR ast IS NULL OR chol IS NULL THEN 1
ELSE 0
END AS has_missing
FROM lab_results
),What does this code do so far?
- The comment banner and print statements create a visible section header when you run the script, matching the pattern from Steps 2 and 3.
- The cleaned CTE is repeated here because each SQL statement is self-contained. MySQL does not persist a CTE across separate queries.
- The CASE expression maps raw category codes to readable disease_stage labels, and the has_missing flag marks incomplete records.
- Continue adding the rest of the query directly below (still inside the same triple-quoted string):
complete_cases AS (
SELECT * FROM cleaned WHERE has_missing = 0
)
SELECT
patient_id,
disease_stage,
alt,
ROW_NUMBER() OVER (PARTITION BY disease_stage ORDER BY alt DESC) AS alt_rank,
bil,
NTILE(4) OVER (PARTITION BY disease_stage ORDER BY bil) AS bil_quartile
FROM complete_cases
"""What do the two window functions do?
- ROW_NUMBER() OVER (PARTITION BY disease_stage ORDER BY alt DESC) AS alt_rank assigns rank 1 to the patient with the highest ALT in each disease group, rank 2 to the next, and so on. This answers "who has the worst liver enzyme levels in each group?"
- NTILE(4) OVER (PARTITION BY disease_stage ORDER BY bil) AS bil_quartile divides each disease group into 4 roughly equal buckets ordered by bilirubin. A patient in quartile 4 has bilirubin in the top 25% of their group. This is the SQL equivalent of computing percentile groups.
- complete_cases filters to only rows with no NULLs, so the window functions compute on clean data.
Execute the query and load into a DataFrame
- Directly below the closing """ of window_query, add this code to execute the query and load results into pandas:
cursor.execute(window_query)
results = cursor.fetchall()
col_names = ['patient_id', 'disease_stage', 'alt', 'alt_rank', 'bil', 'bil_quartile']
df_ranked = pd.DataFrame(results, columns=col_names)
print("\nTop 5 patients by ALT rank (sample):")
print(df_ranked.head(10).to_string(index=False))What does this code do?
- cursor.execute(window_query) sends the full CTE + window function query to MySQL.
- cursor.fetchall() pulls every result row back into Python as a list of tuples.
- pd.DataFrame(results, columns=col_names) wraps the raw tuples into a DataFrame with meaningful column names so you can inspect and manipulate the data in the next step.
Before you run this, what do you expect to see? Each row should still be one patient, but now with two extra columns showing their rank and quartile within their disease group.
- Save analysis.py.
- Run the script by executing this command in your terminal:
python analysis.pyUnder the Step 4 heading in the output, you should see a table with columns patient_id, disease_stage, alt, alt_rank, bil, and bil_quartile. Each patient has a rank number within their disease group, proving the window function computed a per-row result without collapsing anything.
Seeing an error when running the script?
- If you see a syntax error in the SQL, check that your triple-quoted string starts with """ and ends with """ on its own line. A missing quote breaks the entire query.
- If you see "Unknown column" errors, make sure the column names inside the CASE expression match exactly (category not Category). MySQL column references are case-sensitive on some systems.
- If you get a "commands out of sync" error, ensure you called cursor.fetchall() on the Step 3 query results before executing the Step 4 query. MySQL requires you to consume all results before sending a new query on the same cursor.
Help me debug my window function query in analysis.py
✔️ Awesome, I've got everything!
Great. Make sure you have saved analysis.py with all changes.
ⓧ I'd like to double check the full code
Scroll up and confirm that the following three chunks appear at the bottom of your analysis.py file, in this order:
- The section header (comment banner with "STEP 4" and the three print lines).
- The window_query string assignment containing the full CTE chain with ROW_NUMBER() and NTILE(4) window functions.
- The execution block: cursor.execute(window_query), cursor.fetchall(), DataFrame creation as df_ranked, and the print(df_ranked.head(10).to_string(index=False)) line.
If any chunk is missing or out of order, scroll back to the matching substep and paste it again.
You now have every patient ranked within their disease group, with quartile buckets that directly map to the statistical concept of percentiles. Next up, you will use this structured data to compute descriptive statistics and discover exactly when the mean lies to you.
Compute Descriptive Statistics and Discover When the Mean Lies
You now have clean, ranked data flowing from SQL into Python. The next question is: what single number best summarizes each biomarker for a group of patients?
The answer depends on the shape of the data. In this step, you will compute every core descriptive statistic and discover, through real numbers, that the mean and median diverge dramatically on skewed biomarkers. This is where you build the instinct for "which measure do I trust?"
In this step, get ready to:
- Compute mean, median, mode, SD, IQR, and skewness for all 10 biomarkers.
- Expose bilirubin as the biomarker where the mean lies to you.
- Build a skewness-based decision framework for choosing the right summary statistic.
Pull complete cases and compute summary statistics
Before computing statistics, you need all the cleaned data in a pandas DataFrame. You will reuse the same CTE pattern from Steps 3 and 4 to pull only complete cases (no NULLs) into Python.
- In analysis.py, add this code below your Step 4 code to start the Step 5 section and define the query that pulls all complete cases:
print("\n" + "=" * 60)
print("STEP 5: DESCRIPTIVE STATISTICS")
print("=" * 60)
# Pull all complete cases for full analysis
full_query = """
WITH cleaned AS (
SELECT
patient_id,
CASE
WHEN category = '0=Blood Donor' THEN 'Healthy'
WHEN category = '0s=suspect Blood Donor' THEN 'Suspect Donor'
WHEN category = '1=Hepatitis' THEN 'Hepatitis'
WHEN category = '2=Fibrosis' THEN 'Fibrosis'
WHEN category = '3=Cirrhosis' THEN 'Cirrhosis'
ELSE 'Unknown'
END AS disease_stage,
age, sex, alb, alp, alt, ast, bil, che, chol, crea, ggt, prot,
CASE
WHEN alb IS NULL OR alp IS NULL OR alt IS NULL
OR ast IS NULL OR chol IS NULL THEN 1
ELSE 0
END AS has_missing
FROM lab_results
),
complete_cases AS (
SELECT * FROM cleaned WHERE has_missing = 0
)
SELECT patient_id, disease_stage, alb, alp, alt, ast, bil, che, chol, crea, ggt, prot
FROM complete_cases
"""What does this query do?
This is the same CTE chain from Steps 3 and 4. It maps raw category codes to clean disease stage labels and flags rows with missing values. The final SELECT pulls only complete cases with all 10 lab value columns into Python for statistical analysis.
- Directly below the query, add this code to execute it, load the results into a DataFrame, and compute a summary statistics table for all 10 biomarkers:
cursor.execute(full_query)
results = cursor.fetchall()
col_names = ['patient_id', 'disease_stage', 'alb', 'alp', 'alt', 'ast',
'bil', 'che', 'chol', 'crea', 'ggt', 'prot']
df_full = pd.DataFrame(results, columns=col_names)
# Compute descriptive statistics for each biomarker
biomarkers = ['alb', 'alp', 'alt', 'ast', 'bil', 'che', 'chol', 'crea', 'ggt', 'prot']
print("\n--- Summary Statistics Table ---")
print(f"{'Biomarker':<10} {'Mean':<10} {'Median':<10} {'SD':<10} {'IQR':<10} {'Skewness':<10}")
print("-" * 60)
for col in biomarkers:
data = df_full[col].dropna()
mean_val = np.mean(data)
median_val = np.median(data)
sd_val = np.std(data, ddof=1)
iqr_val = np.percentile(data, 75) - np.percentile(data, 25)
skew_val = stats.skew(data)
print(f"{col.upper():<10} {mean_val:<10.2f} {median_val:<10.2f} {sd_val:<10.2f} {iqr_val:<10.2f} {skew_val:<10.2f}")What does this code do?
- df_full holds all complete patient records as a pandas DataFrame.
- biomarkers lists the 10 lab value columns you will analyze.
- The loop computes five statistics per biomarker: mean (balance point), median (middle value), standard deviation (spread), IQR (middle-50% spread), and skewness (asymmetry measure).
- dropna() removes any remaining NULLs from individual columns before computing, so no statistic is silently skewed.
- Save analysis.py.
- Run the script to see your summary table by running:
python analysis.py- Scroll to the bottom of the output. You should see a formatted table with all 10 biomarkers and their Mean, Median, SD, IQR, and Skewness values.
What should I see?
The table prints one row per biomarker. Notice that some biomarkers (like ALB) have mean and median values close together, while others (like BIL) show a large gap. That gap is the first clue that the mean is not trustworthy for every column.
Seeing an error about stats.skew?
Make sure your import at the top of the file says from scipy import stats. If you imported scipy alone, the stats module will not be available directly.
Help me debug my scipy stats import
Expose bilirubin as the biomarker where the mean lies
The summary table hinted that BIL (bilirubin) has a big gap between mean and median. Now you will zoom in on that single biomarker and measure exactly how much the mean misleads.
- Below your summary statistics loop in analysis.py, add this code to isolate bilirubin and compare it with albumin:
# The key insight: BIL mean vs median
bil_data = df_full['bil'].dropna()
bil_mean = np.mean(bil_data)
bil_median = np.median(bil_data)
print(f"\n--- KEY INSIGHT: Bilirubin (BIL) ---")
print(f"Mean: {bil_mean:.2f}")
print(f"Median: {bil_median:.2f}")
print(f"Difference: {((bil_mean - bil_median) / bil_median * 100):.1f}%")
print(f"Skewness: {stats.skew(bil_data):.2f}")
print("The mean is pulled toward extreme liver-damage values.")
print("The median better represents the 'typical' patient.")
# Compare ALB (symmetric) vs BIL (skewed)
alb_data = df_full['alb'].dropna()
alb_skew = stats.skew(alb_data)
bil_skew = stats.skew(bil_data)
print(f"\n--- COMPARISON ---")
print(f"ALB skewness: {alb_skew:.2f} (symmetric -> mean is trustworthy)")
print(f"BIL skewness: {bil_skew:.2f} (right-skewed -> use median)")Why does bilirubin's mean lie?
Most patients in this dataset are healthy blood donors with low bilirubin values. A small number of cirrhosis and hepatitis patients have extremely elevated bilirubin due to liver damage. Those extreme values yank the mean upward, making it unrepresentative of the typical patient.
The median ignores those extremes entirely. It just picks the middle value. When data is right-skewed (a long tail to the right), the median is the more honest summary.
- Now add the visualization code that plots both BIL and ALB histograms side by side with mean and median lines:
# Visualization: BIL histogram with mean/median lines
fig, axes = plt.subplots(1, 2, figsize=(12, 5))
# BIL - skewed
axes[0].hist(bil_data, bins=30, color='steelblue', edgecolor='black', alpha=0.7)
axes[0].axvline(bil_mean, color='red', linestyle='--', linewidth=2, label=f'Mean: {bil_mean:.1f}')
axes[0].axvline(bil_median, color='green', linestyle='-', linewidth=2, label=f'Median: {bil_median:.1f}')
axes[0].set_title('BIL (Bilirubin) - Right Skewed')
axes[0].set_xlabel('Value')
axes[0].set_ylabel('Frequency')
axes[0].legend()
# ALB - symmetric
alb_mean = np.mean(alb_data)
alb_median = np.median(alb_data)
axes[1].hist(alb_data, bins=30, color='coral', edgecolor='black', alpha=0.7)
axes[1].axvline(alb_mean, color='red', linestyle='--', linewidth=2, label=f'Mean: {alb_mean:.1f}')
axes[1].axvline(alb_median, color='green', linestyle='-', linewidth=2, label=f'Median: {alb_median:.1f}')
axes[1].set_title('ALB (Albumin) - Approximately Symmetric')
axes[1].set_xlabel('Value')
axes[1].set_ylabel('Frequency')
axes[1].legend()
plt.tight_layout()
plt.savefig('mean_vs_median.png', dpi=150)
plt.show()
print("\nHistogram saved as mean_vs_median.png")What does this visualization show?
- The left panel plots BIL values. The red dashed line (mean) sits far to the right of the green solid line (median), proving the mean is pulled by extreme values.
- The right panel plots ALB values. The red and green lines nearly overlap, showing that mean and median agree when data is approximately symmetric.
- The visible gap between the lines IS the skewness. The bigger the gap, the less you should trust the mean.
- Save analysis.py and run it again:
python analysis.py- A matplotlib window will open showing two histograms side by side. Close the window to let the script finish.
- In your terminal, you should see the BIL mean vs median comparison printed, showing a difference greater than 50%.
Histogram window not appearing?
Some environments (like remote servers or WSL) do not support GUI windows. If you do not see a plot window, the image is still saved as mean_vs_median.png in your project folder. Open it manually to see the result.
Help me display matplotlib plots in my environment
Build your decision framework
You have seen that BIL's mean is misleading. Now you will formalize the rule: use skewness to automatically decide which summary statistic to trust for any biomarker.
- Below your visualization code in analysis.py, add this decision framework and connection cleanup:
# Decision framework
print("\n--- DECISION FRAMEWORK ---")
print("Rule: If |skewness| > 1, use MEDIAN and IQR")
print(" If |skewness| <= 1, MEAN and SD are trustworthy")
print("\nApplied to our biomarkers:")
for col in biomarkers:
data = df_full[col].dropna()
skew_val = stats.skew(data)
if abs(skew_val) > 1:
rec = "MEDIAN + IQR (skewed)"
else:
rec = "MEAN + SD (symmetric)"
print(f" {col.upper():<6} skewness={skew_val:>6.2f} -> {rec}")
cursor.close()
conn.close()
print("\nDone. Connection closed.")What is the skewness threshold?
The rule of thumb is: if the absolute value of skewness exceeds 1, the distribution is heavily asymmetric and the mean is not a reliable center measure. Use median and IQR instead.
If skewness falls between -1 and 1, the data is approximately symmetric and mean plus standard deviation describe it well. This threshold gives you an automatic decision for any new column you encounter.
Before you run the final version, predict: which biomarkers do you think will be flagged as skewed (recommending median) versus symmetric (recommending mean)?
- Save analysis.py and run it one final time:
python analysis.py- Scroll to the DECISION FRAMEWORK section of the output. You should see each biomarker listed with its skewness value and a recommendation. BIL, GGT, and CREA should be flagged as skewed (recommending MEDIAN + IQR). ALB and CHOL should be flagged as symmetric (recommending MEAN + SD).
- The final line should read Done. Connection closed. confirming your MySQL connection was cleanly shut down.
Seeing a connection error on the second run?
If your script crashes partway through, the cursor.close() and conn.close() at the bottom never execute. On the next run the connection opens fresh, so this is only a problem if MySQL hit its connection limit. Restart MySQL if needed.
Help me fix my MySQL connection error
✔️ Awesome, I've got everything!
Great. Double check you have saved analysis.py and that mean_vs_median.png exists in your project folder.
ⓧ I'd like to double check the full code
Your analysis.py is now too long to display in a single block. Scroll through your file and confirm these sections exist in order:
- Imports and MySQL connection at the top.
- STEP 2 section: raw data exploration and NULL trap query.
- STEP 3 section: CTE with CASE for cleaning and the complete_cases count query.
- STEP 4 section: window function query creating df_ranked.
- STEP 5 section: full_query pulling complete cases into df_full, the summary statistics loop, BIL insight block, ALB vs BIL comparison, the side-by-side histogram, and the decision framework.
- cursor.close() and conn.close() at the very end.
You now have a complete statistical pipeline: SQL cleans and structures the data, Python computes every descriptive statistic, and your decision framework tells you which measure to trust. Next up, you will automate this entire process into a reusable report function.
Secret mission
Build an Automated Statistical Profile Report
You've manually computed statistics and made judgment calls about which measure to trust. What if you could automate that entire decision process into a single reusable function that profiles any biomarker, detects its shape, and tells you exactly which summary statistic to use?
Clean Up Your Resources
Clean Up Your Resources
This project runs entirely on your local machine. There are no cloud resources or ongoing costs. Decide whether to keep your resources for future reference, pause them, or delete them entirely.
Resources you used:
- MySQL database hcv_biochemistry with the lab_results table (615 rows of HCV patient data).
- Project files on your Desktop: setup_db.py, analysis.py, hcvdat0.csv, mean_vs_median.png, statistical_profile_report.png.
Keep everything running
No action needed. Choose this if you plan to extend the analysis, try the Secret Mission, or revisit the SQL queries later.
- Your MySQL database stays intact and queryable anytime you start the MySQL server.
- Your Python scripts and generated images remain in your project folder on the Desktop.
- MySQL, Anaconda, and the Python packages you had before this project remain installed as they were.
Pause - I'll come back to this later
Stop the MySQL server to free up memory. Your database and files stay on disk for when you return.
- On macOS, open System Preferences (or System Settings on newer versions), then click MySQL and click Stop MySQL Server.
- On Windows, press the Windows key, type Services, open the Services app, find MySQL80, right-click it, and select Stop.
- Your database and project files remain on disk. When you're ready to resume, start the MySQL server again.
Delete - I don't want to use this again
Remove the database and project files completely. This cannot be undone.
- Drop the MySQL database by opening a terminal and running:
mysql -u root -p -e "DROP DATABASE IF EXISTS hcv_biochemistry;"Enter your MySQL root password when prompted. This removes the database and its lab_results table permanently.
- Delete your project folder from the Desktop by running:
macOS
cd ~/Desktop
lsWindows
cd ~\Desktop
dirFind the name of your project folder in the listing, then delete it.
macOS
rm -rf ~/Desktop/your-project-folder-nameWindows
Remove-Item -Recurse -Force ~\Desktop\your-project-folder-nameReplace your-project-folder-name with the actual name of the folder you created for this project.
Nice Work!
Nice Work!
You just built a complete SQL-to-Python data analysis pipeline on real clinical biochemistry data from 615 hepatitis C patients.
You've learned how to:
- Load real-world data into MySQL and clean it with CTEs and CASE expressions, transforming messy category codes into meaningful clinical labels and flagging incomplete records.
- Use window functions (ROW_NUMBER, NTILE) to rank patients within disease groups without collapsing the result set.
- Compute every core descriptive statistic (mean, median, mode, SD, variance, range, IQR, skewness) and visually prove when each one is trustworthy versus misleading on skewed biomarker data.
- Built an automated Statistical Profile Report that detects data shape and recommends the right summary measure for each biomarker.
You can now look at any new dataset and immediately know which descriptive statistic to reach for based on the distribution's skewness.
Ready to quiz yourself?