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

8 views (last 30 days)
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");

Accepted Answer

Star Strider
Star Strider on 2 Mar 2024
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
.
  2 Comments
Sanley Guerrier
Sanley Guerrier on 2 Mar 2024
Edited: Sanley Guerrier on 2 Mar 2024
Thank you, Star Strider.
Is it possible to point out the hour and minute when the max, min, and mean occur?
Star Strider
Star Strider on 2 Mar 2024
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}
.

Sign in to comment.

More Answers (1)

cui,xingxing
cui,xingxing on 2 Mar 2024
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")

Categories

Find more on Preprocessing Data in Help Center and File Exchange

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!