Create Every Combination of Two Lists with a Cross Join in pandas

A sales table only contains the store-and-product pairs that sold something. A report that should show every product for every store — so that a zero is visible as a zero, and a gap in a range is obvious — needs the combinations that did not happen too. The same shape appears in training records (every employee against every mandatory course), budgets (every cost centre for every month) and price lists (every product in every currency). The tool is a cross join: pair every row of one list with every row of another. pandas does it with merge(how="cross"), and a left join of the real data onto that grid fills in what actually happened. This page belongs to Merging and Joining Excel DataFrames.

Prerequisites

pip install pandas openpyxl

stores_products.xlsx has three sheets: three stores, four products, and a sales table with only the six store/product pairs that sold anything this month.

Step 1 — Every combination with merge(how="cross")

import pandas as pd

book = pd.read_excel("stores_products.xlsx", sheet_name=None)
stores, products, sales = book["Stores"], book["Products"], book["Sales"]
grid = stores.merge(products, how="cross")
print(grid.to_string(index=False))
print(len(stores), "stores ×", len(products), "products =", len(grid), "rows")

A cross join needs no key: each of the three store rows is paired with each of the four product rows, giving twelve rows that carry all columns of both tables — the store's region and the product's category come along for free. The row order is "all products for the first store, then the next store", which is usually how a report wants it.

Step 2 — Fill in what actually happened

Left-join the sales onto the grid, so every combination survives and the ones without sales get zero:

full = grid.merge(sales, on=["Store", "Product"], how="left", validate="one_to_one")
full["Units"] = full["Units"].fillna(0).astype(int)
print(full.to_string(index=False))
print("combinations with no sales:", int((full["Units"] == 0).sum()))

The left join keeps all twelve rows; the six pairs absent from the sales table get NaN, filled with 0. validate="one_to_one" makes pandas raise an error if the sales table has two rows for the same store and product, which would otherwise duplicate grid rows silently — the check explained in Validate a Merge to Avoid Duplicate Rows. Six of the twelve combinations sold nothing — information that was invisible in the sales table.

Step 3 — Present it as a matrix

The completed grid pivots cleanly into a store × product matrix, with zeros where nothing sold:

matrix = full.pivot(index="Store", columns="Product", values="Units").loc[stores["Store"], products["Product"]]
print(matrix)
gaps = full.loc[full["Units"] == 0, ["Store", "Product"]]
print(gaps.groupby("Store")["Product"].agg(", ".join))

Reindexing with .loc[stores, products] keeps the stores and products in their original list order rather than alphabetical. The gap list per store — Hull sold no drills, hoses or rakes — is often the most useful output: a ranging check for merchandisers, a missing-training list for HR. Pivoting without the cross join would also produce a matrix, but any product that no store sold would be missing as a column altogether.

Step 4 — Three dimensions, and the size check

Adding months turns the grid into store × product × month. MultiIndex.from_product builds the combinations directly from lists:

months = pd.period_range("2026-07", "2026-09", freq="M").astype(str)
idx = pd.MultiIndex.from_product([stores["Store"], products["Product"], months], names=["Store", "Product", "Month"])
cube = idx.to_frame(index=False)
print(len(cube), "rows")

def cross_size(*lists):
    n = 1
    for lst in lists:
        n *= len(lst)
    return n

print("estimated rows for 500 stores × 20,000 products × 36 months:", f"{cross_size(range(500), range(20000), range(36)):,}")

from_product and chained cross joins give the same rows; from_product is convenient when the inputs are plain lists. The size check is not decoration: the row count is the product of the list lengths, so a realistic retailer with 500 stores, 20,000 products and three years of months produces 360 million rows — far beyond what a laptop or an Excel sheet can hold. Always estimate before building, and narrow the inputs (active products only, one year) when the number is large.

Step 5 — Remove combinations that cannot happen

Not every pair is valid: garden products are not ranged in the city-centre store, a course applies only to some roles. Filter the grid with a rule table, so the zeros that remain are real gaps rather than impossibilities:

not_ranged = pd.DataFrame({"Region": ["East"], "Category": ["Garden"]})
valid = (grid.merge(not_ranged.assign(Excluded=True), on=["Region", "Category"], how="left")
             .query("Excluded != True")
             .drop(columns="Excluded"))
valid_full = valid.merge(sales, on=["Store", "Product"], how="left").fillna({"Units": 0})
print(len(grid), "combinations,", len(valid), "valid")
print(valid_full.loc[valid_full["Units"] == 0, ["Store", "Product"]].to_string(index=False))

Hull is in the East region, where garden products are not ranged, so its hose and rake combinations are removed before the gap analysis; its missing drill sales remain flagged, because drills should be stocked there. Keeping exclusions in a small table — rather than conditions in code — lets the business maintain them.

Step 6 — Write the grid to Excel

with pd.ExcelWriter("store_product_grid.xlsx", engine="xlsxwriter") as xw:
    matrix.to_excel(xw, sheet_name="Matrix")
    valid_full.to_excel(xw, sheet_name="Valid pairs", index=False)
    ws = xw.sheets["Matrix"]
    zero = xw.book.add_format({"bg_color": "#FDEFD8", "font_color": "#7A4E06"})
    ws.conditional_format(1, 1, len(matrix), len(matrix.columns), {"type": "cell", "criteria": "==", "value": 0, "format": zero})
with pd.ExcelFile("store_product_grid.xlsx") as xf:
    print("written:", xf.sheet_names)

The matrix sheet highlights the zero cells; the long sheet lists every valid pair with its units, ready for filtering. Highlighting rules are covered in Highlight Cells Above a Threshold with openpyxl.

Step 7 — The same pattern for a training-compliance grid

Swap stores for employees and products for mandatory courses and the code is unchanged — only the meaning of a gap changes. Here a missing combination is not a zero but a course nobody has completed:

staff = pd.DataFrame({"Employee": ["Adaeze", "Jonas", "Claire"]})
courses = pd.DataFrame({"Course": ["Fire safety", "Data protection", "First aid"]})
completed = pd.DataFrame({"Employee": ["Adaeze", "Adaeze", "Jonas"],
                          "Course": ["Fire safety", "Data protection", "Fire safety"],
                          "Completed": pd.to_datetime(["2026-03-02", "2025-06-15", "2026-08-20"])})
status = staff.merge(courses, how="cross").merge(completed, on=["Employee", "Course"], how="left")
expiry = status["Completed"] + pd.DateOffset(years=1)
status["Status"] = "not done"
status.loc[status["Completed"].notna(), "Status"] = "valid"
status.loc[status["Completed"].notna() & (expiry < pd.Timestamp("2026-10-02")), "Status"] = "expired"
print(status.pivot(index="Employee", columns="Course", values="Status"))

The grid gives nine employee/course pairs; three have a completion date and six do not. Adaeze's data-protection course from June 2025 has expired a year later, so the status distinguishes "valid", "expired" and "not done" — three different actions for HR. Without the cross join, Claire, who has completed nothing, would not appear in the report at all, which is precisely the person it exists to find.

Performance notes

A cross join's cost is its output size: building twelve rows is instant, building ten million takes a few seconds and a few hundred megabytes, and anything near a hundred million rows needs a different approach — aggregate first, restrict the lists, or keep the data in long form and only fill gaps within each store. The left join of facts onto the grid is a hash merge and costs about as much as building the grid. Writing a matrix to Excel is limited by the sheet: 16,384 columns and 1,048,576 rows, which a store × product matrix of a large retailer can exceed in the column direction.

Common pitfalls

• Pivoting without a grid. Combinations — and whole products — with no data disappear.
• Inner joins onto the grid. They throw the empty combinations away again; use how="left" from the grid.
• Duplicate facts. Two sales rows for one pair duplicate grid rows; validate the merge.
• Size explosions. The row count is the product of the list sizes; estimate first.
• Invalid combinations as gaps. Exclude pairs that cannot happen before reporting zeros.

Conclusion

A cross join turns two (or more) lists into every combination: left.merge(right, how="cross") or pd.MultiIndex.from_product([...]). Left-join the real data onto that grid and fill missing values, so zeros and gaps become visible, pivot it into a matrix in list order, exclude combinations that cannot occur, and estimate the size before building, because cross joins grow multiplicatively.

Frequently asked questions

How do I get every combination of two columns or lists in pandas? Use df1.merge(df2, how="cross"), which pairs every row of df1 with every row of df2. For plain lists, build a MultiIndex with from_product from the two lists and convert it with to_frame(index=False) for the same result.

How do I show missing combinations as zero? Build the full grid with a cross join, left-join the actual data onto it on the key columns, and fill the missing values with 0.

How big will a cross join be? The number of rows is the product of the input lengths, so 500 stores and 20,000 products make 10 million rows, and adding 36 months makes 360 million. Estimate before building and narrow the inputs if needed.

Is there an Excel equivalent of a cross join? Power Query can add a custom column that references the other table and expand it, and Excel 365 formulas can build combinations with dynamic arrays. For large lists, building the grid in pandas and writing the result is simpler.

Related

• Up one level: Merging and Joining Excel DataFrames — combining data from several sheets and files.
• Merge Two Excel Files on a Common Column with Python — key-based joins.
• Build a Crosstab from Excel Data with pandas — counting combinations that occur.
• Fill Missing Dates in a Time Series from Excel with pandas — the same gap-filling idea along a date axis.