Python - Data Cleaning in Pandas

Data Cleaning in Pandas - Python

Data cleaning is an essential part of data preprocessing. Before performing any analysis or building machine learning models, it is important to clean and transform the data to ensure accuracy, consistency, and completeness. In Python, the Pandas library provides powerful tools for handling missing values, correcting data types, removing duplicates, renaming columns, and more. This guide covers the core techniques and methods used for data cleaning in Pandas with detailed explanations and examples.

Introduction to Data Cleaning

Data cleaning refers to identifying and correcting (or removing) corrupt or inaccurate records from a dataset. Common issues include:

• Missing data
• Incorrect data types
• Duplicate records
• Inconsistent formatting
• Outliers and anomalies

Pandas offers a high-level abstraction for managing tabular data using the DataFrame structure, which makes it easy to identify and fix these problems.

Loading Data with Pandas

Using read_csv

import pandas as pd

# Load a dataset
df = pd.read_csv('data.csv')

# Preview the first few rows
print(df.head())

After loading the data, we can begin the data cleaning process.

Checking and Handling Missing Data

Detecting Missing Values

Missing values are typically represented as NaN (Not a Number) in Pandas. To detect them:

# Detect missing values
print(df.isnull())

# Count total missing values in each column
print(df.isnull().sum())

Dropping Missing Values

Use dropna() to remove rows or columns with missing data.

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

# Drop columns with all missing values
df_cleaned = df.dropna(axis=1, how='all')

Filling Missing Values

You can fill missing values using a fixed value or a method like forward-fill or backward-fill:

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

# Forward fill
df.fillna(method='ffill', inplace=True)

# Backward fill
df.fillna(method='bfill', inplace=True)

Filling with Mean/Median/Mode

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

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

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

Fixing Incorrect Data Types

Checking Data Types

# Check data types
print(df.dtypes)

Converting Data Types

# Convert string to numeric
df['Salary'] = pd.to_numeric(df['Salary'], errors='coerce')

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

# Convert to category
df['Department'] = df['Department'].astype('category')

Removing Duplicates

Detecting Duplicates

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

# Count duplicates
print(df.duplicated().sum())

Removing Duplicates

# Remove duplicate rows
df = df.drop_duplicates()

Renaming Columns

View Column Names

print(df.columns)

Rename Columns

# Rename specific columns
df.rename(columns={'EmpName': 'Employee_Name', 'Dept': 'Department'}, inplace=True)

# Rename all columns (e.g., make lowercase)
df.columns = [col.lower() for col in df.columns]

Handling Outliers

Using Statistical Methods

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

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

# Filter out outliers
df_no_outliers = df[(df['Salary'] >= lower_bound) & (df['Salary'] <= upper_bound)]

Using Z-Score

from scipy import stats
import numpy as np

# Z-score method
z_scores = np.abs(stats.zscore(df[['Salary']]))
df = df[(z_scores < 3).all(axis=1)]

Replacing Values

Using replace()

# Replace specific values
df['Gender'].replace({'M': 'Male', 'F': 'Female'}, inplace=True)

# Replace using regex
df['Email'] = df['Email'].replace(r'\.com$', '.org', regex=True)

String Cleaning

Trimming Whitespace

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

Changing Case

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

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

Replacing Substrings

# Replace substrings
df['Address'] = df['Address'].str.replace('Street', 'St')

Working with Categorical Data

Inspect Categories

# Convert to category and inspect
df['Department'] = df['Department'].astype('category')
print(df['Department'].cat.categories)

Renaming Categories

# Rename categories
df['Department'].cat.rename_categories({'HR': 'Human Resources', 'IT': 'Tech'}, inplace=True)

Parsing Dates and Times

Convert to Datetime

df['JoinDate'] = pd.to_datetime(df['JoinDate'], errors='coerce')

Extract Components

# Extract year, month, day
df['Year'] = df['JoinDate'].dt.year
df['Month'] = df['JoinDate'].dt.month
df['Day'] = df['JoinDate'].dt.day

Creating New Columns for Cleaning

Splitting Strings

# Split name column into first and last names
df[['FirstName', 'LastName']] = df['Name'].str.split(' ', 1, expand=True)

Applying Custom Functions

# Define function to standardize salary
def clean_salary(salary):
    if pd.isna(salary):
        return 0
    return float(salary.replace('$', '').replace(',', ''))

df['Salary'] = df['Salary'].apply(clean_salary)

Saving Cleaned Data

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

Comprehensive Cleaning Example

import pandas as pd

# Load raw data
df = pd.read_csv('employees_raw.csv')

# Drop duplicates
df = df.drop_duplicates()

# Strip whitespaces
df['Name'] = df['Name'].str.strip()

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

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

# Handle incorrect genders
df['Gender'] = df['Gender'].replace({'M': 'Male', 'F': 'Female', 'male': 'Male', 'female': 'Female'})

# Remove outliers in Salary
Q1 = df['Salary'].quantile(0.25)
Q3 = df['Salary'].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR
df = df[(df['Salary'] >= lower) & (df['Salary'] <= upper)]

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

Data cleaning is a crucial skill in data science and analytics. Pandas offers comprehensive tools for handling missing values, fixing data types, removing duplicates, parsing dates, cleaning text, handling outliers, and more. Clean data ensures accurate, reliable analysis and modeling. Mastering data cleaning with Pandas equips you to tackle real-world datasets with confidence and efficiency.