Treat and handle missing hourly data (with daily profile), that might have large gaps

I want to treat huge missy temperature data with many missing values (presented as 999.9).

If there is few missing data within the day, I would take average from data before and after. But if I have large missing clusters (almost full-day missing, or up to 100 values in a row), I would take average of 1PM temperature from yesterday and 1PM temperature from tomorrow to get 1PM value for today, and same goes for all hours.

Note: I don't wish to change valid assigned tempratures linked to hours (like what interp1 would do with values order).

What can I use to handle these data?

08/09/2016 	4:00:00	 26
08/09/2016 	5:00:00	 26
08/09/2016 	6:00:00	 25
08/09/2016 	6:00:00	 999.9
08/09/2016 	7:00:00	 24
08/09/2016 	8:00:00	 25
08/09/2016 	9:00:00	 24
08/09/2016 	9:00:00	 999.9
08/09/2016 	10:00:00 23

[Anwaar Alghamdi]

@Stephen23

Could you please take a look.

Also, if there is duplicate in hours how can I make sure that for each hour, only the resonable maximum value is presented. like below:

08/09/2016 	6:00:00	 23
08/09/2016 	6:00:00	 23
08/09/2016 	6:10:00	 24
08/09/2016 	6:30:00	 999.9
08/09/2016 	6:47:00	 25

I need the output to be:

08/09/2016 	6:00:00	 25

[Jiri Hajek]

Hi, it looks like you need to do at least a two-pass cleanup of the data. 1) Identify small missing clusters that you want to treat using linear interpolation and clean up these, 2) Identify large missing clusters and use whatever method you prefer. But IMHO such a cleanup of measured data may damage the credibility of your subsequent analysis and as such is not a good practice...

[Anwaar Alghamdi]

Hi Jiri

Do you have an idea on how to identify small missing clusters?

Do you have a recommendation on how to handle large missing clusters?

[Anwaar Alghamdi]

@Jiri Hajek

Also, if I do linear interpolation, the non-999 values will be missed up (at least their order). I don't want to touch the temperatures assigned for each hour. Only estimate the 999 values.

[Jiri Hajek]

As for the cluster identification, I can give you some hints - will put them below into an answer. As for the handling of large missing clusters, I would leave themo out, i.e. constrain the scope.

Answer 1

[Jiri Hajek]

To identify the clusters of outliers, one may use logical indexing and the time vector. This is just a skeletal draft of the algorithm, but you can get the idea.

timeColumn  % your datatime values
temperatureColumnRaw % your original temperatures

outlierPoints = temperatureColumnRaw > 900;
outlierTimes = timeColumn(outlierPoints);

timeDifsOfOutliers = diff(outlierTimes);
clusterStartsLogical = [1; timeDifsOfOutliers > mode(diff(timeColumn))];
clusterStartTimes = outlierTimes(clusterStartsLogical);

nClusters = length(clusterStart); 
if nClusters > 1
    clusterStartIndices = find(clusterStartsLogical);
    clusterEndPoints = [clusterStartIndices(2:end)-1;length(outlierTimes)];
    clusterEndTimes = outlierTimes(clusterEndPoints);
end

clusterDurations = clusterEndTimes-clusterStartTimes;
shortClusterIndices = clusterDurations > hours(3);   % you define, what is a short cluster