Creat blank cell, where data are missing and generate related date

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.

Answer 1

[Adam Danz]

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

[BN]

Thank you, it was great...

[BN]

Dear Adam,

When I tried to use this for my real data I got this error, can you help about it?

here is script:

filename = 'Abali.xlsx'
T = readtable(filename);
Sort = sortrows(T, 8);
allDates = (min(Sort.data):max(Sort.data)).';
tempTable = table(allDates(~ismember(allDates,Sort.data)),...
    NaN(sum(~ismember(allDates,Sort.data)),1),'VariableNames',T.Properties.VariableNames); % I got error here
%T2 = outerjoin(T,tempTable,'MergeKeys', 1)

this is an error:

Error using table (line 328)
The VariableNames property must contain one name for each
variable in the table. 

thank you

[Adam Danz]

Look closely at the differences between my demo data and your real data.

In my answer, both the demo data and the tempTable have the same number of columns. That's required. When you merge the tables, there must be data for every column. So your tempTable must have the same number of rows.

I updated my answer so that it's more flexible.

% Old line
tempTable = table(allDates(~ismember(allDates,T.ColumnA)), NaN(sum(~ismember(allDates,T.ColumnA)),1),'VariableNames',T.Properties.VariableNames);

% New Line
tempTable = table(allDates(~ismember(allDates,T.ColumnA)), NaN(sum(~ismember(allDates,T.ColumnA)),size(T,2)-1),'VariableNames',T.Properties.VariableNames);
% Changes >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> here ^ ^ ^ ^ ^ ^ 

[BN]

Dear Adam,

Thank you again, As you said I used this:

filename = 'Abali.xlsx'
T = readtable(filename);
Sort = sortrows(T, 8);
Sort = Sort (:, 8:9); % to have just 2 column like your Demo
allDates = (min(Sort.data):max(Sort.data)).';
tempTable = table(allDates(~ismember(allDates,Sort.data)), NaN(sum(~ismember(allDates,Sort.data)),size(Sort,2)-1),'VariableNames',Sort.Properties.VariableNames);
T2 = outerjoin(Sort,tempTable,'MergeKeys', 1);

and the problem was fixed, thank you. but do you have any idea if I want to generalize this code for:

Sort = Sort(:, 1:12); % instead of just 8 and 9 columns

mean that I want the code to generate NaN cell every where in these 12 columns when there is no value.

I want to ask you if you know please help me in this issue.

Best Regards.

[Adam Danz]

Answered here

https://www.mathworks.com/matlabcentral/answers/497458-how-to-generalize-this-code