pandas two dataframe cross join

Question

I can't find anything about cross join include the merge/join or some other. I need deal with two dataframe using {my function} as myfunc . the equivalent of :

{
    for itemA in df1.iterrows():
           for itemB in df2.iterrows():
                       t["A"] = myfunc(itemA[1]["A"],itemB[1]["A"])
 }

the equivalent of :

{
 select myfunc(df1.A,df2.A),df1.A,df2.A from df1,df2;
}

but I need more efficient solution: if used apply i will be how to implement them thx;^^

did you get this worked out? Comment on my answer if you have questions. — leroyJr, Dec 09 '15 at 17:32

score 62 · Answer 1 · edited Mar 15 '21 at 12:37

62

Create a common 'key' to cross join the two:

df1['key'] = 0
df2['key'] = 0

df1.merge(df2, on='key', how='outer')

edited Mar 15 '21 at 12:37

Liran Funaro

2,750
2
22
33

answered Oct 23 '17 at 18:13

A.Kot

7,615
2
22
24

As scrappy as this method is, it is very conceptually easy to understand! Thanks – chinnychinchin Apr 11 '19 at 00:25
12

you also need on='key' in the merge statement – Jon Scott Jun 25 '19 at 07:08
7

I don't think the `how` parameter has any impact on the result. You could leave it out and go with the default (which is `inner`) – alejandro Apr 30 '20 at 22:26
@alejandro OP specifically requests cross join which is the same as an outer, NOT an inner. – A.Kot Jan 29 '22 at 14:15
2

@A.Kot I see your point, but talking strictly about your code, using the `how` parameter does not affect because you are joining on a constant key, which will always match, so `inner` does not eliminate any of the rows – alejandro Feb 01 '22 at 03:49

score 12 · Accepted Answer · edited Feb 21 '22 at 20:45

For the cross product, see this question.

Essentially, you have to do a normal merge but give every row the same key to join on, so that every row is joined to each other across the frames.

You can then add a column to the new frame by applying your function:

new_df = pd.merge(df1, df2, on=key)
new_df.new_col = new_df.apply(lambda row: myfunc(row['A_x'], row['A_y']), axis=1)

axis=1 forces .apply to work across the rows. 'A_x' and 'A_y' will be the default column names in the resulting frame if the merged frames share a column like in your example above.

pandas two dataframe cross join

2 Answers2

Linked

Related