Data Cleaning in Python

Every data scientist learns the same lesson early: the dataset is never as clean as it looks. Numbers arrive wrapped in text, dates come as strings, the same category is spelled four different ways, and somewhere in there a customer has a birth date in the future. Cleaning this up is not glamorous, but it is where analysis quietly succeeds or fails. This article is a practical tour of the whole repertoire in pandas: type conversion, string normalization, duplicates, category consistency, missing values, fuzzy matching, and record linkage.

Start by Checking What Pandas Thinks You Have

Before cleaning anything, find out what types pandas assigned to each column, because the assignment is often the first clue something is wrong.

df.info()
df["trip_duration"].dtype
df["trip_duration"].describe()
df["trip_duration"] = df["trip_duration"].astype("int")
df["membership"] = df["membership"].astype("category")
assert df["membership"].dtype == "category"

df.info() is the first thing to run on any new dataset: it lists every column’s dtype and non-null count in one screen, and problems jump out immediately. A column you expect to be numeric showing up as object means there is text hiding in it somewhere. astype() converts a column to a different type, and describe() gives you summary statistics that change shape depending on the dtype, mean and standard deviation for numbers, top value and frequency for categories.

The assert statement deserves a special mention because it is the cheapest insurance in data work. It does nothing when its condition is true and crashes loudly when it is false. Sprinkle assertions after each cleaning step and a mistake stops your pipeline immediately, instead of flowing silently into wrong results three notebooks later.

Download the free cheat sheet below:

Scrub the Strings Before Converting

A column might look numeric, “30 minutes”, “45 minutes”, but pandas stores it as text because of the unit suffix. Convert first and you get an error; scrub first and the conversion goes through.

df["trip_duration"] = df["trip_duration"].str.strip("minutes")
df["trip_duration"] = df["trip_duration"].astype(int)
df["customer_name"] = df["customer_name"].str.replace("Dr.", "")
df["customer_name"] = df["customer_name"].str.replace("Mr.", "")
df["region"] = df["region"].str.lower()
df["region"] = df["region"].replace({"eur": "europe"})
df["size_label"] = df["size_label"].str.strip()

The same pattern handles most dirty text fields: strip honorifics out of names, lowercase everything so “Europe”, “europe”, and “EUROPE” collapse into one category, then fix known typos with a replacement dictionary. Two methods look similar here and behave differently. .str.replace substitutes a substring inside each value, while plain .replace swaps entire values using a dictionary. And .str.strip() with no argument removes leading and trailing whitespace, the fix for the eternal problem of " medium " and "medium" counting as different values.

Capping Impossible Values

Sometimes the data contains values that simply cannot be right: a bicycle wheel size of 50 inches, or a rental date in 2050. You can drop those rows, but capping is gentler. You keep the row and force the value into a sensible range, a technique statisticians call winsorizing.

df.loc[df["wheel_size"] > 27, "wheel_size"] = 27
import datetime as dt
today = dt.date.today()
df.loc[df["rental_date"] > today, "rental_date"] = today

The idiom to internalize is df.loc[mask, "col"] = value. The boolean comparison builds a mask that is true exactly where the constraint is violated, and the assignment touches only those rows, leaving everything else alone. This is the standard pandas pattern for “modify some rows, leave the rest” and it shows up again and again in cleaning work.

Dates Are Strings Until You Make Them Dates

A date stored as a string cannot do date math. pd.to_datetime parses strings into proper datetime64 values, and two arguments make it survive real-world mess.

import pandas as pd
df["rental_date"] = pd.to_datetime(df["rental_date"], errors="coerce")
df["year"] = df["rental_date"].dt.strftime("%Y")
df["month"] = df["rental_date"].dt.month
df["weekday"] = df["rental_date"].dt.weekday

errors="coerce" is the important one: instead of crashing the entire conversion because one row contains “N/A” or “tomorrow”, unparseable strings become NaT, Not a Time, the datetime cousin of NaN, which you can find and handle afterwards. Older tutorials also pass infer_datetime_format=True to speed up parsing, but modern pandas infers the format automatically and has deprecated that flag, so you can leave it out.

Once converted, the .dt accessor unlocks the date toolkit: .dt.month and .dt.weekday pull numeric components (weekday counts from 0 for Monday), and .dt.strftime("%Y") formats dates into strings using the same format codes C programmers have used for decades.

Duplicates Come in Two Flavors

Exact duplicates have identical values in every column, and drop_duplicates() deletes them in one call. Partial duplicates are sneakier: they share a key, like a rental ID, but differ slightly in other columns thanks to typos or repeated data entry. Those need aggregation, not deletion.

duplicates = df["rental_id"].duplicated(keep=False)
df[duplicates].sort_values("rental_id")
df_clean = df.drop_duplicates()
df_unique = df_clean.groupby("rental_id").agg({
    "rider_birth_year": "min",
    "trip_duration": "mean"
}).reset_index()
assert df_unique.duplicated(subset="rental_id").sum() == 0

The keep=False flag is the inspection tool: by default duplicated() marks only the second and later copies, but with keep=False it marks every copy, so sorting by the key lines the duplicates up next to each other where you can actually look at them. For the fix, group by the key and tell pandas how to reconcile each column, the earliest birth year, the average duration, whatever makes sense for the field. And as always, close with an assert that proves the duplicates are gone.

Keeping Categories Honest

Suppose a survey column is supposed to contain only “Clean”, “Average”, or “Dirty”, but typos and stray entries crept in. Set algebra finds the intruders elegantly.

invalid = set(df["cleanliness"]).difference(valid_categories["cleanliness"])
mask = df["cleanliness"].isin(invalid)
df[mask]
df[~mask]

Take the unique values actually present, subtract the values that are allowed, and whatever remains should not be there. The isin mask then isolates the offending rows, and ~mask flips it to keep the clean ones.

Two related tools handle the opposite problem, when categories are valid but too fine-grained. A replacement dictionary collapses values into broader buckets, and pd.cut does the same for numbers.

import numpy as np
mappings = {
    "Monday": "weekday", "Tuesday": "weekday",
    "Saturday": "weekend", "Sunday": "weekend"
}
df["day_type"] = df["day"].replace(mappings)
bins = [0, 60, 180, np.inf]
labels = ["short", "medium", "long"]
df["wait_type"] = pd.cut(df["wait_minutes"], bins=bins, labels=labels)

pd.cut chops a numeric range into labeled buckets, and np.inf makes a convenient open-ended top bin so the longest waits have somewhere to land.

When Two Columns Should Agree

Some columns are supposed to be derivable from others. Age should equal the current year minus the birth year. When they disagree, one of them is lying.

import datetime as dt
today = dt.date.today()
computed_ages = today.year - df["birth_date"].dt.year
inconsistent = df[df["age"] != computed_ages]
print(f"Inconsistent ages: {inconsistent.shape[0]}")

The pattern generalizes well beyond ages: compute the expected value from the source column, compare it to the stored value, and investigate the rows that disagree. This one check catches data-entry errors, stale fields nobody updated, and botched merges where rows got shuffled. Any time column A should be derivable from column B, write the derivation as code and verify it holds.

One Unit to Rule Them All

A numeric column in mixed units is unusable for aggregation. An average over a column where some rows are in dollars and some in euros means nothing. The fix takes two masked assignments, one for the values and one for the label.

eu_mask = df["currency"] == "euro"
df.loc[eu_mask, "balance"] = df.loc[eu_mask, "balance"] * 1.1
df.loc[eu_mask, "currency"] = "dollar"
assert df["currency"].unique() == "dollar"

Build the mask of rows in the wrong unit, multiply just those values by the conversion rate, update the unit label so downstream code knows everything is consistent, and assert that unique() now returns only the target unit. If a straggler slipped through, the assertion fails on the spot rather than letting a half-converted column poison your sums.

Missing Values Have Patterns, and Patterns Have Meaning

Counting NaNs is the easy part. The more valuable question is whether the missingness follows a pattern, because the pattern tells you why the data is missing, and the why determines the right fix.

import missingno as msno
import matplotlib.pyplot as plt
df.isna().sum()
msno.matrix(df)
plt.show()
df_sorted = df.sort_values("age")
msno.matrix(df_sorted)
plt.show()

The missingno matrix plot draws your DataFrame as a grid, dark where data exists and blank where it is missing. On its own it shows you which columns are sparse. The trick is sorting by another column first: suddenly you might see that every account opened before 2015 is missing the new field, or that missingness clusters in one region. Random missingness and systematic missingness call for very different treatments, and this one plot distinguishes them.

Once you understand the pattern, three options cover most cases.

df = df.dropna(subset=["customer_id"])
df["balance"] = df["balance"].fillna(df["invoice_amount"] * 5)
df = df.fillna({"col_a": 0, "col_b": df["other_col"] * 5})

Drop rows missing a key column you cannot work without, fill with a value computed from another column when a sensible estimate exists, or pass a dictionary to fillna to give each column its own default in a single call.

Fuzzy Matching: When Exact Equality Is Too Strict

To a computer, “asain”, “Asian”, and “asian” are three unrelated strings. Fuzzy matching computes a similarity score between strings, 100 for identical, 0 for nothing in common, and the standard library for it is thefuzz.

from thefuzz import process
matches = process.extract("asian", df["cuisine"].unique(), limit=10)
# [("asian", 100), ("Asian", 95), ("asain", 88), ...]
categories = ["american", "asian", "italian"]
for cuisine in categories:
    matches = process.extract(cuisine, df["cuisine"], limit=len(df["cuisine"]))
    for match_str, score in matches:
        if score >= 80:
            df.loc[df["cuisine"] == match_str, "cuisine"] = cuisine

The cleaning recipe: for each canonical category you know should exist, find every value in the data scoring above a threshold against it and overwrite those values with the canonical form. After one pass, all the typo variants collapse into clean categories. The threshold of 80 is a community convention, high enough to avoid false matches, low enough to catch real typos. If wrong values are getting swallowed, raise it toward 90; if obvious typos are slipping through, lower it toward 70 and inspect the results.

Record Linkage: Deduplicating Across Two Tables

Record linkage is the heavyweight version of fuzzy matching, for when you have two DataFrames describing the same kind of entity, say restaurant listings from two sources, but no shared ID to merge on. The recordlinkage library structures it as a pipeline.

import recordlinkage
indexer = recordlinkage.Index()
indexer.block("cuisine")
pairs = indexer.index(venues_a, venues_b)
comp = recordlinkage.Compare()
comp.exact("city", "city", label="city")
comp.exact("cuisine", "cuisine", label="cuisine")
comp.string("venue_name", "venue_name", label="name", threshold=0.8)
potential_matches = comp.compute(pairs, venues_a, venues_b)
matches = potential_matches[potential_matches.sum(axis=1) >= 3]
matched_indices = matches.index.get_level_values(1)
new_rows = venues_b[~venues_b.index.isin(matched_indices)]
full_df = pd.concat([venues_a, new_rows])

Each stage solves a specific problem. Blocking exists because comparing every row to every other row is N times M comparisons, often billions; by only comparing pairs that match exactly on one field, like cuisine, you collapse the search space to something tractable. The comparison stage then scores each surviving pair: exact matches on city and cuisine score one or zero, while the name comparison uses string similarity with a 0.8 threshold to tolerate spelling differences. Filtering keeps pairs whose scores sum to at least 3, meaning all three fields agreed.

The last three lines do the actual deduplication. The matches carry a two-level index pairing a row from each table, so get_level_values(1) extracts which rows of the second table already exist in the first. Inverting that membership test keeps only the genuinely new rows, and concatenating them onto the first table produces a deduplicated union of both sources. Older tutorials end this pipeline with df1.append(...), but append was removed from modern pandas, so pd.concat is the way.

The Checklist

When a new dataset lands on your desk, this order of operations covers almost everything: run df.info() to check types and nulls, fix the dtypes with astype and pd.to_datetime, clean string columns with strip, replace, and lower, constrain value ranges by capping outliers and future dates, find and resolve duplicates with drop_duplicates plus a groupby aggregation, validate categories with a set difference against the allowed list, unify units with masked assignments, handle missing values once you have looked at their pattern, collapse typos with fuzzy matching, and reach for record linkage when duplicates span two tables.

None of these steps is difficult on its own. The discipline is doing them in order, asserting after each one, and resisting the urge to start the analysis before the foundation is solid. Clean data does not make a good analysis, but dirty data reliably ruins one.

See you soon.