How to calculate hourly average value for measured Temperature, CO2, RH and Rid in 5 minutes interval

Hello all.

Hopfully you all stay well and safe.

I have Temperature, CO2, RH and Rid measured each 5 minutes interval for about 36 days, (from 09/04/2020 to 14/05/2020) about 10080 rows in total. From these data I need to calculate the hourly average value for these measured Temperature, CO2, RH and Rid which means that eventually I must have 24 hourly averaged values per day.

I would appreciate any ideas on the matter!

I have attached the excel file I'm working on.

Regards

Answer 1

[Walter Mabry]

The loop below breaks up you time stamps into hours and creates a new vector with the mean value for each hour of the provided data.

Temp = data1(:,4);
i = 12:12:840;
for k = 1:length(i)
    DailyAvg(k) = mean(Temp((i(k)-11):i(k)));
end

[Walter Mabry]

Let me know if this is helpful.

[Gadelhag M Omar Mohmed]

Hi

Thank you for your time and help.

It is giving this error:

Error in mean (line 127)

y = sum(x, dim, flag) ./ mysize(x,dim);

Error in trycode (line 8)

DailyAvg(k) = mean(Temp((i(k)-11):i(k)));

[Walter Mabry]

post what your code is

[Walter Mabry]

Interested to see your code because what i posted worked for me. All i did was use the import tool to bring in your file as a numeric matrix

[Gadelhag M Omar Mohmed]

clc, clear

[~,~,data1]= xlsread('data.xlsx','Greenhouse climate','A1:E10082');

Temp = data1(:,4);

i = 12:12:840;

for k = 1:length(i)

DailyAvg(k) = mean(Temp((i(k)-11):i(k)));

end

[Walter Mabry]

you are importanting the data as a cell. just import the numbers as an array with out the headings. You could use readmatrix() or just use the import data tool under the home tab

[Walter Mabry]

or just convert you cell to an array without headings

Answer 2

[Cris LaPierre]

Use groupsummary. I suggest combining your dates and times into a single datetime varlable to make this easier.

opts = detectImportOptions("Gadelhagdata.xlsx","Range",'A:K');
opts = setvartype(opts,"time","duration");
opts = setvaropts(opts,"time","InputFormat","hh:mm:ss");
data = readtable("Gadelhagdata.xlsx",opts);

% combine data and time
data.date = data.date + data.time;
data.time = [];

hrAvg = groupsummary(data,"date","hour","mean")

hrAvg = 841x6 table
         hour_date          GroupCount    mean_CO2    mean_Temperature    mean_RH    mean_Rid
    ____________________    __________    ________    ________________    _______    ________

    09-Apr-2020 17:00:00        12         407.94          14.628         51.458      44.909 
    09-Apr-2020 18:00:00        12         402.29          11.935         59.218      12.554 
    09-Apr-2020 19:00:00        12         402.71          11.103         61.477           0 
    09-Apr-2020 20:00:00        12         402.04          10.733         63.702           0 
    09-Apr-2020 21:00:00        12         402.33          10.162         65.337           0 
    09-Apr-2020 22:00:00        12         405.42             9.6         71.393           0 
    09-Apr-2020 23:00:00        12         415.88          8.0933         86.765           0 
    10-Apr-2020 00:00:00        12          438.6            7.32          92.14           0 
    10-Apr-2020 01:00:00        12         456.71           7.825         93.467           0 
    10-Apr-2020 02:00:00        12         455.62          7.0467          95.45           0 
    10-Apr-2020 03:00:00        12         464.56           4.975          97.17           0 
    10-Apr-2020 04:00:00        12         468.23          3.8833         97.365           0 
    10-Apr-2020 05:00:00        12         474.52          3.0817         97.262     0.44142 
    10-Apr-2020 06:00:00        12         466.52          4.0617          95.98      27.312 
    10-Apr-2020 07:00:00        12         439.96           8.615         84.262      101.31 
    10-Apr-2020 08:00:00        12         417.48          15.528         62.305      229.32 

[Gadelhag M Omar Mohmed]

Thank you for your answer, it looks easy, but it is still giving me an error:

Error using matlab.io.ImportOptions/setvaropts (line 485)

Expected a name or numeric index of a variable name.

Error in trycode (line 7)

opts = setvaropts(opts,"time","InputFormat","hh:mm:ss");

Here is my code:

clc, clear

opts = detectImportOptions("data.xlsx","Range",'A:K');

opts = setvartype(opts,"time","duration");

opts = setvaropts(opts,"time","InputFormat","hh:mm:ss");

data_2 = readtable("data.xlsx",opts);

% combine data and time

data_2.date = data_2.date + data_2.time;

data_2.time = [];

hrAvg = groupsummary(data_2,"date","hour","mean")

[Cris LaPierre]

What version of MATLAB are you using?

[Gadelhag M Omar Mohmed]

R2018b

[Cris LaPierre]

Double quotes were not supported in setvaropts yet. Change that line to single quotes

opts = setvaropts(opts,'time','InputFormat','hh:mm:ss');

[Gadelhag M Omar Mohmed]

Your help was crucial! Thank you a million times!!

[Cris LaPierre]

Did you accept the answer you meant to?

Answer 3

[Sean de Wolski]

Read it in as a timetable readtimetable then call retime which does exactly what you want.

t = readtimetable('yourfile')
fiveminutemean = retime(t, 'Regular', 'mean', 'TimeStep', minutes(5))