Creat blank cell, where data are missing and generate related date
9 views (last 30 days)
Show older comments
Hi
I have a table where the first column of the date and the second column are the values corresponding to that date.
This table sometimes contains missing data. Where there is a missing number, there is no date in front of it. So there is no space left in the second column, although data may be missing. I want to know if MATLAB can able to add the date where there was no date and no value. furthermore, I put an empty cell in front of it so I could figure out where the missing data was and then fill in the available methods.
for example:
column A: column B:
2015 0.5
2016 0.75
2017 1.25
2019 0.5
(you can see I have missing value in 2018 but there is no empty cell. I want to do this:
column A: column B:
2015 0.5
2016 0.75
2017 1.25
2018 (empty cell)
2019 0.5
This is just an example. my real data are in monthly bases and are very bigger than that (1982-2015 monthly)
do you know how to do this? please help me, thanks.
0 Comments
Accepted Answer
Adam Danz
on 19 Dec 2019
Edited: Adam Danz
on 19 Dec 2019
Create a temporary table that contains the missing dates in column A and NaN values in Column B. Then merge the temporary table with your primary table using outerjoin().
Note that this will not insert an empty cell. Instead, it will use NaN as a placeholder. This is because the values in Column B are numeric and you cannot mix data types within a column.
% Create a demo table that is missing row for 2018
T = table((2015:2019)', rand(5,1),'VariableNames',{'ColumnA','ColumnB'});
T(4,:) = [];
% Create temporary table containing missing years
allDates = (min(T.ColumnA):max(T.ColumnA)).'; % column vector of all dates
tempTable = table(allDates(~ismember(allDates,T.ColumnA)), NaN(sum(~ismember(allDates,T.ColumnA)),size(T,2)-1),'VariableNames',T.Properties.VariableNames);
% Merge tables
T2 = outerjoin(T,tempTable,'MergeKeys', 1)
Result
T2 =
5×2 table
ColumnA ColumnB
_______ _______
2015 0.99679
2016 0.77368
2017 0.74885
2018 NaN
2019 0.70716
More Answers (0)
See Also
Categories
Find more on Shifting and Sorting Matrices 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!