Is it possible to aggregate data in a timetable with a weighted daily mean?
8 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Hannes Schenk
le 9 Nov 2021
Réponse apportée : Seth Furman
le 10 Nov 2021
I have a timetable T with one or more entries corresponding to a single date:
With the retime function it is possible to aggregate the data with a daily mean like this:
T_agg = retime(T,'daily','mean');
However, what I really want do compute is the weighted daily average like this:
WeightedMean = sum(Measurement.*Weight)./sum(Weight);
where the variable "Measurement" and "Weight" are nx1 column vectors, where n is the number of data points in a single day.
The correct result for example for the 02-11-2021 is 374.06, opposed to the 494.58 resulting from the conventional mean.
My question is now, if it is possible to compute the weighted mean with the retime function by using a custom function handle of the aggregation method? If yes, how? Unfortunately, the number of data entries per day is not always 6. Unlike the figure suggests, the number of data entries per day varies.
I imagine a solution of this type:
WeightMean = @(Mea,Weight)sum(Mea.*Weight)./sum(Weight);
T_agg = retime(T,'daily',WeightMean,Mea,Weight);
Thank you for your help, I can't seem to figure this out myself.
EDIT: Attatched you can find the timetable as .mat-file
3 commentaires
Adam Danz
le 9 Nov 2021
One approach would be to add a 4th column containing the weighted measurments and then use retime on the new column.
Réponse acceptée
Seth Furman
le 10 Nov 2021
Another way to approach this is to
1) Copy the timetable.
2) Shift the copied timetable's row times to the start of the day.
3) Use rowfun with the weighted mean function handle you defined above, grouping by the row-times (i.e. by day).
load timetable.mat
T_agg = T;
T_agg.Properties.RowTimes = dateshift(T_agg.Properties.RowTimes, "start", "day");
weightedMean = @(M, W) sum(M .* W) ./ sum(W);
T_agg = rowfun(weightedMean, T_agg, "GroupingVariables", T_agg.Properties.DimensionNames{1}, "OutputVariableNames", "WeightedMean");
head(T_agg)
%
% ans =
%
% 8×2 timetable
%
% sample_date GroupCount WeightedMean
% ___________ __________ ____________
%
% 27-07-2020 1 313.55
% 03-08-2020 2 493.64
% 10-08-2020 2 234.58
% 17-08-2020 2 33.334
% 24-08-2020 2 63.589
% 31-08-2020 2 102.61
% 07-09-2020 2 492.55
% 14-09-2020 2 279.11
0 commentaires
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Timetables dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!