How to append cell arrays to from a unique array and then fill one of its column out with data from excel sheets?

Hello all,

Using the code below, I am going to load a cell array called "stations_CT" that includes information about all weather stations existing in Connecticut ( ID, start-date and end-date of capturing data, elevation, latitude, and longitude). Then, I am going to fill the 8th column of this cell array using the excel file ("wb_CT.xlsx") including average tempreture data for each weather station( each sheet in "wb_CT.xlsx" corresponds to daily tempreture for each weather station ID existing in the "stations_CT").

I have the same cell arrays like "station_CT"(e.g. "stations_DE", "stations_VT" and...) and the same excel files like "wb_CT.xlsx"(e.g. "wb_DE.xlsx", "wb_VT.xlsx and ...). I need to append all the stations to from a unique cell and then fill out the 8th column of this cell with the corresponding excel files. In other words, I need to create a for loop on my code.

Can anyone help me with this regard?

clear
close all
clc
load("stations_CT.mat")
FName ='wb_CT.xlsx';  
S_names = sheetnames(FName);
for ii=1:numel(S_names)
    D{ii}=readtable(FName, 'Sheet', S_names(ii));   % Data read as a table array
    stations1{ii,8} = D{ii};                        % Inserted in column 8 of the existing variable: stations11   
end 

Answer 1

[Voss]

Obviously if you just wrapped that code in a loop, your variable stations1 would be overwritten on each iteration of that loop, when the next mat file is loaded. It's not clear what the end goal is for all those different stations1 variables. The code below saves them back into a mat file with a different name than the one they came from. You can modify it if you want to do something else.

The tricky part to me is not making a loop; it's making sure you load the xlsx file that corresponds to a given mat file. The following is one way to do it.

% get information about mat files in the current directory:
% (change this to get information about your mat files, wherever they are)
mat_info = dir('*.mat');

% pull the state abbreviations from the mat file names, assuming the ones
% you want are all of the form "stations_XXXX.mat", where XXXX is a
% state or other jurisdiction abbreviation of any length. this grabs the 
% part of the file name after the last underscore before the period and 
% stores it in the variable "states":
states = regexp({mat_info.name},'^stations_([^_]+).mat$','tokens','once');

% remove from states and mat_info any entries whose state is empty (file
% name didn't conform to the "stations_XXXX.mat" pattern):
idx = cellfun(@isempty,states);
states(idx) = [];
mat_info(idx) = [];

% store the state abbreviations (as chars, not cells) with the rest of 
% the mat file information:
states = [states{:}];
[mat_info.state] = states{:};

% perform the same steps for the xlsx files. this time the pattern is
% "wb_XXXX.xlsx":
xlsx_info = dir('*.xlsx');
states = regexp({xlsx_info.name},'^wb_([^_]+).xlsx$','tokens','once');
idx = cellfun(@isempty,states);
states(idx) = [];
xlsx_info(idx) = [];
states = [states{:}];
[xlsx_info.state] = states{:};

% the two sets of files may not represent the same set of states, so only
% use the states they have in common:
[states,idx_mat,idx_xlsx] = intersect({mat_info.state},{xlsx_info.state});

% construct full-path file names for those common states, in the same
% order:
mat_files = fullfile({mat_info(idx_mat).folder},{mat_info(idx_mat).name});
xlsx_files = fullfile({xlsx_info(idx_xlsx).folder},{xlsx_info(idx_xlsx).name});

% now loop over the states, load each mat file, put the contents of the
% corresponding xlsx file in the stations1 variable, and save the mat file
% with a new name (e.g., stations_VT_modified.mat, in this case)
for jj = 1:numel(states)
    S = load(mat_files{jj});
    FName = xlsx_files{jj};
    S_names = sheetnames(FName);
    for ii = 1:numel(S_names)
        S.stations1{ii,8} = readtable(FName, 'Sheet', S_names{ii});
    end
    % save(mat_files{jj},'-struct','S'); % in case you want to overwrite the mat file, use the original name
    [pn,fn,ext] = fileparts(mat_files{jj});
    save(fullfile(pn,[fn '_modified' ext]),'-struct','S');
end