Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


From   Nick Cox <n.j.cox@durham.ac.uk>
To   "'statalist@hsphsun2.harvard.edu'" <statalist@hsphsun2.harvard.edu>
Subject   st: RE: Reshape, Transpose... a (little) puzzling data manipulation
Date   Thu, 29 Sep 2011 16:56:32 +0100

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/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index