如何在Python中复刻Excel SUMIFS+EDATE逻辑为DataFrame添加多列成本数据

你的核心需求是基于销售数据的起始月份和变量，生成往后36个月的对应成本列，替代Excel里用SUMIFS结合EDATE生成多列的操作。下面是两种高效的实现方案：

方案一：先聚合再扩展合并（适合大数据量） #

这种方法通过先聚合成本数据，再扩展销售数据的月份偏移，最后转置成宽表，比多次pd.merge效率高得多：

步骤1：统一日期格式 #

首先确保日期列是datetime类型，方便后续月份计算：

import pandas as pd

# 转换销售和成本数据的日期列为datetime
sales['CONTRACT_ITEM_START_MTH'] = pd.to_datetime(sales['CONTRACT_ITEM_START_MTH'])
cogs['Start Date'] = pd.to_datetime(cogs['Start Date'])

步骤2：聚合成本数据 #

按变量VAR和月份分组，计算每个组合的总成本，避免重复计算：

# 按VAR和月末日期聚合成本，生成每个VAR每月的总成本
cogs_monthly = cogs.groupby(
    ['VAR', pd.Grouper(key='Start Date', freq='M')]
)['COST_COLUMN'].sum().reset_index()

# 重命名列，方便后续合并
cogs_monthly = cogs_monthly.rename(
    columns={'Start Date': 'Month', 'VAR': 'VAR1', 'COST_COLUMN': 'Total_Cost'}
)

步骤3：扩展销售数据的月份偏移 #

生成0到35个月的偏移，为每个销售条目创建36条对应不同月份的记录：

# 定义36个月的偏移量（0=起始月，35=第36个月）
month_offsets = range(36)

# 扩展销售数据，每个条目重复36次（对应每个偏移）
sales_expanded = sales.assign(Offset=pd.Series(month_offsets).repeat(len(sales))).reset_index(drop=True)

# 计算每个偏移对应的目标月末日期
sales_expanded['Target_Month'] = (
    sales_expanded['CONTRACT_ITEM_START_MTH'] + 
    pd.DateOffset(months=sales_expanded['Offset']) +
    pd.offsets.MonthEnd(0)  # 对齐到月末，和成本数据的日期格式统一
)

步骤4：合并并转置成宽表 #

合并成本数据后，将长表转成宽表，得到36列成本数据：

# 合并聚合后的成本数据
sales_expanded = pd.merge(
    sales_expanded, 
    cogs_monthly, 
    left_on=['VAR1', 'Target_Month'], 
    right_on=['VAR1', 'Month'], 
    how='left'
)

# 缺失的成本填充为0
sales_expanded['Total_Cost'] = sales_expanded['Total_Cost'].fillna(0)

# 转置成宽表，每个偏移对应一列
result = sales_expanded.pivot(
    index=sales.columns.tolist(), 
    columns='Offset', 
    values='Total_Cost'
).reset_index()

# 重命名列，比如Cost_0（起始月）、Cost_1（第2个月）...Cost_35（第36个月）
result.columns = [col if isinstance(col, str) else f'Cost_{col}' for col in result.columns]

方案二：字典映射+apply（适合小数据量） #

如果数据量不大，代码可以更简洁，直接通过字典快速查找对应成本：

# 先把聚合后的成本数据转成字典，键为(VAR1, Month)，值为总成本
cost_lookup = cogs_monthly.set_index(['VAR1', 'Month'])['Total_Cost'].to_dict()

# 定义函数，为每一行生成36个月的成本
def calculate_monthly_costs(row):
    start_month = row['CONTRACT_ITEM_START_MTH'] + pd.offsets.MonthEnd(0)
    costs = []
    for offset in month_offsets:
        target_month = start_month + pd.DateOffset(months=offset)
        # 查找对应成本，找不到则返回0
        costs.append(cost_lookup.get((row['VAR1'], target_month), 0))
    # 返回带列名的Series
    return pd.Series(costs, index=[f'Cost_{i}' for i in month_offsets])

# 生成成本列并合并到销售数据
cost_columns = sales.apply(calculate_monthly_costs, axis=1)
result = pd.concat([sales, cost_columns], axis=1)

关键说明 #

• 两种方案都先对成本数据做了按月聚合，和Excel的SUMIFS逻辑一致，确保每个月的成本是汇总值
• 日期统一用月末日期对齐，避免因日期格式不一致导致匹配失败
• 缺失的成本值填充为0，符合Excel中SUMIFS找不到匹配时返回0的逻辑

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