How to generalize this code?

Hello everyone,

Thanks to Adam Danz I have this code below in order to create a NaN cell, where data are missing and generate related dates.

https://www.mathworks.com/matlabcentral/answers/497318-creat-blank-cell-where-data-are-missing-and-generate-related-date?s_tid=answers_rc1-1_p1_MLT

here is the code:

clear all
clc
filename='Qaen.xlsx'
T = readtable(filename);
Sort = sortrows(T, 8);
Sort = Sort (:, 8:9);

dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';

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

this code was work perfect when I have 2 columns (date and value), but in my real data set, I have some other columns. in fact, the date column was my 8 column and 9, 10, 11, and 12 are my separate values (max_temp, min_temp, rainfall, average_temp).

I want to generalize the code in order to do that and conduct my research.

in fact, I want to have 1:7 column stationary and to what this code doing for 9, 10, 11, and 12 columns.

please help me. I attached my .xlsx file many thanks

Answer 1

[Adam Danz]

Instead of removing columns from your table, keep the table all together (unless you have a really good reason not to).

Check out each line of code, what it does, etc and see in-line comments for details on what I changed / added.

Let me know if you have any questions.

filename='Qaen.xlsx'
T = readtable(filename);
[~, sortIdx] = sort(T.data); % Avoid using column numbers when indexing a table; use var-names.
Sort = T(sortIdx,:);   %consider re-naming "Sort" (too close to sort() function).  

dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';

% list all missing dates
% Must have same variable name as the date column in Sort. 
tempTable = table(allDates(~ismember(allDates,Sort.data)),'VariableNames',{'data'});

% merge rows
T2 = outerjoin(Sort,tempTable,'MergeKeys', 1);

[BN]

Thank you. it's awesome. really appreciate it.

[Adam Danz]

happy to learn along with ya!