BUG: multi-index joining returns wrong multiindex

[OXPHOS]

Code Sample, a copy-pastable example if possible

modified from TestMergeMulti.test_join_multi_levels

import pandas as pd

household = (
    pd.DataFrame(
        dict(A=[1, 2, 3],
             B=[0, 1, 0],
             C=[19.3, 31.7, 29]),
        columns=['A', 'B', 'C'])
        .set_index('A'))
portfolio = (
    pd.DataFrame(
        dict(A=[1, 2, 2, 3, 3, 3, 4],
             d=["nl0", "nl3", "gb0",
                "gb0", "lu4", "nl5", 'EMPTY'],
             e=["ABN", "Robeco", "Royal", "Royal",
                "AAB", "Postbank", 'EMPTY'],
             f=[1.0, 0.4, 0.6, 0.15, 0.6, 0.25, 1.0]),
        columns=['A', 'd', 'e', 'f'])
        .set_index(['A', 'd']))
result = household.join(portfolio, how='inner')

print household 
     B     C
  A         
  1  0  19.3
  2  1  31.7
  3  0  29.0

print portfolio
                  e     f
  A d                    
  1 nl0         ABN  1.00
  2 nl3      Robeco  0.40
    gb0       Royal  0.60
  3 gb0       Royal  0.15
    lu4         AAB  0.60
    nl5    Postbank  0.25
  4 EMPTY     EMPTY  1.00

print result
         B     C         e     f
  A d                           
  1 nl0  0  19.3       ABN  1.00
  2 nl3  1  31.7    Robeco  0.40
    gb0  1  31.7     Royal  0.60
  3 gb0  0  29.0     Royal  0.15
    lu4  0  29.0       AAB  0.60
    nl5  0  29.0  Postbank  0.25

print result.columns
  MultiIndex(levels=[[1, 2, 3], [u'EMPTY', u'gb0', u'lu4', u'nl0', u'nl3', u'nl5']],
             labels=[[0, 1, 1, 2, 2, 2], [3, 4, 1, 1, 2, 5]],
             names=[u'A', u'd'])

Problem description

The result looks okay but I think the 'EMPTY' should be dropped from the MultiIndex.

Expected Output

  MultiIndex(levels=[[1, 2, 3], [ u'gb0', u'lu4', u'nl0', u'nl3', u'nl5']],
             labels=[[0, 1, 1, 2, 2, 2], [2, 3, 0, 0, 1, 4]],
             names=[u'A', u'd'])

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 2.7.13.final.0 python-bits: 64 OS: Darwin OS-release: 16.5.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: None LANG: en_US.UTF-8 LOCALE: None.None

pandas: 0.19.2
nose: 1.3.7
pip: 9.0.1
setuptools: 27.2.0
Cython: 0.25.2
numpy: 1.11.3
scipy: 0.18.1
statsmodels: 0.6.1
xarray: None
IPython: 5.1.0
sphinx: 1.5.1
patsy: 0.4.1
dateutil: 2.6.0
pytz: 2016.10
blosc: None
bottleneck: 1.2.0
tables: 3.3.0
numexpr: 2.6.1
matplotlib: 2.0.0
openpyxl: 2.4.1
xlrd: 1.0.0
xlwt: 1.2.0
xlsxwriter: 0.9.6
lxml: 3.7.2
bs4: 4.5.3
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.1.5
pymysql: None
psycopg2: None
jinja2: 2.9.4
boto: 2.45.0
pandas_datareader: None

[TomAugspurger]

See #2770. This is a detail of how multiindexes (currently) work.

You can use http://pandas-docs.github.io/pandas-docs-travis/generated/pandas.MultiIndex.remove_unused_levels.html?highlight=remove_unused#pandas.MultiIndex.remove_unused_levels to remove the unused levels afterwards.