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

[jorisvandenbossche]

Here it is the column being aggregated that is a categorical, the other issue it was the index of the dataframe being categorical

[jorisvandenbossche]

cc @jbrockmendel

[charlesdong1991]

Hi, @jorisvandenbossche and @TomAugspurger (I am trying to fix tonight at least for nunique and count) Many thanks for your answer!

While working on the PR, i found another behaviour issue: is Int64 an extension array dtype?

>>> from pandas.core.dtypes.common import is_extension_array_dtype
>>> is_extension_array_dtype("Int64")
True

>>> from pandas.core.dtypes.common import is_extension_type
>>> is_extension_type("Int64")
False

IIUC, is_extension_type should be replaced by is_extension_array_dtype, and later be deprecated, so at least this two should return the same result. And Int64 does not seem to be an extension type, right (or it's IntegerArray?) Please correct me if I misunderstand the usage, thanks!

[jbrockmendel]

IIUC, is_extension_type should be replaced by is_extension_array_dtype, and later be deprecated, so at least this two should return the same result

is_extension_type is deprecated not because the behavior is identical to is_extension_array_dtype, but because the small difference is misleading. The three cases it does catch are AFAICT the three EAs we had at the time it was implemented. If you wanted to extend it to catch the newer EAs, that would probably be OK.