BUG: Pivot table on empty frame generates MultiIndex (regression)

[rxxg]

Pandas version checks

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

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

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import numpy as np

a = b = np.array([1,2])
m = pd.DataFrame(data=np.array([a,b]).T, columns=["a", "b"])
p = pd.pivot_table(m, index=["a"], columns=["b"], aggfunc=len)
p.columns # OK, Int64Index

a = b = np.array([])
m = pd.DataFrame(data=np.array([a,b]).T, columns=["a", "b"])
p = pd.pivot_table(m, index=["a"], columns=["b"], aggfunc=len)
p.columns # NOK, MultiIndex

Issue Description

Looks like this was originally reported as #13483, but regressed with 1.3.0
Calling pivot_table on an empty DataFrame returns a new DataFrame where the columns are described as a MultiIndex, which is incoherent with its behaviour in the non-empty case.

Expected Behavior

Script should give the following output

Int64Index([1, 2], dtype='int64', name='b')
Index([], dtype='object')

Instead, it gives

Int64Index([1, 2], dtype='int64', name='b')
MultiIndex([], names=[None, 'b'])

Installed Versions

INSTALLED VERSIONS

commit : 945c9ed
python : 3.9.0.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.18362
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 12, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_Ireland.1252
pandas : 1.3.4
numpy : 1.21.4
pytz : 2021.3
dateutil : 2.8.2
pip : 21.3.1
setuptools : 59.5.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : 1.7.3
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : 2.0.1
xlwt : None
numba : None

[zhenqin77]

take

[phofl]

This happened because of a change in groupby.agg. This returns "a" and "b" now in the columns and in the index if empty. Is this expected @rhshadrach ?

m = pd.DataFrame(columns=["a", "b"])
result = m.groupby(["a", "b"]).agg(len)
result.index
result.columns

[rhshadrach]

I don't think so. This code ends up going through apply, which doesn't use the group selection. E.g.

m = pd.DataFrame(columns=["a", "b"])
gb = m.groupby(["a", "b"])
with gb._group_selection_context():
    result = gb.agg(len)
print(result.index)
print(result.columns)

MultiIndex([], names=['a', 'b'])
Index([], dtype='object')

This is all due to apply using the groupers in computation whereas agg doesn't.

m = pd.DataFrame([[1, 2], [1, 3]], columns=["a", "b"])
gb = m.groupby(["a"])
print(gb.apply(lambda x: x.sum()))
print(gb.agg(lambda x: x.sum()))

   a  b
a      
1  2  5
   b
a   
1  5

Would be nice to straighten this out. My ideal solution would be to allow the user to include / exclude the grouper in the computation as they see fit (default - exclude); but this might complicate the groupby code. Barring this, it seems to me the shortest path to consistency would be removing the groupers from the computation in apply.

[rxxg]

Hi @rhshadrach, your second example gives the same results in pandas version 1.2, so I wonder if your proposition to remove groupers from the apply computation wouldn't give other compatibility problems?

[rhshadrach]

@rxxg - yes, I am suggesting an non-backwards compatible API change. The behavior change @phofl isolated with .agg was part of another bugfix. I don't think undoing that to fix this regression is the way to go; the root cause here is the behavior inconsistencies between agg and apply.