CellStruct

Hi,

I would like to know if there are functions(specially if cellstruct would work) in MATLAB to solve my problem or do i have to go ahead with an algorithm to do the same.I have data in the structure attached. It is a n x m matrix basically where I have 1 million and odd rows and 4 columns in a excel sheet. The columns being code,name, date, price. I would like to transpose them into a structure where there is just 1 row for each cocode. Now i have multiple rows for each code for different dates. I'd like the dates to be part of the header and price in the data.

header [cocode coname date1 date2 date3...] data [123 hero honda 1500.00 1345.75 1279.50]

There are 3500 and odd distinct dates in my data. And all of them don't have data for all dates. So I will have to have a zero or some identifier to denote I don't have data for the date.

Is there a trick to do it quickly ? or no other go than writing an algorithm ?

Answer 1

[Oleg Komarov]

You may try out my Pivot/unPivot submission, I created it to manage time series as you pointed out.

• 1st case - double matrix

A1 = {'code','name'  ,'date','price'
      13    ,'Honda' ,734530, 1
      13    ,'Honda' ,734531, 1.1
      14    ,'Yamaha',734530, 0.5
      14    ,'Yamaha',734531, 0.52};

% I would keep strings out of the game to reduce memory consumption:
B1 = Pivot(A1(2:end,[3,1,4]));
B1 =
     NaN  734530  734531
      13       1     1.1
      14     0.5    0.52

% To get the names:
[unCod,idx] = unique([A1{2:end,1}]);
[tf,loc]    = ismember(B1(2:end,1),unCod);
A1(idx(loc)+1,2)
ans = 
    'Honda'
    'Yamaha'

• 2nd case - cell array (keeping names and dates as strings)

A2 = {'code','name'  ,'date'       ,'price'
      13    ,'Honda' ,'27-Jan-2011', 1
      13    ,'Honda' ,'28-Jan-2011', 1.1
      14    ,'Yamaha','27-Jan-2011', 0.5
      14    ,'Yamaha','28-Jan-2011', 0.52};

B2 = Pivot(A2(2:end,[3,2,4]))
B2 = 
    [   NaN]    '27-Jan-2011'    '28-Jan-2011'
    'Honda'     [          1]    [        1.1]
    'Yamaha'    [        0.5]    [       0.52]

Difference in memory usage:

whos B1 B2
 Name      Size            Bytes  Class     Attributes
  B1        3x3                72  double              
  B2        3x3               646  cell

Pivot allows you to keep the headers or to strip them, and to specify any other placeholder instead of NaN for missing data. I recommend to use NaN and to keep the matrix as double and not a cell.

Oleg

[Ms. Mat]

Thanks a million ton :) This is exactly what I wanted.