Why is my df.sort_values() not correctly sorting the data points?

Question

I have a dataframe with returns from various investments in %. sort.values does not correctly order my returns. For example I just want to simply see the TEST column returns sorted by lowest to highest or vice versa. Please look at the test output, it is not correct.

df.sort_values('TEST')

gives me an output of returns that are NOT sorted correctly. Sort values code not in correct order

Also I am having an issue where it sorts positive numbers lowest to highest, then half way down starts again for negative numbers lowest to highest.

I just want it to look like following: -3% -1% -0.5% 1% 2% 5%

looks like you are sorting strings lexicographically, check this answer: stackoverflow.com/questions/33314175/… — M_S
– M_S, Commented Feb 3, 2023 at 19:05

Timeless · Accepted Answer · 2023-02-03 19:13:09Z

0

Go for numpy.lexsort and boolean indexing :

import numpy as np

arr = np.array([float(x.rstrip("%")) for x in df["TEST"]])
idx = np.lexsort((arr,))

df = df.iloc[idx]

Output :

print(df)

    TEST
0    -3%
1    -1%
2  -0.5%
3     1%
4     2%
5     5%

Input used :

df = pd.DataFrame({"TEST": ["1%", "-3%","-0.5%", "-1%", "5%", "2%"]})

    TEST
0     1%
1    -3%
2  -0.5%
3    -1%
4     5%
5     2%

answered Feb 3, 2023 at 19:13

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

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

Comments

mozway · Accepted Answer · 2023-02-03 19:34:22Z

0

The issue is that the lexicographic order of string is different from the natural order (1->10->2 vs 1->2->10).

One option using the key parameter of sort_values:

df.sort_values('TEST', key=lambda s: pd.to_numeric(s.str.extract(r'(-?\d+\.?\d*)', expand=False)))

Or:

df.sort_values('TEST', key=lambda s: pd.to_numeric(s.str.rstrip('%')))

Output:

    TEST
1    -3%
3    -1%
2  -0.5%
0     1%
5     2%
4     5%

edited Feb 3, 2023 at 19:34

answered Feb 3, 2023 at 19:29

mozway

267k13 gold badges56 silver badges106 bronze badges

2 Comments

JoeBloggs90 Over a year ago

Hi your first solution works - thank you. TEST was just one column. Let's say I now have a big data set with 100 columns and I want to apply this same logic, is there a way to do this without listing each column indiidually. I.E Can I somehow convert the whole CSV to have this formatting? Thank you

mozway Over a year ago

@JoeBloggs90 can you provide an extra input/output example with more columns? Are all columns to be sorted with the same logic?

Collectives™ on Stack Overflow

Why is my df.sort_values() not correctly sorting the data points?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related