Enhancement Proposal: Input of data using string - A Simplified Approach to Inputting Data into MESSAGEix without MS Excel

[shreeyashn20]

Dear Researchers,

I present to you a Simplified Approach to Inputting Data into MESSAGEix without MS Excel.

This improved method for inputting data into MESSAGEix offers a user-friendly tabular format, eliminating the need for MS Excel files and version management of codes and xlsx files separately. It utilizes comma-separated values (CSV) fed to a string, which is then merged with the year_df dataset. The resulting table can be used as input to the parameter, simplifying the data input process for non-coders.

1. Create a CSV and store it as string in a variable.

data_var_cost = """
node_loc,technology,mode,time,value,unit
India,coal,standard,year,5,$/kWh
India,gas,standard,year,6,$/kWh
India,oil,standard,year,7,$/kWh
"""

2. Read the CSV and String file and store it in new variable.
   df_var_cost = pd.read_csv(StringIO(data_var_cost))

3. Now merge the newly created variable with year_df. This step can be modified to merge year_vtg or year_act.

year_df['key'] = 0
df_var_cost['key'] = 0

merged_var_cost = pd.merge(year_df, df_var_cost, on='key').drop('key', axis=1)

4. Finally, feed the merged table into the parameter function.
   scenario.add_par("var_cost",merged_var_cost)

This method can be introduced in Westeros tutorials to help new users write and see data as well as code in single ipynb file. Major advantage over using for loop mentioned in Westeros tutorial is the ability to change data for future model years.

This is my small and humble contribution in open-source community of MESSAGEix.

Thank you for patient reading!

[khaeru]

Thanks, @shreeyashn20! This is indeed a fluent use of pandas and Python features for data input. We do similar things in many places, for instance in message-ix-models. Here I'll give some refinements/alternate approaches to achieve the same task that your example does. To be clear, any code that achieves the intended result is fine! Different users may choose different approaches for their own reasons.

First, we can use pandas.read_fwf() which allows an input string that is perhaps even more reader-friendly:

from io import StringIO

import pandas as pd

data_var_cost = """
node_loc technology mode     time value unit
India    coal       standard year 5     $/kWh
India    gas        standard year 6     $/kWh
India    oil        standard year 7     $/kWh
"""

df_var_cost = pd.read_fwf(StringIO(data_var_cost))

Second, we can use the built-in message_ix.make_df(). The advantage is that the output will always have the correct dimensions for the target parameter. We can use the Python ** syntax to pass the existing columns of df_var_cost as keyword arguments for this function:

from message_ix import make_df

tmp = make_df("var_cost", **df_var_cost)

tmp now looks like this:

  node_loc technology year_vtg year_act      mode  time  value   unit
0    India       coal     None     None  standard  year      5  $/kWh
1    India        gas     None     None  standard  year      6  $/kWh
2    India        oil     None     None  standard  year      7  $/kWh

…with None in the columns we have not yet filled.

Third, we can use the utility function message_ix_models.util.broadcast() together with pandas.DataFrame.pipe(). You don't say what year_df is in your example, but at several places in the tutorials the following is done:

year_df = scen.vintage_and_active_years()

so for a self-contained example let's use:

year_df = pd.DataFrame(
    [[2020, 2020], [2020, 2025], [2025, 2025]],
    columns=["year_vtg", "year_act"],
)

We broadcast tmp over all combinations of $(y^V, y^A)$ in year_df:

from message_ix_models.util import broadcast

df = tmp.pipe(broadcast, year_df)

df now looks like this, as intended:

  node_loc technology  year_vtg  year_act      mode  time  value   unit
0    India       coal      2020      2020  standard  year      5  $/kWh
1    India        gas      2020      2020  standard  year      6  $/kWh
2    India        oil      2020      2020  standard  year      7  $/kWh
3    India       coal      2020      2025  standard  year      5  $/kWh
4    India        gas      2020      2025  standard  year      6  $/kWh
5    India        oil      2020      2025  standard  year      7  $/kWh
6    India       coal      2025      2025  standard  year      5  $/kWh
7    India        gas      2025      2025  standard  year      6  $/kWh
8    India        oil      2025      2025  standard  year      7  $/kWh

Putting this all together:

from io import StringIO

import pandas as pd
from message_ix import make_df
from message_ix_models.util import broadcast

# Name of the parameter for which to create data
name = "var_cost"

data = """
node_loc technology mode     time value unit
India    coal       standard year 5     $/kWh
India    gas        standard year 6     $/kWh
India    oil        standard year 7     $/kWh
"""

data_years = """
year_vtg year_act
2020     2020
2020     2025
2025     2025
"""

df_base = pd.read_fwf(StringIO(data))
df_year = pd.read_fwf(StringIO(data_years))
df = make_df(name, **df_base).pipe(broadcast, df_year)

with scenario.transact(f"Add data for {name}"):
    scenario.add_par(name, df)

[khaeru]

Also, a few editorial comments:

• I agree that using CSV or other text formats can be more transparent; is more compatible with versioning using Git; and performance is also generally better than reading Excel files through pandas, especially when the files are large.
• However, we have found that many of our new users have a lower fluency with writing Python code. This means that they may even find it a struggle to write the Python string data_var_cost that appears in your example and can be parsed by pandas without error. On the other hand, these users have fluency with Excel. So, by supporting Excel input, we allow these users to use a tool for data preparation that is comfortable to them.
• As you note, the tutorials exist to help new users learn the features and behaviour of message_ix. In general, we show data that is to be added to a scenario directly: for example, in westeros_baseline.ipynb, the recommended first tutorial. The only tutorials that use Excel for data input are the westeros_baseline_using_xlsx_import*.ipynb. These are specifically to help users understand the Excel input feature. Users can then decide if they will use this feature in their modeling workflows. If they are not comfortable with writing and reading CSV files, this may be a good choice, as discussed above. But if they are comfortable, and performance is a concern, then they could instead try the approaches we are discussing here.

[shreeyashn20]

Thank you for insightful reply!