CODEWITHSUNDEEP
pythonpandasdataframe
James Wong
James Wong

Reputation: 1137

Join pandas series of multIndex

How can I join Series A multiindexed by (A, B) with Series B indexed by A?

Upvotes: 1

Views: 65

Answers (1)

unutbu
unutbu

Reputation: 879939

Currently the only way is to bring the indices to a common footing -- e.g. move the B level of the series_A MultiIndex to a column so that both series_A and series_B are indexed only by A:

import pandas as pd

series_A = pd.Series(1, index=pd.MultiIndex.from_product([['A1', 'A4'],['B1','B2']], names=['A','B']), name='series_A')
# A   B 
# A1  B1    1
#     B2    1
# A4  B1    1
#     B2    1
# Name: series_A, dtype: int64

series_B = pd.Series(2, index=pd.Index(['A1', 'A2', 'A3'], name='A'), name='series_B')
# A
# A1    2
# A2    2
# A3    2
# Name: series_B, dtype: int64

tmp = series_A.to_frame().reset_index('B')
result = tmp.join(series_B, how='outer').set_index('B', append=True)
print(result)

yields

        series_A  series_B
A  B                      
A1 B1        1.0       2.0
   B2        1.0       2.0
A2 NaN       NaN       2.0
A3 NaN       NaN       2.0
A4 B1        1.0       NaN
   B2        1.0       NaN

Another way to join them would be to unstack the B level from series_A:

In [215]: series_A.unstack('B').join(series_B, how='outer')
Out[215]: 
     B1   B2  series_B
A                     
A1  1.0  1.0       2.0
A2  NaN  NaN       2.0
A3  NaN  NaN       2.0
A4  1.0  1.0       NaN

unstack moves the B index level to the column index. Thus the theme is the same (bring the indices to a common footing), though the result is different.

Upvotes: 2

Related Questions