Python Pandas merging two data frames and map one row from one data frame to all rows from the other

Question

So basically I am trying to achieve something like this:

DataFrameA:

    A    pr
0  true  0.3
1  false 0.7

DataFrameB:

    B    pr
0  true  0.9
1  false 0.1

Result:

    A     pr_a   B     pr_b
0  true   0.3    true  0.9
1  true   0.3    false 0.1
2  false  0.7    true  0.9
3  false  0.7    false 0.1

Is there a convenient way to achieve this? How about a general case? For example A,B merge with C gives me all the mapping for A,B,C.

Thank you in advance!

So you basically want what would be called a "cross-join" in SQL right? Try using the answers in this question: http://stackoverflow.com/questions/13269890/cartesian-product-in-pandas — Nicholas Aldershof, Jun 11 '15 at 16:44

score 3 · Accepted Answer · answered Jun 11 '15 at 17:23

Found the solution.

Add another column called key and fill that column with 1s like this:

DataFrameA:

   A      pr   key
0  true   0.3  1
1  false  0.7  1

DataFrameB:

   B      pr   key
0  true   0.9  1
1  false  0.1  1

Then merge A and B together on='key' like this:

 newDataFrame = pandas.merge(DataFrameA, DataFrameB, on='key')

The result of this will be:

   A      pr_a  key  B     pr_b
0  true   0.3   1    true  0.9
1  true   0.3   1    false 0.1
2  false  0.7   1    true  0.9
3  false  0.7   1    false 0.1

At last, simply remove the 'key' column.

Python Pandas merging two data frames and map one row from one data frame to all rows from the other

1 Answers1