Home > Back-end >  Sort pandas data frame alphabetically not working [closed]
Sort pandas data frame alphabetically not working [closed]

Time:09-17

I know we can sort df using df.sort_values() but it is not working for me for some reason. I am stuck here for almost 6 hours.

I have a dictnory

dict_table  = {'2 bedroom flat in London': 3515, '2 bedroom flat in london': 3515, 'Dalton': 188, 'Lincolnshire': 796, 'London': 3515, 'Yorkshire': 0, 'garden': 566, 'greenwich': 1740, 'london': 3515}

I am converting it to a data frame using

df = pd.DataFrame(dict_table.items())
df.columns = ['Search query', 'No of search results']
df.index  =1

    Search query                No of search results
1   2 bedroom flat in London    3515
2   2 bedroom flat in london    3515
3   Dalton                      188
4   Lincolnshire                796
5   London                      3515
6   Yorkshire                   0
7   garden                      566
8   greenwich                   1740
9   london                      3515

then I try to sort it using df = df.sort_values('Search query') which didn't work but df.sort_values('No of search results') does work.

    Search query                No of search results
6   Yorkshire                   0
3   Dalton                      188
7   garden                      566
4   Lincolnshire                796
8   greenwich                   1740
1   2 bedroom flat in London    3515
2   2 bedroom flat in london    3515
5   London                      3515
9   london                      3515

soo can anyone help me sort data frame using Search query.

please please please

CodePudding user response:

Your solution working perfectly, because data are lexicographical sorted: Numbers first, then uppercase and lowercase. (like mentioned Corralien in comments)

IIUC if need sorting by lowercases:

df = df.sort_values(by=['Search query'], key=lambda x: x.str.lower())
print (df)
               Search query  No of search results
1  2 bedroom flat in London                  3515
2  2 bedroom flat in london                  3515
3                    Dalton                   188
7                    garden                   566
8                 greenwich                  1740
4              Lincolnshire                   796
5                    London                  3515
9                    london                  3515
6                 Yorkshire                     0

CodePudding user response:

If your goal is to sort ignoring case AND leading numbers, you can create a custom sort function like this:

f = lambda x: x.str.replace('^\d \s*', '', regex=True).str.lower()
print(df.sort_values('Search query', key=f))

               Search query  No of search results
1  2 bedroom flat in London                  3515
2  2 bedroom flat in london                  3515
3                    Dalton                   188
7                    garden                   566
8                 greenwich                  1740
4              Lincolnshire                   796
5                    London                  3515
9                    london                  3515
6                 Yorkshire                     0
  • Related