Filtering and Sorting Data

1. The Core of Filtering: Boolean Indexing

Before we can filter, we need to understand the mechanism Pandas uses: boolean indexing. The idea is simple: you create a Series of True and False values that is the same length as your DataFrame. This is often called a "boolean mask". When you apply this mask to your DataFrame, Pandas returns only the rows where the mask's value is True.

Let's see this foundational concept in action.

As the video explains, a condition like df['column'] == 'value' produces a boolean mask. Now, how do we use that mask to actually filter the DataFrame?

You can pass the mask directly inside square brackets (e.g., df[mask]), but the recommended and more powerful way is to use the .loc indexer, which you're already familiar with from a previous lesson.

df.loc[mask]

Using .loc is generally preferred because it's more explicit and avoids potential confusion. It also allows you to select specific columns for your filtered rows at the same time: df.loc[mask, ['column1', 'column2']].

The next video segment demonstrates exactly this.

2. Combining and Negating Conditions

Real-world analysis rarely involves a single condition. You'll often need to find data that meets multiple criteria (e.g., "sales greater than 1000 and in the 'Electronics' category") or one of several criteria (e.g., "country is 'USA' or 'Canada'").

Pandas uses bitwise operators for this, which might look a little different from standard Python's and and or.

• & for AND (select rows where both conditions are True)
• | for OR (select rows where either condition is True)
• ~ for NOT (select rows where the condition is False; it inverts the mask)

Crucially, you must wrap each individual condition in parentheses () when combining them. This is a common source of errors for beginners.

df.loc[(condition1) & (condition2)]

Let's watch how to implement this.

For a supplementary text-based explanation, you can refer to the "Boolean Indexing" section of this article.

3. More Convenient Filtering Methods

While & and | are powerful, Pandas provides some convenient methods for common filtering scenarios.

The .isin() Method

Instead of chaining multiple | conditions like df.loc[(df['Country'] == 'USA') | (df['Country'] == 'India') | ...], you can use the .isin() method to check if a column's value is present in a list of allowed values.

countries_to_find = ['United States', 'India', 'United Kingdom', 'Germany', 'Canada']
filt = df['Country'].isin(countries_to_find)
df.loc[filt]

The .str.contains() Method

For string columns, you often need to find rows containing a certain substring. The .str.contains() method is perfect for this. It's particularly useful for "messy" text data.

# Find all rows where the 'LanguagesWorkedWith' column contains the word 'Python'
filt = df['LanguageWorkedWith'].str.contains('Python')
df.loc[filt]

Note the .str accessor before .contains(). This is how you access string-processing methods for a Series in Pandas.

Let's watch a practical demonstration of both of these methods on a large, real-world dataset.

import pandas as pd
data = {'product_name': ['Laptop', 'Keyboard', 'Monitor', 'Mouse', 'Webcam', 'Docking Station'],
        'category': ['Electronics', 'Electronics', 'Electronics', 'Electronics', 'Peripherals', 'Peripherals'],
        'price': [1200, 75, 300, 25, 50, 150],
        'stock': [8, 35, 12, 50, 20, 5]}
products_df = pd.DataFrame(data)

electronics_under_100 = products_df.loc[(products_df['category'] == 'Electronics') & (products_df['price'] < 100)]
print(electronics_under_100)

  product_name     category  price  stock
1     Keyboard  Electronics     75     35
3        Mouse  Electronics     25     50

4. Sorting Data by Column Values

Once you've filtered your data, you'll often want to sort it to make it easier to interpret. For example, you might want to see the most expensive products or the oldest records first. Pandas makes this straightforward with the .sort_values() method.

You can sort by a single column or by multiple columns in a specific order.

As you saw in the video, the key parameters are:

• by: A string for a single column df.sort_values(by='price') or a list of strings for multiple columns df.sort_values(by=['category', 'price']).
• ascending: A single boolean (True or False) or a list of booleans corresponding to the columns in by. For example, ascending=[True, False] would sort the first column A-Z and the second column Z-A.

5. An Important Final Step: Resetting the Index

When you filter a DataFrame, the original index values are kept. This can be confusing. Look at the index of the electronics_under_100 DataFrame from our exercise above: it's 1 and 3, not 0 and 1. This can cause problems in later processing steps.

It is good practice to reset the index after filtering to create a clean, new 0, 1, 2, ... index. You can do this with the .reset_index() method.

So, a complete filtering and cleaning operation often looks like this:

# Filter for electronics under $100
filtered_df = products_df.loc[(products_df['category'] == 'Electronics') & (products_df['price'] < 100)]

# Sort by price, descending
sorted_df = filtered_df.sort_values(by='price', ascending=False)

# Reset the index
final_df = sorted_df.reset_index(drop=True)

print(final_df)

This can also be chained together into a single, elegant line of code:

final_df = (products_df
            .loc[(products_df['category'] == 'Electronics') & (products_df['price'] < 100)]
            .sort_values(by='price', ascending=False)
            .reset_index(drop=True)
           )

Conclusion

You now have two of the most fundamental data manipulation tools in your Pandas toolkit. Being able to effectively filter and sort your data is the first step toward uncovering insights and preparing your dataset for machine learning.

Key Takeaways:

• Filtering in Pandas works by creating a boolean mask (a Series of True/False values).
• Apply masks using .loc for clarity and power: df.loc[mask].
• Combine conditions using & (AND) and | (OR), remembering to wrap each condition in parentheses.
• Use the ~ operator to negate a condition.
• Leverage convenience methods like .isin() for multiple OR conditions and .str.contains() for substring searches.
• Use .sort_values() to reorder your DataFrame by one or more columns, controlling the direction with ascending.
• Always consider using .reset_index(drop=True) after filtering to create a clean new index.

Preview of the Next Lesson:
Filtering and sorting help you select and view the data you want. But what if you need to change the data itself? In our next lesson, we will explore how to transform data by applying functions and creating new columns. This will allow you to clean data, calculate new metrics, and engineer features—essential steps on the path to building machine learning models.