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

Question

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

Culvert

Welcome to SO. What's your time stamp format? How do you want to agg data after resampling? What's your logic? – Courses

@ShubhamSharma Time format is YYYY-MM-DD-HH-mm, what exactly do you mean by agg? – Culvert

I mean how do you exactly obtained the values 7.589 7.593 7.589 7.593 for 201901011700 – Courses

@ShubhamSharma Open is the first price in the timeframe, high is the highest price, low is the lowest price, close is the last price before a new timeframe. – Culvert

Answer 1

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

Tiller

I don't know if it's too much to ask, but how do you convert your index from type Int64 to a time index. The index is formatted like a timestamp (YYYYMMDDHHmm), the type is just Int64 instead of a timestamp format. (I found a solution for converting Unix time, but can't find anything for this) – Culvert

Added to the code. If this answers your question, could you please accept it as an answer for future generations? – Tiller

Thank you so much! It works perfectly :D. I've marked your answer as correct! – Culvert

thank you for the answer, it helped me as well – Ephesian

Thank you. I was looking for this. Short, sweet, precise solution! – Chifley

Answer 2

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)

Pericardium

Nice it works. Many thanks. – Densimeter