Sort by multiple columns in Pandas, but with 'na_position' for secondary sort

user_9235409 0
Viewed 1645

I have a Pandas DataFrame like this

df = pd.DataFrame(
 {
   'OrderID': ['o1','o2','o3','o4','o5'],
   'CustomerID': ['c1','c1','c2','c2','c3'],
   'CustomerRating': [5,1,3, NaN,NaN]
    
 }
)

I want to sort it by CustomerID first, and then by CustomerRating and in a such way that NaNs in Customer Rating come last. I know about the df.sort_values(na_position = 'last'), but that just works for the primary sort. How do I make it work for the secondary sort?

So just like I specify ascending argument as a list where each element corresponds to one sort level, I need something similar for na_position argument, so something like this:

df.sort_values(['CustomerID', 'CustomerRating', ascending = [False, False], na_position =['last', 'last']]

How do I do it?

Thanks

pandas sorting python
3 Answers

From the documentation you have to Specify list for multiple sort orders. My interpretation, the sort order has to be logical. Additionally, you cant specificy na_position to correspond to a column without 'NaN'.

 print(df.sort_values(['CustomerID', 'CustomerRating'], ascending = [False, False], na_position ='first'))#Here, NaN is first because `c3` and `c2` appear on top



  OrderID CustomerID  CustomerRating
4      o5         c3             NaN
3      o4         c2             NaN
2      o3         c2             3.0
0      o1         c1             5.0
1      o2         c1             1.0

print(df.sort_values(['CustomerID', 'CustomerRating'], ascending = [True, True], na_position ='last'))# This is reversed again because the sort is logical



   OrderID CustomerID  CustomerRating
1      o2         c1             1.0
0      o1         c1             5.0
2      o3         c2             3.0
3      o4         c2             NaN
4      o5         c3             NaN

print(df.sort_values(['CustomerID', 'CustomerRating'], ascending = [False, True], na_position ='first'))



  OrderID CustomerID  CustomerRating
4      o5         c3             NaN
3      o4         c2             NaN
2      o3         c2             3.0
1      o2         c1             1.0
0      o1         c1             5.0
user_89869750

The following code will order the rows based on the count of null values present in each row.

df.iloc[df.isnull().sum(axis=1).mul(1).argsort()]

Alternate Solution

The below code will work perfectly for all test cases. Null values will always be present at the last and at the same time ordered by OrderID and CustomerID.

null_df=df[df.isnull().any(axis=1)]
all_df=df[~df.index.isin(null_df.index)]

all_df.sort_values(['OrderID', 'CustomerID'], ascending = [True, True], inplace=True)
null_df.sort_values(['OrderID', 'CustomerID'], ascending = [True, True], inplace=True)

final_df=pd.concat([all_df, null_df]).reset_index(drop=True)
user_129302350

simple df.sort_values(['CustomerID','CustomerRating'])

user_42740850
Related