String Cleaning and Text Processing

# CHAPTER 9

String Cleaning and Text Processing #

1. Chapter Introduction #

2. The .str Accessor in Pandas #

Pandas provides a .str accessor that lets you apply Python string methods to an entire column at once.

import pandas as pd

df = pd.DataFrame({
    'name': ['  Alice Smith  ', 'BOB JONES', 'charlie brown', 'David  Lee'],
    'state': ['NY', 'n.y.', 'New York', ' ny '],
    'phone': ['(555) 123-4567', '555.123.4568', '555-123-4569', '1234567890']
})

print("=== RAW DATA ===")
print(df)

3. Normalization: Case and Whitespace #

The first step in string cleaning is making everything look uniform.

# 1. Strip leading and trailing whitespace
df['name_clean'] = df['name'].str.strip()

# 2. Standardize case (title case for names, upper for state codes)
df['name_clean'] = df['name_clean'].str.title()

# Fix internal multiple spaces (David  Lee -> David Lee)
# replace '  ' (two spaces) with ' ' (one space)
df['name_clean'] = df['name_clean'].str.replace(r'\s+', ' ', regex=True)

print("\n=== CLEANED NAMES ===")
print(df[['name', 'name_clean']])

4. Replacing and Standardizing Strings #

Often you need to map messy variations to a single standard format.

# Standardize state to uppercase and strip spaces
df['state_clean'] = df['state'].str.strip().str.upper()

# Remove punctuation (like periods in N.Y.)
df['state_clean'] = df['state_clean'].str.replace('.', '', regex=False)

# Map variations to a standard code
state_map = {
    'NEW YORK': 'NY'
}
# Replace specific strings using a dictionary
df['state_clean'] = df['state_clean'].replace(state_map)

print("\n=== CLEANED STATES ===")
print(df[['state', 'state_clean']])

5. Regex: The Ultimate Text Tool #

Regular Expressions (Regex) define a search pattern. They are complex but incredibly powerful for extracting or replacing data.

*Basic Regex cheat sheet:*

• \d: Any digit (0-9)

• \D: Any non-digit

• \s: Any whitespace

• \w: Any word character (a-z, 0-9, _)

• ^: Start of string

• $: End of string

# Clean Phone Numbers: We only want the 10 digits
# \D matches anything that is NOT a digit. We replace it with nothing ('')
df['phone_clean'] = df['phone'].str.replace(r'\D', '', regex=True)

print("\n=== CLEANED PHONES ===")
print(df[['phone', 'phone_clean']])

6. Extracting Data with Regex #

Sometimes data is trapped inside a longer string (e.g., extracting an invoice number from a description).

invoices = pd.DataFrame({
    'desc': [
        'Paid INV-1045 on Tuesday',
        'Invoice INV-3390 processed',
        'Check for INV-0021'
    ]
})

# Extract pattern: "INV-" followed by exactly 4 digits
# The parenthesis () capture the specific part we want to keep
invoices['invoice_num'] = invoices['desc'].str.extract(r'(INV-\d{4})')

print("\n=== EXTRACTED DATA ===")
print(invoices)

7. Splitting Strings #

You often need to split a single column into multiple columns.

names = pd.DataFrame({'full_name': ['Smith, John', 'Doe, Jane', 'Bond, James']})

# Split on the comma and space
# expand=True returns a DataFrame with multiple columns instead of a list
split_names = names['full_name'].str.split(', ', expand=True)

# Rename the new columns
split_names.columns = ['last_name', 'first_name']

# Combine with original
names = pd.concat([names, split_names], axis=1)

print("\n=== SPLIT NAMES ===")
print(names)

8. Common Mistakes #

• Forgetting regex=True (or False): In Pandas, str.replace('.', '') might replace EVERY character if it assumes '.' is a regex wildcard. Use regex=False for literal characters, or regex=True when using patterns like \d.

• Not standardizing case before grouping: If you group sales by "Apple" and "apple", they are treated as two different companies. Always run .str.lower() or .str.upper() before doing aggregations.

9. MCQs #

• Q: A column contains product IDs like "PROD1234ABC", "PROD56_XY". Write a Pandas command to extract just the numeric part.

• Q: How do you handle a column where city names are typed inconsistently (e.g., "la", "L.A.", "Los Angeles")?

11. Summary #

12. Next Chapter Recommendation #