Pandas Dataframe sort_values() multiple column with custom key

Question

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?

Answer 1

The map is applied for both columns. It finds no matches for reviews and returns NaN values. So you need to replace those by the original values using fillna, like this:

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