Add a 'wide' version of the Excel file format

[meng25meng]

If you have a question about how to use message_ix or ixmp, please first:

• Read the Prerequisite knowledge & skills page of the documentation.
• Check the Discussions associated with the message_ix repository.

If these do not help, delete this text including the "----" line, and describe your issue.

---

Hello, I compared the differences in the data reading methods in the tutorial. If I want to build a 31-node model, it is more convenient to use excel to read. If I create a table for each parameter, the possibilities are very large, and for each parameter One technique for expressing the year requires several lines, like this one, I think if possible, you can write the year on the row header, and then add all the added technical parameters to the same table column header, with the horizontal row representing the index parameter, so that the index value of a parameter can be represented by one row Instead of several lines, it's like this: Do you think this is possible? So when setting ix_type_mapping, do you need to set the table name or the technical parameters added by yourself?

[khaeru]

Hi @meng25meng—thanks for the question/suggestion.

I did a few things:

• Moved the issue from message_ix to the underlying ixmp package. The Excel file format is established by this package. The format is documented on the page “File formats and input/output” and the section “Scenario/model data”.
• Removed the template text from the top of the issue description. Please do this yourself next time.
• Changed the title to a more descriptive one.
• Marked the issue as "won't fix".

Below I explain why this is "won't fix" and make some alternate suggestions.

This is a proprietary or idiosyncratic file format. It is not used by any other software except for ixmp. (I am not even sure if the successor package ixmp4 yet supports it, or should.) This means we have to do 100% of the work of building, testing, documenting, and helping users understand and use this format. If there were no other formats that allowed to exchange this kind of data, that might be valuable. But there are such formats, so this is generally a bad use of our time.

For those reasons:

• The format is final, and will not be updated or changed.
• We will only fix bugs in the documented code that reads/writes this format, and not add features.

Instead, there are many better options.

We generally encourage users to learn to use Python, pandas, and the API directly. This makes it possible to generate large amounts of message_ix/ixmp-structured data with a few, short function calls, and add those to a Scenario. This code ends up being easier to understand than a large file. There is a big reward from time invested to learn these things.

For one example:

• In your second screenshot above, you have one dimension "pivoted" to columns. It's either year_act or year_vtg; which one is not clear. (This is a common mistake people make when inventing new formats, and one reason not to do so.)
• Pandas provides DataFrame.pivot() (long to wide) and DataFrame.melt() (wide to long) that you can use to convert between these layouts.
• So, you can store data in whatever layout you like, and then do something like:

  data = pd.read_excel("my_custom_format.xlsx")
  scenario.add_par(
    "var_cost",
    (
        data.query("parameter == 'var_cost'")
        .drop("parameter", axis=1)
        .melt(var_name="year_act", ...)  # or "year_vtg", as you like
    ),
  )

For a second example, see our colleagues' code in message_ix_models.model.water: here. This combines message_ix.make_df(), plus DataFrame.pipe() and utilities from message_ix_models like broadcast() and same_node(), to generate data for their model.

Finally, we are taking first steps towards using the SDMX standard formats for model data. As an ISO standard, these formats are much more widely used and supported than the ixmp Excel format, and will be a more sustainable option in the future.