Experiment Data Cleaning Pipeline — Replicable Handling of Missing Values, Duplicates, and Outliers

After completing this topic

You will be able to create your own pipeline to cleanse messy experimental data CSVs in a reproducible manner, by combining the data cleaning, apply/map, and mutable argument trap concepts learned in the textbook. The problem of inconsistent cleaning leading to unreproducible results will be solved.

This article is a general educational example. In practice, more sophisticated tools such as dbt, Great Expectations, and Pandera are used.

"Why aren't the results from last week showing up?" — The Pitfalls of Manual Cleaning

Let's say you receive a CSV file of experimental data every week and analyze it. In the first week, you:

Open it in Excel and visually check for missing cells.
Delete values that look suspicious.
Combine the data from multiple files into one.
Save and analyze.

You repeat the same process the following week. Then, your advisor asks, "Why aren't the results from last week showing up this week?"

There isn't just one reason.

Problem 1: The values you delete manually are different each time. One week you might delete only values outside of 3σ, while the next week you delete only values outside of 4σ.

Problem 2: The way you handle missing values changes each time. Some weeks you delete them, and other weeks you fill them in with the average.

Problem 3: You don't remember which files were included this time.

The real solution is an explicit pipeline. Specify each cleaning step in code, save the code, and use version control. If you process the same data with the same code again, you will get exactly the same results. This is the foundation of scientific reproducibility.

From Black Box to Components

Component 1: Explicit Steps in Pandas

Each cleaning action as a separate function.

python

import pandas as pd
import numpy as np


def load_and_validate(path: str, required_cols: list[str]) -> pd.DataFrame:
    df = pd.read_csv(path)
    missing = set(required_cols) - set(df.columns)
    if missing:
        raise ValueError(f"Missing columns: {missing}")
    return df


def remove_duplicates(df: pd.DataFrame, subset: list[str]) -> tuple[pd.DataFrame, int]:
    n_before = len(df)
    df_clean = df.drop_duplicates(subset=subset).copy()
    return df_clean, n_before - len(df_clean)


def drop_missing(df: pd.DataFrame, required_cols: list[str]) -> tuple[pd.DataFrame, int]:
    n_before = len(df)
    df_clean = df.dropna(subset=required_cols).copy()
    return df_clean, n_before - len(df_clean)


def filter_outliers_iqr(df: pd.DataFrame, col: str, factor: float = 1.5) -> tuple[pd.DataFrame, int]:
    q1 = df[col].quantile(0.25)
    q3 = df[col].quantile(0.75)
    iqr = q3 - q1
    low, high = q1 - factor * iqr, q3 + factor * iqr
    n_before = len(df)
    df_clean = df[(df[col] >= low) & (df[col] <= high)].copy()
    return df_clean, n_before - len(df_clean)

Key: Each function returns how many items were removed (count). This is logged later.

Component 2: Column Transformation with `apply` and `map`

Transform values explicitly with apply or map.

python

def normalize_gene_symbols(df: pd.DataFrame, col: str = "gene") -> pd.DataFrame:
    """
    Convert gene symbols to a standard uppercase format.
    """
    df = df.copy()
    df[col] = df[col].str.upper().str.strip()
    return df


def standardize_units(df: pd.DataFrame, col: str, unit_col: str) -> pd.DataFrame:
    """
    Combine concentration values and units to standardize to μM.
    """
    df = df.copy()
    
    def to_micromolar(row):
        value = row[col]
        unit = row[unit_col].lower()
        multiplier = {"nm": 0.001, "μm": 1, "um": 1, "mm": 1000, "m": 1_000_000}
        return value * multiplier.get(unit, np.nan)
    
    df["concentration_uM"] = df.apply(to_micromolar, axis=1)
    return df

Caution: Always use df.copy(). Prevent modification of the original DataFrame.

Component 3: Avoiding the Mutable Trap

A common mistake for Python beginners: mutable default arguments.

python

# Dangerous code
def add_error_log(errors: list = []) -> list:
    errors.append("something wrong")
    return errors


# First call: ["something wrong"]
# Second call: ["something wrong", "something wrong"] — Unexpected!

Reason: Python function default values are evaluated only once, when the function is defined. The [] in errors=[] is created when the function is defined and reused.

Correction:

python

def add_error_log(errors: list | None = None) -> list:
    if errors is None:
        errors = []
    errors.append("something wrong")
    return errors

The same trap exists with DataFrames.

python

# Dangerous
def process(df):
    df["new_col"] = df["old_col"] * 2  # Modifies the original df!
    return df

original = pd.read_csv("data.csv")
processed = process(original)
# original["new_col"] also exists. The original is contaminated.


# Safe
def process(df):
    df = df.copy()
    df["new_col"] = df["old_col"] * 2
    return df

Each step in the pipeline should be a pure function (returns a new output without modifying the input) to maintain reproducibility.

Pipeline Assembly

Now, let's assemble the above components into a single pipeline.

python

from dataclasses import dataclass, field
from datetime import datetime


@dataclass
class CleaningReport:
    input_rows: int = 0
    output_rows: int = 0
    steps: list = field(default_factory=list)
    
    def add(self, step_name: str, removed: int, details: str = "") -> None:
        self.steps.append({
            "step": step_name,
            "removed": removed,
            "details": details,
            "timestamp": datetime.now().isoformat()
        })


def clean_experiment_data(
    csv_path: str,
    required_cols: list[str] = None,
    outlier_col: str = "value",
    outlier_factor: float = 1.5
) -> tuple[pd.DataFrame, CleaningReport]:
    required_cols = required_cols or ["gene", "value", "unit"]
    
    df = load_and_validate(csv_path, required_cols)
    report = CleaningReport(input_rows=len(df))
    
    df = normalize_gene_symbols(df, "gene")
    report.add("normalize_gene_symbols", 0, "uppercased and stripped")
    
    df, n_dup = remove_duplicates(df, subset=["gene", "value"])
    report.add("remove_duplicates", n_dup)
    
    df, n_missing = drop_missing(df, required_cols)
    report.add("drop_missing", n_missing)
    
    df = standardize_units(df, "value", "unit")
    report.add("standardize_units", 0)
    
    df, n_outlier = filter_outliers_iqr(df, outlier_col, outlier_factor)
    report.add("filter_outliers_iqr", n_outlier, f"factor={outlier_factor}")
    
    report.output_rows = len(df)
    return df, report

Usage:

python

clean_df, report = clean_experiment_data("raw_data.csv")

print(f"Input: {report.input_rows} rows, Output: {report.output_rows} rows")
for step in report.steps:
    print(f"  {step['step']}: -{step['removed']} ({step['details']})")

Expected Output:

text

Input: 1000 rows, Output: 934 rows
  normalize_gene_symbols: -0 (uppercased and stripped)
  remove_duplicates: -12
  drop_missing: -30
  standardize_units: -0
  filter_outliers_iqr: -24 (factor=1.5)

Fading — Three Blanks You Need to Fill In

Blank 1: Schema Validation

Explicitly validate the type and range of each column.

python

def validate_schema(df: pd.DataFrame, schema: dict) -> list[str]:
    """
    schema = {"col_name": {"type": float, "min": 0, "max": 100}}
    Returns a list of error messages for failed validations. Returns an empty list if all validations pass.
    """
    errors = []
    
    for col, rules in schema.items():
        if col not in df.columns:
            errors.append(f"Missing column: {col}")
            continue
        
        # TODO 1: Type validation (check df[col].dtype)
        # TODO 2: Min/max range validation (if present, compare with df[col].min(), df[col].max())
        pass
    
    return errors

Hint: if not pd.api.types.is_numeric_dtype(df[col]): errors.append(...). Range: if "min" in rules and df[col].min() < rules["min"]: errors.append(...).

Blank 2: Log File Saving

Save the CleaningReport as a CSV log.

python

def save_report(report: CleaningReport, log_path: str) -> None:
    """
    Saves `report.steps` to a CSV file.
    """
    # TODO: Convert to pd.DataFrame(report.steps) and then save using to_csv
    pass

Blank 3: Reproducibility Hash

A hash to verify that the same code produces the same output for the same input.

python

import hashlib


def compute_output_hash(df: pd.DataFrame) -> str:
    """
    Sorts the contents of `df` and then calculates the hash.
    Should always produce the same hash for the same `df`.
    """
    # TODO 1: Sort `df` into a stable order (e.g., by all columns)
    # TODO 2: Convert to a string using to_csv(index=False)
    # TODO 3: Return hashlib.sha256(bytes).hexdigest()
    pass

Hint:

python

sorted_df = df.sort_values(list(df.columns)).reset_index(drop=True)
content = sorted_df.to_csv(index=False).encode()
return hashlib.sha256(content).hexdigest()[:16]

Reflection — Differences from Real-World Data Pipelines

dbt (data build tool): The standard for SQL-based pipelines. Each step is an SQL model, dependencies are automatically managed, and testing is integrated into the pipeline. It is the standard at companies like Airbnb and Netflix.

Great Expectations / Pandera: Data schema and quality validation frameworks. They allow you to declaratively specify the expected values for each column and provide notifications when violations occur.

Apache Airflow / Prefect: Large-scale pipeline orchestration tools. They provide features such as scheduling, retries, and backfilling.

dask / polars: If your data is 100GB or larger, use these tools instead of pandas. They offer parallel processing and lazy evaluation.

Data lineage tracking: In real-world scenarios, each output is automatically tracked to determine which inputs it came from and what transformations were applied. This is often done using a standard like OpenLineage.

Extension Projects

1. Streamlit Dashboard: Create a web UI for your cleaning pipeline. Allow users to upload CSV files and visualize the resulting reports.

2. Great Expectations Integration: Replace your CleaningReport with the standard reports provided by Great Expectations.

3. Merging Multiple Files: When merging multiple CSV files, automatically verify schema consistency before merging.

4. Data Catalog: Store and search metadata for each experimental dataset in an SQLite database.

Component Guide for This Section

[F] Data Cleaning: Explicit handling of missing values, duplicates, and outliers. Logging for each step.
[F] apply/map: Functional approach to column transformation. Understanding axis=1.
[F] Mutable Trap: Avoiding mutable default values and in-place modifications of DataFrames. Adhering to the principle of pure functions.
[W] File I/O: Reading and writing CSV files (complete script provided).

[F] = You implement it yourself / [W] = Provided as complete code.

BioPlayground

A reproducible data cleaning pipeline for handling missing values, duplicates, and outliers.

Experiment Data Cleaning Pipeline — Replicable Handling of Missing Values, Duplicates, and Outliers

After completing this topic

"Why aren't the results from last week showing up?" — The Pitfalls of Manual Cleaning

From Black Box to Components

Component 1: Explicit Steps in Pandas

Component 2: Column Transformation with `apply` and `map`

Component 3: Avoiding the Mutable Trap

Pipeline Assembly

Fading — Three Blanks You Need to Fill In

Blank 1: Schema Validation

Blank 2: Log File Saving

Blank 3: Reproducibility Hash

Reflection — Differences from Real-World Data Pipelines

Extension Projects

Component Guide for This Section

💬 Questions & Comments

A reproducible data cleaning pipeline for handling missing values, duplicates, and outliers.

Experiment Data Cleaning Pipeline — Replicable Handling of Missing Values, Duplicates, and Outliers

After completing this topic

"Why aren't the results from last week showing up?" — The Pitfalls of Manual Cleaning

From Black Box to Components

Component 1: Explicit Steps in Pandas

Component 2: Column Transformation with apply and map

Component 3: Avoiding the Mutable Trap

Pipeline Assembly

Fading — Three Blanks You Need to Fill In

Blank 1: Schema Validation

Blank 2: Log File Saving

Blank 3: Reproducibility Hash

Reflection — Differences from Real-World Data Pipelines

Extension Projects

Component Guide for This Section

💬 Questions & Comments

Component 2: Column Transformation with `apply` and `map`