Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: Reshaping data from long to wide


From   Philip Ryan <philip.ryan@adelaide.edu.au>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Reshaping data from long to wide
Date   Mon, 12 Apr 2004 20:05:31 +0930

I don't immediately see a _very_ easy way, but this seems to work:

. list

     +-------------------------------------------------+
     | id   X1Y1Z1   X1Y1Z2   X1Y2Z1   X2Y1Z5   X2Y2Z6 |
     |-------------------------------------------------|
  1. |  1       10       20       30       40       50 |
  2. |  2       10       20       30       40       50 |
     +-------------------------------------------------+

The trick is to reconstruct your variable names by placing the x,y and 
z "coordinates" (in that order) at the end of the variable name, so that a 
subsequent -reshape- command has something valid to grab:

. foreach var of varlist _all {
  2. local s1 = substr("`var'", 2,1)
  3. local s2 = substr("`var'", 4,1)
  4. local s3 = substr("`var'", 6,1)
  5. rename `var'  score`s1'`s2'`s3'
  6. }

. rename scored id  // oops, _all was a bit too inclusive!
. li

     +-----------------------------------------------------------+
     | id   score111   score112   score121   score215   score226 |
     |-----------------------------------------------------------|
  1. |  1         10         20         30         40         50 |
  2. |  2         10         20         30         40         50 |
     +-----------------------------------------------------------+

. reshape long score , i(id) j(xyz)
. sort id
. li

     +------------------+
     | id   xyz   score |
     |------------------|
  1. |  1   111      10 |
  2. |  1   112      20 |
  3. |  1   121      30 |
  4. |  1   215      40 |
  5. |  1   226      50 |
     |------------------|
  6. |  2   111      10 |
  7. |  2   112      20 |
  8. |  2   121      30 |
  9. |  2   215      40 |
 10. |  2   226      50 |
     +------------------+

Now break up xyz into its components, here is one way:

. gen x = int(xyz/100)
. gen y = int((xyz-100*x)/10)
. gen z = xyz - ((100*x)+(10*y))
. li

     +------------------------------+
     | id   xyz   score   x   y   z |
     |------------------------------|
  1. |  1   111      10   1   1   1 |
  2. |  1   112      20   1   1   2 |
  3. |  1   121      30   1   2   1 |
  4. |  1   215      40   2   1   5 |
  5. |  1   226      50   2   2   6 |
     |------------------------------|
  6. |  2   111      10   1   1   1 |
  7. |  2   112      20   1   1   2 |
  8. |  2   121      30   1   2   1 |
  9. |  2   215      40   2   1   5 |
 10. |  2   226      50   2   2   6 |
     +------------------------------+


I hope this helps.


Phil


Quoting Pradeep Kurukulasuriya <pradeep.kurukulasuriya@yale.edu>:

> I would be most grateful for some help with the syntax for reshaping
> data from wide to long. I have not previously used the reshape and stack
> commands much so i am unfamiliar with them. 
> 
> Basically what i trying to do is stack/reshape the following variables,
> x[i]y[j]z[k] where i={1,3}, j={1,2} and k={1,2,3,4,5,6}. These are
> variables which reference crop values in season (x), plot (y) and
> croptype (z).
> 
> So if the data looks something like this:
> 
> id    X1Y1Z1 	    X1Y1Z2     X1Y2Z1    	X2Y1Z5  	  X2Y2Z6
> 1        10            20            30            40            50
> 2        10            20            30            40            50
> .....
> 
> and i am trying to get to this:
> 
> id        season    	plot        crop      value
> 1            1            1            1        10
> 1            1            1            2        20
> 1            1            2            1        30
> 1            2            1            5        40                    
> 1            2            2            6        50
> 2            1            1            1        10
> 2            1            1            2        20 
> 2            1            2            1        30
> 2            2            1            5        50
> etc....
> 
> Thanks
> Pradeep
> 
> 
> 
> 
> 
> 
> 
> 
> 
> ------------------------------------------------------------
> Pradeep Kurukulasuriya
> Yale University -School of Forestry and Environment
> Email: pradeep.kurukulasuriya@yale.edu
> Tel/fax: 718-623 0835 (home)
> Mobile: 718 938 9965
> 
> 
> *
> *   For searches and help try:
> *   http://www.stata.com/support/faqs/res/findit.html
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
> 


-- 
Philip Ryan
Associate Professor
Department of Public Health

Associate Dean (IT)
Faculty of Health Sciences

Head, Data Management and Analysis Centre
Department of Public Health and Department of General Practice

University of Adelaide
5005 South Australia
AUSTRALIA
CRICOS Provider Number 00123M
----------------------------------------------------------- 
This email message is intended only for the addressee(s) 
and contains information that may be confidential and/or 
copyright.  If you are not the intended recipient please 
notify the sender by reply email and immediately delete 
this email. Use, disclosure or reproduction of this email 
by anyone other than the intended recipient(s) is strictly 
prohibited. No representation is made that this email or 
any attachments are free of viruses. Virus scanning is 
recommended and is the responsibility of the recipient.
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   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   |   What's new   |   Site index