Missing dates in a dataset and filling in for them.
7 views (last 30 days)
Show older comments
Hello. I have a 2916x14 matrix of data that I have attached. Column 6 gives the date but my problem is that some random days are missing. I am looking to add in the missing dates and for columns 1-5, to use what was in the previous row (they are the same throughout the data) and to fill in 'NA' for columns 7-14 for all of the missing dates. I have tried to play around with retime and datetime but I am not having much luck and to be honest I don't know where to go from here. Any help would be greatly appreciated, thank you!m
2 Comments
Adam Danz
on 29 May 2020
Could you share what you tried with retime? That should solve most of the items on the list.
Answers (1)
Adam Danz
on 29 May 2020
Convert your table to a timetable.
Then use retime() to fill in missing dates on a daily basis. The columns will be filled with values from the previous row. See retime() for details.
Since the data in columns 7:end are all "NA", the NA values will be filled according to your description.
However, the last two lines show how to fill the new rows with NA values even though they are already NA.
TT = table2timetable(eustis48to55,'RowTimes','DATE');
TTr = retime(TT,'daily','previous');
% Find which rwos of TTr have been added.
missingIdx = ~ismember(TTr.DATE,TT.DATE);
TTr(missingIdx,7:end) = {categorical({'NA'})};
1 Comment
Adam Danz
on 29 May 2020
If you want to convert back to a table, use timetable2table() but I suggest you keep the timetable since they offer advantages when working with timeseries data.
See Also
Categories
Find more on Timetables 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!