Count occurrences of an event by date

Question

Sourangsu Chowdhury 2018-2-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/384653-count-occurrences-of-an-event-by-date

回答： Peter Perkins 2018-2-26

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Star Strider 2018-2-25

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/384653-count-occurrences-of-an-event-by-date#answer_306966

编辑：Star Strider 2018-2-25

在 MATLAB Online 中打开

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 个评论
显示无隐藏无

Sourangsu Chowdhury 2018-2-25

Thanks a lot! This works

Star Strider 2018-2-25

As always, my pleasure!

请先登录，再进行评论。

Answer 2

Peter Perkins 2018-2-26

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/384653-count-occurrences-of-an-event-by-date#answer_307161

在 MATLAB Online 中打开

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 3

KSSV 2018-2-25

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/384653-count-occurrences-of-an-event-by-date#answer_306947

在 MATLAB Online 中打开

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Sourangsu Chowdhury 2018-2-25

编辑：Sourangsu Chowdhury 2018-2-25

在 MATLAB Online 中打开

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,:);