Cleaning tabular data

Motivation

  • Data in the real world is dirty (not yet ready to be analyzed)
    • incomplete: missing values or interesting attributes, or largely aggregate data
    • noisy: containing errors or outliers
    • inconsistent: containing discrepancies in codes or names
  • Effective data cleaning is crucial because it directly impacts the accuracy and reliability of subsequent analyses and modeling.
  • Data cleaning can consume a significant portion of a data scientist’s time, often ranging from 50% to 80% of the entire data analysis process.
  • The time spent on data cleaning varies based on the dataset’s quality and complexity, with messier data requiring more effort.
  • For small, clean datasets, data cleaning might take a few hours to a day, while for large and messy datasets, it can span weeks or even months.
  • Automated tools & scripts can streamline data cleaning & reduce the time required.

Motivating example:

  • You should save as much USEFUL data as you can, and extract most useful features
  • TO many missing values, in a column may mean it is unlikely to be informative

Causes of dirty data

  • Missing Values: Cells or fields with missing data.
  • Duplicates: Duplicate rows or entries within the dataset.
  • Inconsistent Formats: Variability in data formats (e.g., date formats, units of measurement).
  • Outliers: Data points that significantly deviate from the norm.
  • Incorrect Values: Data entries that are factually incorrect or implausible.
  • Spelling and Typographical Errors: Misspelled or mistyped text data.
  • Incomplete or Inaccurate Labels: Incorrect or incomplete variable or column labels.
  • Irrelevant Information: Columns or variables that are not needed for the analysis.
  • Inconsistent Casing: Mixed use of uppercase and lowercase characters in text data.
  • Special Characters and Symbols: Non-standard characters or symbols that may need encoding.
  • Inconsistent Encoding: Encoding issues, especially in international data.
  • Data Entry Errors: Human errors during data entry.
  • Inconsistent Units: Variations in units of measurement for numerical data.
  • Inconsistent Date and Time Zones: Time zone discrepancies in timestamp data.
  • Data Entry Conventions: Different conventions used for representing the same information.

Data cleaning is an iterative process

  • This is an over simplification: cleaning process is non-linear and task specific

Data Normalization

  • Normalization is the process of scaling & standardizing numerical features or variables
  • Normalization does not change the underlying distribution of the data but, ensures that features have a consistent scale for more accurate and stable modeling.
  • Eliminates magnitude differences \(\rightarrow\) remap data to a common scale, typically 0 to 1
  • Normalization helps prevent certain features from dominating others in machine learning algorithms that rely on distance
  • More on this next week!

Aside: General comment on coding