pandas-dev/pandas

BUG: grouping with categorical interval columns

Closed this issue · 11 comments

antipisa commented

Versions:
pandas 1.0.3
numpy 1.18.1

There is a bug in the 1.XXX pandas release that does not allow you to group by a categorical interval index column together with another column.

import numpy as np
import pandas as pd
pd.set_option("use_inf_as_na",True)
t = pd.DataFrame({"x":np.random.randn(100), 'w':np.random.choice(list("ABC"), 100)})
qq = pd.qcut(t['x'], q=np.linspace(0,1,5))

This works and gives the expected result:
t.groupby([qq])['x'].agg('mean')

x (-10.001, -1.0] -1.431893 (-1.0, 0.0] -0.423564 (0.0, 1.0] 0.461174 (1.0, 10.0] 1.662297 Name: x, dtype: float64

This raises a TypeError:
t.groupby([qq,'w'])['x'].agg('mean')

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-43-6d7782f17653> in <module>
----> 1 t.groupby([qq,'w'])['x'].agg('mean')

~/miniconda3/lib/python3.7/site-packages/pandas/core/groupby/generic.py in aggregate(self, func, *args, **kwargs)
    245 
    246         if isinstance(func, str):
--> 247             return getattr(self, func)(*args, **kwargs)
    248 
    249         elif isinstance(func, abc.Iterable):

~/miniconda3/lib/python3.7/site-packages/pandas/core/groupby/groupby.py in mean(self, *args, **kwargs)
   1223         nv.validate_groupby_func("mean", args, kwargs, ["numeric_only"])
   1224         return self._cython_agg_general(
-> 1225             "mean", alt=lambda x, axis: Series(x).mean(**kwargs), **kwargs
   1226         )
   1227 

~/miniconda3/lib/python3.7/site-packages/pandas/core/groupby/groupby.py in _cython_agg_general(self, how, alt, numeric_only, min_count)
    907             raise DataError("No numeric types to aggregate")
    908 
--> 909         return self._wrap_aggregated_output(output)
    910 
    911     def _python_agg_general(self, func, *args, **kwargs):

~/miniconda3/lib/python3.7/site-packages/pandas/core/groupby/generic.py in _wrap_aggregated_output(self, output)
    384             output=output, index=self.grouper.result_index
    385         )
--> 386         return self._reindex_output(result)._convert(datetime=True)
    387 
    388     def _wrap_transformed_output(

~/miniconda3/lib/python3.7/site-packages/pandas/core/groupby/groupby.py in _reindex_output(self, output, fill_value)
   2481         levels_list = [ping.group_index for ping in groupings]
   2482         index, _ = MultiIndex.from_product(
-> 2483             levels_list, names=self.grouper.names
   2484         ).sortlevel()
   2485 

~/miniconda3/lib/python3.7/site-packages/pandas/core/indexes/multi.py in from_product(cls, iterables, sortorder, names)
    551 
    552         codes = cartesian_product(codes)
--> 553         return MultiIndex(levels, codes, sortorder=sortorder, names=names)
    554 
    555     @classmethod

~/miniconda3/lib/python3.7/site-packages/pandas/core/indexes/multi.py in __new__(cls, levels, codes, sortorder, names, dtype, copy, name, verify_integrity, _set_identity)
    278 
    279         if verify_integrity:
--> 280             new_codes = result._verify_integrity()
    281             result._codes = new_codes
    282 

~/miniconda3/lib/python3.7/site-packages/pandas/core/indexes/multi.py in _verify_integrity(self, codes, levels)
    366 
    367         codes = [
--> 368             self._validate_codes(level, code) for level, code in zip(levels, codes)
    369         ]
    370         new_codes = FrozenList(codes)

~/miniconda3/lib/python3.7/site-packages/pandas/core/indexes/multi.py in <listcomp>(.0)
    366 
    367         codes = [
--> 368             self._validate_codes(level, code) for level, code in zip(levels, codes)
    369         ]
    370         new_codes = FrozenList(codes)

~/miniconda3/lib/python3.7/site-packages/pandas/core/indexes/multi.py in _validate_codes(self, level, code)
    302         to a level with missing values (NaN, NaT, None).
    303         """
--> 304         null_mask = isna(level)
    305         if np.any(null_mask):
    306             code = np.where(null_mask[code], -1, code)

~/miniconda3/lib/python3.7/site-packages/pandas/core/dtypes/missing.py in isna(obj)
    124     Name: 1, dtype: bool
    125     """
--> 126     return _isna(obj)
    127 
    128 

~/miniconda3/lib/python3.7/site-packages/pandas/core/dtypes/missing.py in _isna_old(obj)
    181         return False
    182     elif isinstance(obj, (ABCSeries, np.ndarray, ABCIndexClass, ABCExtensionArray)):
--> 183         return _isna_ndarraylike_old(obj)
    184     elif isinstance(obj, ABCGeneric):
    185         return obj._constructor(obj._data.isna(func=_isna_old))

~/miniconda3/lib/python3.7/site-packages/pandas/core/dtypes/missing.py in _isna_ndarraylike_old(obj)
    281         else:
    282             result = np.empty(shape, dtype=bool)
--> 283             vec = libmissing.isnaobj_old(values.ravel())
    284             result[:] = vec.reshape(shape)
    285 

TypeError: Argument 'arr' has incorrect type (expected numpy.ndarray, got Categorical)
dsaxton commented

This seems to work on master for me:

[ins] In [1]: import numpy as np                                                                                                                                                                             

[ins] In [2]: import pandas as pd                                                                                                                                                                            

[ins] In [3]: pd.set_option("use_inf_as_na",True)                                                                                                                                                            

[ins] In [4]: t = pd.DataFrame({"x":np.random.randn(100), 'w':np.random.choice(list("ABC"), 100)})                                                                                                           

[ins] In [5]: qq = pd.qcut(t['x'], q=np.linspace(0,1,5))                                                                                                                                                     

[ins] In [6]: t.groupby([qq,'w'])['x'].agg('mean')                                                                                                                                                           
Out[6]: 
x                              w
(-2.7649999999999997, -0.736]  A   -1.412247
                               B   -1.319972
                               C   -1.108550
(-0.736, -0.114]               A   -0.351454
                               B   -0.388151
                               C   -0.404094
(-0.114, 0.587]                A    0.134442
                               B    0.235705
                               C    0.406392
(0.587, 1.864]                 A    1.056471
                               B    0.973123
                               C    1.189502
Name: x, dtype: float64
antipisa commented

Here is my env:

Package                Version   
---------------------- ----------
appnope                0.1.0     
asn1crypto             1.2.0     
attrs                  19.3.0    
backcall               0.1.0     
bleach                 3.1.5     
certifi                2019.11.28
cffi                   1.13.0    
chardet                3.0.4     
conda                  4.8.2     
conda-package-handling 1.6.0     
cryptography           2.8       
decorator              4.4.2     
defusedxml             0.6.0     
entrypoints            0.3       
idna                   2.8       
importlib-metadata     1.6.0     
ipykernel              5.2.1     
ipython                7.14.0    
ipython-genutils       0.2.0     
ipywidgets             7.5.1     
jedi                   0.17.0    
Jinja2                 2.11.2    
jsonschema             3.2.0     
jupyter                1.0.0     
jupyter-client         6.1.3     
jupyter-console        6.1.0     
jupyter-core           4.6.3     
MarkupSafe             1.1.1     
mistune                0.8.4     
mkl-fft                1.0.15    
mkl-random             1.1.0     
mkl-service            2.3.0     
nbconvert              5.6.1     
nbformat               5.0.6     
notebook               6.0.3     
numpy                  1.18.1    
packaging              20.3      
pandas                 1.0.3     
pandocfilters          1.4.2     
parso                  0.7.0     
pexpect                4.8.0     
pickleshare            0.7.5     
pip                    19.3.1    
prometheus-client      0.7.1     
prompt-toolkit         3.0.5     
ptyprocess             0.6.0     
pycosat                0.6.3     
pycparser              2.19      
Pygments               2.6.1     
pyOpenSSL              19.0.0    
pyparsing              2.4.7     
pyq                    4.2.1     
pyrsistent             0.16.0    
PySocks                1.7.1     
python-dateutil        2.8.1     
pytz                   2020.1    
pyzmq                  19.0.1    
qtconsole              4.7.4     
QtPy                   1.9.0     
requests               2.22.0    
ruamel-yaml            0.15.46   
Send2Trash             1.5.0     
setuptools             41.4.0    
six                    1.12.0    
terminado              0.8.3     
testpath               0.4.4     
tornado                6.0.4     
tqdm                   4.36.1    
traitlets              4.3.3     
urllib3                1.24.2    
wcwidth                0.1.9     
webencodings           0.5.1     
wheel                  0.33.6    
widgetsnbextension     3.5.1     
zipp                   3.1.0
banditelol commented

Also works for me on 1.3.5
image
Should this issue be closed?

jreback commented

would take a regression test to close

banditelol commented

Do you mean writing a regression test for the correct behavior like this one, to make sure the behavior stays correct ?

pandas/pandas/tests/groupby/test_groupby.py

Lines 108 to 147 in dbc3afa

def test_groupby_return_type():
# GH2893, return a reduced type
df1 = DataFrame(
[
{"val1": 1, "val2": 20},
{"val1": 1, "val2": 19},
{"val1": 2, "val2": 27},
{"val1": 2, "val2": 12},
]
)
def func(dataf):
return dataf["val2"] - dataf["val2"].mean()
with tm.assert_produces_warning(FutureWarning):
result = df1.groupby("val1", squeeze=True).apply(func)
assert isinstance(result, Series)
df2 = DataFrame(
[
{"val1": 1, "val2": 20},
{"val1": 1, "val2": 19},
{"val1": 1, "val2": 27},
{"val1": 1, "val2": 12},
]
)
def func(dataf):
return dataf["val2"] - dataf["val2"].mean()
with tm.assert_produces_warning(FutureWarning):
result = df2.groupby("val1", squeeze=True).apply(func)
assert isinstance(result, Series)
# GH3596, return a consistent type (regression in 0.11 from 0.10.1)
df = DataFrame([[1, 1], [1, 1]], columns=["X", "Y"])
with tm.assert_produces_warning(FutureWarning):
result = df.groupby("X", squeeze=False).count()
assert isinstance(result, DataFrame)

jreback commented

yes

banditelol commented

Noted, can I take on this issue?

banditelol commented

After tweaking around on notebook, it seems like I could replicate the problem with the condition:

  • Using pandas 1.0.3 (no problem on 1.0.4)
  • MultiIndexing dataframe
  • Using column with type of categoricalDtype not IntervalDtype
  • Using observed=False as groupby's argument
    And it seems like the current test suite has already take this into account with these following lines:

    pandas/pandas/tests/groupby/test_categorical.py

    Lines 317 to 353 in 361021b

    def test_observed(observed):
    # multiple groupers, don't re-expand the output space
    # of the grouper
    # gh-14942 (implement)
    # gh-10132 (back-compat)
    # gh-8138 (back-compat)
    # gh-8869
    cat1 = Categorical(["a", "a", "b", "b"], categories=["a", "b", "z"], ordered=True)
    cat2 = Categorical(["c", "d", "c", "d"], categories=["c", "d", "y"], ordered=True)
    df = DataFrame({"A": cat1, "B": cat2, "values": [1, 2, 3, 4]})
    df["C"] = ["foo", "bar"] * 2
    # multiple groupers with a non-cat
    gb = df.groupby(["A", "B", "C"], observed=observed)
    exp_index = MultiIndex.from_arrays(
    [cat1, cat2, ["foo", "bar"] * 2], names=["A", "B", "C"]
    )
    expected = DataFrame({"values": Series([1, 2, 3, 4], index=exp_index)}).sort_index()
    result = gb.sum()
    if not observed:
    expected = cartesian_product_for_groupers(
    expected, [cat1, cat2, ["foo", "bar"]], list("ABC"), fill_value=0
    )
    tm.assert_frame_equal(result, expected)
    gb = df.groupby(["A", "B"], observed=observed)
    exp_index = MultiIndex.from_arrays([cat1, cat2], names=["A", "B"])
    expected = DataFrame({"values": [1, 2, 3, 4]}, index=exp_index)
    result = gb.sum()
    if not observed:
    expected = cartesian_product_for_groupers(
    expected, [cat1, cat2], list("AB"), fill_value=0
    )
    tm.assert_frame_equal(result, expected)

    I want to test pandas version 1.0.3 against this unit test do you have any recommendation to do so?
PrimeF commented

I've added a test for this older bug which, as stated above, was already fixed a while back.

mcgeestocks commented

Looks like Closed PR#52818 should have closed this task. Pinging @phofl for visibility.

  • 👍1
aibanez7 commented

Hello,
Could this task be closed?
Thank you,