CODEWITHSUNDEEP
python
Ralphy Chomps
Ralphy Chomps

Reputation: 87

groupby() with sort_values() - how to yield correct order of priority

I have this DF

    type    sub_type    price
0   bed     single      15
1   bed     double      25
2   chair   single      75
3   chair   double      105
4   couch   single      45
5   couch   double      95

This is the order of sum-total price by type

    type    price
0   chair   180
1   couch   140
2   bed     40

CURRENT OUTPUT - I am trying to have this reflected when I groupby() and sort_values() but I cannot stop it from sorting the 'type' name alphabetically - as follows

df_1 = pd.DataFrame(df.groupby(['type','sub_type']).sum()['price'])
df_1.sort_values(['type', 'price'], ascending=False)

    

type    sub_type    price
couch     double    95
          single    45
chair     double    105
          single    75
bed       double    25
          single    15

DESIRED OUTPUT - chair and couch are in proper order based off of total price

type      sub_type  price
chair     double    105
          single    75
couch     double    95
          single    45
bed       double    25
          single    15

Thank you in advance

Upvotes: 0

Views: 83

Answers (1)

Rick M
Rick M

Reputation: 1012

You can use pd.merge:

q = df.groupby('type')['price'].sum().sort_values(ascending=False).reset_index()
    
df_1 = pd.merge(q[['type']], df.sort_values('price', ascending=False), on='type')

print(df_1)
    type sub_type  price
0  chair   double    105
1  chair   single     75
2  couch   double     95
3  couch   single     45
4    bed   double     25
5    bed   single     15

Upvotes: 1

Related Questions