13

MCVE

df = pd.DataFrame({
    'Cat': ['SF', 'W', 'F', 'R64', 'SF', 'F'], 
    'ID': [1, 1, 1, 2, 2, 2]
})

df.Cat = pd.Categorical(
    df.Cat, categories=['R64', 'SF', 'F', 'W'], ordered=True)

As you can see, I've define an ordered categorical column on Cat. To verify, check;

0     SF
1      W
2      F
3    R64
4     SF
5      F
Name: Cat, dtype: category
Categories (4, object): [R64 < SF < F < W]

I want to find the largest category PER ID. Doing groupby + max works.

df.groupby('ID').Cat.max()

ID
1    W
2    F
Name: Cat, dtype: object

But I don't want ID to be the index, so I specify as_index=False.

df.groupby('ID', as_index=False).Cat.max()

   ID Cat
0   1   W
1   2  SF

Oops! Now, the max is taken lexicographically. Can anyone explain whether this is intended behaviour? Or is this a bug?

Note, for this problem, the workaround is df.groupby('ID').Cat.max().reset_index().

Note, 

>>> pd.__version__
'0.22.0'
cs95
  • 379,657
  • 97
  • 704
  • 746
  • 4
    Note, in v0.23.0, this also works: `df.groupby("ID", as_index=False).Cat.apply(max)`. – jpp Jun 09 '18 at 21:46
  • 2
    from - http://pandas.pydata.org/pandas-docs/stable/groupby.html In the case of multiple keys, the result is a MultiIndex by default, though this can be changed by using the as_index option. Since the df was created using two lists, as_index = False, enables the list index, whose comparisons are lexicographic? – pyeR_biz Jun 09 '18 at 22:57
  • 1
    So you're saying the orderer Categorical variable gets lost and is treated as a string when the Multiindex is created? Sounds like a good bug report for pandas. github. – smci Jun 10 '18 at 01:17
  • @smci I want to, but I am so so lazy... – cs95 Jun 10 '18 at 04:11
  • what about: `df.groupby('ID', as_index=False).max()`? It seems to give a good result. – Mabel Villalba Sep 28 '18 at 11:34

1 Answers1

1

This is not intended behavior, it's a bug.

Source diving shows the flag does two completely different things. The one simply ignores grouper levels and names, it just takes the values with a new range index. The other one clearly keeps them.

firelynx
  • 30,616
  • 9
  • 91
  • 101