The Xpress Mosel language has evolved considerably over more than 20 years since its first release in 2001. Whilst continuously building out the available optimization features or adding support for new solvers (such as Constraint Programming with Xpress Kalis or Global Optimization), Mosel is increasingly used for the implementation of large-scale projects and general programming tasks. Therefore, recent releases of Xpress Mosel have built out the programming capabilities of the language. Having introduced the concepts of union types and reflection in the previous blog in this series, we are now going to take a closer look at various new data handling and programming features that make use of union types.
Efficient reading of large-scale CSV-format files through ’diskdata‘
Mosel provides several different methods for reading (and writing) CSV-format text files:
When populating single- or multidimensional Mosel arrays from external sources, we usually work with the following standard assumptions (see for example duodd.mos ):
Dataframe format for Excel spreadsheets
Mosel 6.6 extends the spreadsheet reading and writing capabilities of the xls, xlsx, and csv drivers of mmsheet to support dataframe format data (see example ‘dataframesht.mos’). Dataframe format data assumes that arrays are indexed by a single, common index to multiple arrays (each held in a table column). The Mosel array that receives the dataframe has two indices, a range for the row count and a set of string corresponding to the column titles (or simply a second range if no column titles are given like the array ‘DfArrT2’ in this example).
The type of the Mosel array will generally be either ‘text’ (untyped textual representation) or the container type ‘any’ that identifies the data types automatically while reading the input data. The ‘dataframe’ option can be combined with the usual range definition and column selection syntax of the mmsheet drivers.
If the data type is known upfront and homogeneous across the selection of columns to be input, then a suitable specific type may be defined for the Mosel array:
Another frequently occurring case is the representation of multidimensional data arrays in the form of a (spreadsheet) table that has index columns for N-1 indices, writing the last index ‘across the columns’. The resulting table has some similarities with the dataframe format (one set of index values is given by the column headers), however, the data range contains an additional N-1 columns for the other index sets. The spreadsheet drivers provide the option ‘partndx’ for reading and writing data formatted this way, where it is assumed that the index range for the last index is known upfront. The screenshot below is taken from the example ‘threedimarr.mos’. Please also refer to the example ‘indexeg.mos’ that shows various ways of formatting data, in particular within spreadsheets or CSV-format files, with their corresponding I/O driver options.
Dataframe format with ODBC-enabled databases
The dataframe format has also been extended to databases accessed through ODBC. Again, the user can elect to work with specifically typed arrays (that need to match types of the corresponding database fields) or the container type ‚any‘, where data types will be populated directly from the database type information while reading the database table contents. The following code snippet (see example dataframedb.mos) shows some of the available functionality for reading a database table into different types of two-dimensional Mosel arrays where the line count serves as the first index and database field names provide the value for the second index:
New functionality for handling JSON-format files
JSON is a text data format that structures data in hierarchical fashion, with entries of the form ‘label: value’ where ‘value’ can be either a scalar (textual or numerical value or constant NULL), a collection (surrounded by ‘{}’) or a list (surrounded by ‘[]’) of JSON objects. JSON parsing functionality has been available from the Mosel module mmxml (resulting in an internal representation as an XML document) for more than 15 years. With the increasing use of JSON format files in the context of HTTP queries, the Mosel module mmhtml now defines subroutines jsonread and jsonwrite for reading and writing Mosel entities in JSON format. In many cases, JSON data is best matched with record structures in Mosel. Assume that we wish to read this file (‘bookexamples.json’) that contains a list of directories with model example and data files into Mosel data structures:
When reading JSON data via the subroutine jsonread of mmhttp, the implicit assumption is made that the structure of data to be read is known. This is indeed typically the case when reading JSON attachments sent by a specific app or webservice. This code snippet is an extract of the example readjson.mos:
Alternatively, if the contents of the JSON file is a priori completely unknown, the package json implements JSON reading functionality based on union types that supports reading (and writing) arbitrary JSON format data. After populating the union structure with input data it can be enumerated using standard union-based access routines as shown in this example:
The functionality for outputting JSON-format data has equally been improved: the jsonwrite subroutine of mmhttp defines additional formatting options and it now also supports special values:
The package json provides functionality for serializing JSON collections (represented in Mosel by an entity of type ‘jarr’), resulting in a configurable table-format output. For example, if we read a data file with this contents:
We can display the data that is read via the functionality of the json package in table format using different formatting options:
To access training material on modeling and problem solving with Xpress Mosel, follow this link.
Self-learning videos about Xpress Mosel are available here.
If you are interested in learning more from Susanne make sure to check out her most recent series on new modeling constructs in Mosel that allow for an easier formulation of MIP models and the contribution ‘Xpress Mosel: A 20 Year Success Story’.
In case you haven’t already, make sure to subscribe to our Optimization blog to be notified of more content on this topic. - Look out for the next contribution in this series that will talk about ‚new solving and maths features in Mosel 6‘.
Efficient reading of large-scale CSV-format files through ’diskdata‘
Mosel provides several different methods for reading (and writing) CSV-format text files:
- The ‘diskdata’ functionality of module mmetc provides a means for reading and writing CSV-format based on linewise processing, that is, avoiding the upfront loading of a file into memory, which makes this implementation particularly suitable for large-scale data files.
- The I/O driver ‚mmsheet.csv‘ implements access functionality for CSV-format files in alignment with the handling of spreadsheet files (formats XLS and XLSX) that is equally provided through this module: a file accessed through the mmsheet drivers is fully loaded into memory, providing a convenient means for repeated accesses to the same file, such as populating several Mosel data arrays through independent read statements involving non-trivial selections. This case is discussed in the next section.
- Free-format reading and writing / parsing using Mosel’s programming capabilities: this option makes it possible to adapt your data input or output to very specific formatting or error handling requirements that may not be covered by the previous two options, see for example file readcsv.mos.
When populating single- or multidimensional Mosel arrays from external sources, we usually work with the following standard assumptions (see for example duodd.mos ):
- The type of the data to be read is known (such as numerical, Boolean, textual, date/time values)
- The index values are either explicitly defined in the data source (sparse data format) or known/fixed upfront in the Mosel model. In this case it is sufficient to specify just the actual values in the data source (dense data format).
Dataframe format for Excel spreadsheets
Mosel 6.6 extends the spreadsheet reading and writing capabilities of the xls, xlsx, and csv drivers of mmsheet to support dataframe format data (see example ‘dataframesht.mos’). Dataframe format data assumes that arrays are indexed by a single, common index to multiple arrays (each held in a table column). The Mosel array that receives the dataframe has two indices, a range for the row count and a set of string corresponding to the column titles (or simply a second range if no column titles are given like the array ‘DfArrT2’ in this example).
The type of the Mosel array will generally be either ‘text’ (untyped textual representation) or the container type ‘any’ that identifies the data types automatically while reading the input data. The ‘dataframe’ option can be combined with the usual range definition and column selection syntax of the mmsheet drivers.
If the data type is known upfront and homogeneous across the selection of columns to be input, then a suitable specific type may be defined for the Mosel array:
Another frequently occurring case is the representation of multidimensional data arrays in the form of a (spreadsheet) table that has index columns for N-1 indices, writing the last index ‘across the columns’. The resulting table has some similarities with the dataframe format (one set of index values is given by the column headers), however, the data range contains an additional N-1 columns for the other index sets. The spreadsheet drivers provide the option ‘partndx’ for reading and writing data formatted this way, where it is assumed that the index range for the last index is known upfront. The screenshot below is taken from the example ‘threedimarr.mos’. Please also refer to the example ‘indexeg.mos’ that shows various ways of formatting data, in particular within spreadsheets or CSV-format files, with their corresponding I/O driver options.
Dataframe format with ODBC-enabled databases
The dataframe format has also been extended to databases accessed through ODBC. Again, the user can elect to work with specifically typed arrays (that need to match types of the corresponding database fields) or the container type ‚any‘, where data types will be populated directly from the database type information while reading the database table contents. The following code snippet (see example dataframedb.mos) shows some of the available functionality for reading a database table into different types of two-dimensional Mosel arrays where the line count serves as the first index and database field names provide the value for the second index:
New functionality for handling JSON-format files
JSON is a text data format that structures data in hierarchical fashion, with entries of the form ‘label: value’ where ‘value’ can be either a scalar (textual or numerical value or constant NULL), a collection (surrounded by ‘{}’) or a list (surrounded by ‘[]’) of JSON objects. JSON parsing functionality has been available from the Mosel module mmxml (resulting in an internal representation as an XML document) for more than 15 years. With the increasing use of JSON format files in the context of HTTP queries, the Mosel module mmhtml now defines subroutines jsonread and jsonwrite for reading and writing Mosel entities in JSON format. In many cases, JSON data is best matched with record structures in Mosel. Assume that we wish to read this file (‘bookexamples.json’) that contains a list of directories with model example and data files into Mosel data structures:
When reading JSON data via the subroutine jsonread of mmhttp, the implicit assumption is made that the structure of data to be read is known. This is indeed typically the case when reading JSON attachments sent by a specific app or webservice. This code snippet is an extract of the example readjson.mos:
Alternatively, if the contents of the JSON file is a priori completely unknown, the package json implements JSON reading functionality based on union types that supports reading (and writing) arbitrary JSON format data. After populating the union structure with input data it can be enumerated using standard union-based access routines as shown in this example:
The functionality for outputting JSON-format data has equally been improved: the jsonwrite subroutine of mmhttp defines additional formatting options and it now also supports special values:
The package json provides functionality for serializing JSON collections (represented in Mosel by an entity of type ‘jarr’), resulting in a configurable table-format output. For example, if we read a data file with this contents:
We can display the data that is read via the functionality of the json package in table format using different formatting options:
To access training material on modeling and problem solving with Xpress Mosel, follow this link.
Self-learning videos about Xpress Mosel are available here.
If you are interested in learning more from Susanne make sure to check out her most recent series on new modeling constructs in Mosel that allow for an easier formulation of MIP models and the contribution ‘Xpress Mosel: A 20 Year Success Story’.
In case you haven’t already, make sure to subscribe to our Optimization blog to be notified of more content on this topic. - Look out for the next contribution in this series that will talk about ‚new solving and maths features in Mosel 6‘.
Updated 2 years ago
Version 3.0