如何使用修改的索引来摆动 pandas 数据框？ [英] How to pivot a pandas dataframe using a modified index?
问题描述
我有一个以下格式的时间序列数据框：
I have a timeseries dataframe of the form:
rng = pd.date_range('1/1/2013', periods=1000, freq='10min')
ts = pd.Series(np.random.randn(len(rng)), index=rng)
ts = ts.to_frame(name=None)
我需要做两件事：
步骤1：修改索引，以便每天从前一天的17:00:00开始。我使用这样做：
Step 1: Modify the index, so that every day starts at 17:00:00 of the day before. I do this using:
ts.index = pd.to_datetime(ts.index.values + np.where((ts.index.time >= datetime.time(17)), pd.offsets.Day(1).nanos, 0))
步骤2：转动数据框，如下所示：
Step 2: Pivot the dataframe, like this:
ts_ = pd.pivot_table(ts, index=ts.index.date, columns=ts.index.time, values=0)
我遇到的问题是，当摆动数据框时，熊猫似乎忘记了我在步骤1中修改的索引。
The problem I have, is that when pivoting the dataframe, pandas seems to forget the modification of index I made in Step 1.
这就是我获得
00:00:00 00:10:00 00:20:00 ... 23:50:00
2013-01-10 -1.800381 -0.459226 -0.172929 ... -1.000381
2013-01-11 -1.258317 -0.973924 0.955224 ... 0.072929
2013-01-12 -0.834976 0.018793 -0.141608 ... 2.072929
2013-01-13 -0.131197 0.289998 2.200644 ... 1.589998
2013-01-14 -0.991653 0.276874 -1.390654 ... -2.090654
相反，这是期望的结果
17:00:00 17:10:00 17:20:00 ... 16:50:00
2013-01-10 -2.800381 1.000226 2.172929 ... 0.172929
2013-01-11 0.312587 1.003924 2.556624 ... -0.556624
2013-01-12 2.976834 1.000003 -2.141608 ... -1.141608
2013-01-13 1.197131 1.333998 -2.999944 ... -1.999944
2013-01-14 -1.653991 1.278884 -1.390654 ... -4.390654
编辑 - 澄清说明：请注意它的希望每天从'17：00：00'开始在'16：50：00'结束。
Edit - Clarification Note: Please notice how Its desired that each day starts at '17:00:00' ends at '16:50:00'.
使用Python 2.7
Using Python 2.7
注意：尼克尔·马维里（Nickil Maveli）提出的解决方案概括了答案，但是将日期转向错误的方式。这个想法是Day_t =在Day_t-1在'17：00'开始。现在，解决方案是在Day_t =Day：at17：00开始。
Note: The solution presented by Nickil Maveli aproximates the answer but is shifting the date the wrong way. The idea is that Day_t = Starts at Day_t-1 at '17:00'. Right now, the solution is doing Day_t = Starts at Day_t at '17:00'.
推荐答案
所以我需要画一些图片，所以 here 他们是：
So I needed to draw some pictures, so here they are:
# Step 1:
df1 = df.ix[:, :'16:59'] # http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.ix.html
df2 = df.ix[:, '17:00' : ]
# Step 2:
df3 = df2.shift(periods = 1) # http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.shift.html
# Step 3:
df4 = pandas.concat([df3, df1], axis = 1) # http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html
这篇关于如何使用修改的索引来摆动 pandas 数据框？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
