Data cleaning lab

Why this matters

Real data is rarely clean. A file exported from an old spreadsheet may use a different set of letter codes than your computer expects. A form may let people type dates in any style they like. A signup list may have blank cells, placeholder words such as N/A, and the same person listed twice. Before any program can count, sort or report on such data, someone has to clean it.

Data cleaning is the job of turning a messy file into a file where every row follows the same rules. Engineers spend a lot of time on this work, and it is easy to get wrong. A wrong date or a silently dropped row can make a report look correct while it is not.

This lab takes one messy file and cleans it in four steps: the letter encoding, the dates, the missing fields, and the duplicate rows. You will write a short Python script for each step, run it, and read the output. The lab uses only the standard library, which is the set of tools that comes with Python, so every step is visible in the code. Other lessons in this module cover the file formats themselves. The CSV lesson explains rows and columns, and the Unicode lesson explains letter codes in more depth. This lesson uses both, but does not repeat them.

The commands and code here were run with Python 3.14.4 on Linux. The same code runs on other systems with Python 3.

The messy file

A CSV file is a text file where each line is one row and the values in a row are separated by commas. The file in this lab has five columns: an id, a name, a city, a signup date and an amount of money.

Create a folder for the lab and move into it:

$ mkdir -p ~/forge-lab/cleaning && cd ~/forge-lab/cleaning

Save this file as make_messy.py. It writes the sample data to a file called messy.csv. You do not need to understand every line yet. The important part is the last two lines, which save the text in a specific letter code. That detail is the first problem you will fix.

rows = [
    "id,name,city,signup,amount",
    "1,Zoë Martín,Málaga,2026-03-04,120.50",
    "2,Ana Ruiz,Sevilla,04/03/2026,",
    "3,José Pérez,,15 Mar 2026,88",
    "4,Renée Dubois,Lyon,N/A,45.00",
    "5,Zoë Martín,Málaga,2026-03-04,120.50",
    "6,Liam O'Brien,Dublin,2026-13-01,-",
    "7,Ünal Kaya,Istanbul,22/03/2026,310",
    "8,Sam Lee,Leeds,  ,12.0",
]
text = "\n".join(rows) + "\n"
with open("messy.csv", "wb") as f:
    f.write(text.encode("cp1252"))

Run it and look at the file:

$ python3 make_messy.py
$ cat messy.csv
id,name,city,signup,amount
1,Zo� Mart�n,M�laga,2026-03-04,120.50
2,Ana Ruiz,Sevilla,04/03/2026,
3,Jos� P�rez,,15 Mar 2026,88
4,Ren�e Dubois,Lyon,N/A,45.00
5,Zo� Mart�n,M�laga,2026-03-04,120.50
6,Liam O'Brien,Dublin,2026-13-01,-
7,�nal Kaya,Istanbul,22/03/2026,310
8,Sam Lee,Leeds,  ,12.0

The letters with accents show as question-mark boxes or odd symbols, and the exact symbols depend on your terminal. The rest of the text looks fine. This is the first problem. The file is readable, but the terminal is showing its bytes with the wrong letter code.

The file has eight data rows and one header row. Here is what is wrong with them, so you know what the script must fix:

• Rows 1, 3, 4, 5 and 7 have accented letters that show up as boxes.
• Row 2 has an amount that is empty.
• Row 3 has an empty city.
• Row 4 has N/A where the signup date should be.
• Row 5 is a copy of row 1, except for the id.
• Row 6 has a month of 13, which does not exist, and a dash where the amount should be.
• Row 7 uses a day-first date, while row 1 uses a year-first date.
• Row 8 has spaces where the signup date should be.

Step 1: Fix the encoding

What a letter code is

A computer stores text as numbers. An encoding is the table that says which number stands for which letter. Two common encodings are:

• UTF-8, which is the default on most systems today. It can store every letter in every language.
• Windows-1252 (written cp1252 in Python), an older encoding used by many Windows programs. It covers the letters of Western European languages.

A byte is one unit of storage, a number from 0 to 255. Look at the bytes the sample file really holds:

raw = open("messy.csv", "rb").read()
print(raw[:40])

The output is:

b'id,name,city,signup,amount\n1,Zo\xeb Mart\xedn,'

The letter ë is stored as the single byte \xeb, which is the number 235. In UTF-8 the same letter takes two bytes, so this file is not valid UTF-8. Reading it as UTF-8 breaks. Here is a script that tries:

with open("messy.csv", encoding="utf-8") as f:
    print(f.read())

It fails with this error:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xeb in position 31: invalid continuation byte

The message says the decoder found byte 0xeb (the number 235, written in hexadecimal) where it expected the start of a UTF-8 character. The number 31 is the position of that byte in the file, counting from zero.

Reading with the right encoding

When the encoding is known, pass it to open. Save this file as step1.py:

with open("messy.csv", encoding="cp1252", newline="") as f:
    for i, line in enumerate(f):
        if i == 2:
            break
        print(line, end="")

Run it:

$ python3 step1.py
id,name,city,signup,amount
1,Zoë Martín,Málaga,2026-03-04,120.50

The accented letters are now correct. The code enumerate(f) numbers the lines, and break stops after the first three lines. The end="" part stops print from adding a second blank line, because each line already ends with a new-line character.

The newline="" part tells Python to leave line breaks alone. The csv module handles line breaks itself, so Python should not change them. Without this option, a value that contains a line break inside quotes can be split in the wrong place. On Windows, writing a CSV file without it adds an empty line between every row. Use newline="" every time you open a CSV file.

How to find the encoding of an unknown file

The file does not say which encoding it uses. You have to guess and then check the result. Here is the method:

1. Try UTF-8 first. If it works, the file is probably UTF-8.
2. If it fails with a UnicodeDecodeError, try cp1252.
3. Read the decoded text and look at the accented letters. If the names are correct, you have the right encoding.

A file that was saved in a different old encoding may decode without an error but show the wrong letters, such as é where é should be. Trust your eyes on the decoded text, not only on the absence of an error. If cp1252 gives wrong letters, try the encoding the source program is known to use.

The rest of this lab opens the file with encoding="cp1252". Once the text is decoded correctly, the script writes it back out as UTF-8, the encoding the rest of your tools expect.

Step 2: Fix the dates

One format at a time

The signup column has three date styles in it:

• 2026-03-04 is year, month, day. This is the form defined by the ISO 8601 standard, which is the safest form to store.
• 04/03/2026 is day, month, year, with slashes.
• 15 Mar 2026 is day, short month name, year.

Python's datetime.strptime reads a date from text when you give it a format string. The format string uses codes for each part: %Y is a four-digit year, %m is a month number, %d is a day number, %b is a short month name such as Mar. A format string that does not match the text raises a ValueError.

Here are the three formats tried in turn on one date each:

from datetime import datetime
 
print(datetime.strptime("2026-03-04", "%Y-%m-%d").date().isoformat())

2026-03-04

Trying the wrong format fails loudly:

datetime.strptime("04/03/2026", "%Y-%m-%d")

ValueError: time data '04/03/2026' does not match format '%Y-%m-%d'

A function is a named block of code that takes an input and returns a result. Put the three formats in a list and try each one in turn. The first format that works wins. If none work, the function returns None, which means "no value" in Python. Save this as dates.py:

from datetime import datetime
 
DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%d %b %Y"]
 
 
def parse_date(text):
    for fmt in DATE_FORMATS:
        try:
            return datetime.strptime(text, fmt).date().isoformat()
        except ValueError:
            pass
    return None
 
 
for sample in ["2026-03-04", "04/03/2026", "15 Mar 2026", "2026-13-01", "hello"]:
    print(repr(sample), "->", repr(parse_date(sample)))

Run it:

$ python3 dates.py
'2026-03-04' -> '2026-03-04'
'04/03/2026' -> '2026-03-04'
'15 Mar 2026' -> '2026-03-15'
'2026-13-01' -> None
'hello' -> None

Each date came out in the same form, YYYY-MM-DD. The try and except lines catch the ValueError so the loop can move on to the next format. The pass line means "do nothing here". The isoformat() method turns the date object back into text in the ISO form.

The month 13 date is rejected, and so is the word hello. The function does not guess. A month of 13 does not exist, so the function returns None, and the calling code decides what to do with the bad value.

The day and month problem

The text 04/03/2026 could mean 4 March or 3 April. The script above reads it as day first, because the format list says %d/%m/%Y. That is a choice, and it might be wrong. If the file came from a source that writes month first, the same text means 3 April.

Here is the same text read with the month first:

from datetime import datetime
 
print(datetime.strptime("04/03/2026", "%m/%d/%Y").date().isoformat())

2026-04-03

Both readings are valid dates. Python cannot tell which one is correct. The only safe way is to know where the file came from. If the source is a form used in India or the United Kingdom, day first is the usual form. If the source is a US system, month first is the usual form. Write down the choice in a comment, and check a few rows by hand against the original source before you trust the result.

A simple rule for the lab: day-first for slash dates, because the other rows in the file do not show a month-first pattern. A better rule in real work: ask the person who owns the export.

Dates that look valid but are not

Some dates have the right shape but do not exist. February 30 is one example:

from datetime import datetime
 
datetime.strptime("2026-02-30", "%Y-%m-%d")

ValueError: day 30 must be in range 1..28 for month 2 in year 2026

The same function returns None for this value, because no format in the list accepts it. This is the behaviour you want. A date that does not exist should be reported, not fixed silently.

Step 3: Handle missing fields

Marking missing values as one kind

The file uses several different ways to say "no value":

• an empty cell, such as the amount in row 2
• the word N/A, in the signup date of row 4
• a dash, in the amount of row 6
• spaces only, in the signup date of row 8

A program does not know these are the same thing unless you tell it. The fix is to pick one form for "missing" and convert every variant to it. This lab uses an empty string, because the CSV file stores a missing value as an empty cell.

Here is a function that trims spaces and converts the placeholder words:

MISSING_WORDS = {"", "n/a", "-", "null"}
 
 
def clean_text(value):
    value = value.strip()
    if value.lower() in MISSING_WORDS:
        return ""
    return value
 
 
for sample in ["  N/A ", " Lyon ", "-", "  ", "Dublin"]:
    print(repr(sample), "->", repr(clean_text(sample)))

Save the code as clean_text_test.py and run it:

$ python3 clean_text_test.py
'  N/A ' -> ''
' Lyon ' -> 'Lyon'
'-' -> ''
'  ' -> ''
'Dublin' -> 'Dublin'

The strip() method removes spaces from both ends of the text. The lower() method converts it to lower case, so N/A and n/a match the same entry in the set. A set, written with curly braces in Python, holds unique items and checks membership quickly. The spaces in row 8 are an easy mistake to miss. Without strip(), the value " " is not equal to an empty string, so the script would treat it as a real value.

Report, do not invent

A script has three common choices for a missing value.

1. Keep the row and leave the cell empty. The data stays honest. Later steps and readers can see that the value is unknown.
2. Fill the cell with a guess. For example, put the average amount in the empty cells. This makes the table look complete, but the numbers are now partly made up. Later totals will look more precise than they are.
3. Drop the row. This loses every other good value in the row, and it can change counts in a report without anyone noticing.

For this lab, choice 1 is used, and the script prints a line for every empty field so a person can decide. Filling a value with a guess is a business decision, and it should be made by the person who owns the data. The script should never make it on its own.

Step 4: The whole script

Now put the four steps together in one file. The script reads the messy file, cleans each row, writes a clean file, and prints a report. Save it as clean.py:

import csv
import sys
from datetime import datetime
 
MISSING_WORDS = {"", "n/a", "-", "null"}
DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%d %b %Y"]
FIELDS = ["id", "name", "city", "signup", "amount"]
 
 
def clean_text(value):
    value = value.strip()
    if value.lower() in MISSING_WORDS:
        return ""
    return value
 
 
def parse_date(text):
    for fmt in DATE_FORMATS:
        try:
            return datetime.strptime(text, fmt).date().isoformat()
        except ValueError:
            pass
    return None
 
 
def main(src, dst):
    seen = set()
    kept = []
    problems = []
    with open(src, encoding="cp1252", newline="") as f:
        for line_no, row in enumerate(csv.DictReader(f), start=2):
            row = {key: clean_text(value) for key, value in row.items()}
            if row["signup"]:
                date = parse_date(row["signup"])
                if date is None:
                    problems.append(f"line {line_no}: bad date {row['signup']!r}")
                    row["signup"] = ""
                else:
                    row["signup"] = date
            for field in FIELDS:
                if row[field] == "":
                    problems.append(f"line {line_no}: empty {field}")
            key = (row["name"], row["city"], row["signup"], row["amount"])
            if key in seen:
                problems.append(f"line {line_no}: duplicate of an earlier row, skipped")
                continue
            seen.add(key)
            kept.append(row)
    with open(dst, "w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=FIELDS)
        writer.writeheader()
        writer.writerows(kept)
    for problem in problems:
        print(problem)
    print(f"kept {len(kept)} rows, {len(problems)} problems")
 
 
if __name__ == "__main__":
    main(sys.argv[1], sys.argv[2])

A few lines need explaining.

csv.DictReader reads each row as a dictionary, a set of key and value pairs, where the keys are the column names from the header. So row["signup"] gives the signup value for that row.

enumerate(..., start=2) numbers the rows so that the first data row is line 2 of the file. Line 1 is the header. This makes the report match what you see in an editor.

The seen set remembers the keys of rows already kept. The key is the tuple of name, city, signup and amount. A tuple is a fixed group of values, written with round brackets. The id is left out of the key on purpose, because the duplicate in this file differs only in its id.

csv.DictWriter writes the dictionary rows back to a file. The writeheader() call writes the column names first.

Run the script with the two file names:

$ python3 clean.py messy.csv clean.csv
line 3: empty amount
line 4: empty city
line 5: empty signup
line 6: duplicate of an earlier row, skipped
line 7: bad date '2026-13-01'
line 7: empty signup
line 7: empty amount
line 9: empty signup
kept 7 rows, 8 problems

Read the report carefully. Line 6 is the copy of row 1, so it is skipped. Line 7 is row 6, the month-13 date, so its signup is blank and the amount is blank too. Line 9 is row 8, the one with spaces. Line 5 is row 4, the N/A.

Now look at the cleaned file:

$ cat clean.csv
id,name,city,signup,amount
1,Zoë Martín,Málaga,2026-03-04,120.50
2,Ana Ruiz,Sevilla,2026-03-04,
3,José Pérez,,2026-03-15,88
4,Renée Dubois,Lyon,,45.00
6,Liam O'Brien,Dublin,,
7,Ünal Kaya,Istanbul,2026-03-22,310
8,Sam Lee,Leeds,,12.0

The accents now show correctly, every date has the same form, the placeholder words are gone, and the copy of row 1 is removed. Row 2 keeps its signup date, which was written day first as 04/03/2026 and is now 2026-03-04. The blank cells remain blank, and the report says where they are.

The output file is written in UTF-8. Check the bytes to confirm:

print(open("clean.csv", "rb").read()[:60])

b'id,name,city,signup,amount\r\n1,Zo\xc3\xab Mart\xc3\xadn,M\xc3\xa1laga,2026-03-0'

The letter ë is now two bytes, \xc3\xab, which is the standard UTF-8 form. The line ends are \r\n. The csv module writes a carriage return and a new line at the end of each row by default. Most programs read this without trouble.

Checking the result

A cleaning script should be checked, not trusted. Three checks are worth doing every time:

1. Count the rows. Eight data rows went in, one was a duplicate, so seven should come out. The report says kept 7 rows, which matches.
2. Search the output for leftover placeholders. The command below should print nothing:

$ grep -in "n/a" clean.csv

The command prints nothing. grep exits with status 1 when no line matches, which is the expected result here.

3. Open the original and the cleaned file side by side for a few rows and compare them by hand, especially the dates. A script that reads day and month in the wrong order will pass every automatic check and still be wrong.

Common mistakes

Reading a Windows-1252 file as UTF-8

This is the most common error in this lesson. The message is:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xeb in position 31: invalid continuation byte

The fix is to pass the real encoding to open, such as encoding="cp1252". Do not try to hide the error by adding errors="ignore" to the open call. That option silently drops the accented letters, so the names come out wrong and no error appears.

A byte order mark in the first column name

Some programs save a UTF-8 file with a hidden marker at the very start, called a byte order mark (BOM). It is three bytes that tell readers the file is UTF-8. A plain utf-8 reader keeps the marker as part of the first column name, so a lookup by name fails:

import csv
 
with open("bom.csv", "wb") as f:
    f.write("id,name\n1,Ana\n".encode("utf-8"))
with open("bom.csv", encoding="utf-8", newline="") as f:
    reader = csv.DictReader(f)
    print(reader.fieldnames)
    row = next(reader)
    print(row["id"])

['\ufeffid', 'name']
KeyError: 'id'

The first name is id, not id. The  is the BOM, shown as an escape code. The fix is to open the file with encoding="utf-8-sig". The sig part tells Python to skip the marker when it is there. Use this encoding when a file might come from a spreadsheet program.

Guessing the day and month

Nothing in the script stops you from reading 04/03/2026 as day first when the source meant month first. Both readings are valid dates, so no error appears. Check the source of the file. If the data cannot be checked, write the assumption into the script comment and into the report, so a reader knows what was chosen.

A column name that does not match

Column names are case sensitive. The header in the file says signup, not Signup. The lookup fails if the case differs:

import csv
 
with open("messy.csv", encoding="cp1252", newline="") as f:
    row = next(csv.DictReader(f))
print(row["Signup"])

KeyError: 'Signup'

Print the column names with reader.fieldnames when a KeyError appears, and compare them with the name in your code.

Forgetting to strip spaces

Without strip(), a cell with two spaces is not empty, so the script keeps it as a value. The date parser then fails on it, and the report shows a problem that is really a missing value. Always clean text before you test it.

A file that is not there

If the file name is wrong, Python stops at the first line that opens it:

FileNotFoundError: [Errno 2] No such file or directory: 'missing_file.csv'

Check that you are in the right folder with pwd, and check the spelling with ls.

Practice

Each exercise has an answer under it. Try each one before you read the answer. Run your code in the lab folder, with clean.py and messy.csv next to it.

Exercise 1: count the empty fields in one row

Write a function count_empty(row) that takes a row dictionary and returns how many values are empty strings. Test it on this row:

{"id": "4", "name": "Renée Dubois", "city": "", "signup": "", "amount": "45.00"}

It should return 2.

Show the answer

def count_empty(row):
    return sum(1 for value in row.values() if value == "")
 
 
print(count_empty({"id": "4", "name": "Renée Dubois", "city": "", "signup": "", "amount": "45.00"}))

The output is 2. The sum function adds up one 1 for each empty value. The part for value in row.values() loops over the values in the dictionary.

Exercise 2: add a date style

Change parse_date so it also accepts dates such as Mar 15, 2026, with the short month name first. Test it on Mar 15, 2026 and March 15, 2026. Which one works with the short format?

Show the answer

Add "%b %d, %Y" to DATE_FORMATS. Calling datetime.strptime(text, "%b %d, %Y") on each date gives:

Mar 15, 2026 2026-03-15
March 15, 2026 ValueError: time data 'March 15, 2026' does not match format '%b %d, %Y'

The short form %b matches Mar, but it does not match March. For the full month name, add a second format with %B in place of %b. Both formats can sit in the same list, and the loop will try each one.

Exercise 3: check that an amount is a number

Write a function is_number(text) that returns True when float(text) works, and False when it raises a ValueError. Test it on "120.50", "", "-", "12.0", "abc" and "1,000".

Show the answer

def is_number(text):
    try:
        float(text)
        return True
    except ValueError:
        return False
 
 
for sample in ["120.50", "", "-", "12.0", "abc", "1,000"]:
    print(repr(sample), is_number(sample))

'120.50' True
'' False
'-' False
'12.0' True
'abc' False
'1,000' False

The value "1,000" is not a number to Python, because the comma is not allowed in a float. A cleaning step could remove commas from amounts before this test, but only if you know the comma is a thousands separator and not a decimal separator used in another country.

Exercise 4: read a file that starts with a byte order mark

The file bom.csv from the mistakes section has a BOM in front of its first column name. Change the open call so that row["id"] works, and print the row.

Show the answer

import csv
 
with open("bom.csv", encoding="utf-8-sig", newline="") as f:
    for row in csv.DictReader(f):
        print(row)

{'id': '1', 'name': 'Ana'}

The encoding utf-8-sig removes the marker when it is at the start of the file. Reading a file that has no marker with this encoding is safe, so it is a good default for spreadsheet exports.

Exercise 5: save the report to a file

Change clean.py so the list of problems is also written to a file called report.txt, one problem per line. Keep the printed output.

Show the answer

Add these lines before the for problem in problems loop:

    with open("report.txt", "w", encoding="utf-8") as report:
        report.write("\n".join(problems) + "\n")

The "\n".join(problems) call puts a new-line character between the problems, and the + "\n" adds one at the end. Run the script again and open report.txt with cat. It should hold the same eight lines as the printed report.

Exercise 6: add a rule for a new placeholder

A new export uses the word tbc for a missing value. Add it to MISSING_WORDS, then explain why the result does not change for messy.csv.

Show the answer

Add "tbc" to the set:

MISSING_WORDS = {"", "n/a", "-", "null", "tbc"}

The output for messy.csv does not change, because that file has no tbc value. The rule only matters when a file contains the word. Adding a rule with no matching data is safe, but check the next file that uses it, because a placeholder that was never seen before will show up as an ordinary value until the rule is added.

Summary

• Fix the encoding first. Read the file with the encoding the source uses, such as cp1252, and write the cleaned file as UTF-8.
• Parse dates with a list of formats, and return nothing when no format works. Never guess a day and month order without knowing the source.
• Convert every kind of missing value to one form, and trim spaces before you test a value.
• Keep rows with missing values and report them. Do not invent values or drop rows silently.
• Print a report of every problem, and check the output by counting rows, searching for leftover placeholders and comparing a few rows by hand.