How to sort with predetermined order in Pandas

Question

My dataset df look like this:

date           high
2018-01-01     -1
2018-01-02     1
2018-01-03     -2
2018-01-04     0
...., ....
2018-12-31     1

Where,

-2 >= high <= 2

high is always between -2 and 2

I want to sort the value of high in the following pattern:

To start, Group all 0 and sort by date and so on for other values.

Sort the high value in the following order:

It would be best if it's flexible enough that I can change the order if required.

I know how to sort in asc or desc by doing this:

df.sort_values(by='high', ascending=False)

Could you please help me solve how do I sort using predetermined values?

df = df.sort_values(['high', 'date'], ascending=[True,True]) — Madhur Yadav, Jul 29 '19 at 18:52
The proposed code does exactly what you are describing. I think you are not correctly explaining your problem. — Akaisteph7, Jul 29 '19 at 19:04
`df.assign(temp=df['high'].abs()).sort_values(['temp', 'date']).drop('temp', axis=1)` — Erfan, Jul 29 '19 at 19:13
@Erfan good solution. But this does not satisfy OP's requirement of `"It would be best if it's flexible enough that I can change the order if required."`. It is best to Use a `Categorical Series` in this case. — harvpan, Jul 29 '19 at 19:15

score 5 · Accepted Answer · answered Jul 29 '19 at 19:12

You need to define high as a Categorical Series with order as your choice.

order = [0 , 1, -1, 2, -2]
df['high'] = pd.Categorical(df['high'], order)
df.sort_values(['high', 'date'])

Output:

        date    high
3   2018-01-04  0
1   2018-01-02  1
4   2018-12-31  1
0   2018-01-01  -1
2   2018-01-03  -2

1 Answers1