Counting in one column that is conditional on another column

For each cell (or "trial") in the variable trials (attached):

I'd like to count the number of 1's in column 4 that is dependent on certain conditions in column 2 for each trial.

Conditions saved in their own variable:

Count 1's in column 4 if column 2 is between 0 and 4 (less than 4, not equal to)

Count 1's in column 4 if column 2 is between 4 and 5 (less than 5, not equal to)

Count 1's in column 4 if column 2 is between 5 and 6 (less than 6, not equal to)

Count 1's in column 4 if column 2 is between 6 and 7 (less than 7, not equal to)

Count 1's in column 4 if column 2 is between 7 and end

Answer 1

[per isakson]

Does this do what you look for?

%%
limits = { [0;4], [4;5], [5;6], [6;7], [7;inf] };
%%  some simple test data
trials  = {[ 0, 2, 0, 1, 0, 0, 0
             0, 2, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 9, 0, 1, 0, 0, 0 ]};
%%
fh = @(trial,lim) sum( (trial(:,4)==1) & (lim(1)<=trial(:,2) & trial(:,2)<lim(2)), 1 );
%%
counts = zeros( numel(trials), numel(limits) );
for rr = 1 : numel(trials)
    for cc = 1 : numel(limits) 
        counts(rr,cc) = fh( trials{rr}, limits{cc} );
    end
end

Inspect

>> counts
counts =
     1     0     2     0     1

Yes, sum() can operate on logicals

>> sum([true,true,false])
ans =
     2

[Mirthand]

When I inspect counts in the data it returns a 56 x 5 double where the first row

is:

29 0 0 0 0

It seems like it is summing column 4 for each trial but not counting within the limits. The first number 29 is correct for the total 1's in that trial column.

Edit: maybe its because in my file I have zeros in my data and in your sample test data there is not zeros in the second column. For example my data in second column:

[0;0;3;0;3;0;3;0;0;0;4;0]

[per isakson]

The matrix in the first cell of trials has nearly five thousand rows, which makes it hard to inspect visually. However, I tried and I think that [29 0 0 0 0] is the correct result.

>> sum( trials{1}(:,2)==1 & trials{1}(:,4)==1 )
ans =
    29
>> sum( trials{1}(:,2)==1 )
ans =
    29    

ans==29 in both cases. Thus trials{1}(:,2)==1 when trials{1}(:,4)==1

[per isakson]

With

trials  = {[ 0, 2, 0, 1, 0, 0, 0
             0, 2, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 6, 0, 1, 0, 0, 0 ]};

I get

>> counts
counts =
     1     0     2     1     0

[per isakson]

[29,0,0,0,0] is not what you expected!

• Did I misunderstand your question? Did you inspect my code?
• The data, trials, does that deviate from your expectations?

In the entire matrix, counts, (calculated with my code) values differ from zero only in the first column. (That's the case even when I initiate counts with NaNs.)

[Mirthand]

I think the data, trials, deviated from my expectations in that there are 1's in the second column.

I think if I can convert all the 1's in the second column to 0's, the count code should work.

trials  = {[ 0, 2, 0, 1, 0, 0, 0
             0, 2, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 1, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 1, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 6, 0, 1, 0, 0, 0 ]};

[per isakson]

clearvars, clc
%%  data from your recent comment
trials  = {[ 0, 2, 0, 1, 0, 0, 0
             0, 2, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 1, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 1, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 6, 0, 1, 0, 0, 0 ]};
%%  convert all the 1's in the second column to 0's,        
num = trials{1};
num( num(:,2)==1, 2 ) = 0;
trials = {num}; 
%%
limits = { [0;4], [4;5], [5;6], [6;7], [7;inf] };
%%
fh = @(trial,lim) sum( (trial(:,4)==1) & (lim(1)<=trial(:,2) & trial(:,2)<lim(2)), 1 );
%%
counts = nan( numel(trials), numel(limits) );
for rr = 1 : numel(trials)
    for cc = 1 : numel(limits) 
        counts(rr,cc) = fh( trials{rr}, limits{cc} );
    end
end

and the result is

>> counts
counts =
     3     0     1     1     0
>> 

[Mirthand]

For this data, I would want counts to be: 2 0 2 1 0

trials  = {[ 0, 2, 0, 1, 0, 0, 0        bin 1
             0, 2, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 1, 0, 1, 0, 0, 0        bin 1
             0, 5, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0        bin 3
             0, 1, 0, 1, 0, 0, 0        bin 1
             0, 5, 0, 0, 0, 0, 0    
             0, 0, 0, 0, 0, 0, 0
             0, 6, 0, 1, 0, 0, 0 ]};    bin 4

limits = { [0;4], [4;5], [5;6], [6;7], [7;inf] };

I would want the 7th line to be grouped with [5;6].

0, 1, 0, 1, 0, 0, 0

[per isakson]

"I would want counts to be: 2 0 2 1 0" and "I would want the 7th line to be grouped with [5;6]" That would break the rules given in your question. You would have to set up new rules.

(I have tagged the rows (in your most recent comment) to show to which bin each row contributes.)

[Mirthand]

You are right, I need to think carefully about how I want to change column 2 so that it works.

[Mirthand]

Thanks for your help again!

Answer 2

[Steven Lord]

Use discretize or findgroups to identify groups based on the values in column 2, then use groupsummary or splitapply to perform group processing on column 4 based on the groups you identified in the first step.

[Mirthand]

Would discretize work if there are zeros?

for example, I still want to group the third row with the group that is 2.

The 7th row, containing zero would also be grouped with 5.

trials  = {[ 0, 2, 0, 1, 0, 0, 0
             0, 2, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 5, 0, 1, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 5, 0, 0, 0, 0, 0
             0, 0, 0, 0, 0, 0, 0
             0, 6, 0, 1, 0, 0, 0 ]};