Sorting tables is a regular Excel operation. We sort a table to make it easier to view or use the data. When your data is large or contains calculations, sorting in Excel can be quite slow.
Let us take the sampleExcelFile.xlsx file as an example here.
We can sort the excel data using the python pandas module in 2 ways.
- Using sort_index() Function.
- Using sort_values() Function.
sort_index() Function:
The dataframe.sort_index() function of the pandas module sorts objects by labels along the specified axis.
Essentially, the sorting algorithm is performed to the axis labels rather than the actual data in the dataframe, and the data is rearranged as a result. We have complete control over which sorting method we use. We can utilize one of three sorting algorithms: ‘quicksort, “mergesort,’ or ‘heapsort.’
Syntax:
dataframe.sort_index(axis=0, level=None, ascending=True, inplace=False)
Parameters
- axis: index, columns for sorting. Sorting by index is indicated by 0, while sorting by columns is shown by 1. 0 is the default.
- level: If it is not None, sort on values in the specified index levels
- ascending: It indicates sorting either in ascending or descending order. True represents ascending order, while False represents descending order.
- inplace: If the inplace argument is set to True, the resultant dataframe replaces the original one, otherwise no. False is the default value.
Sorting Excel data using Python
Below are different ways to sort Excel data in Python:
- Sorting Excel Table by Columns
- Sorting Excel Table by Index(Ascending Order)
- Sorting Excel Table in Descending Order
- Sorting Excel Table by a Specified Column in Ascending Order
- Sorting Excel Table by a Specified Column in Descending order
- Sort by Multi Columns (Static Input)
- Sort by Multi Columns (User Input)
Printing the Excel file data as pandas Dataframe
# import pandas module using the import keyword
import pandas as pd
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Print the above dataframe
print(rslt_dataframe)
Output:
Website name Name Age City State Student \ 0 Sheets Tips Vikram 21 Hyderabad Telangana Yes 1 Python-Programs Ashritha 22 Pune Mumbai Yes 2 BTechGeeks Manish 19 Indore Madhya Pradesh Yes 3 PythonArray Anish 20 Rajkot Gujarat Yes 4 Sheets Tips Vishal 18 Ranchi Jharkhand Yes 5 Python-Programs Pavan 23 Lucknow Uttarpradesh No 6 Sheets Tips John 32 Los Angeles America No 7 Python-Programs Virat 34 Agra Delhi No 8 BTechGeeks Cary 36 Tokyo Japan No 9 PythonArray Tara 39 Helsinki Finland No 10 Sheets Tips Smith 42 Nairobi Kenya No 11 Python-Programs Elizabeth 66 Paris Italy No Employed Married Scholorship Id 0 No No Not Applicable 1234 1 No No Already Taken 2344 2 No No Already Taken 1345 3 No No Already Taken 1356 4 No No Already Taken 1938 5 Yes No Not Applicable 2345 6 Yes Yes Not Applicable 5622 7 Yes No Not Applicable 1734 8 Yes Yes Already Taken 4562 9 Yes Yes Not Applicable 2365 10 Yes Yes Not Applicable 2463 11 Yes Yes Not Applicable 2677
Method #1: Sorting Excel Table by Columns
We may want to display the columns in a specific order (like alphabetical, increasing/decreasing, etc.). With axis = 1, we can utilise the sort_index() method. The table is now sorted alphabetically by column names, as shown in the output below. However, since inplace=False is the default, the generated data frame does not replace the original data frame.
Approach:
- Import pandas module using the import keyword
- Pass some random excel file path as an argument to the read_excel() function to read an excel file as a panads dataframe and store it in a variable.
- Sort the Excel data by column names in an alphabetical order along the specified axis using the sort_index() function by passing the axis value as an argument to it and print it
- Here axis=1 implies sorting by column.
- The Exit of the Program.
Below is the implementation:
# import pandas module using the import keyword
import pandas as pd
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Sort the excel data by column names in an alphabetical order along the specified axis
# using the sort_index() function by passing the axis value as an argument to it and print it
# Here axis=1 implies sorting by column
print(rslt_dataframe.sort_index(axis=1))
Output:
Age City Employed Id Married Name Scholorship \
0 21 Hyderabad No 1234 No Vikram Not Applicable
1 22 Pune No 2344 No Ashritha Already Taken
2 19 Indore No 1345 No Manish Already Taken
3 20 Rajkot No 1356 No Anish Already Taken
4 18 Ranchi No 1938 No Vishal Already Taken
5 23 Lucknow Yes 2345 No Pavan Not Applicable
6 32 Los Angeles Yes 5622 Yes John Not Applicable
7 34 Agra Yes 1734 No Virat Not Applicable
8 36 Tokyo Yes 4562 Yes Cary Already Taken
9 39 Helsinki Yes 2365 Yes Tara Not Applicable
10 42 Nairobi Yes 2463 Yes Smith Not Applicable
11 66 Paris Yes 2677 Yes Elizabeth Not Applicable
State Student Website name
0 Telangana Yes Sheets Tips
1 Mumbai Yes Python-Programs
2 Madhya Pradesh Yes BTechGeeks
3 Gujarat Yes PythonArray
4 Jharkhand Yes Sheets Tips
5 Uttarpradesh No Python-Programs
6 America No Sheets Tips
7 Delhi No Python-Programs
8 Japan No BTechGeeks
9 Finland No PythonArray
10 Kenya No Sheets Tips
11 Italy No Python-Programs
Method #2: Sorting Excel Table by Index(Ascending Order)
Approach:
- Import pandas module using the import keyword
- Pass some random excel file path as an argument to the read_excel() function to read an excel file as a panads dataframe and store it in a variable.
- Sort the excel in an ascending order using the sort_index() function by passing the ascending value as True as an argument to it and printing it.
- The Exit of the Program.
Below is the implementation:
# import pandas module using the import keyword
import pandas as pd
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Sort the excel in an ascending order using the sort_index()
# function by passing the ascending value as True as an argument to it and print it
print(rslt_dataframe.sort_index(ascending=True))
Output:
Website name Name Age City State Student \ 0 Sheets Tips Vikram 21 Hyderabad Telangana Yes 1 Python-Programs Ashritha 22 Pune Mumbai Yes 2 BTechGeeks Manish 19 Indore Madhya Pradesh Yes 3 PythonArray Anish 20 Rajkot Gujarat Yes 4 Sheets Tips Vishal 18 Ranchi Jharkhand Yes 5 Python-Programs Pavan 23 Lucknow Uttarpradesh No 6 Sheets Tips John 32 Los Angeles America No 7 Python-Programs Virat 34 Agra Delhi No 8 BTechGeeks Cary 36 Tokyo Japan No 9 PythonArray Tara 39 Helsinki Finland No 10 Sheets Tips Smith 42 Nairobi Kenya No 11 Python-Programs Elizabeth 66 Paris Italy No Employed Married Scholorship Id 0 No No Not Applicable 1234 1 No No Already Taken 2344 2 No No Already Taken 1345 3 No No Already Taken 1356 4 No No Already Taken 1938 5 Yes No Not Applicable 2345 6 Yes Yes Not Applicable 5622 7 Yes No Not Applicable 1734 8 Yes Yes Already Taken 4562 9 Yes Yes Not Applicable 2365 10 Yes Yes Not Applicable 2463 11 Yes Yes Not Applicable 2677
Method #3: Sorting Excel Table in Descending Order
Approach:
- Import pandas module using the import keyword
- Pass some random excel file path as an argument to the read_excel() function to read an excel file as a panads dataframe and store it in a variable.
- Sort the excel data in descending order using the sort_index() function by passing the ascending value as False as an argument to it and print it.
- The Exit of the Program.
Below is the implementation:
# import pandas module using the import keyword
import pandas as pd
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Sort the excel data in descending order using the sort_index()
# function by passing the ascending value as False as an argument to it and print it
print(rslt_dataframe.sort_index(ascending=False))
Output:
Website name Name Age City State Student \ 11 Python-Programs Elizabeth 66 Paris Italy No 10 Sheets Tips Smith 42 Nairobi Kenya No 9 PythonArray Tara 39 Helsinki Finland No 8 BTechGeeks Cary 36 Tokyo Japan No 7 Python-Programs Virat 34 Agra Delhi No 6 Sheets Tips John 32 Los Angeles America No 5 Python-Programs Pavan 23 Lucknow Uttarpradesh No 4 Sheets Tips Vishal 18 Ranchi Jharkhand Yes 3 PythonArray Anish 20 Rajkot Gujarat Yes 2 BTechGeeks Manish 19 Indore Madhya Pradesh Yes 1 Python-Programs Ashritha 22 Pune Mumbai Yes 0 Sheets Tips Vikram 21 Hyderabad Telangana Yes Employed Married Scholorship Id 11 Yes Yes Not Applicable 2677 10 Yes Yes Not Applicable 2463 9 Yes Yes Not Applicable 2365 8 Yes Yes Already Taken 4562 7 Yes No Not Applicable 1734 6 Yes Yes Not Applicable 5622 5 Yes No Not Applicable 2345 4 No No Already Taken 1938 3 No No Already Taken 1356 2 No No Already Taken 1345 1 No No Already Taken 2344 0 No No Not Applicable 1234
Method #4: Sorting Excel Table by a Specified Column in Ascending Order
After learning how to sort by index, let’s see how to sort by a specific column. Sort the table using the “state” column. Since ascending order is utilized by default, states with appear with ascending order. Of course, by specifying ascending=False, we can reverse the table.
Approach:
- Import pandas module using the import keyword.
- Pass some random excel file path as an argument to the read_excel() function to read an excel file as a panads dataframe and store it in a variable.
- Sort the excel data in ascending order by passing the column name as an argument to the sort_values() function
- Apply on the above dataframe and print the results.
- The Exit of the Program.
Below is the implementation:
# import pandas module using the import keyword
import pandas as pd
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Sort the excel data in ascending order by passing the column name as an argument to the sort_values() function
# Apply on the above dataframe and print the results
print(rslt_dataframe.sort_values(by='State'))
Output:
Website name Name Age City State Student \ 6 Sheets Tips John 32 Los Angeles America No 7 Python-Programs Virat 34 Agra Delhi No 9 PythonArray Tara 39 Helsinki Finland No 3 PythonArray Anish 20 Rajkot Gujarat Yes 11 Python-Programs Elizabeth 66 Paris Italy No 8 BTechGeeks Cary 36 Tokyo Japan No 4 Sheets Tips Vishal 18 Ranchi Jharkhand Yes 10 Sheets Tips Smith 42 Nairobi Kenya No 2 BTechGeeks Manish 19 Indore Madhya Pradesh Yes 1 Python-Programs Ashritha 22 Pune Mumbai Yes 0 Sheets Tips Vikram 21 Hyderabad Telangana Yes 5 Python-Programs Pavan 23 Lucknow Uttarpradesh No Employed Married Scholorship Id 6 Yes Yes Not Applicable 5622 7 Yes No Not Applicable 1734 9 Yes Yes Not Applicable 2365 3 No No Already Taken 1356 11 Yes Yes Not Applicable 2677 8 Yes Yes Already Taken 4562 4 No No Already Taken 1938 10 Yes Yes Not Applicable 2463 2 No No Already Taken 1345 1 No No Already Taken 2344 0 No No Not Applicable 1234 5 Yes No Not Applicable 2345
Method #5: Sorting Excel Table by a Specified Column in Descending order
After learning how to sort by index, let’s see how to sort by a specific column. Sort the table using the “state” column. Since ascending order is utilized by default, states with appear with ascending order.
Approach:
- Import pandas module using the import keyword.
- Pass some random excel file path as an argument to the read_excel() function to read an excel file as a panads dataframe and store it in a variable.
- Sort the excel data in ascending order by passing the column name as an argument to the sort_values() function and apply it to the above dataframe and print the results.
- The Exit of the Program.
Below is the implementation:
# import pandas module using the import keyword
import pandas as pd
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Sort the excel data in ascending order by passing the column name as an argument to the sort_values() function
# Apply on the above dataframe and print the results
print(rslt_dataframe.sort_values(by='State',ascending=False))
Output:
Website name Name Age City State Student \ 5 Python-Programs Pavan 23 Lucknow Uttarpradesh No 0 Sheets Tips Vikram 21 Hyderabad Telangana Yes 1 Python-Programs Ashritha 22 Pune Mumbai Yes 2 BTechGeeks Manish 19 Indore Madhya Pradesh Yes 10 Sheets Tips Smith 42 Nairobi Kenya No 4 Sheets Tips Vishal 18 Ranchi Jharkhand Yes 8 BTechGeeks Cary 36 Tokyo Japan No 11 Python-Programs Elizabeth 66 Paris Italy No 3 PythonArray Anish 20 Rajkot Gujarat Yes 9 PythonArray Tara 39 Helsinki Finland No 7 Python-Programs Virat 34 Agra Delhi No 6 Sheets Tips John 32 Los Angeles America No Employed Married Scholorship Id 5 Yes No Not Applicable 2345 0 No No Not Applicable 1234 1 No No Already Taken 2344 2 No No Already Taken 1345 10 Yes Yes Not Applicable 2463 4 No No Already Taken 1938 8 Yes Yes Already Taken 4562 11 Yes Yes Not Applicable 2677 3 No No Already Taken 1356 9 Yes Yes Not Applicable 2365 7 Yes No Not Applicable 1734 6 Yes Yes Not Applicable 5622
Method #6: Sort by Multi Columns (Static Input)
We can also perform a multi-columns sort. In the example below, State names are sorted first, and then “age” is sorted again inside each State.
Approach:
- Import pandas module using the import keyword
- Pass some random excel file path as an argument to the read_excel() function to read an excel file as a panads dataframe and store it in a variable
- Pass the column’s name as a list.
- Sort the excel data in ascending order by passing the column names list as an argument to the sort_values() function
- Apply on the above dataframe and print the results.
- The Exit of the Program.
Below is the implementation:
# import pandas module using the import keyword
import pandas as pd
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Pass the columns name as an list
# Sort the excel data in ascending order by passing the column names list as an argument to the sort_values() function
# Apply on the above dataframe and print the results
print(rslt_dataframe.sort_values(by=['State','Age']))
Output:
Website name Name Age City State Student \ 6 Sheets Tips John 32 Los Angeles America No 7 Python-Programs Virat 34 Agra Delhi No 9 PythonArray Tara 39 Helsinki Finland No 3 PythonArray Anish 20 Rajkot Gujarat Yes 11 Python-Programs Elizabeth 66 Paris Italy No 8 BTechGeeks Cary 36 Tokyo Japan No 4 Sheets Tips Vishal 18 Ranchi Jharkhand Yes 10 Sheets Tips Smith 42 Nairobi Kenya No 2 BTechGeeks Manish 19 Indore Madhya Pradesh Yes 1 Python-Programs Ashritha 22 Pune Mumbai Yes 0 Sheets Tips Vikram 21 Hyderabad Telangana Yes 5 Python-Programs Pavan 23 Lucknow Uttarpradesh No Employed Married Scholorship Id 6 Yes Yes Not Applicable 5622 7 Yes No Not Applicable 1734 9 Yes Yes Not Applicable 2365 3 No No Already Taken 1356 11 Yes Yes Not Applicable 2677 8 Yes Yes Already Taken 4562 4 No No Already Taken 1938 10 Yes Yes Not Applicable 2463 2 No No Already Taken 1345 1 No No Already Taken 2344 0 No No Not Applicable 1234 5 Yes No Not Applicable 2345
Method #7: Sort by Multi Columns (User Input)
Giving the column names as user input to sort them.
Approach:
- Import pandas module using the import keyword
- Take a list to the column names and store it in a variable
- Pass some random excel file path as an argument to the read_excel() function to read an excel file as a panads dataframe and store it in a variable
- Sort the excel data in ascending order by passing the column names list as an argument to the sort_values() function
- Apply on the above dataframe and print the results.
- The Exit of the Program.
Below is the implementation:
# import pandas module using the import keyword
import pandas as pd
# Take a list to the column names and store it in a variable
columnNames=['State','Age']
# Pass some random excel file path as an argument to the read_excel()
# function to read an excel file as a panads dataframe and store it in a variable
rslt_dataframe = pd.read_excel('sampleExcelFile.xlsx')
# Sort the excel data in ascending order by passing the column names list as an argument to the sort_values() function
# Apply on the above dataframe and print the results
print(rslt_dataframe.sort_values(by=columnNames))
Output:
Website name Name Age City State Student \ 6 Sheets Tips John 32 Los Angeles America No 7 Python-Programs Virat 34 Agra Delhi No 9 PythonArray Tara 39 Helsinki Finland No 3 PythonArray Anish 20 Rajkot Gujarat Yes 11 Python-Programs Elizabeth 66 Paris Italy No 8 BTechGeeks Cary 36 Tokyo Japan No 4 Sheets Tips Vishal 18 Ranchi Jharkhand Yes 10 Sheets Tips Smith 42 Nairobi Kenya No 2 BTechGeeks Manish 19 Indore Madhya Pradesh Yes 1 Python-Programs Ashritha 22 Pune Mumbai Yes 0 Sheets Tips Vikram 21 Hyderabad Telangana Yes 5 Python-Programs Pavan 23 Lucknow Uttarpradesh No Employed Married Scholorship Id 6 Yes Yes Not Applicable 5622 7 Yes No Not Applicable 1734 9 Yes Yes Not Applicable 2365 3 No No Already Taken 1356 11 Yes Yes Not Applicable 2677 8 Yes Yes Already Taken 4562 4 No No Already Taken 1938 10 Yes Yes Not Applicable 2463 2 No No Already Taken 1345 1 No No Already Taken 2344 0 No No Not Applicable 1234 5 Yes No Not Applicable 2345