How to compute the frequency of hourly/daily observation?

Hi everyone,

I want to compuet the hourly/daily number of observations. My data consistes of six colums (YYYY MM DD HH MM SS). Each date-time entry show one observation.

The measurements are uneven i.e. 0 to 100 etc. From the data set presnted below: I am interested to calcualet the hourly observations from 2015-05-06-10-35-00 to 2015-05-08-22-35-00 and same for the daily observations. (data also attached in excel sheet).

Thank you!

Answer 1

[Cris LaPierre]

Your spreadsheet is empty.

I would load the data using readtable.

I would then create a new column and use datetime to convert YYYY MM DD HH MM SS to a datetime.

I would then use groupsummary to calculate a summary using 'hour' or 'day' as my grouping method.

[aa]

Apology, here is the spreadsheet.

[Cris LaPierre]

Ok. There is only time data in your spreadsheet. Without actual data to summarize, this is not very helpful.

[aa]

this the actual data with mssing enteries for example most of the time there is no event so we skip that

[aa]

Apology, I have modified the file and attach a new data set for your reference

[Cris LaPierre]

I guess I'm just misunderstanding your data set. There are just 6 columns of data, containing Y, M, d, H, m, s, and you want to know how many there are per hour?

The groupcount result from groupsummary will tell you that.

data1 = readtable("aaTest_data_modified.xlsx");
data2 = readtable("aaTest_data.xlsx");
data = [data1;data2];
data.timestamp = datetime(data{:,1:6});
data(:,1:6)=[];

hrAvg = groupsummary(data,"timestamp","hour")

hrAvg = 81x2 table
       hour_timestamp       GroupCount
    ____________________    __________

    26-Apr-2015 08:00:00        1     
    30-Apr-2015 11:00:00        1     
    01-May-2015 08:00:00        1     
    05-May-2015 02:00:00        1     
    07-May-2015 07:00:00        1     
    12-May-2015 07:00:00        1     
    12-May-2015 21:00:00        1     
    19-May-2015 16:00:00        1     
    20-May-2015 23:00:00        1     
    22-May-2015 22:00:00        1     
    23-May-2015 00:00:00        1     
    29-May-2015 07:00:00        1     
    01-Jun-2015 06:00:00        1     
    01-Jun-2015 20:00:00        1     
    17-Jun-2015 14:00:00        1     
    10-Jul-2015 04:00:00        1     

dayAvg = groupsummary(data,"timestamp","day")

dayAvg = 44x2 table
    day_timestamp    GroupCount
    _____________    __________

     26-Apr-2015         1     
     30-Apr-2015         1     
     01-May-2015         1     
     05-May-2015         1     
     07-May-2015         1     
     12-May-2015         2     
     19-May-2015         1     
     20-May-2015         1     
     22-May-2015         1     
     23-May-2015         1     
     29-May-2015         1     
     01-Jun-2015         2     
     17-Jun-2015         1     
     10-Jul-2015         1     
     18-Jul-2015         1     
     27-Jul-2015         2     

[aa]

Thank you for help. This works excellent. However, there is somthing more i need calculate. Let me expalin you. For example, my data start from 2016-11-30-03-12-53.11 and ends at 2016-12-25-00-52-22.64 (test_data modified). I requires to calculate the hourly number of events from 2016-12-05-13-35-00 to 2016-12-10-13-35-00. More explicitly, hourly events as

2016-12-05-13-35-00

2016-12-05-14-35-00

2016-12-05-15-35-00

2016-12-05-16-35-00

2016-12-05-17-35-00

and so on. Where there is no event there should be a zero.

Thank you

[Cris LaPierre]

You will have to create your groupbins manually rather than use 'day' and 'hour'. To get a result for each bin, use the 'IncludeEmptyGroups' and 'IncludeMissingGroups' options.

% Create groupbins for hourly and daily
startT = datetime(2016,12,05,13,35,00);
endT = datetime(2016,12,10,13,35,00);
newT_hr = startT:hours(1):endT;
newT_day = startT:caldays(1):endT;

% Count groups
hrAvg = groupsummary(data,"timestamp",newT_hr,"IncludeEmptyGroups",true,"IncludeMissingGroups",true)
dayAvg = groupsummary(data,"timestamp",newT_day,"IncludeEmptyGroups",true,"IncludeMissingGroups",true)