Machine Learning in DolphinDB

Download the dataset, and import it to DolphinDB with function loadText.

wineSchema = table(
    `Label`Alcohol`MalicAcid`Ash`AlcalinityOfAsh`Magnesium`TotalPhenols`Flavanoids`NonflavanoidPhenols`Proanthocyanins`ColorIntensity`Hue`OD280_OD315`Proline as name,
    `INT`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE`DOUBLE as type
)
wine = loadText("/home/dataset/wine.data", schema=wineSchema)

The DolphinDB randomForestClassifier function requires the class labels to be integers in [0, numClasses), while the wine dataset uses class labels 1, 2, 3. So we use the following code to process these labels as 0, 1, 2.

update wine set Label = Label - 1

Split the data into training and testing sets in a 7:3 ratio. In this example, the function trainTestSplit is defined to facilitate the split.

def trainTestSplit(x, testRatio) {
    xSize = x.size()
    testSize = xSize * testRatio
    r = (0..(xSize-1)).shuffle()
    return x[r > testSize], x[r <= testSize]
}

wineTrain, wineTest = trainTestSplit(wine, 0.3)
wineTrain.size()    // 124
wineTest.size()     // 54

We perform random forest classification on the training set with function randomForestClassifier. The function has four required parameters:

• ds: The input data source (usually generated using the sqlDS function).
• yColName: The column name of the dependent variable in the data source.
• xColNames: The column names of the dependent variables in the data source.
• numClasses: The number of classes.

model = randomForestClassifier(
    sqlDS(<select * from wineTrain>),
    yColName=`Label,
    xColNames=`Alcohol`MalicAcid`Ash`AlcalinityOfAsh`Magnesium`TotalPhenols`Flavanoids`NonflavanoidPhenols`Proanthocyanins`ColorIntensity`Hue`OD280_OD315`Proline,
    numClasses=3
)

Predict the test set with the trained model:

predicted = model.predict(wineTest)

Examine the prediction accuracy:

sum(predicted == wineTest.Label) \ wineTest.size();

0.925926

Save the trained model to disk using the saveModel function:

model.saveModel("/home/model/wineModel.bin")

You can load the model from disk with loadModel for predictions:

model = loadModel("/home/model/wineModel.bin")
predicted = model.predict(wineTest)

In this example, missing values in the raw data are filled with the ffill function. The first 10 rows of the 10-day moving average and RSI calculation result are empty and we remove these records from output. We use the transDS! function to apply the preprocessing steps to the original data. In this example, calculating RSI uses the ta module of DolphinDB, and the specific usage can be found in DolphinDBModules.

use ta

def preprocess(t) {
    ohlc = select ffill(Open) as Open, ffill(High) as High, ffill(Low) as Low, ffill(Close) as Close from t
    update ohlc set OpenClose = Open - prev(Close), OpenOpen = Open - prev(Open), S_10 = mavg(Close, 10), RSI = ta::rsi(Close, 10), Target = iif(next(Close) > Close, 1, 0)
    update ohlc set Corr = mcorr(Close, S_10, 10)
    return ohlc[10:]
}

After loading the data, generate a data source through function sqlDS, and use transDS! to convert the data source using the preprocessing function preprocess defined above:

ohlc = database("dfs://trades").loadTable("ohlc")
ds = sqlDS(<select * from ohlc>).transDS!(preprocess)

We use function logisticRegression which has three required parameters:

• ds: The input data source (usually generated using the sqlDS function).
• yColName: The column name of the dependent variable in the data source.
• xColNames: The column names of the dependent variables in the data source.

The data source generated in Data Preprocessing can be passed to ds:

model = logisticRegression(ds, `Target, `Open`High`Low`Close`OpenClose`OpenOpen`S_10`RSI`Corr)

Then use the trained model for prediction and check the classification accuracy:

aapl = preprocess(select * from ohlc where Ticker = `AAPL)
predicted = model.predict(aapl)
score = sum(predicted == aapl.Target) \ aapl.size()    // 0.756522

Download the pre-compiled XGBoost plugin from xxxx to your local machine. Then, run loadPlugin(pathToXgboost) in DolphinDB, where pathToXgboost is the path to the downloaded PluginXgboost.txt file:

pathToXgboost = "C:/DolphinDB/plugin/xgboost/PluginXgboost.txt"
loadPlugin(pathToXgboost)

We also use the wine dataset for model training in this example. The training method xgboost::train(Y, X, [params], [numBoostRound=10], [xgbModel]) is used. The Label column of the wineTrain set is taken as the input Y, and the other columns are kept as X.

Y = exec Label from wineTrain
X = select Alcohol, MalicAcid, Ash, AlcalinityOfAsh, Magnesium, TotalPhenols, Flavanoids, NonflavanoidPhenols, Proanthocyanins, ColorIntensity, Hue, OD280_OD315, Proline from wineTrain

Before training the model, we need to specify a dictionary for params. We will train a multi-classification model so the objective is set to "multi-softmax" and the number of classification num_class is set to 3. You can refer to XGBoost doc for the parameter descriptions.

Parameters in this example are set as below:

params = {
    objective: "multi:softmax",
    num_class: 3,
    max_depth: 5,
    eta: 0.1,
    subsample: 0.9
}

Train the model, predict and calculate the classification accuracy:

model = xgboost::train(Y, X, params)

testX = select Alcohol, MalicAcid, Ash, AlcalinityOfAsh, Magnesium, TotalPhenols, Flavanoids, NonflavanoidPhenols, Proanthocyanins, ColorIntensity, Hue, OD280_OD315, Proline from wineTest
predicted = xgboost::predict(model, testX)

sum(predicted == wineTest.Label) \ wineTest.size()    // 0.962963

Similarly, the model can be persisted to or loaded from disk:

xgboost::saveModel(model, "xgboost001.mdl")

model = xgboost::loadModel("xgboost001.mdl")

By specifying the xgbModel parameter of xgboost::train, incremental training can be performed on an existing model:

model = xgboost::train(Y, X, params, , model)