Count Occurrences of a Variable from Excel per Chronological Date
3 ビュー (過去 30 日間)
古いコメントを表示
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 件のコメント
採用された回答
Bhaskar R
2020 年 2 月 1 日
編集済み: Bhaskar R
2020 年 2 月 1 日
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 件のコメント
その他の回答 (0 件)
参考
カテゴリ
Help Center および File Exchange で Calendar についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!