Pandas sort one column by custom order and the other naturally

Question

Consider the following code:

import pandas
import numpy

strs = ['custom','sort']*5
df = pandas.DataFrame(
    {
        'string': strs,
        'number': numpy.random.randn(len(strs)),
    }
)

sort_string_like_this = {'sort': 0, 'custom': 1}
print(df.sort_values(['string','number'], key=lambda x: x.map(sort_string_like_this)))

which prints

   string    number
1    sort -0.074041
3    sort  1.057676
5    sort -0.612289
7    sort  0.757922
9    sort  0.671288
0  custom -0.339373
2  custom -0.320231
4  custom -1.125380
6  custom  2.120829
8  custom -0.031580

I would like to sort it according to the column string using a custom ordering as given by the dictionary and the column number using the natural ordering of numbers. How can this be done?

Answer 1

You can use a condition in the sort key function:

df.sort_values(
    ["string", "number"],
    key=lambda x: x.map(sort_string_like_this) if x.name == "string" else x,
)

   string    number
7    sort -1.673626
3    sort -0.212634
5    sort -0.071417
9    sort  0.413497
1    sort  0.489508
8  custom -1.787110
0  custom  0.230875
4  custom  0.535791
2  custom  0.671282
6  custom  2.119993