Count Occurrences of a Variable from Excel per Chronological Date
3 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
CMatlabWold
am 1 Feb. 2020
Kommentiert: CMatlabWold
am 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
0 Kommentare
Akzeptierte Antwort
Bhaskar R
am 1 Feb. 2020
Bearbeitet: Bhaskar R
am 1 Feb. 2020
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);
4 Kommentare
Weitere Antworten (0)
Siehe auch
Kategorien
Mehr zu Calendar finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!