How can I get my script to calculate the average correctly?

Hello. I have an excel sheet that I get output from an experiemnt that contains at minimimum 13 sheets. I am attempting to take all the values from two columns in each sheet, labeled "displacement into surface" and "load on sample," in order to generate a new table called average load and average depth. My script outputs each curve into two columns but doesn't take the average and I am not sure why. Script is included below and I have been stuggling on it.

% Define the directory where your Excel files are located
directory = 'C:\Users\Brans\Desktop\Research Data\Nanoindentation Data\PSSH';
% List all Excel files in the directory
excelFiles = dir(fullfile(directory, '3.25.24-PSSH-0.10mNs-1.xls'));
% Initialize arrays to store depth and load data
allDepthData = [];
allLoadData = [];
% Loop through each Excel file
for i = 1:numel(excelFiles)
    % Read the Excel file
    [~, sheetNames] = xlsfinfo(fullfile(directory, excelFiles(i).name));
    
    % Loop through each sheet in the Excel file
    for j = 4:numel(sheetNames) % Start from the fourth sheet
        % Read the data from "Displacement Into Surface" and "Load On Sample" columns
        [~, ~, raw] = xlsread(fullfile(directory, excelFiles(i).name), sheetNames{j});
        
        % Find the column indices corresponding to "Displacement Into Surface" and "Load On Sample"
        displacementColumnIndex = find(strcmpi(raw(1, :), 'Displacement Into Surface'));
        loadColumnIndex = find(strcmpi(raw(1, :), 'Load On Sample'));
        
        % Print the column indices
        disp(['Displacement column index: ' num2str(displacementColumnIndex)]);
        disp(['Load column index: ' num2str(loadColumnIndex)]);
        
        % Extract depth and load data from the corresponding columns
        depth = cell2mat(raw(3:end, displacementColumnIndex));
        load = cell2mat(raw(3:end, loadColumnIndex));
        
        % Store depth and load data
        allDepthData = [allDepthData; depth];
        allLoadData = [allLoadData; load];
    end
end
% Determine the maximum number of rows among all sheets
maxRows = max(size(allDepthData, 1), size(allLoadData, 1));
% Pad depth and load data to have the same number of rows
depthDataPadded = [allDepthData; nan(maxRows - size(allDepthData, 1), size(allDepthData, 2))];
loadDataPadded = [allLoadData; nan(maxRows - size(allLoadData, 1), size(allLoadData, 2))];
% Calculate average depth and average load for each row
averageDepth = mean(depthDataPadded, 2, 'omitnan');
averageLoad = mean(loadDataPadded, 2, 'omitnan');
% Create a table with the averaged depth and load data
dataTable = table(averageDepth, averageLoad);
% Save the table to an Excel file
outputFileName = 'PSSH0.01.xlsx';
writetable(dataTable, outputFileName); % By default, writetable saves as Excel file
% Display a message indicating the file has been saved
disp(['Data has been saved to ' outputFileName]);

Answer 1

[Voss]

Are you sure you want to take the mean over the second dimension here?

% Calculate average depth and average load for each row

averageDepth = mean(depthDataPadded, 2, 'omitnan');

averageLoad = mean(loadDataPadded, 2, 'omitnan');

If each sheet has one column called 'Displacement Into Surface' and one column called 'Load On Sample', then depthDataPadded and loadDataPadded are column vectors, because you're doing vertical concatentation here:

% Store depth and load data

allDepthData = [allDepthData; depth];

allLoadData = [allLoadData; load];

Taking the mean over the second dimension of a column vector has no effect, since its size in the second dimension is 1. Example:

data = rand(4,1)

data = 4x1

0.1411 0.4829 0.7847 0.1575

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

mean(data,2,'omitnan')

ans = 4x1

0.1411 0.4829 0.7847 0.1575

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Instead, it seems like you would take the mean over the first dimension:

mean(data,1,'omitnan')

ans = 0.3916

However, it's likely you intended depthDataPadded and loadDataPadded to be matrices, not column vectors. For that, you'd have to change how allDepthData and allLoadData are constructed. In particular, you'd need to do horizontal concatenatation instead of vertical concatenation, but you'd also need to handle the fact that different sheets can have different number of rows of data, e.g., by padding with NaNs inside the loops before concatenating horizontally.

[Branston]

So i had my whole idea backwards. I wasn't really implementing a code that was anywhere close to the goal. I restarted and got it to work.