0

If I have a number of named parameters in an AMPL model, and a table (either in an Excel spreadsheet or in AMPL's built-in format) with columns ParamName, ParamValue, is there a nice way to read these values in so that the parameter with name ParamName gets the value ParamValue?

For example, if I had a file called params.tab with the contents

ampl.tab 1 1
ParamName ParamVal
n 9
max 100
min 0

is there a way to get an equivalent result to loading a data file containing

param n := 9;
param max := 100;
param min := 0;

?

In particular, I would like an approach that is flexible to changes in what parameters are involved, by which I mean that if I add a new parameter to the model I'd like to just add it as a new row in the table and not have to modify the commands used to load it in too much.

If it simplifies things, it's safe to assume that all of the parameters have the same type (e.g. integer).

I can think of two approaches that I will write up as a self-answer, but I would very much appreciate any better options.

Improve this question

2 Answers 2

Reset to default
0

Approach 1: Read from indexed array to parameters

So the first thing I thought of is to load the table into an indexed array, then assign the values in the array into the parameters.

This would look something like:

set PARAMNAMES;
param ParamValues {PARAMNAMES};

table Params IN "params.tab":
  PARAMNAMES <- [ParamNames], ParamValues;

read table Params;

let n := ParamValues[n];
let max := ParamValues[max];
let min := ParamValues[min];

The main problem with this approach is the need to have a laundry list of let statements that stays in sync with the model parameters and the input file.

Approach 2: Replace the parameters with an indexed array

The second thing I thought of is to do away with individual parameters in the model itself, and instead store them in an array that can be directly read in from the table as above. So in the model I would have a declaration like

set PARAM_NAMES := { n max min };
param params {PARAM_NAMES};

in the model do a find-and-replace changing n with params[n], etc., and then use a read table statement like in Approach 1 to load the values from the table.

The main drawbacks I see with this are:

  1. I'm not sure how easy it is to specify individual default values for each parameter.
  2. The names become clunkier, which might make the model harder to read.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Your two approaches are good, I do not think there is a straightforward way to assign automatically "single" parameters, you would need to list all of them when assigning the table.

Nevertheless, I highly recommend to use an external table handler to read your spreadsheet and play with the data over there. In Python (using amplpy and pandas spreadsheet reader)

from amplpy import AMPL
import pandas as pd

file_path = 'parameters.xlsx'

# May be necessary to install openpyxl library
df = pd.read_excel(file_path)

ampl = AMPL()
ampl.read('problem.mod')

# Loop through the DataFrame to assign data into ampl
for index, row in df.iterrows():
    ampl.param[row['ParamName']] = row['ParamVal']

Notice that with this approach you do not need to use "table statements" in ampl, so your model might become even easier

Improve this answer

2 Comments

Thanks for the info! For various reasons I was avoiding Python in this instance, but it's definitely something I will look into.
IMO Python (and chatgpt) are great to treat and transform your data. Furthermore, your model/script gets cleaner.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.