I often need to work with dataframes where the rownames (index in pandas) are like of the type [a-z][0-9]+ (e.g. 'a20','a21',... or 'b1','b11','b2',...)
I would like to be able to sort the rows based on the number alone. From what I can gather of the API, df.sort_index() does not take a function argument where I can define the sorting function.
Is there a neat way of achieving this without resorting to removing the initial characters when creating the dataframe?
You can use extract, convert to int and get position of sorted values by argsort, last use iloc:
(borrowing data from @Dark):
df = df.iloc[df.index.str.extract('(\d+)', expand=False).astype(int).argsort()]
print (df)
0
a5 5
a6 6
a10 2
a11 3
b11 7
a12 1
b12 8
a15 4
Explanation:
df = pd.DataFrame([1,2,3,4,5,6,7,8],index=['a12','a10','a11','a15','a5','a6','b11','b12'])
print (df)
0
a12 1
a10 2
a11 3
a15 4
a5 5
a6 6
b11 7
b12 8
First extract all integers:
print (df.index.str.extract('(\d+)', expand=False))
Index(['12', '10', '11', '15', '5', '6', '11', '12'], dtype='object')
Cast to integers:
print (df.index.str.extract('(\d+)', expand=False).astype(int))
Int64Index([12, 10, 11, 15, 5, 6, 11, 12], dtype='int64')
Get positions of sorted values by argsort:
print (df.index.str.extract('(\d+)', expand=False).astype(int).argsort())
[4 5 1 2 6 0 7 3]
Last select values by positions by iloc:
print (df.iloc[df.index.str.extract('(\d+)', expand=False).astype(int).argsort()])
0
a5 5
a6 6
a10 2
a11 3
b11 7
a12 1
b12 8
a15 4
By using natsort sort by index list then reindex
df=pd.DataFrame({'v':[1,2,3]},index=['b1','b11','b2'])
from natsort import natsorted, ns
df.reindex(natsorted(df.index.tolist(), alg=ns.IGNORECASE))
Out[795]:
v
b1 1
b2 3
b11 2
You can use sorted and reindex i.e
df = pd.DataFrame([1,2,3,4,5,6,7,8],index=['a12','a10','a11','a15','a5','a6','b11','b12'])
import re
df.reindex(sorted(df.index,key=lambda x: re.sub('[A-z]','',x)))
0
a10 2
a11 3
b11 7
a12 1
b12 8
a15 4
a5 5
a6 6
int :)