How to get daily max, min, and mean from a timetable?

Hi all;

I have a timetable and I want to get the daily min, max, mean values from the table and store them in a new table. Can someone help me with that? Thank you!

tbl = readtable("T.xlsx");

tbl = table2timetable(tbl);

result = groupsummary(tbl,"time","day",["max","min","mean"],"S1","S2","S3");

Answer 1

[Star Strider]

Your groupsummary call is correct, except for not putting the data variables in square brackets (or curly braces).

With that change, it works —

tbl = readtable("T.xlsx");
tbl = table2timetable(tbl)

tbl = 240×3 timetable
            time             S1       S2       S3  
    ____________________    _____    _____    _____

    01-Dec-2023 00:09:00    22.82    25.88    30.74
    01-Dec-2023 00:19:00     22.1     25.7    30.56
    01-Dec-2023 00:29:00    21.56    25.52    30.56
    01-Dec-2023 00:39:00    21.02    25.34    30.74
    01-Dec-2023 00:49:00    20.48    24.98    30.56
    01-Dec-2023 00:59:00    20.12     24.8    30.56
    01-Dec-2023 01:09:00    19.76    24.44    30.56
    01-Dec-2023 01:19:00     19.4    24.26    30.56
    01-Dec-2023 01:29:00    19.04    24.08    30.56
    01-Dec-2023 01:39:00    18.86     23.9    30.56
    01-Dec-2023 01:49:00     18.5    23.72    30.56
    01-Dec-2023 01:59:00    18.32    23.54    30.56
    01-Dec-2023 02:09:00    18.14    23.36    30.56
    01-Dec-2023 02:19:00    17.96    23.18    30.38
    01-Dec-2023 02:29:00    17.78       23    30.38
    01-Dec-2023 02:39:00    17.42    22.82    30.38

result = groupsummary(tbl,"time","day",["max","min","mean"],["S1","S2","S3"])

result = 2×11 table
     day_time      GroupCount    max_S1    min_S1    mean_S1    max_S2    min_S2    mean_S2    max_S3    min_S3    mean_S3
    ___________    __________    ______    ______    _______    ______    ______    _______    ______    ______    _______

    01-Dec-2023       144        40.64     16.88     24.508      33.8     21.74      26.07     30.92     29.12     30.161 
    02-Dec-2023        96        41.72     19.04     26.819     34.34     22.46      26.42     30.56     29.12     29.684 

.

[Sanley Guerrier]

Thank you, Star Strider.

Is it possible to point out the hour and minute when the max, min, and mean occur?

[Star Strider]

As always, my pleasure!

‘Is it possible to point out the hour and minute when the max, min, and mean occur?’

Yes! Although the maximum and minimum values may not be unique (there could be several matches) and there may be no match at all for the mean.

I checked this by examining the results each step produced (the commented-out section) since the results at first seemed unusual. It turns out that the code does exactly what it is supposed to do. You can check that as well buy un-commenting some or all of those assignments. The results are not voluminous.

The ‘Times’ cell array contains the ‘result’ variable name, followed by the matching times for those values. Each ‘Times’ cell spans both days.

This took a few minutes to code —

tbl = readtable("T.xlsx");
tbl = table2timetable(tbl)

tbl = 240×3 timetable
            time             S1       S2       S3  
    ____________________    _____    _____    _____

    01-Dec-2023 00:09:00    22.82    25.88    30.74
    01-Dec-2023 00:19:00     22.1     25.7    30.56
    01-Dec-2023 00:29:00    21.56    25.52    30.56
    01-Dec-2023 00:39:00    21.02    25.34    30.74
    01-Dec-2023 00:49:00    20.48    24.98    30.56
    01-Dec-2023 00:59:00    20.12     24.8    30.56
    01-Dec-2023 01:09:00    19.76    24.44    30.56
    01-Dec-2023 01:19:00     19.4    24.26    30.56
    01-Dec-2023 01:29:00    19.04    24.08    30.56
    01-Dec-2023 01:39:00    18.86     23.9    30.56
    01-Dec-2023 01:49:00     18.5    23.72    30.56
    01-Dec-2023 01:59:00    18.32    23.54    30.56
    01-Dec-2023 02:09:00    18.14    23.36    30.56
    01-Dec-2023 02:19:00    17.96    23.18    30.38
    01-Dec-2023 02:29:00    17.78       23    30.38
    01-Dec-2023 02:39:00    17.42    22.82    30.38

result = groupsummary(tbl,"time","day",["max","min","mean"],["S1","S2","S3"])

result = 2×11 table
     day_time      GroupCount    max_S1    min_S1    mean_S1    max_S2    min_S2    mean_S2    max_S3    min_S3    mean_S3
    ___________    __________    ______    ______    _______    ______    ______    _______    ______    ______    _______

    01-Dec-2023       144        40.64     16.88     24.508      33.8     21.74      26.07     30.92     29.12     30.161 
    02-Dec-2023        96        41.72     19.04     26.819     34.34     22.46      26.42     30.56     29.12     29.684 

VN = result.Properties.VariableNames;

for k1 = 1:size(result,2)-2
    kk = (k1-1)+1;
    kq = (k1-1)+3;
    vbl = VN{kq};
    vblnr = extractAfter(vbl,'_');
    LvS = ismember(tbl.(vblnr), result{:,kq});
    % VarName = vbl                                         % Un-Comment These To See The Essential Results
    % LookFor = result{:,kq}
    % CheckTime = tbl.time(LvS)
    % CheckData = tbl.(vblnr)(LvS)
    % CheckRows = tbl(LvS,:)
    Times{k1,:} = {vbl, tbl.time(LvS).'};
end

Times{:}

ans = 1×2 cell array
    {'max_S1'}    {[01-Dec-2023 12:49:00    02-Dec-2023 12:59:00]}

ans = 1×2 cell array
    {'min_S1'}    {1×8 datetime}

ans = 1×2 cell array
    {'mean_S1'}    {1×0 datetime}

ans = 1×2 cell array
    {'max_S2'}    {1×12 datetime}

ans = 1×2 cell array
    {'min_S2'}    {1×13 datetime}

ans = 1×2 cell array
    {'mean_S2'}    {1×0 datetime}

ans = 1×2 cell array
    {'max_S3'}    {1×49 datetime}

ans = 1×2 cell array
    {'min_S3'}    {1×11 datetime}

ans = 1×2 cell array
    {'mean_S3'}    {1×0 datetime}

.

Answer 2

[xingxingcui]

@Sanley Guerrier

Your 5th input parameter should put all the variables on one positional parameter. or just leave it out and default to all variables "S1","S2","S3"

tbl = readtable("T.xlsx");
tbl = table2timetable(tbl);
head(tbl) % preview table

            time             S1       S2       S3  
    ____________________    _____    _____    _____

    01-Dec-2023 00:09:00    22.82    25.88    30.74
    01-Dec-2023 00:19:00     22.1     25.7    30.56
    01-Dec-2023 00:29:00    21.56    25.52    30.56
    01-Dec-2023 00:39:00    21.02    25.34    30.74
    01-Dec-2023 00:49:00    20.48    24.98    30.56
    01-Dec-2023 00:59:00    20.12     24.8    30.56
    01-Dec-2023 01:09:00    19.76    24.44    30.56
    01-Dec-2023 01:19:00     19.4    24.26    30.56

result = groupsummary(tbl,"time","day",["max","min","mean"])

result = 2×11 table
     day_time      GroupCount    max_S1    min_S1    mean_S1    max_S2    min_S2    mean_S2    max_S3    min_S3    mean_S3
    ___________    __________    ______    ______    _______    ______    ______    _______    ______    ______    _______

    01-Dec-2023       144        40.64     16.88     24.508      33.8     21.74      26.07     30.92     29.12     30.161 
    02-Dec-2023        96        41.72     19.04     26.819     34.34     22.46      26.42     30.56     29.12     29.684 

writetable(result,"result.xlsx")

-------------------------Off-topic interlude, 2024-------------------------------

I am currently looking for a job in the field of CV algorithm development, based in Shenzhen, Guangdong, China,or a remote support position. I would be very grateful if anyone is willing to offer me a job or make a recommendation. My preliminary resume can be found at: https://cuixing158.github.io/about/ . Thank you!

Email: [e-mail]

[Sanley Guerrier]

Thank you, Cui.