Data transformer update about spine-toolbox

[LouisFliche]

• During an attempt to translate RTS GMLC data into SpineOpt, I found myself faced with the problem of having a tool to do what is described above. To try and solve this problem, I tried to build the beginnings of a tool to allow such database modifications. This is by no means a tool that aims to become the future data transformer. However, it can give an idea of the useful functionalities in practice.

• First of all, I created an initial tool (cf. the Spine-Toolbox project RTS_To_SpineOpt_Basis), enabling systematic operations on parameters: renaming, multiplying by a constant factor, and between parameters on a SpineOpt database: creation of a parameter equal to the sum of two others, multiplication, division, maximum between two parameters, etc. In an Excel file, which acts as a user interface, you enter the name of the parameters you want to create or modify, the parameters you are using and then the operation you want to perform on these parameters. The python tool in Spine-Toolbox evaluates these different expressions in the Excel file, performs the operations on the specified input database, and creates the output database. In this way, all the mathematical operations of python (and numpy) can easily be performed on parameters in a SpineOpt database.

• However, these operations are performed each time on all the values of the specified parameters. Translating RTS into a SpineOpt format database also required the ability to partially filter/select the input data, for example selecting parameters corresponding only to certain objects (i.e. selecting a parameter according to the value of another parameter), and to perform operations on the different values of the same parameter: maximum of the values taken by this parameter, etc. To do this I added a 'Filters and column operations' section to the previous workflow, upstream of the operations between/among the parameters described above (see the Spine-Toolbox RTS_To_SpineOpt_with_filters project).

• However, the tool in question was very limited. Firstly, it used an Excel file as the user interface, which is undesirable. It also involved string evaluation operations in the code, which was not very practical, not very clean, and could lead to a large number of errors (typically this system was not at all resilient to the slightest typo). Finally, and most importantly, the tool didn't allow us to perform all the operations we would have liked, and which were necessary to translate the database we were trying to translate. In fact, we would have liked to be able to perform all the basic operations on databases: unions, intersections, etc. (all the operations described above). We therefore had to make a real decision about how to go about it (recode all the possible operations in SQL in a dedicated tool, use database management modules in Python or another language, etc.). Finally, the workflow in two distinct parts (essentially: selections, then operations) was not practical.

• In conclusion, this attempt to translate the RTS database (for which you can find the codes here) is an example that shows the importance of such a translation tool, its necessary versatility - it must be capable of performing all the basic operations that can be performed in SQL - and the importance of having a single tool with a dedicated user interface.