PadhaikoAll subjects

Cleaning, Wrangling, Enrichment, Validation & Publishing

Foundation of Data Science

Open in the Padhaiko app

3.4 Data Cleaning

Real-world observational data is rarely ready for analytical consumption upon initial ingestion. Sensor drops, human typing mistakes, network packet timeouts, and schema merges introduce dirty, corrupt, or inconsistent artifacts.

Definition

Data Cleaning Imperatives and Error Types

Data Cleaning (also known as data cleansing) is the systematic process of identifying, filtering, correcting, or removing corrupt, incomplete, duplicate, irrelevant, or erroneously formatted observations from a dataset.

Data cleaning is essential because unmitigated data flaws propagate directly into machine learning pipelines, triggering four key remediation tasks:

  1. Removing unwanted and duplicate observations: Discarding redundant records, irrelevant features, and duplicate rows that artificially inflate sample weighting.
  2. Fixing structural and typographical errors: Correcting mismatched naming conventions, erratic string capitalization, and syntax errors.
  3. Detecting and managing outliers: Identifying extreme recording artifacts or transmission glitches that distort mean and variance estimates.
  4. Handling missing values: Resolving null, blank, or NaN cells through statistical imputation or controlled case deletion.

Handling Missing Data

Missing data arises when an attribute value is unobserved or unrecorded. Statisticians classify missingness into three formal mechanisms:

Primary Remediation Strategies

  1. Listwise Deletion (Complete-Case Analysis): Discards any record that contains one or more missing values. While unbiased under pure MCAR, it discards valuable observed information in other features and severely reduces sample size when missingness is widespread.
  2. Pairwise Deletion: Uses all available pairs of observed values when computing specific bivariate statistics (e.g., covariance between feature and ). This preserves sample size but can yield non-positive-definite covariance matrices.
  3. Statistical Imputation:
    • Mean Imputation: Replaces missing values with feature mean . Preserves sample mean but artificially contracts variance and suppresses correlation.
    • Median Imputation: Replaces missing values with sample median . Highly robust against skewed distributions and outliers.
    • Mode Imputation: Replaces missing categorical or discrete values with the most frequent category.
    • Model-Based Imputation: Imputes values using -nearest neighbors (-NN) or Multiple Imputation by Chained Equations (MICE).

Handling Duplicate and Inconsistent Data

Outlier Detection and Remediation

An outlier is an observation that deviates markedly from the overall distribution of the dataset:

  1. -Score Method: For normally distributed data, an observation is flagged as an outlier if:
  1. Interquartile Range (IQR) Method: Non-parametric and robust against skewed data. The interquartile range is . Fences are constructed:

Observations lying outside are classified as outliers.

AspectData CleaningData Preparation
Primary FocusIdentifying and resolving errors, omissions, and corruptionsTransforming clean data into formats optimized for model training
Operational ScopeNarrow: deduplication, missing value imputation, outlier pruningBroad: encompasses cleaning plus feature scaling, encoding, and selection
Core ObjectiveMaximize data quality, validity, and truthfulnessMaximize model usability, algorithm compatibility, and predictive performance
Typical TasksImputing NaNs, standardizing string casings, removing duplicatesMin-max normalization, one-hot encoding, log transformations
Example

Statistical Imputation and IQR Outlier Detection on Academic Scores

Perform mean imputation for the missing value, compute the five-number summary parameters, and test for outliers using the rule.

Compute mean of observed scores and impute missing value: The observed scores are ().

Imputing yields the complete 9-element dataset.

Order the complete dataset and identify median: Sorted in ascending order ():

The median () is the 5th value:

Compute quartiles and interquartile range ():

  • Lower half ( values strictly below median):
  • Upper half ( values strictly above median):
  • Interquartile range:

Construct fences and detect outlier:

Evaluate the score :

Because exceeds the upper fence, it is formally flagged as an outlier (likely caused by a data-entry error on an exam marked out of 100).

Answer:

The missing entry is imputed with the observed mean . Under the rule (), the upper fence is . Since , it is an anomalous outlier.

3.5 Data Wrangling and Enrichment

While data cleaning focuses on removing errors, data wrangling encompasses the end-to-end transformation of complex, disparate data into cohesive analytical tables.

Definition

The Data Wrangling Lifecycle

Data Wrangling (often called data munging) is the iterative process of transforming, restructuring, and mapping raw data from diverse sources into a unified, clean, and analytically usable format for downstream machine learning and statistical modeling.

The standard six-stage data wrangling workflow comprises:

  1. Discovering: Profiling raw data assets to understand schema, distributions, missingness patterns, and anomalies.
  2. Structuring: Reorganizing data structures, reshaping matrices, unpivoting cross-tabulations, and parsing hierarchical nested structures.
  3. Cleaning: Imputing missing values, removing duplicate records, and repairing typographical inconsistencies.
  4. Enriching: Augmenting internal records with external data sources (e.g., geospatial coordinates, demographic census tables) to add contextual predictive depth.
  5. Validating: Verifying that transformed data satisfies schema rules, range constraints, and referential integrity.
  6. Publishing: Exporting the clean, validated dataset into production storage (data warehouse, feature store) with documentation and metadata.

Data Wrangling Tools and Ecosystem

Modern data wrangling relies on specialized programming libraries and visual tools:

AspectData WranglingData Cleaning
ScopeBroad: encompasses discovery, restructuring, joining, enriching, and exportingFocused: dedicated specifically to rectifying errors, nulls, and anomalies
Workflow StageSpans the entire pipeline from raw ingestion to model-ready feature setsAn intermediate sub-phase executed inside the wrangling lifecycle
Output GoalCreates new features, combined schemas, and enriched analytical datasetsProduces error-free, deduplicated, and consistent data records
Representative ExampleMerging online sales tables with CRM logs and deriving customer lifetime metricsStripping trailing whitespaces from customer names or imputing NaN values

Data Enrichment

Definition

Data Enrichment and Dimensional Augmentation

Data Enrichment is the practice of combining first-party internal datasets with supplementary internal or external data sources to augment analytical context, improve predictive power, and uncover deeper latent relationships.

Key forms of data enrichment include:

Example

Multi-Source Relational Join and Metric Enrichment

Join the transactions with customer demographics and evaluate the mean total customer spend across the Gold tier cohort.

Perform relational equi-join on primary key customer_id: We merge Table 2 and Table 1 using an inner join on customer_id:

  • T1
  • T2
  • T3
  • T4
  • T5

Aggregate total spend per individual customer:

  • Customer C1:
  • Customer C2:
  • Customer C3:

Compute enriched metric for Gold tier segment: The Gold loyalty tier contains two customers: C1 and C3.

Enriching transactions with loyalty tiers reveals that Gold members spend on average more than the Silver member ( vs ).

Answer:

Enriching transaction logs with customer loyalty tiers links transaction totals to customer segments. The Gold tier encompasses two customers (C1 and C3) with a combined spend of , yielding a mean expenditure of per Gold customer.

3.6 Data Validation and Publishing

Data pipelines must systematically verify that cleaned and transformed data meets quality specifications before being published to downstream consumer applications.

Definition

Data Validation and Quality Verification

Data Validation is the automated or rule-based verification process that evaluates whether a dataset conforms to predefined logical schemas, statistical constraints, domain rules, and security policies before ingestion into production analytics.

Data validation ensures operational accuracy by enforcing:

Five Key Considerations Before Publishing or Sharing Datasets

Before publishing a dataset to a shared data lake, public repository, or machine learning registry, data engineers must review five governance pillars:

  1. Accuracy: Confirming that all records reflect true physical values and have undergone rigorous validation checks.
  2. Completeness: Verifying that essential feature columns contain no unhandled missing values that could cause downstream model inference to fail.
  3. Consistency: Enforcing uniform column naming conventions (e.g., lowercase snake_case), standardized measurement units, and synchronized timestamp formats across all files.
  4. Privacy, Security, and Anonymization: Redacting Personally Identifiable Information (PII) such as full names, IP addresses, and phone numbers. Applying pseudonymization, -anonymity, or differential privacy to prevent re-identification attacks.
  5. Documentation and Metadata: Publishing comprehensive data dictionaries, schema definitions, licensing terms (e.g., Creative Commons, MIT), and data provenance records (lineage detailing source origins, transformation tools, and update frequencies).

Exercises: Cleaning, Wrangling & Publishing

Question 1

Define data cleaning and explain why it is necessary. Describe concrete operational techniques to handle missing, duplicate, and inconsistent data.

Definition and necessity: Data cleaning is the process of detecting and removing errors, duplicates, inconsistencies, and missing entries in a dataset. It is necessary because raw datasets contain noise, corruptions, and missing fields that distort statistical parameters, cause optimization routines to diverge, and violate the GIGO principle.

Handling Missing Data:

  • Mean/Median/Mode Imputation: Substitute missing entries with feature central tendency statistics (mean for symmetric numeric data, median for skewed data, mode for categorical variables).
  • Listwise Deletion: Drop entire rows if missingness is rare and randomly distributed (MCAR).
  • Advanced Imputation: Use regression or -nearest neighbors to predict missing values from correlated observed features.

Handling Duplicate Data:

  • Primary Key Deduplication: Group records by unique primary keys (e.g., user_id) and keep only the latest timestamped entry.
  • Fuzzy Deduplication: Compute string similarity metrics (Levenshtein distance) to identify and merge near-identical entity names (e.g., "Tribhuvan Univ." and "Tribhuvan University").

Handling Inconsistent Data:

  • Format Canonicalization: Parse and standardize dates to ISO 8601 (YYYY-MM-DD).
  • Case Harmonization: Convert all string attributes to lowercase and strip leading/trailing whitespace.
  • Value Mapping: Standardize equivalent categorical labels (e.g., mapping {"M", "male", "Male"} to "male").
Answer:

Data cleaning detects and corrects data errors to ensure validity and stability. Missing values are handled via statistical imputation or listwise deletion; duplicates are resolved via exact key grouping and fuzzy text matching; inconsistent data is resolved through format standardization, regex filtering, and categorical mapping dictionaries.

Question 2

Explain the difference between data cleaning and data preparation across at least three distinct dimensions.

Evaluate comparative dimensions:

  1. Objective: Data cleaning focuses strictly on identifying and fixing errors, corruptions, and missing values to improve data quality. Data preparation focuses on structuring, transforming, and encoding data to improve model usability.
  2. Scope: Data cleaning is a specific subset of tasks (deduplication, imputation, outlier filtering). Data preparation is an overarching lifecycle phase that includes cleaning plus feature scaling, categorical encoding, dimensionality reduction, and train-test splitting.
  3. Operations: Data cleaning removes negative ages, strips invalid symbols, or imputes NaNs. Data preparation executes min-max normalization, one-hot encoding, polynomial feature generation, and tokenization.
Answer:

Data cleaning targets error detection and correction (imputation, deduplication, syntax repair) to maximize data quality. Data preparation is the broader engineering process that incorporates data cleaning along with feature scaling, categorical encoding, and transformation to make data directly compatible with machine learning models.

Question 3

Define data wrangling and list its main steps. Mention three popular tools or libraries used for data wrangling, and describe a real-world example where data wrangling is applied before analysis.

Definition and steps: Data wrangling is the end-to-end transformation of raw, heterogeneous data into an organized, unified, model-ready format. The six primary steps are:

  1. Discovering: Profiling and understanding raw data assets.
  2. Structuring: Reshaping tables, flattening hierarchies, and unnesting arrays.
  3. Cleaning: Imputing missing values and removing duplicates.
  4. Enriching: Joining external datasets to add context.
  5. Validating: Checking schema integrity and boundary constraints.
  6. Publishing: Delivering the clean dataset to downstream data pipelines.

Popular tools and libraries:

  1. Pandas (Python): Provides fast DataFrame manipulation, grouping, and merging.
  2. NumPy (Python): Vectorized numerical operations and multi-dimensional array slicing.
  3. OpenRefine: Interactive data cleaning and entity clustering tool.

Real-world scenario: An online retail platform compiles sales records from three sources: web store SQL databases, mobile app JSON event streams, and third-party courier CSV delivery sheets. Data wrangling is applied by:

  • Flattening nested JSON app logs into relational columns.
  • Merging delivery status from couriers using tracking IDs.
  • Converting all currency transactions to NPR based on transaction-date exchange rates.
  • Handling duplicate order retries caused by network timeouts.
Answer:

Data wrangling transforms raw, disparate data into an integrated, model-ready format through discovery, structuring, cleaning, enriching, validating, and publishing. Common tools include Pandas, NumPy, and OpenRefine. An example is consolidating e-commerce sales from web databases, mobile JSON logs, and shipping CSVs into a single unified analytical table.

Question 4

Explain how data wrangling differs from data cleaning in scope, methodology, and end deliverables.

Comparative analysis:

  • Scope: Data wrangling has a broad scope covering the entire journey from raw data discovery to relational schema integration and feature publishing. Data cleaning has a narrower scope focused strictly on resolving defects and errors.
  • Methodology: Data cleaning relies on error detection algorithms, outlier fences, and imputation formulas. Data wrangling employs relational joins, aggregations, schema reshaping (melting/pivoting), and external API integration.
  • End Deliverables: The output of data cleaning is an error-free, sanitized version of the input dataset. The output of data wrangling is an enriched, multi-source analytical feature store structured specifically for predictive modeling.
Answer:

Data wrangling encompasses the broad end-to-end pipeline of discovering, reshaping, joining, enriching, and validating heterogeneous datasets. Data cleaning is a focused sub-phase within wrangling dedicated specifically to correcting erroneous values, removing duplicates, and imputing missing data.

Question 5

Define data enrichment and describe its role in enhancing datasets. Provide two concrete practical examples of data enrichment in industry applications.

Definition and role: Data enrichment is the augmentation of an existing dataset with relevant external or internal secondary data sources. Its role is to inject contextual depth, reduce residual variance in predictive models, and provide features that cannot be captured by the primary dataset alone.

Practical Example 1: Customer Relationship Management (CRM): A telecommunications provider possesses basic subscriber call records (user_id, total_minutes, monthly_bill). By enriching this table with demographic census data (neighborhood average income) and smartphone device specs (5G support, screen size), the churn-prediction model gains significant predictive accuracy.

Practical Example 2: Financial Fraud Detection: A banking platform logs credit card transactions (card_id, merchant_id, amount, timestamp). The fraud-detection team enriches each transaction with IP geolocation data (distance between current IP and cardholder billing address) and merchant category risk scores, enabling the classification algorithm to identify anomalous overseas transactions in real time.

Answer:

Data enrichment augments primary datasets with supplementary internal or external data to enhance context and predictive power. Examples include enriching telecom customer records with neighborhood demographic census data to predict churn, and augmenting credit card transactions with IP geolocation coordinates to detect payment fraud.

Question 6

What is data validation, and how does it ensure data accuracy? Describe five key considerations before publishing or sharing datasets.

Definition and mechanism: Data validation is the automated checking of data against predefined logical, statistical, and structural rules before consumption. It ensures accuracy by enforcing schema rules (correct data types), checking domain ranges (e.g., rejection of negative prices), matching patterns via regular expressions, and verifying relational integrity across tables.

Five key pre-publishing considerations:

  1. Accuracy: Verifying that all values are correct and validated against ground-truth domain constraints.
  2. Completeness: Ensuring no unhandled missing values exist in critical predictive columns.
  3. Consistency: Standardizing measurement units, date formats, and attribute naming conventions across all records.
  4. Privacy and Security: Redacting PII (names, phone numbers, addresses) and applying anonymization (e.g., -anonymity) to protect privacy.
  5. Documentation and Metadata: Providing clear data dictionaries, schema contracts, source lineage, and licensing terms.
Answer:

Data validation tests data against explicit logical constraints and schema rules to prevent corrupt entries from entering production. Before publishing datasets, engineers must verify accuracy, completeness, consistency, privacy/PII redaction, and comprehensive documentation/metadata.

Question 7

Consider an academic performance dataset recording exam scores for five students:

  1. Calculate the sample mean and sample variance of the observed scores under listwise deletion.
  2. Impute the missing third score with the observed mean, and calculate the resulting sample variance of the complete 5-student sample.
  3. Evaluate the effect of mean imputation on sample variance.

Listwise deletion sample statistics: Under listwise deletion, the student with the missing score is removed, leaving complete records: .

Compute sample variance ( with Bessel's correction ):

Mean imputation sample statistics: Impute the missing score with the observed mean . The complete sample () is:

The sample mean remains unchanged:

Compute sample variance ():

Evaluate variance shrinkage: Mean imputation adds an observation whose deviation from the mean is identically zero (). While the numerator (sum of squared deviations) remains , the denominator increases from to :

Mean imputation artificially shrinks the sample variance by , underestimating standard errors in subsequent hypothesis tests.

Answer:

Under listwise deletion (), the sample mean is and sample variance is . Following mean imputation ( with imputed score ), the sample mean is preserved at , but sample variance shrinks to (a reduction), demonstrating that mean imputation artificially attenuates variance.

Question 8

An environmental monitoring station records the following eight temperature observations ():

Using the interquartile range (IQR) method:

  1. Determine the first quartile , median , and third quartile .
  2. Calculate the and establish the upper and lower outlier fences.
  3. Identify whether any recorded values represent anomalous outliers.

Calculate quartiles : The data is ordered ():

  • Median (): Average of the 4th and 5th values:
  • First Quartile (): Median of lower half :
  • Third Quartile (): Median of upper half :

Calculate and outlier fences:

Construct fences using the criterion:

Evaluate dataset for outliers: Valid range is .

  • Lowest reading: (valid).
  • Second highest reading: (valid).
  • Highest reading: .

Since exceeds the upper fence of , it is classified as an anomalous outlier.

Answer:

, , , and . The valid range defined by fences is . The reading lies above the upper fence and is flagged as an outlier.

Past exam questions for Foundation of Data Science

← Types of Data, Preprocessing Needs & Data CollectionData Transformation, Encoding & Normalization →