How to fill up missing rows in a matrix based on time column?

Hello guys I have a data matrix like the attached file. This is time series data which shows hourly price of a stock for different dates. The first column shows the dates, the second column shows the hours and the third column shows the prices. My problem is that I need same number of hours for each of the days (in this case 5 hourly data each day) but the data set is missing some hours so I am not getting same number of observations each day. To solve the problem I need to insert the rows that are missing in each days. for example I should have data of hour 4, 5, 6 , 7 and 8 each day.But say that there is no row showing hour 4 in a given date. I need to insert that row. Another day I may not find the row showing hour 5 data, so that is needed to be inserted. I know how to fill up missing data if I have all the required rows but I can not create all the required rows according to the hours or time intervals. Anyone can please help?

Sayeed

Answer 1

[Star Strider]

I hope this is general enough to work will all your data. It works with your example:

load('example_rowfillup.mat')                       % Data were imported and saved as a mat-file
Data = [VarName1  VarName2  VarName3];              % VarName1 = Date, VarName2 = Time, VarName3 = Value
DateTimeVal = [Data(:,1)+Data(:,2)  Data(:,3)];     % Create ‘datenum’ from Date+Time
DateTimev = datevec(DateTimeVal(:,1));              % Create date vector from Date+Time
Days = accumarray(DateTimev(:,3),1);                % Find the numbers of data for each date
DaysInc = find(Days < 5 & Days > 0);                % Indices of entries with missing data
for k1 = 1:length(DaysInc)
    RecInc(:,k1) = find(ismember(DateTimev(:,3), DaysInc(k1)));     % Indices of times with missing data
    FullData(:,k1) = interp1(rem(DateTimeVal(RecInc(:,k1),1),1),  DateTimeVal(RecInc(:,k1),2), (4:8)'/24, 'linear', 'extrap');
    FullDateVal(:,k1,:) = [repmat(fix(DateTimeVal(RecInc(1,k1),1)),5,1)+(4:8)'/24  FullData(:,k1)]; % Create matrix with interpolated data
end
DateTimeIns = reshape(FullDateVal, [], 2);          % Convert FullDateVal to (N x 2) array
DateTimeVal(RecInc(:),:) = [];                      % Delete data for times with missing data from original array
DateTimeVal = sort([DateTimeVal;  DateTimeIns]);    % Insert interpolated data, sort
Out = [datevec(DateTimeVal(:,1))  DateTimeVal(:,2)];    % Full matrix of data with interpolated data for missing times

The only uncommented line interpolates/extrapolates the missing times to fill them in for hours [4 5 6 7 8]. (It was too long to add a comment to.)

I used the ‘Import Data Wizard’ rather than xlsread to import your data, and then saved it to a ‘.mat’ file. I loaded the mat file to this routine to work with it.

[Mohammad Sayeed]

Brother str Strider You have done it nicely and successfully inserted the missing time intervals.Unfortunately the price column is not coming in the corect way. The prics of different hours will remain unchanged and in the inserted rows the price infomaton will not be shown. such as if we insert hour 4 in the second day then the price of that hour will remain blank. Can you please make this modification for me? kind regards

Sayeed

[Star Strider]

I cannot reproduce the behaviour you are seeing. I checked and tested the code before I posted it.

Adding these lines at the end (after the ‘Out = ...’ line and leaving the rest of my code unchanged):

Outh = Out(:,[1:4 7])

fprintf(1,'\nYear  Mo  Dy  Hr  Val\n')
fprintf(1,'%4d  %02d  %02d  %02d  %.1f\n', Outh')

I get:

Year  Mo  Dy  Hr  Val
2013  11  28  04  28.7
2013  11  28  05  29.0
2013  11  28  06  29.0
2013  11  28  07  29.1
2013  11  28  08  29.1
2013  12  01  04  29.2
2013  12  01  05  29.2
2013  12  01  06  29.4
2013  12  01  07  29.4
2013  12  01  08  29.5
2013  12  02  04  29.6
2013  12  02  05  29.6
2013  12  02  06  29.7
2013  12  02  07  29.8
2013  12  02  08  29.8
2013  12  03  04  29.8
2013  12  03  05  29.9
2013  12  03  06  30.0
2013  12  03  07  30.0
2013  12  03  08  30.4

I chose the 'linear' interpolation/extrapolation method because I have no reason to choose the other options. (I’m running MATLAB R2014a. There may be version differences, but I doubt they would be that extreme.)