How to copy pandas framework using modified index?

Question

I have a temporary form framework:

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) 

I need to do two things:

Step 1: Change the index so that every day starts at 17:00:00 in the afternoon. 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)) 

Step 2:. Expand the data block, for example:

 ts_ = pd.pivot_table(ts, index=ts.index.date, columns=ts.index.time, values=0) 

The problem I have is that when you rotate the pandas data frame, it seems to forget the modification of the index I made in step 1.

This is what I get

  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 

Instead, this is the desired result.

  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 

Modify - Explanation Note: Please note that his desire that every day starts at '17: 00: 00 'ends at '16: 50: 00'.

Using Python 2.7

Note: The solution presented by Nikil Maveli, aproximates the answer, but the date is shifted incorrectly. The idea is that Day_t = starts with Day_t-1 at '17: 00 '. Right now, the decision is making Day_t = Start at Day_t at '17: 00 '.

Answer 1

I needed to take 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 

Answer 2

You really don't need to use np.where here, since you just filter by only one parameter. In addition, the else part is executed 0. Thus, there is absolutely no reduction in the index obtained after this step.

Instead, you should:

1. Create a Boolean mask to filter the date and time whose hour attribute is greater than or equal to 17 with the offset of the added day:

 arr = ts.index idx = arr[arr.hour >= 17] + pd.offsets.Day(1) 

2.Reindex based on a modified index:

 ts_clip = ts.reindex(idx) 

Operation 3.Perform pivot :

 pd.pivot_table(ts_clip, index=ts_clip.index.date, columns=ts_clip.index.time, values=0) 

---

Edit

 ts_clip = ts.iloc[np.argwhere(ts.index.hour.__eq__(17)).ravel()[0]:] ts_clip_shift = ts_clip.tshift(-17, freq='H') df = pd.pivot_table(ts_clip_shift, index=(ts_clip_shift.index + pd.offsets.Day(n=1)), columns=ts_clip_shift.index.time, values=0) df.columns= ts_clip.iloc[:len(df.columns)].index.time 

Check DF Characteristics:

 df.info() <class 'pandas.core.frame.DataFrame'> Index: 7 entries, 2013-01-02 to 2013-01-08 Columns: 144 entries, 17:00:00 to 16:50:00 dtypes: float64(144) memory usage: 7.9+ KB