how to plot a diagram that includes many excel data?
Show older comments
hello everyone. I have a problem with finding an approprieate program in matlab.
I have specifically 173 excel files including a sheet of time data and a sheet of many concentration data from different chemical compounds. The excel files are connected as the time continues in every file. My task is to find the averages of both time and concentration per 5 minutes and per 1 hour and plot seperately each chemical compound concentration with time ( one diagram every time). The problem is that i dont know how to insert all of the excel files and connect the data
5 Comments
Mathieu NOE
on 28 Feb 2024
Edited: Mathieu NOE
on 28 Feb 2024
I suggest to split the work in two steps
1/ make a loop to read all your excel files and generate a single array containing all your excel data
2/ then do the stats (averages )
for topic 1 , you can use this template code (adapt to your own needs)
in case you have all files in the same folder
fileDir = pwd; % current directory (or specify which one is the working directory)
outfile = 'OUT.xlsx'; % output file name
S = dir(fullfile(fileDir,'data00*.xlsx')); % get list of data files in directory
S = natsortfiles(S); % sort file names into natural order , see :
%(https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
max_rows = 1e4; % rough estimate (by excess) of max number of rows
out_data = [];
max_nn = 0;
for k = 1:length(S)
filename = S(k).name % to actually show filenames are sorted (see command window)
out = readmatrix( fullfile(fileDir, filename),"Range",'C:C'); % extract column 3 (C) of each xlsx files
nn = numel(out);
tmp = NaN(max_rows,1); % initiate tmp vector with NaN's
tmp(1:nn) = out; % fill tmp vector with data (from the top)
out_data = [out_data tmp]; % vertical concatenation
max_nn = max(max_nn,nn); % store longest file rows qty (see below : retrieve trailing nan's)
end
% retrieve trailing nan's
out_data = out_data(1:max_nn,:);
% store out_data in excel file
writematrix(out_data,fullfile(fileDir,outfile));
in case you have multiple folders , you can use that code (again, adapt to your own needs)
%% define path
yourpath = pwd; % or your specific path
list=dir(yourpath); %get info of files/folders in current directory
isfile=~[list.isdir]; %determine index of files vs folders
dirnames={list([list.isdir]).name}; % directories names (including . and ..)
dirnames=dirnames(~(strcmp('.',dirnames)|strcmp('..',dirnames))); % remove . and .. directories names from list
%% demo for excel files
sheet = 1; % specify which sheet to be processed (my demo) - if needed
%% Loop on each folder
for ci = 1:length(dirnames) %
fileDir = char(dirnames(ci)); % current directory name
S = dir(fullfile(fileDir,'data_*.xlsx')); % get list of data files in directory according to name structure 'Sheeta*.xlsx'
S = natsortfiles(S); % sort file names into natural order (what matlab does not) , see FEX :
%(https://fr.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort)
%% Loop inside folder
for k = 1:length(S) % read data in specified sheet
data = xlsread(fullfile(fileDir, S(k).name),sheet); % or use a structure (S(k).data ) to store the full data structure
% your own code here for data processing. this is just for my demo
% for now :
title_str = [fileDir ' / ' S(k).name ' / sheet : ' num2str(sheet)];
figure,plot(data),title(title_str);
end
end
Dyuman Joshi
on 28 Feb 2024
"The problem is that i dont know how to insert all of the excel files and connect the data"
See the 2nd example on this documentation page for example - https://in.mathworks.com/help/matlab/import_export/process-a-sequence-of-files.html
Star Strider
on 28 Feb 2024
Are the times in each file sequential between files (so the end time of one file is the beginning of the next file) and all the columns (variables) are the same, or are the times all the same (or close to being the same times)?
If they are sequential and have the same variables, you can vertically concatenate the files easily, however if they are all the same times, that would require either outerjoin or synchronize to horizontally concatenate the tables correctly.
Please provide at least two of the files. Use the paperclip icon in the top toolstrip (to the right of the Σ symbol) to attach the files.
Athena Argyropoulou
on 28 Feb 2024
Athena Argyropoulou
on 28 Feb 2024
Accepted Answer
Star Strider
on 28 Feb 2024
Edited: Star Strider
on 29 Feb 2024
One approach is to read the files in to separate cell arrays, then vertically concatenate them, and then choose the ‘Absolute Time’ column from the concatenated ‘Time’ sheet and horizontally concatenate it with the ‘Concentration’ sheet to get the result.
Try this —
files = dir('*.xlsx');
for k = 1:numel(files)
shnm = sheetnames(files(k).name);
% Choose_Sheets = shnm([2 4])
Time{k,:} = readtable(files(k).name, 'VariableNamingRule','preserve', 'Sheet',2);
Start_Stop = Time{k}([1 end],:)
Conc{k,:} = readtable(files(k).name, 'VariableNamingRule','preserve', 'Sheet',4);
end
Timevc = vertcat(Time{:});
Concvc = vertcat(Conc{:});
Time_Conc = [Timevc(:,2) Concvc]
VN = Time_Conc.Properties.VariableNames;
figure
plot(Time_Conc{:,1}, Time_Conc{:,2:6}) % Plot Some Example Data
grid
xlabel('Time')
ylabel('Concentration')
title('Concentration Data')
legend(VN{2:6}, 'Location','best')
EDIT — (29 Feb 2024 at 01:37)
If the Excel files are not imported in sorted order, to sort them by time, add:
Time_Conc = sortrows(Time_Conc,1);
after creating the concatenated ‘Time_Conc’ table (in my code, name it appropriately for your needs).
.
More Answers (0)
Categories
Find more on Spreadsheets in Help Center and File Exchange
on 28 Feb 2024
on 29 Feb 2024
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
