How to Clean Messy Data in Python: Data Cleaning Guide

How to Clean Messy Data in Python: Data Cleaning Guide

If you're working with messy data in Python, you need methods to clean, standardize, and prepare it for analysis. 73% of data scientists spend 60% of their time cleaning data before analysis.

By the end of this guide, you'll know how to clean messy data in Python—using pandas to handle missing values, remove duplicates, standardize formats, and transform data efficiently.

Quick Summary

• Load data with pandas - Read CSV, Excel, and other formats
• Handle missing values - Remove, fill, or impute missing data
• Remove duplicates - Eliminate duplicate rows and records
• Standardize formats - Normalize dates, numbers, and text

Common Messy Data Problems

1. Missing values - NaN, None, empty strings, "N/A"
2. Duplicate rows - Same data repeated multiple times
3. Inconsistent formats - Mixed date formats, number formats
4. Text inconsistencies - Mixed case, extra spaces, special characters
5. Data type issues - Numbers as strings, dates as strings
6. Outliers - Extreme values that skew analysis
7. Inconsistent categories - "Electronics", "Electronic", "Elec" for same category
8. Encoding issues - Weird characters from wrong encoding
9. Structural problems - Wrong column names, misaligned data
10. Mixed data types - Same column with different types

Step-by-Step: Clean Messy Data in Python

Step 1: Load Data with Pandas

Import data into pandas DataFrame for cleaning.

Install Pandas

Install pandas:

pip install pandas

Or with conda:

conda install pandas

Load CSV File

Read CSV:

import pandas as pd

# Load CSV file
df = pd.read_csv('data.csv')

# Display first few rows
print(df.head())

With options:

# Load with specific encoding
df = pd.read_csv('data.csv', encoding='utf-8')

# Load with specific delimiter
df = pd.read_csv('data.csv', delimiter=';')

# Load with header row
df = pd.read_csv('data.csv', header=0)

# Load without header
df = pd.read_csv('data.csv', header=None)

Load Excel File

Read Excel:

# Load Excel file
df = pd.read_excel('data.xlsx')

# Load specific sheet
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

Note: Requires openpyxl or xlrd for Excel files.

---

Step 2: Explore Data

Understand data structure and identify issues.

Basic Information

Data overview:

# Shape (rows, columns)
print(df.shape)

# Column names
print(df.columns)

# Data types
print(df.dtypes)

# Basic statistics
print(df.describe())

# Info about data
print(df.info())

Check for Issues

Identify problems:

# Missing values
print(df.isnull().sum())

# Duplicate rows
print(df.duplicated().sum())

# Unique values in column
print(df['column_name'].unique())

# Value counts
print(df['column_name'].value_counts())

---

Step 3: Handle Missing Values

Deal with NaN, None, and empty values.

Identify Missing Values

Count missing values:

# Total missing per column
missing = df.isnull().sum()
print(missing)

# Percentage missing
missing_pct = (df.isnull().sum() / len(df)) * 100
print(missing_pct)

Remove Missing Values

Drop rows with missing values:

# Drop rows with any missing values
df_clean = df.dropna()

# Drop rows where all values are missing
df_clean = df.dropna(how='all')

# Drop rows with missing in specific column
df_clean = df.dropna(subset=['column_name'])

Fill Missing Values

Fill with constant:

# Fill with 0
df['column_name'].fillna(0, inplace=True)

# Fill with string
df['column_name'].fillna('Unknown', inplace=True)

# Fill with mean
df['column_name'].fillna(df['column_name'].mean(), inplace=True)

# Fill with median
df['column_name'].fillna(df['column_name'].median(), inplace=True)

# Fill with mode
df['column_name'].fillna(df['column_name'].mode()[0], inplace=True)

Forward fill / Backward fill:

# Forward fill (use previous value)
df['column_name'].fillna(method='ffill', inplace=True)

# Backward fill (use next value)
df['column_name'].fillna(method='bfill', inplace=True)

---

Step 4: Remove Duplicates

Eliminate duplicate rows.

Find Duplicates

Identify duplicates:

# Check for duplicates
duplicates = df.duplicated()
print(duplicates.sum())

# Show duplicate rows
print(df[df.duplicated()])

Remove Duplicates

Drop duplicates:

# Remove all duplicates (keep first)
df_clean = df.drop_duplicates()

# Remove duplicates, keep last
df_clean = df.drop_duplicates(keep='last')

# Remove duplicates based on specific columns
df_clean = df.drop_duplicates(subset=['column1', 'column2'])

# Remove all duplicates (keep none)
df_clean = df.drop_duplicates(keep=False)

---

Step 5: Standardize Text Data

Clean and normalize text columns.

Remove Extra Spaces

Trim whitespace:

# Remove leading/trailing spaces
df['column_name'] = df['column_name'].str.strip()

# Remove all extra spaces
df['column_name'] = df['column_name'].str.replace('  ', ' ')

Standardize Case

Convert case:

# Convert to lowercase
df['column_name'] = df['column_name'].str.lower()

# Convert to uppercase
df['column_name'] = df['column_name'].str.upper()

# Convert to title case
df['column_name'] = df['column_name'].str.title()

Remove Special Characters

Clean text:

# Remove special characters (keep alphanumeric and spaces)
df['column_name'] = df['column_name'].str.replace(r'[^a-zA-Z0-9\s]', '', regex=True)

# Remove specific characters
df['column_name'] = df['column_name'].str.replace('#', '')
df['column_name'] = df['column_name'].str.replace('@', '')

---

Step 6: Standardize Date Formats

Convert and normalize dates.

Convert to DateTime

Parse dates:

# Convert to datetime
df['date_column'] = pd.to_datetime(df['date_column'])

# Convert with specific format
df['date_column'] = pd.to_datetime(df['date_column'], format='%Y-%m-%d')

# Handle multiple formats
df['date_column'] = pd.to_datetime(df['date_column'], infer_datetime_format=True)

# Handle errors
df['date_column'] = pd.to_datetime(df['date_column'], errors='coerce')

Format Dates

Standardize format:

# Format as string
df['date_column'] = df['date_column'].dt.strftime('%Y-%m-%d')

# Extract components
df['year'] = df['date_column'].dt.year
df['month'] = df['date_column'].dt.month
df['day'] = df['date_column'].dt.day

---

Step 7: Standardize Number Formats

Clean and normalize numeric data.

Convert to Numbers

Convert text to numeric:

# Convert to numeric
df['column_name'] = pd.to_numeric(df['column_name'])

# Convert with error handling
df['column_name'] = pd.to_numeric(df['column_name'], errors='coerce')

# Remove non-numeric characters
df['column_name'] = df['column_name'].str.replace(r'[^0-9.]', '', regex=True)
df['column_name'] = pd.to_numeric(df['column_name'], errors='coerce')

Remove Currency Symbols

Clean currency:

# Remove $ and commas
df['price'] = df['price'].str.replace('$', '').str.replace(',', '')
df['price'] = pd.to_numeric(df['price'], errors='coerce')

Round Numbers

Standardize decimals:

# Round to 2 decimal places
df['column_name'] = df['column_name'].round(2)

# Round to nearest integer
df['column_name'] = df['column_name'].round(0).astype(int)

---

Step 8: Normalize Categories

Standardize categorical data.

Map Categories

Create mapping:

# Define mapping
category_map = {
    'Electronics': 'Electronics',
    'Electronic': 'Electronics',
    'Elec': 'Electronics',
    'E-Products': 'Electronics'
}

# Apply mapping
df['category'] = df['category'].map(category_map)

Replace Values

Replace specific values:

# Replace values
df['category'] = df['category'].replace('Electronic', 'Electronics')
df['category'] = df['category'].replace(['Elec', 'E-Products'], 'Electronics')

---

Step 9: Handle Outliers

Identify and handle extreme values.

Detect Outliers

Using IQR method:

# Calculate IQR
Q1 = df['column_name'].quantile(0.25)
Q3 = df['column_name'].quantile(0.75)
IQR = Q3 - Q1

# Define outliers
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Find outliers
outliers = df[(df['column_name'] < lower_bound) | (df['column_name'] > upper_bound)]
print(outliers)

Remove Outliers

Drop outliers:

# Remove outliers
df_clean = df[(df['column_name'] >= lower_bound) & (df['column_name'] <= upper_bound)]

Cap outliers:

# Cap at bounds
df['column_name'] = df['column_name'].clip(lower=lower_bound, upper=upper_bound)

---

Step 10: Save Cleaned Data

Export cleaned data to file.

Save to CSV

Export CSV:

# Save to CSV
df.to_csv('cleaned_data.csv', index=False)

# Save with encoding
df.to_csv('cleaned_data.csv', index=False, encoding='utf-8')

Save to Excel

Export Excel:

# Save to Excel
df.to_excel('cleaned_data.xlsx', index=False)

# Save to specific sheet
df.to_excel('cleaned_data.xlsx', sheet_name='Cleaned', index=False)

---

Real Example: Cleaning Messy Data

Before (Messy Data):

import pandas as pd

# Load messy data
df = pd.read_csv('messy_data.csv')
print(df.head())

Issues:

• Missing values (NaN, "N/A")
• Duplicate rows
• Inconsistent text case
• Mixed date formats
• Numbers as strings
• Extra spaces

After (Cleaned Data):

# Clean data
df_clean = df.copy()

# Remove duplicates
df_clean = df_clean.drop_duplicates()

# Handle missing values
df_clean['price'].fillna(df_clean['price'].mean(), inplace=True)
df_clean['category'].fillna('Unknown', inplace=True)

# Standardize text
df_clean['product'] = df_clean['product'].str.strip().str.title()

# Convert dates
df_clean['date'] = pd.to_datetime(df_clean['date'], errors='coerce')

# Convert numbers
df_clean['price'] = pd.to_numeric(df_clean['price'], errors='coerce')

# Normalize categories
category_map = {'Elec': 'Electronics', 'Electronic': 'Electronics'}
df_clean['category'] = df_clean['category'].map(category_map).fillna(df_clean['category'])

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

Result:

• No duplicates
• Missing values handled
• Consistent formats
• Ready for analysis

---

Cleaning Checklist

Use this checklist when cleaning data in Python:

• Loaded data with pandas
• Explored data structure
• Identified missing values
• Handled missing values (remove/fill)
• Removed duplicates
• Standardized text (trim, case)
• Converted dates to datetime
• Converted numbers to numeric
• Normalized categories
• Handled outliers (if needed)
• Verified cleaned data
• Saved cleaned data

---

Mini Automation Using RowTidy

You can clean messy data automatically using RowTidy's intelligent cleaning, then use Python for advanced analysis.

The Problem:
Cleaning messy data in Python requires:

• Writing code for each cleaning step
• Handling edge cases
• Testing and debugging
• Time-consuming process

The Solution:
Use RowTidy for initial cleaning, then Python for analysis:

1. Upload messy file - Drag and drop to RowTidy
2. AI cleans data - Removes duplicates, standardizes formats
3. Download clean file - Get cleaned CSV
4. Load in Python - Use pandas for advanced analysis

Workflow:

• RowTidy: Quick cleaning (duplicates, formats, standardization)
• Python: Advanced analysis (statistics, machine learning, visualization)

Time saved: 2 hours Python cleaning → 5 minutes RowTidy + Python analysis

Use RowTidy for initial cleaning, then Python for advanced data science work. Try RowTidy's data cleaning →

---

FAQ

1. How do I clean messy data in Python?

Use pandas to load data, handle missing values (drop/fill), remove duplicates, standardize text/number/date formats, normalize categories, then save cleaned data.

2. What's the best library for data cleaning in Python?

Pandas is the standard library for data cleaning. It provides functions for handling missing values, duplicates, formatting, and transformations.

3. How do I handle missing values in pandas?

Use dropna() to remove, fillna() to fill with values (mean, median, mode, constant), or interpolate() for time series data.

4. How do I remove duplicates in pandas?

Use drop_duplicates() to remove duplicate rows. Can specify columns to check and which duplicate to keep (first, last, none).

5. How do I standardize text in pandas?

Use string methods: str.strip() for spaces, str.lower()/str.upper() for case, str.replace() for characters.

6. How do I convert dates in pandas?

Use pd.to_datetime() to convert to datetime. Can specify format or let pandas infer. Use errors='coerce' to handle invalid dates.

7. How do I convert text numbers to numeric in pandas?

Use pd.to_numeric() to convert. Use errors='coerce' to convert invalid values to NaN. Remove non-numeric characters first if needed.

8. Can I automate data cleaning in Python?

Yes. Create functions for common cleaning tasks, use pipelines, or use libraries like pyjanitor for advanced cleaning workflows.

9. Should I use RowTidy or Python for data cleaning?

Use RowTidy for quick, automated cleaning (duplicates, formats, standardization). Use Python for advanced cleaning, custom logic, or when you need programmatic control.

10. How long does it take to clean data in Python?

Depends on data size and complexity: small files (1000 rows) = 10-30 minutes, medium (10,000 rows) = 30-60 minutes, large (100,000+ rows) = 1-3 hours. RowTidy cleans in minutes.

---

Related Guides

• How to Clean Messy Dataset →
• 5 Steps in Data Cleansing →
• How to Prepare Data for Analysis →
• Excel Data Cleaning Best Practices →

---

Conclusion

Cleaning messy data in Python requires using pandas to handle missing values, remove duplicates, standardize formats (text, dates, numbers), normalize categories, and handle outliers. Use RowTidy for initial automated cleaning, then Python for advanced data science and analysis.

Try RowTidy — quickly clean messy data, then use Python for advanced analysis and machine learning.