Important Operations for Exploratory Data Analysis

Calculating read time…

📌 Sticky Note: To follow along with this tutorial, download the Marketing Campaign Dataset from Kaggle. You can access it directly here:
https://www.kaggle.com/datasets/rodsaldanha/arketing-campaign

Complete Data Manipulation Guide for EDA Beginners

Data manipulation is the foundation of Exploratory Data Analysis (EDA). It's the process of cleaning, organizing, and transforming raw data into a structured format suitable for analysis. Mastering these essential operations will help you uncover insights, identify patterns, and prepare your data for machine learning models.

In this comprehensive guide, we'll cover 11 essential data manipulation techniques using Python's pandas library. We'll use the Marketing Campaign dataset throughout our examples, ensuring you get practical, hands-on experience.

💡 Before We Begin:

Make sure you have pandas and numpy installed: pip install pandas numpy
Download the dataset from the Kaggle link above
This guide assumes basic Python knowledge but explains everything step-by-step

Loading the Dataset

import pandas as pd
import numpy as np

# Load the dataset - note the tab separator
df = pd.read_csv('marketing_campaign.csv', sep='\t')

print("Dataset Information:")
print(f"Shape: {df.shape}")  # (rows, columns)
print(f"Columns: {df.columns.tolist()}")
print(f"\nSample Data:")
print(df.head(3))

1. Grouping Data with groupby()

Grouping allows you to split your data into groups based on categories and perform calculations on each group separately. This is essential for understanding patterns across different segments of your data.

Why Grouping Matters

Compare metrics across different categories (e.g., income by education level)
Analyze patterns within specific segments
Calculate aggregate statistics for different groups

Basic Grouping Operations

# Group by a single column and calculate mean
avg_income_by_education = df.groupby('Education')['Income'].mean().round(2)
print("Average Income by Education Level:")
print(avg_income_by_education)

# Group by multiple columns
education_marital_stats = df.groupby(['Education', 'Marital_Status']).agg({
    'Income': 'mean',
    'MntWines': 'sum',
    'ID': 'count'
}).round(2)
print("\nStatistics by Education and Marital Status:")
print(education_marital_stats.head(8))

Multiple Aggregation Methods

# Apply multiple aggregation functions
detailed_stats = df.groupby('Education').agg({
    'Income': ['mean', 'median', 'std', 'min', 'max', 'count'],
    'MntWines': 'sum',
    'Response': 'mean'
}).round(2)

print("Detailed Education Group Analysis:")
print(detailed_stats)

✅ DOs for Grouping:

Check for missing values in grouping columns
Use .reset_index() to convert groupby results back to DataFrame
Sort groups for better readability
Consider memory usage when grouping large datasets

❌ DON'Ts for Grouping:

Group by columns with too many unique values (it creates too many groups)
Forget to handle categorical variables appropriately
Ignore the impact of outliers on group statistics

2. Appending Data

Appending means adding new rows to an existing DataFrame. This is crucial when you collect new data over time that needs to be combined with existing data.

When to Use Appending

Adding new records to a customer database
Combining monthly or quarterly data
Merging data from different sources with the same structure

Appending in Practice

# Create new customer records
new_customers = pd.DataFrame({
    'ID': [9991, 9992, 9993],
    'Year_Birth': [1990, 1985, 1978],
    'Education': ['PhD', 'Master', 'Graduation'],
    'Marital_Status': ['Single', 'Married', 'Divorced'],
    'Income': [72000, 58000, 49000],
    'MntWines': [350, 210, 180],
    'Response': [1, 0, 1]
})

# Method 1: Using concat (recommended)
df_appended = pd.concat([df, new_customers], ignore_index=True)

print(f"Original shape: {df.shape}")
print(f"After appending: {df_appended.shape}")
print(f"\nLast 3 rows (new customers):")
print(df_appended.tail(3))

📝 Important Note:

pd.concat() is preferred over the older .append() method because:

It's faster, especially for large DataFrames
It's more flexible (can concatenate multiple DataFrames at once)
The .append() method is deprecated in recent pandas versions

3. Concatenating Data

Concatenation joins DataFrames along rows or columns. Unlike appending, concatenation can handle DataFrames with different structures.

Vertical vs Horizontal Concatenation

# Split the dataset
df_part1 = df.iloc[:100]  # First 100 rows
df_part2 = df.iloc[100:200]  # Next 100 rows

# Vertical concatenation (axis=0)
df_vertical = pd.concat([df_part1, df_part2], axis=0)
print(f"Vertical concatenation shape: {df_vertical.shape}")

# Create separate DataFrames for different information
customer_info = df[['ID', 'Education', 'Marital_Status']].head(10)
spending_info = df[['ID', 'MntWines', 'MntFruits']].head(10)

# Horizontal concatenation (axis=1)
df_horizontal = pd.concat([customer_info, spending_info.drop('ID', axis=1)], axis=1)
print(f"\nHorizontal concatenation shape: {df_horizontal.shape}")
print("\nResult:")
print(df_horizontal.head())

✅ DOs for Concatenation:

Check column alignment before horizontal concatenation
Use ignore_index=True when you want new indices
Consider using keys to track source DataFrames
Verify row counts after concatenation

❌ DON'Ts for Concatenation:

Concatenate DataFrames with mismatched columns without checking
Forget to handle duplicate indices
Use concatenation when merging would be more appropriate

4. Merging Data

Merging combines DataFrames based on common columns, similar to SQL JOIN operations. This is essential when you have related data in separate tables.

Types of Merges

# Create two related DataFrames
demographics = df[['ID', 'Education', 'Marital_Status', 'Income']].head(100)
purchases = df[['ID', 'MntWines', 'MntFruits', 'Dt_Customer']].head(80)

print("DataFrame shapes:")
print(f"Demographics: {demographics.shape}")
print(f"Purchases: {purchases.shape}")

# Inner Merge (default) - only matching rows
inner_merge = pd.merge(demographics, purchases, on='ID', how='inner')
print(f"\nInner merge shape: {inner_merge.shape}")

# Left Merge - keep all from left DataFrame
left_merge = pd.merge(demographics, purchases, on='ID', how='left')
print(f"Left merge shape: {left_merge.shape}")
print(f"Missing purchase data: {left_merge['MntWines'].isnull().sum()} rows")

Merging with Different Column Names

# Create DataFrames with different ID column names
df1 = df[['ID', 'Education', 'Income']].head(50).rename(columns={'ID': 'Customer_ID'})
df2 = df[['ID', 'MntWines', 'Response']].head(60).rename(columns={'ID': 'Client_ID'})

# Merge with different column names
merge_diff_names = pd.merge(df1, df2, left_on='Customer_ID', right_on='Client_ID')
print("Merge with different column names:")
print(f"Result shape: {merge_diff_names.shape}")
print(merge_diff_names.head())

💡 Merge Performance Tips:

Use pd.merge() instead of df.merge() for better readability
For large datasets, consider setting validate parameter to check merge assumptions
Use indicator=True to see where rows came from
Sort DataFrames before merging if you need sorted results

5. Sorting Data

Sorting organizes your data in a specific order, making it easier to analyze, visualize, and understand.

Basic Sorting Operations

# Sort by single column
df_sorted_income = df.sort_values('Income', ascending=False)
print("Top 5 Highest Income Customers:")
print(df_sorted_income[['ID', 'Education', 'Income']].head(5))

# Sort by multiple columns
df_multi_sorted = df.sort_values(['Education', 'Income'], ascending=[True, False])
print("\n\nTop Earner in Each Education Group:")
for education in df['Education'].unique():
    top_earner = df_multi_sorted[df_multi_sorted['Education'] == education].iloc[0]
    print(f"{education}: ID {top_earner['ID']}, Income ${top_earner['Income']:,.0f}")

✅ DOs for Sorting:

Sort before calculating running totals or cumulative sums
Use inplace=True if you want to modify the original DataFrame
Consider stability (pandas sort is stable by default)
Sort before exporting to CSV for better readability

❌ DON'Ts for Sorting:

Sort unnecessarily large datasets if you only need top N rows
Forget to reset index after sorting if you need sequential indices
Sort string columns without considering case sensitivity

6. Categorizing Data

Categorizing converts continuous variables into discrete categories, making patterns easier to identify and analyze.

Creating Categories with pd.cut()

# Create income categories
income_bins = [0, 30000, 50000, 75000, 100000, float('inf')]
income_labels = ['Very Low', 'Low', 'Medium', 'High', 'Very High']

df['Income_Category'] = pd.cut(df['Income'], bins=income_bins, labels=income_labels)

print("Income Category Distribution:")
print(df['Income_Category'].value_counts().sort_index())

# Analyze spending by income category
category_analysis = df.groupby('Income_Category').agg({
    'MntWines': 'mean',
    'MntFruits': 'mean',
    'MntMeatProducts': 'mean',
    'ID': 'count'
}).round(2)

print("\nSpending Analysis by Income Category:")
print(category_analysis)

Creating Age Groups

# Calculate age from birth year
df['Age'] = 2024 - df['Year_Birth']

# Create age groups
age_bins = [0, 25, 35, 45, 55, 65, 100]
age_labels = ['18-25', '26-35', '36-45', '46-55', '56-65', '66+']

df['Age_Group'] = pd.cut(df['Age'], bins=age_bins, labels=age_labels)

print("Age Group Distribution:")
age_distribution = df['Age_Group'].value_counts().sort_index()
print(age_distribution)

print(f"\nPercentage of customers in each age group:")
print((age_distribution / len(df) * 100).round(2))

📊 Categorization Best Practices:

Use pd.cut() for numerical binning with custom ranges
Use pd.qcut() for quantile-based binning (equal-sized groups)
Convert string columns with few unique values to categorical to save memory
Always validate your bins cover all data (use include_lowest=True if needed)

7. Removing Duplicate Data

Duplicate removal is essential for maintaining data quality and avoiding skewed analysis results.

Identifying Duplicates

# Check for exact duplicate rows
duplicate_rows = df.duplicated().sum()
print(f"Exact duplicate rows: {duplicate_rows}")

# Check for duplicates based on specific columns
id_duplicates = df.duplicated(subset=['ID']).sum()
print(f"Duplicate IDs: {id_duplicates}")

# Check for customers with identical demographics
demo_cols = ['Year_Birth', 'Education', 'Marital_Status', 'Income']
demo_duplicates = df.duplicated(subset=demo_cols, keep=False).sum()
print(f"Customers with identical demographics: {demo_duplicates}")

Removing Duplicates

# Remove exact duplicates
df_no_duplicates = df.drop_duplicates()
print(f"Original shape: {df.shape}")
print(f"After removing exact duplicates: {df_no_duplicates.shape}")

# Remove duplicates based on specific columns, keep first occurrence
df_unique_demo = df.drop_duplicates(subset=demo_cols, keep='first')
print(f"After removing duplicate demographics: {df_unique_demo.shape}")

✅ DOs for Duplicate Removal:

Always inspect duplicates before removing them
Understand why duplicates exist (data entry error vs legitimate duplicates)
Use keep=False to see all duplicates
Document which duplicates were removed and why

❌ DON'Ts for Duplicate Removal:

Remove duplicates blindly without understanding the data
Assume all duplicates are errors
Forget to consider which duplicate to keep (first, last, or none)
Remove duplicates from time-series data without considering timestamp

8. Dropping Data Rows and Columns

Dropping removes unnecessary rows or columns from your DataFrame, helping to focus on relevant data and reduce memory usage.

Dropping Columns

print("Original columns:", df.columns.tolist())
print(f"Original shape: {df.shape}")

# Drop single column
df_without_complain = df.drop('Complain', axis=1)
print(f"\nAfter dropping 'Complain' column: {df_without_complain.shape}")

# Drop multiple columns
columns_to_drop = ['Z_CostContact', 'Z_Revenue']
df_cleaned = df.drop(columns_to_drop, axis=1)
print(f"After dropping {len(columns_to_drop)} columns: {df_cleaned.shape}")

Dropping Rows

print(f"Original row count: {len(df)}")

# Drop rows with missing income
df_no_missing_income = df.dropna(subset=['Income'])
print(f"After dropping rows with missing Income: {len(df_no_missing_income)}")

# Drop rows by index
indices_to_drop = [0, 1, 2, 3, 4]  # First 5 rows
df_dropped_rows = df.drop(indices_to_drop)
print(f"After dropping first 5 rows: {len(df_dropped_rows)}")

⚠️ Important Considerations:

Use axis=0 for rows and axis=1 for columns
inplace=True modifies the original DataFrame - use with caution
Consider making a copy before dropping: df_copy = df.copy()
Dropping vs filtering: Dropping removes, filtering keeps based on condition

9. Replacing Data

Replacing modifies specific values in your DataFrame. This is crucial for data cleaning, error correction, and value transformation.

Simple Value Replacement

print("Original Marital_Status values:", df['Marital_Status'].unique())

# Replace specific values
marital_replacements = {
    'Together': 'Married',
    'Absurd': 'Single',  # Assuming data error
    'Alone': 'Single',
    'YOLO': 'Single'    # Assuming data error
}

df['Marital_Status_Clean'] = df['Marital_Status'].replace(marital_replacements)
print("\nCleaned Marital_Status values:", df['Marital_Status_Clean'].unique())

# Replace multiple values at once
df['Education_Standardized'] = df['Education'].replace({
    '2n Cycle': 'Graduation',
    'Graduation': 'Graduate'
})
print("\nStandardized Education values:", df['Education_Standardized'].unique())

Conditional Replacement

# Replace values based on conditions using numpy where
df['Income_Adjusted'] = np.where(
    df['Income'] > 100000,  # Condition
    100000,                  # Value if True
    df['Income']            # Value if False
)

print("Income Capping at $100,000:")
print(f"Original max: ${df['Income'].max():,.2f}")
print(f"Adjusted max: ${df['Income_Adjusted'].max():,.2f}")

✅ DOs for Data Replacement:

Always check value distributions before and after replacement
Use dictionaries for mapping replacements (cleaner code)
Consider creating new columns instead of overwriting original data
Document all replacements for reproducibility

❌ DON'Ts for Data Replacement:

Replace values without understanding why they need replacement
Use inplace=True without keeping the original data
Forget to handle edge cases and unexpected values
Make assumptions about data without validation

10. Changing Data Format

Format changing ensures your data is in the correct type for analysis, which affects calculations, memory usage, and processing speed.

Basic Type Conversion

print("Original Data Types:")
print(df.dtypes.head(10))

# Convert specific columns
df['Income'] = pd.to_numeric(df['Income'], errors='coerce')  # Convert to numeric, NaN for errors
df['Response'] = df['Response'].astype(int)  # Convert to integer
df['Education'] = df['Education'].astype('category')  # Convert to category

print("\nConverted Data Types:")
print(df[['Income', 'Response', 'Education']].dtypes)

Memory Optimization

# Check memory usage before optimization
original_memory = df.memory_usage(deep=True).sum() / 1024**2
print(f"Original memory usage: {original_memory:.2f} MB")

# Downcast numerical columns
for col in df.select_dtypes(include=['int64']).columns:
    df[col] = pd.to_numeric(df[col], downcast='integer')

for col in df.select_dtypes(include=['float64']).columns:
    df[col] = pd.to_numeric(df[col], downcast='float')

optimized_memory = df.memory_usage(deep=True).sum() / 1024**2
print(f"Optimized memory usage: {optimized_memory:.2f} MB")
print(f"Memory saved: {(original_memory - optimized_memory):.2f} MB ({(1 - optimized_memory/original_memory)*100:.1f}%)")

🔧 Data Type Guidelines:

int8/16/32/64: Integer numbers (choose smallest that fits your data)
float16/32/64: Decimal numbers
category: Text with few unique values (saves memory)
datetime64: Dates and times
bool: True/False values
Always use errors='coerce' when converting to handle problematic values

11. Dealing with Missing Values

Missing value handling is one of the most critical aspects of data preprocessing. How you handle missing data can significantly impact your analysis results.

Identifying Missing Values

# Comprehensive missing value analysis
missing_summary = pd.DataFrame({
    'Missing_Count': df.isnull().sum(),
    'Missing_Percentage': (df.isnull().sum() / len(df)) * 100,
    'Data_Type': df.dtypes
}).sort_values('Missing_Count', ascending=False)

print("Missing Value Summary:")
print(missing_summary[missing_summary['Missing_Count'] > 0])

# Visualize missing data pattern
print(f"\n📊 Missing Data Overview:")
print(f"Total missing values: {df.isnull().sum().sum()}")
print(f"Rows with any missing values: {df.isnull().any(axis=1).sum()}")
print(f"Columns with any missing values: {df.isnull().any(axis=0).sum()}")

Handling Strategies

# Strategy 1: Remove rows with missing values
df_dropped = df.dropna()
print(f"Strategy 1 - Drop all rows with any missing values:")
print(f"  Original: {len(df)} rows")
print(f"  After drop: {len(df_dropped)} rows")
print(f"  Removed: {len(df) - len(df_dropped)} rows ({((len(df) - len(df_dropped))/len(df))*100:.1f}%)")

# Strategy 2: Fill with constant value
df_filled_constant = df.fillna({
    'Income': 0,
    'MntWines': df['MntWines'].median()
})
print(f"\nStrategy 2 - Fill with constants:")
print(f"  Income missing filled with: 0")
print(f"  MntWines missing filled with median: {df['MntWines'].median():.2f}")

✅ DOs for Missing Values:

Always analyze why data is missing (MCAR, MAR, MNAR)
Consider multiple imputation methods
Create missing indicators for important variables
Validate your imputation results
Document your missing value handling strategy

❌ DON'Ts for Missing Values:

Ignore missing values (they won't disappear)
Always use mean imputation (consider distribution)
Drop too much data without justification
Assume missingness is random without checking

Complete Data Cleaning Pipeline

Now let's combine all these techniques into a complete data cleaning pipeline.

def clean_marketing_data(df):
    """
    Complete data cleaning pipeline for Marketing Campaign dataset
    """
    df_clean = df.copy()
    
    # 1. Handle missing values
    # Fill missing income with education group median
    education_income_median = df_clean.groupby('Education')['Income'].transform('median')
    df_clean['Income'] = df_clean['Income'].fillna(education_income_median)
    
    # 2. Remove unrealistic values
    df_clean = df_clean[df_clean['Year_Birth'] > 1900]
    df_clean = df_clean[df_clean['Income'] > 1000]  # Minimum reasonable income
    
    # 3. Standardize categorical variables
    marital_mapping = {
        'Together': 'Married',
        'Absurd': 'Single',
        'Alone': 'Single',
        'YOLO': 'Single'
    }
    df_clean['Marital_Status'] = df_clean['Marital_Status'].replace(marital_mapping)
    
    education_mapping = {
        '2n Cycle': 'Graduation',
        'Graduation': 'Graduate'
    }
    df_clean['Education'] = df_clean['Education'].replace(education_mapping)
    
    # 4. Create derived features
    df_clean['Age'] = 2024 - df_clean['Year_Birth']
    df_clean['Total_Spending'] = df_clean[[
        'MntWines', 'MntFruits', 'MntMeatProducts',
        'MntFishProducts', 'MntSweetProducts', 'MntGoldProds'
    ]].sum(axis=1)
    
    # Create spending categories
    spending_bins = [0, 100, 500, 1000, 5000, float('inf')]
    spending_labels = ['Very Low', 'Low', 'Medium', 'High', 'Very High']
    df_clean['Spending_Category'] = pd.cut(
        df_clean['Total_Spending'], 
        bins=spending_bins, 
        labels=spending_labels
    )
    
    # 5. Optimize data types
    categorical_cols = ['Education', 'Marital_Status', 'Spending_Category']
    for col in categorical_cols:
        if col in df_clean.columns:
            df_clean[col] = df_clean[col].astype('category')
    
    # Downcast numerical columns
    for col in df_clean.select_dtypes(include=['int64']).columns:
        df_clean[col] = pd.to_numeric(df_clean[col], downcast='integer')
    
    for col in df_clean.select_dtypes(include=['float64']).columns:
        df_clean[col] = pd.to_numeric(df_clean[col], downcast='float')
    
    # 6. Remove unnecessary columns
    columns_to_drop = ['Z_CostContact', 'Z_Revenue']
    df_clean = df_clean.drop([col for col in columns_to_drop if col in df_clean.columns], axis=1)
    
    # 7. Remove duplicates
    df_clean = df_clean.drop_duplicates(subset=['ID'], keep='first')
    
    # 8. Reset index
    df_clean = df_clean.reset_index(drop=True)
    
    return df_clean

# Apply the cleaning pipeline
print("Starting data cleaning pipeline...")
df_cleaned = clean_marketing_data(df)
print(f"✅ Cleaning complete!")
print(f"Original shape: {df.shape}")
print(f"Cleaned shape: {df_cleaned.shape}")
print(f"Columns removed: {set(df.columns) - set(df_cleaned.columns)}")
print(f"New columns added: {set(df_cleaned.columns) - set(df.columns)}")
print(f"\nSample of cleaned data:")
print(df_cleaned[['ID', 'Age', 'Income', 'Education', 'Total_Spending', 'Spending_Category']].head(5))

Conclusion and Best Practices

🎯 Key Takeaways:

Always start with data exploration before manipulation
Keep a copy of your original data
Document every transformation you make
Validate your results at each step
Consider the business context when making decisions

Workflow Summary

Load and Explore: Understand your data structure and content
Handle Missing Values: Decide on appropriate strategy for each column
Clean Data: Fix errors, standardize values, remove duplicates
Transform Data: Create derived features, change formats
Validate: Check that your transformations worked correctly
Document: Record all steps for reproducibility

Common Pitfalls to Avoid

❌ Manipulating data without understanding it first
❌ Using inplace=True without keeping backups
❌ Ignoring the business context of your data
❌ Not checking for edge cases and outliers
❌ Forgetting to validate your results