How can I add missing hours (rows) in a time series matrix?

An example from an excel file with month-day-hour-solar irradiance is attached as a text file with data for two days. I have to work with many files like this with data for each year and I do not know where the missing values are. What I would like to do is to identify the missing hours and insert the missing rows with the correct month-day-hour, and the indication NaN or something else in the missing data.

Could you please let me know which code I could use to solve this?

Thanks, K.

Answer 1

[Star Strider]

No file attached.

For each file, I would use the beginning and end year-month-day-hour values for each day, converted to date numbers with the datenum function, and create a continuous vector of hours between them.

Then use those vectors with the interp1 function to interpolate the missing data.

[Katerina F]

Thanks. I attached the file now. Could you please give me a bit more detail in the answer as I do not quite understand what to do? E.g. an example of the code? How are the rows generated? Is the datenum function a Matlab funtion or of the toolboxes?

[Evan]

These might help:

• datenum help
• interp1 help

Both are available toolbox free, I believe. There also is the interp function, but that does require a toolbox.

[Star Strider]

Actually, you don’t need datenum with your data format. (It is a built-in core MATLAB function, as is interp1.)

Does this do what you want?

D = dlmread('example time series.txt');             % Read Data
DIdx = find(D(:,2) == 1, 1, 'last');                % Find Where Day#1 Ends
Hq = [0:23]';                                       % Create 24-Hr Interpolation Vector
% Interpolate:
Irad(1:24,:)  = interp1(D(1:DIdx,3), D(1:DIdx,4), Hq, 'linear','extrap');
Irad(25:48,:) = interp1(D(DIdx+1:end,3), D(DIdx+1:end,4), Hq, 'linear','extrap');
% Create Output Matrix (Match Input Format):
TimeSeries = [ones(48,1)  [ones(24,1)*1; ones(24,1)*2]  [Hq; Hq]  Irad];

The TimeSeries matrix matches the input data in case you need all that. If the first column is not always a series of 1, and you need it, we will have to work on a way to reproduce it correctly. If you don’t need it, discard it. The other columns create the day number 1 or 2, the hour vector for each day [0:23], and the interpolated or extrapolated irradiance values.