Reputation: 863
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
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