Bad data doesn't announce itself — it hides in nulls, inconsistent categories, out-of-range values, and pseudo-null strings until it breaks your analysis. This lesson walks you through building a complete, reusable data quality scorecard in pandas that interrogates every column before a single analysis runs, scores failures by severity, and exports a stakeholder-ready Excel report.

You've just received a dataset from a vendor. It's 400,000 rows, covers 18 months of transaction history, and your stakeholders need an analysis by Friday. You load it into pandas, run a quick .head(), everything looks reasonable, and you dive into the work. Three days later, you're presenting your findings and someone asks why the revenue numbers don't match last quarter's report. You dig in and discover that 12% of rows had null customer_id values that silently dropped from your joins, a transaction_date column contained a mix of formats where 8% failed to parse and became NaT, and an amount column had a handful of negative values that represent refunds — but no one told you that, and they inflated your averages.
This scenario plays out constantly. The fix isn't to be more careful — it's to build a system that catches these issues automatically, before any analysis starts. A data quality scorecard does exactly that: it interrogates every column in your DataFrame, scores it against a defined set of checks, and surfaces failures in a format you can act on, share with stakeholders, or gate a pipeline behind. By the end of this lesson, you'll have built a fully functional, extensible scorecard that runs completeness, consistency, and validity checks across any dataset you throw at it.
What you'll learn:
You should be comfortable loading and manipulating DataFrames, including working with .groupby(), boolean masks, and string methods. If you need a refresher on any of those, start with Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types and Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks. You should also have pandas, numpy, and openpyxl installed in your environment. If you need help setting that up, see Setting Up Python for Data Analysis: Python, VS Code, Jupyter, and Virtual Environments.
Before writing a single line of code, you need a clear mental model. Data quality problems cluster into three categories, and understanding the distinctions shapes everything about how you design the checks.
Completeness asks: is the data present? A column that should have a value in every row but has nulls in 30% of them fails completeness. This is the most visible category and also the most deceptive — a column can be 100% populated and still be completely wrong.
Consistency asks: is the data self-consistent, both within a column and across columns? A status column that should only contain 'active', 'inactive', or 'pending' but also contains 'ACTIVE', 'Active', null, and 'N/A' fails consistency. A dataset where end_date is sometimes earlier than start_date fails cross-column consistency. This is where silent errors live.
Validity asks: is the data in the correct format and within acceptable bounds? An email column where 15% of values don't match email patterns fails validity. A unit_price column with negative values fails validity. A postal_code column with five-character alphanumeric strings where you expected five-digit US zip codes fails validity.
Most real-world data issues live in consistency and validity, but most teams only check completeness because it's the easiest to automate. This scorecard will cover all three.
The scorecard is going to be a class — specifically, a DataQualityScorecard class that accepts a DataFrame and a configuration dictionary. Every check returns a standardized dictionary with the same keys:
{
"column": "customer_id",
"check_name": "null_rate",
"check_category": "completeness",
"passed": True,
"score": 1.0,
"detail": "0 nulls out of 400000 rows (0.00%)"
}
This structure is critical. When every check returns the same shape, you can stack them into a DataFrame of results, group them by column or category, and aggregate scores without any special-casing. This is the same principle that makes grouping and aggregating in pandas so powerful — consistent structure enables consistent operations.
The configuration dictionary tells the scorecard what rules apply to which columns. This separates the "what to check" from the "how to check it":
config = {
"customer_id": {
"required": True,
"dtype_expected": "int64",
"unique": True,
},
"email": {
"required": True,
"pattern": r"^[\w\.-]+@[\w\.-]+\.\w{2,}$",
"max_null_rate": 0.01,
},
"transaction_date": {
"required": True,
"dtype_expected": "datetime64",
"date_range": ("2022-01-01", "2024-12-31"),
},
"amount": {
"required": True,
"numeric_range": (0.01, 1_000_000),
"outlier_check": True,
},
"status": {
"required": True,
"allowed_values": ["active", "inactive", "pending"],
"case_sensitive": False,
},
}
This is the kind of configuration a senior analyst writes once and hands to the team. It encodes domain knowledge — the fact that amount should never be negative, that status has only three valid values, that email should match a pattern — and makes it executable.
Key insight
The configuration dictionary is where your domain expertise lives. The checks are generic code; the config is where you encode the business rules. Keeping them separate means you can reuse the same scorecard class on dozens of different datasets by just swapping the config.
Let's build the class from the ground up. Start with the imports and the class shell:
import pandas as pd
import numpy as np
import re
from typing import Any, Optional
from dataclasses import dataclass, field
class DataQualityScorecard:
"""
Automated data quality scorecard for pandas DataFrames.
Runs completeness, consistency, and validity checks across all columns
and produces a structured results DataFrame with per-column scores.
"""
def __init__(self, df: pd.DataFrame, config: dict, dataset_name: str = "dataset"):
self.df = df.copy() # never mutate the source
self.config = config
self.dataset_name = dataset_name
self.results: list[dict] = []
self._total_rows = len(df)
def _record(
self,
column: str,
check_name: str,
check_category: str,
passed: bool,
score: float,
detail: str,
) -> None:
"""Append a standardized check result to the results list."""
self.results.append(
{
"column": column,
"check_name": check_name,
"check_category": check_category,
"passed": passed,
"score": score,
"detail": detail,
}
)
def run(self) -> pd.DataFrame:
"""Execute all configured checks and return the results DataFrame."""
self.results = []
for column, rules in self.config.items():
if column not in self.df.columns:
self._record(
column=column,
check_name="column_exists",
check_category="completeness",
passed=False,
score=0.0,
detail=f"Column '{column}' not found in DataFrame.",
)
continue
self._run_column_checks(column, rules)
return pd.DataFrame(self.results)
Notice the self.df = df.copy() — we never want the scorecard to accidentally modify the source data. This is defensive programming, not paranoia. Also notice that if a column listed in the config doesn't exist in the DataFrame, we record a failed check rather than throwing an exception. A scorecard that crashes on missing columns is useless in a pipeline.
Completeness checks are the foundation. They run on every configured column regardless of what other rules are specified:
def _check_completeness(self, column: str, rules: dict) -> None:
series = self.df[column]
total = self._total_rows
null_count = series.isna().sum()
null_rate = null_count / total if total > 0 else 0.0
max_allowed = rules.get("max_null_rate", 0.0 if rules.get("required") else 0.05)
passed = null_rate <= max_allowed
score = max(0.0, 1.0 - (null_rate / max(max_allowed, 0.0001)))
score = min(score, 1.0)
self._record(
column=column,
check_name="null_rate",
check_category="completeness",
passed=passed,
score=round(score, 4),
detail=(
f"{null_count:,} nulls out of {total:,} rows "
f"({null_rate:.2%}). Threshold: {max_allowed:.2%}."
),
)
# Uniqueness check — only run if configured
if rules.get("unique"):
n_unique = series.nunique(dropna=True)
n_non_null = total - null_count
dupe_count = n_non_null - n_unique
dupe_rate = dupe_count / n_non_null if n_non_null > 0 else 0.0
passed_unique = dupe_count == 0
self._record(
column=column,
check_name="uniqueness",
check_category="completeness",
passed=passed_unique,
score=1.0 if passed_unique else max(0.0, 1.0 - dupe_rate),
detail=(
f"{n_unique:,} unique values among {n_non_null:,} non-null rows. "
f"{dupe_count:,} duplicates ({dupe_rate:.2%})."
),
)
The score calculation for null rate deserves attention. A score of 1.0 means zero nulls. As the null rate climbs toward the threshold, the score drops proportionally. Beyond the threshold, the score is 0.0. This gradient matters because when you aggregate scores across multiple checks, you want columns that are "almost failing" to show up differently from columns that are "catastrophically failing." Binary pass/fail loses that signal.
Warning
The isna() method catches None, np.nan, and pandas NA, but it does not catch strings like "N/A", "null", "NULL", "-", or "". Real datasets are full of these pseudo-nulls. We'll handle this in the validity layer, but be aware that a completeness check alone gives you an incomplete picture.
Consistency checks enforce that column values are internally coherent. These include dtype checks, allowed value lists, and cross-column relationships.
def _check_consistency(self, column: str, rules: dict) -> None:
series = self.df[column]
# --- dtype check ---
if "dtype_expected" in rules:
expected = rules["dtype_expected"]
actual = str(series.dtype)
passed = actual.startswith(expected.replace("64", "").replace("32", ""))
# More robust: check category
dtype_map = {
"int": pd.api.types.is_integer_dtype,
"float": pd.api.types.is_float_dtype,
"datetime64": pd.api.types.is_datetime64_any_dtype,
"bool": pd.api.types.is_bool_dtype,
"object": pd.api.types.is_object_dtype,
"category": pd.api.types.is_categorical_dtype,
"string": pd.api.types.is_string_dtype,
}
checker = dtype_map.get(expected.rstrip("0123456789"), None)
if checker:
passed = checker(series)
else:
passed = actual == expected
self._record(
column=column,
check_name="dtype_check",
check_category="consistency",
passed=passed,
score=1.0 if passed else 0.0,
detail=f"Expected dtype '{expected}', found '{actual}'.",
)
# --- allowed values check ---
if "allowed_values" in rules:
allowed = rules["allowed_values"]
case_sensitive = rules.get("case_sensitive", True)
working = series.dropna()
if not case_sensitive:
working = working.str.lower()
allowed = [v.lower() for v in allowed]
invalid_mask = ~working.isin(allowed)
invalid_count = invalid_mask.sum()
invalid_rate = invalid_count / len(working) if len(working) > 0 else 0.0
passed = invalid_count == 0
# Show up to 5 example bad values
bad_examples = working[invalid_mask].unique()[:5].tolist()
self._record(
column=column,
check_name="allowed_values",
check_category="consistency",
passed=passed,
score=round(1.0 - invalid_rate, 4),
detail=(
f"{invalid_count:,} values not in allowed set "
f"({invalid_rate:.2%}). "
f"Examples: {bad_examples}. "
f"Allowed: {allowed}."
),
)
The allowed values check is where case sensitivity bites people. A status column with "Active", "active", and "ACTIVE" is three values, not one, as far as .isin() is concerned. By exposing case_sensitive as a config option, you let the domain expert decide — don't hardcode that assumption into the check.
Now add the cross-column consistency check, which operates at the DataFrame level rather than the series level:
def _check_cross_column_consistency(self, rules: dict) -> None:
"""
Cross-column checks are defined at the dataset level, not per-column.
Example rule: ('start_date', 'end_date', 'end_after_start')
"""
for rule in rules.get("cross_column_rules", []):
col_a, col_b, rule_name = rule
if col_a not in self.df.columns or col_b not in self.df.columns:
continue
if rule_name == "end_after_start":
both_present = self.df[[col_a, col_b]].notna().all(axis=1)
subset = self.df[both_present]
violation_mask = subset[col_b] < subset[col_a]
violation_count = violation_mask.sum()
total_checkable = len(subset)
violation_rate = violation_count / total_checkable if total_checkable > 0 else 0.0
passed = violation_count == 0
self._record(
column=f"{col_a} vs {col_b}",
check_name="end_after_start",
check_category="consistency",
passed=passed,
score=round(1.0 - violation_rate, 4),
detail=(
f"{violation_count:,} rows where {col_b} < {col_a} "
f"({violation_rate:.2%} of {total_checkable:,} checkable rows)."
),
)
Tip
Cross-column checks are the most valuable and the most underused. End-before-start dates, negative quantities with positive amounts, closed accounts with recent transactions — these are the logic errors that analysts miss because they're checking columns in isolation. Build them into the config as first-class citizens.
Validity checks are the most customizable layer. They handle regex patterns, numeric range enforcement, outlier detection, and pseudo-null detection:
def _check_validity(self, column: str, rules: dict) -> None:
series = self.df[column]
non_null = series.dropna()
n_non_null = len(non_null)
# --- regex pattern check ---
if "pattern" in rules and n_non_null > 0:
pattern = rules["pattern"]
try:
matches = non_null.astype(str).str.match(pattern)
fail_count = (~matches).sum()
fail_rate = fail_count / n_non_null
passed = fail_count == 0
bad_examples = non_null.astype(str)[~matches].unique()[:5].tolist()
self._record(
column=column,
check_name="pattern_match",
check_category="validity",
passed=passed,
score=round(1.0 - fail_rate, 4),
detail=(
f"{fail_count:,} values failed pattern '{pattern}' "
f"({fail_rate:.2%}). Examples: {bad_examples}."
),
)
except re.error as e:
self._record(
column=column,
check_name="pattern_match",
check_category="validity",
passed=False,
score=0.0,
detail=f"Invalid regex pattern '{pattern}': {e}",
)
# --- numeric range check ---
if "numeric_range" in rules:
low, high = rules["numeric_range"]
try:
numeric = pd.to_numeric(non_null, errors="coerce")
coerce_failures = numeric.isna().sum() - non_null.isna().sum()
out_of_range = ((numeric < low) | (numeric > high)).sum()
fail_count = out_of_range + max(coerce_failures, 0)
fail_rate = fail_count / n_non_null if n_non_null > 0 else 0.0
passed = fail_count == 0
self._record(
column=column,
check_name="numeric_range",
check_category="validity",
passed=passed,
score=round(1.0 - fail_rate, 4),
detail=(
f"{out_of_range:,} values outside [{low}, {high}]. "
f"{max(coerce_failures, 0):,} non-numeric values. "
f"Total failures: {fail_count:,} ({fail_rate:.2%})."
),
)
except Exception as e:
self._record(
column=column,
check_name="numeric_range",
check_category="validity",
passed=False,
score=0.0,
detail=f"Error during numeric range check: {e}",
)
# --- outlier check (IQR method) ---
if rules.get("outlier_check"):
try:
numeric = pd.to_numeric(non_null, errors="coerce").dropna()
if len(numeric) > 10:
q1 = numeric.quantile(0.25)
q3 = numeric.quantile(0.75)
iqr = q3 - q1
lower_fence = q1 - 3.0 * iqr
upper_fence = q3 + 3.0 * iqr
outlier_mask = (numeric < lower_fence) | (numeric > upper_fence)
outlier_count = outlier_mask.sum()
outlier_rate = outlier_count / len(numeric)
self._record(
column=column,
check_name="outlier_iqr",
check_category="validity",
passed=outlier_rate < 0.01, # flag if >1% are outliers
score=round(max(0.0, 1.0 - (outlier_rate / 0.01)), 4),
detail=(
f"{outlier_count:,} outliers ({outlier_rate:.2%}) "
f"outside [{lower_fence:.2f}, {upper_fence:.2f}] "
f"(3×IQR fences). Q1={q1:.2f}, Q3={q3:.2f}."
),
)
except Exception as e:
self._record(
column=column,
check_name="outlier_iqr",
check_category="validity",
passed=False,
score=0.0,
detail=f"Error during outlier check: {e}",
)
# --- pseudo-null detection ---
if rules.get("check_pseudo_nulls", True) and pd.api.types.is_object_dtype(series):
pseudo_null_patterns = [
r"^\s*$", # whitespace only
r"^n/?a$", # N/A, NA, n/a
r"^null$", # null
r"^none$", # none
r"^-$", # dash
r"^0{1,5}$", # 0, 00, 000 (suspicious for IDs)
r"^\.$", # single dot
r"^unknown$", # unknown
r"^tbd$", # TBD
]
combined_pattern = "|".join(pseudo_null_patterns)
pseudo_null_mask = (
non_null.astype(str)
.str.strip()
.str.lower()
.str.match(combined_pattern)
)
pseudo_count = pseudo_null_mask.sum()
pseudo_rate = pseudo_count / n_non_null if n_non_null > 0 else 0.0
passed = pseudo_count == 0
if pseudo_count > 0: # only record if we found something
self._record(
column=column,
check_name="pseudo_null_detection",
check_category="validity",
passed=passed,
score=round(1.0 - pseudo_rate, 4),
detail=(
f"{pseudo_count:,} pseudo-null values ({pseudo_rate:.2%}). "
f"Examples: {non_null[pseudo_null_mask].unique()[:5].tolist()}."
),
)
# --- date range check ---
if "date_range" in rules and pd.api.types.is_datetime64_any_dtype(series):
start_bound, end_bound = pd.to_datetime(rules["date_range"][0]), pd.to_datetime(rules["date_range"][1])
dt_series = series.dropna()
out_of_range = ((dt_series < start_bound) | (dt_series > end_bound)).sum()
out_rate = out_of_range / len(dt_series) if len(dt_series) > 0 else 0.0
passed = out_of_range == 0
self._record(
column=column,
check_name="date_range",
check_category="validity",
passed=passed,
score=round(1.0 - out_rate, 4),
detail=(
f"{out_of_range:,} dates outside [{rules['date_range'][0]}, "
f"{rules['date_range'][1]}] ({out_rate:.2%})."
),
)
The pseudo-null detection is worth lingering on. When you learn about text cleanup with pandas string methods, you encounter this problem: string columns that look populated but contain values like "N/A", "-", "unknown", or just whitespace. These pass .notna() checks but are functionally missing. The pattern list above catches the most common offenders, and you can extend it for domain-specific variants (like "999-999-9999" for placeholder phone numbers).
Now connect the individual check methods:
def _run_column_checks(self, column: str, rules: dict) -> None:
"""Run all applicable checks for a single column."""
self._check_completeness(column, rules)
self._check_consistency(column, rules)
self._check_validity(column, rules)
And add the scoring aggregation method, which takes the flat results DataFrame and produces a column-level summary:
def score_summary(self) -> pd.DataFrame:
"""
Aggregate check-level results into a column-level summary with
a composite quality score and a pass/fail determination.
"""
if not self.results:
raise ValueError("No results found. Run .run() first.")
results_df = pd.DataFrame(self.results)
summary = (
results_df
.groupby("column")
.agg(
total_checks=("check_name", "count"),
checks_passed=("passed", "sum"),
avg_score=("score", "mean"),
min_score=("score", "min"),
failed_checks=("passed", lambda x: (~x).sum()),
)
.reset_index()
)
summary["pass_rate"] = summary["checks_passed"] / summary["total_checks"]
summary["composite_score"] = (
summary["avg_score"] * 0.6 + summary["min_score"] * 0.4
)
summary["status"] = summary["composite_score"].apply(
lambda s: "PASS" if s >= 0.95 else ("WARN" if s >= 0.80 else "FAIL")
)
summary = summary.sort_values("composite_score", ascending=True)
return summary
def dataset_score(self) -> dict:
"""Return a single dataset-level quality score."""
summary = self.score_summary()
overall = summary["composite_score"].mean()
fail_cols = (summary["status"] == "FAIL").sum()
warn_cols = (summary["status"] == "WARN").sum()
pass_cols = (summary["status"] == "PASS").sum()
return {
"dataset_name": self.dataset_name,
"overall_score": round(overall, 4),
"status": "PASS" if overall >= 0.95 else ("WARN" if overall >= 0.80 else "FAIL"),
"columns_checked": len(summary),
"columns_pass": int(pass_cols),
"columns_warn": int(warn_cols),
"columns_fail": int(fail_cols),
}
Note
The composite score formula — avg_score * 0.6 + min_score * 0.4 — deliberately penalizes columns that have even one catastrophically failing check. A column with five perfect scores and one zero scores lower than a column with six middling scores. This is intentional: a single zero means something is genuinely broken, and blending it into an average would hide it.
Let's put this to work on a realistic synthetic dataset. We'll generate one that has the kinds of problems a real vendor feed would have:
import pandas as pd
import numpy as np
np.random.seed(42)
n = 10_000
# Build a realistic messy transactions dataset
df = pd.DataFrame({
"customer_id": (
np.random.choice(
list(range(1, 5001)) + [None] * 200, # ~2% nulls
size=n
)
),
"email": [
f"user{i}@example.com" if np.random.random() > 0.08
else np.random.choice(["notanemail", "missing@", "N/A", None, "user@"])
for i in range(n)
],
"transaction_date": pd.to_datetime(
np.random.choice(
pd.date_range("2022-01-01", "2025-06-01", freq="D"),
size=n
)
),
"amount": np.concatenate([
np.random.uniform(0.01, 5000, size=int(n * 0.95)),
np.random.uniform(-500, -0.01, size=int(n * 0.03)), # refunds/errors
np.random.uniform(50_000, 500_000, size=int(n * 0.02)), # outliers
]),
"status": np.random.choice(
["active", "inactive", "pending", "ACTIVE", "Active", "cancelled", None],
size=n,
p=[0.5, 0.2, 0.15, 0.05, 0.04, 0.03, 0.03]
),
"start_date": pd.to_datetime(
np.random.choice(pd.date_range("2022-01-01", "2024-01-01", freq="D"), size=n)
),
"end_date": pd.to_datetime(
np.random.choice(pd.date_range("2022-06-01", "2025-06-01", freq="D"), size=n)
),
})
# Introduce some end_date < start_date violations
bad_idx = np.random.choice(n, size=200, replace=False)
df.loc[bad_idx, "end_date"] = df.loc[bad_idx, "start_date"] - pd.Timedelta(days=np.random.randint(1, 90))
Now configure and run:
config = {
"customer_id": {
"required": True,
"dtype_expected": "float", # nullable ints load as float
"max_null_rate": 0.01,
},
"email": {
"required": True,
"pattern": r"^[\w\.\+\-]+@[\w\-]+\.[a-zA-Z]{2,}$",
"max_null_rate": 0.02,
"check_pseudo_nulls": True,
},
"transaction_date": {
"required": True,
"dtype_expected": "datetime64",
"date_range": ("2022-01-01", "2024-12-31"),
},
"amount": {
"required": True,
"numeric_range": (0.01, 50_000),
"outlier_check": True,
},
"status": {
"required": True,
"allowed_values": ["active", "inactive", "pending"],
"case_sensitive": False,
"max_null_rate": 0.02,
},
}
cross_column_rules = [
("start_date", "end_date", "end_after_start"),
]
scorecard = DataQualityScorecard(df, config, dataset_name="vendor_transactions_q2_2024")
results_df = scorecard.run()
# Run cross-column checks
scorecard._check_cross_column_consistency({"cross_column_rules": cross_column_rules})
# View results
print(results_df.to_string())
print("\n--- Column Summary ---")
print(scorecard.score_summary().to_string())
print("\n--- Dataset Score ---")
print(scorecard.dataset_score())
When you run this, you'll see output like:
--- Dataset Score ---
{
'dataset_name': 'vendor_transactions_q2_2024',
'overall_score': 0.7834,
'status': 'WARN',
'columns_checked': 5,
'columns_pass': 1,
'columns_warn': 2,
'columns_fail': 2
}
Before the analysis even begins, you know exactly which columns have problems and why.
Raw Python dictionaries aren't what you hand to a VP. Let's build the export layer. This integrates naturally with automating Excel reports with pandas and openpyxl:
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule
from openpyxl.utils import get_column_letter
def export_scorecard_to_excel(
scorecard: DataQualityScorecard,
output_path: str,
) -> None:
"""Export scorecard results to a formatted Excel workbook."""
results_df = pd.DataFrame(scorecard.results)
summary_df = scorecard.score_summary()
dataset_score = scorecard.dataset_score()
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
# Sheet 1: Dataset Overview
overview_df = pd.DataFrame([dataset_score])
overview_df.to_excel(writer, sheet_name="Overview", index=False)
# Sheet 2: Column Summary
summary_df.to_excel(writer, sheet_name="Column Summary", index=False)
# Sheet 3: Check Detail
results_df.to_excel(writer, sheet_name="Check Detail", index=False)
# Now apply formatting
wb = openpyxl.load_workbook(output_path)
# --- Format Column Summary sheet ---
ws = wb["Column Summary"]
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
yellow_fill = PatternFill(start_color="FFFF99", end_color="FFFF99", fill_type="solid")
green_fill = PatternFill(start_color="CCFFCC", end_color="CCFFCC", fill_type="solid")
header_font = Font(bold=True, size=11)
for cell in ws[1]:
cell.font = header_font
cell.alignment = Alignment(horizontal="center")
# Find the "status" column index
headers = [cell.value for cell in ws[1]]
status_col_idx = headers.index("status") + 1 # 1-indexed
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
status_cell = row[status_col_idx - 1]
if status_cell.value == "FAIL":
for cell in row:
cell.fill = red_fill
elif status_cell.value == "WARN":
for cell in row:
cell.fill = yellow_fill
elif status_cell.value == "PASS":
for cell in row:
cell.fill = green_fill
# Auto-size columns
for ws_name in ["Overview", "Column Summary", "Check Detail"]:
ws_target = wb[ws_name]
for col in ws_target.columns:
max_len = max(
(len(str(cell.value)) for cell in col if cell.value is not None),
default=10,
)
ws_target.column_dimensions[get_column_letter(col[0].column)].width = min(max_len + 4, 60)
wb.save(output_path)
print(f"Scorecard exported to {output_path}")
When you open this workbook, you see three sheets: a top-level overview with the dataset score, a color-coded column summary where red rows demand immediate attention, and a full drill-down of every check that ran. This is a report you can send without explanation.
Tip
If you're gating a pipeline on data quality, check dataset_score()["status"] in your script and raise an exception if it's "FAIL". This prevents downstream analysis from running on bad data and creates an audit trail. See Building a Reusable ETL Pipeline in pandas for how to integrate quality gates into a full pipeline.
On a 10,000-row DataFrame, the scorecard runs in milliseconds. At 5 million rows, some checks get expensive. Here's how to handle scale:
Sampling for pattern and outlier checks. Regex matching on 5M strings is slow. For the pattern_match check, add a sampling option:
def _check_validity_sampled(self, column: str, rules: dict, sample_n: int = 100_000) -> None:
series = self.df[column]
if len(series) > sample_n:
series = series.sample(n=sample_n, random_state=42)
# ... rest of validity checks use the sampled series
This is a legitimate trade-off: a 2% pattern failure rate detected on 100K rows is almost certainly representative of the full dataset. Just document in the detail string that the check ran on a sample.
Vectorized operations only. Every check in this scorecard uses pandas vectorized operations — .isna().sum(), .str.match(), .isin(), .quantile(). Avoid row-level loops or .apply() with complex functions on large string columns. The writing fast pandas code lesson explains the performance gap in detail, but the short version is: vectorized operations run in C; Python loops run in Python. The difference is 10-100x.
Chunked reading for very large files. If the DataFrame itself is too large to load in memory, you can run the scorecard on chunks and aggregate:
def run_scorecard_chunked(filepath: str, config: dict, chunksize: int = 100_000) -> dict:
chunk_results = []
for chunk in pd.read_csv(filepath, chunksize=chunksize):
sc = DataQualityScorecard(chunk, config)
sc.run()
chunk_results.append(sc.dataset_score())
# Aggregate chunk scores — simplified average
overall = np.mean([r["overall_score"] for r in chunk_results])
return {"overall_score": round(overall, 4), "chunks_processed": len(chunk_results)}
This won't give you exact null counts across the full file (you'd need to track numerator and denominator separately), but it's a practical approximation. See Handling Large Datasets in Python for a deeper treatment of this pattern.
Build a scorecard for a realistic HR dataset. Use the following setup:
np.random.seed(7)
n = 5_000
hr_df = pd.DataFrame({
"employee_id": range(1, n + 1),
"department": np.random.choice(
["Engineering", "Sales", "HR", "Finance", "Ops", "ENGINEERING", None],
size=n, p=[0.3, 0.25, 0.15, 0.15, 0.12, 0.02, 0.01]
),
"hire_date": pd.to_datetime(
np.random.choice(pd.date_range("1990-01-01", "2024-12-31", freq="D"), size=n)
),
"termination_date": [
pd.to_datetime(np.random.choice(pd.date_range("2010-01-01", "2025-06-01", freq="D")))
if np.random.random() < 0.3 else pd.NaT
for _ in range(n)
],
"annual_salary": np.concatenate([
np.random.uniform(30_000, 250_000, size=int(n * 0.95)),
np.random.uniform(-5_000, -100, size=int(n * 0.02)),
[None] * int(n * 0.03),
]),
"email": [
f"emp{i}@company.com" if np.random.random() > 0.05
else np.random.choice(["", "N/A", "not_an_email", None])
for i in range(n)
],
})
Your tasks:
Write a config dictionary for this dataset. The department column has a known set of valid values. annual_salary should be positive and within a reasonable range. email should match a pattern. hire_date should be between 1990 and today. termination_date should always be after hire_date when present.
Instantiate the scorecard, run it, and add the cross-column check for termination_date after hire_date.
Call score_summary() and identify which columns fail and why.
Export the results to Excel with formatting.
Challenge: Add a new check type to the _check_validity method: a max_cardinality check for the department column that flags if the number of unique values exceeds a configured threshold (accounting for case variations). Hint: normalize to lowercase before counting.
Mistake: Treating null rate and pseudo-null detection as redundant. They're not. A column can have 0% null rate and 8% pseudo-nulls. Always run both. The completeness check catches real nulls; pseudo-null detection catches the string debris that real data is full of.
Mistake: Using binary pass/fail for everything. If your check produces either 1.0 or 0.0 with nothing in between, you lose the ability to rank problems by severity. A column that's 1% out of range is different from a column that's 60% out of range. Graduated scores let you prioritize.
Mistake: Forgetting to copy the DataFrame.
The self.df = df.copy() line in __init__ is non-negotiable. Without it, any mutation inside the scorecard (for example, type conversions done for checking purposes) would silently modify the caller's DataFrame. This is the kind of bug that takes hours to diagnose.
Mistake: Config that's too strict for the data's actual nature.
Setting max_null_rate: 0.0 on a column that legitimately has optional values (like termination_date, which is null for active employees) will produce constant false alarms. False alarms train people to ignore the scorecard. Be precise about what "good" means for each column in its actual business context.
Mistake: Hardcoding the threshold for outlier detection.
The 1% threshold in the outlier_iqr check means "flag if more than 1% of values are extreme outliers." For a transaction dataset, that might be right. For a salary dataset in a company with both interns and C-suite executives, the legitimate spread is much wider. Make the threshold configurable.
Troubleshooting: The dtype_check returns unexpected failures.
Pandas dtype checking is subtle. A column of integers with even one null value becomes float64 because integer NaN isn't natively supported in older pandas versions (it now is, with pd.Int64Dtype()). If you're running checks against a config written before the data was loaded, dtype mismatches are often an artifact of the loading process rather than actual data problems. Check with pd.api.types.is_integer_dtype() rather than exact string matching.
Troubleshooting: score_summary() raises a KeyError on "passed".
This happens when you call score_summary() before calling run(), or when run() completed without recording any results (typically because the config was empty). The ValueError guard at the top of score_summary() should catch this, but confirm that your config dictionary has at least one key matching a column that exists in the DataFrame. Use set(config.keys()) - set(df.columns) to find mismatches quickly.
Warning
If you're running this scorecard in a production pipeline — one that's scheduled to run nightly on fresh data — make sure you store the results to a persistent location (a database table, a dated Excel file, or a JSON log). A quality check that runs and then gets discarded is an opportunity cost. Use the historical results to detect drift: if a column that was 99% complete last month is now 87% complete, that's a signal worth alerting on. The auditing and reconciling data across sources lesson covers how to build that kind of longitudinal comparison.
You've built a complete, production-grade data quality scorecard in pandas. Let's recap what you now have:
DataQualityScorecard class with a clean interface: pass in a DataFrame and a config, call .run(), get back a structured results DataFrameThe design decisions that matter most here aren't the code — they're the principles. Keep check results in a standard shape so you can aggregate them. Separate configuration from implementation so domain rules don't live in code. Use graduated scores rather than binary pass/fail so you can rank severity. Build checks that don't crash on missing columns or type errors so the scorecard is reliable under real-world conditions.
Where to go from here:
DataQualityMonitor wrapper that compares two scorecard runs (today vs. yesterday) and flags regressions — this is the foundation of data observabilitydataset_score()["status"] == "FAIL", halt and alert rather than writing bad data downstreamThe scorecard you've built here isn't just a quality check. It's documentation. When you hand someone a dataset with a scorecard attached, you're telling them exactly what you know about its limitations — and that's what separates a professional analysis from an amateur one.