Re: st: RE: Reshape, Transpose... a (little) puzzling data manipulation

[John Litfiba <cariboupad@gmx.fr>]

Many Thanks Nick,

I will try your solution.
You are totally right, I limited myself to the reshape basic help
because I was not sure that reshape (a double reshape!) was indeed the
solution.
I will double check the next time ;-)

Best regards and many thanks again

On 29 September 2011 17:56, Nick Cox <n.j.cox@durham.ac.uk> wrote:
> This looks like a -reshape long- followed by a -reshape wide-.
>
> The way to understand this in broad terms is that you have a wide structure and you want another wide structure. -reshape- only goes from wide to long or the other way, so the solution is as above.
>
> This silly example adds a bit to the arm-waving above.
>
> clear
> input str7 ISIN type Date1 Date2 Date3
> "US00001"  1            1 2 3
> "US00001"  2            4 5 6
> "US00002"  1            1 2 3
> "US00002"  2            4 5 6
> end
> reshape long Date, i(ISIN type) j(price)
> reshape wide price, i(ISIN Date) j(type)
> l
>
> Your title shows that you know -reshape- might be the answer, so you could have explored some of the resources already available before posting. In particular, some awkward problems are discussed in
>
> FAQ     . . . . . . . . . . . . . . . . . . . . . . . .  Problems with reshape
>        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
>        8/05    I am having problems with the reshape command. Can
>                you give further guidance?
>                http://www.stata.com/support/faqs/data/reshape3.html
>
> and that FAQ emphasises that you may need _two_ -reshape-s.
>
>
> Nick
> n.j.cox@durham.ac.uk
>
> John Litfiba
>
> I have a little problem and I hope you will be able to help me:
>
> I have a dataset containing stock price data organized as follow :
>
> I know the unique identifier for each stock (ISIN) I have 4 types of
> prices (high, low, closing price, average) and then more than 7000
> dates...
>
>
>    ISIN       PRICE_type Date1 Date2 Date3 Date4 Date5 Date6 Date7
> ... date 7000
> 1 US00001  Type1            10      12  etc
> 2 US00001  Type2            15     11  etc
> 3 US00002  Type 1          120
> 4 US00002  Type 2          110
> 5
>
> So one big problem here is that the dates are column labels...
> Therefore each row gives me the whole history for a particular ISIN
> and Price combination.
> Instead, would like to obtain something like this
>
> Date        ISIN            PRICE_Type1 PRICE_Type2 PRICE_Type3 PRICE_Type4
> 1.Date1    US00001      10          15               10               20
> 2.Date2    US00001      12          11               10               20
> 3.Date1    US00002      120       110               10 .5            2
> 4.etc
> 5.
>
> Here each row gives the all relevant prices for a particular ISIN and
> a particular date
>
> Do you have any idea on how to proceed ? Dont ask me why my data is
> formatted like that please... ;-)
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
>

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/