Missing Data & Dara cleaning implementation. Comprehensive Notes on Missing Data Management and Data Cleaning Principles

Categorization and Fundamentals of Missing Data

• Missing data occurs when observations in a dataset lack expected values or information across one or more variables.

• Missing data is divided into two primary classifications based on the underlying rationale for the missingness:

  • Legitimate Missing Data:

    • Occurs when missing information is logically justified and accurate given the context of the observation.

    • Example: A survey asks for the color of a respondent's car. If the respondent does not own a car, leaving the car color field empty is a legitimate and logical absence of data.

  • Illegitimate Missing Data:

    • Occurs when data is omitted due to non-response, refusal, or skipping of relevant questions by subjects when data ought to exist.

    • Example: A survey asks respondents for their income level. A subject feels uncomfortable sharing sensitive personal financial details and chooses to skip the question, resulting in illegitimate missing data.

• Contextual Dataset Example:

  • Consider a dataset tracking demographic and economic attributes with variables such as depth, age of person, and income level.

  • In a specific sample instance, the age variable column contains exactly $2$ missing data entries where two distinct observations fail to specify the person's age.

Methodologies for Handling Missing Data

• Omission Strategies (Data Removal):

  • Removing Observations (Row Removal):

    • Applicable when missing values are restricted to a very small count or percentage of total records.

    • Involves deleting the entire row/observation containing the missing entry.

    • Trade-off: Direct loss of data. In the sample dataset instance, deleting missing age records results in a permanent loss of $2$ full observations.

  • Removing Variables (Column Dropping):

    • Applicable when missing values are heavily concentrated within a few specific variables across the dataset.

    • Involves dropping the affected variable column entirely from the dataset and utilizing alternative proxy variables for analysis.

    • Example: Deleting the entire age variable column from the dataset if it contains missing entries across records.

  • Limitations of Omission:

    • If missing values are widespread across many records, omitting rows or columns becomes highly impractical due to excessive data destruction.

• Imputation Strategies (Data Substitution):

  • Imputation involves substituting missing entries with statistically reasonable alternative values instead of discarding data points.

  • Preserves full observational records without reducing sample size, leveraging existing contextual information to fill data gaps.

  • Mean Value Imputation:

    • Calculates the arithmetic mean of all existing, non-missing entries for a continuous variable and replaces every missing entry with this computed average.

    • Example Calculation: Averaging all valid, non-missing age values in the sample dataset yields an average age of $29.4$ years. The $2$ missing age entries are subsequently replaced with the calculated mean value of $29.4$.

Data Cleaning Dynamics, Scope, and Resource Allocation

• Nature and Complexity of Data Cleaning:

  • Data cleaning is inherently data-specific, requiring customized, specialized workflows tailored to the exact structure, domain, and anomalies of each individual dataset.

  • It is exceptionally difficult to fully automate due to the non-standardized variety of data errors across different sources.

• Analyst Time Allocation Ratio:

  • Data cleaning and data preparation consume approximately $80\%$ of a data analyst's total operational time.

  • Only $20\%$ of an analyst's total operational time is dedicated to executing actual data analysis, modeling, and interpretation.

• Role of Emerging Technologies:

  • Advancements and improvements in artificial intelligence (AI) offer developing opportunities to streamline, enhance, and partially automate the data cleaning pipeline.

Structural Data Integration and Cross-Departmental Challenges

• Dirty Structures:

  • Refers to incoming datasets whose raw structural layout fails to match the required format for analytical software or processing frameworks.

  • Requires structural transformations to reconfigure schemas, align data types, and normalize structures into a compatible layout.

• Cross-Departmental Dataset Combination:

  • Analytical initiatives frequently require merging disparate datasets from multiple organizational business units into a single consolidated master dataset.

  • Examples of departmental data sources:

    • Financial Data: Extracted from the Finance Department.

    • Sales Data: Extracted from the Sales Department.

    • Production Data: Extracted from the Production Department.

  • Organizational Friction: Consolidating cross-departmental data requires significant time, effort, and coordination, particularly when individual departments or stakeholders have not fully bought into the new data initiative.

Specific Data Cleaning Techniques and Standardization Rules

• Duplicate Elimination:

  • Identifying and removing duplicate observations where the exact same entity or event is recorded more than once.

• Textual and Typographical Corrections:

  • Spelling Error Resolution: Fixing misspellings across text entries.

  • Naming Standardization: Harmonizing alternative abbreviations or naming versions (e.g., converting mixed variations like UNCW and UNC-W into a single, uniform standard).

  • Letter Casing Normalization: Aligning mismatched uppercase and lowercase text strings.

  • Whitespace Trimming: Removing unwanted leading, trailing, or middle extra spaces within text fields.

• Character Stripping and Field Parsing:

  • Special Character Removal: Stripping non-numeric characters such as dollar signs ($) from numeric data fields to render them mathematically computable.

  • Date Format Harmonization: Converting inconsistent date representations across rows into a single, uniform date standard.

  • Unstacking Concatenated Fields: Splitting multi-attribute single columns (e.g., a combined column containing city, county, and country together) into separate dedicated columns.

• Standard Dataset Hygiene Principles:

  • Single Field Isolation: The optimal dataset layout requires exactly one column representing one single field alone.

  • Formatting Consistency: All data values within a single field (such as dates or currency) must adhere strictly to the exact same format throughout.

  • Redundant Summary Removal: Deleting unnecessary rows or columns containing totals, subtotals, or aggregated summaries embedded within raw observational data.

  • Empty Element Deletion: Identifying and purging empty rows or empty columns situated within the interior or boundaries of the dataset.

  • Universal Missing Data Protocol: Every analytical dataset must systematically address missing values through deletion or imputation prior to downstream analytics.