sort grade letters within pandas groupby-CodePudding

I have a df and upon groupby

df.groupby(['Gender', 'Scored Grade']).size().reset_index()

I get

    Gender  Scored Grade    0
0   Female  A-  1
1   Female  B   4
2   Female  B   4
3   Female  B-  9
4   Female  C   3
5   Female  C   4
6   Male    B   8
7   Male    B   4
8   Male    B-  9
9   Male    C   5
10  Male    C   8
11  Male    C-  1

I want to sort grade letters and thus tried this method after some searching here.

df.sort_values("Scored Grade", ascending=False,
 key=lambda x: (x[0], - x.endswith(' ')   x.endswith('-'))).groupby(['Gender', 'Scored Grade']).size().reset_index()

but it gave me an error

AttributeError: 'Series' object has no attribute 'endswith'

I also tried this method

def particular_sort(series):
    """
    Must return one Series
    """
    return series.apply(lambda x: (x[0], - x.endswith(' ')   x.endswith('-')))


df2 = df.groupby(['Gender', 'Scored Grade']).size().reset_index()
df2.loc[df2["Gender"] == "Male"].sort_values("Gender", key=particular_sort)

but it did not work,

I want to sort grade letters within each group of gender so as B > B > B-, etc.

CodePudding user response：

We could do pd.Categorical with input grade

import itertools 
cate = list(map(''.join,itertools.product(list('ABCD'),[' ','','-'])))
cate
Out[958]: ['A ', 'A', 'A-', 'B ', 'B', 'B-', 'C ', 'C', 'C-', 'D ', 'D', 'D-']
df['Scored Grade'] = pd.Categorical(df['Scored Grade'], cate)
df = df.sort_values('Scored Grade')
out = df.value_counts(['Gender', 'Scored Grade'])