MATLAB Central > MATLAB Newsreader > [Time series] everything in one column. Need ma...

Thread Subject: [Time series] everything in one column. Need matrix.

Subject: [Time series] everything in one column. Need matrix.

From: Peter

Date: 8 Feb, 2010 23:57:05

Message: 1 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

Hi. Have search already for couple hours but cannot find a solution. Suppose i have the following matrix

'1-1-1999' 1
'1-2-1999' 2
'1-3-1999'
'1-1-1999' 3
'1-2-1999' 4
'1-3-1999' 6

but i need it in this format:

'1-1-1999' 1 3
'1-2-1999' 2 4
'1-3-1999' 6

indeed there is missing data, i need to have it. it is allowed to be NaN.

could anyone give a hint? Thanks alot

Subject: everything in one column. Need matrix.

From: TideMan

Date: 9 Feb, 2010 00:36:33

Message: 2 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

On Feb 9, 12:57 pm, "Peter " <han...@geemaaaiill.com> wrote:
> Hi. Have search already for couple hours but cannot find a solution. Suppose i have the following matrix
>
> '1-1-1999' 1
> '1-2-1999' 2
> '1-3-1999'
> '1-1-1999' 3
> '1-2-1999' 4
> '1-3-1999' 6
>
> but i need it in this format:
>
> '1-1-1999' 1 3
> '1-2-1999' 2 4
> '1-3-1999' 6
>
> indeed there is missing data, i need to have it. it is allowed to be NaN.
>
> could anyone give a hint? Thanks alot

When you say you "have" this matrix, what do you mean exactly?
Is it a file you must read in?
Or is it a cell array?
It cannot be a matrix as such because the first column is a string.

Subject: [Time series] everything in one column. Need matrix.

From: Peter

Date: 9 Feb, 2010 00:40:22

Message: 3 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

Sorry, i mean i have three columns in my initial matrix

date1 stock1 10.22
date2 stock1 11.33
date3 stock1 12.33
date4 stock1 10.10
date2 stock2 11.22
date4 stock2 13.22

i would like to get this matrix:
stock1 stock2
date1 10.22
date2 11.33 11.22
date3 12.33
date4 10.10 13.22

So basically i have asynchronious time series with missing data. there are three columns. date, stock id and price. i would like to make a matrix with column data and n columns for each stock.

Someone plz?

Subject: everything in one column. Need matrix.

From: Nathan

Date: 9 Feb, 2010 00:41:45

Message: 4 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

On Feb 8, 3:57 pm, "Peter " <han...@geemaaaiill.com> wrote:
> Hi. Have search already for couple hours but cannot find a solution. Suppose i have the following matrix
>
> '1-1-1999' 1
> '1-2-1999' 2
> '1-3-1999'
> '1-1-1999' 3
> '1-2-1999' 4
> '1-3-1999' 6
>
> but i need it in this format:
>
> '1-1-1999' 1 3
> '1-2-1999' 2 4
> '1-3-1999' 6
>
> indeed there is missing data, i need to have it. it is allowed to be NaN.
>
> could anyone give a hint? Thanks alot

One way, among many solutions:

A = {'1-1-1999' 1
'1-2-1999' 2
'1-3-1999' NaN
'1-1-1999' 3
'1-2-1999' 4
'1-3-1999' 6 };
[ix ix] = sort(A(:,1));
A = A(ix,:);
[b1 m1 n1] = unique(A(:,1),'first');
[b2 m2 n2] = unique(A(:,1));
newA = [b,A(m1,2),A(m2,2)]
%%%%%%%%%%%%%%%%%%%%%%
newA =
    '1-1-1999' [ 1] [3]
    '1-2-1999' [ 2] [4]
    '1-3-1999' [NaN] [6]

-Nathan

Subject: everything in one column. Need matrix.

From: Peter

Date: 9 Feb, 2010 00:51:05

Message: 5 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

Nathan <ngreco32@gmail.com> wrote in message <b69c67b0-7c58-40c6-9d4a-3e4059145cc1@s36g2000prh.googlegroups.com>...
> On Feb 8, 3:57 pm, "Peter " <han...@geemaaaiill.com> wrote:
> > Hi. Have search already for couple hours but cannot find a solution. Suppose i have the following matrix
> >
> > '1-1-1999' 1
> > '1-2-1999' 2
> > '1-3-1999'
> > '1-1-1999' 3
> > '1-2-1999' 4
> > '1-3-1999' 6
> >
> > but i need it in this format:
> >
> > '1-1-1999' 1 3
> > '1-2-1999' 2 4
> > '1-3-1999' 6
> >
> > indeed there is missing data, i need to have it. it is allowed to be NaN.
> >
> > could anyone give a hint? Thanks alot
>
> One way, among many solutions:
>
> A = {'1-1-1999' 1
> '1-2-1999' 2
> '1-3-1999' NaN
> '1-1-1999' 3
> '1-2-1999' 4
> '1-3-1999' 6 };
> [ix ix] = sort(A(:,1));
> A = A(ix,:);
> [b1 m1 n1] = unique(A(:,1),'first');
> [b2 m2 n2] = unique(A(:,1));
> newA = [b,A(m1,2),A(m2,2)]
> %%%%%%%%%%%%%%%%%%%%%%
> newA =
> '1-1-1999' [ 1] [3]
> '1-2-1999' [ 2] [4]
> '1-3-1999' [NaN] [6]
>
> -Nathan

Nathan, thanks a lot. But I forgot to include the stock id column in the first example.

It is a financial time series from the CRSP database.

I saw it is done by the command PROC TRANSPOSE but in a (unix based?) language/application called SAS. I guess what I really need is transpose and grouped by some variable. I will take a look into that now...

Subject: everything in one column. Need matrix.

From: Oleg Komarov

Date: 9 Feb, 2010 01:30:20

Message: 6 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

"Peter "
> Nathan
> > On Feb 8, 3:57 pm, "Peter "
> > > Hi. Have search already for couple hours but cannot find a solution. Suppose i have the following matrix
> > >
> > > '1-1-1999' 1
> > > '1-2-1999' 2
> > > '1-3-1999'
> > > '1-1-1999' 3
> > > '1-2-1999' 4
> > > '1-3-1999' 6
> > >
> > > but i need it in this format:
> > >
> > > '1-1-1999' 1 3
> > > '1-2-1999' 2 4
> > > '1-3-1999' 6
> > >
> > > indeed there is missing data, i need to have it. it is allowed to be NaN.
> > >
> > > could anyone give a hint? Thanks alot
> >
> > One way, among many solutions:
> >
> > A = {'1-1-1999' 1
> > '1-2-1999' 2
> > '1-3-1999' NaN
> > '1-1-1999' 3
> > '1-2-1999' 4
> > '1-3-1999' 6 };
> > [ix ix] = sort(A(:,1));
> > A = A(ix,:);
> > [b1 m1 n1] = unique(A(:,1),'first');
> > [b2 m2 n2] = unique(A(:,1));
> > newA = [b,A(m1,2),A(m2,2)]
> > %%%%%%%%%%%%%%%%%%%%%%
> > newA =
> > '1-1-1999' [ 1] [3]
> > '1-2-1999' [ 2] [4]
> > '1-3-1999' [NaN] [6]
> >
> > -Nathan
>
> Nathan, thanks a lot. But I forgot to include the stock id column in the first example.
>
> It is a financial time series from the CRSP database.
>
> I saw it is done by the command PROC TRANSPOSE but in a (unix based?) language/application called SAS. I guess what I really need is transpose and grouped by some variable. I will take a look into that now...

You can use Pivot fnc: http://www.mathworks.com/matlabcentral/fileexchange/26119-pivotunpivot

Oleg

Subject: everything in one column. Need matrix.

From: Peter

Date: 9 Feb, 2010 10:41:04

Message: 7 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

"Oleg Komarov" <oleg.komarovRemove.this@hotmail.it> wrote in message <hkqdrc$sur$1@fred.mathworks.com>...
> "Peter "
> > Nathan
> > > On Feb 8, 3:57 pm, "Peter "
> > > > Hi. Have search already for couple hours but cannot find a solution. Suppose i have the following matrix
> > > >
> > > > '1-1-1999' 1
> > > > '1-2-1999' 2
> > > > '1-3-1999'
> > > > '1-1-1999' 3
> > > > '1-2-1999' 4
> > > > '1-3-1999' 6
> > > >
> > > > but i need it in this format:
> > > >
> > > > '1-1-1999' 1 3
> > > > '1-2-1999' 2 4
> > > > '1-3-1999' 6
> > > >
> > > > indeed there is missing data, i need to have it. it is allowed to be NaN.
> > > >
> > > > could anyone give a hint? Thanks alot
> > >
> > > One way, among many solutions:
> > >
> > > A = {'1-1-1999' 1
> > > '1-2-1999' 2
> > > '1-3-1999' NaN
> > > '1-1-1999' 3
> > > '1-2-1999' 4
> > > '1-3-1999' 6 };
> > > [ix ix] = sort(A(:,1));
> > > A = A(ix,:);
> > > [b1 m1 n1] = unique(A(:,1),'first');
> > > [b2 m2 n2] = unique(A(:,1));
> > > newA = [b,A(m1,2),A(m2,2)]
> > > %%%%%%%%%%%%%%%%%%%%%%
> > > newA =
> > > '1-1-1999' [ 1] [3]
> > > '1-2-1999' [ 2] [4]
> > > '1-3-1999' [NaN] [6]
> > >
> > > -Nathan
> >
> > Nathan, thanks a lot. But I forgot to include the stock id column in the first example.
> >
> > It is a financial time series from the CRSP database.
> >
> > I saw it is done by the command PROC TRANSPOSE but in a (unix based?) language/application called SAS. I guess what I really need is transpose and grouped by some variable. I will take a look into that now...
>
> You can use Pivot fnc: http://www.mathworks.com/matlabcentral/fileexchange/26119-pivotunpivot
>
> Oleg

Spasibo Oleg. I will take a look at it.

Subject: everything in one column. Need matrix.

From: Oleg Komarov

Date: 9 Feb, 2010 12:38:04

Message: 8 of 8

Reply to this message

Add author to My Watch List

View original format

Flag as spam

"Peter "
> "Oleg Komarov"
> > "Peter "
> > > Nathan
> > > > On Feb 8, 3:57 pm, "Peter "
> > > > > Hi. Have search already for couple hours but cannot find a solution. Suppose i have the following matrix
> > > > >
> > > > > '1-1-1999' 1
> > > > > '1-2-1999' 2
> > > > > '1-3-1999'
> > > > > '1-1-1999' 3
> > > > > '1-2-1999' 4
> > > > > '1-3-1999' 6
> > > > >
> > > > > but i need it in this format:
> > > > >
> > > > > '1-1-1999' 1 3
> > > > > '1-2-1999' 2 4
> > > > > '1-3-1999' 6
> > > > >
> > > > > indeed there is missing data, i need to have it. it is allowed to be NaN.
> > > > >
> > > > > could anyone give a hint? Thanks alot
> > > >
> > > > One way, among many solutions:
> > > >
> > > > A = {'1-1-1999' 1
> > > > '1-2-1999' 2
> > > > '1-3-1999' NaN
> > > > '1-1-1999' 3
> > > > '1-2-1999' 4
> > > > '1-3-1999' 6 };
> > > > [ix ix] = sort(A(:,1));
> > > > A = A(ix,:);
> > > > [b1 m1 n1] = unique(A(:,1),'first');
> > > > [b2 m2 n2] = unique(A(:,1));
> > > > newA = [b,A(m1,2),A(m2,2)]
> > > > %%%%%%%%%%%%%%%%%%%%%%
> > > > newA =
> > > > '1-1-1999' [ 1] [3]
> > > > '1-2-1999' [ 2] [4]
> > > > '1-3-1999' [NaN] [6]
> > > >
> > > > -Nathan
> > >
> > > Nathan, thanks a lot. But I forgot to include the stock id column in the first example.
> > >
> > > It is a financial time series from the CRSP database.
> > >
> > > I saw it is done by the command PROC TRANSPOSE but in a (unix based?) language/application called SAS. I guess what I really need is transpose and grouped by some variable. I will take a look into that now...
> >
> > You can use Pivot fnc: http://www.mathworks.com/matlabcentral/fileexchange/26119-pivotunpivot
> >
> > Oleg
>
> Spasibo Oleg. I will take a look at it.
Ne za chto. I use it especially for time series manipulation like performances etc...

Oleg

Tags for this Thread

Everyone's Tags:

Add a New Tag:

Separated by commas
Ex.: root locus, bode

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Tag Activity for This Thread
Tag	Applied By	Date/Time
time	Peter	8 Feb, 2010 18:59:06
series	Peter	8 Feb, 2010 18:59:06

Feed for this Thread

MATLAB Central Terms of Use

NOTICE: Any content you submit to MATLAB Central, including personal information, is not subject to the protections which may be afforded information collected under other sections of The MathWorks, Inc. Web site. You are entirely responsible for all content that you upload, post, e-mail, transmit or otherwise make available via MATLAB Central. The MathWorks does not control the content posted by visitors to MATLAB Central and, does not guarantee the accuracy, integrity, or quality of such content. Under no circumstances will The MathWorks be liable in any way for any content not authored by The MathWorks, or any loss or damage of any kind incurred as a result of the use of any content posted, e-mailed, transmitted or otherwise made available via MATLAB Central. Read the complete Terms prior to use.