how to create an index that keep tracking the hours in the data

Is anyone helping me with a simple code ???

In the code below I want to find the peak load and average peak price in peak hour from 7-10 and then do it the same for each peak hours (11-15, 16-21 and 23-6am. I write the code below but I couldn't figure out how to calculate the peak load and average peak price for each peak with the same code

h=twomonth(:,1)
 p1= 7 <= h & h < 10
 price=twomonth(:,2);
 price=price.*p1  
 demand=twomonth(:,3);
 demand=demand.*p1
 Index1=(7:24:N)'; ss1=10-1
N1=length( Index1);
Data1=zeros(N1,2);
for k=1:N1
    kk=Index1(k); kkk=kk+ss1-1;
    dd=sum(d(kk:kkk));pp=sum(p(kk:kkk).*d(kk:kkk)); pp=pp/dd;
    Data1(k,:)=[pp dd];
end

[Star Strider]

What does ‘isn’t working’ mean when you try to run it?

I can’t run it.

The error:

d=load.*p1

Error using load
Unable to read file 'matlab.mat'. No such file or directory.

Please do not name a variable ‘load’. It overshadows the load function, necessary for reading .mat files and other files.

What do you want to do in that line? Note that you are overwriting ‘d’ that you defined in the line just before it.

Also, ’p’, and ‘N’ are not defined.

[gjashta]

Sorry Star Strider, you are right! I have fixed my mistakes in the code above.

Hope I was clear enough and you can help me!

Answer 1

[Guillaume]

Here's how I'd do it:

%twomonth: a Nx3 matrix, whose first column is hour, 2nd is price, 3rd is demand
bin = discretize(mod(twomonth(:, 1)-7, 24), [7, 11, 16, 22, 23, Inf]-7);  %offset bin edges by 7 in order to put the 23-6 in the same bin (5)
%bin == 1 is [7-11), bin == 2 is [11-16), bin == 3 is [16-22), bin == 4 is [22], bin == 5 is [23-6]
avgprice = splitapply(@mean, twomonth(:, 2), bin);
peakdemand = splitapply(@max, twomonth(:, 3), bin);
result = table({'7-10'; '11-15'; '16-21'; '22'; '23-6'}, avgprice, peakdemand, 'VariableNames', {'period', 'avgprice', 'peakdemand'})

I've included the lone 22 in the result, you can always remove row 4 if you don't want it.

You have to do a little bit of arithmetic as discretize can't give you the same bin number for the start and end of a range, but once you've got your bin numbers it's trivial to calculate your mean and peak with splitapply.

[gjashta]

Thank you Guillaume! Please, How can I use your code to calculate daily average price by bin per demand e.i for bin1 (sum(pi*di))/sum(di) when i=1:3 and average demand by hours in the bin1 sum(di)/i when i=1:3? As I mentioned above I need a code that take into account also the days with more/less hours in a specific bin.

[Guillaume]

There's currently no day information in your matrix. If we assume a new day when the hours go from 23 to 0, then:

daybin = cumsum([1; diff(twomonth(:, 1)) < 0])

then, it's pretty much just the same, with an addition of a findgroups:

hourbin = discretize(mod(twomonth(:, 1)-7, 24), [7, 11, 16, 22, 23, Inf]-7);  %offset bin edges by 7 in order to put the 23-6 in the same bin (5)
hourranges = {'7-10'; '11-15'; '16-21'; '22'; '23-6'};
[bin, actualday, actualhour] = findgroups(daybin, hourbin);
actualhour = hourranges(actualhour);
avgprice = splitapply(@mean, twomonth(:, 2), bin);
peakdemand = splitapply(@max, twomonth(:, 3), bin);
weightedprice = splitapply(@(price, demand) mean(price .* demand), twomonth(:, 2), twomonth(:, 3), bin); 
result = table(actualday, actualhour, avgprice, peakdemand, weightedprice)

As for, "So hourRanges must be daily and another problem is that some days during the year has more/less hours due to daylight saving", that's vastly different from your original question and will require a different approach altogether. Currently, the necessary information is not in your data so I'm not sure what you want us to do about it. At the very least your data would need to contain date information (as datetime). A timetable would be more suitable for that instead of a matrix. We'd also need to know how you define the hour range for a particular day.

[gjashta]

Thank you Guillaume for the code. I used only two month data because I thought it easier for me to play with the data. I attached the original data in exel because I couldn't transfer them into matlab with the date.

[Guillaume]

Importing that excel file is trivial with readtable. However, if you want to take into account DST, at some point you'll need to specify a timezone.

You haven't explained how the hour bins are supposed to change according to the date.

[gjashta]

I have used this code but I do not know how to conect the datetime colomn with your code above. The hour bins are supposed to change as following: 2 april 2006 and 11 march 2007 days have only 23 hour( no hour 2:00) but days 29 october 2006 and 4 november 2007 have 25 hour ( hour 1:00am repeats twice.

start_dt = datetime('2006-1-1 01:00', 'TimeZone', 'America/New_York');

end_dt = datetime('2007-12-31 23:00', 'TimeZone', 'America/New_York');

[Adam Danz]

This answer seems to be heading in the right direction so I removed mine (+1 Guillaume).

@Ida 1, " I do not know how to conect the datetime colomn with your code above"

Instead of working with a matrix and a datetime vector, it may be easier and more organized to work with a table. This should get you started.

opts = spreadsheetImportOptions;
opts.VariableNames = {'date','hour','price','demand'};
opts.VariableTypes = {'datetime','double','double','double'};
M = readtable('DataOrg.xlsx',opts);

head(M) % View the first few rows

[Guillaume]

The dst shift is already embedded in the original data, so actually, it's no something we have to worry about. What I'm still unclear about is how the hourly bins vary from day to day.

What should the bins be on 1 January 2006, 1 April 2006, 2 April 2006, or any other day? How do you specify this?

[gjashta]

Thanks, Guillaume, hope I will be clear enough below!

The first bin has the same hours every day (7-10),but bin 4 only on 2 April 2006, 11 March 2007 has 8 hours and only on 29 october 2006, 4 november 2007 has 9 hours. So, the hours on bin number 4 is changing only in these 4 days.

Guillaume, in your code above, how can I save the daily results in different table for each bin? For example, tab1 contains the daily avgprice and peakdemand for bin1 from 1 January 2006 to 31 December 2007.

[Guillaume]

So, the edges of the bin don't change then? Your 4th bin is always 23-6. With the code I proposed, it doesn't matter what's inside the bin, all the hours that fall within the interval are automatically taken into account.

Why is 22 not in any bin?

[gjashta]

Yes, the edges of the bin are ok. I have included 22 in the last bin (22-6). Please, can you help me how to split the result in different table for each bin.

result = table(actualday, actualhour, avgprice, peakdemand, weightedprice)

[Guillaume]

Untested code, I don't have access to matlab right now:

%import the data. Use this or Adam's code
hourlydata = readtable('DataOrg.xlsx', 'ReadVariableNames', false);
hourlydata.Properties.VariableNames = {'date', 'hour', 'price', 'demand'};
%bin definition:
hourbins = [7, 11, 16, 22, Inf]; %bins must be contiguous and the last bin will wrap around to the start of the first bin

%discretize rows into bins:
[daybin, actualday] = discretize(hourlydata.date, 'day');
hourbin = discretize(mod(hour(hourlydata.hour) - hourbins(1), 24), hourbins - hourbins(1));  %the offset and mod is to wrap around the last bin
[group, groupday, grouphour] = findgroups(daybin, hourbin);
groupday = actualday(groupday);
starthour = hourbins(grouphour);
avgprice = splitapply(@mean, hourlydata.price, group);
peakdemand = splitapply(@max, hourlydata.demand, group);
weightedprice = splitapply(@(price, demand) mean(price .* demand), hourlydata.price, hourlydata.demand, group); 
result = table(groupday, starthour, avgprice, peakdemand, weightedprice)

[gjashta]

Thank you Guillaume! I just run your code, and I have gotten this error only from the last line:

result = table(groupday, starthour,avgprice, peakdemand, weightedprice)

Error using table (line 231)

All input variables must have the same number of rows.

[Guillaume]

Oh yes, starthour probably needs transposing

result = table(groupday, starthour.', avgprice, peakdemand, weightedprice)

if it's not that then please give the size of each variables.

[gjashta]

Thank you very much Guillaume! Now the code is OK!