Join Series on MultiIndex in pandas

Question

I have multiple Series with a MultiIndex and I'd like to combine them into a single DataFrame which joins them on the common index names (and broadcasts values). The setup is like

import pandas as pd
a=pd.Series([12,13,14,15], index=pd.MultiIndex.from_tuples([(1,1),(1,2),(2,1),(2,2)], names=["i", "j"]))
b=pd.Series([21,22], index=pd.MultiIndex.from_tuples([(1,),(2,)], names=["i"]))

How can I get the result

i  j  a  b
1  1  12 21 
1  2  13 21
2  1  14 22
2  2  15 22

Can you suggest how to get this result? Ideally this should also work on more than two Series.

Answer 1

Since pandas 0.24.0, it is possible to merge multiindexed data frames with each other using the overlapping index levels. As per the release notes:

index_left = pd.MultiIndex.from_tuples([('K0', 'X0'), ('K0', 'X1'),
                                        ('K1', 'X2')],
                                        names=['key', 'X'])

left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']}, index=index_left)

index_right = pd.MultiIndex.from_tuples([('K0', 'Y0'), ('K1', 'Y1'),
                                        ('K2', 'Y2'), ('K2', 'Y3')],
                                        names=['key', 'Y'])

right = pd.DataFrame({'C': ['C0', 'C1', 'C2', 'C3'],
                      'D': ['D0', 'D1', 'D2', 'D3']}, index=index_right)

left.join(right)

Out:

            A   B   C   D
key X  Y                 
K0  X0 Y0  A0  B0  C0  D0
    X1 Y0  A1  B1  C0  D0
K1  X2 Y1  A2  B2  C1  D1

[3 rows x 4 columns]

Answer 2

This is documented here:

pd.merge(a.reset_index(), b.reset_index(), on='i')

You can set the index back if you want with set_index. Alternatively:

pd.DataFrame(b).join(pd.DataFrame(a), rsuffix='a')