pandas human sort of index

Question

Probably this has been asked before but I cant find any information

df = pd.DataFrame(
    {"i1":[1,1,1,1,2,4,4,2,3,3,3,3],
     "i2":[1,3,2,2,1,1,2,2,1,1,3,2],
     "d1":['c1','ac2','c3','c4','c5','c6','c7','c8','c9','c10','c11','a']}
)
df.set_index('d1', inplace=True)
df.sortlevel()

yields

obviously this is not desired. I would like to have c10 and c11 at the end. How can I provide a key to the sorting algorithm (e.g. split the strings and ints) ?

This is lexicographical sorting of strings, you probably have to provide a sort function that indeed splits and converts the numeric part to numbers. Tuples `('c',11)` will sort correctly. — Andras Deak -- Слава Україні, Sep 28 '17 at 11:40
so how can I provide such a function ? In python I could use a key but how could I do that using pandas ? — Moritz, Sep 28 '17 at 11:41
Related and probable dupe: https://stackoverflow.com/questions/29580978/naturally-sorting-pandas-dataframe — EdChum, Sep 28 '17 at 12:12

Eric Duminil · Accepted Answer · 2017-09-28T12:12:08.067

Plain python, with `sorted` and `key`

You could define a function in order to split the index into a pair of letters (as string) and numbers (as integer):

d1 = ['c1','ac2','c3','c4','c5','c6','c7','c8','c9','c10','c11','a']

import re
pattern = re.compile('([a-z]+)(\d*)', re.I)
def split_index(idx):
    m = pattern.match(idx)
    if m:
        letters = m.group(1)
        numbers = m.group(2)
        if numbers:
            return (letters, int(numbers))
        else:
            return (letters, 0)

As an example:

>>> split_index('a')
('a', 0)
>>> split_index('c11')
('c', 11)
>>> split_index('c1')
('c', 1)

You can then use this function as a key to sort the indices lexicographically:

print(sorted(d1, key=split_index))
# ['a', 'ac2', 'c1', 'c3', 'c4', 'c5', 'c6', 'c7', 'c8', 'c9', 'c10', 'c11']

Pandas sort

You could create a new, temporary column with the tuples from split_index, sort according to this column and delete it:

import pandas as pd
df = pd.DataFrame(
    {"i1":[1,1,1,1,2,4,4,2,3,3,3,3],
     "i2":[1,3,2,2,1,1,2,2,1,1,3,2],
     "d1":['c1','ac2','c3','c4','c5','c6','c7','c8','c9','c10','c11','a']}
)
df['order'] = df['d1'].map(split_index)
df.sort_values('order', inplace=True)
df.drop('order', axis=1, inplace=True)
df.set_index('d1', inplace=True)
print(df)

It outputs:

     i1  i2
d1         
a     3   2
ac2   1   3
c1    1   1
c3    1   2
c4    1   2
c5    2   1
c6    4   1
c7    4   2
c8    2   2
c9    3   1
c10   3   1
c11   3   3

Nice solution. Works well for multiindex dataframes. I will update my question with your solution asap — Moritz, Sep 28 '17 at 12:25
@Moritz: Why would you update your question? If you want to show that an answer helped you, you can mark it as accepted. — Eric Duminil, Sep 28 '17 at 12:37
To show how it would work for multiindex frames. Nice to have information for noobs like me. — Moritz, Sep 28 '17 at 13:44

jezrael · Answer 2 · 2017-09-29T14:25:22.503

I think you need extract numeric from index values and sort MultiIndex created by extracted numbers (\d+) and non numbers (\D+) by sort_index:

#change ordering from default
df = df.sort_index(ascending=False)

a = df.index.str.extract('(\d+)', expand=False).astype(float)
b = df.index.str.extract('(\D+)', expand=False)
df.index = [b, a, df.index]
print (df)
             i1  i2
d1 d1   d1         
c  9.0  c9    3   1
   8.0  c8    2   2
   7.0  c7    4   2
   6.0  c6    4   1
   5.0  c5    2   1
   4.0  c4    1   2
   3.0  c3    1   2
   11.0 c11   3   3
   10.0 c10   3   1
   1.0  c1    1   1
ac 2.0  ac2   1   3
a  NaN  a     3   2

df = df.sort_index(level=[0,1]).reset_index([0,1], drop=True)
print (df)
     i1  i2
d1         
a     3   2
ac2   1   3
c1    1   1
c3    1   2
c4    1   2
c5    2   1
c6    4   1
c7    4   2
c8    2   2
c9    3   1
c10   3   1
c11   3   3

np.lexsort working with numeric only :(

pandas human sort of index

2 Answers2

Plain python, with sorted and key

Pandas sort

Plain python, with `sorted` and `key`