Add a 'wide' version of the Excel file format about ixmp

[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.