MATLAB: Extracting data from excel spreadsheet

excel spreadsheet

I am working on a project where I have temperature values at different depths over a time range. As depth increases, there are dates with unavailable data. I am trying to write a script that extracts the lowest (zero depth) and the highest depth temperatures for each day. I would appreciate help with a script to addres this. A smaller sample of the spreadsheet is attached.

Best Answer

This solution reads in the data using readmatrix() and stores the headers (row 1) and row-definitions (col 1) in separate variables and removes them from the matrix.
An anonymous function ("depthFunc") is created that allows you to apply any function handle to the row-definitions column (depth) while only considering non-nan elements for each column of the matrix.
See comment for details. 
% Read the entire file as a matrix
T = readmatrix('sample.xlsx');
% Remove header (row 1) and row-defs ("depths", column 1)
dt = datetime(T(1,2:end),'ConvertFrom','datenum');
depth = T(2:end,1); 
T(1,:) = []; 
T(:,1) = []; 
% Now we've got size T [n x m], dt [1 x m, and depth [n x 1]
% Create a function that applies any function handle to the values of 'depth'
% that correspond to non-nan elements of each column of T.
% The input is a function handle. For example, 
%  depthFunc(@max) will find the max value of depth for each column of T, ignoring NaNs. 
%  depthFunc(@median) will find the median value of depth for each column of T, ignoring NaNs. 
depthFunc = @(fcn)arrayfun(@(i)fcn(depth(~isnan(T(:,i)))),1:size(T,2));
% Get min depth per day and its row number
[minDepth, minDepthRowNum] = depthFunc(@min); 
% Get the max depth per day and its row number
[maxDepth, maxDepthRowNum] = depthFunc(@max); 
% Get the mean depth per day
meanDepth = depthFunc(@mean);
% Get the range of depths per day
rangeDepths = depthFunc(@range);
*Note, the minDepthRowNum and maxDepthRowNum values will be incorrect if there are any NaN values at the start of each day or in between other data points. If NaNs are just at the end of the each day, those values will be OK. But those aren't the values you were asking about, anyway.
Related Question