基于历史行条件过滤Pandas DataFrame预测数据

问题背景 #

现有一个Pandas DataFrame，存储某仓库单个商品的每日预测数据，每个日期对应两条预测记录：分别为提前1周和2周生成的预测（对应FCST_CREATE_DATE字段），LOAD_ARRIVAL_DATE字段标记当日是否有未来到货订单。

过滤规则 #

需要按以下规则过滤数据，仅保留订单下达时可用的最新预测：

• 忽略DAILY_DATE早于第一个LOAD_ARRIVAL_DATE的行；
• 在两个LOAD_ARRIVAL_DATE的区间内，仅保留订单创建日期之前生成的最新预测；
• 该规则需延续至最后一个订单之后的日期。

原始数据代码 #

import pandas as pd

data = {
    'ITEM': [1]*22,
    'WAREHOUSE': [1000]*22,
    'DAILY_DATE': ['9/20/2024', '9/20/2024', '9/21/2024', '9/21/2024', '9/22/2024', 
                   '9/22/2024', '9/23/2024', '9/23/2024', '9/24/2024', '9/24/2024', 
                   '9/25/2024', '9/25/2024', '9/26/2024', '9/26/2024', '9/27/2024', 
                   '9/27/2024', '9/28/2024', '9/28/2024', '9/29/2024', '9/29/2024', '9/30/2024', 
                   '9/30/2024'],
    'LOAD_ARRIVAL_DATE': ['9/26/2024', '9/26/2024', '', '', '', '', '9/29/2024', '9/29/2024', 
                        '9/30/2024', '9/30/2024', '', '', '', '', '', '', '', '', '', '', '', ''],
    'FCST_CREATE_DATE': ['9/14/2024', '9/7/2024', '9/14/2024', '9/7/2024', '9/21/2024', 
                                '9/14/2024', '9/21/2024', '9/14/2024', '9/21/2024', '9/14/2024', 
                                '9/21/2024', '9/14/2024', '9/21/2024', '9/14/2024', '9/21/2024', 
                                '9/14/2024', '9/21/2024', '9/14/2024', '9/21/2024','9/28/2024', '9/28/2024', 
                                '9/21/2024'],
    'FCST_QTY': [17.06, 10.4624, 12.1876, 16.2801, 8.8821, 8.3799, 11.1043, 10.7751, 
                              8.8821, 5.9846, 5.5555, 8.3799, 11.1043, 16.7597, 11.1043, 9.5809, 
                              11.1043, 8.3799, 130.02, 157.2252, 48.6241, 235.9502]
}

df = pd.DataFrame(data)

解决方案 #

步骤说明 #

1. 日期类型转换：将所有日期字段转为datetime类型，便于后续比较计算；
2. 提取关键到货节点：筛选LOAD_ARRIVAL_DATE非空值并排序，得到划分区间的时间节点；
3. 过滤前置无效数据：直接剔除DAILY_DATE早于第一个到货日期的记录；
4. 划分日期区间：为每个DAILY_DATE匹配对应区间的上限（最近的下一个到货日期，最后区间用极大值兜底）；
5. 筛选最新有效预测：在每个区间内保留FCST_CREATE_DATE早于区间上限的记录，再按日期分组取最新的预测结果。

实现代码 #

# 1. 转换日期字段为datetime类型
df['DAILY_DATE'] = pd.to_datetime(df['DAILY_DATE'])
df['FCST_CREATE_DATE'] = pd.to_datetime(df['FCST_CREATE_DATE'])
df['LOAD_ARRIVAL_DATE'] = pd.to_datetime(df['LOAD_ARRIVAL_DATE'], errors='coerce')

# 2. 获取去重排序后的到货日期
arrival_dates = df['LOAD_ARRIVAL_DATE'].dropna().unique()
arrival_dates.sort()
first_arrival = arrival_dates[0]

# 3. 过滤早于第一个到货日期的记录
df_filtered = df[df['DAILY_DATE'] >= first_arrival].copy()

# 4. 为每个日期分配区间上限
extended_arrivals = list(arrival_dates) + [pd.Timestamp.max]
df_filtered['interval_upper'] = pd.cut(
    df_filtered['DAILY_DATE'],
    bins=[pd.Timestamp.min] + extended_arrivals,
    labels=extended_arrivals,
    include_lowest=True
)

# 5. 筛选有效预测并取每个日期的最新记录
result = df_filtered[df_filtered['FCST_CREATE_DATE'] < df_filtered['interval_upper']]\
    .sort_values(['DAILY_DATE', 'FCST_CREATE_DATE'], ascending=[True, False])\
    .groupby('DAILY_DATE').first().reset_index()

# 查看结果
print(result[['DAILY_DATE', 'FCST_CREATE_DATE', 'FCST_QTY', 'LOAD_ARRIVAL_DATE']])

结果说明 #

最终的result DataFrame将严格符合规则：仅保留每个日期下，对应到货区间内可用的最新预测数据，覆盖从第一个到货日到最后一个订单之后的所有日期。

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