Cleaning data

Intermediate

Introduction

Data collected by people and systems is never perfect. Names are typed with extra spaces and random capitals, a form allows an age of -3, a field is left empty, the same customer appears twice. An analysis run on such data gives wrong answers without any error message: the duplicate counts twice, the -3 lowers the average age. Data cleaning finds these problems and fixes them in a way you can explain and repeat. Surveys of data scientists consistently find it takes more of their time than any other step.

How it works

Cleaning deals with four kinds of problem.

Inconsistent text. "Dhaka", "dhaka " and "DHAKA" are the same city, but Python sees three values. Standardise text with strip() to remove surrounding spaces and lower(), upper() or title() for case, and replace() for known variants.

Impossible values. A validation rule says what a value may be: an age is a whole number from 0 to 120, a price is not negative, a date is not in the future. A value that breaks the rule is almost always a mistake, and is best treated as missing.

Missing values. Empty fields, "NA", "n/a" or None mean the value was not recorded. There are three ways to handle them:

  • Drop the rows, which is safe when few are missing and they are missing at random, but wastes data and can bias results.
  • Flag them by keeping None, so later steps can skip them.
  • Impute them, filling in an estimate: the mean for symmetric numeric data, the median when there are outliers (it is not dragged by extreme values), or the most common value for categories.

Duplicates. The same record may appear twice, often with small differences. Decide which columns identify a record (an email address, or a name and city), normalise them, and keep only the first occurrence.

The order matters: standardise text first, so that duplicates and categories are recognised, then validate, then remove duplicates, then fill the remaining gaps. Keep the raw data unchanged, and count every change, so the report can say, for example, "2 ages were missing and filled with the median, 31.5".

In pandas the same steps are df["city"].str.strip().str.title(), df.drop_duplicates(), df["age"].fillna(df["age"].median()) and df.isna().sum().

Example

# Cleaning: fix text, treat impossible values as missing, drop duplicates, fill gaps.
import statistics

raw = [
    {"name": "  asha ", "age": "34", "city": "Dhaka"},
    {"name": "BEN", "age": "", "city": "dhaka "},
    {"name": "Chen", "age": "-3", "city": "Sylhet"},
    {"name": "asha", "age": "34", "city": "Dhaka"},
    {"name": "Dina", "age": "29", "city": ""},
]

clean = []
seen = set()
for row in raw:
    name = row["name"].strip().title()
    city = row["city"].strip().title() or "Unknown"
    age = int(row["age"]) if row["age"].strip() else None
    if age is not None and not 0 <= age <= 120:
        age = None  # an impossible age is treated as missing
    if (name, city) in seen:
        continue  # a duplicate of a row we already kept
    seen.add((name, city))
    clean.append({"name": name, "age": age, "city": city})

known = [r["age"] for r in clean if r["age"] is not None]
fill = statistics.median(known)
for r in clean:
    if r["age"] is None:
        r["age"] = fill
print("kept", len(clean), "of", len(raw), "rows; missing ages filled with the median,", fill)
for r in clean:
    print(r)
Open in compiler

Line by line

  1. Each row's name and city are stripped and title-cased; an empty city becomes "Unknown".
  2. An empty age becomes None, and an age outside 0 to 120 is treated as missing too.
  3. A row whose name and city were already seen is a duplicate and is skipped.
  4. The median of the known ages fills the missing ones, and the program reports what it did.

Expected result

kept 4 of 5 rows; missing ages filled with the median, 31.5
{'name': 'Asha', 'age': 34, 'city': 'Dhaka'}
{'name': 'Ben', 'age': 31.5, 'city': 'Dhaka'}
{'name': 'Chen', 'age': 31.5, 'city': 'Sylhet'}
{'name': 'Dina', 'age': 29, 'city': 'Unknown'}

In the real world

Hospitals clean patient records before any study, removing duplicate visits and impossible measurements. Banks validate every transaction field before it reaches a fraud model. Data teams often write their cleaning rules as code that runs every time new data arrives, so that the same mistakes are fixed the same way, and they keep a log of how many values each rule changed.

Common mistakes

  • Filling missing values with 0, which drags averages down and invents data.
  • Removing duplicates before standardising text, so " asha" and "Asha" both survive.
  • Using the mean to fill a column with extreme values, where the median is more representative.
  • Cleaning by hand in a spreadsheet, so the steps cannot be repeated or checked.

Best practices

  • Write a validation rule for every column and test every value against it.
  • Prefer the median for skewed numeric columns and the most common value for categories.
  • Report the number of values dropped, fixed and filled for each column.

Summary

Cleaning fixes inconsistent text, impossible values, missing values and duplicates. Standardise text, validate with explicit rules, treat bad values as missing, remove duplicates by a normalised key, and then drop, flag or impute what is still missing. Keep the raw data and report every change.

Quick check

1. Which is the better fill for a missing income when a few people earn enormous amounts?
2. An age of -3 is found. What is usually best?
3. Why standardise text before removing duplicates?
4. What is imputation?

Key takeaways

  • Real data is messy; cleaning usually takes most of a project's time.
  • Decide per column what counts as missing or impossible, and how to handle it.
  • Never clean silently: count and report every change you make.

Practice problems for this lesson

My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.