Show code
from pathlib import Path
from datetime import datetime, timezone
import hashlib
import json
import re
import sys
import matplotlib
import matplotlib.pyplot as plt
import numpy as np
import pandas as pdA hands-on exercise for inspecting, cleaning, validating, and documenting a real-world dataset with Python
Hands-on exercises and step-by-step tools for doing, applying, and building evidence.
A hands-on exercise for inspecting, cleaning, validating, and documenting a real-world dataset with Python
This CDI Practical is designed to be completed actively rather than read passively.
You will work with a realistic tabular dataset containing common data-quality problems, including missing values, duplicate records, inconsistent categories, incorrect data types, invalid numeric ranges, irregular dates, and unreliable identifiers.
Work through the practical in order. Each section builds on the previous one and contributes to a final cleaned dataset, a documented quality-assessment report, and portfolio-ready evidence of your work.
You are encouraged to:
The practical is designed to render as an HTML page and can be followed in a browser. You may use JupyterLab, VS Code, Positron, Google Colab, or another Python environment that supports notebooks or scripts.
Complete the practical in four passes.
Inspect the rows, columns, data types, missing values, categories, ranges, dates, and identifiers before making changes.
Translate the dataset description into explicit validation rules. A value should not be labelled invalid merely because it looks unusual.
Apply documented cleaning decisions, then rerun the checks to verify that the cleaned dataset meets the agreed rules.
Export the cleaned data, quality-control tables, and a concise data-quality statement that explains what changed, what remained unresolved, and how the dataset is now suitable for analysis.
Data cleaning is often treated as a preliminary task, but it is part of the analysis itself.
Every correction, exclusion, replacement, and standardization decision can influence later results. A reliable workflow therefore needs more than a sequence of commands. It needs:
In this practical, you will clean a fictional service-delivery dataset containing participant records. The dataset is deliberately imperfect so that you can practise realistic decision-making.
The workflow uses Python with pandas, numpy, matplotlib, and the standard library.
By the end of the practical, you will create:
Suggested output structure:
results/
├── figures/
│ ├── missing-values-before-cleaning.png
│ └── data-quality-issues-summary.png
├── quality-control/
│ ├── missing-values.csv
│ ├── duplicate-records.csv
│ ├── invalid-ranges.csv
│ ├── invalid-dates.csv
│ ├── invalid-identifiers.csv
│ └── issue-register.csv
└── tables/
├── cleaned-service-data.csv
├── quarantined-service-data.csv
├── before-after-quality-summary.csv
├── data-quality-summary.csv
├── cleaning-log.csv
└── run-manifest.json
The practical uses:
data/example-service-data.csv
The example dataset should contain fields such as:
record_idparticipant_nameregionservice_typeagevisit_datesatisfaction_scorefollow_up_requiredcontact_emailFrom the project root, create and activate a virtual environment, then install the exact tested package versions:
python3 -m venv .venv
source .venv/bin/activate # Windows PowerShell: .venv\Scripts\Activate.ps1
python -m pip install -r requirements.txtfrom pathlib import Path
from datetime import datetime, timezone
import hashlib
import json
import re
import sys
import matplotlib
import matplotlib.pyplot as plt
import numpy as np
import pandas as pdRESULTS_DIR = Path("results")
FIGURES_DIR = RESULTS_DIR / "figures"
TABLES_DIR = RESULTS_DIR / "tables"
QUALITY_DIR = RESULTS_DIR / "quality-control"
for directory in [FIGURES_DIR, TABLES_DIR, QUALITY_DIR]:
directory.mkdir(parents=True, exist_ok=True)Before cleaning, define what one row represents and what each column is intended to contain.
For this dataset:
record_id should uniquely identify each record;region should contain one approved region label;service_type should contain one approved service category;age should contain plausible whole-number ages;visit_date should contain valid dates;satisfaction_score should range from 1 to 5;follow_up_required should contain a consistent yes/no value;contact_email should contain either a plausible email address or a missing value.A data dictionary makes these assumptions explicit.
data_dictionary = pd.DataFrame(
{
"column": [
"record_id",
"participant_name",
"region",
"service_type",
"age",
"visit_date",
"satisfaction_score",
"follow_up_required",
"contact_email",
],
"expected_type": [
"string",
"string",
"category",
"category",
"integer",
"date",
"integer",
"boolean-like",
"string",
],
"validation_rule": [
"unique and non-missing",
"non-empty after trimming",
"approved region label",
"approved service category",
"between 0 and 110",
"valid date and not in the future",
"between 1 and 5",
"Yes or No",
"valid email format or missing",
],
}
)
data_dictionary| column | expected_type | validation_rule | |
|---|---|---|---|
| 0 | record_id | string | unique and non-missing |
| 1 | participant_name | string | non-empty after trimming |
| 2 | region | category | approved region label |
| 3 | service_type | category | approved service category |
| 4 | age | integer | between 0 and 110 |
| 5 | visit_date | date | valid date and not in the future |
| 6 | satisfaction_score | integer | between 1 and 5 |
| 7 | follow_up_required | boolean-like | Yes or No |
| 8 | contact_email | string | valid email format or missing |
Data quality is not a single property. A dataset may be complete but inconsistent, or valid but duplicated.
This practical evaluates six dimensions:
Accuracy is often the hardest dimension to verify from a dataset alone. When the correct value cannot be established, flagging the issue is usually safer than guessing.
Never overwrite the original input file.
DATA_PATH = Path("data/example-service-data.csv")
if not DATA_PATH.exists():
raise FileNotFoundError(
f"Expected input file not found: {DATA_PATH}. "
"Add the example dataset before running the practical."
)raw_data = pd.read_csv(DATA_PATH)
data = raw_data.copy(deep=True)
# Stable traceability key for returning to the original source row.
data.insert(0, "source_row_number", np.arange(2, len(data) + 2))
raw_file_sha256 = hashlib.sha256(DATA_PATH.read_bytes()).hexdigest()
print(f"Rows: {data.shape[0]:,}")
print(f"Columns: {data.shape[1]:,}")Rows: 123
Columns: 10
cleaning_log = []
issue_register = []
def log_change(step, column, action, affected_rows, rationale):
cleaning_log.append(
{
"step": step,
"column": column,
"action": action,
"affected_rows": int(affected_rows),
"rationale": rationale,
}
)
def register_issues(mask, rule_id, column, severity, message, status="open"):
"""Append one issue per affected source row to the audit register."""
affected = data.loc[mask, ["source_row_number", "record_id"]].copy()
for row in affected.itertuples(index=False):
issue_register.append(
{
"source_row_number": int(row.source_row_number),
"record_id": row.record_id,
"rule_id": rule_id,
"column": column,
"severity": severity,
"message": message,
"status": status,
}
)Begin with a broad inspection.
display(data.head())
display(data.sample(min(5, len(data)), random_state=2026))
display(data.tail())| source_row_number | record_id | participant_name | region | service_type | age | visit_date | satisfaction_score | follow_up_required | contact_email | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2 | REC-0017 | Hassan Mtei | North | Training | 16.0 | NaN | 4.0 | true | hassan.mtei@example.com |
| 1 | 3 | REC-0061 | Baraka Lema | Central | Training | 81.0 | 2026-06-14 | 3.0 | No | baraka.lema@service.net |
| 2 | 4 | REC-0066 | Esther Kweka | South | SCREENING | 79.0 | 2025-09-11 | 3.0 | Yes | esther.kweka@mail.org |
| 3 | 5 | REC-0077 | John Mrema | North | Referral | 47.0 | 2026-01-20 | NaN | No | john.mrema@example.com |
| 4 | 6 | REC-0028 | Irene Kweka | East | Consultation | 54.0 | 2025-12-21 | 5.0 | 1 | irene.kweka@example.com |
| source_row_number | record_id | participant_name | region | service_type | age | visit_date | satisfaction_score | follow_up_required | contact_email | |
|---|---|---|---|---|---|---|---|---|---|---|
| 111 | 113 | REC-0085 | Paul Kweka | South | Screening | 79.0 | 2025-11-04 | 3.0 | Yes | paul.kweka@example.com |
| 18 | 20 | REC-0020 | Kelvin Mbise | South | Referral | 45.0 | 2025-12-14 | 4.0 | Yes | kelvin.mbise@service.net |
| 80 | 82 | REC-0011 | Queen Mbise | Central | Screening | 32.0 | 2025-02-18 | 2.0 | No | queen.mbise@example.com |
| 108 | 110 | REC-0117 | David Mtei | Central | Referral | 38.0 | 2025-01-23 | 4.0 | Yes | NaN |
| 120 | 122 | REC-0109 | Samuel Kweka | South | Consultation | 48.0 | 2026-04-14 | NaN | No | samuel.kweka@example.com |
| source_row_number | record_id | participant_name | region | service_type | age | visit_date | satisfaction_score | follow_up_required | contact_email | |
|---|---|---|---|---|---|---|---|---|---|---|
| 118 | 120 | REC-0014 | Linda Mashauri | Northern | Referral | 77.0 | 2026-02-21 | 4.0 | No | linda.mashauri@service.net |
| 119 | 121 | REC-0035 | Paul Mashauri | North | Training | 66.0 | 2025-01-11 | 5.0 | Yes | NaN |
| 120 | 122 | REC-0109 | Samuel Kweka | South | Consultation | 48.0 | 2026-04-14 | NaN | No | samuel.kweka@example.com |
| 121 | 123 | REC-0001 | Clara Massawe | Central | Training | 37.0 | 2025-11-13 | NaN | Yes | clara.massawe@mail.org |
| 122 | 124 | REC-0009 | Tatu Mollel | North | Training | -4.0 | 2026-02-23 | 4.0 | No | tatu.mollel@mail.org |
Check the shape, column names, and data types.
structure_summary = pd.DataFrame(
{
"column": data.columns,
"dtype": data.dtypes.astype(str).values,
"non_missing": data.notna().sum().values,
"missing": data.isna().sum().values,
"unique_values": data.nunique(dropna=True).values,
}
)
structure_summary| column | dtype | non_missing | missing | unique_values | |
|---|---|---|---|---|---|
| 0 | source_row_number | int64 | 123 | 0 | 123 |
| 1 | record_id | object | 122 | 1 | 117 |
| 2 | participant_name | object | 120 | 3 | 99 |
| 3 | region | object | 118 | 5 | 12 |
| 4 | service_type | object | 119 | 4 | 11 |
| 5 | age | float64 | 117 | 6 | 59 |
| 6 | visit_date | object | 120 | 3 | 106 |
| 7 | satisfaction_score | float64 | 114 | 9 | 8 |
| 8 | follow_up_required | object | 123 | 0 | 10 |
| 9 | contact_email | object | 113 | 10 | 102 |
Inspect descriptive summaries.
display(data.describe(include="all").transpose())| count | unique | top | freq | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| source_row_number | 123.0 | NaN | NaN | NaN | 63.0 | 35.651087 | 2.0 | 32.5 | 63.0 | 93.5 | 124.0 |
| record_id | 122 | 117 | REC-0063 | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| participant_name | 120 | 99 | John Mrema | 3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| region | 118 | 12 | North | 31 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| service_type | 119 | 11 | Training | 41 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| age | 117.0 | NaN | NaN | NaN | 56.230769 | 90.476458 | -4.0 | 31.0 | 49.0 | 63.0 | 999.0 |
| visit_date | 120 | 106 | 2025-12-23 | 3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| satisfaction_score | 114.0 | NaN | NaN | NaN | 3.561404 | 1.475673 | 0.0 | 3.0 | 4.0 | 5.0 | 9.0 |
| follow_up_required | 123 | 10 | No | 73 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| contact_email | 113 | 102 | john.mrema@example.com | 3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
Questions to answer before cleaning:
original_columns = data.columns.tolist()
standardized_columns = (
pd.Index(original_columns)
.str.strip()
.str.lower()
.str.replace(r"[^a-z0-9]+", "_", regex=True)
.str.strip("_")
.tolist()
)
duplicate_standardized_columns = sorted(
{
column
for column in standardized_columns
if standardized_columns.count(column) > 1
}
)
if duplicate_standardized_columns:
raise ValueError(
"Column-name normalization created collisions: "
f"{duplicate_standardized_columns}"
)
data.columns = standardized_columns
# Columns that define an exact source record. The traceability key is excluded
# so it does not prevent genuine duplicate rows from being detected.
record_columns = [
column for column in data.columns
if column != "source_row_number"
]
required_columns = {
"source_row_number",
"record_id",
"participant_name",
"region",
"service_type",
"age",
"visit_date",
"satisfaction_score",
"follow_up_required",
"contact_email",
}
missing_columns = sorted(required_columns.difference(data.columns))
if missing_columns:
raise ValueError(f"Required columns are missing: {missing_columns}")
print("All required columns are present.")
changed_columns = sum(
original != updated
for original, updated in zip(original_columns, data.columns)
)
log_change(
step="structure",
column="all columns",
action="standardized column names",
affected_rows=changed_columns,
rationale="Use consistent lowercase snake_case names.",
)
data.columns.tolist()All required columns are present.
['source_row_number',
'record_id',
'participant_name',
'region',
'service_type',
'age',
'visit_date',
'satisfaction_score',
'follow_up_required',
'contact_email']
numeric_columns = ["age", "satisfaction_score"]
for column in numeric_columns:
before_missing = data[column].isna().sum()
data[column] = pd.to_numeric(data[column], errors="coerce")
after_missing = data[column].isna().sum()
log_change(
step="types",
column=column,
action="converted to numeric",
affected_rows=after_missing - before_missing,
rationale="Values that could not be parsed were converted to missing for review.",
)before_missing_dates = data["visit_date"].isna().sum()
# Preserve the source representation before parsing for audit and recovery.
data["visit_date_raw"] = data["visit_date"].astype("string")
data["visit_date"] = pd.to_datetime(
data["visit_date"],
errors="coerce",
)
after_missing_dates = data["visit_date"].isna().sum()
unparseable_date_mask = (
data["visit_date_raw"].notna()
& data["visit_date"].isna()
)
register_issues(
unparseable_date_mask,
rule_id="DATE_PARSE_001",
column="visit_date",
severity="error",
message="Source date could not be parsed.",
)
log_change(
step="types",
column="visit_date",
action="parsed as datetime",
affected_rows=after_missing_dates - before_missing_dates,
rationale="Unparseable date values were converted to missing for review.",
)Missing values should be measured before deciding how to handle them.
missing_summary = (
data.isna()
.sum()
.rename("missing_count")
.to_frame()
.assign(
missing_percent=lambda x: (
x["missing_count"] / len(data) * 100
).round(2)
)
.sort_values("missing_percent", ascending=False)
)
missing_summary.to_csv(
QUALITY_DIR / "missing-values.csv",
index_label="column",
)
missing_summary| missing_count | missing_percent | |
|---|---|---|
| contact_email | 10 | 8.13 |
| satisfaction_score | 9 | 7.32 |
| age | 6 | 4.88 |
| visit_date | 6 | 4.88 |
| region | 5 | 4.07 |
| service_type | 4 | 3.25 |
| participant_name | 3 | 2.44 |
| visit_date_raw | 3 | 2.44 |
| record_id | 1 | 0.81 |
| source_row_number | 0 | 0.00 |
| follow_up_required | 0 | 0.00 |
missing_plot_data = (
missing_summary
.reset_index(names="column")
.query("missing_count > 0")
)
fig, ax = plt.subplots(figsize=(8, 5))
ax.barh(
missing_plot_data["column"],
missing_plot_data["missing_percent"],
)
ax.set_xlabel("Missing values (%)")
ax.set_ylabel("")
ax.set_title("Missing Values Before Cleaning")
ax.invert_yaxis()
fig.tight_layout()
fig.savefig(
FIGURES_DIR / "missing-values-before-cleaning.png",
dpi=300,
bbox_inches="tight",
)
plt.show()
Do not automatically fill every missing value.
Use rules such as:
Unknown only when analytically appropriate;A missing value means the information is unavailable or unknown. It should not be replaced with zero unless zero is a valid and justified value for that field.
exact_duplicate_mask = data.duplicated(
subset=record_columns,
keep=False,
)
exact_duplicates = data.loc[exact_duplicate_mask].copy()
exact_duplicates.to_csv(
QUALITY_DIR / "duplicate-records.csv",
index=False,
)
print(f"Exact duplicate rows: {exact_duplicate_mask.sum():,}")Exact duplicate rows: 6
duplicate_id_mask = (
data["record_id"].notna()
& data["record_id"].duplicated(keep=False)
)
duplicate_identifiers = (
data.loc[duplicate_id_mask]
.sort_values("record_id")
)
duplicate_identifiers| source_row_number | record_id | participant_name | region | service_type | age | visit_date | satisfaction_score | follow_up_required | contact_email | visit_date_raw | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 19 | 21 | REC-0012 | Neema Mrema | South | Consultation | 26.0 | 2026-05-25 | 5.0 | No | neema.mrema@mail.org | 2026-05-25 |
| 67 | 69 | REC-0012 | Neema Mrema | South | Consultation | 26.0 | 2026-05-25 | 5.0 | No | neema.mrema@mail.org | 2026-05-25 |
| 49 | 51 | REC-0036 | Neema Kweka | North | Consultation | 29.0 | 2025-01-06 | 5.0 | No | neema.kweka@mail.org | 2025-01-06 |
| 62 | 64 | REC-0036 | Neema Kweka | North | Consultation | 29.0 | 2025-01-06 | 5.0 | No | neema.kweka@mail.org | 2025-01-06 |
| 25 | 27 | REC-0063 | Baraka Massawe | North | Training | 29.0 | 2025-01-14 | 4.0 | Yes | baraka.massawe@example.com | 2025-01-14 |
| 95 | 97 | REC-0063 | Esther Mrema | North | Training | 66.0 | 2026-05-17 | 5.0 | No | esther.mrema@example.com | 2026-05-17 |
| 3 | 5 | REC-0077 | John Mrema | North | Referral | 47.0 | 2026-01-20 | NaN | No | john.mrema@example.com | 2026-01-20 |
| 102 | 104 | REC-0077 | John Mrema | North | Referral | 47.0 | 2026-01-20 | NaN | No | john.mrema@example.com | 2026-01-20 |
| 41 | 43 | REC-0094 | Linda Mushi | Central | Referral | 22.0 | 2025-09-24 | 1.0 | No | linda.mushi@service.net | 2025-09-24 |
| 113 | 115 | REC-0094 | Samuel Nnko | West | Screening | 34.0 | 2025-12-23 | 3.0 | Yes | samuel.nnko@mail.org | 2025-12-23 |
register_issues(
duplicate_id_mask,
rule_id="ID_UNIQUE_001",
column="record_id",
severity="error",
message="Identifier is shared by more than one row.",
)rows_before = len(data)
data = data.drop_duplicates(subset=record_columns).copy()
rows_removed = rows_before - len(data)
log_change(
step="duplicates",
column="all columns",
action="removed exact duplicate rows",
affected_rows=rows_removed,
rationale="Rows were identical across all columns.",
)Rows sharing an identifier but differing elsewhere should not be removed automatically. They may represent data-entry errors, repeated events, or legitimate updates.
text_columns = [
"record_id",
"participant_name",
"region",
"service_type",
"follow_up_required",
"contact_email",
]
for column in text_columns:
original = data[column].copy()
data[column] = data[column].astype("string").str.strip()
changed = (
original.astype("string").fillna("<NA>")
!= data[column].fillna("<NA>")
).sum()
log_change(
step="text",
column=column,
action="trimmed leading and trailing whitespace",
affected_rows=changed,
rationale="Remove formatting differences that do not change meaning.",
)
# Treat empty strings as missing without converting genuine missing values to text.
for column in text_columns:
empty_mask = data[column].eq("")
data.loc[empty_mask, column] = pd.NA
log_change(
step="text",
column=column,
action="converted empty strings to missing",
affected_rows=empty_mask.sum(),
rationale="Empty text does not contain usable information.",
)region_map = {
"north": "North",
"northern": "North",
"south": "South",
"southern": "South",
"east": "East",
"eastern": "East",
"west": "West",
"western": "West",
"central": "Central",
"centre": "Central",
}
region_before = data["region"].copy()
data["region"] = (
data["region"]
.str.lower()
.map(region_map)
.fillna(data["region"])
)
region_changes = (
region_before.fillna("<NA>")
!= data["region"].fillna("<NA>")
).sum()
log_change(
step="categories",
column="region",
action="standardized equivalent region labels",
affected_rows=region_changes,
rationale="Merge spelling and naming variants into approved labels.",
)service_map = {
"consult": "Consultation",
"consultation": "Consultation",
"screening": "Screening",
"screen": "Screening",
"training": "Training",
"workshop": "Training",
"referral": "Referral",
"refer": "Referral",
}
service_before = data["service_type"].copy()
data["service_type"] = (
data["service_type"]
.str.lower()
.map(service_map)
.fillna(data["service_type"])
)
service_changes = (
service_before.fillna("<NA>")
!= data["service_type"].fillna("<NA>")
).sum()
log_change(
step="categories",
column="service_type",
action="standardized service categories",
affected_rows=service_changes,
rationale="Use one approved label for each service type.",
)follow_up_map = {
"yes": "Yes",
"y": "Yes",
"true": "Yes",
"1": "Yes",
"no": "No",
"n": "No",
"false": "No",
"0": "No",
}
follow_up_before = data["follow_up_required"].copy()
data["follow_up_required"] = (
data["follow_up_required"]
.str.lower()
.map(follow_up_map)
)
unknown_follow_up_mask = (
follow_up_before.notna()
& data["follow_up_required"].isna()
)
register_issues(
unknown_follow_up_mask,
rule_id="FOLLOWUP_DOMAIN_001",
column="follow_up_required",
severity="warning",
message="Value was not in the approved yes/no mapping.",
)
follow_up_changes = (
follow_up_before.fillna("<NA>")
!= data["follow_up_required"].fillna("<NA>")
).sum()
log_change(
step="categories",
column="follow_up_required",
action="standardized yes/no values",
affected_rows=follow_up_changes,
rationale="Use consistent binary labels.",
)approved_regions = {"North", "South", "East", "West", "Central"}
approved_services = {
"Consultation",
"Screening",
"Training",
"Referral",
}
unexpected_regions = sorted(
set(data["region"].dropna()) - approved_regions
)
unexpected_services = sorted(
set(data["service_type"].dropna()) - approved_services
)
print("Unexpected regions:", unexpected_regions)
print("Unexpected services:", unexpected_services)Unexpected regions: []
Unexpected services: []
invalid_region_mask = (
data["region"].isna()
| ~data["region"].isin(approved_regions)
)
invalid_service_mask = (
data["service_type"].isna()
| ~data["service_type"].isin(approved_services)
)
register_issues(
invalid_region_mask,
rule_id="REGION_DOMAIN_001",
column="region",
severity="error",
message="Region is missing or outside the approved domain.",
)
register_issues(
invalid_service_mask,
rule_id="SERVICE_DOMAIN_001",
column="service_type",
severity="error",
message="Service type is missing or outside the approved domain.",
)Unexpected categories should be reviewed rather than silently forced into an existing group.
numeric_rules = pd.DataFrame(
{
"column": ["age", "satisfaction_score"],
"minimum": [0, 1],
"maximum": [110, 5],
"whole_number": [True, True],
}
)
numeric_rules| column | minimum | maximum | whole_number | |
|---|---|---|---|---|
| 0 | age | 0 | 110 | True |
| 1 | satisfaction_score | 1 | 5 | True |
invalid_age_mask = (
data["age"].notna()
& (
(data["age"] < 0)
| (data["age"] > 110)
| (data["age"] % 1 != 0)
)
)
invalid_ages = data.loc[
invalid_age_mask,
["record_id", "age"],
].copy()
invalid_ages| record_id | age | |
|---|---|---|
| 39 | REC-0037 | 145.0 |
| 94 | REC-0084 | 999.0 |
| 122 | REC-0009 | -4.0 |
register_issues(
invalid_age_mask,
rule_id="AGE_RANGE_001",
column="age",
severity="error",
message="Age is outside 0–110 or is not a whole number.",
status="remediated",
)invalid_score_mask = (
data["satisfaction_score"].notna()
& (
(data["satisfaction_score"] < 1)
| (data["satisfaction_score"] > 5)
| (data["satisfaction_score"] % 1 != 0)
)
)
invalid_scores = data.loc[
invalid_score_mask,
["record_id", "satisfaction_score"],
].copy()
invalid_scores| record_id | satisfaction_score | |
|---|---|---|
| 16 | REC-0058 | 7.0 |
| 76 | REC-0022 | 0.0 |
| 97 | REC-0103 | 9.0 |
register_issues(
invalid_score_mask,
rule_id="SCORE_RANGE_001",
column="satisfaction_score",
severity="error",
message="Satisfaction score is outside the documented 1–5 scale.",
status="remediated",
)invalid_ranges = pd.concat(
[
invalid_ages.assign(issue="invalid_age"),
invalid_scores.assign(issue="invalid_satisfaction_score"),
],
ignore_index=True,
sort=False,
)
invalid_ranges.to_csv(
QUALITY_DIR / "invalid-ranges.csv",
index=False,
)
invalid_ranges| record_id | age | issue | satisfaction_score | |
|---|---|---|---|---|
| 0 | REC-0037 | 145.0 | invalid_age | NaN |
| 1 | REC-0084 | 999.0 | invalid_age | NaN |
| 2 | REC-0009 | -4.0 | invalid_age | NaN |
| 3 | REC-0058 | NaN | invalid_satisfaction_score | 7.0 |
| 4 | REC-0022 | NaN | invalid_satisfaction_score | 0.0 |
| 5 | REC-0103 | NaN | invalid_satisfaction_score | 9.0 |
age_issues = invalid_age_mask.sum()
score_issues = invalid_score_mask.sum()
data.loc[invalid_age_mask, "age"] = np.nan
data.loc[invalid_score_mask, "satisfaction_score"] = np.nan
log_change(
step="numeric validation",
column="age",
action="set invalid ages to missing",
affected_rows=age_issues,
rationale="Values outside the plausible range or non-whole values could not be verified.",
)
log_change(
step="numeric validation",
column="satisfaction_score",
action="set invalid scores to missing",
affected_rows=score_issues,
rationale="Scores outside the documented 1–5 scale could not be verified.",
)today = pd.Timestamp.today().normalize()
missing_date_mask = data["visit_date_raw"].isna()
future_date_mask = data["visit_date"].notna() & (data["visit_date"] > today)
invalid_date_mask = missing_date_mask | unparseable_date_mask | future_date_mask
invalid_dates = data.loc[
invalid_date_mask,
["record_id", "visit_date"],
].copy()
invalid_dates.to_csv(
QUALITY_DIR / "invalid-dates.csv",
index=False,
)
invalid_dates| record_id | visit_date | |
|---|---|---|
| 0 | REC-0017 | NaT |
| 6 | REC-0060 | NaT |
| 72 | REC-0026 | NaT |
| 74 | REC-0115 | 2031-06-01 |
| 75 | REC-0013 | NaT |
| 96 | REC-0044 | NaT |
| 104 | REC-0073 | NaT |
| 117 | REC-0101 | 2030-01-15 |
register_issues(
missing_date_mask,
rule_id="DATE_REQUIRED_001",
column="visit_date",
severity="error",
message="Required visit date is missing.",
)
register_issues(
future_date_mask,
rule_id="DATE_FUTURE_001",
column="visit_date",
severity="error",
message="Visit date is later than the validation date.",
)A missing date and an unparseable date may both appear as NaT. The original raw value should be retained in a separate audit field when that distinction matters.
identifier_pattern = re.compile(r"^REC-\d{4}$")
invalid_identifier_mask = (
data["record_id"].isna()
| ~data["record_id"].fillna("").str.match(identifier_pattern)
| (
data["record_id"].notna()
& data["record_id"].duplicated(keep=False)
)
)
invalid_identifiers = data.loc[
invalid_identifier_mask,
["record_id", "participant_name", "visit_date"],
].copy()
invalid_identifiers.to_csv(
QUALITY_DIR / "invalid-identifiers.csv",
index=False,
)
invalid_identifiers| record_id | participant_name | visit_date | |
|---|---|---|---|
| 25 | REC-0063 | Baraka Massawe | 2025-01-14 |
| 41 | REC-0094 | Linda Mushi | 2025-09-24 |
| 47 | R-0049 | Tatu Mbise | 2025-04-03 |
| 53 | REC18 | Rehema Lema | 2025-12-08 |
| 59 | <NA> | Neema Kimaro | 2025-12-13 |
| 65 | REC-ABC | Hassan Mbise | 2025-01-20 |
| 95 | REC-0063 | Esther Mrema | 2026-05-17 |
| 113 | REC-0094 | Samuel Nnko | 2025-12-23 |
invalid_id_format_mask = (
data["record_id"].isna()
| ~data["record_id"].fillna("").str.match(identifier_pattern)
)
register_issues(
invalid_id_format_mask,
rule_id="ID_FORMAT_001",
column="record_id",
severity="error",
message="Identifier is missing or does not match REC-0000.",
)email_pattern = re.compile(
r"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"
)
invalid_email_mask = (
data["contact_email"].notna()
& ~data["contact_email"].str.match(email_pattern)
)
invalid_emails = data.loc[
invalid_email_mask,
["record_id", "contact_email"],
].copy()
invalid_emails| record_id | contact_email | |
|---|---|---|
| 13 | REC-0008 | amina.example.com |
| 30 | REC-0069 | @example.com |
| 42 | REC-0047 | name@ |
| 72 | REC-0026 | missing-at-symbol.org |
register_issues(
invalid_email_mask,
rule_id="EMAIL_FORMAT_001",
column="contact_email",
severity="warning",
message="Email address does not match the documented format check.",
status="remediated",
)Invalid email addresses should usually be set to missing or flagged for correction rather than guessed.
invalid_email_count = invalid_email_mask.sum()
data.loc[invalid_email_mask, "contact_email"] = pd.NA
log_change(
step="format validation",
column="contact_email",
action="set invalid email values to missing",
affected_rows=invalid_email_count,
rationale="Values did not match the documented email pattern.",
)At this stage, apply only decisions supported by the validation rules.
Examples:
Convert validated numeric fields to nullable integer types.
data["age"] = data["age"].round().astype("Int64")
data["satisfaction_score"] = (
data["satisfaction_score"]
.round()
.astype("Int64")
)Create a review flag.
data["requires_review"] = (
data["record_id"].isna()
| data["record_id"].duplicated(keep=False)
| ~data["record_id"].fillna("").str.match(identifier_pattern)
| data["visit_date"].isna()
| (data["visit_date"] > today)
| data["participant_name"].isna()
| data["region"].isna()
| ~data["region"].isin(approved_regions)
| data["service_type"].isna()
| ~data["service_type"].isin(approved_services)
| unknown_follow_up_mask
)
data["requires_review"].value_counts(dropna=False)requires_review
False 94
True 26
Name: count, dtype: Int64
register_issues(
data["participant_name"].isna(),
rule_id="NAME_REQUIRED_001",
column="participant_name",
severity="error",
message="Required participant name is missing after trimming.",
)Create the consolidated issue register. Keeping issues in long format makes it possible for one row to fail several rules without losing detail.
issue_register_table = pd.DataFrame(issue_register)
if issue_register_table.empty:
issue_register_table = pd.DataFrame(
columns=[
"source_row_number", "record_id", "rule_id", "column",
"severity", "message", "status",
]
)
issue_register_table.to_csv(
QUALITY_DIR / "issue-register.csv",
index=False,
)
issue_register_table.head()| source_row_number | record_id | rule_id | column | severity | message | status | |
|---|---|---|---|---|---|---|---|
| 0 | 77 | REC-0013 | DATE_PARSE_001 | visit_date | error | Source date could not be parsed. | open |
| 1 | 98 | REC-0044 | DATE_PARSE_001 | visit_date | error | Source date could not be parsed. | open |
| 2 | 106 | REC-0073 | DATE_PARSE_001 | visit_date | error | Source date could not be parsed. | open |
| 3 | 5 | REC-0077 | ID_UNIQUE_001 | record_id | error | Identifier is shared by more than one row. | open |
| 4 | 21 | REC-0012 | ID_UNIQUE_001 | record_id | error | Identifier is shared by more than one row. | open |
Cleaning is not complete until the checks are rerun.
remaining_exact_duplicates = data.duplicated(subset=record_columns).sum()
remaining_duplicate_ids = (
data["record_id"].notna()
& data["record_id"].duplicated(keep=False)
).sum()
print(f"Remaining exact duplicates: {remaining_exact_duplicates}")
print(f"Rows with duplicate identifiers: {remaining_duplicate_ids}")Remaining exact duplicates: 0
Rows with duplicate identifiers: 4
remaining_invalid_regions = (
data["region"].notna()
& ~data["region"].isin(approved_regions)
).sum()
remaining_invalid_services = (
data["service_type"].notna()
& ~data["service_type"].isin(approved_services)
).sum()
print(f"Invalid region labels: {remaining_invalid_regions}")
print(f"Invalid service labels: {remaining_invalid_services}")Invalid region labels: 0
Invalid service labels: 0
remaining_invalid_ages = (
data["age"].notna()
& ~data["age"].between(0, 110)
).sum()
remaining_invalid_scores = (
data["satisfaction_score"].notna()
& ~data["satisfaction_score"].between(1, 5)
).sum()
print(f"Invalid ages: {remaining_invalid_ages}")
print(f"Invalid satisfaction scores: {remaining_invalid_scores}")Invalid ages: 0
Invalid satisfaction scores: 0
remaining_future_dates = (
data["visit_date"].notna()
& (data["visit_date"] > today)
).sum()
remaining_invalid_id_format = (
data["record_id"].notna()
& ~data["record_id"].str.match(identifier_pattern)
).sum()
print(f"Future visit dates: {remaining_future_dates}")
print(f"Invalid identifier formats: {remaining_invalid_id_format}")Future visit dates: 2
Invalid identifier formats: 3
validation_summary = pd.DataFrame(
{
"check": [
"exact duplicate rows",
"duplicate identifiers",
"invalid region labels",
"invalid service labels",
"invalid ages",
"invalid satisfaction scores",
"future visit dates",
"invalid identifier formats",
"records requiring review",
],
"remaining_issues": [
remaining_exact_duplicates,
remaining_duplicate_ids,
remaining_invalid_regions,
remaining_invalid_services,
remaining_invalid_ages,
remaining_invalid_scores,
remaining_future_dates,
remaining_invalid_id_format,
int(data["requires_review"].sum()),
],
}
)
validation_summary.to_csv(
TABLES_DIR / "data-quality-summary.csv",
index=False,
)
validation_summary| check | remaining_issues | |
|---|---|---|
| 0 | exact duplicate rows | 0 |
| 1 | duplicate identifiers | 4 |
| 2 | invalid region labels | 0 |
| 3 | invalid service labels | 0 |
| 4 | invalid ages | 0 |
| 5 | invalid satisfaction scores | 0 |
| 6 | future visit dates | 2 |
| 7 | invalid identifier formats | 3 |
| 8 | records requiring review | 26 |
The practical distinguishes between records that are ready for analysis and records that require manual resolution. This avoids presenting a file containing known critical problems as fully validated.
analysis_ready_data = data.loc[~data["requires_review"]].copy()
quarantined_data = data.loc[data["requires_review"]].copy()
release_gate = {
"status": "PASS" if len(quarantined_data) == 0 else "PASS_WITH_QUARANTINE",
"analysis_ready_rows": int(len(analysis_ready_data)),
"quarantined_rows": int(len(quarantined_data)),
"open_error_issues": int(
(
(issue_register_table["severity"] == "error")
& (issue_register_table["status"] == "open")
).sum()
),
}
release_gate{'status': 'PASS_WITH_QUARANTINE',
'analysis_ready_rows': 94,
'quarantined_rows': 26,
'open_error_issues': 34}
The cleaning log should explain what changed and why.
cleaning_log_table = pd.DataFrame(cleaning_log)
cleaning_log_table.to_csv(
TABLES_DIR / "cleaning-log.csv",
index=False,
)
cleaning_log_table| step | column | action | affected_rows | rationale | |
|---|---|---|---|---|---|
| 0 | structure | all columns | standardized column names | 0 | Use consistent lowercase snake_case names. |
| 1 | types | age | converted to numeric | 0 | Values that could not be parsed were converted... |
| 2 | types | satisfaction_score | converted to numeric | 0 | Values that could not be parsed were converted... |
| 3 | types | visit_date | parsed as datetime | 3 | Unparseable date values were converted to miss... |
| 4 | duplicates | all columns | removed exact duplicate rows | 3 | Rows were identical across all columns. |
| 5 | text | record_id | trimmed leading and trailing whitespace | 2 | Remove formatting differences that do not chan... |
| 6 | text | participant_name | trimmed leading and trailing whitespace | 3 | Remove formatting differences that do not chan... |
| 7 | text | region | trimmed leading and trailing whitespace | 1 | Remove formatting differences that do not chan... |
| 8 | text | service_type | trimmed leading and trailing whitespace | 1 | Remove formatting differences that do not chan... |
| 9 | text | follow_up_required | trimmed leading and trailing whitespace | 2 | Remove formatting differences that do not chan... |
| 10 | text | contact_email | trimmed leading and trailing whitespace | 2 | Remove formatting differences that do not chan... |
| 11 | text | record_id | converted empty strings to missing | 0 | Empty text does not contain usable information. |
| 12 | text | participant_name | converted empty strings to missing | 0 | Empty text does not contain usable information. |
| 13 | text | region | converted empty strings to missing | 0 | Empty text does not contain usable information. |
| 14 | text | service_type | converted empty strings to missing | 0 | Empty text does not contain usable information. |
| 15 | text | follow_up_required | converted empty strings to missing | 0 | Empty text does not contain usable information. |
| 16 | text | contact_email | converted empty strings to missing | 0 | Empty text does not contain usable information. |
| 17 | categories | region | standardized equivalent region labels | 7 | Merge spelling and naming variants into approv... |
| 18 | categories | service_type | standardized service categories | 6 | Use one approved label for each service type. |
| 19 | categories | follow_up_required | standardized yes/no values | 8 | Use consistent binary labels. |
| 20 | numeric validation | age | set invalid ages to missing | 3 | Values outside the plausible range or non-whol... |
| 21 | numeric validation | satisfaction_score | set invalid scores to missing | 3 | Scores outside the documented 1–5 scale could ... |
| 22 | format validation | contact_email | set invalid email values to missing | 4 | Values did not match the documented email patt... |
Create a before-and-after scorecard so the buyer can demonstrate measurable improvement rather than merely showing a cleaned file.
before_after_summary = pd.DataFrame(
{
"metric": [
"rows",
"missing source-field cells",
"exact duplicate rows",
"rows requiring review",
],
"before": [
len(raw_data),
int(raw_data.isna().sum().sum()),
int(raw_data.duplicated().sum()),
pd.NA,
],
"after": [
len(data),
int(data[record_columns].isna().sum().sum()),
int(data.duplicated(subset=record_columns).sum()),
int(data["requires_review"].sum()),
],
}
)
before_after_summary.to_csv(
TABLES_DIR / "before-after-quality-summary.csv",
index=False,
)
before_after_summary| metric | before | after | |
|---|---|---|---|
| 0 | rows | 123 | 120 |
| 1 | missing source-field cells | 41 | 53 |
| 2 | exact duplicate rows | 3 | 0 |
| 3 | rows requiring review | <NA> | 26 |
A useful cleaning log records:
The dataset was inspected for structural errors, missing values, duplicate records, inconsistent categories, invalid numeric ranges, malformed dates, and identifier problems. Exact duplicate rows were removed, equivalent categorical labels were standardized, and invalid unverifiable values were set to missing rather than guessed. Records with unresolved identifier, date, or required-field issues were retained with a review flag. The cleaned dataset is suitable for exploratory analysis, but conclusions should acknowledge the remaining missingness and unresolved records.
Export dates in a portable ISO format.
clean_data_export = analysis_ready_data.copy()
quarantined_data_export = quarantined_data.copy()
for export_frame in [clean_data_export, quarantined_data_export]:
export_frame["visit_date"] = export_frame["visit_date"].dt.strftime("%Y-%m-%d")Save the cleaned dataset.
clean_output_path = TABLES_DIR / "cleaned-service-data.csv"
clean_data_export.to_csv(
clean_output_path,
index=False,
)
quarantine_output_path = TABLES_DIR / "quarantined-service-data.csv"
quarantined_data_export.to_csv(quarantine_output_path, index=False)
print(f"Saved cleaned dataset to: {clean_output_path}")
print(f"Saved quarantined records to: {quarantine_output_path}")Saved cleaned dataset to: results/tables/cleaned-service-data.csv
Saved quarantined records to: results/tables/quarantined-service-data.csv
Create a run manifest that records the input fingerprint, execution time, package versions, output counts, and release decision.
run_manifest = {
"practical_id": "CDI-DATASCIENCE-PRACTICAL-001",
"run_timestamp_utc": datetime.now(timezone.utc).isoformat(),
"input_path": DATA_PATH.as_posix(),
"input_sha256": raw_file_sha256,
"raw_rows": int(len(raw_data)),
"analysis_ready_rows": int(len(analysis_ready_data)),
"quarantined_rows": int(len(quarantined_data)),
"release_status": release_gate["status"],
"python_version": sys.version.split()[0],
"pandas_version": pd.__version__,
"numpy_version": np.__version__,
}
manifest_output_path = TABLES_DIR / "run-manifest.json"
manifest_output_path.write_text(
json.dumps(run_manifest, indent=2),
encoding="utf-8",
)
run_manifest{'practical_id': 'CDI-DATASCIENCE-PRACTICAL-001',
'run_timestamp_utc': '2026-08-09T23:27:41.131545+00:00',
'input_path': 'data/example-service-data.csv',
'input_sha256': '5872bf7b8351c25fbace4f674fec8101ada8839c1cf8f67b3551d13477151335',
'raw_rows': 123,
'analysis_ready_rows': 94,
'quarantined_rows': 26,
'release_status': 'PASS_WITH_QUARANTINE',
'python_version': '3.12.6',
'pandas_version': '2.3.3',
'numpy_version': '2.1.3'}
Create a final issue summary figure.
plot_data = validation_summary.sort_values(
"remaining_issues",
ascending=True,
)
fig, ax = plt.subplots(figsize=(9, 6))
ax.barh(
plot_data["check"],
plot_data["remaining_issues"],
)
ax.set_xlabel("Number of remaining issues")
ax.set_ylabel("")
ax.set_title("Data Quality Issues After Cleaning")
fig.tight_layout()
fig.savefig(
FIGURES_DIR / "data-quality-issues-summary.png",
dpi=300,
bbox_inches="tight",
)
plt.show()
A cleaned dataset is not automatically a correct dataset.
Interpretation should distinguish among:
Important limitations may include:
Do not describe the cleaned dataset as error-free. Describe it as checked, standardized, and suitable for a defined purpose under stated limitations.
This practical can support portfolio evidence when you retain:
A strong portfolio entry should emphasize decisions rather than only tools.
Cleaned and validated a realistic service-delivery dataset using Python and pandas. Built reproducible checks for missing values, duplicates, inconsistent categories, numeric ranges, dates, identifiers, and email formats. Produced a documented cleaning log, validation summary, quality-control figures, and analysis-ready dataset while preserving unresolved records for review.
Use the following question to guide your final project summary:
How did systematic data cleaning and validation change the reliability, consistency, and usability of the example service dataset?
Your answer should refer to:
Describe the dataset in your final report.
Include:
Use code to generate a compact dataset summary.
dataset_summary = pd.DataFrame(
{
"metric": [
"raw rows",
"cleaned rows",
"columns",
"raw missing cells",
"cleaned missing cells",
"records requiring review",
],
"value": [
len(raw_data),
len(data),
data.shape[1],
int(raw_data.isna().sum().sum()),
int(data.isna().sum().sum()),
int(data["requires_review"].sum()),
],
}
)
dataset_summary| metric | value | |
|---|---|---|
| 0 | raw rows | 123 |
| 1 | cleaned rows | 120 |
| 2 | columns | 12 |
| 3 | raw missing cells | 41 |
| 4 | cleaned missing cells | 56 |
| 5 | records requiring review | 26 |
Summarize the workflow in six stages:
Your workflow description should explain why each stage was necessary.
The main result is not simply the cleaned CSV file.
It is the combination of:
Create a concise summary of the largest changes.
main_results = (
cleaning_log_table
.groupby("action", as_index=False)["affected_rows"]
.sum()
.sort_values("affected_rows", ascending=False)
)
main_results| action | affected_rows | |
|---|---|---|
| 11 | trimmed leading and trailing whitespace | 11 |
| 10 | standardized yes/no values | 8 |
| 8 | standardized equivalent region labels | 7 |
| 9 | standardized service categories | 6 |
| 5 | set invalid email values to missing | 4 |
| 2 | parsed as datetime | 3 |
| 3 | removed exact duplicate rows | 3 |
| 4 | set invalid ages to missing | 3 |
| 6 | set invalid scores to missing | 3 |
| 0 | converted empty strings to missing | 0 |
| 1 | converted to numeric | 0 |
| 7 | standardized column names | 0 |
Write a short interpretation explaining which actions affected the most records and whether those changes altered meaning or only standardized representation.
Before declaring the practical complete, verify:
Use assertions for checks that must pass.
assert data.duplicated(subset=record_columns).sum() == 0
assert data.loc[data["age"].notna(), "age"].between(0, 110).all()
assert (
data.loc[
data["satisfaction_score"].notna(),
"satisfaction_score",
]
.between(1, 5)
.all()
)
assert clean_output_path.exists()
assert quarantine_output_path.exists()
assert manifest_output_path.exists()
assert len(analysis_ready_data) + len(quarantined_data) == len(data)
assert not analysis_ready_data["requires_review"].any()
print("Core validation checks passed.")Core validation checks passed.
Document limitations honestly.
Consider:
A good limitation statement protects the user from treating a cleaned dataset as more certain than it is.
The practical uses:
Record the package versions used in your environment.
package_versions = pd.DataFrame(
{
"tool": [
"Python",
"pandas",
"NumPy",
"Matplotlib",
],
"version": [
sys.version.split()[0],
pd.__version__,
np.__version__,
matplotlib.__version__,
],
}
)
package_versions| tool | version | |
|---|---|---|
| 0 | Python | 3.12.6 |
| 1 | pandas | 2.3.3 |
| 2 | NumPy | 2.1.3 |
| 3 | Matplotlib | 3.10.0 |
Reflect on the decisions made during the practical.
Use these prompts:
A suitable next practical is Exploratory Data Analysis Practical, where the cleaned dataset can be used to study distributions, relationships, outliers, and patterns.
Confirm that you have completed each item.
You have completed the Data Cleaning and Validation Practical.
You now have a reproducible workflow for:
The next step is to apply the same workflow to another dataset and compare how the validation rules change across domains.