Resources

collapse the observations

0

Hi Aptech,

I have a question like this, if I have a matrix in the form of

X=[A 2, B 3, C 5, B 4]

I want to produce a matrix like this

Y=[A 2 NA,
   B 3  4,
   C 5 NA]

Could you please tell me how I can get this.

Thank you very much !


asked August 25, 2014

1 Answer

0

Below is a procedure that should work. I would encourage you to step through this with the debugger to help you to understand how it works.

X = { A 2, B 3, C 5, B 4 };
X_new = collapseObs(X);

proc (1) = collapseObs(X);
   local U, mask, max_len, Y, Y_row;
    
   //Find the unique identifiers
   //from column 1
   U = unique(X[.,1], 0);
    
   //each column of 'mask' corresponds
   //to a row from 'U', above
   mask = (X[.,1] .$== U');
    
   //Find out the maximum number of
   //elements corresponding to any
   //of the unique values in 'U'
   max_len = maxc(sumc(mask));
    
   //Fill a matrix full of missing values
   Y = reshape(error(0), rows(U), max_len + 1);
    
   //Overlay unique values from 'U'
   //into first column
   Y[.,1] = U;
    
   //Fill in observations
   for i(1, rows(Y), 1);
       //Find observations for the
       //i'th element of 'U'
       Y_row = selif(X[.,2], mask[.,i])';
       Y[i, 2:cols(Y_row)+1] = Y_row;
   endfor;
    
   retp(Y);
endp;
aptech
342