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."

Saturday, 14 December 2024

Date your data - "Unique Dates"

Another week has passed us by. And quite a few may not have noticed something unique about last week.

Last Thursday was an interesting day from a numerological point of view. If we express the date as dd-mm-yy eg 12-12-24, 09-04-24 etc then the day before yesterday i.e Thu is the last day for this century where

  • dd = mm part of date and 
  • dd + mm = yy

because there are no month values >12. So there have been 12 such dates, 1st Jan 2002, 2nd Feb 2004, 3rd Mar 2006, 4th Apr 2008, 5th May 2010, 6th Jun 2012, 7th Jul 2014, 8th Aug 2016, 9th Sep 2018, 10th Oct 2020, 11th Nov 2022, and 12th Dec 2024.

So I thought instead of New Year resolutions or Birthday resolutions, why not keep a marker for this last unique date 12th Dec 2024 and try to change myself, say give up a bad habit or improve a skill, declutter my mind from negativity etc. One of the reasons why New Year resolutions or Birthday resolutions don't work is the same event would appear again the next year. But Unlike birthdays/ new years, this unique arrangement of numbers for the date may not occur again during our lifespan since the next such event would only be in the next century. i.e. 01-01-2102.

Below shows a short code snippet in R programming language that prints these dates:-

and this is the output obtained. There are 36525 days in a century.

Trivia : - Symmetric Timestamps:

If we express in from 21st century as dd-yy-mm hh:mm:ss , and if all the values from the 1st "d" to the last "s" are equal such a symmetric timestamp has been used in the recent past for a major event. The number 8 is considered "lucky" in Chinese culture since its pronunciation “Ba” sounds similar to the word “fa”, which means to make fortune. So during the 2008 Beijing Olympics, China inaugurated the Opening Ceremony at exactly 08-08-08 08:08:08 PM. This is the symmetric timestamp that was of significance. The last such for this century has already passed us a decade back at 12-12-12 12:12:12 PM.

Manipulating dates and times is an important skill in Data Science. Messy date cleaning, date extraction,  getting prev month/ next month, grouping by month-year these are essential skills.

Here's a cheatsheet of datetime in R by Datacamp.

https://www.datacamp.com/cheat-sheet/dates-and-time-in-r-cheat-sheet