# Panel Data Analysis in DolphinDB

Panel data is a two-dimensional data form that combines cross-sectional and time-series data. In Python, panel data can be represented by a pandas DataFrame or a NumPy 2d array; In DolphinDB, it can be organized in a table or matrix. This tutorial introduces how to process panel data with SQL statements or vectorized functions in DolphinDB. The comparison test shows that the performance of panel analysis in DolphinDB outperforms Python by over 200 times.

All examples are illustrated based on version 1.30.16/2.00.4.

## 1. Organize and Analyze Panel Data

DolphinDB offers 2 methods of panel analysis by applying

- SQL queries or vectorized functions to tables;
- vectorized functions to matrices.

DolphinDB uses columnar storage for tables and matrices. The following are some commonly-used operators and functions applicable to columns:

- Binary operators: +, -, *, /, ratio, %, &&, ||, &, |, pow
- Order-sensitive functions: ratios, deltas, prev, next, move
- Moving functions: mcount, mavg, msum, mmax, mimax, mimin, mmin, mprod, mstd, mvar, mmed, mpercentile, mrank, mwavg, mwsum, mbeta, mcorr, mcovar
- Cumulative functions: cumcount, cumavg, cumsum, cummax, cummin, cumprod, cumstd, cumvar, cummed, cumpercentile, cumPositiveStreak, cumrank, cumwavg, cumwsum, cumbeta, cumcorr, cumcovar
- Aggregate functions: count, avg, sum, sum2, first, firstNot, last, lastNot, max, min, std, var, med, mode, percentile, atImax, atImin, wavg, wsum, beta, corr, covar
- Row-based functions (applied to rows): rowCount, rowAvg, rowSum, rowSum2, rowProd, rowMax, rowMin, rowStd, rowVar, rowBeta, rowCorr, rowAnd, rowOr, rowXor

## 2. Process Panel Data with SQL Queries

DolphinDB's SQL syntax is similar to the standard SQL language. It extends support to panel analysis, asof join, window join and window functions. This chapter introduces how to process panel data with SQL queries in DolphinDB.

For panel data represented by a DolphinDB table, usually a column represents a variable (such as open, high, low, close, or volume) and a row of record can be viewed as an observation at a timestamp. For calculations involving multiple indicators, data alignment is not needed. However, when the panel analysis requires grouped data (e.g., time-series analysis on stocks, or cross-sectional calculations by time range), grouping must be conducted first. DolphinDB offers SQL keywords `group by`

, `context by`

, and `pivot by`

for data grouping. As grouping data has certain overhead, it is recommended to perform grouped calculations within one SQL query.

The following script simulates a data set with 3 stocks for later use.

```
sym = `C`C`C`C`MS`MS`MS`IBM`IBM
timestamp = [09:34:57,09:34:59,09:35:01,09:35:02,09:34:57,09:34:59,09:35:01,09:35:01,09:35:02]
price= 50.6 50.62 50.63 50.64 29.46 29.48 29.5 174.97 175.02
volume = 2200 1900 2100 3200 6800 5400 1300 2500 8800
t = table(sym, timestamp, price, volume);
t;
# output
sym timestamp price volume
--- --------- ------ ------
C 09:34:57 50.6 2200
C 09:34:59 50.62 1900
C 09:35:01 50.63 2100
C 09:35:02 50.64 3200
MS 09:34:57 29.46 6800
MS 09:34:59 29.48 5400
MS 09:35:01 29.5 1300
IBM 09:35:01 174.97 2500
IBM 09:35:02 175.02 8800
```

### 2.1. 2.1 context by

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
```

### 2.2. 2.2 pivot by

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
```

## 3. Process Panel Data with Vectorized Functions

Use DolphinDB built-in function `panel`

to rearrange one or more columns into a matrix.

Arrange the column price of table t as a matrix:

```
price = panel(t.timestamp, t.sym, t.price);
price;
# output
C IBM MS
----- ------ -----
09:34:57|50.60 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
```

The following script converts the columns price and volume into 2 matrices and returns a tuple:

`price, volume = panel(t.timestamp, t.sym, [t.price, t.volume]);`

You can specify row and column labels for the matrix with function `panel`

. Note that the labels must be composed of distinct values in ascending order. For example:

```
rowLabel = 09:34:59..09:35:02;
colLabel = ["C", "MS"];
volume = panel(t.timestamp, t.sym, t.volume, rowLabel, colLabel);
volume;
# output
C MS
---- ----
09:34:59|1900 5400
09:35:00|
09:35:01|2100 1300
09:35:02|3200
```

Use function `rowNames`

(or `colNames`

) to obtain the row (or column) labels specified by function `panel`

.

```
volume.rowNames();
volume.colNames();
```

We recommend you use matrices rather than tables for panel analysis as DolphinDB supports vectorized and binary operations on matrices with optimal performance.

### 3.1. 3.1 Examples

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]
```

### 3.2. 3.2 Binary Operations on Aligned Matrices

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
```

### 3.3. 3.3 Resample and Frequency Conversion

DolphinDB built-in functions `resample`

and `asfreq`

are used for frequency conversion and resampling on the indexed series or indexed matrix with temporal index.

#### 3.3.1. 3.3.1 resample

The function `resample`

is mainly used for frequency conversion and resampling of time series. An aggregate function must be specified.

Downsample to monthly frequency:

```
index=2020.01.01..2020.06.30;
s=indexedSeries(index, take(1,size(index)));
s.resample("M",sum);
# output
#0
--
2020.01.31|31
2020.02.29|29
2020.03.31|31
2020.04.30|30
2020.05.31|31
2020.06.30|30
```

#### 3.3.2. 3.3.2 asfreq

The function `asfreq`

is used for frequency conversion. Different from `resample`

, aggregate functions cannot be used in function `asfreq`

to process the data. `asfreq`

is usually used to convert low-frequency data to a higher frequency (upsampling) and it is usually used with a filling function.

Upsample the data on daily frequency:

```
index=2020.01.01 2020.01.05 2020.01.10
s=indexedSeries(index, take(1,size(index)));
s.asfreq("D").ffill()
# output
#0
--
2020.01.01|1
2020.01.02|1
2020.01.03|1
2020.01.04|1
2020.01.05|1
2020.01.06|1
2020.01.07|1
2020.01.08|1
2020.01.09|1
2020.01.10|1
```

### 3.4. 3.4 Aggregating Matrices

#### 3.4.1. 3.4.1 Aggregating Columns

The calculations of built-in functions (including vectorized functions, aggregate functions, and window functions) to matrices are conducted based on columns.

For example, apply the function sum to a matrix:

```
m = rand(10, 20)$10:2
sum(m)
# output
[69, 38]
```

Each column is treated as a single vector in calculation.

To apply different arguments to the columns of a matrix, you can use the higher-order function `each`

.

```
m = rand(10, 20)$10:2
m
# output
#0 #1
-- --
0 8
5 2
5 3
6 9
6 4
8 2
9 8
5 6
0 4
4 4
each(msum, m, 3 4)
# output
#0 #1
-- --
10
16 22
17 18
20 18
23 23
22 20
14 20
9 22
```

#### 3.4.2. 3.4.2 Aggregating Rows

DolphinDB provides a higher-order function `byRow`

and built-in row-based functions to perform calculation by row.

For example, apply function `rowCount`

to a matrix:

```
m=matrix([4.5 NULL 1.5, 1.5 4.8 5.9, 4.9 2.0 NULL]);
rowCount(m);
# output
[3,2,2]
```

The function `rowCount`

returns a vector whose length is the same as the number of rows in the original matrix. Each element in the vector indicates the number of non-NULL elements in a row.

In addition to the row-based functions, you can use functions with the higher-order function `byRow`

for row-based calculation. The following example reuses function `mfunc`

and matrix m defined in section 3.4.1.

```
byRow(mfunc{, 0}, m)
# output
[6,5.5,3.5,5,8,4.5,6,7.5,3.5,3.5]
```

#### 3.4.3. 3.4.3 Aggregating in Groups

You can use an SQL `group by`

clause on a table, or function `regroup`

on a matrix to implement aggregation in groups.

For example, aggregate a matrix based on the timestamp label:

```
timestamp = 09:00:00 + rand(10000, 1000).sort!()
id= rand(['st1', 'st2'], 1000)
price = (190 + rand(10.0, 2000))$1000:2
regroup(price, minute(timestamp), avg, true)
```

For a matrix generated with `pivot by`

, you can aggregate it based on the labels obtained with `rowNames`

(or `colNames`

).

```
n=1000
timestamp = 09:00:00 + rand(10000, n).sort!()
id = take(`st1`st2`st3, n)
vol = 100 + rand(10.0, n)
t = table(timestamp, id, vol)
m = exec vol from t pivot by timestamp, id
regroup(m,minute(m.rowNames()), avg)
```

## 4. DolphinDB vs. Python: Performance of Panel Analysis

This chapter uses a complex example to demonstrate how to process panel data efficiently. The paper 101 Formulaic Alphas presents 101 quant trading alphas used by WorldQuant, one of Wall Street's top quantitative hedge funds.

Take Alpha 98 for example:

`alpha_098 = (rank(decay_linear(correlation(((high_0+low_0+open_0+close_0)*0.25), sum(mean(volume_0,5), 26.4719), 4.58418), 7.18088)) -rank(decay_linear(ts_rank(ts_argmin(correlation(rank(open_0), rank(mean(volume_0,15)), 20.8187), 8.62571),6.95668), 8.07206)))`

To compare the performance of the methods mentioned above, we use one year's daily stock data with approximately 1 million records (which can be generated by the panelDataSimulation script).

The table contains the following columns:

- securityid: the stock symbol
- tradetime: the trading time
- vol: the trading volume
- vwap: the weighted average prices
- open: the opening price
- close: the closing price

### 4.1. 4.1 SQL Queries vs. Vectorized Functions

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.

### 4.2. 4.2 DolphinDB vs. pandas

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).