Pandas筛选Excel中YES/NO结果为空，求拆分保存数据方案

Hey there! I get it, it’s super frustrating when you can see the YES/NO records in your Excel file but your Pandas code keeps returning empty dataframes. Let’s break down what’s probably going wrong and fix it step by step.

Common Causes of Empty Filters #

First, let’s cover the most likely issues leading to this problem:

• Incomplete column name cleaning: Your code cuts off at df.columns = df.columns.str.repl...—if you didn’t finish cleaning column names, you might be filtering on a column that doesn’t exist (or has a typo).
• Hidden whitespace or casing mismatches: The YES/NO values in your Excel might have leading/trailing spaces (like YES instead of YES) or use different capitalization (like yes instead of YES). Pandas uses exact matches, so these tiny differences break the filter.
• Redundant data loading: You’ve got pd.read_excel(labels) twice in your code—this isn’t breaking things, but it’s redundant and could overwrite any changes between reads.

Step-by-Step Fix #

Let’s rewrite your code with checks and fixes to ensure it works:

1. Load and Inspect Your Data

Start by loading the Excel file and verifying column names and target values to catch mismatches early:

import pandas as pd

# Load your Excel file
labels = 'sample.xlsx'
df = pd.read_excel(labels)

# Print all column names to confirm the exact name of your YES/NO column
print("All column names:", df.columns.tolist())

# Replace 'Target_Column_Name' with the actual name from the print output
target_column = 'Target_Column_Name'

# Print unique values in the target column to see how YES/NO is formatted
print(f"Unique values in {target_column}:", df[target_column].unique())

2. Clean Column Names and Values

Based on your inspection, clean up the data to eliminate mismatches:

# Clean column names: remove spaces, special chars, and standardize casing
df.columns = df.columns.str.strip().str.replace(' ', '_').str.upper()

# Update the target column name to match the cleaned version
target_column = target_column.strip().replace(' ', '_').upper()

# Clean the target column values: remove whitespace and standardize to uppercase
df[target_column] = df[target_column].str.strip().str.upper()

3. Filter and Save the Results

Now you can safely filter the data and save it to separate files:

# Filter rows where the target column is YES
df_yes = df[df[target_column] == 'YES']

# Filter rows where the target column is NO
df_no = df[df[target_column] == 'NO']

# Save to Excel files (index=False removes the extra Pandas index column)
df_yes.to_excel('yes_records.xlsx', index=False)
df_no.to_excel('no_records.xlsx', index=False)

Quick Debugging Tip #

If you’re still getting empty results, try a fuzzy match to check for casing/whitespace issues:

# Check for any rows containing "yes" (case-insensitive, ignores NaN values)
print(df[df[target_column].str.contains('yes', case=False, na=False)])

This will show you all rows with YES/yes/Yes etc., so you can confirm the actual format of your values.

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