Setting up piecewise linear variables reading excel data

Support
1

Hello,
I am reading excel data using “amplxl.dll” and want to use it to setup several piecewise linear functions in AMPL. Is there a common procedure (best practice) to do this?
My excel data contains data like:

|id|trf_id|tap|value|
|1|1|0|1.4|
|2|1|11|1.0|
|3|1|27|0.8|
|4|2|10|1.5|
|5|2|15|1.75|
|6|2|20|2.0|
|7|2|25|2.25|
|8|2|30|3.0|
|9|2|35|3,75|

Here, “id” is simply an index to be able to read the data. There are several sampling points of different size (number) for two transformer (tr_id): Three sampling points for the 1st and six for the 2nd transformer. How can the slopes be calculated in AMPL? Is there a “generic internal function” which can do the job? Maybe defining and using several sets or sub-sets due to the diffenrt number of sampling points as well as minimum and maximum values for the tap values?

So far, I am stating using this code (not finished):

reset;
load amplxl.dll;

set ID default {};
param trf_id {ID} default 0;
param tap {ID} default 0;
param value {ID} default 0;

table trf_data IN “amplxl” “Testdata.xlsx” “Table1”: ID ← [id], trf_id, tap, value;
read table trf_data;

param max_id = max{i in ID} trf_id[i];
set id_new = {1… max_id};

param number_of_sampling_points{j in id_new} = sum{i in ID: trf_id[i]==j} 1;
…

Best regards,
Lothar

2

{4C306C69-D29B-4939-8AEF-D18B13A514CA}
{4C306C69-D29B-4939-8AEF-D18B13A514CA}1676×404 35.3 KB

I think, I solved it using the data, depected in the attached picture.
Here is the code which could contain some “work arounds”. Thus, any comments or ways to do it better are welcome:

reset;

load amplxl.dll;

set ID default {};
param trf_id {ID} default 0;
param tap {ID} default 0;
param value {ID} default 0;

table trf_data IN “amplxl” “Testdata.xlsx” “Tabelle1”: ID ← [id], trf_id, tap, value;
read table trf_data;

param max_id = max{i in ID} trf_id[i];
set id_new = {1… max_id};
param number_of_sampling_points{j in id_new} = sum{i in ID: trf_id[i]==j} 1;

set taps{id_new} ordered default {};
set vals{id_new} ordered default {};

let {j in id_new, i in ID: trf_id[i]==j} taps[j] := taps[j] union {tap[i]};
let {j in id_new, i in ID: trf_id[i]==j} vals[j] := vals[j] union {value[i]};

param p_taps {i in id_new, j in 1…card(taps[i])} = member(j, taps[i]);
param p_vals {i in id_new, j in 1…card(vals[i])} = member(j, vals[i]);

param p_slope{i in id_new, j in 1…card(taps[i])-1} = ((member(j+1, vals[i]) - member(j, vals[i])) / (member(j+1, taps[i]) - member(j, taps[i])));

param p_taps2use{i in id_new, j in 1…card(taps[i])-2} = p_taps[i, j+1];
param p_vals2use{i in id_new, j in 1…card(vals[i])-2} = p_vals[i, j+1];
param p_offset{i in id_new} = p_vals2use[i,1]-p_slope[i,1]*p_taps2use[i,1];

var v_tap_pos {id_new};
let v_tap_pos[1] := 11;
let v_tap_pos[2] := 30;
let v_tap_pos[3] := 20;

var A{i in id_new} = p_offset[i] + << {j in 1…number_of_sampling_points[i]-2} p_taps2use[i,j]; {j in 1…number_of_sampling_points[i]-1} p_slope[i,j] >> v_tap_pos[i];

display A;

1 Like
3

Hi @Lothar_Lower ,

Glad that you found a workaround! I think it would be easier to handle Excel reading and breakpoints/slopes calculation outside Ampl (for example in Python using the API). This way, you could write a clean model easier to read and maybe more natural in terms of procedure.

This is some simple code to read the Excel and compute the breakpoints/slopes in Python:

import pandas as pd

df = pd.read_excel('data.xlsx')

# handle the decimal separator...
df['value'] = df['value'].astype(str).str.replace(',', '.').astype(float)

# create a couple python data structures to send to ampl afterwards
breakpoints = {}
slopes = {}

for trf_id, group in df.groupby('trf_id'):
    sorted_group = group.sort_values('tap')
    taps = sorted_group['tap'].tolist()
    values = sorted_group['value'].tolist()
    
    slope_list = []
    for i in range(len(taps) - 1):
        delta_tap = taps[i+1] - taps[i]
        delta_value = values[i+1] - values[i]
        slope_list.append(delta_value / delta_tap)
    
    breakpoints[trf_id] = taps
    slopes[trf_id] = slope_list

print("Breakpoints:", breakpoints)
print("Slopes:", slopes)

Finally, to send it to Ampl assuming you have a couple sets and parameters:

set ID;
set taps{i in ID} ordered;
set slopes{i in ID};
# ...
# given an id i...
<<{tap in taps[i]} tap ; {slope in slopes[i]} slope[i]>> ...

Or something really similar but not too big and easier to read :slight_smile:

(Finally) Send the data to Ampl through Amplpy:

import amplpy

# read the excel as above...
# ...

ampl.set['taps'] = breakpoints
ampl.param['slopes'] = slopes

That’s all, no tables statements involved here.

Some adjustment might be needed, like the offset and so on. Hope this helps!