如何基于现有Pandas DataFrame的日期条件筛选求和生成新DataFrame

实现步骤与代码 #

首先需确保所有日期列已转换为datetime类型，再按以下步骤实现需求：

步骤1：生成结果表基础结构

提取原表Sched Week列的去重值，排序后作为结果表的基准列：

import pandas as pd

# 转换日期列格式（若你的数据已完成格式转换可跳过该步）
df["Sched Week"] = pd.to_datetime(df["Sched Week"])
df["Ship Week"] = pd.to_datetime(df["Ship Week"])
df["Ready Week"] = pd.to_datetime(df["Ready Week"])

# 生成结果表基础结构
df_result = pd.DataFrame({
    "Sched Week": df["Sched Week"].unique()
}).sort_values("Sched Week", ignore_index=True)

步骤2：按条件求和计算foo列

两种实现方案可按需选择：

• 方案1：逻辑直观，适合数据量小于10万的场景

def sum_matched_vals(current_sched):
    match_mask = (
        (df["Sched Week"] <= current_sched) &
        (df["Ship Week"] > current_sched) &
        (df["Ready Week"] <= current_sched)
    )
    return df.loc[match_mask, "vals"].sum()

df_result["foo"] = df_result["Sched Week"].apply(sum_matched_vals)

• 方案2：广播运算，速度更快，适合百万级以上大表

import numpy as np

# 提取数组做广播匹配
sched_col = df["Sched Week"].values[:, None]
ship_col = df["Ship Week"].values[:, None]
ready_col = df["Ready Week"].values[:, None]
target_scheds = df_result["Sched Week"].values[None, :]
vals_col = df["vals"].values[:, None]

# 批量匹配后求和
match_mask = (sched_col <= target_scheds) & (ship_col > target_scheds) & (ready_col <= target_scheds)
df_result["foo"] = (vals_col * match_mask).sum(axis=0)

用你提供的示例数据运行后，输出结果与描述逻辑完全匹配，第一个2021-01-04对应的foo值为20，符合预期。

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