How to write a Loop to run a specific code to every table in a cell

I have many excel files (540 files) which I used this code to import them to Matlab:

D = 'C:\Users\Behzad\Desktop\New folder (2)';
filePattern = fullfile(D, '*.xlsx');
file = dir(filePattern);
x={};
for k = 1 : numel(file) 
      baseFileName = file(k).name;
      fullFileName = fullfile(D, baseFileName)
      x{k} = readtable(fullFileName);
      fprintf('read file %s\n', fullFileName);
end

After doing that x is a 1*540 cell which contains 540 tables. I want to apply this code below for all these tables:

T = readtable(filename);
sort = sortrows(T, 8);
selected_table = 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,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
T2 = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'})

I don't know how to do that. I guess I should use the loop again but haven't any idea how to do it.

Best Regards

[Adam Danz]

The for-loop wouldn't be too much different from your first for-loop. What part are you stuck on?

https://www.mathworks.com/help/matlab/ref/for.html

[BN]

it's hard for me I don't know how to write the code. I mean I can do this on a paper but as I'm new to Matlab I don't know this language well. the problem is I don't know how to define a new loop when x{k} is generated from the previous loop. I searched a lot and write this code below:

% allDates should be out of the loop because it's not necessary to be in the loop
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
% creating loop
for idx = 1:numel(x) 
T = readtable(idx);
sort = sortrows(T, 8);
selected_table = sort (:, 8:9); 
tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
T2 = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'})
end

if you can help with this issue too, I would be grateful.

Answer 1

[Adam Danz]

I can't run your code but here are some feedback at first glance.

First, always use correct indentation so that it's much easier to read the code. "Smart indentation" can be done by selecting all of the code and pressing ctrl+i.

See my 3 notes within your code.

% allDates should be out of the loop because it's not necessary to be in the loop
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';

% 1) pre-allocate a cell array that will store 
%   your tables (see note #3)
T2 = cell(size(x);  % this should work, I don't know what x is

% creating loop
for idx = 1:numel(x)
    T = readtable(idx);
    % 2) This line should probably be T = readtable(x(idx));
    
        
    sort = sortrows(T, 8);
    selected_table = sort (:, 8:9);
    tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
    T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
    T2 = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'})
    % 3) You're overwriting the variabe T2 on each iteration of the i-loop. 
    % to save each table, do this
    % T2{idx} = fillissing(.....)

end

Now, to access table #n, T2{n}

As I said, I can't run your code so if you come across problems, show me the updated code and describe what went wrong.

Also, it would be a very wise investment of your time to spend 1/2 an hour teaching yourself how to debug code using debug mode.

[BN]

Dear Adam Danz, really thank you for your kind advice.

here is the complete code:

clear
close all
clc
D = 'C:\Users\Behzad\Desktop\New folder (2)';
filePattern = fullfile(D, '*.xlsx');
file = dir(filePattern);
x={};
for k = 1 : numel(file)
    baseFileName = file(k).name;
    fullFileName = fullfile(D, baseFileName);
    x{k} = readtable(fullFileName);
    fprintf('read file %s\n', fullFileName);
end
% allDates should be out of the loop because it's not necessary to be in the loop
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
% 1) pre-allocate a cell array that will store
%   your tables (see note #3)
T2 = cell(size(x)); % this should work, I don't know what x is
% the x is xlsx files and have different sizes, so I think it should be in
% a loop?
% creating loop
for idx = 1:numel(x)
    T = readtable(x(idx));
    % 2) This line should probably be T = readtable(x(idx));
    sort = sortrows(T, 8);
    selected_table = sort (:, 8:9);
    tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
    T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
    % 3) You're overwriting the variabe T2 on each iteration of the i-loop.
    % to save each table, do this
    T2{1i} = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});
    
end % It gives me the error here

this code gives an error in the second loop :

Error using readtable (line 216)
Input must be a row vector of characters or string scalar.

in the code x is .xlsx file, since each file has vary size I think it should be placed in a loop?

[Adam Danz]

Two changes

1) try T = readtable(x{idx}); with curly brackets

2)In the last line of the for-loop, I had a typo in my answer (since corrected) it should be

T2{idx} = fillmissing(. . .

[BN]

Thanks. I tired this but the error still exist !

the updated code is:

clear
close all
clc
D = 'C:\Users\Behzad\Desktop\New folder (2)';
filePattern = fullfile(D, '*.xlsx');
file = dir(filePattern);
x={};
for k = 1 : numel(file)
    baseFileName = file(k).name;
    fullFileName = fullfile(D, baseFileName);
    x{k} = readtable(fullFileName);
    fprintf('read file %s\n', fullFileName);
end
% allDates should be out of the loop because it's not necessary to be in the loop
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
% 1) pre-allocate a cell array that will store
%   your tables (see note #3)
T2 = cell(size(x)); % this should work, I don't know what x is
% the x is xlsx files and have different sizes, so I think it should be in
% a loop?
% creating loop
for idx = 1:numel(x)
    T = readtable(x{idx});
    % 2) This line should probably be T = readtable(x(idx));
    sort = sortrows(T, 8);
    selected_table = sort (:, 8:9);
    tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
    T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
    % 3) You're overwriting the variabe T2 on each iteration of the i-loop.
    % to save each table, do this
    T2{idx} = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});
    
end

[Adam Danz]

There are 2 lines in your code that use readtable() and I don't know which one is causing the error. I assum it's the second one. If i'm correct, what is the value of x at the iteration causing the error? x(idx) or x{idx}

[BN]

Oh yes. I changed code and remove one of them:

clear
close all
clc
D = 'C:\Users\Behzad\Desktop\New folder (2)';
filePattern = fullfile(D, '*.xlsx');
file = dir(filePattern);
x = {};
for k = 1 : numel(file)
    baseFileName = file(k).name;
    fullFileName = fullfile(D, baseFileName);
    x{k} = readtable(fullFileName);
    fprintf('read file %s\n', fullFileName);
end
% allDates should be out of the loop because it's not necessary to be in the loop
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
% 1) pre-allocate a cell array that will store
%   your tables (see note #3)
T2 = cell(size(x)); % this should work, I don't know what x is
% the x is xlsx files and have different sizes, so I think it should be in
% a loop?
% creating loop
for idx = 1:numel(x)
    T = x{idx};
    % 2) This line should probably be T = readtable(x(idx));
    sort = sortrows(T, 8);
    selected_table = sort (:, 8:9);
    tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
    T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
    % 3) You're overwriting the variabe T2 on each iteration of the i-loop.
    % to save each table, do this
    T2(idx) = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});
    
end

but this error appeared:

Subscripting a table using linear indexing (one subscript) or
multidimensional indexing (three or more subscripts) is not
supported. Use a row subscript and a variable subscript.

Also I changed x{idx} to x(idx) but in this case this new error appeared:

Error using
matlab.internal.math.sortrowsParseInputs>legacyParseCOL (line
106)
Column sorting vector must contain integers with absolute value
between 1 and the number of columns in the first argument.
Error in matlab.internal.math.sortrowsParseInputs (line 29)
    [col,colProvided] = legacyParseCOL(col,n,in2);
Error in sortrows (line 60)
    [col, nanflag, compareflag] =
    matlab.internal.math.sortrowsParseInputs(A,varargin{:}); 

[dpb]

    T2(idx) = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});

as the error message says, has only one subscript for the 2D table variable address. You need both a row and column address.

But fillmissing doesn't need (or want) a loop over the rows of the table; it operates (as do most of the Matlab functions; that's why it's called MATRIX LAB) over the selected variables of the table as vectors.

See the examples using tables at doc fillmissing to see application.

[BN]

Even when I turn this block to comment

% T2(idx) = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});

Matlab gives me this error:

Error using
matlab.internal.math.sortrowsParseInputs>legacyParseCOL (line
106)
Column sorting vector must contain integers with absolute value
between 1 and the number of columns in the first argument.
Error in matlab.internal.math.sortrowsParseInputs (line 29)
    [col,colProvided] = legacyParseCOL(col,n,in2);
Error in sortrows (line 60)
    [col, nanflag, compareflag] =
    matlab.internal.math.sortrowsParseInputs(A,varargin{:});

[dpb]

Take the FILLMISSING line out of the loop. When you've done that there should be no addressing expression.

T2=fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});

I've not read the code in detail but I'm guessing you don't need the loop at all.

Explain what you're trying to do overall big picture.

[BN]

Thank you. Overall, I have many excel (.xlsx) files that I want to work on them. Although the number of columns in all of these is the same, rows number is different in each file.

the first point is in my data (.xlsx files) some dates do not exist, for example: (MM:DD/YYYY)

1/1/2010

2/1/2010

3/1/2010

5/1/2010 (you can see 4/1/2010 not exist)

I want to create a date rows for them and set NaN values for corresponding variables on these dates.

Second I want to sort every .xlmx file by date (ascending).

Third I want to fill the blank cell in the columns of lat, lon, and station_elevation according to the next good value in each column.

Finally, I want to fill NaN values in the tm_m column by averaging column tmax_m and tmin_m.

Here is the code so far:

clear
close all
clc
D = 'C:\Users\Behzad\Desktop\New folder (2)';
filePattern = fullfile(D, '*.xlsx');
file = dir(filePattern);
x = {};
for k = 1 : numel(file)
    baseFileName = file(k).name;
    fullFileName = fullfile(D, baseFileName);
    x{k} = readtable(fullFileName);
    fprintf('read file %s\n', fullFileName);
end
% allDates should be out of the loop because it's not necessary to be in the loop
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
% 1) pre-allocate a cell array that will store
%   your tables (see note #3)
T2 = cell(size(x)); % this should work, I don't know what x is
% the x is xlsx files and have different sizes, so I think it should be in
% a loop?
% creating loop
for idx = 1:numel(x)
    T = x(idx);
    % 2) This line should probably be T = readtable(x(idx));
    sort = sortrows(T, 8);
    selected_table = sort (:, 8:9);
    tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
    T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
    % 3) You're overwriting the variabe T2 on each iteration of the i-loop.
    % to save each table, do this
    T2(idx) = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});
    T2.tm_m(isnan(T2.tm_m)) = mean([T2.tmax_m(isnan(T2.tm_m)), T2.tmin_m(isnan(T2.tm_m))],2);
    
end

and I attached T2.mat and 2 sample of my xlsx files. Thank you again for your help.

[dpb]

Look at the datetime object. retime does the first problem; fillmissing will do the next. The infill of missing (nan) values by row is done by logical addressing

 ix=isnan(tm_m);
 tm_m(ix)=mean([tmax_m(ix) tmin_m(ix)],2);

No loop other than over the files is necessary.

[BN]

Dear dpb,

I don't know how to calculate the code without the second loop. If you can help with it, I would be appreciated. I was thinking about this code all day long and I thought this update below could work. but it gives me an error.

the updated code is:

clear
close all
clc
D = 'C:\Users\Behzad\Desktop\New folder (2)';
filePattern = fullfile(D, '*.xlsx');
file = dir(filePattern);
x = {};
for k = 1 : numel(file)
    baseFileName = file(k).name;
    fullFileName = fullfile(D, baseFileName);
    x{k} = readtable(fullFileName);
    fprintf('read file %s\n', fullFileName);
end
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
T2 = cell(size(x)); %create T2
for idx = 1:numel(x)
    T = x{idx};
    [~, sortIdx] = sort(T.data); %sorting data based on dates
    Sort = T(sortIdx,:);
    tempTable = table(allDates(~ismember(allDates,Sort.data)),'VariableNames',{'data'});
    T2 = outerjoin(Sort,tempTable,'MergeKeys', 1);
    T2(idx) = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});
    T2.tm_m(isnan(T2.tm_m)) = mean([T2.tmax_m(isnan(T2.tm_m)), T2.tmin_m(isnan(T2.tm_m))],2);
end

and the error is:

Subscripting a table using linear indexing (one subscript) or
multidimensional indexing (three or more subscripts) is not
supported. Use a row subscript and a variable subscript.

Thank you.

[Adam Danz]

I've been away for ~2 days and am now catching up. I got your section of code working after making 4 changes listed in the comments below.

Now it's up to you to make sure the rest of your checklist gets accomplished.

D = 'C:\Users\Behzad\Desktop\New folder (2)'; 
filePattern = fullfile(D, '*.xlsx');
file = dir(filePattern);
x = {};
for k = 1 : numel(file)
    baseFileName = file(k).name;
    fullFileName = fullfile(D, baseFileName);
    x{k} = readtable(fullFileName);
    fprintf('read file %s\n', fullFileName);
end
% allDates should be out of the loop because it's not necessary to be in the loop
dt1 = datetime([1982 01 01]);
dt2 = datetime([2018 12 31]);
allDates = (dt1 : calmonths(1) : dt2).';
allDates.Format = 'MM/dd/yyyy';
% 1) pre-allocate a cell array that will store
%   your tables (see note #3)


% CHANGE 1: STORE THE TABLES IN CELL ARRAY C
C = cell(size(x)); % this should work, I don't know what x 


% the x is xlsx files and have different sizes, so I think it should be in
% a loop?
% creating loop
for idx = 1:numel(x)
    
    % CHANGE 2: USE CURLY BRACKETS 
    %     T = x(idx);
    T = x{idx};
    
    
    % 2) This line should probably be T = readtable(x(idx));
    sort = sortrows(T, 8);
    selected_table = sort (:, 8:9);
    tempTable = table(allDates(~ismember(allDates,selected_table.data)), NaN(sum(~ismember(allDates,selected_table.data)),size(selected_table,2)-1),'VariableNames',selected_table.Properties.VariableNames);
    T2 = outerjoin(sort,tempTable,'MergeKeys', 1);
    % 3) You're overwriting the variabe T2 on each iteration of the i-loop.
    % to save each table, do 

    %CHANGE 3: REPLACE THIS LINE
    % T2(idx) = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});
    T2 = fillmissing(T2, 'next', 'DataVariables', {'lat', 'lon', 'station_elevation'});
    T2.tm_m(isnan(T2.tm_m)) = mean([T2.tmax_m(isnan(T2.tm_m)), T2.tmin_m(isnan(T2.tm_m))],2);
   
    % CHANGE 4: TABLE IN CELL ARRAY
    C{idx} = T2; 
end

[BN]

Thanks all of you. I learned so many things in this question from you and now my problem solved. Thanks for your time and kindness.

[Adam Danz]

Glad I could help. dpb's timetable/retime approach is a nice alternative to this approach that you may want to keep in mind.

[dpb]

dpb would strongly recommend same, in fact! :)

It makes use of the methods TMW has built into the product for the express purpose; is clear and concise and solves the problem at hand (once BN fixes whatever typo there is in his copy anyway).

Answer 2

[dpb]

D='C:\Users\Behzad\Desktop\New folder (2)';
d=dir(fullfile(D, '*.xlsx'));
for i=1:numel(d)
  tt=readtable(fullfile(d(i).folder,d(i).name));        % read file as table
  tt=table2timetable(tt,'RowTimes',tt.data);            % convert to timetable ('data' is typo for 'date'???)
  tt.data=[];                                           % get rid of the now unneeded date column
  [~,isrt]=sort(tt.Time);                               % sorted row index with which to...
  tt=tt(isrt,:);                                        % sort by increasing time
  tt.region_id=categorical(tt.region_id);               % I'd do this with all these type variables, just example how
  tt=retime(tt,'monthly');                              % put on monthly basis, create any missing
  tt=fillmissing(tt,'next','datavariables',{'lat','lon','station_elevation'});  % fill in missing 
  isn=isnan(tt.tm_m);                                   % find missing mean temperatures
  tt.tm_m(isn)=mean([tt.tmax_m(isn) tt.tmin_m(isn)],2); % fill in with average min/max (presumes they're not nan, too)
end

Above does all you asked for each file; do what is needed like writetable or save or whatever at the end of the loop before going on and overwriting the previous.

No second loop required for any operations; read the Matlab examples carefully and see how vectorized functions work...it's the key to effective use of Matlab.

[BN]

Dear dpb,

First of all, I want to thank you for your kindly answer. Unfortunately after run this code I get following error:

Unable to perform assignment because the left and right sides
have a different number of elements.

[dpb]

Have to see the code exactly as you ran it and the specific error in context...it ran on sample file here.

If the optional second argument to the mean() function were missing that would explain the error, though; it would average the columns to two values instead of the rows to the number of missing elements.

NB: Your fillmissing will NOT fill in anything for the temperatures for the missing dates so there will be NaN average temperatures at those locations. That may well be desired; just noting.

Answer 3

[Image Analyst]

I'd build your second snippet of code into a function and then call that inside the loop of your first snippet.