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

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

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.

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?

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 <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...

"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

"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.

"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