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

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.

Answer 1

[Siddharth Bhutiya]

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');

[Siddharth Bhutiya]

Also to convert the data obtained from your file to a timetable that contains Date and counts of S1,S2 and S3 on that date, you could try using unstack with a dummy variable as follows instead of the logic mentioned in the example:

(If you are using 2019a or later then you can also use readtimetable to directly read the data into a timetable)

tt = readtimetable('SS_none.xls');

% Add a dummy variable, use int32 instead of doubles otherwise your data
% will contain NaNs instead of zeros in the unstack output
tt.Dummy = ones(height(tt),1,'int32');

% Now unstack your timetable as follows using sum as your aggregation function
data = unstack(tt,'Dummy','ComplaintLocation','AggregationFunction',@sum);

[Siddharth Bhutiya]

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');

[Joe Sheckles]

That's perfect. Thanks!