How to sort in customized order in python pandas?

Question

I have a dataframe in below format

country region  measure Option1 Option2
A   R1  141 max max
A   R1  141 max min
A   R1  141 max default
A   R1  86  min max
A   R1  86  min min
A   R1  86  min default
A   R1  86  default max
A   R1  86  default min
A   R1  86  default default
A   R2  215 max max
A   R2  215 max min
A   R2  215 max default
A   R2  72  min max
A   R2  72  min min
A   R2  72  min default
A   R2  72  default max
A   R2  72  default min
A   R2  72  default default
A   R3  473 max max
A   R3  473 max min
A   R3  473 max default
A   R3  36  min max
A   R3  36  min min
A   R3  36  min default
A   R3  36  default max
A   R3  36  default min
A   R3  36  default default

after sorting using df.sort(['country','Option1','Option2','region'])

country region  measure Option1 Option2
A   R1  86  default default
A   R2  72  default default
A   R3  36  default default
A   R1  86  default max
A   R2  72  default max
A   R3  36  default max
A   R1  86  default min
A   R2  72  default min
A   R3  36  default min
A   R1  141 max default
A   R2  215 max default
A   R3  473 max default
A   R1  141 max max
A   R2  215 max max
A   R3  473 max max
A   R1  141 max min
A   R2  215 max min
A   R3  473 max min
A   R1  86  min default
A   R2  72  min default
A   R3  36  min default
A   R1  86  min max
A   R2  72  min max
A   R3  36  min max
A   R1  86  min min
A   R2  72  min min
A   R3  36  min min

Here I want to specifically have the order in below format:

country region  measure Option1 Option2
A   R1  86  min min
A   R2  72  min min
A   R3  36  min min
A   R1  86  min max
A   R2  72  min max
A   R3  36  min max
A   R1  86  min default
A   R2  72  min default
A   R3  36  min default
A   R1  86  default min
A   R2  72  default min
A   R3  36  default min
A   R1  86  default default
A   R2  72  default default
A   R3  36  default default
A   R1  86  default max
A   R2  72  default max
A   R3  36  default max
A   R1  141 max min
A   R2  215 max min
A   R3  473 max min
A   R1  141 max default
A   R2  215 max default
A   R3  473 max default
A   R1  141 max max
A   R2  215 max max
A   R3  473 max max

Can anyone help me with an approach to achieve this customized order of rows?

Sorry you need to define the criteria for the sorting, also isn't this a dupe of this: http://stackoverflow.com/questions/23279238/custom-sorting-with-pandas? — EdChum, Mar 23 '16 at 10:47
Edchum,its almost the same issue as in the other question.I just have 2 columns here and there is slight variable in Option2.Will look into it.Thanks. — marupav, Mar 23 '16 at 11:25
My comment still holds you need to define the criteria for sorting, just showing the result doesn't explain enough, for instance what is the order criteria for `Option1` and `Option2` — EdChum, Mar 23 '16 at 11:26
My bad that I didnt post it here and explained in some other question.It is 1. min 2.default and 3.max.Got it worked following the other example with small exception in Option2 where for min it is min,max and default. — marupav, Mar 23 '16 at 11:33

jezrael · Answer 1 · 2016-03-23T11:40:23.227

You can use sorted Categorical, but in column Option2 is it different, stil order is min, max and default:

d3['Option1'] = pd.Categorical(d3['Option1'],['min','default','max'], ordered=True)
d3['Option2'] = pd.Categorical(d3['Option2'],['min','max','default'], ordered=True)
d3 = d3.sort_values(['country','Option1', 'Option2' ])
print d3

   country region  measure  Option1  Option2
4        A     R1       86      min      min
13       A     R2       72      min      min
22       A     R3       36      min      min
3        A     R1       86      min      max
12       A     R2       72      min      max
21       A     R3       36      min      max
5        A     R1       86      min  default
14       A     R2       72      min  default
23       A     R3       36      min  default
7        A     R1       86  default      min
16       A     R2       72  default      min
25       A     R3       36  default      min
6        A     R1       86  default      max
15       A     R2       72  default      max
24       A     R3       36  default      max
8        A     R1       86  default  default
17       A     R2       72  default  default
26       A     R3       36  default  default
1        A     R1      141      max      min
10       A     R2      215      max      min
19       A     R3      473      max      min
0        A     R1      141      max      max
9        A     R2      215      max      max
18       A     R3      473      max      max
2        A     R1      141      max  default
11       A     R2      215      max  default
20       A     R3      473      max  default
31       B     R1       79      min      min
40       B     R2       88      min      min
49       B     R3       54      min      min
30       B     R1      384      min      max
39       B     R2      439      min      max
48       B     R3      172      min      max
32       B     R1       79      min  default
41       B     R2       88      min  default
50       B     R3       54      min  default
34       B     R1       79  default      min
43       B     R2       88  default      min
52       B     R3       54  default      min
33       B     R1      384  default      max
42       B     R2      439  default      max
51       B     R3      172  default      max
35       B     R1       79  default  default
44       B     R2       88  default  default
53       B     R3       54  default  default
28       B     R1       79      max      min
37       B     R2       88      max      min
46       B     R3       54      max      min
27       B     R1      384      max      max
36       B     R2      439      max      max
45       B     R3      172      max      max
29       B     R1       79      max  default
38       B     R2       88      max  default
47       B     R3       54      max  default

Or:

d3['Option1'] = pd.Categorical(d3['Option1'],['min','default','max'], ordered=True)
d3['Option2'] = pd.Categorical(d3['Option2'],['min','default','max'], ordered=True)
d3 = d3.sort_values(['country','Option1', 'Option2' ])
print d3

   country region  measure  Option1  Option2
4        A     R1       86      min      min
13       A     R2       72      min      min
22       A     R3       36      min      min
5        A     R1       86      min  default
14       A     R2       72      min  default
23       A     R3       36      min  default
3        A     R1       86      min      max
12       A     R2       72      min      max
21       A     R3       36      min      max
7        A     R1       86  default      min
16       A     R2       72  default      min
25       A     R3       36  default      min
8        A     R1       86  default  default
17       A     R2       72  default  default
26       A     R3       36  default  default
6        A     R1       86  default      max
15       A     R2       72  default      max
24       A     R3       36  default      max
1        A     R1      141      max      min
10       A     R2      215      max      min
19       A     R3      473      max      min
2        A     R1      141      max  default
11       A     R2      215      max  default
20       A     R3      473      max  default
0        A     R1      141      max      max
9        A     R2      215      max      max
18       A     R3      473      max      max
31       B     R1       79      min      min
40       B     R2       88      min      min
49       B     R3       54      min      min
32       B     R1       79      min  default
41       B     R2       88      min  default
50       B     R3       54      min  default
30       B     R1      384      min      max
39       B     R2      439      min      max
48       B     R3      172      min      max
34       B     R1       79  default      min
43       B     R2       88  default      min
52       B     R3       54  default      min
35       B     R1       79  default  default
44       B     R2       88  default  default
53       B     R3       54  default  default
33       B     R1      384  default      max
42       B     R2      439  default      max
51       B     R3      172  default      max
28       B     R1       79      max      min
37       B     R2       88      max      min
46       B     R3       54      max      min
29       B     R1       79      max  default
38       B     R2       88      max  default
47       B     R3       54      max  default
27       B     R1      384      max      max
36       B     R2      439      max      max
45       B     R3      172      max      max

jezrael,the exception that I pointed out in Option 2 is that for combination of min in Option1 the order is min,max,default where as for default and max in Option 1 it min,default and max.That was the order expected in the output as per assignment.But I feel that controlling to that level may be challenging. Nevertheless,both the above methods seems to be logical. — marupav, Mar 23 '16 at 11:43

How to sort in customized order in python pandas?

1 Answers1