How to create mappings for matrix-oriented data via name-binds-to

Sometimes your data source consists of both column- and row-oriented data, showing data matrix-style. For these setups you can still use a mapping file, using certain elements in your tags to guide the data to the correct identifier(s). In this how-to, we’ll use an example to show you how to write the corresponding mapping files.

Running example

When creating a diet, care is taken to adhere to limits on selected ingredients. The actual selection of ingredients that is important to a person depends on the person. For instance, some people need restrictions on salt and some people need restrictions on refined sugar. And so on. Also, the availability of products, and their fractions of these ingredients, varies over time and location.

Thus the core information used to create a diet depends on a matrix that looks like this:

../../_images/ExcelFoodNutrientTable.png

Whereby both the collection of

  1. foods, and

  2. nutrients

vary.

The purpose of this article is to illustrate the use of Data Exchange Library, and in particular the mapping attribute name-bind-to, to flexibly and compactly specify exchanging data between data source and AIMMS. In this example, the data source is an EXCEL workbook.

The running example is based on the Diet Problem example from AIMMS The modeling guide.

A selection of the declarations:

  1. Nutriets, index: \(n\). In model: i_n in s_nutrients

  2. Food types, index: \(f\). In model: i_f in s_foodTypes

  3. Fractions: Nutrient value per unit of food, \(fr_{f,n}\). In model: p_nutrientValuePerUnit(i_f,i_n)

The corresponding AIMMS 4.84 project can be downloaded here

Mapping file version 1

 1<?xml version="1.0"?>
 2<AimmsExcelMapping>
 3    <ExcelSheetMapping name="foodnutrient">
 4        <RowMapping>
 5            <ColumnMapping name="food" binds-to="i_f" />
 6            <ColumnMapping name-binds-to="i_n" name-regex=".*" maps-to="p_nutrientValuePerUnit(i_f,i_n)" />
 7        </RowMapping>
 8    </ExcelSheetMapping>
 9    <ExcelSheetMapping name="food">
10        <RowMapping>
11            <ColumnMapping name="food" binds-to="i_f" />
12            <ColumnMapping name="maxQnt" maps-to="p_maximumNumberOfServings(i_f)" />
13            <ColumnMapping name="Price" maps-to="p_pricePerUnit(i_f)" />
14            <ColumnMapping name="Weight" maps-to="p_weightOfFoodType(i_f)" />
15        </RowMapping>
16    </ExcelSheetMapping>
17    <ExcelSheetMapping name="nutrient">
18        <RowMapping>
19            <ColumnMapping name="nutrient" binds-to="i_n" />
20            <ColumnMapping name="atMost" maps-to="p_maximumAllowanceOfNutrient(i_n)" />
21            <ColumnMapping name="atLeast" maps-to="p_minimumRequirementOfNutrient(i_n)" />
22        </RowMapping>
23    </ExcelSheetMapping>
24</AimmsExcelMapping>

Remarks:

  1. Lines 5,6: The sheet foodnutrient is used for exchanging data with the parameter p_nutrientValuePerUnit(i_f,i_n) as follows:

    • Rows are mapped to the s_foodTypes set. The <ColumnMapping/> element uses the following attributes:

      1. name="food": The row names are taken from the column with name food

      2. binds-to="i_f": in the AIMMS model, these row names are mapped to element names in the set s_foodTypes as this set is the range of the index i_f.

      This mapping element is also used for CSV and Parquet files.

    • Columns are mapped to the s_nutrients set. The <ColumnMapping/> element uses the following attributes for this purpose:

      1. name-binds-to="i_n" This indicates that the names of columns, in row 1, are input for the index i_n.

      2. name-regex=".*" This regular expression rule on the column names indicates that the column names can be used as is for the set s_nutrients. More about name-regex as part of name-binds-to.

      3. maps-to="p_nutrientValuePerUnit(i_f,i_n)" As the indices i_f and i_n are now bound, the EXCEL content can be assigned to this parameter.

  2. The sheets food and nutrient are used for exchanging data with the other parameters, indexed over i_f and i_n respectively.

In this section, two sheets were used to present the data regarding the foods. One for the nutrients, and one for the other aspects of each food. It is possible to combine these two sheets into one; provided the data for the nutrients is clearly identified.

Mapping file version 2

By combining the information from the sheets foodnutrient and food, we get a sheet that looks as follows:

../../_images/ExcelFoodNutrientAspects.png

Note that the column names for the nutrients are now prefixed with nut-. We use this in the mapping as specified below.

 1<?xml version="1.0"?>
 2<AimmsExcelMapping>
 3    <ExcelSheetMapping name="foodnutrient">
 4        <RowMapping>
 5            <ColumnMapping name="food" binds-to="i_f" />
 6            <ColumnMapping name="maxQnt" maps-to="p_maximumNumberOfServings(i_f)" />
 7            <ColumnMapping name="Price" maps-to="p_pricePerUnit(i_f)" />
 8            <ColumnMapping name="Weight" maps-to="p_weightOfFoodType(i_f)" />
 9            <ColumnMapping name-binds-to="i_n"
10                name-regex=".*"
11                name-regex-prefix="nut-"
12                maps-to="p_nutrientValuePerUnit(i_f,i_n)" />
13        </RowMapping>
14    </ExcelSheetMapping>
15    <ExcelSheetMapping name="nutrient">
16        <RowMapping>
17            <ColumnMapping name="nutrient" binds-to="i_n" />
18            <ColumnMapping name="atMost" maps-to="p_maximumAllowanceOfNutrient(i_n)" />
19            <ColumnMapping name="atLeast" maps-to="p_minimumRequirementOfNutrient(i_n)" />
20        </RowMapping>
21    </ExcelSheetMapping>
22</AimmsExcelMapping>

Remarks:

  1. All that is indexed over food is now moved in the ExcelSheetMapping on lines 3-14.

  2. Line 11: New is the prefix “nut-”. This prefix is used in two ways:

    1. To recognize a column that contains nutrient information.

    2. To remove the prefix from the column name before adding that name to the set s_nutrients.