Geek Answers Handbook

List data in Matlab / R data matrix

I have a data matrix that looks like this:

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 something like:

  MSFT GOOG CSCO 2/1 .21 .11 .09 2/2 .22 .19 .15

Is there an easy way to do this transfer in Matlab / R, with the exception of run cycles, to get this?

+4
source share
5 answers

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
+5
source

and in matlab:

as if they were loaded using 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);

reorganized:

 [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 as elegant as R, but hey. MATRIXMULTIPLY!

+1
source

FOR MATLAB CODE, THE RIGHT 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);
+1
source

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

date = {'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;

0
source

The above solution for MATLAB will not work if you do not have data in the original matrix that is difficult to verify before conversion.

In 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 with NaN.

R melt / dcast so elegant. Love it! I want to have similar tools in MATLAB.

0
source

More articles:


All Articles