Extracting data from 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.

Answer 1

[Adam Danz]

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.

[Yeye10]

Thank you Adam. The output information I require for each time period is the temperature at depth 0 and the temperature at the max depth before the NaNs. I'm looking at how to obtain that information from the script.

[Adam Danz]

Woops....

% Read the entire file as a matrix
T = readmatrix('sample.xlsx');
% Remove header and depths
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 depth matrix
depthMat = depth * ones(1,size(T,2));
depthMat(isnan(T)) = NaN; 

% Find temperature at max-depth per day
[~, rowNumMax] = max(depthMat,[],1); 
idx = sub2ind(size(T),rowNumMax,1:size(T,2)); 
tempMax = T(idx); 
depthMax = depthMat(idx); 

% Find temperature at min-depth per day
[~, rowNumMax] = min(depthMat,[],1); 
idx = sub2ind(size(T),rowNumMax,1:size(T,2)); 
tempMin = T(idx); 
depthMin = depthMat(idx); 

[Yeye10]

Thanks. I attached a spread sheet of the anticated results I'm trying to obtain from the data.

[Adam Danz]

"The output information I require for each time period is the temperature at depth 0 and the temperature at the max depth before the NaNs"

That's what the code in my comment does above.

dt are the dates

tempMin are the min temperatures

tempMax are the max temperatures

I've updated that comment to show how to get the depths, too.

Have you tried it?

[Yeye10]

Thank you! I've tried it and It works perfectly.

[Adam Danz]

Good! now you can organize those vectors into a table to keep the data organized. It will look something like this (not tested)

table(dt(:), tempMax(:), depthMax(:), tempMin(:), depthMin(:),...
    'VariableNames', {'dates','tempMax','depthMax','tempMin','depthMin'})

Or you could use a timetable().

[Yeye10]

I just used it, works perfectly. Thanks!!

Answer 2

[darova]

Use xlsread to read data

Use surf to display it