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

27 views (last 30 days)
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.

Accepted Answer

Siddharth Bhutiya
Siddharth Bhutiya on 5 Mar 2020
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');
  3 Comments
Siddharth Bhutiya
Siddharth Bhutiya on 6 Mar 2020
My original answer would give you daily and monthly averages by date so for example for daily you would have separate entires for 2-April-2010, 2-April-2011,....,2-April-2019.
I reread your question and I realized that when you mentioned daily/monthly average, you might want the mean of that day over all years (so just one entry for April 2, which is the mean of all April 2s over all the years), if that's what you are looking for then you could create separate variable for dayof year and month and then use them as the grouping variables in varfun as follows:
% FOR DAILY AVERAGE
% Add a Day variable and get rid of the Date
temp = data;
temp.Day = day(temp.Date,'dayofyear');
temp.Date = [];
% Now use varfun with @mean as your function and Day as your GroupingVariable
dailyAverage = varfun(@mean,temp,'GroupingVariables','Day');
% FOR MONTHLY AVERAGE
% Add a Month variable and get rid of the Date
temp = data;
temp.Month = month(temp.Date);
temp.Date = [];
% Now use varfun with @mean as your function and Day as your GroupingVariable
monthlyAverage = varfun(@mean,temp,'GroupingVariables','Month');

Sign in to comment.

More Answers (0)

Categories

Find more on Timetables 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!