pandas cross join no columns in common

Question

How would you perform a ~~full outer join~~ a cross join of two dataframes with no columns in common using pandas?

In MySQL, you can simply do:

SELECT *
FROM table_1
[CROSS] JOIN table_2;

But in pandas, doing:

df_1.merge(df_2, how='outer')

gives an error:

MergeError: No common columns to perform merge on

The best solution I have so far is using sqlite:

import sqlalchemy as sa
engine = sa.create_engine('sqlite:///tmp.db')
df_1.to_sql('df_1', engine)
df_2.to_sql('df_2', engine)
df = pd.read_sql_query('SELECT * FROM df_1 JOIN df_2', engine)

score 37 · Accepted Answer · edited Sep 10 '22 at 09:07

37

Update:
From Paul's comment, you can now use df = df1.merge(df2, how="cross").

The older method of creating temporary columns:
IIUC you need merge with temporary columns tmp of both DataFrames:

import pandas as pd

df1 = pd.DataFrame({'fld1': ['x', 'y'],
                'fld2': ['a', 'b1']})


df2 = pd.DataFrame({'fld3': ['y', 'x', 'y'],
                'fld4': ['a', 'b1', 'c2']})

print df1
  fld1 fld2
0    x    a
1    y   b1

print df2
  fld3 fld4
0    y    a
1    x   b1
2    y   c2

df1['tmp'] = 1
df2['tmp'] = 1

df = pd.merge(df1, df2, on=['tmp'])
df = df.drop('tmp', axis=1)
print df
  fld1 fld2 fld3 fld4
0    x    a    y    a
1    x    a    x   b1
2    x    a    y   c2
3    y   b1    y    a
4    y   b1    x   b1
5    y   b1    y   c2

edited Sep 10 '22 at 09:07

Nav

19,885
27
92
135

answered Feb 08 '16 at 11:09

jezrael

822,522
95
1,334
1,252

1

Looks like a hack, but it works! And waaay faster than sqlite. Wish there was a cleaner way to do this though... :( – ostrokach Feb 08 '16 at 11:18
1

It is call for [enhancement](https://github.com/pydata/pandas/issues/5401), so maybe in later version in `pandas` it will be possible. – jezrael Feb 08 '16 at 11:27
4

Now use df1.merge(df2, how="cross") – Paul Rougieux Mar 18 '22 at 18:19

score 2 · Answer 2 · answered Feb 08 '16 at 09:06

Even in MySQL you have to specify which fields are you joining on.

http://dev.mysql.com/doc/refman/5.7/en/join.html

Example:

SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);

Same concept with Pandas:

Parameters: 
right : DataFrame
how : {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’
left: use only keys from left frame (SQL: left outer join)
right: use only keys from right frame (SQL: right outer join)
outer: use union of keys from both frames (SQL: full outer join)
inner: use intersection of keys from both frames (SQL: inner join)
on : label or list
Field names to join on. Must be found in both DataFrames. If on is None and not merging on indexes, then it merges on the intersection of the columns by default.
left_on : label or list, or array-like
Field names to join on in left DataFrame. Can be a vector or list of vectors of the length of the DataFrame to use a particular vector as the join key instead of columns
right_on : label or list, or array-like
Field names to join on in right DataFrame or vector/list of vectors per left_on docs

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html

In MySQL, the ON clause [is optional](http://stackoverflow.com/a/16471286/2063031). Without it, MySQL performs a cross join. I edited the question to be more clear. — ostrokach, Feb 08 '16 at 09:14

pandas cross join no columns in common

2 Answers2

Linked