Clean and standardise contact data

Clean and standardise contact data

This systematically fixes common data quality issues. Adapt column names and cleaning rules to your needs.

import pandas as pd
import re
from datetime import datetime

# Load data
contacts = pd.read_csv('contacts.csv')
print(f"Loaded {len(contacts)} contacts")
print(f"Columns: {', '.join(contacts.columns)}")

# Create a copy for cleaning
cleaned = contacts.copy()
changes_log = []

def log_change(category, description, count):
    """Track what changes were made"""
    changes_log.append({
        'category': category,
        'description': description,
        'records_affected': count
    })

# 1. Fix postcodes in wrong fields
# UK postcode pattern
postcode_pattern = r'[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}'

# Check if address field contains postcodes
if 'address' in cleaned.columns and 'postcode' in cleaned.columns:
    # Find addresses that contain postcodes
    for idx, row in cleaned.iterrows():
        address = str(row['address'])
        # Search for postcode in address
        match = re.search(postcode_pattern, address, re.IGNORECASE)
        if match and pd.isna(row['postcode']):
            # Extract postcode from address
            postcode = match.group()
            # Remove from address, add to postcode field
            cleaned.at[idx, 'postcode'] = postcode.upper()
            cleaned.at[idx, 'address'] = address.replace(match.group(), '').strip()

    fixed_postcodes = cleaned['postcode'].notna().sum() - contacts['postcode'].notna().sum()
    if fixed_postcodes > 0:
        log_change('Postcodes', f'Moved {fixed_postcodes} postcodes from address to postcode field', fixed_postcodes)

# 2. Standardise phone numbers
if 'phone' in cleaned.columns:
    def clean_phone(phone):
        if pd.isna(phone):
            return phone

        phone = str(phone)
        # Remove common separators
        phone = phone.replace(' ', '').replace('-', '').replace('(', '').replace(')', '')

        # Handle UK numbers
        if phone.startswith('0'):
            phone = '+44' + phone[1:]  # Replace leading 0 with +44
        elif not phone.startswith('+'):
            phone = '+44' + phone  # Add +44 if missing

        return phone

    original_phones = cleaned['phone'].copy()
    cleaned['phone'] = cleaned['phone'].apply(clean_phone)
    changed_phones = (cleaned['phone'] != original_phones).sum()
    if changed_phones > 0:
        log_change('Phone', f'Standardised {changed_phones} phone numbers to +44 format', changed_phones)

# 3. Fix name case inconsistencies
if 'name' in cleaned.columns:
    original_names = cleaned['name'].copy()
    # Title Case for names
    cleaned['name'] = cleaned['name'].str.title()
    changed_names = (cleaned['name'] != original_names).sum()
    if changed_names > 0:
        log_change('Names', f'Standardised {changed_names} names to Title Case', changed_names)

# 4. Clean and validate emails
if 'email' in cleaned.columns:
    def clean_email(email):
        if pd.isna(email):
            return email

        email = str(email).lower().strip()

        # Basic validation: must have @ and domain
        if '@' not in email or '.' not in email.split('@')[1]:
            return None  # Invalid

        # Remove obvious placeholders
        if email in ['none', 'n/a', 'na', 'null', 'no email']:
            return None

        return email

    original_emails = cleaned['email'].copy()
    cleaned['email'] = cleaned['email'].apply(clean_email)
    changed_emails = (cleaned['email'] != original_emails).sum()
    if changed_emails > 0:
        log_change('Emails', f'Cleaned {changed_emails} emails (lowercase, removed invalids)', changed_emails)

# 5. Standardise dates
date_columns = [col for col in cleaned.columns if 'date' in col.lower()]
for col in date_columns:
    def parse_date(date_str):
        if pd.isna(date_str):
            return date_str

        # Try multiple date formats
        formats = ['%Y-%m-%d', '%d/%m/%Y', '%d-%m-%Y', '%Y/%m/%d', '%b %Y', '%B %Y']

        for fmt in formats:
            try:
                parsed = datetime.strptime(str(date_str).strip(), fmt)
                return parsed.strftime('%Y-%m-%d')  # Standardise to YYYY-MM-DD
            except:
                continue

        return date_str  # Couldn't parse

    original_dates = cleaned[col].copy()
    cleaned[col] = cleaned[col].apply(parse_date)
    changed_dates = (cleaned[col] != original_dates).sum()
    if changed_dates > 0:
        log_change('Dates', f'Standardised {changed_dates} dates in {col} to YYYY-MM-DD', changed_dates)

# 6. Remove leading/trailing spaces from all text fields
text_columns = cleaned.select_dtypes(include=['object']).columns
for col in text_columns:
    cleaned[col] = cleaned[col].str.strip()

# Generate cleaning report
print("\n" + "="*60)
print("DATA CLEANING REPORT")
print("="*60)

if len(changes_log) == 0:
    print("\nNo changes needed - data is already clean!")
else:
    print(f"\nTotal changes: {len(changes_log)} categories")
    print("\nChanges made:")
    for change in changes_log:
        print(f"\n{change['category']}:")
        print(f"  {change['description']}")

    total_affected = sum(c['records_affected'] for c in changes_log)
    print(f"\nTotal records affected: {total_affected} of {len(cleaned)}")

# Show examples of changes
print("\n" + "="*60)
print("Sample of cleaned data (first 5 rows):")
print("="*60)
print(cleaned.head().to_string())

# Save cleaned data
cleaned.to_csv('contacts_cleaned.csv', index=False)

# Save changes log
changes_df = pd.DataFrame(changes_log)
if len(changes_df) > 0:
    changes_df.to_csv('cleaning_report.csv', index=False)

print("\n" + "="*60)
print("Files saved:")
print("  contacts_cleaned.csv - cleaned data")
if len(changes_log) > 0:
    print("  cleaning_report.csv - what was changed")
print("\nNext steps:")
print("1. Review cleaned data carefully")
print("2. Spot check 20-30 random records")
print("3. Handle any exceptions manually")
print("4. Import back to CRM if validation passes")
print("5. Document standards and share with team")