BUG: GroupBy.idxmax breaks when grouping by Categorical with unused categories

[jorisvandenbossche]

From SO: http://stackoverflow.com/questions/31690493/idxmax-doesnt-work-on-seriesgroupby-that-contains-nan

idxmax works with a normal dataframe:

In [60]: df = pd.DataFrame({'a':list('abab'), 'b':[0,1,2,3]})

In [61]: df.groupby('a').idxmax()
Out[61]:
   b
a
a  2
b  3

Also when 'a' is a categorical there are no problems:

In [62]: df['a'] = df['a'].astype('category')

In [63]: df.groupby('a').idxmax()
Out[63]:
   b
a
a  2
b  3

But when it is a categorical with an unused category, idxmax and apply don't work, only agg does:

In [67]: df['a'] = df['a'].cat.add_categories('c')

In [68]: df['a']
Out[68]:
0    a
1    b
2    a
3    b
Name: a, dtype: category
Categories (3, object): [a, b, c]

In [70]: df.groupby('a').idxmax()
Out[70]:
Empty DataFrame
Columns: []
Index: []

In [71]: df.groupby('a').apply(lambda x: x.idxmax())
...
ValueError: could not convert string to float: a

In [72]: df.groupby('a').agg(lambda x: x.idxmax())
Out[72]:
    b
a
a   2
b   3
c NaN

Others like first, last, max, mean do work correctly (idxmin also fails).

[jorisvandenbossche]

The strange thing is I get even different error messages with the example on SO compared to my dummy example:

In [27]: income = DataFrame({'name': ['Adam', 'Bill', 'Chris', 'Dave', 'Edison',
 'Frank'],
   ....:                     'age': [22, 24, 31, 45, 51, 55],
   ....:                     'income': [1000, 2500, 1200, 1500, 1300, 1600],
   ....:                     })

In [28]:

In [28]: income = income.drop(2)

In [29]: ageBin = pd.cut(income.age, [20, 30, 40, 50, 60])

In [30]: grouped = income.groupby([ageBin])

In [31]: grouped.income.idxmax()
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-31-ce76f8e7a4f6> in <module>()
----> 1 grouped.income.idxmax()

c:\users\vdbosscj\scipy\pandas-joris\pandas\core\groupby.pyc in idxmax(self, axi
s, out, skipna)

c:\users\vdbosscj\scipy\pandas-joris\pandas\core\groupby.pyc in wrapper(*args, *
*kwargs)
    588                         return self._aggregate_item_by_item(name, *args,
 **kwargs)
    589                     except (AttributeError):
--> 590                         raise ValueError
    591
    592         return wrapper

ValueError:

[cfperez]

Also, .value_counts() does not work under the same conditions, with the same error.

On the other hand, .nunique() does work for me (#11635). Is there an issue where we should collect groupby+category related test cases?

[rbu]

Just ran into this bug (in the variant where I insert pd.cut() as a Series and then do .groupby("cuts"). As @jorisvandenbossche points out, changing the series type from category to something like int64 works around this bug.

[gosuto-inzasheru]

A quick fix for this is to handle empty categories yourself: .agg(lambda x : np.nan if x.count() == 0 else x.idxmax(). Would be nice to have this implemented in .idxmax() itself though; it is very difficult figuring out what the problem is when all it does is give back an empty result.

[rhshadrach]

I currently get ValueError: attempt to get argmax of an empty sequence on master, which is at least a more sensible error message.

The only value that seems to make sense for output in the case of a missing category np.nan. This has the unfortunate side-effect of making the result float64, which is almost always the wrong result when one wants to use an index.

Any thoughts on what the output should be, @jorisvandenbossche?

[rhshadrach]

Note: in pandas 3.0, we'll need to specify observed=False to reproduce the bug.

[rhshadrach]

I'm thinking we should use NA for unobserved categories. This agrees with the other reductions (e.g. min), and makes it so that the op doesn't raise on certain values rather than others.

[rhshadrach]

Raising is the expected behavior for consistency with Series and DataFrame (see #33941 (comment)). However, we don't currently raise: we only raise when there is one grouping (e.g. df.groupby('a')).