Python pandas : Merge two tables without keys (Multiply 2 dataframes with broadcasting all elements; NxN dataframe)

notilas picture notilas · Feb 5, 2016 · Viewed 8.4k times · Source

I want to merge 2 dataframes with broadcast relationship: No common index, just want to find all pairs of the rows in the 2 dataframes. So want to make N row dataframe x M row dataframe = N*M row dataframe. Is there any rule to make this happen without using itertool?

DF1=
  id  quantity  
0  1        20  
1  2        23  

DF2=
      name  part  
    0  'A'   3  
    1  'B'   4  
    2  'C'   5  

DF_merged=
      id  quantity name part 
    0  1        20  'A'  3 
    1  1        20  'B'  4 
    2  1        20  'C'  5 
    3  2        23  'A'  3
    4  2        23  'B'  4
    5  2        23  'C'  5

Answer

jezrael picture jezrael · Feb 5, 2016

You can use helper columns tmp filled 1 in both DataFrames and merge on this column. Last you can drop it:

DF1['tmp'] = 1
DF2['tmp'] = 1

print DF1
   id  quantity  tmp
0   1        20    1
1   2        23    1

print DF2
  name  part  tmp
0  'A'     3    1
1  'B'     4    1
2  'C'     5    1

DF = pd.merge(DF1, DF2, on=['tmp'])
print DF
   id  quantity  tmp name  part
0   1        20    1  'A'     3
1   1        20    1  'B'     4
2   1        20    1  'C'     5
3   2        23    1  'A'     3
4   2        23    1  'B'     4
5   2        23    1  'C'     5

print DF.drop('tmp', axis=1)
   id  quantity name  part
0   1        20  'A'     3
1   1        20  'B'     4
2   1        20  'C'     5
3   2        23  'A'     3
4   2        23  'B'     4
5   2        23  'C'     5