Filter pandas DataFrame by substring criteria

pandas

DataFrame

substring

filtering

Python

Filter pandas DataFrame by substring criteria

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

Introduction

To filter a pandas DataFrame by substring, use df[df['column'].str.contains('substring')]. This returns rows where the specified column contains the given substring. For case-insensitive matching, pass case=False. For exact pattern matching, use regex patterns. Other useful methods include str.startswith(), str.endswith(), str.match(), and str.fullmatch(). Always handle NaN values with na=False to avoid errors.

Basic Substring Filtering with str.contains

python

1import pandas as pd
2
3df = pd.DataFrame({
4    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
5    'job': ['Engineer', 'Doctor', 'Engineer', 'Teacher', 'Doctor']
6})
7
8# Filter rows where job contains 'Doctor'
9doctors = df[df['job'].str.contains('Doctor')]
10print(doctors)
11#     name     job
12# 1    Bob  Doctor
13# 4  Edward  Doctor
14
15# Case-insensitive search
16engineers = df[df['job'].str.contains('engineer', case=False)]
17print(engineers)
18#      name       job
19# 0   Alice  Engineer
20# 2  Charlie  Engineer

Handling NaN Values

str.contains() returns NaN for missing values, which causes a boolean indexing error. Use na=False to treat NaN as non-matching:

python

1df = pd.DataFrame({
2    'name': ['Alice', 'Bob', None, 'David'],
3    'city': ['New York', 'Boston', 'New Orleans', None]
4})
5
6# Without na=False — raises ValueError if NaN present
7# df[df['city'].str.contains('New')]  # Error if NaN exists
8
9# With na=False — NaN rows are excluded
10result = df[df['city'].str.contains('New', na=False)]
11print(result)
12#    name         city
13# 0  Alice     New York
14# 2   None  New Orleans

Regex Pattern Matching

str.contains() supports regular expressions by default:

python

1df = pd.DataFrame({
2    'email': ['[email protected]', '[email protected]', '[email protected]',
3              '[email protected]', '[email protected]']
4})
5
6# Match emails ending with gmail.com
7gmail_users = df[df['email'].str.contains(r'@gmail\.com$', regex=True)]
8print(gmail_users)
9#             email
10# 0  [email protected]
11
12# Match any .com email
13com_emails = df[df['email'].str.contains(r'\.com$', na=False)]
14print(com_emails)
15#                email
16# 0   [email protected]
17# 1    [email protected]
18# 4  [email protected]
19
20# Disable regex for literal string matching
21df[df['email'].str.contains('.com', regex=False)]  # Treats '.' as literal

Multiple Substring Matching

Filter by multiple substrings using regex OR (|):

python

1df = pd.DataFrame({
2    'fruit': ['apple', 'banana', 'cherry', 'date', 'elderberry', 'fig']
3})
4
5# Match rows containing 'apple' OR 'cherry' OR 'fig'
6result = df[df['fruit'].str.contains('apple|cherry|fig')]
7print(result)
8#    fruit
9# 0  apple
10# 2  cherry
11# 5     fig
12
13# Using isin() for exact matches (not substrings)
14exact = df[df['fruit'].isin(['apple', 'cherry', 'fig'])]

str.startswith and str.endswith

python

1df = pd.DataFrame({
2    'filename': ['report.pdf', 'data.csv', 'image.png', 'notes.pdf', 'backup.csv']
3})
4
5# Files starting with 'report'
6df[df['filename'].str.startswith('report')]
7#       filename
8# 0  report.pdf
9
10# Files ending with '.csv'
11csv_files = df[df['filename'].str.endswith('.csv')]
12print(csv_files)
13#      filename
14# 1   data.csv
15# 4  backup.csv
16
17# Multiple extensions
18pdf_or_csv = df[df['filename'].str.endswith(('.pdf', '.csv'))]

Negating the Filter (NOT Contains)

Use the ~ operator to invert the boolean mask:

python

1df = pd.DataFrame({
2    'status': ['active', 'inactive', 'active', 'pending', 'inactive']
3})
4
5# Rows that do NOT contain 'active'
6not_active = df[~df['status'].str.contains('active', na=False)]
7print(not_active)
8#    status
9# 3  pending

Filtering Across Multiple Columns

python

1df = pd.DataFrame({
2    'name': ['Alice Smith', 'Bob Jones', 'Charlie Brown'],
3    'bio': ['Engineer at Google', 'Doctor in Boston', 'Teacher in NYC']
4})
5
6# Search for 'Boston' in any string column
7mask = df.apply(lambda row: row.astype(str).str.contains('Boston').any(), axis=1)
8result = df[mask]
9print(result)
10#        name               bio
11# 1  Bob Jones  Doctor in Boston
12
13# Search specific columns
14cols_to_search = ['name', 'bio']
15mask = df[cols_to_search].apply(lambda col: col.str.contains('in', na=False)).any(axis=1)

Performance Comparison

python

1import pandas as pd
2import numpy as np
3
4# Create a large DataFrame
5n = 100_000
6df = pd.DataFrame({'text': [f'item_{i}_data' for i in range(n)]})
7
8# str.contains (regex enabled) — slower
9%timeit df[df['text'].str.contains('999')]
10
11# str.contains (regex disabled) — faster for literal strings
12%timeit df[df['text'].str.contains('999', regex=False)]
13
14# List comprehension — often fastest
15%timeit df[['999' in s for s in df['text']]]

For large DataFrames with simple literal substring matching, passing regex=False is significantly faster. Python list comprehensions can be even faster for simple cases.

Common Pitfalls

Not passing na=False when the column contains NaN: str.contains() returns NaN for missing values, which cannot be used as a boolean index. Always add na=False (or na=True to include NaN rows) to avoid ValueError.
Forgetting that str.contains uses regex by default: Special regex characters like ., *, +, (, ) are interpreted as patterns. To search for a literal period, use regex=False or escape with \.. For example, str.contains('file.txt') matches filetxt too.
Using str.contains when isin is more appropriate: str.contains('apple') matches partial substrings (e.g., "pineapple"). For exact value matching, use df['col'].isin(['apple']) instead.
Applying substring filters to non-string columns: If the column dtype is not object or string, str.contains() fails. Convert first with df['col'].astype(str).str.contains(...).
Chaining multiple str.contains calls instead of using regex OR: Writing df[df['col'].str.contains('a')] & df[df['col'].str.contains('b')] creates intermediate DataFrames. Use df['col'].str.contains('a|b') for a single efficient pass.

Summary

df[df['col'].str.contains('text', na=False)] is the standard substring filter
Pass case=False for case-insensitive matching
Use regex=False for literal string matching (faster and avoids regex escaping)
Use str.startswith() and str.endswith() for prefix/suffix matching
Negate with ~ operator to get rows that do NOT match
Always include na=False to handle NaN values safely