使用Python Pandas筛选CSV时生成空DataFrame的解决方法

我编写了一段Python Pandas代码，可将文件存入指定输出文件夹，但生成的所有文件均为空。打印filter1和filter2后发现二者均为Empty DataFrame，输出信息如下：

Empty DataFrame
Columns: [Type Of Offer, Print Code, Offer Category, English Title, Discount Amount, Discount Currency, Offer Region, Offer Brand, Start Date, End Date, SKU, Description, Stock Class Code]
Index: []

请问如何确保筛选出的内容正常显示？我使用的是Jupyter Notebook，代码如下：

import pandas as pd
import datetime
import os


df = pd.read_csv(r"C:\Path_to_file.csv", encoding= 'unicode_escape')
output_folder = r"C:\Output_folder"

filter1 = df.loc[(df['Type Of Offer'].str.strip()=='BIG') & (df['Offer Region'].str.strip()=='United States') & (df['Offer Brand'].str.strip()=='PLACEHOLDER') & 
   (df['Start Date'].str.strip()=='8/1/2023') & (df['End Date'].str.strip()=='8/1/2023'), ['Type Of Offer', 'Print Code', 'Offer Category', 'English Title', 'Discount Amount', 'Discount Currency', 'Offer Region', 'Offer Brand', 'Start Date', 'End Date', 'SKU', 'Description', 'Stock Class Code']]
                 
filter2 = df.loc[(df['Type Of Offer'].str.strip()=='SMALL') & (df['Offer Region'].str.strip()=='United States') & (df['Offer Brand'].str.strip()=='PLACEHOLDER2') & 
   (df['Start Date'].str.strip()=='8/1/2023') & (df['End Date'].str.strip()=='8/1/2023'), ['Type Of Offer', 'Print Code', 'Offer Category', 'English Title', 'Discount Amount', 'Discount Currency', 'Offer Region', 'Offer Brand', 'Start Date', 'End Date', 'SKU', 'Description', 'Stock Class Code']]

offer1_df = df[filter1]
offer2_df = df[filter2]

offer1_file = os.path.join(output_folder, 'offer1.csv')
offer2_file = os.path.join(output_folder, 'offer2.csv')

if os.path.exists(offer1_file):
    os.remove(offer1_file)

if os.path.exists(offer2_file):
    os.remove(offer2_file)

offer1_df.to_csv(offer1_file, index=False)
offer2_file.to_csv(offer2_file, index=False)

1. 修正筛选逻辑的错误用法 #

你当前的filter1和filter2已经是通过loc筛选得到的DataFrame，但后续用df[filter1]索引是错误的——DataFrame不能直接作为布尔索引使用。正确做法是：

• 先单独生成布尔筛选条件，再用条件筛选数据；或者直接将filter1、filter2作为最终的筛选结果，无需再通过df二次索引。

修改示例：

# 先生成布尔条件
condition1 = (df['Type Of Offer'].str.strip()=='BIG') & \
             (df['Offer Region'].str.strip()=='United States') & \
             (df['Offer Brand'].str.strip()=='PLACEHOLDER') & \
             (df['Start Date'].str.strip()=='8/1/2023') & \
             (df['End Date'].str.strip()=='8/1/2023')

condition2 = (df['Type Of Offer'].str.strip()=='SMALL') & \
             (df['Offer Region'].str.strip()=='United States') & \
             (df['Offer Brand'].str.strip()=='PLACEHOLDER2') & \
             (df['Start Date'].str.strip()=='8/1/2023') & \
             (df['End Date'].str.strip()=='8/1/2023')

# 用条件筛选目标列
columns_to_keep = ['Type Of Offer', 'Print Code', 'Offer Category', 'English Title', 'Discount Amount', 'Discount Currency', 'Offer Region', 'Offer Brand', 'Start Date', 'End Date', 'SKU', 'Description', 'Stock Class Code']
offer1_df = df.loc[condition1, columns_to_keep]
offer2_df = df.loc[condition2, columns_to_keep]

2. 排查筛选条件不匹配的核心原因 #

如果修正后仍得到空DataFrame，说明你的筛选条件与数据实际值不匹配，按以下步骤排查：

• 统一字符串大小写：数据中的值可能存在大小写差异，比如Type Of Offer实际是big而非BIG，可以用str.lower()统一后再比较：

  condition1 = (df['Type Of Offer'].str.strip().str.lower()=='big') & ...
  

• 标准化日期格式：数据中的日期可能不是8/1/2023格式（比如2023-08-01或08/01/2023），先将日期列转为datetime类型再筛选：

  df['Start Date'] = pd.to_datetime(df['Start Date'], errors='coerce')
  df['End Date'] = pd.to_datetime(df['End Date'], errors='coerce')
  # 用datetime对象匹配
  target_date = pd.to_datetime('2023-08-01')
  condition1 = ... & (df['Start Date'] == target_date) & (df['End Date'] == target_date)
  

• 检查实际数据值：即使调用了str.strip()，数据仍可能包含不可见特殊字符。打印列的唯一值确认实际内容：

  print(df['Type Of Offer'].unique())
  print(df['Offer Region'].unique())
  print(df['Offer Brand'].unique())
  print(df['Start Date'].unique())
  

  根据输出调整筛选条件，确保与实际值完全匹配。

3. 修正文件保存的笔误 #

代码最后一行offer2_file.to_csv(offer2_file, index=False)是变量名错误，应改为offer2_df.to_csv(offer2_file, index=False)，否则会导致报错或保存无效内容。

完整修正后的代码 #

import pandas as pd
import os

df = pd.read_csv(r"C:\Path_to_file.csv", encoding='unicode_escape')
output_folder = r"C:\Output_folder"

# 标准化日期列格式
df['Start Date'] = pd.to_datetime(df['Start Date'], errors='coerce')
df['End Date'] = pd.to_datetime(df['End Date'], errors='coerce')
target_date = pd.to_datetime('2023-08-01')

# 生成大小写不敏感的筛选条件
condition1 = (df['Type Of Offer'].str.strip().str.lower() == 'big') & \
             (df['Offer Region'].str.strip().str.lower() == 'united states') & \
             (df['Offer Brand'].str.strip().str.lower() == 'placeholder') & \
             (df['Start Date'] == target_date) & \
             (df['End Date'] == target_date)

condition2 = (df['Type Of Offer'].str.strip().str.lower() == 'small') & \
             (df['Offer Region'].str.strip().str.lower() == 'united states') & \
             (df['Offer Brand'].str.strip().str.lower() == 'placeholder2') & \
             (df['Start Date'] == target_date) & \
             (df['End Date'] == target_date)

# 筛选目标列数据
columns_to_keep = ['Type Of Offer', 'Print Code', 'Offer Category', 'English Title', 'Discount Amount', 'Discount Currency', 'Offer Region', 'Offer Brand', 'Start Date', 'End Date', 'SKU', 'Description', 'Stock Class Code']
offer1_df = df.loc[condition1, columns_to_keep]
offer2_df = df.loc[condition2, columns_to_keep]

# 保存文件
offer1_file = os.path.join(output_folder, 'offer1.csv')
offer2_file = os.path.join(output_folder, 'offer2.csv')

if os.path.exists(offer1_file):
    os.remove(offer1_file)
if os.path.exists(offer2_file):
    os.remove(offer2_file)

offer1_df.to_csv(offer1_file, index=False)
offer2_df.to_csv(offer2_file, index=False)

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