🧹 Week 3 – Data Cleaning & EDA · Day 15
Day 15: Data Cleaning: Missing, Duplicates, Types
⏱ 2.5 hours📶 Intermediate🎯 100% self-study
Day 15 / 60
✅ Today you will learn
- Build a data-quality report in 5 lines
- Missing values: drop, fill with median/mode, flag
- Duplicates: find and remove
- Fix text (case, spaces, M/F) and wrong types
- Handle impossible values and mixed date formats
🏢 In the real world
The BazaarKart warehouse exported 324 orders from an old system. Cities are written as “DELHI” and “ Delhi ”, quantity says “2 pcs”, some prices are −1 and a few customers are 250 years old. Data scientists spend up to 60% of their time on exactly this – today you learn to do it quickly and safely.
Step 1: a data-quality report
Python
import pandas as pd
raw = pd.read_csv("sales_dirty.csv")
print("Shape:", raw.shape)
print("Duplicate rows:", raw.duplicated().sum())
quality = pd.DataFrame({"dtype": raw.dtypes.astype(str),
"missing": raw.isna().sum(),
"unique": raw.nunique()})
print(quality)Shape: (312, 13)
Duplicate rows: 12
dtype missing unique
order_id object 0 300
order_date object 0 219
city object 0 26
region object 0 4
category object 0 5
product object 0 25
quantity object 0 8
unit_price int64 0 270
discount float64 0 6
payment_mode object 0 8
customer_age float64 13 37
customer_gender object 0 4
rating float64 3 7
🔎 What the report already tells us
quantity is object (text) – it should be a number. city has more than 11 unique values – case/space problems. customer_age and rating have missing values, and there are 12 duplicate rows.
- 1Copy raw data
- 2Remove duplicates
- 3Fix text
- 4Fix types
- 5Fix impossible values
- 6Fill missing
- 7Validate & save
Step 2: duplicates
Python
import pandas as pd
raw = pd.read_csv("sales_dirty.csv")
df = raw.copy() # never edit the raw data
print(df[df.duplicated(keep=False)].sort_values("order_id").head(4)[["order_id", "city", "unit_price"]])
df = df.drop_duplicates()
print("After drop_duplicates:", len(df), "| repeated order_ids left:", df["order_id"].duplicated().sum()) order_id city unit_price
184 BK100133 Lucknow 618
302 BK100133 Lucknow 618
13 BK100225 Bengaluru 3122
307 BK100225 Bengaluru 3122
After drop_duplicates: 300 | repeated order_ids left: 0
Step 3: text problems
Python
import pandas as pd
df = pd.read_csv("sales_dirty.csv").drop_duplicates()
print("Before:", sorted(df["city"].unique())[:6])
df["city"] = df["city"].str.strip().str.title()
df["payment_mode"] = df["payment_mode"].str.strip().str.title().replace({"Upi": "UPI", "Cash On Delivery": "Cash on Delivery"})
df["customer_gender"] = df["customer_gender"].replace({"M": "Male", "F": "Female"})
print("After :", sorted(df["city"].unique()))
print(df["payment_mode"].value_counts().to_dict())
print(df["customer_gender"].value_counts().to_dict())Before: [' Bengaluru ', ' Jaipur ', ' Mumbai ', ' Patna ', ' Pune ', 'AHMEDABAD']
After : ['Ahmedabad', 'Bengaluru', 'Chennai', 'Delhi', 'Hyderabad', 'Jaipur', 'Kolkata', 'Lucknow', 'Mumbai', 'Patna', 'Pune']
{'UPI': 136, 'Card': 79, 'Cash on Delivery': 55, 'Net Banking': 30}
{'Male': 166, 'Female': 134}
Step 4: wrong data types
Python
import pandas as pd
df = pd.read_csv("sales_dirty.csv").drop_duplicates()
print(df["quantity"].unique())
df["quantity"] = df["quantity"].str.replace("pcs", "").str.strip().astype(int)
print(df["quantity"].dtype, df["quantity"].sum())
print(df.loc[df["order_date"].str.contains("/"), "order_date"].head(3).tolist())
iso = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
df["order_date"] = iso.fillna(dmy)
print(df["order_date"].dtype, "| unparsed:", df["order_date"].isna().sum())['2' '1' '5' '4' '3' '1 pcs' '2 pcs' '3 pcs']
int64 575
['29/11/2024', '30/05/2024', '07/08/2024']
datetime64[ns] | unparsed: 0
💡 errors="coerce"
pd.to_numeric(x, errors="coerce") and pd.to_datetime(x, errors="coerce") turn anything they cannot read into NaN/NaT instead of crashing – then you can count and inspect those rows.
Step 5: impossible values
Python
import pandas as pd
import numpy as np
df = pd.read_csv("sales_dirty.csv").drop_duplicates()
print("Negative prices:", (df["unit_price"] <= 0).sum(), "| ages > 100:", (df["customer_age"] > 100).sum())
df.loc[df["unit_price"] <= 0, "unit_price"] = np.nan
df.loc[df["customer_age"] > 100, "customer_age"] = np.nan
# a missing price can be filled from the same product's median price
df["unit_price"] = df["unit_price"].fillna(df.groupby("product")["unit_price"].transform("median"))
print("Prices still missing:", df["unit_price"].isna().sum(), "| min price:", df["unit_price"].min())Negative prices: 8 | ages > 100: 3
Prices still missing: 0 | min price: 140.0
Step 6: missing values – three strategies
🗑️
DropWhen very few rows are missing and they are random. dropna(subset=[...])🧮
FillMedian for numbers (robust to outliers), mode for categories, group median for better accuracy.🚩
FlagAdd a column like age_missing = 1 – “missing” itself can be useful information for a model.Python
import pandas as pd
df = pd.read_csv("sales_dirty.csv").drop_duplicates()
df.loc[df["customer_age"] > 100, "customer_age"] = None
print(df[["customer_age", "rating"]].isna().sum().to_dict())
df["age_missing"] = df["customer_age"].isna().astype(int) # flag
df["customer_age"] = df["customer_age"].fillna(df["customer_age"].median()) # fill
df = df.dropna(subset=["rating"]) # drop (only 3 rows)
print(df[["customer_age", "rating"]].isna().sum().to_dict(), "| rows:", len(df), "| flagged:", df["age_missing"].sum()){'customer_age': 15, 'rating': 3}
{'customer_age': 0, 'rating': 0} | rows: 297 | flagged: 15
Putting it together: one reusable function
Python
import pandas as pd
import numpy as np
def clean_sales(path):
df = pd.read_csv(path).drop_duplicates().copy()
df["city"] = df["city"].str.strip().str.title()
df["payment_mode"] = df["payment_mode"].str.strip().str.title().replace({"Upi": "UPI", "Cash On Delivery": "Cash on Delivery"})
df["customer_gender"] = df["customer_gender"].replace({"M": "Male", "F": "Female"})
df["quantity"] = pd.to_numeric(df["quantity"].str.replace("pcs", ""), errors="coerce")
d1 = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
df["order_date"] = d1.fillna(pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce"))
df.loc[df["unit_price"] <= 0, "unit_price"] = np.nan
df["unit_price"] = df["unit_price"].fillna(df.groupby("product")["unit_price"].transform("median"))
df.loc[df["customer_age"] > 100, "customer_age"] = np.nan
df["age_missing"] = df["customer_age"].isna().astype(int)
df["customer_age"] = df["customer_age"].fillna(df["customer_age"].median())
df = df.dropna(subset=["rating"])
df["revenue"] = (df["quantity"] * df["unit_price"] * (1 - df["discount"])).round(2)
return df
clean = clean_sales("sales_dirty.csv")
assert clean.duplicated().sum() == 0 and clean["unit_price"].min() > 0
assert clean["city"].nunique() == 11 and clean["customer_age"].max() <= 100
print(clean.shape, "| total revenue ₹", round(clean["revenue"].sum()))
print(clean.dtypes.astype(str).to_dict())
clean.to_csv("sales_clean.csv", index=False)(297, 15) | total revenue ₹ 963060
{'order_id': 'object', 'order_date': 'datetime64[ns]', 'city': 'object', 'region': 'object', 'category': 'object', 'product': 'object', 'quantity': 'int64', 'unit_price': 'float64', 'discount': 'float64', 'payment_mode': 'object', 'customer_age': 'float64', 'customer_gender': 'object', 'rating': 'float64', 'age_missing': 'int64', 'revenue': 'float64'}
💼 Why a function?
Next month the warehouse sends a new messy file. With clean_sales() you clean it in one line, the same way every time – and the assert lines stop you if something new is wrong.
🏅 Job skills unlocked today
✔ Write a data-quality report✔ Choose drop / fill / flag for missing data✔ Standardise text and fix data types✔ Build a reusable cleaning function with checks
Practice tasks
🏋️ Practice – do it yourself
Task 1: In
telecom_churn.csv, total_charges has missing values. Fill them with monthly_charges × tenure_months and confirm none remain.Show solution
Python
import pandas as pd
t = pd.read_csv("telecom_churn.csv")
print("Missing before:", t["total_charges"].isna().sum())
t["total_charges"] = t["total_charges"].fillna(t["monthly_charges"] * t["tenure_months"])
print("Missing after:", t["total_charges"].isna().sum())Missing before: 25
Missing after: 0
Task 2: In
sales_data.csv, fill missing ratings with the median rating of the same category.Show solution
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
s["rating"] = s["rating"].fillna(s.groupby("category")["rating"].transform("median"))
print(s["rating"].isna().sum(), s["rating"].mean().round(3))0 4.058
Task 3: Write a function
quality_report(df) that returns dtype, missing count, missing % and unique count for every column.Show solution
Python
import pandas as pd
def quality_report(df):
return pd.DataFrame({"dtype": df.dtypes.astype(str), "missing": df.isna().sum(),
"missing_%": (df.isna().mean() * 100).round(1), "unique": df.nunique()})
print(quality_report(pd.read_csv("telecom_churn.csv"))) dtype missing missing_% unique
customer_id object 0 0.0 3000
gender object 0 0.0 2
senior_citizen int64 0 0.0 2
tenure_months int64 0 0.0 72
contract object 0 0.0 3
internet_service object 625 20.8 2
tech_support object 0 0.0 3
payment_method object 0 0.0 4
monthly_charges float64 0 0.0 961
total_charges float64 25 0.8 2894
complaints int64 0 0.0 8
data_usage_gb float64 0 0.0 1112
churn object 0 0.0 2
Quick quiz
🧠 Quick Quiz – test yourself
1. Why fill numeric gaps with the median rather than the mean?
One 250-year-old customer pulls the mean but not the median.
2. df.duplicated().sum() returns…
The first occurrence is not counted (keep="first").
3. “ DELHI ” and “Delhi” become the same with…
strip removes spaces, title fixes case.
4. pd.to_numeric("2 pcs", errors="coerce") gives…
Unreadable values become NaN – clean the text first.
5. Best first line of any cleaning script?
Keep the raw data untouched so you can always start again.
Interview corner
💼 Interview Corner
How do you handle missing values?
First measure how many and whether they are random. Drop rows if very few and random; fill numbers with the median (or group median) and categories with the mode or "Unknown"; add a missing-flag column when missingness may carry information; for ML, do the filling inside a pipeline fitted only on training data.
How would you detect impossible values?
Use domain rules (price > 0, 0 < age < 100, dates within range), describe() to see min/max, value_counts() for categories, and assert statements in the cleaning code so future files are checked automatically.
Summary
- Always start with df = raw.copy() and a quality report.
- drop_duplicates, str.strip().str.title(), replace for codes like M/F.
- to_numeric / to_datetime with errors="coerce" fix types safely.
- Missing: drop, fill (median/mode/group) or flag – decide per column.
- Wrap cleaning in a function with assert checks.
➡️ Next
Day 16: dates and text – extract month, weekday and festive season, and clean text with the .str toolkit.
Frequently Asked Questions
Should I fill missing values with 0?
Only when 0 is the true meaning (e.g. no purchases). A missing age or rating is not 0 – filling 0 would distort averages.
Same order_id twice but different values – duplicate?
Not an exact duplicate. Investigate: it may be an update, a return or a data-entry error. Decide with the business which row to keep.
