本文介绍了重采样多索引数据帧中的时间序列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在 MultiIndex DataFrame 中有一个包含时间序列数据的分层表 - 请参阅下面的示例数据 - 请注意,第 1 级索引的填充不均匀.

I have a hierarchical table with time series data in a MultiIndex DataFrame - see sample data below - note that Level 1 index is unevenly populated.

如何重新采样 DataFrame 以生成每周时间序列,从当前日期开始每周向后重新采样.

How do I resample the DataFrame to result in a weekly time series that resamples weekly backwards from the current date.

我已经尝试过但失败了:

I have tried this but failed:

df.index.levels[0].name = 'date'
df.reset_index().groupby(pandas.Grouper(key='date', freq='W'))

示例数据:

                                Price   Sector
2016-08-08  Equity(24 [AAPL])   107.47  311
            Equity(4151 [JNJ])  124.19  206
            Equity(5061 [MSFT]) 57.95   311
            Equity(8347 [XOM])  87.52   309
            Equity(11100 [BRK_B])   145.52  103
            Equity(16841 [AMZN])    765.87  102
            Equity(26578 [GOOG_L])  806.93  311
            Equity(42950 [FB])  125.11  311
2016-08-09  Equity(24 [AAPL])   108.37  311
            Equity(4151 [JNJ])  123.69  206
            Equity(5061 [MSFT]) 58.06   311
            Equity(8347 [XOM])  88.55   309
            Equity(11100 [BRK_B])   145.45  103
            Equity(16841 [AMZN])    766.56  102
            Equity(26578 [GOOG_L])  805.39  311
            Equity(42950 [FB])  125.26  311
2016-08-10  Equity(24 [AAPL])   108.81  311
            Equity(4151 [JNJ])  123.44  206
            Equity(5061 [MSFT]) 58.20   311
            Equity(8347 [XOM])  87.98   309
            Equity(11100 [BRK_B])   147.30  103
            Equity(16841 [AMZN])    768.32  102
            Equity(26578 [GOOG_L])  807.47  311
            Equity(42950 [FB])  125.06  311
2016-08-11  Equity(24 [AAPL])   108.00  311
            Equity(4151 [JNJ])  123.43  206
            Equity(5061 [MSFT]) 58.02   311
            Equity(8347 [XOM])  86.41   309
            Equity(11100 [BRK_B])   146.73  103
            Equity(16841 [AMZN])    768.44  102
            Equity(26578 [GOOG_L])  808.66  311
            Equity(42950 [FB])  124.87  311
2016-08-12  Equity(24 [AAPL])   107.93  311
            Equity(4151 [JNJ])  123.76  206
            Equity(5061 [MSFT]) 58.31   311
            Equity(8347 [XOM])  86.73   309
            Equity(11100 [BRK_B])   147.72  103
            Equity(16841 [AMZN])    771.30  102
            Equity(26578 [GOOG_L])  808.20  311
            Equity(42950 [FB])  124.91  311
2016-08-15  Equity(24 [AAPL])   108.16  311
            Equity(4151 [JNJ])  123.21  206
            Equity(5061 [MSFT]) 57.93   311
            Equity(8347 [XOM])  87.80   309
            Equity(11100 [BRK_B])   147.64  103
            Equity(16841 [AMZN])    772.56  102
            Equity(26578 [GOOG_L])  807.05  311
            Equity(42950 [FB])  124.87  311
2016-08-16  Equity(24 [AAPL])   109.50  311
            Equity(4151 [JNJ])  122.33  206
            Equity(5061 [MSFT]) 57.76   311
            Equity(8347 [XOM])  87.81   309
            Equity(11100 [BRK_B])   147.78  103
            Equity(16841 [AMZN])    768.49  102
            Equity(26578 [GOOG_L])  806.00  311
            Equity(42950 [FB])  123.90  311
2016-08-17  Equity(24 [AAPL])   109.38  311
            Equity(4151 [JNJ])  120.34  206
            Equity(5061 [MSFT]) 57.44   311
            Equity(8347 [XOM])  87.94   309
……      ……          ……  ……
2017-07-31  Equity(8347 [XOM])  79.60   309
            Equity(11100 [BRK_B])   173.99  103
            Equity(16841 [AMZN])    1020.01 102
            Equity(25006 [JPM]) 91.27   103
            Equity(26578 [GOOG_L])  958.20  311
            Equity(42950 [FB])  172.46  311
2017-08-01  Equity(24 [AAPL])   148.77  311
            Equity(4151 [JNJ])  132.72  206
            Equity(5061 [MSFT]) 72.72   311
            Equity(8347 [XOM])  80.04   309
            Equity(11100 [BRK_B])   175.07  103
            Equity(16841 [AMZN])    987.50  102
            Equity(25006 [JPM]) 91.81   103
            Equity(26578 [GOOG_L])  945.83  311
            Equity(42950 [FB])  169.28  311
2017-08-02  Equity(24 [AAPL])   150.05  311
            Equity(4151 [JNJ])  132.55  206
            Equity(5061 [MSFT]) 72.55   311
            Equity(8347 [XOM])  80.15   309
            Equity(11100 [BRK_B])   176.29  103
            Equity(16841 [AMZN])    996.19  102
            Equity(25006 [JPM]) 93.00   103
            Equity(26578 [GOOG_L])  946.56  311
            Equity(42950 [FB])  169.85  311
2017-08-03  Equity(24 [AAPL])   157.15  311
            Equity(4151 [JNJ])  132.14  206
            Equity(5061 [MSFT]) 72.25   311
            Equity(8347 [XOM])  80.59   309
            Equity(11100 [BRK_B])   177.78  103
            Equity(16841 [AMZN])    995.32  102
            Equity(25006 [JPM]) 93.08   103
            Equity(26578 [GOOG_L])  947.18  311
            Equity(42950 [FB])  169.25  311
2017-08-04  Equity(24 [AAPL])   155.53  311
            Equity(4151 [JNJ])  133.35  206
            Equity(5061 [MSFT]) 72.14   311
            Equity(8347 [XOM])  80.46   309
            Equity(11100 [BRK_B])   178.94  103
            Equity(16841 [AMZN])    987.14  102
            Equity(25006 [JPM]) 92.46   103
            Equity(26578 [GOOG_L])  940.63  311
            Equity(42950 [FB])  168.58  311
2017-08-07  Equity(24 [AAPL])   156.34  311
            Equity(4151 [JNJ])  133.11  206
            Equity(5061 [MSFT]) 72.66   311
            Equity(8347 [XOM])  80.17   309
            Equity(11100 [BRK_B])   179.79  103
            Equity(16841 [AMZN])    987.41  102
            Equity(25006 [JPM]) 93.68   103
            Equity(26578 [GOOG_L])  945.34  311
            Equity(42950 [FB])  169.59  311
2017-08-08  Equity(24 [AAPL])   158.79  311
            Equity(4151 [JNJ])  132.84  206
            Equity(5061 [MSFT]) 72.40   311
            Equity(8347 [XOM])  80.15   309
            Equity(11100 [BRK_B])   178.00  103
            Equity(16841 [AMZN])    991.80  102
            Equity(25006 [JPM]) 94.02   103
            Equity(26578 [GOOG_L])  945.31  311
            Equity(42950 [FB])  171.96  311

推荐答案

根据您希望如何汇总价格,resample 为您提供了多种选择.以下是涵盖 2 个工作日部分时间的 2 只股票的示例数据框:

Depending on how you want to aggregate the prices, resample gives you many options. Here's a sample dataframe for 2 stocks covering parts of 2 business weeks:

np.random.seed(123)
df1 = pd.DataFrame({ 'price':abs(np.random.randn(5)).cumsum(), 'ticker':'GOOG_L' },
                   index=pd.date_range('1-4-2017', periods=5, freq='B') )
df2 = pd.DataFrame({ 'price':abs(np.random.randn(5)).cumsum(), 'ticker':'AAPL' },
                   index=pd.date_range('1-4-2017', periods=5, freq='B') )
df = df1.append(df2).sort_index()

               price ticker
2017-01-04  1.085631   GOOG_L
2017-01-04  1.651437   AAPL
2017-01-05  2.082976   GOOG_L
2017-01-05  4.078116   AAPL
2017-01-06  2.365955   GOOG_L
2017-01-06  4.507028   AAPL
2017-01-09  3.872249   GOOG_L
2017-01-09  5.772965   AAPL
2017-01-10  4.450850   GOOG_L
2017-01-10  6.639705   AAPL

您可以从那里以多种方式(平均值、最大值、第一等)重新采样:

From there you can resample in a variety of ways (mean, max, first, etc.):

df.groupby('ticker')['price'].resample('W').mean()

ticker
AAPL    2017-01-08    3.412194
        2017-01-15    6.206335
GOOG_L  2017-01-08    1.844854
        2017-01-15    4.161549
Name: price, dtype: float64

df.groupby('ticker')['price'].resample('W').max()

ticker
AAPL    2017-01-08    4.507028
        2017-01-15    6.639705
GOOG_L  2017-01-08    2.365955
        2017-01-15    4.450850
Name: price, dtype: float64

df.groupby('ticker')['price'].resample('W').first()

ticker
AAPL    2017-01-08    1.651437
        2017-01-15    5.772965
GOOG_L  2017-01-08    1.085631
        2017-01-15    3.872249
Name: price, dtype: float64

编辑添加:您可以使用loffset"可选参数调整星期几.比如上面的日期都是星期日.要使其成为星期一,请使用:

edit to add: You can adjust the day of the week with the "loffset" optional argument. For example, the above dates are all Sundays. To make it Monday, use:

df.groupby('ticker')['price'].resample('W',loffset='1D').first()

这篇关于重采样多索引数据帧中的时间序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 11:09
查看更多