Fixing Inconsistent Data & Outlier Detection in Python

The Hidden Cost of Inconsistent Data

After missing values and duplicates are handled, a subtler and often more dangerous class of data quality problem remains: inconsistency. Inconsistent data looks valid on the surface — it passes null checks, it has the right data type, it appears in the right column — but it is quietly wrong in ways that corrupt every downstream calculation.

Fixing String Inconsistencies

String columns are the most common source of data inconsistency. Text entered by humans — through forms, CRMs, mobile apps — accumulates case variations, trailing spaces, abbreviations, and typos that pandas treats as entirely different values.

import pandas as pd
import numpy  as np

# ── Step 1: Diagnose the problem ─────────────────────
print(df['city'].value_counts().head(20))  # spot the variants
print(f"Unique values: {df['city'].nunique()}") # 847 → should be 28

# ── Step 2: Basic normalisation ──────────────────────
df['city'] = (df['city']
    .str.lower()          # "Mumbai" → "mumbai"
    .str.strip()          # " mumbai " → "mumbai"
    .str.replace(r'\s+', ' ', regex=True)  # "new  delhi" → "new delhi"
)

# ── Step 3: Abbreviation map ─────────────────────────
city_map = {
    'mum':     'mumbai',   'mum.':    'mumbai',
    'bombay':  'mumbai',   'mumbay':  'mumbai',
    'del':     'delhi',    'new del': 'delhi',
    'blr':     'bengaluru','bangalore':'bengaluru',
    'chn':     'chennai',  'madras':  'chennai',
    'hyd':     'hyderabad','hyd.':    'hyderabad'
}
df['city'] = df['city'].replace(city_map)

# ── Step 4: Validate ─────────────────────────────────
print(f"Unique values after: {df['city'].nunique()}") # now 28 ✅

Fuzzy String Matching for Harder Cases

from rapidfuzz import process, fuzz

# Find the best canonical match for each dirty value
canonical = ['mumbai', 'delhi', 'bengaluru', 'hyderabad', 'chennai']

def fuzzy_fix(val, choices, threshold=80):
    result = process.extractOne(val, choices, scorer=fuzz.ratio)
    return result[0] if result and result[1] >= threshold else val

df['city'] = df['city'].apply(lambda x: fuzzy_fix(x, canonical))

# Install: pip install rapidfuzz

Fixing Date & Time Inconsistencies

Date columns are the second most common source of inconsistency in real datasets. When data flows from multiple systems — a legacy ERP, a web form, a mobile app, and a spreadsheet — each source uses its own date format. Pandas treats every format as a separate string value until you explicitly parse and standardise them.

# ── Diagnose: spot the format variants ───────────────
print(df['order_date'].value_counts().head(10))

# ── Parse mixed formats ──────────────────────────────
df['order_date'] = pd.to_datetime(
    df['order_date'],
    dayfirst=True,      # DD/MM/YYYY → day comes first
    errors='coerce'    # unparseable → NaT (not error)
)

# ── Check how many dates failed to parse ─────────────
failed = df['order_date'].isnull().sum()
print(f"{failed} dates could not be parsed → inspect those rows")

# ── Standardise to a single string format if needed ──
df['order_date_str'] = df['order_date'].dt.strftime('%Y-%m-%d')

# ── Extract useful features from clean dates ─────────
df['year']       = df['order_date'].dt.year
df['month']      = df['order_date'].dt.month
df['day_of_week'] = df['order_date'].dt.day_name()
df['quarter']    = df['order_date'].dt.quarter
df['is_weekend']  = df['order_date'].dt.dayofweek >= 5

Fixing Units, Encodings & Type Inconsistencies

Unit and encoding inconsistencies are particularly dangerous because they produce values that look numerically valid — they pass all range checks — but are off by a constant factor. A weight column mixing grams and kilograms will make 500g look like 500kg to every downstream calculation.

# ── Fix boolean / yes-no encoding ────────────────────
yes_no_map = {
    '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.lower().str.strip()
    .map(yes_no_map))

# ── Fix "NA" strings that pandas misses ──────────────
na_strings = ['na', 'n/a', 'none', 'null', '-', '', 'nan', 'unknown']
df = df.replace(na_strings, np.nan)

# ── Fix mixed currency (USD → INR) ───────────────────
usd_mask = df['currency'] == 'USD'
df.loc[usd_mask, 'amount'] = df.loc[usd_mask, 'amount'] * 83.5
df['currency'] = 'INR'

# ── Fix mixed weight units (kg/g → g) ────────────────
kg_mask = df['unit'].str.lower() == 'kg'
df.loc[kg_mask, 'weight'] *= 1000
df['unit'] = 'g'

# ── Standardise gender column ─────────────────────────
gender_map = {
    'm':'Male',    'male':'Male',   'man':'Male',   'boy':'Male',
    'f':'Female',  'female':'Female','woman':'Female',
    'other':'Other', 'o':'Other',  'non-binary':'Other'
}
df['gender'] = (df['gender']
    .str.lower().str.strip()
    .map(gender_map)
    .fillna('Unknown'))

Outlier Detection — Understanding the Problem

An outlier is a data point that lies abnormally far from the bulk of the data. But "abnormally far" is not a fixed rule — it depends on the distribution of the data, the domain context, and what the outlier represents. Before detecting outliers, you must ask: is this a genuine extreme value, or is it a data quality error?

Outlier Detection Methods

Three statistical methods cover the vast majority of outlier detection needs in data science. Each makes different assumptions about the data's distribution and each has its correct use case.

Method 1 — IQR Method (Recommended Default)

# ── Compute IQR bounds ────────────────────────────────
Q1  = df['purchase_amount'].quantile(0.25)
Q3  = df['purchase_amount'].quantile(0.75)
IQR = Q3 - Q1

lower_fence = Q1 - 1.5 * IQR
upper_fence = Q3 + 1.5 * IQR

print(f"Lower fence : ₹{lower_fence:,.0f}")
print(f"Upper fence : ₹{upper_fence:,.0f}")

# ── Flag outliers ─────────────────────────────────────
df['is_outlier_iqr'] = (
    (df['purchase_amount'] < lower_fence) |
    (df['purchase_amount'] > upper_fence)
)
print(f"Outliers: {df['is_outlier_iqr'].sum()} rows ({df['is_outlier_iqr'].mean()*100:.1f}%)")

# ── Inspect before deciding treatment ────────────────
df[df['is_outlier_iqr']].sort_values('purchase_amount', ascending=False).head(20)

Method 2 — Z-Score Method

from scipy import stats

# ── Standard Z-score ─────────────────────────────────
z_scores = np.abs(stats.zscore(df['purchase_amount'].dropna()))
df['is_outlier_z'] = z_scores > 3

# ── Modified Z-score (robust — uses median) ──────────
median_val = df['purchase_amount'].median()
mad        = (df['purchase_amount'] - median_val).abs().median()
mod_z      = 0.6745 * (df['purchase_amount'] - median_val) / mad
df['is_outlier_modz'] = mod_z.abs() > 3.5

# Modified Z is better for skewed data — use it over standard Z

Method 3 — Isolation Forest (Multivariate)

from sklearn.ensemble import IsolationForest

# ── Select numeric features ───────────────────────────
features = df[['age', 'purchase_amount', 'rating', 'delivery_days']].dropna()

# ── Fit Isolation Forest ──────────────────────────────
iso = IsolationForest(
    contamination=0.05,   # expect ~5% of data to be anomalous
    random_state=42,
    n_estimators=200
)
df['is_outlier_iso'] = iso.fit_predict(features) == -1  # -1 = anomaly

# ── Anomaly scores (more negative = more anomalous) ──
df['anomaly_score'] = iso.score_samples(features)

print(f"Isolation Forest anomalies: {df['is_outlier_iso'].sum()}")

Outlier Treatment Strategies

Once outliers are identified and inspected, there are four treatment strategies. The choice depends entirely on whether the outlier is a data error or a legitimate extreme value, and on the requirements of the downstream model.

Treatment 1 — Remove (Errors Only)

# ONLY for confirmed data errors
df = df[
    (df['age'].between(18, 100)) &
    (df['purchase_amount'] >= 0) &
    (df['rating'].between(1, 5))
]

Treatment 2 — Cap / Winsorise

# ── Manual clip at IQR fences ────────────────────────
df['purchase_amount'] = df['purchase_amount'].clip(
    lower=lower_fence, upper=upper_fence
)

# ── Winsorise at 1st / 99th percentile ───────────────
p01 = df['purchase_amount'].quantile(0.01)
p99 = df['purchase_amount'].quantile(0.99)
df['purchase_amount'] = df['purchase_amount'].clip(p01, p99)

# ── Using scipy.stats.mstats.winsorize ───────────────
from scipy.stats.mstats import winsorize
df['purchase_winsorized'] = winsorize(
    df['purchase_amount'], limits=[0.01, 0.01]  # clip 1% from each tail
)

Treatment 3 — Log Transformation

# ── Log transformation (right-skewed data) ───────────
df['purchase_log'] = np.log1p(df['purchase_amount'])  # log(1+x) safe for 0

# ── Square root (moderate skew) ──────────────────────
df['purchase_sqrt'] = np.sqrt(df['purchase_amount'])

# ── Box-Cox (finds best transformation automatically) ─
from scipy.stats import boxcox
df['purchase_boxcox'], lambda_val = boxcox(df['purchase_amount'] + 1)
print(f"Best Box-Cox lambda: {lambda_val:.3f}")

Treatment 4 — RobustScaler (Keep Outliers, Scale Robustly)

from sklearn.preprocessing import RobustScaler, StandardScaler

# RobustScaler uses median and IQR — not affected by outliers
scaler    = RobustScaler()
df['purchase_robust'] = scaler.fit_transform(df[['purchase_amount']])

# StandardScaler — affected by outliers (uses mean and std)
std_scaler = StandardScaler()
df['purchase_std'] = std_scaler.fit_transform(df[['purchase_amount']])

# RobustScaler is the recommended default when outliers are legitimate

Complete Inconsistency & Outlier Fixing Template

def fix_inconsistencies(df, string_maps=None):
    """Fix case, whitespace, and abbreviation issues in all string columns."""
    df = df.copy()
    cat_cols = df.select_dtypes(include=['object', 'category']).columns
    for col in cat_cols:
        df[col] = df[col].str.lower().str.strip()
        if string_maps and col in string_maps:
            df[col] = df[col].replace(string_maps[col])
    return df

def detect_and_treat_outliers(df, cols, method='iqr', treatment='cap'):
    """
    Detect and treat outliers in specified numeric columns.
    method    : 'iqr' | 'zscore'
    treatment : 'cap' | 'log' | 'flag_only'
    """
    df = df.copy()
    report = {}
    for col in cols:
        series = df[col].dropna()
        if method == 'iqr':
            Q1, Q3  = series.quantile([0.25, 0.75])
            IQR     = Q3 - Q1
            lo, hi  = Q1 - 1.5 * IQR, Q3 + 1.5 * IQR
        else:  # zscore
            lo = series.mean() - 3 * series.std()
            hi = series.mean() + 3 * series.std()

        outlier_mask = (df[col] < lo) | (df[col] > hi)
        report[col]  = {'n_outliers': outlier_mask.sum(), 'lo': lo, 'hi': hi}
        df[col + '_outlier'] = outlier_mask

        if treatment == 'cap':
            df[col] = df[col].clip(lo, hi)
        elif treatment == 'log':
            df[col] = np.log1p(df[col].clip(lower=0))

    return df, report

# ── Usage ─────────────────────────────────────────────
maps = {
    'city':   {'mum':'mumbai', 'bombay':'mumbai', 'del':'delhi'},
    'gender': {'m':'male', 'f':'female', 'man':'male'}
}
df = fix_inconsistencies(df, string_maps=maps)
df, out_report = detect_and_treat_outliers(
    df, cols=['purchase_amount', 'age', 'delivery_days'],
    method='iqr', treatment='cap'
)
print(pd.DataFrame(out_report).T)

Golden Rules — Inconsistency & Outliers