3. Cleaning data¶
Intermediate · 11 min read
Real logs are messy: missing values, numbers stored as text, inconsistent spelling, duplicates. Here's a deliberately messy export of chat feedback:
import numpy as np
import pandas as pd
raw = pd.DataFrame({
"user": [" Asha ", "ravi", "Meera", "ravi", None, "Kabir"],
"model": ["GPT-4o-mini", "gpt-4o-mini", "llama-3.1-8b", "gpt-4o-mini", "gpt-4o", "LLAMA-3.1-8B"],
"rating": ["5", "4", "not rated", "4", "3", "5"],
"latency_ms": [820, 960, np.nan, 960, 1500, 450],
"created": ["2026-03-01 09:15", "2026-03-01 11:40", "2026-03-02 08:05",
"2026-03-01 11:40", "2026-03-03 17:30", "2026-03-03 18:00"],
})
print(raw)
Output
user model rating latency_ms created
0 Asha GPT-4o-mini 5 820.0 2026-03-01 09:15
1 ravi gpt-4o-mini 4 960.0 2026-03-01 11:40
2 Meera llama-3.1-8b not rated NaN 2026-03-02 08:05
3 ravi gpt-4o-mini 4 960.0 2026-03-01 11:40
4 NaN gpt-4o 3 1500.0 2026-03-03 17:30
5 Kabir LLAMA-3.1-8B 5 450.0 2026-03-03 18:00
3.1 Find missing values¶
print(raw.isna().sum()) # missing values per column
print(raw[raw.isna().any(axis=1)]) # rows with at least one missing value
Output
user 1
model 0
rating 0
latency_ms 1
created 0
dtype: int64
user model rating latency_ms created
2 Meera llama-3.1-8b not rated NaN 2026-03-02 08:05
4 NaN gpt-4o 3 1500.0 2026-03-03 17:30
3.2 Tidy text¶
df = raw.copy() # keep the raw data untouched
df["user"] = df["user"].str.strip().str.title() # " Asha " → "Asha", "ravi" → "Ravi"
df["model"] = df["model"].str.lower() # one spelling per model
print(df[["user", "model"]])
Output
user model
0 Asha gpt-4o-mini
1 Ravi gpt-4o-mini
2 Meera llama-3.1-8b
3 Ravi gpt-4o-mini
4 NaN gpt-4o
5 Kabir llama-3.1-8b
3.3 Fix types¶
df["rating"] = pd.to_numeric(df["rating"], errors="coerce") # "not rated" → NaN
df["created"] = pd.to_datetime(df["created"])
print(df.dtypes)
3.4 Fill or drop missing values¶
df["latency_ms"] = df["latency_ms"].fillna(df["latency_ms"].median()) # fill with the median
df["user"] = df["user"].fillna("anonymous")
df = df.dropna(subset=["rating"]) # a rating is required: drop rows without one
print(df[["user", "rating", "latency_ms"]])
Output
user rating latency_ms
0 Asha 5.0 820.0
1 Ravi 4.0 960.0
3 Ravi 4.0 960.0
4 anonymous 3.0 1500.0
5 Kabir 5.0 450.0
Fill or drop?
Drop rows when the missing value is essential (no rating → useless for a rating report). Fill when a sensible default exists (median latency, "anonymous"). Never fill silently — keep a count of what you changed.
3.5 Remove duplicates¶
print(df.duplicated(subset=["user", "created"]).sum(), "duplicate row(s)")
df = df.drop_duplicates(subset=["user", "created"]).reset_index(drop=True)
print(len(df), "rows left")
3.6 Work with dates¶
df["day"] = df["created"].dt.date
df["hour"] = df["created"].dt.hour
df["weekday"] = df["created"].dt.day_name()
print(df[["created", "day", "hour", "weekday"]])
print(df[df["created"] >= "2026-03-02"].shape[0], "rows from 2 March on")
Output
created day hour weekday
0 2026-03-01 09:15:00 2026-03-01 9 Sunday
1 2026-03-01 11:40:00 2026-03-01 11 Sunday
2 2026-03-03 17:30:00 2026-03-03 17 Tuesday
3 2026-03-03 18:00:00 2026-03-03 18 Tuesday
2 rows from 2 March on
3.7 Add calculated columns¶
df["fast"] = df["latency_ms"] < 900 # boolean column
df["grade"] = np.where(df["rating"] >= 4, "good", "poor") # if/else
df["speed"] = pd.cut(df["latency_ms"], bins=[0, 600, 1000, 10_000],
labels=["fast", "ok", "slow"]) # bucket numbers
df = df.rename(columns={"latency_ms": "latency"})
print(df[["user", "model", "rating", "latency", "fast", "grade", "speed"]])
Output
user model rating latency fast grade speed
0 Asha gpt-4o-mini 5.0 820.0 True good ok
1 Ravi gpt-4o-mini 4.0 960.0 False good ok
2 anonymous gpt-4o 3.0 1500.0 False poor slow
3 Kabir llama-3.1-8b 5.0 450.0 True good fast
Avoid apply when a column operation exists
df["x"].apply(lambda v: v * 2) runs Python once per row. df["x"] * 2 runs once for the whole
column — much faster on big data. Use apply only for logic with no vectorised version.
Practice¶
- Clean a
pricecolumn of strings like"₹1,299"into numbers (str.replace+to_numeric). - Count how many ratings were invalid before you dropped them.
Next: Grouping & aggregation →