...

9.12 Parse a string as a numerical value

 
spl-cookbook(224)

Sometimes we need to parse a string as a numerical value.
The model performance table records multiple indicators of each type of model, and we are trying to get the numerical models (whose ModelType is 2) and list them by making indicators the column names. Indicators of each model are input to the file in same order. Below is part of the original data:

ID ModelName ModelType Performance
1 HousePrice 2 SquareR=0.933743
2 HousePrice 2 MSE=295749426.986263
3 HousePrice 2 RMSE=17197.366862
4 HousePrice 2 GINI=0.197449
5 HousePrice 2 MAE=12509.456071
6 HousePrice 2 MAPE=7.798386
7 Titanic 1 GINI=0.654867
8 Titanic 1 AUC=0.827434
9 Titanic 1 KS=0.587658
… … … …

SPL provides number(stringExp) function to parse string stringExp as a numerical value.

SPL script:

A
1 =T(“mps.txt”)
2 =A1.select(ModelType:2)
3 =A2.group(ModelName)
4 =A3(1).(Performance.split(“=”)(1)).concat@c()
5 =create(${“ModelName,”+A4})
6 =A3.(A5.record(A3.~.ModelName | A3.~.(number(Performance.split(“=”)(2)))))

A1 Import model performance data file.
A2 Select records of numerical models.
A3 Group selected records by ModelName.
A4 Concatenate indicator names into a string with comma.
A5 Create an empty table sequence where the first column contains model names, which lead a series of model performance indicators after it.
A6 Insert each group of indicators to A5’s table sequence in loop. Here number() function is used to convert a split string into a numerical value.

Execution result:

ModelName SquareR MSE RMSE GINI MAE MAPE
HousePrice 0.933743 295749426.986263 17197.366862 0.197449 12509.456071 7.798386
… … … … … … …

 

spl-cookbook(224)
 
 •  154 View  •