Pandas: Project: Data Analysis
Last updated: 2026-08-26
After 22 lessons of tools and techniques, it is time to put everything together. This lesson simulates a real-world data analysis project: you receive the task "Analyze Q1-Q3 user purchasing behavior," and walk through the full workflow from CSV loading, exploration, cleaning, feature engineering, grouping, visualization, to conclusions. In real data analysis, 80% of the time goes to cleaning and 20% to analysis, but the conclusions depend entirely on that 20%.
⚠️ Note: The code below must be run in a local Python environment.
1. What You Will Learn
- ❶ End-to-end analysis workflow
- ❷ Data loading and exploration
- ❸ Cleaning pipeline (pipe)
- ❹ Feature engineering
- ❺ Aggregation analysis and visualization report
2. Project Background: E-Commerce User Purchase Behavior Analysis
(1) The Task
Charlie receives an assignment: "Analyze our Q1-Q3 user purchasing behavior. Identify high-value users, popular categories, and spending trends, then provide actionable recommendations for operations."
(2) Analysis Workflow
graph TB
A["1. Load Data"] --> B["2. Explore (EDA)"]
B --> C["3. Cleaning Pipeline"]
C --> D["4. Feature Engineering"]
D --> E["5. Group Aggregation"]
E --> F["6. Visualization Report"]
F --> G["7. Conclusions & Recommendations"]
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
3. Data Loading and Exploration
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
: Data Loading and EDA (Difficulty ⭐⭐)
PYTHON
import pandas as pd
import numpy as np
# ============================================
# Step 1-2: Load and Explore
# ============================================
# Simulate e-commerce data (in real project: pd.read_csv)
np.random.seed(42)
n = 2000
users = pd.DataFrame({
'user_id': range(1, n + 1),
'name': [f'User_{i:04d}' for i in range(1, n + 1)],
'age': np.random.randint(18, 70, n),
'gender': np.random.choice(['M', 'F'], n),
'city': np.random.choice(['NYC', 'LA', 'Chicago', 'Houston', 'Phoenix'], n),
'join_date': pd.to_datetime(np.random.choice(
pd.date_range('2023-01-01', '2024-09-30'), n
))
})
orders = pd.DataFrame({
'order_id': [f'O{i:06d}' for i in range(1, 5001)],
'user_id': np.random.randint(1, n + 1, 5000),
'product_id': np.random.randint(1, 51, 5000),
'category': np.random.choice(['Electronics', 'Clothing', 'Home', 'Sports', 'Books'], 5000),
'amount': np.round(np.random.exponential(100, 5000), 2),
'quantity': np.random.randint(1, 5, 5000),
'order_date': pd.to_datetime(np.random.choice(
pd.date_range('2024-01-01', '2024-09-30'), 5000
))
})
# Inject data quality issues
orders.loc[np.random.choice(5000, 200, replace=False), 'amount'] = np.nan
orders.loc[np.random.choice(5000, 100, replace=False), 'category'] = np.nan
duplicates = orders.sample(50)
orders = pd.concat([orders, duplicates], ignore_index=True)
# EDA: shape, dtypes, missing, duplicates
print(f"Users: {users.shape}, Orders: {orders.shape}")
print(f"\nMissing per column:\n{orders.isnull().sum()}")
print(f"\nDuplicate orders: {orders.duplicated(subset='order_id').sum()}")
print(f"\nDate range: {orders['order_date'].min()} to {orders['order_date'].max()}")
print(f"\nAmount stats:\n{orders['amount'].describe()}")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
4. Cleaning Pipeline
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
: pipe Cleaning Pipeline (Difficulty ⭐⭐⭐)
PYTHON
# ============================================
# Step 3: Cleaning Pipeline (pipe)
# ============================================
def remove_duplicates(df):
"""Remove duplicate orders, keep latest"""
before = len(df)
df = df.drop_duplicates(subset='order_id', keep='last')
print(f" Duplicates removed: {before - len(df)}")
return df
def fill_missing(df):
"""Fill missing values with appropriate strategies"""
df = df.copy()
# Fill amount with median per category
df['amount'] = df.groupby('category')['amount'].transform(
lambda x: x.fillna(x.median())
)
# Fill category with mode
df['category'] = df.fillna({'category': df['category'].mode()[0]})
return df
def fix_types(df):
"""Fix data types"""
df = df.copy()
df['order_date'] = pd.to_datetime(df['order_date'])
df['category'] = df['category'].astype('category')
return df
def add_derived(df):
"""Add derived columns"""
df = df.copy()
df['total_price'] = df['amount'] * df['quantity']
df['month'] = df['order_date'].dt.to_period('M')
return df
# Execute pipeline
clean_orders = (orders
.pipe(remove_duplicates)
.pipe(fill_missing)
.pipe(fix_types)
.pipe(add_derived)
)
print(f"\nClean orders: {clean_orders.shape}")
print(f"Remaining missing: {clean_orders.isnull().sum().sum()}")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
5. Feature Engineering and Aggregation
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
: RFM Features + Group Analysis (Difficulty ⭐⭐⭐)
PYTHON
# ============================================
# Step 4-5: Feature Engineering + Aggregation
# ============================================
# RFM Analysis (Recency, Frequency, Monetary)
reference_date = clean_orders['order_date'].max() + pd.Timedelta(days=1)
rfm = clean_orders.groupby('user_id').agg(
recency=('order_date', lambda x: (reference_date - x.max()).days),
frequency=('order_id', 'count'),
monetary=('total_price', 'sum')
).reset_index()
# RFM scoring (quartile-based)
rfm['R_score'] = pd.qcut(rfm['recency'], 4, labels=[4, 3, 2, 1]).astype(int)
rfm['F_score'] = pd.qcut(rfm['frequency'].rank(method='first'), 4, labels=[1, 2, 3, 4]).astype(int)
rfm['M_score'] = pd.qcut(rfm['monetary'].rank(method='first'), 4, labels=[1, 2, 3, 4]).astype(int)
rfm['RFM_score'] = rfm['R_score'] + rfm['F_score'] + rfm['M_score']
# Segment users
rfm['segment'] = pd.cut(rfm['RFM_score'], bins=[0, 5, 8, 10, 12],
labels=['At-Risk', 'Average', 'Good', 'Champions'])
print("=== User Segments ===")
print(rfm['segment'].value_counts())
# Monthly revenue trend
monthly_rev = clean_orders.groupby('month')['total_price'].sum()
print(f"\n=== Monthly Revenue ===\n{monthly_rev}")
# Category analysis
cat_stats = clean_orders.groupby('category').agg(
orders=('order_id', 'count'),
revenue=('total_price', 'sum'),
avg_amount=('amount', 'mean')
).sort_values('revenue', ascending=False)
print(f"\n=== Category Stats ===\n{cat_stats}")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
6. Visualization Report and Conclusions
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
: Visualization Report (Difficulty ⭐⭐⭐)
PYTHON
# ============================================
# Step 6-7: Visualization & Conclusions
# ============================================
import matplotlib.pyplot as plt
# Summary report (no actual plots — code for local execution)
print("=" * 50)
print(" E-COMMERCE USER BEHAVIOR ANALYSIS REPORT")
print("=" * 50)
print(f"\n📊 Dataset Overview:")
print(f" Users: {len(users):,}")
print(f" Orders: {len(clean_orders):,}")
print(f" Period: {clean_orders['order_date'].min().date()} to {clean_orders['order_date'].max().date()}")
print(f"\n🏆 Top Categories:")
for i, row in cat_stats.head(3).iterrows():
print(f" {i}: ${row['revenue']:,.0f} ({row['orders']} orders)")
print(f"\n👤 User Segments:")
for seg, count in rfm['segment'].value_counts().items():
pct = count / len(rfm) * 100
print(f" {seg}: {count} ({pct:.1f}%)")
print(f"\n📈 Monthly Trend:")
monthly_growth = monthly_rev.pct_change().dropna() * 100
print(f" Avg monthly growth: {monthly_growth.mean():.1f}%")
print(f" Peak month: {monthly_rev.idxmax()}")
print(f" Lowest month: {monthly_rev.idxmin()}")
print(f"\n💡 Recommendations:")
print(f" 1. Champions segment ({(rfm['segment']=='Champions').sum()} users) → VIP program")
print(f" 2. At-Risk segment ({(rfm['segment']=='At-Risk').sum()} users) → Win-back campaign")
print(f" 3. Electronics is top category → expand selection")
print(f" 4. Focus on high-F-score users for cross-selling")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
7. Data Validation and Quality Report
▶ Example
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
: Data Validation and Quality Report (Difficulty ⭐⭐)
PYTHON
# ============================================
# Step: Data Validation & Quality Report
# Calculate missing rate / duplicate rate /
# outlier ratio, generate quality report
# ============================================
# 1. Missing rate analysis
missing_count = orders.isnull().sum()
missing_rate = (missing_count / len(orders) * 100).round(2)
print("=== Missing Rate Report ===")
for col in missing_count[missing_count > 0].index:
print(f" {col}: {missing_count[col]} rows missing ({missing_rate[col]}%)")
# 2. Duplicate rate analysis
dup_count = orders.duplicated(subset='order_id').sum()
dup_rate = dup_count / len(orders) * 100
print(f"\n=== Duplicate Rate Report ===")
print(f" Duplicate orders: {dup_count} ({dup_rate:.2f}%)")
# 3. Outlier detection (amount too high or too low)
q1 = orders['amount'].quantile(0.25)
q3 = orders['amount'].quantile(0.75)
iqr = q3 - q1
lower_bound = q1 - 1.5 * iqr
upper_bound = q3 + 1.5 * iqr
outliers = orders[(orders['amount'] < lower_bound) | (orders['amount'] > upper_bound)]
outlier_rate = len(outliers) / len(orders) * 100
print(f"\n=== Outlier Report ===")
print(f" Amount outliers: {len(outliers)} rows ({outlier_rate:.2f}%)")
print(f" Normal range: ${lower_bound:.2f} ~ ${upper_bound:.2f}")
# 4. Overall data quality score
quality_score = 100 - (missing_rate.sum() + dup_rate + outlier_rate) / 3
print(f"\n=== Data Quality Score: {quality_score:.1f}/100 ===")
print(f" {'✅ Good quality' if quality_score > 90 else '⚠️ Needs attention'}")
TEXT
📖 Display only
> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.
❓ FAQ
Q Where should I start the analysis?
A Start with EDA (Exploratory Data Analysis). After loading, check shape/dtypes/missing/describe first to build intuition about the data. Do not jump straight into modeling or plotting. 80% of analysis problems can be caught during EDA (missing values, outliers, wrong types). Good EDA determines the quality of your analysis.
Q How clean is clean enough?
A Clean until the data "no longer affects your conclusions." Critical fields (IDs, amounts, dates) must be spotless; secondary fields can tolerate minor missingness. Rule of thumb: 0% missing in key columns, less than 5% in non-key columns. Cleaning is not about perfection; it is about eliminating noise.
Q What does feature engineering involve?
A Deriving useful columns from raw data for analysis. Common examples: time features (month/quarter/day of week), aggregate features (RFM), ratio features (profit margin), and categorical features (price tier). Feature engineering is "creating information from business knowledge" -- good features are more valuable than complex models.
Q How do I validate analysis results?
A Three-step validation: (1) Numerical consistency (total revenue = sum of categories = sum of months); (2) Logical plausibility (monthly growth should not exceed 200% unless there was a promotion); (3) Cross-validation (compute the same metric with different methods and confirm they agree). If any step fails, trace back.
Q How should I structure the report?
A Structure: conclusion first, then supporting data, then recommendations. Format: executive summary (one paragraph), key findings (3-5 points with data), detailed analysis (charts + interpretation), recommendations (actionable). Avoid "data dumps" -- every chart must have a takeaway, and every takeaway must be backed by data.
Q What is RFM?
A Recency (days since last purchase), Frequency (number of purchases), Monetary (total spend) -- three dimensions for measuring user value. Low R + High F + High M = Champions (most valuable); High R + Low F + Low M = At-Risk (about to churn). RFM is the most classic and practical user segmentation method.
Q What are the benefits of pipe?
A pipe turns multi-step cleaning into a top-to-bottom readable pipeline where each function has a single responsibility, is testable, and reusable. It is clearer than nested calls like
f3(f2(f1(df))) or repeated reassignment like df = df.... The more cleaning steps you have, the more pipe shines.📖 Summary
- End-to-end workflow: load, explore, clean, engineer features, aggregate, visualize, conclude
- EDA is the starting point: shape/dtypes/missing/describe to build data intuition
- pipe builds a cleaning pipeline: deduplicate, fill, fix types, add derived columns
- RFM is the classic user segmentation: Recency + Frequency + Monetary
- Three-step validation: numerical consistency, logical plausibility, cross-validation
- Report structure: conclusion first, data-backed findings, actionable recommendations
📝 Exercises
- Basic (Difficulty ⭐): Load the simulated e-commerce data, complete EDA (shape/missing/describe), and write down 3 data quality findings.
- Intermediate (Difficulty ⭐⭐): Build a 3-step pipe cleaning pipeline (deduplicate, fill, fix types), then verify that missing = 0 and duplicates = 0 after cleaning.
- Challenge (Difficulty ⭐⭐⭐): Complete a full end-to-end analysis: load, pipe cleaning, RFM features, segment classification, monthly/category aggregation, and output a conclusions-and-recommendations report.
← Previous: Styling Output · Next: Project - Time Series →
