Pandas - Data validation

Calculating read time…

This Titorial shows you how to create validation rules to catch errors and ensure data quality using Pandas.

We'll cover everything from basic null checks to complex business rules with practical, beginner-friendly examples.

Sample Dataset for Practice

Let's create a sample customer dataset that contains various data quality issues. You can use this exact dataset to practice all the validation techniques in this guide!

import pandas as pd
import numpy as np

# Create sample dataset with intentional errors
data = {
    'Customer_ID': [101, 102, 103, 104, None, 106, 107, 108, 109, 110],
    'Name': ['Alice Smith', 'bob jones', 'CHARLIE BROWN', 'Diana Prince', 'Eve Wilson', 'frank miller', 'Grace Lee', 'Henry Ford', 'Ivy Chen', 'Jack Ryan'],
    'Age': [25, 150, 30, -5, 45, 28, 200, 35, 22, 40],
    'Email': ['alice@email.com', 'bobemailcom', 'charlie@domain.org', 'diana@test.com', None, 'frank@company.com', 'gracetest.com', 'henry@mail.com', 'ivy@example.com', 'jack@office.com'],
    'Phone': ['1234567890', '98765', '5551234567', '9876543210', '4445556666', '123-456-7890', '9998887777', '5554443333', '1112223333', '6667778888'],
    'Salary': [50000, 60000, -10000, 75000, 80000, 0, 55000, 90000, 48000, 70000],
    'Rating': [4.5, 3.2, 6.0, 2.8, 4.0, 5.5, 3.9, 4.2, 0.5, 4.8],
    'Status': ['Active', 'Inactive', 'Pending', 'Active', 'Unknown', 'Active', 'Inactive', 'Pending', 'Active', 'Invalid'],
    'Join_Date': ['2023-01-15', '2023-03-20', '2025-12-01', '2022-11-10', '2023-05-08', '2023-07-22', '2023-09-14', '2022-12-30', '2023-02-18', '2023-08-05']
}

df = pd.DataFrame(data)
print(df)

Dataset Preview:

Customer_ID	Name	Age	Email	Salary	Rating	Status
101	Alice Smith	25	alice@email.com	50000	4.5	Active
102	bob jones ❌	150 ❌	bobemailcom ❌	60000	3.2	Inactive
103	CHARLIE BROWN ❌	30	charlie@domain.org	-10000 ❌	6.0 ❌	Pending
104	Diana Prince	-5 ❌	diana@test.com	75000	2.8	Active
None ❌	Eve Wilson	45	None ❌	80000	4.0	Unknown ❌

Notice: This table shows just the first 5 rows. Red marks (❌) indicate data quality issues we'll catch with validation!

Why Data Validation Matters

Imagine analyzing customer data and finding that someone is listed as 250 years old, or has a negative salary, or an email without the @ symbol. These errors can completely mess up your analysis! Validation catches these issues before they cause problems.

Data validation answers key questions:

Are all required fields filled in?
Are values within acceptable ranges?
Do emails, phone numbers, and other fields follow the correct format?
Are there any impossible or suspicious values?

1. Basic Null Value Validation

The simplest validation - checking if required columns have missing data. You don't want critical fields like customer IDs or names to be empty!

# Check for null values in a specific column
null_count = df['Customer_ID'].isnull().sum()
print(f"Customer_ID has {null_count} null values")

# Check multiple columns at once
important_columns = ['Customer_ID', 'Name', 'Email']
for column in important_columns:
    null_count = df[column].isnull().sum()
    print(f"{column}: {null_count} null values")

Example Output:

Customer_ID: 0 null values
Name: 3 null values
Email: 5 null values

Real-World Use Case: Before sending marketing emails, validate that all customer records have valid email addresses!

2. Numeric Range Validation

Numbers should make sense! Ages should be reasonable, salaries should be positive, ratings should be within scale. Let's validate numeric ranges.

# Validate age is between 18 and 100
invalid_age = df[(df['Age'] < 18) | (df['Age'] > 100)]
print(f"Invalid ages: {len(invalid_age)} records")
print(invalid_age[['Name', 'Age']])

# Validate salary is positive
invalid_salary = df[df['Salary'] <= 0]
print(f"Invalid salaries: {len(invalid_salary)} records")

# Validate rating is between 1 and 5
invalid_rating = df[(df['Rating'] < 1) | (df['Rating'] > 5)]
print(f"Invalid ratings: {len(invalid_rating)} records")

Example:

Input data with ages: [25, 150, 30, -5, 45]
Invalid ages found: 2 records (150 and -5)

Pro Tip: Use the | (OR) operator to check if values are outside the acceptable range on either end!

3. String Pattern Validation

Emails need @, phone numbers need digits, URLs need http - let's validate text patterns!

# Validate email contains @ symbol
invalid_email = df[~df['Email'].str.contains('@', na=False)]
print(f"Invalid emails (no @): {len(invalid_email)}")

# Validate phone numbers have exactly 10 digits
invalid_phone = df[~df['Phone'].str.match(r'^\d{10}$', na=False)]
print(f"Invalid phone numbers: {len(invalid_phone)}")

# Validate website URLs start with http
invalid_url = df[~df['Website'].str.startswith('http', na=False)]
print(f"Invalid URLs: {len(invalid_url)}")

Example:

Emails: ["user@email.com", "invalidemail.com", "another@domain.org"]
Invalid emails found: 1 (invalidemail.com - missing @)

Note: The ~ symbol means "NOT" - so we're finding rows that do NOT contain @

4. Category Validation

Sometimes values should only be from a specific list. For example, status should be "Active", "Inactive", or "Pending" - nothing else!

# Define valid categories
valid_status = ['Active', 'Inactive', 'Pending']
invalid_status = df[~df['Status'].isin(valid_status)]
print(f"Invalid status values: {len(invalid_status)}")
print(invalid_status[['Name', 'Status']])

# Validate gender is M or F
valid_gender = ['M', 'F', 'Male', 'Female']
invalid_gender = df[~df['Gender'].isin(valid_gender)]
print(f"Invalid gender values: {len(invalid_gender)}")

# Validate country codes (ISO 2-letter)
valid_countries = ['US', 'UK', 'IN', 'CA', 'AU']
invalid_country = df[~df['Country'].isin(valid_countries)]
print(f"Invalid country codes: {len(invalid_country)}")

Example:

Status values: ["Active", "Inactive", "Unknown", "Pending"]
Invalid status found: 1 ("Unknown" - not in valid list)

5. Date Validation

Dates should make sense - birth dates shouldn't be in the future, order dates shouldn't be before the company existed!

import pandas as pd
from datetime import datetime

# Convert to datetime first
df['Birth_Date'] = pd.to_datetime(df['Birth_Date'], errors='coerce')

# Validate birth date is not in the future
today = datetime.now()
invalid_birth = df[df['Birth_Date'] > today]
print(f"Future birth dates: {len(invalid_birth)}")

# Validate order date is within last 5 years
five_years_ago = today - pd.Timedelta(days=5*365)
invalid_order = df[df['Order_Date'] < five_years_ago]
print(f"Orders older than 5 years: {len(invalid_order)}")

# Validate hire date is before today
invalid_hire = df[df['Hire_Date'] > today]
print(f"Future hire dates: {len(invalid_hire)}")

Real-World Use Case: E-commerce platforms validate that order dates aren't in the future and delivery dates are after order dates!

6. Cross-Column Validation

Sometimes one column's value depends on another. For example, end date should be after start date, discounted price should be less than original price.

# Validate end date is after start date
invalid_dates = df[df['End_Date'] <= df['Start_Date']]
print(f"End date before start date: {len(invalid_dates)}")

# Validate discount price is less than original price
invalid_price = df[df['Discount_Price'] >= df['Original_Price']]
print(f"Discount >= original price: {len(invalid_price)}")

# Validate total matches quantity × price
df['Calculated_Total'] = df['Quantity'] * df['Price']
invalid_total = df[abs(df['Total'] - df['Calculated_Total']) > 0.01]
print(f"Total doesn't match calculation: {len(invalid_total)}")

Example:

Start Date: 2024-01-15, End Date: 2024-01-10
Invalid: End date (Jan 10) is before start date (Jan 15)

7. Create a Complete Validation Function

Now let's put it all together into one powerful validation function that checks everything at once!

def validate_data(df):
    """Validate cleaned data against business rules"""
    
    print("=== DATA VALIDATION REPORT ===\n")
    
    # Define validation rules
    rules = {
        'Customer_ID': 'Should not be null',
        'Name': 'Should be title case',
        'Age': 'Between 18 and 100',
        'Email': 'Should contain @',
        'Salary': 'Should be positive',
        'Rating': 'Between 1 and 5'
    }
    
    # Check each rule
    for column, rule in rules.items():
        if column in df.columns:
            
            if column == 'Customer_ID':
                null_count = df[column].isnull().sum()
                print(f" {column}: {null_count} null values")
            
            elif column == 'Name':
                # Check if names are in title case
                not_title = df[df[column] != df[column].str.title()]
                print(f" {column}: {len(not_title)} not in title case")
            
            elif column == 'Age':
                invalid = df[(df['Age'] < 18) | (df['Age'] > 100)]
                print(f" {column}: {len(invalid)} invalid (outside 18-100)")
            
            elif column == 'Email':
                invalid = df[~df['Email'].str.contains('@', na=False)]
                print(f" {column}: {len(invalid)} invalid (no @ symbol)")
            
            elif column == 'Salary':
                invalid = df[df['Salary'] <= 0]
                print(f" {column}: {len(invalid)} invalid (<= 0)")
            
            elif column == 'Rating':
                invalid = df[(df['Rating'] < 1) | (df['Rating'] > 5)]
                print(f" {column}: {len(invalid)} invalid (outside 1-5)")
    
    # Summary
    print(f"\n Final dataset shape: {df.shape}")
    print(f" Total rows: {len(df)}")
    print(f" Total columns: {len(df.columns)}")
    print(f"\n Data types:\n{df.dtypes}")

# Use the function
validate_data(df_clean)

Example Output:

=== DATA VALIDATION REPORT ===

 Customer_ID: 0 null values
 Name: 2 not in title case
 Age: 3 invalid (outside 18-100)
 Email: 5 invalid (no @ symbol)
 Salary: 1 invalid (<= 0)
 Rating: 0 invalid (outside 1-5)

 Final dataset shape: (1000, 8)
 Total rows: 1000
 Total columns: 8

8. Advanced: Custom Validation with Lambda Functions

For complex rules, you can create custom validation functions using lambda!

# Validate email format more strictly
def is_valid_email(email):
    return '@' in email and '.' in email.split('@')[1]

invalid_emails = df[~df['Email'].apply(is_valid_email)]
print(f"Invalid email format: {len(invalid_emails)}")

# Validate name has at least 2 words (first and last name)
invalid_names = df[df['Name'].str.split().str.len() < 2]
print(f"Names missing last name: {len(invalid_names)}")

# Validate phone number format (XXX-XXX-XXXX)
def is_valid_phone(phone):
    parts = str(phone).split('-')
    return len(parts) == 3 and len(parts[0]) == 3 and len(parts[1]) == 3 and len(parts[2]) == 4

invalid_phones = df[~df['Phone'].apply(is_valid_phone)]
print(f"Invalid phone format: {len(invalid_phones)}")

9. Export Validation Results

Save invalid records to a file so you can review and fix them!

# Find all invalid age records
invalid_age = df[(df['Age'] < 18) | (df['Age'] > 100)]

# Save to CSV for review
invalid_age.to_csv('invalid_ages.csv', index=False)
print(f"Saved {len(invalid_age)} invalid age records to invalid_ages.csv")

# Find all invalid emails
invalid_email = df[~df['Email'].str.contains('@', na=False)]
invalid_email.to_csv('invalid_emails.csv', index=False)

# Create a summary report
validation_summary = {
    'Check': ['Null IDs', 'Invalid Age', 'Invalid Email', 'Invalid Salary'],
    'Count': [
        df['Customer_ID'].isnull().sum(),
        len(df[(df['Age'] < 18) | (df['Age'] > 100)]),
        len(df[~df['Email'].str.contains('@', na=False)]),
        len(df[df['Salary'] <= 0])
    ]
}

summary_df = pd.DataFrame(validation_summary)
summary_df.to_csv('validation_summary.csv', index=False)
print("Validation summary saved!")

10. Best Practices for Data Validation

Validate early: Check data right after loading, before any transformations
Document rules: Keep a clear list of what makes data valid for your use case
Be specific: Don't just say "invalid" - explain WHY (e.g., "Age outside 18-100 range")
Save invalid records: Export them to CSV for manual review and correction
Automate it: Create validation functions you can reuse across projects
Test edge cases: What about very old dates? Very large numbers? Special characters?

Quick Reference: Common Validation Patterns

# Check for nulls
null_count = df['column'].isnull().sum()

# Check numeric range
invalid = df[(df['Age'] < 18) | (df['Age'] > 100)]

# Check string pattern
invalid = df[~df['Email'].str.contains('@', na=False)]

# Check valid categories
valid_list = ['A', 'B', 'C']
invalid = df[~df['Category'].isin(valid_list)]

# Check dates
invalid = df[df['Date'] > datetime.now()]

# Cross-column check
invalid = df[df['End_Date'] <= df['Start_Date']]

# Custom validation
invalid = df[~df['Column'].apply(custom_function)]

Happy Learning !! 🐼

Search This Blog

Pandas - Data validation

Sample Dataset for Practice

Why Data Validation Matters

1. Basic Null Value Validation

2. Numeric Range Validation

3. String Pattern Validation

4. Category Validation

5. Date Validation

6. Cross-Column Validation

7. Create a Complete Validation Function

8. Advanced: Custom Validation with Lambda Functions

9. Export Validation Results

10. Best Practices for Data Validation

Quick Reference: Common Validation Patterns

Labels

Comments

Post a Comment

Got a bright idea? 💡

About Me & This Blog

What I explore

Beyond technology

Explore Topics

Tutorials