Sorting values in a pandas column with type string

Question

I'm trying to get the values from highest to lowest which is pretty straightforward using the code but this is what I'm receiving when I do:

Input:

s.sort_values(by('1617 Entries & Exits), ascending = True)

Output:

1617 Entries & Exits Station Name 2559 NaN Manchester United Football Ground 2558 NaN Heathrow Terminals 2 & 3 2557 NaN Heathrow Terminal 5 2556 NaN Heathrow Terminal 4 1285 998,316 King's Lynn 1230 997,912 Irvine 915 996 Gainsborough Central 641 994 Cynghordy 1697 990,438 Ockendon 1540 99,704 Metheringham 847 99,610 Falmouth Docks 1294 99,484 Kintbury 2378 99,403,096 Waterloo 1642 99,394 Newcourt 34 99,380 Albrighton 2196 99,042 Summerston 2392 989,728 Wellingborough 1048 984,504 Hampton Wick 1677 984,332 North Wembley 2302 983,704 Trowbridge 1876 982,934 Rectory Road 296 982,592 Bootle New Strand 94 98,472 Ashburys 1282 98,290 Kinghorn 1474 98,234 Lytham 970 98,218 Gorebridge 1220 98,140 Insch 1978 979,098 Scarborough 494 978,986 Cheam 928 974 Garth (Powys)

I was expecting it to return the highest number in that column '1617 Entries & Exits' which is actually 99,039,875 and ascend from there but it isn't happening.

Do I need to remove the missing values (NaN) first or change the data type of the column first?

The data type for that column is a series by the way: pandas.core.series.Series

cs95 · Accepted Answer · 2018-03-15 15:30:45Z

2

You might want to use the thousands argument when loading in df:

df = pd.read_csv(..., thousands=',')

This should read your second column in as a numeric column. Try sorting it now.

If that doesn't work, there's always the ugly fix:

df['1617 Entries & Exits'] = pd.to_numeric(
       df['1617 Entries & Exits'].astype(str).str.replace(',', ''),
       errors='coerce'
)

I hope it doesn't come to this.

edited Mar 15, 2018 at 15:30

answered Mar 15, 2018 at 15:12

cs95

406k106 gold badges744 silver badges797 bronze badges

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

1 Comment

snow_fall Over a year ago

This is super clear - unfortunately I had to do the ugly fix but it's all a learning curve. Thank you!

Collectives™ on Stack Overflow

Sorting values in a pandas column with type string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related