Sorting Pandas DataFrames by Multiple Columns

Sorting data is a fundamental operation in data analysis, and pandas provides an efficient way to sort DataFrames by multiple columns. In this tutorial, we will cover the basics of sorting DataFrames using the sort_values method and explore various scenarios where you might need to sort your data.

Introduction to Sorting

The sort_values method is used to sort a DataFrame by one or more columns. It returns a new sorted DataFrame and leaves the original DataFrame unchanged, unless you specify the inplace=True parameter.

Basic Sorting Example

Let’s start with a simple example where we have a DataFrame with three columns: ‘a’, ‘b’, and ‘c’. We want to sort this DataFrame by column ‘b’ in ascending order and then by column ‘c’ in descending order.

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({
    'a': [1, 2, 3],
    'b': [4, 3, 4],
    'c': [5, 6, 7]
})

# Sort the DataFrame by column 'b' in ascending order and then by column 'c' in descending order
df_sorted = df.sort_values(by=['b', 'c'], ascending=[True, False])

print(df_sorted)

In this example, we pass a list of columns to the by parameter to specify the sorting order. The ascending parameter is also a list where each element corresponds to the sorting order for each column.

Sorting with Multiple Columns

When sorting by multiple columns, pandas uses a stable sort algorithm, which means that when multiple rows have the same value in the first column, the sorting of these rows will be determined by the next column, and so on.

Here’s an example where we create a DataFrame with two columns: ‘date’ and ‘value’. We want to sort this DataFrame by ‘date’ in ascending order and then by ‘value’ in descending order.

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({
    'date': ['2022-01-01', '2022-01-02', '2022-01-01'],
    'value': [10, 20, 30]
})

# Sort the DataFrame by column 'date' in ascending order and then by column 'value' in descending order
df_sorted = df.sort_values(by=['date', 'value'], ascending=[True, False])

print(df_sorted)

In this example, we first sort the DataFrame by ‘date’ in ascending order. When there are multiple rows with the same date (e.g., 2022-01-01), we then sort these rows by ‘value’ in descending order.

Sorting with Custom Sorting Keys

Sometimes, you might need to sort your data based on custom sorting keys. For example, if you have a column with datetime strings and you want to sort it as if the values were datetime objects.

Here’s an example where we create a DataFrame with two columns: ‘date’ and ‘value’. We want to sort this DataFrame by ‘date’ in ascending order (treating ‘date’ as datetime) and then by ‘value’ in descending order.

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({
    'date': ['2022-01-02', '2022-01-01', '2022-01-03'],
    'value': [10, 20, 30]
})

# Sort the DataFrame by column 'date' in ascending order (treating 'date' as datetime) and then by column 'value' in descending order
df_sorted = df.sort_values(by='date', key=pd.to_datetime).sort_values(by='value', ascending=False)

print(df_sorted)

In this example, we use the key parameter of the sort_values method to specify a custom sorting key for the ‘date’ column. We pass a function (pd.to_datetime) that converts the datetime strings to datetime objects.

Conclusion

Sorting DataFrames by multiple columns is a powerful feature in pandas that allows you to manipulate and analyze your data efficiently. By using the sort_values method, you can sort your data based on one or more columns, and even use custom sorting keys when needed.

Remember to always specify the ascending parameter when sorting by multiple columns to ensure the correct sorting order.