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?

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
– user3483203, Commented Jul 16, 2018 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
– a_guest, Commented Jul 16, 2018 at 18:04

W Stokvis · Accepted Answer · 2018-07-16 18:13:54Z

1

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

edited Jul 16, 2018 at 18:13

answered Jul 16, 2018 at 18:04

W Stokvis

1,43910 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sally Kim Over a year ago

great idea! Thanks!

Collectives™ on Stack Overflow

custom data sorting in pandas dataframe

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related