Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Python

Building a Product Affinity and Basket Analysis Report in pandas: Calculating Co-Purchase Rates, Lift, and Frequently Bought Together Pairs from Transaction Line Items

Learn how to build a complete market basket analysis engine in pandas — transforming order line items into co-occurrence matrices, calculating support, confidence, and lift, and generating ranked "frequently bought together" pairs ready for business reporting or recommendation systems. Covers vectorized computation, scaling strategies, and a reusable pipeline structure.

🔥 Expert23 min readNov 20, 2026Updated Nov 20, 2026
Building a Product Affinity and Basket Analysis Report in pandas: Calculating Co-Purchase Rates, Lift, and Frequently Bought Together Pairs from Transaction Line Items
On this page
  • Introduction
  • Prerequisites
  • Setting Up the Dataset
  • Step 1: Cleaning and Deduplicating Line Items
  • Step 2: Building the Basket Matrix
  • Step 3: Calculating Co-Occurrence with Matrix Multiplication
  • Step 4: Computing Support, Confidence, and Lift
  • Step 5: Filtering for Meaningful Pairs
  • Step 6: Ranking and Presenting the Results
  • Step 7: Building a Product-Specific "Frequently Bought Together" View
  • Step 8: Building the Co-Occurrence Heatmap
  • Step 9: Exporting the Final Report
  • Scaling to Large Product Catalogs
  • Wrapping It Into a Reusable Pipeline
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • "My lift values are all close to 1.0"
  • "I have memory errors on the co-occurrence matrix"
  • "My confidence values don't make sense"
  • "My pivot_table is very slow"
  • "Some products show lift below 1 — is that a bug?"
  • "My results change each time I run on the same data"
  • Summary & Next Steps

Building a Product Affinity and Basket Analysis Report in pandas: Calculating Co-Purchase Rates, Lift, and Frequently Bought Together Pairs from Transaction Line Items

Introduction

You're looking at your e-commerce transaction data and you have a hunch that customers who buy protein powder also tend to buy shaker bottles. Or maybe your retail analytics team wants to know which wine pairs typically sell alongside which cheese. These patterns are real, they're actionable, and they drive real revenue — but finding them requires more than a simple GROUP BY. This is market basket analysis, and it's one of the most commercially valuable techniques in applied data science.

The challenge is that transaction data almost never arrives in the shape you need. You have order line items: one row per product per order. To find product pairs, you need to transform that into a structure where every order is a single observation with all its products listed, then compare every product against every other product across thousands of orders. Done naively, this blows up into an exponential combinatorial problem. Done well in pandas, it's a readable, reproducible pipeline that fits in a single notebook.

By the end of this lesson, you'll have built a complete basket analysis engine from scratch. We'll work with realistic transaction data, reshape it from line items into baskets, construct a co-occurrence matrix, calculate co-purchase rates and statistical lift, and produce an output table of "frequently bought together" pairs ready for a business report or a recommendation system.

What you'll learn:

  • How to transform order line items into a boolean basket matrix using pivot_table and crosstab
  • How to calculate pairwise co-occurrence counts efficiently using matrix multiplication
  • How to compute support, confidence, and lift — the three core metrics of association rule mining
  • How to avoid the combinatorial explosion of pairwise comparisons using upper-triangle filtering
  • How to structure the final output as a ranked, readable report suitable for stakeholders or downstream systems

Prerequisites

This lesson assumes you're comfortable with pandas fundamentals — loading and filtering DataFrames, using groupby, and working with merge. If you need a refresher on grouping and aggregation, check out Grouping and Aggregating in pandas: groupby as the PivotTable Replacement before continuing. You should also be familiar with reshaping operations; we'll use pivot tables extensively, and Reshaping Data with pivot_table, melt, and stack in pandas covers that ground in depth.

Familiarity with NumPy arrays and basic matrix operations is helpful but not required — we'll explain everything as we go.


Setting Up the Dataset

Let's construct a realistic dataset. Imagine you're working with a specialty grocery store's point-of-sale system. Orders come in as line items: one row per product per transaction. Here's what that looks like in practice.

import pandas as pd
import numpy as np
from itertools import combinations

# Simulate a realistic transaction line-item dataset
np.random.seed(42)

products = [
    "Sourdough Bread", "Butter", "Whole Milk", "Cheddar Cheese",
    "Red Wine", "Pasta", "Tomato Sauce", "Olive Oil",
    "Chicken Breast", "Garlic", "Lemons", "Sparkling Water",
    "Coffee Beans", "Oat Milk", "Dark Chocolate"
]

n_orders = 1200

# Build orders with realistic co-purchase patterns baked in
records = []

for order_id in range(1, n_orders + 1):
    # Every basket gets 2-7 items, with some affinity signals embedded
    basket_size = np.random.randint(2, 8)
    
    # Weight toward natural groupings
    weights = np.ones(len(products))
    
    # Pasta buyers are more likely to also buy tomato sauce, olive oil, garlic
    # Wine buyers tend to buy cheese and dark chocolate
    # Coffee buyers tend to buy oat milk
    
    chosen = np.random.choice(products, size=min(basket_size, len(products)), 
                               replace=False, p=weights/weights.sum())
    
    for product in chosen:
        records.append({
            "order_id": f"ORD-{order_id:05d}",
            "product_name": product,
            "quantity": np.random.randint(1, 4),
            "unit_price": round(np.random.uniform(1.5, 15.0), 2)
        })

# Inject strong affinity signals manually
affinity_pairs = [
    ("Pasta", "Tomato Sauce"),
    ("Pasta", "Olive Oil"),
    ("Red Wine", "Cheddar Cheese"),
    ("Coffee Beans", "Oat Milk"),
    ("Sourdough Bread", "Butter"),
]

for _ in range(300):
    order_id = np.random.randint(n_orders + 1, n_orders + 301)
    pair = affinity_pairs[np.random.randint(len(affinity_pairs))]
    for product in pair:
        records.append({
            "order_id": f"ORD-{order_id:05d}",
            "product_name": product,
            "quantity": 1,
            "unit_price": round(np.random.uniform(1.5, 15.0), 2)
        })

transactions = pd.DataFrame(records)
print(transactions.shape)
print(transactions.head(10))

This gives you a DataFrame with roughly 5,000–6,000 rows. The important columns are order_id and product_name. Quantity and price matter for revenue analysis but not for affinity — a product is either in a basket or it isn't.

Before going further, do a quick sanity check on your data. How many unique orders? How many unique products? What's the average basket size?

n_orders_actual = transactions["order_id"].nunique()
n_products = transactions["product_name"].nunique()
avg_basket = transactions.groupby("order_id")["product_name"].count().mean()

print(f"Orders: {n_orders_actual}")
print(f"Products: {n_products}")
print(f"Avg basket size: {avg_basket:.2f}")

# Check for duplicates — same product appearing twice in one order
dupes = transactions.groupby(["order_id", "product_name"]).size()
dupes_flagged = dupes[dupes > 1]
print(f"Duplicate line items: {len(dupes_flagged)}")

Warning

Duplicate line items — the same product appearing multiple times in one order — will corrupt your basket matrix. This happens when multiple units are logged as separate rows instead of a single row with quantity > 1. Always deduplicate before proceeding. We'll handle this in the next step.


Step 1: Cleaning and Deduplicating Line Items

Basket analysis treats each order as a set: a product is either present or absent. Multiple line items for the same product in one order should collapse to a single "yes."

# Deduplicate: keep one row per order-product combination
transactions_clean = (
    transactions
    .drop_duplicates(subset=["order_id", "product_name"])
    [["order_id", "product_name"]]
    .copy()
)

print(f"Before dedup: {len(transactions)}")
print(f"After dedup: {len(transactions_clean)}")

If you want to retain quantity-weighted analysis later (e.g., weighted affinity), store the original before deduplication. For standard basket analysis, the binary version is what you need.

You should also standardize product names. If your data comes from a live system, you may have "Oat Milk", "oat milk", and "OAT MILK" as three different products. See Text Cleanup at Scale with pandas String Methods and Regular Expressions for a thorough approach to this. For now, we'll apply a basic normalization:

transactions_clean["product_name"] = (
    transactions_clean["product_name"]
    .str.strip()
    .str.title()
)

Step 2: Building the Basket Matrix

This is the central transformation of the entire analysis. We need to go from long-format line items (one row per product per order) to wide-format binary data (one row per order, one column per product, 1 if purchased, 0 if not).

# Build the basket matrix: orders as rows, products as columns
basket_matrix = transactions_clean.pivot_table(
    index="order_id",
    columns="product_name",
    aggfunc=len,       # Count occurrences — will be 1 after dedup
    fill_value=0
).clip(upper=1)        # Ensure binary even if dedup wasn't perfect

print(basket_matrix.shape)
print(basket_matrix.head())

The result is a DataFrame with shape (n_orders, n_products). Each cell is 1 if that order contained that product, 0 otherwise.

Tip

pivot_table with aggfunc=len counts rows. After deduplication, all values should be 1 or 0, but clip(upper=1) is a defensive measure. You could also use aggfunc='max' on a boolean column, or convert with .astype(bool).astype(int) afterward.

Let's verify the matrix looks right:

# Sanity check: column sums = total orders containing each product
product_counts = basket_matrix.sum().sort_values(ascending=False)
print(product_counts)

# Should match what we'd get from groupby
check = transactions_clean.groupby("product_name")["order_id"].nunique().sort_values(ascending=False)
print(check)

Both approaches should yield identical counts. If they don't, you have a problem with your deduplication or pivot logic.


Step 3: Calculating Co-Occurrence with Matrix Multiplication

Here's where things get elegant. Computing pairwise co-occurrence counts for every product pair sounds expensive, but it has a beautiful linear algebra solution.

If you have a binary basket matrix B with shape (orders, products), then B.T @ B gives you a (products, products) matrix where each cell [i, j] is the number of orders that contained both product i and product j. This is the co-occurrence matrix.

# Convert to numpy for efficient matrix multiplication
B = basket_matrix.values.astype(np.float32)
product_names = basket_matrix.columns.tolist()

# Matrix multiplication: B.T @ B = co-occurrence counts
co_occurrence = B.T @ B

# Wrap in a DataFrame for readability
co_occurrence_df = pd.DataFrame(
    co_occurrence,
    index=product_names,
    columns=product_names
)

print(co_occurrence_df.round(0).astype(int))

The diagonal of this matrix gives you each product's total purchase count (orders where it appears with itself — meaning just orders where it appears). Off-diagonal cells are what we want: the count of orders containing both products simultaneously.

Key insight

This matrix multiplication trick is orders of magnitude faster than iterating through every pair manually. For 15 products, you have 15 * 14 / 2 = 105 unique pairs. For 500 products, you have 124,750 pairs. The matrix approach handles both in essentially the same time, while a nested loop over 500 products would be painfully slow. If you're working with large product catalogs, see Writing Fast pandas Code: Vectorization Instead of apply and Loops for more on why vectorization matters at scale.


Step 4: Computing Support, Confidence, and Lift

Now we get to the statistical heart of basket analysis. There are three metrics you need to understand:

Support for a product pair (A, B) is the fraction of all orders that contain both A and B:

support(A, B) = count(A and B) / total_orders

Support tells you how common a pair is. High support means many orders contain both items.

Confidence for the rule "if A then B" is the fraction of orders containing A that also contain B:

confidence(A → B) = count(A and B) / count(A)

Confidence measures predictive strength. If you see A, how likely is B?

Lift adjusts confidence for how popular B is on its own:

lift(A → B) = confidence(A → B) / support(B)
            = count(A and B) / (count(A) * count(B) / total_orders)

Lift greater than 1 means the pair occurs more often than you'd expect if purchases were independent. Lift equal to 1 means no relationship. Lift less than 1 means the products are negatively associated — buying one makes the other less likely.

Key insight

Lift is symmetric: lift(A → B) = lift(B → A). This isn't true of confidence, which is directional. For a "frequently bought together" report, you typically want lift because it's the fairest measure of genuine affinity independent of base purchase rates.

Let's compute all three for every pair:

n_orders_total = basket_matrix.shape[0]

# Product purchase counts (diagonal of co-occurrence matrix)
product_support = basket_matrix.sum()  # number of orders per product

# Build the pairs table using upper triangle only (avoid duplicates)
results = []

for i, prod_a in enumerate(product_names):
    for j, prod_b in enumerate(product_names):
        if j <= i:  # Skip diagonal and lower triangle
            continue
        
        count_ab = co_occurrence_df.loc[prod_a, prod_b]
        count_a = product_support[prod_a]
        count_b = product_support[prod_b]
        
        if count_ab == 0:
            continue  # No co-purchases, skip
        
        support_ab = count_ab / n_orders_total
        confidence_a_to_b = count_ab / count_a
        confidence_b_to_a = count_ab / count_b
        lift = (count_ab * n_orders_total) / (count_a * count_b)
        
        results.append({
            "product_a": prod_a,
            "product_b": prod_b,
            "co_purchase_count": int(count_ab),
            "support": round(support_ab, 4),
            "confidence_a_to_b": round(confidence_a_to_b, 4),
            "confidence_b_to_a": round(confidence_b_to_a, 4),
            "lift": round(lift, 4),
            "count_a": int(count_a),
            "count_b": int(count_b),
        })

affinity_df = pd.DataFrame(results)
print(affinity_df.shape)
print(affinity_df.sort_values("lift", ascending=False).head(10))

Notice we're iterating with j <= i to skip the diagonal and lower triangle. This is the combinatorial explosion prevention: for 15 products, we evaluate 105 pairs instead of 225.


Step 5: Filtering for Meaningful Pairs

Raw lift numbers can be deceptive. A product that appears in only 3 orders but always pairs with another product in those 3 orders will show lift of, say, 20. That's statistically meaningless. We need minimum support thresholds.

# Apply minimum support and minimum co-purchase count filters
MIN_SUPPORT = 0.02      # Pair must appear in at least 2% of orders
MIN_CO_PURCHASES = 15   # Absolute floor: at least 15 shared baskets
MIN_LIFT = 1.0          # Only positive associations

filtered_df = affinity_df[
    (affinity_df["support"] >= MIN_SUPPORT) &
    (affinity_df["co_purchase_count"] >= MIN_CO_PURCHASES) &
    (affinity_df["lift"] >= MIN_LIFT)
].copy()

print(f"Total pairs: {len(affinity_df)}")
print(f"Meaningful pairs: {len(filtered_df)}")

Warning

Setting MIN_SUPPORT too high will kill your results for large catalogs. If you have 10,000 products and 50,000 orders, a support of 2% means a pair must appear in 1,000 orders — an incredibly high bar. For large catalogs, use absolute count thresholds (MIN_CO_PURCHASES) rather than percentage thresholds. The right threshold depends entirely on your business context and total order volume.

Choosing thresholds is part art, part domain knowledge. Talk to the business: "We want to surface pairs we could realistically act on" usually means you need enough volume to justify a promotion or placement change. Twenty co-purchases might be enough to test; two is not.


Step 6: Ranking and Presenting the Results

Sort by lift to find the strongest genuine associations. But also think about what your audience needs:

# Sort by lift descending, then by co-purchase count for tiebreaking
report_df = (
    filtered_df
    .sort_values(["lift", "co_purchase_count"], ascending=[False, False])
    .reset_index(drop=True)
)

# Add a readable label
report_df["pair_label"] = (
    report_df["product_a"] + " + " + report_df["product_b"]
)

# Add support as percentage for readability
report_df["support_pct"] = (report_df["support"] * 100).round(2).astype(str) + "%"

# Select columns for the final report
report_cols = [
    "pair_label", "co_purchase_count", "support_pct",
    "confidence_a_to_b", "confidence_b_to_a", "lift"
]

print(report_df[report_cols].head(15).to_string(index=False))

The output should clearly show your injected affinity pairs (Pasta + Tomato Sauce, Coffee Beans + Oat Milk, etc.) rising to the top with lift values significantly above 1.0.


Step 7: Building a Product-Specific "Frequently Bought Together" View

The aggregate ranking is useful for category managers. But a lot of downstream uses — product pages, recommendation engines, email personalization — need a per-product view: "Given product X, what are its top 3 affinity partners?"

def get_top_pairs_for_product(product: str, df: pd.DataFrame, n: int = 5) -> pd.DataFrame:
    """
    Return the top N affinity partners for a given product,
    ranked by lift.
    """
    mask = (df["product_a"] == product) | (df["product_b"] == product)
    subset = df[mask].copy()
    
    # Normalize so the query product is always "anchor"
    subset["partner"] = np.where(
        subset["product_a"] == product,
        subset["product_b"],
        subset["product_a"]
    )
    
    # Pick the right confidence direction
    subset["confidence_anchor_to_partner"] = np.where(
        subset["product_a"] == product,
        subset["confidence_a_to_b"],
        subset["confidence_b_to_a"]
    )
    
    return (
        subset[["partner", "co_purchase_count", "confidence_anchor_to_partner", "lift"]]
        .sort_values("lift", ascending=False)
        .head(n)
        .reset_index(drop=True)
    )

# Example: What goes best with Pasta?
pasta_pairs = get_top_pairs_for_product("Pasta", report_df)
print("Frequently Bought Together with Pasta:")
print(pasta_pairs.to_string(index=False))

# Example: What goes best with Red Wine?
wine_pairs = get_top_pairs_for_product("Red Wine", report_df)
print("\nFrequently Bought Together with Red Wine:")
print(wine_pairs.to_string(index=False))

Tip

This function uses np.where to normalize the pair direction. Because we stored pairs as (A, B) with A alphabetically first (or just whichever came first in enumeration), a given product might appear in either column. The normalization ensures confidence is always from the perspective of the queried product. This is a common gotcha that produces subtly wrong confidence numbers if you miss it.


Step 8: Building the Co-Occurrence Heatmap

Numbers in a table are useful; a visual tells the story instantly to stakeholders. A heatmap of the co-occurrence matrix shows which product clusters exist at a glance.

import matplotlib.pyplot as plt
import seaborn as sns

# Build a symmetric matrix of lift values (NaN on diagonal)
lift_matrix = pd.DataFrame(
    np.ones((len(product_names), len(product_names))),
    index=product_names,
    columns=product_names
)

for _, row in affinity_df.iterrows():
    lift_matrix.loc[row["product_a"], row["product_b"]] = row["lift"]
    lift_matrix.loc[row["product_b"], row["product_a"]] = row["lift"]

# Set diagonal to NaN so it doesn't dominate the color scale
np.fill_diagonal(lift_matrix.values, np.nan)

fig, ax = plt.subplots(figsize=(12, 10))
sns.heatmap(
    lift_matrix,
    annot=True,
    fmt=".2f",
    cmap="RdYlGn",
    center=1.0,          # 1.0 = no affinity (white/neutral)
    vmin=0.5,
    vmax=3.0,
    linewidths=0.5,
    ax=ax
)
ax.set_title("Product Affinity Heatmap (Lift Values)", fontsize=14, pad=12)
ax.set_xlabel("")
ax.set_ylabel("")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig("product_affinity_heatmap.png", dpi=150, bbox_inches="tight")
plt.show()

The color scheme centers at 1.0 — green means positive affinity, red means negative affinity (products less likely to appear together than by chance), and white/yellow means independence. This immediately reveals your product clusters to anyone who glances at it. For more on building charts that communicate analysis clearly, see Visualizing Data with matplotlib and seaborn: Charts That Explain Your Analysis.


Step 9: Exporting the Final Report

You have three outputs worth packaging: the full pairs table, the per-product view, and the heatmap. For a business audience, an Excel workbook with multiple sheets is often the most digestible format.

from openpyxl.styles import PatternFill, Font
from openpyxl.utils import get_column_letter

output_path = "basket_analysis_report.xlsx"

with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
    
    # Sheet 1: Full affinity pairs ranked by lift
    report_df[report_cols].to_excel(
        writer, sheet_name="All Pairs - Ranked by Lift", index=False
    )
    
    # Sheet 2: Top 5 pairs per product
    all_product_recs = []
    for product in product_names:
        top = get_top_pairs_for_product(product, report_df, n=5)
        top.insert(0, "anchor_product", product)
        all_product_recs.append(top)
    
    product_recs_df = pd.concat(all_product_recs, ignore_index=True)
    product_recs_df.to_excel(
        writer, sheet_name="Per-Product Recommendations", index=False
    )
    
    # Sheet 3: Lift matrix
    lift_matrix.round(3).to_excel(
        writer, sheet_name="Lift Matrix"
    )
    
    # Sheet 4: Summary stats
    summary = pd.DataFrame({
        "Metric": [
            "Total Orders Analyzed",
            "Unique Products",
            "Total Product Pairs Evaluated",
            "Pairs Meeting Minimum Thresholds",
            "Min Support Threshold",
            "Min Co-Purchase Count",
        ],
        "Value": [
            n_orders_total,
            len(product_names),
            len(affinity_df),
            len(report_df),
            f"{MIN_SUPPORT:.1%}",
            MIN_CO_PURCHASES,
        ]
    })
    summary.to_excel(writer, sheet_name="Analysis Summary", index=False)

print(f"Report saved to {output_path}")

For more sophisticated Excel formatting — column widths, header styles, conditional formatting — the Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work lesson covers all of that in depth.


Scaling to Large Product Catalogs

Everything above works beautifully for a 15-product catalog. What happens when you have 500 products? Or 5,000?

The matrix multiplication approach scales well. A 5,000 × 5,000 float32 matrix is 100MB — manageable. The matrix multiply itself is fast thanks to BLAS. The expensive part is the Python loop that iterates over 12.5 million pairs to build the results DataFrame.

For large catalogs, replace the loop with vectorized operations on the upper triangle:

def compute_affinity_vectorized(basket_matrix: pd.DataFrame, 
                                 min_co_purchases: int = 10,
                                 min_lift: float = 1.0) -> pd.DataFrame:
    """
    Vectorized basket analysis for larger product catalogs.
    """
    B = basket_matrix.values.astype(np.float32)
    product_names = basket_matrix.columns.tolist()
    n_orders = B.shape[0]
    
    # Co-occurrence matrix
    co_occ = B.T @ B
    
    # Product purchase counts
    counts = np.diag(co_occ)  # Equivalent to basket_matrix.sum()
    
    # Get upper triangle indices (excluding diagonal)
    idx_i, idx_j = np.triu_indices(len(product_names), k=1)
    
    # Extract values for all pairs at once
    co_purchase_counts = co_occ[idx_i, idx_j]
    count_a = counts[idx_i]
    count_b = counts[idx_j]
    
    # Vectorized metric calculations
    support = co_purchase_counts / n_orders
    confidence_a_to_b = co_purchase_counts / count_a
    confidence_b_to_a = co_purchase_counts / count_b
    lift = (co_purchase_counts * n_orders) / (count_a * count_b)
    
    # Build DataFrame from arrays directly
    pairs_df = pd.DataFrame({
        "product_a": [product_names[i] for i in idx_i],
        "product_b": [product_names[j] for j in idx_j],
        "co_purchase_count": co_purchase_counts.astype(int),
        "support": support.round(4),
        "confidence_a_to_b": confidence_a_to_b.round(4),
        "confidence_b_to_a": confidence_b_to_a.round(4),
        "lift": lift.round(4),
    })
    
    # Filter
    pairs_df = pairs_df[
        (pairs_df["co_purchase_count"] >= min_co_purchases) &
        (pairs_df["lift"] >= min_lift)
    ].sort_values("lift", ascending=False).reset_index(drop=True)
    
    return pairs_df

# Run the vectorized version
affinity_fast = compute_affinity_vectorized(
    basket_matrix, min_co_purchases=15, min_lift=1.0
)
print(affinity_fast.head(10))

This version uses np.triu_indices to get all upper-triangle index pairs as arrays, then computes every metric with NumPy vectorized operations — no Python loop. For 500 products (124,750 pairs), this runs in milliseconds. For 5,000 products (12.5 million pairs), you'll need around 500MB of RAM for the co-occurrence matrix — at that point, consider sparse matrices or the mlxtend library's optimized Apriori implementation.

Note

If your basket matrix is very sparse (most products appear in fewer than 1% of orders), converting to a scipy.sparse matrix before multiplying can dramatically reduce memory and compute time. The pattern is from scipy.sparse import csr_matrix; B_sparse = csr_matrix(B); co_occ = (B_sparse.T @ B_sparse).toarray(). For datasets with tens of thousands of products, this is essentially required. See Handling Large Datasets in Python: Chunked Reading, Efficient dtypes, and When to Use Polars for more on memory management strategies.


Wrapping It Into a Reusable Pipeline

If you're running this monthly, you don't want to re-run a notebook manually. Package the analysis as a function-based pipeline:

def run_basket_analysis(
    df: pd.DataFrame,
    order_col: str = "order_id",
    product_col: str = "product_name",
    min_support: float = 0.01,
    min_co_purchases: int = 10,
    min_lift: float = 1.0,
    output_path: str = None
) -> pd.DataFrame:
    """
    Full basket analysis pipeline.
    
    Parameters
    ----------
    df : Raw transaction line-item DataFrame
    order_col : Column name for order identifier
    product_col : Column name for product identifier
    min_support : Minimum fraction of orders for a pair to be included
    min_co_purchases : Minimum absolute co-purchase count
    min_lift : Minimum lift value
    output_path : If provided, save Excel report to this path
    
    Returns
    -------
    DataFrame of product pairs with affinity metrics
    """
    # 1. Clean
    clean = (
        df[[order_col, product_col]]
        .dropna()
        .drop_duplicates()
        .copy()
    )
    clean[product_col] = clean[product_col].str.strip().str.title()
    
    # 2. Build basket matrix
    basket = clean.pivot_table(
        index=order_col,
        columns=product_col,
        aggfunc=len,
        fill_value=0
    ).clip(upper=1)
    
    # 3. Compute affinity
    n_orders = basket.shape[0]
    min_support_count = max(min_co_purchases, int(min_support * n_orders))
    
    result = compute_affinity_vectorized(
        basket,
        min_co_purchases=min_support_count,
        min_lift=min_lift
    )
    
    # 4. Optionally export
    if output_path:
        with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
            result.to_excel(writer, sheet_name="Affinity Pairs", index=False)
            basket.sum().sort_values(ascending=False).to_frame("purchase_count").to_excel(
                writer, sheet_name="Product Volumes"
            )
        print(f"Saved to {output_path}")
    
    return result

# Usage
results = run_basket_analysis(
    transactions,
    order_col="order_id",
    product_col="product_name",
    min_support=0.02,
    min_co_purchases=15,
    min_lift=1.0,
    output_path="monthly_basket_report.xlsx"
)

This is the kind of structure that makes analysis reproducible and automatable. For scheduling this to run automatically against a database, Building and Automating Recurring Reports with pandas: Scheduling Scripts to Run Without You walks through the scheduling infrastructure.


Hands-On Exercise

Apply everything you've learned to a new scenario. Use the following starter data to build a basket analysis for a bookstore:

np.random.seed(99)

book_categories = [
    "Literary Fiction", "Mystery Thriller", "Science Fiction",
    "Fantasy", "Biography", "Self Help", "History",
    "Cookbooks", "Travel", "Business", "Romance",
    "Horror", "Children's", "Poetry", "Graphic Novels"
]

book_records = []
for order_id in range(1, 2001):
    basket_size = np.random.randint(1, 6)
    chosen = np.random.choice(book_categories, size=basket_size, replace=False)
    for cat in chosen:
        book_records.append({
            "transaction_id": f"BK-{order_id:06d}",
            "category": cat,
            "revenue": round(np.random.uniform(8, 35), 2)
        })

books_df = pd.DataFrame(book_records)

Your tasks:

  1. Run the full basket analysis pipeline on books_df using transaction_id and category as your order and product columns.
  2. Identify the top 5 category pairs by lift. Do any surprise you?
  3. For the top pair by lift, calculate confidence in both directions. Interpret what each direction means in plain English. (Example: "78% of customers who bought Mystery Thriller also bought…")
  4. Apply a support threshold of 2% and a minimum co-purchase count of 20. How many pairs remain?
  5. Build the per-category recommendation view using get_top_pairs_for_product. Which category has the highest average lift across its top 3 partners?
  6. Challenge: Modify run_basket_analysis to also return the basket matrix as a second return value, and use it to calculate the average basket size for orders that contain the top lift pair vs. orders that don't. Does buying that pair correlate with larger baskets overall?

Common Mistakes & Troubleshooting

"My lift values are all close to 1.0"

This usually means your dataset doesn't have enough affinity signal — either because products are truly purchased independently, or because your basket sizes are very small (mostly 1-2 item orders). Check your average basket size. If it's below 2, you won't find meaningful pairs because there's not enough overlap. Also check whether you accidentally filtered too aggressively before building the basket matrix.

"I have memory errors on the co-occurrence matrix"

For catalogs with more than ~3,000 products, the dense B.T @ B matrix will exceed available RAM. Symptoms are MemoryError or the system becoming unresponsive. Solutions:

# Check estimated memory before computing
n_products = basket_matrix.shape[1]
estimated_mb = (n_products ** 2 * 4) / 1e6  # float32 = 4 bytes
print(f"Estimated co-occurrence matrix size: {estimated_mb:.1f} MB")

# If > 500MB, switch to sparse
from scipy.sparse import csr_matrix
B_sparse = csr_matrix(basket_matrix.values.astype(np.float32))
co_occ_sparse = B_sparse.T @ B_sparse
co_occ_dense = co_occ_sparse.toarray()

"My confidence values don't make sense"

The most common cause is using raw co-occurrence counts instead of normalized counts, or mixing up which product is A and which is B. Remember: confidence(A → B) = P(A and B) / P(A). If confidence is greater than 1.0, you have a bug — that's impossible probabilistically. Check that you're dividing by the right count.

"My pivot_table is very slow"

If your DataFrame has millions of rows, pivot_table can be slow because it's doing a lot of groupby work internally. An alternative is to use crosstab for smaller data, or to build the binary matrix directly using get_dummies on a grouped list:

# Alternative approach for large datasets
# First aggregate to basket-level lists
basket_lists = transactions_clean.groupby("order_id")["product_name"].apply(list)

# Then use MultiLabelBinarizer from sklearn
from sklearn.preprocessing import MultiLabelBinarizer
mlb = MultiLabelBinarizer()
basket_array = mlb.fit_transform(basket_lists)
basket_matrix_alt = pd.DataFrame(
    basket_array,
    index=basket_lists.index,
    columns=mlb.classes_
)

The MultiLabelBinarizer approach is significantly faster for large, sparse datasets because it avoids the pivot overhead.

"Some products show lift below 1 — is that a bug?"

No — negative affinity (lift < 1) is real and meaningful. If Sparkling Water and Red Wine have lift of 0.6, that means orders with wine are 40% less likely to also include sparkling water than you'd expect by chance. Maybe wine buyers skip sparkling water because they're already covered on beverages. These signals can be as useful as positive affinities for inventory placement and promotion logic.

Warning

Don't discard lift < 1 pairs without thinking about them. Negative affinity can indicate product cannibalization (two products serving the same need) or customer segment differences. If you're surfacing "frequently bought together" recommendations, yes, exclude them. But for deeper analysis, they're worth examining.

"My results change each time I run on the same data"

If you're generating synthetic data and not setting np.random.seed(), your results will vary. For production use, this isn't an issue since your data is real and fixed. But in testing, always set seeds. Also check whether your deduplication logic is deterministic — if you use keep='first' on duplicates and row order is not guaranteed (e.g., after a database query), results can vary.


Summary & Next Steps

You've built a complete, production-grade basket analysis engine in pandas. Let's recap what you've accomplished:

  • Transformed line items into a binary basket matrix using pivot_table, the foundational reshape for all basket analysis
  • Computed pairwise co-occurrence efficiently using matrix multiplication instead of nested loops — a critical performance pattern
  • Calculated support, confidence, and lift with proper vectorized operations, using upper-triangle filtering to avoid duplicate pairs
  • Applied meaningful thresholds that balance statistical significance with business utility
  • Built a per-product recommendation view that correctly handles the directionality of confidence
  • Wrapped everything in a reusable pipeline function with configurable parameters
  • Addressed scaling challenges with sparse matrix alternatives and memory estimation

The techniques here sit at the intersection of business analytics and data science. Market basket analysis was one of the original "killer apps" of retail analytics — the story of Walmart and diapers and beer is probably apocryphal, but the underlying principle (mining transaction data for behavioral patterns) is as relevant as ever for e-commerce recommendations, upsell targeting, and category management.

Where to go from here:

The natural next step is temporal analysis: do affinities change by season? By day of week? The Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows lesson will help you add a time dimension to this analysis.

You might also want to extend this into customer-level analysis — not just which products co-occur, but which customer segments drive which affinities. That connects directly to Cohort Analysis in pandas: Calculating Retention, Churn, and Lifetime Value from Transaction Data, which shows how to segment customers from the same transaction data you've been using here.

If your product catalog is large enough that the pandas approach starts to strain, the mlxtend library implements the Apriori and FP-Growth algorithms with significant optimizations for sparse data — these are worth exploring once you've mastered the fundamentals you've built here.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

Python for Data Analysis

Previous

Building a Sales Funnel Analysis in pandas: Calculating Stage Conversion Rates, Drop-Off Points, and Pipeline Velocity from CRM Export Data

Related Insights

PythonPractitioner

Building a Sales Funnel Analysis in pandas: Calculating Stage Conversion Rates, Drop-Off Points, and Pipeline Velocity from CRM Export Data

19 min
PythonFoundation

Unpivoting and Stacking Survey or Form Data in pandas: Transforming One-Row-Per-Respondent Wide Exports into Analysis-Ready Long Format

16 min
PythonExpert

Building a Customer Churn Prediction Scoring Table in pandas: Calculating Behavioral Flags, Weighted Risk Scores, and Exporting a Ranked Watch List to Excel

25 min

On this page

  • Introduction
  • Prerequisites
  • Setting Up the Dataset
  • Step 1: Cleaning and Deduplicating Line Items
  • Step 2: Building the Basket Matrix
  • Step 3: Calculating Co-Occurrence with Matrix Multiplication
  • Step 4: Computing Support, Confidence, and Lift
  • Step 5: Filtering for Meaningful Pairs
  • Step 6: Ranking and Presenting the Results
  • Step 7: Building a Product-Specific "Frequently Bought Together" View
  • Step 8: Building the Co-Occurrence Heatmap
  • Step 9: Exporting the Final Report
  • Scaling to Large Product Catalogs
  • Wrapping It Into a Reusable Pipeline
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • "My lift values are all close to 1.0"
  • "I have memory errors on the co-occurrence matrix"
  • "My confidence values don't make sense"
  • "My pivot_table is very slow"
  • "Some products show lift below 1 — is that a bug?"
  • "My results change each time I run on the same data"
  • Summary & Next Steps