Data Cleaning with Pandas

# CHAPTER 15

Data Cleaning with Pandas #

1. Chapter Introduction #

2. Identifying Missing Data #

In Pandas, missing data is represented as NaN (Not a Number). You must find where these NaNs are before you can fix them.

import pandas as pd
import numpy as np

# A messy dataset
data = {
    "Name": ["Alice", "Bob", "Charlie", "Dave", "Emma"],
    "Age": [25, np.nan, 35, 40, 25], # Bob is missing an age
    "Salary": [50000, 60000, np.nan, 80000, 50000]
}
df = pd.DataFrame(data)

# 1. Returns a True/False table showing where NaNs are
print(df.isna())

# 2. Returns a count of NaNs per column (CRITICAL FUNCTION)
print(df.isna().sum())

3. Handling Missing Data (Dropping vs. Filling) #

You have two choices when dealing with missing data: delete the row entirely, or guess/fill in the missing value.

Option 1: Dropping Rows (dropna) Use this if the dataset is massive and the missing row is useless.

# Drops ANY row that contains at least one NaN
clean_df = df.dropna()

Option 2: Filling Values (fillna) Use this if you don't want to lose data. A common technique is to fill missing numbers with the column's average (mean).

# Calculate the average age
avg_age = df['Age'].mean()

# Fill missing ages with the average
df['Age'] = df['Age'].fillna(avg_age)

# Fill missing salaries with a static number (0)
df['Salary'] = df['Salary'].fillna(0)

4. Handling Duplicates #

Sometimes databases record the exact same transaction twice.

# Check for duplicate rows
print(df.duplicated().sum())

# Drop the duplicates (keeps the first occurrence, deletes the rest)
df = df.drop_duplicates()

5. String Manipulation for Cleaning #

If a "Price" column contains $1,000, Pandas will treat it as a String (Text). You cannot calculate the average of a string. We must clean the text and convert the column type using .str accessor.

messy_prices = pd.Series(["$1,200.50", "$400.00", "Free"])

# 1. Use .str.replace to remove $ and commas
clean_prices = messy_prices.str.replace('$', '').str.replace(',', '')

# 2. Handle the edge case ("Free" -> "0")
clean_prices = clean_prices.replace("Free", "0")

# 3. Cast the column from String to Float using .astype()
final_prices = clean_prices.astype(float)

print(final_prices.mean()) # Now we can do math!

6. The inplace=True Parameter #

By default, functions like dropna() return a *new* copy of the DataFrame and leave the original alone. If you want to modify the original DataFrame directly to save memory, use inplace=True.

# Modifies 'df' directly. No need to write df = df.dropna()
df.dropna(inplace=True) 

7. Mini Project: Customer Data Cleaner #

# Raw messy data
raw = pd.DataFrame({
    "CustomerID": [1, 2, 2, 4], # Duplicate ID 2
    "Email": ["a@test.com", "b@test.com", "b@test.com", None], # Missing email
    "SignUp": ["2023-01-01", "2023/02/01", "2023/02/01", "Jan 5th 2023"] # Messy dates
})

# 1. Drop duplicates
clean = raw.drop_duplicates()

# 2. Fill missing emails with a placeholder
clean['Email'] = clean['Email'].fillna("unknown@email.com")

# 3. Standardize dates using pd.to_datetime (Pandas magic!)
clean['SignUp'] = pd.to_datetime(clean['SignUp'])

print(clean)

8. Common Mistakes #

• Forgetting to reassign or use inplace=True: If you run df.dropna() but then print df, the NaNs will still be there. You must do df = df.dropna() or df.dropna(inplace=True).

• Filling with the wrong data type: If an Age column is numeric, and you do df['Age'].fillna("Unknown"), you have just mixed numbers and strings in the same column. Any future math operations on that column will crash.

9. MCQs #

10. Interview Questions #

• Q: Explain the pros and cons of using .dropna() vs .fillna() when dealing with missing values.

• Q: You import a CSV where the "Revenue" column looks like "$1,500". Write the workflow to convert this to a usable float for analysis.

11. Summary #

12. Next Chapter Recommendation #