Showing posts with label Data Cleaning. Show all posts
Showing posts with label Data Cleaning. Show all posts

Friday, 21 August 2026

From Wordle’s 'STRIP' to Messy Excel Headers: Cleaning Whitespace in Python and R

So Wordle's solution for Tuesday 18th Aug was STRIP.

And this instantly brought to mind the strip() method in Python. In data cleaning, .strip(), .lstrip(), and .rstrip() are the go-to tools for trimming leading and trailing whitespace.

However, standard stripping often fails when dealing with Excel files where column headers contain multi-line cell breaks (Alt + Enter), tabs, or irregular spacing. Take this Excel file column with a line break:

It appears as a \n in Python.

strip() doesn't remove \n. Internal newline and consecutive spaces remain.

Why \s+ Solves the Newline Problem

To collapse internal whitespace, Python’s re module provides the cleanest solution:

Why does this erase \n? In Python regex, \s matches any whitespace character—including:

  • Space ( )
  • Newline (\n)
  • Tab (\t)
  • Carriage return (\r)
  • Form feed (\f) and vertical tab (\v)

The + quantifier denotes >=1 occurrence so all of these get replaced by a single space.

Standardizing to snake_case (Common Practice)

A good practice is to replace all spaces with underscores _. Instead of looping through all the column names, we can do it in a vectorized manner:

Handling Apostrophes and Special Characters ('s, %, $, @, ?)

When standardizing headers into snake_case, simply replacing non-word characters can cause awkward results. For instance, converting "Customer's Name" directly could give "customer_s_name" instead of "customer_name".

To get clean, professional column names, you need a multi-step cleanup strategy:

  1. Remove possessives ('s): "Customer's" -> "Customer".
  2. Strip special characters: Remove symbols like %, $, @, ?.
  3. Collapse whitespace: Replace \n, \t, and spaces with _.
  4. Format case: Trim boundary underscores and convert to lowercase.

We can vectorize these operations across all column names.

Python (Pandas)

R

R has the janitor package which removes leading and trailing spaces, converts to snake case, and removes special symbols. For removing 's, we use str_remove_all from the stringr package.

Conclusion

This is only a primer to the world of data cleaning. Further data cleaning steps could include:

  • Removing punctuation (dots, commas, slashes)—useful in Address fields.
  • Handling consistent structure—e.g., a Quantity column should have only numeric values. If Quantity is written in words for certain rows (e.g., "Two Thousand and Forty Five"), develop a parser to convert these to numeric.
  • Date format handling—e.g., standardizing all date formats to dd-mm-yyyy across multiple weekly time-series files from different systems.
  • Removing duplicate rows.
  • Checking and handling missing values.
  • Managing outliers (either removing outliers or transforming data using a logarithmic scale).
  • Categorical data treatment (one-hot encoding or categorical variables).
  • Removing ``` json formatting tags from LLM JSON outputs.

These steps, if performed before applying any Machine Learning model, dramatically improve accuracy and predictive power. As the old adage goes, "Garbage in, garbage out."