Trim Whitespace in an Excel File Before Reading It into pandas

groupby splitting one city into two

A trailing space on some rows makes groupby treat 'London' and 'London ' as different groups. Pre-trimming the file collapses them.

# Before pre-trim
>>> df.groupby('city').size()
London     412
London       (trailing space)   18

# After dropping the file on the Whitespace Trimmer, then:
>>> pd.read_excel('data-processed.xlsx').groupby('city').size()
London     430

merge dropping rows on a whitespace-padded key

The left frame's key has a trailing space; the right frame's doesn't. The merge silently loses those rows. Trim the source files first.

left.key  = 'A-12 '   (trailing space)
right.key = 'A-12'
>>> left.merge(right, on='key')      # those rows vanish

# After trimming the source xlsx:
left.key = 'A-12' == right.key       # rows merge as expected

The \xa0 that survives .str.strip()

Web-scraped data exported to Excel carries CHAR 160 (\xa0). A bare .str.strip() leaves it because Python's default strip targets ASCII whitespace and \xa0 is non-breaking. This tool removes edge \xa0.

>>> df['name'].iloc[0]
'Globex\xa0'                 # trailing non-breaking space
>>> df['name'].str.strip().iloc[0]
'Globex\xa0'                 # .strip() left it!  (need str.strip() to target it, or...)

# Pre-trim the file → cell becomes 'Globex' before read_excel ever sees it

Internal double space — handle in pandas

This tool keeps 'New York' (internal double space). Collapse it in pandas after loading the pre-trimmed file.

# Trimmer keeps internal spaces:
'New  York'  →  'New  York'

# Collapse in pandas:
df['city'] = df['city'].str.replace(r'\s+', ' ', regex=True).str.strip()
# 'New  York'  →  'New York'

Reproducible pipeline: trim once, read everywhere

Drop the source on the trimmer at ingest, commit the cleaned file, and your read_excel stays free of per-column strip boilerplate.

ingest:  source.xlsx → Whitespace Trimmer → source-processed.xlsx (commit/stage)
load:    df = pd.read_excel('source-processed.xlsx')   # no .str.strip() needed
         # only internal-space / type fixes remain, if any

Python's str.strip() with no argument removes ASCII whitespace and does remove \xa0 in modern Python (it is Unicode-whitespace-aware) — but data engineers are frequently bitten when a column is object with mixed types, when strip is applied to the wrong column, or when the char is U+200B (which is NOT stripped). Pre-trimming the source file removes edge \xa0 deterministically before the DataFrame exists, eliminating that class of surprise.

New York keeps its internal spaces — the tool trims edges only. Normalise internal runs in pandas with df['col'].str.replace(r'\s+', ' ', regex=True).str.strip().

JavaScript trim does not treat U+200B as whitespace, so an edge zero-width space remains. In pandas, target it explicitly: df['col'].str.replace('\u200b', '', regex=False).

The XLSX path processes the first worksheet and returns a single-sheet file. If your pipeline reads specific sheets by name (sheet_name='Q2'), trim each sheet as its own file, or do read_excel(sheet_name=None) and .str.strip() in pandas instead.

Because the file passes through a plain-text stage, a column pandas would have inferred as int64/float64/datetime64 may come back such that read_excel infers it differently (e.g. as object or with a different format). Pin dtypes explicitly: pd.read_excel(..., dtype={'zip':'string'}) and parse dates with parse_dates=[...].

If a ZIP or product code with a leading zero was stored as text in the source, its text (minus edge whitespace) is preserved. Read it as string to keep the zero: dtype={'zip':'string'}. If Excel had already stored it as a number, the zero was gone before trimming.

Date cells export as their displayed text (e.g. 1/15/26) and are trimmed as text. After loading, parse them: pd.read_excel(..., parse_dates=['date']) or pd.to_datetime(df['date']).

Source files above your tier ceiling (Free 5 MB / 10,000 rows) are blocked. For large datasets, either upgrade, or skip pre-trimming and do the strip in pandas with df = df.apply(lambda c: c.str.strip() if c.dtype=='object' else c).

An empty cell stays an empty string through the round-trip. After read_excel, an empty string may read as NaN or '' depending on the cell — normalise with df.replace('', pd.NA) if your logic depends on isna().

A password-encrypted .xlsx can't be read by the browser parser. Decrypt in Excel first, or read it in Python with a library that supports the password and .str.strip() there.