How to sort values in order with a Pandas dataframe

Question

I have a dataframe as follow:

armed	signs_of_mental_illness	count
gun	False	628
gun	True	155
knife	False	142
vehicle	False	104
knife	True	84
metal pole	True	1
metal rake	True	1

I want to sort this dataframe as follow:

armed	signs_of_mental_illness	count
gun	False	628
gun	True	155
knife	False	142
knife	True	84

I tired

armed_mental = focus_age_group.groupby(['armed', 'signs_of_mental_illness'])['id'].count().sort_values(ascending=False)

that had product above result. But I have difficulty getting what I want. The category (armed) with highest number(True + False) of should be on top of the dataframe. Then follow by True and False.

mozway · Accepted Answer · 2023-10-27 19:22:50Z

If you want to sort by the total per "armed", y first need to combine the counts with groupby.transform:

import numpy as np

order = np.lexsort([df['signs_of_mental_illness'],
                    -df.groupby('armed')['count'].transform('sum')])

out = df.iloc[order]

Alternative:

out = (df.assign(total=df.groupby('armed')['count'].transform('sum'))
         .sort_values(by=['total', 'signs_of_mental_illness'], 
                      ascending=[False, True])
         .drop(columns='total')
       )

Output:

        armed  signs_of_mental_illness  count
0         gun                    False    628
1         gun                     True    155
2       knife                    False    142
4       knife                     True     84
3     vehicle                    False    104
5  metal pole                     True      1
6  metal rake                     True      1

Rabia Naz · Accepted Answer · 2023-10-29 16:38:48Z

You can achieve the desired sorting using the sort_values method by specifying multiple columns for sorting. In your case, you should start by sorting the dataframe based on the 'armed' column in descending order to have the highest counts at the top for each 'armed' category. Next, sort by the 'signs_of_mental_illness' column in descending order ('True' before 'False'). Lastly, sort by the 'count' column in descending order.

Here's the code based on the above explanation:

sorted_dataframe = your_dataframe.sort_values(by=['armed', 'signs_of_mental_illness', 'count'], ascending=[False, False, False])

As a result, your sorted dataframe will appear as follows:

     armed      signs_of_mental_illness  count
0    gun                 False            628
1    gun                 True             155
2    knife               False            142
4    knife               True             84
3    vehicle             False            104
6    metal pole          True             1
7    metal rake          True             1

Collectives™ on Stack Overflow

How to sort values in order with a Pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related