使用当月的第一个交易日将每日 pandas 库存数据转换为月度数据 [英] Convert daily pandas stock data to monthly data using first trade day of the month

问题描述

我在熊猫数据框中有一组计算的OHLCVA每日证券数据，如下所示:

I have a set of calculated OHLCVA daily securities data in a pandas dataframe like this:

>>> type(data_dy)
<class 'pandas.core.frame.DataFrame'>
>>> data_dy
              Open    High     Low   Close     Volume  Adj Close
Date                                                            
2012-12-28  140.64  141.42  139.87  140.03  148806700     134.63
2012-12-31  139.66  142.56  139.54  142.41  243935200     136.92
2013-01-02  145.11  146.15  144.73  146.06  192059000     140.43
2013-01-03  145.99  146.37  145.34  145.73  144761800     140.11
2013-01-04  145.97  146.61  145.67  146.37  116817700     140.72

[5 rows x 6 columns]

我正在使用以下字典和pandas resample函数将数据框转换为月度数据:

I'm using the following dictionary and the pandas resample function to convert the dataframe to monthly data:

>>> ohlc_dict = {'Open':'first','High':'max','Low':'min','Close': 'last','Volume': 'sum','Adj Close': 'last'}

>>> data_dy.resample('M', how=ohlc_dict, closed='right', label='right')
               Volume  Adj Close    High     Low   Close    Open
Date                                                            
2012-12-31  392741900     136.92  142.56  139.54  142.41  140.64
2013-01-31  453638500     140.72  146.61  144.73  146.37  145.11

[2 rows x 6 columns]

这可以正确地进行计算，但是我想使用Yahoo!日期约定，用于使用该时段的第一个交易日而不是熊猫使用的时段的最后一个日历日.

This does the calculations correctly, but I'd like to use the Yahoo! date convention for monthly data of using the first trading day of the period rather than the last calendar day of the period that pandas uses.

所以我希望答案是:

               Volume  Adj Close    High     Low   Close    Open
Date                                                            
2012-12-28  392741900     136.92  142.56  139.54  142.41  140.64
2013-01-02  453638500     140.72  146.61  144.73  146.37  145.11

我可以通过将每日数据转换为python列表，处理数据并将数据返回到数据框来做到这一点，但是如何用熊猫来做到这一点呢?

I could do this by converting the daily data to a python list, process the data and return the data to a dataframe, but how do can this be done with pandas?

推荐答案

您可以通过MS作为重新采样规则，而不是M:

Instead of M you can pass MS as the resample rule:

df =pd.DataFrame( range(72), index = pd.date_range('1/1/2011', periods=72, freq='D'))

#df.resample('MS', how = 'mean')    # pandas <0.18
df.resample('MS').mean()  # pandas >= 0.18

已更新为使用尊重美国联邦假日的月份的第一个工作日:

Updated to use the first business day of the month respecting US Federal Holidays:

df =pd.DataFrame( range(200), index = pd.date_range('12/1/2012', periods=200, freq='D'))

from pandas.tseries.offsets import CustomBusinessMonthBegin
from pandas.tseries.holiday import USFederalHolidayCalendar
bmth_us = CustomBusinessMonthBegin(calendar=USFederalHolidayCalendar())

df.resample(bmth_us).mean()

如果要使用数据中找到的最小月份自定义月份的开始时间，请尝试此操作. (虽然不漂亮，但是应该可以).

if you want custom starts of the month using the min month found in the data try this. (It isn't pretty, but it should work).

month_index =df.index.to_period('M')

min_day_in_month_index = pd.to_datetime(df.set_index(new_index, append=True).reset_index(level=0).groupby(level=0)['level_0'].min())

custom_month_starts =CustomBusinessMonthBegin(calendar = min_day_in_month_index)

将custom_start_months传递给resample

这篇关于使用当月的第一个交易日将每日 pandas 库存数据转换为月度数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！