Daily mean of a hourly dataset

Hi,

To determine the annual daily maximum of a dataset, as attached by this question I have to convert my data. My data set is as follows:

[Year, Month, Day, Hour, info1, info2,info3,....] and is a txt-file.

To combine the different time info, I used the function datetime, as this would transfer the four columns into one Time-column. But as I try, the error "All input arguments must be tables" appears. Therefore, I was wondering what I'm doing wrong in my code.

Secondly, I would like to average my data over the days, so I get one data point for every day. As if I have the average of every day, I need to determine the yearly maximum, which I tried with the retime-function.

Can someone help me with what I am doing wrong?

clear; clc; close all;

filename = 'data_1991_2020_edited.txt';

delimiterIn = ' ';

headerlinesIn = 1;

Data = importdata(filename,delimiterIn,headerlinesIn);

%[A,delimiterOut]=importdata(filename)

Data1 = readtable(filename);

% Create datetime array

time_dep = datetime([Data1(:,1),Data1(:,2),Data1(:,3),Data1(:,4),zeros(size(Data1,1),1),zeros(size(Data1,1),1)]);

%TData = timetable(Times, Data1(:,6));

%YrMean = retime(TData, 'yearly', 'max'); % Yearly Maximum

%Max = [YrMean.Times.Year YrMean.Var1]

Answer 1

[Simon Chan]

Try the following:

filename = 'data_2001_2010.txt';
Data1 = readtable(filename,'VariableNamingRule','preserve');
Data1(:,1)=[];                                                              % Remove the 1st column
Data1.YYYYMMDD=datetime(string(Data1.YYYYMMDD),'InputFormat','yyyyMMdd');   % Convert to datetime format
Gmean=groupsummary(Data1,"YYYYMMDD",'day',"mean");                          % Calculate daily mean

[Murielle van Pampus]

For me this gives the following error:

Warning: Column headers from the file were modified to make them

valid MATLAB identifiers before creating variable names for the

table. The original column headers are saved in the

VariableDescriptions property.

Set 'PreserveVariableNames' to true to use the original column

headers as table variable names.

Error using groupfilter (line 236)

The specified method must return a scalar or a column vector with

the same number of rows as the method input.

Error in Assignment0_MD_van_Pampus_final_evaluation_v3 (line 6)

Gmean = groupfilter(Data1,'YYYYMMDD','day',@(x) mean(x));

% Calculate daily mean

[Murielle van Pampus]

I thought maybe if I edited the columns in excel this would be more easy to do with datetime(). But this also isn't working

clear; clc; close all;

filename = 'data_1991_2010_edited.txt';

%delimiterIn = ' ';

%headerlinesIn = 1;

%Data = importdata(filename,delimiterIn,headerlinesIn);

%[A,delimiterOut]=importdata(filename)

Data1 = readtable(filename);

% Create datetime array

time_dep = datetime([Data1(:,1),Data1(:,2),Data1(:,3),Data1(:,4),zeros(size(Data1,1),1),zeros(size(Data1,1),1)]);

%TData = timetable(Times, Data1(:,6));

%YrMean = retime(TData, 'yearly', 'max'); % Yearly Maximum

%Max = [YrMean.Times.Year YrMean.Var1]

[Simon Chan]

Edited the last line.

Typo error, function groupsummary should be used instead of function groupfilter.

[Murielle van Pampus]

For me it gives an error at 'VariableNamingRule'

Could it be it should be the following:

Data1=readtable(filename,'PreserveVariableNames',true)

[Simon Chan]

Use

Data1 = readtable(filename,'VariableNamingRule','preserve');

[Murielle van Pampus]

If I understand correctly, I can also determine the maximum with groupsummary. But then the function determine the maximum of every column right?

So, the function doesn't determine the maximum yearly value of FH and sets the other variables to the value in the row of the max FH. But the function determines the maximum value of each column.

How would I change the function after determining the average to the maximum value of FH and keep the datapoints of that row with the maximum FH value?

clear; clc; close all;

filename = 'data_2001_2010.txt';

Data1 = readtable(filename,'PreserveVariableNames',true);

Data1(:,1)=[]; % Remove the 1st column

Data1.YYYYMMDD=datetime(string(Data1.YYYYMMDD),'InputFormat','yyyyMMdd'); % Convert to datetime format

Gmean=groupsummary(Data1,"YYYYMMDD",'day',"mean"); % Calculate daily mean

Gmax=groupsummary(Data1,"YYYYMMDD",'year',"max");

%YrMean = retime(Gmean, 'yearly', 'max'); % Yearly Maximum

%Max = [YrMean.Times.Year YrMean.mean_FH]

[Murielle van Pampus]

The 'VariableNamingRule' still gives an error if I use it the way you suggest:

Error using readtable (line 223)

Invalid parameter name: VariableNamingRule.

Error in Assignment0_MD_van_Pampus_final_evaluation_v3 (line 3)

Data1 = readtable(filename,'VariableNamingRule','preserve');

Do you know why it gives this error on my computer?

[Simon Chan]

In this case, you need to use function groupfilter:

G2 = groupfilter(Data1,'YYYYMMDD','year',@(x) x==max(x),'FH')

Sorry about the 'VariableNamingRule' warning, it should be set to true

Data1 = readtable(filename,'VariableNamingRule',true);

[Murielle van Pampus]

Would you also know how to remove duplicates if the max value occured multiple times in a year. So for example the value of FH of 180 ocured 5 times in december. Currently it gives 5 values of 180 for 2020. Is there a argument to remove duplicates?

[Simon Chan]

You need to state the criteria to remove duplicates since the values on other columns are different.