DataCareerHub / Data Processing & Preparation
Clean data without losing evidence
Fictional learning case · Attempt the worksheet before opening solutions.
1. Missing values and duplicate records
Missing values and duplicate records
Missingness has causes: uncollected, not applicable, failed parsing or absent export. Decide whether the field is required for the intended use before choosing a response. Replacing every blank with zero mixes absence with a real measurement. Duplicate detection also needs a rule: an exact repeated record, repeated business key and legitimate repeated parent key are different situations. Preserve the raw row number so every decision can be traced back.
Work through the case
L004 has blank quantity, L007 has an ambiguous date and L010 has an unknown SKU. L001 appears twice identically. L005 occurs twice with conflicting quantities. The reference workflow quarantines both L005 versions because there is no authoritative update timestamp or version flag. For the exact duplicate L001, it retains the first occurrence and records the later occurrence as excluded. Reconciliation counts excluded duplicates among quarantined rows; that label includes records set aside for several reasons.
A method you can reproduce
Profile required fields first. Group raw records by stripped line_id and compare their complete values. Identify conflicting keys before processing any row so the first conflicting version cannot slip through. Track a set of exact raw fingerprints to identify repeated records. Assign explicit reason codes; one row can have several reasons, so the number of reasons need not equal the number of rejected records. Keep raw_record as evidence and never sum rejected values into accepted totals.
Check your reasoning
Keeping the first version of a conflicting key is arbitrary without evidence of version order. Conversely, deleting every repeated order_id removes legitimate lines. The disposition depends on the business entity and available authority.
Further reading: pandas merge validation, pandas melt, Python CSV parsing. These official references support the methods; the case and exercises are authored for DataCareerHub.
Exercise
List the missing quantity, exact duplicate and conflicting-key rows. Define a disposition for each. Predict the number of accepted and quarantined records before checking expected_results.json.
Write your answer in learner_worksheet.txt and retain the evidence file. Attempt this before opening the solution.
Worked solution
The workflow accepts 8 of 15 records and quarantines 7. The two L005 records are both quarantined. The later L001 copy is excluded as exact_duplicate. L004, L007, L010 and L013 fail required validity or lookup rules. Count records, not summed reason codes: accepted + quarantined = 15.
Independent extension: change one relevant input in a separate copy. Predict the result first, rerun the checks and explain any difference. Keep the supplied source unchanged.
2. Inconsistent categories and safe corrections
Inconsistent categories and safe corrections
Normalisation makes equivalent representations comparable, but it must preserve meaning. Trimming surrounding spaces is often defensible; mapping an abbreviation needs a declared dictionary. A category mapping is a controlled many-to-one transformation, not a fuzzy guess. Store both original and corrected values in a change log. Distinguish an unmapped category from a recognised Other category, because silently combining them hides a quality problem.
Work through the case
Cedar defines web and ONLINE as web; store and shop as store; partner stays partner. Case and surrounding whitespace do not carry business meaning for these channel labels under the teaching contract. SKU is different: P01 is a declared exact identifier. The reference trims surrounding whitespace but does not invent P99 or convert it to P03. It parses prices using decimal arithmetic before converting them to integer cents.
A method you can reproduce
Write the mapping once and apply it to a stripped, lower-case channel. Reject a value that has no mapping. For each accepted changed field record source_row, line_id, field, before, after and rule. Keep the source columns untouched. A complete raw record is retained for rejected rows; accepted-row change logs do not pretend to describe corrections to records that were never accepted. Review every distinct input label against the mapping before trusting grouped totals.
Check your reasoning
Uncontrolled fuzzy matching can join different entities that merely look alike. Never use approximate SKU similarity as proof of identity. Also avoid binary floating-point rounding for contractual cent values: a value with excessive decimal precision should fail the rule rather than be rounded silently.
Further reading: pandas merge validation, pandas melt, Python CSV parsing. These official references support the methods; the case and exercises are authored for DataCareerHub.
Exercise
Create the channel map and correction log for accepted records. Explain how ONLINE, surrounding spaces and P99 should be treated. Test a new channel value wholesale in a separate source copy.
Write your answer in learner_worksheet.txt and retain the evidence file. Attempt this before opening the solution.
Worked solution
ONLINE maps to web using channel_map_v1; surrounding spaces are recorded by trim_v1. P99 remains unmatched and is quarantined, not guessed. Adding wholesale must create an unmapped_channel rejection until the contract and lookup are deliberately revised. The correction log identifies exact before/after evidence for accepted changes.
Independent extension: change one relevant input in a separate copy. Predict the result first, rerun the checks and explain any difference. Keep the supplied source unchanged.
3. Document corrections and reconcile the result
Document corrections and reconcile the result
A clean-looking table is an output, not a quality argument. A quality argument connects input counts, rule outcomes, correction evidence and financial or operational reconciliation at the right grain. Run independent checks: record conservation, unique accepted keys, valid domains and totals from accepted rows. A corrected dataset can still be incomplete, so report exclusions and affected entities as well as the headline total.
Work through the case
The reference accepts 8 lines belonging to 6 distinct order IDs, worth EUR 220.00 in accepted line value. These are partial accepted-line values, not full business sales. Some orders lose lines to quarantine. Category subtotals use accepted line values and must sum to the accepted total. Source-row reconciliation remains 15 = 8 + 7.
A method you can reproduce
Use quality_log_template.csv to record rule, affected row, evidence, decision and reviewer question. Compare your prepared keys and total against expected_results.json after working independently. Recalculate the accepted total as the sum of quantity × unit_price_cents and again as the sum of category subtotals. Include a limitation that the extract is small, fictional and incomplete for whole-order revenue. A log with zero unresolved entries is not a goal; honest unresolved issues help the next person act.
Check your reasoning
Do not reconcile by forcing totals to match an expected answer. A changed source legitimately changes results. Test the invariants, investigate changed totals and update the evidence record; keep expected baseline values specific to the unchanged fixture.
Further reading: pandas merge validation, pandas melt, Python CSV parsing. These official references support the methods; the case and exercises are authored for DataCareerHub.
Exercise
Prepare a short acceptance note containing the row equation, accepted value, key uniqueness, quarantined reasons and limitations. Alter one accepted quantity in a separate copy and explain what should change.
Write your answer in learner_worksheet.txt and retain the evidence file. Attempt this before opening the solution.
Worked solution
The baseline accepted total is 22000 cents (EUR 220.00). Category totals are {"Equipment": 18000, "Stationery": 2000, "Supplies": 2000}. A one-unit increase in an accepted P01 line adds 1000 cents; row conservation and unique keys remain unchanged. A baseline assertion should fail after that deliberate change, while structural checks should still pass.
Independent extension: change one relevant input in a separate copy. Predict the result first, rerun the checks and explain any difference. Keep the supplied source unchanged.
Practice files
Prepared fictional curriculum. Source data and code are supplied for local practice. Payments and public registration remain disabled.