How to do conditional sorting on a Pandas dataframe (i.e. ascending and descending order within one column based on values in another column)?

user_8667016 0
Viewed 1486

Is it possible to do sort a Pandas DataFrame's rows with respect to multiple columns and have some of the rows within a column being placed ascending order and other rows (within the same column) being placed in descending order? Here's a small reproducible example of what I'm looking for:

Setup

import pandas as pd

df = pd.DataFrame(data={'class':['A','A','A','B','B','B','C','C','C'],
                        'val':[20,10,15,55, 75, 71,3,1,2],
                        'sub':['a','c','b','b','a','c','c','a','b']})

print(df)
# This is the original unsorted DataFrame
#  class  val sub
#0     A   20   a
#1     A   10   c
#2     A   15   b
#3     B   55   b
#4     B   75   a
#5     B   71   c
#6     C    3   c
#7     C    1   a
#8     C    2   b

How can I sort the df DataFrame object above according to the following "rules"?

  • 1st priority: Sorted according to the "class" column in ascending alphabetical order
  • 2nd priority: Then, within each unique value of the "class" column, the rows are then sorted based on the "val" column as follows:
    • For rows with "class" == "A", sort the "val" values in ascending order
    • For rows with "class" == "B", sort the "val" values in descending order
    • For rows with "class" == "C", sort the "val" values in ascending order

In more practical terms, the result I'm looking for would look like this:

# This is the sorted DataFrame that I'm trying to get
#  class  val sub
#1     A   10   c
#2     A   15   b
#0     A   20   a
#4     B   75   a
#5     B   71   c
#3     B   55   b
#7     C    1   a
#8     C    2   b
#6     C    3   c

Is there a direct way to do this?

My attempt

In my attempt to solve this problem, I created an extra "sorting" column and manipulated the values in it such that they would numerically all follow ascending order.

df['val_temp'] = df.apply(lambda x: x['val'] if x['class'] in ['A','C'] else -x['val'], axis=1)

df.sort_values(by=['class','val_temp'], ascending=[True,True]).drop(columns='val_temp')

The problem is that this workaround looks really dirty and doesn't work for non-numeric values. For example, if I wanted the 2nd priority sorting to be done on the "sub" column, I wouldn't know how to proceed.

Does Pandas offer an interface to do this or do I just have to rely on "dirty" workarounds like the one above?

PS

I've looked at this thread and this thread, but they only provide a math-based workaround like the one I do above and which wouldn't work when we need to sort a string column like df["sub"].

pandas dataframe sorting python
1 Answers

You can use GroupBy.apply and a dictionary to map the sorting order per group:

order = {'A': True, 'B': False, 'C': True}

(df.groupby('class', group_keys=False)
   .apply(lambda s: s.sort_values(by='val', ascending=order[s.name]))
 )

NB. groupby sorts the groups by default, so sorting "class" is implicitly done

Output:

  class  val sub
1     A   10   c
2     A   15   b
0     A   20   a
4     B   75   a
5     B   71   c
3     B   55   b
7     C    1   a
8     C    2   b
6     C    3   c
user_163434640
Related