pythonpandas

How can I groupby on one column while sorting by another column (row by row within the group) over the entire dataframe


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.


Solution

  • 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)