How to filter data from columns and extract corresponding x-values in excel files?
20 views (last 30 days)
Show older comments
Hi,
I have attached a part of my datasheet. I want to filter data >=0.125 under coating column. After filtering I want to pick the smallest value from the filtered data and find the corresponding x-value. For example, for column B, 0.175 is the desired coating value for which the corresponding X is 800. Please advise on the functions I can use to achieve this. When I am trying to filter data, it is also filtering my dilution values.
0 Comments
Accepted Answer
Voss
on 25 Jan 2022
Here are some ways to do this, depending on exactly what you want to do.
"Filtering" on column B only:
% data = xlsread('Sample data.xlsx'); % use this if you have an older version of MATLAB
data = readmatrix('Sample data.xlsx');
idx = data(:,2) >= 0.125;
data_subset = data(idx,:);
[~,min_idx] = min(data_subset(:,2));
data_subset(min_idx,1)
"Filtering" on each "Coating" column separately:
% data = xlsread('Sample data.xlsx'); % use this if you have an older version of MATLAB
data = readmatrix('Sample data.xlsx');
for i = 2:size(data,2)
idx = data(:,i) >= 0.125;
data_subset = data(idx,[1 i]);
[~,min_idx] = min(data_subset(:,2));
data_subset(min_idx,1)
end
"Filtering" on all "Coating" columns together (note that only the first instance of the minimum value will be used):
% data = xlsread('Sample data.xlsx'); % use this if you have an older version of MATLAB
data = readmatrix('Sample data.xlsx');
idx = [false(size(data,1),1) data(:,2:end) >= 0.125];
[ridx,~] = find(idx);
[~,min_idx] = min(data(idx));
data(ridx(min_idx),1)
3 Comments
Voss
on 25 Jan 2022
Edited: Voss
on 25 Jan 2022
Try this, which checks that there is at least one value >= 0.125 in each column (and don't use ans as a variable, and note that result here has length one less than the size of the table because the first column is treated differently):
data = readmatrix('Sample data.xlsx');
result = NaN(1,size(data,2)-1);
for i = 2:size(data,2)
idx = data(:,i) >= 0.125;
if ~any(idx)
continue
end
data_subset = data(idx,[1 i]);
[~,min_idx] = min(data_subset(:,2));
result(i-1)=data_subset(min_idx,1);
end
result
More Answers (0)
See Also
Categories
Find more on Timetables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!