Data cleaning
In short: Data cleaning removes duplicates, puts prices, dates and names into one format, and checks in code that the data are right after the cleaning.
Problem
The new price list
A new supplier sent their price list as a CSV. The manager asks two things: how much we will pay per product on average, and what the coffee costs us. The file supplier.csv is next to the code. Here it is:
All four columns are str. The prices are “0,95 €” (the supplier writes prices the continental way, with a decimal comma), “1.45”, blank and “on request”. The dates are in two formats. The white cheese is there twice. The names have capitals, lower case and spaces. The coffee costs 1190.
prices['price'].mean() does not even start: the prices are text. Data like this is called dirty, and it is the norm, not the exception. The supplier's software writes what it writes, and people fill things in by hand.
The idea
Cleaning is code, not handwork
The temptation is to open the file and fix the cells by hand. But in a month the supplier will send a new list with the same mistakes. So cleaning is code: it runs again on every new file.
The path is always the same:
- Look at what is wrong:
dtypes,describe,value_counts, sorting. - Fix it with one step for each kind of dirt: duplicates, text, numbers, dates, outliers.
- Check in code that it worked: the number of rows before and after, NaN only where it should be.
Each step of this lesson is for one of the kinds of dirt in the list.
The idea
Duplicates: duplicated and drop_duplicates
duplicated() gives a mask: True for every row that repeats a row above it. .sum() counts them. With subset= it compares only the given columns, and keep=False marks all copies, not just the second one:
There is one exactly equal row: the white cheese, P07. By code, though, there are two repeats. Fresh milk, P05, is there twice, once as “FRESH MILK” and once as “ Fresh milk”, with a space in front. To pandas these are different texts, so duplicated() does not catch it.
drop_duplicates() removes the repeats and keeps the first row. But if you run it now, the milk stays twice.
The idea
Names: str.strip and str.lower
Text is cleaned with .str from the lesson on types. str.strip() removes the spaces at the ends, and str.lower() makes all letters small. That way “FRESH MILK” and “ Fresh milk” become the same text:
Of 16 rows, 14 remain: the cheese and the milk are removed. The order of the steps matters: first clean the text, then look for duplicates.
More precisely, if you are curious
str.lower() is for comparing. For a label or a report, str.capitalize() makes the first letter a capital: “fresh milk” becomes “Fresh milk”. If names also differ in the middle (two spaces between words), str.replace(' ', ' ') or str.split().str.join(' ') evens them out.
Try it
No repeats
Clean the names in the list (no spaces at the ends, lower case), remove the repeated rows, and store in rows how many rows remain.
Solution
import pandas as pd
prices = pd.read_csv('supplier.csv')
prices['name'] = prices['name'].str.strip().str.lower()
prices = prices.drop_duplicates()
rows = len(prices)
print(rows)prices['name'].str.strip().str.lower() evens out the names, and drop_duplicates() then removes the two repeated rows: of 16, rows = 14 remain. If you remove the duplicates first, 15 remain: at that point the two milk rows are still different, and drop_duplicates removes only the cheese.
The idea
Prices from text: str.replace and pd.to_numeric
“0,95 €” is text for three reasons: the € sign, the comma instead of a point, and the space. str.replace removes each of them. Then pd.to_numeric turns the text into a number. With errors='coerce', anything that is still not a number becomes NaN instead of stopping the code:
The price is float64, and there are two NaN: the apples (a blank field) and the mineral water (“on request”). That is right: the supplier gave no price for them. Without errors='coerce', to_numeric stops with ValueError: Unable to parse string "on request".
errors='coerce' is handy and dangerous: it does not say what it threw away. So you count NaN before and after. If there are more after the conversion than there were blank fields, something became NaN by mistake.
Try it
Prices as numbers
Make the column price a number. NaN should remain only where the supplier gave no price.
Solution
import pandas as pd
prices = pd.read_csv('supplier.csv')
text = prices['price'].str.replace('€', '').str.replace(',', '.').str.strip()
prices['price'] = pd.to_numeric(text, errors='coerce')
print(prices['price'].isna().sum())str.replace('€', ''), then str.replace(',', '.') and str.strip() turn “0,95 €” into “0.95”, and pd.to_numeric(text, errors='coerce') turns that into 0.95. 2 NaN remain: the apples and the water. With only to_numeric(..., errors='coerce'), 11 of 16 become NaN, because every price with a comma quietly disappears, and the code gives no error at all.
See it
Dates in two formats
Half of the dates are “01.11.2026” (day, month, year), and the other half “2026-11-01”. If you let pandas guess:
pandas guesses the format from the first date and reads it as %m.%d.%Y: month first, the American way. On the ISO date it stops with an error. If all the dates had dots, there would be no error: 01.11 would quietly become 11 January.
So you state the format yourself, with format=, once for each format. With errors='coerce' the dates in the other format become NaT, and fillna merges the two reads:
13 rows are valid from 1 November and 2 from 15 November. One date cannot be read with either format: 31.11.2026 for the bananas. There is no such day, November has 30 days.
See it
Outliers: describe and sorting
The prices are numbers now. describe() from the first look tells you whether they look plausible, and sorting shows which are the biggest:
The mean is €102.70, and the median €1.62. A gap like that means one value is far from the rest. This is called an outlier. Sorting finds it at once: the coffee beans at €1,190. We sell the coffee at €14.90, so the supplier's price is €11.90: their software dropped the decimal point.
The fix is a rule for all prices over €100. You write it in the code, not in one cell, so that it catches the same mistake in the next list too:
The coffee costs €11.90, the mean dropped to €4.53, and the median stayed €1.62. The median does not feel one huge number, and the mean feels all of it.
More precisely, if you are curious
The rule “over €100 means a lost decimal point” is right for this list because we know the products: nothing in it costs more than €100. In another list (televisions, furniture) the same rule would break correct prices. When you do not know why a value is strange, ask where it came from instead of fixing it.
The idea
Checks as code: assert
The cleaning is done when you can prove it. assert condition, 'message' does nothing if the condition is true, and stops the code with an AssertionError and the message if it is not. Here is the whole cleaning with the checks at the end:
Of 16 rows, 14 remain. The checks say out loud what you expect:
- every code appears once;
- a price is missing only for the two products without a price;
- every price is between €0.10 and €100;
- only one date is unread.
When the next list comes with a new mistake, the check will stop the code with a clear message instead of the report quietly coming out wrong.
Exercise
The mean supplier price
Answer the manager. Clean the whole list in prices: the names and the duplicates, the prices as numbers, the dates in both formats and the coffee without its decimal point. The products without a price stay, with NaN. Store in avg_price the mean price, rounded to 2 decimals.
Solution
import pandas as pd
prices = pd.read_csv('supplier.csv')
prices['name'] = prices['name'].str.strip().str.lower()
prices = prices.drop_duplicates()
text = prices['price'].str.replace('€', '').str.replace(',', '.').str.strip()
prices['price'] = pd.to_numeric(text, errors='coerce')
dots = pd.to_datetime(prices['valid_from'], format='%d.%m.%Y', errors='coerce')
iso = pd.to_datetime(prices['valid_from'], format='%Y-%m-%d', errors='coerce')
prices['valid_from'] = dots.fillna(iso)
cents = prices['price'] > 100
prices.loc[cents, 'price'] = prices.loc[cents, 'price'] / 100
assert prices['code'].is_unique
assert prices['price'].isna().sum() == 2
avg_price = round(prices['price'].mean(), 2)
print(avg_price)The four steps in this order give 14 rows, 2 prices NaN and 1 date NaT. round(prices['price'].mean(), 2) is €4.53, and the coffee costs €11.90. Without the coffee fix the mean is €102.70. With fillna(0) the water and the apples “cost” €0 and pull the mean down, and with dropna() you also lose the bananas, which have a correct price.
Test