# Count Occurrences of a Variable from Excel per Chronological Date

3 views (last 30 days)
CMatlabWold on 1 Feb 2020
Commented: CMatlabWold on 3 Feb 2020
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

Bhaskar R on 1 Feb 2020
Edited: Bhaskar R on 1 Feb 2020
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);
CMatlabWold on 2 Feb 2020
Hi , yes. It does work without the error message, at times. You are right.
When I look at the table "data", how can I get the dates to begin at 01/01/2010 and end at 12/31/2018, even when there are no complaints on that day.
Also, it sums the complaints per location per day. But then it will list the same date with the tally over and over again. For instance, on 01/25/10 there are 6 complaints for S3, and it sums and lists it. But then it also lists 01/25/10 6 times
CMatlabWold on 3 Feb 2020
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/2018');
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);