Averaging columns in table using only rows where a condition is met.

I would like to find the mean (average) for values in columns 3, 4, 5, 6 using only rows where OOBS.night == 1. This average will be different than the entire column average, as I only want to include a subset of the column values.

I am open to suggestions - perhaps I am going about this wrong!

Thank you.

OOBS = table([hobo_times.OOBS23],[hobo_times.water_elevation_m_NAVD88],[tu_values_23'],[tu_values_27'],[tu_values_29'],[tu_values_35']);
OOBS.Properties.VariableNames = {'times','WtrLvlm','OOBS23','OOBS27','OOBS29','OOBS35'};

OOBS.night = (hour(OOBS.times)>=20 | hour(OOBS.times)<=6);
find(OOBS.night ==1 & OOBS.WtrLvlm>=0.33);

[Campion Loong]

This is really just 1-line of code using groupsummary. The first few lines are just making up fake data, since there is no example data attached:

% Make up some data
Times = (datetime(2021,10,1):minutes(10):datetime(2021,10,31))';
WtrLvlm = rand(length(Times),1);
OOBS23 = rand(length(Times),1);
OOBS27 = rand(length(Times),1);
OOBS29 = rand(length(Times),1);
OOBS35 = rand(length(Times),1);
night = timeofday(Times) > hours(18); % assume 'night' means later than 6PM
tt = timetable(Times, WtrLvlm, OOBS23, OOBS27, OOBS29, OOBS35, night)

tt = 4321×6 timetable
           Times            WtrLvlm     OOBS23       OOBS27      OOBS29     OOBS35     night
    ____________________    _______    _________    ________    ________    _______    _____

    01-Oct-2021 00:00:00    0.38644      0.89286     0.20368     0.93066    0.49296    false
    01-Oct-2021 00:10:00    0.65379      0.79538     0.58104     0.63038    0.50282    false
    01-Oct-2021 00:20:00    0.30412      0.46776     0.51774      0.9148     0.1635    false
    01-Oct-2021 00:30:00    0.23873      0.88916     0.35675     0.69045    0.46307    false
    01-Oct-2021 00:40:00    0.21866      0.76901     0.97638     0.64259      0.728    false
    01-Oct-2021 00:50:00    0.21378      0.66606     0.51613     0.84456    0.10005    false
    01-Oct-2021 01:00:00    0.25439      0.54039    0.049845     0.41393    0.63819    false
    01-Oct-2021 01:10:00    0.16109      0.33963     0.97461      0.3907    0.80241    false
    01-Oct-2021 01:20:00    0.80712      0.74499     0.81297     0.86231    0.92233    false
    01-Oct-2021 01:30:00    0.78628    0.0003465     0.50948      0.4305    0.22823    false
    01-Oct-2021 01:40:00    0.28834      0.55194     0.73841     0.29849    0.57742    false
    01-Oct-2021 01:50:00    0.69068      0.77565     0.95863     0.61898    0.38631    false
    01-Oct-2021 02:00:00    0.22047      0.10725     0.88691      0.5075    0.41322    false
    01-Oct-2021 02:10:00    0.57748      0.55464     0.56385     0.18775    0.40718    false
    01-Oct-2021 02:20:00     0.4007      0.48923     0.85602     0.56587    0.25958    false
    01-Oct-2021 02:30:00    0.61546      0.46406      0.7246    0.009929    0.79512    false

% This 1-liner is what you are actually after
NightAvg = groupsummary(tt,'night','mean')

NightAvg = 2×7 table
    night    GroupCount    mean_WtrLvlm    mean_OOBS23    mean_OOBS27    mean_OOBS29    mean_OOBS35
    _____    __________    ____________    ___________    ___________    ___________    ___________

    false       3271         0.50808         0.49655        0.49466        0.49572        0.49936  
    true        1050         0.49481         0.50501        0.51259        0.50066        0.51605  

% Now it's a slightly different 1-liner if you want to group
% by both 'night' and a 'WtrLvlm' threshold
% (like in your code example, but unlike your descriptions)
groupsummary(tt,["night" "WtrLvlm"],{'none', [0 0.33 Inf]}, "mean")

ans = 4×7 table
    night    disc_WtrLvlm    GroupCount    mean_OOBS23    mean_OOBS27    mean_OOBS29    mean_OOBS35
    _____    ____________    __________    ___________    ___________    ___________    ___________

    false    [0, 0.33)          1033         0.49008         0.4985        0.50889        0.49185  
    false    [0.33, Inf]        2238         0.49954        0.49289        0.48964        0.50283  
    true     [0, 0.33)           348         0.52147        0.50503        0.49881        0.50274  
    true     [0.33, Inf]         702         0.49685        0.51634        0.50158        0.52264  

Answer 1

[David Hill]

n_idx=hour(hobo_times.OOBS23)>=20 | hour(hobo_times.OOBS23)<=6;
m=mean([tu_values_23'(n_idx);tu_values_27'(n_idx);tu_values_29'(n_idx);tu_values_35'(n_idx)]);

[Joshua Himmelstein]

Thanks for the help! Adjusted it slightly as it wasn't working right off the bat!

n_idx = (find(OOBS.night ==1 & OOBS.WtrLvlm>=0.33))';
mean_turbidity = mean([OOBS.OOBS23(n_idx),OOBS.OOBS27(n_idx),OOBS.OOBS29(n_idx),OOBS.OOBS35(n_idx)]);