Selecting rows based on two criteria

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

[per isakson]

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]

Thanks, I have attached a .mat file

Answer 1

[Meg Noah]

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

[William Garrett]

Hi, thank you for your help and your assumption was correct.

I think this has worked, but it is giving me the output in a string format

The actual data set is far far larger and contains millions of date:times. I was only able to include a subset for confidentially/ data privacy purposes.Is there a way to have this data exported to a table?

Also, I have far more categories than just A,B,C,D. Is there a way to define for the script to select them instead of typing them in individually?

Thank you for your help

[Meg Noah]

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

[William Garrett]

I think this is exactly what I needed. I have marked the answer as correct. Thank you ever so much for your help