Expand and merge Pandas dataframes

Question

I have two dataframes I would like to merge. 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 set of data, which shares a condensed version of the index

index  c3  c4
a1     9   10
b1     7   8

I would like to populate DF1 with the 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 an outer merge 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

outer join would work also:

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