Module 3: Cleaning Real-World Data · Lecture 9 of 32
Missing values can mean anonymous, not collected, not applicable, delayed, or corrupted. Develop a field-specific policy and quantify how unknowns affect the population before filling or dropping them.
What you will learn
- Measure missingness by field and population.
- Choose between retaining, labeling, rejecting, and imputing.
- Avoid missing-group disappearance in summaries.
Understand the problem
Ask why a value is absent
An absent customer ID is different from a zero-unit order or a missing price. In the fictional store, anonymous purchases can be included in total revenue, but they cannot be assigned confidently to a customer cohort. Keeping the order for one analysis and excluding it from another is consistent when each population is documented.
Do not let group defaults hide records
Grouping by a column containing missing keys can omit those observations unless dropna=False is specified. A channel report whose groups do not sum to the total is a warning sign. You can keep the missing-key group or create a clearly named unknown category. Do not replace a missing customer key with a shared fake ID and later count that fake ID as a real customer.
Imputation changes the question
Filling an absent measurement with a median can be appropriate for a particular model or exploratory display, but it creates an estimated value. Record which rows were imputed and why. Never compute imputation parameters using future data when evaluating a predictive pipeline. For this descriptive revenue report, an absent price is not imputed: the record is quarantined because the financial amount is unsupported.
Expose sensitivity
Report both the count and revenue associated with unknown customer IDs. A small count can still represent a large share of money. If conclusions depend on how unknown rows are allocated, show a range or separate them instead of presenting a single confident segment ranking. Missingness itself can carry information about the collection process, so compare it by period and source where those fields are reliable.
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 9 --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
orders = load_orders()
missing = orders['customer_id'].isna()
summary = orders.assign(identity=np.where(missing, 'anonymous', 'identified')).groupby('identity').agg(
orders=('order_id', 'size'), net_paise=('net_paise', 'sum'))
by_customer = orders.groupby('customer_id', dropna=False)['net_paise'].sum()
print(summary.to_string())
print('Unknown-customer orders:', int(missing.sum()))
assert int(summary['orders'].sum()) == len(orders)
assert int(by_customer.sum()) == int(orders['net_paise'].sum())
assert int(missing.sum()) == 1
Verified output
orders net_paise
identity
anonymous 1 29900
identified 238 24082520
Unknown-customer orders: 1
Walk through the result
The identity column is a reporting label, not a replacement customer key. Anonymous revenue remains in the grand total. The customer grouping explicitly retains the missing-key bucket, so reconciliation holds. A separate cohort analysis will exclude that bucket because a single missing value cannot establish a stable identity across visits. The same row can legitimately belong in the revenue population and remain outside an identified-customer population.
Common mistakes and diagnosis
fillna(0) is not a universal cleaning step. It can create impossible dates, fictitious customer IDs, or understated prices. A blank string may not count as missing until normalized. sum normally skips missing values; use min_count=1 when an all-missing group should remain unknown instead of becoming zero. Never describe an imputed value as an observed measurement.
Practice assignment
Calculate the share of total net revenue associated with anonymous orders. Then demonstrate the difference between the default sum and sum(min_count=1) for an entirely missing numeric Series. State which result communicates absence of evidence more accurately.
Worked solution and explanation
Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.
unknown_share = orders.loc[missing, 'net_paise'].sum() / orders['net_paise'].sum()
all_missing = pd.Series([pd.NA, pd.NA], dtype='Int64')
assert all_missing.sum() == 0
assert pd.isna(all_missing.sum(min_count=1))
print('Anonymous revenue share:', round(float(unknown_share), 6))
Check your understanding
Why can an order remain in a revenue report but be excluded from a customer retention report?
Answer guide
The revenue report needs a valid monetary amount and an accepted order. Retention needs a stable customer identifier across periods. An anonymous order satisfies the first contract but not the second. Excluding it from retention is defensible only when the report names the identified-customer population and discloses excluded activity.
Reference and next step
Download the complete course kit: datasets, all examples, solutions, and capstone.
Return to the full Data Analysis with Python and pandas curriculum.
