Selecting rows based on two criteria

Question

William Garrett 2020-1-7

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/499312-selecting-rows-based-on-two-criteria

评论： William Garrett 2020-1-9

采纳的回答： Meg Noah

Pump.mat

在 MATLAB Online 中打开

I need to work out how effective a pump is at removing 4 contaminants (Values A:D).

However, I have two pumps and need to work out how effective they are at different speeds and in isolation. I have four settings:

0 = pump off (at switch)
0.5 = pump off (below necessary conc for activation)
0.75 = pump on (half speed)
1 = pump on (full speed)

I need to know how each of the two pumps operate at removing the 4 contaminants for the following scenarios:

Pump is 0.75 AND other pump is 0.5
Pump is 1 AND other pump is 0
Pump is 1 OR 0.75 AND other pump is 0.5 OR 0

There are a few more possibilites, but there is not need to list them all.

How do I select an entire row of variables is multiple AND, OR demands are met in this way?

I have included some example data below and attached a .mat file but the actual data set splits time up per second and is far larger.

	             Value A  Value B Value C Value D Pump 1	Pump 2
16/10/2019 12:00	1	4	5	6	1	0.5
16/10/2019 18:00	4	5	6	14	0.75	0.5
17/10/2019 00:00	1	4	5	3	1	0.5
17/10/2019 06:00	4	2	3	5	0.75	0
17/10/2019 12:00	1	5	2	6	0	0.75
17/10/2019 18:00	4	3	4	6	0.5	1
18/10/2019 00:00	3	2	3	3	0.5	1
18/10/2019 06:00	4	4	2	6	0.5	1

2 个评论
显示无隐藏无

per isakson 2020-1-7

To help those, who want to try their solution before answering, upload the table (in a mat-file) or a script that creates the table.

William Garrett 2020-1-8

Thanks, I have attached a .mat file

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Meg Noah 2020-1-8

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/499312-selecting-rows-based-on-two-criteria#answer_409090

在 MATLAB Online 中打开

Hi, here's a solution that makes this assumption - your columns Pump On and Pump Off are really Pump1 and Pump2. Otherwise, I couldn't figure out what values you had for the two different pumps. I thought that on and off are the values in those columns.

clc
close all
clear all
VarNames = {'Datenum','ValueA','ValueB','ValueC','ValueD','Pump1','Pump2'};
values = { ...
    datenum(2019,10,16,12,00,00),	1,	4,	5,	6,	1,	0.5; ...
    datenum(2019,10,16,18,00,00),	4,	5,	6,	14,	0.75,	0.5; ...
    datenum(2019,10,17,00,00,00),	1,	4,	5,	3,	1,	0.5; ...
    datenum(2019,10,17,06,00,00),	4,	2,	3,	5,	0.75,	0; ...
    datenum(2019,10,17,12,00,00),	1,	5,	2,	6,	0,	0.75; ...
    datenum(2019,10,17,18,00,00),	4,	3,	4,	6,	0.5,	1; ...
    datenum(2019,10,18,00,00,00),	3,	2,	3,	3,	0.5,	1; ...
    datenum(2019,10,18,06,00,00),	4,	4,	2,	6,	0.5, 1};
pumpTable = cell2table(values,'VariableNames',VarNames);
% 0) summarize table
summary(pumpTable)
% 1) one way to query and display
fprintf(1,'Pump1 is 0.75 AND Pump2 is 0.5\n');
idx = find(pumpTable.Pump1 == 0.75 & pumpTable.Pump2 == 0.5);
if (~isempty(idx))
    for ival = 1:numel(idx)
        fprintf(1,'%s Contaminants A = %.1f B = %.1f C = %.1f D = %.1f\n', ...
            datestr(pumpTable.Datenum(idx(ival)),'dd/mm/yyyy HH:MM'), ...
            pumpTable.ValueA(idx(ival)),pumpTable.ValueB(idx(ival)), ...
            pumpTable.ValueC(idx(ival)),pumpTable.ValueD(idx(ival)));
    end
else
    disp('No records meet the search criteria!');
end
% 2) another way to query and display
fprintf(1,'\nPump1 is 1 AND Pump2 is 0\n');
idx = find(pumpTable.Pump1 == 1 & pumpTable.Pump2 == 0);
if (~isempty(idx))
    for ival = 1:numel(idx)
        fprintf(1,'%s Contaminants A = %.1f B = %.1f C = %.1f D = %.1f\n', ...
            datestr(pumpTable.Datenum(idx(ival)),'dd/mm/yyyy HH:MM'), ...
            pumpTable.ValueA(idx(ival)),pumpTable.ValueB(idx(ival)), ...
            pumpTable.ValueC(idx(ival)),pumpTable.ValueD(idx(ival)));
    end
else
    disp('No records meet the search criteria!');
end
% 3) another way to query and display
fprintf(1,'\nPump1 is 0.75 AND Pump2 is 0 or 0.5\n');
idx = find(pumpTable.Pump1 == 1 & (pumpTable.Pump2 == 0 | pumpTable.Pump2 == 0.5));
if (~isempty(idx))
    %     summary(pumpTable(idx,:))
    for ival = 1:numel(idx)
        fprintf(1,'%s Contaminants A = %.1f B = %.1f C = %.1f D = %.1f\n', ...
            datestr(pumpTable.Datenum(idx(ival)),'dd/mm/yyyy HH:MM'), ...
            pumpTable.ValueA(idx(ival)),pumpTable.ValueB(idx(ival)), ...
            pumpTable.ValueC(idx(ival)),pumpTable.ValueD(idx(ival)));
    end
else
    disp('No records meet the search criteria!');
end

3 个评论
显示 1更早的评论隐藏 1更早的评论

Meg Noah 2020-1-9

在 MATLAB Online 中打开

OK. This is one way. First, organize the data so that it is datenum, pumpA operating conditions, pumpB operating conditions, then a list of categories of contaminents, etc.

Search using logic to find the records that meet the criteria, then create a new table that meets just those criteria. Use the summary command to see what that table holds. Can print just by entering on the command line without terminator character. Can save as xlsx file. Can save as .mat file. Can print extensibly.

I'm not sure if this scales to millions of records. You might need to be using the big data set features of matlab. That would be a second question to ask.

clc
close all
clear all
VarNames = {'Datenum','Pump1','Pump2','ValueA','ValueB','ValueC','ValueD'};
values = { ...
    datenum(2019,10,16,12,00,00),	1,	0.5,	1,	4,	5,	6; ...
    datenum(2019,10,16,18,00,00),	0.75,	0.5,	4,	5,	6,	14; ...
    datenum(2019,10,17,00,00,00),	1,	0.5,	1,	4,	5,	3; ...
    datenum(2019,10,17,06,00,00),	0.75,	0,	4,	2,	3,	5; ...
    datenum(2019,10,17,12,00,00),	0,	0.75,	1,	5,	2,	6; ...
    datenum(2019,10,17,18,00,00),	0.5,	1,	4,	3,	4,	6; ...
    datenum(2019,10,18,00,00,00),	0.5,	1,	3,	2,	3,	3; ...
    datenum(2019,10,18,06,00,00),	0.5, 1,	4,	4,	2,	6};
pumpTable = cell2table(values,'VariableNames',VarNames);
% find entries the fit the search criteria
fprintf(1,'Pump1 is 0.75 AND Pump2 is 0.5\n');
idx = find(pumpTable.Pump1 == 0.75 & pumpTable.Pump2 == 0.5);
% create a new table that meets the search criteria
pumpTable_AHalfSpeed_BInactive = pumpTable(idx,:);
% to display just list it on the command line - no ';' termination
pumpTable_AHalfSpeed_BInactive
% to save in a .mat file
save('searchCriteria01.mat','pumpTable_AHalfSpeed_BInactive');
% to clear it from the workspace
clear pumpTable_AHalfSpeed_BInactive
% to restore it from a .mat file
load('searchCriteria01.mat','pumpTable_AHalfSpeed_BInactive');
% to save it as a spreadsheet
writetable(pumpTable_AHalfSpeed_BInactive,'pumpTable_AHalfSpeed_BInactive.xlsx');
% to have some sort of formatted printing
% (can just use indexing as in the last script if you want to not make a
% new table)
nvar = length(pumpTable_AHalfSpeed_BInactive.Properties.VariableNames);
nidx = length(pumpTable_AHalfSpeed_BInactive.Datenum);
dataOfInterest = table2array(pumpTable_AHalfSpeed_BInactive);
VariableNames = pumpTable_AHalfSpeed_BInactive.Properties.VariableNames;
if (~isempty(pumpTable_AHalfSpeed_BInactive))
    for idx = 1:nidx
        fprintf(1,'%s\n',datestr(pumpTable.Datenum(idx)));
        % start at 4 where the contamination variables start
        for ivar = 4:nvar
            fprintf(1,'\t%s = %f\n', VariableNames{ivar}, dataOfInterest(idx,ivar));
        end
    end
else
    disp('No records meet the search criteria!');
end