<p dir="auto">The <a href="http://pandas.pydata.org/pandas-docs/stable/advanced.html#advanced-reindexing-and-alignment" rel="nofollow">Advanced reindexing and alignment</a> section of the documentation describes a method for filling data based on a partial match of indexes.</p>
<p dir="auto">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..</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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])"><pre class="notranslate"><code class="notranslate">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])
</code></pre></div>
<p dir="auto">the result of the last command is <code class="notranslate">TypeError: Join on level between two MultiIndex objects is ambiguous</code></p>
<p dir="auto">Particularly with named index levels, it not clear how this is ambiguous..</p>
<p dir="auto">When you attempt an <code class="notranslate">align</code> where level is specified you end up with the same <code class="notranslate">TypeError</code> and when you attempt</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="df.align(df2)"><pre class="notranslate"><code class="notranslate">df.align(df2)
</code></pre></div>
<p dir="auto">you get a <code class="notranslate">NotImplementedError</code></p>
<p dir="auto">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?</p>
<p dir="auto">I'm trying to trim a very big <code class="notranslate">DataFrame</code> with a <code class="notranslate">MultiIndex</code> using <code class="notranslate">head</code>, and I noticed that the new <code class="notranslate">DataFrame</code> was still ridicolously big because it kept all the previous data from the entire <code class="notranslate">MultiIndex</code>. This doesn't happen if the <code class="notranslate">DataFrame</code> has a regular <code class="notranslate">Index</code>.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In [1]: from pandas import DataFrame

In [2]: df = DataFrame({'a': [1, 2, 3, 4, 5, 6], 'b': ['q', 'w', 'e', 'r', 't', 'y'], 'c': ['a', 's', 'd', 'f', 'g', 'h']}).set_index(['a', 'b'])

In [3]: df.head(3)
Out[3]: 
     c
a b   
1 q  a
2 w  s
3 e  d

In [4]: df.head(3).index
Out[4]: 
MultiIndex(levels=[[1, 2, 3, 4, 5, 6], [u'e', u'q', u'r', u't', u'w', u'y']],
           labels=[[0, 1, 2], [1, 4, 0]],
           names=[u'a', u'b'])"><pre class="notranslate"><span class="pl-v">In</span> [<span class="pl-c1">1</span>]: <span class="pl-s1">from</span> <span class="pl-s1">pandas</span> <span class="pl-k">import</span> <span class="pl-v">DataFrame</span>

<span class="pl-v">In</span> [<span class="pl-c1">2</span>]: <span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-v">DataFrame</span>({<span class="pl-s">'a'</span>: [<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-c1">3</span>, <span class="pl-c1">4</span>, <span class="pl-c1">5</span>, <span class="pl-c1">6</span>], <span class="pl-s">'b'</span>: [<span class="pl-s">'q'</span>, <span class="pl-s">'w'</span>, <span class="pl-s">'e'</span>, <span class="pl-s">'r'</span>, <span class="pl-s">'t'</span>, <span class="pl-s">'y'</span>], <span class="pl-s">'c'</span>: [<span class="pl-s">'a'</span>, <span class="pl-s">'s'</span>, <span class="pl-s">'d'</span>, <span class="pl-s">'f'</span>, <span class="pl-s">'g'</span>, <span class="pl-s">'h'</span>]}).<span class="pl-en">set_index</span>([<span class="pl-s">'a'</span>, <span class="pl-s">'b'</span>])

<span class="pl-v">In</span> [<span class="pl-c1">3</span>]: <span class="pl-s1">df</span>.<span class="pl-en">head</span>(<span class="pl-c1">3</span>)
<span class="pl-v">Out</span>[<span class="pl-c1">3</span>]: 
     <span class="pl-s1">c</span>
<span class="pl-s1">a</span> <span class="pl-s1">b</span>   
<span class="pl-c1">1</span> <span class="pl-s1">q</span>  <span class="pl-s1">a</span>
<span class="pl-c1">2</span> <span class="pl-s1">w</span>  <span class="pl-s1">s</span>
<span class="pl-c1">3</span> <span class="pl-s1">e</span>  <span class="pl-s1">d</span>

<span class="pl-v">In</span> [<span class="pl-c1">4</span>]: <span class="pl-s1">df</span>.<span class="pl-en">head</span>(<span class="pl-c1">3</span>).<span class="pl-s1">index</span>
<span class="pl-v">Out</span>[<span class="pl-c1">4</span>]: 
<span class="pl-v">MultiIndex</span>(<span class="pl-s1">levels</span><span class="pl-c1">=</span>[[<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-c1">3</span>, <span class="pl-c1">4</span>, <span class="pl-c1">5</span>, <span class="pl-c1">6</span>], [<span class="pl-s">u'e'</span>, <span class="pl-s">u'q'</span>, <span class="pl-s">u'r'</span>, <span class="pl-s">u't'</span>, <span class="pl-s">u'w'</span>, <span class="pl-s">u'y'</span>]],
           <span class="pl-s1">labels</span><span class="pl-c1">=</span>[[<span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">2</span>], [<span class="pl-c1">1</span>, <span class="pl-c1">4</span>, <span class="pl-c1">0</span>]],
           <span class="pl-s1">names</span><span class="pl-c1">=</span>[<span class="pl-s">u'a'</span>, <span class="pl-s">u'b'</span>])</pre></div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="#### Expected Output
MultiIndex(levels=[[1, 2, 3], [u'e', u'q', u'w']],
           labels=[[0, 1, 2], [1, 2, 0]],
           names=[u'a', u'b'])"><pre class="notranslate"><span class="pl-c">#### Expected Output</span>
<span class="pl-v">MultiIndex</span>(<span class="pl-s1">levels</span><span class="pl-c1">=</span>[[<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-c1">3</span>], [<span class="pl-s">u'e'</span>, <span class="pl-s">u'q'</span>, <span class="pl-s">u'w'</span>]],
           <span class="pl-s1">labels</span><span class="pl-c1">=</span>[[<span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">2</span>], [<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-c1">0</span>]],
           <span class="pl-s1">names</span><span class="pl-c1">=</span>[<span class="pl-s">u'a'</span>, <span class="pl-s">u'b'</span>])</pre></div>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
# Paste the output here
## INSTALLED VERSIONS
<p dir="auto">commit: None<br>
python: 2.7.12.final.0<br>
python-bits: 64<br>
OS: Linux<br>
OS-release: 4.4.0-45-generic<br>
machine: x86_64<br>
processor: x86_64<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: en_US.UTF-8</p>
<p dir="auto">pandas: 0.18.1<br>
nose: None<br>
pip: 8.1.2<br>
setuptools: 25.1.1<br>
Cython: None<br>
numpy: 1.11.1<br>
scipy: 0.18.0<br>
statsmodels: None<br>
xarray: None<br>
IPython: 5.0.0<br>
sphinx: None<br>
patsy: None<br>
dateutil: 2.5.3<br>
pytz: 2016.6.1<br>
blosc: None<br>
bottleneck: None<br>
tables: None<br>
numexpr: None<br>
matplotlib: 1.5.1<br>
openpyxl: None<br>
xlrd: None<br>
xlwt: None<br>
xlsxwriter: None<br>
lxml: None<br>
bs4: None<br>
html5lib: None<br>
httplib2: None<br>
apiclient: None<br>
sqlalchemy: None<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.8<br>
boto: None<br>
pandas_datareader: None</p>
</details>