CODEWITHSUNDEEP
pythonstringpandassorting
Carl
Carl

Reputation: 121

Sort Strings Containing Numbers and Delimeters in Pandas

I've come across an issue at work that pertains to sorting. I'm currently utilizing Pandas to hold our data and I need to sort on a column that contains a string with numbers and delimiters.

I have already tried using the vanilla df.sort_values('Field Name') on the column that I want to sort, however some unwanted results have occurred.

Sample data in Python format:

import pandas as pd
lis=[]
for i in ['99','100','101','102']:
    for j in map(str,[1,2,3,4,5,6,7,8,10,20,22,21,34]):
        for k in map(str,[1,2,11,12,22,23,33,16,17]):
            lis.append(i+'_'+j+'-'+k)
y = pd.DataFrame(dict(Field=lis))
y.sort_values('Field')

Example output:

         Field
0      100_1-1
1     100_1-11
2     100_1-12
3     100_1-16
4     100_1-17
5      100_1-2
6     100_1-22
7     100_1-23
8     100_1-33
9     100_10-1
10   100_10-11
11   100_10-12
12   100_10-16
13   100_10-17
14    100_10-2
15   100_10-22
16   100_10-23
17   100_10-33
18     100_2-1
19    100_2-11
20    100_2-12
21    100_2-16
22    100_2-17
....

As you can see from this, the list should start with the '99' strings. Also, you have 100_1-11, 100_1-12, 100_1-13 before 100_1-2.

I can fix the first of these issues with the following method, and in theory if I know the delimiters and number of delimiters apriori, then I could iteratively keep doing this until I get the result that I want.

y.reindex(y['Field'].str.split('_',1,expand=True)[0].astype(int).sort_values(0).index).reset_index(drop=True)

But since the delimiters '_' and '-' might be used, they are not necessarily going to be used in the data I receive, nor will I know that there will be only 2 delimiters. So in theory I could get something as bad as the following:

100_1_22-12-34:5

and I still need to be able to sort them as expected.

However, is there a way to get the results that I want in a more general form using Pandas? To be clear, I want all numbers to be in order as expected with as little code as possible.

Upvotes: 1

Views: 150

Answers (1)

Patrick Artner
Patrick Artner

Reputation: 51683

You need to convert your string-numbers to integer after splitting them at all your various characters. Use a tuple of int to sort:

You can do this f.e. like so:

import pandas as pd
lis=[]

# mix up numbers / strings and values
for i in ['103','99','102','101']:
    for j in map(str,[10,2,34,4,5,1,22,21,3]):
        for k in map(str,[1,2,33,16,17]):
            lis.append(i+'_'+j+'-'+k)
df = pd.DataFrame(dict(Field=lis))

# split mixed up stuff using regex ('-' first so it does NOT denote a char-range)
# convert all remainders to int and make them a tuple to sort on (seperate column)
df["tup"] = df["Field"].str.split(r"[-_:]").apply(lambda x: tuple(map(int, x)))
# sort on seperate column
df = df.sort_values("tup")
print(df)

Output:

[180 rows x 1 columns]
        Field            tup
70     99_1-1     (99, 1, 1)
71     99_1-2     (99, 1, 2)
73    99_1-16    (99, 1, 16)
74    99_1-17    (99, 1, 17)
72    99_1-33    (99, 1, 33)
50     99_2-1     (99, 2, 1)
51     99_2-2     (99, 2, 2)
53    99_2-16    (99, 2, 16)
54    99_2-17    (99, 2, 17)
..        ...            ...
34  103_22-17  (103, 22, 17)
32  103_22-33  (103, 22, 33)
10   103_34-1   (103, 34, 1)
11   103_34-2   (103, 34, 2)
13  103_34-16  (103, 34, 16)
14  103_34-17  (103, 34, 17)
12  103_34-33  (103, 34, 33)

[180 rows x 2 columns]

Before sorting:

         Field
0     103_10-1
1     103_10-2
2    103_10-33
3    103_10-16
4    103_10-17
5      103_2-1
..         ...
173  101_21-16
174  101_21-17
175    101_3-1
176    101_3-2
177   101_3-33
178   101_3-16
179   101_3-17

Upvotes: 2

Related Questions