Cleaning and Preparing Data

Textbook data arrives tidy. Real data does not. It comes with blank cells, typos, dates in three formats, the same customer entered twice, and values that cannot possibly be right. Before any average or chart means anything, someone has to sort that out. Cleaning is not a chore that precedes the real work; for most projects it is the largest part of the real work.

What makes cleaning delicate is that it is full of choices, and the choices change the answer. Drop the missing rows or fill them in? Keep the outlier or remove it? Each decision quietly bends the result. That is not a reason to avoid cleaning; it is a reason to do it carefully, make defensible choices, and write down exactly what you did. Undocumented cleaning is how honest people reach wrong conclusions.

Missing values

Data goes missing for reasons: a survey question skipped, a sensor offline, a field never filled. You can drop the affected rows, fill the gap with an estimate, or analyse around it, but each path shifts the result. The danger is when missingness is not random. If the people who skipped an income question tend to be the highest earners, dropping them biases the answer. Ask why the data is missing before deciding what to do about it.

Errors and inconsistent formats

Real datasets are riddled with errors and mismatches: an age of 200, a price with a stray decimal, "UK" and "United Kingdom" and "U.K." treated as three countries, dates written day-first and month-first in the same column. These must be caught and standardised, or your groups will split and your totals will drift. Range checks and a scan of the unique values in each column catch most of it.

Duplicates and outliers

Duplicate records inflate counts and double-weight whoever appears twice, so they need finding and removing. Outliers are trickier. A value far from the rest might be an error to fix or a genuine, important extreme to keep. A logged temperature of minus 400 is impossible and must go; a genuinely huge sale is real and must stay. Deleting outliers just because they are inconvenient is how you engineer the answer you wanted.

Document everything

Every change you make, dropped rows, filled gaps, standardised labels, removed outliers, should be recorded so another person could reproduce your dataset from the raw source. Documentation turns a private judgement call into a public, checkable one. It is also your defence: if a result is challenged, you can show exactly what you did and why.

An analyst cleans a customer spreadsheet before reporting average order value. She scans each column: three orders show negative totals (refunds miscoded), so she flags and excludes them with a note. Country appears as "US" and "USA"; she standardises to one label. Two rows are exact duplicates from a double-submitted form, so she removes one. One order reads 99,999, a clear placeholder, which she treats as missing rather than real. She records every step in a short log. The average she reports is now trustworthy, and anyone can retrace her path from the raw file.

Contrast a rushed analyst who wants a higher average. He notices several small orders "dragging down" the number and quietly deletes them as outliers, leaves the duplicates in because they help, and keeps no record. His average looks great and is a fiction. Nobody, including his future self, can tell what he removed or why. The same tools used with intent to flatter, and without documentation, turn cleaning into quiet manipulation.

In 2010, economists Carmen Reinhart and Kenneth Rogoff published "Growth in a Time of Debt," reporting that countries with public debt above 90 percent of GDP saw sharply lower, even negative, average growth. The claim was cited widely in support of austerity policies. In 2013, Thomas Herndon, then a graduate student at the University of Massachusetts Amherst, obtained their spreadsheet with his advisors Michael Ash and Robert Pollin. They found several issues, including an Excel formula that accidentally left five countries out of a key average, alongside contested choices about which data to exclude and how to weight it. Correcting these, Herndon and colleagues reported the average growth for high-debt countries was around positive 2.2 percent, not the negative figure originally published. The economic debate continued, but the episode is a documented, verifiable case of a spreadsheet slip and cleaning choices shaping a headline conclusion.

• "Cleaning is a quick step before the real analysis." For most projects it is the largest part of the work.
• "Just delete rows with missing values." If the missingness is not random, dropping those rows biases the result.
• "Outliers are errors and should be removed." Some are errors; some are the most important real values. Judge each one.
• "How I cleaned the data does not need writing down." Undocumented cleaning cannot be checked, reproduced, or defended.

Work through a messy sample dataset: flag the impossible values, standardise the mismatched labels, decide what to do with missing entries and outliers, and keep a running log. Then see how your choices move the final average, and compare with a documented reference cleaning.

Think Like a Maester: Every cleaning choice is a fork in the result, so make it in the open and write down which way you turned.

1. Why is it dangerous to simply drop all rows with missing values?
2. Give two examples of inconsistent formatting that could split a single group into several.
3. When should an outlier be removed, and when should it be kept?
4. In the Reinhart-Rogoff case, what kind of error did Herndon and colleagues find, and how did the corrected figure differ?
5. Why does documenting your cleaning steps protect both your reader and yourself?

Real data is messy, and turning it into something trustworthy is most of the analytical work. Missing values, errors, inconsistent formats, duplicates, and outliers each demand a decision, and each decision nudges the result. Make those choices carefully, keep genuine extremes while removing impossible ones, and understand why data is missing before you act. Above all, document every step, because as the Reinhart-Rogoff spreadsheet showed, a small slip in preparation can steer a very large conclusion.

• Types of Data
• Exploratory Data Analysis