data list to data matrix matlab/R

I have a matrix of data which looks like:

date ticker return

2/1  MSFT   .21

2/1  GOOG   .11

2/1  CSCO   .09

2/2  MSFT   .22

2/2  GOOG   .19

2/2  CSCO   .15

and I want to transfer the structure to somethin开发者_如何学JAVAg like:

     MSFT   GOOG  CSCO

2/1  .21    .11   .09

2/2  .22    .19   .15

Is there an easy way to make this transfer in Matlab/R, other than run loops to get this?

You can use the reshape2 library:

library(reshape2)

#sample data
dat <- data.frame(
    date = rep(c("2/1", "2/2"), each = 3)
  , ticker = rep(c("MSFT", "GOOG", "CSCO"), 2)
  , return = runif(6)
)

#Cast the data 
> dcast(dat, date ~ ticker, value_var = "return")

  date      CSCO      GOOG       MSFT
1  2/1 0.2555900 0.6212102 0.43078011
2  2/2 0.7092036 0.7013563 0.01225488

and in matlab:

data as if loaded by textscan:

dates = {'2/1','2/1','2/1','2/2','2/2','2/2'}
sites = {'MSFT','GOOG','CISCO','MSFT','GOOG','CISCO'}
vals = rand(1,6);

reshaped:

[uDate,uiDate,ujDate] = unique(dates);
[uSite,uiSite,ujSite] = unique(sites);
sz = [length(uDate),length(uSite)];
valArray = vals(sub2ind(sz,ujDate,ujSite))
valArray = reshape(valArray,sz);

the value for '2/1' 'GOOG' is now in

valArray(find(strcmpi('2/1',uDate),1), find(strcmpi('GOOG',uSite),1))

not quite as elegant as R, but hey. MATRIXMULTIPLY!

FOR MATLAB CODE, THE CORRECT ONE SHOULD BE:

dates = {'2/1','2/1','2/1','2/2','2/2','2/2'}
sites = {'MSFT','GOOG','CISCO','MSFT','GOOG','CISCO'}
vals = 1:6;

[uDate,uiDate,ujDate] = unique(dates);
[uSite,uiSite,ujSite] = unique(sites);

sz = [length(uDate),length(uSite)];

positionIndex=sub2ind(sz,ujDate,ujSite);

[B,IX] = sort(positionIndex);

valArray = vals(IX);
valArray = reshape(valArray,sz);

Or in a more general case, if there were missing values:

dates = {'2/1','2/1','2/1','2/2','2/2','2/2'};

sites = {'MSFT','GOOG','CISCO','MSFT','GOOG','CISCO'};

vals = 1:6;

[uDate,uiDate,ujDate] = unique(dates);

[uSite,uiSite,ujSite] = unique(sites);

sz = [length(uDate),length(uSite)];

positionIndex=sub2ind(sz,ujDate,ujSite);

valArray=zeros(length(uDate),length(uSite));

valArray(positionIndex)=vals;

The above solution for MATLAB won't work, if you have missing data in the original matrix that is hard to check before transformation.

In the DAVE's answer I would replace

valArray = vals(IX);
valArray = reshape(valArray,sz);

with

valArray = nan(sz);
valArray(B) = vals(positionIndex);

Missing values will be replaced by NaN.

R's melt/dcast are so much more elegant. Love it! I wish to have similar tools in MATLAB.