Pandas makes data cleaning super easy with powerful built-in functions. This guide explains 10 essential cleaning patterns step-by-step with plenty of simple examples, so even complete beginners can follow along and start using them right away.
We'll use practical examples throughout to make it easy to understand and apply immediately.
What is Data Cleaning?
Data cleaning is the process of fixing messy, inconsistent, or incorrect data before analysis. Real-world data is often messy with extra spaces, wrong formats, duplicates, missing values, and inconsistencies. These patterns will help you handle all of these issues efficiently.
1. Remove Whitespace
Extra spaces at the beginning or end of text can cause problems when matching or sorting data. The strip() method removes them instantly.
# Remove leading and trailing whitespace
df['city'] = df['city'].str.strip()
# Before: " New York "
# After: "New York"
Real-World Use Case: You imported a customer database and names have random spaces. This will clean them up instantly!
2. Fix Case Inconsistencies
When different people enter data, you might get "JOHN DOE", "john doe", or "John Doe" all referring to the same person. Let's standardize them!
# Convert to title case (First Letter Capitalized)
df['name'] = df['name'].str.title()
# Result: "John Doe"
# Convert to uppercase (ALL CAPS)
df['country_code'] = df['country_code'].str.upper()
# Result: "USA"
# Convert to lowercase (all small)
df['email'] = df['email'].str.lower()
# Result: "user@email.com"
Example:
Input: ["alice SMITH", "BOB jones", "Charlie BROWN"]
Output: ["Alice Smith", "Bob Jones", "Charlie Brown"]
3. Extract Numbers from Strings
Sometimes prices or quantities come mixed with text like "$100" or "50kg". We need to extract just the numbers for calculations.
# Extract only digits from text
df['price_clean'] = df['price'].str.extract('(\d+)').astype(float)
# Extract decimal numbers
df['weight'] = df['weight'].str.extract('(\d+\.?\d*)').astype(float)
Example:
Input: ["$25.99", "Price: 100", "$45"]
Output: [25.99, 100.0, 45.0]
💡 Pro Tip: Always convert to float after extraction so you can do math operations!
4. Split Columns
Got full names in one column but need first and last names separately? Or maybe addresses that need to be split? Here's how!
# Split full name into first and last name
df[['first_name', 'last_name']] = df['full_name'].str.split(' ', expand=True)
# Split email into username and domain
df[['username', 'domain']] = df['email'].str.split('@', expand=True)
Example:
Input full_name: ["John Smith", "Alice Johnson"]
Output first_name: ["John", "Alice"]
Output last_name: ["Smith", "Johnson"]
5. Map Values
Converting text categories into numbers or standardizing values is super common. The map() function makes this easy!
# Convert status text to numeric codes
status_map = {
'active': 1,
'inactive': 0,
'pending': 2,
'suspended': 3
}
df['status_code'] = df['status'].map(status_map)
# Convert sizes to standardized format
size_map = {'S': 'Small', 'M': 'Medium', 'L': 'Large', 'XL': 'Extra Large'}
df['size_full'] = df['size'].map(size_map)
Real-World Use Case: Perfect for machine learning preparation where algorithms need numeric inputs instead of text!
6. Replace Multiple Values
When the same thing is written in different ways (typos, abbreviations, different spellings), you need to standardize them all.
# Standardize product categories
df['category'] = df['category'].replace({
'Electronics': 'Electronics',
'electronic': 'Electronics',
'ELEC': 'Electronics',
'Electr': 'Electronics',
'Mobile': 'Mobile Phones',
'mob': 'Mobile Phones',
'phone': 'Mobile Phones'
})
Example:
Input: ["electronic", "ELEC", "Electronics", "phone"]
Output: ["Electronics", "Electronics", "Electronics", "Mobile Phones"]
7. Remove Special Characters
Clean up text by removing unwanted symbols, keeping only letters, numbers, and spaces. Great for text analysis!
# Keep only letters, numbers, and spaces
df['clean_text'] = df['text'].str.replace('[^a-zA-Z0-9\s]', '', regex=True)
# Remove all special characters including spaces
df['alphanumeric'] = df['text'].str.replace('[^a-zA-Z0-9]', '', regex=True)
Example:
Input: "Hello! @User #Python2024 $100"
Output: "Hello User Python2024 100"
💡 Pro Tip: This is super useful for cleaning social media data or user comments!
8. Handle Missing Values
Missing data (NaN) is everywhere in real datasets. Here's how to fill, drop, or replace them intelligently.
# Fill missing values with a specific value
df['age'].fillna(0, inplace=True)
# Fill with the average (mean)
df['salary'].fillna(df['salary'].mean(), inplace=True)
# Fill with the most common value (mode)
df['city'].fillna(df['city'].mode()[0], inplace=True)
# Forward fill - use previous valid value
df['temperature'].fillna(method='ffill', inplace=True)
Real-World Use Case: When analyzing sales data, fill missing quantities with 0 or use average prices for missing values.
9. Remove Duplicates
Duplicate rows can skew your analysis. Let's identify and remove them based on specific columns or entire rows.
# Remove duplicate rows (all columns must match)
df_clean = df.drop_duplicates()
# Remove duplicates based on specific columns
df_clean = df.drop_duplicates(subset=['email', 'phone'])
# Keep the last occurrence instead of first
df_clean = df.drop_duplicates(subset=['customer_id'], keep='last')
# Just identify duplicates without removing
duplicates = df[df.duplicated(subset=['email'])]
Example:
Before: 1000 rows with 50 duplicate customer records
After: 950 unique customer records
10. Handle Infinite Values
Sometimes calculations produce infinite values (inf or -inf). These can break your analysis, so let's handle them!
# Import numpy for infinite value handling
import numpy as np
# Replace infinite values with NaN
df.replace([np.inf, -np.inf], np.nan, inplace=True)
# Replace infinite values with a specific number
df['ratio'] = df['ratio'].replace([np.inf, -np.inf], 0)
# Check if any infinite values exist
has_inf = df.isin([np.inf, -np.inf]).any().any()
Real-World Use Case: When calculating ratios or percentages, division by zero creates infinite values. Clean them before analysis!
Common Parameters You'll Use
inplace=True: Modify the DataFrame directly without creating a copyregex=True: Enable regular expressions for pattern matchingexpand=True: Expand split results into separate columnsastype(): Convert data types (int, float, str)
Quick Reference Summary
# Pattern 1: Remove whitespace
df['column'] = df['column'].str.strip()
# Pattern 2: Fix case inconsistencies
df['name'] = df['name'].str.title() # John Doe
df['name'] = df['name'].str.upper() # JOHN DOE
df['name'] = df['name'].str.lower() # john doe
# Pattern 3: Extract numbers from strings
df['price'] = df['price'].str.extract('(\d+)').astype(float)
# Pattern 4: Split columns
df[['first_name', 'last_name']] = df['full_name'].str.split(' ', expand=True)
# Pattern 5: Map values
status_map = {'active': 1, 'inactive': 0, 'pending': 2}
df['status_code'] = df['status'].map(status_map)
# Pattern 6: Replace multiple values
df['category'] = df['category'].replace({
'Electronics': 'Electronics',
'electronic': 'Electronics',
'ELEC': 'Electronics'
})
# Pattern 7: Remove special characters
df['text'] = df['text'].str.replace('[^a-zA-Z0-9\s]', '', regex=True)
# Pattern 8: Handle missing values
df['column'].fillna(0, inplace=True)
df['column'].fillna(df['column'].mean(), inplace=True)
# Pattern 9: Remove duplicates
df_clean = df.drop_duplicates()
df_clean = df.drop_duplicates(subset=['email'])
# Pattern 10: Handle infinite values
import numpy as np
df.replace([np.inf, -np.inf], np.nan, inplace=True)
Happy Learning !! 🐼
Comments
Post a Comment