Get new variable if a condition verifies in a cell

I have a cell type variable with 4 columns and 500000 rows, sorted by c3 and then by c1. For example:

     c1    c2    c3  c4
  A={2008 'VLO'   1   22
  2009    'VCEP' 1   2
  2009    'VLO'   1   22
  2010    'SMDO' 1   4
  2007    'SHBZ' 12  8
  2008    'WNI'   12  7
  2009    'AMB'   12  18
  2010    'CCE'   12  13 …}

For each different c3, and if c1 is equal to 2010 I am trying to have a new variable X, with the year, with c3 and with the average of the values in c3 from that year (2010)and the two previous years/rows (2009 and 2008).

In this example my output would be:

P= {2010 1  12,5                          %(22+2+22+4)/4
    2010 12 12,67}                        %(7+18+13)/3

Can someone help? Thank you.

Answer 1

[Ahmet Cecen]

First eliminate any rows that are not 2010, 2009 or 2008. I am assuming here that 2010 is the latest year so:

 check1=nnz(A(:,1)>=2008);
 B=zeros(check1,4);
 B(:,:)=A(find(A(:,1)>=2008),:);

Now get a list of unique c1:

 check2=unique(B(:,3));

Now march averages over c1:

 P=zeros(length(check2),3);

 for i=1:length(check2)
     current=B(find(B(:,3)==check2(i)),:); %Get c4 at every c3.
     if sum(current(:,1)==2010)>1
         P(i,1)=2010;
         P(i,2)=check2(i);
         P(i,3)=mean(current(:,4));
     end
 end

Finally, remove 0 rows of P:

 P=unique(P,'rows');

[Maria]

2010 is not the latest year! And A is a cell variable!

[Ahmet Cecen]

Then add extra lines :

 check=A(:,1)>=2008;
 check1=A(:,1)<=2010;
 checksize=nnz((check+check1)==2);
 B=zeros(checksize,4);
 B(:,:)=A(find((check+check1)==2),:);

Then the rest is the same.

Answer 2

[Andrei Bobrov]

Aw = cell2mat(A(:,[1,3,4]));
x = unique(Aw(:,2));
n = numel(x);
out = [2010*ones(n,1),x,nan(n,1)];
y = 2008:2010;
for ii = 1:n
    At = Aw(Aw(:,2) == x(ii),:);
    if all(ismember(y,At(:,1)))
        out(ii,end) = mean(At(ismember(At(:,1),y),3));
    end
end
out = out(~isnan(out(:,3)),:);