pandas index - sort string index by numeric substring

Question

Working with Pandas in Python 3.8.

Given an Index of string values that looks like this:

import pandas as pd

foo = pd.Index(['score_1', 'score_10', 'score_11', 'score_12', 'score_13', 'score_14',
       'score_15', 'score_16', 'score_17', 'score_18', 'score_19', 'score_2',
       'score_20', 'score_21', 'score_22', 'score_23', 'score_24', 'score_25',
       'score_26', 'score_27', 'score_3', 'score_4', 'score_5', 'score_6',
       'score_7', 'score_8', 'score_9'],
      dtype='object', name='score_field')

What's the "right" way to get it sorted so that the values are in numerical order, ex: 'score_1', 'score_2' ... 'score_9', 'score_10', etc... ?

This doesn't work:

foo.sort_values(key=lambda x: int(x.split('_')[1]))
AttributeError: 'Index' object has no attribute 'split'

And this doesn't work:

foo.sort_values(key=lambda val: val.str.split('_').str[1].astype(int))
AttributeError: Can only use .str accessor with string values!

This does work, but feels ugly:

foo = pd.Index(sorted(foo.to_list(), key=lambda x: int(x.split('_')[1])),
      dtype=foo.dtype, name=foo.name)

anky · Accepted Answer · 2021-04-22 20:57:35Z

1

Honestly, what you have makes sense to me, however, if you want to use pure pandas way, use Index.str.split and argsort:

foo[foo.str.split('_').str[1].astype(int).argsort()]

Index(['score_1', 'score_2', 'score_3', 'score_4', 'score_5', 'score_6',
   'score_7', 'score_8', 'score_9', 'score_10', 'score_11', 'score_12',
   'score_13', 'score_14', 'score_15', 'score_16', 'score_17', 'score_18',
   'score_19', 'score_20', 'score_21', 'score_22', 'score_23', 'score_24',
   'score_25', 'score_26', 'score_27'],
  dtype='object', name='score_field')

Or if you are okay for a 3rd party lib:

import natsort as ns
pd.Index(ns.natsorted(foo),name=foo.name)

edited Apr 22, 2021 at 20:57

answered Apr 22, 2021 at 20:43

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

2 Comments

sql_knievel Over a year ago

Interesting. That argsort() method worked. I'm sure there must be an answer with the .sort_values() method, but I guess I'll let it go for now. Thanks!

anky Over a year ago

@sql_knievel As far as I now, sort_values shouldnt work on strings unless you split and convert to int which is a long process compared to argsort, if you find something, please let me know. TIA :)

Collectives™ on Stack Overflow

pandas index - sort string index by numeric substring

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related