Pandas Dataframe sort_values() multiple column with custom key-CodePudding

I'm trying to sort by level first, followed by reviews. The dataframe dtype are all str.

ranking_level_sort = {
    "Exceptional": 5,
    "Excellent": 4,
    "Very good": 3,
    "Good": 2,
    "Review score": 1,
    "None": 0
}

hotel_sorted = hotel.sort_values(by=["level", "reviews"],  key=lambda x: x.map(ranking_level_sort), ascending=[False, False])
hotel_sorted.reset_index(drop=True, inplace=True)
hotel_sorted

What I Got

name	price	level	reviews
Miniinn - Double Room with Private Bathroom	47	Exceptional	1
The Empire Brunei	309	Excellent	1464
Higher Hotel	24	Excellent	865
Radisson Hotel Brunei	120	Excellent	1314
Abdul Razak Hotel Apartment	59	Excellent	129

What I Expect

name	price	level	reviews
Miniinn - Double Room with Private Bathroom	47	Exceptional	1
The Empire Brunei	309	Excellent	1464
Radisson Hotel Brunei	120	Excellent	1314
Higher Hotel	24	Excellent	865
Abdul Razak Hotel Apartment	59	Excellent	129

So far, I've managed to sort by level, and is not followed by reviews. The key argument in sort_values can only take one lambda expression. I'm not sure how this can be solved, any pointers?

CodePudding user response：

There is used map for both columns, so in reviews no matching and returned NaNs, so need replace them by original values in fillna like:

hotel_sorted = hotel.sort_values(by=["level", "reviews"],  
                                 key=lambda x: x.map(ranking_level_sort).fillna(x), 
                                 ascending=False)
hotel_sorted.reset_index(drop=True, inplace=True)

print (hotel_sorted)
                                          name  price        level  reviews
0  Miniinn - Double Room with Private Bathroom     47  Exceptional        1
1                            The Empire Brunei    309    Excellent     1464
2                        Radisson Hotel Brunei    120    Excellent     1314
3                                 Higher Hotel     24    Excellent      865
4                  Abdul Razak Hotel Apartment     59    Excellent      129