Importing data from excel and plotting

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

Answer 1

[Walter Roberson]

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.

[Indrani]

When I am reading the file using xlsread(), the first few NaN values are not being read. How do I solve this?

[Walter Roberson]

Solve it by not using xlsread() .

As I indicated, use readcell()

[Indrani]

Too complicated, I still could not solve it.

Thanks Walter

[Walter Roberson]

Your data format is unnecessarily complicated.

Answer 2

[Pratyush]

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;

[Dyuman Joshi]

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?