Using timetable column values in loop control?

I have a years worth of data in a timetable with half hourly timesteps, my datetime is in the format (dd-MM-yyyy HH:mm:SS) but I also have columns for DayOfWeek ('Monday', 'Tuesday' etc.) [I'm pretty sure I can figure out how to change these into numerical values (0-6)] and HrOfDay (0, 1, 2,...,23).

I want to assign the cost of buying electricity which is dependent on the amount imported (one of my other columns - 'GridImport') and the day and time it was purchased. Weekends have a set price independent of the time and weekdays are split into five time zones, with three set prices (i.e. 00:00-07:30 and 21:00-24:00 is one price, 07:30-16:00 and 19:00-21:00 is another price and finally 16:00-19:00 is the other price).

So I want to track how much was spent over different points during the year (amount imported per half hour multiplied by the cost per unit electricity during that time) in a separate column.

I'm feel like I will need to use loop control, e.g

PriceOne=2.5
PriceTwo=3.0

    if tt.DayOfWeek = 0, 6
      tt.Cost=tt.GridImport*PriceOne

else

   while tt.DateTime=00:00:00-07:30:00
  tt.Cost=tt.GridImport*PriceTwo

    end

and something similar for the rest?

[Khadisha Zahra]

This is an example of what the data in Timetable looks like

    DateAndTime       WindSpeed   Temp   RH   Press   Sunlight Rainfall ElecDemand  Gen  GridImport DayOfWeek HrOfDay
  01-10-2015 00:00:00   1.326    10.84  94.5  1.03e+03  -3.4     0         2558     2066   492         5         0
  01-10-2015 00:30:00   1.085    10.66  95.6  1.03e+03  -3.7     0         2556     2491   49          5         0

DayOfWeek- 5 is Thursday

[Joshua]

Khadisha, assuming you convert the days and hours to numbers, you probably will just want to import the data without the text headings. MATLAB typically uses matrices which index with a row and column. You just have to know which columns are which. Then you would get something like this where you can add as many ifelse statements as you want:

tt=[10 0   1.326    10.84  94.5  1.03e+03  -3.4     0         2558     2066   492         5         0;...
    10 30   1.085    10.66  95.6  1.03e+03  -3.7     0         2556     2491   49          5         0];

PriceOne=2.5;
PriceTwo=3.0;
PriceThree=4.0;
PriceFour=2.0;

[m,n]=size(tt);
Cost=zeros(n,1);

for i=1:n % cycle through number of columns in tt
    if tt(i,12) == 0 || tt(i,12)==6 % weekend
        Cost(i,1)=tt(i,11)*PriceOne;
    elseif tt(i,2)<=7.5
            Cost(i,1)=tt(i,11)*PriceTwo;
    elseif tt(i,2)<=16
            Cost(i,1)=tt(i,11)*PriceThree;
    else
        Cost(i,1)=tt(i,11)*PriceFour;
    end
end

You just have to consider all your cases.

Answer 1

[Guillaume]

A loop is not needed and it is probably much simpler not to use a loop:

daypartband = [0, 7.5, 16, 19, 21, 24;  ... hours and fraction of hours
               1, 2,   3,  2,  1,  NaN]; %corresponding band    
bandprice = [2.5, 3, 4, 1.75]';  %price of band 1,2,3 above + band 4 = weekend

hm = hour(tt.DateAndTime) + minute(tt.DateAndTime)/60; %convert time to fractions of hours
band = daypartband(2, discretize(hm, daypartband(1, :)))';
band(isweekend(tt.DateAndTime)) = 4;
tt.Cost = tt.GridImport .* bandprice(band);

[Khadisha Zahra]

So I have applied the coding you have suggested and I haven't quite gotten the result I was expecting (i.e. a cost column with a singular cost corresponding to each half hour) instead I appear to get a double cell (1x17586) per half hour time set (see below)

 DateAndTime        WindSpeed    Temperature    RelativeHumidity    Pressure    SunlightOnInclinePanel    Cum_Rainfall    All_ElecDemand    Total_CHP_Generation    GRIDIMPORT    Amount_Exported    DayOfWeek    HrOfDay          Cost      
 01-10-2015 00:00:00    1.3265       10.843         94.516              1031.1      -3.4006                   0               2558              2066                    492            0                 5            0          [1x17568 double]
 01-10-2015 00:30:00    1.0859       10.665         95.624                1031      -3.7085                   0               2540              2491                     49            0                 5            0          [1x17568 double]

[Guillaume]

Argh! Bitten by matlab stupid rules over which input governs the shape of the final array when indexing.

Transposing bandprice fixes the issue.

<rant>The rule is that A(B) is the same shape as B except when both A and B are vectors, in which case it's the shape of A. Exeptions in programming languages are extremely stupid!<end rant>

Answer 2

[Peter Perkins]

Your description is not entirely clear to me, especially your gola, but this should get you started. It sounds like you need to do a join between your data timetable and a small timetable that has price for each time of day category.

1) Using your datetime variable, get the hour of day (sounds like you have that already). Use discretize on HoD to bin into your "zones", then call isweekend on your datetime variable and overwrite all weekend elements in that bin number4 vector with a 0 or 6 or whatever. Then turn those bin numbers into a categorical variable in your timetable. All that is just a few lines of code.

2) Create a table that has 6 rows, two variables. One variable is the time bin, the otehr is the price for that bin.

3) Join the table to the timetable, using the time bin as the key.

Hope this helps.