Storing unique hour and minute values

I want the code below to runs through a large dataset to distinguish between weekend and weekday consumption at each hour and corresponding minute to calculate the average hourly use value (indicated by A). I tried several different ways to no previal. Any help would be much appreciated.

for i = 1:length(UniqueTimeSteps)

TimeValue(i) = UniqueTimeSteps(i);
[HourValue,MinValue,Seconds] = hms(UniqueTimeSteps(i));
idx = (t.Hour + t.Minute == HourValue & MinValue & weekday(t) > 1 & weekday(t) < 7);
Consumption_Weekday(i) = mean(A(idx));
idx = (t.Hour + t.Minute == HourValue & MinValue & (weekday(t) == 1 | weekday(t) == 7));
Consumption_Weekend(i) = mean(A(idx));

end

figureA = figure;
plot(TimeValue,Consumption_Weekday,'-*b');
hold on
plot(TimeValue,Consumption_Weekend,'--r');

[Fatemah Ebrahim]

I am getting this erorr:

The following error occurred converting from duration to double:

Undefined function 'double' for input arguments of type 'duration'. To convert

from durations to numeric, use the SECONDS, MINUTES, HOURS, DAYS, or YEARS

functions.

[Mehmed Saad]

please attach complete code.

[Fatemah Ebrahim]

Please see the link for the Excel sheet: https://drive.google.com/open?id=1rSA-0F683ndoA1JaEGHiSzYzDqImVsYQ

And I've attached the .m file below as well. So if you can see the same minute values repeat every hour meaning it's not taking into consideration the corresponding hour.

[Mehmed Saad]

so what you are trying to do is compute per hour cosumption of complete dataset (in weekdays and weekends)

Am i right?

[Fatemah Ebrahim]

Yes basically take the average at every hour and minute reading throughout the day over the whole timeperiod in the Excel sheet. I also tried running two loops one for hour and one for minute but that didn't work either.

[Walter Roberson]

That code is super inefficient.

idx = (t.Minute == MinValue & weekday(t) > 1 & weekday(t) < 7);

That matches all of t to find the entries that have the same Minute (of any hour) as the current unique time, and are Monday to Friday. But the same minute occurs in many different entries, and you are doing the same calculation over and over for all of them.

You should be looping over the unique minute entries. Or, more likely, you should be looping over minutes 0 to 59 without bothering to look for unique entries.

You should also be pre-allocating your output arrays.

I really recommend that you investigate findgroups() and splitapply() or grpstats()

[Fatemah Ebrahim]

Is there anyway I can do this without significantly changing my code? Because I run that same code 24 times on different Excel sheets so I would like to avoid writing a new code.

[Mehmed Saad]

I agree with walter. you need to re write the code.

Answer 1

[Mehmed Saad]

clc;
clear;
data = readtable('A_minute.xlsx');

% step 2: store date and time data as a datenum (DaT) and then convert to datetime (t)
DaT = datenum(data.Var1);% 
t = datetime(DaT, 'ConvertFrom','datenum');
TimeofDay = timeofday(t);

Now I made the first change here. I ceil the time to hours i.e. if time is between 0hrs and 1 hrs it is 1hrs, if bw 1hrs and 2hrs it is 2hrs.

TimeofDay = ceil(TimeofDay,'hours');

Now i apply unique and save 3rd argument in pos. what we have in 3rd argument is the unique value position by index

For example

[a,~,ic] = unique([4 5 5 4])

then the unqiue values are

a =

4 5

and there position in input matrix is

ic =

1

2

2

1

i.e. 1st unique value is 1 and 2nd unique value is 2. so where ever 1 exist means the 1st value was on this position and wherever 2 exist means 2nd unique value was here and so on (if there are others)

[UniqueTimeSteps,~,pos] = unique(TimeofDay);

We get 25 unique values because we have

00:00:00

01:00:00

.

.

.

24:00:00

So in pos wherever 1 exist means 00:00:00 and so on till 25.

Now it is your choice, either add 0:00:00 to 1:00:00 or to 24:00:00. I am adding it to 24:00:00

Remove the 1st value from UniqueTimeSteps

UniqueTimeSteps = UniqueTimeSteps(2:end);

Subtract 1 from pos. so that 01:00:00 corresponds to 1, 02:00:00 corresponds to 2 and so on

pos =pos -1; 

since we subtracted 1 from pos, so 00:00:00 pos changes from 1 to zero. Replace it with 24.

pos(pos==0) = 24;

% step 3: store the other columns from the excel data file
AptA = data(:,4);
AptA = table2array(AptA);

% step 4: for loop extracting the hour and indexing it through for weekday versus weekend
Consumption_Weekday = zeros(1,length(UniqueTimeSteps));
Consumption_Weekend = zeros(1,length(UniqueTimeSteps));
wd = weekday(t) > 1 & weekday(t) < 7;

Run for loops for 24 iterations (hours) which are in UniqueTImeSteps

for i = 1:length(UniqueTimeSteps)

suppose i =1, pos==1 is making all index equal to 1 true (for logical indexing). similarly for i=2,3,..24

idx = (pos == i & wd);
Consumption_Weekday(i) = mean(AptA(idx));
idx = (pos == i & (~wd));
Consumption_Weekend(i) = mean(AptA(idx));
end
MinuteValue= UniqueTimeSteps;
% step 5: plot
figureA = figure;
plot(MinuteValue,Consumption_Weekday,'-b');
hold on
plot(MinuteValue,Consumption_Weekend,'-r');
xlabel('Time'), ylabel('Aggregated Electric Demand (kW)'), ...
title('Unit A - Aggregated HVAC Electric Demand (kW)'), ...
legend('Weekdays','Weekends')

I hope this is what you want. Working code is attached.

[Mehmed Saad]

There are two codes attached At least run them before asking anything

Answer 2

[Walter Roberson]

No. Your code really needs a significant rewrite.

To run the same code multiple times, use a loop; in particular write a function that accepts a file name and does the work for you and returns the result. Then you only need one copy of the code.

t = readtable('A_minute.xlsx');
G = findgroups(minute(t.Var1), isweekend(t.Var1));
output = grpstats(t.P4_kWh', G, 'mean');
Consumption_Weekday = output(1:2:end,:);
Consumption_Weekend = output(2:2:end,:);

plot([Consumption_Weekday, Consumption_Weekend]);
legend({'Weekday', 'Weekend'})