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
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