Count occurrences of an event by date

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!

Answer 1

[Star Strider]

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.

[Sourangsu Chowdhury]

Thanks a lot! This works

[Star Strider]

As always, my pleasure!

Answer 2

[Peter Perkins]

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

Answer 3

[KSSV]

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.

[Sourangsu Chowdhury]

Hi,

clc
clear
[Value, Time] = xlsread('F:\1km\fire\2001-02\2001_02.xlsx','Sheet1','A2:D159','',@convertSpreadsheetExcelDates);
tm=datenum(Time);
val=Value(:,4);
data=[tm val];
% a=(datestr(tm));
T1=datetime('9/23/2001');
T2=datetime('6/23/2002');
T = T1:T2;
tm_all=datenum(T);
[l idx] = ismember(tm_all,data(:,1));
out = data(idx,:);

This does not seem to work, the size of data is 158x2 and length of tm_all is 274