Data Cleaning

Data Cleaning

1. Definition and Goal

Data cleaning (data cleansing / scrubbing) routines work to fill in missing values, smooth noisy data, identify or remove outliers, and resolve inconsistencies, thereby improving the quality of the mining data.

Clean data for a sales table means every row has a valid customer id, a non-negative amount, a standard date format and a real (non-duplicate) invoice.

2. Task 1 — Handling Missing Values

Causes: attribute not applicable, not collected, lost on entry, equipment failure, deleted for privacy, or unknown at capture time.

Method	How	Advantage	Disadvantage
Ignore the tuple	Drop rows with missing values	Simple	Loses information, may bias (if class label missing this is commonly used)
Global constant	Fill with 'unknown' / 0 / N.A.	Keeps the row	Misleading for mining algorithms
Attribute mean/median/mode	Fill with the central value	Simple, unbiased on average	Understates variance
Most probable value	Predict using Bayes, decision tree, regression, k-NN; EM algorithm for repeated estimation	Statistically strongest	Computationally expensive
Same-class mean	Fill with the mean of rows of the same class	Class aware	Needs labelled data

Mean imputation:

3. Task 2 — Noisy Data

Noise = random error or variance in a measured variable (faulty instruments, transmission errors, human slip, environment).

(a) Binning — sort the data, partition into equal size bins, then smooth each bin:

• Smoothing by bin mean — every value replaced by bin mean.
• Smoothing by bin median — every value replaced by bin median.
• Smoothing by bin boundaries — every value replaced by the nearest boundary (min or max of the bin).

Example — sorted prices: 4, 8, 15, 21, 21, 24, 25, 28, 34. Equal-frequency bins (depth 3):

Bin	Values	By mean	By boundary
Bin 1	4, 8, 15	9, 9, 9	4, 4, 15
Bin 2	21, 24, 25	23, 23, 23	21, 25, 25
Bin 3	28, 28, 34	30, 30, 30	28, 28, 34

Equal-width binning uses $\text{width} = \frac{\max-\min}{k}$; for prices above with $k=3$: width $= \frac{34-4}{3} = 10$ → bins 4-14, 14-24, 24-34.

(b) Regression — fit the attribute to a smooth function:

• Linear: $y = a + bx$ where $a = \bar y - b\bar x$, $b = \frac{\sum(x_i-\bar x)(y_i-\bar y)}{\sum(x_i-\bar x)^2}$
• Multiple linear regression: $y = w_0 + w_1x_1 + \dots + w_nx_n$ (least squares).

(c) Clustering — group similar values; points far from any cluster are outliers and are removed.

(d) Combined computer & human inspection — human experts label suspicious patterns, system detects them later.

4. Task 3 — Outlier Detection

Univariate

• Z-score rule: flag if $|z| > 3$ where $z = \dfrac{x-\bar x}{\sigma}$.
• IQR rule: flag if $x < Q_1 - 1.5\,IQR$ or $x > Q_3 + 1.5\,IQR$.
• Boxplot shows outliers as points beyond whiskers (Tukey's method).

Multivariate — outliers in $n$-dimensional space detected by distance-based or density-based methods (Mahalanobis distance, LOF, DBSCAN noise points).

Outlier rows may be removed, capped (winsorised), or analysed as a valuable class (fraud, faults).

5. Task 4 — Resolving Inconsistencies

• Different codes for the same thing — M / m / Male / 1 → standardise to Male.
• Different units / formats — kg vs lb, DD-MM-YY vs YYYY-MM-DD, ₹ vs $.
• Contradictory values across tables — detect with functional dependencies and business rules: age must equal current_date - birth_date; total = Σ line items.
• Duplicate records / entity resolution — matching keys, fuzzy matching (edit distance), merge–purge with survivorship rules.

6. Data Cleaning as a Process (Potters's Wheel / Ajax style)

1. Discrepancy detection — profile data: min/max, null ratio, distinct values, histogram, cross-table rules; detect schema-level (data type, uniqueness, referential integrity) and value-level (spelling, domain) discrepancies.
2. Data scrubbing — correct values using domain knowledge, dictionaries, mapping tables and ETL transformation rules.
3. Workflow definition — capture the corrections as repeatable transformations and update metadata so future loads are cleaned automatically.

7. Cleaning in an ETL Pipeline

 Validate -> Reject errors -> Standardize -> Deduplicate -> Merge/purge -> Load
   |            to error                                    (survivorship
   |            table                                       rules)
   +-- log rejected row id + reason code

8. Exam Points

• Explain missing value handling methods with pros/cons — 5-7 marks.
• Binning numerical: given data and depth, show smoothing by mean/median/boundary — 5 marks (very frequent numerical).
• Explain noise causes and removal techniques — 5 marks.
• Write short notes on data cleaning in ETL / discrepancy detection — 3-5 marks.