MATLAB Answers

CellStruct

1 view (last 30 days)
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 ?

Accepted Answer

Oleg Komarov
Oleg Komarov on 6 Feb 2011
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
  1 Comment
Ms. Mat
Ms. Mat on 6 Feb 2011
Thanks a million ton :) This is exactly what I wanted.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by