Python中按条件创建子集并计算债务延迟区间求和问题

实现步骤（基于Pandas） #

先构建示例DataFrame，再分三步完成需求：

1. 筛选有效ID：保留每个Name下，DurationOfDelay至少包含两个不同值的ID
2. 区间Debt提取：对每个有效ID，判断其覆盖的Delay区间，提取对应端点的Debt值
3. 按Name聚合求和：对每个Name，汇总三个区间的Debt总和

完整代码 #

import pandas as pd

# 构建示例DataFrame
data = [
    ["A", "ID1", 10, 15, 1],
    ["A", "ID1", 15, 30, 1],
    ["A", "ID2", 20, 60, 2],
    ["A", "ID2", 40, 60, 3],
    ["A", "ID3", 20, 15, 3],
    ["A", "ID3", 20, 60, 3],
    ["B", "ID4", 15, 30, 4],
    ["B", "ID4", 30, 60, 4],
    ["B", "ID5", 35, 40, 3],
    ["B", "ID6", 35, 0, 2],
    ["B", "ID7", 80, 30, 3],
    ["B", "ID7", 35, 60, 2]
]
df = pd.DataFrame(data, columns=["Name", "ID", "Debt", "DurationOfDelay", "CD"])

# 步骤1：筛选出每个(Name, ID)组中Delay至少有两个不同值的ID
valid_ids = df.groupby(["Name", "ID"])["DurationOfDelay"].nunique().reset_index()
valid_ids = valid_ids[valid_ids["DurationOfDelay"] >= 2][["Name", "ID"]]
filtered_df = df.merge(valid_ids, on=["Name", "ID"], how="inner")

# 步骤2：定义区间判断与Debt提取逻辑
def calculate_debt_groups(group):
    delay_set = set(group["DurationOfDelay"])
    # 15-30区间：ID同时包含15和30，取Delay=15的Debt和
    debt_15_30 = group[group["DurationOfDelay"] == 15]["Debt"].sum() if {15, 30}.issubset(delay_set) else 0
    # 15-60区间：ID同时包含15和60，取Delay=15的Debt和
    debt_15_60 = group[group["DurationOfDelay"] == 15]["Debt"].sum() if {15, 60}.issubset(delay_set) else 0
    # 30-60区间：ID同时包含30和60，取Delay=30的Debt和
    debt_30_60 = group[group["DurationOfDelay"] == 30]["Debt"].sum() if {30, 60}.issubset(delay_set) else 0
    return pd.Series([debt_15_30, debt_15_60, debt_30_60], 
                     index=["Debt_Delay_15to_30", "Debt_Delay_15to_60", "Debt_Delay_30to_60"])

# 步骤3：按Name聚合计算最终结果
result = filtered_df.groupby("Name").apply(calculate_debt_groups).reset_index()
result = result.fillna(0).astype(int)

print(result)

输出结果 #

Name  Debt_Delay_15to_30  Debt_Delay_15to_60  Debt_Delay_30to_60
0    A                   10                  20                   0
1    B                    0                   0                  95

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