How to identify the hourly/daily missing data points?

Hi everyone,

My dataset consists of long time series (about 20 years, excel sheet) with years, month, day, hour, mint, second. Each row represent one event. Some time there is no occurance on an hour or a day or even a month. Manually, its very hard to check with hour has no event or which day has no event.

May someone suggest me how can i handle this problem.

output shuld be like in 7 column year, month, day, hour mint,. second, events

data=load('data.txt');
year=data(:,1);
month=data(:,2);
day=data(:,3);
hour=data(:,4);
minute=data(:,5);
second=data(:,6);
observation = datenum(year,month,day,hour,minute,second);

Answer 1

[Chunru]

% Assume you have the observation as a datenum vector
% Here we generate an example datenum vector
observation = datenum(2021, 1, 1, [0:.5:3 5:1.2:8 8.1:0.1:9]', 0, 0);  % YMDHMS
observation = sort(observation);            % sort it if necessary
idx = find(diff(observation) > 1/24)        % difference is greater than 1h

idx = 3×1

7 8 9

datestr(observation(idx)) 

ans = 3×20 char array
    '01-Jan-2021 03:00:00'
    '01-Jan-2021 05:00:00'
    '01-Jan-2021 06:12:00'

datestr(observation(idx+1))

ans = 3×20 char array
    '01-Jan-2021 05:00:00'
    '01-Jan-2021 06:12:00'
    '01-Jan-2021 07:24:00'

[Andi]

Thank you for help. However, this script only compute the hourly/daily event for the datenum vector provided. Sometimes the data is missing for few hours/days i.e. there is no entry for datenum vector. I need to add missing hours/days as well with 0 observation.

[Chunru]

So you change the following to what you want

idx = find(diff(observation) > 1/24

1/24 is one hour or 1/24 day. You change that to any other value.

[Andi]

How about if there is no observation within 24 hours. I think script just miss that date but i need that date with 0 observation. Thank you!

[Chunru]

idx = find(diff(observation) > 1)     % gaps more than 1 day

[Chunru]

find the missing day: convert the datenum to integer day by using floor. Then if difference between 2 adjacent integer day is greater than 1, there is data missing for that day.

idx = find(diff(floor(observation)) > 1)