Reshape table (pivot table)

[Rex Rutte]

Dear stata-users,

I am writing you, because I hope that anyone here could help me out with your knowledge. Until now, I am not be able to solve it on my own by investigating the help from the Stata manual and forums. It would be wonderful if anyone have good advice!

I want to transpose my panel data, to make my situation more clear, I will illustrate my problem with an example of my panel data.
My own data consists of more than 600 banks over almost 10 years, but to make it easy I will illustrate my date with 3 banks for three days. This is the example of the current dataset:

permno date ticker Company name Cusip Ret
1. 10002 02/07/2007 BTFG Banctrust 05978R19 -2.892
2. 10002 03/07/2007 BTFG Banctrust 05978R19 1.434
3. 10002 04/07/2007 BTFG Banctrust 05978R19 2.324
4. 20395 02/07/2007 WJMK Warrior 61778Q19 1.343
5. 20395 03/07/2007 WJMK Warrior 61778Q19 3.233
6. 20395 04/07/2007 WJMK Warrior 61778Q19 2.233
7. 40234 02/07/2007 BJBP Banc Jones 20124B41 3.233
8. 40234 03/07/2007 BJBP Banc Jones 20124B41 -1.343
9.40234 04/07/2007 BJPP Banc Jones 20124B41 1.234

- Permno is an identifier of the company
- Date = Date
- Ret = Return of the shareprice

Now I want to transpose (like a pivot table in excel) the data to the following new data set:
date permno permno permno
10002 20395 40234
1. 02/07/2007 -2.892 1.343 3.233
2. 03/07/2007 1.434 3.233 -1.343
3. 04/07/2007 2.324 2.233 1.234

So in the new situation I want to have the permno (id) in the column. I want the date in the row. And as values, I want ret (the returns of the share prices of the company).

The problem now is when I use the following command: reshape wide ret, i(permno) j(date) , the problem with this command is that id (permno) stays in the row and you can not choose you what you want in the column, row or as values. I have dropped the other variables that I don't need, otherwise the above command won't work.

I hope that someone could help me out with this command to reach my goal. I am looking forward to hearing of someone, that would be awesome.

[Clyde Schechter]

-reshape- takes a little getting used to. Keep practicing with it and at some point you will have an "aha!" moment, and after that you will always immediately know just how to use it.

In this case, what you want is:

keep permno date ret
reshape wide ret, i(date) j(permno)

So you were on the right track, but you got i() and j() backwards.

But here's some better advice: don't do this! I don't know what you plan to do with your data next, but Stata is optimized to do most analyses with the data in the long layout that you are starting from. If you -reshape- the way you asked for, it is likely that your next post here will be to inquire how to do some analysis that you can't quite figure out and the response will be that the first step is to put your data back into long layout. So unless all you plan to do with this data is export it to a spreadsheet or a word processor in a table that is meant for human eyes, or use one of a very small number of Stata commands that is set up to use wide data, doing what you proposed will be a waste of your time.

[Rex Rutte]

Dear professor Schechter,

Thank you for you're prompt answer and for correcting me on the fault in my command. However, the command is not exactly what I want. Hopefully, I can make it more clear with the following illustrations.

This is my current dataset:

I want to go to this dataset:

Unfortunately with the command in the previous post-->
code: reshape wide ret, i(date) j(permno), I retrieved the following wrong dataset:

So, I want to have the permno (id) in the column. And I want the date in the row. As last, I want the values as ret (the returns of the share prices of the company). I hope you can help me on this subject. You're indeed right that I reshape my dataset in this form for another program. I am looking forward for a possible solution!

[Jorrit Gosens]

This:

But here's some better advice: don't do this! [..] If you -reshape- the way you asked for, it is likely that your next post here will be to inquire how to do some analysis that you can't quite figure out and the response will be that the first step is to put your data back into long layout.

was the answer to your question, really.

Even if you want this because you feel the data is easier to read or something, just forget about the reshape for now, and instead explain what type of calculation or analysis you are trying to do, and how to do that.

[Rex Rutte]

I need the dataset in this way so I can run it in Matlab to calculate the Value at Risk. Therefore, I am wondering if someone could help me on this reshape command, otherwise I can not see immediately another way to solve this situation.

[Jorrit Gosens]

Its likely that that could be calculated in Stata too. But if you have the code ready in matstat and are just using Stata as a go between for data handling, then sure, maybe it makes sense. Could give you a rough code, but its tons easier if you use dataex to privode a data sample, see also http://www.statalist.org/forums/help#stata

[Jorrit Gosens]

approximately:

webuse grunfeld
tostring company year invest mvalue kstock time, replace force
foreach var of varlist _all{
replace `var' = "`var'" in 1
}
foreach var of varlist _all{
replace `var' = substr(`var',4,.) in 1
}

[Rex Rutte]

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date str8 ticker str36 comnam str8 cusip double prc float(lagprc ret)
10002 17349 "BTFG" "BANCTRUST FINANCIAL GROUP INC"    "05978R10"  21.170000076293945     .          .
10002 17350 "BTFG" "BANCTRUST FINANCIAL GROUP INC"    "05978R10"  20.559999465942383 21.17  -2.881439
90086 17349 "MBVT" "MERCHANTS BANCSHARES INC"         "58844810"  24.239999771118164     .          .
90086 17350 "MBVT" "MERCHANTS BANCSHARES INC"         "58844810"    23.8799991607666 24.24  -1.485151
91955 17349 "ASFN" "ATLANTIC SOUTHERN FINL GROUP INC" "04887710"   34.38999938964844     .          .
91955 17350 "ASFN" "ATLANTIC SOUTHERN FINL GROUP INC" "04887710" -34.834999084472656 34.39 -201.29398
end
format %tdDD/NN/CCYY date

[Rex Rutte]

Dear professor Gosens,

I think I am doing it at the right way this time. And therefore I hope that I can clarify this time the problem better.

I show a part of my dataset, since my dataset consists of 610 banks over a daily time between q32007 till q4 2016.

My current dataset looks like this:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date float ret
10002 17349          .
10002 17350  -2.881439
90086 17349          .
90086 17350  -1.485151
91955 17349          .
91955 17350 -201.29398
end
format %tdDD/NN/CCYY date

------------------ copy up to and including the previous line ------------------

But I want my sample to be like this, where A= date; B=10002; C=90086; D=91955:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input int A double(B C D)
17349         .         .        .
17350 -2.881439 -1.485151 -201.294
    .         .         .        .
    .         .         .        .
    .         .         .        .
    .         .         .        .
end
format %tdnn/dd/CCYY A

------------------ copy up to and including the previous line ------------------


It would be great if you can help me on this. I am looking forward to hearing from you!

[Jorrit Gosens]

keep permno date ret
reshape wide ret, i(date) j(permno)
expand 2 in 1

ren date dat00000
foreach var of varlist _all{
local permno= substr("`var'",4,.)
replace `var' = `permno' in 1
}
ren dat00000 date
tostring date, replace
replace date = "date" in 1

Im no professor, btw. Just Jorrit will do.