如何为DataFrame批量计算每周目标向前覆盖天数？

数据说明 #

现有Pandas DataFrame df，数据如下：

Week     Product    Location    Demand  Target  
01/04/2024  A123    L999    109.011091  1680  
08/04/2024  A123    L999    107.085798  1680  
15/04/2024  A123    L999    108.982414  1680  
22/04/2024  A123    L999    107.059032  1680  
29/04/2024  A123    L999    108.956534  1680  
06/05/2024  A123    L999    107.034876  1680  
13/05/2024  A123    L999    108.933176  1680  
20/05/2024  A123    L999    107.013076  1680  
27/05/2024  A123    L999    108.912096  1680  
03/06/2024  A123    L999    106.993401  1680  
10/06/2024  A123    L999    108.893071  1680  
17/06/2024  A123    L999    106.975644  
24/06/2024  A123    L999    108.875901  
01/07/2024  A123    L999    566.636709  
08/07/2024  A123    L999    611.998169  
15/07/2024  A123    L999    582.081501  
22/07/2024  A123    L999    612.327427  

需求描述 #

需计算每周的目标向前覆盖天数——即当前Target库存能覆盖未来多少天的Demand。此前使用Excel公式：

=ROUNDUP((SUMPRODUCT(--(C5>=SUBTOTAL(9,OFFSET(C4:$U4,,,,COLUMN(C4:$U4)-COLUMN(C4)+1))))+ABS(LOOKUP(0,(SUBTOTAL(9,OFFSET(C4:$U4,,,,COLUMN(C4:$U4)-COLUMN(C4)+1))-C5-C4:$U4)/C4:$U4)))*7,6)

该公式能得到正确结果，但面对5000+产品、8个地点、13周的批量数据时效率极低，需用Pandas实现高效批量计算。

解决方案（Pandas代码） #

利用分组+向量化计算替代循环，大幅提升处理效率：

import pandas as pd
import numpy as np

# 预处理：将Target列缺失值转为NaN，避免参与计算
df['Target'] = df['Target'].where(df['Target'].notna())

# 定义分组计算函数
def calculate_days_cover(group):
    group = group.reset_index(drop=True)
    days_cover = []
    for idx, row in group.iterrows():
        target = row['Target']
        if pd.isna(target):
            days_cover.append(np.nan)
            continue
        # 获取当前周及之后的所有Demand
        demands = group.loc[idx:, 'Demand'].values
        # 计算累积需求
        cum_demand = np.cumsum(demands)
        # 找到首个累积需求超过Target的位置
        exceed_idx = np.argmax(cum_demand > target)
        if exceed_idx == 0:
            # 首周需求就超过Target，按比例计算天数
            ratio = target / demands[0]
            total_days = ratio * 7
        else:
            # 完整覆盖周数 + 剩余需求的比例天数
            full_weeks = exceed_idx
            remaining = target - cum_demand[exceed_idx-1]
            ratio = remaining / demands[exceed_idx]
            total_days = (full_weeks + ratio) * 7
        # 保留6位小数
        days_cover.append(round(total_days, 6))
    group['Days Cover'] = days_cover
    return group

# 按产品和地点分组应用计算
df_result = df.groupby(['Product', 'Location'], group_keys=False).apply(calculate_days_cover)

预期输出 #

Week     Product    Location    Demand  Target  Days Cover  
01/04/2024  A123    L999    109.011091  1680    94.400622  
08/04/2024  A123    L999    107.085798  1680    88.747301  
15/04/2024  A123    L999    108.982414  1680    83.070196  
22/04/2024  A123    L999    107.059032  1680    77.385648  
29/04/2024  A123    L999    108.956534  1680    71.610183  
06/05/2024  A123    L999    107.034876  1680    65.856421  
13/05/2024  A123    L999    108.933176  1680    60.08068  
20/05/2024  A123    L999    107.013076  1680    54.326652  
27/05/2024  A123    L999    108.912096  1680    48.550661  
03/06/2024  A123    L999    106.993401  1680    42.837323  
10/06/2024  A123    L999    108.893071  1680    37.124005  
17/06/2024  A123    L999    106.975644      NaN
24/06/2024  A123    L999    108.875901      NaN
01/07/2024  A123    L999    566.636709      NaN
08/07/2024  A123    L999    611.998169      NaN
15/07/2024  A123    L999    582.081501      NaN
22/07/2024  A123    L999    612.327427      NaN

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