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

13 ビュー (過去 30 日間)
Sanley Guerrier
Sanley Guerrier 2024 年 3 月 2 日
編集済み: cui,xingxing 2024 年 4 月 27 日 2:02
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");

サインインしてこの質問に回答する。

採用された回答

Star Strider
Star Strider 2024 年 3 月 2 日
Ran in:
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 件のコメント
Sanley Guerrier
Sanley Guerrier 2024 年 3 月 2 日
編集済み: Sanley Guerrier 2024 年 3 月 2 日
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 2024 年 3 月 2 日
Ran in:
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}
.

サインインしてコメントする。

その他の回答 (1 件)

cui,xingxing
cui,xingxing 2024 年 3 月 2 日
編集済み: cui,xingxing 2024 年 4 月 27 日 2:02
Ran in:
@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: cuixingxing150@gmail.com

カテゴリ

Help Center および File Exchange で Preprocessing Data についてさらに検索

タグ

Community Treasure Hunt

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

Start Hunting!

Translated by