Count Occurrences of a Variable from Excel per Chronological Date

6 次查看（过去 30 天）
CMatlabWold
CMatlabWold 2020-2-1
评论： CMatlabWold 2020-2-3
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
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 个评论
显示 2更早的评论
CMatlabWold
CMatlabWold 2020-2-2
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
CMatlabWold 2020-2-3
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);

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by