MATLAB: Measure Sum of column if statements are true

Question

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

Answer 1

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