Panel Data Analysis in DolphinDB

DolphinDB offers a unique feature context by to simplify the time series manipulation in panel data.

When we work with panel data, it is usually not convenient to apply time-series functions on each cross sectional object. Such examples include calculating moving average/volatility for stock returns, and cumulative returns or running maximums of different trading strategies. The group by clause divides data into different groups and generates a record for each group. It is often used with aggregations to show one value per grouped field. To keep the number of records in each group unchanged, other database systems (such as SQL Server and PostgreSQL) adopt window functions, whereas DolphinDB offers context by clause in SQL statements. context by, as well as group by and pivot by, composes the grouped calculation system in DolphinDB. Compared with the window functions, the context by clause is more concise, efficient and powerful, specifically:

• context by can be used with the select clause to query data, or with the update clause to update data.
• Window functions can only group data based on existing fields, while context by can be performed based on calculated fields.
• Window functions of other database systems only include a limited number of functions, whereas context by clause has no limits on what functions can be used. Meanwhile, context by can be used with any expressions, such as a combination of multiple functions.
• context by can be used with the having clause to filter the records within each group.

Examples

(1) Group the data by symbols and apply function ratios to each group to calculate the ratios of trading volume between 2 consecutive trades for each stock:

select timestamp, sym, price, ratios(volume) ,volume from t context by sym;

# output
timestamp sym price  ratios_volume volume
--------- --- ------ ------------- ------
09:34:57  C   50.6                  2200
09:34:59  C   50.62  0.86           1900
09:35:01  C   50.63  1.106          2100
09:35:02  C   50.64  1.52           3200
09:35:01  IBM 174.97                2500
09:35:02  IBM 175.02 3.52           8800
09:34:57  MS  29.46                 6800
09:34:59  MS  29.48  0.79           5400
09:35:01  MS  29.5   0.24           1300

(2) Calculate moving average price of each stock for every 3 trades with the m-function mavg:

select *, mavg(price,3) from t context by sym;

# output
sym timestamp price  volume mavg_price
--- --------- ------ ------ -----------
C   09:34:57  50.60  2200
C   09:34:59  50.62  1900
C   09:35:01  50.63  2100   50.62
C   09:35:02  50.64  3200   50.63
IBM 09:35:01  174.97 2500
IBM 09:35:02  175.02 8800
MS  09:34:57  29.46  6800
MS  09:34:59  29.48  5400
MS  09:35:01  29.50  1300   29.48

(3) Calculate the cumulative maximum trading volume of each stock with the cum-function cummax:

select timestamp, sym, price,volume, cummax(volume) from t context by sym;

# output
timestamp sym price  volume cummax_volume
--------- --- ------ ------ -------------
09:34:57  C   50.6   2200   2200
09:34:59  C   50.62  1900   2200
09:35:01  C   50.63  2100   2200
09:35:02  C   50.64  3200   3200
09:35:01  IBM 174.97 2500   2500
09:35:02  IBM 175.02 8800   8800
09:34:57  MS  29.46  6800   6800
09:34:59  MS  29.48  5400   6800
09:35:01  MS  29.5   1300   6800

(4) Calculate the maximum trading volume for each stock every minute with the aggregate function max, and then append the result as a new column to the input table.

select *, max(volume) from t context by sym, timestamp.minute();

# output
sym timestamp price  volume max_volume
--- --------- ------ ------ ----------
C   09:34:57  50.61  2200   2200
C   09:34:59  50.62  1900   2200
C   09:35:01  50.63  2100   3200
C   09:35:02  50.64  3200   3200
IBM 09:35:01  174.97 2500   8800
IBM 09:35:02  175.02 8800   8800
MS  09:34:57  29.46  6800   6800
MS  09:34:59  29.48  5400   6800
MS  09:35:01  29.5   1300   1300

The pivot by clause rearranges a column (or multiple columns) of a table in two dimensions. It generates a table with select statement or a matrix with exec statement. As the following example shows, each column in the rearranged table represents a stock and the records are sorted based on the column "timestamp".

select price from t pivot by timestamp, sym;

# output
timestamp C     IBM    MS
--------- ----- ------ -----
09:34:57  50.6         29.46
09:34:59  50.62        29.48
09:35:01  50.63 174.97 29.5
09:35:02  50.64 175.02

pivot by can be used with an aggregate function. For example, you can use pivot by and select to generate a table of the average closing price per minute.

select avg(price) from t where sym in `C`IBM pivot by minute(timestamp) as minute, sym;

# output
minute C      IBM
------ ------ -------
09:34m 50.61
09:35m 50.635 174.995

Alternatively, you can use pivot by with exec to generate a matrix:

resM = exec avg(price) from t where sym in `C`IBM pivot by minute(timestamp) as minute, sym;
resM

# output
       C      IBM
       ------ -------
09:34m|50.61
09:35m|50.635 174.995

Check the data form of "resM" with typestr:

typestr(resM)

# output
FAST DOUBLE MATRIX

The following examples show how to deal with panel data in a matrix.

(1) Calculate the changes in stock prices for each stock with function deltas.

price = panel(t.timestamp, t.sym, t.price);
deltas(price);
 
# output
         C    IBM  MS
         ---- ---- -----
09:34:57|
09:34:59|0.02      0.02
09:35:01|0.01      0.02
09:35:02|0.01 0.05

(2) Calculate the average prices of a stock within every two timestamps with the moving function mavg.

mavg(price,2);
 
# output
         C      IBM      MS
         ------ ------ -----------
09:34:57|
09:34:59|50.61         29.47
09:35:01|50.63  174.97 29.49
09:35:02|50.63  175.00 29.50

(3) Calculate the cumulative rank of prices of each stock with the cumulative function cumrank.

cumrank(price);
 
# output
         C IBM MS
         - --- --
09:34:57|0     0
09:34:59|1     1
09:35:01|2 0   2
09:35:02|3 1

(4) Return the lowest price of each stock with the aggregate function min.

min(price);
 
# output
[50.60,174.97,29.46]

(5) Return the lowest price at each timestamp with the aggregate function rowMin.

rowMin(price);
 
 # output
[29.46,29.48,29.5,50.64]

Binary operations on standard matrices (without labels) are performed on the corresponding elements, which requires the matrices to be of the same shape. DolphinDB provides indexed matrix and indexed series, and the shapes of the matrices do not have to be the same.

• indexed matrix: a matrix with row/column labels as index
• indexed series: a vector with label as index

Before version 1.30.20/2.00.8, indexed series or indexed matrices are used for aligned calculation where the row or column labels (index) must be strictly increasing. When binary operations are performed on indexed series and indexed matrices, the data is aligned with outer join for calculation. Since version 1.30.20/2.00.8, DolphinDB provides function align for more alignment methods.

(1) Binary operations on 2 indexed series:

Data is automatically aligned based on the index.

index1 = 2020.11.01..2020.11.06;
value1 = 1..6;
s1 = indexedSeries(index1, value1);
 
index2 = 2020.11.04..2020.11.09;
value2 =4..9;
s2 = indexedSeries(index2, value2);
 
s1+s2;
 
 # output
           #0
           --
2020.11.01|
2020.11.02|
2020.11.03|
2020.11.04|8
2020.11.05|10
2020.11.06|12
2020.11.07|
2020.11.08|
2020.11.09|

(2) Binary operations on indexed matrices:

Alignment of 2 indexed matrices are the same as that on indexed series.

id1 = 2020.11.01..2020.11.06;
m1 = matrix(1..6, 7..12, 13..18).rename!(id1, `a`b`d)
m1.setIndexedMatrix!()
 
id2 = 2020.11.04..2020.11.09;
m2 = matrix(4..9, 10..15, 16..21).rename!(id2, `a`b`c)
m2.setIndexedMatrix!()
 
m1+m2;
 
 # output
           a  b  c d
           -- -- - -
2020.11.01|        
2020.11.02|        
2020.11.03|        
2020.11.04|8  20   
2020.11.05|10 22   
2020.11.06|12 24   
2020.11.07|        
2020.11.08|        
2020.11.09|

(3) Binary operations on an indexed series and an indexed matrix:

It aligns the data based on the row labels, and the calculation is conducted on the indexed series with each column of the indexed matrix.

m1=matrix(1..6, 11..16);
m1.rename!(2020.11.04..2020.11.09, `A`B);
m1.setIndexedMatrix!();
m1;
 
 # output
           A B
           - --
2020.11.04|1 11
2020.11.05|2 12
2020.11.06|3 13
2020.11.07|4 14
2020.11.08|5 15
2020.11.09|6 16
 
s1;
 
 # output
           #0
           --
2020.11.01|1
2020.11.02|2
2020.11.03|3
2020.11.04|4
2020.11.05|5
2020.11.06|6
 
m1 + s1;
 
 # output
           A B
           - --
2020.11.01|
2020.11.02|
2020.11.03|
2020.11.04|5 15
2020.11.05|7 17
2020.11.06|9 19
2020.11.07|
2020.11.08|
2020.11.09|

(4) Alignment of matrices with align:

x1 = [09:00:00, 09:00:01, 09:00:03]
x2 = [09:00:00, 09:00:03, 09:00:03, 09:00:04]
y1 = `a`a`b
y2 = `a`b`b
m1 = matrix(1 2 3, 2 3 4, 3 4 5).rename!(y1,x1)
m2 = matrix(11 12 13, 12 13 14, 13 14 15, 14 15 16).rename!(y2,x2)
a, b = align(m1, m2, 'ej,aj', false);
a;
 
# output
  09:00:00 09:00:01 09:00:03
  -------- -------- --------
a|1        2        3      
a|2        3        4      
b|3        4        5     
 
b;
 
# output
  09:00:00 09:00:01 09:00:03
  -------- -------- --------
a|11       11       13     
b|12       12       14     
b|13       13       15

DolphinDB built-in functions resample and asfreq are used for frequency conversion and resampling on the indexed series or indexed matrix with temporal index.

The following examples use SQL queries and vectorized functions respectively to calculate Alpha 98 in DolphinDB.

• Use DolphinDB SQL queries:

def alpha98(stock){
	t = select securityid, tradetime, vwap, open, mavg(vol, 5) as adv5, mavg(vol,15) as adv15 from stock context by securityid
	update t set rank_open = rank(open), rank_adv15 = rank(adv15) context by tradetime
	update t set decay7 = mavg(mcorr(vwap, msum(adv5, 26), 5), 1..7), decay8 = mavg(mrank(9 - mimin(mcorr(rank_open, rank_adv15, 21), 9), true, 7), 1..8) context by securityid
	return select securityid, tradetime, rank(decay7)-rank(decay8) as A98 from t context by tradetime
}

t = loadTable("dfs://k_day_level","k_day")
timer alpha98(t)

• Use DolphinDB vectorized functions:

def myrank(x){
	return rowRank(x)\x.columns()
}

def alphaPanel98(vwap, open, vol){
	return myrank(mavg(mcorr(vwap, msum(mavg(vol, 5), 26), 5), 1..7)) - myrank(mavg(mrank(9 - mimin(mcorr(myrank(open), myrank(mavg(vol, 15)), 21), 9), true, 7), 1..8))
}
t = select * from loadTable("dfs://k_day_level","k_day")
timer vwap, open, vol = panel(t.tradetime, t.securityid, [t.vwap, t.open, t.vol])
timer res = alphaPanel98(vwap, open, vol)

The second script using vectorized functions is more concise.

To calculate Alpha 98 for a stock in a day, we need all stocks' data on the day as well as the stock's data in previous days. Therefore, using a matrix to calculate Alpha 98 is simpler as there is no need for dimensional transformations of the intermediate result or the final result. As for the calculation of Alpha 98 with SQL queries, nested functions and grouped calculations are involved. In conclusion, the script using function panel is cleaner and more efficient.

Both scripts are executed with a single thread. The first script with SQL queries takes 610 milliseconds, whereas the second script using a matrix takes 510 milliseconds (function panel takes 70 milliseconds to create the panel data and the calculation takes 440 milliseconds).

The performance of panel analysis with vectorized functions is slightly better. Panel data represented by matrices cannot be regrouped, and users cannot observe multiple indicators at the same time. As for panel data represented by tables, you can use SQL statements to group data by multiple columns and query joint columns, which is more suitable for parallel computing with a large volume of data.

Part of the pandas script of Alpha 98 calculation is shown as follows.

 def myrank(x):
    return ((x.rank(axis=1,method='min'))-1)/x.shape[1]

def imin(x):
    return np.where(x==min(x))[0][0]


def rank(x):
    s = pd.Series(x)
    return (s.rank(ascending=True, method="min")[len(s)-1])-1


def alpha98(vwap, open, vol):
    return myrank(vwap.rolling(5).corr(vol.rolling(5).mean().rolling(26).sum()).rolling(7).apply(lambda x: np.sum(np.arange(1, 8)*x)/np.sum(np.arange(1, 8)))) - myrank((9 - myrank(open).rolling(21).corr(myrank(vol.rolling(15).mean())).rolling(9).apply(imin)).rolling(7).apply(rank).rolling(8).apply(lambda x: np.sum(np.arange(1, 9)*x)/np.sum(np.arange(1, 9))))
start_time = time.time()
re=alpha98(vwap, open, vol)
print("--- %s seconds ---" % (time.time() - start_time))

The pandas script takes 520 seconds to calculate Alpha 98, which is approximately 1000 times slower than using a DolphinDB matrix.

DolphinDB provides optimized built-in functions for time-series processing, such as m-functions mavg, mcorr, mrank, mimin, and msum used above. These built-in functions outperform other database systems by 1-2 orders of magnitude. In particular, the calculation of mcorr takes only 0.6 seconds in DolphinDB, over 200 times faster than 142 seconds in pandas. The comparison tests reduce measurement errors by repeating the execution 10 times on closing prices in 10 years (around 5.3 million records).