Get new variable if a condition verifies in a cell

1 view (last 30 days)
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.

Accepted Answer

Ahmet Cecen
Ahmet Cecen on 14 Aug 2014
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');
  2 Comments
Maria
Maria on 14 Aug 2014
Edited: Maria on 14 Aug 2014
2010 is not the latest year! And A is a cell variable!
Ahmet Cecen
Ahmet Cecen on 14 Aug 2014
Edited: Ahmet Cecen on 14 Aug 2014
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.

Sign in to comment.

More Answers (1)

Andrei Bobrov
Andrei Bobrov on 14 Aug 2014
Edited: Andrei Bobrov on 14 Aug 2014
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)),:);

Tags

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!