Session 2.3: Quality report and light cleaning

# Import os to read env vars
import os
# Import bob and helper functions
from bia_bob import bob, available_models, fix, doc

# Initialize assistant, reads API key from env var automatically
bob.initialize(
    # read endpoint URL from env var
    endpoint=os.getenv('ENDPOINT_URL'),
    # select an available model for your purpose
    model="coder",
    # read the Data Steward system prompt from env var
    system_prompt=os.getenv('SYSTEM_PROMPT_DATA_STEWARD'),
)

#!uv add fg-data-profiling

from data_profiling import ProfileReport

import pandas as pd

# load the RAW data again (so this notebook stands on its own)
raw = pd.read_csv("../data/field_samples_raw.csv")

# one line generates a full data report
report = ProfileReport(raw, title="Field Samples Raw - Data Quality Report")

from pathlib import Path
Path("../outputs").mkdir(exist_ok=True)
report.to_file("../outputs/quality_report_raw.html")
print("Saved report to ../outputs/quality_report_raw.html")

%%bob
I have a pandas DataFrame in the variable `raw` of biological field samples. 
Generate cleaning code that:
- reads missing values that are coded as "", "NA", "n/a", "NULL", "?" or "-999" all as NaN
- drops exact duplicate rows
- strips whitespace from sample_id; title-cases species and island; lower-cases sex
- maps category variants to a controlled vocabulary (e.g. "Gentooo"->"Gentoo", "Bisco"->"Biscoe",
  "Chinstrap Penguin"->"Chinstrap"; sex "m"->"male", "f"->"female", "unknown"/"." -> NaN)
- removes the unit " g" from body_mass_g and converts it to a number
- converts comma decimals in flipper_length_mm (e.g. "195,5") and makes it a number
- parses the mixed date formats in date_collected into ISO format YYYY-MM-DD
Store the result in a new DataFrame `clean`. 
Keep it readable with short comments.

import numpy as np

# Re-read, declaring every "missing" spelling as NaN, and trimming stray spaces
MISSING = ["", "NA", "n/a", "NULL", "?", "-999"]
clean_reference = pd.read_csv(
    "../data/field_samples_raw.csv",
    na_values=MISSING, 
    keep_default_na=True, 
    skipinitialspace=True,
)

# 1. drop exact duplicate rows
clean_reference = clean_reference.drop_duplicates()

# 2. tidy text: strip whitespace and normalise casing
clean_reference["sample_id"] = clean_reference["sample_id"].str.strip()
clean_reference["species"]   = clean_reference["species"].str.strip().str.title()
clean_reference["island"]    = clean_reference["island"].str.strip().str.title()
clean_reference["sex"]       = clean_reference["sex"].str.strip().str.lower()

# 3. map category variants onto the controlled vocabularies
clean_reference["species"] = clean_reference["species"].map(
    {"Adelie": "Adelie", "Chinstrap": "Chinstrap", "Chinstrap Penguin": "Chinstrap",
     "Gentoo": "Gentoo", "Gentooo": "Gentoo"})
clean_reference["island"] = clean_reference["island"].map(
    {"Biscoe": "Biscoe", "Dream": "Dream", "Torgersen": "Torgersen", "Bisco": "Biscoe"})
clean_reference["sex"] = clean_reference["sex"].map(
    {"m": "male", "male": "male", "f": "female", "female": "female",
     "unknown": np.nan, ".": np.nan})

# 4. numbers: strip the " g" unit, fix comma-decimals, convert to numeric
clean_reference["body_mass_g"] = (clean_reference["body_mass_g"].astype("string")
                        .str.replace(" g", "", regex=False).str.strip())
clean_reference["body_mass_g"] = pd.to_numeric(clean_reference["body_mass_g"], errors="coerce")

clean_reference["flipper_length_mm"] = (clean_reference["flipper_length_mm"].astype("string")
                              .str.replace(",", ".", regex=False).str.strip())
clean_reference["flipper_length_mm"] = pd.to_numeric(clean_reference["flipper_length_mm"], errors="coerce")

# 5. parse the four date formats into one ISO format
clean_reference["date_collected"] = pd.to_datetime(
    clean_reference["date_collected"], errors="coerce", dayfirst=True, format="mixed"
).dt.strftime("%Y-%m-%d")

clean_reference.head(10)

import warnings
import pandera.pandas as pa

# reuse the contract from Notebook 2
schema = pa.DataFrameSchema.from_yaml("../outputs/data_schema.yml")

with warnings.catch_warnings():
    # pandera concatenates failure tables internally and triggers a pandas FutureWarning;
    # it does not affect the result, so we silence just this one message.
    warnings.filterwarnings("ignore", category=FutureWarning,
                            message="The behavior of DataFrame concatenation")
    try:
        schema.validate(clean_reference, lazy=True)
        print("✅ All records valid.")
    except pa.errors.SchemaErrors as exc:
        remaining = exc.failure_cases
        print(f"Remaining failing cases: {len(remaining)} (down from ~600 on the raw data)\n")
        print(remaining.groupby(["column", "check"]).size().to_string())

# Records with an out-of-range body mass (kept as-is, flagged for review)
mass_issues = clean_reference[(clean_reference["body_mass_g"] < 2500) | 
    (clean_reference["body_mass_g"] > 6500)]
print("Out-of-range body_mass_g:", len(mass_issues))
display(mass_issues[["sample_id", "species", "body_mass_g", "flipper_length_mm"]].head())

# Records missing a required field
required = ["sample_id", "species", "island", "date_collected",
            "body_mass_g", "flipper_length_mm"]
missing_required = clean_reference[clean_reference[required].isna().any(axis=1)]
print("\nRecords missing at least one required field:", len(missing_required))

clean_reference.to_csv("../outputs/field_samples_clean.csv", index=False)
print("Saved cleaned data to ../outputs/field_samples_clean.csv")

clean_report = ProfileReport(clean_reference, title="Field Samples Cleaned - Data Quality Report")
clean_report.to_file("../outputs/quality_report_clean.html")
print("Saved cleaned-data report to ../outputs/quality_report_clean.html")