Sorting multiple columns based on list in Pandas

Question

Appreciate for any hint on how to sort a given multiple columns based on multiples list in pandas as below

import pandas as pd
sort_a=['a','d','e']
sort_b=['s1','s3','s6']
sort_c=['t1','t2','t3']
df=pd.DataFrame(zip(['a', 'e', 'd','a','a','d','e'], ['s3', 's1', 's6','s6','s3','s3','s1'], ['t3', 't2', 't1','t2','t2','t3','t3']),columns=["a", "b", "c"])

with the ordering column, say, a,c,d

The idea is something like the sort

df.sort(['a', 'c', 'd'], ascending = [sort_a, sort_c, sort_b])

Expected output

a   b   c
a   s3  t2
a   s3  t3
a   s6  t2
d   s3  t3
d   s6  t1
e   s1  t2
e   s1  t3

kindly add your expected output. My output might not be what you want — sammywemmy
– sammywemmy, Commented Aug 4, 2021 at 4:13
Hi @sammywemmy, I have change the df to reflect the use case of 3 sort_list. Your suggestion remain valid — rpb
– rpb, Commented Aug 4, 2021 at 4:27

sammywemmy · Accepted Answer · 2021-08-04 04:08:08Z

3

One option would be to create categories for each column and sort:

categories = {col : pd.CategoricalDtype(categories=cat, ordered=True) 
              for col, cat 
              in zip(df.columns, [sort_a, sort_b, sort_c])}

df.astype(categories).sort_values([*df.columns])

   a   b   c
0  a  s3  t3
2  d  s6  t1
1  e  s1  t2

answered Aug 4, 2021 at 4:08

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

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

Comments

rpb · Accepted Answer · 2021-08-30 03:30:17Z

1

While not directly related to sort with reference to a list, but the key question when posting this OP as I wanted to sort columns with string in it.

Using sort_values for pandas >= 1.1.0

With the new key argument in DataFrame.sort_values, since pandas 1.1.0, we can directly sort a column without setting it as an index using natsort.natsort_keygen:

from natsort import natsort_keygen
df=df.sort_values(
    by=['a','b','c'],
    key=natsort_keygen()
)

Output:

   a   b   c
4  a  s3  t2
0  a  s3  t3
3  a  s6  t2
5  d  s3  t3
2  d  s6  t1
1  e  s1  t2
6  e  s1  t3

answered Aug 30, 2021 at 3:30

rpb

3,3073 gold badges32 silver badges72 bronze badges

Collectives™ on Stack Overflow

Sorting multiple columns based on list in Pandas

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