The Advanced reindexing and alignment section of the documentation describes a method for filling data based on a partial match of indexes.
In this example, one level of a multiindex is dropped and data is copied based on a match of the remaining levels. This example only works because the original multiindex only had 2 levels so that when one level was dropped, the remaining index is no longer a multiindex. Unfortunately in practice you might end up wanting to align or reindex two multiindex based on the remaining levels (as I do), but it seems this behavior is not supported..
midx = pd.MultiIndex(levels=[['zero', 'one'], ['x','y'], ['a', 'b']], labels=[[1]*4+[0]*4,[1,1,0,0]*2,[1,0]*4], names=['first','second','third'])
df = pd.DataFrame(np.random.randn(8,2), index=midx)
df2 = df.mean(level=[k for k in df.index.names if k != 'third'])
df2.reindex(df.index, level=[0, 1])
the result of the last command is TypeError: Join on level between two MultiIndex objects is ambiguous
Particularly with named index levels, it not clear how this is ambiguous..
When you attempt an align where level is specified you end up with the same TypeError and when you attempt
df.align(df2)
you get a NotImplementedError
Based on the example in the documentation you would expect that dropping a multiindex level then reindexing in order to duplicate some data across the dropped index level would work. It is surprising that this doesn't work. Is there a way to implement this functionality?
Comment From: jreback
duplicate of this: https://github.com/pydata/pandas/issues/6360
see also the documenation here: http://pandas.pydata.org/pandas-docs/stable/merging.html#joining-with-two-multi-indexes
pull-request are welcome to fix this, this is the best way.