MATLAB: With a data table, how to calculate the average for each day over a period of time

averagedatemeantable

Hi
I have this code that produces a table.
t= readtable('SS_none.xls'); % read as table
t.S1 = single(strcmp(t.ComplaintLocation, 'S1'));
t.S2 = single(strcmp(t.ComplaintLocation, 'S2'));
t.S3 = single(strcmp(t.ComplaintLocation, 'S3'));
% convert to date time
t.Date = datetime(t.Date, 'Format', 'MM/dd/yyyy');
% get unique dates
T1=datetime('01/01/2010');
T2=datetime('12/31/2019');
T=T1:T2;
unique_dates = T;
for ii =1: length(unique_dates)
tmp = t(ismember(t.Date, unique_dates(ii)), :);
% get unique dates
data(ii).Date = unique_dates(ii);
data(ii).S1 = sum(tmp.S1);
data(ii).S2 = sum(tmp.S2);
data(ii).S3 = sum(tmp.S3);
end
% convert structure to table
data = struct2table(data);
The table consists of the sum of complaints for three locations, S1, S2, and S,3 for each date from 2010 – 2019.
Now, I'd like to calculate the mean for each day over 10 years for each location.
For instance, the mean for S1 on April 2 during the 10 years might be 1. i.e. Over 10 years, the average complaints for location S1 on April 2nd was 1.
Then, I'd like to calculate the mean for each month over the 10 years for each location.
For instance, the mean for S2 in July over the 10 years might be 3. For the month of July, the average complaints over the 31 days was 3.
Third, I need the annual mean. The average number of complaints for the 365 days for a location over the course of the 10 years.
I truly appreciate if anyone can help me with this.

Best Answer

Since you are working with time-stamped data, you should convert the data into a timetable, as timetables provide a lot of useful functions for working with time-stamped data (here's a list).
One of the timetable functions include retime, that would allow you to resample or aggregate that data in your timetable. So you can easily calculate the daily or monthly averages as follows
% convert to a timetable
data = table2timetable(data);
% Use 'daily' timestep with 'mean' method to get the daily average
dailyAverage = retime(data,'daily','mean');
% Use 'monthly' timestep with 'mean' method to get the monthly average
monthlyAverage = retime(data,'monthly','mean');
Related Question