Pandas string cleaning with reversible mappings

Keep the original text and write cleaned values to separate columns. Many cleaning operations are many-to-one: trimming spaces or changing case can make different source strings identical. You cannot reliably reconstruct the original text from the cleaned label alone.

A reversible workflow therefore preserves record identity, raw values and the mapping version. It does not claim that every string transformation has a mathematical inverse.

Separate normalization from business mapping

The pandas quality lab includes Software with whitespace and case variations, TRAINING and an unfamiliar New service category. First create a normalized lookup key; then map approved keys to canonical labels.

from build_and_verify import load_orders

orders = load_orders()
raw_before = orders['category_raw'].copy()
mapping = {'software': 'Software', 'training': 'Training', 'support': 'Support'}
orders['category_key'] = orders['category_raw'].str.strip().str.casefold()
orders['category_clean'] = orders['category_key'].map(mapping)
orders['category_status'] = 'mapped'
orders.loc[orders['category_clean'].isna(), 'category_status'] = 'unmapped'
orders['category_display'] = orders['category_clean'].fillna('Unmapped')
assert orders['category_raw'].equals(raw_before)
assert orders.loc[orders['order_id'].eq('P01'), 'category_raw'].iloc[0] == ' Software '
assert orders.loc[orders['order_id'].eq('P01'), 'category_clean'].iloc[0] == 'Software'
assert orders.loc[orders['category_status'].eq('unmapped'), 'order_id'].tolist() == ['P07']
assert orders['category_key'].str.strip().str.casefold().equals(orders['category_key'])
print(orders[['order_id', 'category_raw', 'category_clean', 'category_status']].to_string(index=False))

The final assertion checks that applying this normalization again does not further change the key. Idempotence is helpful for repeated processing, but it does not prove the mapping is semantically correct.

The Series.map reference describes dictionary-based value mapping. Unmapped keys become missing in the mapped result, which is why the status column matters.

Do not turn an unfamiliar label into a familiar one

New service remains unmapped until an authorized category definition explains it. Guessing Training because that seems closest would change the business classification.

The display label Unmapped is a reporting convenience. The raw New service value remains available for investigation. A production mapping table should include ownership, effective dates when relevant and a version identifier.

Audit the mapping's effect

from build_and_verify import load_orders

orders = load_orders()
key = orders['category_raw'].str.strip().str.casefold()
orders['category_display'] = key.map({'software': 'Software', 'training': 'Training',
                                      'support': 'Support'}).fillna('Unmapped')
paid = orders.loc[orders['status'].eq('Paid')]
totals = paid.groupby('category_display')['amount_paise'].sum(min_count=1)
assert totals.to_dict() == {'Software': 30000, 'Support': 0, 'Training': 20000, 'Unmapped': 7000}
assert int(totals.sum()) == 57000
assert paid.loc[paid['category_display'].eq('Software'), 'amount_paise'].isna().sum() == 1
print(totals)

The known subtotal remains 57,000 paise after classification. Software still has an unknown amount, so its 30,000 paise is a known subtotal rather than a complete total.

Also compare row counts and unique order IDs. Preserving the total alone would not detect every accidental row loss or duplication.

Treat identifiers differently from labels

Case folding may be appropriate for a controlled category vocabulary but wrong for an external identifier whose source treats case as significant. Removing punctuation from product codes can collapse distinct products.

Define transformations field by field. A generic clean_every_string function can create damage that is difficult to reverse if it overwrites every original column.

Keep the repair trail small and useful

An audit table can store raw label, normalized key, mapped label, mapping version and affected-row count. Retain stable record keys in the dataset so a reviewer can identify exactly which records changed when a mapping is updated.

Exercise: propose a mapping for New service, record its justification and rerun the category summary. Show that only the intended classification changes while order count, known subtotal and missing-amount count remain invariant.

NeuraPath's Data Analytics with Generative AI course connects pandas text handling with traceable data preparation. Clean labels are useful when the original evidence and classification decisions remain inspectable.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Reshape survey data with melt and pivot.
• Continue with Categorical data: reduce memory without losing unknown values.