[Python] How to calculate rolling sum of trade quantities and time differences in DolphinDB?

I’m working with a trade dataset containing timestamps, quantities, and prices. I need to calculate:

Rolling sum of quantities for every 100-trade window (with step size of 1 trade) Time difference between first and last trade in each window Here’s my Python implementation using pandas:

import pandas as pd

data = {
'timestamp': pd.to_datetime(['09:34:07', '09:36:42', '09:36:51', '09:36:59', '09:32:47', '09:35:26', '09:34:16', '09:34:26', '09:38:12'], format='%H:%M:%S'),
'sym': ['C', 'MS', 'MS', 'MS', 'IBM', 'IBM', 'C', 'C', 'C'],
'qty': [2200, 1900, 2100, 3200, 6800, 5400, 1300, 2500, 8800],
'price': [49.6, 29.46, 29.52, 30.02, 174.97, 175.23, 50.76, 50.32, 51.29]
}

df = pd.DataFrame(data)
window_size = 2

# Calculate rolling sum
df['qty_sum'] = df.groupby('sym')['qty'].rolling(window=window_size, min_periods=1).sum().reset_index(level=0, drop=True)

# Calculate time differences
def calculate_time_delta(group, window_size):
tDelta = [pd.Timedelta(0)] * len(group)
for i in range(window_size - 1, len(group)):
tDelta = group['timestamp'].iloc - group['timestamp'].iloc[i - window_size + 1]
return pd.Series(tDelta, index=group.index)

df['tDelta'] = df.groupby('sym').apply(lambda group: calculate_time_delta(group, window_size)).reset_index(level=0, drop=True)


Now I need to implement this in DolphinDB. My table is defined as:

sym = `C`MS`MS`MS`IBM`IBM`C`C`C$SYMBOL
price = 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t1 = table(timestamp, sym, qty, price)


My questions are:

1. How to calculate the rolling sum of quantities grouped by sym with a window size of N trades?
2. How to compute the time difference between first and last trade in each window?
3. The window should move forward by 1 trade at each step (step size = 1)

I’m particularly interested in the most efficient way to do this in DolphinDB, as my actual dataset is quite large.

Continue reading...