BUG: DataFrame.stack does not work when MultiIndex had duplicated names

[cipri-tom]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Code Sample, a copy-pastable example

data.csv:

    , Q1, Q1, Q1, Q2, Q2, Q2
    , V1, V2, V3, V1, V2, V3
Ram , 11, 19, 10, 10, 12, 14
Syam, 11, 19, 10, 10, 12, 14

# Your code here
df = pd.read_clipboard(',', header=[0,1], index_col=0)
df.stack(0)

ValueError: The name      occurs multiple times, use a level number

Problem description

Well, I am using a level number, not a name, but there still is an error because the level names are duplicated: each is      (4 spaces). I would expect it to work, or to have a more descriptive error, maybe upon creation of the data-frame: "MultiIndex should not have identical names".

I believe this is related to #19029

However, interestingly df.stack(1) works correctly, no problems with duplicate names.

Expected Output

H2         V1   V2   V3
     H1
Ram   Q1   11   19   10
      Q2   10   12   14
Syam  Q1   11   19   10
      Q2   10   12   14

Solved it with:

df.columns.names = ['H1', 'H2']
df.stack(0) # works

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 2a7d332
python : 3.7.6.final.0
python-bits : 64
OS : Darwin
OS-release : 19.4.0
Version : Darwin Kernel Version 19.4.0: Wed Mar 4 22:28:40 PST 2020; root:xnu-6153.101.6~15/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : en_US.UTF-8
LANG : en_UK.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.1.2
numpy : 1.19.0
pytz : 2020.1
dateutil : 2.8.1
pip : 20.2.3
setuptools : 49.6.0.post20200814
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.5.2
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.11.2
IPython : 7.14.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : 0.8.7
xarray : None
xlrd : None
xlwt : None
numba : None

[dsaxton]

Thanks @cipri-tom, this is indeed a bug and it exists on master as well

[cipri-tom]

👍 Thanks for the quick fix ! 🥳
I see this will be available in 1.1.3

[jreback]

we don't close an issue until the pr has actually merged