Module 4: Data analysis with pandas · 10 min
Cleaning messy data
Real data has missing values, duplicates and wrong types. Cleaning is often most of a data job.
import pandas as pd
# a small messy table: odd spacing and capitals, a missing value, a duplicate row, dates as text
df = pd.DataFrame({
"city": [" lahore", "Karachi ", "Multan", "Karachi "],
"sales": [250, 400, None, 400],
"date": ["2024-01-05", "2024-01-06", "2024-01-07", "2024-01-06"],
})
print(df.isna().sum()) # missing values per column
df["city"] = df["city"].str.strip().str.title() # tidy text
df = df.drop_duplicates() # remove repeated rows
df["sales"] = df["sales"].fillna(0) # fill missing sales with 0 (or use df.dropna() to drop them)
df["date"] = pd.to_datetime(df["date"]) # turn text into real dates
print(df)
- Decide on purpose: dropping missing rows and filling them give different results.
- Keep the original file untouched and save the cleaned data as a new file.
- Write down what you changed, so someone else (or you next month) can repeat it.