如何用指定子串替换Pandas DataFrame列字符串值并过滤数据

Hey there! Let's work through how to solve this problem in Pandas—replacing a column's string values with specific substrings (and dropping rows that don't match any of those substrings) in Python 3.

First, let's start with a sample DataFrame to demonstrate the workflow:

import pandas as pd

# Sample data matching your example structure
data = {
    'A': ['Ottawa ON and some extra text', 'Toronto ON is great', 'Vancouver BC rules', 'Montreal QC', 'Random text with no target substring'],
    'B': [1, 2, 3, 4, 5],
    'C': ['x', 'y', 'z', 'w', 'v']
}
df = pd.DataFrame(data)

Let's say our target substrings are province codes like ['ON', 'BC', 'QC']. Here's how to handle the replacement and filtering:

Step 1: Filter rows that contain any target substring #

We'll use str.contains() with a regex pattern that matches any of our target substrings. Adding na=False ensures we handle missing values cleanly:

target_substrings = ['ON', 'BC', 'QC']
# Build a regex pattern: "ON|BC|QC"
pattern = '|'.join(target_substrings)

# Keep only rows where column 'A' has at least one matching substring
filtered_df = df[df['A'].str.contains(pattern, na=False)].copy()

Note: The copy() method avoids the SettingWithCopyWarning when we modify the filtered DataFrame later.

Step 2: Replace the column value with the matching substring #

Use str.extract() to pull out the first matching substring from the column. We wrap each substring in parentheses to create capture groups for extraction:

# Update the pattern to capture matches
capture_pattern = f'({pattern})'

# Replace column 'A' with the captured substring
filtered_df['A'] = filtered_df['A'].str.extract(capture_pattern)

Full Combined Code #

import pandas as pd

# Sample DataFrame
data = {
    'A': ['Ottawa ON and some extra text', 'Toronto ON is great', 'Vancouver BC rules', 'Montreal QC', 'Random text with no target substring'],
    'B': [1, 2, 3, 4, 5],
    'C': ['x', 'y', 'z', 'w', 'v']
}
df = pd.DataFrame(data)

# Target substrings to match
target_substrings = ['ON', 'BC', 'QC']
pattern = '|'.join(target_substrings)
capture_pattern = f'({pattern})'

# Filter rows and replace column values
result_df = df[df['A'].str.contains(pattern, na=False)].copy()
result_df['A'] = result_df['A'].str.extract(capture_pattern)

print(result_df)

Output #

A  B  C
0  ON  1  x
1  ON  2  y
2  BC  3  z
3  QC  4  w

Extra Tips #

• If you want to match whole words only (e.g., avoid matching "ON" in "ONe"), add word boundaries to the pattern:

  pattern = '|'.join([f'\\b{sub}\\b' for sub in target_substrings])
  

• If a row contains multiple target substrings, str.extract() will grab the first match. To handle multiple matches, use str.findall() and adjust the logic (e.g., join matches into a single string).

内容的提问来源于stack exchange，提问作者Gambit