Data cleaning and integration
Practical and exam-focused notes on data cleaning (handling missing, noisy, inconsistent, duplicate data) and data integration (schema matching, record linkage, conflict resolution), with definitions, formulas, worked examples and common mistakes.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
Data used in engineering systems is often incomplete, wrong or scattered across many places. Data cleaning and data integration are the processes an engineer uses before analysis, machine learning or storing in a warehouse so results are correct, models learn true patterns, and ETL pipelines (see ETL and the metadata repository) run reliably.
Key ideas
MCAR (Missing Completely At Random): probability of missing does not depend on data. Useful because simple deletion is unbiased.
MAR (Missing At Random): missingness depends on observed attributes (not the missing value itself). Requires conditional methods like regression or EM for unbiased imputations.
MNAR (Missing Not At Random): missingness depends on the missing value. Hardest to handle; needs domain assumptions.
Noise (random errors) and outliers (extreme values): noise is small random perturbation; outliers are points that do not follow the general distribution and may be errors or rare real events. Distinguish by domain knowledge before deleting.
Duplicate records / record linkage / entity resolution: tasks to detect when two or more records represent the same real-world entity. Key techniques: standardisation, blocking (to limit comparisons), similarity functions (edit distance, Jaccard, numeric difference), and clustering or classification of candidate pairs.
Schema integration: combining multiple data sources that use different schemas into a single global schema. Steps: attribute name matching, type matching, unit conversion, mapping to global schema. Watch for semantic heterogeneity: same name different meaning, different name same meaning.
Data fusion / conflict resolution: when several sources give different values for the same attribute. Strategies: choose by source trustworthiness, most recent, majority vote, or merge values. Keep provenance (source information) for audit.
Standardisation and normalization: before comparing strings or numbers, standardize formats (dates, phone numbers, units). For numbers use the normalisation methods covered in Normalisation and distance measures.
Algorithms and pipelines: typical steps in a cleaning+integration pipeline: data acquisition → standardization → missing-value handling → noise smoothing and outlier detection → duplicate detection → schema matching → data fusion → load into target (ETL). Draw a pipeline diagram: boxes left-to-right: raw sources → cleaning → integration → consolidated store; label arrows with transformations (standardize, impute, match, fuse).
Statistical methods: mean/mode/median imputation, k-NN imputation, regression imputation, hot-deck, Expectation-Maximisation (EM) for missing data, multiple imputation (e.g., MICE) and use of missingness indicators (add a binary flag) for predictive models, binning and smoothing for noise, IQR and z-score for outliers. For entity resolution: blocking, sorted-neighbourhood, pairwise comparison, clustering, and probabilistic record linkage (Fellegi–Sunter model).
Assumptions: every method has assumptions — mean imputation assumes MCAR or low bias risk (but reduces variance); regression imputation assumes linear relationship and MAR; IQR makes no normality assumption and is robust to skew and outliers; z-score assumes approximately normal data. State assumptions when recommending methods.
Formulas
μ = (1/n) · Σ_{i=1..n} x_i — mean of numeric attribute; μ is unit of x, SI unit as per attribute; applies to quantitative data.
σ² = (1/n) · Σ_{i=1..n} (x_i − μ)² — population variance; σ is standard deviation (unit of x); use n−1 for sample variance if estimating from sample.
z_i = (x_i − μ) / σ — z-score (unitless); measures how many standard deviations x_i is from mean; applies when distribution is near-normal.
Q1, Q3 — first and third quartiles (medians of lower and upper halves of sorted data); IQR = Q3 − Q1 — interquartile range (unit of x).
Lower fence = Q1 − 1.5·IQR and Upper fence = Q3 + 1.5·IQR — typical IQR outlier decision rules; unit of x.
(Levenshtein distance DP recurrence): d(i,0)=i, d(0,j)=j, d(i,j)=min( d(i−1,j)+1, d(i,j−1)+1, d(i−1,j−1)+cost ) where cost=0 if char_i==char_j else 1; distance is integer; applies to string similarity.
(Simple linear regression for imputation) ŷ = a + b·x, where b = Σ(x_i−x̄)(y_i−ȳ) / Σ(x_i−x̄)² and a = ȳ − b·x̄; units of y are same as ŷ; apply when you have paired observed attributes and MAR holds.
(EM outline for missing data) E-step: compute expected sufficient statistics given current parameters; M-step: maximize expected log-likelihood to update parameters. (No single-line closed form — depends on model.)
Worked examples
Example 1 — Mean imputation effect (numerical, step-by-step) Data (single attribute X): 10, 12, NA, 14, 16. Steps:
- Compute mean of observed values:
μ = (10+12+14+16)/4 = 52/4 = 13. - Impute NA with 13. New data: 10, 12, 13, 14, 16.
- Original mean ignoring NA was 13 (same by design). Compute population variance after imputation:
σ² = (1/5)[(10−13)²+(12−13)²+(13−13)²+(14−13)²+(16−13)²] = (1/5)[9+1+0+1+9] = 20/5 = 4, soσ = 2(units of X). Note: if we had used only observed values, variance would beσ²_obs = (1/4)[(10−13)²+(12−13)²+(14−13)²+(16−13)²] = 20/4 = 5,σ_obs = √5 ≈ 2.236. Imputation reduced variance — a known bias of single mean imputation.
Final answer: mean-imputed dataset values are 10,12,13,14,16, with population standard deviation = 2. (Bold your final imputed dataset and the main observation: mean imputation preserves mean but underestimates variance.)
Example 2 — Outlier detection: IQR vs z-score (numerical) Data: 10, 12, 13, 14, 15, 100. Steps:
- Sort: 10,12,13,14,15,100. Median = (13+14)/2 = 13.5.
- Q1 = median of lower half [10,12,13] = 12. Q3 = median of upper half [14,15,100] = 15. IQR = 3.
- Lower fence = 12 − 1.5·3 = 7.5. Upper fence = 15 + 1.5·3 = 19.5. 100 > 19.5 → 100 flagged as an outlier by IQR.
- Mean μ = 164/6 ≈ 27.333. Population σ ≈ 32.53. z(100) = (100−27.333)/32.53 ≈ 2.23 → not flagged by |z|>3 criterion. Explanation: mean and σ were inflated by the extreme value, so z-score misses it; IQR is robust and detects it.
Final answer: 100 is an outlier under IQR, but not under a 3σ z-score rule — choose method based on robustness and domain.
Example 3 — Record linkage by blocking and edit distance (string example) Two records sets (Name, ZIP): A1: "John Doe", 560001 A2: "J. Patel", 380001 B1: "Jon Doe", 560001 B2: "Jatin Patel", 380001 Steps:
- Blocking on ZIP: pairs only within same ZIP: {A1,B1} and {A2,B2}.
- Compute Levenshtein distances for names: d("John Doe","Jon Doe") = 1 (remove 'h') → high similarity. d("J. Patel","Jatin Patel") ≈ need token-based or punctuation handling: after removing punctuation, compare "J Patel" vs "Jatin Patel" → consider token similarity or edit distance; suppose normalized similarity passes threshold.
- If similarity ≥ threshold (e.g., edit distance ≤2 for short names) → mark as duplicates; else non-match.
- Resolve: merge attributes, keep both sources as provenance.
Final: pairs A1-B1 and A2-B2 match after simple blocking and normalization.
Common mistakes
- Using z-score outlier detection on skewed data — z-rule assumes near-normality.
- Comparing all record pairs without blocking — leads to O(n^2) comparisons and timeouts on large data.
- Forgetting to standardize units (kg vs g) before integrating numeric attributes.
- Treating outliers always as errors — some are real and important (anomaly detection vs cleaning distinction).
- Not keeping provenance when fusing conflicting values — makes audits impossible.
For your exam
Question types you will see: definitions (MCAR/MAR/MNAR, blocking, Levenshtein), short notes (compare imputation methods, outlier detection methods), derivations (show IQR fences, show least-squares formula for regression imputation), numericals (compute mean/variance after imputation, detect outliers), algorithm traces (blocking + pairwise compare, edit distance DP table), and diagrams (ETL pipeline with cleaning stage or schema integration mapping).
What full-marks answers include:
- Definitions with bold terms and short examples.
- Clear statement of assumptions (e.g., MCAR) before choosing a method.
- Step-by-step calculations with formulas written, numbers substituted, and SI units (if numeric data has units).
- For algorithms: show intermediate steps (e.g., blocking keys, candidate pairs, DP table for Levenshtein).
- State pros/cons and when methods fail; mention provenance and data audit requirements.
Quick check
- What does MCAR mean and why does it matter when you delete records with missing values?
- Give the formula for the IQR-based upper fence and explain one reason why IQR is preferred to z-score for skewed data.
- Name two blocking strategies used in record linkage and a similarity function for strings.
Answers: 1) Missing Completely At Random — missingness unrelated to any data; deletion is unbiased (MCAR). 2) Upper fence = Q3 + 1.5·IQR; IQR is robust to extreme values so skew/outliers do not inflate spread. 3) Blocking by ZIP or sorted-neighbourhood; similarity: Levenshtein (edit) distance or Jaccard token overlap.
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?