custom data sorting in pandas dataframe

Question

Below is my data.

TX  Subj    W1  W2  W3  W4
0B  C2  33.1    32.9    35.6    35.9
1A  B12 22.5    22.2    21.9    22.6
1A  B10 22.4    22.1    21.6    22.9
1B  A6  28.7    29.1    28.6    29.8
0B  C1  30.7    32.1    35.4    36.1
0A  C4  39.6    39.8    42.9    43.4
0A  B4  42.7    41.4    44.1    43.9
1A  C6  31.5    31.8    34.4    33.4
0A  D7  23.4    26.3    29.7    29.4
1A  D2  31.6    27.8    30.2    30.5
1B  A20 21.7    21.8    22.3    23.7
1C  A1  34.7    34.8    34.6    38.3
1A  C5  27.5    28.1    29.1    28.4

I would like to sort this data in this specific order '0A', '1A', '0B', '1B', '1C'. I could use the sort_index. But it gave me sorting data in the ascending order (0A, 0B, 1A, 1B, 1C).

Which function should I use to sort this data by the specific order?

Possible duplicate of [Custom sorting with Pandas](https://stackoverflow.com/questions/23279238/custom-sorting-with-pandas) — user3483203, Jul 16 '18 at 18:01
From the dup, just create a dictionary like this: `{'0A': 0, '0B': 2, '1A': 1, '1B': 3, '1C': 4}`, then do `df['rank'] = df.TX.map(dct)`, and then sort by rank — user3483203, Jul 16 '18 at 18:01
You need to be more clear on the sorting. Is your index compound of _one_ letter and a number? Or could it be multiple letters? Are the _digits_ to be treated as one number or single digits (i.e. `20A > 3A`)? Depending on these requirements you can just `reverse` the index for sorting or you need to parse it specifically. — a_guest, Jul 16 '18 at 18:04

W Stokvis · Answer 1 · 2018-07-16T18:13:54.113

Without hard coding a dictionary, you can try this. The first line creates two columns TX1 and TX2 by parsing TX and then you use sort_values to order it. Additionally, I've dropped the columns I created. That part is optional:

df = pd.concat([df, df.TX.apply(lambda s: pd.Series({'TX1': s[1], 'TX2':s[0]}))], axis=1)
df.sort_values(['TX1','TX2'], ascending = [True, True]).drop(['TX1','TX2'], axis=1)
     TX Subj    W1    W2    W3    W4
    5   0A   C4  39.6  39.8  42.9  43.4
    6   0A   B4  42.7  41.4  44.1  43.9
    8   0A   D7  23.4  26.3  29.7  29.4
    1   1A  B12  22.5  22.2  21.9  22.6
    2   1A  B10  22.4  22.1  21.6  22.9
    7   1A   C6  31.5  31.8  34.4  33.4
    9   1A   D2  31.6  27.8  30.2  30.5
    12  1A   C5  27.5  28.1  29.1  28.4
    0   0B   C2  33.1  32.9  35.6  35.9
    4   0B   C1  30.7  32.1  35.4  36.1
    3   1B   A6  28.7  29.1  28.6  29.8
    10  1B  A20  21.7  21.8  22.3  23.7
    11  1C   A1  34.7  34.8  34.6  38.3

custom data sorting in pandas dataframe

1 Answers1