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

Question

BN 2019-12-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/497318-creat-blank-cell-where-data-are-missing-and-generate-related-date

评论： Adam Danz 2019-12-19

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Adam Danz 2019-12-19

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/497318-creat-blank-cell-where-data-are-missing-and-generate-related-date#answer_407011

编辑：Adam Danz 2019-12-19

在 MATLAB Online 中打开

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

5 个评论
显示 3更早的评论隐藏 3更早的评论

BN 2019-12-19

编辑：BN 2019-12-19

在 MATLAB Online 中打开

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);