0

I excercice with some stock market data and have a dataframe starts at 09:30 and ends at 16:00. I want to resample to an 4Hour Interval using

agg_dict = {'open': 'first','high': 'max','low': 'min','cls': 'last','vol': 'sum'}
data_4hour = fullRth.resample('4H',label='left',origin='end').agg(agg_dict).dropna().round(2).sort_index(ascending=False)

my output is:

data_4hour.head()

                          open    high    low      cls       vol
time                    
2021-09-03 11:59:00     452.59  453.63  452.48  453.06  21407679
2021-09-03 07:59:00     451.98  453.05  451.55  452.59  16481655
2021-09-02 11:59:00     453.47  453.52  451.91  453.20  22855174
2021-09-02 07:59:00     453.32  454.05  453.05  453.48  14509768
2021-09-01 11:59:00     452.37  453.11  451.54  451.82  24303603

my desired output should look like this:

                          open    high    low      cls       vol
time                    
2021-09-03 11:59:00     452.59  453.63  452.48  453.06  21407679
2021-09-03 09:30:00     451.98  453.05  451.55  452.59  16481655
2021-09-02 11:59:00     453.47  453.52  451.91  453.20  22855174
2021-09-02 09:30:00     453.32  454.05  453.05  453.48  14509768
2021-09-01 11:59:00     452.37  453.11  451.54  451.82  24303603

As far as I understand it has to do how binning is depending on the datetime(?). I've read this answer on github, But as it is from 2013 I was wondering if there is a possibility to do it.

I'm using: python : 3.9.6.final.0 pandas : 1.3.0 numpy : 1.21.1

Hank Gordon
  • 127
  • 1
  • 9

1 Answers1

0

I did a little work around. If someone has a better solution, I would like to read it. First I made sure that the values in the bins are correct located. Then I did with the DataFrame above:

# separating date and time from DatetimeIndex into new columns
data_4hour['times'] = data_4hour.index.time.astype(str)
data_4hour['date'] = data_4hour.index.date.astype(str)

# changing the values with `np.where` 
data_4hour['times'] = np.where(data_4hour['times']=='07:59:00','09:30:00',data_4hour['times'])

# concatenate date and time column into Datetime column
data_4hour['datetime'] = pd.to_datetime(data_4hour['date'] + ' ' + data_4hour['times'])

data_4hour.reset_index(inplace=True)

del data_4hour['time'],data_4hour['times'],data_4hour['date']

data_4hour = data_4hour.set_index('datetime')

print(data_4hour)

                       open    high     low     cls       vol
datetime                                                     
2021-09-03 11:59:00  452.59  453.63  452.48  453.06  21407679
2021-09-03 09:30:00  451.98  453.05  451.55  452.59  16481655
2021-09-02 11:59:00  453.47  453.52  451.91  453.20  22855174
2021-09-02 09:30:00  453.32  454.05  453.05  453.48  14509768
2021-09-01 11:59:00  452.37  453.11  451.54  451.82  24303603
Hank Gordon
  • 127
  • 1
  • 9