groupby level after stack not actually grouping · Issue #28301 · pandas-dev/pandas (original) (raw)

Code Sample, a copy-pastable example if possible

columns = pd.MultiIndex.from_product([['a', 'b'], [1, 2]]) index = [0, 0, 1, 1] values = np.arange(16).reshape(-1, 4)

df = pd.DataFrame(values, columns=columns, index=index)

a       b
1   2   1   2

0 0 1 2 3 0 4 5 6 7 1 8 9 10 11 1 12 13 14 15

df.stack(1)

  a   b

0 1 0 2 2 1 3 1 4 6 2 5 7 1 1 8 10 2 9 11 1 12 14 2 13 15

ACTUAL BEHAVIOR

df.stack(1).groupby(level=[0, 1]).sum()

  a   b

0 1 0 2 2 1 3 1 4 6 2 5 7 1 1 8 10 2 9 11 1 12 14 2 13 15

EXPECTED

  a   b

0 1 4 8 2 6 10 1 1 20 24 2 22 26

WORKAROUND

df.stack(1).reset_index().groupby(['level_0', 'level_1']).sum().rename_axis([None, None])

Problem description

When grouping by levels after stacking, no grouping seems to be taking place. However, after resetting the index and using those series to group, the groupby works as expected

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit : None python : 3.6.7.final.0 python-bits : 64 OS : Windows OS-release : 10 machine : AMD64 processor : Intel64 Family 6 Model 58 Stepping 9, GenuineIntel byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : None.None

pandas : 0.25.0
numpy : 1.17.0
pytz : 2018.6
dateutil : 2.7.3
pip : 18.1
setuptools : 39.1.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.3.4
html5lib : 1.0.1
pymysql : None
psycopg2 : 2.7.5 (dt dec pq3 ext lo64)
jinja2 : 2.10.1
IPython : 7.2.0
pandas_datareader: None
bs4 : 4.7.1
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : 4.3.4
matplotlib : 3.0.0
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.2.1
sqlalchemy : 1.2.12
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None