How to calculate the z-scores of factors in multiple columns in DolphinDB?

Question

I have a table with a column trade_date and multiple factor columns. I would like to standardize these factor columns using the funciton zscore(), with trade-date as the grouping basis.

How can I pass these columns in batch?

Answer 1

Let's say you have a table tbl with columns trade_date, f1, and f2. You can use update function to group the table by the trade_date column and calculate the z-scores for each factor column using the zscore() function. The result includes new columns f1_zscore and f2_zscore which are standardized values of f1 and f2.

update tbl by trade_date, f1_zscore:zscore(f1), f2_zscore:zscore(f2) from tbl

Answer 2

You can refer to the following methods:

For single column:

select zscore(factor1) from t context by trade_date

For multiple columns:

(1) in-memory table:

contextby(zscore, t, t.trade_date)

(2) Otherwise, use meta-programming:

t = table(100:0, [`trade_date, `symbol, `f1,`f2,`f3,`f4,`f5], [DATE, SYMBOL, DOUBLE, DOUBLE, DOUBLE, DOUBLE, DOUBLE])
otherCol = [`trade_date, `symbol]
colName = t.colNames()
factorCol = colName[not colName in otherCol]
sql(select=sqlCol(factorCol, zscore, "re_" + factorCol), from=objByName("t"), groupBy=sqlCol("trade_date"), groupFlag=0)