Sorting column labels by numeric in string, Python

Question

I have successfully connected Python to Microsoft Access Database. The problem appears when I am trying to sort the column labels in the data frame by number in increasing order. The column names also contain characters.

I have looked into several sorting functions, but none of them seems to work for this issue.

My data frame is as follows;

C1  C10  C11  C12  C13  C14 ... C2  C20  C21 ... C3 ...

How I want my columns to be sorted;

C1  C2  C3  C4  C5  C6  C7  C8  C9  C10  C11 ...

My data frame also contain other component, such as benzene, toluene, etc, so I would like the list to be in alphabetic order too.

Moreover, is there a way to sort it as;

... C4   C5   iC5   nC5   C6   iC6   nC6.

The above question is most important, but if someone know if/how this can be done, please advice be!

On beforehand, thanks for your help!

Maybe you the prefixes can be stored in a separate column? So something like | Prefix | Code | where prefix is i or n (or nothing), and Code is like C4. — Andrew Grass
– Andrew Grass, Commented Aug 13, 2019 at 15:14
natsorted is what you want here for the first case. See stackoverflow.com/questions/57203726/…. The second seems to be a bit vague, is there a way to distinguish that i is a prefix and that C is not? — ALollz
– ALollz, Commented Aug 13, 2019 at 15:18

Celius Stingher · Accepted Answer · 2019-08-13 15:12:36Z

1

I think this might be answer you are looking for:

data.reindex_axis(sorted(data.columns, key=lambda x: float(x[1:])), axis=1)

You can freely modify the value in x[1:] to include or exclude more characters in the string.

answered Aug 13, 2019 at 15:12

Celius Stingher

18.4k6 gold badges26 silver badges54 bronze badges

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

Comments

ivallesp · Accepted Answer · 2019-08-13 15:13:25Z

0

For the first case, you could do the following.

df = df[list(sorted(df.columns.tolist()))]

For the second case, maybe reversing the strings could work.

rev = lambda cols: list(map(lambda x: "".join(list(reversed(x))), cols)) 
cols = df.columns.tolist()
df = df[rev(list(sorted(rev(df.columns.tolist()))))]

answered Aug 13, 2019 at 15:13

ivallesp

2,2421 gold badge17 silver badges24 bronze badges

Collectives™ on Stack Overflow

Sorting column labels by numeric in string, Python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related