Reading time format from excel file

Problems with syntax of GAMS
Post Reply
Farhad9494
User
User
Posts: 4
Joined: 7 months ago

Reading time format from excel file

Post by Farhad9494 » 1 month ago

Hello,
I have a question about reading set elements from the excel file. by default, GAMS read elements of a set as text while elements are about time intervals. Another problem about creat a subset. this subset include every hour of an operation day. I'm going to calculate Mileage Ratio RegD and RegA for every single hour (mileage ratio RegD(h)=mileage regD(h)/mileage regA(h)). I should summation every MR(t,'MregD') to next step of H(t).(00:00:00-01:00:00)
Mileage_Regsignal.gms
(1.13 KiB) Downloaded 26 times
Attachments
Regsignal.xlsx
(287.92 KiB) Downloaded 22 times

Farhad9494
User
User
Posts: 4
Joined: 7 months ago

Re: Reading time format from excel file

Post by Farhad9494 » 1 month ago

I found a way to convert time format to text string format
in excel have been defined a formula that can convert time to the text string.
Regsignal.xlsx
(950.51 KiB) Downloaded 22 times
Select the cell which will place the text result, type this formula =TEXT(A1,"hh:mm:ss AM/PM"), press Enter key. And if you need, drag the fill handle down to apply this formula to the below cells or use it this way select first cell and hold shif+select finite cell then home>editing>fill and use which option is suitable.
How to do summation parameter Mileage_Reg(t,'signal_D') for every hour?

User avatar
bussieck
Moderator
Moderator
Posts: 663
Joined: 5 years ago

Re: Reading time format from excel file

Post by bussieck » 1 month ago

If you always start with second/minute 0:0 and go in 4 second steps, then you can do the mapping by order: take the first 900 time steps and assign to first hour, take the next 900 steps and assign to second hour, ... You better kill or don't read the 00:00:00 twice (I removed the last row in your workbook). Then the following works:

Code: Select all

$call.checkErrorLevel gdxxrw i=Regsignal.xlsx o=Regsignal.gdx par=data rng=sheet1!a1 cdim=1 rdim=1
set t   'time steps'
    s   'steps' / s1*s900 /
    h   'hours' / h1*h24 /
    hdr 'header' / D, A /;
parameter data(t<,hdr);
$gdxin Regsignal
$load data

set hstmap(h,s,t) / #h.#s:#t /, htmap(h,t); option htmap<hstmap;
Now htmap contains the mapping from hours to timesteps.

-Michael

Farhad9494
User
User
Posts: 4
Joined: 7 months ago

Re: Reading time format from excel file

Post by Farhad9494 » 1 week ago

Thanks for your recommendation I used that manner and reformed my set labels. however, I have a new problem "278 Lags are not allowed on maps". did not permissible using lags and leads in mapping subset? how should define mapping subset be?
Untitled_4.gms
(2.5 KiB) Downloaded 10 times
EX_mileage.xlsx
(4.8 MiB) Downloaded 12 times

User avatar
bussieck
Moderator
Moderator
Posts: 663
Joined: 5 years ago

Re: Reading time format from excel file

Post by bussieck » 6 days ago

Your code still does not have the t set, a one dimensional index over the entire time. So if you would have this as explained in my example and declare your data2 over t instead of ho,sc (or s,h in my example), this would be easy: data2(t,'up')= data(t,'max')-data(t-1,'min'); With your code this is not as easy, but you can do:

Code: Select all

parameter data2(ho,sc,*);
data2(k(ho,sc),'up')= data(ho,sc,'max')-data(ho,sc-1,'min');
data2(k(ho,'s1'),'up')= data(ho,'s1','max')-data(ho-1,'s900','min');
BTW, using dset instead of set in the gdxxrw instruction file for ho and sc allows you to drop the maxDupeErrors=21601 on the gdxxrw execution line and gives you a cleaner log.

-Michael

Post Reply