Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Build a Product Co-occurrence Matrix from Order Lines

Find out which products are bought together from an order-lines sheet: build an order × product incidence table, multiply it by its transpose to count co-occurrences, read the diagonal as order counts, rank pairs by support, confidence and lift, avoid the pitfalls of quantities and duplicate lines, and write a heatmap matrix and a top-pairs list to Excel.
Pivots & summariespandas

"Customers who bought a drill also bought…" is one of the most useful questions a sales dataset can answer: it drives cross-sell suggestions, shelf placement, bundle pricing and stock planning. The data usually already exists as order lines — one row per product per order — in an Excel export. Turning that into a product × product table of how often each pair appears in the same order is a short piece of pandas: build an order × product table of 0s and 1s, and multiply it by its own transpose. From the counts, three standard measures — support, confidence and lift — separate genuinely related products from ones that just happen to be popular. This page belongs to Creating Pivot Tables from Excel Data.

Prerequisites

Bash
pip install pandas numpy openpyxl xlsxwriter

order_lines.xlsx holds 400 orders as order lines: order ID, product and quantity. Eight hardware and garden products; some are often bought together, some are just popular, and one order line is duplicated — the kind of glitch an export can contain.

Step 1 — An order × product incidence table

Python
import pandas as pd

lines = pd.read_excel("order_lines.xlsx")
basket = pd.crosstab(lines["OrderID"], lines["Product"]).clip(upper=1)
print(basket.head())
print(basket.shape[0], "orders,", basket.shape[1], "products")

crosstab counts lines per order and product; clip(upper=1) turns any count above one into 1, so the table records only whether an order contains a product. Clipping handles the duplicated line, and it also means quantities do not matter: an order with three pairs of gloves counts once for gloves, which is what "bought together" means. If you want quantity-weighted analysis, it is a different question and needs a different table.

Order lines become a 0/1 table of orders by products On the left order lines — order 1001 with Drill and Drill bits, order 1002 with Gloves, order 1003 with Hose and Hose reel. On the right one row per order with a 1 under each product it contains. A B C 1 OrderID Product Qty 2 1001 Drill 1 3 1001 Drill bits 2 4 1002 Gloves 3 5 1003 Hose 1 6 1003 Hose reel 1 A B C D E F 1 Order Drill Bits Gloves Hose Reel 2 1001 1 1 0 0 0 3 1002 0 0 1 0 0 4 1003 0 0 0 1 1 order lines incidence table crosstab + clip(upper=1)

Step 2 — Co-occurrence counts with one matrix product

Python
co = basket.T.dot(basket)
print(co)

Multiplying the transposed table by itself counts, for every pair of products, the orders that contain both: each order contributes 1 × 1 to the cells of the products it holds together. The diagonal is each product's own order count (a product always co-occurs with itself), and the matrix is symmetric — drill with bits equals bits with drill. With eight products the result is an 8 × 8 table; with a few thousand products it is still one fast operation, though the table becomes too large to read and Step 4's pair list becomes the useful output.

Step 3 — Support, confidence and lift

Raw counts favour popular products: gloves appear with everything because gloves are in a third of all orders. Three ratios correct for that:

Python
import numpy as np

n_orders = len(basket)
counts = pd.Series(np.diag(co), index=co.index)
upper = co.where(np.triu(np.ones(co.shape, dtype=bool), k=1)).rename_axis(index="A", columns="B")
pairs = upper.stack().dropna().rename("Together").reset_index()
pairs["Together"] = pairs["Together"].astype(int)
pairs["Support"] = pairs["Together"] / n_orders
pairs["Conf A→B"] = pairs["Together"] / pairs["A"].map(counts)
pairs["Conf B→A"] = pairs["Together"] / pairs["B"].map(counts)
pairs["Lift"] = pairs["Support"] / ((pairs["A"].map(counts) / n_orders) * (pairs["B"].map(counts) / n_orders))
print(pairs.sort_values("Lift", ascending=False).round(3).head(8).to_string(index=False))
  • Support is the share of all orders that contain both products.
  • Confidence A→B is the share of orders with A that also contain B — "of drill buyers, how many bought bits?" It is directional: B→A usually differs.
  • Lift compares how often the pair occurs with how often it would if the products were bought independently. Above 1 means they go together more than chance; around 1 means no relationship; below 1 means they rarely meet.

Taking the upper triangle (np.triu with k=1) keeps each pair once and drops the diagonal; dropna() removes the masked lower half, which pandas 3's stack keeps as NaN rows; and renaming the axes to A and B first matters, because both axes are called Product after the crosstab and reset_index refuses to create two columns with the same name. Sorted by lift, the true companions — drill with bits and wall plugs, hose with hose reel — come first, while gloves, despite large raw counts with everything, have lift at or below 1 — gloves and drills share 64 orders, yet that is fewer than independence would predict.

Lift separates real companions from popular products Lift for selected pairs. Hose with Hose reel has lift 3.27, Drill bits with Wall plugs 2.5 and Drill with Drill bits 2.34, so they are bought together far more than chance. Gloves with Drill has lift 0.81 despite 64 shared orders, because gloves are in many orders anyway. Hose ~ Hose reel 3.27 Drill bits ~ Wall plugs 2.5 Drill ~ Drill bits 2.34 Gloves ~ Drill 0.81 · just popular Gloves and drills share 64 orders, more than most pairs — yet less than chance would predict.

Step 4 — Filter out pairs that are too rare to trust

Lift is unstable for rare products: two orders that both contain a slow seller can produce an enormous lift from pure chance. Set a minimum number of orders before ranking:

Python
MIN_TOGETHER = 15
top = (pairs[pairs["Together"] >= MIN_TOGETHER]
       .sort_values(["Lift", "Together"], ascending=False)
       .head(5))
print(top[["A", "B", "Together", "Conf A→B", "Conf B→A", "Lift"]].round(2).to_string(index=False))

A threshold of 15 orders is a judgement call that depends on volume; the point is to have one. The resulting short list is what merchandisers act on, with both confidences shown so they can see the direction: most hose-reel buyers bought a hose, but many hose buyers did not buy a reel — the cross-sell opportunity.

Step 5 — Write a heatmap and the top pairs to Excel

Python
with pd.ExcelWriter("co_occurrence.xlsx", engine="xlsxwriter") as xw:
    co.to_excel(xw, sheet_name="Matrix")
    ws = xw.sheets["Matrix"]
    ws.conditional_format(1, 1, len(co), len(co.columns), {"type": "2_color_scale",
                          "min_color": "#FFFFFF", "max_color": "#5B5CF0"})
    ws.set_column(0, len(co.columns), 11)
    pairs.sort_values("Lift", ascending=False).round(3).to_excel(xw, sheet_name="Pairs", index=False)
    top.round(3).to_excel(xw, sheet_name="Top pairs", index=False)
print("sheets written: Matrix, Pairs, Top pairs")

The matrix sheet with a two-colour scale makes clusters visible at a glance — the drill group and the hose group light up as blocks. The pairs sheet is the full sortable list. Colour scales from Python are covered in Turn a Pivot Table into a Heatmap in Excel.

The co-occurrence matrix as a heatmap A product by product matrix of shared orders. The diagonal holds each product's order count — 160 for Drill. Drill, Drill bits and Wall plugs form a block of high values (105, 75, 56), as do Hose and Hose reel (54); Gloves has moderate values with everything. A B C D E F G 1 Drill Bits Plugs Hose Reel Gloves 2 Drill 160 105 75 42 23 64 3 Bits 105 112 56 33 20 45 4 Plugs 75 56 80 22 15 30 5 Hose 42 33 22 110 54 45 6 Reel 23 20 15 54 60 25 The diagonal holds each product's own order count, e.g. 160 orders contain a drill.

Step 6 — Recommendations per product

For a product page or a sales script, turn the pairs into "top companions" for each product, ranked by confidence in that direction, with the same minimum count:

Python
both_ways = pd.concat([
    pairs.rename(columns={"A": "Product", "B": "Companion", "Conf A→B": "Confidence"}),
    pairs.rename(columns={"B": "Product", "A": "Companion", "Conf B→A": "Confidence"}),
])[["Product", "Companion", "Together", "Confidence", "Lift"]]
recs = (both_ways[(both_ways["Together"] >= MIN_TOGETHER) & (both_ways["Lift"] > 1.1)]
        .sort_values(["Product", "Confidence"], ascending=[True, False])
        .groupby("Product").head(2))
print(recs.round(2).to_string(index=False))

Each pair appears twice — once in each direction, with its own confidence — so that every product can look up its companions. Requiring lift above 1.1 removes companions that are merely popular, which is why gloves are not suggested with everything. The result reads naturally: a hose-reel buyer is shown the hose (90% of reel buyers bought one), a drill buyer drill bits and wall plugs. A few weaker suggestions survive the filters — hose reel with drill bits at lift 1.19, saw with wall plugs at 1.20 — and are worth checking against common sense before they go live; raising the lift threshold to 1.5 would remove them. The same long table can feed a lookup on a product sheet in the workbook, as in Write Lookup Formulas (XLOOKUP, VLOOKUP, INDEX/MATCH) with openpyxl.

Performance notes

The incidence table has one column per product, so a catalogue of 20,000 products and a million orders makes a table that is huge and almost entirely zeros. For that scale, build it as a sparse matrix — scipy.sparse.csr_matrix from the order and product codes — and compute X.T @ X sparsely; only pairs that actually co-occur take memory. Keep the analysis to the top few hundred products by order count for anything written to Excel, where a 20,000 × 20,000 matrix would exceed the sheet's column limit anyway.

Common pitfalls

  • Counting quantities. Use 0/1 incidence; quantities inflate pairs with bulk items.
  • Duplicate order lines. clip(upper=1) neutralises them; summing raw lines would not.
  • Ranking by raw counts. Popular products dominate; use lift (and confidence) instead.
  • Trusting rare pairs. Require a minimum number of shared orders before ranking.
  • Reading direction into lift. Lift is symmetric; confidence gives the direction.

Conclusion

Build an order × product table of 0s and 1s with crosstab(...).clip(upper=1), multiply its transpose by itself to count orders containing each pair, and read the diagonal as each product's order count. Convert counts into support, confidence and lift so that popular products do not crowd out real companions, filter pairs below a minimum count, and write the matrix as a heatmap plus a sortable pair list to Excel.

Frequently asked questions

How do I find which products are bought together in pandas? Create an order-by-product table with pd.crosstab(lines"OrderID", lines"Product").clip(upper=1), then compute basket.T.dot(basket). Each cell counts the orders containing both products.

What is lift in market basket analysis? Lift is the share of orders containing both products divided by the share you would expect if they were bought independently. Values above 1 mean the products appear together more often than chance.

Why do some products appear with everything in my co-occurrence matrix? Popular products are in many orders, so they share high raw counts with every other product. Use lift or confidence, which account for each product's own popularity.

What is on the diagonal of a co-occurrence matrix? The number of orders that contain each product, because every product always co-occurs with itself.

More on pivots & summaries

More on pivots & summaries →