Cross Join
Python · Combining DataFrames (SQL Joins & Unions)
Theory
A cross join pairs every row of one table with every row of another, so 3 stores and 2 months give 3 x 2 = 6 rows. In pandas it is left.merge(right, how='cross'); no key is needed and any on= argument is not allowed. The result has all columns from both sides, in left-table order, with each left row repeated once per right row.
Cross joins are mostly used to build a complete grid of combinations: every store x every month, every product x every size, every employee x every training. You then left-join actual data onto the grid, so missing combinations show up as NaN (or 0 after fillna) instead of silently disappearing from a report. Be careful with size - 10,000 x 10,000 rows is 100 million rows. pd.MultiIndex.from_product([stores, months]) builds the same grid as an index, useful for reindex().
Why we use it
Reports often need a row for every combination, including those with no activity, such as zero-sales months. A cross join creates that full grid, which ordinary joins cannot do because the missing rows do not exist yet.
When to use it
Use how='cross' for small dimension tables you want to combine completely, typically before a left join with fact data. Avoid it on large tables; if you only need matching pairs, use a normal merge on a key.
How it works
- stores.merge(months, how='cross')
- Take each store row
- Pair it with every month row
- Repeat for every store
- Need values per combination? Yes: Left-join facts, then fillna(0). No: Use the grid as is.
- Full grid of combinations
Syntax
left.merge(right, how='cross')
pd.MultiIndex.from_product([list_a, list_b], names=['a', 'b'])Example
grid = stores.merge(months, how='cross')
report = grid.merge(sales, on=['store', 'month'], how='left').fillna({'amount': 0})Key points
- how='cross' needs no key; rows = len(left) * len(right)
- Typical pattern: cross join dimensions, then left-join the facts
- fillna(0) after the left join turns missing combinations into zeros
- Watch the size: it grows multiplicatively
- SQL equivalent: SELECT * FROM stores CROSS JOIN months