Reshape wide to long

[Francois Durant]

Hi,

I have the following dataset:

 
gp
variable
t1
t2

1
1
0.010
0.012

1
2
0.009
0.019

1
3
0.007
0.015

1
4
0.007
0.010

1
5
0.005
0.004

1
6
0.011
0.012

1
7
0.017
0.022

1
8
0.006
0.006

1
9
0.009
0.004

1
10
0.010
0.015

5
1
-0.006
0.006

5
2
-0.011
0.009

5
3
-0.011
0.010

5
4
-0.003
0.007

5
5
0.000
-0.007

5
6
0.008
-0.006

5
7
0.012
-0.002

5
8
-0.012
-0.005

5
9
-0.005
0.004

5
10
-0.003
0.004

That I would like to reshape as follow:

t	gp	variable	ret
1	1	1	0.010
1	1	2	0.009
1	1	3	0.007
1	1	4	0.007
1	1	5	0.005
1	1	6	0.011
1	1	7	0.017
1	1	8	0.006
1	1	9	0.009
1	1	10	0.010
1	5	1	-0.006
1	5	2	-0.011
1	5	3	-0.011
1	5	4	-0.003
1	5	5	0.000
1	5	6	0.008
1	5	7	0.012
1	5	8	-0.012
1	5	9	-0.005
1	5	10	-0.003
2	1	1	0.012
2	1	2	0.019
2	1	3	0.015
2	1	4	0.010
2	1	5	0.004
2	1	6	0.012
2	1	7	0.022
2	1	8	0.006
2	1	9	0.004
2	1	10	0.015
2	5	1	0.006
2	5	2	0.009
2	5	3	0.010
2	5	4	0.007
2	5	5	-0.007
2	5	6	-0.006
2	5	7	-0.002
2	5	8	-0.005
2	5	9	0.004
2	5	10	0.004

I have tried unsuccessfully:

reshape long t, i(gp) j(variable)

Can anyone help? Thanks

[Carlo Lazzaro]

Florent:
you may want to take a look at -stack-.

[Francois Durant]

Carlo,

I have tried the following:

stack t1 t2, into(t)

But I'm loosing "gp" and "variable". Any suggestion?

[Carlo Lazzaro]

Florent:
I do hope that what follows can give you some guidance:

import excel "C:\Users\user\Desktop\RF.xlsx", sheet("Foglio1") firstrow///you can skip this line of code
save "C:\Users\user\Desktop\FR_1.dta"
save "C:\Users\user\Desktop\FR_2.dta"
use "C:\Users\user\Desktop\FR_1.dta", clear
drop t1 t2
expand 2
g id=_n
save "C:\Users\user\Desktop\FR_1.dta", replace
use "C:\Users\user\Desktop\FR_2.dta", clear
drop gp variable
stack t1 t2, into(t)
drop _stack
g id=_n
save "C:\Users\user\Desktop\FR_2.dta", replace
use "C:\Users\user\Desktop\FR_1.dta", clear
merge 1:1 _n using "C:\Users\user\Desktop\FR_2.dta", keepusing(t)
drop _merge id

[Joseph Chrestien]

Hi there, I think what you are looking for is : reshape long t, i(gp variable)
Best regards,
Joseph

. clear

. input  gp  variable  t1  t2

            gp   variable         t1         t2
  1. 1 1 0.010 0.012
  2. 1 2 0.009 0.019
  3. 1 3 0.007 0.015
  4. 1 4 0.007 0.010
  5. 1 5 0.005 0.004
  6. 1 6 0.011 0.012
  7. 1 7 0.017 0.022
  8. 1 8 0.006 0.006
  9. 1 9 0.009 0.004
 10. 1 10 0.010 0.015
 11. 5 1 -0.006 0.006
 12. 5 2 -0.011 0.009
 13. 5 3 -0.011 0.010
 14. 5 4 -0.003 0.007
 15. 5 5 0.000 -0.007
 16. 5 6 0.008 -0.006
 17. 5 7 0.012 -0.002
 18. 5 8 -0.012 -0.005
 19. 5 9 -0.005 0.004
 20. 5 10 -0.003 0.004
 21. end

. reshape long t, i(gp variable)
(note: j = 1 2)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                       20   ->      40
Number of variables                   4   ->       4
j variable (2 values)                     ->   _j
xij variables:
                                  t1 t2   ->   t
-----------------------------------------------------------------------------

. list

     +----------------------------+
     | gp   variable   _j       t |
     |----------------------------|
  1. |  1          1    1     .01 |
  2. |  1          1    2    .012 |
  3. |  1          2    1    .009 |
  4. |  1          2    2    .019 |
  5. |  1          3    1    .007 |
     |----------------------------|
  6. |  1          3    2    .015 |
  7. |  1          4    1    .007 |
  8. |  1          4    2     .01 |
  9. |  1          5    1    .005 |
 10. |  1          5    2    .004 |
     |----------------------------|
 11. |  1          6    1    .011 |
 12. |  1          6    2    .012 |
 13. |  1          7    1    .017 |
 14. |  1          7    2    .022 |
 15. |  1          8    1    .006 |
     |----------------------------|
 16. |  1          8    2    .006 |
 17. |  1          9    1    .009 |
 18. |  1          9    2    .004 |
 19. |  1         10    1     .01 |
 20. |  1         10    2    .015 |
     |----------------------------|
 21. |  5          1    1   -.006 |
 22. |  5          1    2    .006 |
 23. |  5          2    1   -.011 |
 24. |  5          2    2    .009 |
 25. |  5          3    1   -.011 |
     |----------------------------|
 26. |  5          3    2     .01 |
 27. |  5          4    1   -.003 |
 28. |  5          4    2    .007 |
 29. |  5          5    1       0 |
 30. |  5          5    2   -.007 |
     |----------------------------|
 31. |  5          6    1    .008 |
 32. |  5          6    2   -.006 |
 33. |  5          7    1    .012 |
 34. |  5          7    2   -.002 |
 35. |  5          8    1   -.012 |
     |----------------------------|
 36. |  5          8    2   -.005 |
 37. |  5          9    1   -.005 |
 38. |  5          9    2    .004 |
 39. |  5         10    1   -.003 |
 40. |  5         10    2    .004 |
     +----------------------------+