Pandas Equivalent of SQL CROSS JOIN (Cartesian Product)

Question

Let's say I had two tables:

Table1:

   col1  col2
      0     1
      2     3

Table2:

   col3  col4
      5     6
      7     8

In SQL, if I made the following statement:

Select *
From Table1, Table2;

I would expect to get back a table with all combinations from both tables:

col1 col2 col3 col4
   0    1    5    6
   0    1    7    8
   2    3    5    6
   2    3    7    8

Is there a way to do the same thing with two dataframes in pandas?

I actually prefer the accepted solution here over the one linked to above (as it assumes "you have a key that is repeated for each row" in both dataframes). — Dan, Jun 18 '18 at 18:54

score 19 · Accepted Answer · answered Jan 14 '18 at 23:13

19

A standard idiom is using the merge on a dummy column.

df1.assign(foo=1).merge(df2.assign(foo=1)).drop('foo', 1)

   col1  col2  col3  col4
0     0     1     5     6
1     0     1     7     8
2     2     3     5     6
3     2     3     7     8

answered Jan 14 '18 at 23:13

cs95

379,657
97
704
746

1

In pandas version 0.23.4 one must give also `on='foo'` parameter to merge. Like `df1.assign(foo=1).merge(df2.assign(foo=1), on='foo').drop('foo', 1)`. – samu Nov 02 '18 at 16:30
2

@samutamm it is required only if non-key columns across the DataFrames share the same name. – cs95 Dec 06 '18 at 15:48
Good, thank you @cs95 – Roger Nieto Apr 17 '20 at 03:09

Pandas Equivalent of SQL CROSS JOIN (Cartesian Product)

1 Answers1