pandas sort values of one column by the average of the other column

Question

assuming I have the following dataframe:

    A    B
1  cat   3
2  cat   7
3  dog   5
4  dog   8
5  dog   11 
6  bird  3
7  bird  5

The average of 'cat' is 5, the average of 'dog' is 8 and the average of 'bird' is 4, thus I want to sort the dataframe so that dog will be first, than cat and than bird, like the following:

    A    B
1  dog   5
2  dog   8
3  dog   11
4  cat   3
5  cat   7
6  bird  3
7  bird  5

what is the best way of doing this? I tried to set the 'A' column to index and than play with sorting it but I couldn't figure it out. Any help will be appreciated!

Answer 1

Use GroupBy.transform with mean for Series of aggregate values with same size as original DataFrame, then use argsort in descending order for positions and reorder by DataFrame.iloc:

df = df.iloc[(-df.groupby('A')['B'].transform('mean')).argsort()]
print (df)
      A   B
3   dog   5
4   dog   8
5   dog  11
1   cat   3
2   cat   7
6  bird   3
7  bird   5

Or convert aggregate values to ordered Categorical and then sort_values:

a = df.groupby('A')['B'].mean().sort_values(ascending=False)

df['A'] = pd.Categorical(df['A'], ordered=True, categories=a.index)
df = df.sort_values('A')
print (df)
      A   B
3   dog   5
4   dog   8
5   dog  11
1   cat   3
2   cat   7
6  bird   3
7  bird   5