← Back to Blog

How to Filter Rows in Pandas by Regex

As a data scientist or software engineer, you may often need to filter rows in a Pandas DataFrame using regular expressions (regex) to find specific patterns in your data. In this article, we will explore how to filter rows in Pandas by regex, step-by-step.
By Saturn Cloud | | Miscellaneous | Updated:

As a data scientist or software engineer, you may often need to filter rows in a Pandas DataFrame using regular expressions (regex) to find specific patterns in your data. In this article, we will explore how to filter rows in Pandas by regex, step-by-step.

Table of Contents

What is Pandas?

Pandas is a popular open-source data analysis and manipulation library for Python. It provides fast, flexible, and easy-to-use data structures for working with data in Python. Pandas is widely used in data science, machine learning, and data analytics.

What is regex?

Regex, short for regular expression, is a sequence of characters that define a search pattern. It is a powerful tool for finding patterns in text data. Regex is commonly used in programming languages and text editors to perform search and replace operations.

Filtering Rows in Pandas by Regex

Pandas provides a powerful method called str.contains() to filter rows in a DataFrame using regex. The str.contains() method returns a Boolean Series that indicates whether each string in the DataFrame matches the regex pattern.

Here is the syntax for using str.contains() method:

df[df['column_name'].str.contains('regex_pattern')]

Let’s break down the above syntax:

  • df is the DataFrame you want to filter.
  • column_name is the name of the column you want to filter.
  • regex_pattern is the regular expression pattern you want to use for filtering.

Now, let’s see some examples of how to filter rows in Pandas by regex.

Example 1: Filter Rows by Exact String Match

Suppose you have a DataFrame with a column named ‘Name’ containing names of different employees. You want to filter rows that contain the exact string ‘John’. Here is how you can do it:

import pandas as pd

# create a sample dataframe
data = {'Name': ['John Smith', 'Jane Doe', 'John Doe', 'Sarah Johnson']}
df = pd.DataFrame(data)

# filter rows by exact string match
filtered_df = df[df['Name'].str.contains(r'\bJohn\b')]

# print the filtered dataframe
print(filtered_df)

Output:

         Name
0  John Smith
2    John Doe

In the above example, we used the regex pattern r'\bJohn\b' \b represents a word boundary, ensuring that ‘John’ is matched as a whole word.

Pros

  • Precise Matching: The method allows for precise matching of exact strings, useful when the goal is to filter rows that exactly match a specific pattern.

  • Simple Syntax: The syntax using the \b symbol is simple and easy to understand, making it accessible even for users with limited regex experience.

Cons

  • Limited Flexibility: This method is limited to exact string matches, which may not be suitable for scenarios requiring more flexible matching criteria.

Example 2: Filter Rows by Partial String Match

Suppose you have a DataFrame with a column named ‘Email’ containing email addresses of different users. You want to filter rows that contain the string ‘gmail’. Here is how you can do it:

import pandas as pd

# create a sample dataframe
data = {'Email': ['john.smith@gmail.com', 'jane.doe@yahoo.com', 'john.doe@gmail.com', 'sarah.johnson@hotmail.com']}
df = pd.DataFrame(data)

# filter rows by partial string match
filtered_df = df[df['Email'].str.contains('gmail')]

# print the filtered dataframe
print(filtered_df)

Output:

                  Email
0  john.smith@gmail.com
2    john.doe@gmail.com

In the above example, we used the regex pattern 'gmail' to match the partial string ‘gmail’. The str.contains() method returns True for any row that contains the string ‘gmail’.

Pros

  • Versatility: This method is versatile, allowing users to filter rows based on partial string matches, which is often useful when dealing with variations of a pattern.

  • Ease of Use: The syntax for partial string matching is straightforward, making it accessible for users who need to filter based on a substring.

Cons

  • Potential False Positives: Depending on the chosen partial match, there is a risk of capturing unintended rows that contain the specified substring, leading to potential false positives.

Example 3: Filter Rows by Multiple Patterns

Suppose you have a DataFrame with a column named ‘Comments’ containing comments from different users. You want to filter rows that contain either the string ‘good’ or ‘excellent’. Here is how you can do it:

import pandas as pd

# create a sample dataframe
data = {'Comments': ['The product is good', 'The service was excellent', 'The price is too high', 'The quality is good']}
df = pd.DataFrame(data)

# filter rows by multiple patterns
filtered_df = df[df['Comments'].str.contains('good|excellent')]

# print the filtered dataframe
print(filtered_df)

Output:

                    Comments
0        The product is good
1  The service was excellent
3        The quality is good

In the above example, we used the regex pattern 'good|excellent' to match either the string ‘good’ or ‘excellent’. The '|' symbol is used to match either pattern.

Pros

  • Increased Complexity: This method allows for more complex filtering by combining multiple patterns using the | symbol, providing increased flexibility.

  • Comprehensive Matching: Users can filter rows that match any of the specified patterns, offering a comprehensive approach to filtering diverse data.

Cons

  • Higher Learning Curve: The syntax for multiple patterns may be more challenging for users new to regex, contributing to a higher learning curve.

  • Potential Overhead: Depending on the complexity of the combined patterns, there may be a performance overhead, especially with large datasets.

Handling Potential Issues

In the process of filtering rows in a Pandas DataFrame using regular expressions (regex), it’s crucial to implement error-handling strategies to address possible issues. Let’s explore common scenarios and how to manage them:

  1. Missing Column: Specifying a column that does not exist in the DataFrame can result in a KeyError. Check the existence of the specified column before applying the regex operation and provide a clear error message if the column is not found.

  2. Invalid Regex Pattern: If the regex pattern provided is invalid, a re.error may occur during execution. Validate the regex pattern before applying it and handle any errors that may arise. Offer informative feedback about the issue with the pattern.

  3. Unexpected Errors: Unforeseen errors during the execution of the code may disrupt the filtering process. Implement a general exception handling mechanism to catch unexpected errors. This ensures that even if an unforeseen issue occurs, the user receives a helpful error message, facilitating debugging and troubleshooting.

Conclusion

Filtering rows in Pandas by regex can be very useful when working with text data. In this article, we explored how to filter rows in Pandas by regex, step-by-step. We learned how to use the str.contains() method to filter rows by exact string match, partial string match, and multiple patterns.

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Request a demo today to learn more.

Get a Technical Demo