如何在Pandas中按规则计算月度结转累加值？

问题背景 #

现有如下pandas DataFrame：

date        time    value
2021-08     0.0     22.50
2021-08     5.0     6600.00
2021-09     0.0     1057.62
2021-09     1.0     646.35
2021-09     2.0     311.76
2021-09     3.0     3982.50
2021-09     4.0     900.00
2021-09     7.0     546.00
2021-09     9.0     1471.50
2021-09     11.0    1535.16

其中time列表示对应value从date起始月份开始的持续支付月数：

• 例如第二行的6600.00从2021-08开始支付，持续到2022-02，因此2021-09的总value需要包含该6600.00。

需要计算每个月度的累计结转值，预期结果示例：

leased  value
2021-08 22.50 + 6600
2021-09 6600 + 1057.62 + 646.35 + 311.76 + 3982.50 + 900.00 + 546.00 + 1471.50 + 1535.16
2021-10 6600 + 646.35 + 311.76 + 3982.50 + 900.00 + 546.00 + 1471.50 + 1535.16
2021-11 6600 + 3982.50 + 900.00 + 546.00 + 1471.50 + 1535.16
...

用户已尝试创建目标DataFrame，但不确定如何填充数据：

new_df = pd.DataFrame(pd.date_range(start='2021-08', end=datetime.datetime.now(), freq='M'), columns=['value'])
new_df['commission'] = 0

解决方案 #

可以通过以下步骤实现需求：

1. 预处理原始数据 #

先将date列转为日期类型，计算每个value的支付起始和结束月份：

import pandas as pd
import datetime

# 加载原始数据（若已存在可跳过此步）
df = pd.DataFrame({
    'date': ['2021-08', '2021-08', '2021-09', '2021-09', '2021-09', '2021-09', '2021-09', '2021-09', '2021-09', '2021-09'],
    'time': [0.0, 5.0, 0.0, 1.0, 2.0, 3.0, 4.0, 7.0, 9.0, 11.0],
    'value': [22.50, 6600.00, 1057.62, 646.35, 311.76, 3982.50, 900.00, 546.00, 1471.50, 1535.16]
})

# 转换date为月末日期格式
df['date'] = pd.to_datetime(df['date']) + pd.offsets.MonthEnd(0)
# 计算支付结束月份：起始月 + time个月（time=0表示仅当月有效）
df['end_date'] = df['date'] + pd.to_timedelta(df['time'].astype(int), unit='M') + pd.offsets.MonthEnd(0)

2. 生成目标月度序列 #

创建包含所有需要计算的月度的DataFrame，统一使用月末日期格式：

# 确定时间范围：从最早起始月到当前月
start_month = df['date'].min()
end_month = pd.to_datetime(datetime.datetime.now()) + pd.offsets.MonthEnd(0)
leased_months = pd.date_range(start=start_month, end=end_month, freq='M')
new_df = pd.DataFrame({'leased': leased_months})

3. 匹配月度对应value并汇总 #

遍历每个目标月度，筛选处于支付周期内的value并拼接成预期字符串：

def get_monthly_values(leased_date, df):
    # 筛选条件：当前月在支付起始月和结束月之间
    mask = (df['date'] <= leased_date) & (leased_date <= df['end_date'])
    selected_values = df.loc[mask, 'value'].round(2).astype(str)
    return ' + '.join(selected_values)

new_df['value'] = new_df['leased'].apply(lambda x: get_monthly_values(x, df))
# 将日期格式转为YYYY-MM字符串
new_df['leased'] = new_df['leased'].dt.strftime('%Y-%m')

4. 查看结果 #

运行后new_df的输出示例：

leased                                                                 value
0  2021-08                                                          22.5 + 6600.0
1  2021-09  6600.0 + 1057.62 + 646.35 + 311.76 + 3982.5 + 900.0 + 546.0 + 1471.5 + 1535.16
2  2021-10  6600.0 + 646.35 + 311.76 + 3982.5 + 900.0 + 546.0 + 1471.5 + 1535.16
3  2021-11        6600.0 + 3982.5 + 900.0 + 546.0 + 1471.5 + 1535.16
...

大数据量优化方案 #

如果数据量较大，遍历方式效率偏低，可改用交叉连接+分组的向量化操作：

# 交叉连接目标月度和原始数据
cross_df = new_df.assign(key=1).merge(df.assign(key=1), on='key').drop('key', axis=1)
# 筛选有效支付记录
cross_df = cross_df[(cross_df['date'] <= cross_df['leased']) & (cross_df['leased'] <= cross_df['end_date'])]
# 按月度分组拼接value字符串
new_df = cross_df.groupby('leased')['value'].apply(lambda x: ' + '.join(x.round(2).astype(str))).reset_index()
new_df['leased'] = new_df['leased'].dt.strftime('%Y-%m')

内容的提问来源于stack exchange，提问作者Jonas Palačionis