Regression bugs when applying GroupBy Aggregations to Categorical columns

[frances-h]

>>> import pandas as pd

>>> ids = range(5)
>>> groups = pd.Series([0, 1, 1, 2, 2])
>>> values = pd.Categorical([0, 0, 0, 0, 1])
>>> df = pd.DataFrame({
            'id': ids,
            'groups': groups,
            'value': values
    }).set_index('id')

>>> df.groupby('groups').agg(pd.Series.nunique)
       value
groups
0          1
1          1
2        NaN

>>> df.groupby('groups').agg(pd.Series.nunique)['value'].dtype
CategoricalDtype(categories=[0, 1], ordered=False)

>>> df.groupby('groups').agg(pd.Series.count)
       value
groups
0          1
1        NaN
2        NaN

>>> df.groupby('groups').agg(pd.Series.mode)
                                                                           value
groups
0                                                                              0
1              0    0
Name: value, dtype: category
Categories (2, int64): [0, 1]
2       0    0
1    1
Name: value, dtype: category
Categories (2, int64): [0, 1]

Problem description

There's some regression bugs in 1.0.0rc0 when applying groupby aggregations to Categorical columns. All of these examples work as expected in 0.25.3.

Applying pd.Series.nunique and pd.Series.count both result in NaN for some groupings, the result has a Categorical dtype. The resulting dtype should be int64 and NaN should not be produced.

Applying pd.Series.mode returns the correct result for the first grouping but produces nonsensical output for subsequent groupings.

Expected Output

Aggregations should work as expected on Categorical columns as they do in 0.25.3:

>>> df.groupby('groups').agg(pd.Series.nunique)
        value
groups
0           1
1           1
2           2

>>> df.groupby('groups').agg(pd.Series.nunique)['value'].dtype
dtype('int64')

>>> df.groupby('groups').agg(pd.Series.count)
        value
groups
0           1
1           2
2           2

>>> df.groupby('groups').agg(pd.Series.mode)
         value
groups
0      0     0
1      0     0
2      0     0
       1     1

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.6.9.final.0
python-bits : 64
OS : Darwin
OS-release : 18.7.0
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.0.0rc0
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.1
setuptools : 45.1.0
Cython : None
pytest : 5.2.0
hypothesis : None
sphinx : 2.0.1
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.10.3
IPython : 7.2.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : 0.3.2
gcsfs : None
lxml.etree : None
matplotlib : 3.0.2
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : 5.2.0
s3fs : 0.4.0
scipy : 1.4.1
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
numba : 0.47.0

[TomAugspurger]

I don't believe this is fixed by #31238.

[charlesdong1991]

no, it won't be fixed by 31238. i initially thought this was a duplicate, but after seeing the fix i re-assigned to myself. i already found the issue, will submit a patch later tonight to make sure this can be included before 1.0.0 releases.

[charlesdong1991]

Hi @TomAugspurger

I have a question about the current behaviour, currently, on 0.25.3,

>>> ids = range(5)
>>> groups = pd.Series([0, 1, 1, 2, 2])
>>> values = pd.Categorical([0, 0, 0, 0, 1])
>>> df = pd.DataFrame({
            'id': ids,
            'groups': groups,
            'value': values
    }).set_index('id')
>>> df.groupby('groups').agg(pd.Series.mode)
         value
groups
0      0     0
1      0     0
2      0     0
       1     1

there is an extra index after aggregation, and I am not very sure if this is desired behaviour? could you pls give some thoughts on it? it seems that this extra index comes from output of pd.Series.mode which is a Series. should this be like this? or this extra index should be removed?

[TomAugspurger]

I'm not sure what the desired behavior is there.

…

On Mon, Jan 27, 2020 at 2:47 PM Kaiqi Dong ***@***.***> wrote: Hi @TomAugspurger <https://github.com/TomAugspurger> I have a question about the current behaviour, currently, on 0.25.3, >>> ids = range(5)>>> groups = pd.Series([0, 1, 1, 2, 2])>>> values = pd.Categorical([0, 0, 0, 0, 1])>>> df = pd.DataFrame({ 'id': ids, 'groups': groups, 'value': values }).set_index('id')>>> df.groupby('groups').agg(pd.Series.mode) value groups0 0 01 0 02 0 0 1 1 there is an extra index after aggregation, and I am not very sure if this is desired behaviour? could you pls give some thoughts on it? it seems that this extra index comes from output of mode which is a Series. should this be like this? or this extra index should be removed? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#31256?email_source=notifications&email_token=AAKAOIS63TI4RUUQSRS5R5TQ75B7VA5CNFSM4KK3WLF2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKBACOY#issuecomment-578945339>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAKAOIWLJ4ZFOZRMCEN5CS3Q75B7VANCNFSM4KK3WLFQ> .

[TomAugspurger]

@charlesdong1991 I don't think that case is valid. .agg(f) is supposed to return a scalar. But Series.mode isn't an aggregation, it returns a Series.

[TomAugspurger]

@jorisvandenbossche do you think this is a blocker?

[jorisvandenbossche]

The mode issue is fishy (I am also not sure how/if this should work, as it is not a typical aggregation), but the other ones seem more as actual regressions:

pd 0.25:

In [1]: >>> import pandas as pd 
   ...:  
   ...: >>> ids = range(5) 
   ...: >>> groups = pd.Series([0, 1, 1, 2, 2]) 
   ...: >>> values = pd.Categorical([0, 0, 0, 0, 1]) 
   ...: >>> df = pd.DataFrame({ 
   ...:             'id': ids, 
   ...:             'groups': groups, 
   ...:             'value': values 
   ...:     }).set_index('id') 
   ...:  
   ...: >>> df.groupby('groups').agg(pd.Series.nunique)  
Out[1]: 
        value
groups       
0           1
1           1
2           2

vs master

In [1]: >>> import pandas as pd 
   ...:  
   ...: >>> ids = range(5) 
   ...: >>> groups = pd.Series([0, 1, 1, 2, 2]) 
   ...: >>> values = pd.Categorical([0, 0, 0, 0, 1]) 
   ...: >>> df = pd.DataFrame({ 
   ...:             'id': ids, 
   ...:             'groups': groups, 
   ...:             'value': values 
   ...:     }).set_index('id') 
   ...:  
   ...: >>> df.groupby('groups').agg(pd.Series.nunique) 
Out[1]: 
       value
groups      
0          1
1          1
2        NaN