I have a dataframe that looks like this:
id total
1 50
1 0
1 0
2 100
2 0
2 0
3 75
3 0
3 0
But I need it to sort by the total
in descending order, while keeping the rows grouped by id
. Like this:
id total
2 100
2 0
2 0
3 75
3 0
3 0
1 50
1 0
1 0
I've tried some suggestions using groupby like this:
grouped = df.groupby('id').apply(lambda g: g.sort_values('total', ascending=False))
It looks like what it's doing is grouping and sorting the id
in ascending order and then sub-sorting the total
within each grouped id
. But I need it to sort all the rows in the total
while keeping the rows grouped by id
Any suggestions would be appreciated.
You can also do this by cummax()
method,sort_values()
method and loc
accessor:
df=df.loc[df.cummax().sort_values('id',ascending=False).index]
OR
Via groupby()
and assign()
method:
df=df.assign(order=df.groupby('id').cummax()).sort_values('order',ascending=False).drop('order',1)