Siraj S. Siraj S. - 1 month ago 8
Python Question

merge multiindex dataframe with unequal length

here are my two dataframes

index = pd.MultiIndex.from_product([['a','b'],[1,2]],names=['one','two'])
df = pd.DataFrame({'col':[10,20,30,40]}, index = index)
df
col
one two
a 1 10
2 20
b 1 30
2 40

index_1 = pd.MultiIndex.from_product([['a','b'],[1.,2],['abc','mno','xyz']], names = ['one','two','three'])
temp = pd.DataFrame({'col1':[1,2,3,4,5,6,7,8,9,10,11,12]}, index = index_1)
temp
col1
one two three
a 1.0 abc 1
mno 2
xyz 3
2.0 abc 4
mno 5
xyz 6
b 1.0 abc 7
mno 8
xyz 9
2.0 abc 10
mno 11
xyz 12


how can I merge both of them?
I have tried, this

pd.merge(left = temp, right = df, left_on = temp.index.levels[0], right_on = df.index.levels[0])


but this does not work

KeyError: "Index([u'a', u'b'], dtype='object', name=u'one') not in index"


if I convert the index into columns through reset_index() than the merge works. However, I wish to achieve this while preserving the index structure.

my desired output is:

enter image description here

Answer

method 1
reset_index + merge

df.reset_index().merge(temp.reset_index()).set_index(index_1.names)

method 2
join with reset_index partial

df.join(temp.reset_index('three')).set_index('three', append=True)

enter image description here