# Find Sum and Time from a Table and Skip Leap Year in Dates

2 views (last 30 days)
Joe Sheckles on 26 Mar 2020
Commented: Peter Perkins on 14 Apr 2020
Good Day,
I'll attach my code and spreadsheet. But, I'll also paste my code below.
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);
% 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');
1. My first goal was to tabulate the number of complaints per location, S1, S2, and S3. This was achieved.
Location S1: 09/03: 1.2
This was not acheived in the "DailyAverage" part of the code that was created.
Also there is the problem of leap years in 2012 and 2016 where the data becomes skewed.
3. My third goal is find the montly average over the ten years. With this ten years of data, how many complaints does January average?
4. My fourth goal is find the total number of complaints per year per location. For instance, S2 had 80 complaints in 2010, 87 complaints in 2011, 91 complaints in 2012, etc...
I know this is a lot. I'd appreciate any help
Peter Perkins on 14 Apr 2020