Plotting Hourly,Daily,Monthly Energy Consumption

I have a data file contianing the time and energy consumption for a small office. the data was collected every hour for a year.

firstly i would like to caterozige the data into days and months so i can sum thier energy consumptions

• how would find catorgize the data in such ways

I am unfamilar with datetime functions, ive tried to use this funtion in multiple ways unsucessfully. I could resort to using the Time coloumn in the data file to catogize the data but ive been recommneded datetime functions for the simplcity.

-for hourly, my coded attempt looked like the following:

Electricity=xlsread(['Book1','Sheet1','B:B');]

t=[datetime(2018,1,1,1):datetime(2018,end,end,end)].';    % build a sample dataset...
tt=timetable(t,randn(size(t))+10);              % just some data to go with the dates
plot(tt.t,tt.Electricity) 

secondly I want to appropiately label the data so that each point on the x axis corresponds to the hour,day,month.

• how would one label these points accordining for each plot

I am fiamilar with xlabel but that is just for the axis as a whole. Ive tried using timetables and datetick but these functions are confusing. Ive found some resources on simular questions. One i thought was helpful is found at: https://www.mathworks.com/support/search.html/answers/447221-plotting-timetable-data-how-to-add-x-axis-ticks-labels-and-gridlines-on-1st-and-15th-day-of-each-m.html?fq[]=asset_type_name:answer&fq[]=category:support/axis-labe1116&page=1

but this resources using functions that are hard for me to comprehend and follow in logic.

any help understanding these functions or suggestions for either question are greatly aprecciated.

thank you.

[Cris LaPierre]

You forgot to attach your spreadsheet. You can use the paperclip icon to do that.

Answer 1

[Cris LaPierre]

First, the first column of your table contains date and time. The easiest thing to do is to read that in along with the rest of the data in your spreadsheet. It is missing the year, so I add that to the datetimes after loading.

This will load your data to a timetable. See this page on how to Access Data in Tables.

One other note. MATLAB does not support the 24:00:00, so those times get read in a NaT (not a time). In MATLAB, 24:00:00 would be inputted as 0:00:00 of the next day. All that means is I had to do a little correcting to replace the NaTs with the correct time as MATLAB expects it.

opts = detectImportOptions("Book1.xlsx");
opts = setvartype(opts,"Time","datetime");
opts = setvaropts(opts,"Time","InputFormat","MM/dd HH:mm:ss");
E = readtimetable('Book1.xlsx',opts);

% Add in the year
E.Time.Year = 2018;

% fix the NaTs by finding the time just before and adding 1 hour to it.
ind = 1:height(E);
ind = ind(ismissing(E.Time));
E.Time(ind) = E.Time(ind-1) + hours(1)

E = 8760×2 timetable
         Time         Electricity       Gas    
    ______________    ___________    __________

    01/01 01:00:00    1.7272e+07     6.4815e+07
    01/01 02:00:00    1.5193e+07     4.4021e+07
    01/01 03:00:00    1.6268e+07     5.1038e+07
    01/01 04:00:00    1.5193e+07     3.9435e+07
    01/01 05:00:00    1.6268e+07      4.199e+07
    01/01 06:00:00    1.5193e+07     3.3017e+07
    01/01 07:00:00    1.6268e+07     4.0699e+07
    01/01 08:00:00    1.3377e+07     3.2679e+07
    01/01 09:00:00    1.0817e+07     3.8633e+07
    01/01 10:00:00    9.7427e+06     3.2319e+07
    01/01 11:00:00    1.0817e+07     4.1469e+07
    01/01 12:00:00    9.7427e+06     2.9901e+07
    01/01 13:00:00    1.0817e+07     4.0286e+07
    01/01 14:00:00    9.7427e+06     3.0191e+07
    01/01 15:00:00    1.0817e+07     3.6504e+07
    01/01 16:00:00    9.7427e+06     2.9119e+07

Since the data is already hourly, you could just plot the data to view that data:

plot(E.Time,E.Electricity)

With your data in a timetable, you can use the retime function to get daily, yearly, and monthly summaries fairly easily (see here). You can specify how you want to combine the data. Below I sum to get total electric consumption. You can learn more about how to specify other methods here.

% weekly sum
figure
Ewk = retime(E,'weekly','sum')

Ewk = 53×2 timetable
         Time         Electricity       Gas    
    ______________    ___________    __________

    12/31 00:00:00    3.5225e+09     3.4084e+09
    01/07 00:00:00    3.7675e+09     3.9116e+09
    01/14 00:00:00    3.6029e+09     4.6919e+09
    01/21 00:00:00    3.7445e+09     1.1969e+09
    01/28 00:00:00    3.7823e+09     3.5734e+09
    02/04 00:00:00    3.7914e+09     3.9039e+09
    02/11 00:00:00    3.7806e+09     3.4974e+09
    02/18 00:00:00    3.2752e+09     5.8686e+08
    02/25 00:00:00    3.7075e+09      7.883e+08
    03/04 00:00:00    3.6827e+09     7.7418e+08
    03/11 00:00:00    3.7084e+09     1.3586e+09
    03/18 00:00:00    3.7173e+09     6.3148e+08
    03/25 00:00:00    3.7158e+09     4.4476e+08
    04/01 00:00:00    3.7148e+09      8.766e+08
    04/08 00:00:00     3.905e+09     4.0579e+08
    04/15 00:00:00    4.1544e+09     2.2464e+08

plot(Ewk.Time,Ewk.Electricity)

At this point, it would be very easy to also visualize Gas consumption. As an example, here is the code for visualizing the mean hourly consumption of gas each month.

% Monthly hourly mean
figure
Em = retime(E,'Monthly','mean');
plot(Em.Time,Em.Gas)