CODEWITHSUNDEEP
pythonpandasdataframecurrencyfinance
Vindrue
Vindrue

Reputation: 93

How do you convert 1 minute open-high-low-close data to another timeframe(fx: 5 minute, 1 hour) in Python?

I'm pretty new to Python and StackOverflow so bear with me if I make mistakes in this post.

I have a Pandas dataframe with 1 minute open, high, low, and close data, with time as the index, for a currency. How would I go about turning it into a dataframe with, for example, 5-minute open, high, low, close data, and make the timestamp fit too? Here is an example of the 1-minute data printed out:

                   ZARJPY_open  ZARJPY_high  ZARJPY_low  ZARJPY_close
time                                                            
201901011700        7.589        7.589       7.589         7.589
201901011701        7.590        7.590       7.590         7.590
201901011702        7.589        7.590       7.589         7.589
201901011703        7.590        7.593       7.590         7.593
201901011705        7.592        7.593       7.592         7.593

I would like to turn this into:

                  ZARJPY_open  ZARJPY_high  ZARJPY_low  ZARJPY_close
time                                                            
201901011700        7.589        7.593       7.589         7.593
201901011706                  -next 5 minutes-

Any help is appreciated :)

Edit: Time stamp is in YYYYMMDDHHmm (year, month, day, hour, minute) format

Upvotes: 7

Views: 8987

Answers (2)

martinho
martinho

Reputation: 3379

Use pandas resample() method:

# convert 1 min OHLC to 5 min OHLC
df = df.resample('5Min').agg({'Open': 'first', 'High': 'max', 'Low': 'min', 'Close': 'last'})
df = df.dropna()
print(df)

Upvotes: 1

Roy2012
Roy2012

Reputation: 12503

You can use a 5-minute grouper object: 

# parse the time. 
df.time = pd.to_datetime(df.time, format="%Y%m%d%H%M")

#make the time the index. 
df = df.set_index("time")

# group in 5-minute chunks. 
t = df.groupby(pd.Grouper(freq='5Min')).agg({"ZARJPY_open": "first", 
                                             "ZARJPY_close": "last", 
                                             "ZARJPY_low": "min", 
                                             "ZARJPY_high": "max"})
t.columns = ["open", "close", "low", "high"]
print(t)

The result is: 

                      open  close    low   high
time                                           
2019-01-01 17:00:00  7.589  7.593  7.589  7.593
2019-01-01 17:05:00  7.592  7.593  7.592  7.593

Upvotes: 18

Related Questions