A complicated data manipulation--Help!

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!

Answer 1

[Andrei Bobrov]

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(:))]

[Sean de Wolski]

unique works on cellstrs so the str2double is probably skippable.

[Xiao Tang]

Your code is awesome! I didn't know accumarray could be used like this! Without a loop, it works more efficient~

Sean, str2double is not skippable.

[Sean de Wolski]

@Xiao, sure it is!

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 = cell2mat(A(:,2:end));

A2 = bsxfun(@times,A1(:,2:end)~=0,A1(:,1));

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(:))]

[Xiao Tang]

@Sean de Wolski. In this way you totally changed A1. Minor mistake but won't get the result.

[Sean de Wolski]

typo, should be

[a b b] = unique(A(:,1)), not A1

Answer 2

[per isakson]

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.

[Xiao Tang]

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.