Data cleaning (also called data wrangling or data preprocessing) is the process of detecting and correcting errors, inconsistencies, and formatting issues in raw datasets before analysis or processing. Studies consistently show that data professionals spend 60–80% of their time on data cleaning rather than actual analysis. This guide covers the most common data quality issues and practical techniques for resolving them efficiently.

Why Data Cleaning Matters

Dirty data produces unreliable results. A machine learning model trained on data with 15% duplicate records will be biased. A SQL query counting customers by country will produce wrong totals if "United States", "US", "U.S.", "USA", and "united states" are treated as separate values. A financial report will be inaccurate if some currency amounts are stored as "1,234.56" (US format) and others as "1.234,56" (European format).

The rule of thumb in data science is "garbage in, garbage out" — no amount of sophisticated analysis compensates for fundamental data quality problems.

1. Removing Duplicates

Duplicate records are one of the most common data quality issues, arising from data entry errors, system migrations, multiple data sources, or API pagination bugs that process the same records twice.

In spreadsheets, Excel's "Remove Duplicates" function and Google Sheets' UNIQUE() function handle simple cases. For more control — such as deduplicating by a specific set of columns or keeping the most recent record — use SQL:

-- Keep the most recent record per email (PostgreSQL)
DELETE FROM contacts
WHERE id NOT IN (
  SELECT DISTINCT ON (email) id
  FROM contacts
  ORDER BY email, created_at DESC
);

For text-based duplicate removal in lists and CSVs, the Duplicate Line Remover handles it instantly in the browser — no code required.

2. Fixing Whitespace Issues

Invisible whitespace — extra spaces at the beginning or end of values, double spaces between words, tab characters, non-breaking spaces (U+00A0) — causes string comparison failures and import errors that are notoriously hard to spot visually.

Common sources of whitespace problems:

  • Copy-pasting from PDF files, which often introduces non-standard whitespace
  • Web scraping, where HTML source often includes indentation characters in data values
  • Manual data entry with inconsistent spacing habits
  • Non-breaking spaces from word processors (Word, LibreOffice) that look identical to regular spaces

SQL solution: TRIM(column_name) removes leading and trailing whitespace. REGEXP_REPLACE(column_name, '\s+', ' ', 'g') in PostgreSQL collapses multiple spaces into one. The Whitespace Cleaner handles all these cases for text outside of databases.

3. Standardizing Case and Format

Inconsistent capitalization is a category problem that invalidates grouping operations. Country "Germany" vs "germany" vs "GERMANY" will appear as three separate countries in a GROUP BY query or pivot table.

The solution depends on the field type:

  • Names: Apply Title Case standardization. Note that simple title-casing ("Mcdonald" instead of "McDonald") has edge cases — for person names, database-level normalization is often impractical; enforce it at the input layer instead.
  • Email addresses: Always lowercase. Email addresses are case-insensitive by RFC 5321, but storing them as-entered creates duplicates when the same address is entered with different capitalization.
  • Country codes, currency codes, state abbreviations: Store as UPPERCASE standardized codes (ISO 3166 alpha-2 for countries: "US", "GB", "DE").
  • Product codes, SKUs: Enforce a consistent format at insert time using database constraints or application validation.

4. Handling Null and Missing Values

Missing values appear as NULL in databases, as empty strings, as "N/A", "None", "null", "-", "0" (used incorrectly as a placeholder), or simply as absent fields in JSON. Each encoding of "missing" must be handled differently:

  • NULL vs. empty string: SQL NULL means "unknown/absent." An empty string "" means "known to be empty." They have different semantics. Use NULL for truly missing data, not empty strings. Standardize at the import stage.
  • Imputation vs. removal: For analysis, missing numerical values can be imputed (replaced with the column mean, median, or a predicted value) or the row can be excluded. The choice depends on how much data is missing and why. Missing at random is safer to impute than missing for a systematic reason.
  • Forward-fill and backward-fill: For time-series data, missing values are often filled with the previous or next known value — a technique called forward-fill (ffill) or backward-fill (bfill) in pandas.

5. Date and Time Normalization

Date formats vary wildly across systems, locales, and individuals:

  • 2025-12-31 (ISO 8601 — the international standard)
  • 12/31/2025 (US format — MM/DD/YYYY)
  • 31/12/2025 (European format — DD/MM/YYYY)
  • December 31, 2025 (long form)
  • 31-Dec-25 (abbreviated)
  • 1735689600 (Unix timestamp in seconds)

Always store dates in ISO 8601 format (YYYY-MM-DD) in databases and data files. Parse incoming dates explicitly rather than relying on auto-detection, which is ambiguous for dates like "01/02/03". Always store timestamps in UTC; convert to local time only at the display layer.

6. Validating Referential Integrity

In relational databases, referential integrity means that foreign key values in one table always exist as primary key values in the related table. Data quality failures here produce "orphaned records" — orders with no customer, line items with no product, etc.

-- Find orphaned order records (orders with no matching customer)
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

7. Detecting Outliers

Outliers are data points that deviate significantly from the rest of the dataset. They may represent legitimate extreme values, data entry errors, or system glitches. A product priced at $999,999 may be a real luxury item or a missing decimal point error.

Statistical outlier detection techniques include: Z-score (flag values more than 3 standard deviations from the mean), IQR method (flag values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR), and domain-specific range checks (e.g., age must be between 0 and 130, temperature must be within physically plausible range).

8. Consistency Checks Across Fields

Cross-field validation catches impossible combinations: birth date after death date, order total not matching sum of line items, start date after end date, country "Japan" with state "Texas". These are best enforced through database CHECK constraints and application-layer validation at write time, rather than discovered during analysis.

Practical Workflow

A systematic data cleaning workflow:

  1. Profile the data — count rows, check column types, identify nulls, find min/max/mean
  2. Remove exact duplicates
  3. Standardize formats — dates, case, whitespace, codes
  4. Handle nulls — decide on imputation strategy or exclusion
  5. Validate ranges and cross-field constraints
  6. Document what was changed and why

For text-based data cleaning tasks, the tools on this site handle common operations directly in your browser: Whitespace Cleaner, Duplicate Remover, Case Converter, and Find & Replace with regex support.