Exploratory Data Analysis for Cleaning

# CHAPTER 15

Exploratory Data Analysis for Cleaning #

1. Chapter Introduction #

2. Descriptive Statistics #

The first step in EDA is generating summary statistics. This instantly reveals impossible minimums, maximums, and strange distributions.

import pandas as pd
import numpy as np

# Sample dataset
df = pd.DataFrame({
    'age': [25, 30, 35, 28, 999, 32, -5, 29], # 999 and -5 are anomalies
    'salary': [50k, 60k, 55k, 62k, 0, 58k, 65k, 1M], # 0 and 1M are anomalies
    'category': ['A', 'B', 'A', 'a', 'C', 'B', 'A ', 'A'] # 'a' and 'A ' are issues
}) # Pseudo-code representations for readability

# 1. Summarize Numeric Data
print("=== NUMERIC SUMMARY ===")
print(df.describe())
# Look at the min and max!
# If age min is -5 and max is 999, you instantly know you have dirty data.

# 2. Summarize Categorical Data
print("\n=== CATEGORICAL SUMMARY ===")
print(df.describe(include=['object']))
# Shows count, unique values, top value, and frequency.

3. Data Profiling (Value Counts) #

For categorical columns, .value_counts() is the ultimate dirt-detector.

# Checking for inconsistent text entries
print("=== VALUE COUNTS FOR CATEGORY ===")
print(df['category'].value_counts(dropna=False))
# Output might look like:
# A     3
# B     2
# a     1
# C     1
# A     1  <-- Notice the trailing space making it a separate category!

# Pro-tip: Normalize immediately after seeing this
df[&#039;category'] = df['category'].str.strip().str.upper()

4. Visualizing Data Quality #

Visualizations highlight outliers and strange distributions instantly.

import matplotlib.pyplot as plt
import seaborn as sns

# 1. Boxplots for Outlier Detection
plt.figure(figsize=(8, 4))
sns.boxplot(x=df['salary'])
plt.title("Salary Boxplot: Spotting Outliers")
plt.show()
# Boxplots visually plot the IQR. Dots outside the "whiskers" are outliers.

# 2. Histograms for Distribution and Zero-Spikes
plt.figure(figsize=(8, 4))
sns.histplot(df['age'], bins=20)
plt.title("Age Distribution")
plt.show()
# If you see a massive spike at 0 or 99, it usually means 
# missing values were hard-coded as 0 or 99 by the system.

5. Correlation Matrices for Logical Checks #

Sometimes, columns must logically correlate. For example, 'Years of Experience' and 'Age' should have a positive correlation. If they are negatively correlated, the data might be swapped or corrupted.

# Calculate correlation matrix
corr = df[['age', 'salary', 'experience']].corr()

# Visualize with a heatmap
sns.heatmap(corr, annot=True, cmap='coolwarm')
plt.title("Correlation Heatmap")
plt.show()

6. Automated EDA Tools #

Writing describe(), value_counts(), and plotting histograms for 50 columns takes hours. Modern Python developers use automated profiling libraries.

YData Profiling (formerly Pandas Profiling): *(Note: Requires installation: pip install ydata-profiling)*

from ydata_profiling import ProfileReport

# Generate a complete HTML report with 1 line of code
profile = ProfileReport(df, title="Data Quality Report")

# Save to an HTML file to view in your browser
profile.to_file("data_quality_report.html")

This generates a beautiful interactive report showing missing value matrices, histograms, correlation heatmaps, and explicit warnings (e.g., "Column X has 15% zeros", "Column Y has high cardinality").

7. Mini Project: Sales Dataset Analysis #

Scenario: You receive a raw sales dataset. Before writing any cleaning code, you perform EDA.

1. 1. You run df.describe(). You notice minimumquantity is -10. (Insight: Returns are recorded as negative quantities. Do you want to include returns in your sales total? Action: Separate returns from sales).

1. 2. You run df['state'].valuecounts(). You see 52 unique states. (Insight: There are only 50 US states. Action: Check for typos like 'TX' vs 'Texas' or territories like 'PR').

1. 3. You plot a histogram of saledate. You see a massive 3-month gap in the middle of 2023. (Insight: The tracking pixel broke on the website. Action: Do not impute this. Inform stakeholders the data is missing).

12. Next Chapter Recommendation #