Perform Calculation Between Rows With Datetime Index And Place Them Into New Columns

I have a dataframe, produced by this piece of code: import pandas as pd import datetime as dt datarange=pd.date_range('01-05-2018 00:00:00', periods=50, freq='4H') range_series_1=

Solution 1:

Use:

from datetime import time

np.random.seed(2019)

datarange=pd.date_range('01-05-2018 00:00:00', periods=50, freq="4H")
range_series_1=pd.Series(np.random.randint(-5,3,size=50).astype(float), index=datarange)
range_series_2=pd.Series(np.random.randint(5,9,size=50).astype(float), index=datarange)
frame=pd.DataFrame({'value1':range_series_1, 'value2':range_series_2})
frame.index.name='datetime'
#print (frame)

Idea is compare times for index and use DatetimeIndex.floor for remove times (created default 00:00:00) times for Series and DataFrame:

s=frame.loc[frame.index.time==time(4,0),'value1']s.index=s.index.floor('d')print(s)datetime2018-01-05   -3.02018-01-06   -5.02018-01-07   -5.02018-01-08   -5.02018-01-09   -1.02018-01-10   -4.02018-01-11   -2.02018-01-12    0.02018-01-13    1.0Name:value1,dtype:float64df1=frame.loc[frame.index.time==time(12,0), ['value1', 'value2']]df1.index=df1.index.floor('d')print(df1)value1value2datetime2018-01-05     0.06.02018-01-06     2.08.02018-01-07    -5.07.02018-01-08     2.07.02018-01-09    -1.05.02018-01-10     1.07.02018-01-11     2.07.02018-01-12    -2.06.0

Then subtract from right side by DataFrame.rsub, add some prefix to new columns and join to original:

frame=frame.join(df1.rsub(s,axis=0).add_prefix('new_'))print(frame.head(15))value1value2new_value1new_value2datetime2018-01-05 00:00:00    -5.06.0-3.0-9.02018-01-05 04:00:00    -3.05.0NaNNaN2018-01-05 08:00:00     2.07.0NaNNaN2018-01-05 12:00:00     0.06.0NaNNaN2018-01-05 16:00:00    -5.07.0NaNNaN2018-01-05 20:00:00     1.06.0NaNNaN2018-01-06 00:00:00     1.05.0-7.0-13.02018-01-06 04:00:00    -5.08.0NaNNaN2018-01-06 08:00:00     0.06.0NaNNaN2018-01-06 12:00:00     2.08.0NaNNaN2018-01-06 16:00:00    -1.08.0NaNNaN2018-01-06 20:00:00    -3.08.0NaNNaN2018-01-07 00:00:00    -5.05.00.0-12.02018-01-07 04:00:00    -5.08.0NaNNaN2018-01-07 08:00:00     2.05.0NaNNaN

Solution 2:

IIUC, you want to shift by time:

shifted = frame.shift(-8, freq='H')

frame['col1'] = frame['value1'] - shifted['value1']
frame['col2'] = frame['value1'] - shifted['value2']

Output (head):

value1value2col1col2datetime2018-01-05 00:00:00    -3.08.0-2.0-8.02018-01-05 04:00:00     0.07.0-2.0-5.02018-01-05 08:00:00    -1.05.0-1.0-8.02018-01-05 12:00:00     2.05.03.0-5.02018-01-05 16:00:00     0.07.04.0-6.0