English 中文 العربية 日本語 Português

Pandas: Project: Comprehensive Analysis

Last updated: 2026-08-26

Real-world projects never involve just one table -- think 5 CSVs + 1 Excel file + a database query, merged 4 times, grouped across 3 dimensions, pivoted into 2 cross-tabs, and styled into 1 polished report. This lesson is the grand finale of the entire course, bringing together every skill from the previous 24 lessons into a single project: multi-source integration -> quality audit -> complex joins -> multi-dimensional analysis -> automated reporting.

Warning: The code below must be run in a local Python environment.

1. What You Will Learn

2. Project Background: Cross-Border E-Commerce Analysis

(1) The Task

Bob needs to analyze Q1-Q3 data for a cross-border e-commerce business: 4 CSVs (orders/products/customers/regions) + 1 SQLite database (inventory) -> integrate -> analyze -> report.

(2) Full Workflow

100% 
graph TB
    A["Load 5 Data Sources"] --> B["Data Quality Audit"]
    B --> C["4-Table Merge"]
    C --> D["Cleaning Pipeline"]
    D --> E["Multi-Dim Groupby"]
    E --> F["Pivot Cross-Analysis"]
    F --> G["Styler Report"]
    G --> H["Multi-Sheet Export"]
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 your pandas version.

3. Multi-Source Data Loading

▶ 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 your pandas version.

: Loading 5 data sources (Difficulty: 3/5 stars)

PYTHON 
import pandas as pd
import numpy as np
import sqlite3
from io import StringIO, BytesIO

# ============================================
# Step 1: Multi-source Data Loading
# ============================================

np.random.seed(42)

# Source 1: Customers (CSV)
customers = pd.DataFrame({
    'customer_id': range(1, 501),
    'name': [f'Customer_{i:04d}' for i in range(1, 501)],
    'segment': np.random.choice(['Consumer', 'Corporate', 'Home Office'], 500),
    'city': np.random.choice(['New York', 'Los Angeles', 'Chicago', 'Houston', 'London', 'Tokyo'], 500),
    'country': np.random.choice(['US', 'US', 'US', 'US', 'UK', 'JP'], 500)
})

# Source 2: Products (CSV)
products = pd.DataFrame({
    'product_id': range(1, 51),
    'product_name': [f'Product_{i:02d}' for i in range(1, 51)],
    'category': np.random.choice(['Electronics', 'Clothing', 'Home', 'Sports', 'Books'], 50),
    'unit_price': np.round(np.random.uniform(10, 500, 50), 2)
})

# Source 3: Orders (CSV)
orders = pd.DataFrame({
    'order_id': [f'ORD-{i:05d}' for i in range(1, 2001)],
    'customer_id': np.random.randint(1, 501, 2000),
    'product_id': np.random.randint(1, 51, 2000),
    'order_date': pd.to_datetime(np.random.choice(
        pd.date_range('2024-01-01', '2024-09-30'), 2000)),
    'quantity': np.random.randint(1, 10, 2000),
    'discount': np.random.choice([0, 0.05, 0.1, 0.2, 0.3], 2000)
})

# Source 4: Regions (Excel)
regions = pd.DataFrame({
    'country': ['US', 'UK', 'JP', 'DE', 'FR'],
    'region': ['North America', 'Europe', 'Asia Pacific', 'Europe', 'Europe'],
    'currency': ['USD', 'GBP', 'JPY', 'EUR', 'EUR']
})

# Source 5: Inventory (SQLite)
conn = sqlite3.connect(':memory:')
inventory = pd.DataFrame({
    'product_id': range(1, 51),
    'stock': np.random.randint(0, 500, 50),
    'reorder_level': np.random.randint(20, 100, 50)
})
inventory.to_sql('inventory', conn, index=False, if_exists='replace')

# Load from database
inv_df = pd.read_sql('SELECT * FROM inventory', conn)
conn.close()

print(f"✅ Loaded: customers({len(customers)}), products({len(products)}), "
      f"orders({len(orders)}), regions({len(regions)}), inventory({len(inv_df)})")
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 your pandas version.

4. Data Quality Audit

▶ 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 your pandas version.

: Quality audit (Difficulty: 2/5 stars)

PYTHON 
# ============================================
# Step 2: Data Quality Audit
# ============================================

def audit_quality(df, name):
    """Run quality audit on a DataFrame"""
    issues = []
    # Missing values
    missing = df.isnull().sum()
    if missing.sum() > 0:
        issues.append(f"Missing: {dict(missing[missing > 0])}")
    # Duplicates
    dups = df.duplicated().sum()
    if dups > 0:
        issues.append(f"Duplicates: {dups}")
    # Types
    object_cols = df.select_dtypes(include='object').columns.tolist()
    if object_cols:
        issues.append(f"Object columns: {object_cols}")
    status = "⚠️ ISSUES" if issues else "✅ CLEAN"
    print(f"{name}: {status}")
    for issue in issues:
        print(f"  - {issue}")
    return issues

audit_quality(customers, 'Customers')
audit_quality(products, 'Products')
audit_quality(orders, 'Orders')
audit_quality(regions, 'Regions')
audit_quality(inv_df, 'Inventory')

# Inject issues for demo
orders.loc[np.random.choice(2000, 100, replace=False), 'discount'] = np.nan
dups = orders.sample(30)
orders = pd.concat([orders, dups], ignore_index=True)
print(f"\nAfter injecting issues: orders shape = {orders.shape}")
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 your pandas version.

5. 4-Table Merge + Cleaning

▶ 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 your pandas version.

: Complex merge + pipe cleaning (Difficulty: 3/5 stars)

PYTHON 
# ============================================
# Step 3-4: 4-table Merge + Cleaning
# ============================================

def clean_orders(df):
    df = df.drop_duplicates(subset='order_id', keep='last')
    df['discount'] = df['discount'].fillna(0)
    return df

# Step 1: Clean orders
clean_orders_df = orders.pipe(clean_orders)

# Step 2: Merge orders + products
op = pd.merge(clean_orders_df, products, on='product_id', how='left')

# Step 3: Merge + customers
opc = pd.merge(op, customers, on='customer_id', how='left')

# Step 4: Merge + regions
full = pd.merge(opc, regions, on='country', how='left')

# Step 5: Merge + inventory
full = pd.merge(full, inv_df, on='product_id', how='left')

# Derived columns
full['revenue'] = full['unit_price'] * full['quantity'] * (1 - full['discount'])
full['cost_estimate'] = full['unit_price'] * full['quantity'] * 0.6
full['profit'] = full['revenue'] - full['cost_estimate']
full['month'] = full['order_date'].dt.to_period('M')
full['is_low_stock'] = full['stock'] < full['reorder_level']

print(f"✅ Full dataset: {full.shape}")
print(f"Columns: {full.columns.tolist()}")
print(f"Missing: {full.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 your pandas version.

6. Multi-Dimensional Groupby + Pivot Analysis

▶ 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 your pandas version.

: Multi-dimensional aggregation and cross-analysis (Difficulty: 3/5 stars)

PYTHON 
# ============================================
# Step 5-6: Multi-dim Analysis
# ============================================

# 1. Revenue by region × category
region_cat = full.groupby(['region', 'category']).agg(
    revenue=('revenue', 'sum'),
    orders=('order_id', 'count'),
    avg_order_value=('revenue', 'mean'),
    profit_margin=('profit', lambda x: x.sum() / full.loc[x.index, 'revenue'].sum())
).round(2)
print("=== Revenue by Region × Category ===")
print(region_cat.head(8))

# 2. Monthly trend by region
monthly_region = full.groupby(['month', 'region'])['revenue'].sum().unstack()
print(f"\n=== Monthly by Region ===\n{monthly_region.tail(3)}")

# 3. Pivot table: segment × category
seg_cat = pd.pivot_table(
    full, values='revenue', index='segment', columns='category',
    aggfunc='sum', margins=True, margins_name='Total'
).round(0)
print(f"\n=== Segment × Category Pivot ===\n{seg_cat}")

# 4. Top products
top_products = full.groupby('product_name').agg(
    revenue=('revenue', 'sum'),
    quantity=('quantity', 'sum'),
    avg_discount=('discount', 'mean')
).nlargest(5, 'revenue')
print(f"\n=== Top 5 Products ===\n{top_products}")

# 5. Low stock alert
low_stock = full[full['is_low_stock']].groupby('product_name').agg(
    stock=('stock', 'first'),
    reorder_level=('reorder_level', 'first'),
    total_orders=('order_id', 'count')
).sort_values('total_orders', ascending=False)
print(f"\n=== Low Stock Alert ===\n{low_stock.head(5)}")
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 your pandas version.

7. Styler Report and Multi-Sheet Export

▶ 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 your pandas version.

: Styler report + Excel export (Difficulty: 3/5 stars)

PYTHON 
# ============================================
# Step 7: Styled Report + Export
# ============================================

# Style the segment × category pivot
styled_pivot = (seg_cat.style
    .format('${:,.0f}')
    .background_gradient(cmap='RdYlGn', axis=None)
    .set_caption('Revenue by Segment × Category')
)

# Style top products
styled_products = (top_products.style
    .format({'revenue': '${:,.0f}', 'quantity': '{:,}', 'avg_discount': '{:.1%}'})
    .bar(subset=['revenue'], color='lightblue')
    .highlight_max(subset=['revenue'], color='lightgreen')
)

# Style low stock alert
styled_stock = (low_stock.head(10).style
    .format({'stock': '{:.0f}', 'reorder_level': '{:.0f}', 'total_orders': '{:,}'})
    .background_gradient(subset=['total_orders'], cmap='YlOrRd')
    .set_caption('Low Stock Alert — High Demand Products')
)

# Export to Excel with multiple sheets
output_path = 'ecommerce_report.xlsx'
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
    # Summary sheet
    summary = pd.DataFrame({
        'Metric': ['Total Revenue', 'Total Profit', 'Total Orders',
                    'Unique Customers', 'Unique Products', 'Avg Order Value',
                    'Profit Margin'],
        'Value': [full['revenue'].sum(), full['profit'].sum(), len(full),
                  full['customer_id'].nunique(), full['product_id'].nunique(),
                  full['revenue'].mean(), full['profit'].sum() / full['revenue'].sum()]
    })
    summary.to_excel(writer, sheet_name='Summary', index=False)

    # Region × Category
    region_cat.to_excel(writer, sheet_name='Region-Category')

    # Monthly trend
    monthly_region.to_excel(writer, sheet_name='Monthly-Trend')

    # Segment × Category (styled)
    styled_pivot.to_excel(writer, sheet_name='Segment-Category')

    # Top Products
    top_products.to_excel(writer, sheet_name='Top-Products')

print(f"✅ Report exported: {output_path}")

# Final summary
print("\n" + "=" * 50)
print("  E-COMMERCE COMPREHENSIVE ANALYSIS COMPLETE")
print("=" * 50)
print(f"  Revenue: ${full['revenue'].sum():,.0f}")
print(f"  Profit: ${full['profit'].sum():,.0f}")
print(f"  Margin: {full['profit'].sum() / full['revenue'].sum():.1%}")
print(f"  Top Region: {full.groupby('region')['revenue'].sum().idxmax()}")
print(f"  Top Category: {full.groupby('category')['revenue'].sum().idxmax()}")
print(f"  Low Stock Items: {len(low_stock)}")
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 your pandas version.

❓ FAQ

Q How do you unify formats across multiple data sources?
A Standardize in three steps: (1) Normalize column names -- use the same name for the same key (e.g., customer_id everywhere); (2) Unify types -- convert all date columns with to_datetime and all ID columns to int; (3) Unify encoding -- prefer UTF-8, and convert GBK to UTF-8. Before merging, check the dtype and unique values of key columns to confirm they can match.
Q How do you quantify data quality?
A Assess four dimensions: (1) Completeness (missing rate = missing count / total count, target < 5%); (2) Consistency (do key columns have matches in related tables?); (3) Accuracy (proportion of outliers, e.g., negative amounts or extreme values); (4) Timeliness (when was the data last updated -- is it stale?). Score each dimension from 1 to 5 for an overall assessment.
Q What if the merge chain gets too long?
A Merge step by step and validate after each join -- check row count and missing values every time. For a 5-table merge, do not write it all at once. Break it into 4 steps: orders+products -> +customers -> +regions -> +inventory. After each step, print(shape, missing) to confirm correctness before proceeding.
Q How do you automate reporting?
A Use ExcelWriter to produce multiple sheets (summary + per-dimension analysis + styled pivots), or use Styler.to_html() to output a web-based report. Going further: use Jupyter Notebook + nbconvert to auto-generate PDFs. The ultimate approach: parameterize notebooks with papermill and run them on a schedule to produce reports automatically.
Q How does this compare to BI tools?
A Pandas excels at flexible, custom analysis -- it is not limited by a BI tool's built-in features, and you can implement arbitrarily complex logic. BI tools (Tableau/Power BI) are better suited for standardized dashboards and interactive exploration -- drag-and-drop charting is fast, but complex calculations are constrained. Strategy: use Pandas for deep analysis and data preparation, then use BI tools for visual presentation.
Q How do you build multi-dimensional pivot tables?
A Pass multiple columns to index: pd.pivot_table(df, index=['region','segment'], columns='category'), which produces MultiIndex columns. Set margins=True to add row and column totals. Cross-analysis means one dimension as rows, one as columns, and one as values -- this is the most intuitive way to display three-dimensional data.
Q How do you implement low-stock alerts?
A Compare stock against reorder_level -- df[df['stock'] < df['reorder_level']]. To prioritize, sort by order volume: low stock + high order volume = most urgent. Use Styler's background_gradient to indicate urgency level, with red meaning "restock immediately."

📖 Summary

📝 Exercises

  1. Basic (Difficulty: 1/5 stars): Create 2 DataFrames (customers + orders), merge them, compute total sales by region, and use the indicator parameter to check match coverage.
  2. Intermediate (Difficulty: 2/5 stars): Simulate 3 data sources (customers/products/orders), perform 3 merges -> clean -> multi-dimensional groupby aggregation -> pivot_table cross-tab -> Styler formatting.
  3. Challenge (Difficulty: 3/5 stars): Complete the full project from this lesson: load 5 sources -> quality audit -> 4-table merge -> pipe cleaning -> multi-dimensional groupby + pivot -> Styler report -> ExcelWriter multi-sheet export.

<- Previous Lesson: Project - Time Series | Course Complete ->

Web-Tutorial.com

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

100%

🙏 帮我们做得更好

我们是刚上线的编程教程站，几个人的小团队，精力有限。页面虽经检查，难免还有疏漏——链接失效、排版错乱、内容有误、语言生硬……

如果您发现了，麻烦告诉我们，我们会在收到反馈后第一时间进行修复，再次感谢您的光临 🙏