Description
Code Sample, a copy-pastable example if possible
rows1 = 3
df1 = pd.DataFrame(np.random.randn(rows1, 2), columns=['A', 'B'], index=['Z1']*rows1)
rows2 = 3
df2 = pd.DataFrame(np.random.randn(rows2, 2), columns=['A', 'B'], index=['Z1']*rows2)
df = pd.concat([df1, df2], keys=['Key1', 'Key2'], names=['KEY', 'ID'])
print(df.index)Problem description
If rows1 = rows2 as in the example, I get the following multi index from concat where 'Z1' is repeated with separate references in labels. However, if rows1 != rows2 (e.g. change rows1 to 4), I get what seems to be the more reasonable result in the expected output where 'Z1' is listed once with the same reference in labels. Also, if I add the multi index before concat and just do concat([df1, df2]), I get the expected result.
MultiIndex(levels=[['Key1', 'Key2'], ['Z1', 'Z1', 'Z1']],
labels=[[0, 0, 0, 1, 1, 1], [0, 1, 2, 0, 1, 2]],
names=['KEY', 'ID'])
The repeating multi index seemed to cause issues later when using inner merge on left/right index. Is this concat behavior expected?
Expected Output
MultiIndex(levels=[['Key1', 'Key2'], ['Z1']],
labels=[[0, 0, 0, 1, 1, 1], [0, 0, 0, 0, 0, 0]],
names=['KEY', 'ID'])
Output of pd.show_versions()
[paste the output of pd.show_versions() here below this line]
INSTALLED VERSIONS
commit: None
python: 3.6.1.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 94 Stepping 3, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None
pandas: 0.22.0
pytest: 3.0.7
pip: 9.0.1
setuptools: 27.2.0
Cython: 0.25.2
numpy: 1.12.1
scipy: 1.0.1
pyarrow: None
xarray: None
IPython: 5.3.0
sphinx: 1.5.6
patsy: 0.4.1
dateutil: 2.6.0
pytz: 2017.2
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: 2.0.2
openpyxl: 2.4.7
xlrd: 1.0.0
xlwt: 1.2.0
xlsxwriter: 0.9.6
lxml: 3.7.3
bs4: 4.6.0
html5lib: 0.999
sqlalchemy: 1.2.5
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None