MATLAB: Count Occurrences of a Variable from Excel per Chronological Date

spreadsheettally

I have a spreadsheet with complaints made from one of three locations on a variety of dates from 1/1/2010 to 12/31/2018. I need to tally the number of complaints per location per day for each day of the year for the 9 year time span.
For instance, my first 8 entries are:
1/7/2010 S1
1/25/2010 S3
1/25/2010 S3
1/25/2010 S3
1/25/2010 S3
1/25/2010 S3
1/25/2010 S3
1/27/2010 S1
where S1, S2, and S3 are the complaint locations (There are only 3).
I would like this to read
Date S1 S2 S3
1/1/2010 0 0 0
1/2/2010 0 0 0
etc…
1/25/2010 0 0 6
1/26/2010 0 0 0
1/27/2010 1 0 0
etc…
I appreciate if anyone can help me on this

Best Answer

t= readtable('SS.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

unique_dates = t.Date;
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);
Related Question