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

2 views (last 30 days)
Good Day,
I'll attach my code and spreadsheet. But, I'll also paste my code below.
t= readtable('SS_032620.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);
% 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.
  2. My second goal was to tabulate the average of complaints for each day of the year over the course of 10 years. For instance, hypothetically, if on September 3, there were the following chronological complaints for location S1: 2010 had 1, 2011 had 2, 2012 had 0, 2013 had 1, 2014 had 1, 2015 had 3, 2016 had 1, 2017 had 2, 2018 had 0, 2019 had 1, then I would like an average calculated: (1+2+0+1+1+3+1+2+0+1)/10 such that it would read
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

Answers (0)

Categories

Find more on Dates and Time in Help Center and File Exchange

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!