Python GroupBy sort Descending by column within grouping

user_18569213 0
Viewed 149

I have a dataset with the following columns - ID, Old Stage, New Stage and Cycle Number. Each ID has multiple rows (2+), depicting a series of back and forth between old and new stage; this is detailed by the Cycle Number.

I am trying to group multiple rows by ID (that's ok), but within that grouping I want to sort by Cycle Number. For e.g. if ID 1 has 6 cycles, I want cycle #6 to be listed first, then 5, 4, 3, etc.

grouped2 = df.groupby(['ID', 'Old_Stage', 'New_Stage'], as_index=False)['Cycle_Number'].max().sort_values(['Cycle_Number'], ascending=False)
print(grouped2)

This is what I tried, however, it only sorts the Cycle Numbers in descending order overall, not within the ID grouping.

EDIT

Current dataframe:

|ID |Old Stage   |New Stage   |Cycle Number|
|100|In Progress |Under Review|1
|100|Not Started |In Progress |0
|100|Under Review|Completed   |2
|100|Completed   |In Progress |3

Desired dataframe:

|ID |Old Stage   |New Stage   |Cycle Number|
|100|Completed   |In Progress |3
|   |Under Review|Completed   |2
|   |In Progress |Under Review|1
|   |Not Started |In Progress |0
pandas python
1 Answers

As furas and jezrael mentioned, using pandas.DataFrame.sort_values, as follows, should solve OP's problem

df = df.sort_values(by=['ID', 'Cycle Number'], ascending=[True, False])

[Out]:
    ID     Old Stage     New Stage  Cycle Number
3  100     Completed   In Progress             3
2  100  Under Review     Completed             2
0  100   In Progress  Under Review             1
1  100   Not Started   In Progress             0

However, OP mentioned

It doesn't keep it grouped by ID

It seems that OP is referring to the order of the index. As one can see on the output of the previous dataframe, it goes from 3, to 2, to 0, to 1, and, IIUC, OP wants it going from 0 to 1, to 2, and so on.

If that is the case, what is missing is just .reset_index(drop=True) as follows

df = df.sort_values(by=['ID', 'Cycle Number'], ascending=[True, False]).reset_index(drop=True)

[Out]:
    ID     Old Stage     New Stage  Cycle Number
0  100     Completed   In Progress             3
1  100  Under Review     Completed             2
2  100   In Progress  Under Review             1
3  100   Not Started   In Progress             0
user_71098690
Related