Skip to content
Kuza Ndoto Academy

Cleaning Data

Beginner → IntermediateChapter 3 · Page 9 of 205 min readUpdated 15 Nov 2026

Real-world data is messy: names typed in different ways, missing values, repeated rows and numbers stored as text. Cleaning fixes these problems so your analysis is correct. It often takes more time than any other step, and it is where careful analysts earn their reputation.

A messy dataset

Here is a small customer list from a shop. Look closely and you'll spot several problems.

import pandas as pd

raw = pd.DataFrame({
    "customer": ["Achieng", "brian ", "Achieng", "Fatuma", "Kamau"],
    "county": ["Kisumu", "nairobi", "Kisumu", "Mombasa", None],
    "amount": ["1200", "850", "1200", "n/a", "2300"],
})
print(raw)
customer county amount 0 Achieng Kisumu 1200 1 brian nairobi 850 2 Achieng Kisumu 1200 3 Fatuma Mombasa n/a 4 Kamau None 2300
  • Row 2 is an exact duplicate of row 0.
  • "brian " has a lowercase letter and a trailing space; "nairobi" is lowercase.
  • Kamau's county is missing.
  • amount is stored as text, and one value is "n/a".

Step 1: find the problems

print(raw.isna().sum())     # missing values per column
print(raw.duplicated())     # True for rows that repeat an earlier row
customer 0 county 1 amount 0 dtype: int64 0 False 1 False 2 True 3 False 4 False dtype: bool

isna() marks each missing value as True, and sum() counts them per column. Notice that "n/a" is not counted as missing: in this DataFrame it is just a three-letter string. (When reading a CSV file, pandas would treat n/a as missing automatically; see Loading Data.)

Step 2: fix them

clean = raw.drop_duplicates().copy()                                # remove repeated rows
clean["customer"] = clean["customer"].str.strip().str.title()       # tidy names
clean["county"] = clean["county"].fillna("Unknown").str.title()     # fill, then tidy
clean["amount"] = pd.to_numeric(clean["amount"], errors="coerce")   # text to numbers
clean["amount"] = clean["amount"].fillna(clean["amount"].median())  # fill the gap

print(clean)
print(clean.dtypes)
customer county amount 0 Achieng Kisumu 1200.0 1 Brian Nairobi 850.0 3 Fatuma Mombasa 1200.0 4 Kamau Unknown 2300.0 customer object county object amount float64 dtype: object

Line by line:

  • drop_duplicates() removed row 2. The .copy() makes clean a fully independent table (more on why below).
  • The .str accessor applies string methods to every value: strip() removes surrounding spaces and title() capitalises each word.
  • fillna("Unknown") replaced the missing county with a label.
  • pd.to_numeric(..., errors="coerce") converted the text to numbers and turned anything unconvertible ("n/a") into NaN.
  • The missing amount was filled with the median of the known amounts (850, 1200 and 2300), which is 1200.0.

The index still reads 0, 1, 3, 4 because a row was removed. Add clean = clean.reset_index(drop=True) if you want 0, 1, 2, 3 again.

Before (raw) After (clean) customercountyamount 0AchiengKisumu"1200" 1"brian "nairobi"850" 2AchiengKisumu"1200" 3FatumaMombasa"n/a" 4KamauNone"2300" clean customercountyamount 0AchiengKisumu1200.0 1BrianNairobi850.0 3FatumaMombasa1200.0 4KamauUnknown2300.0 problem value duplicate row removed fixed or filled value
Figure 1. The raw table has text that needs tidying, a duplicate row, a missing county and an amount stored as "n/a". After cleaning, every amount is a number and every row is unique.

Missing values: drop or fill?

MethodWhat it doesUse when
df.dropna()Removes every row with any missing valueOnly a few rows are affected and they aren't special
df.dropna(subset=["county"])Removes rows missing a value in the listed columnsOnly some columns matter for your question
df["col"].fillna(value)Replaces missing values with a fixed valueA sensible default exists ("Unknown", 0)
fillna(df["col"].median())Fills with the median (or mean)Numeric columns where a typical value is reasonable
print(raw.dropna().shape)   # Kamau's row is dropped
(4, 3)
Common mistake

Filling missing numbers with the mean when the data has extreme values. One customer who spent KES 200,000 can drag the mean far above what a typical customer spends. The median is not affected by extremes, so it is usually the safer fill value. Also remember that filling invents data: always note how many values you filled. See Descriptive Statistics for mean versus median.

Other everyday cleaning tools

df = df.rename(columns={"amount": "amount_kes"})           # rename a column
df["county"] = df["county"].replace({"Nbi": "Nairobi"})    # fix specific values
df = df.drop(columns=["notes"])                            # remove a column
df["units"] = df["units"].astype(int)                      # change a column's type
df["date"] = pd.to_datetime(df["date"])                    # text to real dates
df["phone"] = df["phone"].str.replace(" ", "")             # remove spaces inside text

These lines assume a DataFrame that has those columns; they show the pattern rather than a single runnable example. Notice that each one assigns the result back. Most pandas methods return a new object and leave the original unchanged.

SettingWithCopyWarning and chained assignment

This is the most famous pandas pitfall. It appears when you change a table that was selected from another table.

sales = pd.DataFrame({
    "county": ["Nairobi", "Kisumu", "Nairobi"],
    "units": [30, 12, 8],
})

# Problem 1: changing a filtered subset
nairobi = sales[sales["county"] == "Nairobi"]
nairobi["units"] = 0       # SettingWithCopyWarning

# Problem 2: chained assignment (select, then set, in one line)
sales[sales["county"] == "Nairobi"]["units"] = 0   # sales is NOT changed

In problem 1, pandas can't tell whether you meant to change sales too, so it warns you. In problem 2, the first pair of brackets creates a temporary copy, the value is set on that copy, and the copy is thrown away, so sales stays the same. The fixes:

# Fix 1: change the original in ONE step with .loc[rows, column]
sales.loc[sales["county"] == "Nairobi", "units"] = 0
print(sales)

# Fix 2: if you want a separate table, say so with .copy()
nairobi = sales[sales["county"] == "Nairobi"].copy()
nairobi["units"] = 100     # no warning; sales is untouched
county units 0 Nairobi 0 1 Kisumu 12 2 Nairobi 0
Common mistake

Never set values with two sets of brackets in a row, like df[condition]["col"] = value. Use df.loc[condition, "col"] = value instead. The same applies to df["col"].fillna(0, inplace=True): write df["col"] = df["col"].fillna(0). From pandas 3.0, "Copy-on-Write" is always on, so chained assignment never changes the original at all; code written with .loc and .copy() works the same in every version.

Key points

  • Find problems first with isna().sum(), duplicated(), dtypes and unique().
  • Fix text with the .str accessor, numbers with pd.to_numeric(), and gaps with dropna() or fillna().
  • Prefer the median over the mean when filling numbers that may contain extremes.
  • Change data with df.loc[rows, col] = ... and make subsets independent with .copy().
Up next · pandasFiltering & SortingFind the rows that matter.
→

Tip: use the ← and → keys to move between pages.