Extracting data from excel spreadsheet

260 views (last 30 days)
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.

Accepted Answer

Adam Danz
Adam Danz on 11 Oct 2019
Edited: Adam Danz on 7 Oct 2020
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.
  7 Comments
Adam Danz
Adam Danz on 11 Oct 2019
Edited: Adam Danz on 11 Oct 2019
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
Yeye10 on 11 Oct 2019
I just used it, works perfectly. Thanks!!

Sign in to comment.

More Answers (1)

darova
darova on 11 Oct 2019
Use xlsread to read data
Use surf to display it

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!