Course index · Previous · Next

Profile a Dataset and Build a Data Quality Audit

Module 2: Reading and Understanding Tables · Lecture 8 of 32

A quick head() is useful but cannot establish that a dataset is trustworthy. Build an audit that accounts for every raw row and separates duplicate delivery from invalid data and legitimate unknown fields.

What you will learn

• Measure missingness, duplicate keys, and raw-to-clean row accounting.
• Distinguish completeness, validity, uniqueness, and consistency.
• Keep rejected data visible rather than silently dropping it.

Understand the problem

Inspect the entire population

The first five rows can look plausible while later rows contain broken timestamps, invalid quantities, or repeated exports. Start with shape, columns, dtypes, null counts, unique values for low-cardinality fields, and candidate-key duplication. Profiling produces questions; it does not automatically supply correct cleaning rules.

Different defects require different responses

Completeness asks whether required information is present. Validity asks whether values obey the domain. Uniqueness asks whether a key identifies one entity. Consistency asks whether related fields agree, such as a refund not exceeding the discounted gross amount. Timeliness and coverage require source knowledge: no pandas method can prove that a missing export day had no business activity.

Use a row-accounting equation

The shared cleaner removes exact duplicate rows, rejects invalid records, and retains accepted rows. Therefore raw rows must equal exact duplicates plus rejected rows plus accepted rows. A conflicting duplicate key is handled differently: the pipeline raises instead of choosing an arbitrary winner. This preserves uncertainty that could affect revenue.

Unknown is not always invalid

A missing customer_id can represent an anonymous checkout, so the course retains it. A missing channel becomes the explicit unknown category. Missing unit price cannot support a trustworthy revenue calculation, so that record is rejected. These are declared teaching policies. Real source contracts may require different treatment, and a quality report should make those differences visible.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 8 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

raw = load_raw()
orders, audit = clean_orders(raw)
profile = pd.DataFrame({'missing': raw.isna().sum(),
                        'distinct_nonmissing': raw.nunique(dropna=True)})
print('Audit:', audit)
print(profile.to_string())
print('Repeated raw order IDs:', int(raw['order_id'].duplicated().sum()))
assert audit == {'input_rows': 244, 'exact_duplicates': 2,
                 'rejected_rows': 3, 'accepted_rows': 239}
assert sum(audit[k] for k in ['exact_duplicates', 'rejected_rows', 'accepted_rows']) == len(raw)
assert orders['order_id'].is_unique

Verified output

Audit: {'input_rows': 244, 'exact_duplicates': 2, 'rejected_rows': 3, 'accepted_rows': 239}
                  missing  distinct_nonmissing
order_id                0                  242
customer_id             1                   48
ordered_at              0                  241
channel                 1                    4
units                   0                    5
unit_price_paise        0                    5
discount_paise          0                   13
refunded_paise          0                   23
status                  0                    3
Repeated raw order IDs: 2

Walk through the result

The 244 raw rows reconcile to two exact duplicates, three rejected records, and 239 accepted orders. One rejected record was an originally valid order whose price was deliberately corrupted; two additional invalid records were appended. Therefore it is incorrect to expect 240 clean orders just because 240 base orders were generated. Distinct counts exclude missing values here, while the missing column reports them explicitly. Retaining those conventions prevents misleading comparisons between profile tables.

Common mistakes and diagnosis

A high fill rate does not prove accuracy: a fully populated column can contain the wrong prices. Do not remove every row with any missing field when some fields are optional. Do not “repair” a conflicting key by keeping the last row unless a reliable timestamp or source rule establishes which record supersedes which. A good audit is executable and reconciled, not just a colorful summary.

Practice assignment

Write audit.json and a column-profile CSV. Calculate the rejection rate using the raw-row denominator, then calculate the rate after exact duplicate removal. Explain why the two rates differ and which one you would use for monitoring source validity.

import json
(OUT / 'audit.json').write_text(json.dumps(audit, indent=2), encoding='utf-8')
profile.to_csv(OUT / 'column-profile.csv', index_label='column')
raw_rate = audit['rejected_rows'] / audit['input_rows']
unique_delivery_rate = audit['rejected_rows'] / (audit['input_rows'] - audit['exact_duplicates'])
assert unique_delivery_rate > raw_rate
print('Validity rejection rate:', round(unique_delivery_rate, 6))

Check your understanding

Why should a conflicting duplicate key stop the report instead of being silently deduplicated?

Reference and next step

• pandas DataFrame information

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next