🎯 What You'll Learn
- Detect and handle missing values:
isnull,dropna,fillna - Find and remove duplicate rows
- Fix data types and clean inconsistent string values
- Use
groupbyto compute aggregate statistics per group - Merge and join DataFrames (like SQL JOINs)
- Apply functions to transform columns and rows
- Conduct a structured Exploratory Data Analysis (EDA) on a real dataset
A widely-cited industry observation: data scientists spend roughly 70–80% of their time on data cleaning and preparation — not on modeling. This isn't a complaint; it reflects reality. Garbage in, garbage out. A clean dataset with a simple model will almost always outperform a dirty dataset with a complex model.
1 Detecting Missing Values
Missing values (NaN, None, or sometimes sentinel values like -999) appear in virtually every real-world dataset. Pandas represents missing numeric values as NaN (Not a Number) and missing object/string values as None or NaN.
import pandas as pd
import numpy as np
# Simulated messy customer dataset
df = pd.DataFrame({
'customer_id': [1, 2, 3, 4, 5, 6, 7, 8],
'age': [28, None, 35, 42, None, 22, 55, 31],
'income': [55_000, 82_000, None, 96_000, 44_000, None, 115_000, 68_000],
'city': ['NYC', 'LA', 'NYC', None, 'Chicago', 'LA', None, 'NYC'],
'churn': [0, 1, 0, 0, 1, 0, 1, 0]
})
# ── Detecting missing values ──
# Boolean mask of where NaN is
print(df.isnull())
# customer_id age income city churn
# 0 False False False False False
# 1 False True False False False
# ...
# Count missing values per column — do this first on any new dataset
print(df.isnull().sum())
# customer_id 0
# age 2
# income 2
# city 2
# churn 0
# As percentages — helps decide whether to drop or impute
missing_pct = (df.isnull().sum() / len(df) * 100).round(1)
print(missing_pct)
# age 25.0
# income 25.0
# city 25.0
# Any row with at least one missing value?
print(df.isnull().any(axis=1))
# 0 False
# 1 True ← row 1 has a missing value
# ...
# Count of rows with at least one missing value
print(df.isnull().any(axis=1).sum()) # 4A "missingness map" makes the pattern jump out instantly — each row is a customer, each column a field, and the highlighted cells show exactly where the gaps are:
Missing-value pattern for the 8-row customer dataset — highlighted cells are NaN. age, income, and city each have 2 missing values, scattered across different rows.
2 Handling Missing Values
There are three strategies. Choosing the right one requires understanding why the data is missing:
- Drop: safe when a small fraction is missing and missingness is random
- Fill with a statistic: use mean/median for continuous, mode for categorical — simple and usually good enough
- Fill with a predicted value: use another model to predict the missing value — most sophisticated, used when missingness rate is high
Strategy 1: Drop rows or columns
# Drop any row with at least one missing value
df_dropped = df.dropna()
print(df_dropped.shape) # (4, 5) — 4 rows remain
# Drop only rows where a SPECIFIC column is missing
df_no_age_nan = df.dropna(subset=['age'])
print(df_no_age_nan.shape) # (6, 5) — kept rows with age present
# Drop column if >50% missing
threshold = 0.5
cols_to_drop = df.columns[df.isnull().mean() > threshold]
df_clean = df.drop(columns=cols_to_drop)Strategy 2: Fill (impute) with a value
# Fill with a constant
df['city'] = df['city'].fillna('Unknown')
# Fill with mean (for normally distributed continuous features)
age_mean = df['age'].mean()
df['age'] = df['age'].fillna(age_mean)
print(df['age']) # NaN positions now have the mean value
# Fill with median (robust to outliers — preferred for skewed distributions)
income_median = df['income'].median()
df['income'] = df['income'].fillna(income_median)
# Fill with mode (most frequent value — for categorical)
city_mode = df['city'].mode()[0] # .mode() returns a Series; [0] gets the value
df['city'] = df['city'].fillna(city_mode)
# Forward-fill (carry last valid observation forward — good for time series)
df['age'] = df['age'].fillna(method='ffill')
# Backward-fill
df['age'] = df['age'].fillna(method='bfill')The right fill strategy depends on the shape of your data. Using the same age column from this lesson's customer dataset ([28, NaN, 35, 42, NaN, 22, 55, 31]), pick a strategy below and watch how differently each one fills the two gaps:
Mean imputation — both gaps filled with the column average (35.5), flattening local trend.
Remember the note in Lesson 02 about held-back data? It applies here too: once your projects split data into a learning portion and a held-back test portion (Phase 2), compute fill values (mean, median, mode) from the learning portion only, and reuse them on the test portion. In Phase 2 you'll meet a scikit-learn tool (SimpleImputer) that handles this bookkeeping for you.
3 Duplicate Rows
Duplicate rows inflate sample counts and can severely bias a model toward duplicated examples. They're especially common after merging tables from different sources or when event logging systems fire multiple times.
df_with_dupes = pd.DataFrame({
'user_id': [1, 2, 2, 3, 4, 4, 4],
'event': ['click', 'view', 'view', 'click', 'purchase', 'purchase', 'purchase'],
'amount': [0, 0, 0, 0, 49.99, 49.99, 49.99]
})
# Detect duplicates
print(df_with_dupes.duplicated())
# 0 False
# 1 False
# 2 True ← exact duplicate of row 1
# ...
print(df_with_dupes.duplicated().sum()) # 3 duplicate rows
# Remove duplicates, keeping the first occurrence
df_clean = df_with_dupes.drop_duplicates()
print(df_clean.shape) # (4, 3) — 3 duplicates removed
# Or keep the last occurrence
df_clean = df_with_dupes.drop_duplicates(keep='last')
# Check duplicates based on a subset of columns only
# (useful if two rows are the same user but different timestamp)
df_clean = df_with_dupes.drop_duplicates(subset=['user_id'])4 Fixing Data Types and String Cleaning
Real-world data is dirty. Numbers are stored as strings, dates are mixed formats, categorical values have typos and inconsistent casing. Fixing these issues is non-glamorous but critical work.
Type Conversion
messy = pd.DataFrame({
'price': ['$10.99', '$24.50', '$5.00', '$89.95'],
'quantity': ['2', '1', '5', '3'],
'date': ['2024-01-15', '2024-02-03', '2024-01-28', '2024-03-10'],
'in_stock': ['yes', 'no', 'yes', 'yes']
})
# Remove '$' and convert price to float
messy['price'] = messy['price'].str.replace('$', '', regex=False).astype(float)
# Convert string to integer
messy['quantity'] = messy['quantity'].astype(int)
# Parse dates — Pandas will automatically detect common formats
messy['date'] = pd.to_datetime(messy['date'])
# Map string categories to binary integers
messy['in_stock'] = messy['in_stock'].map({'yes': 1, 'no': 0})
print(messy.dtypes)
# price float64
# quantity int32
# date datetime64[ns]
# in_stock int64
# Now extract useful date features
messy['month'] = messy['date'].dt.month
messy['dayofweek'] = messy['date'].dt.dayofweek # 0=Monday, 6=Sunday
messy['is_weekend'] = messy['dayofweek'].isin([5, 6]).astype(int)String Cleaning
cities = pd.Series([' New York ', 'los angeles', 'CHICAGO', 'New York', 'Chicago '])
# The .str accessor gives vectorized string operations
cities_clean = (cities
.str.strip() # remove leading/trailing whitespace
.str.title() # Title Case: "new york" → "New York"
)
print(cities_clean.value_counts())
# New York 2
# Los Angeles 1
# Chicago 2
# Check if string contains a pattern
emails = pd.Series(['user@gmail.com', 'admin@company.org', 'invalid-email', 'test@test.com'])
is_valid = emails.str.contains(r'@\w+\.\w+', regex=True)
print(is_valid) # [True, True, False, True]
# Extract parts of a string
df['domain'] = emails.str.extract(r'@(.+)') # extract text after @
print(df['domain']) # ['gmail.com', 'company.org', NaN, 'test.com']5 GroupBy — The SQL GROUP BY of Pandas
groupby splits your DataFrame into groups, applies a function to each group, and combines the results. It's how you compute per-segment statistics — survival rates by gender, average spend by customer tier, conversion rates by traffic source.
The split-apply-combine pattern behind every groupby call: rows are split into groups by key (here, category), an aggregation function is applied independently to each group, and the per-group results are combined into a single summary table — one row per group.
import pandas as pd
# E-commerce transaction dataset
df = pd.DataFrame({
'customer_id': [1, 1, 1, 2, 2, 3, 3, 3, 3],
'category': ['Electronics', 'Clothing', 'Electronics', 'Clothing',
'Clothing', 'Electronics', 'Books', 'Books', 'Electronics'],
'amount': [299, 49, 199, 89, 34, 599, 12, 19, 399],
'returned': [0, 0, 1, 0, 0, 0, 0, 1, 0]
})
# ── Basic aggregation ──
# Total spend per customer
spend_per_customer = df.groupby('customer_id')['amount'].sum()
print(spend_per_customer)
# customer_id
# 1 547
# 2 123
# 3 1029
# Multiple statistics at once using .agg()
category_stats = df.groupby('category')['amount'].agg(['mean', 'count', 'sum'])
print(category_stats.round(1))
# mean count sum
# category
# Books 15.5 2 31.0
# Clothing 57.3 3 172.0
# Electronics 374.0 4 1496.0
# Multiple columns at once
full_stats = df.groupby('category').agg(
avg_amount = ('amount', 'mean'),
total_spend = ('amount', 'sum'),
order_count = ('amount', 'count'),
return_rate = ('returned', 'mean') # mean of 0/1 = proportion
).round(2)
print(full_stats)
# avg_amount total_spend order_count return_rate
# category
# Books 15.50 31.0 2 0.50
# Clothing 57.33 172.0 3 0.00
# Electronics 374.00 1496.0 4 0.25
# ── Adding group-level stats back to original rows ──
# (transform returns the same shape as the input — great for feature engineering)
df['customer_total'] = df.groupby('customer_id')['amount'].transform('sum')
df['customer_avg'] = df.groupby('customer_id')['amount'].transform('mean')
print(df[['customer_id', 'amount', 'customer_total', 'customer_avg']].head(4))groupby().agg() collapses rows (you get one row per group). groupby().transform() keeps the same shape as the original DataFrame, adding the group statistic to each row. This is essential for creating features like "customer lifetime spend" or "category average price" as new columns on the transaction-level data.
6 Merging and Joining DataFrames
In the real world, data lives in multiple tables. Customer info is in one table, their transactions in another, their support tickets in a third. You need to join them — exactly like SQL JOINs.
Merging customers and orders on the shared customer_id key. Rows highlighted in green have a match on both sides and survive an inner join; grey rows (Bob's customer row, Diana entirely) have no counterpart and are dropped unless you switch to a left/right/outer join.
customers = pd.DataFrame({
'customer_id': [1, 2, 3, 4],
'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
'tier': ['Gold', 'Silver', 'Bronze', 'Gold']
})
orders = pd.DataFrame({
'order_id': [101, 102, 103, 104, 105],
'customer_id': [1, 2, 1, 3, 5], # customer 5 has no matching record
'amount': [299, 89, 149, 599, 25]
})
# INNER JOIN: only rows matching in both tables (default)
inner = pd.merge(customers, orders, on='customer_id', how='inner')
print(inner)
# customer_id name tier order_id amount
# 0 1 Alice Gold 101 299
# 1 1 Alice Gold 103 149
# 2 2 Bob Silver 102 89
# 3 3 Charlie Bronze 104 599
# Note: customer 4 (Diana) and customer 5 both dropped — no match in both
# LEFT JOIN: keep all rows from customers, fill with NaN if no order
left = pd.merge(customers, orders, on='customer_id', how='left')
print(left[left['order_id'].isnull()]) # Diana: order_id=NaN, amount=NaN
# RIGHT JOIN: keep all rows from orders
right = pd.merge(customers, orders, on='customer_id', how='right')
print(right[right['name'].isnull()]) # customer 5: name=NaN, tier=NaN
# Merge on columns with different names
pd.merge(customers, orders,
left_on='customer_id',
right_on='customer_id') # in this case the same, but often different
# Concatenating DataFrames vertically (stacking rows — like UNION in SQL)
df_jan = pd.DataFrame({'month': ['Jan']*3, 'sales': [100, 150, 200]})
df_feb = pd.DataFrame({'month': ['Feb']*3, 'sales': [120, 180, 210]})
combined = pd.concat([df_jan, df_feb], ignore_index=True)
print(combined.shape) # (6, 2)7 Applying Functions with apply()
For transformations that can't be expressed as simple vectorized operations, use apply() to pass a Python function over rows or columns.
df = pd.DataFrame({
'first_name': ['alice', 'bob', 'charlie'],
'last_name': ['smith', 'jones', 'brown'],
'salary': [55000, 82000, 96000]
})
# Apply to a single column (Series)
df['full_name'] = df['first_name'].apply(lambda x: x.title())
# Apply a more complex function
def salary_band(salary):
if salary < 60_000: return 'Junior'
elif salary < 90_000: return 'Mid'
else: return 'Senior'
df['level'] = df['salary'].apply(salary_band)
# Apply across columns (axis=1)
df['display_name'] = df.apply(
lambda row: f"{row['first_name'].title()} {row['last_name'].title()}",
axis=1
)
print(df[['display_name', 'level']])
# display_name level
# 0 Alice Smith Junior
# 1 Bob Jones Mid
# 2 Charlie Brown Seniorapply() runs a Python function in a Python loop internally — it's faster than writing your own loop, but it's still not as fast as native Pandas or NumPy operations. For performance-critical code, always try vectorized .str methods, pd.cut(), np.where(), or arithmetic operations first. Use apply() only when none of those work.
Real-World Spotlight: Full EDA on a Customer Churn Dataset
Customer churn (users leaving a service) is one of the most common ML use cases across SaaS, telecom, and streaming companies. Spotify, Netflix, and Salesforce all have churn prediction models. Here's a realistic EDA pipeline:
import pandas as pd
import numpy as np
# Synthetic telecom churn dataset
np.random.seed(42)
n = 1000
df = pd.DataFrame({
'customer_id': range(1, n+1),
'tenure_months': np.random.randint(1, 72, n),
'monthly_charge': np.random.uniform(20, 120, n).round(2),
'num_complaints': np.random.poisson(0.5, n),
'plan_type': np.random.choice(['Basic', 'Standard', 'Premium'], n,
p=[0.4, 0.35, 0.25]),
'churned': None # will compute below
})
# Realistic churn: higher charge + more complaints + shorter tenure → more churn
churn_prob = (
0.3 * (df['monthly_charge'] / 120) +
0.4 * (df['num_complaints'] / 5) +
0.3 * (1 - df['tenure_months'] / 72)
)
df['churned'] = (np.random.rand(n) < churn_prob).astype(int)
# ── Step 1: Assess data quality ──
print("=== DATA QUALITY ===")
print(f"Shape: {df.shape}")
print(f"Missing values:\n{df.isnull().sum()}")
print(f"Duplicates: {df.duplicated().sum()}")
# ── Step 2: Target variable distribution ──
print("\n=== TARGET ===")
print(df['churned'].value_counts())
print(f"Churn rate: {df['churned'].mean():.1%}")
# Churn rate: ~35.2% ← only 1 in 3 churn; the two groups are
# different sizes — worth remembering when we model this later
# ── Step 3: Numerical feature distributions ──
print("\n=== NUMERICAL FEATURES ===")
print(df[['tenure_months', 'monthly_charge', 'num_complaints']].describe().round(2))
# ── Step 4: Churn rates by segment ──
print("\n=== CHURN BY PLAN ===")
print(df.groupby('plan_type')['churned'].agg(['mean', 'count']).round(3))
# plan_type mean count
# Basic 0.38 401 ← Basic plan churns most
# Premium 0.28 254
# Standard 0.35 345
# ── Step 5: Correlations with target ──
# Correlation measures how strongly two columns move together,
# from -1 (opposite) through 0 (no link) to +1 (in lockstep).
# Lessons 05-06 cover it properly — here, just read the signs.
print("\n=== CORRELATIONS WITH CHURN ===")
numeric_cols = ['tenure_months', 'monthly_charge', 'num_complaints']
corr = df[numeric_cols + ['churned']].corr()['churned'].drop('churned')
print(corr.sort_values(ascending=False).round(3))
# num_complaints 0.412 ← strongest positive correlation
# monthly_charge 0.298
# tenure_months -0.362 ← negative: longer tenure → less churn
# ── Step 6: Prepare for ML ──
# Models need numbers, not text. get_dummies turns the plan_type
# column into 0/1 columns, one per plan ("one-hot encoding" —
# you'll study this properly in Phase 2, Lesson 10)
df_ml = pd.get_dummies(df, columns=['plan_type'], drop_first=True)
features = ['tenure_months', 'monthly_charge', 'num_complaints',
'plan_type_Premium', 'plan_type_Standard']
X = df_ml[features].values.astype(np.float32)
y = df_ml['churned'].values
print(f"\nML-ready X shape: {X.shape}") # (1000, 5)
print(f"ML-ready y shape: {y.shape}") # (1000,)This 50-line EDA told you: the churn rate (~35%), the most predictive features (complaints and tenure), the at-risk segment (Basic plan), and it prepared a clean NumPy feature matrix — ready for the prediction models you'll start building in Phase 2 of this curriculum.
✍️ Practice Exercise
Using the Titanic dataset (pd.read_csv('https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv')), complete a full cleaning pipeline:
- Check missingness: how many columns have missing values, and what percentage?
- Handle missing
Agevalues by filling with the median age. Handle missingEmbarkedby filling with the mode. Drop theCabincolumn entirely (too many missing). - Create a new feature
'Title'by extracting the title from theNamecolumn. (Hint: usestr.extract(r'([A-Za-z]+)\.')). Usevalue_counts()to see all titles. - Use
groupbyto compute the survival rate and average fare for each combination ofPclassandSex. Which group had the highest survival rate?
▶ Show Solution
import pandas as pd
url = 'https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv'
df = pd.read_csv(url)
# Task 1: missingness
missing = df.isnull().sum()
missing_pct = (missing / len(df) * 100).round(1)
print(missing_pct[missing_pct > 0])
# Age 19.9%
# Cabin 77.1%
# Embarked 0.2%
# Task 2: clean missing values
df['Age'] = df['Age'].fillna(df['Age'].median())
df['Embarked'] = df['Embarked'].fillna(df['Embarked'].mode()[0])
df = df.drop(columns=['Cabin'])
print(df.isnull().sum().sum()) # 0 — no more missing values
# Task 3: extract title
df['Title'] = df['Name'].str.extract(r'([A-Za-z]+)\.')
print(df['Title'].value_counts())
# Mr 517
# Miss 182
# Mrs 125
# Master 40
# Dr 7
# ...
# Task 4: survival rate by Pclass and Sex
result = df.groupby(['Pclass', 'Sex']).agg(
survival_rate = ('Survived', 'mean'),
avg_fare = ('Fare', 'mean'),
count = ('Survived', 'count')
).round(3)
print(result)
# Pclass Sex survival_rate avg_fare count
# 1 female 0.968 106.126 94
# 1 male 0.369 67.226 122
# 2 female 0.921 21.970 76
# 2 male 0.157 19.741 108
# 3 female 0.500 16.118 144
# 3 male 0.135 12.661 347
# Highest: 1st class female — 96.8% survival rateYou can now create, manipulate, and compute with NumPy arrays, and load, explore, clean, and transform real tabular datasets with Pandas. These skills form the foundation of everything that follows. The next two lessons cover visualization (seeing the data) and the essential statistics you'll need to understand ML algorithms.
📚 Primary Source for This Lesson
Pandas: Working with Missing Data — official Pandas documentation
The complete guide to NaN handling in Pandas, including edge cases and performance considerations. Equally recommended: the Kaggle Data Cleaning micro-course — 5 free interactive notebooks covering real-world cleaning scenarios.