Measure Sum of column if statements are true

Hi there,

I have a matrix array of 4 columns

first: ID
Second: ShopID
Third: Visits
Forth: 1 or -1

How can I measure the sum of Visits (3rd column) for every ID (1st column) when the 4th column is 1 and how many visits when the 4th column is -1

Thanks

[the cyclist]

Do you want the sum of each ID separately, or all IDs summed together?

[Panayiotis Christodoulou]

each id separetely

Answer 1

[Ahmed Rashid]

a = [1 1 2 1; 2 2 3 1; 1 1 2 -1; 2 2 1 -1; 3 3 2 1; 1 2 3 1; 1 2 1 -1];

uniqueIds = unique(a(:, 1));

nrOfUniqueIds = length(uniqueIds);
sortedSumOnes = zeros(nrOfUniqueIds, 2); % first column is id second is the sum
sortedSumMinusOnes = zeros(nrOfUniqueIds, 2); % first column is id, second is the sum

for ii = 1:nrOfUniqueIds
    sortedSumOnes(ii, :) = [uniqueIds(ii), sum(a((a(:, 4) == 1) & (a(:, 1) == uniqueIds(ii)), 3))];
    sortedSumMinusOnes(ii, :) = [uniqueIds(ii), sum(a((a(:, 4) == -1) & (a(:, 1) == uniqueIds(ii)), 3))];
end

Just replace the a matrix with your own matrix

[Panayiotis Christodoulou]

thanks

Answer 2

[Matt Kindig]

There are a couple of ways to do this, but I think the easiest is to use the consolidator function on the File Exchange here:

In that way, the result would be found as

% suppose "M" is your N x 4 matrix
positive = (M(:,4)==1);    
negative = (M(:,4)==-1);
[ID, positiveSum] = consolidator(M(positive,1), M(positive,3), @sum);
[ID, negativeSum] = consolidator(M(negative,1), M(negative,3), @sum);