MATLAB Answers

Storing unique hour and minute values

1 view (last 30 days)
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));
figureA = figure;
hold on


Show 5 older comments
Walter Roberson
Walter Roberson on 11 May 2020
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
Fatemah Ebrahim on 11 May 2020
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
Mehmed Saad on 11 May 2020
I agree with walter. you need to re write the code.

Sign in to comment.

Accepted Answer

Mehmed Saad
Mehmed Saad on 11 May 2020
Edited: Mehmed Saad on 11 May 2020
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 =
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
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));
MinuteValue= UniqueTimeSteps;
% step 5: plot
figureA = figure;
hold on
xlabel('Time'), ylabel('Aggregated Electric Demand (kW)'), ...
title('Unit A - Aggregated HVAC Electric Demand (kW)'), ...
I hope this is what you want. Working code is attached.

  1 Comment

Mehmed Saad
Mehmed Saad on 11 May 2020
There are two codes attached At least run them before asking anything

Sign in to comment.

More Answers (1)

Walter Roberson
Walter Roberson on 11 May 2020
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'})


Sign in to comment.


Translated by