Reshaping panel data

[Tom regt]

Dear Statalist,

I'm having difficulties with reshaping my paneldata. I have five different service variables(service1, service2, service3, service4 and service5). I have data on these variables for 1999-2014 for all 50 U.S. states.

The way that I have now arranged them is(in short):

 
  			
State
Year 
service1
service2
service3
service4
service5

1
1999
0,160
0,013
0,041
0,074
0,288

1
2000
0,161
0,014
0,041
0,076
0,292

1
2001
0,161
0,015
0,041
0,079
0,296

1
2002
0,158
0,014
0,041
0,077
0,291

1
2003
0,155
0,013
0,041
0,079
0,288

1
2004
0,154
0,012
0,040
0,079
0,285

1
2005
0,152
0,012
0,039
0,082
0,285

1
2006
0,151
0,011
0,038
0,083
0,284

1
2007
0,150
0,011
0,038
0,084
0,283

1
2008
0,152
0,011
0,039
0,086
0,288

1
2009
0,150
0,010
0,039
0,084
0,284

1
2010
0,146
0,010
0,038
0,083
0,276

1
2011
0,145
0,009
0,037
0,085
0,276

1
2012
0,146
0,009
0,036
0,086
0,277

1
2013
0,145
0,009
0,037
0,086
0,276

1
2014
0,145
0,009
0,037
0,086
0,277

2
1999
0,155
0,017
0,030
0,065
0,267

2
2000
0,155
0,020
0,030
0,065
0,270

2
2001
0,151
0,019
0,029
0,062
0,261

2
2002
0,151
0,018
0,028
0,060
0,257

2
2003
0,151
0,017
0,029
0,061
0,257

2
2004
0,147
0,017
0,029
0,059
0,252

2
2005
0,148
0,016
0,030
0,058
0,252

2
2006
0,147
0,016
0,029
0,058
0,251

But I would like to reshape it in a way so that it becomes like this:

 
State
Year
Service

1
1999
1

1
1999
2

1
1999
3

1
1999
4

1
1999
5

1
2000
1

1
2000
2

1
2000
3

1
2000
4

1
2000
5

Etc.

I can not seem to figure out how to do this.
Is there anyone who could help me out?

Kind regards,
Tom

[Nick Cox]

You should give code you tried, even code that does not work. Only that way can we explain what you had wrong.

Here the key step is to recognise that the observations are identified jointly by State and Year.

. input State     Year    service1        service2        service3        service4  
>       service5

         State       Year   service1   service2   service3   service4   service5
  1. 1       1999    0.160   0.013   0.041   0.074   0.288
  2. 1       2000    0.161   0.014   0.041   0.076   0.292
  3. 1       2001    0.161   0.015   0.041   0.079   0.296
  4. 1       2002    0.158   0.014   0.041   0.077   0.291
  5. 1       2003    0.155   0.013   0.041   0.079   0.288
  6. 1       2004    0.154   0.012   0.040   0.079   0.285
  7. 1       2005    0.152   0.012   0.039   0.082   0.285
  8. 1       2006    0.151   0.011   0.038   0.083   0.284
  9. 1       2007    0.150   0.011   0.038   0.084   0.283
 10. 1       2008    0.152   0.011   0.039   0.086   0.288
 11. 1       2009    0.150   0.010   0.039   0.084   0.284
 12. 1       2010    0.146   0.010   0.038   0.083   0.276
 13. 1       2011    0.145   0.009   0.037   0.085   0.276
 14. 1       2012    0.146   0.009   0.036   0.086   0.277
 15. 1       2013    0.145   0.009   0.037   0.086   0.276
 16. 1       2014    0.145   0.009   0.037   0.086   0.277
 17. 2       1999    0.155   0.017   0.030   0.065   0.267
 18. 2       2000    0.155   0.020   0.030   0.065   0.270
 19. 2       2001    0.151   0.019   0.029   0.062   0.261
 20. 2       2002    0.151   0.018   0.028   0.060   0.257
 21. 2       2003    0.151   0.017   0.029   0.061   0.257
 22. 2       2004    0.147   0.017   0.029   0.059   0.252
 23. 2       2005    0.148   0.016   0.030   0.058   0.252
 24. 2       2006    0.147   0.016   0.029   0.058   0.251
 25. end 

. 
. reshape long service , i(State Year) 
(note: j = 1 2 3 4 5)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                       24   ->     120
Number of variables                   7   ->       4
j variable (5 values)                     ->   _j
xij variables:
         service1 service2 ... service5   ->   service
-----------------------------------------------------------------------------

. rename service value 

. rename _j service 

. 
. list, sepby(State Year) 

     +--------------------------------+
     | State   Year   service   value |
     |--------------------------------|
  1. |     1   1999         1     .16 |
  2. |     1   1999         2    .013 |
  3. |     1   1999         3    .041 |
  4. |     1   1999         4    .074 |
  5. |     1   1999         5    .288 |
     |--------------------------------|
  6. |     1   2000         1    .161 |
  7. |     1   2000         2    .014 |
  8. |     1   2000         3    .041 |
  9. |     1   2000         4    .076 |
 10. |     1   2000         5    .292 |
     |--------------------------------|
 11. |     1   2001         1    .161 |
 12. |     1   2001         2    .015 |
 13. |     1   2001         3    .041 |
 14. |     1   2001         4    .079 |
 15. |     1   2001         5    .296 |
     |--------------------------------|
 16. |     1   2002         1    .158 |
 17. |     1   2002         2    .014 |
 18. |     1   2002         3    .041 |
 19. |     1   2002         4    .077 |
 20. |     1   2002         5    .291 |
     |--------------------------------|
 21. |     1   2003         1    .155 |
 22. |     1   2003         2    .013 |
 23. |     1   2003         3    .041 |
 24. |     1   2003         4    .079 |
 25. |     1   2003         5    .288 |
     |--------------------------------|
 26. |     1   2004         1    .154 |
 27. |     1   2004         2    .012 |
 28. |     1   2004         3     .04 |
 29. |     1   2004         4    .079 |
 30. |     1   2004         5    .285 |
     |--------------------------------|
 31. |     1   2005         1    .152 |
 32. |     1   2005         2    .012 |
 33. |     1   2005         3    .039 |
 34. |     1   2005         4    .082 |
 35. |     1   2005         5    .285 |
     |--------------------------------|
 36. |     1   2006         1    .151 |
 37. |     1   2006         2    .011 |
 38. |     1   2006         3    .038 |
 39. |     1   2006         4    .083 |
 40. |     1   2006         5    .284 |
     |--------------------------------|
 41. |     1   2007         1     .15 |
 42. |     1   2007         2    .011 |
 43. |     1   2007         3    .038 |
 44. |     1   2007         4    .084 |
 45. |     1   2007         5    .283 |
     |--------------------------------|
 46. |     1   2008         1    .152 |
 47. |     1   2008         2    .011 |
 48. |     1   2008         3    .039 |
 49. |     1   2008         4    .086 |
 50. |     1   2008         5    .288 |
     |--------------------------------|
 51. |     1   2009         1     .15 |
 52. |     1   2009         2     .01 |
 53. |     1   2009         3    .039 |
 54. |     1   2009         4    .084 |
 55. |     1   2009         5    .284 |
     |--------------------------------|
 56. |     1   2010         1    .146 |
 57. |     1   2010         2     .01 |
 58. |     1   2010         3    .038 |
 59. |     1   2010         4    .083 |
 60. |     1   2010         5    .276 |
     |--------------------------------|
 61. |     1   2011         1    .145 |
 62. |     1   2011         2    .009 |
 63. |     1   2011         3    .037 |
 64. |     1   2011         4    .085 |
 65. |     1   2011         5    .276 |
     |--------------------------------|
 66. |     1   2012         1    .146 |
 67. |     1   2012         2    .009 |
 68. |     1   2012         3    .036 |
 69. |     1   2012         4    .086 |
 70. |     1   2012         5    .277 |
     |--------------------------------|
 71. |     1   2013         1    .145 |
 72. |     1   2013         2    .009 |
 73. |     1   2013         3    .037 |
 74. |     1   2013         4    .086 |
 75. |     1   2013         5    .276 |
     |--------------------------------|
 76. |     1   2014         1    .145 |
 77. |     1   2014         2    .009 |
 78. |     1   2014         3    .037 |
 79. |     1   2014         4    .086 |
 80. |     1   2014         5    .277 |
     |--------------------------------|
 81. |     2   1999         1    .155 |
 82. |     2   1999         2    .017 |
 83. |     2   1999         3     .03 |
 84. |     2   1999         4    .065 |
 85. |     2   1999         5    .267 |
     |--------------------------------|
 86. |     2   2000         1    .155 |
 87. |     2   2000         2     .02 |
 88. |     2   2000         3     .03 |
 89. |     2   2000         4    .065 |
 90. |     2   2000         5     .27 |
     |--------------------------------|
 91. |     2   2001         1    .151 |
 92. |     2   2001         2    .019 |
 93. |     2   2001         3    .029 |
 94. |     2   2001         4    .062 |
 95. |     2   2001         5    .261 |
     |--------------------------------|
 96. |     2   2002         1    .151 |
 97. |     2   2002         2    .018 |
 98. |     2   2002         3    .028 |
 99. |     2   2002         4     .06 |
100. |     2   2002         5    .257 |
     |--------------------------------|
101. |     2   2003         1    .151 |
102. |     2   2003         2    .017 |
103. |     2   2003         3    .029 |
104. |     2   2003         4    .061 |
105. |     2   2003         5    .257 |
     |--------------------------------|
106. |     2   2004         1    .147 |
107. |     2   2004         2    .017 |
108. |     2   2004         3    .029 |
109. |     2   2004         4    .059 |
110. |     2   2004         5    .252 |
     |--------------------------------|
111. |     2   2005         1    .148 |
112. |     2   2005         2    .016 |
113. |     2   2005         3     .03 |
114. |     2   2005         4    .058 |
115. |     2   2005         5    .252 |
     |--------------------------------|
116. |     2   2006         1    .147 |
117. |     2   2006         2    .016 |
118. |     2   2006         3    .029 |
119. |     2   2006         4    .058 |
120. |     2   2006         5    .251 |
     +--------------------------------+

[Tom regt]

Thank you very much for your help, this has helped me to rearrange the data in Stata.
And you are right, next time i should give the codes that I tried.
Kind regards,
Tom