Cleaning messy data

Why it matters

Real data is messy. Survey answers have stray spaces, sensor logs contain "ERR", exports duplicate rows, and somebody typed "unknown" into a number column. Cleaning is often most of the work in an analysis, and mistakes here quietly change every result after it. The goal is a clean table produced by code you can rerun, never by hand edits.

Find the problems first

df.info()                  # dtypes and non-null counts
df.isna().sum()            # missing values per column
df.duplicated().sum()      # fully duplicated rows
df["city"].value_counts()  # spelling and spacing variants

Suppose the data looks like this:

   city      temp_c
0  Boston    21.5
1  boston    22.0
2  Denver    ERR
3  Boston    21.5
4  Austin     30.25

Problems: "boston" and " Boston" differ in case and spacing, temp_c is text because of "ERR" (pandas already reads empty cells, NA and n/a as missing, but not arbitrary codes), and row 3 may duplicate row 0 once the city is cleaned.

Fixing text: the .str accessor

String methods on a whole column live under .str:

df["city"] = df["city"].str.strip().str.title()
# " boston " -> "Boston"

Other useful ones: .str.lower(), .str.replace("$", "", regex=False), .str.contains("lab"), .str.split("-").

Fixing types

pd.to_numeric converts text to numbers. With errors="coerce", anything that cannot be converted becomes NaN (missing) instead of raising an error:

df["temp_c"] = pd.to_numeric(df["temp_c"], errors="coerce")

Strip units or symbols first if needed:

df["price"] = pd.to_numeric(df["price"].str.replace("$", "", regex=False), errors="coerce")

For dates, use pd.to_datetime(df["date"], errors="coerce"). Use .astype(int) only when you are sure there are no missing values.

Handling missing values

NaN means "no value". Most pandas statistics skip it (mean() ignores NaNThe marker pandas uses for a missing value; most statistics such as mean() skip it.), but you still have to decide what missing means in your data.

df.dropna(subset=["temp_c"])        # drop rows missing temp_c
df["temp_c"].fillna(0)              # fill with a value (rarely right for measurements)
df["temp_c"].fillna(df["temp_c"].median())   # fill with the median

Rules of thumb:

• Drop when the row cannot be used without that value and there are few such rows.
• Fill when a sensible value exists, such as 0 for "no sales recorded", and say so in your notes.
• Keep as NaN when later steps can skip it and dropping would throw away useful columns.
• Count before and after. Report how many rows you removed and why.

Removing duplicates

df = df.drop_duplicates()                     # rows identical in every column
df = df.drop_duplicates(subset=["order_id"])  # one row per order_id (keeps the first)

Clean text before removing duplicates, otherwise "Boston" and "boston " look different and survive.

Putting it in a function

Wrap the steps in a function that returns a new DataFrame, so the raw data stays untouched and you can rerun the cleaning:

def clean_temps(raw):
    df = raw.copy()
    df["city"] = df["city"].str.strip().str.title()
    df["temp_c"] = pd.to_numeric(df["temp_c"], errors="coerce")
    df = df.dropna(subset=["temp_c"])
    df = df.drop_duplicates()
    return df.reset_index(drop=True)

reset_index(drop=True) renumbers rows 0, 1, 2, ... after rows were removed.

Common mistakes

• Editing the original in place without meaning to. Start with df = raw.copy().
• Assigning to a filtered slice. df[df["x"] > 0]["y"] = 1 may not change df. Use df.loc[df["x"] > 0, "y"] = 1.
• Filling measurements with 0. A missing temperature is not 0 degrees.
• Deduplicating before normalising text. Near-duplicates survive.
• Silently dropping rows. Always know how many rows you lost.

Quick recap

• Inspect with info, isna().sum(), duplicated().sum() and value_counts().
• .str.strip().str.title() normalises text; pd.to_numeric(..., errors="coerce") fixes numbers.
• Decide per column whether to drop, fill or keep missing values, and record the choice.
• Normalise text first, then drop_duplicates, inside a function that returns a copy.

Try it

In the Do tab you will write clean(df) for a messy city-temperature table: fix the city names, convert temperatures, drop unusable rows and remove duplicates.