0

I have intraday OHLCV data on several coins on the 4H timeframe that I want to convert to the daily timeframe. I am new to Python so I don't know where to proceed next(my focus right now is studying sql). So here's the sample of the dataframe:

Pair Date Open High Low Close Volume
BTCUSDT 2021-06-30 08:00:00 35000 36100 34000 35500 10000000
2021-06-30 12:00:00 35505 37000 35400 35600 12000000
2021-06-30 16:00:00 35650 42000 32000 41000 20000000
2021-06-30 20:00:00 41100 41500 38000 39000 15000000
ETCUSDT 2021-06-30 08:00:00 3500 3800 3200 3300 1000000
2021-06-30 12:00:00 3350 3600 3100 3200 730000
2021-06-30 16:00:00 3270 4000 3200 3800 2000000
2021-06-30 20:00:00 3900 4300 3800 4200 3000000

So for the dataframe, I used "Pair" and "Date" as multiindex. There are over 300 pairs in the actual data. Tried searching for similar questions but unlike my question, the others had only one pair, or for those with multiple pairs, they don't have the OHLCV data. They just only have the close or mean price.

Here is the result I want:

Pair Date Open High Low Close Volume
BTCUSDT 2021-06-30 35000 42000 32000 39000 57000000
ETCUSDT 2021-06-30 3500 4300 3100 4200 6730000
jomx99
  • 3
  • 4
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community May 09 '22 at 20:17
  • Does this answer your question? [Converting OHLC stock data into a different timeframe with python and pandas](https://stackoverflow.com/questions/9944436/converting-ohlc-stock-data-into-a-different-timeframe-with-python-and-pandas) – bl79 Mar 29 '23 at 22:04

1 Answers1

1

Might be better to create your own classes to feed the data to and manage the data (appending data to list within class so you know the open (first data point) and close (last data point), high and low are just max and min, volume is sum), but to keep within pandas you can calculate these individually after removing the time part of the Date field, then concat together at end.

df['Date'] = df['Date'].apply(lambda x: x[:10])

# Open
o = df.groupby(['Pair', 'Date']).agg('first')['Open']

# Close
c = df.groupby(['Pair', 'Date']).agg('last')['Close']

# High
h = df.groupby(['Pair', 'Date']).agg('max')['High']

# Low
l = df.groupby(['Pair', 'Date']).agg('min')['Low']

# Volume
v = df.groupby(['Pair', 'Date']).agg('sum')['Volume']

final_df = pd.concat([o, h, l, c, v], axis=1)

If converting to weekly timeframe, use this to parse dates in dataframe but use the same code to calculate OHLCV

from datetime import datetime, timedelta

def convert_to_week(dt):
    d = datetime.strptime(dt[:10], '%Y-%m-%d')
    d = d - timedelta(days=d.weekday()+1)
    return d.strftime('%Y-%m-%d')

df['Date'] = df['Date'].apply(lambda x: convert_to_week(x))
Jacob Warren
  • 48
  • 7