Topic 8.7: Format Standardization

A transport authority merges bus-stop records from three regional systems. The same stops appear under different names: 'Tahrir Sq', 'tahrir sq.', 'TAHRIR SQUARE'. When the authority generates a route map, each spelling becomes a separate stop — the same location appears three times. Route calculations give passengers conflicting information, and journey planners show phantom stops that do not exist.

This is the invisible failure mode of formatting inconsistencies. The data is structurally correct — one column, one variable, one value per cell. But the content is semantically broken: the same real-world entity is written in multiple ways. The W8 competition dataset has exactly this problem in the city column (7 spellings for 3 cities) and in the date column (4 different date format conventions).

The first step in any string standardisation is to see exactly what variants exist. The W8 dataset has 7 different city spellings for only 3 actual cities. The table below maps every variant to its canonical (correct) form.

import pandas as pd

df = pd.read_csv('../Datasets/8_4_competition_results.csv')

# See how many unique 'cities' exist due to formatting
print(df['city'].value_counts())

The problem has two components: case variation ('cairo' vs 'Cairo' vs 'CAIRO') and abbreviation ('Alex' vs 'Alexandria'). Case variation can be solved with string methods alone. Abbreviations require an explicit mapping — there is no string function that knows 'Alex' means 'Alexandria'.

The two most important string cleaning operations for any text column are case normalisation and whitespace removal. The notebook applies .str.lower() first to convert every value to lowercase, then chains .str.strip() to remove leading and trailing whitespace.

# Standardize all city names to lowercase
df['city'] = df['city'].str.lower()

print(df['city'].value_counts())

From 7 spellings to 4 in one line. 'cairo', 'CAIRO', and 'Cairo' have all merged into 'cairo' (95 records). 'giza' and 'Giza' have merged into 'giza' (40 records). The remaining problem is 'alex' vs 'alexandria' — a case-only fix cannot resolve an abbreviation. Chaining .str.strip() onto the same line additionally removes any leading/trailing whitespace left over from manual data entry:

# Chain lower() and strip() together
df['city'] = df['city'].str.lower().str.strip()

print(df['city'].value_counts())

Chaining string methods is a common and idiomatic pandas pattern: each .str. call returns a new Series, so the next .str. call can be applied directly on the result. The order does not matter for lower() and strip() specifically, since they act on different aspects of the string (case vs. surrounding whitespace).

Abbreviation variants require an explicit, whole-value replacement — there is no string function that knows 'alex' means 'alexandria'. The notebook uses .replace() on the Series (not .str.replace()), which matters: .str.replace() matches substrings, so replacing 'alex' would also corrupt 'alexandria' into 'alexandriaandria'. Plain .replace() only matches whole values.

# Replace a specific wrong spelling with the correct one
# IMPORTANT: use .replace() (whole-value match), NOT .str.replace() which matches substrings
# .str.replace('alex','alexandria') would turn 'alexandria' into 'alexandriaandria'
df['city'] = df['city'].replace('alex', 'alexandria')

# Verify the fix
print(df['city'].value_counts())

The city column is now fully standardised: 3 cities, 3 spellings, correct counts. 'cairo' has 95 records, 'alexandria' has 46 (32 originally spelled out + 14 from the 'alex' abbreviation), and 'giza' has 40. Any groupby('city') operation will now produce exactly 3 groups, each containing all records for that city.

The W8 dataset's date column contains four different date format conventions in the same column. No single format string can parse all four — the solution is pd.to_datetime(format='mixed'), which inspects each value individually and applies the appropriate parsing rule.

# Inspect date variants before conversion
print("Date value sample (showing all format variants):")
print(df['date'].value_counts().head(10))
print(f"\nCurrent dtype: {df['date'].dtype}")

# Convert string dates to proper datetime format
# format='mixed' lets pandas infer per element when the column mixes formats
df['date'] = pd.to_datetime(df['date'], format='mixed')

print(df['date'].dtype)
print(df['date'].head())

The date column is now stored as datetime64[ns] instead of plain text. All four format variants have been parsed correctly and stored in a unified internal representation. Date-based operations like .min(), .max(), .dt.month, and time-series sorting all now work correctly — they would fail or behave unpredictably on the original string column.

With the column now a true datetime type, individual date components can be pulled out as their own columns using the .dt accessor:

# Extract year, month, day as separate columns
df['year']  = df['date'].dt.year
df['month'] = df['date'].dt.month
df['day']   = df['date'].dt.day

print(df[['date', 'year', 'month', 'day']].head())

After all format standardisation steps, a final verification confirms that the column types and value counts match expectations before moving to encoding (Topic 8.8).

# Full post-standardisation verification
print("=== Column types after standardisation ===")
print(df.dtypes)

print("\n=== City distribution (should be 3 cities) ===")
print(df['city'].value_counts())

print("\n=== Date range ===")
print(f"Min: {df['date'].min().date()}, Max: {df['date'].max().date()}")

print("\n=== Any remaining missing values? ===")
print(df.isna().sum())

All verifications pass. The date column is now datetime64[ns]. The city column now has exactly 3 categories: cairo (95 records), alexandria (46 records), and giza (40 records). Every value_counts() check returns 3 categories instead of 7, so any groupby('city') operation now produces clean, complete results. The dataset is ready for encoding in Topic 8.8.

Converting the date column to datetime64 is not merely a type-correction exercise — it unlocks the full power of Pandas' time-based operations through the .dt accessor. Once dates are stored as datetime, you can extract components, sort chronologically, filter by date range, and compute time differences.

import pandas as pd

# After conversion, demonstrate .dt accessor operations
df = pd.read_csv('../Datasets/8_4_competition_results.csv')
df['city'] = df['city'].str.lower().str.strip().replace('alex', 'alexandria')
df['date'] = pd.to_datetime(df['date'], format='mixed')
df['year']  = df['date'].dt.year
df['month'] = df['date'].dt.month
df['day']   = df['date'].dt.day

print("Date-based operations after conversion:")
print(f"Month extracted: {df['date'].dt.month.unique()}")
print(f"Day names: {df['date'].dt.day_name().unique()}")
print(f"Date range: {df['date'].min().date()} to {df['date'].max().date()}")

# Sort by date — works correctly as datetime
print("\nEarliest 3 records by date:")
print(df.sort_values('date')[['name', 'city', 'score', 'date']].head(3).to_string())

The competition ran across 8 days (January 15–22, 2024). All dates fall within January, and the day-name extraction confirms the competition ran across all days of the week. None of this time-based analysis is possible while the column is stored as a text string — chronological sorting on text would sort alphabetically, not chronologically.