[Python] Merge hourly data with 15 minute data

By using very inefficient string manipulation (replace the minute with zeros, i.e. '06:15:00' -> '06:00:00'), I am able to merge hourly data with the 15 minute data. I am wondering if there is a more elegant way of merging the data.

Thanks in advance!

import ccxt
import pandas as pd

ex = ccxt.binance({'enableRateLimit': True})

df_15m = pd.DataFrame(ex.fetch_ohlcv(symbol='BTC/USDT', timeframe='15m', limit=9), columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])
df_1h = pd.DataFrame(ex.fetch_ohlcv(symbol='BTC/USDT', timeframe='1h', limit=3), columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])

df_15m = df_15m.loc[:, ['timestamp', 'close']]
df_1h = df_1h.loc[:, ['timestamp', 'close']]

df_15m['timestamp'] = pd.to_datetime(df_15m['timestamp'], unit='ms')
df_1h['timestamp'] = pd.to_datetime(df_1h['timestamp'], unit='ms')

df_15m['timestamp_h'] = df_15m['timestamp'].astype("string").str[:14] + '00:00'
df_1h.rename(columns={"timestamp": "timestamp_h"}, inplace=True)
df_1h['timestamp_h'] = df_1h['timestamp_h'].astype("string")

df_15m.rename(columns={"close": "close_15m"}, inplace=True)
df_1h.rename(columns={"close": "close_h"}, inplace=True)

print('Hourly Data:\n', df_1h, '\n')
print('15m Data:\n', df_15m, '\n')

df_merged = pd.merge(left=df_15m, right=df_1h, how='left', on=['timestamp_h'])

print('Merged Data:\n', df_merged, '\n')


Output:

Hourly Data:
timestamp_h close_h
0 2022-11-13 05:00:00 16853.68
1 2022-11-13 06:00:00 16684.45
2 2022-11-13 07:00:00 16731.94

15m Data:
timestamp close_15m timestamp_h
0 2022-11-13 05:00:00 16857.53 2022-11-13 05:00:00
1 2022-11-13 05:15:00 16849.16 2022-11-13 05:00:00
2 2022-11-13 05:30:00 16856.41 2022-11-13 05:00:00
3 2022-11-13 05:45:00 16853.68 2022-11-13 05:00:00
4 2022-11-13 06:00:00 16862.98 2022-11-13 06:00:00
5 2022-11-13 06:15:00 16807.98 2022-11-13 06:00:00
6 2022-11-13 06:30:00 16806.79 2022-11-13 06:00:00
7 2022-11-13 06:45:00 16684.45 2022-11-13 06:00:00
8 2022-11-13 07:00:00 16731.94 2022-11-13 07:00:00

Merged Data:
timestamp close_15m timestamp_h close_h
0 2022-11-13 05:00:00 16857.53 2022-11-13 05:00:00 16853.68
1 2022-11-13 05:15:00 16849.16 2022-11-13 05:00:00 16853.68
2 2022-11-13 05:30:00 16856.41 2022-11-13 05:00:00 16853.68
3 2022-11-13 05:45:00 16853.68 2022-11-13 05:00:00 16853.68
4 2022-11-13 06:00:00 16862.98 2022-11-13 06:00:00 16684.45
5 2022-11-13 06:15:00 16807.98 2022-11-13 06:00:00 16684.45
6 2022-11-13 06:30:00 16806.79 2022-11-13 06:00:00 16684.45
7 2022-11-13 06:45:00 16684.45 2022-11-13 06:00:00 16684.45
8 2022-11-13 07:00:00 16731.94 2022-11-13 07:00:00 16731.94

Continue reading...