Dataframe grouping

user_4464045 0
Viewed 63
data=

                              open     high      low    close  volume
timestamp                                                            
2015-02-02 09:15:00+05:30  1280.00  1286.00  1280.00  1284.55    5847
2015-02-02 09:16:00+05:30  1284.55  1290.00  1283.90  1284.05     260
2015-02-02 09:17:00+05:30  1284.05  1288.20  1280.30  1283.90     424
2015-02-02 09:18:00+05:30  1283.90  1285.75  1283.90  1285.35      40
2015-02-02 09:19:00+05:30  1285.35  1290.00  1283.30  1285.40     203
2015-02-02 09:20:00+05:30  1285.35  1286.00  1283.50  1284.65     267

Want to get/group 2H data starting from 9:00:00.

For 1H data I tried:

data.groupby(pd.Grouper(freq='1h')).agg({"open":"first","close":"last","low":"min","high":"max","volume":"sum"})

It appears result as follows:

                              open    close      low     high  volume
timestamp                                                            
2015-02-02 09:00:00+05:30  1280.00  1285.65  1280.00  1290.00   12620
2015-02-02 10:00:00+05:30  1285.65  1281.00  1281.00  1294.00    5103
2015-02-02 11:00:00+05:30  1281.00  1261.55  1261.20  1284.40    8995
2015-02-02 12:00:00+05:30  1261.30  1266.00  1256.00  1267.00   12772
2015-02-02 13:00:00+05:30  1266.00  1263.85  1260.00  1266.85    4076
2015-02-02 14:00:00+05:30  1261.35  1264.60  1260.00  1268.00    5937
2015-02-02 15:00:00+05:30  1263.00  1262.00  1256.65  1265.00    9505
2015-02-02 16:00:00+05:30      NaN      NaN      NaN      NaN       0
2015-02-02 17:00:00+05:30      NaN      NaN      NaN      NaN       0

How to remove NaN lines?

For 2H, in place of 1H, result:

                              open    close      low    high  volume
timestamp                                                           
2015-02-02 08:00:00+05:30  1280.00  1285.65  1280.00  1290.0   12620
2015-02-02 10:00:00+05:30  1285.65  1261.55  1261.20  1294.0   14098
2015-02-02 12:00:00+05:30  1261.30  1263.85  1256.00  1267.0   16848
2015-02-02 14:00:00+05:30  1261.35  1262.00  1256.65  1268.0   15442
2015-02-02 16:00:00+05:30      NaN      NaN      NaN     NaN       0
2015-02-02 18:00:00+05:30      NaN      NaN      NaN     NaN       0

Two problems: 1. NaN rows for outside trading hour. 2. Want it to start from 9AM.

This I think will work, but not sure:

data.groupby(pd.Grouper(freq='2h', offset='1h')).agg({"open":"first","close":"last","low":"min","high":"max","volume":"sum"})

If I try for a 7min or 7H timeframe (just to generalize) offset will not work properly, as 60min is not divisible by 7... It will work for 2min, 3min, 4min, 5min, 6min, etc... Any help, on how to generalize?

pandas python
1 Answers

Grouper and resample output are the same, but if you add origin="2015-02-09 09:00:00", the output will be from the specified date and time. I am getting my Apple data from Yahoo Finance. I am removing the time zone for the data I get. The start time was 9:30, so origin= is set to 9:30.

import yfinance as yf
import pandas as pd

df = yf.download("AAPL", interval='1m', start="2021-03-01", end="2021-03-08")
df.index = pd.to_datetime(df.index)
df.index = df.index.tz_localize(None)

df_2h_origin = df.resample('2h', origin='2021-03-01 09:30:00').agg({"Open":"first","Close":"last","Low":"min","High":"max","Volume":"sum"})
df_2h_origin.dropna(how='any', axis=0, inplace=True)

df_2h_origin.head()

    Open    Close   Low     High    Volume
Datetime                    
2021-03-01 09:30:00     123.500000  125.879898  122.790001  126.154999  46633988
2021-03-01 11:30:00     125.879997  126.209297  125.669998  126.620003  21994558
2021-03-01 13:30:00     126.205002  127.559998  126.089996  127.750000  24004585
2021-03-01 15:30:00     127.565002  127.790001  127.269997  127.930000  11684669
2021-03-02 09:30:00     128.410004  125.959999  125.500000  128.720001  41931239
user_131078040
Related