Start with a small contract

In this fictional order extract, each order_id must be unique and each amount is a non-negative number of US dollars written as a whole-number string. Missing amounts must be reviewed. The exercise uses integer cents for the final total; production input rules may need Decimal parsing, refund handling and a wider set of valid formats.

import pandas as pd
raw = pd.DataFrame({
    'order_id': [1, 2, 3, 4],
    'amount_usd': ['100', '', 'oops', '0']
})
work = raw.copy()
text = work['amount_usd'].str.strip()
missing = text.eq('') | text.isna()
valid = text.str.fullmatch(r'\d+').fillna(False)
invalid = ~missing & ~valid
assert work['order_id'].notna().all()
assert work['order_id'].is_unique
clean = work.loc[valid].copy()
clean['amount_cents'] = text.loc[valid].astype('int64') * 100
print(int(missing.sum()), int(invalid.sum()))
print(clean['amount_cents'].sum())

Expected output: 1 missing amount, 1 invalid amount and 10000 cents across the two valid rows. The known zero remains valid. The missing and invalid rows are excluded from this calculation explicitly; the $100 result is a partial total, not the complete value of all four orders.

Keep an exception table

Save the rejected rows with a reason and the source identifier. An empty value might reflect a delayed payment; “oops” might be a parsing error or manual entry problem. Those cases require different follow-up. Silently filling both with zero removes the evidence needed to correct the source.

Do not deduplicate without a rule

If an order appears twice, decide whether it is an accidental duplicate, a new version or a separate event. Keeping the last row is meaningful only after defining a reliable ordering field and tie-breaker. File order alone may change between exports. Count duplicated IDs before and after the operation and document what was discarded.

Check joins after cleaning

A cleaned order table can still become wrong when merged with a customer table that has duplicate keys. Use merge validation for the intended relationship and inspect unmatched keys. Reconcile the order count and amount before and after enriching the table. A successful merge is not evidence that the relationship was correct.

Turn the exercise into a deliverable

1. Preserve the original extract separately.
2. Record input rows, accepted rows and rejected rows.
3. State the parsing rules and monetary units.
4. Export accepted data and exceptions separately.
5. Label downstream totals as partial if unresolved rows remain.

The example was executed with pandas 3.0.5. Assertions are useful for this practice exercise; production validation should raise explicit errors where checks must run even when Python assertions are disabled.

Documentation and further reading

• pandas missing-data guide