Data cleaning and feature engineering

In short

Data cleaning is the set of operations that turn raw, messy records into a consistent, analysable table — fixing missing values, duplicates, wrong types, inconsistent categories and outliers. Feature engineering is the step that follows: converting cleaned columns into numeric predictors a model can learn from, by encoding, scaling, transforming and combining them. Together they consume 60-80% of a real data science project's effort and usually decide model accuracy more than the choice of algorithm. Indian university syllabi examine this under Data Science / Machine Learning labs and the "data preprocessing" unit; GATE DA (Data Science and AI, introduced 2024) tests imputation, standardisation, outliers and the curse of dimensionality. Placement rounds at TCS, Infosys, Wipro, Accenture, Mu Sigma, Fractal, ZS Associates, Flipkart, Swiggy and bank analytics teams almost always include a pandas cleaning task and a question on data leakage.

Explained

What "dirty data" looks like and the quality dimensions

• Dimensions of data quality: accuracy, completeness, consistency, timeliness, believability, interpretability, uniqueness. Exam answers are usually framed on these.
• Types of defects: missing values (blank, NaN, NULL, sentinel codes like -1, 999, "NA", "N/A"); duplicate rows; inconsistent representation ("Mumbai", "mumbai", "BOM", "Bombay"); wrong data type (numeric stored as text because of commas or ₹ signs); structural errors (merged header rows, trailing spaces); outliers and impossible values (age 250, salary ₹0); mixed units (₹ lakh vs ₹; kg vs g); encoding problems (mojibake from non-UTF-8 files).
• Tidy data rules (Wickham): one variable per column, one observation per row, one observational unit per table. Wide-to-long reshaping (melt) is part of cleaning.
• Garbage in, garbage out: a model cannot recover information destroyed by bad cleaning, and a leak introduced during cleaning inflates validation scores silently.

Handling missing values

• Missingness mechanisms (examined):
  • MCAR (missing completely at random) — missingness independent of all data; deletion is unbiased.
  • MAR (missing at random) — missingness depends on *observed* variables; model-based imputation works.
  • MNAR (missing not at random) — depends on the unobserved value itself (high earners refuse to state income); needs domain modelling plus a missing-indicator flag.
• Deletion: *listwise* (drop the row), *pairwise*, or drop the column if (say) >40-50% missing. Simple, but loses data and biases results unless MCAR.
• Single imputation: mean (numeric, symmetric), median (numeric, skewed — the default for Indian income/price data), mode (categorical), constant ("Unknown", 0), forward/backward fill (ffill, bfill) and interpolation for time series.
• Model-based imputation: k-NN imputation (average of k nearest complete rows, needs scaling first); iterative / MICE (IterativeImputer — regress each column on the others, round-robin); regression imputation.
• Add an indicator column (add_indicator=True) when missingness itself is informative — e.g. "PAN not supplied" in a loan application.
• Limitation: mean/median imputation shrinks variance and weakens correlations; it understates standard errors. Multiple imputation (several imputed datasets, pooled estimates) fixes this but is rarely used in placement tasks.
• Rule: compute the imputation statistic on the training set only and apply it to validation/test.

Duplicates, types and consistency

1. Exact duplicates: df.duplicated(subset=key_cols) → drop_duplicates(keep='first'). Decide the business key first (order_id? customer+timestamp?).
2. Fuzzy / near duplicates: same entity typed differently. Standardise (lowercase, strip, remove punctuation) then match with edit distance (Levenshtein), Jaro-Winkler, or phonetic keys (Soundex, Metaphone) — "record linkage" or "entity resolution". Blocking on a coarse key (PIN code, first letter) keeps the pairwise comparison tractable.
3. Type fixing: strip ₹, commas and "Lakh"; pd.to_numeric(..., errors='coerce'); pd.to_datetime(..., dayfirst=True) because Indian data is DD-MM-YYYY while the pandas default reads MM-DD-YYYY.
4. Categorical standardisation: case folding, whitespace trim, mapping tables for state/city names, validating against masters — PIN code (6 digits), GSTIN (15 characters: 2-digit state code + 10-character PAN + entity code + "Z" + checksum), Aadhaar (12 digits, Verhoeff check digit), IFSC (4 letters + "0" + 6 alphanumerics), mobile (10 digits starting 6-9).
5. Constraint checks: range (0 ≤ age ≤ 120), cross-field (date_of_discharge ≥ date_of_admission), uniqueness, referential integrity against a dimension table.
6. Privacy note: personal data cleaning in India is governed by the Digital Personal Data Protection Act, 2023; its rules were notified in 2025 with phased implementation, so obligations such as consent notices and breach reporting are being rolled out in stages. Practical effect: de-identify or mask Aadhaar/PAN/mobile before analysis, keep purpose limitation in mind, and do not copy production PII into notebooks. (Dates of each phase have been revised; check the current notification.)

Outliers

• Definition: an observation far from the bulk of the data. It may be an *error* (fix or drop) or a *genuine extreme* (keep — fraud and churn models live on these).
• Univariate detection:
  • IQR / Tukey: outside Q1 − 1.5·IQR to Q3 + 1.5·IQR (mild) or 3·IQR (extreme). Distribution-free; the boxplot method.
  • Z-score: |z| > 3. Assumes near-normality; the mean and SD are themselves dragged by outliers.
  • Robust z-score: 0.6745·(x − median)/MAD, flag |value| > 3.5.
  • Percentile capping / winsorising: clip at the 1st and 99th percentile.
• Multivariate detection: Mahalanobis distance; Isolation Forest; Local Outlier Factor; DBSCAN noise points; one-class SVM.
• Treatments: remove, cap (winsorise), transform (log), bin into ranges, impute as missing, or use a robust model (tree ensembles, Huber loss, quantile regression) and leave them alone.

Feature engineering: transformations and encodings

• Scaling (needed for distance- and gradient-based learners: k-NN, k-means, SVM, PCA, logistic/linear regression with regularisation, neural nets; *not* needed for decision trees, random forest, gradient boosting):
  • Standardisation (StandardScaler): z = (x − μ)/σ → mean 0, SD 1.
  • Min-max normalisation (MinMaxScaler): to [0, 1]; sensitive to outliers.
  • Robust scaling (RobustScaler): (x − median)/IQR; outlier-tolerant.
  • MaxAbs, unit-norm (row-wise L2) for sparse text vectors.
• Distribution shaping: log or log1p for right-skewed money/count data; square root for counts; Box-Cox (positive data) and Yeo-Johnson (allows zero/negative) power transforms; quantile transform to a uniform or normal target.
• Categorical encoding:
  • One-hot (nominal, low cardinality); drop one column to avoid the dummy-variable trap in linear models.
  • Ordinal / label encoding — only for genuinely ordered categories (Low < Medium < High) or tree models; label-encoding a nominal variable fakes an order.
  • Target / mean encoding with smoothing and out-of-fold computation; powerful for high-cardinality keys (PIN code, merchant ID) but leaks badly if done naively.
  • Frequency / count encoding, binary and hashing encoding for very high cardinality, leave-one-out, WoE (weight of evidence — standard in Indian credit-scoring teams).
  • Rare-category grouping into "Other"; handle unseen test categories (handle_unknown='ignore').
• Discretisation (binning): equal-width, equal-frequency (quantile), k-means bins, or domain cut-points (age bands, income slabs). Adds robustness, loses resolution.
• Derived features: ratios (debt-to-income, EMI/income), differences, aggregates by group (customer's mean order value), date-time parts (hour, weekday, month, is_month_end, days-since-last-purchase, festival flag for Diwali demand), interaction and polynomial terms, text features (length, token counts, TF-IDF), geo features (distance, PIN-code-level cluster), cyclical encoding via sin/cos for hour and month.
• Time-series features: lags, rolling mean/SD, expanding means, differences — computed only from the past to avoid look-ahead bias.
• Imbalanced targets: class weights, random under/over-sampling, SMOTE (synthetic minority oversampling) — apply inside the training fold only, never before the split.

Feature selection, dimensionality and the pipeline

• Why select: curse of dimensionality, overfitting, multicollinearity, cost of data collection, interpretability.
• Filter methods (model-free): variance threshold, correlation with target, chi-square (categorical), ANOVA F-test, mutual information; drop one of each pair with |r| > 0.8-0.9, or features with VIF > 5-10.
• Wrapper methods: forward selection, backward elimination, RFE — accurate, expensive.
• Embedded methods: L1 (Lasso) drives coefficients to zero; tree-based importance; permutation importance (more trustworthy than impurity importance).
• Extraction vs selection: PCA, LDA, t-SNE/UMAP create new combined features (losing interpretability); selection keeps original columns.
• Data leakage — the single most asked interview point:
  • *Target leakage:* a feature that encodes the outcome (e.g. "settlement_amount" for predicting default).
  • *Train-test contamination:* fitting a scaler, imputer, encoder, PCA or feature selector on the whole dataset before splitting.
  • *Temporal leakage:* random splitting of time-ordered data; use a chronological split or TimeSeriesSplit.
  • *Duplicate leakage:* the same record in train and test.
  • Cure: split first → wrap every transformer in a Pipeline/ColumnTransformer → fit on train, transform on test → cross-validate the whole pipeline.
• Reproducibility: fix random_state, version the data, log the cleaning steps, keep raw data immutable and write cleaned output to a new file.

Key points

• Data cleaning - detecting and correcting inaccurate, incomplete, inconsistent or duplicate records before analysis.
• Feature engineering - creating, transforming and selecting input variables so a model can learn the signal.
• MCAR / MAR / MNAR - missingness at random / explainable by observed data / dependent on the hidden value itself; decides whether deletion is safe.
• Median over mean - median imputation is safer for skewed data (income, price, claim amount); mode for categorical.
• Missing indicator - an extra 0/1 column recording that a value was absent, when absence itself carries information.
• IQR rule - outliers lie outside Q1 − 1.5·IQR and Q3 + 1.5·IQR; distribution-free.
• Z-score rule - |z| > 3 flags outliers but assumes normality and is itself distorted by outliers; MAD-based robust z is better.
• Standardisation vs normalisation - z-score to mean 0/SD 1 vs min-max rescaling to [0, 1]; robust scaling uses median and IQR.
• Trees need no scaling - decision trees and ensembles split on order, so monotone rescaling changes nothing.
• One-hot vs label encoding - one-hot for nominal, ordinal/label only for ordered categories or tree models.
• Dummy-variable trap - keeping all k dummies plus an intercept makes the design matrix singular; drop one.
• High cardinality - use target (out-of-fold, smoothed), frequency, hashing or WoE encoding instead of thousands of dummies.
• Log/Box-Cox transform - compresses right skew and stabilises variance; log1p handles zeros.
• Binning - converts a continuous variable into ordered ranges; robust to outliers, loses information.
• Data leakage - any information from the test set or the future reaching training; the most common cause of a model that scores 0.98 offline and fails in production.
• Fit on train, transform on test - every statistic (mean, SD, min, max, category list, target mean) comes from the training fold only.
• Pipeline + ColumnTransformer - the reproducible way to apply different cleaning to numeric and categorical columns and still cross-validate honestly.
• SMOTE after splitting - resample only the training fold; oversampling before the split duplicates rows into the test set.
• DPDP Act, 2023 - India's personal data law; mask or de-identify Aadhaar/PAN/mobile and respect purpose limitation when cleaning customer data (rules notified 2025, phased).

Formulas and examples

Example 1 — IQR outlier detection on monthly salaries (₹ thousand). Data (n = 11, sorted): 18, 22, 25, 28, 30, 33, 36, 40, 45, 52, 180.

• Median (6th value) = 33. Lower half (5 values): 18, 22, 25, 28, 30 → Q1 = 25. Upper half: 36, 40, 45, 52, 180 → Q3 = 45.
• IQR = 45 − 25 = 20. Fences: 25 − 1.5(20) = −5; 45 + 1.5(20) = 75.
• Only 180 exceeds 75 → ₹1,80,000 is the lone outlier. Extreme fence = 45 + 3(20) = 105, so it is an *extreme* outlier too.
• Compare treatments: mean of all 11 = 509/11 = 46.3; median = 33. After removing 180: mean = 329/10 = 32.9. One point moved the mean by ₹13,400 and the median not at all — which is why median imputation is preferred here.

Example 2 — scaling the same column (train statistics only). Train = the 10 values without 180: mean = 32.9; Σ(x − x̄)² = 222.01 + 118.81 + 62.41 + 24.01 + 8.41 + 0.01 + 9.61 + 50.41 + 146.41 + 364.81 = 1006.90 → population variance = 100.69, σ = 10.03.

• Standardise x = 52 → z = (52 − 32.9)/10.03 = +1.90.
• Min-max with x_min = 18, x_max = 52 → x′ = (52 − 18)/34 = 1.00; for x = 33 → 15/34 = 0.441.
• Robust scaling (median 31.5 for these 10 values; Q1 = 25, Q3 = 40, IQR = 15) → x = 52 gives (52 − 31.5)/15 = 1.37.
• Now the test row 180 arrives: z = (180 − 32.9)/10.03 = +14.7, min-max = (180 − 18)/34 = 4.76 (outside [0, 1] — expected, and correct behaviour; it must *not* trigger a refit).

Example 3 — smoothed target encoding of city for a loan-default model. Global default rate ȳ = 0.12; smoothing m = 10.

• Nagpur's raw 40% is based on 5 rows; smoothing pulls it to 21.3%, close to the global rate. Mumbai, with 500 rows, barely moves. This must be computed out-of-fold, else each row's own target contributes to its encoding — classic leakage.

Example 4 — missing-value decision table for a customer dataset of 10,000 rows.

• After dropping only second_phone, all 10,000 rows survive; listwise deletion on the five columns would have removed at least 8,700 rows (87%).

Example 5 — pandas / scikit-learn pipeline that avoids leakage.

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.pipeline import Pipeline
from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.preprocessing import StandardScaler, OneHotEncoder
from sklearn.linear_model import LogisticRegression

df = pd.read_csv("loans.csv")
df["income"] = (df["income"].astype(str)
                  .str.replace(r"[₹,]", "", regex=True)
                  .pipe(pd.to_numeric, errors="coerce"))
df["app_date"] = pd.to_datetime(df["app_date"], dayfirst=True, errors="coerce")
df["city"] = df["city"].str.strip().str.title()
df = df.drop_duplicates(subset="application_id")

num = ["age", "income", "emi"]
cat = ["city", "employment_type"]
X_tr, X_te, y_tr, y_te = train_test_split(
    df[num + cat], df["default"], test_size=0.2,
    stratify=df["default"], random_state=42)      # split FIRST

pre = ColumnTransformer([
    ("n", Pipeline([("imp", SimpleImputer(strategy="median", add_indicator=True)),
                    ("sc", StandardScaler())]), num),
    ("c", Pipeline([("imp", SimpleImputer(strategy="most_frequent")),
                    ("oh", OneHotEncoder(handle_unknown="ignore",
                                         drop="first"))]), cat)])

model = Pipeline([("pre", pre), ("clf", LogisticRegression(max_iter=1000))])
model.fit(X_tr, y_tr)        # all statistics learned from train only
print(model.score(X_te, y_te))

Where it is used

• IT services and analytics (TCS, Infosys, Wipro, Accenture, Cognizant, Mu Sigma, Fractal, ZS Associates): data engineer and analyst roles whose daily work is cleaning client ERP/CRM extracts and building feature tables for dashboards and models.
• Banking, NBFC and fintech (HDFC, ICICI, SBI, Bajaj Finserv, Paytm, PhonePe, CRED, CIBIL/credit bureaus): credit scorecards built on WoE-encoded, binned features; KYC de-duplication across PAN, Aadhaar and mobile; UPI fraud features (velocity, time-of-day, device change).
• E-commerce, quick commerce and ride-hailing (Flipkart, Amazon India, Myntra, Swiggy, Zomato, Zepto, Ola, Uber): catalogue de-duplication and attribute normalisation, address and PIN-code cleaning, demand features for festival and weather effects.
• Healthcare and insurance (Apollo, Practo, HDFC Ergo, Star Health, ABDM/ABHA records): claims cleaning, ICD code standardisation, missing lab values, de-identification before analysis.
• Government and public data (MoSPI, NFHS, Census, data.gov.in, Open Government Data platform, Smart City dashboards): survey non-response weighting, state and district name harmonisation, unit and year reconciliation.
• Job roles: data analyst, data engineer, ML engineer, data scientist, BI developer, risk/credit analyst, MIS executive; the skill is tested directly in take-home pandas assignments and Kaggle-style hiring tests.

Interview questions

• Q: When would you impute with the median rather than the mean? A: When the column is skewed or has outliers — income, transaction value, claim size — because the mean is pulled towards the extremes while the median is not; mode is used for categorical columns.
• Q: What is data leakage and how do you prevent it? A: Any information from the test set or the future entering training — including fitting a scaler, imputer or encoder on the full dataset. Prevent it by splitting first and wrapping all transformations in a pipeline fitted only on the training fold.
• Q: Difference between standardisation and normalisation? A: Standardisation subtracts the mean and divides by the SD (mean 0, SD 1, unbounded); min-max normalisation rescales linearly to a fixed range such as [0, 1] and is far more sensitive to outliers.
• Q: Do random forests need feature scaling? A: No. Tree splits depend only on the order of values, so any monotone rescaling gives the same tree; scaling matters for distance-, gradient- and regularisation-based models.
• Q: Why is label encoding wrong for a nominal column in linear regression? A: It assigns arbitrary integers, so the model infers an order and equal spacing (Delhi = 3 is "three times" Mumbai = 1) that does not exist; one-hot encoding avoids this.
• Q: How do you handle a categorical feature with 50,000 levels, such as merchant ID? A: Out-of-fold smoothed target encoding, frequency/count encoding, feature hashing, or group rare levels into "Other" — one-hot encoding would create an unusable sparse matrix.
• Q: Explain MCAR, MAR and MNAR with an example. A: MCAR — a sensor dropped readings at random; MAR — income missing more often for younger customers, which you *observe*; MNAR — high earners deliberately hide income, so missingness depends on the hidden value itself. MNAR needs a missing-indicator and domain modelling.
• Q: Should you always remove outliers? A: No. Decide whether it is an error or a genuine extreme; fraud, churn and failure models depend on extremes. Alternatives are capping, log transform, binning or using a robust model.
• Q: Where exactly should SMOTE be applied in a cross-validation loop? A: Inside each training fold only, after the split. Resampling before splitting puts synthetic copies of training rows into the validation set and inflates the score.
• Q: How do you detect and treat multicollinearity? A: Correlation matrix (|r| > 0.8) and VIF > 5-10; drop one of the pair, combine them into a ratio, or use PCA or ridge/L1 regularisation.
• Q: What is the difference between feature selection and feature extraction? A: Selection keeps a subset of the original interpretable columns (filter, wrapper, embedded methods); extraction builds new combinations such as PCA components, which usually lose interpretability.
• Q: Your model scores 0.97 AUC in validation and 0.62 in production. First thing you check? A: Leakage — a target-encoded or outcome-derived feature, preprocessing fitted before the split, duplicate rows across train and test, or a random split on time-ordered data.
• Q: How do you de-duplicate a customer master where names are typed differently? A: Normalise text, block on a coarse key such as PIN code or date of birth, score candidate pairs with Jaro-Winkler or Levenshtein plus phonetic keys, then review above a threshold; validate identifiers (PAN format, 10-digit mobile) as tie-breakers.

Common mistakes

• Imputing, scaling or encoding the whole dataset before the train-test split — the most common and most costly error.
• Using the mean on heavily skewed money columns, or imputing a categorical column with its mean-coded number.
• Label-encoding nominal categories for linear or distance-based models, creating a false order.
• Keeping all k dummy columns with an intercept (dummy-variable trap) and then wondering why coefficients are unstable.
• Deleting every row with any missing value, losing most of the dataset when a flag plus median imputation would have kept it.
• Treating every outlier as an error and deleting it, destroying exactly the signal a fraud or churn model needs.
• Parsing Indian DD-MM-YYYY dates with the pandas default, silently turning 03-11-2026 into 11 March; and forgetting errors='coerce' so stray text crashes or stays as object dtype.
• Forgetting handle_unknown='ignore', so an unseen test category makes the encoder fail at inference.
• Overwriting the raw data file in place, leaving the cleaning un-reproducible and un-auditable.

Related topics

Python for data analysis: NumPy and pandas · Python ML libraries: scikit-learn, PyTorch and TensorFlow · Data collection and web scraping · Data visualisation · SQL queries and joins · Probability and statistics for data science