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.
采纳的回答
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 个评论
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
2019-10-11
Adam Danz
2019-10-11
"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
2019-10-11
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
2019-10-11
更多回答（1 个）
darova
2019-10-11
0 个投票
Use xlsread to read data
Use surf to display it
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Translated by 
