Expand and merge Pandas dataframes

Question

I have two data frames that I would like to combine. DF1 has this form

index c1 c2 a1 1 2 a1 2 1 a1 3 1 b1 5 2 b1 4 7 

DF2 is another dataset that shares a compressed version of an index

 index c3 c4 a1 9 10 b1 7 8 

I would like to populate DF1 with data from DF2

 index c1 c2 c3 c4 a1 1 2 9 10 a1 2 1 9 10 a1 3 1 9 10 b1 5 2 7 8 b1 4 7 7 8 

What is the most efficient way to do this?

Answer 1

You want to do external merging and set left_index=True, right_index=True :

 In [65]: DF1.merge(DF2, how='outer', left_index=True, right_index=True) Out[65]: c1 c2 c3 c4 index a1 1 2 9 10 a1 2 1 9 10 a1 3 1 9 10 b1 5 2 7 8 b1 4 7 7 8 

external join will also work:

 In [66]: DF1.join(DF2, how='outer') Out[66]: c1 c2 c3 c4 index a1 1 2 9 10 a1 2 1 9 10 a1 3 1 9 10 b1 5 2 7 8 b1 4 7 7 8