Count Occurrences of a Variable from Excel per Chronological Date

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

Answer 1

[Bhaskar R]

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

[CMatlabWold]

Hi R. Bhaskar,

Thank you for your answer.

I did encounter an error message:

"Subscripting a table using linear indexing (one subscript) or multidimensional indexing (three or more subscripts) is not supported. Use a row subscript and a variable subscript."

This occurred at line 12: data(ii).Date = unique_dates(ii);

Would you know how to fix this?

Thanks,

C

[Bhaskar R]

It is a tasted code in MATLAB version 2016a using your provided file. I don't know why did you get the error. Have you applied same code i posted?

[CMatlabWold]

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]

I'm all set, R. Bhaskar. Thank you for all your help. I made a little adjustment. Now, evertyhing is perfect. Thanks again!

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

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