Sort pandas data frame alphabetically not working

Question

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

`df.sort_values('Search query')` works. Data are lexicographical sorted: Numbers first, then uppercase and lowercase. — Corralien, Sep 09 '21 at 06:34
Does this answer your question? [Sorting a list of strings with numbers in them in python](https://stackoverflow.com/questions/43484731/sorting-a-list-of-strings-with-numbers-in-them-in-python) — Stef, Sep 09 '21 at 13:18

score 1 · Answer 1 · answered Sep 09 '21 at 06:33

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

Thanks for the help i was not aware of lexicographical sort – Alfin Paul Sep 09 '21 at 11:23 — Alfin Paul, Sep 09 '21 at 11:23

score 1 · Accepted Answer · answered Sep 09 '21 at 06:42

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

Thanks for helping, I was not aware of lexicographical sorted. — Alfin Paul, Sep 09 '21 at 11:23

Sort pandas data frame alphabetically not working

2 Answers2