How to get daily max, min, and mean from a timetable?
19 views (last 30 days)
Show older comments
Sanley Guerrier
on 2 Mar 2024
Edited: cui,xingxing
on 27 Apr 2024
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");
0 Comments
Accepted Answer
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)
result = groupsummary(tbl,"time","day",["max","min","mean"],["S1","S2","S3"])
.
2 Comments
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)
result = groupsummary(tbl,"time","day",["max","min","mean"],["S1","S2","S3"])
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{:}
.
More Answers (1)
cui,xingxing
on 2 Mar 2024
Edited: cui,xingxing
on 27 Apr 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
result = groupsummary(tbl,"time","day",["max","min","mean"])
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
See Also
Categories
Find more on Data Preprocessing in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!