Importing data from excel and plotting
6 views (last 30 days)
Show older comments
I have to import data from the excel file (attached) and plot the data for each hour for each day. How do I go about it?
Thanks
0 Comments
Answers (2)
Walter Roberson
on 26 Oct 2023
readcell()
Parse row 3 column 1 to figure out the first date -- which is in a completely different format than all of the other dates in the file.
After that, throw away the first 8 rows.
strcmp() on column 2 to find all of the 'N/A'; and replace those cells with NaN; save the output back in column 2
regexp() on column 1 to locate '\d\d:\d\d' with 'once' option and 'match'; save the output to a variable Hours
regexp on column 1 to locate '\d\d\.\d\d\.\d\d\d\d' with 'once' option and 'match'; save the output to a second variable Dates
now you need to start doing maskHours = ~cellfun(@isempty, Hours); maskDates = ~cellfun(@isempty, Dates);
Now you need to identify blocks in maskHours .
block_starts = strfind([false; maskHours].', [0 1]);
block_stops = strfind([maskHours; false].', [1 0]);
block_starts and block_stops will both be vectors the same length, with the property that there was a block of hour data from starting from row block_starts(J) ending with block_stops(J) . You can now use arrayfun() to extract those rows using 'uniform', 0 option, which will give you a cell array with one day's worth of hour data. You can then cellfun and pull out column 1 of each block and duration() it to get the hour numbers per block, and you can cellfun to pull out column 2 for the matching number data
You do not need to use strfind() on maskDates: you can just use find() because they are all isolated.
Now remember that the first date line you extract this way will be for the second day -- the date for the first day was in that ugly format in row 3.
Anyhow, you can now pull together the date information, and the extracted hour durations, and the corresponding numeric blocks, and do whatever plotting is appropriate.
4 Comments
Pratyush
on 26 Oct 2023
Hi Indrani,
I understand that you want to plot the data of your XLSX file.
You can use 'xlsread' function to read data from the excel file.
[~, txt, raw] = xlsread('your_file.xlsx');
You can now iterate over the txt cell array and make an array of struct, where each struct contain an array of x and y values for each day.
for i = startIndex:size(txt, 1)
x = txt{i, 1};
y = txt{i, 2};
% Check if x contains a '-' symbol
if contains(x, '-')
% Check if the current struct is empty
if isempty(currentStruct)
% Create a new struct and start filling x and y
currentStruct = struct('X', {string(x)}, 'Y', {str2double(y)});
else
% Add x and y to the current struct
currentStruct.X = [currentStruct.X,string(x)];
currentStruct.Y = [currentStruct.Y,str2double(y)];
end
else
% Add the current struct to the data array
if ~isempty(currentStruct)
data = [data, currentStruct];
end
end
end
% Add the last struct to the data array if it's not empty
if ~isempty(currentStruct)
data = [data, currentStruct];
end
Now you can create plot for each of the struct by iterating the data array
% Number of structs in the array
numStructs = numel(data);
figure;
hold on;
for i = 1:numStructs
labels = [data(i).X]';
values = [data(i).Y]';
plot(values);
xticks(1:numel(labels));
xticklabels(labels);
xlabel('Labels');
ylabel('Values');
title('Line Graph');
end
hold off;
1 Comment
Dyuman Joshi
on 26 Oct 2023
Even though Walter has clearly mentioned to not use xlsread() and the documentation of xlsread says it is not recommended to use, you still suggest to use it.
Why? Why do you want to use deprecated functions?
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!