Cleaning Data
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)
- 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.
amountis 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
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)
Line by line:
drop_duplicates()removed row 2. The.copy()makescleana fully independent table (more on why below).- The
.straccessor applies string methods to every value:strip()removes surrounding spaces andtitle()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") intoNaN.- 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.
Missing values: drop or fill?
| Method | What it does | Use when |
|---|---|---|
df.dropna() | Removes every row with any missing value | Only a few rows are affected and they aren't special |
df.dropna(subset=["county"]) | Removes rows missing a value in the listed columns | Only some columns matter for your question |
df["col"].fillna(value) | Replaces missing values with a fixed value | A 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
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
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(),dtypesandunique(). - Fix text with the
.straccessor, numbers withpd.to_numeric(), and gaps withdropna()orfillna(). - 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().
Tip: use the ← and → keys to move between pages.
