Pandas数据处理：将DataFrame指定列的0及特定后缀值替换为np.nan

Got it, let's break down these two requirements step by step with practical pandas code and explanations. First, make sure you've got the necessary libraries imported:

import pandas as pd
import numpy as np
import re

Step 1: Replace All 0s in s6.iloc[:,4:] with np.nan #

This is a straightforward task using pandas' built-in replace() method. We'll target the subset of your DataFrame starting from the 5th column (index 4) and swap any 0 values (whether numeric or string) with np.nan:

# Target columns 4 onwards and replace 0s with NaN
s6.iloc[:, 4:] = s6.iloc[:, 4:].replace(0, np.nan)
# If your 0s are stored as strings, use this instead:
# s6.iloc[:, 4:] = s6.iloc[:, 4:].replace('0', np.nan)

Step 2: Replace Cells Ending with _Q + Number (Where Number < 5) with np.nan #

For this, we'll use regular expressions to identify cells that match the pattern, then check if the trailing number is less than 5. Here's how to do it cleanly:

# First, isolate the column subset we're working with
subset = s6.iloc[:, 4:]

# Extract the trailing number from cells ending with _Q or _q (case-insensitive)
trailing_number = subset.astype(str).str.extract(r'_[Qq](\d)$', expand=False).astype(float)

# Create a mask: True for cells that match the pattern AND have a number <5
mask = ~trailing_number.isna() & (trailing_number < 5)

# Replace matching cells with NaN
s6.iloc[:, 4:] = subset.mask(mask)

Quick Breakdown of Step 2: #

1. We convert the subset to string first to safely handle any non-string values in your DataFrame.
2. The regex _[Qq](\d)$ looks for strings ending with _Q or _q followed by a single digit, and captures that digit.
3. We convert the captured digit to a float, then build a mask that only includes cells where we found a valid digit (not NaN) and that digit is less than 5.
4. The mask() method replaces all cells where the mask is True with np.nan, leaving other values untouched.

Testing this with your example:

Original columns (cols 4+): col5=110_q9, col6=111_q4, col7=110_q8, col8=111_q9
After processing: col5 stays as 110_q9, col6 becomes NaN, col7 stays as 110_q8, col8 stays as 111_q9

That's exactly the output you're looking for!

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