Pandas计算基金累计收益率遇溢出及FutureWarning问题求解

问题背景 #

需要基于按日期、基金名称排序的DataFrame（包含nav资产净值字段）计算累计收益率，使用lambda+np.cumprod实现时触发两个警告：

• 溢出警告：尝试修改dtype未解决
• FutureWarning：已知可通过group_keys参数处理

原代码：

import pandas as pd
import io
import numpy as np

temp = u"""
date,Fund ID,nav
2023-01-05,FUND_A,262877379.16
...（省略数据行）
2023-02-28,FUND_B,358814683.62
"""
test = pd.read_csv(io.StringIO(temp), sep=",", parse_dates=False)
test['date'] = pd.to_datetime(test['date'], format ="%Y-%m-%d")
test['cum_return'] = test.groupby('Fund ID')['nav'].apply(lambda x: np.cumprod((1 + x)) - 1)

触发的警告：

C:\Users\SAMSUNG\anaconda3\Lib\site-packages\numpy\core\fromnumeric.py:57: RuntimeWarning: overflow encountered in accumulate
  return bound(*args, **kwds)
C:\Users\SAMSUNG\AppData\Local\Temp\ipykernel_15364\496371556.py:1: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.
To preserve the previous behavior, use

.groupby(..., group_keys=False)

To adopt the future behavior and silence this warning, use 

.groupby(..., group_keys=True)
  test['cum_return'] = test.groupby('Fund ID')['nav'].apply(lambda x: np.cumprod((1 + x)) - 1)

警告修复步骤 #

1. 解决FutureWarning #

按照警告提示，在groupby中添加group_keys参数即可消除警告：

• 若要保留原有行为（不将分组键添加到结果索引），使用group_keys=False
• 若要适配未来版本行为，使用group_keys=True

2. 解决溢出警告 #

根本原因：计算逻辑错误
原代码中1 + x的x是nav本身（数值为几百万），直接对如此大的数值做累积乘积必然导致数值溢出。累计收益率的正确计算逻辑应为：
累计收益率 = (当日净值 / 该基金首日净值) - 1
或等价于每日收益率的累积乘积减1：累计收益率 = 累积乘积(1 + 每日收益率) - 1，其中每日收益率 = (当日净值/前一日净值) - 1

两种修正方案：

方案一（更简洁高效）：

test['cum_return'] = test.groupby('Fund ID', group_keys=False)['nav'].apply(lambda x: (x / x.iloc[0]) - 1)

方案二（基于每日收益率累积）：

test['cum_return'] = test.groupby('Fund ID', group_keys=False)['nav'].apply(lambda x: (x.pct_change().fillna(0) + 1).cumprod() - 1)

完整修复后的代码 #

import pandas as pd
import io
import numpy as np

temp = u"""
date,Fund ID,nav
2023-01-05,FUND_A,262877379.16
2023-01-06,FUND_A,263507035.14
2023-01-07,FUND_A,264752531.29
2023-01-08,FUND_A,264293118.56
2023-01-09,FUND_A,265590797.93
2023-01-10,FUND_A,265590775.93
2023-01-11,FUND_A,265590898.93
2023-01-12,FUND_A,265621139.41
2023-01-13,FUND_A,264868632.86
2023-01-14,FUND_A,266123265.88
2023-01-15,FUND_A,267106475.96
2023-01-16,FUND_A,267582033.3
2023-01-17,FUND_A,267581903.3
2023-01-18,FUND_A,267581909.3
2023-01-19,FUND_A,267597565.49
2023-01-20,FUND_A,268828419.07
2023-01-21,FUND_A,269879026.75
2023-01-22,FUND_A,269209075.28
2023-01-23,FUND_A,268023948.37
2023-01-24,FUND_A,268023589.37
2023-01-25,FUND_A,268023331.37
2023-01-26,FUND_A,267874945.91
2023-01-27,FUND_A,268421081.55
2023-01-28,FUND_A,268729376.71
2023-01-29,FUND_A,268293499.95
2023-01-30,FUND_A,268070195.12
2023-01-31,FUND_A,268070570.12
2023-02-01,FUND_A,268069964.12
2023-02-02,FUND_A,267188684.75
2023-02-03,FUND_A,267496712.59
2023-02-04,FUND_A,267526762.57
2023-02-05,FUND_A,271378160.78
2023-02-06,FUND_A,270154112.72
2023-02-07,FUND_A,270153984.72
2023-02-08,FUND_A,270154208.72
2023-02-09,FUND_A,269202371.67
2023-02-10,FUND_A,269034459.46
2023-02-11,FUND_A,268260972.59
2023-02-12,FUND_A,268793639.85
2023-02-13,FUND_A,267819922.47
2023-02-14,FUND_A,267819544.47
2023-02-15,FUND_A,267820160.47
2023-02-16,FUND_A,267501280.07
2023-02-17,FUND_A,266718816.91
2023-02-18,FUND_A,266318793.92
2023-02-19,FUND_A,266100701.19
2023-02-20,FUND_A,266357789.28
2023-02-21,FUND_A,266358292.28
2023-02-22,FUND_A,266358374.28
2023-02-23,FUND_A,266297038.11
2023-02-24,FUND_A,265152593.68
2023-02-25,FUND_A,265280938.19
2023-02-26,FUND_A,265753112.99
2023-02-27,FUND_A,264823479.12
2023-02-28,FUND_A,264823962.12
2023-01-05,FUND_B,354997525.29
2023-01-06,FUND_B,355716206.9
2023-01-07,FUND_B,357328327.64
2023-01-08,FUND_B,356755368.55
2023-01-09,FUND_B,358525763.27
2023-01-10,FUND_B,358525715.27
2023-01-11,FUND_B,358525829.27
2023-01-12,FUND_B,358560032.03
2023-01-13,FUND_B,357544775.85
2023-01-14,FUND_B,359237768.65
2023-01-15,FUND_B,360513139.37
2023-01-16,FUND_B,361224708.97
2023-01-17,FUND_B,361224452.97
2023-01-18,FUND_B,361224357.97
2023-01-19,FUND_B,361307618.45
2023-01-20,FUND_B,362992630.7
2023-01-21,FUND_B,364502776.04
2023-01-22,FUND_B,363667782.2
2023-01-23,FUND_B,362172298.58
2023-01-24,FUND_B,362172196.58
2023-01-25,FUND_B,362172061.58
2023-01-26,FUND_B,362053109.42
2023-01-27,FUND_B,362853781.39
2023-01-28,FUND_B,363344529.15
2023-01-29,FUND_B,362846268.28
2023-01-30,FUND_B,362601837.45
2023-01-31,FUND_B,362601459.45
2023-02-01,FUND_B,362601711.45
2023-02-02,FUND_B,361458780.23
2023-02-03,FUND_B,361943215.36
2023-02-04,FUND_B,362075761.92
2023-02-05,FUND_B,367337579.83
2023-02-06,FUND_B,365773518.56
2023-02-07,FUND_B,365773899.56
2023-02-08,FUND_B,365774045.56
2023-02-09,FUND_B,364551357.85
2023-02-10,FUND_B,364338309.96
2023-02-11,FUND_B,363294040.03
2023-02-12,FUND_B,364033223.52
2023-02-13,FUND_B,362717028.06
2023-02-14,FUND_B,362716228.06
2023-02-15,FUND_B,362716588.06
2023-02-16,FUND_B,362315772.87
2023-02-17,FUND_B,361310193.74
2023-02-18,FUND_B,360777411.01
2023-02-19,FUND_B,360524129.16
2023-02-20,FUND_B,360859219.16
2023-02-21,FUND_B,360859566.16
2023-02-22,FUND_B,360860075.16
2023-02-23,FUND_B,360794922.2
2023-02-24,FUND_B,359270093.58
2023-02-25,FUND_B,359443774.88
2023-02-26,FUND_B,360108483.71
2023-02-27,FUND_B,358814690.62
2023-02-28,FUND_B,358814683.62
"""
test = pd.read_csv(io.StringIO(temp), sep=",", parse_dates=False)
test['date'] = pd.to_datetime(test['date'], format ="%Y-%m-%d")
# 先按基金和日期排序，确保计算逻辑正确
test = test.sort_values(by=['Fund ID', 'date']).reset_index(drop=True)
# 使用方案一，同时添加group_keys=False消除FutureWarning
test['cum_return'] = test.groupby('Fund ID', group_keys=False)['nav'].apply(lambda x: (x / x.iloc[0]) - 1)

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