How to calculate data higher than monthly mean value for one year data set?

Hi, I try to calculate the simple Mean High Water Level from one year tidal data set. The data is attached.

When I tried for one month data (September only), the code is work.

%Trial MSL and MHWL one month, September only
load TT_15_Sep.mat
MSL= retime(TT_15_Sep,'monthly','mean');
HWL = TT_15_Sep(TT_15_Sep.WL > MSL.WL,:);
MHWL= retime(HWL,'monthly','mean');

But when I tried to do same thing for one year dataset, it said that "the matrix dimension must agree". Anything wrong with this code?. Thank you.

%Trial MSL and MHWL for one year
load TT_15.mat
MSL= retime(TT_15,'monthly','mean');
HWL = TT_15(TT_15.WL > MSL.WL,:); % error in this part "Matrix dimensions must agree."
MHWL= retime(HWL,'monthly','mean');

[SALAH ALRABEEI]

Because your MSL is 12 means values ( for each month), and TT_15 is the over all, you cannot use logic operations unless both variables are equal or one of them is single value.

Consider using loop over each mean of month ( TT_15.WL(i)) the extract the mean of each month.

Answer 1

[dpb]

rowfun to the rescue.

TT_15.Month=month(TT_15.DT1);   % generate grouping variable; rowfun() needs it in table
TT_HHWL15=rowfun(@(wl) deal(mean(wl,'omitnan'),mean(wl(wl>mean(wl,"omitnan")),"omitnan")),TT_15, ...
                        'InputVariables','WL', ...
                        'GroupingVariables',"Month", ...
                        "NumOutputs",2, ...
                        'OutputVariableNames',{'HWL','HHWL'});

returns

ans =
  12×4 timetable
        DT1        Month    GroupCount      HWL       HHWL
    ___________    _____    __________    _______    _______
    01-Jan-2015     1.00      744.00      1884.39    2038.19
    01-Feb-2015     2.00      672.00      1719.78    1875.63
    01-Mar-2015     3.00      744.00      1750.48    1895.43
    01-Apr-2015     4.00      720.00      1804.50    1940.51
    01-May-2015     5.00      744.00      1920.51    2050.72
    01-Jun-2015     6.00      720.00      1866.68    2006.25
    01-Jul-2015     7.00      744.00      1816.60    1956.12
    01-Aug-2015     8.00      744.00      1777.96    1917.84
    01-Sep-2015     9.00      720.00      1766.41    1913.30
    01-Oct-2015    10.00      744.00      1776.43    1894.45
    01-Nov-2015    11.00      720.00      1862.92    2016.21
    01-Dec-2015    12.00      744.00      1933.37    2071.70
>> 

NB: There are NaN elements in many months so will return NaN w/o the 'omitnan' flag for mean

[Amra Rajuli]

Thank for your response. I tried your code, but I got error in first line:

TT_15.Month=month(TT_15); %Undefined function 'month' for input arguments of type 'timetable'.

then I modified the code by changing the variable TT_15 to TT_15.DT1. It work

TT_15.Month=month(TT_15.DT1); 

and the result was:

ans=
8760x1 double

1
1
1
...
12    

Then I run the second line and I get:

TT_HHWL15=rowfun(@(WL) deal(mean(WL,'omitnan'),mean(WL(WL>mean(WL,"omitnan")),"omitnan")),TT_15, ...
                        'InputVariables','WL', ...
                        'GroupingVariables',"Month", ...
                        "NumOutputs",2, ...
                        'OutputVariableNames',{'HWL','HHWL'});
% the error message:
% Error using tabular/rowfun (line 177) Table variable subscripts must be real positive integers, logicals, character vectors, or cell arrays of character vectors.

Do you have any clue? Thank you

[dpb]

Sorry about the typo for the grouping variable -- I didn't copy from command window but just typed in the Answer code and forgot the variable name..

But, with that correction, the code works here...

>> TT_15.Month=month(TT_15.DT1);   % generate grouping variable; rowfun() needs it in table
TT_HHWL15=rowfun(@(wl) deal(mean(wl,'omitnan'),mean(wl(wl>mean(wl,"omitnan")),"omitnan")),TT_15, ...
                        'InputVariables','WL', ...
                        'GroupingVariables',"Month", ...
                        "NumOutputs",2, ...
                        'OutputVariableNames',{'HWL','HHWL'});
>> TT_HHWL15
TT_HHWL15 =
  12×4 timetable
        DT1        Month    GroupCount      HWL       HHWL  
    ___________    _____    __________    _______    _______
    01-Jan-2015     1.00      744.00      1884.39    2038.19
    01-Feb-2015     2.00      672.00      1719.78    1875.63
    01-Mar-2015     3.00      744.00      1750.48    1895.43
    01-Apr-2015     4.00      720.00      1804.50    1940.51
    01-May-2015     5.00      744.00      1920.51    2050.72
    01-Jun-2015     6.00      720.00      1866.68    2006.25
    01-Jul-2015     7.00      744.00      1816.60    1956.12
    01-Aug-2015     8.00      744.00      1777.96    1917.84
    01-Sep-2015     9.00      720.00      1766.41    1913.30
    01-Oct-2015    10.00      744.00      1776.43    1894.45
    01-Nov-2015    11.00      720.00      1862.92    2016.21
    01-Dec-2015    12.00      744.00      1933.37    2071.70
>> 

[Amra Rajuli]

Thank you for your response. I finally found why the code did not work. If you may pay attention to

rowfun(@(wl) deal(mean(wl,'omitnan') % the omitnan part

the other omitnan was written "omitnan". Just synchronize the written format of 'omitnan' and finally the code work. Thank you very much. Really appreciate it.

PS: The output variable suppose to be:

'OutputVariableNames',{'MSL','MHWL'});

[dpb]

The use of a char string vs string for the omitnan flag is immaterial -- something else got munged in your copy...if you'll look at the result I pasted from command window that was taken by copying from the updated Answer and pasted, it also shows the different forms and ran just fine...

I can't reproduce the error here with anything I can think of to try -- oh! wait--what release of ML are you on? The message says the variable names must be "...character vectors, or cell arrays of character vectors." Is it possible you're using an earlier release of MATLAB (I'm on R2020B) that doesn't recognize the string variable for "Month" in the 'GroupingVariables' parameter value field I wonder?

Post the actual two lines that fail and work and your release number--I'll bet that's it before was updated to accept the string input form.