unable to iterate serially through files in a for loop

I am running a matlab for loop to iterate through 30 .csv files and 30 .xlsx files. At each iteration of the for loop, i expect matlab to read the days from the .xlsx file, pass it to the .csv file so the same days can be selected and then do the calculation in the code. The code runs well, but the files saved are not showing the correct answers like when the calculations are done individually for each .mat and .xlsx file at a time. Only the anwers in the first saved for loop file is correct, the others are not what i expect. it looks like matlab is skipping the files or mixing them up in the for loop iteration, as only the first iteration is correct. The files are arranged serially in my working folder, hence i expect the for loop to iterate serially. Attached here are some of three of the files. Also attached here is the code. Please the main point of concern are the for loops which iterates through the .xlsx and .csv files. Thank you.

startIndex = 1;
endIndex = 3;
startIndex1 = 1;
endIndex1 = 3;

filelist = dir('C:\Users\shedr\Downloads\tec data\2017\DOBUM2\*.csv');
filelistt = dir('C:\Users\shedr\Downloads\tec data\2017\DOBUM2\*.xlsx'); 
output1 = cell(31,1);
for fileidx = startIndex:endIndex
    % for fileidx = 1:numel(filelist)
    
    
    spectrum = readmatrix(filelist(fileidx).name);
    % y = spectrum(2:end,1:4);
    % ctm = cell2mat(y)
    e = length(spectrum);
    [F] = fillmissing(spectrum, 'linear');
    for fileidx1 = startIndex1:endIndex1
        % for fileidx1 = 1:numel(filelistt)
        if fileidx == fileidx1
            spectrum1 = readmatrix(filelistt(fileidx1).name);
            % % trying to extract the 5 quiet days
            yy1 = spectrum1(:,1);
            % ctm1 = cell2mat(yy1);
            rmn1 = F(:,1) == yy1;
            sbd1 = F(rmn1, :);
            df1 = sbd1(:,4);
            dd = size(df1);
            yy2 = spectrum1(:,2);
            % ctm2 = cell2mat(yy2);
            rmn2 = F(:,1) == yy2;
            sbd2 = F(rmn2, :);
            df2 = sbd2(:,4);
            dd = size(df2);
            yy3 = spectrum1(:,3);
            % ctm3 = cell2mat(yy3);
            rmn3 = F(:,1) == yy3;
            sbd3 = F(rmn3, :);
            df3 = sbd3(:,4);
            dd = size(df3);
            yy4 = spectrum1(:,4);
            % ctm4 = cell2mat(yy4);
            rmn4 = F(:,1) == yy4;
            sbd4 = F(rmn4, :);
            df4 = sbd4(:,4);
            dd = size(df4);
            yy5 = spectrum1(:,5);
            % ctm5 = cell2mat(yy5);
            rmn5 = F(:,1) == yy5;
            sbd5 = F(rmn5, :);
            df5 = sbd5(:,4);
            dd = size(df5);
            % concatenating of the 5 quiet days
            c = [df1,df2,df3,df4,df5]; 
            % mean along horz. line
            cm = mean(c,2);
            % mean for the entire column 
            cmm = mean(cm);
            crp = repmat(cmm,24,1);
            cmm1 = cm-crp;
            cmm2 = cmm1.^2;
            cmm3 = mean(cmm2);
            cmm4 = sqrt(cmm3);   %% this is the standard deviation
            mc = cm';
            ee = e/24;
            con = repmat(mc,1,ee);
            cno = con';
            tyc = F(:,4);
            tycc = tyc-cno
            ty = F(:,1);
            % concatenating the time column with the computed tec values.
            yt = [ty,tycc];
        elseif fileidx <= 10
            save(['C:\Users\shedr\Downloads\tec data\2017\DOBUM2\casc', num2str(fileidx)],'yt')
            % elseif fileidx <= 20
            % save(['C:\Users\shedr\Downloads\tec data\2017\DOBUM2\frns', num2str(fileidx)],'yt')
            % elseif fileidx <= 30
            % save(['C:\Users\shedr\Downloads\tec data\2017\DOBUM2\func', num2str(fileidx)],'yt')
            
            
            % we = size(cno)
        end
    end
    % output1{fileidx} = y;
end

[Stephen23]

"The files are arranged serially in my working folder, hence i expect the for loop to iterate serially"

Nope. Filesystems (see Note0) do not have any (reliable, practical) sense of order. At best perhaps by character code. But in any case, MATLAB simply returns the filenames in the same order that the filesystem returns, whatever that may be (search this forum for detailed descriptions of what that might be). Again: this order has nothing to do with MATLAB.

So if you expect the filenames to be in a particular order, then you will have to sort them yourself (see Note1). My advice is: relying on the order of filenames is fragile. If you expect files to correspond to each other then use a lookup table.

Note0: a tool or application e.g. Windows File Explorer is completely unrelated to the filesystem. And Windows File Explorer does a lot of magical locale-specific undocumented parsing of dates and numeric values and perhaps other things, so what it shows may be completely unrelated to the order that the filesystem.

Note1: if you expect the filenames to be sorted into chronological order of the datestamps in the names then you will need to e.g. parse those datestamps into DATETIME, sort the DATETIME array and return the indices, then use the indices to sort the original filename array. This would be much simpler if the filenames used a better datestamp format e.g. ISO 8601, then you could directly sort the filenames into chronological order: https://en.wikipedia.org/wiki/ISO_8601

[Walter Roberson]

filelist = dir('C:\Users\shedr\Downloads\tec data\2017\DOBUM2\*.csv');
filelistt = dir('C:\Users\shedr\Downloads\tec data\2017\DOBUM2\*.xlsx'); 
%...
spectrum = readmatrix(filelist(fileidx).name);
%...
spectrum1 = readmatrix(filelistt(fileidx1).name);

You fetch filename information from a very specific directory, with the implication that likely that directory is not the current directory.

You then try to read out the found files from the current directory. That is highly likely to fail unless it is certain that the specific directory is the current directory.

You should be using something like

projectdir = 'C:\Users\shedr\Downloads\tec data\2017\DOBUM2';
filelist = dir(fullfile(projectdir, '*.csv'));
filelistt = dir(fullfile(projectdir, '*.xlsx'));
%...
spectrum = readmatrix(fullfile(filelist(fileidx).folder, filelist(fileidx).name));
%...
spectrum1 = readmatrix(fullfile(filelist(fileidx1).folder, filelistt(fileidx1).name));

[shedrach]

I have the script file which contains this matlab code in the same folder(DOBUM2) as the .CSV & .xlsx files. Please, what do you mean that I try fetching the file from a specific directory and try to read in the current directory, with the implication that it is not the current directory?

[Walter Roberson]

With the below changes, you do not depend on the current directory being DOBUM2

projectdir = 'C:\Users\shedr\Downloads\tec data\2017\DOBUM2';

startIndex = 1;
endIndex = 3;
startIndex1 = 1;
endIndex1 = 3;

filelist = dir(fullfile(projectdir, '*.csv'));
filelistt = dir(fullfile(projectdir, '*.xlsx'));
output1 = cell(31,1);
for fileidx = startIndex:endIndex
    % for fileidx = 1:numel(filelist)
    
    
    spectrum = readmatrix(fullfile(filelist(fileidx).folder, filelist(fileidx).name));
    % y = spectrum(2:end,1:4);
    % ctm = cell2mat(y)
    e = length(spectrum);
    [F] = fillmissing(spectrum, 'linear');
    for fileidx1 = startIndex1:endIndex1
        % for fileidx1 = 1:numel(filelistt)
        if fileidx == fileidx1
            spectrum1 = readmatrix(fullfile(filelist(fileidx1).folder, filelistt(fileidx1).name));
            % % trying to extract the 5 quiet days
            yy1 = spectrum1(:,1);
            % ctm1 = cell2mat(yy1);
            rmn1 = F(:,1) == yy1;
            sbd1 = F(rmn1, :);
            df1 = sbd1(:,4);
            dd = size(df1);
            yy2 = spectrum1(:,2);
            % ctm2 = cell2mat(yy2);
            rmn2 = F(:,1) == yy2;
            sbd2 = F(rmn2, :);
            df2 = sbd2(:,4);
            dd = size(df2);
            yy3 = spectrum1(:,3);
            % ctm3 = cell2mat(yy3);
            rmn3 = F(:,1) == yy3;
            sbd3 = F(rmn3, :);
            df3 = sbd3(:,4);
            dd = size(df3);
            yy4 = spectrum1(:,4);
            % ctm4 = cell2mat(yy4);
            rmn4 = F(:,1) == yy4;
            sbd4 = F(rmn4, :);
            df4 = sbd4(:,4);
            dd = size(df4);
            yy5 = spectrum1(:,5);
            % ctm5 = cell2mat(yy5);
            rmn5 = F(:,1) == yy5;
            sbd5 = F(rmn5, :);
            df5 = sbd5(:,4);
            dd = size(df5);
            % concatenating of the 5 quiet days
            c = [df1,df2,df3,df4,df5]; 
            % mean along horz. line
            cm = mean(c,2);
            % mean for the entire column 
            cmm = mean(cm);
            crp = repmat(cmm,24,1);
            cmm1 = cm-crp;
            cmm2 = cmm1.^2;
            cmm3 = mean(cmm2);
            cmm4 = sqrt(cmm3);   %% this is the standard deviation
            mc = cm';
            ee = e/24;
            con = repmat(mc,1,ee);
            cno = con';
            tyc = F(:,4);
            tycc = tyc-cno
            ty = F(:,1);
            % concatenating the time column with the computed tec values.
            yt = [ty,tycc];
        elseif fileidx <= 10
            save(fullfile(projectdir, ['casc', num2str(fileidx)]),'yt')
            % elseif fileidx <= 20
            % save(['C:\Users\shedr\Downloads\tec data\2017\DOBUM2\frns', num2str(fileidx)],'yt')
            % elseif fileidx <= 30
            % save(['C:\Users\shedr\Downloads\tec data\2017\DOBUM2\func', num2str(fileidx)],'yt')
            
            
            % we = size(cno)
        end
    end
    % output1{fileidx} = y;
end

[Stephen23]

"Please, what do you mean that I try fetching the file from a specific directory and try to read in the current directory, with the implication that it is not the current directory?"

Explanation:

• your DIR calls look for files in this location: C:\Users\shedr\Downloads\tec data\2017\DOBUM2
• your READMATRIX calls try to open files in the current directory.

The code is fragile/buggy, because in general those are not the same location. The approach of storing the script in the same location as the data files just clutters up the data folders and can slow down MATLAB: it is usually much better to keep data files and code files separate.

Solution:

Supply the same path when you call READMATRIX, e.g. using FULLFILE.

Answer 1

[Mathieu NOE]

hello @shedrach

try this

as simple approach where I first look at the xls files and create a 2D array that contains month and year of those files

then as you loop over the csv files I simply pick the right (matching ) xls file based on an index I calculated to have same month and year.

therefore the code should be robust to the fact that dir command does not sort the files in a predictable way.

with the posted files you have this result displayed in the command window (just to test the code) :

------------------------------

CSVfilename = 'casc_2017_m02_1h.csv'

XLSfilename_selected = '1 FEB 2017 Q.xlsx'

------------------------------

CSVfilename = 'casc_2017_m03_1h.csv'

XLSfilename_selected = '2 MARCH 2017 Q.xlsx'

------------------------------

CSVfilename = 'casc_2017_m04_1h.csv'

XLSfilename_selected = '3 APRIL 2017 Q.xlsx'

code :

filelist = dir('C:\Users\shedr\Downloads\tec data\2017\DOBUM2\*.csv');
filelistt = dir('C:\Users\shedr\Downloads\tec data\2017\DOBUM2\*.xlsx'); 


nFilesCSV = numel(filelistCSV);
nFilesXLS = numel(filelistXLS);

%% loop the XLSX files to store month & year info's in XLS_table
for fileidx = 1:nFilesXLS
   
    XLSfilename = filelistXLS(fileidx).name;
    % extract date data
    A1 = split(XLSfilename,' ');
    XLSfilename_month = A1{2};
    XLSfilename_monthnum = monthNameToNum(XLSfilename_month);  % This will return the month converted in numeric value (1 to 12)
    XLSfilename_year = str2double(A1{3});
    XLS_table(fileidx,:) = [ XLSfilename_monthnum XLSfilename_year]; % store in numeric format the month and year of the file 
   
end

%% now : loop the CSV files and find in  XLS_table the matching XLS file
startIndex = 1;
endIndex = nFilesCSV;

for fileidx = startIndex:endIndex
   
        disp('------------------------------');
    
    % CSV filelist name extraction 
    CSVfilename = filelistCSV(fileidx).name
    % extract date infos
    A1 = regexp(char(CSVfilename),'[-+]?([0-9]*[.])?[0-9]+([eE][-+]?\d+)?','match'); % extract numerical content of string
    out = str2double(A1); % year / month / day (or hour) of CSV file - anyway we just use year and month data
    CSVfilename_month = str2double(A1{2});
    CSVfilename_year = str2double(A1{1});
    CSV_table = [CSVfilename_month CSVfilename_year];
    % find matching XLS file (row index of XLS_table)
    diff = sum(abs(XLS_table - CSV_table),2);
    indXLS = find(diff<eps);
    XLSfilename_selected = filelistXLS(indXLS).name
    
    % INSERT YOUR CODE HERE....
    
    

end

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function monthNum = monthNameToNum(monthName)
    % If you want to convert month names to their corresponding numerical values in MATLAB, 
    % you can use a simple approach with a mapping of month names to numbers. 
    
    % Define a cell array of month names
    months = {'January', 'February', 'March', 'April', 'May', 'June', ...
              'July', 'August', 'September', 'October', 'November', 'December'};
    
    % Find the index of the month name in the cell array
    monthNum = find(contains(lower(months),lower(monthName))); % NB : not case sensitive
    
    % Check if the month name is valid
    if isempty(monthNum)
        error('Invalid month name.');
    end

end


%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% not used here , but may be worth having it in your toolbox
%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function name = monthName(num)
    name = month(datetime(1,num,1), 'name');
end

[Mathieu NOE]

hello @shedrach

problem solved ?