[Python] Performance comparison: calculating pairwise stock correlations in DolphinDB vs Python

I need to calculate pairwise correlations between 3,000 stocks using minute-level returns derived from high-frequency trade data. I have a working DolphinDB implementation and want to compare its performance with an equivalent Python implementation. DolphinDB Implementation (Reference)

// 1. Calculate minute-level VWAP
priceMatrix = exec wavg(Trade_Price, Trade_Volume)
from trades
where Time between 09:30:00.000000000 : 16:00:00.000000000
pivot by minute(Time) as minute, Symbol

// 2. Forward fill missing values
priceMatrix.ffill!()

// 3. Compute minute-level returns
retMatrix = each(def(x): ratios(x)-1, priceMatrix)

// 4. Calculate pairwise correlations
corrMatrix = cross(corr, retMatrix)


I'm aware of pandas/numpy but need guidance on the most performant approach:

import pandas as pd
import numpy as np
from datetime import datetime, time
import time as tm

# 1. Generate sample trade data (replace with your actual data)
num_stocks = 3000
symbols = [f'S_{i}' for i in range(num_stocks)]
times = pd.date_range("2023-01-01 09:30:00", "2023-01-01 16:00:00", freq="s")

np.random.seed(42)
trades = pd.DataFrame({
'Time': np.random.choice(times, size=10_000_000),
'Symbol': np.random.choice(symbols, size=10_000_000),
'Trade_Price': np.random.uniform(10, 500, size=10_000_000),
'Trade_Volume': np.random.randint(1, 10000, size=10_000_000)
})

# Filter market hours
trades = trades[trades['Time'].dt.time.between(time(9,30), time(16,0))]

start = tm.time()
# 2. Calculate minute-level VWAP
vwap = (trades.groupby([pd.Grouper(key='Time', freq='1min'), 'Symbol'])
.apply(lambda x: np.average(x['Trade_Price'], weights=x['Trade_Volume']))
.unstack()
.ffill())
print(f"VWAP calculation completed in {tm.time() - start:.2f} seconds") # 158.85s

start = tm.time()
# 3. Compute returns
returns = vwap.pct_change()
print(f"Returns calculation completed in {tm.time() - start:.2f} seconds") # 0.25s

start = tm.time()
# 4. Calculate correlation matrix (optimized)
corr_matrix = returns.corr()
print(f"Correlation matrix calculation completed in {tm.time() - start:.2f} seconds") #11.54s


The above Python code executes very slowly when calculating pairwise correlations across 3,000 stocks. Are there more efficient approaches for large-scale correlation computations in Python?

Continue reading...