The 12% Null Column Two Days Before Deadline
You are cleaning a 480,000-row bike-share trip file two days before a deadline. The schema is ride_id, start_time, end_time, start_station, end_station, member_casual, and ride_length_sec. Problems found: 1,900 duplicate ride_ids; start_time is ISO 8601 in roughly 70% of rows and MM/DD/YYYY in the rest; station names are free text containing 12 spellings of the same downtown station; ride_length_sec is null in 12% of rows. A junior analyst proposes deleting every row with a null ride_length so the file is "clean," which would leave about 422,000 rows. Your manager wants the file finished on time and has explicitly said she does not want a long write-up.
1. Evaluate the junior analyst's proposal. What is the real risk, and what would you do instead?
Read the worked answer
The proposal is only safe if the missingness is completely at random - which is a claim, not an assumption you get for free.
Test it before acting:
- Cross-tab the null rate by
member_casual, bystart_station, and by month. If nulls concentrate in one segment (say casual riders, or one station with an ageing kiosk), deleting them systematically shrinks that segment and every downstream comparison - average trip length for casual riders, station rankings, seasonality - is biased. - Check whether the null is even a true gap. Trip duration is derivable as
end_time − start_time. Most of the 12% probably have both timestamps and simply never got a duration written.
What to do instead:
- Recompute duration from the timestamps. Only what is still missing after that is genuinely missing.
- Report the residual missing share by segment before deciding anything.
- If you must exclude, exclude on the validated gap, document the count, and keep the rows in the raw file.
The judgement: "clean" is not a state, it is a decision with consequences. The deadline pressure is real, but deleting 57,600 rows is irreversible in a deliverable you will be asked to defend.
2. Give the ordered cleaning sequence. For each step, say what you drop, what you fix, and what you flag.
Read the worked answer
| # | Step | Action | Drop / Fix / Flag |
|---|---|---|---|
| 0 | Preserve | Copy the raw file, read-only, never edit in place | - |
| 1 | Deduplicate | Remove duplicates on ride_id with a stated rule (e.g. keep earliest by ingest time); record the count |
Drop 1,900, document the rule |
| 2 | Parse timestamps | Use explicit format strings for the two formats - do not let the parser infer, that is how day/month get silently swapped | Fix; flag any row that fails both formats |
| 3 | Normalise time zone | Convert to one zone (UTC), state it | Fix |
| 4 | Recompute duration | end_time − start_time |
Fix |
| 5 | Validate durations | Investigate negatives, zero-second, and >24h trips - usually docking errors or lost bikes | Flag, do not silently delete; state the exclusion rule if you apply one |
| 6 | Standardise station names | Build a mapping table from the distinct values; keep the raw column alongside | Fix |
| 7 | Reconcile | Final row count must equal 480,000 − 1,900 = 478,100, or the difference must be explained | - |
Order matters: dedupe before counting anything, and recompute duration before judging completeness - otherwise your "12% missing" figure is wrong and your row totals will not reconcile.
3. Your manager does not want a long write-up. What is the minimum documentation you insist on, and why is it not optional?
Read the worked answer
Minimum viable documentation - four artifacts, none longer than a page:
- A changelog table: step, rule applied, rows affected, rows remaining, reason. This is what answers "why is the file 478,100 rows and not 480,000?" six weeks from now.
- A data dictionary: column, type, unit, allowed values, and the definition of each derived field (especially duration and time zone).
- The script itself as executable documentation. If the cleaning was done by hand in a spreadsheet, that is the real finding - the work is not reproducible.
- A three-bullet known-issues note: residual missingness and its direction of bias, the duration exclusion rule, and the station-name mapping's known gaps.
Why it is non-negotiable: every cleaning choice above is a judgement call (dedupe rule, time zone, outlier threshold). Undocumented judgement looks identical to an error to the next analyst, the auditor, or your manager in a bad meeting. One page of changelog converts "I cleaned it" into "here is exactly what I did and what it costs you."