How to fill up missing rows in a matrix based on time column?
1 view (last 30 days)
Show older comments
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
0 Comments
Answers (1)
Star Strider
on 20 May 2014
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.
2 Comments
Star Strider
on 20 May 2014
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.)
See Also
Categories
Find more on Price and Analyze Financial Instruments in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!