将具有多个索引的每日数据框转换为每季度
我想将股票数据的每日数据框转换为季度数据框.但是，使用重新采样无法正常工作，因为我有一个多指标，所以我希望我的最终季度数据框架仍包含单个股票(重新采样只是将所有股票汇总):
I would like to convert my daily dataframe of stock data to a quarterly one. However, using resample did not work, because I have a multi index, so I would like my final quarterly dataframe to still contain the individual stocks (resample just summarizes all of them):
import pandas as pd
dict1 = [
{'ticker':'jpm','date': '2016-11-27','returns': 0.2},
{'ticker':'jpm','date': '2016-11-28','returns': 0.2},
{'ticker':'ge','date': '2016-11-27','returns': 0.2},
{'ticker':'ge','date': '2016-11-28','returns': 0.2},
{'ticker':'amzn','date': '2016-11-27','returns': 0.2},
{'ticker':'amzn','date': '2016-11-28','returns': 0.2},
]
df1= pd.DataFrame(dict1)
df1['date'] = pd.to_datetime(df1['date'])
df1=df1.set_index(['date','ticker'], drop=True)
我的最终结果应该是:
Q42016 JPM 0.2
Q42016 GE 0.2
Q42016 AMZ 0.2
使用重采样时，我得到:
When I used resample, I get:
Q42016 0.2
此外，我对Pandas 0.18(长话说)感到困惑.感谢您的帮助.
Also, I am stuck with Pandas 0.18 (long story). Any help is appreciated.
第一个想法是通过将 ticker 转换为列来创建 DatetimeIndex ，然后使用 groupby 带有 的代码>重新采样 :
First idea is create DatetimeIndex by convert ticker to column, then use groupby with resample:
df1 = df1.reset_index('ticker').groupby('ticker').resample('Q').mean()
print (df1)
returns
ticker date
amzn 2016-12-31 0.2
ge 2016-12-31 0.2
jpm 2016-12-31 0.2
使用 Grouper 的另一种解决方案代码> :
Another solution with Grouper:
df1 = df1.groupby([pd.Grouper(freq='Q', level='date'), 'ticker']).mean()
print (df1)
returns
date ticker
2016-12-31 amzn 0.2
ge 0.2
jpm 0.2