Cleaning Data

By the end of this lesson, you will be able to identify and remove common data quality issues in Python using pandas, ensuring your dataset is ready for analysis.

What it is

Data cleaning is the process of detecting and correcting (or removing) corrupt or inaccurate records from a dataset. In Python, the pandas library provides powerful tools for this task. The mental model involves treating raw data as "dirty" and applying specific filters or transformations to make it "clean." Key concepts include handling missing values (NaN), removing duplicates, and standardizing formats.

Why it matters

• Accuracy: Dirty data leads to incorrect statistical conclusions and machine learning predictions.
• Efficiency: Clean datasets are smaller and faster to process.
• Consistency: Standardized data ensures that operations like grouping or merging work correctly across different sources.
• Reliability: Automated pipelines fail less often when input data is validated and cleaned upfront.

Syntax or steps

The most common workflow involves three steps: inspecting for nulls, dropping rows with critical missing data, and removing duplicates. Use df.isnull().sum() to count missing values per column. Use df.dropna() to remove rows containing any NaN values. Use df.drop_duplicates() to remove identical rows.

Example

import pandas as pd
import numpy as np

# Create a messy DataFrame
data = {
    'Name': ['Alice', 'Bob', 'Charlie', 'Alice', None],
    'Age': [25, 30, np.nan, 25, 40],
    'City': ['New York', 'London', 'Paris', 'New York', 'Berlin']
}
df = pd.DataFrame(data)

print("Original Data:")
print(df)

# Step 1: Remove rows where Age is missing
df_clean = df.dropna(subset=['Age'])

# Step 2: Remove duplicate rows based on Name and City
df_clean = df_clean.drop_duplicates(subset=['Name', 'City'])

print("\nCleaned Data:")
print(df_clean)

This code first creates a DataFrame with missing values and duplicates. It then uses dropna(subset=['Age']) to keep only rows where the 'Age' column has data. Finally, drop_duplicates(subset=['Name', 'City']) removes redundant entries, leaving a unique, complete dataset.

Common mistakes

• Dropping all NaNs blindly: Using df.dropna() without arguments removes any row with any missing value, which might delete useful data. Always specify subset if possible.
• Ignoring case sensitivity: When cleaning text columns, 'apple' and 'Apple' are treated as different. Use .str.lower() before deduplication if needed.
• Not checking for empty strings: Pandas treats "" as valid data, not NaN. Replace empty strings with np.nan using replace('', np.nan) before dropping.
• Forgetting to assign results: Methods like dropna() return a new DataFrame by default. You must assign it back to a variable (e.g., df = df.dropna()) unless you use inplace=True.

When to use it

Cleaning is essential before any analysis. Compare two main strategies for missing data:

Practice

Guided Exercise: Load a CSV file into a DataFrame. Check how many missing values exist in each column. Drop rows where the 'Price' column is missing.

Challenge: Take a DataFrame with mixed-case names. Convert all names to lowercase, then remove duplicates. Hint: Use df['Name'].str.lower() followed by drop_duplicates().

Quick check

Question: What does df.dropna(how='all') do?

Answer: It drops rows only if all values in that row are NaN, preserving rows with at least one valid entry.

Summary

Data cleaning transforms raw, unreliable inputs into structured, trustworthy datasets. Mastering dropna() and drop_duplicates() allows you to quickly eliminate noise, forming the foundation of robust data science workflows.