日期比较失效求助：筛选StateChangeDate区间返回结果为0

我尝试筛选WorkItemType为Test Case、State为Ready且StateChangeDate处于指定日期区间的数据，但以下两种实现方法均返回0条结果，请求协助排查解决。

方法1代码 #

date1 = "2023-02-08"
date2 = "2023-05-23"

newdate1 = time.strptime(date1, "%Y-%m-%d")
newdate2 = time.strptime(date2, "%Y-%m-%d")

df['StateChangeDate'] = pd.to_datetime(df['StateChangeDate'], format='%Y-%m-%d')
df['identifier'] = df.apply(lambda row: 1 if ('Test Case' in row['WorkItemType']) and ('Ready' in row['State'])  and (newdate1 <= row['StateChangeDate'] <= newdate2) else 0, axis=1)

filtered_df = df[df['identifier'] != 0]
filtered_df = filtered_df.reset_index(drop=True)
y1.append(filtered_df.shape[0])
del filtered_df['identifier']

方法2代码 #

df['identifier'] = df.apply(lambda row: 1 if ('Test Case' in row['WorkItemType']) and ('Ready' in row['State'])  and ('2023-03-22' <= row['StateChangeDate'] <= '2023-04-04') else 0, axis=1)
filtered_df = df[df['identifier'] != 0]
filtered_df = filtered_df.reset_index(drop=True)
y1.append(filtered_df.shape[0])
del filtered_df['identifier']

方法1的核心问题 #

你将StateChangeDate转换为pandas的datetime类型，但用来比较的newdate1、newdate2是Python标准库的time.struct_time类型，两种不同的日期类型无法直接比较，导致条件永远不成立，最终筛选结果为空。

修复后的代码 #

import pandas as pd

date1 = "2023-02-08"
date2 = "2023-05-23"

# 转换为pandas兼容的Timestamp类型，和StateChangeDate类型统一
newdate1 = pd.to_datetime(date1)
newdate2 = pd.to_datetime(date2)

df['StateChangeDate'] = pd.to_datetime(df['StateChangeDate'], format='%Y-%m-%d')
# 使用pandas向量化筛选，比apply效率更高且更可靠
filtered_df = df[
    (df['WorkItemType'] == 'Test Case') & 
    (df['State'] == 'Ready') & 
    (df['StateChangeDate'].between(newdate1, newdate2))
]
filtered_df = filtered_df.reset_index(drop=True)
y1.append(filtered_df.shape[0])

方法2的核心问题 #

1. StateChangeDate是datetime类型，直接与字符串日期比较时，若StateChangeDate包含时分秒信息（即使显示仅为年月日），字符串比较逻辑会出错；
2. 使用'Test Case' in row['WorkItemType']和'Ready' in row['State']是模糊匹配，可能匹配到非预期值（比如Test Case V2），若需求是精确匹配，应使用==。

修复后的代码 #

import pandas as pd

start_date = pd.to_datetime('2023-03-22')
end_date = pd.to_datetime('2023-04-04')

df['StateChangeDate'] = pd.to_datetime(df['StateChangeDate'], format='%Y-%m-%d')
filtered_df = df[
    (df['WorkItemType'] == 'Test Case') & 
    (df['State'] == 'Ready') & 
    (df['StateChangeDate'].between(start_date, end_date))
]
filtered_df = filtered_df.reset_index(drop=True)
y1.append(filtered_df.shape[0])

额外排查建议 #

• 先检查原始数据：执行print(df[['WorkItemType', 'State', 'StateChangeDate']].sample(10))，确认字段值是否符合预期（比如WorkItemType是否有大小写差异、State是否为Ready而非ready/READY、StateChangeDate格式是否正确）；
• 分步验证筛选条件：先单独筛选WorkItemType == 'Test Case'查看结果，再叠加State == 'Ready'，最后加入日期条件，逐步定位哪一步导致结果为空。

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