Get new variable based on a condition

I have a double variable with 3 columns and 60000 rows, sorted by c2 and then by c1. For example:

     c1         c2      c3 
 M=[2008  1  1       
    2009  1  2
    2010  1  34
    1996  12  7
    1997  12  *11*
    1998  12  *5*
    1999  12  *5*  
    2000  12  4
    1998  13  14
    1999  13  1 ] %in this case I don't have the value for the two previous years

For each different c2, and if c1 is equal to 1999 I am trying to have a new variable P, with the year, with c2 and with the sum of the values in c3 from that year (1999 )and the two previous years/rows (1998 and 1997).

In this example my output would be:

P=[ 1999 12 21] %(5 + 5 + 11)

Thanks

[Image Analyst]

Is 13 not included because it did not have at least two prior years, it had only 1 prior year?

[Maria]

Exactly

[Maria]

Because it is possible that it happens.

Answer 1

[Image Analyst]

Try this and see if it's what you want:

clc;
workspace;
M=[2008  1  1       
    2009  1  2
    2010  1  34
    1996  12  7
    1997  12  11
    1998  12  5
    1999  12  5  
    2000  12  4
    1998  13  14
    1999  13  1 ]
uniqueC2 = unique(M(:,2))
P = [0,0,0];
counter = 1;
for k = 1 : length(uniqueC2)
  % Find rows for this c2.
  thisC2 = M(:,2) == uniqueC2(k);
  % Find rows where c1 == 1999
  validRows = M(:,1) == 1999;
  % AND them
  validRows = validRows & thisC2;
  if any(validRows)
    % This c2 has at least one year with 1999
    % Find out what row it's in.
    the1999Row = find(validRows);
    % Sum this row only if there are at least two prior rows
    % with the same value of C2.
    if thisC2(the1999Row-1) && thisC2(the1999Row-2)
      % Prior 2 rows also belongs to this c2.
      theSum = M(the1999Row, 3) + M(the1999Row - 1, 3) + M(the1999Row - 2, 3);
      P(counter, :) = [1999, uniqueC2(k), theSum];
      counter = counter + 1;
    end
  end
end
% Print to command window:
P

It's easy to understand and fast. If you want something more compact, but probably harder to understand, someone will probably post a one-liner. But this gives exactly the output you asked for.

[Maria]

Yes it does! It gives me exactly the output I want! It is a long code, but it's fast so it's perfect. Thank you very much.

Answer 2

[Azzi Abdelmalek]

M=[2008  1  1       
  2009  1  2
  2010  1  34
  1996  12  7
  1997  12   11 
  1998  12   5 
  1999  12   5   
  2000  12  4
  1998  13  14
  1999  13  1 ]
c4=ismember(M(:,1),1997:1999);
[ii,jj,kk]=unique(M(:,2),'stable');
b=accumarray(kk,M(:,3).*c4);
out=[1999*ones(numel(jj),1) ii b];
idx=accumarray(kk,c4)==3;
out=out(idx,:)

Answer 3

[Andrei Bobrov]

   M=[2008  1  1       
      2009  1  2
      2010  1  34
      1996  12  7
      1997  12  11
      1998  12  5
      1999  12  5 
      2000  12  4
      1997      13      100
      1998  13  14
      1999  13  1 
      1998      14      3
      1999      14      6];

[l0,ii] = ismember(M(:,1),1997:1999);
T = accumarray([ii(l0),M(l0,2)],M(l0,3),[],[],nan);
i1 = find(all(~isnan(T))).';
s = sum(T).';
out = [1999*ones(numel(i1),1), i1, s(i1)];

or with for..end loop

u = unique(M(:,2));
out = zeros(numel(u),3);
for i1 = 1:numel(u)
    M1 = M(u(i1) == M(:,2),:);
    t0  = ismember(M1(:,1),1997:1999);
    if nnz(t0) == 3
        out(i1,:) = [1999, u(i1), sum(M1(t0,3))];
    end
end
out = out(any(out,2),:);