A complicated data manipulation--Help!

1 view (last 30 days)
Guys, I know It's complicated. But I really need your help. Please take a look at my example and you'll know how it works.
A =
'15' 3854 4534 0 31
'10' 6331 0 346 0
'10' 4324 342 124 11
'20' 3874 345 0 13
'25' 4321 73 311 15
'15' 6835 0 0 14
'10' 3321 0 153 16
'15' 3821 532 129 19
'20' 9324 153 0 0
I need to sum the 2nd column according to the 1st column and another respective column, and then create a new matrix. E.g, for '10', in the new matrix the first column is:
'10'
'15'
'20'
'25'.
In the 2nd column I should sum up (0,4324,0)),because 1st, they all belong to '10'. 2nd,only choose 4324 because in the original 3rd column it is non-zero. Always sum up numbers in Column 2. The respective column gives you a signal that you should not include the number if it's zero.
In the 3rd column of the new matrix I should sum up(6331,4324,3321), because they all belong to '10' and in the original 3rd column all of them are non-zero, and so on. The final result should be
B =
'10' 4324 13976 7645
'15' 7675 3821 14510
'20' 13198 0 3874
'25' 4321 4321 4321
I absolutely have no idea how to do this... Any tips??
======== Both andrei bobrov's and per isakson's codes work!

Accepted Answer

Andrei Bobrov
Andrei Bobrov on 18 Jun 2012
A ={...
'15' 3854 4534 0 31
'10' 6331 0 346 0
'10' 4324 342 124 11
'20' 3874 345 0 13
'25' 4321 73 311 15
'15' 6835 0 0 14
'10' 3321 0 153 16
'15' 3821 532 129 19
'20' 9324 153 0 0};
A1 = [str2double(A(:,1)) cell2mat(A(:,2:end))];
A2 = bsxfun(@times,A1(:,3:end)~=0,A1(:,2));
n = size(A2);
[a b b] = unique(A1(:,1));
m = ones(n(1),1)*(1:n(2));
subs1 = [kron(ones(n(2),1),b), m(:)];
out = [a,accumarray(subs1,A2(:))]
  5 Comments
Xiao Tang
Xiao Tang on 20 Jun 2012
@Sean de Wolski. In this way you totally changed A1. Minor mistake but won't get the result.
Sean de Wolski
Sean de Wolski on 20 Jun 2012
typo, should be
[a b b] = unique(A(:,1)), not A1

Sign in to comment.

More Answers (1)

per isakson
per isakson on 18 Jun 2012
Here is an alternative code. The statement in the inner loop is a "translation" of your description. I didn't see the point in using characters in the first column.
A = [
15 3854 4534 0 31
10 6331 0 346 0
10 4324 342 124 11
20 3874 345 0 13
25 4321 73 311 15
15 6835 0 0 14
10 3321 0 153 16
15 3821 532 129 19
20 9324 153 0 0 ];
key_list = unique( A(:,1) );
B = nan( length( key_list ), 4 );
for kk = 1 : length( key_list )
key = key_list(kk);
B( kk, 1 ) = key;
for col = 3 : 5
B(kk,col-1) = sum( A((A(:,1)==key) & not(A(:,col)==0), 2), 1 );
end
end
.
Logical indexing is powerful.
  1 Comment
Xiao Tang
Xiao Tang on 20 Jun 2012
Thanks per isakson. Your code is simpler and powerful. It didn't take much time to understand! Really nice use of sum
I didn't know the term 'Logical Indexing' before but I checked it out. Thanks to you I've learned a lot.
By the way the first column is in string. That's the condition. I can surely transfer it to numerical first.

Sign in to comment.

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!