Data Cleaning in Python — Handling Missing Values & Removing Duplicates

The Dirty Data Problem 🧹गंदे Data की समस्या 🧹脏数据问题 🧹

Real datasets are rarely clean. Values are missing, rows are repeated, the same city is spelled five ways, and some numbers are impossible. Surveys of data scientists say a large share of their time — often quoted as 40–80% — goes into finding and fixing these problems before any model is trained.

असली datasets शायद ही कभी साफ़ होते हैं। मान ग़ायब होते हैं, rows दोहराई जाती हैं, एक ही city पाँच तरह से लिखी जाती है, और कुछ संख्याएँ असंभव होती हैं। Data scientists के surveys कहते हैं कि उनके समय का बड़ा हिस्सा — अक्सर 40–80% बताया जाता है — किसी model को train करने से पहले इन समस्याओं को ढूँढने और ठीक करने में जाता है।

真实数据集很少是干净的：有缺失值、重复行，同一个城市有五种写法，还有些数字根本不可能。对数据科学家的调查显示，他们很大一部分时间 —— 常见说法是 40–80% —— 花在训练模型之前发现并修复这些问题上。

This tutorial uses one messy file, customers_raw.csv (Section 8 builds it). Its problems were planted on purpose, and we kept the complete version, so you can see which methods truly work.

यह tutorial एक बिखरी file, customers_raw.csv (Section 8 इसे बनाता है), इस्तेमाल करता है। इसकी समस्याएँ जानबूझकर डाली गईं, और हमने पूरा version रखा है, ताकि आप देख सकें कि कौन से तरीके सच में काम करते हैं।

本教程使用一个杂乱的文件 customers_raw.csv（第 8 节生成）。其中的问题是故意埋入的，我们还保留了完整版本，所以你能看到哪些方法真正有效。

Step 1 — Audit Before You Clean 🔍क़दम 1 — सफ़ाई से पहले Audit 🔍第 1 步 —— 先审查再清洗 🔍

Before you change a single value, measure the damage: how many values are missing in each column, how many rows are repeated, and how many spellings each label has.

एक भी मान बदलने से पहले नुक़सान मापें: हर column में कितने मान ग़ायब हैं, कितनी rows दोहराई गई हैं, और हर label की कितनी वर्तनियाँ हैं।

改动任何值之前，先量化问题：每列缺多少值、有多少重复行、每个标签有几种写法。

# 1) Audit before you clean
import pandas as pd, numpy as np
df_raw = pd.read_csv("customers_raw.csv", parse_dates=["order_date"])
df = df_raw.copy()                                   # never edit the raw data
print(df.shape)

null_report = pd.DataFrame({
    "missing": df.isnull().sum(),
    "pct": (df.isnull().mean() * 100).round(1),
    "dtype": df.dtypes.astype(str),
}).query("missing > 0").sort_values("pct", ascending=False)
print(null_report)

print(f"\nExact duplicate rows : {df.duplicated().sum()}")
print(f"Duplicate customer_id: {df['customer_id'].duplicated().sum()}")
print(f"City spellings       : {df['city'].nunique()}  (6 real cities)")

Why Is the Data Missing? 🤔Data ग़ायब क्यों है? 🤔数据为什么缺失？🤔

The reason a value is missing decides which fix is safe. Statisticians (Rubin, 1976) name three cases:

कोई मान क्यों ग़ायब है, यही तय करता है कि कौन सा सुधार सुरक्षित है। Statisticians (Rubin, 1976) तीन स्थितियाँ बताते हैं:

值为什么缺失，决定了哪种修复是安全的。统计学家（Rubin，1976）把它分成三种情况：

# 2) Why is it missing? Compare rows with and without a value
for col in ["age", "monthly_income", "rating"]:
    miss = df[col].isnull()
    print(f"{col:<15} missing {miss.mean():5.1%} | "
          f"median purchase: missing ₹{df.loc[miss, 'purchase_amount'].median():>6,.0f}  "
          f"present ₹{df.loc[~miss, 'purchase_amount'].median():>6,.0f} | "
          f"mean delivery_days: {df.loc[miss, 'delivery_days'].mean():.1f} vs {df.loc[~miss, 'delivery_days'].mean():.1f}")

Handling Missing Values — Strategy by Strategy 🛠️ग़ायब मान संभालना — एक-एक रणनीति 🛠️处理缺失值 —— 逐个策略 🛠️

There is no single right method. The choice depends on the mechanism, how much is missing, the column type, and whether the column is your target. Use the helper below, then read the strategies.

कोई एक सही तरीका नहीं है। चुनाव mechanism, कितना ग़ायब है, column के प्रकार, और column target है या नहीं, इस पर निर्भर है। नीचे का helper इस्तेमाल करें, फिर रणनीतियाँ पढ़ें।

没有唯一正确的方法。选择取决于缺失机制、缺失比例、列的类型，以及该列是否为目标变量。先用下面的助手，再读各个策略。

Strategy 1 — Drop (dropna)रणनीति 1 — हटाएँ (dropna)策略 1 —— 删除（dropna）

# 3) Strategy 1 — drop (dropna)
print(f"dropna() on every column : {len(df.dropna()):,} of {len(df):,} rows left")   # 'notes' empties almost everything
df = df.drop(columns=df.columns[df.isnull().mean() > 0.5])     # drop columns more than 50% empty
print("columns dropped          :", sorted(set(df_raw.columns) - set(df.columns)))
cc = df.dropna(subset=["monthly_income"])
print(f"median income, complete rows only: ₹{cc['monthly_income'].median():.1f}k  (true value: ₹{pd.read_csv('truth.csv')['monthly_income'].median():.1f}k)")

Strategy 2 — Fill With a Statistic (fillna)रणनीति 2 — किसी Statistic से भरें (fillna)策略 2 —— 用统计量填充（fillna）

Median for numbers (it ignores extreme values), mode for categories, and a group-wise median when the gaps depend on another column (MAR). Always save a missing-flag first — the fact that a value was missing can itself be useful to a model.

संख्याओं के लिए median (यह अति मानों को नज़रअंदाज़ करता है), categories के लिए mode, और जब कमियाँ किसी दूसरे column पर निर्भर हों (MAR) तो group-wise median। हमेशा पहले missing-flag save करें — मान का ग़ायब होना ख़ुद model के काम आ सकता है।

数值用中位数（不受极端值影响），类别用众数；当缺失取决于另一列（MAR）时用分组中位数。务必先保存缺失标记 —— 值曾经缺失这件事本身对模型就可能有用。

# 4) Strategy 2 — fill with a statistic (fillna). Assign the result back:
#    df["age"].fillna(..., inplace=True) does NOTHING in pandas 3 (Copy-on-Write).
df["age_missing"] = df["age"].isnull().astype(int)                # keep the fact that it was missing
before = df["age"].describe()
df["age"] = df["age"].fillna(df["age"].median())
df["city"] = df["city"].fillna(df["city"].mode()[0])
df["is_returned"] = df["is_returned"].fillna(False).astype(bool)
df["rating"] = df.groupby("delivery_days")["rating"].transform(lambda s: s.fillna(s.median()))   # group-wise
print(f"age median used: {df['age'].median():.0f}")
print(f"age std  before {before['std']:.2f} → after {df['age'].std():.2f}")
print(f"rows with age exactly {df['age'].median():.0f}: before {int((df_raw['age'] == df['age'].median()).sum())}, after {int((df['age'] == df['age'].median()).sum())}")
print(df.isnull().sum()[lambda s: s > 0])

Strategy 3 — Time Series: Interpolate (and Think About Cycles)रणनीति 3 — Time Series: Interpolate (और चक्रों के बारे में सोचें)策略 3 —— 时间序列：插值（并考虑周期）

# 5) Strategy 3 — time series: interpolate, or borrow the same hour from the day before
ts = pd.read_csv("temps.csv", parse_dates=["timestamp"]).set_index("timestamp")["temperature"]
truth = pd.read_csv("temps_truth.csv", parse_dates=["timestamp"]).set_index("timestamp")["temperature"]
gap = ts.isnull()
fills = {
    "mean-fill":    ts.fillna(ts.mean()),
    "ffill":        ts.ffill(),
    "linear":       ts.interpolate(method="time"),
    "same hour −1d": ts.fillna(ts.shift(24)),            # uses yesterday's pattern
}
for name, s in fills.items():
    err = (s[gap] - truth[gap]).abs().mean()
    print(f"{name:<14} mean abs error in the gap: {err:4.2f} °C   gap peak: {s[gap].max():.1f} °C")
print(f"{'true values':<14} gap peak: {truth[gap].max():.1f} °C")

Strategy 4 — KNN and Model-Based Imputationरणनीति 4 — KNN और Model-आधारित Imputation策略 4 —— KNN 与基于模型的填补

KNNImputer fills a gap with the average of the k most similar rows. IterativeImputer predicts each column from the others in turns. Two rules: scale first (KNN uses distances, so income in thousands would swamp delivery days), and never include ID columns.

KNNImputer कमी को k सबसे मिलती-जुलती rows के औसत से भरता है। IterativeImputer बारी-बारी हर column का दूसरों से अनुमान लगाता है। दो नियम: पहले scale करें (KNN दूरी इस्तेमाल करता है, इसलिए हज़ारों में income delivery दिनों को दबा देगी), और ID columns कभी शामिल न करें।

KNNImputer 用 k 个最相似行的平均值来填补。IterativeImputer 轮流用其他列预测每一列。两条规则：先标准化（KNN 依赖距离，以千为单位的收入会压倒配送天数），并且绝不把 ID 列放进去。

# 6) Strategy 4 — KNN imputation. Scale first: KNN uses distances.
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import StandardScaler
from sklearn.impute import KNNImputer

cols = ["age", "monthly_income", "purchase_amount", "delivery_days"]        # NOT customer_id
true_inc = pd.read_csv("truth.csv").set_index("customer_id")["monthly_income"]
miss = df["monthly_income"].isnull()
knn = make_pipeline(StandardScaler(), KNNImputer(n_neighbors=5))
filled = pd.DataFrame(knn.fit_transform(df[cols]), columns=cols, index=df.index)
filled = filled * knn[0].scale_ + knn[0].mean_                              # back to original units
t = true_inc.loc[df.loc[miss, "customer_id"]].to_numpy()
for name, est in [("median fill", np.full(miss.sum(), df["monthly_income"].median())),
                  ("KNN (scaled)", filled.loc[miss, "monthly_income"].to_numpy())]:
    print(f"{name:<13} mean abs error ₹{np.abs(est - t).mean():5.1f}k   mean of filled ₹{est.mean():5.1f}k   true mean ₹{t.mean():5.1f}k")

from sklearn.experimental import enable_iterative_imputer   # still required in scikit-learn 1.9
from sklearn.impute import IterativeImputer
imp = make_pipeline(StandardScaler(), IterativeImputer(max_iter=10, random_state=42))
# In a real project: fit on the TRAINING rows only, then transform validation/test rows.

Removing Duplicates 👯Duplicates हटाना 👯去除重复 👯

A duplicate repeats information that is already in the table. It may be an exact copy, a repeated key (the same customer with two versions of their record), or a fuzzy match (the same thing spelled differently). Duplicates inflate counts and totals, and if copies land in both training and test sets they make a model look better than it is.

Duplicate वह जानकारी दोहराता है जो table में पहले से है। यह हूबहू copy, दोहराई key (एक ही ग्राहक के record के दो versions), या fuzzy मेल (एक ही चीज़ अलग वर्तनी में) हो सकता है। Duplicates गिनती और जोड़ बढ़ाते हैं, और अगर copies training और test दोनों में पहुँच जाएँ तो model असल से बेहतर दिखता है।

重复行重复了表里已有的信息。它可能是完全副本、重复的键（同一顾客有两个版本的记录），或模糊匹配（同一事物的不同写法）。重复会抬高计数和总额；若副本同时进入训练集和测试集，模型会显得比实际更好。

# 7) Duplicates: look first, then decide
print("exact duplicates   :", df.duplicated().sum())
print(df[df.duplicated(keep=False)].sort_values("customer_id").head(4)[["customer_id", "order_date", "purchase_amount"]])
df = df.drop_duplicates()
key = df["customer_id"].duplicated(keep=False)
print("\nsame customer_id, different values:", int(key.sum()), "rows")
df = (df.sort_values("order_date")                          # oldest → newest
        .drop_duplicates(subset="customer_id", keep="last")  # keep the newest record
        .sort_index())
# Fuzzy spellings: normalise text, then map known aliases
c = df["city"].str.strip().str.lower().str.replace(r"[^a-z ]", "", regex=True)
aliases = {"mum": "mumbai", "bombay": "mumbai", "blr": "bengaluru", "bangalore": "bengaluru", "new delhi": "delhi"}
df["city"] = c.replace(aliases).str.title()
print("rows now:", len(df), "| cities:", sorted(df["city"].unique()))

Step 3 — Validate the Clean Data ✅क़दम 3 — साफ़ Data जाँचें ✅第 3 步 —— 验证清洗后的数据 ✅

After cleaning, check in code that the problems are gone and that no new ones appeared. Make each check fail loudly. Python's assert is fine in a notebook, but it is switched off when Python runs with -O, so pipelines should raise an error themselves.

सफ़ाई के बाद code में जाँचें कि समस्याएँ चली गईं और कोई नई नहीं आई। हर जाँच को ज़ोर से fail होने दें। Notebook में Python का assert ठीक है, पर Python -O के साथ चलने पर यह बंद हो जाता है, इसलिए pipelines को ख़ुद error raise करनी चाहिए।

清洗之后，用代码检查问题确实消失了，且没有引入新问题。每个检查都要明确报错。在笔记本里用 Python 的 assert 没问题，但用 -O 运行时它会被关闭，所以数据管道应自己抛出错误。

# 8) Validate — fail loudly if a rule is broken
df = df.drop(columns=["monthly_income"])   # MNAR and 12% missing: no safe fill here, so drop it (or ask the business)

def check(cond, msg):
    if not cond:
        raise ValueError(msg)              # 'assert' is skipped when Python runs with -O
check(df.isnull().sum().sum() == 0, "missing values left")
check(df.duplicated().sum() == 0 and df["customer_id"].is_unique, "duplicates left")
check(df["age"].between(18, 100).all(), "age out of range")
check(df["purchase_amount"].ge(0).all(), "negative purchase")
check(df["rating"].between(1, 5).all(), "rating outside 1–5")

report = {"rows_before": len(df_raw), "rows_after": len(df), "rows_removed": len(df_raw) - len(df),
          "nulls_remaining": int(df.isnull().sum().sum()), "dupes_remaining": int(df.duplicated().sum())}
print(pd.Series(report))

A Reusable Cleaning Function 🧰दोबारा इस्तेमाल लायक़ Cleaning Function 🧰可复用的清洗函数 🧰

This function does the routine steps in a safe order: duplicates first (so they do not distort the medians), then mostly-empty columns, then fills with missing-flags. It never fills the ID column, and it handles pandas 3's str dtype.

यह function नियमित क़दम सुरक्षित क्रम में करता है: पहले duplicates (ताकि वे medians न बिगाड़ें), फिर ज़्यादातर ख़ाली columns, फिर missing-flags के साथ fills। यह ID column कभी नहीं भरता, और pandas 3 का str dtype संभालता है।

这个函数按安全的顺序完成常规步骤：先去重（以免扭曲中位数），再删几乎全空的列，然后带缺失标记进行填充。它从不填充 ID 列，并能处理 pandas 3 的 str 类型。

# 9) A reusable cleaning function (a starting point — always review its choices)
def clean_dataframe(df, id_col=None, date_col=None, max_missing=0.5, verbose=True):
    out = df.copy()
    report = {"rows_start": len(out), "cols_start": out.shape[1]}
    # 1. Exact duplicates first, so they do not distort the medians below
    out = out.drop_duplicates()
    if id_col and date_col:                                  # keep the newest record per id
        out = out.sort_values(date_col).drop_duplicates(subset=id_col, keep="last").sort_index()
    # 2. Drop empty rows, and columns that are mostly empty
    out = out.dropna(how="all")
    out = out.loc[:, out.isnull().mean() <= max_missing]
    report["cols_dropped"] = report["cols_start"] - out.shape[1]
    # 3. Missing-indicator + median for numbers (never fill the id)
    for col in out.select_dtypes(include="number").columns.drop(id_col, errors="ignore"):
        if out[col].isnull().any():
            out[col + "_missing"] = out[col].isnull().astype(int)
            out[col] = out[col].fillna(out[col].median())
    # 4. Mode for text — 'string' is needed for pandas 3's str dtype
    for col in out.select_dtypes(include=["object", "string", "category"]).columns:
        if out[col].isnull().any():
            out[col] = out[col].fillna(out[col].mode()[0])
    report.update(rows_end=len(out), nulls_remaining=int(out.isnull().sum().sum()), dupes_remaining=int(out.duplicated().sum()))
    if verbose:
        print(f"{report['rows_start']:,} rows → {report['rows_end']:,} | columns dropped: {report['cols_dropped']} | "
              f"nulls left: {report['nulls_remaining']} | dupes left: {report['dupes_remaining']}")
    return out, report

df_clean, rep = clean_dataframe(df_raw, id_col="customer_id", date_col="order_date")
print("new indicator columns:", [c for c in df_clean if c.endswith("_missing")])

Make the Messy Practice Data 🐍बिखरा Practice Data बनाएँ 🐍生成杂乱的练习数据 🐍

Run this first. It builds the complete data, saves it as truth.csv, then plants the problems: MCAR gaps in age, MNAR gaps in income, MAR gaps in rating, messy city names, 150 exact copies and 120 updated records. It also makes the temperature series.

इसे पहले चलाएँ। यह पूरा data बनाता है, उसे truth.csv के रूप में save करता है, फिर समस्याएँ डालता है: age में MCAR कमियाँ, income में MNAR, rating में MAR, बिखरे city नाम, 150 हूबहू copies और 120 updated records। यह तापमान series भी बनाता है।

请先运行它。它生成完整数据并保存为 truth.csv，然后埋入问题：age 的 MCAR 缺失、income 的 MNAR 缺失、rating 的 MAR 缺失、混乱的城市名、150 个完全副本和 120 条更新记录。它还生成温度序列。

# 0) Build a messy practice dataset with KNOWN problems (so we can check our cleaning)
import numpy as np, pandas as pd
rng = np.random.default_rng(7)
n = 5000
age = np.clip(np.round(18 + rng.gamma(4, 4.5, n)), 18, 75)
income = np.round(np.exp(3.2 + 0.012 * age + rng.normal(0, 0.45, n)), 1)          # ₹ thousand / month
cities = rng.choice(["Mumbai", "Delhi", "Bengaluru", "Chennai", "Pune", "Kolkata"], n, p=[.26, .22, .2, .12, .11, .09])
delivery = rng.integers(1, 11, n)
df = pd.DataFrame({
    "customer_id": np.arange(10001, 10001 + n),
    "order_date": pd.Timestamp("2024-01-01") + pd.to_timedelta(rng.integers(0, 366, n), unit="D"),
    "age": age, "city": cities, "monthly_income": income,
    "purchase_amount": np.round(np.exp(5.6 + 0.9 * np.log(income) + rng.normal(0, 0.5, n)), -1),
    "delivery_days": delivery,
    "rating": np.clip(np.round(5.3 - 0.3 * delivery + rng.normal(0, 0.7, n)), 1, 5),
    "is_returned": rng.random(n) < 0.08,
    "notes": np.where(rng.random(n) < 0.12, "gift wrap", None),
})
df.to_csv("truth.csv", index=False)                 # the complete data — we only keep it to check our work

# --- Plant missing values with three different mechanisms ---
df["is_returned"] = df["is_returned"].astype(object)
df.loc[rng.random(n) < 0.12, "age"] = np.nan                                    # MCAR: random form error
rank = df["monthly_income"].rank(pct=True)
df.loc[rng.random(n) < np.where(rank > 0.8, 0.45, 0.03), "monthly_income"] = np.nan   # MNAR: top earners skip it
df.loc[rng.random(n) < 0.02 + 0.025 * (df["delivery_days"] - 1), "rating"] = np.nan    # MAR: slow orders rarely rated
df.loc[rng.random(n) < 0.04, "city"] = np.nan
df.loc[rng.random(n) < 0.03, "is_returned"] = np.nan

# --- Messy spellings of the same city ---
variants = {"Mumbai": ["mumbai", " Mumbai ", "Mum.", "Bombay"], "Bengaluru": ["Bangalore", "BLR", "bengaluru "], "Delhi": ["delhi", "New Delhi"]}
for city, vs in variants.items():
    idx = df.index[(df["city"] == city) & (rng.random(n) < 0.15)]
    df.loc[idx, "city"] = rng.choice(vs, len(idx))

# --- Duplicates: a batch loaded twice, and customers whose record was updated later ---
exact = df.sample(150, random_state=1)
updated = df.sample(120, random_state=2).copy()
updated["order_date"] += pd.to_timedelta(rng.integers(5, 40, 120), unit="D")
updated["rating"] = np.clip(updated["rating"].fillna(3) + rng.choice([-1, 1], 120), 1, 5)
messy = pd.concat([df, exact, updated]).sample(frac=1, random_state=3).reset_index(drop=True)
messy.to_csv("customers_raw.csv", index=False)

# --- Hourly temperature for one July week, with a 6-hour sensor outage ---
t = pd.date_range("2024-07-08", periods=7 * 24, freq="h")
temp = np.asarray(30 + 4.5 * np.sin(2 * np.pi * (t.hour - 9) / 24) + rng.normal(0, 0.35, len(t)) + np.linspace(0, 1.5, len(t)))
pd.DataFrame({"timestamp": t, "temperature": temp.round(1)}).to_csv("temps_truth.csv", index=False)
temp[(t >= "2024-07-10 11:00") & (t < "2024-07-10 17:00")] = np.nan
pd.DataFrame({"timestamp": t, "temperature": temp.round(1)}).to_csv("temps.csv", index=False)
print(f"customers_raw.csv: {len(messy):,} rows   temps.csv: {len(t)} hours (6 missing)")

Golden Rules of Data Cleaning 🏆Data Cleaning के सुनहरे नियम 🏆数据清洗黄金法则 🏆