如何对比DataFrame日期列按条件求和，计算结算余额并补全缺失日期

核心思路 #

• 先按id和结算日期SettleDate分组，统计每个结算日当日的总结算金额增量，不受交易发生时间影响，完全以结算日作为统计口径
• 生成覆盖业务所需全部日期的连续序列（范围取所有交易的最早交易日到最晚结算日即可，也可根据业务需要自行调整日期范围）
• 对每个id关联全量连续日期，生成无缺失的日期基础表
• 将结算日增量金额关联到基础表，未结算日的增量填充为0后按id分组累加，即可得到每日结算余额

代码实现 #

import pandas as pd
import numpy as np

# 1、构造原始DataFrame，实际使用时替换为自己的数据源即可
df = pd.DataFrame({
    'id': [3136,3136,3136,3136,3136,3136],
    'trade_id': [6828,6828,6828,6934,7007,7050],
    'TradeDate': [20200616,20200616,20200620,20200623,20200623,20200625],
    'SettleDate': [20200620,20200620,20200620,20200630,20200630,20200630],
    'amt': [15000000.0,15000000.0,15000000.0,15000000.0,30000000.0,25000000.0]
})

# 2、将日期列转成datetime格式方便生成连续序列
df['SettleDate'] = pd.to_datetime(df['SettleDate'], format='%Y%m%d')
df['TradeDate'] = pd.to_datetime(df['TradeDate'], format='%Y%m%d')

# 3、计算每个id每个结算日的增量金额
settle_amt = df.groupby(['id', 'SettleDate'], as_index=False)['amt'].sum().rename(columns={'amt': 'daily_settle_amt'})

# 4、生成完整连续日期序列，可根据业务需要调整start、end范围
start_dt = df['TradeDate'].min()
end_dt = df['SettleDate'].max()
full_dates = pd.date_range(start=start_dt, end=end_dt, name='SettleDate').to_frame(index=False)

# 5、生成每个id对应的全量日期基础表
id_list = df[['id']].drop_duplicates()
full_df = id_list.merge(full_dates, how='cross')

# 6、关联每日结算增量，填充空值后累加得到结算余额
res = full_df.merge(settle_amt, on=['id', 'SettleDate'], how='left')
res['daily_settle_amt'] = res['daily_settle_amt'].fillna(0)
res['settlement_bal'] = res.groupby('id')['daily_settle_amt'].cumsum()

# 如果需要把日期转回原始的8位数字格式，可取消注释下面这行
# res['SettleDate'] = res['SettleDate'].dt.strftime('%Y%m%d').astype(int)

# 只保留需要的列输出
res = res[['id', 'SettleDate', 'settlement_bal']]
print(res)

输出验证 #

运行代码后的结果和样例完全匹配：2020-06-20之前余额全为0，当日余额为45000000.0，之后到2020-06-29都保持45000000.0，2020-06-30余额为115000000.0，同时支持多id、结算日早于/晚于交易日等各类场景。

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