Real datasets are messy, and cleaning is a large part of an analyst's job. A consistent checklist makes cleaning faster and, just as important, explainable to others.
The examples use a fictional hostel booking export from StayNest.
1. Tidy column names
df.columns = (df.columns.str.strip().str.lower()
.str.replace(r"[^a-z0-9]+", "_", regex=True).str.strip("_"))"Booking Date " becomes booking_date, which is much easier to type and read.
2. Fix data types
df["amount"] = pd.to_numeric(df["amount"].astype(str).str.replace(",", ""), errors="coerce")
df["booking_date"] = pd.to_datetime(df["booking_date"], dayfirst=True, errors="coerce")
df["is_cancelled"] = df["is_cancelled"].map({"Y": True, "N": False})errors="coerce" turns unparseable values into NaN/NaT instead of crashing. Afterwards, check how many values became missing; that number tells you how messy the source was.
3. Clean text
df["city"] = df["city"].str.strip().str.title()
df["city"] = df["city"].replace({"Bangalore": "Bengaluru", "Gurgaon": "Gurugram"})
df["phone"] = df["phone"].astype(str).str.replace(r"\D", "", regex=True).str[-10:]Always run value_counts() on category columns after cleaning to catch remaining variants.
4. Remove duplicates
before = len(df)
df = df.drop_duplicates(subset="booking_id", keep="last")
print(before - len(df), "duplicate bookings removed")Decide what a duplicate means. The same booking exported twice is a duplicate; two bookings by the same guest on different dates are not.
5. Handle missing values
df.isna().mean().sort_values(ascending=False) # share missing per columnOptions, depending on the reason:
- Drop rows if a few are missing at random and the column is essential.
- Fill with a meaningful value: zero for "no discount", the median for a measurement, "Unknown" for a category.
- Fill within groups:
df["price"].fillna(df.groupby("room_type")["price"].transform("median")). - Keep and flag: add
price_missing = df["price"].isna()so you can check whether missing rows behave differently.
6. Validate values
df[df["amount"] < 0]
df[df["checkout_date"] < df["checkin_date"]]
df[~df["status"].isin(["Confirmed", "Cancelled", "No-show"])]Write these checks as code so they run every time the data refreshes.
7. Outliers
q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
suspicious = df[(df["amount"] < q1 - 1.5 * iqr) | (df["amount"] > q3 + 1.5 * iqr)]Look at the suspicious rows before deciding. A group booking may be a genuine large amount, while an extra zero typed by mistake is an error.
8. Derive useful columns
df["nights"] = (df["checkout_date"] - df["checkin_date"]).dt.days
df["booking_month"] = df["booking_date"].dt.to_period("M")
df["lead_days"] = (df["checkin_date"] - df["booking_date"]).dt.days9. Keep a cleaning log
For each step, note what you changed and how many rows it affected. In a project write-up, this log shows judgement and care, and interviewers often ask about it.
10. Wrap it in a function
def clean_bookings(raw: pd.DataFrame) -> pd.DataFrame:
df = raw.copy()
# steps 1-8
return dfNext month's file then takes one line to clean.
What not to do
- Do not drop every row with any missing value (
dropna()with no arguments) without checking how much data you lose. - Do not edit the raw file by hand; keep raw data untouched and clean in code.
- Do not fill missing values before splitting data for machine learning; fit imputation on training data only.
Practise
Our pandas cleaning set covers these steps, and the dates set covers parsing and date arithmetic. For the bigger picture, see the EDA checklist.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: