Track 14 · Practical Data Analysis · Lesson 5 of 11
Making and documenting cleaning decisions
Turn data-quality findings into explicit cleaning rules, log how many records each rule changes, and measure how cleaning changes the results.
Two analysts receive the same customer export. One reports an average annual spend of 12 128 SAR, the other 5 481 SAR. Both are “right” for the data they used — the difference is six undocumented cleaning decisions. A cleaning log turns those hidden choices into something a manager can read, question and repeat.
Finding problems is half the job (see Duplicates and invalid values). The other half is deciding what to do about each one — and writing the decision down so that someone else gets exactly the same cleaned table.
A cleaning rule has three parts: a condition that any reader can check (“age below 16 or above 110”), an action (“set age to missing”), and a reason (“impossible for a customer; probably a code or a typo”). A cleaning log lists the rules in the order they are applied, with the number of records each one changed.
Included with a Formula Quest membership
It has 8 graded exercises with feedback, hints and worked solutions. After it you will be able to:
- Choose between correcting a value, setting it to missing, dropping a record and keeping it, and justify the choice
- Write cleaning rules as condition, action and reason, applied in a fixed order with duplicates first
- Log how many records each rule changes and check the counts after re-importing the cleaned file
- Measure and report how cleaning changes the row count, means, medians and missingness