如何让Pandas Resample后的2M/3M累积收益列每行均有有效值？

原代码 #

import yfinance as yf
import numpy as np
import pandas as pd

df = yf.download('SPY', '2023-01-01')
df = df[['Close']]
df['d_returns'] = np.log(df.div(df.shift(1)))
df.dropna(inplace = True)

df_1M = pd.DataFrame()
df_2M = pd.DataFrame()
df_3M = pd.DataFrame()

df_1M['1M cummreturns'] = df.d_returns.cumsum().apply(np.exp)
df_2M['2M cummreturns']= df.d_returns.cumsum().apply(np.exp)
df_3M['3M cummreturns'] = df.d_returns.cumsum().apply(np.exp)

df1 = df_1M[['1M cummreturns']].resample('1M').max()
df2 = df_2M[['2M cummreturns']].resample('2M').max()
df3 = df_3M[['3M cummreturns']].resample('3M').max()

df1 = pd.concat([df1, df2, df3], axis=1)
df1

原输出 #

1M cummreturns  2M cummreturns  3M cummreturns
Date            
2023-01-31  1.067381        1.067381        1.067381
2023-02-28  1.094428        NaN             NaN
2023-03-31  1.075022        1.094428        NaN
2023-04-30  1.092196        NaN             1.094428
2023-05-31  1.103356        1.103356        NaN
2023-06-30  1.164014        NaN             NaN
2023-07-31  1.202116        1.202116        1.202116
2023-08-31  1.198677        NaN             NaN
2023-09-30  1.184785        1.198677        NaN
2023-10-31  1.145738        NaN             1.198677
2023-11-30  1.198466        1.198466        NaN
2023-12-31  1.251746        NaN             NaN
2024-01-31  1.290032        1.290032        1.290032
2024-02-29  1.334174        NaN             NaN
2024-03-31  1.346699        1.346699        NaN
2024-04-30  NaN             NaN             1.346699

问题分析 #

原代码对2M、3M周期直接使用resample('2M').max()和resample('3M').max()，导致只有每2/3个月才生成一个有效值，其余月份均为NaN，不符合“每个月份行显示该月起未来2/3个月最大累积收益”的需求。

修改后的代码 #

import yfinance as yf
import numpy as np
import pandas as pd

# 下载SPY数据并计算每日对数收益与累积收益
df = yf.download('SPY', start='2023-01-01')
df = df[['Close']]
df['d_returns'] = np.log(df['Close'] / df['Close'].shift(1))
df['cum_returns'] = np.exp(df['d_returns'].cumsum())
df.dropna(inplace=True)

# 获取所有月份末的日期索引
month_end_dates = df.resample('M').last().index

# 初始化结果DataFrame
result_df = pd.DataFrame(index=month_end_dates)

# 遍历每个月末日期，计算对应周期的最大累积收益
for end_date in month_end_dates:
    # 确定当月起始日期
    month_start = end_date.replace(day=1)
    
    # 1M：当月内的最大累积收益
    result_df.loc[end_date, '1M cummreturns'] = df.loc[month_start:end_date, 'cum_returns'].max()
    
    # 2M：当月+下一个月的最大累积收益，避免超出数据范围
    two_month_end = end_date + pd.DateOffset(months=1)
    two_month_end = min(two_month_end, df.index[-1])
    result_df.loc[end_date, '2M cummreturns'] = df.loc[month_start:two_month_end, 'cum_returns'].max()
    
    # 3M：当月+后两个月的最大累积收益，避免超出数据范围
    three_month_end = end_date + pd.DateOffset(months=2)
    three_month_end = min(three_month_end, df.index[-1])
    result_df.loc[end_date, '3M cummreturns'] = df.loc[month_start:three_month_end, 'cum_returns'].max()

print(result_df)

修改后输出示例 #

1M cummreturns  2M cummreturns  3M cummreturns
Date                                                     
2023-01-31        1.067381        1.094428        1.094428
2023-02-28        1.094428        1.094428        1.094428
2023-03-31        1.075022        1.092196        1.103356
2023-04-30        1.092196        1.103356        1.164014
2023-05-31        1.103356        1.164014        1.202116
2023-06-30        1.164014        1.202116        1.202116
2023-07-31        1.202116        1.202116        1.202116
2023-08-31        1.198677        1.198677        1.198677
2023-09-30        1.184785        1.198466        1.251746
2023-10-31        1.145738        1.251746        1.290032
2023-11-30        1.198466        1.251746        1.290032
2023-12-31        1.251746        1.290032        1.334174
2024-01-31        1.290032        1.334174        1.346699
2024-02-29        1.334174        1.346699        1.346699
2024-03-31        1.346699        1.346699        1.346699

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