learning-it by example

#09 Python for data 6 min

Clean Messy Data with Pandas 3: End to End, 3 Mistakes

Clean a messy CSV with pandas 3 from start to finish in about twenty lines, and check your work with counts. Includes three real mistakes.

1. Why does the cleaning script tidy the text columns before it removes duplicates?
2. The messy orders file has 8 raw rows. What did the cleaning script report?
3. What does pd.to_datetime with errors set to coerce do to a date it cannot read, and what should you do about it?
4. In pandas 3, what happens when you assign through a chain such as orders[orders['amount'] > 100]['amount'] = 0, and what is the fix?
5. What does the assert on the amount column do in the cleaning script?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Add a ninth row

    Copy the code folder first. Add a ninth line to messy.csv for a new customer with an amount of 30.00 pounds, a date of 2026-03-01 and the country England. Predict the raw rows count, the duplicates removed and the clean rows count, then run python clean.py. This needs pandas 3.1.0, as in the video.

    A hint

    Which numbers change when a good, new row is added, and which stay the same?

  2. Remove one country spelling

    In clean.py take england out of the mapping that turns country names into UK. Predict what the country column shows for Eli Moss, who is listed as England. Then run it and check.

    A hint

    The code lowercases the text, applies the mapping, and then makes it uppercase.

  3. Fix the chained assignment

    In gotchas.py add a line after the chained assignment that uses loc to set the amount to 0 where it is above 100, then print list(orders['amount']). Predict the list before you run python gotchas.py.

    A hint

    Use orders.loc with the condition and the column together.

  4. Break the assert on purpose

    Add one more row to messy.csv with any customer, any country, an amount of -5 and any valid date. Predict what the script does when it reaches the assert line, and whether the line that prints the clean rows is reached. Then run python clean.py.

    A hint

    Read what the assert checks and what the video says it is for.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

One small made-up orders file, cleaned with rules chosen for it; the chained assignment trap is shown for pandas 3.1.0 on Python 3.12 only, and day-first or mixed date formats are not covered.

Up next in Python for data

More in Python for data

All Python for data videos

Related videos