6.2 Filtering and Sorting DataFrames

DataFrame Basics

Choose Library:

Pandas

Polars

Filtering is a fundamental aspect to working with DataFrames. It allows us to select specific rows or columns based on certain conditions. It allows us to see only the data that is relevant to our analysis. Not only that, but it can be done simply by limiting rows or in incredibly complex ways that allow us to slice and dice our data in a variety of ways.

Sorting, on the other hand, helps us organize our data in a meaningful way, either by index or by values in a specific column.

Filtering by Row

The simplest way to filter a DataFrame is by filtering the rows we want to see. This can be done in a variety of ways, but the most common is to use boolean indexing. Boolean indexing allows us to filter rows based on a condition. For example, we can filter a DataFrame to only show rows where a certain column is greater than a specific value.

Let’s start with a simple DataFrame:

import pandas as pd

data = {
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Eva'],
    'Age': [24, 30, 22, 35, 28],
    'City': ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Phoenix']
}
df = pd.DataFrame(data)
df

	Name	Age	City
0	Alice	24	New York
1	Bob	30	Los Angeles
2	Charlie	22	Chicago
3	David	35	Houston
4	Eva	28	Phoenix

Now let’s filter the DataFrame to only show rows where the Age is greater than 25. We can do this by using boolean indexing with the .loc[] method or the .iloc[] method.

.loc[]

# Filter rows where Age is greater than 25 using .loc[]
filtered_df_loc = df.loc[df['Age'] > 25]
filtered_df_loc

	Name	Age	City
1	Bob	30	Los Angeles
3	David	35	Houston
4	Eva	28	Phoenix

.iloc[]

With .iloc[], we rarely would use it for filterting because we need label-based conditions. However, we can filter it if we accept every row where the positional index of ‘Age’ is greater than 25.

# Filter rows where Age is greater than 25 using .iloc[]
filtered_df_iloc = df.iloc[:, 1] > 25
filtered_df_iloc

0    False
1     True
2    False
3     True
4     True
Name: Age, dtype: bool

.filter()

The .filter() method is another way to filter a DataFrame. It allows us to filter rows or columns based on labels For example, we can filter the DataFrame to only show the ‘Name’ and ‘City’ columns.

# Filter columns to only show 'Name' and 'City'
filtered_df_filter = df.filter(items=['Name', 'City'])
filtered_df_filter

	Name	City
0	Alice	New York
1	Bob	Los Angeles
2	Charlie	Chicago
3	David	Houston
4	Eva	Phoenix

Renaming vs in place

Before we move further, there are two ways to modify our DataFrame. We can either rename it or modify it in place. Effectively, both do the same thing, but they follow separate syntax.

Renaming creates a new DataFrame with the changes, while modifying in place changes the original DataFrame.

# Renaming
renamed_df = df.rename(columns={'Name': 'Full Name'})
renamed_df

	Full Name	Age	City
0	Alice	24	New York
1	Bob	30	Los Angeles
2	Charlie	22	Chicago
3	David	35	Houston
4	Eva	28	Phoenix

But we can also follow this same process by renaming the dataframe the same name, effectively overwriting it.

# Renaming
df = df.rename(columns={'Name': 'Full Name'})
df

	Full Name	Age	City
0	Alice	24	New York
1	Bob	30	Los Angeles
2	Charlie	22	Chicago
3	David	35	Houston
4	Eva	28	Phoenix

This an explicit overwrite, which is a common practice in data science.

# Modifying in place
df.rename(columns={'Name': 'Full Name'}, inplace=True)
df

	Full Name	Age	City
0	Alice	24	New York
1	Bob	30	Los Angeles
2	Charlie	22	Chicago
3	David	35	Houston
4	Eva	28	Phoenix

My preference is to overwrite the original DataFrame, as it is more explicit and easier to read.

Sorting DataFrames

Sorting is another fundamental aspect of working with DataFrames. It allows us to organize our data in a meaningful way, either by index or by values in a specific column.

.sort_index()

The .sort_index() method is used to sort a DataFrame by its index (row labels). By default, it sorts in ascending order.

# Sort by Index
sorted_df_index = df.sort_index()
sorted_df_index

	Full Name	Age	City
0	Alice	24	New York
1	Bob	30	Los Angeles
2	Charlie	22	Chicago
3	David	35	Houston
4	Eva	28	Phoenix

.sort_values()

The .sort_values() method is used to sort a DataFrame by the values in a specific column. By default, it sorts in ascending order.

# Sort by Values in the 'Age' column
sorted_df_values = df.sort_values(by='Age')
sorted_df_values

	Full Name	Age	City
2	Charlie	22	Chicago
0	Alice	24	New York
4	Eva	28	Phoenix
1	Bob	30	Los Angeles
3	David	35	Houston

we can also change the order to descending by setting the ascending parameter to False.

# Sort by Values in the 'Age' column in descending order
sorted_df_values_desc = df.sort_values(by='Age', ascending=False)
sorted_df_values_desc

	Full Name	Age	City
3	David	35	Houston
1	Bob	30	Los Angeles
4	Eva	28	Phoenix
0	Alice	24	New York
2	Charlie	22	Chicago

Filtering by Row

Filtering data frames in Polars is much more straight forward than in Pandas. Pandas has many ways that do similar things, but with different limitations that can be difficult to keep up with. In Polars, the syntax is simple. We use the .filter() method and pass the criteria as a parameter.