Count occurrences of an event by date
14 views (last 30 days)
Show older comments
Sourangsu Chowdhury
on 25 Feb 2018
Answered: Peter Perkins
on 26 Feb 2018
I am facing an issue with counting number of occurrences by date, suppose I have an excel file where the data are as follows
1/1/2001 23
1/1/2001 29
1/1/2001 24
3/1/2001 22
3/1/2001 23
My preferred output should be
1/1/2001 3
2/1/2001 0
3/1/2001 2
Though 2/1/2001 doesnot appear in the input, I want that included in the output with 0 counts. Thank You!
0 Comments
Accepted Answer
Star Strider
on 25 Feb 2018
Edited: Star Strider
on 25 Feb 2018
Try this:
A = {'1/1/2001' 23
'1/1/2001' 29
'1/1/2001' 24
'3/1/2001' 22
'3/1/2001' 23};
Adt = datetime(A(:,1), 'Format','MM/dd/yyyy');
[H,E] = discretize(Adt, 'month');
Tally = accumarray(H, 1);
Result = table(E(1:numel(Tally))', Tally) % Note Transpose Operator (') To Force ‘E’ To Be A Column Vector
Result =
3×2 table
Var1 Tally
__________ _____
01/01/2001 3
02/01/2001 0
03/01/2001 2
This works with the data you posted.
Experiment with it with your complete data array to get the result you want.
EDIT — Corrected typographical error in the comment.
2 Comments
More Answers (2)
Peter Perkins
on 26 Feb 2018
If you have R2016b or later, this is one line, using a timetable. Given this timetable ...
>> d = datetime({'1/1/2001';'1/1/2001';'1/1/2001';'3/1/2001';'3/1/2001'});
>> tt = timetable([23;29;24;22;23],'RowTimes',d)
tt =
5×1 timetable
Time Var1
___________ ____
01-Jan-2001 23
01-Jan-2001 29
01-Jan-2001 24
01-Mar-2001 22
01-Mar-2001 23
... just call retime:
>> ttCounts = retime(tt,'monthly','count')
ttCounts =
3×1 timetable
Time Var1
___________ ____
01-Jan-2001 3
01-Feb-2001 0
01-Mar-2001 2
0 Comments
KSSV
on 25 Feb 2018
You can make your own dates in serial order.
T1=datetime('1/1/2001');
T2=datetime('3/1/2001');
T = T1:T2;
Then use ismember to get the common dates. Read about ismembet.
1 Comment
See Also
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!