CODEWITHSUNDEEP
pandastime-seriesdatetimeindex
cJc
cJc

Reputation: 863

How do I make a pandas datatimeindex into twice daily frequency?

I have a pandas df that looks like this:

           Open_fut  Close_fut
Date                           
2017-05-12   20873.0    20850.0
2017-05-11   20887.0    20869.0
2017-05-10   20891.0    20888.0
2017-05-09   20943.0    20886.0
2017-05-08   21001.0    20943.0

My dates are datetime64[ns] and the other columns float64.

How can I make my time series so that Open_fut comes at 2017-05-12 09:30:00 and Close_fut at 2017-05-12 15:30:00 and so on for each day?

EDIT:

Ideally the new df would look like this:

                    fut  
Date                           
2017-05-12 09:30:00 20873.0
2017-05-12 15:30:00 20850.0
.
.

Upvotes: 1

Views: 59

Answers (1)

jezrael
jezrael

Reputation: 863801

It seems you need MultiIndex.from_arrays with adding times by to_timedelta:

time1 = '09:30:00'
time2 = '15:30:00'

df.index = pd.MultiIndex.from_arrays([df.index + pd.to_timedelta(time1),
                                      df.index + pd.to_timedelta(time2)],
                                      names=['date1','date2'])
print (df)
                                         Open_fut  Close_fut
date1               date2                                   
2017-05-12 09:30:00 2017-05-12 15:30:00   20873.0    20850.0
2017-05-11 09:30:00 2017-05-11 15:30:00   20887.0    20869.0
2017-05-10 09:30:00 2017-05-10 15:30:00   20891.0    20888.0
2017-05-09 09:30:00 2017-05-09 15:30:00   20943.0    20886.0
2017-05-08 09:30:00 2017-05-08 15:30:00   21001.0    20943.0

For your output is solution similar, only is used lreshape for reshaping + set_index + sort_index:

time1 = '09:30:00'
time2 = '15:30:00'

df['date1'] = df.index + pd.to_timedelta(time1)
df['date2'] = df.index + pd.to_timedelta(time2)                                   
df = pd.lreshape(df, {'date':['date1', 'date2'], 'fut':['Open_fut', 'Close_fut']}) 
df = df.set_index('date').sort_index()             
print (df)
                         fut
date                        
2017-05-08 09:30:00  21001.0
2017-05-08 15:30:00  20943.0
2017-05-09 09:30:00  20943.0
2017-05-09 15:30:00  20886.0
2017-05-10 09:30:00  20891.0
2017-05-10 15:30:00  20888.0
2017-05-11 09:30:00  20887.0
2017-05-11 15:30:00  20869.0
2017-05-12 09:30:00  20873.0
2017-05-12 15:30:00  20850.0

EDIT:

lreshape is now undocumented, but is possible in future will by removed (with pd.wide_to_long too).

Possible solution is merging all 3 functions to one - maybe melt, but now it is not implementated. Maybe in some new version of pandas. Then my answer will be updated.

Upvotes: 3

Related Questions