Warn on duplicate names in MI?

[TomAugspurger]

Opening a new issue so this isn't lost.

In #18882 banned duplicate names in a MultiIndex. I think this is a good change since allowing duplicates hit a lot of edge cases when you went to actually do something. I want to make sure we understand all the cases that actually produce duplicate names in the MI though, specifically groupby.apply.

In [1]: import dask.dataframe as dd

In [2]: import pandas as pd

In [3]:     pdf = pd.DataFrame({'a': [1, 2, 3, 4, 5, 6, 7, 8, 9],
   ...:                         'b': [4, 5, 6, 3, 2, 1, 0, 0, 0]},
   ...:                        index=[0, 1, 3, 5, 6, 8, 9, 9, 9]).set_index("a")
   ...:
   ...:

In [4]: pdf.groupby(pdf.index).apply(lambda x: x.b)

Another, more realistic example: groupwise drop_duplicates:

In [18]: df = pd.DataFrame({"B": [0, 0, 0, 1, 1, 1, 2, 2, 2]}, index=pd.Index([0, 1, 1, 2, 2, 2, 0, 0, 1], name='a'))

In [19]: df
Out[19]:
   B
a
0  0
1  0
1  0
2  1
2  1
2  1
0  2
0  2
1  2

In [20]: df.groupby('a').apply(pd.DataFrame.drop_duplicates)
Out[20]:
     B
a a
0 0  0
  0  2
1 1  0
  1  2
2 2  1

Is it possible to throw a warning on this for now, in case duplicate names are more common than we thought?

[jreback]

cc @toobaz

[toobaz]

I don't have a strong opinion on this... we could revert and emit a warning, we could revert and abandon the idea of forbidding duplicated names (and solve otherwise the problems exposed in #18872), we could rename name to name1 (then name2...) when name is already present, or we could just drop the name (reset to None) when it is already present.

My personal preference, at least if we think that our main source of concern should be groupby, is probably for this last solution, which would then (temporarily?) also emit a warning. It is maybe not very elegant, but it's very close to perfect backward compatibility, while still allowing to simplify/clean a bit the code. (But should we do this even when the user sets two levels with the same name simultaneously, or could we at least in that case raise an error?)

This said, in the above examples we would probably better serve the user by dropping one of the two levels, which are exact copies. Certainly we can come up with examples which would still fail, but maybe both things are worth implementing together, so that the "black magic" (reset name to None) is used sparingly, if ever.