By WebNest Studio Engineering Team
Python Tutorial
Cleaning Data with pandas
Real-world data is messy: missing values, duplicate rows, numbers stored as text, inconsistent spelling, dates in odd formats, impossible outliers. Analysts often say that 80% of the work is cleaning — and conclusions drawn from dirty data are simply wrong.
This lesson works through a deliberately messy dataset and fixes it step by step: detecting and handling missing values, removing duplicates, fixing types, standardising text, parsing dates, handling outliers, and renaming and reordering columns.
Why it matters
Real data is messy: values are missing, rows are duplicated, names have stray spaces and inconsistent capitals. Analysts commonly say that most of their time goes on cleaning. Results computed from dirty data are simply wrong, so being able to clean a dataset methodically is one of the most valuable practical skills in data work.
Missing Values
pandas marks missing data as NaN/NaT/None (shown as NaN or <NA>). Count them with df.isna().sum(). Then decide per column: dropna() removes rows (use subset= to consider only key columns), fillna(value) fills with a constant, the mean/median or the most common value, and ffill()/bfill() carry values forward/backward in time series. Record what you did — filling changes the data.
Duplicates, Types and Text
duplicated() flags repeated rows and drop_duplicates(subset=[...], keep="first") removes them. Convert text to numbers with pd.to_numeric(col, errors="coerce") (bad values become NaN rather than crashing), dates with pd.to_datetime(col, format=..., errors="coerce"), and repeated labels with astype("category") to save memory. Clean text with .str.strip(), .str.lower()/.title() and .str.replace(), and unify spellings with replace({...}).
Outliers and Tidy Columns
Check ranges with describe() and rules you know (ages between 0 and 120, no negative quantities). The IQR rule marks values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR as possible outliers; decide whether they are errors to remove, values to cap (clip), or real extremes to keep. Finally, rename(columns=...) to consistent snake_case names, drop unused columns and reset_index(drop=True).
Examples
Inspecting a messy dataset
import numpy as np
import pandas as pd
raw = pd.DataFrame({
"Customer Name": [" Asha Rao", "ravi kumar", "Meera Iyer", "ravi kumar", "Kiran Shah", None],
"City": ["pune", "Mumbai ", "PUNE", "Mumbai ", "Bengaluru", "Delhi"],
"Order Date": ["2026-01-05", "2026-01-07", "not recorded", "2026-01-07", "2026-01-09", "2026-01-10"],
"Amount": ["1,250", "480", "95000", "480", "n/a", "720"],
"Age": [34, 29, np.nan, 29, 41, 250],
})
print(raw.dtypes)
print(raw.isna().sum())
print("duplicate rows:", raw.duplicated().sum())
Customer Name str
City str
Order Date str
Amount str
Age float64
dtype: object
Customer Name 1
City 0
Order Date 0
Amount 0
Age 1
dtype: int64
duplicate rows: 1
Cleaning it step by step
import numpy as np
import pandas as pd
raw = pd.DataFrame({
"Customer Name": [" Asha Rao", "ravi kumar", "Meera Iyer", "ravi kumar", "Kiran Shah", None],
"City": ["pune", "Mumbai ", "PUNE", "Mumbai ", "Bengaluru", "Delhi"],
"Order Date": ["2026-01-05", "2026-01-07", "not recorded", "2026-01-07", "2026-01-09", "2026-01-10"],
"Amount": ["1,250", "480", "95000", "480", "n/a", "720"],
"Age": [34, 29, np.nan, 29, 41, 250],
})
df = raw.rename(columns=lambda c: c.strip().lower().replace(" ", "_")) # snake_case names
df = df.drop_duplicates() # 1 exact duplicate
df = df.dropna(subset=["customer_name"]) # no name -> unusable
df["customer_name"] = df["customer_name"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title()
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce") # bad text -> NaT
df["amount"] = pd.to_numeric(df["amount"].str.replace(",", ""), errors="coerce")
df.loc[~df["age"].between(0, 120), "age"] = np.nan # impossible ages
df["age"] = df["age"].fillna(df["age"].median())
df["amount"] = df["amount"].fillna(df["amount"].median())
df = df.reset_index(drop=True)
print(df)
print(df.dtypes)
customer_name city order_date amount age
0 Asha Rao Pune 2026-01-05 1250.0 34.0
1 Ravi Kumar Mumbai 2026-01-07 480.0 29.0
2 Meera Iyer Pune NaT 95000.0 34.0
3 Kiran Shah Bengaluru 2026-01-09 1250.0 41.0
customer_name str
city str
order_date datetime64[us]
amount float64
age float64
dtype: object
Detecting outliers with the IQR rule
import pandas as pd
amounts = pd.Series([420, 480, 510, 530, 560, 600, 640, 700, 95000], name="amount")
q1, q3 = amounts.quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print(f"Q1={q1}, Q3={q3}, IQR={iqr}, fences=({low}, {high})")
print("outliers:", amounts[(amounts < low) | (amounts > high)].tolist())
print("mean with / without:", round(amounts.mean(), 1), round(amounts[amounts <= high].mean(), 1))
print("median is robust:", amounts.median())
print("capped:", amounts.clip(upper=high).tolist())
Q1=510.0, Q3=640.0, IQR=130.0, fences=(315.0, 835.0)
outliers: [95000]
mean with / without: 11048.9 555.0
median is robust: 560.0
capped: [420, 480, 510, 530, 560, 600, 640, 700, 835]
Common Mistakes
- Dropping every row with any missing value and losing most of the data.
- Filling missing values with the mean when the column has extreme outliers (the median is safer).
- Using astype(float) on dirty text and crashing, instead of pd.to_numeric(..., errors="coerce").
- Not standardising text, so "Pune", "pune " and "PUNE" become three different cities.
- Silently deleting outliers that are real — investigate before removing.
Exercise
Clean the DataFrame in three steps: remove the surrounding spaces from the names and give them a capital first letter; drop the duplicate rows; and fill the missing age with the median of the remaining ages. Then print the number of rows, the names and the ages.
Expected output: 3, ['Asha', 'Ravi', 'Zoya'], [25.0, 30.0, 27.5] (one per line)
import pandas as pd
df = pd.DataFrame({
"name": [" asha ", "Ravi", "Ravi", "Zoya"],
"age": [25, 30, 30, None],
})
# TODO: strip the names and convert them to title case
# TODO: drop duplicate rows
# TODO: fill the missing age with the median age
print(len(df))
print(df["name"].tolist())
print(df["age"].tolist())
Show hints
- String methods are reached through
.str:df["name"].str.strip().str.title(). df.drop_duplicates()returns a new DataFrame, anddf["age"].fillna(df["age"].median())a new column; assign both back.
Show solution
import pandas as pd
df = pd.DataFrame({
"name": [" asha ", "Ravi", "Ravi", "Zoya"],
"age": [25, 30, 30, None],
})
df["name"] = df["name"].str.strip().str.title()
df = df.drop_duplicates()
df["age"] = df["age"].fillna(df["age"].median())
print(len(df))
print(df["name"].tolist())
print(df["age"].tolist())
Quick quiz
1. Which call counts the missing values in each column?
- A. df.count()
- B. df.isna().sum()
- C. df.missing()
- D. df.null()
Show answer
B. isna() marks each missing cell as True, and sum() counts the Trues in each column.
2. What does df.dropna() do by default?
- A. Removes columns with missing values
- B. Fills missing values with 0
- C. Removes every row that has at least one missing value
- D. Removes duplicates
Show answer
C. This can discard a great deal of data, so check how many rows would be lost first.
3. A numeric column was read as text because one cell contains "N/A". How do you convert it?
- A. df["col"].astype(str)
- B. It cannot be converted
- C. df["col"].round()
- D. pd.to_numeric(df["col"], errors="coerce")
Show answer
D. errors="coerce" turns values that cannot be converted into NaN.
Interview questions
How do you handle missing values?
Show answer
First find out how many there are and why they are missing. Then choose: drop the rows or columns if few are affected and they are not important; fill with a fixed value, the median or the mean for numeric data, or the most frequent value for categories; or fill from neighbouring rows in a time series. The choice depends on the meaning of the data, and whatever is done should be recorded, because it affects the results.
How would you detect outliers in a numeric column?
Show answer
A common rule uses the interquartile range. Compute the first and third quartiles, Q1 and Q3, and the IQR as Q3 − Q1; values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR are flagged. A box plot shows the same thing visually. An outlier is not automatically an error: it should be investigated before deciding whether to correct it, remove it or keep it.
Key Points to Remember
- Start with dtypes, isna().sum(), duplicated() and describe().
- Handle missing data deliberately: dropna(subset=...), fillna, ffill/bfill.
- to_numeric and to_datetime with errors="coerce" convert dirty columns safely.
- Standardise text with .str methods and replace.
- Use the IQR rule and domain rules to find outliers; remove, cap or keep them consciously.
Practice in the playground
Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.
Run the examples
Change an input, predict the result, then compare it with the output. Explain why the result changes.
These examples need a project environment on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.
Written by
WebNest Studio Engineering Team
Practical lessons from developers working with Java, Python, React, databases and production systems.
Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering