At the end of the previous module we promised to put statistics to work on real, imperfect data, and here we deliver on that promise. MercaFresh's datasets — like those of any real company — arrive with duplicates, data-entry errors, impossible values and categories spelled three different ways. No algorithm from module 4 can compensate for corrupt input data: if we train the churn model on duplicated customers or negative ages, it will learn patterns that don't exist. In this lesson you'll learn how to audit a dataset, detect its problems and fix them systematically with pandas. According to most practitioners, this is the phase where an ML project spends the most time: doing it well makes the difference between a useful model and a misleading one.

1. Why real-world data is dirty
2. The MercaFresh customer dataset
3. Initial audit: know the terrain
4. Duplicates: exact and logical
5. Data-entry errors and impossible values
6. Text and category inconsistencies
7. Incorrect data types
8. Outliers: detecting them and deciding what to do

Tutorial datasets come clean; company datasets don't. The causes are always similar:

• Manual entry: a MercaFresh call-center agent types "BCN" one day and "Barcelona" the next.
• System integration: the website, the mobile app and the call center store data in different formats (dates 2025-03-14 vs 14/03/2025).
• Software bugs: a bug recorded order amounts as €0 for two weeks.
• Historical migrations: when the CRM was replaced in 2023, some customers were imported twice.
• Sentinel values: someone used -1 or 999 to mean "unknown", and now they look like real data.

The practical consequence is summed up by the classic phrase garbage in, garbage out: a model trained on garbage produces garbage predictions, but with the appearance of mathematical precision. That's why cleaning is not a formality — it's part of the analysis.

This is the order we'll follow: first understand, then fix, from the most obvious to the most subtle.

We'll work with a fictional but realistic extract of the customer table, exported from the CRM for the churn project. Let's build it in code so you can reproduce everything that follows:

Even at a glance you can sense trouble: customer 102 appears twice, there's an age of -5 and another of 127, the city "Barcelona" is written four different ways, the spend is stored as text and there's a signup date in 2027 (in the future!). In a 10-row dataset you spot these instantly; in one with 200,000 rows you need a method.

Before touching anything, you need an X-ray of the dataset. Three pandas functions cover 80% of the initial audit:

info() answers three questions: how many rows are there? what type is each column? how many non-null values does each one have? Here we discover the first silent problem: total_spend is of type object (text), not numeric. An object where you expected numbers is a red flag.

describe() gives us the mean, standard deviation, minimum, maximum and quartiles — exactly the statistics from module 2 (lesson 02-01). Its great value in cleaning is checking min and max: a minimum age of -5 and a maximum of 127 need no further analysis to know something is wrong.

nunique() counts distinct values per column. If we know MercaFresh operates in 4 cities but city has 7 unique values, there are spelling inconsistencies. unique() shows them to us: ['Barcelona' 'BCN' ' barcelona ' 'Valencia' 'VALENCIA' 'Madrid' 'madrid' 'Sevilla'].

An exact duplicate is a row identical to another in every column. At MercaFresh, customer 102 (Luis Gil) was imported twice during the CRM migration:

Key points about drop_duplicates:

• keep="first" (the default) keeps the first copy; keep="last", the last one; keep=False removes all of them.
• It returns a new DataFrame: you have to reassign it (df = df.drop_duplicates()).

Why does this matter for the churn model? A duplicated customer weighs twice as much in training: the model will "see" their patterns twice and skew its conclusions toward that profile.

More dangerous are logical duplicates: rows that represent the same entity but are not byte-for-byte identical. For example, the same customer signed up twice with their spending split across both records, or the same order logged by both the website and the app with different ids. To hunt them down, you look for duplication only in the columns that define identity:

With logical duplicates, deleting blindly is risky: sometimes the right move is to merge (add up the orders from both records) instead of removing. It's a business decision, not just a technical one: always ask what each row represents.

An impossible value is one that violates the rules of the real world or of the business. The technique is to define explicit validity rules and check how many rows break them:

And what do we do with them? There are three options, in order of preference:

1. Correct, if we know the error: an age of -5 with a flipped sign could be 5 (impossible, a minor) or a typo; if the original CRM has the good value, recover it from there.
2. Mark as missing (np.nan), if we can't know the real value: it's honest to admit we don't know, and in the next lesson we'll see how to handle those nulls.
3. Delete the row, only if the whole record is unrecoverable and there are few such cases.

Note the pattern df.loc[condition, column] = value: it's the safe way in pandas to modify a subset (it avoids the infamous SettingWithCopyWarning).

The €0 amounts deserve separate reflection: a total_spend of 0 with num_orders of 0 can be legitimate (a registered customer who never bought — highly relevant for churn!), while a spend of 0 with 12 orders is an error. The same figure can be valid data or an error depending on context: validate combinations of columns, not columns in isolation.

The city column is the perfect example: "Barcelona", "BCN" and " barcelona " are the same city to a human, but three different categories to pandas and to any model. If we don't fix it, the upcoming one-hot encoding (lesson 03-04) will create three columns where there should be one.

Normalization follows a three-step recipe using the str.* string methods:

An important detail: str.strip() and str.lower() transform the text character by character, while replace() substitutes whole values using a dictionary. For substitutions inside the text (for example, removing the periods from "S.L.") there is str.replace("...", "..."), which is a different method — confusing the two is a classic mistake.

To discover aliases you didn't know about, value_counts() is your friend: it sorts categories by frequency, and the rare variants show up at the bottom of the list with counts of 1 or 2.

A number stored as text is an invisible problem until it blows up: you can't compute means, sorting is alphabetical ("15400" < "890" because "1" < "8") and describe() ignores it.

The format parameter deserves attention: "14/03/2025" could be March 14 or (in the American convention) an error. Specifying format="%d/%m/%Y" removes the ambiguity. Converting dates to real datetime also unlocks operations we'll use in lesson 03-03: extracting the month, computing tenure, subtracting dates.

You already know outliers from module 2: in 02-01 you saw them poke out of the boxplots of order amounts, and in 02-02 you learned that a high z-score signals an oddity. Now we apply them as cleaning tools on total_spend.

A reminder from 02-02: if the data were normal, only 0.3% of values would fall beyond |z| > 3. Customer 107, with €15,400 in spending and 210 orders, is off the charts. The z-score has a weakness: the mean and standard deviation it uses are contaminated by the outliers themselves, so in small or heavily skewed samples it can fail.

The interquartile range method (02-01) is more robust because the quartiles barely flinch at extreme values:

It's exactly the rule that draws the "whiskers" of the boxplot you already know.

Here is the nuance that separates mechanical cleaning from good cleaning: an outlier is not necessarily an error. Customer 107 could be a restaurant that buys from MercaFresh daily — an extremely valuable customer, not corrupt data. The options:

The golden rule: never remove an outlier without being able to explain why. Document every decision — your future self (and the churn model) will thank you.

• Cleaning without auditing first. Running drop_duplicates "just in case" before understanding the data can delete legitimate records. First info(), describe(), nunique(); then act.
• Forgetting to reassign. df.drop_duplicates() without df = changes nothing: most pandas operations return a copy.
• Confusing replace() with str.replace(). The first substitutes whole values in the Series; the second, substrings inside each text.
• Automatically removing outliers with |z| > 3. It's a detector, not a judge: every outlier deserves a diagnosis (error or exceptional customer?).
• Validating columns in isolation. A spend of 0 may or may not be valid depending on num_orders: powerful validity rules cross columns.
• Not documenting the changes. Record the number of rows removed and the rules applied; in a real project you'll be held accountable for every discarded record.
• Tip: write the cleaning as a reproducible function (def clean_customers(df): ...) instead of loose notebook cells. When next month's export arrives, you'll apply it in seconds.

With the original df DataFrame from the lesson (before cleaning), write the code that: (a) counts the exact duplicates, (b) removes them keeping the first occurrence, and (c) checks whether any repeated customer_id remains.

The MercaFresh team hands you this column from a new export: ["Madrid", "MAD", " madrid", "Màdrid", "madriz"]. Normalize it so that every value ends up as "Madrid". Hint: you'll need strip, lower and an alias dictionary that includes the typos.

The orders table has an order_amount column with these values: [22.5, 19.0, 24.3, 21.7, 480.0, 23.1, 20.9, 18.4]. Detect outliers with the IQR method and reason (in a comment) whether the detected value should be removed, kept or investigated, knowing that MercaFresh also serves small restaurants.

Exercise 1

If (c) returned more than 0, you would inspect those rows with keep=False and decide whether to merge or remove.

Exercise 2

Typos (madriz) can't be fixed with general rules: you have to discover them with value_counts() and add them to the alias dictionary one by one.

Exercise 3

In this lesson you've learned to turn a chaotic dataset into a trustworthy one: audit with info(), describe() and nunique(); remove exact duplicates and diagnose logical ones with drop_duplicates; define validity rules to hunt down impossible values; unify categories with str.strip/lower and replace; fix types with astype, to_numeric and to_datetime; and detect outliers with z-score and IQR, knowing that detecting them is not sentencing them. The central idea: cleaning is analysis, not paperwork — every correction is a decision you must be able to explain.

You'll have noticed that several times the honest solution was to turn a suspicious value into NaN: we've been deliberately accumulating gaps. In the next lesson we tackle exactly that: MercaFresh's missing data, why it's missing (which is not always random) and the strategies for dropping or imputing it without deceiving the model.