ENH: reindex and align with two multiindex dataframes

[swinman]

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?

[jreback]

duplicate of this: #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.