Reshape Cell to Matrix

2011 12 21
1 个回答
回答已采纳
3 次查看（30 天）

0 个投票

I'm receiving a very large quantity of data in a table like format with only 3 columns (Ticker, Return, Date). What I end up with are thousands of instances of the same date and ticker which becomes very inefficient for Matlab to utilize in calculations. I'd like a matrix with Tickers across the columns and dates down the rows. The trick is that not all tickers have a Return for every date so I can't just reshape the matrix based on a unique count of tickers and dates. Does anyone have any idea how to go about doing this? Here's an example of what I'm looking for.
Initial Format
IBM,1.01,1
IBM,1.04,2
IBM,1.06,3
GOOG,.99,1
GOOG,.98,2
AAPL,1,1
AAPL,1.1,2
AAPL,.99,3
Matrix Format below. Where Dates and Tickers would likely be separate arrays. This example should copy to Excel and make it more clear
,AAPL,GOOG,IBM
1,1,99,1.01
2,1.1,.98,1.04
3,.99,Nan,1.06

请先登录，再回答此问题。

 采纳的回答

Andrei Bobrov
Andrei Bobrov 2011-12-22

1 个投票

EDIT [13:20MSK 23.12.2011]
data = {'IBM',1.01,1
'IBM',1.04,2
'IBM',1.06,3
'GOOG',.99,1
'GOOG',.98,2
'AAPL',1,1
'AAPL',1.1,2
'AAPL',.99,3};
[a,b,b] = unique(data(:,1));
d23 = cell2mat(data(:,2:3));
N = max(d23(:,2));
outdata = accumarray([d23(:,2),b],d23(:,1),[N numel(a)],[],NaN)
out = [{'Name'} a';num2cell([(1:N)',outdata])]
OR
out = [{'Name'},a';...
num2cell((1:N)'),accumarray([d23(:,2),b],d23(:,1),[N numel(a)],@(x){x},{NaN})];
small corrected in [N numel(a)]

4 个评论
显示 2更早的评论 隐藏 2更早的评论

Brian
Brian 2011-12-22
Thanks a lot for the answer Andrei. I think I see where you're attempting to go with this and it might work. I do get an "Index exceeds matrix dimensions" error on the outdata line that I am attempting to debug. If you have a second to look at it today and help me fix the syntax it would be much appreciated.
Thanks,
Brian
Andrei Bobrov
Andrei Bobrov 2011-12-23
Hi Brian! Corrected.
Brian
Brian 2011-12-23
Pure Genius. Extremely useful function that accumarray, I couldn't figure out how to use it properly but I believe I understand it now looking at your revisions. Thanks so much for taking the time to help.
Brian
Brian
Brian 2011-12-27
So I'm having an issue adjusting this function to work with my actual dataset and it may be because I still don't understand the function quite right.
My actual dataset format is as follows....
data = {'06/03/2011','00163T10'
'06/06/2011','00163T10'
'06/07/2011','00163T10'
'06/03/2011','ABCDEFGH'
'06/06/2011','ABCDEFGH'
'06/07/2011','ABCDEFGH'
'06/03/2011','ABCDWXYZ'
'06/06/2011','ABCDWXYZ'};
How would I go about using the arguments above with a dataset in this format. Ultimately, I am attempting to create a logical matrix of whether the ticker was in or out (1 or 0) for each given day.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Downloads 的更多信息

产品

标签

Community Treasure Hunt

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

Start Hunting!

Translated by