Cleaning and Validating Data

Introduction

The practice data from the basics course was clean. Real data is not. Analysts often spend more time cleaning than analysing. The goal is not perfect data, but data you understand, with problems found, handled and written down. This course continues with the library loans (df from before), and the outputs are from real runs.

Look before you fix

Start with questions about the data:

• How many rows and columns? Are the types right?
• Which columns have missing values, and how many?
• Are there duplicates?
• Are there impossible values, such as a return date before the loan date?
• Is text consistent: spelling, capitals, spaces?

print(df.shape)
print(df.isna().sum().to_string())

In our data only returned_on has gaps (3 rows), and those are legitimate: the books are still out. Not every missing value is an error.

Duplicates

print(df.duplicated(subset=["book_id", "member_id", "loaned_on"]).sum())

0

No row repeats the same book, member and date. Choose the columns that define "the same record". If duplicates appear, find out why before you drop them. Two identical-looking loans might be real.

Impossible values

print((df["returned_on"] < df["loaned_on"]).sum())

0

No returns happen before loans. Checks like this catch data entry mistakes and wrongly formatted dates. Other typical checks:

• Numbers outside a sensible range (negative ages, loan lengths of 400 days)
• Future dates where none are possible
• IDs in one table that do not exist in another (orphans)

Treat problems deliberately

For each problem, choose and record one approach:

• Fix it, if the correct value is certain, such as a clear typo.
• Flag it with a new column, so you can include or exclude it.
• Exclude it from specific calculations, and say so.
• Leave it, if it is legitimate.
• Ask the data owner when you cannot tell.

Never alter the raw data. Make a cleaned copy with code, so the steps are repeatable.

Standardise text

df["city"] = df["city"].str.strip().str.title()

Inconsistent text splits groups: "leeds", "Leeds " and "LEEDS" would count as three cities. Standardise, then check the distinct values with df["city"].unique().

Validation checks that run every time

Turn your checks into code that stops loudly if something is wrong:

def validate(df):
    assert df["id"].is_unique, "Duplicate loan ids"
    assert df["loaned_on"].notna().all(), "Missing loan dates"
    assert (df["returned_on"].isna() | (df["returned_on"] >= df["loaned_on"])).all(), \
        "Return before loan"
    assert df["city"].notna().all(), "Missing city"

validate(df)
print("data checks passed")

data checks passed

Run validate after loading and after cleaning. When new data arrives with a problem, you will know at once instead of publishing wrong results.

Document the cleaning

In your README or notebook, keep a short cleaning log: what you found, how many rows were affected, what you did and why. Someone should be able to reproduce your cleaned data from the raw data.

Try it yourself

Take your dataset, deliberately introduce three problems (a duplicate row, a wrong date and a misspelt city), then write checks that catch each one, and a cleaning step that resolves them. Record what you did.

Summary

• Look for missing values, duplicates, impossible values and inconsistent text before analysing.
• Choose fix, flag, exclude, leave or ask for each problem, and document the decision.
• Write validation checks as code that fails loudly, and never edit the raw data.