Extracting data from excel spreadsheet
256 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
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.
0 Commenti
Risposta accettata
Adam Danz
il 11 Ott 2019
Modificato: Adam Danz
il 7 Ott 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 Commenti
Adam Danz
il 11 Ott 2019
Modificato: Adam Danz
il 11 Ott 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().
Più risposte (1)
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!