Reshape matrix (panel data)

I have a matrix with N individual stocks with up to T observations each. The matrix has four columns: column one describes time, ranging from 1:T, column two describes the individual from 1 to N, column three describes weight of individual i at time t, and column four describes price of individual i at time t. (The data is downloaded from CRSP into Stata, but I'd like to apply MatLab for the next step of the analysis)

The thing is that not all individuals has observations from 1 through T, but may start at t=10, or end at t=T-k (for some number k).

If I had observations for all individual at all times, I would've used B=reshape(data,T,4*N).

Does anybody have any suggestion for how to deal with this problem?

[Azzi Abdelmalek]

To make your question clear, just give a numeric example, and show what you are expecting as result. It's not important to know if your variable is a speed or a flow.

[Thomas Leirvik]

Thanks for the reply!

I have A = [1 1 0.5 3; 2 1 0.25 2; 1 2 0.5 10; 2 2 0.5 11; 2 3 0.25 6];

and I want

B = [0.5 0.5 0 3 10 0; 0.25 0.5 0.25 2 11 6]

This means that in A, column 1 describes time, for which I have observations of individual 1 and 2, but not 3. Column 2 describes individual. Column 3 describes weights (which sum to 1 for each time-step) and column 4 describes prices at time t.

Answer 1

[Kelly Kearney]

Might this function help: vec2grid?

[Thomas Leirvik]

Thanks for the info, but I'm not sure if I can apply it, as the matrix I have does not have any missing values. If the price of stock i starts at t=k (k>1) instead of t=1, then the block for stock i will just be shorter than one with observations from 1:T. See my reply above for matrix A and the result B.

Maybe I misunderstood how the vec2grid works, but I tried on my data, and got an error message.

[Kelly Kearney]

Using your example from above:

a = [1 1 0.5 3; 2 1 0.25 2; 1 2 0.5 10; 2 2 0.5 11; 2 3 0.25 6];

[row, col, price]  = vec2grid(a(:,[1 2 4]));
[row, col, weight] = vec2grid(a(:,[1 2 3]));

>> [weight price]

 ans =

   Columns 1 through 5

          0.5          0.5          NaN            3           10
         0.25          0.5         0.25            2           11

   Column 6

          NaN
            6

Looks like your B, except using NaN instead of 0 as a placeholder, which is a quick fix.

 B = [weight price];
 B(isnan(B)) = 0;

[Thomas Leirvik]

Wow, it works smooth! Fantastic! Thanks!!