re: st: reshape with j split

[David Airey <david.airey@vanderbilt.edu>]

Nick helped me figure out a reshape problem where "j" in the reshape 
command is split amongst several variables.


> I think you're moving in the right direction
> for a -reshape- to wide. As for the ANOVA,
> your original data structure looks better.
>
> egen j = concat(s1*), p("_")
> drop s1*
> reshape wide s2peak, i(animal) j(j) string
>
> Nick
> n.j.cox@durham.ac.uk
I was going from long to wide, to calculate a ratio of experimental 
conditions, and back to wide to calculate a mixed model on the ratios. 
What he suggested works nicely:

Here I have a sample of the data set:

     +----------------------------------------+
     | s1level   s1s2de~y   animal   s2peak~e |
     |----------------------------------------|
  1. |       0         50   1_1_0F     773.75 |
  2. |       0        100   1_1_0F    1001.63 |
  3. |      75         50   1_1_0F      472.5 |
  4. |      75        100   1_1_0F    927.875 |
  5. |      85         50   1_1_0F    611.375 |
  6. |      85        100   1_1_0F    654.375 |
     |----------------------------------------|
  7. |       0         50   1_1_1F    1116.88 |
  8. |       0        100   1_1_1F    1101.38 |
  9. |      75         50   1_1_1F    544.875 |
 10. |      75        100   1_1_1F    567.875 |
 11. |      85         50   1_1_1F    443.875 |
 12. |      85        100   1_1_1F        466 |
     |----------------------------------------|
 13. |       0         50   1_1_2F      309.5 |
 14. |       0        100   1_1_2F    336.286 |
 15. |      75         50   1_1_2F    442.625 |
     +----------------------------------------+

And I wanted to get to:

animal s2peak0_50 s2peak0_100 s2peak75_50 s2peak75_100 s2peak85_50 
s2peak85_100

The following code does just that and then returns to long format 
appropriate to the ANOVA.

* reshape to wide
egen j = concat(s1*), p("_")
drop s1*
reshape wide s2peak, i(animal) j(j) string

. clist in 1/3

       animal  s2~0_100  s2p~0_50  s~75_100  s2~75_50  s~85_100  
s2~85_50
  1.   1_1_0F   1001.63    773.75   927.875     472.5   654.375   
611.375
  2.   1_1_1F   1101.38   1116.88   567.875   544.875       466   
443.875
  3.   1_1_2F   336.286     309.5       265   442.625   192.375     
264.5


* calculate ppi
gen startle = (s2peakvalue0_50 + s2peakvalue0_100)/2
gen ppi75_50 = s2peakvalue75_50 / startle * 100
gen ppi85_50 = s2peakvalue85_50 / startle * 100
gen ppi75_100 = s2peakvalue75_100 / startle * 100
gen ppi85_100 = s2peakvalue85_100 / startle * 100
drop s2* startle

. clist in 1/3

       animal   ppi75_50   ppi85_50  ppi75_100  ppi85_100
  1.   1_1_0F   53.22819   68.87277   104.5272   73.71682
  2.   1_1_1F   49.12656   40.02029   51.20027    42.0151
  3.   1_1_2F   137.0811   81.91572   82.07057   59.57859

*reshape to long
reshape long ppi, i(animal) j(treatment) string
gen str s1level_str = substr(treatment, 1, index(treatment, "_") - 1)
gen str s1s2delay_str = substr(treatment, index(treatment, "_") + 1, .)
encode s1level_str, generate(s1level)
encode s1s2delay_str, generate(s1s2delay)
drop *_str treatment

. list, sep(4)

     +----------------------------------------+
     | animal        ppi   s1level   s1s2de~y |
     |----------------------------------------|
  1. | 1_1_0F   104.5272        75        100 |
  2. | 1_1_0F   53.22819        75         50 |
  3. | 1_1_0F   73.71682        85        100 |
  4. | 1_1_0F   68.87277        85         50 |
     |----------------------------------------|
  5. | 1_1_1F   51.20027        75        100 |
  6. | 1_1_1F   49.12656        75         50 |
  7. | 1_1_1F    42.0151        85        100 |
  8. | 1_1_1F   40.02029        85         50 |
     |----------------------------------------|
  9. | 1_1_2F   82.07057        75        100 |
 10. | 1_1_2F   137.0811        75         50 |

etc.

Thank you, Nick.

For comparison, I had originally made use of Stata's "_n" to calculate 
what I needed directly from the long format:

egen step = seq(), from(0) to(5) block(1)
gen ppi1 = (s2peak[_n-step] - s2peak[_n])/(s2peak[_n-step])*100 ///
	if s1s2delay == 50
replace ppi1 = (s2peak[_n-step+1] - s2peak[_n])/(s2peak[_n-step+1])*100 
///
	if s1s2delay == 100
gen ppi2 = ((s2peak[_n-step]+s2peak[_n-step+1])/2 - 
s2peak[_n])/((s2peak[_n-step]+s2peak[_n-step+1])/2)*100
drop if s1level == 0

which works, but wants no missing values. That is easy to assert before 
running, but I was curious to also get the results using reshape, which 
I avoided for lack of experience. Now I know!

*
*   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/