ENH: favor columns over index levels when groupby-ing over ambiguous label

[toobaz]

• 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

In [2]: df = pd.DataFrame(2, index=pd.MultiIndex.from_product([range(4), list('abcd')], names=['I', 'II']), columns=['I'])                                                                       

In [3]: df.groupby('I')                                                                                                                                                                          
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-3-8e63025e89e4> in <module>
----> 1 df.groupby('I')

~/nobackup/repo/pandas/pandas/core/frame.py in groupby(self, by, axis, level, as_index, sort, group_keys, squeeze, observed)
   5881             group_keys=group_keys,
   5882             squeeze=squeeze,
-> 5883             observed=observed,
   5884         )
   5885 

~/nobackup/repo/pandas/pandas/core/groupby/groupby.py in __init__(self, obj, keys, axis, level, grouper, exclusions, selection, as_index, sort, group_keys, squeeze, observed, mutated)
    407                 sort=sort,
    408                 observed=observed,
--> 409                 mutated=self.mutated,
    410             )
    411 

~/nobackup/repo/pandas/pandas/core/groupby/grouper.py in get_grouper(obj, key, axis, level, sort, observed, mutated, validate)
    591             if gpr in obj:
    592                 if validate:
--> 593                     obj._check_label_or_level_ambiguity(gpr, axis=axis)
    594                 in_axis, name, gpr = True, gpr, obj[gpr]
    595                 exclusions.append(name)

~/nobackup/repo/pandas/pandas/core/generic.py in _check_label_or_level_ambiguity(self, key, axis)
   1556                 f"{label_article} {label_type} label, which is ambiguous."
   1557             )
-> 1558             raise ValueError(msg)
   1559 
   1560     def _get_label_or_level_values(self, key: str, axis: int = 0) -> np.ndarray:

ValueError: 'I' is both an index level and a column label, which is ambiguous.

Problem description

1. The groupby docs don't even mention the possibility that a label is interpreted as an index level. They only say "A label or list of labels may be passed to group by the columns in self".

2. There is an explicit way to resolve the ambiguity in favor of an index level, which is the level= argument

3. In my experience (and I do use MultiIndexes a lot), grouping on index levels is far less common than grouping on columns

4. ... but there is no easy way to resolve the ambiguity in favor of a column (other than changing the columns/levels names, or explicitly passing the corresponding series)

All this said, I propose to suppress the ValueError above and just intepret the name as a column name. Given that we suppress an error, this shouldn't break any working code. This is the outcome which was agreed on in #5677 , but then in the PR Jeff suggested to implement the current behavior. I think the rationale made sense but should reconsidered in the context of the typical usage (also reflected in the docs).

Moreover, while Jeff suggested to use a pd.Grouper in that case, it doesn't allow to group on an ambiguous column name and an index level in the desired order, which was precisely one of the arguments for allowing passing levels to by=.

Expected Output

In [9]: df.reset_index(drop=True).groupby('I')                                                                                                                                                   
Out[9]: <pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f70e60e3828>

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.3.final.0
python-bits : 64
OS : Linux
OS-release : 4.19.0-8-amd64
machine : x86_64
processor :
byteorder : little
LC_ALL : None
LANG : it_IT.UTF-8
LOCALE : it_IT.UTF-8

pandas : 1.1.0.dev0+276.g2495068ad
numpy : 1.16.4
pytz : 2019.2
dateutil : 2.8.0
pip : 18.1
setuptools : 41.0.1
Cython : 0.29.13
pytest : 4.6.3
hypothesis : 3.71.11
sphinx : 1.8.4
blosc : 1.7.0
feather : None
xlsxwriter : 0.9.3
lxml.etree : 4.3.2
html5lib : 1.0.1
pymysql : None
psycopg2 : 2.7.7 (dt dec pq3 ext lo64)
jinja2 : 2.10.1
IPython : 7.5.0
pandas_datareader: 0.8.1
bs4 : 4.8.0
bottleneck : 1.2.1
fastparquet : None
gcsfs : None
lxml.etree : 4.3.2
matplotlib : 3.0.2
numexpr : 2.6.9
odfpy : None
openpyxl : 2.4.9
pandas_gbq : None
pyarrow : None
pytables : None
pytest : 4.6.3
pyxlsb : None
s3fs : None
scipy : 1.1.0
sqlalchemy : 1.2.18
tables : 3.4.4
tabulate : 0.8.3
xarray : 0.11.3
xlrd : 1.2.0
xlwt : 1.3.0
xlsxwriter : 0.9.3
numba : 0.45.0