Fixing Inconsistent Data & Outlier Detection in Python

The Hidden Cost of Inconsistent Data 🕵️Inconsistent Data की छिपी क़ीमत 🕵️不一致数据的隐性代价 🕵️

After missing values and duplicates, a quieter problem remains: inconsistency. The values are not empty, the type looks right, and no check fails — but "Mumbai", "MUMBAI", "Mum." and "Bombay" are counted as four different cities, and "05/03/2024" means different days in different systems.

Missing values और duplicates के बाद एक शांत समस्या बचती है: inconsistency। मान ख़ाली नहीं हैं, type सही लगता है, और कोई जाँच fail नहीं होती — पर "Mumbai", "MUMBAI", "Mum." और "Bombay" चार अलग cities गिने जाते हैं, और "05/03/2024" अलग systems में अलग दिन का मतलब रखता है।

处理完缺失值和重复之后，还剩一个更隐蔽的问题：不一致。值不为空，类型看起来也对，没有任何检查报错 —— 但 “Mumbai”、“MUMBAI”、“Mum.” 和 “Bombay” 被当成四个不同的城市，“05/03/2024” 在不同系统里代表不同的日子。

Six Kinds of InconsistencyInconsistency के छह प्रकार六类不一致

All examples below use one practice file, orders_raw.csv (Section 9 builds it), with 3,000 orders from six source systems and all six problems planted on purpose.

नीचे के सभी उदाहरण एक practice file, orders_raw.csv (Section 9 इसे बनाता है), इस्तेमाल करते हैं — छह source systems से 3,000 orders, और छहों समस्याएँ जानबूझकर डाली गई हैं।

下面所有示例都使用一个练习文件 orders_raw.csv（第 9 节生成）：来自六个源系统的 3,000 个订单，六类问题都是故意埋入的。

# 1) Diagnose: inconsistencies never raise an error, so you must look for them
import pandas as pd, numpy as np
raw = pd.read_csv("orders_raw.csv")
df = raw.copy()
print("city   unique:", df["city"].nunique(), " (7 real cities + 1 neighbour)")
print("gender unique:", df["gender"].nunique(), "→", sorted(df["gender"].dropna().unique()))
print("order_date dtype:", df["order_date"].dtype, "| e.g.", df.groupby("source")["order_date"].first().tolist())
print("rating dtype:", df["rating"].dtype, "| real NaN:", df["rating"].isna().sum(),
      "| fake-NA text left:", sorted(set(df["rating"].dropna()) - {"1.0", "2.0", "3.0", "4.0", "5.0"}))
print("currencies:", df["currency"].value_counts().to_dict(), "| units:", df["unit"].value_counts().to_dict())

Fixing Text: Case, Spaces, Aliases and Typos 🔤Text ठीक करना: Case, Spaces, Aliases और Typos 🔤修正文本：大小写、空格、别名和拼写错误 🔤

Apply the steps in a fixed order. Each step makes the next one work: if you map aliases before lowering the case, "BOMBAY" will not match the key "bombay".

क़दम एक तय क्रम में लगाएँ। हर क़दम अगले को काम करने देता है: अगर आप case छोटा करने से पहले aliases map करें, तो "BOMBAY" key "bombay" से मेल नहीं खाएगा।

按固定顺序执行各步骤，每一步都为下一步铺路：如果在转小写之前做别名映射，“BOMBAY” 就匹配不上键 “bombay”。

# 2) Normalise text in a fixed order: lower → strip → squeeze spaces → drop punctuation → aliases
city = (df["city"].str.lower()
          .str.strip()
          .str.replace(r"\s+", " ", regex=True)        # "delhi  " / "new  delhi" → single spaces
          .str.replace(r"[^a-z ]", "", regex=True))    # "mum." → "mum", "hyd." → "hyd"
print("after basic steps:", city.nunique(), "values")
aliases = {"mum": "mumbai", "bombay": "mumbai", "new delhi": "delhi", "blr": "bengaluru", "bangalore": "bengaluru",
           "madras": "chennai", "hyd": "hyderabad", "calcutta": "kolkata"}
city = city.replace(aliases)
print("after alias map  :", city.nunique(), "values")
print(city.value_counts().to_string())

Fuzzy Matching for Typos — CarefullyTypos के लिए Fuzzy Matching — सावधानी से用模糊匹配修正拼写错误 —— 要谨慎

rapidfuzz scores how similar two strings are (0–100). It fixes small typos like "Mumbay" or "Chenai". But a score is not a meaning: "Navi Mumbai" is a real, separate city that scores 71 against "mumbai", and a looser scorer such as WRatio gives it 90. Use a threshold, keep a list of protected values, and review every new match by eye.

rapidfuzz बताता है कि दो strings कितनी मिलती-जुलती हैं (0–100)। यह "Mumbay" या "Chenai" जैसे छोटे typos ठीक करता है। पर score मतलब नहीं है: "Navi Mumbai" एक असली, अलग city है जिसका "mumbai" से score 71 है, और WRatio जैसा ढीला scorer इसे 90 देता है। Threshold रखें, सुरक्षित मानों की list रखें, और हर नए मेल को आँख से जाँचें।

rapidfuzz 给两个字符串的相似度打分（0–100），能修正 “Mumbay”、“Chenai” 这类小错误。但分数不等于含义：“Navi Mumbai” 是一个真实存在的独立城市，与 “mumbai” 的得分为 71，而 WRatio 这类宽松的评分器会给出 90。要设阈值、保留受保护值列表，并人工检查每个新匹配。

# 3) Fuzzy matching for typos — with a threshold, a review list, and a protected list
from rapidfuzz import process, fuzz
canonical = ["mumbai", "delhi", "bengaluru", "chennai", "hyderabad", "pune", "kolkata"]
protected = {"navi mumbai"}                                   # real places that LOOK like typos

def fuzzy_fix(val, threshold=80):
    if val in canonical or val in protected:
        return val
    match, score, _ = process.extractOne(val, canonical, scorer=fuzz.ratio)
    return match if score >= threshold else val

for v in ["mumbay", "dehli", "chenai", "kolkatta", "banglore", "navi mumbai"]:
    m, s, _ = process.extractOne(v, canonical, scorer=fuzz.ratio)
    print(f"{v:<12} best: {m:<10} score {s:5.1f} → {fuzzy_fix(v)}")
city = city.map(fuzzy_fix).replace({"banglore": "bengaluru"})   # fuzzy missed it: add to the alias list after review
df["city"] = city.str.title()
print("final:", df["city"].nunique(), "cities →", sorted(df["city"].unique()))

Fixing Dates From Several Systems 📅कई Systems से आई Dates ठीक करना 📅修正来自多个系统的日期 📅

# 4) Dates from six systems. The usual one-liner silently loses most of them:
quick = pd.to_datetime(df["order_date"], dayfirst=True, errors="coerce")
print(f"to_datetime(dayfirst=True, errors='coerce'): {quick.isna().sum():,} of {len(df):,} became NaT")

mixed = pd.to_datetime(df["order_date"], format="mixed", dayfirst=True, errors="coerce")
truth = pd.read_csv("true_dates.csv", parse_dates=["true_date"])["true_date"]
print(f"format='mixed', dayfirst=True : {mixed.isna().sum()} NaT, but {(mixed != truth).sum()} WRONG dates (ISO days/months swapped)")

# Safe way: one explicit format per source
fmt = {"pos": "%d/%m/%Y", "web": "%Y-%m-%d", "erp": "%d-%b-%y", "manual": "%B %d, %Y", "finance": "%d.%m.%Y", "api": "%Y%m%d"}
parsed = pd.Series(pd.NaT, index=df.index, dtype="datetime64[ns]")
for s, f in fmt.items():
    rows = df["source"] == s
    parsed[rows] = pd.to_datetime(df.loc[rows, "order_date"], format=f, errors="coerce")
print(f"explicit format per source     : {parsed.isna().sum()} NaT, {(parsed != truth).sum()} wrong")
df["order_date"] = parsed
df["month"] = df["order_date"].dt.month
df["is_weekend"] = df["order_date"].dt.dayofweek >= 5

Units, Currencies, Codes and Fake NAs ⚖️इकाइयाँ, मुद्राएँ, Codes और नक़ली NA ⚖️单位、货币、编码与伪缺失值 ⚖️

These values look like valid numbers, so they pass range checks — but they are off by a fixed factor. A weight of 0.5 in kg next to 500 in grams, or $12 next to ₹1,000, quietly breaks every sum and average.

ये मान सही संख्याओं जैसे दिखते हैं, इसलिए range जाँच पार कर जाते हैं — पर एक तय गुणक से ग़लत होते हैं। Grams में 500 के बगल में kg में 0.5, या ₹1,000 के बगल में $12, हर जोड़ और औसत को चुपचाप बिगाड़ देता है।

这些值看起来是有效数字，能通过范围检查 —— 但相差一个固定倍数。以千克记的 0.5 挨着以克记的 500，或 $12 挨着 ₹1,000，会悄悄破坏每个求和与平均值。

# 5) Fake NAs, yes/no codes, currencies, units, gender
df = df.copy()
df["rating"] = pd.to_numeric(df["rating"].replace(["N/A", "NA", "-", "none", "unknown"], np.nan))
# (better: pd.read_csv("orders_raw.csv", na_values=["N/A", "NA", "-", "none", "unknown"]))
yes_no = {"yes": True, "y": True, "1": True, "true": True, "no": False, "n": False, "0": False, "false": False}
df["is_returned"] = df["is_returned"].astype(str).str.strip().str.lower().map(yes_no)
usd = df["currency"] == "USD"
df.loc[usd, "amount"] = (df.loc[usd, "amount"] * 83.5).round(2)    # use the rate for each order's date in real work
df["currency"] = "INR"
kg = df["unit"].str.lower() == "kg"
df.loc[kg, "weight"] = df.loc[kg, "weight"] * 1000
df["unit"] = "g"
g = df["gender"].str.strip().str.lower().map({"m": "Male", "male": "Male", "f": "Female", "female": "Female", "other": "Other"})
df["gender"] = g.fillna("Unknown")
print("rating:", df["rating"].dtype, "missing", df["rating"].isna().sum(), "| is_returned:", df["is_returned"].value_counts().to_dict())
print("gender:", df["gender"].value_counts().to_dict())
print(f"median amount ₹{df['amount'].median():,.0f} | median weight {df['weight'].median():,.0f} g")

Outliers — Error or Important Fact? 🎯Outliers — ग़लती या ज़रूरी तथ्य? 🎯异常值 —— 错误还是重要事实？🎯

An outlier is a value far from most of the data. "Far" depends on the shape of the data and on the domain. Before choosing a method, ask the key question: is this a mistake or a real extreme?

Outlier वह मान है जो ज़्यादातर data से दूर हो। "दूर" data के आकार और domain पर निर्भर है। तरीका चुनने से पहले मुख्य सवाल पूछें: क्या यह ग़लती है या असली अति मान?

异常值是远离大部分数据的值。“远”取决于数据的形状和所属领域。选择方法之前，先问关键问题：这是错误还是真实的极端值？

Outlier Detection Methods 🔎Outlier पहचानने के तरीके 🔎异常值检测方法 🔎

Method 1 — IQR, Then Lookतरीका 1 — IQR, फिर देखें方法 1 —— IQR，然后查看

# 6) IQR fences — then LOOK at what was flagged
x = df["amount"]
q1, q3 = x.quantile([0.25, 0.75]); iqr = q3 - q1
lo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr
df["out_iqr"] = (x < lo) | (x > hi)
print(f"fences: ₹{lo:,.0f} to ₹{hi:,.0f} | flagged {df['out_iqr'].sum()} rows ({df['out_iqr'].mean():.1%})")
print(df[df["out_iqr"]].nlargest(5, "amount")[["order_id", "city", "amount"]].to_string(index=False))
print("age errors:", df.loc[~df["age"].between(18, 100), "age"].value_counts().to_dict())

Method 2 — z-Score and Modified z-Scoreतरीका 2 — z-Score और Modified z-Score方法 2 —— z 分数与修正 z 分数

# 7) z-score vs modified z-score (median + MAD)
from scipy import stats
z = np.abs(stats.zscore(x, nan_policy="omit"))          # same length as df — safe to assign
df["out_z"] = z > 3
med = x.median(); mad = (x - med).abs().median()
df["out_modz"] = (0.6745 * (x - med) / mad).abs() > 3.5
print(f"z > 3 : {df['out_z'].sum():3d} rows   (mean ₹{x.mean():,.0f}, std ₹{x.std():,.0f} — inflated by the extremes)")
print(f"mod-z : {df['out_modz'].sum():3d} rows   (median ₹{med:,.0f}, MAD ₹{mad:,.0f})")
x_rest = x.drop(x.nlargest(4).index)                    # set the 4 biggest values aside
print(f"z > 3 on the other rows: {(np.abs(stats.zscore(x_rest)) > 3).sum()} rows  ← the 4 giants inflated std and hid these (masking)")

Method 3 — Isolation Forest (Several Columns)तरीका 3 — Isolation Forest (कई Columns)方法 3 —— 孤立森林（多列）

# 8) Isolation Forest — several columns at once
from sklearn.ensemble import IsolationForest
feats = ["age", "amount", "weight", "rating"]
ok = df[feats].notna().all(axis=1)                      # IsolationForest cannot take NaN
X = df.loc[ok, feats].assign(amount=np.log1p(df.loc[ok, "amount"]))
iso = IsolationForest(n_estimators=200, contamination=0.01, random_state=42)
df["out_iso"] = False
df.loc[ok, "out_iso"] = iso.fit_predict(X) == -1        # align by index, not by position
df.loc[ok, "anomaly_score"] = iso.score_samples(X)
print("IsolationForest flagged:", int(df["out_iso"].sum()), "rows (contamination=1% sets this number)")
print(df[df["out_iso"]].nsmallest(5, "anomaly_score")[["order_id", "age", "amount", "weight", "rating"]].to_string(index=False))

Treating Outliers 🛠️Outliers का इलाज 🛠️处理异常值 🛠️

# 9) Treatments: remove errors, cap, log, Box-Cox, robust scaling
from scipy.stats import boxcox
from sklearn.preprocessing import RobustScaler, StandardScaler
err = ~df["age"].between(18, 100) | df["order_id"].isin([12, 223, 1334])   # confirmed errors only
df = df[~err].copy()
print(f"removed {err.sum()} confirmed errors → {len(df):,} rows")
a = df["amount"]
p01, p99 = a.quantile([0.01, 0.99])
print(f"skew raw {a.skew():6.2f} | clipped 1–99% {a.clip(p01, p99).skew():5.2f} | log1p {np.log1p(a).skew():5.2f}", end="")
bc, lam = boxcox(a + 1)
print(f" | Box-Cox {pd.Series(bc).skew():5.2f} (λ = {lam:.3f})")
typical = a.between(a.quantile(0.25), a.quantile(0.75))
for name, sc in [("StandardScaler", StandardScaler()), ("RobustScaler", RobustScaler())]:
    v = sc.fit_transform(df[["amount"]]).ravel()
    print(f"{name:<14} middle 50% of orders span {v[typical].min():6.2f} to {v[typical].max():5.2f}   largest order → {v.max():8.1f}")

Reusable Helpers 🧰दोबारा इस्तेमाल लायक़ Helpers 🧰可复用的辅助函数 🧰

These helpers do the routine part — normalise text and flag outliers with a report. They deliberately do not cap or delete anything: that decision needs the inspection steps above. Note that the IQR rule flags 8 people aged 65–80 here; they are real customers, not errors.

ये helpers नियमित हिस्सा करते हैं — text normalise करना और report के साथ outliers चिह्नित करना। ये जानबूझकर कुछ cap या delete नहीं करते: उस फ़ैसले के लिए ऊपर के जाँच वाले क़दम ज़रूरी हैं। ध्यान दें कि IQR नियम यहाँ 65–80 साल के 8 लोगों को चिह्नित करता है; वे असली ग्राहक हैं, ग़लतियाँ नहीं।

这些辅助函数完成常规工作 —— 规范化文本，并在报告中标记异常值。它们故意不做截断或删除：那需要上面的检查步骤来决定。注意，这里 IQR 规则标记了 8 位 65–80 岁的顾客；他们是真实顾客，不是错误。

# 10) Reusable helpers — they FLAG and report; you decide what to change
def normalise_text(df, cols, aliases=None):
    out = df.copy()
    for c in cols:
        s = (out[c].astype("string").str.lower().str.strip()
               .str.replace(r"\s+", " ", regex=True).str.replace(r"[^\w ]", "", regex=True))
        out[c] = s.replace((aliases or {}).get(c, {}))
    return out

def flag_outliers(df, cols, k=1.5):
    out, rows = df.copy(), []
    for c in cols:
        q1, q3 = out[c].quantile([0.25, 0.75]); lo, hi = q1 - k * (q3 - q1), q3 + k * (q3 - q1)
        out[c + "_outlier"] = ~out[c].between(lo, hi) & out[c].notna()
        rows.append({"column": c, "low": round(lo, 1), "high": round(hi, 1), "flagged": int(out[c + "_outlier"].sum())})
    return out, pd.DataFrame(rows).set_index("column")

test = normalise_text(raw, ["city"], {"city": {"bombay": "mumbai", "mum": "mumbai"}})
print("city values:", raw["city"].nunique(), "→", test["city"].nunique())
_, rep = flag_outliers(df, ["amount", "age", "weight"])
print(rep)

Make the Practice Data 🐍Practice Data बनाएँ 🐍生成练习数据 🐍

Run this first. It plants all the problems used above: 35 city spellings (and the real city Navi Mumbai), nine gender codes, six date formats from six systems, USD next to INR, kg next to g, eight yes/no codes, fake "NA" strings, six ages of 999, three amounts with two extra zeros, and one genuine ₹42 lakh order.

इसे पहले चलाएँ। यह ऊपर इस्तेमाल हुई सभी समस्याएँ डालता है: 35 city वर्तनियाँ (और असली city Navi Mumbai), नौ gender codes, छह systems से छह date formats, INR के बगल में USD, g के बगल में kg, आठ yes/no codes, नक़ली "NA" strings, 999 वाली छह उम्रें, दो अतिरिक्त शून्य वाली तीन राशियाँ, और एक असली ₹42 लाख का order।

请先运行它。它埋入了上面用到的所有问题：35 种城市写法（以及真实城市 Navi Mumbai）、9 种性别编码、六个系统的六种日期格式、INR 中夹着 USD、g 中夹着 kg、8 种是/否编码、伪 “NA” 字符串、6 个 999 岁的年龄、3 个多了两个零的金额，以及一笔真实的 ₹420 万订单。

# 0) Build a practice file with planted inconsistencies and outliers
import numpy as np, pandas as pd
rng = np.random.default_rng(21)
n = 3000
city = rng.choice(["Mumbai", "Delhi", "Bengaluru", "Chennai", "Hyderabad", "Pune", "Kolkata"], n, p=[.25, .2, .17, .12, .1, .09, .07])
dirty = {"Mumbai": ["mumbai", "MUMBAI", " Mumbai ", "Mum.", "mum", "Bombay", "Mumbay"],
         "Delhi": ["delhi", "DELHI", "New Delhi", "Dehli", "Delhi  "],
         "Bengaluru": ["Bangalore", "bengaluru", "BLR", "Banglore"],
         "Chennai": ["chennai", "Madras", "Chenai"], "Hyderabad": ["hyderabad", "Hyd.", "HYD"],
         "Pune": ["pune", "PUNE "], "Kolkata": ["Calcutta", "kolkata", "Kolkatta"]}
city = np.array([rng.choice(dirty[c]) if rng.random() < 0.35 else c for c in city], dtype=object)
city[rng.random(n) < 0.01] = "Navi Mumbai"                       # a REAL, different city
gender = rng.choice(["Male", "Female", "male", "female", "M", "F", "MALE", "f", "Other", "N/A"], n,
                    p=[.3, .3, .08, .08, .07, .07, .03, .03, .02, .02])

# Dates from six systems, each with its own format
src = rng.choice(["pos", "web", "erp", "manual", "finance", "api"], n, p=[.3, .3, .15, .1, .1, .05])
true_date = pd.Timestamp("2024-01-01") + pd.to_timedelta(rng.integers(0, 366, n), unit="D")
fmt = {"pos": "%d/%m/%Y", "web": "%Y-%m-%d", "erp": "%d-%b-%y", "manual": "%B %d, %Y", "finance": "%d.%m.%Y", "api": "%Y%m%d"}
order_date = [d.strftime(fmt[s]) for d, s in zip(true_date, src)]

amount_inr = np.round(np.exp(rng.normal(8.0, 0.8, n)), -1)       # skewed spend, ₹
amount_inr[[11, 222, 1333]] *= 100                               # typing errors: two extra zeros
amount_inr[2500] = 4_200_000                                      # one genuine enterprise order
currency = np.where(rng.random(n) < 0.08, "USD", "INR")
amount = np.where(currency == "USD", np.round(amount_inr / 83.5, 2), amount_inr)
age = np.clip(np.round(rng.normal(36, 10, n)), 18, 80)
age[rng.choice(n, 6, replace=False)] = 999                        # impossible ages
weight = np.round(rng.gamma(2, 600, n))                           # grams
unit = np.where(rng.random(n) < 0.2, "kg", "g")
weight = np.where(unit == "kg", np.round(weight / 1000, 2), weight)
ret = rng.random(n) < 0.1
returned = [rng.choice(["yes", "Y", "1", "True"]) if r else rng.choice(["no", "N", "0", "False"]) for r in ret]
rating = np.clip(np.round(rng.normal(4, 0.8, n)), 1, 5).astype(object)
fake = rng.random(n) < 0.06
rating[fake] = rng.choice(["N/A", "-", "none", "NA", "unknown"], fake.sum())

pd.DataFrame({"order_id": np.arange(1, n + 1), "source": src, "order_date": order_date, "city": city, "gender": gender,
              "age": age, "currency": currency, "amount": amount, "weight": weight, "unit": unit,
              "is_returned": returned, "rating": rating}).to_csv("orders_raw.csv", index=False)
pd.DataFrame({"order_id": np.arange(1, n + 1), "true_date": true_date}).to_csv("true_dates.csv", index=False)
print(f"orders_raw.csv: {n:,} rows")

Golden Rules — Inconsistency and Outliers 🏆सुनहरे नियम — Inconsistency और Outliers 🏆黄金法则 —— 不一致与异常值 🏆