Topic 7 — Cleaning and preparing data

Goal: Learn why messy data is the norm and how to make it trustworthy.

Lesson 7.1 — The chart that was wrong before she started

Nadia's third week at Perch, the online furniture store. Marcus, Head of Marketing, wants a single number: how many orders did the new desk line bring in last month? Nadia writes the SQL, runs it, gets a clean total, and feels good. Then, out of an old retail habit, she scrolls the raw rows before she sends anything. There they are: the same order ID showing up twice. A customer's country listed as "USA" in some rows and "United States" in others. A handful of blank dates. One order for 4,000 desks.

She almost shipped a number built on all of that.

Here is the part nobody warns new analysts about. You picture the job as discovering insights, and instead a large share of it — the figure people quote is around 80% — is finding, cleaning, and preparing data before any analysis can happen. That 80% isn't a precise measurement; it comes from the widely-cited CrowdFlower (later Figure Eight) Data Science Report, where analysts said they spent roughly 60% of their time cleaning and another ~19% collecting data, which people round up to "about 80% on data prep." A separate year of that same survey found two-thirds of analysts named cleaning their single most time-consuming task. So treat "80%" as the well-known shorthand, not a law of physics — the exact number gets argued over, but every working analyst recognizes the shape of it. The preparation is the work.

Cleaning the data isn't the chore you do before the real job. For most of the week, it is the real job.

Data comes from imperfect people typing into imperfect systems, so it arrives messy by default. Priya, Nadia's mentor, puts it plainly: nobody hands you a clean dataset. The skill is cleaning-is-the-job — treating preparation as the craft, not the warm-up. We'll come back to that one desk order. It matters more than it looks.

Lesson 7.2 — Why a beautiful chart can still lie

Suppose Nadia had skipped the scroll. The duplicate order inflates the count. The split country names break her "orders by region" grouping into pieces. The 4,000-desk order yanks the average sky-high. Her chart would have rendered perfectly — clean colors, neat labels, completely wrong.

That's the rule every analyst learns the hard way: garbage-in-garbage-out. The most sophisticated analysis run on dirty data produces a confident, polished, wrong answer. The polish is the danger, because a wrong number that looks professional gets believed and acted on.

Dana, Perch's VP of Operations, won't see Nadia's working. Dana sees one sentence and a recommendation, and she'll move budget based on it. So the trust has to be built underneath the chart, where Dana never looks. Clean inputs are the only thing that makes the output mean anything.

This is why the careful analyst is worth more than the clever one. A clean, well-understood dataset beats a brilliant technique applied to a dirty one, every time.

Lesson 7.3 — The five problems you'll meet again and again

Once you start looking, the same handful of issues show up in almost every dataset. Learn to name them on sight — these are the common-data-problems.

• Missing values — blank cells. An order with no date, a customer with no country. The blank itself isn't the danger; what you do with it is (Lesson 7.4).
• Duplicates — the same record counted twice. The quietest problem on this list, because nothing looks broken. The total just runs high, and you'd never know unless you checked.
• Inconsistent formatting — the same thing written different ways.
• Outliers — values far outside the normal range. The 4,000-desk order. A customer age of 200. Could be a real bulk buyer, could be a typo for 4.
• Wrong types — numbers stored as text. A price column that's secretly text won't add up; it'll concatenate or error instead of summing.

Inconsistent formatting deserves a closer look, because it's the one that fools people. A computer matches values character for character. To it, USA, U.S.A., United States, and us are four different countries, so a count splits into four small piles instead of one real total. Dates do the same: 01/02/2024 and 2024-02-01 may be the same day or two different days depending on the format, and the machine won't warn you. Even an invisible trailing space turns Sofa and Sofa into two distinct products. Nadia hit this in retail too, back when guessing was the only option she had.

A computer never reads "USA" and "United States" as the same country. It only sees characters, and those don't match.

Lesson 7.4 — Missing values and duplicates: the two judgment calls

A blank cell is not a problem with one right answer. It's a decision, and you own it. With handling-missing-values you have three honest moves:

• Exclude the rows that are missing the field you need — and report how many you dropped.
• Fill (impute) the blank with a reasonable stand-in, such as the column's median, when dropping the row would throw away too much else.
• Flag it — keep the row but mark the value as missing, so the gap is visible downstream instead of hidden.

Whatever you pick, say which you did. The move that gets analysts in trouble is the silent one: filling blanks with 0 because it's easy. Tom, Perch's data engineer, warns Nadia about exactly this. Thirty orders have no delivered-date yet because they haven't shipped. Turn those blanks into 0 and you've invented thirty orders that delivered on day zero, dragging your average delivery time toward nonsense. A blank means unknown; zero means the value is zero. They are not the same, and pretending they are quietly corrupts every average and total built on the column.

Duplicates are the other judgment call. Nadia's duplicate order came from a checkout that double-fired — the same order_id written twice. The cure is duplicates-and-dedup: find rows that repeat on the field that's supposed to be unique, confirm they're truly the same event, then keep one. Care matters here, because not every repeat is a duplicate. Two orders from the same customer on the same day for the same sofa might be one glitch — or a real second purchase. You check the unique key before you delete, so you remove glitches without erasing real sales.

Lesson 7.5 — Look first, sanity-check, and write down what you touched

The habit that catches the most errors costs the least: look at the raw data first. Before any query, Nadia scrolls the table, eyeballs the minimum and maximum of each number column, and reads a sample of rows. The 4,000-desk order and the age of 200 jump out the second you actually look — and most people never look.

Then she sanity-checks the totals. Does the row count roughly match what she'd expect for a month? Is the revenue in the right ballpark, or off by a suspicious factor of ten? This is where document-and-sanity-check earns its keep, and it carries a rule worth tattooing on the wall:

If a result surprises you, suspect the data before you believe the surprise.

A jaw-dropping spike in repeat purchases is usually a duplicated table or a broken join, not a miracle. Nadia learned to feel a flash of doubt instead of a flash of excitement, and to chase the doubt down before she tells Marcus anything.

The last habit makes all the others trustworthy: document every change, and never silently delete data. "Removed 12 duplicate orders, excluded 30 rows with missing delivery dates" is one line, and it turns a private guess into work that Priya can review, Tom can verify against the source, and Nadia herself can repeat next month. Care and skepticism — written down — are the most valuable things an analyst brings to a table.

Worked example — Cleaning Perch's March orders before Dana sees it

Marcus needs one number for Dana: total March revenue for the desk line, and whether it grew. Dana wants a sentence and a recommendation, not a method. So the method has to be airtight underneath.

Nadia pulls the March orders table — 2,140 rows — and resists running the SUM. She scrolls first. Four things surface.

1. A look at the raw data shows order_id 88231 appearing twice. She checks the unique key, confirms the checkout double-fired, and removes one copy. Dedup: 1 duplicate removed.
2. The country column has USA, U.S.A., and United States. She standardizes all three to United States so her region grouping stops splitting into pieces. Formatting fixed.
3. Eighteen rows have a blank revenue because those orders were cancelled before payment. She excludes them rather than filling 0, since a cancelled order is a non-sale, not a $0 sale. Missing handled: 18 rows excluded.
4. The big one: an order for 4,000 desks. An outlier she does not auto-delete. She emails Tom, who confirms it's a real bulk order from an office-fit-out client. It stays. Outlier investigated and kept.

Now she runs the total — and sanity-checks it against February before trusting it. The shape is believable, so she writes the one line for Marcus to pass up: "March desk revenue grew 9% over February; growth is steady, recommend holding the current ad spend." And underneath, the note that makes it defensible: "Removed 1 duplicate order, standardized 3 country spellings, excluded 18 cancelled orders, kept 1 verified bulk order (4,000 units)." If Dana ever asks how she knows, the answer already exists.