re: st: reshape with j split

 From David Airey <[email protected]> To [email protected] Subject re: st: reshape with j split Date Mon, 15 Dec 2003 10:59:52 -0600

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
[email protected]
```
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/