Comments (11)
There seems to be a difference between the .dat reader and the table handler reader. I was able to reproduce the issue with a relational table in amplxl, odbc and the .tab handler.
To reproduce the issue with the .tab handler copy the following data to a file named amounts.tab
ampl.tab 2 1
NUTR FOOD amt
A BEEF 60
A CHK .
A FISH .
A HAM 40
A MCH 15
A MTL 70
A SPG 25
A TUR 60
B1 BEEF .
B1 CHK 20
B1 FISH 15
B1 HAM 35
B1 MCH 15
B1 MTL 15
B1 SPG 25
B1 TUR 15
B2 BEEF 15
B2 CHK 20
B2 FISH .
B2 HAM .
B2 MCH 15
B2 MTL 15
B2 SPG 15
B2 TUR .
C BEEF 20
C CHK .
C FISH .
C HAM 40
C MCH 35
C MTL 30
C SPG 50
C TUR 20
and the following to a .run script
reset;
set NUTR := {"A", "B1", "B2", "C"};
set FOOD := {"BEEF", "CHK", "FISH", "HAM", "MCH", "MTL", "SPG", "TUR"};
set LINKS within {NUTR, FOOD};
param amt {LINKS} >= 0;
table amounts IN:
LINKS <- [NUTR, FOOD], amt;
read table amounts;
display LINKS, amt;
the display in AMPL shows
set LINKS :=
(A,BEEF) (A,SPG) (B1,MCH) (B2,FISH) (C,BEEF) (C,SPG)
(A,CHK) (A,TUR) (B1,MTL) (B2,HAM) (C,CHK) (C,TUR)
(A,FISH) (B1,BEEF) (B1,SPG) (B2,MCH) (C,FISH)
(A,HAM) (B1,CHK) (B1,TUR) (B2,MTL) (C,HAM)
(A,MCH) (B1,FISH) (B2,BEEF) (B2,SPG) (C,MCH)
(A,MTL) (B1,HAM) (B2,CHK) (B2,TUR) (C,MTL);
amt [*,*] (tr)
: A B1 B2 C :=
BEEF 60 . 15 20
CHK . 20 20 .
FISH . 15 . .
HAM 40 35 . 40
MCH 15 15 15 35
MTL 70 15 15 30
SPG 25 25 15 50
TUR 60 15 . 20
;
from plugins.
Based on Nicolau's comment I looked at the escrow/acl/tables.c and it seems that in the Add_Rows function we are inserting the the keys before check the rest of values.
The behavior Robert expect is not present in any table handler, we can rework the code to achieve that behavior and it'll break (change behavior) for existing ampl scripts.
from plugins.
After a while I'm revisiting my comment because it was not correct, on Nicolau's example it's not equivalent with Robert example, to be equivalent it should be like this:
ampl.tab 2 1
NUTR FOOD amt
A BEEF 60
A HAM 40
A MCH 15
A MTL 70
A SPG 25
A TUR 60
B1 CHK 20
B1 FISH 15
B1 HAM 35
B1 MCH 15
B1 MTL 15
B1 SPG 25
B1 TUR 15
B2 BEEF 15
B2 CHK 20
B2 MCH 15
B2 MTL 15
B2 SPG 15
C BEEF 20
C HAM 40
C MCH 35
C MTL 30
C SPG 50
C TUR 20
And then running it will get this output (that somehow match Robert example):
set LINKS :=
(A,BEEF) (A,SPG) (B1,HAM) (B1,TUR) (B2,MTL) (C,MCH)
(A,HAM) (A,TUR) (B1,MCH) (B2,BEEF) (B2,SPG) (C,MTL)
(A,MCH) (B1,CHK) (B1,MTL) (B2,CHK) (C,BEEF) (C,SPG)
(A,MTL) (B1,FISH) (B1,SPG) (B2,MCH) (C,HAM) (C,TUR);
amt [*,*] (tr)
: A B1 B2 C :=
BEEF 60 . 15 20
CHK . 20 20 .
FISH . 15 . .
HAM 40 35 . 40
MCH 15 15 15 35
MTL 70 15 15 30
SPG 25 25 15 50
TUR 60 15 . 20
;
It seems that if there isn't data in a cell we should not include it in a call to AddRow
.
from plugins.
With this modification to initial Robert example we can see the equivalent "LINKS' table:
set NUTR;
set FOOD;
set LINKS within {NUTR, FOOD};
param amt {LINKS} >= 0;
data test-empty.dat;
table amounts_dat OUT:
[NUTR, FOOD], amt;
write table amounts_dat;
table links_dat OUT:
{(n,f) in LINKS} -> [n ~NUTR, f ~FOOD];
write table links_dat;
display LINKS, amt;
ampl.tab 2 0
NUTR FOOD
A BEEF
C BEEF
B2 BEEF
B1 CHK
B2 CHK
B1 FISH
A HAM
C HAM
B1 HAM
A MCH
C MCH
B1 MCH
B2 MCH
A MTL
C MTL
B1 MTL
B2 MTL
A SPG
C SPG
B1 SPG
B2 SPG
A TUR
C TUR
B1 TUR
from plugins.
I'm still in doubt about my second comment, it seems that there is no way from the table handler to know if it's updating a set
like ampl can when reading dat
files.
I tried modify escrow/acl/tables.c::Add_Rows but could not get it to work as expected so far.
I'm again inclined to say the problem is inside ampl not the table handler.
from plugins.
Finally I could find a possible fix for this issue with this commit https://github.com/ampl/escrow/commit/f85bf5c67cedf8d39cbbce5fe10e33e16f354f0c, I was correct on my first comment but I didn't understood the code at that time to be able to move the insertion on the indexed set after checking for '.'
values.
from plugins.
Can you test the fix with the following files?
dat_data.dat
param: dat_keys: dat_vals:=
A 1
B 2
C .
D 4
;
tab_data.tab
ampl.tab 1 1
K V
A 1
B 2
C .
D 4
example.run
reset;
# data loaded from .dat
set dat_keys;
param dat_vals{dat_keys};
data dat_data.dat;
# data loaded from .tab
set tab_keys;
param tab_vals{tab_keys};
table tab_data IN:
tab_keys <- [K], tab_vals ~ V;
read table tab_data;
# compare
display dat_keys, dat_vals;
display tab_keys, tab_vals;
expected output:
set dat_keys := A B C D;
dat_vals [*] :=
A 1
B 2
D 4
;
set tab_keys := A B C D;
tab_vals [*] :=
A 1
B 2
D 4
;
from plugins.
Good catch !
Thanks for the sample script I extended the initial fix here https://github.com/ampl/escrow/commit/a8aed66684fcfd74abdc63c8358a6756f602e5d7 , I could not identify where the "escrow/acl/read.c" manage this case and came with this not elegant/clean fix.
It now produces the expected output for both of Nicolau's examples, I could not reproduce Robert example because I could not create the xlsx file that could be accepted by the table handler definition.
A peer review is welcome !
from plugins.
The behavior seems to be different for different dimensions, for example with the followinf script
reset;
set A := {"a", "b", "c", "d"};
set B within A;
param C{B};
data data.dat;
display A, B, C;
reading the data
param: B: C:=
a 1
b 2
c .
d 4
;
AMPL does not skip the missing values and displays
set A := a b c d;
set B := a b c d;
C [*] :=
a 1
b 2
d 4
;
from plugins.
There is also a confuse or not clear way to explain this behavior where we have a set declared with dimen
and check (what really change in the data format is having a 2D table or not, independent of the within
clause):
set NUTR;
set FOOD;
set LINKS dimen 2;
check LINKS within {NUTR, FOOD};
param dat_vals {LINKS};
data dat_data2.dat;
display dat_vals, LINKS;
param :LINKS: dat_vals (tr):
A C B1 B2 :=
BEEF 60 20 . 15
CHK . . 20 20
FISH . . 15 .
HAM 40 40 35 .
MCH 15 35 15 15
MTL 70 30 15 15
SPG 25 50 25 15
TUR 60 20 15 . ;
from plugins.
It seems that the rules for reading set and parameter values together from a .dat
table are as follows:
- For 1D tables, a member is read from every row of the table, regardless of
.
entries. - For 2D tables, a member is generated for every entry in the table, skipping
.
entries.
I believe that these rules were chosen to correspond to the cases that are most likely to be useful. It's possible to think up situations where the opposites of the above rules would be desirable, but they are less likely cases and can be handled in other ways.
Given that these are indeed the rules for .dat
tables, I propose to use analogous rules for amplxl tables in spreadsheets.
from plugins.
Related Issues (17)
- Cannot read spreadsheet open in Excel; error message is uninformative
- duplicate subscript ['Missing','TA5X6','CH'] HOT 1
- Trouble with very large spreadsheet file HOT 1
- "Could not parse data" message for duplicate keys HOT 1
- Unexpected handling of duplicate column names
- Missing set member is given unexpected name HOT 1
- Uninformative error message when range or sheet name not found HOT 1
- Accented characters from spreadsheet file don't display correctly HOT 2
- Trailing spaces cause hard-to-spot errors
- Extra messages when reading spreadsheet HOT 1
- Crash when creating new spreadsheet HOT 1
- Spreadsheet must be repaired after writing HOT 1
- Empty .xlsx.amplbak file created HOT 1
- Unexpected "Could not extract sheet" HOT 9
- New spreadsheet file opens with blank page HOT 2
- Error on writing 2D table from set of pairs HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from plugins.