Pandas function sorting mismatch

Question

I am having trouble with sorting column with string and floats. Here is the example:

Used code: df.sort_values([0, 1], ascending=[True, True])

input:

       0              1   2
0   JJAA   Extensão 0.0   4
1   JJAA   Extensão 6.0   3
2   JJAA  Extensão 12.0   3
3   JJAT   Extensão 0.0   3
4   JJAT   Extensão 6.0   2
5   JJSL  Extensão 18.0   1
6   JJSP  Extensão 12.0   3
7   JJSP  Extensão 24.0   1
0   JJAA      Perna 1.5   4
1   JJAA      Perna 4.5  16
2   JJAA      Perna 6.0   8
3   JJAA      Perna 7.5   4
4   JJAA      Perna 9.0   8
5   JJAT      Perna 3.0   4
6   JJAT      Perna 6.0   4
7   JJAT      Perna 7.5   4
8   JJAT      Perna 9.0   4
9   JJAT     Perna 10.5   4
10  JJSL      Perna 7.5   4
11  JJSP      Perna 4.5   4
12  JJSP      Perna 6.0   4
13  JJSP      Perna 7.5   8

output:

        0              1      2
    0   JJAA   Extensão 0.0   4
 ** 2   JJAA  Extensão 12.0   3 **
    1   JJAA   Extensão 6.0   3
    0   JJAA      Perna 1.5   4
    1   JJAA      Perna 4.5  16
    2   JJAA      Perna 6.0   8
    3   JJAA      Perna 7.5   4
    4   JJAA      Perna 9.0   8
    3   JJAT   Extensão 0.0   3
    4   JJAT   Extensão 6.0   2
 ** 9   JJAT     Perna 10.5   4 **
    5   JJAT      Perna 3.0   4
    6   JJAT      Perna 6.0   4
    7   JJAT      Perna 7.5   4
    8   JJAT      Perna 9.0   4
    5   JJSL  Extensão 18.0   1
    10  JJSL      Perna 7.5   4
    6   JJSP  Extensão 12.0   3
    7   JJSP  Extensão 24.0   1
    11  JJSP      Perna 4.5   4
    12  JJSP      Perna 6.0   4
    13  JJSP      Perna 7.5   8

Repair that the "**" rowns mismatch with the correct order. I think that the sort is "seeing" only the first number of the float value. Any ideas to fix that?

user_7964527 · Answer

Let us try natsort

import natsort as ns 
out = df.iloc[ns.index_natsorted(df['1'])].sort_values(['0'],kind='mergesort')
Out[15]: 
       0             1   2
0   JJAA   Extensão0.0   4
1   JJAA   Extensão6.0   3
2   JJAA  Extensão12.0   3
0   JJAA      Perna1.5   4
1   JJAA      Perna4.5  16
2   JJAA      Perna6.0   8
3   JJAA      Perna7.5   4
4   JJAA      Perna9.0   8
3   JJAT   Extensão0.0   3
4   JJAT   Extensão6.0   2
5   JJAT      Perna3.0   4
6   JJAT      Perna6.0   4
7   JJAT      Perna7.5   4
8   JJAT      Perna9.0   4
9   JJAT     Perna10.5   4
5   JJSL  Extensão18.0   1
10  JJSL      Perna7.5   4
6   JJSP  Extensão12.0   3
7   JJSP  Extensão24.0   1
11  JJSP      Perna4.5   4
12  JJSP      Perna6.0   4
13  JJSP      Perna7.5   8

user_15600610 · Answer

It looks like the first column is being sorted as str values. If this column is supposed to store floats, you might go back to where you created this dataframe to correct the data type. However, as a quick fix you should be able to use

df.sort_values([0, 1], ascending=[True, True], key=float)

Using the key parameter lets you specify how to sort the values

df.sort_values([0, 1], ascending=[True, True], key=float)

Using the key parameter lets you specify how to sort the values

Pandas function sorting mismatch

2 Answers