通过Python中的 pandas 将每日库存数据转换为基于每周的数据 [英] converting daily stock data to weekly-based via pandas in Python

问题描述

我有一个DataFrame，用于存储基于每天的数据，如下所示:

I've got a DataFrame storing daily-based data which is as below:

Date              Open        High         Low       Close   Volume
2010-01-04   38.660000   39.299999   38.509998   39.279999  1293400   
2010-01-05   39.389999   39.520000   39.029999   39.430000  1261400   
2010-01-06   39.549999   40.700001   39.020000   40.250000  1879800   
2010-01-07   40.090000   40.349998   39.910000   40.090000   836400   
2010-01-08   40.139999   40.310001   39.720001   40.290001   654600   
2010-01-11   40.209999   40.520000   40.040001   40.290001   963600   
2010-01-12   40.160000   40.340000   39.279999   39.980000  1012800   
2010-01-13   39.930000   40.669998   39.709999   40.560001  1773400   
2010-01-14   40.490002   40.970001   40.189999   40.520000  1240600   
2010-01-15   40.570000   40.939999   40.099998   40.450001  1244200   

我打算做的是将其合并到基于每周的数据中.分组后:

What I intend to do is to merge it into weekly-based data. After grouping:

1. 日期应该是每个星期一(此时，应该考虑在星期一不是交易日的假期情况，我们应将当前周的第一个交易日作为日期".
2. 开放时间应为周一(或本周的第一个交易日)开放时间.
3. 收盘价应该是星期五(或本周的最后一个交易日)收盘价.
4. 最高价应该是当前一周中交易日的最高价.
5. 低应该是当前一周中交易日的最低低.
6. 交易量应为当周所有交易日交易量的总和.

1. the Date should be every Monday (at this point, holidays scenario should be considered when Monday is not a trading day, we should apply the first trading day in current week as the Date).
2. Open should be Monday's (or the first trading day of current week) Open.
3. Close should be Friday's (or the last trading day of current week) Close.
4. High should be the highest High of trading days in current week.
5. Low should be the lowest Low of trading days in current week.
6. Volumn should be the sum of all Volumes of trading days in current week.

应如下所示:

Date              Open        High         Low       Close   Volume
2010-01-04   38.660000   40.700001   38.509998   40.290001  5925600   
2010-01-11   40.209999   40.970001   39.279999   40.450001  6234600   

当前，我的代码段如下所示，我应该使用哪个函数将基于每日的数据映射到预期的基于每周的数据?非常感谢！

Currently, my code snippet is as below, which function should I use to mapping daily-based data to the expected weekly-based data? Many thanks!

import pandas_datareader.data as web

start = datetime.datetime(2010, 1, 1)
end = datetime.datetime(2016, 12, 31)
f = web.DataReader("MNST", "yahoo", start, end, session=session)
print f

推荐答案

您可以resample(每周一次)，offset(轮班)和apply聚合规则，如下所示:

You can resample (to weekly), offset (shift), and apply aggregation rules as follows:

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

offset = pd.offsets.timedelta(days=-6)

f = pd.read_clipboard(parse_dates=['Date'], index_col=['Date'])
f.resample('W', loffset=offset).apply(logic)

获得:

                 Open       High        Low      Close   Volume
Date                                                           
2010-01-04  38.660000  40.700001  38.509998  40.290001  5925600
2010-01-11  40.209999  40.970001  39.279999  40.450001  6234600

这篇关于通过Python中的 pandas 将每日库存数据转换为基于每周的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！